0013534: Crash when trying to upgrade database from 2017.02.05 to 2017.08.x #6695

Closed
Gloirin opened this Issue Jun 9, 2018 · 6 comments

Comments

Projects
None yet
1 participant
@Gloirin

Gloirin commented Jun 9, 2018

Reported by BoehserWolf on 9 Oct 2017 14:41

Version: 2017.08.4 Community Edition

Upgrade 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)
#2 /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)
#2 /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)
#2 /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)
#2 /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)
#2 /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)
#2 /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)
#2 /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)
#2 /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)
#2 /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)
#2 /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 information: Possible 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.

@Gloirin Gloirin self-assigned this Jun 9, 2018

@Gloirin Gloirin closed this Jun 9, 2018

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 10 Oct 2017 12:42

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 10 Oct 2017 12:42

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by BoehserWolf on 11 Oct 2017 08:58

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)
    #2 /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)
#2 /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.

Gloirin commented Jun 11, 2018

Comment posted by BoehserWolf on 11 Oct 2017 08:58

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)
    #2 /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)
#2 /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.

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 13 Oct 2017 05:07

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.

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 13 Oct 2017 05:07

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.

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 13 Oct 2017 05:10

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)

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 13 Oct 2017 05:10

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)

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by BoehserWolf on 14 Oct 2017 09:41

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?

Gloirin commented Jun 11, 2018

Comment posted by BoehserWolf on 14 Oct 2017 09:41

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?

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 16 Oct 2017 04:23

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 16 Oct 2017 04:23

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

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