SQL Error (2006): MySQL server has gone away. Anyone that can help?

[Tommyruin]

Get this error when loading files greater than 4MB? Also get error For example loading 'Gameobject_spawns', I get this list of errors

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/* SQL Error (1231): Variable 'time_zone' can't be set to the value of 'NULL' *//*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/* SQL Error (1231): Variable 'sql_mode' can't be set to the value of 'NULL' *//*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/* SQL Error (1231): Variable 'foreign_key_checks' can't be set to the value of 'NULL' *//*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/* SQL Error (1231): Variable 'unique_checks' can't be set to the value of 'NULL' *//*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/* SQL Error (1231): Variable 'character_set_client' can't be set to the value of 'NULL' *//*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/* SQL Error (1231): Variable 'collation_connection' can't be set to the value of 'NULL' *//* 8,411 rows affected, 0 rows found. Duration for 27 of 29 queries: 0.312 sec. */

and the second image, anyone got any tips? Working on an ArcEmu core Thanks

[stoneharry]

A bad database dump is causing the errors.

As for the error, basically your SQL file is executing stupidly big queries (INSERT INTO x VALUES (...)(...)(...)...repeat 50000x...(...)).

The server gets part way into the query then decides it's too big, taking too much resources, and stops it. This is why you get the error "mysql server has gone away". It stopped the query.

To allow your query through you need to edit your MySQL server's settings to support a larger max_server_query value (if that is the correct option from memory). 512M is big enough.

I always dump my databases as single line queries - takes up more memory and takes longer to execute, but prevents errors like this.

[Tommyruin]

I have been able to do it before with no errors though? But thanks, will try it now

[stoneharry]

I have been able to do it before with no errors though? But thanks, will try it now

Larger tables? Different server? Different database backup options? Any of these (and more) could cause the error to be triggered.

[Tommyruin]

I didn't know you could change any of that, but I could have set it up differently I suppose

[Tommyruin]

Quick question, how do I edit my SQL server setting?:3 I always screw up when it comes to SQL :L

[stoneharry]

Quick question, how do I edit my SQL server setting?:3 I always screw up when it comes to SQL :L

What MySQL server are you using?

The default binary, you go to:

\MySQL\MySQL Server 5.5\my.ini

add the line:

[mysqld]

If it doesn't already exist, then add the line underneath:

max_allowed_packet=500M

So it should look like:

[mysqld]
max_allowed_packet=500M

Then restart your MySQL server (or computer) for the changes to take effect.

Alternatively, you could try executing this query:

SET GLOBAL max_allowed_packet=1073741824;

Before you try to execute whatever else.

Source: mysql - How to change max_allowed_packet size - Stack Overflow

[Tommyruin]

Now i'm getting a different error, error 2013 and i'm still having error 2006 Why does this all happen to meeeee :<

[stoneharry]

Can't really read it, screenshot is too small.

Try google'ing - include the error code in your search.

[Tommyruin]

Its ok, I managed to sort it out by installing and reinstalling different versions of MYSQL until one worked :L the latest version did it for me thanks though! (would +rep but ive already repped you too much apparently :3 )