0013504: setup.php --restore -- db=1 bails out and leave database unusable #6680

Open
Gloirin opened this Issue Jun 9, 2018 · 7 comments

Comments

Projects
None yet
1 participant
@Gloirin

Gloirin commented Jun 9, 2018

Reported by estradis on 29 Sep 2017 12:57

While trying to restore database from last backup, i found following problem:

When an error occours during table creation after it was deleted in first, setup.php bails out and leaves database unusable.

I my case the statements

DROP TABLE IF EXISTS tine20_addressbook;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/
!40101 SET character_set_client = utf8 /;
CREATE TABLE tine20_addressbook (
[...]
CONSTRAINT tine20_addressbook::container_id--container::id FOREIGN KEY (container_id) REFERENCES tine20_container (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/
!40101 SET character_set_client = @saved_cs_client */;

will cause the error. At this point the database seems not to be able to reference the constraint and therefore the execution fails.

Maybe the better solution will be to create the table without constraint in first and alter it afterwards to set the constraint.

  • or -

  • Delete constraints

  • Rename table

  • create new table

  • Create constraints

Additional information: Flow:

root@tine:~# /usr/bin/php /usr/share/tine20/setup.php --restore -- db=1 backupDir=/tmp/tine-20170929-120600/
ERROR 1215 (HY000) at line 336: Cannot add foreign key constraint

bzcat: I/O or other error, bailing out. Possible reason follows.
bzcat: Broken pipe
Input file = /tmp/tine-20170929-120600//tine20_mysql.sql.bz2, output file = (stdout)

root@tine:~# /usr/bin/php /usr/share/tine20/setup.php --restore -- db=1 backupDir=/tmp/tine-20170929-120600/
PHP Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tine20db.tine20_addressbook' doesn't exist in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->execute(Array)
#2 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `tine20
...', Array)
#4 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Select.php(711): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#5 /usr/share/tine20/Tinebase/User/Sql.php(328): Zend_Db_Select->query()
#6 /usr/sha in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tine20db.tine20_addressbook' doesn't exist in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->execute(Array)
#2 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `tine20
...', Array)
#4 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Select.php(711): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#5 /usr/share/tine20/Tinebase/User/Sql.php(328): Zend_Db_Select->query()
#6 /usr/sha in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pschuele on 3 Nov 2017 08:52

as we just use mysqldump to create the sql backup, there is little room for improvement. maybe there are options for mysqldump that add more stability here?

Gloirin commented Jun 11, 2018

Comment posted by pschuele on 3 Nov 2017 08:52

as we just use mysqldump to create the sql backup, there is little room for improvement. maybe there are options for mysqldump that add more stability here?

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by estradis on 3 Nov 2017 11:38

ah, didn't know that! I also have absolutly no idea about any options using mysqldump. I'm not firm with mysql and to be honest, i don't really like it.

Unfortunatly using a built-in method of mysql which fails afterwards by using another build-in method means that there might something wrong with the data consinstency. Therefore i try to cleanup and audit the contents in first, before proceeding with this issue, but please be patient. It might take a while!

Gloirin commented Jun 11, 2018

Comment posted by estradis on 3 Nov 2017 11:38

ah, didn't know that! I also have absolutly no idea about any options using mysqldump. I'm not firm with mysql and to be honest, i don't really like it.

Unfortunatly using a built-in method of mysql which fails afterwards by using another build-in method means that there might something wrong with the data consinstency. Therefore i try to cleanup and audit the contents in first, before proceeding with this issue, but please be patient. It might take a while!

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pschuele on 16 Nov 2017 10:22

we'll add the -f param to the mysql import command (see https://stackoverflow.com/questions/11263018/mysql-ignore-errors-when-importing). that should skip the errors with fk constraints during the import.

Gloirin commented Jun 11, 2018

Comment posted by pschuele on 16 Nov 2017 10:22

we'll add the -f param to the mysql import command (see https://stackoverflow.com/questions/11263018/mysql-ignore-errors-when-importing). that should skip the errors with fk constraints during the import.

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pschuele on 16 Nov 2017 10:23

http://gerrit.tine20.com/customers/6419

Gloirin commented Jun 11, 2018

Comment posted by pschuele on 16 Nov 2017 10:23

http://gerrit.tine20.com/customers/6419

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pschuele on 16 Nov 2017 13:59

if you see any errors, you might have to run the --restore multiple times. the problems can occur when you restore into an existing database of an older tine version with a different sql schema.

Gloirin commented Jun 11, 2018

Comment posted by pschuele on 16 Nov 2017 13:59

if you see any errors, you might have to run the --restore multiple times. the problems can occur when you restore into an existing database of an older tine version with a different sql schema.

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by estradis on 7 Mar 2018 10:18

Sorry for reopening this issue. It was necessary to comment additional information to complete this task.

Finally I found a working solution. As the SQL Dump results in a huge Sql-Batch, I opended the zip, copied the content of the *.sql file to SQL-Workbench from Oracle (any other should to it too) and modified it like follows:

// At the top of the script:
use tine20db; // (optional)
SET FOREIGN_KEY_CHECKS=0;

// [backup script goes here]

// At the end of the script:
SET FOREIGN_KEY_CHECKS=1;

See also: https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql

I'd like to recommend to change your export routine to include these statements.

Gloirin commented Jun 11, 2018

Comment posted by estradis on 7 Mar 2018 10:18

Sorry for reopening this issue. It was necessary to comment additional information to complete this task.

Finally I found a working solution. As the SQL Dump results in a huge Sql-Batch, I opended the zip, copied the content of the *.sql file to SQL-Workbench from Oracle (any other should to it too) and modified it like follows:

// At the top of the script:
use tine20db; // (optional)
SET FOREIGN_KEY_CHECKS=0;

// [backup script goes here]

// At the end of the script:
SET FOREIGN_KEY_CHECKS=1;

See also: https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql

I'd like to recommend to change your export routine to include these statements.

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pschuele on 22 Mar 2018 16:40

ok, we'll have a look. i thought, mysqldump already adds those statements.

Gloirin commented Jun 11, 2018

Comment posted by pschuele on 22 Mar 2018 16:40

ok, we'll have a look. i thought, mysqldump already adds those statements.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment