Ticket 2268 - sql error with mariadb 10.x
Summary: sql error with mariadb 10.x
Status: RESOLVED CANNOTREPRODUCE
Alias: None
Product: Slurm
Classification: Unclassified
Component: slurmdbd (show other tickets)
Version: 15.08.5
Hardware: Linux Linux
: 3 - Medium Impact
Assignee: David Bigagli
QA Contact:
URL:
Depends on:
Blocks:
 
Reported: 2015-12-17 19:53 MST by David Bigagli
Modified: 2015-12-20 21:26 MST (History)
0 users

See Also:
Site: SchedMD
Slinky Site: ---
Alineos Sites: ---
Atos/Eviden Sites: ---
Confidential Site: ---
Coreweave sites: ---
Cray Sites: ---
DS9 clusters: ---
Google sites: ---
HPCnow Sites: ---
HPE Sites: ---
IBM Sites: ---
NOAA SIte: ---
NoveTech Sites: ---
Nvidia HWinf-CS Sites: ---
OCF Sites: ---
Recursion Pharma Sites: ---
SFW Sites: ---
SNIC sites: ---
Tzag Elita Sites: ---
Linux Distro: ---
Machine Name:
CLE Version:
Version Fixed:
Target Release: ---
DevPrio: ---
Emory-Cloud Sites: ---

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this ticket.
Description David Bigagli 2015-12-17 19:53:21 MST 
From the user list:

----------------------------------------------------------
Dear SLURM Community,

After we upgrade the database from MariaDB-5.x to 10.0.22, we are having DB issues when adding new users. More specifically, there are some problem running database "UPDATE".

The command I ran:
========================
/usr/bin/sacctmgr add  user Name=foo  Partition=batch  Account=acct AdminLevel=None
========================

Here is the error we see in the slurmdbd.log:
========================
[2015-12-17T14:30:22.024] error: mysql_query failed: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'partition='batch' order by lft FOR UPDATE' at line 1
========================

Have any of you seen the similar issues? If so, can you shine some lights on how to resolve it?

Thanks!

Kai

-- 
Kai Song
<ksong@lbl.gov> 1.510.495.2180
1 Cyclotron Rd. Berkeley, CA94720, MS-50B 3209
High Performance Computing Services (HPCS)
Lawrence Berkeley National Laboratory - http://scs.lbl.gov
----------------------------------------------------------

David
Comment 1 Tim Wickberg 2015-12-18 01:29:27 MST 
I can't currently reproduce, but suspect that the debian mariadb version is doing something differently than CentOS.

PARTITION is a keyword in MariaDB, so that appears to be causing the problem. It looks like this has been available as an optional feature in prior MySQL versions, but I'm guessing it's now on by default and thus their parser has changed?

It's an unfortunate namespace collision - I'm guessing the immediate fix is to escape the column name everywhere, or maybe to rename it in 16.05?
Comment 2 Danny Auble 2015-12-18 01:45:36 MST 
Partition should be handled, perhaps it's missed somewhere? If appears to be the case based on the output. I haven't looked at the code but if it is just a simple overlook it should be a simple fix.
Comment 3 Danny Auble 2015-12-18 03:29:29 MST 
David could you turn on DebugFlags=DB_ASSOC and send the slurmdbd.log file for the time you see the error?  I don't see this happening for 15.08 or 14.11.
Comment 4 David Bigagli 2015-12-20 21:26:05 MST 
I have installed MariaDB 10.0.23 from http://yum.mariadb.org/10.1/centos6-amd64
and run 14.11 and 15.08 clusters with no errors. 

David