EOSERV Forum > EOSERV > install db sql syntax error
Page: << 1 >>
install db sql syntax error
Author Message
Post #203139 install db sql syntax error

Hi all, at first I received the error telling me that each primary key must be not null or to use unique, instead of primary null, so changed that to unique, but now I'm getting another query error:

ERROR 1064 (42000) at line 104: 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 'IF NOT EXISTS `character_account_index` ON `characters` (`account`)' at line 1

I've tried a couple of things, but don't seem to work, any help is appreciated, as server crashes upon creation of character.

50 weeks, 2 days ago
Post #203140 Re: install db sql syntax error

What is the query in question?

Edit: nvm I just realized this is in the EOServ sub-forum. Try using an install.sql from an earlier revision, that fixed it for me.

---
stay tuned.
50 weeks, 2 days ago
Post #203141 Re: install db sql syntax error

It sounds like the install.sql wasn't completed. You could try a couple of things like make a clean database and run it again, or try running it again in the current database. If that doesn't work for you a last resort would be to open install.sql in notepad and use that as a reference to manually add the tables. You might even change null to not null in the install.sql where you are getting errors. I have seen inconsistencies before when running install.sql on various platforms. Not really sure why it is throwing syntax errors when that is pretty much a standard sql file. 

50 weeks, 2 days ago
Post #203143 Re: install db sql syntax error

mysql doesn't let you do if exists comparisons on table columns.. it's pretty lame. someone asked for it to get implemented 8 years ago and nothing happened.

maybe that sql script was written for mariadb?

---
I not hacker

“Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its
whole life believing that it is stupid.” - Albert Einstein : Really Great Quote Ramy!
50 weeks, 2 days ago
Post #203146 Re: install db sql syntax error
Apollo posted: (2nd May 2017, 03:39 pm)

It sounds like the install.sql wasn't completed. You could try a couple of things like make a clean database and run it again, or try running it again in the current database. If that doesn't work for you a last resort would be to open install.sql in notepad and use that as a reference to manually add the tables. You might even change null to not null in the install.sql where you are getting errors. I have seen inconsistencies before when running install.sql on various platforms. Not really sure why it is throwing syntax errors when that is pretty much a standard sql file. 


I did go to the lines where the errors occured and changed it to not null, instead of null, that fixed it, but the last errors are still there.
50 weeks, 1 day ago
Post #203147 Re: install db sql syntax error

What do you mean by last errors?

50 weeks, 1 day ago
Post #203148 Re: install db sql syntax error

The problem is exactly as I said.

Line 104: CREATE INDEX IF NOT EXISTS `character_account_index` ON `characters` (`account`);

MySQL syntax for CREATE INDEX: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

"CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 14.1.8, “ALTER TABLE Syntax”. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead."

Looking at the ALTER TABLE syntax you can't use IF NOT EXISTS.
Here is a link to the feature request for IF NOT EXISTS in ALTER TABLE created 13 years ago... https://bugs.mysql.com/bug.php?id=3706

The solution? Start fresh and get rid of the IF NOT EXISTS or just use mariadb instead of mysql.

Maybe you should use MariaDB instead. https://eoserv.net/forum/topic/23138

---
I not hacker

“Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its
whole life believing that it is stupid.” - Albert Einstein : Really Great Quote Ramy!
50 weeks, 22 hours ago
Post #203149 Re: install db sql syntax error

It's okay, I fixed it ^^

IF TABLE NOT EXIST is just not supported very well.

50 weeks, 19 hours ago
Post #203154 Re: install db sql syntax error

Thanks for reporting. It's not intentional that it doesn't work with MySQL.

49 weeks, 5 days ago
Post #203157 Re: install db sql syntax error

No problem, hope you fix it for less future trouble ^^

49 weeks, 19 hours ago
Page: << 1 >>

EOSERV Forum > EOSERV > install db sql syntax error