Sql Database Import, Problem With ''

[dwessell]

Right on.. So I did a SQL backup of a Mambo database using Cpanel… Thought everything was wonderful..

It wasn't 🙁

I'm now trying to import the backup..

First we had an issue with the keyword option, so I enclosed it as `option`.

That went well.

Now I'm having an issue with mediumtext…

I'm using SQLyog to import the data.. It's about 70 megs, so I can't use phpmyadmin..

SQLyog seems to add 'mediumtext', which the server won't take….. But what I have in the text file is mediumtext without the '' around it.

Is this an option somewhere that I can disable, that I haven't been able to find yet?

Thanks

David

[peterlaursen]

Quote:

So I did a SQL backup of a Mambo database using Cpanel

…

I'm using SQLyog to import the data

If you don't use SQLyog for the exports/backup operation, you should not blame it for the content of the SQL-file! When importing a dump to MySQL SQLyog does not do anything else than executing the SQL-statements in the file with the MySQL-server.

Did I understand you right?

[dwessell]

I suppose posting more information would be helpful..

Here's the error key from SQLyog:

Query:

CREATE TABLE mos_contact_details (

id int(11) NOT NULL auto_increment,

name varchar(100) NOT NULL default '',

con_position varchar(50) default NULL,

address text,

suburb varchar(50) default NULL,

state varchar(20) default NULL,

country varchar(50) default NULL,

postcode varchar(10) default NULL,

telephone varchar(25) default NULL,

fax varchar(25) default NULL,

misc `mediumtext`,

image varchar(100) default NULL,

imagepos varchar(20) default NULL,

email_to varchar(100) default NULL,

default_con tinyint(1) unsigned NOT NULL default '0',

published tinyint(1) unsigned NOT NULL default '0',

checked_out int(11) unsigned NOT NULL default '0',

checked_out_time datetime NOT NULL default '0000-00-00 00:00:00',

ordering int(11) NOT NULL default '0',

params text NOT NULL,

user_id int(11) NOT NULL default '0',

catid int(11) NOT NULL default '0',

access tinyint(3) unsigned NOT NULL default '0',

PRIMARY KEY (id)

) TYPE=MyISAM

Line no.:288

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 'mediumtext`,

image varchar(100) default NULL,

imagepos varchar(

---

Now here's the actual text from the sql file:

DROP TABLE IF EXISTS mos_contact_details;

CREATE TABLE mos_contact_details (

id int(11) NOT NULL auto_increment,

name varchar(100) NOT NULL default '',

con_position varchar(50) default NULL,

address text,

suburb varchar(50) default NULL,

state varchar(20) default NULL,

country varchar(50) default NULL,

postcode varchar(10) default NULL,

telephone varchar(25) default NULL,

fax varchar(25) default NULL,

misc mediumtext,

image varchar(100) default NULL,

imagepos varchar(20) default NULL,

email_to varchar(100) default NULL,

default_con tinyint(1) unsigned NOT NULL default '0',

published tinyint(1) unsigned NOT NULL default '0',

checked_out int(11) unsigned NOT NULL default '0',

checked_out_time datetime NOT NULL default '0000-00-00 00:00:00',

ordering int(11) NOT NULL default '0',

params text NOT NULL,

user_id int(11) NOT NULL default '0',

catid int(11) NOT NULL default '0',

access tinyint(3) unsigned NOT NULL default '0',

PRIMARY KEY (id)

) TYPE=MyISAM;

Notice the curious lack of '' around medium text…

With all that being said.. I'm ALMOST glad I had this issue, becuase I found SQLyog… This is MUCH nicer then having to do it through phpmyadmin…. Thanks for the great program.. (Not trying to suck up, but I guess everyone likes nice things to be said about their program :))

Thanks

David

[peterlaursen]

No problem here!

I can import your sql-statement form SQL-pane in SQLyog and from a file.

Does the server use ANSI ?

[peterlaursen]

server modes are explained her

http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

Could that be it?

If “MySQL Administrator” can connect it will show you the current settings.

[peterlaursen]

or are there some non-pritable characters in file ?

did you open it in any sort of word-processor to do the editing ?

this I don't understand:

Quote:

First we had an issue with the keyword option, so I enclosed it as `option`.

[dwessell]

I'm checking as we type..

I can import fine from the pane… But when I import from that actual file, that's when the '' seems to get added on…

Now.. I copied JUST that tidbit, into a seperate file, and imported it just fine.. It's only when trying to do the full file (70 megs) that I recieve the errors. (Error is around line 280 something)..

Thanks

David

peterlaursen wrote on Aug 26 2005, 10:54 AM:

No problem here!

I can import your sql-statement form SQL-pane in SQLyog and from a file.

Does the server use ANSI ?

[post=”6991″]<{POST_SNAPBACK}>[/post]

[Ritesh]

This is very strange. SQLyog does not add anything to the query nor to the error message. It simply executes a query and shows the error as returned by MySQL itself.

If the data is not confidential then is it possible for me to look at the sql file? I would be glad to provide you with FTP access where you can upload the file.

[dwessell]

I have no issues with that.. I'll take any help that I can get at the moment..

You can email it to me at dave at wessell dot net

I do appreciate the assistance.

Thanks

David

Ritesh wrote on Aug 26 2005, 11:15 AM:

This is very strange. SQLyog does not add anything to the query nor to the error message. It simply executes a query and shows the error as returned by MySQL itself.

If the data is not confidential then is it possible for me to look at the sql file? I would be glad to provide you with FTP access where you can upload the file.

[post=”6997″]<{POST_SNAPBACK}>[/post]

[peterlaursen]

I believe that you used an editor tool (such as a word-processor) that you should not use for a pure text/sql-file?

70 MB is quite a bit but try opening the file in a PURE text-editor and save it. If notepad can not (I would not even try!) I believe the Textpad and Ultraedit can do that.

[dwessell]

I had used Wordpad…

But reloaded from the original untouched, unlooked at, backup file in UE.. Same issue…

peterlaursen wrote on Aug 26 2005, 11:24 AM:

I believe that you used an editor tool (such as a word-processor) that you should not use for a pure text/sql-file?

70 MB is quite a bit but try opening the file in a PURE text-editor and save it.  If notepad can not (I would not even try!) I believe the Textpad and Ultraedit can do that.

[post=”6999″]<{POST_SNAPBACK}>[/post]

[peterlaursen]

OK then …

then someone will have to test with (at least some of) the data with MySQL in logging mode and maybe inspect the file as such in a hex-editor. My best idea right now.

Ritesh probaly have better tools and surroundings for this than I or anybody else.

What is the MySQL version?

[dwessell]

MySQL Version 4.0.25-standard-log

Actually.. I've made some progress… I took your advice, and reloaded in UE, saved it there, instead of just viewing it (The original untouched file). And I'm not getting the inserts '' anymore..

It errors out a little further down..

CREATE TABLE mos_content (

id int(11) unsigned NOT NULL auto_increment,

title varchar(100) NOT NULL default '',

title_alias varchar(100) NOT NULL default '',

introtext mediumtext NOT NULL,

fulltext mediumtext NOT NULL,

Line no.:336

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 'mediumtext NOT NULL,

state tinyint(3) NOT NULL default '0',

Should I be encasing these in ''…. Thanks a ton for your time, it's really appreciated.

Thanks

David

[peterlaursen]

I am not in doubt that it is a problem with the file itself then!

the reaon why I asked if the server is running in ANSI-mode (or some other non-standard mode), is that could confuse the Cpanel backup-utility.

I think there will be the need for an extensive “search and replace” with that file from an editor like Ultraedit.

[dwessell]

peterlaursen wrote on Aug 26 2005, 11:48 AM:

I am not in doubt that it is a problem with the file itself then!

the reaon why I asked if the server is running in ANSI-mode (or some other non-standard mode), is that could confuse the Cpanel backup-utility.

I think there will be the need for an extensive “search and replace” with that file from an editor like Ultraedit.

[post=”7003″]<{POST_SNAPBACK}>[/post]

Peter.. Thanks for your assistance.. I'm making some pretty decent progress finally.. It's my first real experience with SQL, and I'm learning a lot..

Can I use UE to break the file apart into smaller logical pieces? Or is that going to cause some unforseen catastrophe?

Thanks

David

[peterlaursen]

a sql file is a series of statements only. You can cut it into pieces! Just add the statement “use `db_name`;” at the top of of each piece, if insert-statements don't use full syntax like `db_name`.`tablename`.`columname` but only `tablename`.`columname`.

Beware that “backquotes” are not apostroph's!

[peterlaursen]

And if server uses ANSI the ' and ” and ` have other meanings then if it does not.

[Author]

Posts