MySQL Bugs: #9187: Adnormal network disconnect

Bug #9187	Adnormal network disconnect
Submitted:	15 Mar 2005 10:27	Modified:	30 Jun 2005 16:59
Reporter:	Aidan Loughran	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Windows (xp pro sp2)
Assigned to:		CPU Architecture:	Any

Description:
I connect to MySQL server on remote computer using VB6, ADO, ODBC 3.51, and InnoDB table and appear in the user connections list. Now if I pull out the network cable and wait 60 secs or so and plug in the network cable again the user is still listed in the connections list.
I then execute a query in my app and a new instance of the user appears in the connection list. If I now close the app the new connection disappears but the old one remains.
To remove it I must restart the service. From what I have read I thought when a connection was lost MySQL closed it and removed it from the list but this is not what I see. Am I missing something obvious?
It really becomes a problem for me when I want to use transactions because after the network cable is pulled out I get a lock wait timeout error the next time I try to delete data.
I have read http://dev.mysql.com/doc/mysql/en/windows-vs-unix.html. The named pipe problems seem similar to mine but I do not use named pipes.
Is this a ADO problem or MySQL problem?

How to repeat:
1. Connect to MySQL server on remote computer using VB6, ADO, ODBC 3.51
2. Start Transaction
3. Start inserting data
4. Pull out the network cable before transfer complete (break point in vb app)
5. Continue app
6. Error connection lost
7. Try rollback (cant since not connected!)
8. Plug in network cable
9. Try deleting records from table (even different from those inserted)
10. Should get lock wait time out

only way to remove it is restart server

Hi!

Can you please provide us with a test-case that shows this behaviour ?

THanks!

I will put a sample up on Monday or Tuesday.

Test Program

Attachment: mySQL.exe (application/octet-stream, text), 28.00 KiB.

I have test added a vb exe to try. You will need to set up a InnoDB table on a REMOTE daabase called "table1" and a field called "test1" (integer data type).
Start program, login to db and wait for successful message box. 
Hit the "Add Records" button. This will attempt 5 consecutive inserts to the table grouped together with a transaction. 
On the third insert a message box will appear asking user to disconnect the network cable.
Disconnect cable and then hit ok.
After a short wait should get error "lost connection to mysql server during query".
Reconnect cable and hit "Add Records" again, this time ignore disconnect message and just hit ok. Query should complete successfully. (May get server gone away message - make sure cable is in properly)
Now hit the "Delete Records" button. After 50 seconds get "lock wait timeout exceeded; try restarting transaction" message.
You can still insert records but you can not delete them.
The same timeout message will be received even if you close and reopen the app when you try to delete records.
The only way is to restart the server.
If you have any problems please ask.

vb project file

Attachment: mySQL.vbp (application/octet-stream, text), 876 bytes.

vb from module

Attachment: frmMySQL.frm (application/octet-stream, text), 9.35 KiB.

frm data

Attachment: frmMySQL.frx (application/octet-stream, text), 458 bytes.

dunno

Attachment: mySQL.vbw (text/plain), 53 bytes.

added the source just in case, sorry didn't zip them. Think you only need the .frm as the others will be created for you.

Thank you for the test-case files.  I'll check them right now

Thank you for the test-case, i was able to reproduce the problem

Is there an easy fix for this? I am already testing PostgreSQL and it does what I want to do. However I would still like to use MySQL if the bug was fixed.

Jorge: You need to verify that this behavior is actually incorrect.

From what I can see, this appears to just be a normal deadlock situation, which should be resolve itself when the disrupted connection times out (or is killed).

After that, it needs to be determined if the problem is specific to ADO/ODBC and/or Windows.

Hi,

Can you please send us the output of show innodb status exactly when this error happens ?  Basically I want to confirm if a deadlock exists, and what its cause is.

Thanks

As requested from sample program...

mysql> show status;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_clients          | 0     |
| Aborted_connects         | 2     |
| Binlog_cache_disk_use    | 0     |
| Binlog_cache_use         | 0     |
| Bytes_received           | 2674  |
| Bytes_sent               | 2736  |
| Com_admin_commands       | 0     |
| Com_alter_db             | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
| Com_backup_table         | 0     |
| Com_begin                | 4     |
| Com_change_db            | 1     |
| Com_change_master        | 0     |
| Com_check                | 0     |
| Com_checksum             | 0     |
| Com_commit               | 1     |
| Com_create_db            | 0     |
| Com_create_function      | 0     |
| Com_create_index         | 0     |
| Com_create_table         | 0     |
| Com_dealloc_sql          | 0     |
| Com_delete               | 7     |
| Com_delete_multi         | 0     |
| Com_do                   | 0     |
| Com_drop_db              | 0     |
| Com_drop_function        | 0     |
| Com_drop_index           | 0     |
| Com_drop_table           | 0     |
| Com_drop_user            | 0     |
| Com_execute_sql          | 0     |
| Com_flush                | 1     |
| Com_grant                | 0     |
| Com_ha_close             | 0     |
| Com_ha_open              | 0     |
| Com_ha_read              | 0     |
| Com_help                 | 0     |
| Com_insert               | 8     |
| Com_insert_select        | 1     |
| Com_kill                 | 0     |
| Com_load                 | 0     |
| Com_load_master_data     | 0     |
| Com_load_master_table    | 0     |
| Com_lock_tables          | 0     |
| Com_optimize             | 0     |
| Com_preload_keys         | 0     |
| Com_prepare_sql          | 0     |
| Com_purge                | 0     |
| Com_purge_before_date    | 0     |
| Com_rename_table         | 0     |
| Com_repair               | 0     |
| Com_replace              | 0     |
| Com_replace_select       | 0     |
| Com_reset                | 0     |
| Com_restore_table        | 0     |
| Com_revoke               | 0     |
| Com_revoke_all           | 0     |
| Com_rollback             | 0     |
| Com_savepoint            | 0     |
| Com_select               | 1     |
| Com_set_option           | 9     |
| Com_show_binlog_events   | 0     |
| Com_show_binlogs         | 0     |
| Com_show_charsets        | 0     |
| Com_show_collations      | 0     |
| Com_show_column_types    | 0     |
| Com_show_create_db       | 0     |
| Com_show_create_table    | 0     |
| Com_show_databases       | 1     |
| Com_show_errors          | 0     |
| Com_show_fields          | 1     |
| Com_show_grants          | 0     |
| Com_show_innodb_status   | 0     |
| Com_show_keys            | 1     |
| Com_show_logs            | 0     |
| Com_show_master_status   | 0     |
| Com_show_new_master      | 0     |
| Com_show_open_tables     | 0     |
| Com_show_privileges      | 0     |
| Com_show_processlist     | 0     |
| Com_show_slave_hosts     | 0     |
| Com_show_slave_status    | 0     |
| Com_show_status          | 1     |
| Com_show_storage_engines | 0     |
| Com_show_tables          | 1     |
| Com_show_variables       | 0     |
| Com_show_warnings        | 0     |
| Com_slave_start          | 0     |
| Com_slave_stop           | 0     |
| Com_truncate             | 0     |
| Com_unlock_tables        | 0     |
| Com_update               | 0     |
| Com_update_multi         | 0     |
| Connections              | 10    |
| Created_tmp_disk_tables  | 0     |
| Created_tmp_files        | 4     |
| Created_tmp_tables       | 0     |
| Delayed_errors           | 0     |
| Delayed_insert_threads   | 0     |
| Delayed_writes           | 0     |
| Flush_commands           | 1     |
| Handler_commit           | 2     |
| Handler_delete           | 2     |
| Handler_discover         | 0     |
| Handler_read_first       | 7     |
| Handler_read_key         | 6     |
| Handler_read_next        | 1     |
| Handler_read_prev        | 0     |
| Handler_read_rnd         | 0     |
| Handler_read_rnd_next    | 54    |
| Handler_rollback         | 5     |
| Handler_update           | 0     |
| Handler_write            | 9     |
| Key_blocks_not_flushed   | 0     |
| Key_blocks_unused        | 7172  |
| Key_blocks_used          | 5     |
| Key_read_requests        | 11    |
| Key_reads                | 5     |
| Key_write_requests       | 8     |
| Key_writes               | 6     |
| Max_used_connections     | 4     |
| Not_flushed_delayed_rows | 0     |
| Open_files               | 2     |
| Open_streams             | 0     |
| Open_tables              | 2     |
| Opened_tables            | 18    |
| Qcache_free_blocks       | 0     |
| Qcache_free_memory       | 0     |
| Qcache_hits              | 0     |
| Qcache_inserts           | 0     |
| Qcache_lowmem_prunes     | 0     |
| Qcache_not_cached        | 0     |
| Qcache_queries_in_cache  | 0     |
| Qcache_total_blocks      | 0     |
| Questions                | 41    |
| Rpl_status               | NULL  |
| Select_full_join         | 0     |
| Select_full_range_join   | 0     |
| Select_range             | 0     |
| Select_range_check       | 0     |
| Select_scan              | 0     |
| Slave_open_temp_tables   | 0     |
| Slave_running            | OFF   |
| Slow_launch_threads      | 0     |
| Slow_queries             | 2     |
| Sort_merge_passes        | 0     |
| Sort_range               | 0     |
| Sort_rows                | 0     |
| Sort_scan                | 0     |
| Table_locks_immediate    | 34    |
| Table_locks_waited       | 0     |
| Threads_cached           | 0     |
| Threads_connected        | 4     |
| Threads_created          | 4     |
| Threads_running          | 1     |
| Uptime                   | 596   |
+--------------------------+-------+
156 rows in set (0.00 sec)

Hi!

Thanks  for sending us status  info.

But my   colleague did not ask for  general status, he asked for the output from SHOW  INNODB STATUS.

Also, deadlocks are to be expected  in transactional engines, so your bug report truly looks like  support issue.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".