Problem With Uploading Mysql Baseon Web Server

[Skeewe]

Can someone please help me since I am doing this for the first time.

Database is developed on my local computer and now I am trying to transfer it to webserver using SQLyog, both metods “Copy databse to diferent host ” and “Import from SQL dump” cannot do it, I got following error log:

“Query:

CREATE TABLE `bars` (

`no` int(10) unsigned NOT NULL auto_increment,

`housename` varchar(45) NOT NULL default '',

`cityid` int(10) unsigned NOT NULL default '0',

PRIMARY KEY (`no`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Line no.:22

Error Code: 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 'DEFAULT CHARSET=latin1' at line 6 “

Anyone help ??? Thank you

[peterlaursen]

probably the MySQL server versions are “too much different” for “copy to other host” and the “sync-tool” to work. It looks like you are running 4.1 og 5.0 on local and on the webhost 3.23 or 4.0. Charset management (and localization issues in general) with MySQL is fundamentally different up to version 4.0.x and from 4.1.x. I believe that simply is the problem

However the dump-upload should work if you simply delete this

“DEFAULT CHARSET=latin1”

from the definitions section of the sql-file – provided that the server version running at the remote host supports INNODB tables.

You could also delete all this

“ENGINE=InnoDB DEFAULT CHARSET=latin1”

and tables will be created using the default storage engine (probably MYISAM) and charset (probably utf7) on the remoste host. But of course if you need INNODB/Foreign Keys you should not delete the INNODB specification

Don't delete the “;” character that teminates the create statement!

[peterlaursen]

I'll warn you about another problem that might occur too!

You might experience that the INSERT-statements in the sql-file is too long for the server-configuration at the webhost.

The MySQL server has a setting namend MAX_ALLOWED_PACKET, and if you use HTTP-tunneling there is as a MAX_POST setting with php configuration. Both settiings must be greater than the memory-buffer needed to execute the INSERT-statements. And those settings are probably not under your control! And they are often very low at webhosting …

From the export-dialogue in SQLyog you can then uncheck the “create bulk insert statements” and each row of data will have its own INSERT-statement. It is a little bit slower to import this way, but you might need to do it!

[peterlaursen]

BTW …

you can also use the ODBC-import tool to transfer data!

Even when versions are “too much different”.

You must then install and configure the MyODBC driver on your local machine to point to the database at your local that should be transferred.

You can actually configure the ODBC to do something very similar to a one-way-sync. Using ODBC is not as effective as the checksums-algorithm of the sync-tool, but – configured correctly – result will be the same.

[Author]

Posts