When upgrading a site from 1.x to 3.x, the location_instance table is not created.

Errors include the following: 



    * user warning: Table 'sunflower_work.location_instance' doesn't exist query: location_load_locations SELECT lid FROM location_instance WHERE vid = 2 in /home/dave/work/sunflower/htdocs/includes/database.mysql.inc on line 172.
    * user warning: Table 'sunflower_work.location_instance' doesn't exist query: location_load_locations SELECT lid FROM location_instance WHERE vid = 3 in /home/dave/work/sunflower/htdocs/includes/database.mysql.inc on line 172.
    * user warning: Unknown column 'eid' in 'field list' query: site_update_update_2 INSERT IGNORE INTO location (eid, lid, type, name, street, additional, city, province, postal_code, country, latitude, longitude, source, is_primary) VALUES (1,1,'user','','','','','','','',NULL,NULL,0,0); in /home/dave/work/sunflower/htdocs/includes/database.mysql.inc on line 172.

Executed queries are these:

gmap module
Update #5000

    * No queries

Update #5001

    * No queries

location_views module
Update #5300

    * UPDATE {system} SET weight = 1 WHERE name = '%s' AND type = '%s'

location module
Update #5300

    * CREATE TABLE {cache_location} ( cid varchar(255) NOT NULL default '', data longblob, expire int NOT NULL default '0', created int NOT NULL default '0', headers text, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ 

Update #5301

    * INSERT INTO {location_instance} (nid,vid,lid) (SELECT nr.nid, nr.vid, l.lid FROM {node_revisions} nr INNER JOIN {node_revisions} nr2 ON nr.nid = nr2.nid INNER JOIN {location} l ON nr2.vid = l.eid AND l.type = 'node')
    * INSERT INTO {location_instance} (uid,lid) (SELECT eid, lid FROM {location} WHERE type = 'user')
    * INSERT INTO {location_instance} (genid, lid) (SELECT CONCAT(type, ':', eid), lid FROM {location} WHERE type <> 'user' AND type <> 'node')
    * ALTER TABLE {location} DROP COLUMN type
    * ALTER TABLE {location} DROP COLUMN eid

Update #5302

    * No queries
CommentFileSizeAuthor
location_update_errors.pdf81.21 KBDave Cohen

Comments

bdragon’s picture

bdragon commented

How sure are you about the non-existence of the table? I recently noticed that I was running the query in a way that it wasn't being listed on the queries performed screen, but it should still have happened...

Dave Cohen’s picture

Dave Cohen commented

You're right that the table was created. So I'm not sure where the errors come from. Also I don't trust the data written to that table, theres a lot of NULL.

mysql> describe location_instance;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| nid   | int(10) unsigned | YES  | MUL | NULL    |       |
| vid   | int(10) unsigned | YES  | MUL | NULL    |       |
| uid   | int(10) unsigned | YES  | MUL | NULL    |       |
| genid | varchar(255)     |      | MUL |         |       |
| lid   | int(10) unsigned |      | MUL | 0       |       |
+-------+------------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> select * from location_instance;
+------+------+------+-------+------+
| nid  | vid  | uid  | genid | lid  |
+------+------+------+-------+------+
| 1001 | 1001 | NULL |       | 1001 |
| 1002 | 1002 | NULL |       | 1002 |
| 1009 | 1009 | NULL |       | 1003 |
| 1010 | 1010 | NULL |       | 1004 |
| 1014 | 1014 | NULL |       | 1008 |
| 1015 | 1015 | NULL |       | 1012 |
| NULL | NULL | 1005 |       | 1005 |
| NULL | NULL | 1006 |       | 1006 |
| NULL | NULL |    1 |       | 1007 |
| NULL | NULL |    1 |       |    1 |
| NULL | NULL | 1007 |       | 1009 |
| NULL | NULL | 1007 |       | 1010 |
| NULL | NULL | 1006 |       | 1011 |
| NULL | NULL | 1008 |       | 1013 |
| NULL | NULL |    0 |       | 1014 |
| NULL | NULL |    0 |       | 1015 |
| NULL | NULL | 1005 |       | 1016 |
| NULL | NULL | 1008 |       | 1017 |
| NULL | NULL |    2 |       | 1018 |
+------+------+------+-------+------+
19 rows in set (0.02 sec)
bdragon’s picture

bdragon commented

The errors were probabaly leftovers from the page request before you hit update.php... Since only at the end does the updater check for messages to display, and since messages that are generated late in a request get deferred to the next request, they end up getting displayed at that point.

The nulls are on purpose. The table joins to multiple types of objects (users, nodes, other things), and I optimized the users and nodes cases by giving them their own columns. Only one of nid+vid, uid, or genid should generally be in use for a specific row. The rest should be NULL to prevent accidental retrieval in the wrong context.

(The purpose of this table is twofold: It joins locations to objects, and it is used to count how many times a specific location is in use.)

The only possible error I see in your data there is the two locations associated with the anonymous user (lid 1014 and 1015) -- Was this intentional, or is this errant data?

bdragon’s picture

bdragon commented
Status: Active » Fixed

Assuming this is working sufficiently well.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

karunadave’s picture

karunadave commented
Status: Closed (fixed) » Active

I see this happening in Drupal 5.12 from location 2.7 to 3.0 rc2

For example, when loading the update.php page or refreshing update.php (refreshing the page, not proceeding with the update) this message is displayed:

user warning: Table 'upayasol_lamayedrp1.location_instance' doesn't exist query: SELECT lid FROM location_instance WHERE uid = 1 in C:\dev\htdocs\drupal512\includes\database.mysql.inc on line 174.

When I do proceed with the update I see the table is created:

Update #5301

* CREATE TABLE {location_instance} ( nid int UNSIGNED DEFAULT NULL, vid int UNSIGNED DEFAULT NULL, uid int UNSIGNED DEFAULT NULL, genid varchar(255) NOT NULL default '', lid int UNSIGNED NOT NULL DEFAULT '0', INDEX {location_instance}_nid_idx (nid), INDEX {location_instance}_vid_idx (vid), INDEX {location_instance}_uid_idx (uid), INDEX {location_instance}_genid_idx (genid), INDEX {location_instance}_lid_idx (lid) ) /*!40100 DEFAULT CHARACTER SET utf8 */
* INSERT INTO {location_instance} (nid,vid,lid) (SELECT nr.nid, nr.vid, l.lid FROM {node_revisions} nr INNER JOIN {node_revisions} nr2 ON nr.nid = nr2.nid INNER JOIN {location} l ON nr2.vid = l.eid AND l.type = 'node')
* INSERT INTO {location_instance} (uid,lid) (SELECT eid, lid FROM {location} WHERE type = 'user')
* INSERT INTO {location_instance} (genid, lid) (SELECT CONCAT(type, ':', eid), lid FROM {location} WHERE type <> 'user' AND type <> 'node')
* ALTER TABLE {location} DROP COLUMN type
* ALTER TABLE {location} DROP COLUMN eid

bdragon’s picture

bdragon commented
Status: Active » Fixed

Yes, there's no easy way around this really, a 3.x site running 2.7 tables will cause errors until the request *after* updates are finished running.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented
Version: 5.x-3.x-dev » 6.x-3.1-rc1
Status: Closed (fixed) » Active

Sorry, Brandon, I got this going from 5.x..-dev from June 19, 2007 to 3.1-rc1 -- and while running update.php.

user warning: Table 'youthpts_ysp.location_instance' doesn't exist
query: SELECT lid FROM location_instance WHERE vid = 47 in /home/youthpts/public_html/includes/database.mysql.inc on line 174.

Yes, the table now exists and has 92 rows in it.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented
Version: 6.x-3.1-rc1 » 5.x-3.1-rc1

wrong release

ansari.wajid’s picture

ansari.wajid commented
Version: 5.x-3.1-rc1 » 6.x-3.0

Hi

I am getting this error, I tried dissabling/deleting the module and tried enabling but of no use. Please let me know what is the remedy for this messages. On some pages there are 3 warning messages and on some it is showing 10 messages and on some pages it is showing no messages...different quantity of messages on different pages. Thanks in advance.

 * user warning: Table 'next1_drupal6.location_instance' doesn't exist query: SELECT lid FROM location_instance WHERE vid = 73 in /usr/www/users/next1/drupal6/sites/all/modules/location/location.module on line 989.
    * user warning: Table 'next1_drupal6.location_instance' doesn't exist query: SELECT lid FROM location_instance WHERE vid = 71 in /usr/www/users/next1/drupal6/sites/all/modules/location/location.module on line 989.
    * user warning: Table 'next1_drupal6.location_instance' doesn't exist query: SELECT lid FROM location_instance WHERE vid = 70 in /usr/www/users/next1/drupal6/sites/all/modules/location/location.module on line 989.
yesct’s picture

yesct commented
Status: Active » Postponed (maintainer needs more info)

Please check to see if this is still a problem in the new release. If it is, reply with a new summary and set the status back to active.

ankur’s picture

ankur commented
Status: Postponed (maintainer needs more info) » Closed (cannot reproduce)

Closing due to lack of follow up.