One of the silent killers of MySQL Connections is the MySQL Packet. Even the I/O Thread of MySQL Replication can be victimized by this.
According to the MySQL Documentation
You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.
At the very least, you must make sure the packet sizes for both the machine you mysqldump'd from and the machine you are loading are identical.
There may be two(2) approaches you can take:
APPROACH #1 : Perform the mysqldump using --skip-extended-insert
This will make sure the MySQL Packet is not inundated with multiple BLOBs, TEXT fields. That way SQL INSERTs are performed one at a time. The major drawbacks are
- the mysqldump is much larger
- reloading such a dump takes much longer.
APPROACH #2 : Increase max_allowed_packet
This may be the preferred approach because implementing this is just a mysql restart away. Understanding what the MySQL Packet is may clarify this.
According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:
MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory---a cap
on the size of the packet, which this
option accomplishes.
The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().
This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.
Given this explanation, making bulk INSERTs will load/unload a MySQL Packet rather quickly. This is especially true when max_allowed_packet is too small for the given load of data coming at it.
CONCLUSION
In most installs of MySQL, I usually set this to 256M or 512M. You should experiement with larger values when data loads produces "MySQL has gone away" errors.
WOW that's a very old version of MySQL.
Here is mysql.user from version 4.1.20:
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) | | PRI | | |
| User | varchar(16) | | PRI | | |
| Password | varchar(41) | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Show_db_priv | enum('N','Y') | | | N | |
| Super_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
| Execute_priv | enum('N','Y') | | | N | |
| Repl_slave_priv | enum('N','Y') | | | N | |
| Repl_client_priv | enum('N','Y') | | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | | 0 | |
| max_updates | int(11) unsigned | | | 0 | |
| max_connections | int(11) unsigned | | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)
Since we are talking Windows, there should be a service called MySQL or some other name pointing to C:\Program Files\MySQL. If that's not the folder, search the entire disk for my.ini. Once you found my.ini, here is a sure fire way to insert a new user called 'oracleclient':
Step 01) Shutdown Application
Step 02) net stop mysql
Step 03) Add skip-grant-tables to my.ini
Step 04) net start mysql
Step 05) run 'mysql' at the DOS prompt (no password needed)
Step 06) From mysql prompt, run this INSERT statement
INSERT INTO mysql.user SET
Host='IP of Oracle Server',
User='oracleclient',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';
Step 07) exit mysql
Step 08) net stop mysql
Step 09) net start mysql (close your eyes and hit enter)
Step 10) See if everything works !!!
You should be able to connect from the oracle server.
BUT WAIT !!!
What if there is no my.ini and the application has the setting only ???
Here is something a little more daring:
Step 01) Install the same version (MySQL 4.1.18) onto another PC (Server2)
Using the wizard, this should place the MySQL binaries: C:\Program Files\MySQL\MySQL 4.1. This would be considered the basedir. The subfolder data\mysql would be the home of the mysql schema.
Step 02) Create my.ini in basedir of Server2
Step 03) Copy user.frm, user.MYI, user.MYD from the mysql schema of Server1 into the same place in Server2
Step 04) Perform Steps 3-8 from the first plan
Step 05) Make sure you have a backup copy of the mysql schema of Server1
Step 06) Copy user.frm, user.MYI, user.MYD from the mysql schema of Server2 into the same place in Server1
Step 07) net start mysql on Server1 and the application (close your eyes and hit enter)
Step 08) See if everything works !!!
Give it a Try !!!
Best Answer
Added
skip-character-set-client-handshaketo the [mysqld] group of the my.cnf file.My-small.cnf as supplied in "support files" does not contain this, subsequently my connection was overriding the values, causing me to think the file was not being read... newbie mistake.
Solution mentioned on the MySQL manual here.