I've been testing with migrating my site from 4.7 to 5.1 and update.php has been returns me these errors when it runs update #12 in the latest 5.x-1.x-dev code:

user warning: Duplicate column name 'is_cacheable' query: ALTER TABLE view_view ADD is_cacheable int(1) in /home/xxx/public_html/drupal5/includes/database.mysql.inc on line 172. 
user warning: Table 'xxx_drupal5.cache_views' doesn't exist query: SELECT data, created, headers, expire FROM cache_views WHERE cid = 'views_tables:pt' in /home/xxx/public_html/drupal5/includes/database.mysql.inc on line 172. 
user warning: Table 'xxx_drupal5.cache_views' doesn't exist query: LOCK TABLES cache_views WRITE in /home/xxx/public_html/drupal5/includes/database.mysql.inc on line 172. 
user warning: Table 'xxx_drupal5.cache_views' doesn't exist query: UPDATE cache_views SET data = 'a:4:{s:6:\"tables\";a:42:{s:4:\"book\";a:5:{s:4:\"name\";s:4:\"book\";s:8:\"provider\";s:8:\"internal\";s:4:\"join\";a:2:{s:4:\"left\";a:2:{s:5:\"table\";s:4:\"node\";s:5:\"field\";s:3:\"vid\";}s:5:\"right\";a:1:{s:5:\"field\";s:3:\"vid\";}}s:7:\"filters\";a:1:{s:6:\"parent\";a:4:{s:4:\"name\";s:17:\"Book: Parent Node\";s:4:\"list\";s:37:\"views_handler_filter_book_parent_zero\";s:8:\"operator\";s:28:\"views_handler_operator_andor\";s:4:\"help\";s:53:\"This allows you to filter books based on parent node.\";}}s:5:\"sorts\";a:1:{s:6:\"weight\";a:3:{s:4:\"name\";s:12:\"Book: Weight\";s:7:\"handler\";s:30:\&qu in /home/xxx/public_html/drupal5/includes/database.mysql.inc on line 172. 
user warning: Can't DROP 'query'; check that column/key exists query: ALTER TABLE view_view DROP query in /home/xxx/public_html/drupal5/includes/database.mysql.inc on line 172. 
user warning: Can't DROP 'countquery'; check that column/key exists query: ALTER TABLE view_view DROP countquery in /home/xxx/public_html/drupal5/includes/database.mysql.inc on line 172.

The status report says this:

The following queries were executed
views module
Update #12
Failed: ALTER TABLE {view_view} ADD is_cacheable int(1)
UPDATE {view_view} SET is_cacheable = 1 WHERE vid = 16
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 11
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 12
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 20
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 19
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 18
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 21
UPDATE {view_view} SET is_cacheable = 1 WHERE vid = 25
UPDATE {view_view} SET is_cacheable = 1 WHERE vid = 23
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 10
UPDATE {view_view} SET is_cacheable = 0 WHERE vid = 13
Failed: ALTER TABLE {view_view} DROP query
Failed: ALTER TABLE {view_view} DROP countquery
CREATE TABLE {cache_views} ( 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 */

I'm upgrading from 4.7.x-1.x-dev, and it seems to me (looking at that version's code) that these three failed queries were already executed by 4.7.x-1.x-dev upgrade #12. However, I still need to run 5.1-1.x-dev's upgrade #12 because without it, the cache_views table does not get created. Should this cache_views query then be by itself in an upgradre #13?

Not sure if this means that there's something wrong that should be corrected or if it's just that these modules are not ready for primetime and I'm already messing with them. ;)

Comments

merlinofchaos’s picture

merlinofchaos commented

Ooogh. This is an interesting problem in that 4.7 and 5.x need to always keep their update numbers in sync. Hm. Now I need to check and see what state things are in and see if I can fix this. Could be Exciting.

merlinofchaos’s picture

merlinofchaos commented

After looking at those for a little bit, I think that those errors are basically harmless.

However, you're right about the cache_views table. I'm not sure what to do about that. This could be more than a little bit complicated to resolve. :/

ricmadeira’s picture

ricmadeira commented

Yeah, I think the errors are harmless.

The single big problem is this:

When one upgrades from 4.7 to 5.1, the cache_views table is not created automatically; one has to force update.php to run update #12. I'm not sure how Drupal works, but I'm guessing since it has run update 12 in 4.7 it won't run update 12 again for 5.1. I got these errors after forcing drupal to run update 12 in 5.1... because before I ran that, I was getting cache_views missing table errors every page I visited on my upgraded test site.

I'm guessing that will be what happens to every other user that goes from views 4.7.x-dev to 5.1. The current "normal" release for 4.7 does not yet include update #12, so those users won't have any problems whatsoever.

lobotomir’s picture

lobotomir commented

when upgrading from views 5.x-1.5 to 5.x-1.6-beta, running upgrade.php also shows an error saying that cache_views doesn't exist.

wim leers’s picture

wim leers
Location Ghent 🇧🇪🇪🇺
commented

I can confirm this issue, and I also agree that it's completely harmless.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

Well I have an ugly fix for this now (going out in beta3); it'll work for the cache table but I have to be very careful about things that'll exist in 5.x and not 4.7.x going forward.

plhosk’s picture

plhosk commented
Status: Fixed » Active

I upgraded from 1.5 to 1.6-beta3 and ran update.php. I got some errors which I failed to write down. Now it appears the table cache_views no longer exists. I'm getting a lot of errors that look like this:

Table 'weebook.cache_views' doesn't exist query: LOCK TABLES cache_views WRITE in /var/www/includes/database.mysql.inc on line 172.

Which mysql commands will recreate this table? and why was it deleted?

plhosk’s picture

plhosk commented

i recreated the database by running:
db_query("CREATE TABLE {cache_views} (
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 */ ");

the error messages are gone. But it appears there's a bug in the upgrade script that causes the cache_views table to not be created properly.

senpai’s picture

senpai commented
Title: Bug in views_update_12()? » views_update_12 causes user warning "Table cache_views doesn't exist"

I'm concurring with lobotomir on the April 13th post, and I'm changing the title of this bug so it's more easily found during searches.

After updating a 5.1 site from Views 1.5 to 1.6-dev today, I was greeted by this error/warning. It's quite scary, cause it's over three pages long. WimLeers says its harmless. I agree, but only from a code perspective. The resulting mental trauma incurred after updating CCK to 1.5 and being subsequently encouraged to upgrade Views to 1.6-dev in order to ensure complete functionality is rather disturbing. Let's get this bug fixed. Here's what I can tell you about it.

Perusing the MySQL snapshot that I took right before this CCK 1.5 and Views 1.6 update, I find that the table "cache_views" did not exist before the CCK update or the subsequent and immediate Views update. I got no errors on the CCK update. Since the cache_views table exists after the update, I can only presume that this line in Update #12 was successful:

CREATE TABLE {cache_views} ( 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 */

Now that the table has been created, the next update that runs is Update #13. At that point, DELETE FROM {cache_views} is executed. Somewhere in the middle of those two, the three lines of SQL that are executed fail. All three lines attempt to use the cache_views table before it exists in the DB, and all three times, database.mysql.inc on line 172 is involved.

I have the entire update.php?op=finished error page saved as html, if anyone is interested. I'm not attaching it to this bug report because it contains most of my site's data as a SET data "" string.

ricmadeira’s picture

ricmadeira commented
Priority: Normal » Critical

I just did another upgrade test from 4.7-dev to the current 5.1-dev and I noticed the same bug as plhosk: the cache_views table does not get created even when I select upgrades 12-14 to run.

ricmadeira’s picture

ricmadeira commented

Hmm... could this be the bug? In the function:

function views_make_cache_table(&$ret) {
  if (!db_table_exists('view_view')) {
    $ret[] = update_sql("CREATE TABLE {cache_views} (

Shouldn't that if statement be:

if (!db_table_exists('cache_views')) {

It seemed to work for me!

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Why not "CREATE TABLE IF NOT EXISTS ..."

merlinofchaos’s picture

merlinofchaos commented

nancyw: Because 'if not exists' doesn't work under non-mysql databases.

ricmadeira: That's really bizarre. As of beta4 that should read 'cache_views' -- the view_view was a bug in beta3. I'm really curious how you're even seeing that. But it was fixed in views.install 1.21.4.12 which is what has the beta4 tag. And that's been checked in for 2 weeks, so the current -dev should have it, though now I need to download it and check I guess.

ricmadeira’s picture

ricmadeira commented
Priority: Critical » Normal

Hmm... you're indeed right, merlin. This is already fixed in both the 5.x-beta4 and the 5.x-dev. I must have downloaded the wrong version while looking for the -dev (it's buried in the middle of the other versions) when I updated my module. Sorry.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

I believe I've fixed all of the problems with the annoying _update_12 in the latest version.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
dh@tbed.org’s picture

dh@tbed.org commented

excuse me - do you mean run update as soon as one has copied the Views module directory to the site, or after checking activate?

I see these error messages as soon as I have copied the Views module folder -- I have therefore not activated the module, nor run update.php. which step next?

federico carol’s picture

federico carol commented

Great! I used

$ mysql -u username -p
> use your_database;
> CREATE TABLE cache_views ( 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 */;
> quit

that's it. Thanks Senpal

vkr11’s picture

vkr11 commented

subscribe

chrisd’s picture

chrisd commented
Version: 5.x-1.x-dev » 5.x-1.8
Status: Closed (fixed) » Active

This issue is still open. I just upgraded one of my old sites from Drupal 4.7.8 to 5.23 and I was getting tons (50+ per minute?) of similar error in the log.
I did a search "cache_views" on the views folders (version 5.1.8 and 5.1.0) and no install file seems to be creating this table....

Creating the table with:
CREATE TABLE cache_views ( 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) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

helped solve this.

Which version of views is suppose to create that table?

Letharion’s picture

Letharion commented
Assigned: Unassigned » merlinofchaos
merlinofchaos’s picture

merlinofchaos commented
Status: Active » Closed (won't fix)

With the release of Drupal 7, Drupal 5 is no longer supported.

robbyahn’s picture

robbyahn commented

For the Drupal 6 views.

DB way to fix.
Here is the script if you see the error..

create cache_view table

CREATE TABLE cache_views (
cid varchar(255) NOT NULL default '',
data longblob,
expire int NOT NULL default '0',
created int NOT NULL default '0',
headers text,
serialized smallint,
PRIMARY KEY (cid),
INDEX expire (expire)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

create cache_view_data
CREATE TABLE cache_views_data (
cid varchar(255) NOT NULL default '',
data longblob,
expire int NOT NULL default '0',
created int NOT NULL default '0',
headers text,
serialized smallint,
PRIMARY KEY (cid),
INDEX expire (expire)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;