Syntay-error on databese create
Closed this issue · 23 comments
I have a syntyx-error on Database create after the command 'node bin/db_patcher.js'. Serverstart is not possible. What can i do?
Sorry for my bad english :-)
{"Timestamp":1535453805299000000,"Logger":"fxa-auth-db-server","Type":"bin.db_patcher.patch-error","Severity":2,"Pid":1757,"EnvVersion":"2.0","Fields":{"err":"Error: ER_PARSE_ERROR: 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 'ALGORITHM = INPLACE LOCK = NONE;\n\n-- But it's *not* safe to do the following in ' at line 8"}}
Holger ...
@misterhsp, if you want to run on MariaDB you'll need to manually edit the code yourself. We only run on MySQL here so don't notice when something creeps into a migration that doesn't work in both databases.
Specifically, I think there's a few places where you need to add a comma after ALGORITHM = INPLACE and before LOCK = NONE. If you grep inside the lib/db/schema/ directory, you should be able to find all the places you need to change.
So, if I grep for ALGORITHM = INPLACE, I get these results:
~/c/fxa-auth-db-mysql (master) $ git grep 'ALGORITHM = INPLACE'
lib/db/schema/patch-022-023.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-022-023.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-028-029.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-029-028.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-036-035.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-036-035.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-036-035.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-036-035.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-038-039.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-038-039.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-039-038.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-039-038.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-042-043.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-054-055.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-055-054.sql:--ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-058-059.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-059-058.sql:--ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-059-060.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-060-059.sql:--ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-060-061.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-061-060.sql:--ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-061-062.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-062-061.sql:-- ALTER TABLE devices DROP COLUMN nameUtf8, ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-062-063.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-063-062.sql:--ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-067-068.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-067-068.sql:ALGORITHM = INPLACE LOCK=NONE;
lib/db/schema/patch-068-067.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-071-072.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-072-071.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-072-073.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-072-073.sql:ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-073-072.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
lib/db/schema/patch-073-072.sql:-- ALGORITHM = INPLACE, LOCK = NONE;
You can see that most of those have a comma before LOCK = NONE, but some don't. When you run the bin/db_patcher script, each of the forward migrations is applied to your database in turn. As soon as MariaDB hits one that doesn't have a comma there, it falls over.
So, filtering down to the results without a comma:
~/c/fxa-auth-db-mysql (master) $ git grep 'ALGORITHM = INPLACE[^,]'
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-035-036.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-038-039.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-042-043.sql:ALGORITHM = INPLACE LOCK = NONE;
lib/db/schema/patch-067-068.sql:ALGORITHM = INPLACE LOCK=NONE;
It looks like you need to edit those 4 SQL files to insert a comma after ALGORITHM = INPLACE. Then you probably want to do a DROP DATABASE fxa, because it's likely that the failed migration will have left the database in an in-between state. And then you can run node bin/db_patcher again and it should hopefully complete fine. (or if not you should get a different error message, which we can help you to debug as well)
...and if you want to open a PR if/when you have it working, that would be welcome too btw! We have this issue come up for external contributors every now and then, so it seems like something we should fix in-tree even though we don't officially support MariaDB.
Hmm, that looks like it might still be related to the same thing. At least that line is ALGORITHM = INPLACE again, anyway. Can you paste the output from running:
head -32 lib/db/schema/patch-035-036.sql
?
Ah! Ok, for CREATE INDEX it looks like there needs to be a comma before ALGORITHM = INPLACE too. If you notice, for the first one of those CREATE INDEX statements in your pastebin, there is a comma at the end of the second line. Let's try doing the same for the other two to see if it helps!
Hmm, I'm really not sure in that case, sorry. Maybe I was completely wrong about the comma, I thought I remembered that helping somebody else in the past, but I could be mistaken.
It's probably best to revert the changes you made so far as they're likely not helping. When I get a bit more time I can try setting up FxA on a box with MariaDB and see if I can work through the issues, but until then I haven't got more to suggest. Sorry for the time-wasting!
btw: I use now Mysql and fxa-auth-db-mysql works :)
Great!
I can't find any documentation about fxa-oauth-server and fxa-profile-server.service. Konfiguration and no URL/port (proxy-pass) on which the servers listen.
If you're comfortable reading the source, it's hopefully straightforward enough to follow. These are the default settings for fxa-oauth-server:
https://github.com/mozilla/fxa-oauth-server/blob/master/lib/config.js#L279-L305
And fxa-profile-server:
https://github.com/mozilla/fxa-profile-server/blob/master/lib/config.js#L201-L231
There's other config settings you might be interested in there too, connection settings for databases and so on. If you have any specific questions about them, let us know!
Only fxa-auth-server needs to talk to fxa-auth-db-mysql, so you could run it on a closed port on the same box as the auth server (I think that's how we set it up here).
Config for the fxa-auth-db-mysql:
https://github.com/mozilla/fxa-auth-db-mysql/blob/master/config/config.js#L15-L25
And while I'm here, the auth server config in case you hadn't seen that yet:
https://github.com/mozilla/fxa-auth-server/blob/master/config/index.js
@misterhsp, we have this big diagram of how everything fits together, you might find it useful:
https://github.com/mozilla/fxa/blob/master/docs/index.md#code
(it's not completely accurate, but it's close enough)
Good morning,
(sry if my english is bad, i am not a native speaker)
I have sucessfully found the error:
MariaDB dosnt support the options ALGORITHM and LOCK on DROP INDEX (see docs of drop index: https://mariadb.com/kb/en/library/drop-index/).
An open issue already exist for MariaDB (https://jira.mariadb.org/browse/MDEV-12572).
As a workaround you can change
DROP INDEX index_name
ON table_name
ALGORITHM = INPLACE LOCK = NONE;
to
ALTER TABLE table_name
DROP INDEX index_name,
ALGORITHM = INPLACE, LOCK = NONE;
the effected files are:
- patch-035-036.sql (line 98)
- patch-038-039.sql (line 16)
After changed the files the db patcher runs without error.
That's great to know, thanks @itgenie98! I'll patch it soon...
any News?
Sorry @misterhsp, I just haven't been able to prioritise it above other work yet. I know it's only a small fix though, so I'll make some time for it this Friday, I promise!
@misterhsp, @itgenie98, we landed these changes today so MariaDB should be okay now without changes. Let us know if anything else is failing...
Thanks a lot Phil, works :-)
...