SMF Development > Bug Reports

Syntax errors from SMF database backup.

<< < (7/9) > >>

GravuTrad:
Drop phpmyadmin, use mysqldumper....

Antechinus:

--- Quote from: emanuele on May 23, 2012, 07:06:07 PM ---Just tested this on my localhost and it works properly (it also allowed me to fix a couple of bugs in the new export code :P): exported with SMF and imported with phpMyAdmin without any error.
--- End quote ---

How did you test the results? I know it will import without throwing any errors in phpMyAdmin. That's part of the trap. The problem is/was that although the actual import process would complete without any apparent problems, when viewing the post content of the restored db, as actually displayed on the forum, there would be large and random chunks of data missing, even though the same data was present in the db if you took the time to look for it.

emanuele:
Ohhh...but that's could really be another problem then!
That smells a *lot* of encoding issue...

Antechinus:
I'd try a quick export from phpMyAdmin. One table should do the trick, as long as it has apostrophes. The calendar holidays table is a good test for this as it's short and near the top. If it exports from phpMyAdmin with apostrophes escaped as two singles ( '' rather than \' ) then I'd say the problem is still there.

emanuele:

--- Quote from: MrPhil on May 23, 2012, 08:51:56 PM ---Is everyone satisfied that timeouts won't happen on any host? Not just the one or two you tested your changes on. It's now impossible to get a timeout on either backup or restore on any "reasonable" host? Even if the database is absolutely humungous? Even if a standard utility like phpMyAdmin is used instead of SMF's backup/restore?
--- End quote ---
I sure SMF doesn't work on each and every host out there. ;)
SMF cannot restore backups and that's a piece of code I'm not interested in, so the problem in that sense doesn't exist.
That said, of course, I cannot guarantee anything, but at least it wont timeout as it does now.


--- Quote from: MrPhil on May 23, 2012, 08:51:56 PM ---
--- Quote ---4) you will have to explain to the users that they have to strictly follow the order to re-upload the files, otherwise the consistency would go to...

--- End quote ---
The consistency would go to vB? :) Put all the table DROPs and CREATEs in the first backup file, and then after that it doesn't matter what order they're done in. With numbered backup files, you could automatically import them in sequence in a restore utility. Using phpMyAdmin, the user would have to watch the sequence numbers to make sure they do "1" first. After that, it doesn't matter.
--- End quote ---
As far as I remember SMF relies on data order for the board index and for smiley (the two I remember at the moment), so if for example the import queries of the boards table are split over two different files the result will be a messed up board index.


--- Quote from: MrPhil on May 23, 2012, 08:51:56 PM ---I'm not sure we're talking about the same thing when we say "split the backup". What I'm meaning is "back up the database into multiple, relatively small .sql files so that neither the backup nor restore (nor the file transfers) time out. This may involve playing some games so that a fresh process is started for the next .sql file -- maybe holding the last .sql file number done, and (for backup) its table and its ending record number in a scratch file or something. That might be necessary to avoid a timeout. Start with some large number of records to process per file, and if the user experiences timeouts they can adjust this record count down to where the backup and restore processes manage to run OK. Feasible?
--- End quote ---
We are talking about exactly the same thing...and I don't like it so much.
It's a hack and rather than this I'd wipe out the backup (and wipe out in my dictionary means remove it completely and not provide any alternative at all, except for: "use phpMyAdmin").


--- Quote from: MrPhil on May 23, 2012, 08:51:56 PM ---
--- Quote ---Convert from a backup is pretty difficult/annoying.
Additionally many conversions don't make much sense: basically any conversion to SQLite is pretty much useless, and I don't think there are many people that would switch from MySQL to Postgres or vice versa...
Also SQLite to Postgres is unlikely...I'd guess.

--- End quote ---
As mentioned before, if there's a universal format we should use that. Otherwise, foreign format import/export for SMF's utility should be supported, and we can think about converting between .sql file formats if necessary. After all, sooner or later someone is going to decide they want to move to another host that doesn't support, say, MySQL, and all they have is a MySQL backup. SMF looks much better if we can say, "No Problem!"

--- End quote ---
As said, the only one that uses a different format AFAIK is MySQL.
The other DBMS should use the same standard (but honestly I never tried to import a SQLite into a postgre db...will try sooner or later! :D).


--- Quote from: Arantor on May 23, 2012, 10:05:53 PM ---The bigger picture that some seem to be unwilling to grasp is that doing it in SMF is a bad idea, and even phpMyAdmin will choke on larger stuff, but if you're hitting that limit, 1) you're on a VPS or a REALLY bad host and 2) you should probably take the time, if you haven't already, to learn how to do it properly or get someone competent to do it.
--- End quote ---
* emanuele dreams a world where people read instructions manuals...unfortunately I'm the first that doesn't read any manual... lol


--- Quote from: Arantor on May 23, 2012, 10:05:53 PM ---Take me for example. My VPS does not have phpMyAdmin installed (through my choice), and I run quite a few forums on that server. I don't use SMF's admin backup, I have a script that does it for me, especially as in two cases even phpMyAdmin is going to choke, and no amount of SMF magic would fix that.
--- End quote ---
I started my web experience with wordpress and so I'm used to do backups from phpMyAdmin (the only tool available at my host), so I didn't even know there was a tool to backup the database in SMF until recently... lol


--- Quote from: Arantor on May 23, 2012, 10:05:53 PM ---That's the point: the current backup as in SMF 2.0 does not use a file at all. I haven't looked at how 2.1 does it but I'm assuming an exclusively-locked file in the temporary area.
--- End quote ---
* emanuele copied phpMyAdmin...That's all.
Dropped the output buffer, used (also) set_time_limit to gain time (again during the process), etc.
Of course it's not bullet proof, that why I added few checks on the admin page to explain the admin what's the best approach to take (including disable the button for large databases).


--- Quote from: Arantor on May 23, 2012, 10:05:53 PM ---The bottom line is that, for the most part, dealing with this is a rabbit hole, and I would argue there are many better things to be doing with the development time than fighting a losing battle, wherein there are actually more scenarios that the backup will NOT be suitable for than the number that it will, as compared to other solutions.

--- End quote ---
That was somehow funny.
I usually don't touch things I don't like... :P

Navigation

[0] Message Index

[#] Next page

[*] Previous page