MySQL Tips

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_posts table is encoded. Here's an example to convert...

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'):...

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...

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))