mysql error

[dave99@gmail.com]

I'm having some problems getting the nightly routines to fire in mysql. System is centos 5.3 x64, with mysql 5.1.50 icc. When midnight rolls around, mysql becomes unresponsive and I have to do a kill -9 on it's process to end it and then restart. I've gone through the install a couple of times trying to narrow it down, sometimes it installs ok, sometimes it has the same kind of error as above. I've updated from mysql 5.1.39 to 5.1.50 to see if that helped, didn't seem to. Event scheduler is on and appears to be firing the events. I can create the partitions manually (from other posts in the forum on how to do so), but something is the automatic function is just killing it.

Not much to go on, but was hoping someone might have an idea. 

Thanks,
dave

[cdukes]

Can you turn on full mysql logging to see if your event scheduler is broken somehow?
Are there any other jobs that run at midnight that may be having an adverse affect on mysql?

[dave99@gmail.com]

I don't see much that helps unfortunately.
Here is the normal mysql.err file. I disabled all the other mysql connections to try and keep the log file clean. I wiped out the syslog db and user, restarted mysql with logging enabled, installed logzilla, restarted syslog-ng waited a minute then tried to restart mysql, which was hung at that point. Killed the process and restarted:

100828 16:36:50  InnoDB: Started; log sequence number 1 2301577432
100828 16:36:50 [Note] Event Scheduler: Loaded 0 events
100828 16:36:50 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.1.50-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
100828 16:36:50 [Note] Event Scheduler: scheduler thread started with id 1
100828 16:38:45 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
100828 16:38:45 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
100828 16:38:45 [Note] Event Scheduler: Waiting for the scheduler thread to reply
100828 16:38:45 [Note] Event Scheduler: Stopped
100828 16:38:45 [Note] Event Scheduler: Purging the queue. 3 events
100828 16:38:47 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 43  user: 'root'
100828 16:38:47 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 42  user: 'root'
100828 16:38:47 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 40  user: 'syslogadmin'
100828 16:39:09 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
100828 16:39:09 [Note] Plugin 'FEDERATED' is disabled.
100828 16:39:09  InnoDB: Started; log sequence number 1 2301577432
100828 16:39:09 [Note] Recovering after a crash using /usr/local/mysql/data/mysql-bin-repl
100828 16:39:09 [Note] Starting crash recovery...
100828 16:39:09 [Note] Crash recovery finished.
100828 16:39:09 [Note] Event Scheduler: Loaded 3 events
100828 16:39:09 [Note] /usr/local/mysql/bin/mysqld: ready for connections.Version: '5.1.50-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
100828 16:39:09 [Note] Event Scheduler: scheduler thread started with id 1


I enabled query logging in the cnf file with:
#general_log_file=/tmp/genlog.log
#general_log=1

I've attached the query log also.

[dave99@gmail.com]

Here are a couple more details I was able to get when it died again last night. What happens is it stops accepting connections, but I can still get into the mysql console. Running a show connections gets me this (trimmed down to show the first few connections, the rest is all cacti and other processes trying to open tables)

*************************** 1. row ***************************
     Id: 1
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 44205
  State: Waiting for next activation
   Info: NULL
*************************** 2. row ***************************
     Id: 6277
   User: syslogadmin
   Host: localhost:53517
     db: syslog
Command: Query
   Time: 30570
  State: Saving state
   Info: LOAD DATA LOCAL INFILE '/dev/shm/infile.txt' INTO TABLE logs FIELDS TERMINATED BY "\t" LINES TERMINA
*************************** 3. row ***************************
     Id: 6284
   User: snmptt
   Host: localhost:44050
     db: snmp
Command: Query
   Time: 30259
  State: Opening tables
   Info: INSERT INTO snmptt_unknown (trapoid,enterprise,community,hostname,agentip,uptime,traptime,formatline
*************************** 4. row ***************************
     Id: 6287
   User: cacti
   Host: localhost
     db: cacti
Command: Query
   Time: 30403
  State: closing tables
   Info: REPLACE INTO settings (name,value) VALUES ('poller_lastrun',1283072702)
*************************** 5. row ***************************
     Id: 6288
   User: syslogadmin
   Host: localhost:44067
     db: syslog
Command: Query
   Time: 30403
  State: Opening tables
   Info: UPDATE sph_counter SET max_id= (SELECT MAX(id) FROM logs)

[cdukes]

Which version of LogZilla?
I corrected an old issue of using mysql_pconnect instad of mysql_connect after v3.0.86, so maybe you are hitting that bug?

[dave99@gmail.com]

3.0.85, I'll get 3.0.91 installed and see if that changes anything.

[dave99@gmail.com]

Same thing, it dies shortly after install (within a minute or so), this is near the top of the processlist:
*************************** 3. row ***************************
     Id: 9675
   User: syslogadmin
   Host: localhost:39614
     db: syslog
Command: Query
   Time: 161
  State: Saving state
   Info: LOAD DATA LOCAL INFILE '/dev/shm/infile.txt' INTO TABLE logs FIELDS TERMINATED BY "\t" LINES TERMINA

[dave99@gmail.com]

Changing from /dev/shm to /tmp/infile.txt seems to avoided the mysql crash that occurs after install, we'll see if it craps out at midnight.

Should this line always be in the processlist? (I'm assuming it's kind of acting like a daemon waiting to insert data)

Code: [Select]

Kill  50  syslogadmin  localhost:36206  syslog  Query  120  Saving state  LOAD DATA LOCAL INFILE '/tmp/infile.txt' INTO TABLE LOGS FIELDS TERMINATED BY "\t" LINES TERMINATED


[cdukes]

Changing from /dev/shm to /tmp/infile.txt seems to avoided the mysql crash that occurs after install, we'll see if it craps out at midnight.

Ah, is there no /dev/shm (shared memory) on CentoS?

Should this line always be in the processlist? (I'm assuming it's kind of acting like a daemon waiting to insert data)

Code: [Select]

Kill  50  syslogadmin  localhost:36206  syslog  Query  120  Saving state  LOAD DATA LOCAL INFILE '/tmp/infile.txt' INTO TABLE LOGS FIELDS TERMINATED BY "\t" LINES TERMINATED


Only while it's loading the file which usually takes < 1 second.
Did you follow the Centos install guide? I'm not up on that OS, but someone else was kind enough to write one at:
http://nms.gdd.net/index.php/LogZilla_on_Redhat/CentOS/Fedora

[dave99@gmail.com]

There is a /dev/shm, but I thought maybe it was causing trouble. I was wrong, it still died, just took a bit longer this time. After a couple restarts mysql and syslog-ng, it's running now without the local data infile process running. We'll see what happens.

[cdukes]

ok, keep me posted.
If you can't get it figured out, let me know and we can do a remote session to see if I can figure it out w/ you

[ThomasH]

I found out (maybe) the newer mysql-Server Packages aren´t compiled with --enable-local-infile.

So the db_insert.pl script come with the error message: "ERROR 1148: The used command is not allowed with this MySQL version".

I first see this on a OpenSUSE 11.3 (while writing some manuals).

Can you verify this?

Tom

[cdukes]

Ah yes...
I'll add that to the docs!
http://forum.logzilla.info/index.php/topic,236.0.html

[cdukes]

Oops, it's already in the documentation

http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.0#Mysql_Error:_The_used_command_is_not_allowed_with_this_MySQL_version

[dave99@gmail.com]

Crapped out again at midnight.

Code: [Select]

Kill      61      syslogadmin      localhost:47054      syslog      Query      39      Saving state      LOAD DATA LOCAL INFILE '/tmp/infile.txt' INTO TABLE LOGS FIELDS TERMINATED BY "\t" LINES TERMINATED

Kill      1      event_scheduler      localhost      None      Daemon      40      Waiting for next activation      ---
I had already added the local file fixes to my .cnf and the db_insert.pl from reading other threads. Out of habit, I use the intel compiled binaries from mysql, I'll try switching to the regular ones and see if that makes a difference. That's the last thing I can think of.