MySQL troubles

[IR15H]

To cut a long story short; my site crashed and my sql back up didn't work.

I'm running vB 3.8.3, Apache 2.2.11, PHP 5.2.9-2 and MySQl 5.1.33 (which I downloaded as a WAMP bundle) and using MySQL Admin 5.1. I remote desktop to the machine, which is running Windows Server 2003. I've disabled all plugins by putting define('DISABLE_HOOKS', true); in my config.

When I try a restore via MySQl Admin I get the following error;

Error while executing this query:INSERT INTO `customavatar` (`userid`,`filedata`,`dateline`,`filename`,`visibl e`,`filesize`,`width`,`height`,`filedata_thumb`,`w idth_thumb`,`height_thumb`) VALUES ...LOADS OF NUMBERS...
The server has returned this error message:MySQL server has gone away
MySQL Error.

I tried altering my.ini to max_allowed_packet = 256M but that didn't help. Unfortunately, this is the case for all my manual back ups going back 6 months .


I did manage to copy the info from mysql/data/schema-name and try that way, rather then an sql restore. However, I've noticed that this method gives 12 missing tables (there's a .frm file there but no corresponding .myd or .myi). These files/tables are;

• albumupdated
• discussion
• discussionread
• groupread
• noticedismiseed
• pmthrottle
• prefixpermission
• profileblockprivacy
• socialgroupcategory
• socialgroupicon
• subscribeddiscussion
• subscribegroup

Those missing tables gave me a DB error when I tried to view my forum home. I tried installed vb fresh, which works fine, and then did a sql backup of those 12 missing tables - importing them into my existing forum database. That allows me to access my forums and (seemingly) do all normal things, except those related to the missing tables.

------------

I have daily backups made via this mod, but MySQL Admin doesn't like it as it didn't originally make the back up. I don't know if they'd be any use anyway, given the above sql error.

Is there any way I can;

• try a different sql restore program that allows for ~100MB sql files?
• get to the stage where I lose all data for the above missing tables, but they don't produce a DB error?
• break an sql file down, so I can skip out the customavatar table and hopefully get the rest to work? At the moment if I check "ignore errors" no tables alphabetically after customavatar are restored
• try anything else that might work

I've been trying for days without really getting anywhere so any help would be greatly appreciated. Also, I don't really know anything about mysql, I've just been googling lots and trying different things mentioned, so if you could keep any possible advice noob-friendly that'd also help.

[jchamber2010]

Do not restore the Custom Avatars table until last, as it stores large amounts of data, and can make your queries stop working, also use a script called bigdump http://www.ozerov.de/bigdump.php to import it to prevent memory overloads when trying to import large databases, as phpMyAdmin is limited by how much memory that PHP can use. This imports only a little at time and then moves on, making memory overloads rarely happen.

Anyway I hope this helps

[DragonBlade]

Ouch, no way that you can use a Linux server, eh? If so, it'd be a lot easier to help ya. XD That's just because I'm more familiar with the commands.

Anyways, there's a few methods available to you. BigDump as described above by jchamber2010 is one I've surprisingly not heard of until now, but seems it's been around since 2003. Still, with a "0.29b" version, I think I'd stick with something a bit more well known. xP

MySQL Dumper ( http://www.mysqldumper.de/en/ ) is what I use when I don't have shell access to servers, but I do have FTP access. (When I have shell access, I just execute the command-line statements to restore a database, but I honestly have no idea how to do that in Windows. I'm sure there's got to be a way, though, and that would probably be your BEST option.)

[Paul M]

Originally Posted by IR15H

I have daily backups made via this mod, but MySQL Admin doesn't like it as it didn't originally make the back up. I don't know if they'd be any use anyway, given the above sql error.

That error has no bearing on how useful those backups are, and you can import them direct into mysql using ssh access.

As to the error, what is your wait_timeout set to in my.ini ?

[IR15H]

Originally Posted by jchamber2010

Do not restore the Custom Avatars table until last, as it stores large amounts of data, and can make your queries stop working, also use a script called bigdump http://www.ozerov.de/bigdump.php to import it to prevent memory overloads when trying to import large databases, as phpMyAdmin is limited by how much memory that PHP can use. This imports only a little at time and then moves on, making memory overloads rarely happen.

Anyway I hope this helps

Thanks for your reply. The sql file I have has all the tables in one, so I have no control over which tables it chooses to restore first (at least, I don't think). I attempted to use "Big Dump" but ran into pretty much the same problem;

Error at the line 25369: ...LOADS OF NUMBERS...equilibrium-ani2a.gif...LOADS OF NUMBERS...

Query: INSERT INTO `vb_customavatar` (`userid`,`filedata`,`dateline`,`filename`,`visibl e`,`filesize`,`width`,`height`,`filedata_thumb`,`w idth_thumb`,`height_thumb`) VALUES

...LOADS OF NUMBERS...equilibrium-ani2a.gif...LOADS OF NUMBERS...

MySQL: Got a packet bigger than 'max_allowed_packet' bytes
Stopped on error

I've set the max packet size to 10000M, (which I confirmed took effect by looking in phpMyAdmin > Variables > max allowed packet 1,048,576) so don't really see how the above error can be true as the entire sql file is less than 100MB.

-----------------------------

Originally Posted by DragonBlade

Ouch, no way that you can use a Linux server, eh? If so, it'd be a lot easier to help ya. XD That's just because I'm more familiar with the commands.

Anyways, there's a few methods available to you. BigDump as described above by jchamber2010 is one I've surprisingly not heard of until now, but seems it's been around since 2003. Still, with a "0.29b" version, I think I'd stick with something a bit more well known. xP

MySQL Dumper ( http://www.mysqldumper.de/en/ ) is what I use when I don't have shell access to servers, but I do have FTP access. (When I have shell access, I just execute the command-line statements to restore a database, but I honestly have no idea how to do that in Windows. I'm sure there's got to be a way, though, and that would probably be your BEST option.)

In theory I could install Linux, but that'd probably take me another few days . I wouldn't have the first idea how to use it either, if I did manage to install it. I also suspect that I'll just run into the same sort of problems on there also.

I tried the program you suggested, thanks. Unfortunately that gave an error of;

Error in Query:

MySQL says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES (93,'count_depth','1',1055,'yesno','',1,-1)' at line 1

I have no idea what that means, or where to check to try and fix it.

-----------------------------

Originally Posted by Paul M

That error has no bearing on how useful those backups are, and you can import them direct into mysql using ssh access.

As to the error, what is your wait_timeout set to in my.ini ?

Sorry, I didn't mean to sound like I was implying the backup weren't useful. What I was trying to say is that MySQL Admin won't run them - and that's the only way I know how to restore SQL files at present. Is SSH Linux only? Is there a similar method I could run through Command Prompt for Windows?

Time out is set to 180 seconds, the error pops up after less than 10. Due to that, I assume this isn't the issue (or else it's be at least 180 seconds before the error)?

-----------------------------

I tried downloading some sql editor thing, Apex and seeing if I could just delete the damn table from the backup. Unfortunately, I get a "System.OutOfMemoryException: Out of memory." error when trying to opn my SQL file in it. I can manage to open smaller SQL files, so I'm guessing it just crashes because it's too big a file for it to open .

[snakes1100]

Originally Posted by Paul M

That error has no bearing on how useful those backups are, and you can import them direct into mysql using ssh access.

As to the error, what is your wait_timeout set to in my.ini ?

As stated above, winblowz dont have ssh access.

You need to install the "mysql commmand line client" for windows to use mysql like cmds to import a db into a winblowz server, its basically like ssh on a linux box, but in a dos window.

then simply use your typical sql cmds to import the db.

[DragonBlade]

Originally Posted by IR15H

I tried the program you suggested, thanks. Unfortunately that gave an error of;

I have no idea what that means, or where to check to try and fix it.

Hrm, that actually sounds like a problem with your backup. Is there any way you could post the first line of your backup?

--------------- Added [DATE]1246136279[/DATE] at [TIME]1246136279[/TIME] ---------------

Oh, poo, didn't see that you had a problem opening the backup file.

Damn.

Is there any way you could upload the backup file to a Linux server? Then you can just use the "head" command to print out the first line or two.

--------------- Added [DATE]1246136781[/DATE] at [TIME]1246136781[/TIME] ---------------

Oh, poo, didn't see that you had a problem opening the backup file.

Damn.

Is there any way you could upload the backup file to a Linux server? Then you can just use the "head" command to print out the first line or two.

[IR15H]

I finally got it to work, with all the information .

I downloaded a program called PS Pad, which is a kinda like notepad++ except it can also read SQL (and doesn't suffer out of memory/file to big to open errors some other specialised sql editors do).

I ran the SQL backup through MySQL Admin, getting the usual error message;

Error while executing this query:INSERT INTO `customavatar` (`userid`,`filedata`,`dateline`,`filename`,`visibl e`,`filesize`,`width`,`height` ,`filedata_thumb`,`width_thumb`,`height_thumb`) VALUES ...LOADS OF NUMBERS...
The server has returned this error message:MySQL server has gone away
MySQL Error.

I then copied the offending string of numbers and did a search in the sql file in PS Pad. Once I found it I deleted the whole INSERT row command for that particular value. In total I had two erroneous queries, both contained within the customavatar table. After I got rid of the two dodgy quires I simply saved the sql file again and ran it through MySQL Admin's restore function, this time successfully without errors.


Thanks to everyone who tried to help .

[DragonBlade]

Heh, great that you got it fixed.

And great that I was right--it was the backup. XD