CodeIgniter Forums
dbutil restore from backup problem (order of tables) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: dbutil restore from backup problem (order of tables) (/showthread.php?tid=30838)



dbutil restore from backup problem (order of tables) - El Forum - 05-27-2010

[eluser]Jaffer Haider[/eluser]
Hey guys,

I'm using the internal dbutil ($this->load->dbutil()) to backup a database. Backup works fine, but when I run the .sql file to restore the database, I run into a problem because the tables are sorted alphabetically, and not by their foreign key constraints.

I've used phpMyAdmin's import feature, as well as simply pasting in the SQL script into the SQL tab of phpMyAdmin (after creating an empty database).

I don't see any topic in the forum related to this. Hasn't anyone run into this problem? What's a common workaround for this?

Thanks!


dbutil restore from backup problem (order of tables) - El Forum - 05-31-2010

[eluser]Jaffer Haider[/eluser]
Guys? No one?


dbutil restore from backup problem (order of tables) - El Forum - 05-31-2010

[eluser]WanWizard[/eluser]
CI doesn't have any visibility on possible constraints, so you have to give the order of your tables manually by passing it as a parameter:
Code:
$tables = array('table1', 'table2', 'table3');
$this->dbutil->backup( array('tables' => $tables) );
If you don't do this, you'll get the tables in the order provided by the 'SHOW TABLES' SQL command, which is probably platform specific.


dbutil restore from backup problem (order of tables) - El Forum - 05-31-2010

[eluser]Jaffer Haider[/eluser]
Hmm, I was hoping to avoid giving the order of tables, since you need to maintain this list as the DB changes.

Thanks!


dbutil restore from backup problem (order of tables) - El Forum - 05-31-2010

[eluser]vitoco[/eluser]
i think that you can disable foreign keys checking at the beginning of the script, and at the end...enable it again. Cause check the order to make the dump it's very hard.


dbutil restore from backup problem (order of tables) - El Forum - 06-01-2010

[eluser]WanWizard[/eluser]
If you have no portability requirement, that is possible. For MySQL's innodb:
Code:
SET FOREIGN_KEY_CHECKS=0;
// do your thing here
SET FOREIGN_KEY_CHECKS=1;