Libmysqlclient Is Missing Trying Again With Extra Runtime Libraries
Mysql2 - A mod, simple and very fast MySQL library for Blood-red - bounden to libmysql
Travis CI 
Appveyor CI
The Mysql2 gem is meant to serve the extremely common use-instance of connecting, querying and iterating on results. Some database libraries out there serve as direct 1:1 mappings of the already circuitous C APIs bachelor. This ane is not.
It also forces the use of UTF-eight [or binary] for the connection [and all strings in 1.nine, unless Encoding.default_internal is set then information technology'll convert from UTF-eight to that encoding] and uses encoding-aware MySQL API calls where it can.
The API consists of two classes:
Mysql2::Client - your connexion to the database.
Mysql2::Result - returned from issuing a #query on the connection. It includes Enumerable.
Installing
General Instructions
jewel install mysql2 This gem links confronting MySQL's libmysqlclient library or Connector/C library, and compatible alternatives such as MariaDB. You may need to install a parcel such as libmysqlclient-dev, mysql-devel, or other appropriate package for your system. Run into below for arrangement-specific instructions.
Past default, the mysql2 jewel will try to find a re-create of MySQL in this order:
- Option
--with-mysql-dir, if provided (see below). - Option
--with-mysql-config, if provided (see below). - Several typical paths for
mysql_config(default for the majority of users). - The directory
/usr/local.
Configuration options
Employ these options past gem install mysql2 -- [--optionA] [--optionB=argument].
-
--with-mysql-dir[=/path/to/mysqldir]- Specify the directory where MySQL is installed. The mysql2 gem volition not utilizemysql_config, just volition instead look atmysqldir/libandmysqldir/includefor the library and header files. This option is mutually exclusive with--with-mysql-config. -
--with-mysql-config[=/path/to/mysql_config]- Specify a path to themysql_configbinary provided by your copy of MySQL. The mysql2 gem will ask thismysql_configbinary about the compiler and linker arguments needed. This pick is mutually exclusive with--with-mysql-dir. -
--with-mysql-rpath=/path/to/mysql/lib/--without-mysql-rpath- Override the runtime path used to find the MySQL libraries. This may exist needed if you deploy to a system where these libraries are located somewhere different than on your build system. This overrides any rpath calculated by default or by the options above.
Linux and other Unixes
You may demand to install a package such equally libmysqlclient-dev or mysql-devel; refer to your distribution'south bundle guide to find the item package. The most common consequence we see is a user who has the library file libmysqlclient.so but is missing the header file mysql.h -- double check that you have the -dev packages installed.
Mac OS X
You may use MacPorts, Homebrew, or a native MySQL installer package. The most common paths will be automatically searched. If you desire to select a specific MySQL directory, use the --with-mysql-dir or --with-mysql-config options higher up.
Windows
Make sure that yous have Reddish and the DevKit compilers installed. We recommend the Blood-red Installer distribution.
By default, the mysql2 gem will download and use MySQL Connector/C from mysql.com. If you lot prefer to utilize a local installation of Connector/C, add the flag --with-mysql-dir=c:/mysql-connector-c-x-y-z (this path may use forwards slashes).
By default, the libmysql.dll library will be copied into the mysql2 gem directory. To prevent this, add the flag --no-vendor-libmysql. The mysql2 precious stone will search for libmysql.dll in the following paths, in club:
- Environment variable
RUBY_MYSQL2_LIBMYSQL_DLL=C:\path\to\libmysql.dll(note the Windows-fashion backslashes). - In the mysql2 gem'south own directory
vendor/libmysql.dll - In the system'south default library search paths.
Usage
Connect to a database:
# this takes a hash of options, nigh all of which map directly # to the familiar database.yml in rails # See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html client = Mysql2 :: Customer . new ( :host => " localhost " , :username => " root " ) Then query it:
results = client . query ( " SELECT * FROM users WHERE group='githubbers' " ) Need to escape something first?
escaped = client . escape ( " gi'thu\"bbe\0r's " ) results = client . query ( " SELECT * FROM users WHERE group=' #{ escaped } ' " ) You tin can get a count of your results with results.count.
Finally, iterate over the results:
results . each practise | row | # conveniently, row is a hash # the keys are the fields, as y'all'd await # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL puts row [ " id " ] # row["id"].class == Fixnum if row [ " dne " ] # not-existant hash entry is nil puts row [ " dne " ] finish stop Or, you might merely keep it simple:
client . query ( " SELECT * FROM users WHERE group='githubbers' " ) . each practice | row | # do something with row, information technology's gear up to rock end How nigh with symbolized keys?
client . query ( " SELECT * FROM users WHERE group='githubbers' " , :symbolize_keys => true ) do | row | # do something with row, it'south gear up to rock end You can get the headers and the columns in the order that they were returned by the query similar this:
headers = results . fields # <= that'southward an array of field names, in order results . each ( :every bit => :array ) exercise | row | # Each row is an array, ordered the aforementioned as the query results # An otter's den is called a "holt" or "couch" finish Connectedness options
Y'all may set the following connection options in Mysql2::Client.new(...):
Mysql2::Client.new( :host, :username, :password, :port, :database, :socket = '/path/to/mysql.sock', :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS, :encoding = 'utf8', :read_timeout = seconds, :write_timeout = seconds, :connect_timeout = seconds, :reconnect = true/false, :local_infile = true/simulated, :secure_auth = true/false, :default_file = '/path/to/my.cfg', :default_group = 'my.cfg section', :init_command => sql ) SSL options
Setting any of the post-obit options volition enable an SSL connection, but only if your MySQL customer library and server have been compiled with SSL support. MySQL customer library defaults will be used for whatsoever parameters that are left out or set to zip. Relative paths are immune, and may be required by managed hosting providers such as Heroku.
Mysql2 :: Client . new ( # ...options every bit above..., :sslkey => ' /path/to/client-fundamental.pem ' , :sslcert => ' /path/to/client-cert.pem ' , :sslca => ' /path/to/ca-cert.pem ' , :sslcapath => ' /path/to/cacerts ' , :sslcipher => ' DHE-RSA-AES256-SHA ' ) Multiple result sets
You can also recollect multiple result sets. For this to work you need to connect with flags Mysql2::Client::MULTI_STATEMENTS. Multiple result sets can be used with stored procedures that return more than than i consequence set, and for bundling several SQL statements into a unmarried telephone call to customer.query.
customer = Mysql2 :: Client . new ( :host => " localhost " , :username => " root " , :flags => Mysql2 :: Client :: MULTI_STATEMENTS ) result = client . query ( ' CALL sp_customer_list( 25, ten ) ' ) # result now contains the first consequence set while customer . next_result result = client . store_result # upshot now contains the adjacent upshot set finish Repeated calls to client.next_result volition return true, false, or enhance an exception if the respective query erred. When client.next_result returns true, phone call client.store_result to retrieve a result object. Exceptions are not raised until client.next_result is called to find the condition of the respective query. Subsequent queries are not executed if an before query raised an exception. Subsequent calls to client.next_result will return simulated.
upshot = client . query ( ' SELECT 1; SELECT two; SELECT A; SELECT 3 ' ) p result . commencement while client . next_result result = client . store_result p consequence . get-go terminate Yields:
{"1"=>ane} {"2"=>ii} next_result: Unknown column 'A' in 'field list' (Mysql2::Fault) See https://gist.github.com/1367987 for using MULTI_STATEMENTS with Active Record.
Secure auth
Starting wih MySQL 5.6.5, secure_auth is enabled by default on servers (it was disabled past default prior to this). When secure_auth is enabled, the server volition turn down a connection if the account password is stored in erstwhile pre-MySQL 4.1 format. The MySQL 5.6.5 client library may likewise refuse to attempt a connection if provided an older format password. To bypass this restriction in the client, pass the option :secure_auth => false to Mysql2::Client.new(). If using ActiveRecord, your database.yml might expect something like this:
development: adapter: mysql2 encoding: utf8 database: my_db_name username: root password: my_password host: 127.0.0.i port: 3306 secure_auth: false Reading a MySQL config file
You may read configuration options from a MySQL configuration file past passing the :default_file and :default_group paramters. For example:
Mysql2 :: Client . new ( :default_file => ' /user/.my.cnf ' , :default_group => ' customer ' ) Initial command on connect and reconnect
If yous specify the init_command option, the SQL cord you lot provide volition be executed after the connexion is established. If :reconnect is set to true, init_command volition too be executed after a successful reconnect. It is useful if y'all desire to provide session options which survive reconnection.
Mysql2 :: Customer . new ( :init_command => " Ready @@SESSION.sql_mode = 'STRICT_ALL_TABLES' " ) Cascading config
The default config hash is at:
Mysql2 :: Client . which defaults to:
{ :async => imitation , :as => :hash , :symbolize_keys => false } that can be used as and so:
# these are the defaults all Mysql2::Client instances inherit Mysql2 :: Customer . . merge! ( :every bit => :array ) or
# this will change the defaults for all future results returned past the #query method _for this connectedness only_ c = Mysql2 :: Customer . new c . . merge! ( :symbolize_keys => truthful ) or
# this will set the options for the Mysql2::Result case returned from the #query method c = Mysql2 :: Customer . new c . query ( sql , :symbolize_keys => truthful ) Event types
Array of Arrays
Laissez passer the :as => :assortment selection to any of the higher up methods of configuration
Array of Hashes
The default issue type is fix to :hash, but you tin can override a previous setting to something else with :as => :hash
Timezones
Mysql2 now supports ii timezone options:
:database_timezone # this is the timezone Mysql2 will assume fields are already stored as, and will use this when creating the initial Time objects in blood-red :application_timezone # this is the timezone Mysql2 will convert to earlier finally handing back to the caller In other words, if :database_timezone is prepare to :utc - Mysql2 will create the Time objects using Time.utc(...) from the raw value libmysql hands over initially. Then, if :application_timezone is gear up to say - :local - Mysql2 will then convert the just-created UTC Time object to local time.
Both options simply allow 2 values - :local or :utc - with the exception that :application_timezone tin can be [and defaults to] zippo
Casting "boolean" columns
You can now tell Mysql2 to bandage tinyint(1) fields to boolean values in Ruby with the :cast_booleans option.
client = Mysql2 :: Customer . new consequence = client . query ( " SELECT * FROM table_with_boolean_field " , :cast_booleans => true ) Skipping casting
Mysql2 casting is fast, but not as fast every bit not casting data. In rare cases where typecasting is not needed, it volition exist faster to disable information technology by providing :bandage => false. (Note that :bandage => simulated overrides :cast_booleans => true.)
customer = Mysql2 :: Client . new result = client . query ( " SELECT * FROM table " , :bandage => false ) Hither are the results from the query_without_mysql_casting.rb script in the benchmarks folder:
user system total existent Mysql2 (cast: true) 0.340000 0.000000 0.340000 ( 0.405018) Mysql2 (cast: false) 0.160000 0.010000 0.170000 ( 0.209937) Mysql 0.080000 0.000000 0.080000 ( 0.129355) do_mysql 0.520000 0.010000 0.530000 ( 0.574619) Although Mysql2 performs reasonably well at retrieving uncasted information, it (currently) is non as fast as the Mysql gem. In spite of this small disadvantage, Mysql2 even so sports a friendlier interface and doesn't block the entire reddish procedure when querying.
Async
NOTE: Not supported on Windows.
Mysql2::Client takes advantage of the MySQL C API's (undocumented) non-blocking function mysql_send_query for all queries. But, in order to take total advantage of it in your Cherry code, y'all can do:
client . query ( " SELECT sleep(5) " , :async => true ) Which will render zilch immediately. At this bespeak y'all'll probably desire to use some socket monitoring mechanism like EventMachine or even IO.select. Once the socket becomes readable, you can practise:
# result will exist a Mysql2::Outcome example issue = client . async_result Annotation: Because of the mode MySQL's query API works, this method volition block until the issue is ready. So if you actually need things to stay async, it'southward best to simply monitor the socket with something like EventMachine. If you demand multiple query concurrency take a look at using a connection puddle.
Row Caching
By default, Mysql2 will cache rows that have been created in Red (since this happens lazily). This is particularly helpful since it saves the cost of creating the row in Red if you lot were to iterate over the collection again.
If you only program on using each row once, and then it'due south much more efficient to disable this behavior past setting the :cache_rows option to false. This would be helpful if you wanted to iterate over the results in a streaming manner. Significant the GC would cleanup rows you don't need anymore as you're iterating over the effect ready.
Streaming
Mysql2::Customer can optionally only fetch rows from the server on demand by setting :stream => true. This is handy when handling very large issue sets which might non fit in retentivity on the client.
event = customer . query ( " SELECT * FROM really_big_Table " , :stream => truthful ) In that location are a few things that demand to be kept in heed while using streaming:
-
:cache_rowsis ignored currently. (if you want to use:cache_rowsyou probably don't want to exist using:stream) - You must fetch all rows in the outcome set of your query before you can make new queries. (i.e. with
Mysql2::Result#each)
Read more most the consequences of using mysql_use_result (what streaming is implemented with) hither: http://dev.mysql.com/medico/refman/five.0/en/mysql-use-result.html.
Lazy Everything
Well... almost ;)
Field name strings/symbols are shared across all the rows and so only one object is ever created to correspond the field proper name for an unabridged dataset.
Rows themselves are lazily created in ruby-land when an endeavor to yield it is made via #each. For case, if you were to yield 4 rows from a 100 row dataset, simply 4 hashes will exist created. The rest will sit down and await in C-land until you desire them (or when the GC goes to cleanup your Mysql2::Consequence example). Now say y'all were to iterate over that aforementioned collection once more, this time yielding 15 rows - the 4 previous rows that had already been turned into cherry-red hashes would exist pulled from an internal enshroud, then eleven more would be created and stored in that enshroud. Once the entire dataset has been converted into carmine objects, Mysql2::Result will gratuitous the Mysql C upshot object as information technology'south no longer needed.
This caching behavior can exist disabled by setting the :cache_rows option to false.
As for field values themselves, I'k workin on information technology - but await that presently.
Compatibility
This gem is tested with the following Ruby versions on Linux and Mac OS X:
- Ruby MRI 1.8.7, ane.ix.2, 1.ix.3, 2.0.0, 2.1.ten, 2.2.x (ongoing patch releases)
- Ruby Enterprise Edition (based on MRI 1.8.7)
- Rubinius 2.ten
This gem is tested with the following MySQL and MariaDB versions:
- MySQL v.0, 5.1, five.v, 5.half dozen, 5.7
- MySQL Connector/C vi.0 and 6.ane (primarily on Windows)
- MariaDB 5.5, 10.0
Active Tape
- mysql2 0.two.ten includes an Active Record driver uniform with AR ii.3 and 3.0
- mysql2 0.3.x does not include an AR driver because it is included in AR 3.1 and above
Asynchronous Active Record
Delight come across the em-synchrony project for details about using EventMachine with mysql2 and Rails.
Sequel
Sequel includes a mysql2 adapter in all releases since iii.fifteen (2010-09-01). Use the prefix "mysql2://" in your connectedness specification.
EventMachine
The mysql2 EventMachine deferrable api allows you to make async queries using EventMachine, while specifying callbacks for success for failure. Here's a simple example:
require ' mysql2/em ' EM . run do client1 = Mysql2 :: EM :: Client . new defer1 = client1 . query " SELECT sleep(three) as first_query " defer1 . callback do | effect | puts " Effect: #{ result . to_a . inspect } " finish client2 = Mysql2 :: EM :: Client . new defer2 = client2 . query " SELECT sleep(1) second_query " defer2 . callback practise | result | puts " Result: #{ effect . to_a . inspect } " terminate end Benchmarks and Comparing
The mysql2 gem converts MySQL field types to Ruby data types in C code, providing a serious speed benefit.
The do_mysql jewel besides converts MySQL fields types, just has a considerably more than circuitous API and is still ~2x slower than mysql2.
The mysql jewel returns just nil or string data types, leaving you to convert field values to Ruby types in Ruby-red-land, which is much slower than mysql2'due south C code.
For a comparative benchmark, the script below performs a basic "SELECT * FROM" query on a table with 30k rows and fields of well-nigh every Ruby-representable data type, then iterating over every row using an #each similar method yielding a block:
user organization full real Mysql2 0.750000 0.180000 0.930000 (ane.821655) do_mysql one.650000 0.200000 1.850000 (2.811357) Mysql seven.500000 0.210000 7.710000 (8.065871) These results are from the query_with_mysql_casting.rb script in the benchmarks folder.
Evolution
Use 'bundle install' to install the necessary development and testing gems:
parcel install rake The tests crave the "test" database to exist, and await to connect both every bit root and the running user, both with a blank password:
CREATE DATABASE test; CREATE USER '<user>'@'localhost' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON exam.* TO '<user>'@'localhost'; You tin can change these defaults in the spec/configuration.yml which is generated automatically when you run rake (or explicitly rake spec/configuration.yml).
For a normal installation on a Mac, y'all most likely practise not demand to exercise anything, though.
Special Thanks
- Eric Wong - for the contribution (and the informative explanations) of some thread-safety, non-blocking I/O and cleanup patches. You stone dude
- Yury Korolev (http://github.com/yury) - for TONS of help testing the Active Record adapter
- Aaron Patterson (http://github.com/tenderlove) - tons of contributions, suggestions and general badassness
- Mike Perham (http://github.com/mperham) - Async Active Record adapter (uses Fibers and EventMachine)
- Aaron Stone (http://github.com/sodabrew) - additional client settings, local files, microsecond time, maintenance back up.
Source: https://www.rubydoc.info/gems/mysql2/0.3.18