0

This is how the error happened - I was using MySQL shell to import a 16G .sql file that contains multiple tables using the source command and optimizations (as the first solution in this question, with maximum values indicated in MySQL documentation). The import seem to have gone on for a while, and then I got ERROR 2006: Server has gone away when I checked it after three days. It seems to have happened in the middle of inserting rows into a table that already has hundreds and thousands of rows inserted.

I restarted the server, want to pick up where the import has left off so I don't have to duplicate all the work and possibly run into the same problem, and got stuck trying the following options -

  1. Finding where the problem is through logs. Since I haven't initiated error logs (reference here), I looked for binary logs. The SHOW BINARY LOGS command shows a list logs as the documentation says it would, but I couldn't view the logs themselves, so I can't figure out where it's gone wrong.

  2. Insert-ignore - I have tried the first and second solutions to this question import mysql data interrupted, how to resume? but kept getting syntax error when I tried to reference the full path to the .sql file, which is on an external drive.

  3. Look for an insert-ignore option that works with the source command, with which I've imported several smaller large .sql files, but so far haven't found it.

  4. Run snippets of the .sql file using MySQL WorkBench - it was unresponsive for a few hours and eventually loaded a blank window. I also tried opening the .sql file using NotePad, ATOM and Sublime, they all haven't loaded.

My last resort would be to break up the .sql file into snippets, and then copy and paste potentially hundreds of statements into MySQL shell, but I'm hoping that I don't have to go there.

Any suggestions？I'm using MySQL community version 8.0...and also I am a SQL newbie so might be missing something really obvious. Thanks in advance！

EDIT - On 2）- Figured out how the syntax works with full path in INSERT-IGNORE after rereading documentation of the query. But then the LOAD DATA command has directory and also unique key constraints which I might not be able to satisfy.

lyra
  • 1
  • 1
  • *"kept getting syntax error when I tried to reference the full path to the .sql file, which is on an external drive."*: try to move the file to your main drive and retry the given solutions ? Migrating data is not an easy task. Have you considered using a dedicated tool ? – AymDev Oct 13 '21 at 14:20
  • @AymDev I haven't moved the file to main drive b/c I'm concerned that it will fill up (I'm in the process of importing many databases), but might try that too. I'm also wondering why referencing full path wouldn't work. Point taken about migrating data as now I'm finding out...what kind of dedicated tool do you have in mind? – lyra Oct 13 '21 at 14:36
  • Might need to increase the max packet size, I know you mentioned max value in the question but not sure if this is referring to max packet size or something else I don't know about, but in the past I've seen this and increase the max packet size has fixed the issue – Boardy Oct 13 '21 at 14:53
  • @lyra If you know PHP I built a tool for data migration called [Fregata](https://github.com/AymDev/Fregata) but it might be overkill for your needs. I should provide some helpers to make simple data migrations easier to configure in a next minor version. – AymDev Oct 13 '21 at 14:55
  • @Boardy I have set the max_allowed_packet to the maximum on the outset...is that what you're referring to? – lyra Oct 13 '21 at 19:12
  • @AymDev Thanks! I don't know PHP but wonder if learning it makes navigating MySQL easier. – lyra Oct 13 '21 at 19:13
  • @lyra that is what I was referring to yes, if you are still having the issue though I don' know what else to suggest I'm afraid – Boardy Oct 14 '21 at 08:15
  • @Boardy got it, thanks! – lyra Oct 14 '21 at 15:53
  • This question looks like a better fit for [dba.se] than here at StackOverflow. I'd recommend deleting this question here and re-asking it there. – Stephen Ostermiller Oct 15 '21 at 09:17

0 Answers0