Lyrion 9.0.0: MariaDB database schema changes failed
JKDingwall opened this issue · 5 comments
JKDingwall commented
I am using MariaDB on another host for the database, e.g.
dbsource: dbi:mysql:hostname=mariadb.example.com;port=3306;database=slimserver
The first error was:
Dec 03 11:06:38 lms0 systemd[1]: Started Lyrion Music Server.
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: [24-12-03 11:06:40.0756] main::init (387) Starting Lyrion Music Server (v9.0.0, 1732822968, Fri Nov 29 12:08:17 UTC 2024) perl 5.034000 - x86_64-linux-gnu-thread-multi
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: [24-12-03 11:06:40.4713] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::mysql::db do failed: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [for Statement "ALTER TABLE tracks ADD work integer"]
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: [24-12-03 11:06:40.4721] Slim::Schema::Storage::throw_exception (122) Backtrace:
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 0: Slim::Utils::Log::logBacktrace (/usr/share/perl5/Slim/Schema/Storage.pm line 122)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 1: Slim::Schema::Storage::throw_exception (/usr/share/squeezeboxserver/CPAN/DBIx/Class/Storage/DBI.pm line 1007)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 2: DBIx::Class::Storage::DBI::__ANON__ (/usr/share/squeezeboxserver/lib/DBIx/Migration.pm line 113)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 3: DBIx::Migration::migrate (/usr/share/perl5/Slim/Schema.pm line 463)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 4: Slim::Schema::migrateDB (/usr/share/perl5/Slim/Schema.pm line 167)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 5: Slim::Schema::init (/usr/sbin/squeezeboxserver line 547)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 6: main::init (/usr/sbin/squeezeboxserver line 670)
Dec 03 11:06:40 lms0 squeezeboxserver[100742]: frame 7: main::main (/usr/sbin/squeezeboxserver line 1215)
I resolved by following instructions at https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/ and changing the database tables like:
MariaDB [slimserver]> ALTER TABLE videos ROW_FORMAT=DYNAMIC;
Next I got:
Dec 03 11:15:23 lms0 systemd[1]: Started Lyrion Music Server.
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: [24-12-03 11:15:25.0969] main::init (387) Starting Lyrion Music Server (v9.0.0, 1732822968, Fri Nov 29 12:08:17 UTC 2024) perl 5.034000 - x86_64-linux-gnu-thread-multi
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: [24-12-03 11:15:26.2573] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near '
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: composer integer,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: title blob,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: titlesort text,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: titlesearch text,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: FO...' at line 2 [for Statement "
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: CREATE TABLE works (
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: id integer PRIMARY KEY AUTOINCREMENT,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: composer integer,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: title blob,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: titlesort text,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: titlesearch text,
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: FOREIGN KEY (`composer`) REFERENCES `contributors` (`id`) ON DELETE CASCADE
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: )
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: "]
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: [24-12-03 11:15:26.2580] Slim::Schema::Storage::throw_exception (122) Backtrace:
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 0: Slim::Utils::Log::logBacktrace (/usr/share/perl5/Slim/Schema/Storage.pm line 122)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 1: Slim::Schema::Storage::throw_exception (/usr/share/squeezeboxserver/CPAN/DBIx/Class/Storage/DBI.pm line 1007)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 2: DBIx::Class::Storage::DBI::__ANON__ (/usr/share/squeezeboxserver/lib/DBIx/Migration.pm line 113)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 3: DBIx::Migration::migrate (/usr/share/perl5/Slim/Schema.pm line 463)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 4: Slim::Schema::migrateDB (/usr/share/perl5/Slim/Schema.pm line 167)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 5: Slim::Schema::init (/usr/sbin/squeezeboxserver line 547)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 6: main::init (/usr/sbin/squeezeboxserver line 670)
Dec 03 11:15:26 lms0 squeezeboxserver[100830]: frame 7: main::main (/usr/sbin/squeezeboxserver line 1215)
Manually creating the table with this statement worked (aligning type for compser and adding _ to AUTOINCREMENT:
CREATE TABLE works (
id integer PRIMARY KEY AUTO_INCREMENT,
composer int(10) unsigned,
title blob,
titlesort text,
titlesearch text,
FOREIGN KEY (`composer`) REFERENCES `contributors` (`id`) ON DELETE CASCADE
);
LMS seems very keen to keep rewriting the dbsource option in server.prefs after an error as:
hostname=127.0.0.1;port=9092;database=slimserver
JKDingwall commented
Now its a bit stuck due to the partial schema upgrade:
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: [24-12-03 11:47:19.9908] main::init (387) Starting Lyrion Music Server (v9.0.0, 1732822968, Fri Nov 29 12:08:17 UTC 2024) perl 5.034000 - x86_64-linux-gnu-thread-multi
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: [24-12-03 11:47:20.2564] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::mysql::db do failed: Duplicate column name 'work' [for Statement "ALTER TABLE tracks ADD work integer"]
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: [24-12-03 11:47:20.2571] Slim::Schema::Storage::throw_exception (122) Backtrace:
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 0: Slim::Utils::Log::logBacktrace (/usr/share/perl5/Slim/Schema/Storage.pm line 122)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 1: Slim::Schema::Storage::throw_exception (/usr/share/squeezeboxserver/CPAN/DBIx/Class/Storage/DBI.pm line 1007)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 2: DBIx::Class::Storage::DBI::__ANON__ (/usr/share/squeezeboxserver/lib/DBIx/Migration.pm line 113)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 3: DBIx::Migration::migrate (/usr/share/perl5/Slim/Schema.pm line 463)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 4: Slim::Schema::migrateDB (/usr/share/perl5/Slim/Schema.pm line 167)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 5: Slim::Schema::init (/usr/sbin/squeezeboxserver line 547)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 6: main::init (/usr/sbin/squeezeboxserver line 670)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: frame 7: main::main (/usr/sbin/squeezeboxserver line 1215)
Dec 03 11:47:20 lms0 squeezeboxserver[101106]: [24-12-03 11:47:20.2576] Carp::Clan::__ANON__ (214) Warning: Carp::Clan::__ANON__(): DBI Exception: DBD::mysql::db do failed: Duplicate column name 'work' [for Statement "ALTER TABLE tracks ADD work integer"] at /usr/share/perl5/Slim/Schema/Storage.pm line 126
JKDingwall commented
As the create works table was the last step in /usr/share/squeezeboxserver/SQL/mysql/schema_23_up.sql I did:
update dbix_migration set value='23' where name='version';
which seems to have got things running...
michaelherger commented
Thanks for the feedback! Would you mind providing a pull request with the changes you had to apply?
BTW: would dropping the videos and images table have simplified things or prevented the issue in the first place? We no longer use those tables.
JKDingwall commented
For the 'ROW_FORMAT=DYNAMIC;' change I just applied that to all the tables in the database. It could have been targeted at the tables which errored. I'm not sure what version of MySQL or MariaDB the database was created with and whether this is required probably depends on that. I'll make a PR for the 'CREATE TABLE' change shortly...
michaelherger commented
Fixed by #1237 - thanks!