itlivewire dev blogJust another web development blog…

Example errors:

“Duplicate entry ’123711155′ for key ‘PRIMARY’” or “Duplicate entry ’14676163′ for key ’1′”

Most likely this happens for tables with more than 5 million records

THE FIX

The fix is to use repair table

REPAIR TABLE <TABLE_NAME>;

Note: Sometimes the duplicate error is also caused by your field type having the maximum value already, for example in tinyint which has the maximum value of 127 so it better to use bigint for your auto ids, if this is not the case you can try the solution above.

Lets say we have this table the ORDERS TABLE

ID customer ordered
—————————-
1111 – John – pizza
2222 – Pete – salad
3333 – Rach – pizza
4444 – Maxx – pizza
5555 – Zigg – rice
1212 – Vinc – salad
4545 – Grac – sushi
1313 – Mark – cake
2424 – Phils – salad

We only want to show items which has been ordered more than 2 times here is our query

—————————————————————————————————————————
SELECT *,count(ordered) as cnt FROM ORDERS group by ordered having cnt > 2
—————————————————————————————————————————-
result:
1111 – John – pizza
3333 – Rach – pizza
4444 – Maxx – pizza
2222 – Pete – salad
1212 – Vinc – salad
2424 – Phils – salad

We use here the having keyword

note: having will work if you have group by on your query

On snow leopard the bundled version of PHP installed is 5.3 And it’s new PHP mysql driver is mysqlnd not the old one (ext/mysql)

This might cause the following error:
mysqlnd cannot connect to MySQL 4.1+ using old authentication

Solution:
type in mysql

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

Findings:
Because mysqlnd has a bigger password hash it will not be compatible anymore with the password written beforehand.

Ref:
http://dev.mysql.com/doc/refman/5.1/en/old-client.html

Error Code : 1349
View’s SELECT contains a subquery in the FROM clause

It’s a long standing MYSQL BUG

You cannot do this!
*******************************************************************
DROP VIEW IF EXISTS `db`.`empmain_vw`;
CREATE
VIEW `db`.`empmain_vw`
AS
select * from (select * from employees group by salary) a;
********************************************************************
In the code above we are trying to build a view called empmain_vw
But an error appears
Error Code : 1349
View’s SELECT contains a subquery in the FROM clause

This means you cannot create a view with a SUBQUERY in the FROM clause, no no no…… ohh! no!

Simple workaround (double task) is to create a view of the subquery first, lets create empsalary_vw
*******************************************************************
DROP VIEW IF EXISTS `db`.`empsalary_vw`;
CREATE
VIEW `db`.`empsalary_vw`
AS
select * from employees group by salary;
********************************************************************

Then combine it with your previously wanna build view, so lets create again empmain_vw
*******************************************************************
DROP VIEW IF EXISTS `db`.`empmain_vw`;
CREATE
VIEW `db`.`empmain_vw`
AS
select * from empsalary_vw;
********************************************************************

This one will work now coz there is no subquery in the FROM clause

The bug has been there i think for a long time, other DBMS is capable creating views even with subquery on clause oracle and etc.