+- Support Forums (https://www.supportforums.net)
+-- Forum: Categories (https://www.supportforums.net/forumdisplay.php?fid=87)
+--- Forum: Coding Support Forums (https://www.supportforums.net/forumdisplay.php?fid=18)
+---- Forum: Database Programming (https://www.supportforums.net/forumdisplay.php?fid=28)
+---- Thread: MySQL TIPS and TRICKS (/showthread.php?tid=2591)
MySQL TIPS and TRICKS - zone - 11-06-2009
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/
RE: MySQL TIPS and TRICKS - flAmingw0rm - 11-15-2009
Nice tips dude, i regular visit MySQL-tips and it helps me a lot.