Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by James Marshall -
Number of replies: 13

Hi people.

I have been trying to upgrade from Moodle 1.7.2+ to the latest version - 1.9.1+.

I initially upgraded our 1.7 installation to 1.8.5+ which all seemed to go smoothly and installed with no problems. I then decided to upgrade to 1.9.1+ which has cause a small problem when it tries to upgrade the database and gives me the following error message:

 1170: BLOB/TEXT column 'action' used in key specification without a key length

                       
ErrorScroll to next warning
Scroll to previous warningUpgrade savepoint: Error during main upgrade to version 2007101508.02Scroll to next warning
Scroll to previous warningMain Upgrade failed! See lib/db/upgrade.phpScroll to continue button

I am not entirely sure how to solve the problem. Does anyone else know what is cuasing this?

Would apreciate some advice.

Cheers guys,
James

Average of ratings: Useful (1)
In reply to James Marshall

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Richard Enison -
JM,

I am a little puzzled because I think I might know what the solution is but it doesn't quite fit. Here's why:

Usually when I see an error msg. complaining about a BLOB field or other related stuff about a SQL query that Moodle tries to execute during an installation or upgrade, it means that
  1. the user is using MySQL, and
  2. (s)he is running it in strict mode (see http://docs.moodle.org/en/Installing_Moodle#Requirements, near end of section, or do a search of the page for "strict").
The solution is to turn strict mode off and the problem goes away. Now here's why this doesn't seem to fit: if you were doing that, it would have shown up when you upgraded Moodle to 1.8.5+. Is it possible you (or your host) have recently changed the way MySQL is being run? If so, see http://moodle.org/mod/forum/discuss.php?d=70388#p317823.

The other problem with this scenario is that, usually, when a strict mode problem happens, there is an error msg. from a MySQL-specific function, and I don't see one in your post. The only other explanation I can think of is that there is a bug in Moodle 1.9.1+, but if that were true, everyone would be reporting it!

RLE
Average of ratings: -
In reply to Richard Enison

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by James Marshall -

Thanks for your reply Richard

Very perceptive of you smile I have indeed disabled strict mode for MySQL. This is the only change I have made to MySQL while doing the upgrade.

This is a development server so I can change settings on it as I need to before upgrading our live Moodle installation. I installed 1.7.2+ and copied the database accross from our current 1.7.2+ installation. I then upgraded to 1.8.5+ and as I mentioned there seemed to be no problems. I then copied the 1.8 installation into a new folder and uploaded 1.9.1+ and tried upgrading Moodle, which is when I saw this error. I first of all figured it might be to do with strict mode so I turned this off and restarted the MySQL service. No change. I then rebooted the server...still no change.

Do you think this could be a problem with the way PHP/MySQL is setup on our development server? Or do you think I should try a different version of 1.9?

Edit: Just to give you an idea of our setup...the dev server is running Windows XP Professional SP2 with IIS 6.0, PHP 5.2.3 and MySQL 5.0.45. Strangely though, when I run phpinfo() it reports that we are running 5.0.37, which is the version on our live server. When I installed PHP I uncommented the MySQL dll in php.ini and when I looked at phpinfo() it reported version 5.0.37 was installed when in fact MySQL hadn't been installed yet....could this be an issue? I removed strict mode from the version I actually installed (5.0.45). Just a thought.

Thanks,
James

Average of ratings: Useful (1)
In reply to James Marshall

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Richard Enison -
JM,

All I can think of is this: Is it possible that you have two MySQL servers floating around somewhere on your dev. server; one 5.0.37, and one 5.0.45 (who knows, maybe more than two!)? You removed strict mode, as you said, from the 5.0.45 version, because you thought that was the version you were using, but PHP is actually using the 5.0.37 version, and that one still has strict mode on.

RLE
Average of ratings: Useful (1)
In reply to Richard Enison

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by James Marshall -

Unfortunatley not sad

I have done an extensive search for "mysql" and "my.ini" and I can only find 1 instance of each.

I might try to install it on our live server under a different folder & database name to see if it is the way we have setup PHP and MySQL on the dev server.

Any other ideas? I'm really stuck and could do with a solution...

Thanks,
James

Average of ratings: Useful (1)
In reply to James Marshall

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Richard Enison -
JM,

Is your dev. server connected to a network (LAN/WAN)? Could there be another computer in that network (like your live server) that has MySQL 5.0.37 running on it? Could that be the db server PHP is using? Because that would explain the fact that phpinfo is telling you that your MySQL version is 5.0.37, as well as the fact that turning off strict mode for MySQL 5.0.45 doesn't help.

I know it is possible to use a db server on another machine in a network. I don't know if that applies to the Internet, but I wouldn't be surprised.

RLE
Average of ratings: Useful (1)
In reply to Richard Enison

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by James Marshall -

Our dev and live server are in-house on our local network. Is there a setting anywhere that PHP uses to find where MySQL is installed? Can't say I have noticed one...

I'll try changing the setting in the my.ini on our live server and see if it makes a difference.

Thanks for the advice so far smile

James

Average of ratings: -
In reply to James Marshall

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Richard Enison -
JM,

I don't believe there is a setting in PHP to find MySQL. I think when you fire up any MySQL server on a machine, PHP will find it. That is my experience. When I try to start another MySQL server, I get an error msg. that the port MySQL uses (I forget the number offhand) is already in use. So obviously, the system has a way of knowing that a db server using that port is running, and can find it. There must be a port table somewhere whereby it can look up which ports are in use, and where the task is that is using each one. How that relates to MySQL servers running on other machines, I'm not sure. But I have had a few thoughts since my previous post:
  1. What does your config.php file have for $CFG->dbhost?
  2. How do you start up MySQL every day (or however often you boot up)? The reason I ask is, there is a switch in the mysqld command that sets the location of the error log. If you manually start MySQL, then you should be able to find the error log. If it is started automatically, you will need to investigate how that is triggered. The reason I suggest looking at the MySQL error log, is that it contains a record of when MySQL starts and stops. It might be of interest to determine which MySQL server has been starting up each time.
  3. If you can start up the client program mysql, which is a command line oriented utility for running SQL queries and other MySQL commands, run this query:

    SELECT VERSION();

    This should tell you whether the MySQL server currently being recognized as controlling the MySQL port is 5.0.37 or 5.0.45. However, this should not be necessary, because when you start up the mysql utility, it tells you the server version. Be sure to end the utility by typing quit.
  4. Run that same query in phpMyAdmin. That should tell you which version MySQL server PHP is using.
  5. You say phpinfo reported MySQL version as 5.0.37 before you installed MySQL on the dev server. Too bad you didn't try some of these things then. Why not temporarily uninstall MySQL 5.0.45 and see what happens when you try to start a MySQL client, or even Moodle, on the dev server? If it doesn't work, check phpinfo again and see if it is reporting MySQL 5.0.37 as before. If not, start up MySQL on the live server and try again.
  6. If I'm right and it is picking up the MySQL server from the live server, trying to start up MySQL 5.0.45 manually on the dev server while the other one is running should fail with the "port in use" error.
  7. Your idea of changing the setting in my.ini for the MySQL 5.0.37 server on the live server is also a good one.
RLE
Average of ratings: -
In reply to James Marshall

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Richard Enison -
JM,

We may have been barking up the wrong tree all this time. I gave some reasons why the strict mode theory doesn't quite fit in my first post in this thread, and it now appears that it doesn't. See http://dev.mysql.com/doc/refman/5.0/en/create-index.html. It looks like the SQL query Moodle is trying to execute really is an error no matter what mode is in effect. Which brings us back to the question, why isn't everyone getting this problem? I'm out of ideas for now.

But it still would be nice to know which MySQL server you are using.

RLE
Average of ratings: -
In reply to Richard Enison

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Keith Breckenridge -
Hi.

I'm trying to upgrade from 1.8.5 on a brandnew Ubuntu LAMP server with MySQL version 5.0.51a-3ubuntu5.1. I've tried turning off strict mode, and setting it to MySQL4. Results of the SELECT mode:

mysql> SELECT @@global.sql_mode;
+-----------------------------+
| @@global.sql_mode |
+-----------------------------+
| MYSQL40,HIGH_NOT_PRECEDENCE |
+-----------------------------+
1 row in set (0.00 sec)

I've restarted mysqld after changing the settings in my.cnf (also tried it without the restart after setting the global variable.) In all cases - I get the same error described in this thread. Deleting the log table allows the upgrade through without any trouble -- I must just do that as we are nearly at the semester-end. But it seems a little weird. I have the Questionnaire custom module, and I had to reset the UTF encoding for the database, but otherwise I can't think of any other reason. I know that I've seen this error before on upgrading (to 1.8?) but I can't remember how I resolved it.

Average of ratings: -
In reply to Keith Breckenridge

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Ken Wilson -
Looks like the action column is set to BLOB/TEXT for some reason. Try changing it to VARCHAR(40) using phpMyAdmin or the mysql client using these commands:

mysql> USE moodle18; (substitute the database name of your 1.8 install)
mysql> ALTER TABLE `mdl_log` CHANGE `action` `action` VARCHAR( 40 );
mysql> QUIT;

Then re-run the upgrade.

Ken

Average of ratings: -
In reply to Ken Wilson

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by Keith Breckenridge -
Thank you -- that worked perfectly.
Average of ratings: -
In reply to Keith Breckenridge

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by hector whatever -
I'm upgrading from Moodle 1.8.9+ to 1.9.5 and receive the error that was original posted. The person who posted the error doesn't state whether a solution was found. I have been working on this problem for about a month. Please help.
Average of ratings: -
In reply to hector whatever

Re: Upgrading From 1.8 to 1.9: 1170: BLOB/TEXT column 'action' used in key specification without a key length

by hector whatever -

YAY!!!! The fix given for the second post fixed my problem. Thanks.

Average of ratings: -