mbzdb icon indicating copy to clipboard operation
mbzdb copied to clipboard

Published 20 hours ago • verified publisher icon elliotchance

404

Open wucko opened this issue 10 years ago • 20 comments

runing init.pl, then option 1:

Downloading schema... Error downloading http://git.musicbrainz.org/gitweb/?p=musicbrainz-server.git;a=blob_plain;f=admin/sql/CreateTables.sql;hb=master: 404 Not Found

404

why?

wucko avatar Feb 26 '14 10:02 wucko

The schema resources doesn't longer exist? Where have they moved? There is the schema available for MySQL?

Found that, but is for PostgreSQL: https://github.com/metabrainz/musicbrainz-server/tree/master/admin

rubertu avatar Jun 13 '14 16:06 rubertu

The schema URLs have changed when musicbrainz-server moved to github. I think the base URL in settings.pl should be changed to https://raw.githubusercontent.com/metabrainz/musicbrainz-server/master and the sql scripts URLs should be changed to something like $schema_base/admin/sql/CreateTables.sql

hectoras avatar Jun 18 '14 09:06 hectoras

Hi Churruka:

You are right, but Im looking for the MySql schema, and what you can find in the url you are giving me it's the PostgreSQL. If you modify the $schema_base the script it's gonna fail because it's gonna be unable tu create the schema due syntax error. The pl script it's programmed for MySql engine and what you're giving him it's Postge. If you find out something let me know! Thank you! ;)

rubertu avatar Jun 18 '14 09:06 rubertu

The install script should convert the Postgres sql to valid MySQL queries. Btw, usually the translator has to be "fixed" each time the musicbrainz schema changes.

I sent a pull request some months ago (when the previous schema change took place), but it has not been merged into the master branch yet. Can you check if the database install works using it? It's located at https://github.com/ChurruKa/mbzdb

hectoras avatar Jun 18 '14 09:06 hectoras

I've downloaded your code but I'm still having the same error than I had before:

DBD::mysql::db do failed: BLOB/TEXT column 'join_phrase' can't have a default va lue at src/functions.pl line 95, <SQL> line 2351. [ERROR] SQL: 'ALTER TABLE artist_credit_name ( -- replicate (verbose) ADD joi n_phrase TEXT NOT NULL DEFAULT '''

I've also noticed that all the tables are created with names including the comments, look at the example below:

+---------------------------------------------+ | Tables_in_ngsdb | +---------------------------------------------+ | annotation ( -- replicate (verbose) | | application | | area ( -- replicate (verbose) | | area_alias ( -- replicate (verbose) | | area_alias_type ( -- replicate | | area_annotation ( -- replicate (verbose) | | area_gid_redirect ( -- replicate (verbose) | | area_type ( -- replicate | | artist ( -- replicate (verbose) | | artist_alias ( -- replicate (verbose) | | artist_alias_type ( -- replicate | | artist_annotation ( -- replicate (verbose) | | artist_credit ( -- replicate | | artist_credit_name ( -- replicate (verbose) | | artist_deletion | | artist_ipi ( -- replicate (verbose) | | artist_isni ( -- replicate (verbose) | | artist_meta ( -- replicate | | artist_rating_raw | | artist_tag ( -- replicate (verbose) | | artist_tag_raw | | kv | +---------------------------------------------+ 22 rows in set (0.00 sec)

Thank you ChurruKa in advance for your interest!

rubertu avatar Jun 18 '14 10:06 rubertu

I'm working on it right now, I'll send a commit as soon as I check and (hopefully) fix the whole process (create tables + insert data + create indexes)

hectoras avatar Jun 18 '14 10:06 hectoras

Thank you very much! Because I tried myself but Im new in this project and I got lost and gave up... I thought the the project was abandoned because no one gave signs of live in months . If you archive it, and im sure you will, please let me know! :+1:

Thank you again!

rubertu avatar Jun 18 '14 10:06 rubertu

Please check if the last commit from https://github.com/ChurruKa/mbzdb solves the issue.

hectoras avatar Jun 19 '14 08:06 hectoras

Hi Churruca, now the tables names are created correctly, but I get this error with the field artist_credit_name:

DBD::mysql::db do failed: BLOB/TEXT column 'join_phrase' can't have a default va lue at src/functions.pl line 95, <SQL> line 2351. [ERROR] SQL: 'ALTER TABLE artist_credit_name ADD join_phrase TEXT NOT NULL D EFAULT '''

Thank you!

rubertu avatar Jun 19 '14 08:06 rubertu

@ChurruKa Did you have time to figure out what was happening? Because I've been unable to solve it by myself. Sorry for disturbing you

rubertu avatar Jun 26 '14 08:06 rubertu

@rubertu I think this is caused by a bug of MySQL on Windows: http://stackoverflow.com/questions/3466872/why-cant-a-text-column-have-a-default-value-in-mysql

I think you can workaround the error by following these steps:

  1. run these commands from a mysql root connection: set @orig_mode = @@global.sql_mode; set @@global.sql_mode = MYSQL40;

  2. run init.pl, option 1 (table creation)

  3. run this command from the previous mysql root connection: set @@global.sql_mode = @orig_mode

  4. run all the remaining steps from init.pl

Please post if that workaround solves the issue (or if not). Ideally, we should then modify mbzdb to "detect" this kind of error and change the sql_mode automatically (or rewrite the query to remove the default value).

hectoras avatar Jul 23 '14 18:07 hectoras

Hi @ChurruKa your workaround worked perfectly!

I've executed the full script (init.pl option 1) and I've detected a pair of bugs, I've tried to fix them myself by I've never written code in Perl and I don't know how to debug it, anyway I found why it's failing and I can describe them in detail.

  1. After the script download the dumps, I found these errors: Logging into MusicBrainz FTP (ftp.musicbrainz.org)... The latest is mbdump is '20140723-002832' Thu Jul 24 11:36:02 2014: Downloading mbdump-cover-art-archive.tar.bz2... Done Thu Jul 24 11:36:05 2014: Downloading mbdump-stats.tar.bz2... Done Thu Jul 24 11:36:11 2014: Downloading mbdump-derived.tar.bz2... Done Thu Jul 24 11:36:17 2014: Downloading mbdump.tar.bz2... Done Thu Jul 24 11:43:15 2014: Uncompressing mbdump-cover-art-archive.tar.bz2... The system cannot find the file specified.

We I went to the folder where the script resides I found that the script is uncompressing the files in two difference folders, and don't deleting the source files: /mbdump /replication/mbdump This causes that the importer function only fill 33 tables, those that are located in /mbdump. To verify that this was correct I moved everything from /replication/mbdump to /mbdump and now I have the database completely filled.

  1. There is a problem with the mysql translator script that fails with this line: CREATE INDEX edit_idx_editor_id_desc ON edit (editor, id DESC); It translates to: CREATE INDEX edit_idx_editor_id_desc23 ON edit (editor,'id DESC'); giving the error: ERROR 1072 (42000): Key column 'id DESC' doesn't exist in table

Thank you very much for your help! and I hope my comments help you to fix it. If I can do anything to help you just let me know!

rubertu avatar Jul 24 '14 13:07 rubertu

any idea what this could be?

ADD COLUMN `end_date_year`
ADD COLUMN `end_date_month`
ADD COLUMN `end_date_day`
ADD COLUMN `time`

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITHOUT TIME ZONE' at line 1 at src/functions.pl line 95, <SQL> line 2659. [ERROR] SQL: 'ALTER TABLE event ADD time TIME WITHOUT TIME ZONE'

error that didn't seem to be around here, from new tables?

andrewkaz avatar Dec 06 '14 02:12 andrewkaz

NVM!

andrewkaz avatar Dec 06 '14 04:12 andrewkaz

@andrewkaz if you are still having this issue maybe my mbzdb fork works for you: https://github.com/ChurruKa/mbzdb

hectoras avatar Dec 16 '14 12:12 hectoras

You're a lifesaver, @ChurruKa. Thank you!

mwpastore avatar Feb 04 '15 03:02 mwpastore

@rubertu I had that issue with the 'id DESC' as well. Simply removing the ' DESC' from replication/CreateIndexes.sql (line 70) did the trick. Hope that helps.

mwpastore avatar Feb 05 '15 01:02 mwpastore

Hello @hectoras,

Currently, your scripts work perfectly! I have just had to apply the MySql workaround you describe in a previous post:

set @orig_mode = @@global.sql_mode; set @@global.sql_mode = MYSQL40; set @@global.sql_mode = @orig_mode

I have also checked the following message:

"Thu Jul 24 11:43:15 2014: Uncompressing mbdump-cover-art-archive.tar.bz2... The system cannot find the file specified."

And once imported by first time, I copied all the files in replication to mbdump. Then, I re-ran the init.pl script and selected option 4.

I don't know why MusicBrainz doesn't link to your fork, which is the correct! Furthermore, I hope the "update" script works correctly...have anybody tried it?

So, thanks you once again! Now it's time to search through the tables...

lopezavila85 avatar Jul 26 '15 17:07 lopezavila85

Ouch! I didn't get a complete import:

Tue Jul 28 01:40:38 2015: Loading data into 'recording' (160 of 217)... DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at src/functions.pl line 95, <STDIN> line 2. [ERROR] SQL: 'LOAD DATA LOCAL INFILE 'mbdump/recording' INTO TABLE recording FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '\n' STARTING BY '''

Afterwards, I used init.pl to load the rest of the tables except from 'recording', whose import last several hours...it still is the unique table I have not inserted completely on my DB.

lopezavila85 avatar Jul 28 '15 04:07 lopezavila85

...and last but not least: Sun Aug 2 09:25:21 2015: Loading data into 'recording' (1 of 1)... Done (66h 4m 32s)

Complete (66h 4m 32s)

lopezavila85 avatar Aug 05 '15 04:08 lopezavila85