Sunday, May 22, 2011

MySql query to get all the child tables for given parent table

SELECT c.table_schema,u.table_name,u.column_name,u.referenced_table_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
AND u.referenced_table_schema='db'
AND u.referenced_table_name = 'table'
ORDER BY c.table_schema,u.table_name;

Saturday, May 21, 2011

MySql InnoDB tables getting corrupted

One not-so-common issue I faced when my db crashed because of disk-space issue on the server machine.
After cleaning up the space, when I tried to restart the database and recreated the schema, all the tables were getting created of type MYISAM even though the db create script as well as my.cnf enforced the tables to be InnoDB.

Resolution: The issue can get resolved by deleting ib* files from /var/lib/mysql.
cd /var/lib/mysql
rm ib*
/etc/init.d/mysqld restart