top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

alter table modify syntax error in MySql

+1 vote
432 views

I'm trying to use a very basic alter table command to position a column after another column.

mysql> describe car_table;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| car_id | int(11) | NO | PRI | NULL | auto_increment |
| vin | varchar(17) | YES | | NULL | |
| color | varchar(10) | YES | | NULL | |
| year | decimal(4,0) | YES | | NULL | |
| make | varchar(10) | YES | | NULL | |
| model | varchar(20) | YES | | NULL | |
| howmuch | decimal(5,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+

I am trying to position the 'color' column after the 'model' column with the following command:

mysql> alter table car_table modify column color after model;

And I'm getting the following error:

ERROR 1064 (42000): 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 'after model' at line 1

I'm just wondering what I'm doing wrong here

posted Jun 28, 2014 by anonymous

Looking for an answer?  Promote on:
Facebook Share Button Twitter Share Button LinkedIn Share Button
You can try
alter table car_table modify column color varchar(10) after model;

Similar Questions
0 votes

I would like to change the layout of my production database, I would like to add a column to an existing table. As I think before the ALTER TABLE statement all access to the database should be denied/ended, then the ALTER TABLE should be performed, and then user/applications should be able to use the database once again.

My tables is quite small ~4MB data & indexes.

So is the ALTER TABLE on a running/opened to clients database/table desirable or should it be run when all access to the database/table is forbidden?

0 votes

I am using MySQL Proxy. When I type command like "show databa;" I should get error messages from MySQL server, which is forwarded by MySQL Proxy. How can I access this error message, modify it, and then send the modified error message to the client?

0 votes

I have a mysql query that would not execute and I would like to display information about why that's happening.

$myQuery= $mysqli->query("UPDATE table SET id = 1 WHERE id = 3");
if(!$myQuery) //If query couldnt be executed
{
echo $mysqli->error; //Display information about why wasnt executed (eg. Error: couldnt find table)
}
+2 votes

I have a rails application, in which I am using delayed_job_active_record gem for running background jobs. While using the method .delay with an object, I am getting the following mysql error:

"INCORRECT STRING VALUE: \'XE2X9CX93"X0A ...\' FOR COLUMN \'HANDLER\' AT ROW 1"

I already searched for the above error and found that its because of the difference in encoding in mysql and rails. The solution suggested by many programmers is to alter the encoding in mysql database to utf8. But I also read that MySQL's utf8 charset only partially implements proper UTF-8 encoding. It can only store UTF-8-encoded symbols that consist of one to three bytes; encoded symbols that take up four bytes aren't supported. Which might cause trouble in some other cases. Also, when I tried to insert the value directly in mysql, it worked like a charm. Suggesting that the issue might lie elsewhere. So, can anyone please suggest the right method to rectify this problem?

Any help would be greatly appreciated.

+2 votes

I am running MySQL 5.5.31 on FreeBSD 9.2. I have a web server with a miss-configured service that generates faulty connections. After a while, MySQl blocks any connection from the web server.

At some stage, I had set-up a script that would browse syslog log and look for a string like "Host 'xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'" and would then issue a mysqladmin flush-hosts.

But after an upgrade, MySQl stopped reporting to syslog. Any help will be greatly appreciated.

...