Author Topic: SQL Error - clean install  (Read 113 times)

gcole58799

  • Newbie
  • *
  • Posts: 1
    • View Profile
SQL Error - clean install
« on: August 15, 2019, 12:00:31 PM »
"There was an error running the query [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 ',salary_hour,image_url from ranks where rank_id=1' at line 1]"

I got this error after clean install. Using Bitnami as mysql server, php and apache. home page loads fine the tried to login as vam100 and got this error message..

drgullen

  • Full Member
  • ***
  • Posts: 142
    • View Profile
Re: SQL Error - clean install
« Reply #1 on: September 20, 2019, 08:59:25 AM »
I got this error as well.  It is because the word "rank" is now a reserved word and can no longer be used as a field name as of MySQL 8.0, which is what I'm running and what I am assuming you are as well?

The query trying to be run is this: select minimum_hours, rank from ranks where rank_id = 1;

This would require a rename of that field in that table and then changing all SQL queries referencing it to whatever the new name is.

I might attempt to fix this myself since Alejandro seems MIA these days, but it might be easier to just back up to MySQL 7.x instead.

drgullen

  • Full Member
  • ***
  • Posts: 142
    • View Profile
Re: SQL Error - clean install
« Reply #2 on: September 20, 2019, 06:50:42 PM »
This actually isn't too hard to fix.  It's just a bit of trial and error as there's lots of php pages to change.  Here's what I did:

1) You have to drop and then recreate the Ranks table:

DROP TABLE IF EXISTS `ranks`;
CREATE TABLE IF NOT EXISTS `ranks` (
  `rank_id` int(11) NOT NULL AUTO_INCREMENT,
  `rank_level` varchar(50) NOT NULL,
  `salary_hour` int(11) NOT NULL,
  PRIMARY KEY (`rank_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Notice the bold line above.  This is where you change the field name.  I chose rank_level, but you can choose whichever name you like as long as it isn't just rank.

2) The rest of the fields in this table were added subsequently in the db schema script file, so you have to run those statements again.  For me, they are on lines 1392 through 1403 of that file:

ALTER TABLE
  `ranks`
ADD
  `minimum_hours` INT NULL;
ALTER TABLE
  `ranks`
ADD
  `maximum_hours` INT NULL;
ALTER TABLE
  `ranks`
ADD
  `image_url` varchar(500) NULL;

3)  If you want the default ranks that were in there to begin with, you'll have to insert them again.  This is optional, but if you don't do this, keep in mind your new Ranks table will be empty to start with.  This code is on line 20,750 of the db_data_2.sql db file:

INSERT INTO `ranks` (`rank_id`, `rank_level`, `salary_hour`) VALUES
(1, 'New Hire', 100),
(2, 'First Officier', 250),
(3, 'Captain', 1000),
(4, 'Senior Captain', 2000);

Again, notice in bold where I'm using rank_level instead of just rank.

4)  Finally, it's just a matter of combing through the entire application searching for the word rank and, where appropriate, changing it to (in my case) rank_level, since that's how I defined it when I recreated the table.

You will get a lot of false positives in the search, so be careful what you change.  I take no responsibility if you change something that messes up your application -- I provided this information just for those that are SQL savvy -- if you don't know SQL, don't do any of this.

I should also note that you need to keep in mind that if Alejandro fixes this himself in a future patch, he may choose to not use the same name as me/you for this field, meaning if you do this, his next update might break your Ranks again.  So, if you want to fix this "officially", you must downgrade to MySQL 7.x and wait for an official VAM patch before returning to MySQL 8.

Furthermore, I've noticed that PHP 7.3 essentially breaks the Administration area, so if you're on that version of PHP, you must downgrade that as well.  I'm on PHP 7.2.2 and all seems to work properly now.