19 November 2009, 20:48

Hm. Annoying MySQL 

Basically, if you run the upgrader once and it crashes, you have to stop, restore your database from backup, then try again. The errors you get the second time you try the upgrade are irrelevant. This is because MySQL is not capable of modifying the database inside a transaction, so if the upgrader fails, the database will be left in an inconsistent state.

So, we need to solve the first error first . If that one is solved, the second one might well solve itself 

So the first problem is the 'error 150'. Could I get you to try out this and see what you get:

Drop and restore your database from backup

Run the upgrader (of course it will crash with that error)

On the MySQL command line, run this command, and paste the output here:

SHOW ENGINE INNODB STATUS;

And also if you could run these ones too:

SHOW VARIABLES; 

SHOW FULL COLUMNS FROM import_event_subscription;

SHOW KEYS FROM import_event_subscription; 

That'll at least give us an idea of what key to look at.

20 November 2009, 7:00

When upgrading (on the first go and no refresh) I get the error:

Could not execute command: CREATE TABLE import_event_subscription ( id BIGINT(10) unsigned NOT NULL auto_increment, plugin VARCHAR(255) NOT NULL,event VARCHAR(50) NOT NULL, callfunction VARCHAR(255) NOT NULL, CONSTRAINT PRIMARY KEY (id), CONSTRAINT impoevensubs_plu_fk FOREIGN KEY (plugin) REFERENCES import_installed (name), CONSTRAINT impoevensubs_eve_fk FOREIGN KEY (event) REFERENCES event_type (name), CONSTRAINT impoevensubs_pluevecal_uk UNIQUE (plugin, e
 vent, callfunction) )ENGINE=innodb

I ran the instructions and here's the info:

SHOW ENGINE INNODB STATUS;
+-------------------------------------------------------+
| Status                                                |
+-------------------------------------------------------+
|
=====================================
091120 10:40:39 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 302, signal count 302
Mutex spin waits 0, rounds 3138, OS waits 16
RW-shared spins 446, OS waits 223; RW-excl spins 68, OS waits 62
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091120 10:40:35 Error in foreign key constraint of table hmaharadev/import_event_subscription:
FOREIGN KEY (event) REFERENCES event_type (name),
CONSTRAINT impoevensubs_pluevecal_uk UNIQUE (plugin, event, callfunction)
)ENGINE=innodb:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 38494
Purge done for trx's n:o < 0 38252 undo n:o < 0 0
History list length 71
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3080, OS thread id 3032325008
MySQL thread id 77997, query id 2374826 localhost root
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1083 OS file reads, 10055 OS file writes, 8292 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.88 writes/s, 1.88 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 1947, node heap has 3 buffer(s)
4.82 hash searches/s, 26.79 non-hash searches/s
---
LOG
---
Log sequence number 0 16390176
Log flushed up to   0 16390176
Last checkpoint at  0 16268153
0 pending log writes, 0 pending chkp writes
7789 log i/o's done, 1.88 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 18797514; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       1
Database pages     508
Modified db pages  52
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1256, created 761, written 7123
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 3080, id 2996476816, state: sleeping
Number of rows inserted 9082, updated 166, deleted 4, read 64710
0.21 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5.12 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


+---------------------------------+---------------------------------------------------------+
| Variable_name                   | Value                                                   |
+---------------------------------+---------------------------------------------------------+
| auto_increment_increment        | 1                                                       |
| auto_increment_offset           | 1                                                       |
| automatic_sp_privileges         | ON                                                      |
| back_log                        | 50                                                      |
| basedir                         | /usr/                                                   |
| bdb_cache_size                  | 8388600                                                 |
| bdb_home                        | /var/lib/mysql/                                         |
| bdb_log_buffer_size             | 32768                                                   |
| bdb_logdir                      |                                                         |
| bdb_max_lock                    | 10000                                                   |
| bdb_shared_data                 | OFF                                                     |
| bdb_tmpdir                      | /tmp/                                                   |
| binlog_cache_size               | 32768                                                   |
| bulk_insert_buffer_size         | 8388608                                                 |
| character_set_client            | utf8                                                    |
| character_set_connection        | utf8                                                    |
| character_set_database          | utf8                                                    |
| character_set_filesystem        | binary                                                  |
| character_set_results           | utf8                                                    |
| character_set_server            | utf8                                                    |
| character_set_system            | utf8                                                    |
| character_sets_dir              | /usr/share/mysql/charsets/                              |
| collation_connection            | utf8_general_ci                                         |
| collation_database              | utf8_general_ci                                         |
| collation_server                | utf8_general_ci                                         |
| completion_type                 | 0                                                       |
| concurrent_insert               | 1                                                       |
| connect_timeout                 | 5                                                       |
| datadir                         | /var/lib/mysql/                                         |
| date_format                     | %Y-%m-%d                                                |
| datetime_format                 | %Y-%m-%d %H:%i:%s                                       |
| default_week_format             | 0                                                       |
| delay_key_write                 | ON                                                      |
| delayed_insert_limit            | 100                                                     |
| delayed_insert_timeout          | 300                                                     |
| delayed_queue_size              | 1000                                                    |
| div_precision_increment         | 4                                                       |
| engine_condition_pushdown       | OFF                                                     |
| expire_logs_days                | 0                                                       |
| flush                           | OFF                                                     |
| flush_time                      | 0                                                       |
| ft_boolean_syntax               | + -><()~*:""&|                                          |
| ft_max_word_len                 | 84                                                      |
| ft_min_word_len                 | 4                                                       |
| ft_query_expansion_limit        | 20                                                      |
| ft_stopword_file                | (built-in)                                              |
| group_concat_max_len            | 1024                                                    |
| have_archive                    | NO                                                      |
| have_bdb                        | YES                                                     |
| have_blackhole_engine           | NO                                                      |
| have_compress                   | YES                                                     |
| have_crypt                      | YES                                                     |
| have_csv                        | NO                                                      |
| have_dynamic_loading            | YES                                                     |
| have_example_engine             | NO                                                      |
| have_federated_engine           | NO                                                      |
| have_geometry                   | YES                                                     |
| have_innodb                     | YES                                                     |
| have_isam                       | NO                                                      |
| have_merge_engine               | YES                                                     |
| have_ndbcluster                 | NO                                                      |
| have_openssl                    | DISABLED                                                |
| have_ssl                        | DISABLED                                                |
| have_query_cache                | YES                                                     |
| have_raid                       | NO                                                      |
| have_rtree_keys                 | YES                                                     |
| have_symlink                    | YES                                                     |
| hostname                        | ******removed******                                     |
| init_connect                    | SET NAMES utf8                                          |
| init_file                       |                                                         |
| init_slave                      |                                                         |
| innodb_additional_mem_pool_size | 1048576                                                 |
| innodb_autoextend_increment     | 8                                                       |
| innodb_buffer_pool_awe_mem_mb   | 0                                                       |
| innodb_buffer_pool_size         | 8388608                                                 |
| innodb_checksums                | ON                                                      |
| innodb_commit_concurrency       | 0                                                       |
| innodb_concurrency_tickets      | 500                                                     |
| innodb_data_file_path           | ibdata1:10M:autoextend                                  |
| innodb_data_home_dir            |                                                         |
| innodb_doublewrite              | ON                                                      |
| innodb_fast_shutdown            | 1                                                       |
| innodb_file_io_threads          | 4                                                       |
| innodb_file_per_table           | OFF                                                     |
| innodb_flush_log_at_trx_commit  | 1                                                       |
| innodb_flush_method             |                                                         |
| innodb_force_recovery           | 0                                                       |
| innodb_lock_wait_timeout        | 50                                                      |
| innodb_locks_unsafe_for_binlog  | OFF                                                     |
| innodb_log_arch_dir             |                                                         |
| innodb_log_archive              | OFF                                                     |
| innodb_log_buffer_size          | 1048576                                                 |
| innodb_log_file_size            | 5242880                                                 |
| innodb_log_files_in_group       | 2                                                       |
| innodb_log_group_home_dir       | ./                                                      |
| innodb_max_dirty_pages_pct      | 90                                                      |
| innodb_max_purge_lag            | 0                                                       |
| innodb_mirrored_log_groups      | 1                                                       |
| innodb_open_files               | 300                                                     |
| innodb_rollback_on_timeout      | OFF                                                     |
| innodb_support_xa               | ON                                                      |
| innodb_sync_spin_loops          | 20                                                      |
| innodb_table_locks              | ON                                                      |
| innodb_thread_concurrency       | 8                                                       |
| innodb_thread_sleep_delay       | 10000                                                   |
| interactive_timeout             | 28800                                                   |
| join_buffer_size                | 131072                                                  |
| key_buffer_size                 | 8388600                                                 |
| key_cache_age_threshold         | 300                                                     |
| key_cache_block_size            | 1024                                                    |
| key_cache_division_limit        | 100                                                     |
| language                        | /usr/share/mysql/english/                               |
| large_files_support             | ON                                                      |
| large_page_size                 | 0                                                       |
| large_pages                     | OFF                                                     |
| lc_time_names                   | en_US                                                   |
| license                         | GPL                                                     |
| local_infile                    | ON                                                      |
| locked_in_memory                | OFF                                                     |
| log                             | OFF                                                     |
| log_bin                         | OFF                                                     |
| log_bin_trust_function_creators | OFF                                                     |
| log_error                       |                                                         |
| log_queries_not_using_indexes   | OFF                                                     |
| log_slave_updates               | OFF                                                     |
| log_slow_queries                | OFF                                                     |
| log_warnings                    | 1                                                       |
| long_query_time                 | 10                                                      |
| low_priority_updates            | OFF                                                     |
| lower_case_file_system          | OFF                                                     |
| lower_case_table_names          | 0                                                       |
| max_allowed_packet              | 1048576                                                 |
| max_binlog_cache_size           | 4294967295                                              |
| max_binlog_size                 | 1073741824                                              |
| max_connect_errors              | 10                                                      |
| max_connections                 | 100                                                     |
| max_delayed_threads             | 20                                                      |
| max_error_count                 | 64                                                      |
| max_heap_table_size             | 16777216                                                |
| max_insert_delayed_threads      | 20                                                      |
| max_join_size                   | 4294967295                                              |
| max_length_for_sort_data        | 1024                                                    |
| max_prepared_stmt_count         | 16382                                                   |
| max_relay_log_size              | 0                                                       |
| max_seeks_for_key               | 4294967295                                              |
| max_sort_length                 | 1024                                                    |
| max_sp_recursion_depth          | 0                                                       |
| max_tmp_tables                  | 32                                                      |
| max_user_connections            | 0                                                       |
| max_write_lock_count            | 4294967295                                              |
| multi_range_count               | 256                                                     |
| myisam_data_pointer_size        | 6                                                       |
| myisam_max_sort_file_size       | 2147483647                                              |
| myisam_recover_options          | OFF                                                     |
| myisam_repair_threads           | 1                                                       |
| myisam_sort_buffer_size         | 8388608                                                 |
| myisam_stats_method             | nulls_unequal                                           |
| net_buffer_length               | 16384                                                   |
| net_read_timeout                | 30                                                      |
| net_retry_count                 | 10                                                      |
| net_write_timeout               | 60                                                      |
| new                             | OFF                                                     |
| old_passwords                   | ON                                                      |
| open_files_limit                | 1024                                                    |
| optimizer_prune_level           | 1                                                       |
| optimizer_search_depth          | 62                                                      |
| pid_file                        | /var/run/mysqld/mysqld.pid                              |
| port                            | 3306                                                    |
| preload_buffer_size             | 32768                                                   |
| profiling                       | OFF                                                     |
| profiling_history_size          | 15                                                      |
| protocol_version                | 10                                                      |
| query_alloc_block_size          | 8192                                                    |
| query_cache_limit               | 1048576                                                 |
| query_cache_min_res_unit        | 4096                                                    |
| query_cache_size                | 0                                                       |
| query_cache_type                | ON                                                      |
| query_cache_wlock_invalidate    | OFF                                                     |
| query_prealloc_size             | 8192                                                    |
| range_alloc_block_size          | 2048                                                    |
| read_buffer_size                | 131072                                                  |
| read_only                       | OFF                                                     |
| read_rnd_buffer_size            | 262144                                                  |
| relay_log_purge                 | ON                                                      |
| relay_log_space_limit           | 0                                                       |
| rpl_recovery_rank               | 0                                                       |
| secure_auth                     | OFF                                                     |
| secure_file_priv                |                                                         |
| server_id                       | 0                                                       |
| skip_external_locking           | ON                                                      |
| skip_networking                 | OFF                                                     |
| skip_show_database              | OFF                                                     |
| slave_compressed_protocol       | OFF                                                     |
| slave_load_tmpdir               | /tmp/                                                   |
| slave_net_timeout               | 3600                                                    |
| slave_skip_errors               | OFF                                                     |
| slave_transaction_retries       | 10                                                      |
| slow_launch_time                | 2                                                       |
| socket                          | /var/lib/mysql/mysql.sock                               |
| sort_buffer_size                | 2097144                                                 |
| sql_big_selects                 | ON                                                      |
| sql_mode                        |                                                         |
| sql_notes                       | ON                                                      |
| sql_warnings                    | OFF                                                     |
| ssl_ca                          |                                                         |
| ssl_capath                      |                                                         |
| ssl_cert                        |                                                         |
| ssl_cipher                      |                                                         |
| ssl_key                         |                                                         |
| storage_engine                  | MyISAM                                                  |
| sync_binlog                     | 0                                                       |
| sync_frm                        | ON                                                      |
| system_time_zone                | BST                                                     |
| table_cache                     | 64                                                      |
| table_lock_wait_timeout         | 50                                                      |
| table_type                      | MyISAM                                                  |
| thread_cache_size               | 0                                                       |
| thread_stack                    | 196608                                                  |
| time_format                     | %H:%i:%s                                                |
| time_zone                       | SYSTEM                                                  |
| timed_mutexes                   | OFF                                                     |
| tmp_table_size                  | 33554432                                                |
| tmpdir                          | /tmp/                                                   |
| transaction_alloc_block_size    | 8192                                                    |
| transaction_prealloc_size       | 4096                                                    |
| tx_isolation                    | REPEATABLE-READ                                         |
| updatable_views_with_limit      | YES                                                     |
| version                         | 5.0.45                                                  |
| version_bdb                     | Sleepycat Software: Berkeley DB 4.1.24: (July  4, 2007) |
| version_comment                 | Source distribution                                     |
| version_compile_machine         | i686                                                    |
| version_compile_os              | redhat-linux-gnu                                        |
| wait_timeout                    | 28800                                                   |
+---------------------------------+---------------------------------------------------------+

SHOW FULL COLUMNS FROM import_event_subscription;
ERROR 1146 (42S02): Table 'hmaharadev.import_event_subscription' doesn't exist

SHOW KEYS FROM import_event_subscription;
ERROR 1146 (42S02): Table 'hmaharadev.import_event_subscription' doesn't exist

20 November 2009, 7:25

===================================== 091120 10:21:06 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 50 seconds 

 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 838263, signal count 837627 Mutex spin waits 0, rounds 4737735, OS waits 43356 RW-shared spins 1589441, OS waits 774516; RW-excl spins 27367, OS waits 16717  

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 091120  9:41:31 Error in foreign key constraint of table testmahara/mdl_import_event_subscription:  FOREIGN KEY (event) REFERENCES mdl_event_type (name), CONSTRAINT mdl_impoevensubs_pluevecal_uk UNIQUE (plugin, event, callfunction) )ENGINE=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. 

 ------------ TRANSACTIONS ------------ Trx id counter 0 220746521 Purge done for trx's n:o < 0 220746510 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 4645, OS thread id 2965662608 MySQL thread id 27011, query id 14383567 bc-pc-192.barony.ac.uk 10.10.15.149 ahastie SHOW ENGINE INNODB STATUS ---TRANSACTION 0 0, not started, process no 4645, OS thread id 2966068112 MySQL thread id 25445, query id 14383566 bc-pc-192.barony.ac.uk 10.10.15.149 ahastie ---TRANSACTION 0 220733302, not started, process no 4645, OS thread id 3018877840 MySQL thread id 25377, query id 14383562 bc-pc-192.barony.ac.uk 10.10.15.149 ahastie  

-------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0,  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1946986 OS file reads, 2122889 OS file writes, 2024341 OS fsyncs 2.66 reads/s, 17985 avg bytes/read, 1.60 writes/s, 0.74 fsyncs/s 

 ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 1025480 inserts, 1025480 merged recs, 22970 merges Hash table size 34679, used cells 3994, node heap has 5 buffer(s) 14.82 hash searches/s, 19.98 non-hash searches/s --- LOG --- Log sequence number 8 2357156598 Log flushed up to   8 2357156598 Last checkpoint at  8 2357156598 0 pending log writes, 0 pending chkp writes 2008357 log i/o's done, 0.62 log i/o's/second 

---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 23119586; in additional pool allocated 1048576 Buffer pool size   512 Free buffers       0 Database pages     507 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 12545865, created 33743, written 175709 2.92 reads/s, 0.02 creates/s, 0.96 writes/s Buffer pool hit rate 977 / 1000 

SHOW VARIABLES;

SHOW FULL COLUMNS FROM import_event_subscription;

Table 'testmahara.import_event_subscription' doesn't exist

SHOW KEYS FROM import_event_subscription;

Table 'testmahara.import_event_subscription' doesn't exist

Many thanks

Ali

22 November 2009, 17:16

Thanks guys. I should have know the table didn't exist before trying to get keys from it .

At least now we know which key it's failing on. But still, the error is strange. I have double-checked the upgrade and install code, and I can't really see a reason why it might be failing, unless it's something to do with the database encoding.  But both of your SHOW VARIABLES output says that you're using UTF8.

OK, new plan. Do either of you mind sending a mysqldump of your database to [email protected]? I can try upgrading your database myself and see if I can duplicate the problem. 

26 November 2009, 21:25

Hi guys - sorry for the delay, but I've now had a chance to look into this.

I was able to duplicate the problem, and I worked out what it was. Your databases are not utf8 - in particular, the 'name' column of the event_type table uses the collation latin1_swedish_ci. The upgrade tries to create some new tables that foreign key to this column but with a utf8 collation (utf8_general_ci), which is what is failing.

I determined this by issuing a manual 'create table' statement for the import_event_subscription table without including the foreign key. The MySQL commands SHOW FULL COLUMNS FROM [table] and SHOW KEYS FROM [table] were useful to see all the details:  http://paste.dollyfish.net.nz/87825d

27 November 2009, 7:11

Hi Nigel,

unfortunately all my tables are utf8_general_ci the name column in event_type too.

Nevertheless the upgrade hangs at the same spot. Have you guys worked out a solution yet?

Alex

27 November 2009, 7:21

Hi Nigel

Many thanks. Yeah now I see what you mean . If I find a solution will post it here.

Good luck on you new venture mate, all the best and thanks again.

 Ali.

27 November 2009, 7:57

I did a SHOW FULL COLUMNS FROM event_type and it displaying the name column being of utf8_unicode_ci collation.

The database's collation is utf8_general_ci and all I needed to get the upgrade to work was

 ALTER DATABASE maharadb COLLATE='utf8_unicode_ci';

don't ya just love simple solutions?!

27 November 2009, 20:28

ALTER DATABASE command did not work for me Steve but got it fully installed by:

• carrying out a MYSQL dump of my production database
• opened the file with notepad2 searched for latin1 and replaced with UTF8
• created new database and restored the ammended dump file
• used upgrade link within Firefox (IE 7 did not work, weird) and installed fully with no errors within Apache logs.

Not sure if this would help others but I have version 1.2 fully operational apart from little teething problems like students unzipping within their files area but no files appear after unzip. Could be a PHP/Apache configuration.

01 December 2009, 5:15

Hi Steve,

unfortunately this didn't work in my case :(

So collation should be utf8_unicode_ci? Is there any other way to change collations?

Or has anyone figurred out another work around?