|
Home > Archive > MySQL Server Forum > June 2005 > Mysql console parsing error: 1064
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Mysql console parsing error: 1064
|
|
| wideangle@gmail.com 2005-06-10, 3:23 am |
| 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 2005-06-10, 9:23 am |
| >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 2005-06-10, 11:23 am |
| 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 2005-06-10, 11:23 am |
| wideangle@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 2005-06-10, 11:23 am |
| > 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:930
6/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 2005-06-10, 11:23 am |
| 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:930
6/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 2005-06-10, 11:23 am |
| 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 2005-06-10, 11:23 am |
| wideangle@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 2005-06-10, 8:23 pm |
| 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.
| |
|
| 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 2005-06-10, 8:23 pm |
| 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 2005-06-11, 9:23 am |
| > 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
| |
|
| 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 2005-06-12, 9:23 am |
| 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
|
|
|
|
|