ChrisDekker
ChrisDekker

Reputation: 1773

Recently getting Rails-Mysql connection issues in production

Recently I have been getting strange errors in my production environment, running a rails 3.2.22 app with Mysql 5.6 using the mysql2 gem version 0.3.20

The errors appear seemingly random and if I try to reproduce the same situation, query, etc things go fine. Both in production and dev environments.

These are the errors I am getting:

A NoMethodError occurred in invoice_batches#show:

  undefined method `each' for nil:NilClass
  activerecord (3.2.22) lib/active_record/associations/preloader/association.rb:88:in `block in associated_records_by_owner'

.

A NoMethodError occurred in invoice_batches#show:

  undefined method `fields' for nil:NilClass
  activerecord (3.2.22) lib/active_record/connection_adapters/mysql2_adapter.rb:218:in `exec_query'

Stack inspection only shows internal Rails code, and none of my own code to debug.

Additionally, these native MySQL errors keep popping up

An ActiveRecord::StatementInvalid occurred in invoice_batches#show:

  Mysql2::Error: Lost connection to MySQL server during query: SELECT  `users`.* FROM `users`  WHERE `users`.`archived` = 0 AND `users`.`id` = 63 LIMIT 1
  activerecord (3.2.22) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'

and

An ActiveRecord::StatementInvalid occurred in templates#test_contract:

  Mysql2::Error: Malformed packet: SELECT `permissions`.`pkey` FROM `permissions`  WHERE `permissions`.`user_id` = 33 AND `permissions`.`client_id` = 7
  activerecord (3.2.22) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'

Which, again, I cannot seem to be able to reproduce or trace back to a cause in my code.

Any idea what this could be? I recently updated both the Mysql2 gem to the latest version (0.3.20) as well as the MySQL server from 5.5. to 5.6 Percona. These errors happened before and are still happening. Usually in close succession.

My database.yml has the following content for production:

production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: (...)
  pool: 5
  username: (...)
  password: (...)
  host: localhost
  flags: <%= 65536 | 131072 %>

I generated a completely new my.cnf config file when I upgraded from Mysql 5.5 to 5.6 Percona using a wizard. This are the contents. I had to manually add the collation lines since I got some errors after restoring the dumps from the previous version.

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name Mysql56-1 generated for (...) at 2015-12-13 22:03:44

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid-file                       = /var/run/mysqld/mysqld.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1
log_bin_trust_function_creators = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 100
thread-cache-size              = 16
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql-slow.log

character-set-server  = utf8
collation-server = utf8_general_ci

This server runs virtually with Ubuntu 14.04, 4 cores and 6GB RAM. The server runs everything locally. Apache, Passenger, Ruby and MySQL on the same VM.

Could this be a memory issue? Is 2G too much for a non-dedicated DB server with 6GB RAM? While MySQL seems to be the only process taking up a large chunk of the total memory, there still seems to only be ~500-1000MB free memory at all times.

Upvotes: 2

Views: 1868

Answers (1)

Tsutomu
Tsutomu

Reputation: 5148

Your problem may have relation with the db connection sharing.

See the long discussion at https://gist.github.com/josevalim/470808.

They mention the undefined method 'fields' for nil:NilClass error many times.

As far as I understand, this error occurs when two or more threads share a connection and a thread rollbacks the transaction before others complete all queries.

I don't know if this discussion is relevant to your problem, but it seems to me that your problem is related with concurrency, not with memory allocation.

Upvotes: 1

Related Questions