News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Setting AutoIncrement on tables after importing

Started by 6Dasher, April 23, 2018, 11:33:10 PM

Previous topic - Next topic

6Dasher

So I just moved my site to a new host. I did a fresh install of SMF with new versions of all the mods, transfered all files (even Filezilla in binary mode for all attachments etc) but one of the last issues I have is that the auto-increment flag on the tables was not set for some reason. As I have over 250 tables, is there a method to set all the required fields to AI via some SQL script someone has? Kind of not looking forward to the RSI :)
SMF user since 2005.

vii

How did you export the tables when you saved the old site's db data? A default export using phpMyAdmin on my machine includes the AUTO_INCREMENT value at the end of the CREATE TABLE query. Is it present in yours?

6Dasher

The backup is over 800mb, so when I try to open it in an editor it just hangs. I made a simple export and imported it using BigDump.
SMF user since 2005.

Kindred

well, something didn;t import correctly...


the command is ALTER TABLE tbl AUTO_INCREMENT = 100;
however...   you will not, yo have to specify the restart number for the autoincrement...  which, in an existing set of tables, means that you have to determine what the existing top number is.

I'd suggest re-trying the export/import process again instead
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

6Dasher

Yeah, doing a new export/import would be the way.

I have the autoincrement value checked in the export UI, so not sure what is going wrong.
SMF user since 2005.

6Dasher

OK, I give up. I did several backups and they all give errors when I start up BigDump.

Is there seriously no guide as to what phpmyadmin settings you need for an export? The search on this site is rubbish as there are so many hits in the results that have no information at all. Am I really the only person to want to backup my database? The SMF export is bogus, you eed bigdump to import, it's all starting to sound like this is all just an afterthought as SMF?

Just pissed that something that should be so simple is taking more time and energy than installing the complete forum with mods/themes/settings.
SMF user since 2005.

Kindred

I just use the default options for export from phpmyadmin or cpanel.

for the automated database backups, again, I just use the default options.

in none of those three cases have I ever had any issues...


So, no... there has never actually been any need to make a list of settings since I have never seen anyone have this issue before.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

6Dasher

That is what you would think, but for some reason it will not work for me. Hence the hair pulling sensation :)

I got it to work somehow, don't ask me how, after about 12 attempts and a hard slam on the keyboard.
SMF user since 2005.

GigaWatt

@Kindred: The default options don't work with my host. Actually, I only had to change one setting in phpMyAdmin.

@6Dasher: Try using compression when exporting. My database is about 7 times smaller than yours, but nevertheless, my host's phpMyAdmin couldn't export the database. Try using zip compression to export the database. In my case, it works both with zip and gzip, but I'd go with zip first and if it doesn't work, try gzip. In most cases, it reduces the size 6 or 7 times (with zip).
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Advertisement: