Forum :: EOSERV :: 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.

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.

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. 

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!

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. 

What do you mean by last errors?

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!

It's okay, I fixed it ^^

IF TABLE NOT EXIST is just not supported very well.

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

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