class Mysql

MySQL connection class. @example

my = Mysql.connect('hostname', 'user', 'password', 'dbname')
res = my.query 'select col1,col2 from tbl where id=123'
res.each do |c1, c2|
  p c1, c2
end

Copyright © 2003 TOMITA Masahiro tommy@tmtm.org

Copyright © 2003-2010 TOMITA Masahiro tommy@tmtm.org

Constants

CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
CLIENT_CAPABILITY_EXTENSION
CLIENT_COMPRESS
CLIENT_CONNECT_ATTRS
CLIENT_CONNECT_WITH_DB
CLIENT_DEPRECATE_EOF
CLIENT_FOUND_ROWS
CLIENT_IGNORE_SIGPIPE
CLIENT_IGNORE_SPACE
CLIENT_INTERACTIVE
CLIENT_LOCAL_FILES
CLIENT_LONG_FLAG
CLIENT_LONG_PASSWORD

Client flag

CLIENT_MULTI_RESULTS
CLIENT_MULTI_STATEMENTS
CLIENT_NO_SCHEMA
CLIENT_ODBC
CLIENT_OPTIONAL_RESULTSET_METADATA
CLIENT_PLUGIN_AUTH
CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA
CLIENT_PROTOCOL_41
CLIENT_PS_MULTI_RESULTS
CLIENT_REMEMBER_OPTIONS
CLIENT_RESERVED
CLIENT_SECURE_CONNECTION
CLIENT_SESSION_TRACK
CLIENT_SSL
CLIENT_SSL_VERIFY_SERVER_CERT
CLIENT_TRANSACTIONS
CLIENT_ZSTD_COMPRESSION_ALGORITHM
COM_BINLOG_DUMP
COM_BINLOG_DUMP_GTID
COM_CHANGE_USER
COM_CLONE
COM_CONNECT
COM_CONNECT_OUT
COM_CREATE_DB
COM_DAEMON
COM_DEBUG
COM_DELAYED_INSERT
COM_DROP_DB
COM_FIELD_LIST
COM_INIT_DB
COM_PING
COM_PROCESS_INFO
COM_PROCESS_KILL
COM_QUERY
COM_QUIT
COM_REFRESH
COM_REGISTER_SLAVE
COM_RESET_CONNECTION
COM_SET_OPTION
COM_SHUTDOWN
COM_SLEEP

Command

COM_STATISTICS
COM_STMT_CLOSE
COM_STMT_EXECUTE
COM_STMT_FETCH
COM_STMT_PREPARE
COM_STMT_RESET
COM_STMT_SEND_LONG_DATA
COM_TABLE_DUMP
COM_TIME
DEFAULT_AUTH
DEFAULT_OPTS

@!attribute [rw] host

@return [String, nil]

@!attribute [rw] username

@return [String, nil]

@!attribute [rw] password

@return [String, nil]

@!attribute [rw] database

@return [String, nil]

@!attribute [rw] port

@return [Integer, String, nil]

@!attribute [rw] socket

@return [String, nil] socket filename

@!attribute [rw] flags

@return [Integer, nil]

@!attribute [rw] connect_timeout

@return [Numeric, nil]

@!attribute [rw] read_timeout

@return [Numeric, nil]

@!attribute [rw] write_timeout

@return [Numeric, nil]

@!attribute [rw] init_command

@return [String, nil]

@!attribute [rw] local_infile

@return [Boolean]

@!attribute [rw] load_data_local_dir

@return [String, nil]

@!attribute [rw] ssl_mode

@return [String, Integer] 1 or "disabled" / 2 or "preferred" / 3 or "required"

@!attribute [rw] get_server_public_key

@return [Boolean]
ENABLE_CLEARTEXT_PLUGIN
INIT_COMMAND
MYSQL_TCP_PORT
MYSQL_UNIX_PORT
OPTION_MULTI_STATEMENTS_OFF
OPTION_MULTI_STATEMENTS_ON

Server Option

OPT_BIND
OPT_CAN_HANDLE_EXPIRED_PASSWORDS
OPT_COMPRESS
OPT_COMPRESSION_ALGORITHMS
OPT_CONNECT_ATTR_ADD
OPT_CONNECT_ATTR_DELETE
OPT_CONNECT_ATTR_RESET
OPT_CONNECT_TIMEOUT

Connection Option

OPT_GET_SERVER_PUBLIC_KEY
OPT_LOAD_DATA_LOCAL_DIR
OPT_LOCAL_INFILE
OPT_MAX_ALLOWED_PACKET
OPT_NAMED_PIPE
OPT_NET_BUFFER_LENGTH
OPT_OPTIONAL_RESULTSET_METADATA
OPT_PROTOCOL
OPT_READ_TIMEOUT
OPT_RECONNECT
OPT_RETRY_COUNT
OPT_SSL_CA
OPT_SSL_CAPATH
OPT_SSL_CERT
OPT_SSL_CIPHER
OPT_SSL_CRL
OPT_SSL_CRLPATH
OPT_SSL_FIPS_MODE
OPT_SSL_KEY
OPT_SSL_MODE
OPT_TLS_CIPHERSUITES
OPT_TLS_VERSION
OPT_USE_RESULT
OPT_WRITE_TIMEOUT
OPT_ZSTD_COMPRESSION_LEVEL
PLUGIN_DIR
READ_DEFAULT_FILE
READ_DEFAULT_GROUP
REFRESH_BINARY_LOG
REFRESH_DES_KEY_FILE
REFRESH_ENGINE_LOG
REFRESH_ERROR_LOG
REFRESH_FAST
REFRESH_FOR_EXPORT
REFRESH_GENERAL_LOG
REFRESH_GRANT

Refresh parameter

REFRESH_HOSTS
REFRESH_LOG
REFRESH_MASTER
REFRESH_OPTIMIZER_COSTS
REFRESH_PERSIST
REFRESH_QUERY_CACHE
REFRESH_QUERY_CACHE_FREE
REFRESH_READ_LOCK
REFRESH_RELAY_LOG
REFRESH_SLAVE
REFRESH_SLOW_LOG
REFRESH_STATUS
REFRESH_TABLES
REFRESH_THREADS
REFRESH_USER_RESOURCES
REPORT_DATA_TRUNCATION
SERVER_MORE_RESULTS_EXISTS
SERVER_PS_OUT_PARAMS
SERVER_PUBLIC_KEY
SERVER_QUERY_NO_GOOD_INDEX_USED
SERVER_QUERY_NO_INDEX_USED
SERVER_QUERY_WAS_SLOW
SERVER_SESSION_STATE_CHANGED
SERVER_STATUS_AUTOCOMMIT
SERVER_STATUS_CURSOR_EXISTS
SERVER_STATUS_DB_DROPPED
SERVER_STATUS_IN_TRANS

Server Status

SERVER_STATUS_IN_TRANS_READONLY
SERVER_STATUS_LAST_ROW_SENT
SERVER_STATUS_METADATA_CHANGED
SERVER_STATUS_NO_BACKSLASH_ESCAPES
SET_CHARSET_DIR
SET_CHARSET_NAME
SHARED_MEMORY_BASE_NAME
SSL_MODE_DISABLED

SSL Mode

SSL_MODE_PREFERRED
SSL_MODE_REQUIRED
SSL_MODE_VERIFY_CA
SSL_MODE_VERIFY_IDENTITY
VERSION

Attributes

protocol[R]

@private

Public Class Methods

connect(*args, **opts) click to toggle source

Make Mysql object and connect to mysqld. parameter is same as arguments for {#initialize}. @return [Mysql]

# File lib/mysql.rb, line 82
def connect(*args, **opts)
  self.new(*args, **opts).connect
end
escape_string(str) click to toggle source

Escape special character in string. @param [String] str @return [String]

# File lib/mysql.rb, line 89
def escape_string(str)
  str.gsub(/[\0\n\r\\\'\"\x1a]/) do |s|
    case s
    when "\0" then "\\0"
    when "\n" then "\\n"
    when "\r" then "\\r"
    when "\x1a" then "\\Z"
    else "\\#{s}"
    end
  end
end
Also aliased as: quote
new(*args, **opts) click to toggle source

@overload initialize(uri, **opts)

@param uri [String, URI] "mysql://username:password@host:port/database?param=value&..." / "mysql://username:password@%2Ftmp%2Fmysql.sock/database" / "mysql://username:password@/database?socket=/tmp/mysql.sock"
@param opts [Hash] options

@overload initialize(host, username, password, database, port, socket, flags, **opts)

@param host [String] hostname mysqld running
@param username [String] username to connect to mysqld
@param password [String] password to connect to mysqld
@param database [String] initial database name
@param port [String] port number (used if host is not 'localhost' or nil)
@param socket [String] socket filename (used if host is 'localhost' or nil)
@param flags [Integer] connection flag. Mysql::CLIENT_* ORed
@param opts [Hash] options

@overload initialize(host: nil, username: nil, password: nil, database: nil, port: nil, socket: nil, flags: nil, **opts)

@param host [String] hostname mysqld running
@param username [String] username to connect to mysqld
@param password [String] password to connect to mysqld
@param database [String] initial database name
@param port [String] port number (used if host is not 'localhost' or nil)
@param socket [String] socket filename (used if host is 'localhost' or nil)
@param flags [Integer] connection flag. Mysql::CLIENT_* ORed
@param opts [Hash] options
@option opts :host [String] hostname mysqld running
@option opts :username [String] username to connect to mysqld
@option opts :password [String] password to connect to mysqld
@option opts :database [String] initial database name
@option opts :port [String] port number (used if host is not 'localhost' or nil)
@option opts :socket [String] socket filename (used if host is 'localhost' or nil)
@option opts :flags [Integer] connection flag. Mysql::CLIENT_* ORed
@option opts :charset [Mysql::Charset, String] character set
@option opts :connect_timeout [Numeric, nil]
@option opts :read_timeout [Numeric, nil]
@option opts :write_timeout [Numeric, nil]
@option opts :local_infile [Boolean]
@option opts :load_data_local_dir [String]
@option opts :ssl_mode [Integer]
@option opts :get_server_public_key [Boolean]
# File lib/mysql.rb, line 139
def initialize(*args, **opts)
  @fields = nil
  @protocol = nil
  @sqlstate = "00000"
  @host_info = nil
  @last_error = nil
  @result_exist = false
  @opts = DEFAULT_OPTS.dup
  parse_args(args, opts)
end
quote(str)
Alias for: escape_string

Public Instance Methods

affected_rows() click to toggle source

@return [Integer] number of affected records by insert/update/delete.

# File lib/mysql.rb, line 306
def affected_rows
  @protocol ? @protocol.affected_rows : 0
end
autocommit(flag) click to toggle source

Set autocommit mode @param [Boolean] flag @return [Mysql] self

# File lib/mysql.rb, line 486
def autocommit(flag)
  query "set autocommit=#{flag ? 1 : 0}"
  self
end
character_set_name() click to toggle source

@return [String] charset name

# File lib/mysql.rb, line 259
def character_set_name
  @protocol.charset.name
end
charset() click to toggle source

@return [Mysql::Charset] character set of MySQL connection

# File lib/mysql.rb, line 242
def charset
  @opts[:charset]
end
charset=(cs) click to toggle source

Set charset of MySQL connection. @param [String, Mysql::Charset] cs

# File lib/mysql.rb, line 248
def charset=(cs)
  charset = cs.is_a?(Charset) ? cs : Charset.by_name(cs)
  if @protocol
    @protocol.charset = charset
    query "SET NAMES #{charset.name}"
  end
  @opts[:charset] = charset
  cs
end
close() click to toggle source

Disconnect from mysql. @return [Mysql] self

# File lib/mysql.rb, line 214
def close
  if @protocol
    @protocol.quit_command
    @protocol = nil
  end
  return self
end
close!() click to toggle source

Disconnect from mysql without QUIT packet. @return [Mysql] self

# File lib/mysql.rb, line 224
def close!
  if @protocol
    @protocol.close
    @protocol = nil
  end
  return self
end
commit() click to toggle source

Commit transaction @return [Mysql] self

# File lib/mysql.rb, line 471
def commit
  query 'commit'
  self
end
connect(*args, **opts) click to toggle source

Connect to mysqld. parameter is same as arguments for {#initialize}. @return [Mysql] self

# File lib/mysql.rb, line 153
def connect(*args, **opts)
  parse_args(args, opts)
  if @opts[:flags] & CLIENT_COMPRESS != 0
    warn 'unsupported flag: CLIENT_COMPRESS' if $VERBOSE
    @opts[:flags] &= ~CLIENT_COMPRESS
  end
  @protocol = Protocol.new(@opts)
  @protocol.authenticate
  @host_info = (@opts[:host].nil? || @opts[:host] == "localhost") ? 'Localhost via UNIX socket' : "#{@opts[:host]} via TCP/IP"
  query @opts[:init_command] if @opts[:init_command]
  return self
end
errno() click to toggle source

@return [Integer] last error number

# File lib/mysql.rb, line 264
def errno
  @last_error ? @last_error.errno : 0
end
error() click to toggle source

@return [String] last error message

# File lib/mysql.rb, line 269
def error
  @last_error && @last_error.error
end
escape_string(str) click to toggle source

Escape special character in MySQL.

@param [String] str return [String]

# File lib/mysql.rb, line 236
def escape_string(str)
  self.class.escape_string str
end
Also aliased as: quote
field_count() click to toggle source

@return [Integer] number of columns for last query

# File lib/mysql.rb, line 279
def field_count
  @fields.size
end
host_info() click to toggle source

@return [String] connection type

# File lib/mysql.rb, line 284
def host_info
  @host_info
end
info() click to toggle source

@return [String] information for last query

# File lib/mysql.rb, line 301
def info
  @protocol && @protocol.message
end
insert_id() click to toggle source

@return [Integer] latest auto_increment value

# File lib/mysql.rb, line 311
def insert_id
  @protocol ? @protocol.insert_id : 0
end
kill(pid) click to toggle source

Kill query. @param [Integer] pid thread id @return [Mysql] self

# File lib/mysql.rb, line 323
def kill(pid)
  check_connection
  @protocol.kill_command pid
  self
end
more_results() click to toggle source

@return [Boolean] true if multiple queries are specified and unexecuted queries exists.

# File lib/mysql.rb, line 391
def more_results
  @protocol.server_status & SERVER_MORE_RESULTS_EXISTS != 0
end
Also aliased as: more_results?
more_results?()
Alias for: more_results
next_result() click to toggle source

execute next query if multiple queries are specified. @return [Boolean] true if next query exists.

# File lib/mysql.rb, line 398
def next_result
  return false unless more_results
  check_connection
  @fields = nil
  nfields = @protocol.get_result
  if nfields
    @fields = @protocol.retr_fields nfields
    @result_exist = true
  end
  return true
end
parse_args(args, opts) click to toggle source
# File lib/mysql.rb, line 166
def parse_args(args, opts)
  case args[0]
  when URI
    uri = args[0]
  when /\Amysql:\/\//
    uri = URI.parse(args[0])
  when String
    @opts[:host], user, passwd, dbname, port, socket, flags = *args
    @opts[:username] = user if user
    @opts[:password] = passwd if passwd
    @opts[:database] = dbname if dbname
    @opts[:port] = port if port
    @opts[:socket] = socket if socket
    @opts[:flags] = flags if flags
  when Hash
    # skip
  when nil
    # skip
  end
  if uri
    host = uri.hostname.to_s
    host = URI.decode_www_form_component(host)
    if host.start_with?('/')
      @opts[:socket] = host
      host = ''
    end
    @opts[:host] = host
    @opts[:username] = URI.decode_www_form_component(uri.user.to_s)
    @opts[:password] = URI.decode_www_form_component(uri.password.to_s)
    @opts[:database] = uri.path.sub(/\A\/+/, '')
    @opts[:port] = uri.port
    opts = URI.decode_www_form(uri.query).to_h.transform_keys(&:intern).merge(opts) if uri.query
    opts[:flags] = opts[:flags].to_i if opts[:flags]
  end
  if args.last.kind_of? Hash
    opts = opts.merge(args.last)
  end
  @opts.update(opts)
end
ping() click to toggle source

Check whether the connection is available. @return [Mysql] self

# File lib/mysql.rb, line 428
def ping
  check_connection
  @protocol.ping_command
  self
end
prepare(str) click to toggle source

Parse prepared-statement. @param [String] str query string @return [Mysql::Stmt] Prepared-statement object

# File lib/mysql.rb, line 413
def prepare(str)
  st = Stmt.new @protocol
  st.prepare str
  st
end
query(str, &block) click to toggle source

Execute query string. @overload query(str)

@param [String] str Query.
@return [Mysql::Result]
@return [nil] if the query does not return result set.

@overload query(str, &block)

@param [String] str Query.
@yield [Mysql::Result] evaluated per query.
@return [self]

@example

my.query("select 1,NULL,'abc'").fetch  # => [1, nil, "abc"]
my.query("select 1,NULL,'abc'"){|res| res.fetch}
# File lib/mysql.rb, line 341
def query(str, &block)
  check_connection
  @fields = nil
  begin
    nfields = @protocol.query_command str
    if nfields
      @fields = @protocol.retr_fields nfields
      @result_exist = true
    end
    if block
      while true
        block.call store_result if @fields
        break unless next_result
      end
      return self
    end
    return @fields ? store_result : nil
  rescue ServerError => e
    @last_error = e
    @sqlstate = e.sqlstate
    raise
  end
end
quote(str)
Alias for: escape_string
refresh(op) click to toggle source

Flush tables or caches. @param [Integer] op operation. Use Mysql::REFRESH_* value. @return [Mysql] self

# File lib/mysql.rb, line 437
def refresh(op)
  check_connection
  @protocol.refresh_command op
  self
end
reload() click to toggle source

Reload grant tables. @return [Mysql] self

# File lib/mysql.rb, line 445
def reload
  refresh Mysql::REFRESH_GRANT
end
rollback() click to toggle source

Rollback transaction @return [Mysql] self

# File lib/mysql.rb, line 478
def rollback
  query 'rollback'
  self
end
select_db(db) click to toggle source

Select default database @return [Mysql] self

# File lib/mysql.rb, line 451
def select_db(db)
  query "use #{db}"
  self
end
server_info() click to toggle source

@return [String] server version

# File lib/mysql.rb, line 289
def server_info
  check_connection
  @protocol.server_info
end
server_version() click to toggle source

@return [Integer] server version

# File lib/mysql.rb, line 295
def server_version
  check_connection
  @protocol.server_version
end
set_server_option(opt) click to toggle source

Set server option. @param [Integer] opt {Mysql::OPTION_MULTI_STATEMENTS_ON} or {Mysql::OPTION_MULTI_STATEMENTS_OFF} @return [Mysql] self

# File lib/mysql.rb, line 384
def set_server_option(opt)
  check_connection
  @protocol.set_option_command opt
  self
end
shutdown(level=0) click to toggle source

shutdown server. @return [Mysql] self

# File lib/mysql.rb, line 458
def shutdown(level=0)
  check_connection
  @protocol.shutdown_command level
  self
end
sqlstate() click to toggle source

@return [String] sqlstate for last error

# File lib/mysql.rb, line 274
def sqlstate
  @last_error ? @last_error.sqlstate : "00000"
end
stat() click to toggle source

@return [String] statistics message

# File lib/mysql.rb, line 465
def stat
  @protocol ? @protocol.statistics_command : 'MySQL server has gone away'
end
stmt() click to toggle source

@private Make empty prepared-statement object. @return [Mysql::Stmt] If block is not specified.

# File lib/mysql.rb, line 422
def stmt
  Stmt.new @protocol
end
store_result() click to toggle source

Get all data for last query. @return [Mysql::Result]

# File lib/mysql.rb, line 367
def store_result
  check_connection
  raise ClientError, 'invalid usage' unless @result_exist
  res = Result.new @fields, @protocol
  @result_exist = false
  res
end
thread_id() click to toggle source

@return [Integer] Thread ID

# File lib/mysql.rb, line 376
def thread_id
  check_connection
  @protocol.thread_id
end
warning_count() click to toggle source

@return [Integer] number of warnings for previous query

# File lib/mysql.rb, line 316
def warning_count
  @protocol ? @protocol.warning_count : 0
end

Private Instance Methods

check_connection() click to toggle source
# File lib/mysql.rb, line 493
def check_connection
  raise ClientError::ServerGoneError, 'MySQL server has gone away' unless @protocol
end