Problem upgrading to vbulletin latest version

[John Miller]

I am upgrading from vbulletin 3.0.x version to latest version.

I am on current step,

http://www.mydomain.com/install/upgrade_350b1.php?step=4

which displays the following result in browser window

Step 4) Miscellaneous table alterations 4/6

Altering subscribeevent Table (1 of 5)
Altering subscribeevent Table (2 of 5)
Altering subscribeevent Table (3 of 5)
Altering subscribeevent Table (4 of 5)
Altering subscribeevent Table (5 of 5)
Altering attachment Table (1 of 1)
Altering event Table (1 of 3)
Altering event Table (2 of 3)
Altering event Table (3 of 3)
Altering attachment Table (1 of 1)
Altering customavatar Table (1 of 1)
Altering customprofilepic Table (1 of 1)
Altering cron Table (1 of 1)


Now after click on step 4, i am unable to goto this page

http://www.mydomain.com/install/upgrade_350b1.php?step=5

it timeout, nothing happens even for 30 mins.

Our total attachments size is 1 mb only.

please help at earliest
thanks.

[John Miller]

we have more then 8,00,000 threads in our forum.

[Steve Machol]

This can take many, many hours. You'll just need to wait I'm afraid.

[John Miller]

This can take many, many hours. You'll just need to wait I'm afraid.

is there any faster alternative to this, we have another forum which has over 64,00,000 threads.
thanks.

[Steve Machol]

I've asked the Devs to take a look at this. Sorry for the inconvenience.

[John Miller]

HI Steve,
thanks for understanding customers grievance, i have finished the step 6

$query[] = "ALTER TABLE " . TABLE_PREFIX . "thread CHANGE postusername postusername VARCHAR (100) NOT NULL DEFAULT ''";
	$explain[] = sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'thread', 1, 1);

	$query[] = "ALTER TABLE " . TABLE_PREFIX . "post CHANGE username username VARCHAR (100) NOT NULL DEFAULT ''";
	$explain[] = sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'post', 1, 1);

from upgrade_350b1.php file

although the process is hung again on step 3 for upgrade_350rc3.php file

$query[]['hideerror'] = "ALTER TABLE " . TABLE_PREFIX . "post DROP INDEX iconid";
	$explain[] = sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'post', 1, 1);

being huge amount of data in post and thread tables, any alterations made to this tables are taking huge time.

FYI:
I am checking the same via server_processlist.php under PhpMyAdmin for WHM, once the above process gets completed even though the scripts show a timeout on browser frontend, I have to continute the process further from steps mentioned above in upgrade files. (might be handy for other users)

if we have some easier mechanisms to troubleshoot this problem, this shall be a major advantage to avoid heavy downtimes for large/active forums.

thanks again.

[John Miller]

Upgrade was done after around 28+ hours, everything went fine.

I tried to implement FULL Text Search on VB 3.5.3 version changing the Search type from admincp, since this process was creating heavy server loads on server I had to kill the same.

Now i am getting this error, clicking on any forum/thread/post etc.

Database error in vBulletin 3.5.3:

Invalid SQL:

			SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
			FROM O_post AS post
			WHERE post.userid = 1 AND
			post.visible = 1 AND
			post.threadid IN (0,940910,932032,939757,942881,939642,939254,939140,938261,936970,934363,934362,933739,914595,931560,931392,930603,929656,929416,914602,928825,927042,925818,914601,914600,914599,914598,914597,914596,914591,914594)
			GROUP BY threadid;

MySQL Error  : Can't open file: 'O_post.MYI'. (errno: 144)
Error Number : 1016
Date         : Wednesday, February 8th 2006 @ 02:59:27 PM
Script       : http://www.mydomain.com/forumdisplay.php?f=121
Referrer     : http://www.mydomain.com/index.php?
IP Address   : 143.215.78.247
Username     : Administrator
Classname    : vb_database

[George L]

Hi John does your vB forum have prefix 0_ ?

perror 144
MySQL error: 144 = Table is crashed and last repair failed

run this command on your database in ssh telnet

mysqlcheck -q -r -u root -p dbname

where dbname = your database name

it may take some while to complete the repair due to size of your forums

conincidently what hardware you're on ? care to provide some info asked at http://www.vbulletin.com/forum/showthread.php?t=70117

[John Miller]

hi eva,
thanks for your inputs, yes the forum tables have a prefix "O_"

I have ran the repair utility once, from tools.php (uploading this to my admincp folder), but there is another problem.
I started the repair process 12 hours ago, now watch server_processlist.php via PHPmyAdmin, I can find lot of other processes with this sql query waiting in queue.

SELECT post . * ,
IF (
visible =2, 1, 0
) AS isdeleted, editlog.userid AS edit_userid, editlog

How can i get rid fo this process, till the repair is done. ?

[John Miller]

Hi John does your vB forum have prefix 0_ ?

perror 144
MySQL error: 144 = Table is crashed and last repair failed

run this command on your database in ssh telnet

mysqlcheck -q -r -u root -p dbname

where dbname = your database name

it may take some while to complete the repair due to size of your forums

conincidently what hardware you're on ? care to provide some info asked at http://www.vbulletin.com/forum/showthread.php?t=70117

1.
Dedicated Server

2.
P4-3.0 with 1GB RAM with 50GB of disk space. Hard drives are Western Digital SATA. RedHat 9.0 with Apache 1.3.33 with PHP 4.4.0 and MySQL 4.0.25.

3.
I am not sure about this part ?

4.
Via official MySQL RPMS.

5.
top stats.

  08:32:16  up 21:53,  1 user,  load average: 1.14, 1.13, 1.04
57 processes: 56 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states:   0.0% user   1.0% system    0.0% nice   0.0% iowait  98.0% 
idle
CPU1 states:   0.0% user   0.1% system    0.0% nice   0.0% iowait  99.0% 
idle
Mem:  1028568k av, 1022260k used,    6308k free,       0k shrd,   34860k 
buff
        159028k active,             725360k inactive
Swap: 4192880k av,   67008k used, 4125872k free                  750824k 
cached

   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
20688 root      18   0  1004 1004   740 R     1.9  0.0   0:00   0 top
  5308 mysql      9   0 30004  29M  2224 S     0.9  2.9  60:00   1 mysqld
     1 root       8   0   412  384   364 S     0.0  0.0   0:01   0 init
18570 root       9   0   544  500   464 S     0.0  0.0   0:04   0 syslogd
18581 named      9   0  2736 2380  1480 S     0.0  0.2   0:08   1 named
18595 root       9   0  1068  916   836 S     0.0  0.0   0:00   0 sshd
18605 root       9   0   688  540   540 S     0.0  0.0   0:00   0 xinetd
18625 root       9   0  7796 1776  1172 S     0.0  0.1   0:00   0 chkservd
18697 mailnull   0   0  1256 1000   928 S     0.0  0.0   0:08   1 exim
18705 mailnull   9   0  1180  860   860 S     0.0  0.0   0:00   0 exim
18711 root       9   0  1080  852   772 S     0.0  0.0   0:07   0 
antirelayd
18757 root       8   0   580  576   540 S     0.0  0.0   0:00   1 crond
18999 root      19  19 13056 6836  2108 S N   0.0  0.6   0:11   1 
cpanellogd
19019 root       8   0  5300 2180  1972 S     0.0  0.2   0:00   1 cppop
19070 root       9   0   396  328   328 S     0.0  0.0   0:00   0 
portsentry
  8625 root       8   0   824  604   564 S     0.0  0.0   0:00   1 
pure-ftpd
  8628 root       8   0   508  356   352 S     0.0  0.0   0:00   0 
pure-authd
  2274 root       8   0  3692  856   816 S     0.0  0.0   2:06   0 httpd
  5274 root       9   0  1048 1048   896 S     0.0  0.1   0:00   0 
mysqld_safe
  6498 cpanel     9   0  1760 1696  1280 S     0.0  0.1   0:00   1 
stunnel-4.04loc
  6520 root       9   0  6688 3152  2164 S     0.0  0.3   0:00   0 cpsrvd
20252 root       9   0  1828 1828  1680 S     0.0  0.1   0:00   0 sshd
20258 servint    9   0  1808 1808  1652 S     0.0  0.1   0:00   1 sshd
20259 servint    9   0  1388 1388   860 S     0.0  0.1   0:00   1 tcsh
20287 root       9   0  1036 1036   712 S     0.0  0.1   0:00   1 su
20302 root      14   0  1420 1420   856 S     0.0  0.1   0:00   1 tcsh
20595 root       9   0  1196 1196   956 S     0.0  0.1   0:00   0 bash

6. contents inside of my.cnf

+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                   | Value 
|
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log                        | 50 
|
| basedir                         | / 
|
| binlog_cache_size               | 32768 
|
| bulk_insert_buffer_size         | 8388608 
|
| character_set                   | latin1 
|
| character_sets                  | latin1 big5 czech euc_kr gb2312 gbk 
latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 
cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek 
win1250 croat cp1257 latin5 |
| concurrent_insert               | ON 
|
| connect_timeout                 | 5 
|
| convert_character_set           | 
|
| datadir                         | /var/lib/mysql/ 
|
| default_week_format             | 0 
|
| delay_key_write                 | ON 
|
| delayed_insert_limit            | 100 
|
| delayed_insert_timeout          | 300 
|
| delayed_queue_size              | 1000 
|
| flush                           | OFF 
|
| flush_time                      | 0 
|
| ft_boolean_syntax               | + -><()~*:""&| 
|
| ft_min_word_len                 | 4 
|
| ft_max_word_len                 | 254 
|
| ft_max_word_len_for_sort        | 20 
|
| ft_stopword_file                | (built-in) 
|
| have_bdb                        | NO 
|
| have_crypt                      | YES 
|
| have_innodb                     | YES 
|
| have_isam                       | YES 
|
| have_raid                       | NO 
|
| have_symlink                    | YES 
|
| have_openssl                    | NO 
|
| have_query_cache                | YES 
|
| init_file                       | 
|
| innodb_additional_mem_pool_size | 1048576 
|
| innodb_autoextend_increment     | 8 
|
| innodb_buffer_pool_size         | 8388608 
|
| innodb_data_file_path           | ibdata1:10M:autoextend 
|
| innodb_data_home_dir            | 
|
| innodb_file_io_threads          | 4 
|
| innodb_force_recovery           | 0 
|
| innodb_thread_concurrency       | 8 
|
| innodb_flush_log_at_trx_commit  | 1 
|
| innodb_fast_shutdown            | ON 
|
| innodb_flush_method             | 
|
| innodb_lock_wait_timeout        | 50 
|
| 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_mirrored_log_groups      | 1 
|
| innodb_max_dirty_pages_pct      | 90 
|
| innodb_max_purge_lag            | 0 
|
| innodb_table_locks              | ON 
|
| interactive_timeout             | 15 
|
| join_buffer_size                | 131072 
|
| key_buffer_size                 | 8388600 
|
| language                        | /usr/share/mysql/english/ 
|
| large_files_support             | ON 
|
| license                         | GPL 
|
| local_infile                    | ON 
|
| locked_in_memory                | OFF 
|
| log                             | OFF 
|
| log_update                      | OFF 
|
| log_bin                         | 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              | 52427776 
|
| max_binlog_cache_size           | 4294967295 
|
| max_binlog_size                 | 1073741824 
|
| max_connections                 | 500 
|
| max_connect_errors              | 10 
|
| max_delayed_threads             | 5 
|
| max_insert_delayed_threads      | 5 
|
| max_heap_table_size             | 16777216 
|
| max_join_size                   | 4294967295 
|
| max_relay_log_size              | 0 
|
| max_seeks_for_key               | 4294967295 
|
| max_sort_length                 | 1024 
|
| max_user_connections            | 0 
|
| max_tmp_tables                  | 32 
|
| max_write_lock_count            | 4294967295 
|
| myisam_max_extra_sort_file_size | 268435456 
|
| myisam_max_sort_file_size       | 2147483647 
|
| myisam_repair_threads           | 1 
|
| myisam_recover_options          | OFF 
|
| myisam_sort_buffer_size         | 8388608 
|
| net_buffer_length               | 16384 
|
| net_read_timeout                | 30 
|
| net_retry_count                 | 10 
|
| net_write_timeout               | 60 
|
| new                             | OFF 
|
| open_files_limit                | 2510 
|
| pid_file                        | 
/var/lib/mysql/hostname.MYHOST.net.pid 
|
| log_error                       | 
|
| port                            | 3306 
|
| protocol_version                | 10 
|
| query_alloc_block_size          | 8192 
|
| query_cache_limit               | 1048576 
|
| query_cache_size                | 4194304 
|
| 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 
|
| rpl_recovery_rank               | 0 
|
| server_id                       | 0 
|
| slave_net_timeout               | 3600 
|
| skip_external_locking           | ON 
|
| skip_networking                 | OFF 
|
| skip_show_database              | OFF 
|
| slow_launch_time                | 2 
|
| socket                          | /var/lib/mysql/mysql.sock 
|
| sort_buffer_size                | 2097144 
|
| sql_mode                        | 0 
|
| table_cache                     | 256 
|
| table_type                      | MYISAM 
|
| thread_cache_size               | 5 
|
| thread_stack                    | 126976 
|
| tx_isolation                    | REPEATABLE-READ 
|
| timezone                        | IST 
|
| tmp_table_size                  | 33554432 
|
| tmpdir                          | /tmp/ 
|
| transaction_alloc_block_size    | 8192 
|
| transaction_prealloc_size       | 4096 
|
| version                         | 4.0.25-standard 
|
| version_comment                 | Official MySQL RPM 
|
| version_compile_os              | pc-linux-gnu 
|
| wait_timeout                    | 1800 
|
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

7.
mysql extended-status output

[root@hostname ~]# mysqladmin ext stat ver
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 23         |
| Aborted_connects         | 1          |
| Bytes_received           | 3968294405 |
| Bytes_sent               | 3963431818 |
| Com_admin_commands       | 1119       |
| Com_alter_table          | 100        |
| Com_analyze              | 0          |
| Com_backup_table         | 0          |
| Com_begin                | 1          |
| Com_change_db            | 103        |
| Com_change_master        | 0          |
| Com_check                | 91         |
| Com_commit               | 1          |
| Com_create_db            | 2          |
| Com_create_function      | 0          |
| Com_create_index         | 0          |
| Com_create_table         | 50         |
| Com_delete               | 51         |
| Com_delete_multi         | 0          |
| Com_drop_db              | 1          |
| Com_drop_function        | 0          |
| Com_drop_index           | 0          |
| Com_drop_table           | 50         |
| Com_flush                | 19         |
| Com_grant                | 18         |
| Com_ha_close             | 0          |
| Com_ha_open              | 0          |
| Com_ha_read              | 0          |
| Com_insert               | 3852       |
| Com_insert_select        | 0          |
| Com_kill                 | 7          |
| Com_load                 | 0          |
| Com_load_master_data     | 0          |
| Com_load_master_table    | 0          |
| Com_lock_tables          | 72         |
| Com_optimize             | 91         |
| Com_purge                | 0          |
| Com_rename_table         | 0          |
| Com_repair               | 0          |
| Com_replace              | 16         |
| Com_replace_select       | 0          |
| Com_reset                | 0          |
| Com_restore_table        | 0          |
| Com_revoke               | 0          |
| Com_rollback             | 3          |
| Com_savepoint            | 0          |
| Com_select               | 666        |
| Com_set_option           | 135        |
| Com_show_binlog_events   | 0          |
| Com_show_binlogs         | 22         |
| Com_show_create          | 631        |
| Com_show_databases       | 41         |
| Com_show_fields          | 374        |
| Com_show_grants          | 9          |
| Com_show_keys            | 272        |
| Com_show_logs            | 0          |
| Com_show_master_status   | 0          |
| Com_show_new_master      | 0          |
| Com_show_open_tables     | 0          |
| Com_show_processlist     | 76         |
| Com_show_slave_hosts     | 0          |
| Com_show_slave_status    | 0          |
| Com_show_status          | 5          |
| Com_show_innodb_status   | 0          |
| Com_show_tables          | 273        |
| Com_show_variables       | 9          |
| Com_slave_start          | 0          |
| Com_slave_stop           | 0          |
| Com_truncate             | 0          |
| Com_unlock_tables        | 72         |
| Com_update               | 421        |
| Com_update_multi         | 6          |
| Connections              | 235        |
| Created_tmp_disk_tables  | 1          |
| Created_tmp_tables       | 7          |
| Created_tmp_files        | 8          |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_commit           | 0          |
| Handler_delete           | 4          |
| Handler_read_first       | 370        |
| Handler_read_key         | 7201       |
| Handler_read_next        | 96426112   |
| Handler_read_prev        | 0          |
| Handler_read_rnd         | 7882       |
| Handler_read_rnd_next    | 3970946    |
| Handler_rollback         | 1          |
| Handler_update           | 4521       |
| Handler_write            | 2810165    |
| Key_blocks_used          | 7793       |
| Key_read_requests        | 169026481  |
| Key_reads                | 2693929    |
| Key_write_requests       | 20583389   |
| Key_writes               | 2313880    |
| Max_used_connections     | 7          |
| Not_flushed_key_blocks   | 4739       |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 256        |
| Open_files               | 497        |
| Open_streams             | 0          |
| Opened_tables            | 610        |
| Questions                | 8032       |
| Qcache_queries_in_cache  | 262        |
| Qcache_inserts           | 401        |
| Qcache_hits              | 273        |
| Qcache_lowmem_prunes     | 0          |
| Qcache_not_cached        | 263        |
| Qcache_free_memory       | 1597744    |
| Qcache_free_blocks       | 6          |
| Qcache_total_blocks      | 745        |
| Rpl_status               | NULL       |
| Select_full_join         | 2          |
| Select_full_range_join   | 0          |
| Select_range             | 46         |
| Select_range_check       | 0          |
| Select_scan              | 134        |
| Slave_open_temp_tables   | 0          |
| Slave_running            | OFF        |
| Slow_launch_threads      | 0          |
| Slow_queries             | 12         |
| Sort_merge_passes        | 0          |
| Sort_range               | 7          |
| Sort_rows                | 7867       |
| Sort_scan                | 10         |
| Table_locks_immediate    | 2499       |
| Table_locks_waited       | 0          |
| Threads_cached           | 3          |
| Threads_created          | 8          |
| Threads_connected        | 2          |
| Threads_running          | 2          |
| Uptime                   | 15325      |
+--------------------------+------------+
Uptime: 15325  Threads: 2  Questions: 8033  Slow queries: 12  Opens: 610 
Flush tables: 1  Open tables: 256  Queries per second avg: 0.524
mysqladmin  Ver 8.40 Distrib 4.0.25, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          4.0.25-standard
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 4 hours 15 min 25 sec

Threads: 2  Questions: 8033  Slow queries: 12  Opens: 610  Flush tables: 1 
Open tables: 256  Queries per second avg: 0.524

8.
Currently during upgradation, I have closed all the other sites/scripts on server, since upgrade process is taking a very very long time.

9.
Average of 100 users online on entire server with a 10 minutes cookie timeout, maximum of 300 users.

10.
Eva, Please check your PM.

11.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
MaxRequestsPerChild 0

[John Miller]

I am facing major problems to upgrade to latest version, hence posted the above details as requested by eva2000.

Waiting for your replies to help me out here.

thanks.

[George L]

hi eva,
thanks for your inputs, yes the forum tables have a prefix "O_"

I have ran the repair utility once, from tools.php (uploading this to my admincp folder), but there is another problem.
I started the repair process 12 hours ago, now watch server_processlist.php via PHPmyAdmin, I can find lot of other processes with this sql query waiting in queue.


How can i get rid fo this process, till the repair is done. ?

You running forum's open to public while repairing database ? you should close the forum to help speed things up

As to your upgrade problem, i can definitely tell you for a 21GB database size and 8million threads/posts, your server hardware would be struggling to keep up in server intensive tasks such as this... i can see why you're having major problems when it comes to anything that touches your post table at least.

In all honesty, i would first upgrade your server hardware before attempting the 64 million thread forum upgrade... honestly you'd need at least dual intel p4 3.2ghz xeons (or better yet dual opteron 250 cpus http://www.vbulletin.com/forum/showthread.php?t=160103) with 4-8GB ram mini with fast 15k scsi raid 10 disks to even grasp doing a 64 million thread upgrade i'd imagine db size would be well over 64/8 x 21GB = 160GB then ????

Server you have for 8 million thread/21GB db size forum is under spec'd but my.cnf can be tweaked to better handle the repair process a tad although that involves increasing mysql memory buffers which might be limited by your 1GB ram... if you want to provide me with ssh telnet root and mysql root password for your server i can try to tweak it so it helps more than what you have it as now, BUT it will take a very long time to perform upgrades, repair or backup/import your database on the hardware you have now.

[John Miller]

hi eva,
forums are closed while making any database changes/upgradation (behind .htaacess to prevent running any additional queries too)

21 GB is total size of all databases on server, 2 sites are active to public.
Site A = 4 GB mysql database size
Site B = 3 GB mysql database size

I am facing problems with Site A, database size for Site A = 4 GB

Can you please give me proper my.cnf values which can be used while upgrading database / vbulletin forum software ?

and also

my.cnf values, which can be used when sites are live to public.

I am in process of hardware upgradation again, but this might take couple of more days.

thanks.

[John Miller]

hello support,
can someone kindly help us out here ?
entire site is down since last 4 days now
with a corrupt database, if any help is given out here, we will be able to get our site online back again.

this is my mysql process list for repairing the db.

+-----+------+-----------+------------------+---------+-------+-------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------------------+---------+-------+-------------------+-----------------------+
| 64 | root | localhost | corrupt_db | Query | 34999 | Repair by sorting | REPAIR TABLE `O_post` |
| 352 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+------------------+---------+-------+-------------------+-----------------------+

thanks.

[Steve Machol]

These are all the known ways to fix db corruption:

http://www.vbulletin.com/forum/showthread.php?s=&threadid=44909