search

chamilo / chamilo-lms

Chamilo is a learning management system focused on ease of use and accessibility
https://chamilo.org
GNU General Public License v3.0
798 stars 480 forks source link

Courses stop displaying after upgrade from 1.10.8 to 1.11.2 #1958

Open darkshram opened 7 years ago

darkshram commented 7 years ago

I tested an upgrade from 1.10.8 to 1.11.2. CentOS 6.9, Apache 2.2, PHP 5.6.30, MariaDB 5.5.56.

Installation was previously upgraded from 1.9.10.4 last year.

Cleaned app/cache/twig as suggested by documentation.

Deleted main/exercice/, plugin/ticket/ and main/newscorm/

Upgrade failed because the installation script was unable to delete main/upload/users.

Restored from back up and tried the upgrade again. This time I deleted main/exercice/, plugin/ticket/, main/newscorm/ and main/upload/users.

Upgrade process finished well. Everything seemed to work ok, until trying to access some courses, nothing was displayed. Apache log showed the following:

[Thu May 18 22:21:57 2017] [error] [client xx.xx.xx.xx] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.email_canonical' in 'field list'' in /some/directory/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91\nStack trace:\n#0 /some/directory/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(91): PDOStatement->execute(NULL)\n#1 /some/directory/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(828): Doctrine\\DBAL\\Driver\\PDOStatement->execute()\n#2 /some/directory/public_html/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(712): Doctrine\\DBAL\\Connection->executeQuery('SELECT t0.id AS...', Array, Array)\n#3 /some/directory/public_html/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(730): Doctrine\\ORM\\Persisters\\Entity\\BasicEntityPersister->load(Array, NULL)\n#4 /some/directory/public_html/vendor/doctrine in /some/directory/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 71, referer: http://my.lms.domain.com/courses/CGIS01/

So, restored back up. Seems I'll be using 1.10.8 for a while.

ywarnier commented 7 years ago

Hi @darkshram Any chance you could try this with the 1.11.4 alpha we just released a few minutes ago? https://github.com/chamilo/chamilo-lms/releases/tag/v1.11.4-alpha.1

If the issue is the same, we can definitely work on it before the stable release. Otherwise it's a bit difficult for us to know how to offer a good fix.

darkshram commented 7 years ago

Sure! I'll try it this Friday in a test server.

jmontoyaa commented 7 years ago

Can you show the result of this query after and before the migration?

select * from version;

darkshram commented 7 years ago

I have backups from the test done yesterday.

Before upgrade:

MariaDB [lmsal]> select * from version; +----------------+ | version | +----------------+ | 110 | | 20150423093100 | | 20150504182600 | | 20150505132304 | | 20150505142900 | | 20150507152600 | | 20150511133949 | | 20150519153200 | | 20150521113600 | | 20150522112023 | | 20150522222222 | | 20150527101600 | | 20150527114220 | | 20150527120703 | | 20150528103216 | | 20150529164400 | | 20150603142550 | | 20150603151200 | | 20150603181728 | | 20150604145047 | | 20150608104600 | | 20150609113500 | | 20150610143426 | | 20150615171900 | | 20150616093200 | | 20150624164100 | | 20150625155000 | | 20150706135000 | | 20150709083710 | | 20150713132630 | | 20150803163400 | | 20150803171220 | | 20150805161000 | | 20150810132615 | | 20150812230500 | | 20150813143000 | | 20150813200000 | | 20150819095300 | | 20150821150000 | | 20150825141100 | | 20151101082200 | | 20151101082300 | | 20151119082400 | | 20151214164000 | | 20151214170800 | | 20151221150100 | | 20160707131900 | +----------------+ 47 rows in set (0.00 sec)

After upgrade:

MariaDB [lmsal]> select * from version; +----------------+ | version | +----------------+ | 110 | | 20150423093100 | | 20150504182600 | | 20150505132304 | | 20150505142900 | | 20150507152600 | | 20150511133949 | | 20150519153200 | | 20150521113600 | | 20150522112023 | | 20150522222222 | | 20150527101600 | | 20150527114220 | | 20150527120703 | | 20150528103216 | | 20150529164400 | | 20150603142550 | | 20150603151200 | | 20150603181728 | | 20150604145047 | | 20150608104600 | | 20150609113500 | | 20150610143426 | | 20150615171900 | | 20150616093200 | | 20150624164100 | | 20150625155000 | | 20150706135000 | | 20150709083710 | | 20150713132630 | | 20150803163400 | | 20150803171220 | | 20150805161000 | | 20150810132615 | | 20150812230500 | | 20150813143000 | | 20150813200000 | | 20150819095300 | | 20150821150000 | | 20150825141100 | | 20151101082200 | | 20151101082300 | | 20151119082400 | | 20151214164000 | | 20151214170800 | | 20151221150100 | | 20160707131900 | | 20160808110200 | +----------------+ 48 rows in set (0.00 sec)

The difference was:

--- query-lmsal-previus.txt 2017-05-19 08:09:40.079015536 -0500 +++ query-lmsal-after.txt 2017-05-19 08:09:52.641698719 -0500 @@ -49,5 +49,6 @@ | 20151214170800 | | 20151221150100 | | 20160707131900 | +| 20160808110200 | +----------------+ -47 rows in set (0.00 sec) +48 rows in set (0.00 sec)

Will post later the results of trying using 1.11.4-alpha1

jmontoyaa commented 7 years ago

It seems that the upgrade process never started. The 1.11.x migration start with version "111" in the table and finish with "20161028123400"

darkshram commented 7 years ago

Last table after upgrade is 20160808110200. Means upgrade script trully failed, despite being completed (supposedly) without issues. The second output was from a backup I made from the database after the failed upgrade. I'm wondering if the upgrade I did last year from 1.9.10.4 to 1.10.8 was really successful. What should I validate the current database?

Will try later with 1.11.4-alpha1. I have the backup of the database after the failed upgrade. Should I look for something within to help diagnose this?

ywarnier commented 7 years ago

You can try (on the command line from the root directory of Chamilo):

php bin/doctrine.php orm:schema-tool:update --dump-sql

That should tell you how badly your database is unsynchronized from the expected structure. But that will not fix inconsistencies in the data that might have been generated from previous (incomplete) upgrades...

darkshram commented 7 years ago

Done. What should I look for in this dump? chamilo-dump.txt

ywarnier commented 7 years ago

If your database is OK, then nothing should appear. Obviously, yours is far from OK :-) You can execute all these queries. Some might fail because of an index that should be deleted first. To make sure this is manageable, I used to execute them 10 at a time, and then if something fails you can fix it manually and continue. Once this is all done, your database structure should be clean, but your data might still be failing (hopefully not). If data is failing, then some things might have to be fixed manually reading all migration scripts in the app/Migrations/Schema/ folder... a good amount of work to be planned. Note that this probably happened because of one SQL query failing at some point in one of the previous upgrades. 1.10 has had a series of issues for upgrading from previous versions, which we have solved one by one, but that might have affected early adopters. 1.11 is much more cared for in this sense, although it has also had a (much smaller) number of issues.

darkshram commented 7 years ago

Thank you. Done it on a test server with a copy of the production database. Everything seems to have went well. There are 2 queries that persist after executing everything shown by 'php bin/doctrine.php orm:schema-tool:update --dump-sql':

ALTER TABLE access_url_rel_user ADD CONSTRAINT FK_85574263A76ED395 FOREIGN KEY (user_id) REFERENCES user (id); ALTER TABLE access_url_rel_user ADD CONSTRAINT FK_8557426373444FD5 FOREIGN KEY (access_url_id) REFERENCES access_url (id);

Any suggestions about how to fix it?

Tested login, courses, create new course, grades, logs, etc. Everything seems to be in order. No data seems to be lost. There are no errors logged at error_log.

What should I (double) check before doing it in the production server?

ywarnier commented 6 years ago

Any suggestions about how to fix it?

There is probably already a foreign key doing that in both cases. You could simply drop the foreign key, then execute these queries.

What should I (double) check before doing it in the production server?

Not much. Take a backup first, then proceed.

stale[bot] commented 4 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.