Database migration 188 to 189 throws fatal error

[Monica-Wood]

On system with dev/php8 and php version 8.1.13

After git pull I ran the update-database-schema and got this error

$ support/bin/caUtils update-database-schema

CollectiveAccess 2.0 (189/GIT) Utilities
(c) 2013-2023 Whirl-i-Gig

Are you sure you want to update your CollectiveAccess database from revision 188 to 189?
NOTE: you should backup your database before applying updates!

Type 'y' to proceed or 'N' to cancel, then hit return 
y
PHP Fatal error:  Uncaught mysqli_sql_exception: Data truncated for column 'checked' at row 1 in /srv/rovidence/app/lib/Db/mysqli.php:323
Stack trace:
#0 /srv/providence/app/lib/Db/mysqli.php(323): mysqli_query()
#1 /srv/providence/app/lib/Db/DbStatement.php(150): Db_mysqli->execute()
#2 /srv/providence/app/lib/Db.php(245): DbStatement->executeWithParamsAsArray()
#3 /srv/providence/support/sql/migrations/VersionUpdate189.php(133): Db->query()
#4 /srv/providence/support/sql/migrations/VersionUpdate189.php(106): VersionUpdate189->modifyColumns()
#5 /srv/providence/support/sql/migrations/VersionUpdate189.php(52): VersionUpdate189->runMigrations()
#6 /srv/providence/app/lib/ConfigurationCheck.php(623): VersionUpdate189->applyDatabaseUpdate()
#7 /srvprovidence/app/lib/Utils/CLIUtils/Maintenance.php(424): ConfigurationCheck::performDatabaseSchemaUpdate()
#8 /srv/providence/support/bin/caUtils(172): CLIUtils::update_database_schema()
#9 {main}
  thrown in /srv/providence/app/lib/Db/mysqli.php on line 323

[Monica-Wood]

This Migration worked without fail on system running branch dev/php8 with php version 7.4

[collectiveaccess]

It has to do with the database being updated rather than the version of PHP. The migration doesn't handle errors in some cases. I had added code for one case. I guess it's time to do the rest.

[Monica-Wood]

This is happening on the ca_entity_labels.checked which is a rather new field. Is there anything I can manually do to fix it up in the meantime?

Thanks.

[kehh]

Sorry, that's my migration, and yes, it's a bit unforgiving.

To get it to fix up in the meantime, you'll have to alter the entity labels table so that it looks like this: https://github.com/collectiveaccess/providence/blob/develop/install/inc/schema_mysql.sql#L2902-L2949

[Monica-Wood]

Why was the checked field ever set as a smallint with the default NULL in the first place? It was only added to the model 4 months ago.

I am thinking that if I update all checked that = NULL to 0, then run the migrate it should be ok?

---

I ran this on the DB update ca_entity_labels set checked = 0 where checked is NULL;

then reran the migration and all went through correctly.

[kehh]

Just confirm that the values in there match that pattern first:

SELECT checked, count(*) FROM ca_entity_labels GROUP BY 1;

[collectiveaccess]

I just pushed a fix for this. It constrains values to 0 and 1 before attempting to update them, force anything NULL or not 1 to zero.

[Monica-Wood]

Thank you, have applied to our servers successfully.

[collectiveaccess]

I ran this on a couple of servers that were barfing on the original migration and both worked perfectly this time. So I am optimistic that we've sorted this out :-)