Importing a MySQL database problems - help please! - Tilted Forum Project Discussion Community

cowudders14 · 02-25-2006, 06:26 AM

Hi,

My website has a forum running IPB . I was running v2.0.4, but a "security update" (good security - no more forum!) crashed the site. I was running php 4.1 and the forum requires at least 4.3. I contacted my host (easily.co.uk) and they said they had to move me to a new server to get php updated (and more than double what I was paying them. Grrr....). The new server has a more up to date version of php on it (now 4.3), and I've re-installed the forum, now at v2.1.4, but I can't re-import the database. IPB wont do it without charging me a lot, easily.co.uk are useless and un-cooperative, so I'm stuck with an offline forum and with people emailing me asking when it'll be back online as it's a help site for people who have been raped or falsely accused of rape.

Before the move, I took a backup of the database using the backup function built into IPB. To be careful, I also took a backup of the database using phpmyadmin, once with default settings and once with the "no structure" setting. Trying to import any of these results in database errors. I'm using the "import" function of phpmyadmin v2.7.0. I can't remember the version of phpmyadmin on the old server, but a friend did recently remark "Wow, that's out of date".

Importing the one I exported from phpmyadmin gives the error of:

Quote:

Error

SQL query:

--
-- Dumping data for table `ibf_admin_logs`
--
INSERT INTO `ibf_admin_logs`
VALUES ( 1, 'op', 'dourl', 1, 1085328548, 'Board Settings Updated, Back Up Written', '62.53.78.133' ) ;

MySQL said:
#1062 - Duplicate entry '1' for key 1

Importing the one from the IPB backup facility gives the error of

Quote:

Error

SQL query:

CREATE TABLE ibf_admin_logs(
id bigint( 20 ) NOT NULL AUTO_INCREMENT ,
act varchar( 255 ) default NULL ,
code varchar( 255 ) default NULL ,
member_id int( 10 ) default NULL ,
ctime int( 10 ) default NULL ,
note text,
ip_address varchar( 255 ) default NULL ,
PRIMARY KEY ( id )
) TYPE = MYISAM ;

MySQL said:
#1050 - Table 'ibf_admin_logs' already exists

I've tried dropping all the stuff in the database (installing the froum sets up all the tables etc, and then the import works fine, but the forum gives an error message.

If you want to see the actual SQL backup files - they are here:
http://www.daftmoo.co.uk/temp/broadcaster/

I'm lost, frustrated, and tearing my hair out!!

Please help if you know more about phpmyadmin/mysql than I do!

Thanks

theFez · 02-28-2006, 10:02 PM

for the problem importing the IPB backup, if you add the line

Code:

drop table if exists table_name;

just before each create table call, then your ddl code will run and the table will be re-created and re-populated.
I doubt this will solve your problem however. Most likely between versions the database design changed and your old database is out of date. Also most likely the IPB should have the tools needed to update the database. So, if you can, get your hands on whatever IPB uses to migrate old databases to the new format.
Next, alter the IPB generated script as i suggested above and get that database up and loaded with all your data.
Finally, run the IPB tool to migrate your database to the new format.

If IPB won't give you the tools to migrate the db, you would have to write your own.

cowudders14 · 03-05-2006, 10:08 AM

Hi,

Thanks for the reply. Further investigation shows that even dropping the tables etc as you suggested does not work - the format of the database has changed. Considerably so. IPB won't give out the tools, and won't give a download of the previous version so I can install that and use the upgrade facility in it either. Great...

Thus, I've had to give up and pay the money for them to do the re-import for me. Never mind...

I tried comparing the data structure of the export to the new one, and several changes have been made - the table rows have been re-ordered, some have been added etc. I imported this into Excel as csv, but, naturally, the tables that contained the text also had loads of commas in them, so excel couldn't identify which commas were part of the text and which were actual separators.

Bummer.

Still, thanks for the advice - it was worth a try, and I've learned a lot about sql in the process!

ScottKuma · 03-05-2006, 10:17 AM

What a crappy way for IPB to treat customers.

I despise companies like that. Sorry you got treated that way.

cowudders14 · 03-05-2006, 11:34 AM

Very true, especially as IPB is a commercial product and I have to pay an annual fee for it's usage. I thought the fee bought me good customer service and tech support. Hrmph! That's why I'm strongly considering moving to phpBB...

Trouble is, of course, phpBB will have a different database structure entirely, and the move there will be very much more complicated. There is a IPB -> phpBB converter, but, as IPB have just changed the database format, that will now be invalid, and will need to be updated before becoming usable. As several of the changes I saw in the database were fairly pointless IMHO, I do wonder if part of the reason for the changes is to make more money from mugs like me, and to prevent us pissed off mugs from moving to phpBB, as it will too much hassle and the converters wont work...

Up a creek without a paddle?

theFez · 03-05-2006, 08:15 PM

so i gotta ask,
what is so great about ipb that you chose it over god only knows how many free BB software packages?

cowudders14 · 03-16-2006, 09:35 AM

When I started using IPB, it was also a free product, but you paid for tech support if you needed it. Then, when I'd been running it for a while, and paying for the tech support too, they changed it into a fully commercial product, with licences required for usage.

I didn't really mind as I was paying for tech support anyway, and the forum was growing and being nurtured at the time - I had neither the time, wish nor inclination to change it. Ok, they were being a bit crap with their tech support even then, but it was overall ok and not worth the hassle of moving.
Now, I've finally got the forum back online. I told IPB that the only was I could see of bringing the forum back online was by installing vbulletin and running their conversion tool! They then came up with a suggestion that actually worked. Funny that...

I've still decided to leave then when my licence runs out, and I'll probably move hosts in the process. Only problem is that will mean doing it all again in a few months time...

I'll probably go over to vbulletin, and I haven't chosen which new host to go with - I'm looking around at the moment.

Why not phpBB or something else similar? Security, pure and simple. I have a friend who works full time in IT security as a consultant. He says stay away from free stuff and go with IPB or vbulletin. It's a fair point really - it's important to me to have very god security on the forum. If the forum did get hacked, it's not like here where people would say "oh well", and give Halx a few days to fix it, then come back and start using it again. The forum is a support forum for rape victims, people falsely accused of rape, people with mental health problems and the like. Thus, if the forum got hacked and crap started appearing all over it, it could be quite damaging to various members of the forum.

02-28-2006, 10:02 PM	#2 (permalink)
theFez Crazy Location: here and there	for the problem importing the IPB backup, if you add the line Code: drop table if exists table_name; just before each create table call, then your ddl code will run and the table will be re-created and re-populated. I doubt this will solve your problem however. Most likely between versions the database design changed and your old database is out of date. Also most likely the IPB should have the tools needed to update the database. So, if you can, get your hands on whatever IPB uses to migrate old databases to the new format. Next, alter the IPB generated script as i suggested above and get that database up and loaded with all your data. Finally, run the IPB tool to migrate your database to the new format. If IPB won't give you the tools to migrate the db, you would have to write your own. __________________ # chmod 111 /bin/Laden

03-05-2006, 10:08 AM	#3 (permalink)
cowudders14 Bringer of good Moos... Location: Midlands, UK	Hi, Thanks for the reply. Further investigation shows that even dropping the tables etc as you suggested does not work - the format of the database has changed. Considerably so. IPB won't give out the tools, and won't give a download of the previous version so I can install that and use the upgrade facility in it either. Great... Thus, I've had to give up and pay the money for them to do the re-import for me. Never mind... I tried comparing the data structure of the export to the new one, and several changes have been made - the table rows have been re-ordered, some have been added etc. I imported this into Excel as csv, but, naturally, the tables that contained the text also had loads of commas in them, so excel couldn't identify which commas were part of the text and which were actual separators. Bummer. Still, thanks for the advice - it was worth a try, and I've learned a lot about sql in the process! __________________ Moo! I'm mooey!

03-05-2006, 10:17 AM	#4 (permalink)
ScottKuma Insane Location: Maineville, OH	What a crappy way for IPB to treat customers. I despise companies like that. Sorry you got treated that way. __________________ A government big enough to give you everything you want is big enough to take from you everything you have. -Gerald R. Ford GoogleMap Me

03-05-2006, 11:34 AM	#5 (permalink)
cowudders14 Bringer of good Moos... Location: Midlands, UK	Very true, especially as IPB is a commercial product and I have to pay an annual fee for it's usage. I thought the fee bought me good customer service and tech support. Hrmph! That's why I'm strongly considering moving to phpBB... Trouble is, of course, phpBB will have a different database structure entirely, and the move there will be very much more complicated. There is a IPB -> phpBB converter, but, as IPB have just changed the database format, that will now be invalid, and will need to be updated before becoming usable. As several of the changes I saw in the database were fairly pointless IMHO, I do wonder if part of the reason for the changes is to make more money from mugs like me, and to prevent us pissed off mugs from moving to phpBB, as it will too much hassle and the converters wont work... Up a creek without a paddle? __________________ Moo! I'm mooey!

03-05-2006, 08:15 PM	#6 (permalink)
theFez Crazy Location: here and there	so i gotta ask, what is so great about ipb that you chose it over god only knows how many free BB software packages? __________________ # chmod 111 /bin/Laden

03-16-2006, 09:35 AM	#7 (permalink)
cowudders14 Bringer of good Moos... Location: Midlands, UK	When I started using IPB, it was also a free product, but you paid for tech support if you needed it. Then, when I'd been running it for a while, and paying for the tech support too, they changed it into a fully commercial product, with licences required for usage. I didn't really mind as I was paying for tech support anyway, and the forum was growing and being nurtured at the time - I had neither the time, wish nor inclination to change it. Ok, they were being a bit crap with their tech support even then, but it was overall ok and not worth the hassle of moving. Now, I've finally got the forum back online. I told IPB that the only was I could see of bringing the forum back online was by installing vbulletin and running their conversion tool! They then came up with a suggestion that actually worked. Funny that... I've still decided to leave then when my licence runs out, and I'll probably move hosts in the process. Only problem is that will mean doing it all again in a few months time... I'll probably go over to vbulletin, and I haven't chosen which new host to go with - I'm looking around at the moment. Why not phpBB or something else similar? Security, pure and simple. I have a friend who works full time in IT security as a consultant. He says stay away from free stuff and go with IPB or vbulletin. It's a fair point really - it's important to me to have very god security on the forum. If the forum did get hacked, it's not like here where people would say "oh well", and give Halx a few days to fix it, then come back and start using it again. The forum is a support forum for rape victims, people falsely accused of rape, people with mental health problems and the like. Thus, if the forum got hacked and crap started appearing all over it, it could be quite damaging to various members of the forum. __________________ Moo! I'm mooey!