Convert from SQLite to 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 INSERT operation is:

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;');

Now the same table I created 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 operation for MySQL is:

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 :-there can be only one TIMESTAMP column with current_timestamp in default of on update clause

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

Right, and if you can't INSERT into MySQL, MySQL is without doubt giving you an error message as to why it doesn't like your INSERT statements. Read it, understand it, and at least post it here, so that we might help you. — Thanatos
– Thanatos, Commented Nov 8, 2011 at 7:32
Thanatos :) Question edited, I am newbie in stack overflow so don't no how to ask the questions. next time i remember — John
– John, Commented Nov 8, 2011 at 8:26

Marco · Accepted Answer · 2011-11-08 07:26:14Z

1

In SQLite you have two columns

[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,

while on MySQL you have only one

date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,

and you're trying to insert two values on it...

You should try

INSERT INTO tickets VALUES(..., '2010-11-30 17:46:39', ...)

answered Nov 8, 2011 at 7:26

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Over a year ago

Marco:)exactly the same but if i want to insert the same as above then ?

Marco Over a year ago

@root: use a DATE column and a TIME column ;)

John Over a year ago

INSERT INTO tickets VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™');

John Over a year ago

i got an error :-there can be only one TIMESTAMP column with current_timestamp in default of on update clause

Marco Over a year ago

@root: I've just created a table with two fields (one DATE and one TIME) then I tried `INSERT INTO tb VALUES('2010-11-30', '17:46:39') and it worked...

|

Thanatos · Accepted Answer · 2011-11-08 07:27:07Z

1

At first glace, your varchar column is size 10, but you are inserting greater than length 10 data into it. Make sure your varchar column is wide enough for your data.

answered Nov 8, 2011 at 7:27

Thanatos

44.7k17 gold badges99 silver badges152 bronze badges

3 Comments

mu is too short Over a year ago

+1 for noting OP's next problem (i.e. MySQL evil silent data truncation) after they get the right number of VALUES for the columns.

Thanatos Over a year ago

It's not entirely silent, is it? I think it at least spews a warning somewhere. (Although, personally, I'd like to have it abort in such a situation.)

mu is too short Over a year ago

Anything less than blowing up in your face and screaming bloody murder is too silent for me. You can tighten it up though but fewer people do that than pay attention to the warning (wherever it goes).

Community · Accepted Answer · 2017-05-23 10:24:21Z

0

Your MySQL Schema appears to be incorrect for what you're trying to insert.

Excerpt from this post: Should I use field 'datetime' or 'timestamp'? ...Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.

Change your MySQL schema to something closer to:

...

phone VARCHAR(12) NULL,
date DATE DEFAULT CURRENT_DATE NULL,
time TIME DEFAULT CURRENT_TIME NULL,

...

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Nov 8, 2011 at 11:31

joe

511 bronze badge

1 Comment

John Over a year ago

:) I m trying each and everything not working lol, You can also check You ans in your prompt. Thanks

Collectives™ on Stack Overflow

Convert from SQLite to MySQL

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related