search

scrollmapper / bible_databases

Bible versions and cross-reference databases.
1.01k stars 348 forks source link

Issue with book_info.sql importing in MariaDB #79

Open squidsworld opened 10 months ago

squidsworld commented 10 months ago

Chose not to import the main mysql.sql file due to the DROP TABLE user command in it.

So I imported all the files manually. Ran into this error:

ERROR 1170 (42000) at line 1 in file: '/.../bible_databases/sql/book_info.sql': BLOB/TEXT column 'title_short' used in key specification without a key length

Had to modify the very beginning of the .sql file to this:

CREATE TABLE IF NOT EXISTS book_info ( order INT NOT NULL AUTO_INCREMENT, title_short VARCHAR(255) NOT NULL UNIQUE, title_full VARCHAR(255) NOT NULL UNIQUE, abbreviation VARCHAR(255) NOT NULL UNIQUE, category VARCHAR(255) NOT NULL, otnt VARCHAR(255) NOT NULL, chapters INT, PRIMARY KEY (order) );

Switched the type from text to varchar. Presumably the issue can also be fixed by removing the "UNIQUE" flag from each of the TEXT fields.

paulforbes42 commented 10 months ago

For reference, I’m curious if #69 fixes this issue for newer versions of MairaDB. The intention of #69 was to fix the import into MySQL, but may fix issues on MariaDB as well.

squidsworld commented 10 months ago

Thanks for your feedback paulforbes42,

Just a cursory glance at #69, I would agree that probably would fix the issue. In my view, the issue is fixed either by dropping the UNIQUE keyword or by changing the type to VARCHAR.

"Removed mysql system database and tables that were included in this dump file" This change also would have probably fixed it for me, as I wouldn't have used the individual sql files in the first place had the core mysql tables not been included in the main file.

Thanks, Squid