Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Thank you. I'll try those suggestions and return with the result.
    Currently it runs about five minutes before appearing to time out.



    --
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Increasing the timeout and packet size stops the timeout error message but throws this new error:

    ERROR 1050 (42S01) at line 3820: Table '`newdatabase`,`vb_contenttype`' already exists

    That table does not occur earlier in the dump, but nonetheless, I removed the offending table and reran the import. This time with no errors, but unfortunately the import stopped at the same progress mark as before in the midst of importing one of the larger tables.

    A 'show full processlist' query shows the process state is Sleep.
    So back to square one.



    -
  4. #18
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    There are any number of tools to check the integrity of and repair tables in a mounted database. e.g. check table, repair table, alter table & myisamchk commands to name a few.

    What tools are available to check and repair mysql dumps?

    Can something like the mysql repair table command be added to a dump, so that the command runs in advance of the table being imported into the .MYI file? Also mysql.com talks about incompatibilities introduced in MySQL 5.1.24 by the fix for Bug #27877 that corrected the utf8_general_ci and ucs2_general_ci collations. They provide a workaround for a mounted database.

    Code:
    ALTER TABLE t1
    CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;
    http://dev.mysql.com/doc/refman/5.0/en/rebuilding-tables.html

    But I don't see a way to address the bug, which appears to be relevant via a dump. Any ideas?


    -
  6. #19
  7. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    How was the integrity of the original DB?

    What did you use to create the DB dump?

    What type of data is this? Was everything in the DB properly encoded?
    Last edited by Nilpo; December 27th, 2013 at 08:47 AM.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    As mentioned earlier the mySQL database was backed up with mySQLdump v. 10.13. It's a vbulletin install as the table names suggest and it's a properly functioning vbulletin forum. As far as I know has no integrity or encoding issues.

    However that doesn't cover mySQL bugs such as the one referenced above and in any event we need to test that our restore procedures work. And it would be valuable to know what can and can't be done to fix issues when things don't go to plan.

    So back to the question of what tools or procedures exist for repairing (apparently) bad tables in a dump?


    -
  10. #21
  11. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    Originally Posted by argot
    As mentioned earlier the mySQL database was backed up with mySQLdump v. 10.13. It's a vbulletin install as the table names suggest and it's a properly functioning vbulletin forum. As far as I know has no integrity or encoding issues.

    However that doesn't cover mySQL bugs such as the one referenced above and in any event we need to test that our restore procedures work. And it would be valuable to know what can and can't be done to fix issues when things don't go to plan.

    So back to the question of what tools or procedures exist for repairing (apparently) bad tables in a dump?

    Are you getting any MySQL errors in the error log?

    -
    Did you use extended inserts? You might try the opposite. Using extended inserts should make for a smaller dump file which can help if the host is timing out or if MySQL is timing out the connection. Without inserts may work better if MySQL is choking on large queries.

    Does your host allow remote MySQL access? You could remote in and run your queries one a time. This would work best if you are not using extended inserts.

    Finally, are you restoring the sql file on the same machine it was created on? If so, are you downloading/uploading in between? If yes, make sure you are using gzip on your dump. You might also change your transfer type to binary. These can help prevent large dump files from getting corrupted during transfer.
    Last edited by Nilpo; December 27th, 2013 at 11:14 AM.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    We are not using extended inserts And as cited earlier, the restore is not to the original machine that the dump is being made from.

    This can be separated into two general paths. One is how to manage and recover from import issues. And the other is how to make better dumps. We can try extended inserts as well as hex blobs, -T options, dumping large tables independently /single transaction, compression, mysqlhotcopy or indeed using FTP rather than dumping tables per se.

    Also, Google suggests that extended inserts can cause failures as well as aid in large table dumps, so that's not a panacea and careful use and testing of the option would be prudent.

    But all of that is getting ahead of ourselves. This thread is trying to deal with the existing dump issues - I can start another thread to address best dump practices. I have two main questions at this point.. what tools exist to help with problematic dumps (and can operations such as repair table be inserted into a dump?). And how to fix the referenced character set bug which appears to apply to this dump.

    Thanks


    -
  14. #23
  15. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    Originally Posted by argot
    what tools exist to help with problematic dumps (and can operations such as repair table be inserted into a dump?)
    To my knowledge (and my best Googling efforts) there are none.
    Originally Posted by argot
    And how to fix the referenced character set bug which appears to apply to this dump.
    I'm not sure why you think you have charset issues. I don't think you do. Your last error suggests that you have are trying to import a table that already exists. This can happen if the table does in fact exist, or if it's data files have become orphaned. You have two choices: you can run "DROP TABLE IF EXISTS" on each table that throws an error to remove orphaned files or you can remove them manually from the mysql directory if you have that kind of access on your sever.
    Originally Posted by argot
    But all of that is getting ahead of ourselves. This thread is trying to deal with the existing dump issues
    If this dump file is, in fact, corrupted. You are better off putting your time into creating a good dump, rather than spending that time trying to fix a corrupted file that in all likelihood cannot be repaired anyway.

    All of that aside, I'm not convinced that the dump file is corrupted. I believe you had a server timeout that stopped your import in the middle of an operation leaving orphaned data. Now that you've increased the server limits that will allow the import to complete, you need to remove the orphaned data so that you can start a fresh import.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  16. #24
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    There isn't any basis, at least not yet to suggest that the data in this dump is corrupt beyond salvage. We won't in fact know that until we identify what the specific issues are. As detailed earlier in this thread I tried your suggestion of increasing timeouts and packet size and tried removing the problematic tables. That isn't a viable solution though as the three large tables comprise two thirds of the database information.

    This thread is solely concerned with identifying and attempting to address the import issues. Evolving a robust procedure for more reliable dumps is worthwhile but is not the subject of this thread.

    I found one commercial tool that purports to parse and repair 99% of problem table imports. However it is pricey at $500. Still looking to see what other tools and methods are available to diagnose and repair problem dumps. All of the available evidence suggests that this is not a timeout issue, the thread dies when the problem table import fails. And the force flag does not work on this particular table. And the referenced description of the mysql character set bug fits our situation to a tee, so I am not going to give up on that yet.

    It would be folly to pretend that we will never have another bad dump in the future and it is more than worth learning how to deal with problem tables. Nor is their any reason to think that developing a better dump protocol would be any faster than simply dealing with the problem data at hand. The error about an existing table only pertains to one table and is demonstrably spurious i.e. the table name does not show up earlier in the dump, nor is the table present when the database is queried with 'show table'. As to drop table if exist statements they are already present for all tables in the dump. And throwing out two thirds of the database is not a reasonable option. I appreciate your suggestions and help but you are intent to pursue better ways to make a new dump when I am here specifically looking for advice on how to deal with the existing dump.


    -
  18. #25
  19. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    You are suggesting that I'm intent on creating a better dump. I'm not. I've suggested solutions that include both ways.

    If you want to keep your existing dump file, you need to clear the tables as I mentioned before. The error you are getting suggests that there are orphaned table files. The best way to do this is if you have shell access to the mysql directory where you where you can manually remove the file or files. This will provide a clean slate to start over with your import.

    I've stated before, and will say again, that I do not believe that you actually have a bad dump file now. Your limits were set too low for the import to complete when you ran it and it got botched mid-operation. Cleaning up and starting fresh with the higher limits should prevent the problem from recurring.

    Now you are saying that (I assume removing the tables?) is not a viable solution, but I'm not following you as to why. Is your dump not a complete dump of the entire table? The best solution would actually be to dump the entire database and import it as a whole (using more than one dump file, if necessary.) This ensures a clean transfer, but you can manage a table at a time if necessary.

    It doesn't appear to be a problem, but out of curiosity are both mysql versions the same?
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  20. #26
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Clearing the three problematic tables is not viable as it would discard two thirds of the entire database rendering it useless. The problem tables can be imported independently if that is useful but the central need to repair those three tables remains.

    The previous timeout was set to the mysql default of 28,800 seconds so it was not an issue to start with. The packet size has now been increased as well with no apparent effect. And Google finds lots of people saying that they can import a 10 GB table using the default settings.

    In any event increasing both settings substantially made no apparent difference when subsequently rerunning the import both with and without the problem tables in the dump. The dump was made with the same version currently in use on the server. But the database would originally have been created with an earlier version of mysql and appears to fit all of the criteria for the character set bug.



    -
  22. #27
  23. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    Originally Posted by argot
    Clearing the three problematic tables is not viable as it would discard two thirds of the entire database rendering it useless.
    I was under the impression that was what you were importing. Are you importing into tables with existing data that is not in your dump file? I thought you had a complete dump of these tables.
    Originally Posted by argot
    The previous timeout was set to the mysql default of 28,800 seconds so it was not an issue to start with. The packet size has now been increased as well with no apparent effect. And Google finds lots of people saying that they can import a 10 GB table using the default settings.
    The packet size setting has nothing to do with the size of the entire file. That is dependent upon timeout settings. The packet size setting has to do with the size of individual queries within your dump file.
    Originally Posted by argot
    In any event increasing both settings substantially made no apparent difference when subsequently rerunning the import both with and without the problem tables in the dump.
    How not? It got past the first error and started exhibiting new symptoms, right?
    Originally Posted by argot
    But the database would originally have been created with an earlier version of mysql and appears to fit all of the criteria for the character set bug.
    The bug you mentioned applied to the use of german characters (do you even have any?) and affected data sorting (not data importing, or even exporting for that matter). Aside from that, it was patched all the way back in 2007. Can you provide a link to whatever you are reading that suggests this is your issue?
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  24. #28
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    This is really getting off topic.

    Yes all three problem tables have German characters in them. And the site default for other tables is Latin1, while the new mysqldump is defaulting to utf8. A link to a detailed examination of the mySQL character set bug can be found earlier in the thread.

    Changing the timeout /packet size settings did not achieve any further progress than importing the problems tables separately did. The only difference was the spurious error message about an duplicate /existing table.

    And no we are not importing additional data. The dump is of an operational site and the problem tables contain information that exists in the site at the time of the dump. A successful import would be equivalent to restoring the live site to a new server. The apparent problem is that the three largest tables in the dump are not importing to the restore properly. Quite likely because of various mySQL quirks or the mySQL character set bug. No doubt some creative combination of options could create a new dump that doesn't have import issues. And we will look into that independently.

    For any number of reasons, most of which I had already touched upon I want to concentrate on working with the problem dump rather than discussing good dump practices. There are many good reasons to want to repair the dump rather than simply abandon it and make a new one.

    -
  26. #29
  27. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    Originally Posted by argot
    This is really getting off topic.

    Yes all three problem tables have German characters in them. And the site default for other tables is Latin1, while the new mysqldump is defaulting to utf8. A link to a detailed examination of the mySQL character set bug can be found earlier in the thread.

    Changing the timeout /packet size settings did not achieve any further progress than importing the problems tables separately did. The only difference was the spurious error message about an duplicate /existing table.

    And no we are not importing additional data. The dump is of an operational site and the problem tables contain information that exists in the site at the time of the dump. A successful import would be equivalent to restoring the live site to a new server. The apparent problem is that the three largest tables in the dump are not importing to the restore properly. Quite likely because of various mySQL quirks or the mySQL character set bug. No doubt some creative combination of options could create a new dump that doesn't have import issues. And we will look into that independently.

    For any number of reasons, most of which I had already touched upon I want to concentrate on working with the problem dump rather than discussing good dump practices. There are many good reasons to want to repair the dump rather than simply abandon it and make a new one.

    -
    The bug that you linked to is a bug that was patched in MySQL six years ago. If you are running a MySQL version that is six years old, you have other problems.

    The error that you posted suggests that there is nothing wrong with your dump file. I don't believe there is anything wrong with the file to fix.

    I'm sorry. Perhaps someone else can help you.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  28. #30
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    All right. This thread has been getting farther off topic with every post. To start over, I am having problems importing three large tables from a mysqldump of a working vbulletin install.

    If there are any mySQL gurus here who can suggest some constructive approaches or tools towards repairing dumped tables that would be wonderful.


    -
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo