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

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