I have posted a similar question on the blojsom group but I feel I will
have better chance of an answer here.
Blojsom 3 was developed using MySQL5 for it's back end. However the
host I am with uses 4.0.25 and are unwilling to upgrade - which is fair
enough.
So I decided to see what I can do to make the database creation scripts
MySQL4 compliant. The CHARSET was the easy one - just change it to
CHARACTER SET but the next one was more tricky:
The problem I am having is that version 4 fails giving a syntax error
for the single quotation marks. It appears the script (which was written
on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ',
but doing a general search and replace doesn't fix it. I've searched
the online docs regarding quotes but came up short (too many hits with
too many unrelated items) so if anyone knows the exact url that would be
helpful too.
Here's a snippet of the code:
DRP TABLE IF EXISTS `Blog`;
CREATE TABLE `Blog` (
`blog_id` varchar(50) NT NULL,
PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Thanks, G.

Graham, I seem to recall those single quote marks working without a
problem on various platforms and versions of MySQL. course they are
generally just a nicety and only required if you are using reserved
words as table/column/key names, so you could just remove them entirely.

Could you post the exact error message you're getting please?

Two thoughts -
1) You may have a bogus hidden character in your SQL file. If you look
at it with a text editor (BBEdit, TextWrangler, etc), with the "show
invivisbles" feature on, do you see unusual stuff? Sounds strange but
I've seen stranger.

2) You could work around this problem a different way
- create a 5.0 database yourself using the standard supplied scripts,
perhaps on your own workstation
- dump your new database structure using mysqldump with the flag

which should create a file suitable for piping into a MySQL 4.0 server

Dan

Graham Reeds wrote:
I have posted a similar question on the blojsom group but I feel I will
have better chance of an answer here.

Blojsom 3 was developed using MySQL5 for it's back end. However the
host I am with uses 4.0.25 and are unwilling to upgrade - which is fair
enough.

So I decided to see what I can do to make the database creation scripts
MySQL4 compliant. The CHARSET was the easy one - just change it to
CHARACTER SET but the next one was more tricky:

The problem I am having is that version 4 fails giving a syntax error
for the single quotation marks. It appears the script (which was written
on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ',
but doing a general search and replace doesn't fix it. I've searched
the online docs regarding quotes but came up short (too many hits with
too many unrelated items) so if anyone knows the exact url that would be
helpful too.

Here's a snippet of the code:
DRP TABLE IF EXISTS `Blog`;
CREATE TABLE `Blog` (
`blog_id` varchar(50) NT NULL,
PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thanks, G.

Graham Reeds wrote:
I have posted a similar question on the blojsom group but I feel I will
have better chance of an answer here.

Blojsom 3 was developed using MySQL5 for it's back end. However the
host I am with uses 4.0.25 and are unwilling to upgrade - which is fair
enough.

So I decided to see what I can do to make the database creation scripts
MySQL4 compliant. The CHARSET was the easy one - just change it to
CHARACTER SET but the next one was more tricky:

The problem I am having is that version 4 fails giving a syntax error
for the single quotation marks. It appears the script (which was written
on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ',
but doing a general search and replace doesn't fix it. I've searched
the online docs regarding quotes but came up short (too many hits with
too many unrelated items) so if anyone knows the exact url that would be
helpful too.

Here's a snippet of the code:
DRP TABLE IF EXISTS `Blog`;
CREATE TABLE `Blog` (
`blog_id` varchar(50) NT NULL,
PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thanks, G.

Single quotes (') are for strings. Backticks (`) are for identifiers. To the
best of my knowledge, this hasn't changed from 4.0 to 5. Hence, using your
table definition,

INSERT INT `Blog` (`blog_id`) VALUES ('This is a blog id');

would be correctly quoted.

Strictly speaking, you only need to quote identifiers which wouldn't otherwise
be allowed (reserved words), so none of the backticks are needed in your example
(or in mine). See the manual for details:

<>
<>

What leads you to believe your error is related to quoting? I think it is more
likely to be something else. If you post the query that gives the error and
include the exact error message, I'm sure someone will be able to identify the
problem.

Michael

Wed, Jun 14, 2006 at 11:00:36PM +0100, Graham Reeds wrote:
Dan Buettner wrote:
>Graham, I seem to recall those single quote marks working without a
>problem on various platforms and versions of MySQL. course they are
generally just a nicety and only required if you are using reserved
>words as table/column/key names, so you could just remove them entirely.
>
>Could you post the exact error message you're getting please?

"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 ''Blog'' at line 1"
when line one is "DRP
TABLE IF EXISTS 'Blog';"

You're not quoting the table name correctly. Identifiers must be quoted
using the backtick (`), if they are quoted at all.

You should be fine with:

DRP TABLE IF EXISTS Blog;

or
DRP TABLE IF EXISTS `Blog`;

>2) You could work around this problem a different way
- create a 5.0 database yourself using the standard supplied scripts,
>perhaps on your own workstation
- dump your new database structure using mysqldump with the flag

which should create a file suitable for piping into a MySQL 4.0 server

That worked.

The gotchas:
* CHARSET wasn't exported as CHARACTER SET - it wasn't exported at all.

Because MySQL 4.0 does not understand character sets at the table level.
(It was a new feature in MySQL 4.1.)

* TYPE is now ENGINE (ie TYPE=InnoDB is now ENGINE=InnoDB)

ENGINE is supported as of MySQL 4.0.18.

* The tables have gone from having an initial capital letter to all
lower caps (I thought v5 was all lower caps anyway?)

systems with case-insensitive file systems, MySQL will lowercase
table and database identifiers.

* Also an error occurred regarding varchar size (it appears v5 can hold
4096 while v4 max is 255).

The maximum size of a VARCHAR is 255 bytes in MySQL 4.0, and about 64K
characters in MySQL 4.1 and later.

Can you load a v5 server with a v4 script?

Yes, you can generally load scripts written for MySQL 4.x into
MySQL 5.x.

You may also want to use the special syntax for specifying
version-specific parts of your query:

Jim Winstead
MySQL Inc.

Dan Buettner wrote:
Graham, I seem to recall those single quote marks working without a
problem on various platforms and versions of MySQL. course they are
generally just a nicety and only required if you are using reserved
words as table/column/key names, so you could just remove them entirely.

Could you post the exact error message you're getting please?

"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 ''Blog'' at line 1"
when line one is "DRP
TABLE IF EXISTS 'Blog';"

Two thoughts -
1) You may have a bogus hidden character in your SQL file. If you look
at it with a text editor (BBEdit, TextWrangler, etc), with the "show
invivisbles" feature on, do you see unusual stuff? Sounds strange but
I've seen stranger.

Took a brief look over it but didn't see anything that I thought looked
untoward. Nor did side by side comparison show up anything.

2) You could work around this problem a different way
- create a 5.0 database yourself using the standard supplied scripts,
perhaps on your own workstation
- dump your new database structure using mysqldump with the flag

which should create a file suitable for piping into a MySQL 4.0 server

That worked.

The gotchas:
* CHARSET wasn't exported as CHARACTER SET - it wasn't exported at all.
* TYPE is now ENGINE (ie TYPE=InnoDB is now ENGINE=InnoDB)
* The tables have gone from having an initial capital letter to all
lower caps (I thought v5 was all lower caps anyway?)
* Also an error occurred regarding varchar size (it appears v5 can hold
4096 while v4 max is 255).

Can you load a v5 server with a v4 script?

Thanks for your help!

Graham.

At 23:00 +0100 14/6/06, Graham Reeds wrote:
>>1) You may have a bogus hidden character in your SQL file. If you
>>look at it with a text editor (BBEdit, TextWrangler, etc), with the
>>"show invivisbles" feature on, do you see unusual stuff? Sounds
>>strange but I've seen stranger.
>
>Took a brief look over it but didn't see anything that I thought
>looked untoward. Nor did side by side comparison show up anything.

I know your problem is now solved, but a quick tip for users of
BBEdit when searching for 'rogue' characters:

Sometimes you can't see them at all in the normal display, but if you
go to the page preferences menu - the third little square from the
left inside the message window - and select Show Invisibles, this can
reveal various oddities. You may find, eg, control characters
appearing as inverted red ?s, or non-breaking spaces (I /think/
that's what they are) appearing as grey bullets where you expect
normal spaces.