MySQL 4.0.16 Problems?

[pelicanparts]

Okay, well I upgraded to the new version last night, and the timeout problem I was experiencing disappeared. However, now I wake up this morning, and there's a ton of vBulletin MySQL error emails in my box. Well, not a ton, but like 10, and I usually received about 1 per month when I was on 3.23.

Here are some of the emails:

[pelicanparts]

Database error in vBulletin 2.3.2:

Invalid SQL: UPDATE session SET lastactivity=1066938260,location='/showthread.php?s=&threadid=132792' WHERE sessionhash='83a627752b0c06994492aa18322e314b'

mysql error: Lost connection to MySQL server during query

mysql error number: 2013

Date: Thursday 23rd of October 2003 12:44:20 PM

Script: http://forums.pelicanparts.com/showthread.php?s=&threadid=132792

Referer: http://forums.pelicanparts.com/forumdisplay.php?forumid=17


Database error in vBulletin 2.3.2:

Invalid SQL: UPDATE session SET lastactivity=1066938260,location='/showthread.php?s=&threadid=132792' WHERE sessionhash='83a627752b0c06994492aa18322e314b'

mysql error: Lost connection to MySQL server during query

mysql error number: 2013

Date: Thursday 23rd of October 2003 12:44:20 PM

Script: http://forums.pelicanparts.com/showthread.php?s=&threadid=132792

Referer: http://forums.pelicanparts.com/forumdisplay.php?forumid=17


Database error in vBulletin 2.3.2:

Invalid SQL: DELETE FROM session WHERE sessionhash=''

mysql error: Lost connection to MySQL server during query

mysql error number: 2013

Date: Thursday 23rd of October 2003 12:28:50 PM

Script: http://forums.pelicanparts.com/forumdisplay.php?s=&forumid=8

Referer:


Database error in vBulletin 2.3.2:

Invalid SQL: SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.userid='11857'

mysql error: Lost connection to MySQL server during query

mysql error number: 2013

Date: Thursday 23rd of October 2003 11:21:35 AM

Script: http://forums.pelicanparts.com/showthread.php?threadid=132595&goto=newpost

Referer:


Database error in vBulletin 2.3.2:

Invalid SQL: SELECT template,title

FROM template

WHERE (title IN ('showthread_ratingdisplay','postbit_search','post bit_buddy','postbit_ignore','postbit_useremail','i cq','aim','yahoo','postbit_homepage','postbit_prof ile','postbit_ip_show','postbit_ip_hidden','postbi t','postbit_sendpm','postbit_avatar','postbit_offl ine','postbit_online','postbit_editedby','postbit_ signature','postbit_attachment','postbit_attachmen timage','showthread_adminoptions','showthread_thre adrate','showthread_pollresults_voted','showthread _pollresults_closed','showthread_pollresults_cantv ote','showthread_firstunread','showthread_nextnewe stthread','showthread_nextoldestthread','forumrule s','showthread','gobutton','timezone','username_lo ggedout','username_loggedin','phpinclude','headinc lude','header','footer','forumjumpbit','forumjump' ,'nav_linkoff','nav_linkon','navbar','nav_joiner', 'pagenav','pagenav_curpage','pagenav_firstlink','p agenav_lastlink','paenav_nextlink','pagenav_pageli nk','pagenav_prevlink')

AND (templatesetid=-1 OR templatesetid=1))





ORDER BY templatesetid

mysql error: Unknown MySQL error

mysql error number: 2000

Date: Thursday 23rd of October 2003 11:18:01 AM

Script: http://forums.pelicanparts.com/showthread.php?threadid=132790

Referer: http://forums.pelicanparts.com/forumdisplay.php?s=&forumid=8


Database error in vBulletin 2.3.2:

Invalid SQL: UPDATE session SET lastactivity=1066933081,location='/showthread.php?threadid=132790' WHERE sessionhash='f8f45f2f71b6c0b5e166f379f0ed9a0d'

mysql error: Lost connection to MySQL server during query

mysql error number: 2013

Date: Thursday 23rd of October 2003 11:18:01 AM

Script: http://forums.pelicanparts.com/showthread.php?threadid=132790

Referer: http://forums.pelicanparts.com/forumdisplay.php?s=&forumid=8


Database error in vBulletin 2.3.2:

Invalid SQL: SELECT postid,wordid FROM searchindex WHERE wordid IN (0,'702072','701154','702014','704038')

mysql error: Unknown MySQL error

mysql error number: 2000

Date: Thursday 23rd of October 2003 09:05:56 AM

Script: http://forums.pelicanparts.com/search.php

Referer: http://forums.pelicanparts.com/search.php?s=

[pelicanparts]

Database error in vBulletin 2.3.2:

Invalid SQL:

SELECT findword, replaceword, replacementsetid

FROM replacement

WHERE replacementsetid IN(-1, '2')

ORDER BY replacementsetid, replacementid DESC



mysql error: Unknown MySQL error

mysql error number: 2000

Date: Thursday 23rd of October 2003 08:39:56 AM

Script: http://forums.pelicanparts.com/showthread.php?s=&postid=1004862#post1004862

Referer: http://forums.pelicanparts.com/forumdisplay.php?forumid=24



Database error in vBulletin 2.3.2:

Invalid SQL: DELETE FROM session WHERE sessionhash=''

mysql error: Lost connection to MySQL server during query

mysql error number: 2013

Date: Thursday 23rd of October 2003 08:39:57 AM

Script: http://forums.pelicanparts.com/showthread.php?s=&postid=1004862#post1004862

Referer: http://forums.pelicanparts.com/forumdisplay.php?forumid=24

[Steve Machol]

This is a server issue. See this post for the possible causes of a lost connection error:

http://www.vbulletin.com/forum/showthread.php?s=&postid=310265#post310265

[pelicanparts]

Anyone have a URL for the "official" MySQL forum where I can also report this problem? I couldn't seem to find one...

Thanks,

Wayne

[pelicanparts]

Steve, no offense, but you said that the other day, and it turned out to completely be a MySQL issue (the timeout issue associated with 4.015 that was fixed with 4.016). See the other post about error 2013...

I'm not sure if it's a MySQL issue, or it could be that I don't have enough connections available? Question, if there aren't enough connections open, then will it give these errors? Or will it give a specific error that there aren't any connections open?

Thanks,

Wayne

[Steve Machol]

All I did was post a link to a thread about the release of MySQL 4.0.16. Sorry if you think I was endorsing this as an end-all to all possible problems. That was not my intention. I was merely trying to help.

[pelicanparts]

Whoops Steve, you took my previous post the wrong way. Thank you very much for posting that, as it did fix the other problem I was having with the repair and backup commands timing out after about 30 seconds (confirmed problem in 4.015).

After dealing with those issues, and spending time on them, and then finding out that it was a MySQL bug, I'm not too quick to diagnose this as a Server config problem, that's all.

What version do you guys recommend running now for vBulletin. I *never* had any problems with 3.23 - it might be time to return to that version...

???

Thanks for all your help, Steve...

-Wayne

[pelicanparts]

Why is my previous post white?

Hmmm...

-Wayne

[john_rsd]

Why is my previous post white?

Hmmm...

-Wayne

possibly releated to http://www.vbulletin.com/forum/bugs.php?do=view&bugid=916

[pelicanparts]

After running for about 18 hours today, I got about 20-40 MySQL error messages from PHP/vBulletin. They seem to come in pairs, the first being a 2013 Lost connection, followed by a 2000 Unknown MySQL error message.

Does anyone know anything about this 2000 message?

-Wayne

[Steve Machol]

The MySQL error numbers don't mean much. We'd need to see the complete error message.

[eva2000]

Okay, well I upgraded to the new version last night, and the timeout problem I was experiencing disappeared. However, now I wake up this morning, and there's a ton of vBulletin MySQL error emails in my box. Well, not a ton, but like 10, and I usually received about 1 per month when I was on 3.23.

Here are some of the emails:
btw how large is your forum, post, threads, users and total database size ?

can you post the out of this command in ssh telnet

mysqladmin -u root -p variables

also check your mysql data directory usually /var/lib/mysql there's a file called yourhostname.err

type

cd /var/lib/mysql
tail -200 yourhostname.err

where yourhostname.err is your server's name/hostname

and see what error messages if any are reported

[pelicanparts]

+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | d:\mysql\ |
| bdb_cache_size | 8388572 |
| bdb_log_buffer_size | 1048576 |
| bdb_home | d:\mysql\data\ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\ |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (October 17, 2003) |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 300 |
| convert_character_set | |
| datadir | d:\mysql\data\ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 1800 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | YES |
| have_crypt | NO |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 402653184 |
| language | d:\mysql\share\english\ |
| large_files_support | ON |
| local_infile | ON |
| log | OFF |
| log_update | OFF |
| log_bin | ON |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | ON |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 500 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 0 |
| pid_file | d:\mysql\data\forums2.pid |
| log_error | .\forums2.err |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 4190208 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| sort_buffer_size | 4194268 |
| sql_mode | 0 |
| table_cache | 2048 |
| table_type | MYISAM |
| thread_cache_size | 64 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | Pacific Daylight Time |
| tmp_table_size | 33554432 |
| tmpdir | C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.16-max-debug-log |
| wait_timeout | 4600 |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[pelicanparts]

Here's the error file - nothing that seems too out of the ordinary, or that corresponds with the error messages...

MySql: ready for connections.
Version: '4.0.15-nt' socket: '' port: 3306
031022 23:24:41 MySql: Normal shutdown
031022 23:24:41 MySql: Shutdown Complete
MySql: ready for connections.
Version: '4.0.15-nt' socket: '' port: 3306
031023 0:03:27 MySql: Normal shutdown
031023 0:03:28 MySql: Forcing close of thread 5 user: 'mysql-login'
031023 0:03:28 MySql: Forcing close of thread 4 user: 'mysql-login'
031023 0:03:28 MySql: Forcing close of thread 3 user: 'mysql-login'
031023 0:03:28 MySql: Forcing close of thread 2 user: 'mysql-login'
031023 0:03:28 MySql: Forcing close of thread 1 user: 'mysql-login'
031023 0:03:28 MySql: Shutdown Complete
MySql: ready for connections.
Version: '4.0.15-nt-log' socket: '' port: 3306
031023 0:09:42 MySql: Normal shutdown
031023 0:09:43 MySql: Shutdown Complete
MySql: ready for connections.
Version: '4.0.15-nt-log' socket: '' port: 3306
031023 0:27:08 MySql: Normal shutdown
031023 0:27:08 MySql: Shutdown Complete
MySql: ready for connections.
Version: '4.0.15-nt-log' socket: '' port: 3306
031023 3:04:13 MySql: Normal shutdown
031023 3:04:15 MySql: Forcing close of thread 53 user: 'ODBC'
031023 3:04:15 MySql: Forcing close of thread 25 user: 'mysql-login'
031023 3:04:15 MySql: Forcing close of thread 20 user: 'mysql-login'
031023 3:04:15 MySql: Forcing close of thread 19 user: 'mysql-login'
031023 3:04:15 MySql: Forcing close of thread 18 user: 'mysql-login'
031023 3:04:15 MySql: Forcing close of thread 17 user: 'mysql-login'
MySql: ready for connections.
Version: '4.0.15-nt-log' socket: '' port: 3306
031023 3:14:21 MySql: Normal shutdown
031023 3:14:22 MySql: Forcing close of thread 6 user: 'mysql-login'
031023 3:14:22 MySql: Forcing close of thread 5 user: 'mysql-login'
031023 3:14:22 MySql: Forcing close of thread 4 user: 'mysql-login'
031023 3:14:22 MySql: Forcing close of thread 2 user: 'mysql-login'
031023 3:14:22 MySql: Forcing close of thread 1 user: 'mysql-login'
MySql: ready for connections.
Version: '4.0.15-nt-log' socket: '' port: 3306
031023 3:18:54 MySql: Normal shutdown
031023 3:18:55 MySql: Forcing close of thread 6 user: 'mysql-login'
031023 3:18:55 MySql: Forcing close of thread 5 user: 'mysql-login'
031023 3:18:55 MySql: Forcing close of thread 4 user: 'mysql-login'
031023 3:18:55 MySql: Forcing close of thread 3 user: 'mysql-login'
031023 3:18:55 MySql: Forcing close of thread 2 user: 'mysql-login'
031023 3:18:55 MySql: Shutdown Complete
MySql: ready for connections.
Version: '4.0.16-nt-log' socket: '' port: 3306
031023 3:27:08 Note: Found 662358 of 662130 rows when repairing './vbulletin/word'
031024 2:25:55 MySql: Normal shutdown
031024 2:25:56 MySql: Forcing close of thread 67966 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 31574 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 24515 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 88 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 85 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 70 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 69 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 68 user: 'mysql-login'
031024 2:25:56 MySql: Forcing close of thread 67 user: 'mysql-login'
mysqld: ready for connections.
Version: '4.0.16-max-debug-log' socket: '' port: 3306


???

-Wayne

[eva2000]

there's your problem

key_buffer_size | 402653184 |

WAAAAAAY TOOO HIGH! you'd swap your server to death :)

and table_cache of 2048 is a bit too high

i don't remember recommending you set 400+MB key_buffer for ya heh

keep to this my.cnf below


[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[pelicanparts]

I increased it because the myhuge.ini sample file that comes with MySQL has a key buffer recommendation of 384M. I'm running with 2GB of RAM - the server *never* seems to use anything more than about 800MB.

Is there a disadvantage to upping these values if I have the RAM? I did some independent research that seemed to indicate that increasing these values would improve performance as long as the connections weren't max'ed out...

I'll try going back to the old values and see if I still get the error messages. I would like MySQL to use as much of the memory as it needs - right now it doesn't seem to be doing that...

-Wayne

[pelicanparts]

I'm going to try downgrading again to 3.23. Since I ugpraded to the new version and the new server, I've had MySQL crashing problems, particularly when I try to issue a stop command "net stop mysql" - it says the process has terminated unexpectedly.

So 4.015 didn't work because of the timeout issue
4.016 is giving me MySQL closed connections (server gone)

We'll see what 3.23 does. This is running on Windows 2003 Server - Web Edition, which is new, so I can't be 100% sure that these problems are related specifically to MySQL. This downgrade to 3.23 should tell me.

Theoretically, there's no problem using the datafiles to go back to 3.23, if I remember correctly?

-Wayne

[pelicanparts]

I just installed 3.23 - so far so good. I can start and stop the service without it "terminating unexpectedly."

I'll let everyone here know what I find.

Has anyone else been using Windows Server 2003 and MySQL 4.015 or 4.016?

Thanks,

Wayne

[DVD Plaza]

Has anyone else been using Windows Server 2003 and MySQL 4.015 or 4.016?Your game!!! :D

Admittedly 2003 does seem to have impressive performance, but it's still too bleeding edge to be depended upon yet (not that Windows can be depended upon anyways, but alas)...

[eva2000]

my.ini they recommend is based on a dedicated mysql database server with nothing else running.. if you run apache/php/mail/dns on same server as mysql you'd run out of memory alot faster 16M - 64M for keybuffer should do you

[pelicanparts]

Hmm, well today I got a few more of the 2013 and 2000 errors. Much less than with 4.016, but there are also a lot less people on the server over the weekend. I may end up going back to 2000 if this doesn't work out too well.

I've also been getting PHP access violation errors occassionally as well. This may be the final straw for Windows2003...

-Wayne

[Odysseus]

I'm running MySQL 4 for already more than a week now and did not get any error message.
Maybe there ist something wrong with you operating system configuration, or even with your hardware (RAM)?

Win2k3 ... hm, why not, give it a try. :)

[hardatwork]

there's your problem

key_buffer_size | 402653184 |

WAAAAAAY TOOO HIGH! you'd swap your server to death :)

and table_cache of 2048 is a bit too high

i don't remember recommending you set 400+MB key_buffer for ya heh

keep to this my.cnf below

eva2000 is a mysql tuning wizard but for reasons no one can explain, he always recommends the tiniest key buffer on the planet.

Since this value is shared across all connections, i would recommend doubling or tripling what he recommends.

On a dedicated server with 2 gigs of ram, 256 isn't un reasonable at all I would think. Thats the size I use with 2 gigs of ram running apache on the same box AND innodb tables

If you aren't using iinnodb you should be able use a higher number without problems IMHUWO (in my humble un-wizardly opinion).

[pelicanparts]

As a followup to this, I moved everything back to my old server running win2K, and now the database problems have disappeared after repairing the tables. I think that they might be related to running Windows Server 2003. I'm going to move the forums back to the other server tonite, which has been changed to Win 2000. If it's a hardware problem, it will show up then...

-Wayne