mysql Lock wait timeout exceeded; try restarting transaction

[max101]

I am on a dedicated server. I am trying to upgrade a script called xenforo


I get the following error:

Upgrade Errors
Uh oh, your upgrade to 1.5.24 has failed!

The following elements of the database are incorrect:

Column xf_user.privacy_policy_accepted missing.
Column xf_user.terms_accepted missing.
This is likely caused by an add-on conflict. You may need to restore a backup, remove the offending add-on data from the database, and retry the upgrade. Contact support if you are not sure how to proceed.







I contacted xenforo and they told me to add those 2 columns to mysql.

ALTER TABLE xf_user ADD privacy_policy_accepted INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER is_staff;
ALTER TABLE xf_user ADD terms_accepted INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER privacy_policy_accepted;

I tried adding them but i always get

Lock wait timeout exceeded; try restarting transaction





I tried adding it with the CLI but same problem.




This is my.cnf settings

[mysqld]
performance-schema=0
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
performance-schema=0
innodb=ON
back_log = 512
max_connections = 250
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet=268435456
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
query_cache_limit = 1024K
query_cache_size = 80M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB
log-error=/var/lib/mysql/host.myforum123123.com.err

performance-schema=0
# innodb_large_prefix=1
innodb_purge_threads = 4
# innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 15G
performance-schema=0
innodb_buffer_pool_instances = 15
performance-schema=0
innodb_log_files_in_group = 2
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
# innodb_support_xa=1

performance-schema=0
innodb_io_capacity = 600
innodb_io_capacity_max = 1200
innodb_read_io_threads = 4
innodb_write_io_threads = 4
performance-schema=0
open_files_limit=40000
unix_socket=OFF
[mariadb]
userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 768M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 768M
aria_sort_buffer_size = 192M
[mariadb-5.5]
# innodb_file_format = Barracuda
innodb_file_per_table = 1
query_cache_strip_comments=0
[mysqld_safe]
socket = /var/lib/mysql/mysql.sock
open-files-limit = 8192
[myisamchk]
key-buffer-size=256M
sort_buffer = 32M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
[mariadb-10.0]
# innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_buffer_pool_populate=0
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
[mariadb-10.1]
# innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_buffer_pool_populate=0
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

i changed the
innodb_lock_wait_timeout=50
to 5000

but it just hangs won't complete and never gives me the "Lock wait timeout exceeded" message. I waited 15 minutes.


Xenforo staff says its a server issue.

Seems to me an sql issue also. Since i cant add that query.

How can i investigate this issue?

 

[cPRex]

Hey there! It doesn't sound like this would be related to the cPanel tools on the machine, but we may still be able to help you check MySQL.

If you run "mysqladmin proc status" while you run that Alter Table command, do you see anything odd with the database server that could be a clue? Do you see anything else in the MySQL error logs on the system?

 

[max101]

The only thing i see in the error logs located in log-error=/var/lib/mysql/host.myforum123123.com.err is:

2023-03-31 20:06:07 0 [Note] InnoDB: To roll back: 12 transactions, 22574194 rows
2023-03-31 20:06:22 0 [Note] InnoDB: To roll back: 12 transactions, 22565337 rows
2023-03-31 20:06:37 0 [Note] InnoDB: To roll back: 12 transactions, 22556422 rows
2023-03-31 20:06:52 0 [Note] InnoDB: To roll back: 12 transactions, 22547557 rows
2023-03-31 20:07:07 0 [Note] InnoDB: To roll back: 12 transactions, 22538944 rows
2023-03-31 20:07:22 0 [Note] InnoDB: To roll back: 12 transactions, 22530235 rows
2023-03-31 20:07:37 0 [Note] InnoDB: To roll back: 12 transactions, 22521685 rows
2023-03-31 20:07:52 0 [Note] InnoDB: To roll back: 12 transactions, 22513615 rows
2023-03-31 20:08:07 0 [Note] InnoDB: To roll back: 12 transactions, 22505298 rows
2023-03-31 20:08:22 0 [Note] InnoDB: To roll back: 12 transactions, 22496413 rows



This is what i saw when i ran mysqladmin proc status while running the alter command




Does Waiting for table metadata lock have anything to do with it?

 

[cPRex]

I'm honestly not sure on this one - that indicates that *something* is locking the database.

If you restart MySQL an d then run that command immediately after, does that perform better?

 

[max101]

I tried that. Restarted, right after put the query but i still get that same message

Lock wait timeout exceeded; try restarting transaction

I dont know what is locking the database.

 

[cPRex]

If you'd like to submit a ticket to our team we'd be happy to take a look for you!

 

[max101]

I didn't know I could submit a ticket. This will be my first time.

thanks, i will do that.

 

[cPRex]

Absolutely - we do encourage people to reach out to the partner where they purchased their cPanel license from first, but we're happy to look at any dedicated server.