MySQL TIPS and TRICKS

[zone]

HI SF
Here we learn some basic tips relating to MySQL. Lets our tutorial of MySQL Tips and Tricks.

---

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
If in Mysql get the message "ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key" when trying to drop a primary key, then do the following:

(let's say the table is 'your_table' and the primary key column w/ auto_increment is 'the_column'):

alter table your_table change the_column the_column int unsigned;

to remove the auto_increment, then do a:

alter table your_table drop primary key;

---

How to show warning messages for LOAD DATA INFILE
Ever wondered what the warning messages were when you did a load data infile in MySQL? Well in MySQL 4.1.0 and greater you can by issuing a "SHOW WARNINGS" command at the mysql console- e.g.

mydb1707>load data infile '/tmp/people.txt'
-> into table webapps.merchants
-> fields terminated by '\t'
-> lines terminated by '\n'
-> ignore 1 lines;

Query OK, 103 rows affected, 14 warnings (0.06 sec)
Records: 103 Deleted: 0 Skipped: 0 Warnings: 14

mydb1707>show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'state' at row 13 |
| Warning | 1265 | Data truncated for column 'phone' at row 13 |
| Warning | 1265 | Data truncated for column 'state' at row 14 |
| Warning | 1265 | Data truncated for column 'state' at row 52 |
| Warning | 1265 | Data truncated for column 'phone' at row 59 |
| Warning | 1265 | Data truncated for column 'phone' at row 60 |
| Warning | 1265 | Data truncated for column 'phone' at row 61 |
| Warning | 1265 | Data truncated for column 'state' at row 71 |
| Warning | 1265 | Data truncated for column 'phone' at row 72 |
| Warning | 1265 | Data truncated for column 'phone' at row 78 |
| Warning | 1265 | Data truncated for column 'phone' at row 82 |
| Warning | 1265 | Data truncated for column 'phone' at row 86 |
| Warning | 1265 | Data truncated for column 'state' at row 92 |
| Warning | 1265 | Data truncated for column 'phone' at row 100 |
+---------+------+----------------------------------------------+
14 rows in set (0.02 sec)


Check out http://dev.mysql.com/doc/mysql/en/show-warnings.html for the full details.

---

MySQL difference between dates in number of days
To get a date difference in days in Mysql version before 4.1 (where you can use the datediff() function instead), do the following to calculate date difference:

select (TO_DAYS(date1)-TO_DAYS(date2))

---

MySQL get last 24 hours example SQL
select count(*) as cnt from log where date >= DATE_SUB(CURDATE(),INTERVAL 1 DAY);

---

Alter table auto_increment examples
ALTER TABLE tbl_name AUTO_INCREMENT = 100
will start your records at 100

ALTER TABLE tbl_name AUTO_INCREMENT = 1000
will start your records at 1000

---

Resolving ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table test add orig_order int unsigned auto_increment;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table test add orig_order int unsigned auto_increment, add key(orig_order);
Query OK, 1221 rows affected (0.10 sec)
Records: 1221 Duplicates: 0 Warnings: 0

---

SOURCE AND CREDITS:
---------------------
http://mysql-tips.blogspot.com/

[flAmingw0rm]

Nice tips dude, i regular visit MySQL-tips and it helps me a lot.