[slurm-users] slurmdbd: error: mysql_query failed: 1064

[宋亚磊]

Hello!
I'm using slurm 18.08.3 and mysql 8.0.
When I run sacct, following error will appear:

# sacct
       JobID    JobName  Partition    Account  AllocCPUS      State ExitCode 
------------ ---------- ---------- ---------- ---------- ---------- -------- 
sacct: error: slurmdbd: Unknown error 1064
  
I think 1064 is the error number of mysql. I find the details from slurmdbd.log:


slurmdbd: error: mysql_query failed: 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 'desc' at line 1
select t1.account, t1.admin_comment, t1.array_max_tasks, t1.array_task_str, t1.cpus_req, t1.derived_ec, t1.derived_es, t1.exit_code, t1.id_array_job, t1.id_array_task, t1.id_assoc, t1.id_block, t1.id_group, t1.id_job, t1.pack_job_id, t1.pack_job_offset, t1.id_qos, t1.id_resv, t3.resv_name, t1.id_user, t1.id_wckey, t1.job_db_inx, t1.job_name, t1.kill_requid, t1.mem_req, t1.node_inx, t1.nodelist, t1.nodes_alloc, t1.partition, t1.priority, t1.state, t1.system_comment, t1.time_eligible, t1.time_end, t1.time_start, t1.time_submit, t1.time_suspended, t1.timelimit, t1.track_steps, t1.wckey, t1.gres_alloc, t1.gres_req, t1.gres_used, t1.tres_alloc, t1.tres_req, t1.work_dir, t1.mcs_label, t2.acct, t2.lft, t2.user from "cluster_job_table" as t1 left join "cluster_assoc_table" as t2 on t1.id_assoc=t2.id_assoc left join "cluster_resv_table" as t3 on t1.id_resv=t3.id_resv && ((t1.time_start && (t3.time_start < t1.time_start && (t3.time_end >= t1.time_start || t3.time_end = 0))) || ((t3.time_start < t1.time_submit && (t3.time_end >= t1.time_submit || t3.time_end = 0)) || (t3.time_start > t1.time_submit))) where ((t1.time_end >= 1540828800 || t1.time_end = 0)) && (t1.state != 524288) group by id_job, time_submit desc
slurmdbd: error: Problem getting jobs for cluster cluster
slurmdbd: error: Processing last message from connection 6(127.0.0.1) uid(0)



Here is the slurm.conf and slurmdbd.conf

slurm.conf

# slurm.conf file generated by configurator.html.
# Put this file on all nodes of your cluster.
# See the slurm.conf man page for more information.
#
#
ClusterName=cluster
#ControlMachine=master
SlurmctldHost=master
#ControlAddr=
#BackupController=
#BackupAddr=
#
SlurmUser=slurm
#SlurmUser=slurm
SlurmdUser=root
SlurmctldPort=6817
SlurmdPort=6818
AuthType=auth/munge
#JobCredentialPrivateKey=
#JobCredentialPublicCertificate=
StateSaveLocation=/usr/local/slurm/18.08.3/state
SlurmdSpoolDir=/var/spool/slurm/d
SwitchType=switch/none
MpiDefault=none
SlurmctldPidFile=/var/run/slurmctld.pid
SlurmdPidFile=/var/run/slurmd.pid
ProctrackType=proctrack/pgid
#PluginDir=
FirstJobId=1
ReturnToService=2
#MaxJobCount=
#PlugStackConfig=
#PropagatePrioProcess=
#PropagateResourceLimits=
#PropagateResourceLimitsExcept=
#Prolog=
#Epilog=
#SrunProlog=
#SrunEpilog=
#TaskProlog=
#TaskEpilog=
#TaskPlugin=
#TrackWCKey=no
#TreeWidth=50
#TmpFS=
#UsePAM=
#
# TIMERS
SlurmctldTimeout=300
SlurmdTimeout=300
InactiveLimit=0
MinJobAge=300
KillWait=30
Waittime=0
#
# SCHEDULING
SchedulerType=sched/backfill
#SchedulerAuth=
SelectType=select/linear
#SelectType=select/cons_res
SelectTypeParameters=CR_Memory
#SelectTypeParameters=CR_Core
FastSchedule=0
#PriorityType=priority/multifactor
#PriorityDecayHalfLife=14-0
#PriorityUsageResetPeriod=14-0
#PriorityWeightFairshare=100000
#PriorityWeightAge=1000
#PriorityWeightPartition=10000
#PriorityWeightJobSize=1000
#PriorityMaxAge=1-0
#
# LOGGING
SlurmctldDebug=6
SlurmctldLogFile=/usr/local/slurm/18.08.3/log/slurmctld.log
SlurmdDebug=6
SlurmdLogFile=/var/log/slurmd.log
JobCompType=jobcomp/none
#JobCompLoc=
#
# ACCOUNTING
JobAcctGatherType=jobacct_gather/linux
JobAcctGatherFrequency=30
#
AccountingStorageType=accounting_storage/slurmdbd
AccountingStorageHost=localhost
AccountingStorageLoc=slurm_acct_db
AccountingStoragePass=/var/run/munge/munge.socket.2
AccountingStorageUser=slurm
#
# COMPUTE NODES
NodeName=node[1-8] CPUs=144 Sockets=4 CoresPerSocket=18 ThreadsPerCore=2 State=UNKNOWN
PartitionName=ptt1 Nodes=node[1-2] MaxTime=INFINITE State=UP
PartitionName=ptt2 Nodes=node[3-8] Default=YES MaxTime=INFINITE State=UP

slurmdbd.conf

#
# Example slurmdbd.conf file.
#
# See the slurmdbd.conf man page for more information.
#
# Archive info
ArchiveJobs=yes
ArchiveDir=/usr/local/slurm/18.08.3/archive
ArchiveSteps=yes
#ArchiveScript=
#JobPurge=12
#StepPurge=1
#
# Authentication info
AuthType=auth/munge
AuthInfo=/var/run/munge/munge.socket.2
#
# slurmDBD info
DbdAddr=localhost
DbdHost=localhost
#DbdPort=7031
SlurmUser=slurm
MessageTimeout=60
DebugLevel=4
#DefaultQOS=normal,standby
DefaultQOS=normal
LogFile=/usr/local/slurm/18.08.3/log/slurmdbd.log
PidFile=/var/run/slurmdbd.pid
PluginDir=/usr/local/slurm/18.08.3/lib:/usr/local/slurm/18.08.3/lib/slurm
#PrivateData=accounts,users,usage,jobs
#TrackWCKey=yes
##
# Database info
StorageType=accounting_storage/mysql
StorageHost=localhost
StoragePort=3306
StoragePass=PASSWD
StorageUser=slurm
StorageLoc=slurm_acct_db