0

My application logs show "Prepared statement needs to be re-prepared" after running mysqltuner ( bother version 1.1 and 1.4 ). Once I run "flush tables;", the error goes away. I have looked online and most things mention mysqldump in relation to this error, but I am not running mysqldump. Not really doing anything, in fact. So a few questions related to this error:

  1. Any causes to this that are not related to mysqldump, generally speaking?
  2. Anyone else have this problem with mysqltuner?
  3. Does this suggest a deeper issue I need to look into?

my.conf:

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#  NOTE:  server did not like over 2GB
innodb_buffer_pool_size=1GB
innodb_log_buffer_size=9M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
#  join_buffer_size = 128M
join_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

character-set-server=utf8
query_cache_size=100M
query_cache_type=1
slow_query_log=ON
query_cache_limit = 10M
table_open_cache=164000
open_files_limit=328000

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Tell me if you need other information.

Edit 03/10/2015: I just did the following with no problems:

  1. Ran mysqldump to dump one database ( fully, with data and functions ) to "loadDB.sql" script for later use.
  2. Added new database
  3. Using "mysql < loadDB.sql", loaded my new database
  4. Updated and added rows to my new database I did not have any problems with the above.

So I only have a problem if I run mysqltuner. What I don't know is if

  1. mysqltuner is causing the problem, in which case I just either don't run mysqltuner ever, or immediately run "flush tables;" afterwards.
  2. there is a problem with my mysql configuration, which will cause me problems later and needs to be resolved.
  3. or there is some other problem source I have not considered yet The first step, and the reason for this post, is to determine if mysqltuner could cause this problem.

Thanks.

Tony B
  • 915
  • 1
  • 9
  • 24
  • Are you using any prepared statements in your procedures? – N.B. Mar 09 '15 at 22:09
  • Probably. It is buried in C code, via ODBC. My concern is that everything seems fine until I run mysqltuner. After that, things break and stay broken until I run "flush tables;". I just don't understand how mysqltuner could influence things so much. – Tony B Mar 10 '15 at 00:18
  • Ok, checking one case, we definitely have a prepare in the ODBC based code, but the select involved is super simpler, and is limited to 1 row: SELECT 1 FROM mbo USE INDEX(mbo_mbodock) WHERE mbosts = ? AND WHMSLayer_IS_LOC_ACCESSIBLE( ?, ?, mbodoc ) > 0 ORDER BY mbosts LIMIT 1. – Tony B Mar 10 '15 at 16:42
  • Can you check [this question](http://stackoverflow.com/questions/25873883/general-error-1615-prepared-statement-needs-to-be-re-prepared) and try to increase the value of mentioned variables? – N.B. Mar 10 '15 at 22:57
  • First reply: my table_open_cache is considerably bigger than the suggestion in that post. But I did not change table_definition_cache, so I suppose I could try that. All that said, that post says it still happens sometimes. I see no evidence that he actually solved the problem. Do you know different? – Tony B Mar 11 '15 at 17:35

1 Answers1

0

mysqltuner is not running any FLUSH command at all in the code.

I have checked it in the last 1.5.1 version.

jmrenouard
  • 104
  • 3