By jaddison on

I have gone through every option I can think of, but I can't figure out how to get even the basic blocks to display in 5.1 after an upgrade from 4.7.*. Navigation, User Login, any of them - they simply won't display, no matter where I choose to display them.

It also doesn't matter what theme I choose. Garland, a new downloaded one - they all have the same result.

Has anyone run into this issue before? Do I need to manually clear some tables?

Thank you - I hope someone has some input here. I don't think it is a bug, as I have upgraded other 4.7.* sites successfully.

James Addison
http://www.scottisheyes.com/

⋅ Categories: Drupal 5.x

Comments

jaddison’s picture

jaddison commented

I wish I had some further input on this issue - are there any thoughts out there that could help me?

Thanks,

James Addison
http://www.scottisheyes.com/

vm’s picture

vm commented

you can try clearing the cache tables. for the most reliable way of upgrade, it was stated in the directions to upgrade to 5.0 first then to 5.1.

see upgrade heading: http://drupal.org/drupal-5.1

vinnie1’s picture

vinnie1 commented

I have maneged my upgrade to 5.0 from 4.7.4, but it was a hell of a battle. Now my 3 column layout has gone to 1 and no matter what I try to do I cannot enable any block, even the menu doesn't work, I have to type in the URL's to get anywhere.

I have gone to the logs after trying to enable the blocks. Here is the error I am getting

Type php
Date Thursday, February 15, 2007 - 21:19
User vinnie
Location http://192.168.2.111/admin
Referrer http://192.168.2.111/admin/build/block

Message Table 'Drupal.blocks_roles' doesn't exist query: SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'pushbutton' AND b.status = 1 AND (r.rid IN (2) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in /var/www/html/includes/database.mysql.inc on line 167.

Severity error
Hostname 192.168.2.3

Hope this means something to somebody. It (I think) says the block does not exist, but in the admin block section it is there in the disabled list, in fact I don't even have an enabled list at all.

Cheers

jaddison’s picture

jaddison commented

In searching for "illegal mix of collation" on both Drupal.org and Google, I managed to come up with the following bit of code - you'll need to initialize the DB object yourself, but the important part is the SQL statement. It will set the table and column collations to utf8_general_ci. Use whatever collation best suits your needs.

$query = "SHOW TABLES";
if ( $db->query($query) ) {
 $result = $db->result();
 // iterate through resultset
 while ( $row = mysql_fetch_row($result) ) {
  $res[] = $row[0];
 }
}

foreach ($res as $table) {
 $query = "ALTER TABLE ".$table." CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
 $db2->query($query);
}

I hope this helps someone else - oh, and I apologize for the lack of error checked, etc. It was a quick fix.

Thanks,

James Addison
http://www.scottisheyes.com/

jaddison’s picture

jaddison commented

I reread the previous poster's comment again - my solution addresses MY issue, not the one he/she presented. Sorry!

James Addison
http://www.scottisheyes.com/

vm’s picture

vm commented

@ vinnie

it doesnt say the block doesnt exist, its the table that the date is stored in for blocks per role that is missing.
I don't think you ran update.php after you updated your files to update the database which should have added this table.

vinnie1’s picture

vinnie1 commented

I am having major problems with my website trying to do this upgrade.

Method-1 I run update.php without selecting version numbers in the advanced list I end up with a blank website, but at least no errors.

method-2 I run update.php and go into the advanced selection, I then move the selection slider all the way to the bottom and select 1024 (I think- I don know what this number means actually), this works but I have no blocks, everything is disabled, but at least I can see the main content of the website.

I have totally run out of ideas, I am not a programmer so the above posts have gone way over my head.

NEED HELP PLEASE

vince

tjharman’s picture

tjharman commented

Vinne, do you have phpMyAdmin installed? I fixed the problem in about 5 minutes with it, I can talk you through the steps if you want.

vinnie1’s picture

vinnie1 commented

HI tjharman

yes I do have phpMyAdmin installed, if you could talk me through the steps that would be great.

Vince

TechMosaic’s picture

TechMosaic commented

Please don't use a private post - if you can outline the steps you were going to talk through for a phpadmin fix...please make those available online here...thanks

tjharman’s picture

tjharman commented

Apologies for not posting this in the first place, stupid of me.

Anyway... This is how I fixed the problem and restored blocks to my site:

Open up phpMyAdmin and select your Database from the left hand site. Once all the tables appear in the right hand site, take note of what's in the Collation column. All my tables were of type utf8_unicode_ci until after I upgraded, now they're a mixture of utf8_unicode_ci and utf8_general_ci

I decided everything should be "utf8_unicode_ci" - I don't have ANY understanding of what the differences is so either a) Make sure you do or b) Take a backup before you make changes.

I clicked on the "blocks_roles" table and changed each item in there (module and delta) to "utf8_unicode_ci" from "utf8_general_ci"
Then click on the "blocks" table and make sure all the items in there are set the same as what you set everything to in blocks_roles.

Once I did this, all my blocks appeared and the "Illegal Mix Of Collations" stopped appearing in my logs.

I hope this helps.

TechMosaic’s picture

TechMosaic commented

I did notice a lot of inconsistency on the collations within my site (swedish, general, unicode). But 'fixing' them did not resolve the problem. So I am still looking for an explanation to why my menu and other blocks won't show.

Thanks for posting your fix though....

tjharman’s picture

tjharman commented

Did you fix not only the tables themselves, but also the, Umm I'm not even sure what they're called. The rows(?) in the tables?

Is your site showing anything in the log? -> admin/logs/watchdog and filter by PHP error. Show anything?

Tim

vinnie1’s picture

vinnie1 commented

I gave your idea a go but nothing happening. The error in the logs is the same as before.

One thing I did notice is when I go into the Blocks part of the database I have a warning
No index defined is this my problem ?

Any other ideas?

Vince

tjharman’s picture

tjharman commented

I've just looked at your error message... It's saying you don't have 'Drupal.blocks_roles'

This means in your database, you don't have a "blocks_roles" table. That's not a good thing.

What process did you follow to do the upgrade? You've run update.php??

vinnie1’s picture

vinnie1 commented

Hi tjharman

Yes I have run update.php.

The first way I tried is without using the select versions selection on the update page, if I do this I end up with NO errors during the upgrade but the website is completely blank. I.E I click on the links to go to the admin page or the main page, it's just blank, no errors, no writing nothing.

The second way I have tried is to go into the select versions section and to select a system module version, the highest number on my list is 1021, if I do this I end up in this situation where the content is visible but no blocks.

Any ideas, cause im all out of them. I don't understand why this is so difficult, I mean my website is only using about 4 modules, with very little theme customisation, other than that it's all standered.

P.S I have even tried changing to a standered theme, still no dice

Vince

vm’s picture

vm commented

running the 1021 system update again more then likely made things worse.

I'd revert to the backup that you did before you tried updating to 5. and start again.

vinnie1’s picture

vinnie1 commented

HI

I have tried upgrading my website approximatly 20 - 30 times, each time a different way.

I started by following the upgrade instructions to the letter, after that any way I could think of. After each failed attempt I would (from backup) restore my website with the original 4.7.4 files and database and start all over again. I have even set up a spare webserver for this.

The 1021 system update is so far given the best result, at least I can see the content afterwards. The normal recommended method just leaves me with a blank website!

vm’s picture

vm commented

if it were me, i'd upgrade 4.7.4 - 4.7.5 then to 4.7.6 to insure that I can update to the latest version of Drupal 4. Then I'd upgrade my 4.7.6 to 5.0 and my 5.0 - 5.1.

one of those updates would insert the table that you are missing when trying to upgrade straight to 5.1

insure that you unenable all contrib modules before the updates. and insure that you upgrade the contrib modules in use after you update core.

vinnie1’s picture

vinnie1 commented

Hi

I have just given that a go, but no good. I have a blank website trying to go to 5.0.

Well at least I am updated to the latest 4.7.6 now which works perfectly.

takaakikato’s picture

takaakikato commented

Missing a table might have something to do with MySQL issues.
See the following: http://drupal.org/node/97510 and http://drupal.org/node/54702

This seems to have been fixed in the nightly versions (http://drupal.org/drupal-5.x-dev).

Try it with care. It's not a relased version.

--
Samurai Coder

vinnie1’s picture

vinnie1 commented

I haven't tried it yet, but I take it that this will be included in the next 5.2 release.

If this is the case do I upgrade from 4.7.6 straight to 5.2 when it comes out.

leoklein’s picture

leoklein commented

So my User Login block doesn't show up after I upgraded 4.7->5.1.

I've added it on the Blocks Page but still it doesn't show up on any theme. The LoginToboggan Login Block also doesn't show up.

I'm kind of reluctant to change the "collation" since I don't understand exactly what this will do or why it would work.

FlemmingLeer’s picture

FlemmingLeer commented

Create a backup before you do anything.

I just upgraded from 4.7.6 to 5.1 and encountered this bug.

Setting the collation to the same fixed the bug here at my installation via phpmyadmin. I use utf_danish_ci for all my tables now and in all the colums. (instead of utf_general_ci)

I use mysql 4.1.11 php 4.3.10-16 on a hosted Apache/2.0.54 (Debian GNU/Linux) solution.

leoklein’s picture

leoklein commented

This is far beyond my powers to explain but something must have kicked in at a certain point because the missing user block mysteriously re-appeared.

I have no idea why -- expect that I've been hitting that 'update.php' url for a while now on various mods.

(P.S. 'backup' is my middle name)

JirkaRybka’s picture

JirkaRybka commented

FIXED!

I had the same problem on my testsite, fixed it with just PhpMyAdmin (thanks for tips from this thread, and elsewhere), and now I'm pretty sure what is it all about:

--- Database server being MySQL 4.1 and higher. This version started to support (and require!) character-encoding (i.e. utf-8 vs. Windows 12xx vs. Latin 1/2 etc.) and collations (i.e. national rules for alphabetical sorting).

--- After import from backup done on another version, or after creating test-database with wrong settings, collation differs somewhere, making database server stop and send error (and so the page doesn't show correctly, because it's unfinished).

--- If you have problems with encoding, you'll see your text-contents broken (garbage characters instead of national ones). This will only happen while going from MySQL 4.0 (and below) to 4.1 (and above), as previously there was no info about encoding included in the dumps. I had this problem with dump from live site, used on (newer) test server. You always want to create your new databases with "utf-8" encoding, as this is what Drupal uses (and therefore the already existing real encoding of your old unlabeled data). My problem here was, that I had to patch the back-up utility (german mysqldumper http://www.mysqldumper.de, supporting large databases on webhosting with time limits on php), to call the database query "SET NAMES utf8" just after connecting to database. This sets the correct encoding for importing unlabelled data, where default fails.

--- Problems with collations are solved from PhpMyAdmin by manually going through the tables, and setting THE SAME collation on the following: database AND all tables AND all columns in the tables. I think that you can choose any collation you like (probably will be your national one), but it must be THE SAME everywhere. Consider that imported tables from back-up dumps may also have their collations set, so you want just to create your new database for import with the same. (Drupal seems to create any new tables with the setting used for whole database, so this must be correct!) Manual work comes into play only when there are differences between tables in existing database.

societyofrobots’s picture

societyofrobots commented

Like everyone else, I get this error:

"user warning: Table 'sor_drupal.blocks_roles' doesn't exist query: SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'admin_created' AND b.status = 1 AND (r.rid IN (2) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in /var/www/vhosts/societyofrobots.com/httpdocs/member_tutorials/includes/database.mysql.inc on line 172."

I manually changed all my table collations to latin1_swedish_ci, which was the default before I upgraded drupal.

When first signing into phpMyAdmin, it says this:
MySQL charset: UTF-8 Unicode (utf8)
MySQL connection collation: utf8_general_ci
more info I found on char sets:
http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html

Browsing around, I found other problems -

warnings in Block:
No index defined!

warnings in Node:
PRIMARY and INDEX keys should not both be set for column `nid`
More than one INDEX key was created for column `status`

I'm still stuck and not sure how to proceed . . . anyone with ideas/solution????

Another note, I also got that blank pages problem mentioned earlier. The reason is because there are THREE modules I had that need to be upgraded (each one caused the same problem):
revision_moderation
taxonomy_context
views

Just upload the newest versions of those modules and the blank pages problem goes away =)

Then I ran the update.php file again and it upgraded everything properly (minus the problem I still couldn't solve).

JirkaRybka’s picture

JirkaRybka commented

There are possibly several problems involved, I don't know...

But as for the collations, I recently did a bit of research on these issues, which resulted in a new handbook page: http://drupal.org/node/198184

societyofrobots’s picture

societyofrobots commented

I did this to my DB to get the error to go away:

DROP TABLE IF EXISTS `blocks_roles`;
CREATE TABLE `blocks_roles` (
  `module` varchar(64) NOT NULL,
  `delta` varchar(32) NOT NULL,
  `rid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`module`,`delta`,`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I got that from this I found:
http://dkv-dev.org/bck/teach-dev--bck-07092007.sql

Apparently I didn't even have a table called blocks_roles!!!

So now the error has gone away . . . but I still don't have blocks!!! I suspect I need some data in that table that I'm missing . . . can anyone export their blocks_roles part of their DB and send it to me at palmisano AT@AT gmail DOT.DOT com pleaaasseee?

societyofrobots’s picture

societyofrobots commented

After adding that DB entry, I tried reloading my theme.

Suddenly it all works! yaaaay!

Although for some odd reason the word 'Array' appears at the top left of my page . . . anyone know why? I suspect it has a lot to do with my modified box_grey theme not acting right in v5.5 of Drupal . . .
http://www.societyofrobots.com/member_tutorials/

JirkaRybka’s picture

JirkaRybka commented

My blocks_roles table is - EMPTY! Believe it or not... My 5.x site runs fine with empty table. I think that this table most probably record access limitations on blocks (i.e. which roles can see the block), and I have all blocks with unlimited access, so nothing is recorded.

Your problem is probably somewhere else.

markdionne’s picture

markdionne commented

I had a similar problem with a different cause: I added some custom PHP code to a block when I was using Drupal 4.7. The code would hide the block except for users with a certain Role. Since then I upgraded to Drupal 5.2 and then 5.5. I recently went to remove the custom PHP code and it was not there when I looked at the dialog, but it was still "working".

After much puzzlement, I looked into one of my database backups, and there was the PHP code, in the blocks table, like this:
('menu','360','PIH',1,-9,'left',0,0,2,' global $user; return in_array(\'reviewer\', $user->roles); ','')

I went back into the blocks administrative dialog, "edited" the "Page specific visibility settings" to make sure it was empty (even though it was already empty), hit Submit, and the invisible PHP code evaporated, allowing all Roles to see the block.

Oddly, there was old PHP code on other blocks that apparently made it through the upgrades correctly. I took a quick look at their database records and I don't see any obvious differences. The blocks_role table was empty by the way.

-mark