MySQL "ERROR 1046 (3D000): No database selected" on update query

database, mysql, php

Solution

You have fields named incorrectly, but even if you correct them, this is a bug in `MySQL` that won't let you do it if you don't have default database.

update  test.object1 p
join    (
        select  ur.id_object1, sum(ur.score * ur.weight) as total, count(*) as weight
        from    (
                select  lur.*
                from    (
                        select s.id_object1, s.id_object2, s.dt, s.score, 1 as weight
                        from   test.score as s
                        join   test.object1 as o1
                        using  (id_object1)
                        where  s.dt > o1.dt
                        order by
                               s.id_object1, s.id_object2, s.dt desc
                        ) as lur
                group by
                        lur.id_object1, lur.id_object1, date(lur.dt)
                order by
                        lur.id_object1, lur.id_object1
                ) as ur
        group by ur.id_object1
        ) as r
USING   (id_object1)
SET     p.total = p.total + r.total,
        p.weight = p.weight + r.weight,
        p.dt = now();

The problem is specific to `UPDATE` with double-nested queries and no default database (`SELECT` or single-nested queries or default database work fine)

Problem

I have an UPDATE query where I explicitely reference the database, but MySQL still complains with the message: `ERROR 1046 (3D000): No database selected`. Other queries that are similar of structure, but use an INSERT work fine. Other queries that only perform SELECTs also run fine. To repeat the problem in a test case, try running these queries: ``` create table test.object1 ( id_object1 int unsigned not null auto_increment, total int, weight int, dt datetime, primary key (id_object1) ) engine=InnoDB; create table test.object2 ( id_object2 int unsigned not null auto_increment, primary key (id_object2) ) engine=InnoDB; create table test.score ( id_object1 int unsigned not null, id_object2 int unsigned not null, dt datetime, score float, primary key (id_object1, id_object2), constraint fk_object1 foreign key (id_object1) references object1 (id_object1), constraint fk_object2 foreign key (id_object2) references object2 (id_object2) ) engine=InnoDB; insert into test.object1 (id_object1, total, weight, dt) values (1, 0, 0, '2012-01-01 00:00:00'); insert into test.object1 (id_object1, total, weight, dt) values (2, 0, 0, '2012-01-02 00:00:00'); insert into test.object2 (id_object2) values (1); insert into test.score (id_object1, id_object2, dt, score) values (1, 1, '2012-01-03 00:00:00', 10); insert into test.score (id_object1, id_object2, dt, score) values (2, 1, '2012-01-04 00:00:00', 8); update test.object1 p join ( select ur.id_object1, sum(ur.score * ur.weight) as total, count(*) as weight from ( select lur.* from ( select s.id_object1, s.id_object2, s.dt, s.score, 1 as weight from test.score as s join test.object1 as o1 using(id_object1) where s.dt > o1.dt order by s.id_object1, s.id_object2, s.dt desc ) as lur group by lur.id_object2, lur.id_object1, date(lur.dt) order by lur.id_object1, lur.id_object2 ) as ur group by ur.id_object1 ) as r using(id_object1) set p.total = p.total + r.total, p.weight = p.weight + r.weight, p.dt = now(); ``` Note: I'm running these queries from a PHP environment and I have NOT explicitely used mysql_select_db('test'), because I prefer not to and none of the other (many!) queries require it. I'm sure that using mysql_select_db would solve my issue, but I would like to know why exactly this particular query does not work. For comparison sake: if you'd run this simpler query, also without using mysql_select_db, everything works fine: ``` update test.object1 set total=1, weight=1, dt=now() where id_object1=1; ``` I've searched to no avail. The only thing I found that came close, was this bug report: http://bugs.mysql.com/bug.php?id=28551 and especially that last (unanswered) message...

Original source