MySQL Server has Gone a way; No ticket created but e-mail is sent

RT Users
1

Hi Everyone,

A few weeks ago I sent in a message similar to the problem I am about to describe. So, I hope someone can tell me a work around, as this problem is very annoying.

A user uses the web interface to enter a ticket, as far as the user is concerned the ticket was entered properly and is happily on its way of being taken care of. It looks like the ticket made it because e-mails are sent to the proper parties and everything is all right with the world. Like the clichés?

Anyway, behind the scenes the ticket never makes it in the database. The only record of this happening is that in /var/log/.messages (we are using linux), several errors messages are generated starting with “MySQL server has gone away”. So, all the inserts and updates going to the database are dropped and no ticket is anywhere to be found.

The worse part about this error, it only happens less than 1% of the time.(35 times out of 3500 tickets created).

I have check the database, web server and other system logs and there is nothing indicating a problem. I also checked the database structure and tables in MySQL.

I am running RT 3.4.4 with RTx::SearchBuilder 1.39, mysql 4.1.15, RTFM 2.1.40, RTx::AssetTracker 1.2.2, RTx::RightsMatrix 0.02.05, RTx::Statistics 1.18, Apache 2.0.55, mod_perl2, perl 5.8.7. The only thing to add is that I upgraded from RT V2 back in December.

The bottom line, short of trying to figure out RTx::SearchBuilder, I have trie deverything I can think of.

Anyway, ideas would be welcome, as well as solutions to this rather nagging problem.

Take care!

Nick

Nick Metrowsky

Consulting System Administrator

303-684-4785 Office

303-684-4100 Fax

nmetrowsky@digitalglobe.com mailto:nmetrowsky@digitalglobe.com

DigitalGlobe ®, An Imaging and Information Company

http://www.digitalglobe.com http://www.digitalglobe.com

2

Nick Metrowsky wrote:

Hi Everyone,

A few weeks ago I sent in a message similar to the problem I am about to
describe. So, I hope someone can tell me a work around, as this problem
is very annoying.

A user uses the web interface to enter a ticket, as far as the user is
concerned the ticket was entered properly and is happily on its way of
being taken care of. It looks like the ticket made it because e-mails
are sent to the proper parties and everything is all right with the
world. Like the clichés?

Anyway, behind the scenes the ticket never makes it in the database. The
only record of this happening is that in /var/log/.messages (we are
using linux), several errors messages are generated starting with “MySQL
server has gone away”. So, all the inserts and updates going to the
database are dropped and no ticket is anywhere to be found.

The worse part about this error, it only happens less than 1% of the
time.(35 times out of 3500 tickets created).

I have check the database, web server and other system logs and there is
nothing indicating a problem. I also checked the database structure and
tables in MySQL.

I am running RT 3.4.4 with RTx::SearchBuilder 1.39, mysql 4.1.15, RTFM
2.1.40, RTx::AssetTracker 1.2.2, RTx::RightsMatrix 0.02.05,
RTx::Statistics 1.18, Apache 2.0.55, mod_perl2, perl 5.8.7. The only
thing to add is that I upgraded from RT V2 back in December.

The bottom line, short of trying to figure out RTx::SearchBuilder, I
have trie deverything I can think of.

i would not consider this to ba a rt issue, it looks like your mysql is
dropping the connections

can you post anything interesting you have in /etc/my.cnf or the output
of mysqladmin variables

thanx

3

Hi Chaim,

Thank you for writing. Please find below the output of my MySQL variables from mysqladmin.

Thank you for your help in advance.

Take care!

Nick

| Variable_name | Value |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/var/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:“”&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | ON |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| pid_file | /usr/local/mysql/var/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 258048 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 524280 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | GMT |
| table_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.15-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |

Nick Metrowsky
Consulting System Administrator
303-684-4785 Office
303-684-4100 Fax
nmetrowsky@digitalglobe.com
DigitalGlobe ®, An Imaging and Information Company
http://www.digitalglobe.comFrom: Chaim Rieger [mailto:chaim.rieger@gmail.com]
Sent: Friday, March 03, 2006 2:32 PM
To: Nick Metrowsky
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] MySQL Server has Gone a way; No ticket created but e-mail is sent

Nick Metrowsky wrote:

Hi Everyone,

A few weeks ago I sent in a message similar to the problem I am about to
describe. So, I hope someone can tell me a work around, as this problem
is very annoying.

A user uses the web interface to enter a ticket, as far as the user is
concerned the ticket was entered properly and is happily on its way of
being taken care of. It looks like the ticket made it because e-mails
are sent to the proper parties and everything is all right with the
world. Like the clichés?

Anyway, behind the scenes the ticket never makes it in the database. The
only record of this happening is that in /var/log/.messages (we are
using linux), several errors messages are generated starting with “MySQL
server has gone away”. So, all the inserts and updates going to the
database are dropped and no ticket is anywhere to be found.

The worse part about this error, it only happens less than 1% of the
time.(35 times out of 3500 tickets created).

I have check the database, web server and other system logs and there is
nothing indicating a problem. I also checked the database structure and
tables in MySQL.

I am running RT 3.4.4 with RTx::SearchBuilder 1.39, mysql 4.1.15, RTFM
2.1.40, RTx::AssetTracker 1.2.2, RTx::RightsMatrix 0.02.05,
RTx::Statistics 1.18, Apache 2.0.55, mod_perl2, perl 5.8.7. The only
thing to add is that I upgraded from RT V2 back in December.

The bottom line, short of trying to figure out RTx::SearchBuilder, I
have trie deverything I can think of.

i would not consider this to ba a rt issue, it looks like your mysql is
dropping the connections

can you post anything interesting you have in /etc/my.cnf or the output
of mysqladmin variables

thanx

4

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf
Of Nick Metrowsky
Sent: Friday, March 03, 2006 1:42 PM
To: Chaim Rieger
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] MySQL Server has Gone a way;No ticket
created but e-mail is sent

Hi Chaim,

Thank you for writing. Please find below the output of my
MySQL variables from mysqladmin.

Thank you for your help in advance.

Take care!

Nick

±--------------------------------±--------------------------
| max_allowed_packet | 1047552

This is the variable of interest. Unfortunately, MySQL doesn’t tell you
why it dropped the connection, it just does it. In some testing, I
found that if you are trying to do too large of an extended insert, or a
single insert is too large, your insert will fail. This is normally
caused by large attachments, such as those over 20MB. In your case,
anything over 1MB will fail, because that’s what max_allowed_packet is
set at. I’d bet that the 1% of the time this happens is when someone
attaches a large file. Increase this variable and modify mysql so that
this is something that is set whenever the daemon starts up. To set
this in the current running daemon:

mysql> set global max_allowed_packet=1677216;

5

Hi Eric,

Thank you for writing. This seems to be logical that this variable needs
to be increased. I just did so. However, I have seen this error occur
when a simple text message was created via the web interface, e. g.
“This is a test”.

Now, it could be possible that a large packet could be being sent when
the web is involved.

Take care!

Nick

Nick Metrowsky
Consulting System Administrator
303-684-4785 Office
303-684-4100 Fax
nmetrowsky@digitalglobe.com
DigitalGlobe (r), An Imaging and Information Company
http://www.digitalglobe.comFrom: Schultz, Eric [mailto:ESchultz@corp.untd.com]
Sent: Friday, March 03, 2006 3:15 PM
To: Nick Metrowsky; Chaim Rieger
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] MySQL Server has Gone a way;No ticket created
but e-mail is sent

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf
Of Nick Metrowsky
Sent: Friday, March 03, 2006 1:42 PM
To: Chaim Rieger
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] MySQL Server has Gone a way;No ticket
created but e-mail is sent

Hi Chaim,

Thank you for writing. Please find below the output of my
MySQL variables from mysqladmin.

Thank you for your help in advance.

Take care!

Nick

±--------------------------------±--------------------------
| max_allowed_packet | 1047552

This is the variable of interest. Unfortunately, MySQL doesn’t tell you
why it dropped the connection, it just does it. In some testing, I
found that if you are trying to do too large of an extended insert, or a
single insert is too large, your insert will fail. This is normally
caused by large attachments, such as those over 20MB. In your case,
anything over 1MB will fail, because that’s what max_allowed_packet is
set at. I’d bet that the 1% of the time this happens is when someone
attaches a large file. Increase this variable and modify mysql so that
this is something that is set whenever the daemon starts up. To set
this in the current running daemon:

mysql> set global max_allowed_packet=1677216;

6

Hi Eric,

Thank you for writing. This seems to be logical that this variable needs
to be increased. I just did so. However, I have seen this error occur
when a simple text message was created via the web interface, e. g.
“This is a test”.

Now, it could be possible that a large packet could be being sent when
the web is involved.

Take care!

Nick

I doubt it. With no attachments the data should be easily
less thatn 1 meg.

I would lower that variable back to the previous setting
and see what happens wehn you send a larger attachment.

-Todd

7

Hi Todd,

Thank you for writing. Having the maximum packet size set to 16MB is
useful for when one has to restore the database, in the event that needs
to be done.

I was meaning to increase it on the production system and completely
forgot to do so.

I am curious, could a bad e-mail address to a watcher cause this type of
behavior? I discovered that one of watchers for the queue having this
problem was incorrect. Ah, the mysteries of Mason.

Take care!

Nick

Nick Metrowsky
Consulting System Administrator
303-684-4785 Office
303-684-4100 Fax
nmetrowsky@digitalglobe.com
DigitalGlobe (r), An Imaging and Information Company
http://www.digitalglobe.comFrom: Todd Chapman [mailto:todd@chaka.net]
Sent: Friday, March 03, 2006 3:26 PM
To: Nick Metrowsky
Cc: Schultz, Eric; Chaim Rieger; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] MySQL Server has Gone a way; No ticket created
but e-mail is sent

Hi Eric,

Thank you for writing. This seems to be logical that this variable
needs
to be increased. I just did so. However, I have seen this error occur
when a simple text message was created via the web interface, e. g.
“This is a test”.

Now, it could be possible that a large packet could be being sent when
the web is involved.

Take care!

Nick

I doubt it. With no attachments the data should be easily
less thatn 1 meg.

I would lower that variable back to the previous setting
and see what happens wehn you send a larger attachment.

-Todd

8

-----Original Message-----
From: Todd Chapman [mailto:todd@chaka.net]
Sent: Friday, March 03, 2006 2:26 PM
To: Nick Metrowsky
Cc: Schultz, Eric; Chaim Rieger; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] MySQL Server has Gone a way; No
ticket created but e-mail is sent

Hi Eric,

Thank you for writing. This seems to be logical that this
variable needs
to be increased. I just did so. However, I have seen this
error occur
when a simple text message was created via the web interface, e. g.
“This is a test”.

Now, it could be possible that a large packet could be
being sent when
the web is involved.

Take care!

Nick

I doubt it. With no attachments the data should be easily
less thatn 1 meg.

I would lower that variable back to the previous setting
and see what happens wehn you send a larger attachment.

-Todd

If it’s happening for something so small, then Todd is right, that won’t
be the problem. There are various settings to mess with on your MySQL
server, but I wonder if it isn’t just a network problem between the
database machine and the machine RT is hosted on? Maybe have mtr
running for awhile and see if any packets are getting dropped. Are you
able to put the two on the same machine?

This may also shed some light:
http://dev.mysql.com/doc/refman/4.1/en/gone-away.html

Eric

9

Hi Todd,

Thank you for writing. Having the maximum packet size set to 16MB is
useful for when one has to restore the database, in the event that needs
to be done.

I was meaning to increase it on the production system and completely
forgot to do so.

I am curious, could a bad e-mail address to a watcher cause this type of
behavior? I discovered that one of watchers for the queue having this
problem was incorrect. Ah, the mysteries of Mason.

I doubt it.

10

Hi Eric & Todd,

I believe both of you are right. According to mysqladmin, the
wait_timeout variable is set to 28800 (I assume this is seconds) or 480
minutes. The MySQL server has gone away message is almost immediate. By
the way, my connect timeout is set to 5, should I increase that to lets
say 60?

Take care!

Nick

Nick Metrowsky
Consulting System Administrator
303-684-4785 Office
303-684-4100 Fax
nmetrowsky@digitalglobe.com
DigitalGlobe (r), An Imaging and Information Company
http://www.digitalglobe.comFrom: Schultz, Eric [mailto:ESchultz@corp.untd.com]
Sent: Friday, March 03, 2006 3:34 PM
To: Todd Chapman; Nick Metrowsky
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] MySQL Server has Gone a way; No ticket created
but e-mail is sent

-----Original Message-----
From: Todd Chapman [mailto:todd@chaka.net]
Sent: Friday, March 03, 2006 2:26 PM
To: Nick Metrowsky
Cc: Schultz, Eric; Chaim Rieger; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] MySQL Server has Gone a way; No
ticket created but e-mail is sent

Hi Eric,

Thank you for writing. This seems to be logical that this
variable needs
to be increased. I just did so. However, I have seen this
error occur
when a simple text message was created via the web interface, e. g.
“This is a test”.

Now, it could be possible that a large packet could be
being sent when
the web is involved.

Take care!

Nick

I doubt it. With no attachments the data should be easily
less thatn 1 meg.

I would lower that variable back to the previous setting
and see what happens wehn you send a larger attachment.

-Todd

If it’s happening for something so small, then Todd is right, that won’t
be the problem. There are various settings to mess with on your MySQL
server, but I wonder if it isn’t just a network problem between the
database machine and the machine RT is hosted on? Maybe have mtr
running for awhile and see if any packets are getting dropped. Are you
able to put the two on the same machine?

This may also shed some light:
http://dev.mysql.com/doc/refman/4.1/en/gone-away.html

Eric

11

I saw such problem with mod_perl-2.0.x, then I rebuilt perl, libperl,
mod_perl, DBI and DBD::mysql from sources and problem’s gone away, I
don’t know exactly reason of the error.

2ALL: max_allowed_packet don’t drop connections, but generate error
message, error “mysql has gone away” means that your server was
killed/seg faulted or something like that.On 3/4/06, Nick Metrowsky nmetrowsky@digitalglobe.com wrote:

Hi Eric,

Thank you for writing. This seems to be logical that this variable needs
to be increased. I just did so. However, I have seen this error occur
when a simple text message was created via the web interface, e. g.
“This is a test”.

Now, it could be possible that a large packet could be being sent when
the web is involved.

Take care!

Nick

Nick Metrowsky
Consulting System Administrator
303-684-4785 Office
303-684-4100 Fax
nmetrowsky@digitalglobe.com
DigitalGlobe (r), An Imaging and Information Company
http://www.digitalglobe.com

-----Original Message-----
From: Schultz, Eric [mailto:ESchultz@corp.untd.com]
Sent: Friday, March 03, 2006 3:15 PM
To: Nick Metrowsky; Chaim Rieger
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] MySQL Server has Gone a way;No ticket created
but e-mail is sent

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf
Of Nick Metrowsky
Sent: Friday, March 03, 2006 1:42 PM
To: Chaim Rieger
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] MySQL Server has Gone a way;No ticket
created but e-mail is sent

Hi Chaim,

Thank you for writing. Please find below the output of my
MySQL variables from mysqladmin.

Thank you for your help in advance.

Take care!

Nick

±--------------------------------±--------------------------
| max_allowed_packet | 1047552

This is the variable of interest. Unfortunately, MySQL doesn’t tell you
why it dropped the connection, it just does it. In some testing, I
found that if you are trying to do too large of an extended insert, or a
single insert is too large, your insert will fail. This is normally
caused by large attachments, such as those over 20MB. In your case,
anything over 1MB will fail, because that’s what max_allowed_packet is
set at. I’d bet that the 1% of the time this happens is when someone
attaches a large file. Increase this variable and modify mysql so that
this is something that is set whenever the daemon starts up. To set
this in the current running daemon:

mysql> set global max_allowed_packet=1677216;

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

We’re hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.htm

Best regards, Ruslan.