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_captio n` tinytext NOT NULL,
-> `mytable_full_b ody` text NOT NULL,
-> PRIMARY KEY (`mytable_id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Testin g 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='Testin g 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='Testin g 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_captio n` tinytext NOT NULL,
`mytable_full_b ody` text NOT NULL,
PRIMARY KEY (`mytable_id`)
) ENGINE=MyISAM COMMENT='Testin g 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=FEDERATE D
-> DEFAULT CHARSET=latin1
-> COMMENT='mysql://root@remote_hos t: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=FEDERATE D
-> DEFAULT CHARSET=latin1
-> COMMENT='mysql://root@remote_hos t: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='Testin g 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_captio n` tinytext NOT NULL,
-> `mytable_full_b ody` 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_captio n` tinytext NOT NULL,
-> `mytable_full_b ody` text NOT NULL,
-> PRIMARY KEY (`mytable_id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)