Database Problems

[xertech]

Hello All and thanks in advance for any help you provide..   

My problem is this , my original database name was over 16 letters long..  i/e  *******_smf*******  , the new host has a limit of 16 ( Database name cannot be longer than 16 characters.)   Are there any migration programs that will convert everything over or can I shorten the database name without any errors ?     

signed Screwed !

[busterone]

The database data is what is important, not the name. You can create a new database on your new host using whatever name you choose, then import the data from the former database. After your forum files are transferred to the new host, you can correct the database name by uploading repair_settings.php to your forum root and run it to correct the db name/password/server name differences.

repair_settings.php can be found on this page-
http://download.simplemachines.org/?tools

[CapadY]

Use Find and replace to rename the database in the MySQL backup file before you restore the backup

[xertech]

Thanks for the quick response , I created another database and imported the data from my old file ..  Only problem is all the data isn't being imported .  It gets to the 45 table (after spider hits) and stops .  The tar file is 5mb ,  .   I know it's all there ..   Is there a way to check the database for errors ?

[cicka]

Thanks for the quick response , I created another database and imported the data from my old file ..  Only problem is all the data isn't being imported .  It gets to the 45 table (after spider hits) and stops .  The tar file is 5mb ,  .   I know it's all there ..   Is there a way to check the database for errors ?

Do you get any errors?

[xertech]

Error

SQL query:

INSERT INTO `xmf_log_spider_hits` (`id_hit`, `id_spider`, `log_time`, `url`, `processed`) VALUES (751539, 1, 1262892074, 'a:4:{s:5:\"board\";i:5;s:5:\"topic\";i:433;s:4:\"wap2\";s:0:\"\";s:10:\"USER_AGENT\";s:100:\"DoCoMo/2.0 N905i(c100;TB;W24H16) (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)\";}';, 1), (751540, 1, 1262892100, 'a:4:{s:5:\"board\";i:3;s:5:\"topic\";i:812;s:9:\"prev_next\";s:4:\"next\";s:10:\"USER_AGENT\";s:72:\"Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)\";}';, 1), (751541, 1, 1262892108, 'a:4:{s:5:\"board\";i:4;s:5:\"topic\";i:399;s:9:\"prev_next\";s:4:\"prev\";s:10:\"USER_AGENT\";s:72:\"Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)\";}';, 1), (751542, 1, 1262892114, 'a:3:{s:5:\"board\";i:12;s:5:\"topic\";i:105;s:10:\"USER_AGENT\";s:169:\"SAMSUNG-SGH-E250/1.0 Profile/MIDP-2.0 Configuration/CLDC-1.1 UP.Browser/6.2.3.3.c.1.101 (GUI) MMP/2.0 (compatible; Googlebot-Mobile/2.1; +http://www[...]

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a:3' at line 68

[cicka]

From the error discription the 'a:3 part is missing the rest of its code. But I do not think that table is vital for your forum, so you can truncate and try to import the database again.

[xertech]

How would I empty the table before I reload ?

[cicka]

You can remove the whole INSERT INTO `xmf_log_spider_hits` deal from the backup file for example.

[CapadY]

The most of the smf_log_*** files can be skipped when you make a backup or want to do a removal.

[xertech]

CREATE TABLE IF NOT EXISTS `xmf_log_spider_hits` (
  `id_hit` int(10) unsigned NOT NULL auto_increment,
  `id_spider` smallint(5) unsigned NOT NULL default '0',
  `log_time` int(10) unsigned NOT NULL default '0',
  `url` text NOT NULL,
  `processed` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id_hit`),
  KEY `id_spider` (`id_spider`),
  KEY `log_time` (`log_time`),
  KEY `processed` (`processed`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1124504 ;

--
-- Dumping data for table `xmf_log_spider_hits`
--

INSERT INTO `xmf_log_spider_hits` (`id_hit`, `id_spider`, `log_time`, `url`, `processed`) VALUES
(382129, 1, 1240272037, '', 1),
(382128, 1, 1240271912, '', 1),
(382127, 1, 1240271730, '', 1),

This is what were talking about ?

[xertech]

I appreciate all the help , you guys are on top of your game !    Once in my life I got lucky , the hosting company had a backup of the database and installed it for me..   

THANKS AGAIN ! 

[xertech]

Here's a few things I learned about databases and hopefully this will help someone else ..

1:  always verify your backup by reloading it and making sure you have no mistakes .

2: empty out the large log files before you backup ..  My spiderlog was so large that it messed up my database backups .   it was never able to finish the compression because the log was to large ..

3:  backup at least once a week , larger forums everyday . Use the nice java GUI  interface located on the download page .

4: the last resort , contact your hosting company and check for a backup ..

[busterone]

Indeed. I backup at least every other day, and daily if I make changes. I always make sure all the logs are cleared before the backup. I clear them on a regular basis anyway though. Also optimize tables, and check for errors before the backup. 
Glad you got it working.