Ticket 2268

Summary:	sql error with mariadb 10.x
Product:	Slurm	Reporter:	David Bigagli <david>
Component:	slurmdbd	Assignee:	David Bigagli <david>
Status:	RESOLVED CANNOTREPRODUCE	QA Contact:
Severity:	3 - Medium Impact
Priority:	---
Version:	15.08.5
Hardware:	Linux
OS:	Linux
Site:	SchedMD	Alineos Sites:	---
Atos/Eviden Sites:	---	Confidential Site:	---
Coreweave sites:	---	Cray Sites:	---
DS9 clusters:	---	HPCnow Sites:	---
HPE Sites:	---	IBM Sites:	---
NOAA SIte:	---	OCF Sites:	---
Recursion Pharma Sites:	---	SFW Sites:	---
SNIC sites:	---	Linux Distro:	---
Machine Name:		CLE Version:
Version Fixed:		Target Release:	---
DevPrio:	---	Emory-Cloud Sites:	---

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