1

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™');
INSERT INTO "tickets" VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO "tickets" VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');

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™');
INSERT INTO tickets VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO tickets VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');

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?

John
  • 329
  • 1
  • 4
  • 18
  • problem is only to insert the value in mysql – John Nov 08 '11 at 07:29
  • 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 Nov 08 '11 at 07:32
  • Thanatos :) Question edited, I am newbie in stack overflow so don't no how to ask the questions. next time i remember – John Nov 08 '11 at 08:26

3 Answers3

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', ...)
Marco
  • 56,740
  • 14
  • 129
  • 152
  • Marco:)exactly the same but if i want to insert the same as above then ? – John Nov 08 '11 at 07:35
  • @root: use a `DATE` column and a `TIME` column ;) – Marco Nov 08 '11 at 07:36
  • INSERT INTO tickets VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™'); – John Nov 08 '11 at 07:37
  • i got an error :-there can be only one TIMESTAMP column with current_timestamp in default of on update clause – John Nov 08 '11 at 07:39
  • @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... – Marco Nov 08 '11 at 07:40
  • ok so can you plz edit in the answer what you are doing in your codes ... after all i am trying again – John Nov 08 '11 at 07:41
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.

Thanatos
  • 42,585
  • 14
  • 91
  • 146
  • +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. – mu is too short Nov 08 '11 at 07:33
  • 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.) – Thanatos Nov 08 '11 at 07:35
  • Anything less than blowing up in your face and screaming bloody murder is too silent for me. You can [tighten it up](http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_strict_all_tables) though but fewer people do that than pay attention to the warning (wherever it goes). – mu is too short Nov 08 '11 at 07:55
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,

...

Community
  • 1
  • 1
joe
  • 51
  • 1
  • :) I m trying each and everything not working lol, You can also check You ans in your prompt. Thanks – John Nov 08 '11 at 14:12