INSERT works in SQLite but not MySQL

Question

This table I created in a SQLite database:

CREATE TABLE [tickets] (
[id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[coupon_id] INTEGER  NULL,
[size] FLOAT  NULL,
[phone] VARCHAR(10)  NULL,
[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,
[product] TEXT  NULL
);

Now I convert this table in mysql

CREATE TABLE tickets (
id INTEGER  PRIMARY KEY AUTO_INCREMENT NOT NULL,
coupon_id INTEGER  NULL,
size FLOAT  NULL,
phone VARCHAR(10)  NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
product TEXT  NULL
);

INSERT INTO "tickets" VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting&trade;');
INSERT INTO "tickets" VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider&trade;');
INSERT INTO "tickets" VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High&trade;');

When I am inserting those values I got an error ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

I am able to insert all those values in SQLite, but I am not able to insert all those values into MySQL. Help me?

Are you sure you get the error in the `INSERT` clauses? It's more feasible to get it in the `CREATE TABLE` clause since MySQL tables have the limitation explained in the error message. — Álvaro González, Nov 08 '11 at 09:29
http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0 — Dynamikus, Nov 08 '11 at 09:32
Álvaro G. Vicario:) yes i got the Error 1293 (HY000) Incorrect table Definition; there can be only one TIMESTAMP column with current_timestamp in default of on update clause — John, Nov 08 '11 at 09:32
@root, insert is not a table definition. (do not overflow stack as root:)) — Michael Krelin - hacker, Nov 08 '11 at 09:35

Ziminji · Answer 1 · 2011-11-08T09:43:47.223

0

As far as I know, MySQL will not allow this. If you want to insert the current time into the second field, consider using a trigger to update the record when inserting.

DELIMITER $$
CREATE TRIGGER curtime BEFORE INSERT ON tickets FOR EACH ROW
BEGIN
SET NEW.time = CURRENT_TIMESTAMP();
END;
$$
DELIMITER ;

edited Nov 08 '11 at 09:43

answered Nov 08 '11 at 09:33

Ziminji

1,286
1
14
18

Or switch to a decent RDBMS :) – Michael Krelin - hacker Nov 08 '11 at 09:35
Creating a trigger is like creating a table in that you run the SQL statement in the SQL editor. For MySQL, try using Sequel Pro or phpmyadmin. – Ziminji Nov 08 '11 at 09:55
Ziminji :) i have phpmyadmin and wamp server both right now so how can i run this code i am not able to run this table, can You please edit your ans, I am not able to run your code – John Nov 08 '11 at 09:58
Like rodneyrehm suggested, change the default value (at least on the second field, i.e. "time") to time TIMESTAMP DEFAULT NULL. That should allow you to create the table. – Ziminji Nov 08 '11 at 10:03
Ziminji:)I am using this date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, time TIMESTAMP DEFAULT NULL, but it gives an error Invalid default value for 'time' – John Nov 08 '11 at 10:07
Try '0000-00-00 00:00:00' for the default value. If that doesn't work, try switching the data type to a datetime field and then set the default with this value. – Ziminji Nov 08 '11 at 10:09
Ziminji :) right now i m facing trouble in creating a table, so how can i insert those values – John Nov 08 '11 at 10:11
I would recommend you using the table creator in phpmyadmin rather than the trying to hard code the SQL statement for the create table. You can do that by scrolling to the bottom of the page and look for "Create new table on database" and the enter the number of fields. Then, set up the fields using its built in UI. – Ziminji Nov 08 '11 at 10:17
Ziminji:) yes it is also possible wait – John Nov 08 '11 at 10:19
Ziminji:) what i can select for date and time from UI??? means type and all those things in UI of phpmyadmin – John Nov 08 '11 at 10:24
Did you get it? Just place in the fields, types, and default values from your create table statement into the UI's corresponding fields. Datetime and Timestamp do not require lengths. The rest of textboxes should be self explanatory. – Ziminji Nov 08 '11 at 10:50

score 0 · Answer 2 · answered Nov 08 '11 at 09:35

0

You might want to convert

date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,

to

created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,

and concatenate the values from SQLite, or alter their type

date date DEFAULT NULL,
time time DEFAULT NULL,

and add the current date/time with a trigger.

answered Nov 08 '11 at 09:35

rodneyrehm

13,442
1
40
56

rodneyrehm:) can you please put this in my coding i m newbie, and i don't no more about trigger – John Nov 08 '11 at 09:47

INSERT works in SQLite but not MySQL

2 Answers2