Blogspot - mysql-tips.blogspot.com - MySQL Tips
General Information:
Latest News:
PHPBB Convert poster_ip to real IP address in phpbb_posts table 3 Jan 2009 | 08:31 am
Let's say you see an offending user on your website and you have their IP and you'd like to see if they're a registered poster on your PHPBB forum. The problem is that the poster_ip field in the phpbb...
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 14 May 2005 | 12:06 pm
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...
How to show warning messages for LOAD DATA INFILE 28 Apr 2005 | 12:13 pm
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...
MySQL difference between dates in number of days 17 Apr 2005 | 11:09 pm
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))
Alter table auto_increment examples 17 Apr 2005 | 11:08 pm
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
MySQL get last 24 hours example SQL 17 Apr 2005 | 11:08 pm
select count(*) as cnt from log where date >= DATE_SUB(CURDATE(),INTERVAL 1 DAY);
Resolving ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key 17 Apr 2005 | 10:51 pm
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 ad...
Export a table in mysql to a file in csv format example 17 Apr 2005 | 10:50 pm
select * into outfile '/tmp/zips.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from zips;
MySQL find duplicate repords example 17 Apr 2005 | 10:49 pm
select address, count(address) as cnt from mailing_list group by address having cnt > 1 order by cnt;
MySQL find last digit = number example SQL 17 Apr 2005 | 10:49 pm
select address from $db.$table where ascii(right(address, 1)) >= 48 and ascii(right(address, 1))
