phpmyadmin restore error

[Lightning]

from localhost install onto server using "replace"

both phpmyadmin257full


Database planet_okura running on localhost
Error

SQL-query :

CREATE TABLE `mos_banner` (

`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
)ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =5

MySQL said:


#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 AUTO_INCREMENT=5' at line 19


anyone know how to fix this?

[Bigjohn]

I'm going to bet that you've got a different MYSQL version on your PC...

[Lightning]

my pc is using MySQL 4.1 while server is MySQL 4.0.22

now how can i fix this problem?

[Markku]

Try to take out the charset definition, "DEFAULT CHARSET = latin1".
Just search and replace it with an empty string.

[Lightning]

Originally Posted by Markku

Try to take out the charset definition, "DEFAULT CHARSET = latin1".
Just search and replace it with an empty string.

ok i found the lines:

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

and more..

what you mean by empty string??

can show me one sample thanks!!

[Markku]

Search for the string "DEFAULT CHARSET=latin1"
and replace it with single space. Basically you want to delete that character set definition.

[Lightning]

Originally Posted by Markku

Search for the string "DEFAULT CHARSET=latin1"
and replace it with single space. Basically you want to delete that character set definition.

hello. thanks

i did that and it work but theres new error:



Database planet_okura running on localhost
Error

SQL-query :

CREATE TABLE `phpbb_intellibiz_search_wordlist` (

`word_text` varchar( 50 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL default '',
`word_id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`word_common` tinyint( 1 ) unsigned NOT NULL default '0',
PRIMARY KEY ( `word_text` ) ,
KEY `word_id` ( `word_id` )
)ENGINE = InnoDB AUTO_INCREMENT =24

MySQL said:


#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 'collate latin1_bin NOT NULL default '',
`word_id` mediumint(

[Markku]

Similar thing, try to replace:

CHARACTER SET latin1 COLLATE latin1_bin

with single space, might work

[Lightning]

ok its done thank you. www.okura.planetbuddy.com my hard work displayed with your great help! thanks alot!

[Markku]

Glad to help

[Lightning]

thanks for your time and effort! appreciated!!

[Firefrog]

Hi Seems like I have a similar issue to the resolved one above. I am trying to import mos tables from a .sql text file and started out with this
+++++++++
Error

SQL-query :

CREATE TABLE `mos_banner` (
`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) COLLATE latin1_general_ci NOT NULL default 'banner',
`name` varchar( 50 ) COLLATE latin1_general_ci NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) COLLATE latin1_general_ci NOT NULL default '',
`clickurl` varchar( 200 ) COLLATE latin1_general_ci NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) COLLATE latin1_general_ci default NULL ,
`custombannercode` text COLLATE latin1_general_ci,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
)ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =3

MySQL said:
#1064 - You have an error in your SQL syntax near 'collate latin1_general_ci NOT NULL default 'banner',
`name` varchar(50) collat' at line 4
+++++++++++++++++

I edited out all occurences of the latin thing but now have this


+++++++

SQL-query :

CREATE TABLE `mos_banner` (
`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
)ENGINE = MYISAM AUTO_INCREMENT =3

MySQL said:
#1064 - You have an error in your SQL syntax near 'ENGINE=MyISAM AUTO_INCREMENT=3 ' at line 19


++++++++++

Would anyone have tips on how to correct the error?

[Firefrog]

Bump - really need help with this

Thanks for any tips!

[Markku]

Try to remove "AUTO_INCREMENT =3" statement

[Firefrog]

I have searched these forums and tried every trick I can find, includng the suggestions above.
Each time I solve one error another appears.

There must be a way to export from one sql database and import into another without jumping through hoops, otherwise no one would do it.

My latest error

Error

SQL-query : 

CREATE TABLE `mos_banner` (
`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
)ENGINE = MYISAM INSERT INTO `mos_banner`
VALUES ( 1, 1, '', 'Arpajen', 0, 846, 0, 'arpajen-bnr.gif', 'http://www.arpajen.com.au', '2005-03-28 16:21:10', 0, 0, '0000-00-00 00:00:00', '', '' )

MySQL said:
#1064 - You have an error in your SQL syntax near 'ENGINE=MyISAM  

INSERT INTO `mos_banner` VALUES (1, 1, '', 'Arpajen', 0, 846,' at line 19

[Documentation] · [Back]

Surely I will not have to go through 3000 lines of text and fix every error.

Sorry for the rant, for all my work I seem to be going in circles.

Thanks for any help

[Markku]

Please change from:

)ENGINE = MYISAM INSERT INTO `mos_banner`

To:

)ENGINE = MYISAM ;

INSERT INTO `mos_banner`

Note ";" character and empty line after MYISAM

[Lightning]

how about this new one:

Error

SQL-query :

CREATE TABLE `mos_pshop_waiting_list` (

`waiting_list_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`product_id` int( 11 ) NOT NULL default '0',
`user_id` varchar( 32 ) NOT NULL default '',
`notify_email` varchar( 150 ) NOT NULL default '',
`notified` enum( '0', '1' ) default '0',
`notify_date` timestamp NOT NULL defaultCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY ( `waiting_list_id` ) ,
KEY `product_id` ( `product_id` ) ,
KEY `notify_email` ( `notify_email` )
)ENGINE = MYISAM AUTO_INCREMENT =1

MySQL said:


#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 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY

thanks

[Bigjohn]

space between default and current_timestamp.

[1000ideen]

Sorry but I got one more of this sort and I don`t quite understand it.

I`m moving to a server from MySQL3.23.49 to 4.0.15 I got an ERROR when importing a MySQL file with phpMyAdmin 2.5.7

Thanks

====================

CREATE TABLE mos_components(

id int( 11 ) NOT NULL AUTO_INCREMENT ,
name varchar( 50 ) NOT NULL default '',
link varchar( 255 ) NOT NULL default '',
menuid int( 11 ) unsigned NOT NULL default '0',
parent int( 11 ) unsigned NOT NULL default '0',
admin_menu_link varchar( 255 ) NOT NULL default '',
admin_menu_alt varchar( 255 ) NOT NULL default '',
OPTION varchar( 50 ) NOT NULL default '',
ordering int( 11 ) NOT NULL default '0',
admin_menu_img varchar( 255 ) NOT NULL default '',
iscore tinyint( 4 ) NOT NULL default '0',
params text NOT NULL ,
PRIMARY KEY ( id )
) TYPE = MYISAM


#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 'option varchar(50) NOT NULL default '',
ordering int(11) NOT

[1000ideen]

O.k. I got it. The posting here explains it:
http://forum.mamboserver.com/showpos...8&postcount=14


The MAIN point in the backup - which Brad should also mention but doesnt - is that the words OPTION and FULLTEXT are used as fields in mambo tables - THEY SHOULD NOT BE!

OPTION and FULLTEXT are reserved MySql commands and unless they are enclosed in quotes the restore of a mambo backup will ALWAYS fail (as the guy in this thread did)

When you EXPORT your data using phpMyAdmin you MUST ensure that the option to "Enclose table and field names with backquotes" is checked - this will then enclose the field names in quotes and stop the restore problem.

The other options I ensure are checked on the EXPORT screen are:
Add AUTO_INCREMENT value
Use hexadecimal for binary fields
Complete inserts