View Issue Details

IDProjectCategoryView StatusLast Update
0013534Tine 2.0Setuppublic2017-10-16 06:24
ReporterBoehserWolfAssigned Topmehrer 
PrioritynormalSeveritycrashReproducibilityalways
Status resolvedResolutionno change required 
Platform64bitOSDebianOS Version8.9
Product Version2017.08.4 Community Edition 
Target VersionFixed in Version 
Summary0013534: Crash when trying to upgrade database from 2017.02.05 to 2017.08.x
DescriptionUpgrade from Version 2017.02.05 (or 2017.02.03) to 2017.08.x fails.

Upgrade path's tried already by the use of release versions from the release github site:

2013.10.5 -> 2014.09.12 -> 2015.07.4 -> 2016.03.4 -> 2016.09.6 -> 2017.02.4 -> 2017.02.5 -> crash on update 2017.08.x
more specific path which failed to upgrade:
2017.02.5 -> 2017.08.6
2017.02.5 -> 2017.08.5
2017.02.5 -> 2017.08.3
2017.02.5 -> 2017.08.1

2013.10.5 -> 2014.09.12 -> 2015.07.4 -> 2016.03.4 -> 2016.09.6 -> 2017.02.3 -> crash on update 2017.08.3

errors on update 2017.02.5 -> 2017.08.6
php setup.php --update
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_2593`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_2593`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235



errors on update 2017.02.5 -> 2017.08.6
php setup.php --update
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_262d`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_262d`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235



errors on update 2017.02.5 -> 2017.08.5
php setup.php --update
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_2652`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_2652`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235



errors on update 2017.02.5 -> 2017.08.3
php setup.php --update
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_27db`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_27db`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235



errors on update 2017.02.5 -> 2017.08.1
php setup.php --update
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_2857`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_2857`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(2 in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235
Additional InformationPossible similar issue: https://forge.tine20.org/view.php?id=13406
Sulotion of bug 13406 did not solve my problem.

Please see also form thread https://www.tine20.org/forum/viewtopic.php?f=12&t=18606 for further information.
TagsNo tags attached.
mwticket

Activities

pmehrer

pmehrer

2017-10-10 14:42

developer   ~0020918

thank you for reporting this issue. There are other (CE) reports of the issue too. One thing on the stack trace: it clearly says that the foreign key constraint fails. So there is no doubt about that. Before the update starts, that constraint is in place. During the update the constraint gets dropped at some point, some structure updates happen, then the constraint should be recreated. And that fails. So most probably an other process is manipulating data while your update runs (and the constraint got already dropped). Make sure to turn on maintenance mode eventually disable the cronjobs.

I was not able to reproduce it. My steps trying to reproduce the issue:

git checkout 2017.02.5

vendor/bin/phing -D configdir=/etc/tine20 tine-install

php -d include_path=/etc/tine20/ /usr/local/share/tine20.git/tine20/tine20.php --method=Tinebase.createAllDemoData

git checkout 2017.08.6

php -d include_path=/etc/tine20/ /usr/local/share/tine20.git/tine20/setup.php --update
Updated 32 application(s).
Updated 3 application(s).

looks all good to me
BoehserWolf

BoehserWolf

2017-10-11 10:58

reporter   ~0020922

Tried the same upgrade path as you: 2017.02.5 -> 2017.08.6

I did as you suggested:
- enabled maintenance mode and made sure it is working
- disabled cron jobs
- cleaned directories cache, session, tmp
The update failed.

Additionally to above steps:
- disabled the vhost in apache, restarted apache and made sure it is not available any more
The update failed again.

- php setup.php --update
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_129ad`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php( in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`c1tine20`.`#sql-24d_129ad`, CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE)' in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
0000002 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php( in /var/www/clients/client1/web6/web/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php on line 235

Hope you have any other idea. If you need I can provide you my current DB dump and the tine install folder files.
pmehrer

pmehrer

2017-10-13 07:07

developer   ~0020938

can you please check if the constraint is there before you start the update?
CONSTRAINT `tine20_container_acl::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`)
it has to be present on the table tine20_container_acl

If it is present, a DB dump would be helpful. But if it is not present, you need jus to clean your database.
pmehrer

pmehrer

2017-10-13 07:10

developer   ~0020940

can you run this query:

SELECT acl.id FROM `tine20_container_acl` as acl LEFT JOIN tine20_container AS c ON acl.container_id = c.id WHERE c.id IS NULL

if you get a result, delete those entries from `tine20_container_acl` and the update will work (and you would have had a messed up database)
BoehserWolf

BoehserWolf

2017-10-14 11:41

reporter   ~0020970

Bingo!

My mysql query before (modified output for anonymization):
mysql> SELECT acl.id FROM `tine20_container_acl` as acl LEFT JOIN tine20_container AS c ON acl.container_id = c.id WHERE c.id IS NULL;
+------------------------------------------+
| id |
+------------------------------------------+
| abab4848a90180b4706dcfae2fde7a66309086f5 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| ababb29908aa617304732459f6c6d9b13ded6689 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
| abab159d48406d8f4b2076234cb76d8f09e84697 |
+------------------------------------------+
15 rows in set (0.00 sec)

And afterwards:
mysql> SELECT acl.id FROM `tine20_container_acl` as acl LEFT JOIN tine20_container AS c ON acl.container_id = c.id WHERE c.id IS NULL;
Empty set (0.00 sec)

Did the same than above:
- enabled maintenance mode
- disabled cron jobs
- disabled the vhost in apache
- successfully ran the update 2017.02.5 -> 2017.08.6

Thanks a million!

However I do not understand what has happened to bring in this inconsitency into the database - can you explain this please?

Maybe it would also be a good idea the include this query and some error output into the update routine?
pmehrer

pmehrer

2017-10-16 06:23

developer   ~0020972

regarding the reason for your issue, I can only guess:
* you have an old installation and the update that introduced the constraint failed, so you never had that constraint (rather unlikely, but why not)
* someone dropped the constraint manually
* the corrupted data was created during your first try to upgrade (in a parallel process, cronjob, web, a sync device, etc.) I would guess that was the cause of your issue. You didn't answer my question
"can you please check if the constraint is there before you start the update?"
Also please consider copying your database first and doing your update as a test run on the copied version. Or just create a dump before updating. If it didn't work, restore from dump.

glad to hear your issue is solved
best regards
Paul

Issue History

Date Modified Username Field Change
2017-10-09 16:41 BoehserWolf New Issue
2017-10-10 14:42 pmehrer Note Added: 0020918
2017-10-11 10:58 BoehserWolf Note Added: 0020922
2017-10-13 07:07 pmehrer Note Added: 0020938
2017-10-13 07:10 pmehrer Note Added: 0020940
2017-10-14 11:41 BoehserWolf Note Added: 0020970
2017-10-16 06:23 pmehrer Note Added: 0020972
2017-10-16 06:24 pmehrer Assigned To => pmehrer
2017-10-16 06:24 pmehrer Status new => resolved
2017-10-16 06:24 pmehrer Resolution open => no change required