Do we have any MySql geniuses in the place ?

[hutch--]

I have got a problem with the old database from the UK forum, I have set up and have it working in the ancient forum software but there is one major problem, the post are not synchronised to the topics and none of the posts appear. I have split the database into editable chunks and there is no internal damage in the database backup file (mysqldump file).

I have tried multiple things, every repair option from mysqlcheck and it says its OK bit when I load the sql file into the database I always get an error which seems to have something to do with the MySql version. I have tried various combinations of Apache, PHP and MySql but out of those that will actiually run the forum the missing post proble is still there.

The old forum ran an old version of PHP (4) and while it will partly run with the later version there are some operations that fail by having the wrong or missing function being called.

Currently I am using Apache 2.2.21, php 5.3.10 and MySql 5.5.20 and it "sortof" runs but I know that the PHP version is wrong and probably the MySql version as well.

Any help here would be useful as I need to get it going locally on my Windows box o I( can gut the junk out of it and set it up to make a browsable html site out of it.

[dedndave]

i am about as far from a "MySql genius" as it gets - lol

but - i do know that, along the way, ms has updated the mysql stuff through hotfixes (kb123456)
and i know that older versions are definately not compatible with newer ones
if you put it on an XP box with SP2 and minimal ms updates, you might have a go

i think it gets updated along with .NET, starting with .NET 1.1
so - the level of .NET installation may be the culprit

then...
KB954920
KB955567
KB959237
KB960071

[MichaelW]

I try to steer clear of words that I can't pronounce

[hutch--]

Sad to say I have tracked it down, when I did the very large backup (665 meg) it appears to have been truncated by the account disk size limit and that chopped off the smf_topics section of the database. I could add the last few and it worked but without the topic list I am in trouble. I shoved in the topic list from an ancient backup from 2007 and got topics but sad to say they were not up to date.

I think I have just wasted 2 days on this for no result, the browsable html at the top of the forum is about the best I can get.

[jj2007]

There are converters around, see below, but if you lack the headers it will be difficult :(
What about tweaking the (old) forum search engine and let it find *everything* in some particular order? That should produce a huge html file with all the links you need...

hth, Jochen

http://www.swissql.com/products/sql-translator/sql-converter.html
How it works

The SwisSQL Console SQL query translation / conversion tool uses the underlying SwisSQL API multi-dialect SQL parser and a SQL conversion engine to convert SQL queries from one database dialect to another. The multi-dialect SQL parser builds an SQL object model and the SQL conversion engine transforms the SQL object model so that the original SQL query can be converted to different database's dialect. To use the API from your J2EE/Java application, please look at SwisSQL API product.

[hutch--]

JJ,

Its a nice looking tool but not much use to me, the mysqldump database is truncated and the required data is simply not there. The local forum loads the data but has no topics to point to them.I have used all of the MySql repair techniques but you cannot add data that is not there so I cannot directly use the database. It tok a while as it is so large that I had nothing that would open 655 meg. I ended up splitting into 8 parts and editing each file. Fixed a few of the problems but have no way to add the data back.

I already have at the top of this forum an 800 meg browsable web site in html that does most of the old forum and I don't think I will get much better.

[Tedd]

The easiest way to dump the whole database would be:
mysqldump -u USERNAME -p DBNAME | gzip -9 > forum_db.sql.gz
and compressing on-the-fly should allow you to squeeze a lot more in, but you may still get cut off depending on quota limits.


If that doesn't work, you can do it a table at a time; which is annoying, but hopefully there aren't too many tables.

To get a list of tables:
mysqlshow -u USERNAME -p DBNAME

and then for each table listed:
mysqldump -u USERNAME -p DBNAME TABLE | gzip -9 > forum_db_table.sql.gz

(Making sure you download and remove the files after each sizeable dump.)



And if that doesn't work (because one of the tables is still too big).... you can do multiple queries to get the contents of the offending table a section at a time (SELECT * FROM table OFFSET 0 LIMIT 10000; then offset 10000, 20000, etc.)

[hutch--]

Tedd,

Thanks a lot for the reference on MySql. I am only just coming up to pace with the command line tools locally on my own machine and I have always been very careful experimenting on the server so I don't wreck anything.

LATER : That has been highly successful, I did not even have to individually download the tables. It came down at 321 meg gzipped, expands up to a 2.2 gig sql file. Now I have to see if I can stuff it into a local mysql database and edit the junk out of it. I have a couple of toys for doing this, phpmyadmin is OK ans I have been using sqlbuddy on the older format databases and both work OK.

[hutch--]

There is some virtue in being able to set up the entire old forum locally, it has given me the capacity to properly investigate the structure of the database. The old forum software had this irksome habit of logging sessions without having any way to get rid of them so over time one of the tables in the MySql database smf_sessions added up to some massive number which in turn inflated the database to over 2 gigabytes in size.

I have a PHP toy installed locally called sqlbuddy that is a genuine joy to hack around a database and by truncating the smf_sessions table the database drops from 2.2 gig to about 120 meg and that makes the entire local copy of the old forum run a lot faster. I am playing with a copy of the old forum to try and get its size down to make it available as an archive and with truncating things like PMs and a bit of other junk, it start to become a lot more manageable in size.

Its unfortunate that its no joy to set up software this old due to compatibility reasons with MySql and PHP. It would have been much easier to have set it up on the server but the old software from SMF is so piggy in terms of compatibility that I doubt it is possible. Just setting it up locally was a game of musical chairs with Apache, MySql and PHP versions then testing through SMF versions and after wasting too much time the original files from the UK server worked the best and were compatible with the database.

[Tedd]

:t

Use DROP TABLE generously ;)
(Also scrub the account details - passwords, emails, etc)

Once you're finished, you might try dumping to sql and loading it back in again - drops are done lazily, so you might squeeze a bit more out, but more importantly there won't be random chunks of private data sitting around.

[hutch--]

There are a couple of things that would be worth knowing, I have played with mysqlcheck with its -r (repair option) and -o (optimise) and it does "something" but I don't know how successful the operation is. What I did do was install sqlbuddy on the UK server in an obscure location then open the database for the old forum and simply truncate the massive table and the database dropped in size by some massive amount which I then dumped, gzipped and downloaded it at about 35 meg. It blows up to about 130 meg as an SQL file but I can even edit that locally with one of my editors.

The problem had always been that the UK server was difficult to get phpmyadmin running on it and I did not have an easy way to access the database or edit it apart from command line mysql files and I had nowhere to experiment (learn how they worked).

I understand your suggestion and it is probably the best optimised way to reset up the database as you don't get any left over junk loading an SQL file into an empty database.

[Mr Hippy]

Just export the database(s) from the phpMyAdmin panel in a compressed format.

Great new forum by the way. Seems a lot more cleaner and stable.

[hutch--]

The problem with that suggestion is that PHP has a 64 meg limit where the MySql command line tools don't. The downloaded database from the old forum was 2.2 gig. Once I could get it on my local machine and load it into a database, it was easy to edit and I removed a massive amount of sessions junk that dropped it to a reasonable size, about 130 meg.