mySQL adding column breaks data

Question

I have succesfully created a database in mySQL using the commandline and imported some data. It currently looks like this..

desc data;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| code     | varchar(10) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

SELECT * FROM data;
    +----+----------+
    | id | code     |
    +----+----------+
     | 1 | 123abc
     | 2 | 234def
     | 3 | 567ghi
     | 4 | 890jkl

I would like to add a column to the table called timestamp, I am doing this with..

alter table data add timestamp VARCHAR(20);

But then my table looks like this...

desc data;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| code      | varchar(10) | YES  |     | NULL    |                |
| timestamp | varchar(20) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

SELECT * FROM data;
    +----+----------+-----------+
    | id | code     | timestamp |
    +----+----------+-----------+
     | NULL       |
     | NULL       |
     | NULL       |
     | NULL       |

Where am I going wrong?

TIMESTAMP is a Keyword so you must put it in backticks if you want to use as fieldname. -alter table data add `timestamp` VARCHAR(20); - i cant show it in comment — Bernd Buffen, Mar 10 '16 at 20:45
There's an example here. http://stackoverflow.com/questions/17541312/alter-table-add-multiple-columns-after-column1 It's adding multiple columns, but should help. — BryanT, Mar 10 '16 at 21:05
Could you please supply the output of `describe data`? And how did you _list_ the table's content? `select * from data`? MySQL formats the output different from what you showed. — PerlDuck, Mar 10 '16 at 21:09
I doubt your output does reflect `select * from data`. It probably is `select timestamp from data`, right? And you're wondering about the NULLs for timestamp? When adding columns to a table, MySQLs fills them with a default value and in your case that is NULL so all new rows have timestamp=NULL. — PerlDuck, Mar 10 '16 at 21:17

Bernd Buffen · Accepted Answer · 2016-03-10T21:11:48.937

3

here you can see the backticks

alter table `data` add `timestamp` VARCHAR(20);

SAMPLE

MariaDB []> desc data;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| e     | enum('x1','x2','x3') | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

MariaDB []> alter table `data` add `timestamp` VARCHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB []> desc data;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| e         | enum('x1','x2','x3') | YES  |     | NULL    |                |
| timestamp | varchar(20)          | YES  |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Table Data

MariaDB [who]> select * from `data`;
+----+------+-----------+
| id | e    | timestamp |
+----+------+-----------+
|  1 | x1   | NULL      |
|  2 | x2   | NULL      |
+----+------+-----------+
2 rows in set (0.00 sec)

MariaDB [who]>

edited Mar 10 '16 at 21:11

answered Mar 10 '16 at 20:47

Bernd Buffen

14,525
2
24
39

That gives me 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 ''timestamp' VARCHAR(20)' at line 1 – fightstarr20 Mar 10 '16 at 20:51
@fightstarr20 - UPS, my mistake data also reserved. so you must also quote it with backticks. please let me know if it now work – Bernd Buffen Mar 10 '16 at 20:57
It still gives me the same result, breaks the data – fightstarr20 Mar 10 '16 at 21:00
1

did you use backticks ? copy it from my answer please and test it. i have add in my answer – Bernd Buffen Mar 10 '16 at 21:01
Your example does not show where my error is displayed, if you list the data then it shows broken. – fightstarr20 Mar 10 '16 at 21:05
@fightstarr20 True. Because it is syntactically correct. No errors. – PerlDuck Mar 10 '16 at 21:10
I have updated the original post with all relevant output – fightstarr20 Mar 10 '16 at 21:13
1

try : select * from `data`\G it look like a display error or control char in some field – Bernd Buffen Mar 10 '16 at 21:20
You are right, things look correct if I use \G. The columns are not long so I wonder why it is having display issues? – fightstarr20 Mar 10 '16 at 21:25
2

i think there are carriage return CR in your varchar field. so the display start again at the begin of a line and override the output. Truncate the table and insert new data – Bernd Buffen Mar 10 '16 at 21:29
@fightstarr20 Now you made me curious: What did you use before (instead of `\G`)? I usually use a semicolon `;` to end statements and thought they were interchangeable. Bernd: _That_ would be an explanation (CRs)! It also explains why `select * from data` in the first case looked odd. – PerlDuck Mar 10 '16 at 21:30
1

so...., if you use: SELECT * FROM `data`; the client will display each row like this | 1 | hello | NULL | but when in the second field is a CR control char. it will display | 1 | hello an when CR outputs the cursor go back to the begin of the line and the last field overrides the output with blank and NULL . so you cant see the first output – Bernd Buffen Mar 10 '16 at 21:36
if you use a linux system you can run this samples from commandline: echo -e "ABCD 123"; you see all echo -e "ABCD\r123"; - 123 overrides ABC – Bernd Buffen Mar 10 '16 at 21:40
Ahhh, I just googled. From the [docs](http://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html): _\G shows the result **vertically**_. That's the trick! I knew \G but never used it because I thought that's only for crap keyboards that don't have a `;` handy. Thanks for that! – PerlDuck Mar 10 '16 at 21:40
1

Thanks Bernd, it was an issue with my original data. I was focusing on the wrong thing all along! – fightstarr20 Mar 10 '16 at 21:43

mySQL adding column breaks data

1 Answers1

Linked