User settings - DB Error SQLSTATE[42000]
jhs0 opened this issue · 13 comments
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.
Hi,
since MySQL 8.0.2, "groups" is a reserved word! So, your options are:
- downgrade MySQL, or use MariaDB
- use a backtick (
) to surround "groups" where ever it appears in the code ("select xy from groups.." => "select xy from
groups` ...) - rename table "groups" to somehing else
of course, 2) or 3) should be also considered by the maintainer...
@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.
Hi,
since MySQL 8.0.2, "groups" is a reserved word! So, your options are:
- downgrade MySQL, or use MariaDB
- use a backtick (
) to surround "groups" where ever it appears in the code ("select xy from groups.." => "select xy from
groups` ...)- 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
@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.
@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).
@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 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!
@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.
@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).
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.
...maybe StructuralDBElement.php
I will try to escape the table name there...
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!
Should be fixed now in code too. See new Release Version 0.5.7