User settings - DB Error SQLSTATE[42000]

[jhs0]

Hello,

Just finished installing Part-DB Version: 0.5.6 (stable) php 7.3.7 MySQL 8.0.16

When try going to User settings or User information, I get this error message:

Database error:
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'groups WHERE id='1'' at line 1

SQL-Query:
SELECT * FROM groups WHERE id=?

Parameter:
Array
(
[0] => 1
)

Please help me fix it.

Thank you, Ivan

[jlegen]

Hi,

since MySQL 8.0.2, "groups" is a reserved word! So, your options are: 1) downgrade MySQL, or use MariaDB 2) use a backtick () to surround "groups" where ever it appears in the code ("select xy from groups.." => "select xy fromgroups` ...) 3) rename table "groups" to somehing else of course, 2) or 3) should be also considered by the maintainer...

[jbtronics]

@jhs0 Could you try the current version from the master branch (https://github.com/Part-DB/Part-DB/archive/master.zip) and check if the error is fixed?

All tablenames should now be escaped. That should hopefully fixes your problem.

If it works for you, i will put that patch into the stable branch, too.

[jhs0]

Hi,

since MySQL 8.0.2, "groups" is a reserved word! So, your options are:

1. downgrade MySQL, or use MariaDB
2. use a backtick () to surround "groups" where ever it appears in the code ("select xy from groups.." => "select xy fromgroups` ...)
3. rename table "groups" to somehing else of course, 2) or 3) should be also considered by the maintainer...

Thank you jlegen. I would not consider option 1. Options 2 and 3 I like, however, the Database.php file I have is different and the whitelist array does not contain 'groups'. In fact, the word 'groups' does not exist in this file: $whitelist = array("parts", "categories", "footprints", "storelocations", "suppliers", "pricedetails", "orderdetails", "manufacturers", "attachements", "attachement_types", "devices", "device_parts");

Any ideas where to manipulate the 'groups' table name? Database.zip

[jhs0]

@jbtronics I tried first with the current version. I failed in the early stage of connecting to the DB. If used the part-db user -> complained about permissions. If used root user -> complained about a table not found. I did not take any notes or screenshots though. So I switched to 'stable' and here I am...

If you are interested, I could do it again and provide more info on the above.

[jbtronics]

@jhs0 for testing you could use the same database for the "master" version as you used now (just copy the data/config.php file into the folder of the master version). Then you could check if the error message about the database syntax has disappeared (in that case my fix has worked).

[jhs0]

@jbtronics Now the current version works. I will stick with it. How far is it from stable? Can I expect some rough ride with it?

@jlegen You probably referred to the Database.php from the development version before the escaping of the tables names.

[jlegen]

@jlegen You probably referred to the Database.php from the development version before the escaping of the tables names.

i did not refer to any specific file - i just said "use a backtick" to enclose table names - where jbtronics used the more correct term "escaping"... :) Also, "database.php" seems not to be the only file "affected" by non-escaped table names; if Oracle next week decides that they also want to reserve the string "users", then something in "User.php" may also break: lib/User.php:807: $query = 'SELECT * FROM users WHERE name = ?'; But afaik jbtronics is already busy with refactoring the whole thing - thanks for that, btw!

[jhs0]

@jlegen @jbtronics Many thanks to both of you!

Just one last thing. Could you please help me fix the same error, coming from /edit_groups.php and /edit_users.php in the dev version? I have troubles finding the locations to touch-up.

[jbtronics]

@jhs0 Could you show copy the exact error message? This makes it a lot easier to findout where the issue is.

@jbtronics Now the current version works. I will stick with it. How far is it from stable? Can I expect some rough ride with it?

The dev version should be relativley stable now (i use it too for my database). Nevertheless i have put the patch from above in the stable version too (https://github.com/Part-DB/Part-DB/archive/stable-v0.5.zip).

[jhs0]

@jbtronics

Datenbankfehler:
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'groups WHERE parent_id <=> NULL ORDER BY name ASC' at line 1

SQL-Query:
SELECT * FROM groups WHERE parent_id <=> ? ORDER BY name ASC

Parameter:
Array
(
[0] => 
)

Please see attached.

[jhs0]

...maybe StructuralDBElement.php I will try to escape the table name there...

[jhs0]

yay, it worked! in file lib/Base/StructuralDBElement.php added backticks around table name:

            $query_data = $this->database->query('SELECT * FROM `' . $this->tablename .
                '` WHERE parent_id <=> ? ORDER BY name ASC', array($id));

            'SELECT * FROM `' . $parent_element->getTablename() .
            '` WHERE name=? AND parent_id <=> ? AND id<>?',

thank you, thank you, thank you!!! Cheers!

[jbtronics]

Should be fixed now in code too. See new Release Version 0.5.7