database backup

Hunterseaker

Hi guys,

Ive a question:
Im moving from another host to frihost, i've backed up all my databases.
And now I want to in install the databases on this host, tho when I try to install the databases with Direct Admin and phpMyadmin I get error messages like

Code:

ERROR 1064 at line 21: 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 'collate latin1_general_ci NOT NULL, `Image` varchar(255) coll

So I changed all the latin things to utf8. tho that doesnt help either cause I get an other message again about the collate NOT NULL.
and so on, how is this possible? does phpmyadmin just dont support all my types or is their any other reason. I will post a piece of mysql code here so you can see my database structure:

Code:

-- MySQL dump 10.9
--
-- Host: localhost Database: hunter_affiliates
-- ------------------------------------------------------
-- Server version 4.1.21-standard

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `affiliates`
--

DROP TABLE IF EXISTS `affiliates`;
CREATE TABLE `affiliates` (
`ID` int(11) NOT NULL auto_increment,
`link` text collate utf8 NOT NULL,
`Image` varchar(255) collate utf8 NOT NULL default '',
`alt` varchar(255) collate utf8 NOT NULL default '',
`hits` int(11) NOT NULL default '0',
`last_ip` varchar(255) collate utf8 NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8;

--
-- Dumping data for table `affiliates`
--

LOCK TABLES `affiliates` WRITE;
/*!40000 ALTER TABLE `affiliates` DISABLE KEYS */;
INSERT INTO `affiliates` (`ID`, `link`, `Image`, `alt`, `hits`, `last_ip`) VALUES (6,'http://www.chrislowden.com','http://wallpaperheaven.cwhnetworks.com/affiliates/innovativeideas.gif','chris',5,'66.249.72.201'),(7,'http://www.kevinjb.com/','http://www.kevinjb.com/affiliate.gif','kevinjb',7,'66.249.65.240'),(8,'http://www.quisoft.firemultimedia.nl','http://www.uploadworld.nl/users/quisoft/uploadworld_quisoftbanner1.gif','quisoft',5,'66.249.72.201'),(9,'http://www.loosemoose52.com','http://www.loosemoose52.com/pics/loosemoose52button.jpg','loosemoose52.com',6,'66.249.72.201'),(10,'http://micker.dk','http://micker.dk/gallery/affiliate.gif','micker',8,'74.6.71.190'),(11,'http://www.portfolio-klok.frost-hosting.nl','http://uploaddump.nl/uploads/kkocowqcckcp.gif','portfolio-klok',6,'74.6.70.72'),(12,'http://www.johnny-slabbekoorn.nl','http://www.johnny-slabbekoorn.nl/button.jpg','johnny-slabbekoorn',4,'66.249.72.201'),(13,'http://www.j4xxdesigns.co.uk','http://www.j4xxdesigns.co.uk/resources/j4xxa.gif','j4xxdesigns',5,'200.79.74.114'),(14,'http://fluegelarts.uni.cc','http://fluegelarts.uni.cc/images/fa_aff.gif','fluegelarts',6,'66.249.72.201'),(15,'http://www.inoubliabledesigns.cwhnetworks.com','http://www.inoubliabledesigns.cwhnetworks.com/images/button.gif','inoubliabledesigns',4,'66.249.72.201'),(17,'http://jakepicton.cwhnetworks.com/','http://wallpaperheaven.cwhnetworks.com/images/mael.gif','jakepicton',7,'66.249.72.201'),(18,'http://www.xyco.co.uk/','http://www.xyco.co.uk/xycoaff.gif','xyco',6,'66.249.65.240'),(19,'http://logatic.co.uk/forums','http://img85.imageshack.us/img85/7153/rsz200crazyfrogdesktop11024x76.gif','logatic',6,'66.249.72.201'),(20,'http://www.mikessite.2hell.com/','http://www.freewebs.com/mikealley/images/MSITE.gif','mikessite',5,'66.249.65.240'),(21,'http://www.photoshoplab.net/','http://www.photoshoplab.net/img/pl-button1.gif','photoshoplab',4,'66.249.72.201'),(22,'http://www.pixel2life.com/forums/index.php?act=toplist&code=hitin&site=150','http://wallpaperheaven.cwhnetworks.com/images/pixel2life1.gif','pixel2life',4,'81.207.143.10');
/*!40000 ALTER TABLE `affiliates` ENABLE KEYS */;
UNLOCK TABLES;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Does anyone know how I can install my backups correctly? cause i realy need them.

PS. I used Cpanel for backing up my databases, i dont know if that makes any differens

Daniel15

Hey, do I know you from somewhere? Smile

Anyways, try getting rid of the extra stuff at the end of the CREATE TABLE queries, and all the collate stuff. For example, instead of:

Code:

DROP TABLE IF EXISTS `affiliates`;
CREATE TABLE `affiliates` (
`ID` int(11) NOT NULL auto_increment,
`link` text collate utf8 NOT NULL,
`Image` varchar(255) collate utf8 NOT NULL default '',
`alt` varchar(255) collate utf8 NOT NULL default '',
`hits` int(11) NOT NULL default '0',
`last_ip` varchar(255) collate utf8 NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8;

Try:

Code:

DROP TABLE IF EXISTS `affiliates`;
CREATE TABLE `affiliates` (
`ID` int(11) NOT NULL auto_increment,
`link` text NOT NULL,
`Image` varchar(255) NOT NULL default '',
`alt` varchar(255) NOT NULL default '',
`hits` int(11) NOT NULL default '0',
`last_ip` varchar(255) NOT NULL default '',
PRIMARY KEY (`ID`)
);

Alternatively, an easier method would be to try getting an SQL backup from your old host using phpMyAdmin (as opposed to using the backup feature in cPanel)

Hunterseaker

Daniel15 wrote:

Hey, do I know you from somewhere? Smile

Anyways, try getting rid of the extra stuff at the end of the CREATE TABLE queries, and all the collate stuff. For example, instead of:
[code]
Alternatively, an easier method would be to try getting an SQL backup from your old host using phpMyAdmin (as opposed to using the backup feature in cPanel)

Hi Daniel, Yeah I do think I know you from somewere Wink

cwhnetworks wansnt it?

about your solution:
I will try to loose all the extra stuff.
The problem about backing up with phpMyadmin is that I cant backup via phpMyadmin on the other host cause I cant connect,
i think im accidently banned(dont worry guy's I've done nothing wrong,
I just think it is an accident)
so I cant back it up again.....
I will ask someone else to back it up again.

thnx anyway