Drupal 7.9 Installation Error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

[Jayson Cena]

Hi,

I was searching for a good MySQL Cluster alternative and I stumbled
with Galera. I find it very interesting and its replication technique
is very good compared to other MMM configurations.

I am trying to install Drupal 7.9 on MySQL/Galera when I encountered a
problem. I am not sure if this is a problem with PHP PDO but when I
disabled Galera by commenting the module and the
wsrep_cluster_address, I can install drupal without errors.

Drupal Installation Error:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction

######################
[/etc/my.cnf]
# All files in this package is subject to the GPL v2 license
# More information is in the COPYING file in the top directory of this
package.
# Copyright (C) 2011 severalnines.com

[MYSQLD]
user=mysql
basedir=/usr
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=mysqld.pid
port=3306
log-error=/var/log/mysql.log
relay-log=relay-bin
### INNODB
innodb-buffer-pool-size=175M
innodb-flush-log-at-trx_commit=2
innodb-file-per-table=1
innodb-doublewrite=0
#innodb_log_file_size=64M
#innodb_log_file_size=16M
innodb-log-files-in-group=3
#innodb-buffer-pool-instances=4
innodb-thread-concurrency=0
#innodb-file-format=barracuda
innodb-flush-method = O_DIRECT
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
engine-condition-pushdown=1

#REPLICATION SPECIFIC - GENERAL
#server-id must be unique across all mysql servers participating in
replication.
#server-id=SERVERID
#auto_increment_increment=2
#auto_increment_offset=SERVERID
#REPLICATION SPECIFIC - MASTER
#binlog_format=ROW
#log-bin=binlog
#log-slave-updates=1
#OTHER THINGS, BUFFERS ETC
key_buffer_size = 8M
max-allowed-packet = 16M
sort-buffer-size = 512K
read-buffer-size = 256K
read-rnd-buffer-size = 512K
#thread-cache-size=1024
myisam-sort-buffer_size = 8M
skip-name-resolve
memlock=0
sysdate-is-now=1
max-connections=512
thread-cache-size=512
query-cache-type = 0
query-cache-size = 0
table-open_cache=1024
lower-case-table-names=0
##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Provider specific configuration options
#wsrep_provider_options=

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="my_wsrep_cluster"

# Group communication system handle
#wsrep_cluster_address="gcomm://"
wsrep_cluster_address="gcomm://10.0.0.166:4567"

# Human-readable node name (non-unique). Hostname by default.
#wsrep_node_name=

# Address for incoming client connections. Autodetect by default.
wsrep_node_incoming_address=10.0.0.55

# How many threads will process writesets from other nodes
# (more than one untested)
wsrep_slave_threads=2

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-
master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset
automatically
wsrep_auto_increment_control=1

# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=1

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status - new status of this node
# --uuid - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index - index of this node in the list
#wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=mysqldump

# Address on THIS node to receive SST at. DON'T SET IT TO DONOR
ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first
interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining
nodes.
# Depends on SST method. For mysqldump method it is root:<root
password>
wsrep_sst_auth=root:xxxxxxxxxx

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=
[MYSQL]
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock
[MYSQLD_SAFE]
log-error=/var/log/mysql.log
basedir=/usr
datadir=/var/lib/mysql

######################
mysql> show engine innodb status;

=====================================
111119 14:26:53 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5900 1_second, 5899 sleeps, 564 10_second,
335 background, 335 flush
srv_master_thread log flush and writes: 5897
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 736, signal count 734
Mutex spin waits 572, rounds 17160, OS waits 92
RW-shared spins 641, rounds 19230, OS waits 641
RW-excl spins 0, rounds 90, OS waits 3
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 90.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter AD70
Purge done for trx's n:o < AD70 undo n:o < 0
History list length 1739
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 24, query id 23227 localhost root sleeping
show engine innodb status
---TRANSACTION AC5E, not started
MySQL thread id 2, query id 23123 post SQL applying (11453)
---TRANSACTION AC5B, not started
MySQL thread id 3, query id 23121 post SQL applying (11452)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer
thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0,
0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1064 OS file reads, 57206 OS file writes, 9968 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 363257, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 74303716
Log flushed up to 74303716
Last checkpoint at 74303716
0 pending log writes, 0 pending chkp writes
16526 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 187809792; in additional pool allocated 0
Dictionary memory allocated 232115
Buffer pool size 11199
Free buffers 10505
Database pages 693
Old database pages 235
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 979, created 690, written 40195
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 693, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 8919, id 1206966592, state: waiting for server
activity
Number of rows inserted 7349, updated 463, deleted 5765, read 7045
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

[admin extremeshok.com]

Hi Jayson

Unfortunately Galera does not reach production level quality.

Currently there are 2 major bugs that prevent it from being used:
https://bugs.launchpad.net/codership-mysql/+bug/889523
https://bugs.launchpad.net/codership-mysql/+bug/884615

My advice would be to wait for a fixed 1.1 or a 2.x , the current eta is
around 2 weeks.

[Jayson Cena]

Hi,

Does this also exists on 5.1 version of MySQL?

On Nov 20, 12:00 am, "admin extremeshok.com" <ad...@extremeshok.com>
wrote:

> Hi Jayson
>
> Unfortunately Galera does not reach production level quality.
>

> Currently there are 2 major bugs that prevent it from being used:https://bugs.launchpad.net/codership-mysql/+bug/889523https://bugs.launchpad.net/codership-mysql/+bug/884615

[seppo....@codership.com]

These two bugs concern only MySQL 5.5 based version, and I doubt they
would hurt Drupal 7.9 installation anyways.

We have had good results with Drupal 6 clustering tests, but it turned
out necessary to enable a special variable:
wsrep_drupal_282555_workaround. It should be disabled by default, but
I see that you have it enabled, maybe you tuned this on your own. It
may or may not be a good thing though, Drupal7 has refactored database
access and I expect to see totally new behavior there.

The error message shows that Drupal7 does not treat database deadlock
errors gracefully, or at least there is one situation when deadlock is
not expected and Drupal7 freaks out.

So, I need to investigate this in more detail. To begin with, what is
your cluster installation like? How many Galera nodes and how do you
load balance writes? So when you start Drupal install, do database
writes go to one or several Galera nodes?

-seppo

Quoting Jayson Cena <jayso...@gmail.com>:

> --

[seppo....@codership.com]

I tested Drupal7 install on current 5.5 series 1.1 release candidate
and it seems to install fine on my two node cluster. It may well be
that 1.0 (5.5 series) had a bug, which caused the problem in Drupal
installing.

The 5.1 series 1.0 release should not have same issue.

-seppo

[SyRenity]

Hi.

Just to clarify on this topic, are the latest versions of Galera 5.1 and 5.5 working fine on Drupal 6 (with the wsrep_drupal_282555_

workaround enabled)?

Regards.

[admin extremeshok.com]

The current 1.0 release on mysql5.5 does NOT work.

--
You received this message because you are subscribed to the Google Groups "codership" group.
To view this discussion on the web visit https://groups.google.com/d/msg/codership-team/-/66TtFY2jsTEJ.
To post to this group, send email to codersh...@googlegroups.com.
To unsubscribe from this group, send email to codership-tea...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/codership-team?hl=en.

[SyRenity]

And 5.1?

[Jayson Cena]

5.1 works on Drupal 6 and 7

On Thu, Dec 1, 2011 at 5:51 PM, SyRenity <stas....@gmail.com> wrote:
> And 5.1?

>
> --
> You received this message because you are subscribed to the Google Groups
> "codership" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/codership-team/-/_n1ykuIbUYYJ.

[SyRenity]

Good to know, hope it supported on 5.5 as well soon.