Mysql console parsing error: 1064

[wideangle]

Hello there!

I know it's stupid, but when creating a table in a mysql (win32)
database, it won't let me create this "mytable". Here goes my
ER_PARSE_ERROR.

mysql> CREATE TABLE `mytable` (
-> `mytable_id` int(25) NOT NULL auto_increment,
-> `mytable_title` varchar(100) NOT NULL default '',
-> `mytable_dts` varchar(25) NOT NULL default '',
-> `mytable_caption` tinytext NOT NULL,
-> `mytable_full_body` text NOT NULL,
-> PRIMARY KEY (`mytable_id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Testing Full
Text Search Functionality.';

ERROR 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 COMMENT='Testing Full Text Search
Functi

At the same time while inserting a large text as pasted to mysql
console, only couple of lines gets there.

INSERT INTO `mytable` VALUES (1, 'Mozilla Firefox is Cool!',
'1111813200', 'Mozilla Firefox blah blah .... more 500 words ');

I guess, the mysql console is limiting the size while inputting. Do I
have to edit that my.cnf file?

Could you please point where am I heading?
mysql> status
--------------
mysql Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)

TIA

--
Raqueeb Hassan
Bangladesh

[Gordon Burditt]

>At the same time while inserting a large text as pasted to mysql

console, only couple of lines gets there.

INSERT INTO `mytable` VALUES (1, 'Mozilla Firefox is Cool!',
'1111813200', 'Mozilla Firefox blah blah .... more 500 words ');

I guess, the mysql console is limiting the size while inputting. Do I
have to edit that my.cnf file?

A field of type 'text' is limited to 255 chars, I believe.
It's not the console limiting you. Try 'longtext'.

Gordon L. Burditt

[Aggro]

Gordon Burditt wrote:

A field of type 'text' is limited to 255 chars, I believe.

That is not true. text field can hold a lot more than that. I have for
example saved html codes of large web pages into text fields.

[Aggro]

wi*******@gmail.com wrote:

-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Testing Full
"Improved support for character set handling was added to MySQL in
Version 4.1. The features described here are as implemented in MySQL
4.1.1. (MySQL 4.1.0 has some but not all of these features, and some of
them are implemented differently.)"
http://dev.mysql.com/doc/mysql/en/charset.html

If you remove the character set part, it should work (without character
set):

CREATE TABLE `mytable` (
`mytable_id` int(25) NOT NULL auto_increment,
`mytable_title` varchar(100) NOT NULL default '',
`mytable_dts` varchar(25) NOT NULL default '',
`mytable_caption` tinytext NOT NULL,
`mytable_full_body` text NOT NULL,
PRIMARY KEY (`mytable_id`)
) ENGINE=MyISAM COMMENT='Testing Full
Text Search Functionality.';
At the same time while inserting a large text as pasted to mysql
console, only couple of lines gets there.

INSERT INTO `mytable` VALUES (1, 'Mozilla Firefox is Cool!',
'1111813200', 'Mozilla Firefox blah blah .... more 500 words ');

I guess, the mysql console is limiting the size while inputting. Do I
have to edit that my.cnf file?

When I insert large amounts of data to database I often save the insert
queries into file and then call:

mysql -u username -p databasename < queryfile.sql

[Raqueeb Hassan]

> That is not true. text field can hold a lot more than that. I have for

example saved html codes of large web pages into text fields.

Well, I think the trouble is elsewhere ..... when I try using another
example from the manual, it says the same thing!

mysql> CREATE TABLE federated_table (
-> id int(20) NOT NULL auto_increment,
-> name varchar(32) NOT NULL default '',
-> other int(20) NOT NULL default '0',
-> PRIMARY KEY (id),
-> KEY name (name),
-> KEY other_key (other)
-> )
-> ENGINE=FEDERATED
-> DEFAULT CHARSET=latin1
-> COMMENT='mysql://root@remote_host:9306/federated/test_table';

ERROR 1064: You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the right syntax to u
se near 'FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_h
Any comment?
TIA
--
Raqueeb Hassan
Bangladesh

[Raqueeb Hassan]

Dear Aggro,

Thanks for the reply. Well, I think the trouble is elsewhere ..... when
I try using another example from the manual, it says the same thing!

mysql> CREATE TABLE federated_table (
-> id int(20) NOT NULL auto_increment,
-> name varchar(32) NOT NULL default '',
-> other int(20) NOT NULL default '0',
-> PRIMARY KEY (id),
-> KEY name (name),
-> KEY other_key (other)
-> )
-> ENGINE=FEDERATED
-> DEFAULT CHARSET=latin1
-> COMMENT='mysql://root@remote_host:9306/federated/test_table';

ERROR 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 'FEDERATED DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_h

mysql -u username -p databasename < queryfile.sql

So, I can use even multiple INSERTS from that queryfile.sql ...

INSERT INTO `mytable` VALUES (1, 'Mozilla Firefox is Cool!',
'1111813200', 'Mozilla Firefox blah blah .... more 500 words ');
INSERT INTO `mytable` VALUES (2, 'Test browser', '1111813222', 'Another
browser blah blah .... more 500 words ');

TIA

--
Raqueeb Hassan
Bangladesh

[Aggro]

Raqueeb Hassan wrote:

Thanks for the reply. Well, I think the trouble is elsewhere ..... when
I try using another example from the manual, it says the same thing! -> DEFAULT CHARSET=latin1
Of course it does, because this example has also CHARSET in it. Like I
said "If you remove the character set part, it should work". I'm pretty
sure your problem is that 4.0 doesn't support CHARSET for table. Upgrade
to 4.11 if you can and that should solve the problem.
So, I can use even multiple INSERTS from that queryfile.sql ...

Yes you can. I often use it to create databases, tables and insert data
to them. With mysqldump you can create a copy of your database(s) and
using the syntax above, you can then insert that data back to database.
But nothing stops you from writing the queries manually.

[Bill Karwin]

wi*******@gmail.com wrote:

-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Testing Full

Check the syntax for 'table_option', on the web page:
http://dev.mysql.com/doc/mysql/en/create-table.html

The syntax you should use is:
DEFAULT CHARACTER SET latin1

Using the word CHARSET is not included in the syntax. Neither should
you use the "=" character.

Regards,
Bill K.

[Bill Karwin]

I double-checked the manual, and there are a couple of errors in the
docs regarding "CHARSET" versus "CHARACTER SET" syntax.

I've logged a doc bug with the MySQL website.

http://bugs.mysql.com/bug.php?id=11251

Regards,
Bill K.

[Aggro]

Bill Karwin wrote:

Check the syntax for 'table_option', on the web page:
http://dev.mysql.com/doc/mysql/en/create-table.html

The syntax you should use is:
DEFAULT CHARACTER SET latin1

Did you actually test that it works on MySQL version 4.0? Because I
can't get it to work on my 4.0:

mysql> CREATE TABLE `mytable` (
-> `mytable_id` int(25) NOT NULL auto_increment,
-> `mytable_title` varchar(100) NOT NULL default '',
-> `mytable_dts` varchar(25) NOT NULL default '',
-> `mytable_caption` tinytext NOT NULL,
-> `mytable_full_body` text NOT NULL,
-> PRIMARY KEY (`mytable_id`)
-> ) ENGINE=MyISAM DEFAULT CHARACTER SET latin1;
ERROR 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 CHARACTER SET latin1' at line 8
mysql>
# It only works without CHARACTER SET:

mysql> CREATE TABLE `mytable` (
-> `mytable_id` int(25) NOT NULL auto_increment,
-> `mytable_title` varchar(100) NOT NULL default '',
-> `mytable_dts` varchar(25) NOT NULL default '',
-> `mytable_caption` tinytext NOT NULL,
-> `mytable_full_body` text NOT NULL,
-> PRIMARY KEY (`mytable_id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

[Bill Karwin]

Aggro wrote:

Did you actually test that it works on MySQL version 4.0? Because I
can't get it to work on my 4.0:

Sorry, I bet you're right. I only tried it on 4.1 and 5.0, and forgot
that the original poster is using 4.0.

And http://dev.mysql.com/doc/mysql/en/news-4-1-x.html states that
defining character sets at the database, table, and column level is a
new feature in MySQL 4.1.

In order to do what he wants, he's going to have to upgrade to MySQL 4.1.

Regards,
Bill

[Raqueeb Hassan]

> In order to do what he wants, he's going to have to upgrade to MySQL 4.1.

<snip>

Thanks all of you for the support. I'll upgrade that pretty soon. Right
now, when I'm trying to INSERT INTO something in a table like ....

mysql> INSERT INTO `mytable` VALUES (3, 'MySQL Database Server',
'1111813200', 'MySQL is the greatest database server int he world!',
'The MySQL database server is the world\'s most popular open source
database. Over six million installations use MySQL to power high-volume
Web sites and other critical business systems - including
industry-leaders like The Associated Press, Yahoo, NASA, Sabre Holdings
and Suzuki.');
It says .....

mysql> INSERT INTO `mytable` VALUES (3, 'MySQL Database Server',
'1111813200', 'MySQL is the greatest database server int he world!',
'The
MySQL database server is the world\'s most popular open source
database. Over six million installations use MySQL to power
'> '>

As consulting the manual, '> is waiting for next line, collecting a
string that begins with a single quote. Now, how do I enter the rest of
the lines? Do I have to upgrade?
TIA

--
Raqueeb Hassan
Bangladesh

[Aggro]

Raqueeb Hassan wrote:

mysql> INSERT INTO `mytable` VALUES (3, 'MySQL Database Server',
'1111813200', 'MySQL is the greatest database server int he world!',
'The MySQL database server is the world\'s most popular open source
database. Over six million installations use MySQL to power high-volume
Web sites and other critical business systems - including
industry-leaders like The Associated Press, Yahoo, NASA, Sabre Holdings
and Suzuki.');
There is nothing wrong with that query. I tried it on 4.0 and it was
inserted into the table.

As consulting the manual, '> is waiting for next line, collecting a
string that begins with a single quote. Now, how do I enter the rest of
the lines? Do I have to upgrade?

You just write them. Ignore the "'>". It just means that you have used
single "'" character in your query and you have to close it at some
point, which you will do right after "Suzuki." in your query.

I just copied your query and pasted it to mysql console and it worked
directly that way. But you can manually type it if you like to.

[Raqueeb Hassan]

Thanks for the reply. I think there's something weird going on in
screen buffer in win32 versions. Is it bug or what?

Anyway, right now, I'm loading from the file. Thanks for the support.
--
--
Raqueeb Hassan
Bangladesh