NAME

Net::Wire10 - Pure Perl driver for MySQL, Sphinx and Drizzle.

DESCRIPTION

Net::Wire10 is a Pure Perl connector that talks to MySQL, Sphinx and Drizzle servers.

Net::Wire10 implements the low-level network protocol, alias the MySQL wire protocol version 10, necessary for talking to one of the aforementioned servers without using an external client library such as for example libmysqlclient or libdrizzle.

SYNOPSIS

use Net::Wire10;

my $wire = Net::Wire10->new(
    host     => 'localhost',
    user     => 'test',
    password => 'test',
    database => 'test'
);

$wire->connect;

# CREATE TABLE example
$wire->query(
    "CREATE TABLE foo (id INT, message TEXT)"
);

# INSERT example
$wire->query(
    "INSERT INTO foo (id, message) VALUES (1, 'Hello World')"
);
printf "Affected rows: %d\n", $wire->get_no_of_affected_rows;

# SELECT example
my $results = $wire->query("SELECT * FROM foo");
while (my $row = $results->next_hash) {
  printf
    "Id: %s, Message: %s\n",
    $row->{id},
    $row->{message};
}

$wire->disconnect;

INSTALLATION

Net::Wire10 is installed like any other CPAN perl module:

$ perl -MCPAN -e shell
cpan> install Net::Wire10

For Perl installations where the CPAN module (used above) is missing, you can also just download the .tar.gz from this site and drop the Net folder in the same folder as the Perl file you want to use the driver from.

Some (particularly commercial) Perl distributions may have their own package management systems. Refer to the documentation that comes with your particular Perl distribution for details.

USAGE

From Perl you can begin using the driver like this:

use Net::Wire10;

After that you can connect to a server and start interacting.

Take a look at SYNOPSIS for a complete code example, or individual examples below.

Example: connect

Connection parameters are specified in the constructor to Net::Wire10. After constructing an instance using new(), call connect() to begin talking to the server.

$wire = Net::Wire10->new(
  host     => $host,
  user     => $user,
  password => $password,
);

$wire->connect();

The most frequently used parameters are shown above. For additional parameters, refer to new.

Example: create table

$wire->query(
  "CREATE TABLE foo (id INT, message TEXT)"
);

Example: insert data

$wire->query(
  "INSERT INTO foo (id, message) VALUES (1, 'Hello World')"
);

Example: retrieve data

my $results = $wire->query(
  "SELECT id, message FROM foo"
);

Data is traversed in a row-by-row fashion. To retrieve each row of data, call next_hash() or next_array():

my $row = $results->next_hash;

Do so in a loop to process all rows:

while (my $row = $results->next_hash) {
  printf
    "Id: %d, Message: %s",
    $row->{id},
    $row->{message};
}

Example: disconnect

$wire->disconnect;

FEATURES

Features in the Net::Wire10 driver

new

Creates a new driver instance using the specified parameters.

use Net::Wire10;
use strict;
use warnings;

# Connect to Sphinx server on localhost
my $wire = Net::Wire10->new(
    host       => 'localhost',
    port       => 3307,
    user       => 'test',
    password   => 'test',
  );

The argument hash can contain the following parameters:

host

Host name or IP address of the server to which a connection is desired.

port

TCP port where the server daemon listens. The port differs depending on the server type, so that you can have more than one type of server installed on the same machine.

MySQL uses port 3306,
Sphinx uses port 3307,
Drizzle uses port 4427.

The default is 3306 (MySQL), which is the most commonly used at the moment.

Sphinx needs to be at least version 0.9.9-rc2 for SphinxQL to work. There's more information about SphinxQL here: http://sphinxsearch.com/docs/current.html#sphinxql.

database

Name of the initial default database, eg the database used for a query when that query does not specifically mention a database. The server may deny login if a database is given and the user does not have the necessary privileges to access that database.

user

Username for identifying the service or user to the database server. This will show up in the process list, which is useful if you need to see what or who is hogging resources on the server.

password

Password for authenticating the service or user to the database server.

timeout

How long to wait before a connection attempt fails, and also how long to wait before a query is aborted.

debug

Various informational messages will be printed to the console if a value of 1 is given. The exchanged network protocol messages ("mackets") will be printed to the console if a value of 2 is given. The exchanged TCP packets will be printed to the console if a value of 4 is given.

connect

Establishes or re-establishes a connection to the server.

ping

Pings the daemon on the connected server over the wire protocol.

After connecting, a ping() is useful to ensure that the connection is still alive. The current status of the connection can be looked up using is_connected().

query

The query() method transmits the specified SQL string to the server and obtains the response, including the full set of results.

A result set is returned. The obtained result set will be disconnected from the driver. Being disconnected means that after retrieving the result set, you can fire more queries or close the connection.

Use query() when a small amount of records is needed for use at an arbitrary later point in time. If you want to stream data to a live result set, including large amounts of data, see stream().

stream

The stream() method transmits the specified SQL string to the server, and obtains initial information about the response, but does not begin downloading data.

A result set is returned. The obtained result set will be live with the driver. After retrieving the result set, you must traverse all of its rows before you can fire another query.

Use stream() for large result sets, as it has a smaller memory footprint compared to query(). If you want to download data to a disconnected result set, use query().

Note that stream() will lock the driver until the whole result set has been retrieved. To fetch another set of results while streaming to a live result set, create another driver object.

Also note that if you are using MySQL with the default storage engine, MyISAM, the entire table on the server will be locked for the duration of the live result set, that is until all rows have been retrieved.

prepare

Given a SQL string, returns a prepared statement.

Prepared statements are useful for:

Inserting binary data in the database.: For each parameter, an indication of whether the parameter should be considered text or binary data can be given.
Avoiding bugs that could lead to SQL injections.: Separating query logic and parameters can be beneficial in securing against SQL injections. Because query parameters do not have to be manually quoted, there's less risk of forgetting to quote or using an insecure quoting mechanism.
Reusing query logic with multiple sets of parameters.: Prepared statements can be re-executed with a new set of parameters.

Database and database object names cannot be parameterized, because schema identifiers need to be escaped in a different way than literal strings, and there is currently no additional marker implemented for this purpose. Identifiers can be manually quoted using Net::Wire10::Util::quote_identifier().

cancel (thread safe)

Cancels the running query. Safe to call asynchronously (thread safe).

use threads;

# abort on some condition (sleep for demonstration purposes)
threads->create(sub {
  $wire->cancel if sleep 2;
})->detach;

# run query for 10 seconds (will be interrupted by above thread after 2 sec)
query("SELECT SLEEP(10)");

Run the example above to see how cancel() works to interrupt the SLEEP(10) statement before it finishes, after only 2 seconds instead of the full 10.

Another common way to kill a running query is to create another connection to the server, if possible, and use KILL QUERY (or KILL CONNECTION for older servers). Using the KILL commands require a number of things, for example that the server has available connection slots, server privileges, etc.

is_connected

Returns true after connect() has been called, for as long as no fatal errors has occurred.

After a fatal error, a successful call to connect() causes is_connected to return true once again.

is_error

Returns true if an error code and message is available from the server or the driver.

get_error_code

Returns the server-reported (1xxx) error code, or a client (2xxx) error code.

get_error_state

Returns the SQL state code sent by the server when an error occurs, or HY000 for errors that does not have a standardized designation.

get_error_message

Returns an error string sent by the server. Usually contains a mix of text corresponding to the error code (see above), in whatever language the server is started with, and some factual information from the query that caused the error or similar context.

get_server_version

After connecting, this returns the server version.

get_connection_id

After connecting, this returns a unique identifier for the thread on the server that handles the current connection.

The connection identifier is useful for logging and debugging, killing queries or connections, for naming pseudo-temporary tables or other objects, and more.

disconnect

Transmits a goodbye message to the server to indicate intentions, then closes the underlying socket.

Features in the Net::Wire10::Results iterator

A Net::Wire10::Results object is returned when calling query() or stream(). Depending on which was used to execute the SQL, either a disconnected result set or a live (streaming) result set is returned.

next_array

The next_array() method returns a whole row, with individual field values packed into an array. undef is returned once all rows has been extracted.

while (my $row = $results->next_array) {
  printf
    "Value 1: %s Value 2: %s Value 3: %s\n",
    $row->[0],
    $row->[1],
    $row->[2];
}

When the retrieved columns has been specifically named in the SELECT statement (rather than using the SELECT * wildcard), the position of each individual field in result set rows are known, and next_array() can be used to access field values based on their position.

Column name and order for a SELECT * query can be retrieved using get_column_names().

After calling next_array(), the row has been consumed. There is currently no way to rewind and re-read a row, even for a disconnected result set.

next_hash

The next_hash() method returns a whole row, with individual field values packed into a hash. The key of the hash is the name of the column that each field belongs to. undef is returned once all rows has been extracted.

while (my $row = $results->next_hash) {
  printf
    "Id: %s Name: %s Data: %s\n",
    $row->{id},
    $row->{name},
    $row->{data};
}

Using next_hash() instead of next_array() usually makes the code a bit more readable, especially in cases where a SELECT with column names is not nearby.

After calling next_hash(), the row has been consumed. There is currently no way to rewind and re-read a row, even for a disconnected result set.

spool

Reads the remaining rows of the result set, in effect turning a streaming result set into a disconnected result set.

flush

Reads the remaining rows of the result set and discards them. When done on a live result set, this frees the driver for use.

get_no_of_columns

Returns the number of columns in the set of results.

get_column_names

Return the names of the result set's columns as an array.

has_results

Returns true if the query returned a set of results.

get_no_of_affected_rows

Returns the number of rows influenced by the UPDATE, DELETE or similar query.

my $affected = $wire->get_no_of_affected_rows;

get_no_of_selected_rows

Returns the number of rows in the result set of a SELECT or similar query.

my $selected = $wire->get_no_of_selected_rows;

After consuming a row, the number of available rows will decrease, and get_no_of_selected_rows() will return one less.

Supported only for disconnected result sets, live/streaming result sets are unaware of the total number of records. A streaming result set can be turned into a disconnected result set using spool(), after which the number of available rows becomes known.

get_insert_id

MySQL and Drizzle has the ability to choose unique key values automatically, by enabling auto_increment for a column. When this happens, the newly assigned id value for the last inserted row is stored in this attribute.

get_warning_count

After a query, this returns the number of warnings generated on the server. If the query is streaming to a live result set, an additional warning count is available after the last row of data has been read.

Features in Net::Wire10::PreparedStatement

get_token_count

Returns the number of "?" tokens found in the SQL initially used to create the prepared statement.

set_parameter

Sets the parameter at a given index to a given value. The index corresponds to the relative position of a "?" marker in the prepared statement, with the first "?" marker being 1.

$ps->set_parameter(2, 'Hello World');

There is no need to quote the parameter value, this is done automatically.

Binary data such as for example JPEG image files can also be added:

$ps->set_parameter(3, $bindata, DATA_BINARY);

If the last parameter is specified as DATA_BINARY, the value given is taken as a binary string. Otherwise, the value is taken as either an iso8859-1 string or a Unicode string, depending on what Perl thinks about the string.

clear_parameter

Clears one or more parameters, or all parameters if nothing was specified.

$ps->clear_parameter(1, 3);

query

Execute the prepared statement using the parameters previously set with set_parameter(). All results are spooled before the call returns.

stream

Execute the prepared statement using the parameters previously set with set_parameter(). As soon as the initial metadata arrives from the database server, the call returns, and the results can be traversed in a streaming fashion.

Error handling features

There are two kind of errors in Net::Wire10, fatal and non-fatal errors. Fatal errors causes the connection to close, while non-fatal errors do not.

Non-fatal errors cause the active query to abort.

A query may also cause warnings and informational messages, these are per default only reported summarily via get_warning_count(). The actual messages can be retrieved out-of-band from the server with the SHOW WARNINGS command.

catching errors

All errors can be caught with an eval {} construct. To differentiate between a fatal and a non-fatal error, use is_connected().

# Create driver and connect
$wire = Net::Wire10->new(host=>'localhost', user=>'test', password=>'test');
$wire->connect;

# Execute nonsensical query
eval { $wire->query('Argle-bargle, glyp-glof?!'); };
warn $@ if $@;
print ($wire->is_connected ? "is" : "is not") . " connected.";

recognizing fatal errors

Here's a query that causes a fatal error:

# Execute query that kills the current connection
eval { $wire->query('KILL CONNECTION CONNECTION_ID()'); };
warn $@ if $@;
print ($wire->is_connected ? "is" : "is not") . " connected.";

After running the above code, it is necessary to reconnect the driver before doing additional work.

reconnecting

Once a fatal error has happened, it is trivial to reestablish a connection to the server.

# Reconnect if necessary
$wire->connect unless $wire->is_connected;

Notice that the connection id changes after this.

multiple jobs

If stability is sought, always wrap your code with guard blocks in reasonable spots. For example:

foreach $job (@processing_items) {
  # Process job
  eval {
    $wire->connect unless $wire->is_connected;
    $wire->query("SELECT ... interesting data ...");
    $wire->query("SELECT ... more interesting data ...");
    ... more processing ...
    $wire->query("UPDATE ... whatever ...");
  };
  # Handle errors
  warn "Failed to process item, continuing with next item: $@" if $@;
}

In the above, if any of the jobs in processing_items fail, a warning is printed, and the program reconnects (if necessary) and continues with the next job.

long term connections

If you have long-running jobs, always do a ping to check the connection after periods of inactivity. For example:

# Process some job, then sleep and repeat
while (1) {
  eval { $wire->ping if $wire->is_connected; };
  eval {
    # If the ping (above) failed, reconnect
    $wire->connect unless $wire->is_connected;
    # Actual process code
    $wire->query("SELECT ... interesting data ...");
    $wire->query("SELECT ... more interesting data ...");
    ... more processing ...
    $wire->query("UPDATE ... whatever ...");
  };
  # Handle errors
  warn "Something went wrong: $@" if $@;
  # Wait a while before going again
  sleep 300;
}

In the above, some job is run once every 5 minutes. Before the job is run, the connection is checked with ping(). Should the connection have failed silently, the ping will provoke a fatal error and soon thereafter, connect() will reestablish the connection before processing starts.

These are much superior approaches to automatically reconnecting before firing each query (which some other drivers do). Automatic reconnect schemes hides error messages, looses connection state, drops transactions, can cause half a transaction to complete, can cause table locks to be silently dropped, and similar malfunctions.

If you really want, you can amend the driver object with an extra version of query() (give it a different name) that automatically reconnects, or you can wrap the entire driver in your own object to the same effect, but I much recommend against it. Proper guard blocks, with a connection check at the beginning (as above), is a much healthier practice.

retrieving warnings

Here's an example of how to retrieve warnings generated by the server.

# Random query that emits a warning
my $sql = "SELECT CAST('abc' AS CHAR(1))";

# Run query on an existing connection
my $res = $wire->query($sql);

# Check for warnings
if ($res->get_warning_count > 0) {
  my $warnings = $wire->query("SHOW WARNINGS");
  while (my $msg = $warnings->next_hash) {
    printf
      "Code: %d, Message: %s",
      $msg->{Code},
      $msg->{Message};
  }
}

TROUBLESHOOTING

Supported operating system and Perl versions

This module has been tested on these platforms.

Windows Server 2008

with ActivePerl 5.10.0 build 1004, 32 and 64-bit

Feel free to send in reports of success or failure using different platforms!

Unsupported features

The following features are not supported.

Protocols other than version 10

Only protocol version 10 is supported.

There are several revisions of version 10. The only supported variant is the one described in the protocol documentation.

The documentation (at the time of this writing) covers MySQL Server version 4.1. It is recommended to use the newest GA server release however, because version 4.1 has a number of Unicode bugs, particularly when using built-in SQL functions for text handling.

Transports other than TCP/IP

Shared memory is not supported. Might be easy to add, my guess would be that it's just a ring buffer and one or two locks to synchronize readers and writers.

Named pipe is not supported.

Unix socket is not supported.

Character sets other than UTF-8

Result data is binary strings for binary data and UTF-8 strings for textual data. Testing for a text versus a binary result value is accomplished using utf8::is_utf8().

SQL queries are given as strings, interpreted as either UTF-8 or iso8859-1 depending on what Perl thinks about the string. When using prepared statements, binary data is also supported for parameters. See set_parameter() for details.

It is possible to convert binary data to ASCII hex notation for inclusion directly in the query text:

# Helper that converts binary to ASCII hex notation.
sub raw_to_hex { return unpack("H*", shift); }

# Convert to hex notation.
$hex = raw_to_hex($binary_data);

# Run a query containing hex data.
my $res = $wire->query("SELECT $hex");

A more economical encoding than hex notation is BASE64. If the server does not support BASE64 natively, a decoder can be added using for example this stored procedure: http://wi-fizzle.com/downloads/base64.sql.

# Contains helper that converts binary to BASE64 notation.
use MIME::Base64 qw(encode_base64);

# Convert to BASE64 notation.
my $base64 = encode_base64($binary_data);

# Run a query containing BASE64 data.
my $res = $wire->query("SELECT BASE64_DECODE('$base64')");

An even more economical method is to use a prepared statement to send the binary data in its raw form.

Protocol compression via zlib

There is not much documentation regarding protocol compression, therefore it has not been implemented.

It is possible to add compression at the network level instead using a tunnel rather than the protocol's own support, similarly to stunnel described below, if network bandwidth is at a premium.

Another option is to selectively compress data with zlib before handing it off to the driver, and using the function UNCOMPRESS() to expand the data again once it reaches the server:

# Contains helper that does zlib compression.
use Compress::Zlib;

# Compress some data.
my $compressed = compress('Hello World!');

# Create a prepared statement and bind the compressed data.
my $ps = $wire->prepare('SELECT UNCOMPRESS(?)');
$ps->set_parameter(1, $compressed, DATA_BINARY);

# Run the query to decompress data on the server side.
$res = $ps->query();

Verification of authenticity using SSL certificates

Verifying the server requires SSL support which is not currently implemented.

Verifying the client is not supported by the wire protocol.

SSL/TLS encrypted communication

There is not much documentation regarding protocol support for SSL/TLS, therefore it has not been implemented.

It is possible to add encryption at the network level by using a SSL/TLS wrapper such as "stunnel".

Stunnel provides a richer set of features than the current MySQL protocol supports, such as certificate-based authentication of the client in addition to the server.

Integrated support would be desirable because of simpler error handling and the possibility of performing key creation and certificate signing tasks via SQL.

Protocol-level prepared statements

The protocol design includes protocol-level support for server-side prepared statements. When implemented at a protocol level, these are faster than regular statements. Prepared statements execute faster because query parameters such as text does not need to be quoted before being sent to the server, and are not unquoted when arriving at the server.

Another performance benefit of prepared queries is that it is possible to perform multiple executions of the same query. The query logic is sent and parsed only once, saving a bit of network bandwidth and CPU cycles.

The current protocol design does not allow you to clear a single parameter at a time, but forces you to clear all parameters at once. When reusing a prepared statement for multiple executions, parameters that do not change from one execution to the next can therefore not be kept and has to be resent to the server for each execution of the query. This inefficiency in the design of the protocol can in some cases be alleviated somewhat with a workaround involving user variables.

Some drivers do not pipeline the prepare, parameter, execute and deallocate steps, instead waiting for a response from the server after each step. Such drivers may actually run some queries slower when using prepared statements.

A detail in the protocol specification is that results from non-prepared statements are pushed to the client, whereas results from prepared statements are pulled by the client. The pull mechanism makes it possible to stop in the middle of a result set and cancel receiving any more rows.

Another, related detail in the protocol specification is that prepared statements are assigned a number each, whereas non-prepared statements are not. Multiple prepared statements can therefore be active, running over the same connection at the same time, while only one non-prepared statement can be running at any one time.

In short, protocol-level prepared statements would be very nice to have, but are currently not implemented. Protocol details are described in the manual, but with gaps in the documentation.

There is also a purely SQL-driven interface to prepared statements, using the PREPARE, EXECUTE and DEALLOCATE PREPARE commands. The SQL-driven interface does not have the same performance benefits as the protocol-level one does. It can however be useful for assembling SQL statements on the fly inside stored procedures and for checking SQL queries for correct syntax and identifiers without actually running the query.

There is also a client-side prepared statement interface in the driver, which it is highly recommended to use.

High-granularity streaming

Streaming data along the way as it is consumed or delivered by the client application can lead to dramatical decreases in memory usage.

Streaming outgoing data can be accomplished with server-side prepared statements, because the wire protocol allows prepared statement parameters to be sent one at a time, and even in chunks. Chunking data presumably allows you to interleave data to different fields when sending a row to the server. (See also "Protocol-level prepared statements".)

The driver API currently allows client applications to stream incoming data one row at a time. The highest supported granularity for streaming is one whole row at a time. Streaming at a higher granularity and interleaving chunks of incoming data is not part of the current protocol design.

Streaming incoming data in a row-by-row fashion is also known in some drivers as "use_result mode".

Server-side cursors

Cursors provide a way of keeping open, navigating and optionally modifying a result set on the server, rather than having to transfer the entire result set to the client.

MySQL Server supports only a particular class of cursors known as read-only, forward-only cursors. As the name implies, this kind of cursor cannot be navigated (also called "scrolled") or modified.

The semantics are exactly the same as when streaming incoming data with the stream() call. With cursors however, the server automatically creates a temporary table behind the scenes from which the data is read, thereby always using extra resources for a temporary table, but consequently also releasing locks on original tables as soon as possible.

There is also a purely SQL-driven interface to cursors, which can be useful inside stored procedures.

Server-side cursors are part of the prepared statement protocol features (see above), and are therefore currently not supported.

Multiple statements per query

Currently unsupported. Multiple statements can often cause more trouble than gain by making SQL injection (a security risk) much easier and in return providing diminutive performance gains when compared to other approaches.

If you want to run multiple queries, one method is to create two separate connections. This can also give a performance boost, because one query does not wait for the other to finish. Another related advantage is that multi-core servers can actually perform both queries simultaneously.

Two or more queries can be started simultaneously without resorting to multiple threads on the client. This is done by starting the queries using the stream() call, which does not wait for the server to return data.

See also notes on connection pooling, which can be a useful technique to avoid initialization overhead when creating multiple connections.

Multiple result sets per query

Currently unsupported. Support would be nice to have because MySQL Server requires this for executing stored procedures (but not stored functions) that digs out result set data: before the actual result set, MySQL Server will for any CALL statement generate an extra result set which contains some sort of status data about how the execution went.

Normally this kind of information is sent back in protocol status fields, but that differs when using CALL. The implicit extra result set is very poorly documented, so it is hard to say if it is useful or not. Many utilities seems to completely ignore it.

Multiple result sets per query is documented to be incompatible with prepared statements, but the reason why is not.

If you get the error message "can't return a result set in the given context", this is the server telling you that it would like to generate an extra result set, but can't because the connector does not support it.

Various non-query-related protocol commands that may be useful are not currently supported, for example COM_SHUTDOWN to initiate a server shutdown and COM_DEBUG to trigger the server into dumping a bunch of debug information.

Out of scope features

Connection pooling

Connection pooling can improve performance by removing the need for a TCP handshake and SSL/TLS handshake (for encrypted sessions) when opening a connection, and can also alleviate TIME_WAIT problems in environments with many short-lived connections.

There is no particular advantage to putting pooling code in the driver core itself, but a standard implementation that comes along with the driver would be nice. Currently no such thing exists for this driver.

The LOAD DATA LOCAL INFILE client statement

Poorly documented and therefore unsupported. If necessary, you can emulate the LOAD DATA LOCAL INFILE client statement using two other queries in unison, namely SELECT INTO DUMPFILE and LOAD DATA INFILE.

For optimal network performance, compress the data using zlib before sending it to the driver. See also notes on compression further above.

use Net::Wire10;

# Connect to database server
$wire = Net::Wire10->new(host=>'localhost', user=>'test', password=>'test');
$wire->connect;

# Go!
upload_file('mydata.csv', '`test`.`sometable`');

sub upload_file {
  my $filename = shift;
  my $table = shift;

  # Load file
  open(FILE, $filename) or die "$!";
  binmode FILE;
  $rawdata = join('', <FILE>);

  # Upload file to server
  my $ps = $wire->prepare("SELECT ? INTO DUMPFILE '/tmp/upload.csv'");
  $ps->set_parameter(1, $rawdata, DATA_BINARY);
  $ps->query;

  # Load data - notice that this is server-side, there is no LOCAL keyword.
  $wire->query("LOAD DATA INFILE '/tmp/upload.csv' INTO TABLE $table");

  # Reclaim disk space used by temporary file.
  # (Prohibited by some servers for security reasons; use an alternative method.)
  $wire->query("SELECT '' INTO DUMPFILE '/tmp/upload.csv'");
}

When a similar solution was benchmarked, the performance was identical to a client-side version using the LOCAL feature (tested using the C API).

Be careful to escape data in the CSV file. MySQL Server does not use the same CSV format as the de facto standard format use by Office applications. One difference is that backslashes in CSV data are considered escape characters by MySQL Server.

When bulk loading data, it can be useful to temporarily disable keys with ALTER TABLE ... DISABLE KEYS, and/or to wrap the entire operation inside a transaction.

The DELIMITER client statement

Mostly useful for CLIs, and requires a lot of client-side SQL parsing. Can be implemented in a CLI if necessary, there is no advantage to putting this in the driver.

Miscellaneous other limitations

Due to the way the Perl interpreter works, the following limitations may also apply.

Result sets limited to ~2 billion results on 32-bit Perl or ~9 quintillion results on 64-bit Perl.
Field values limited to under 2 GiB on 32-bit Perl or under 8 EiB on 64-bit Perl.
get_insert_id() values limited to ~2 billion on 32-bit Perl or ~9 quintillion on 64-bit Perl.

If you need BIGINT UNSIGNED for AUTO_INCREMENT columns on 64-bit Perl (or INT UNSIGNED on 32-bit Perl), use "SELECT LAST_INSERT_ID()" rather than get_insert_id().

LAST_INSERT_ID() returns the sequence number as a string instead of as an integer, avoiding any overflow issues with Perl's built-in types. You may also need to disable parsing of the insert_id protocol field in the driver to avoid an overflow error being thrown.

Known bugs

There should be a bug tracker where you can find known bugs.

Bugs in the design of the over-the-wire protocol may affect how the driver works. For example:

No character set info is sent with metadata such as column names, forcing the driver to assume that the client and result character sets are constant, when in fact they are not.
Selecting data larger than max_packet_size server variable causes silent truncation (inserting does not).

Refer to the MySQL issue tracker for more server and protocol issues.

Bugs and design issues in Perl itself may also affect the driver. For example:

Using an alarm to invoke cancel() often does not work, especially on Windows (use a thread instead).
Integer scalars are always signed.

Bugs in IO::Socket or IO::Select may also affect the driver.

AUTHOR

Driver core hacked together by Kim Christensen and David Dindorp at Dubex A/S. Password hashing routines by Hiroyuki OYAMA E, Japan.

COPYRIGHT AND LICENCE

This is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

WARRANTY

Because this software is licensed free of charge, there is absolutely no warranty of any kind, expressed or implied.

To install Net::Wire10, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Net::Wire10

CPAN shell

perl -MCPAN -e shell
install Net::Wire10

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

DESCRIPTION

SYNOPSIS

INSTALLATION

USAGE

Example: connect

Example: create table

Example: insert data

Example: retrieve data

Example: disconnect

FEATURES

Features in the Net::Wire10 driver

new

connect

ping

query

stream

prepare

cancel (thread safe)

is_connected

is_error

get_error_code

get_error_state

get_error_message

get_server_version

get_connection_id

disconnect

Features in the Net::Wire10::Results iterator

next_array

next_hash

spool

flush

get_no_of_columns

get_column_names

has_results

get_no_of_affected_rows

get_no_of_selected_rows

get_insert_id

get_warning_count

Features in Net::Wire10::PreparedStatement

get_token_count

set_parameter

clear_parameter

query

stream

Error handling features

catching errors

recognizing fatal errors

reconnecting

multiple jobs

long term connections

retrieving warnings

TROUBLESHOOTING

Supported operating system and Perl versions

Unsupported features

Protocols other than version 10

Transports other than TCP/IP

Character sets other than UTF-8

Protocol compression via zlib

Verification of authenticity using SSL certificates

SSL/TLS encrypted communication

Protocol-level prepared statements

High-granularity streaming

Server-side cursors

Multiple statements per query

Multiple result sets per query

Non-query related commands

Out of scope features

Connection pooling

The LOAD DATA LOCAL INFILE client statement

The DELIMITER client statement

Miscellaneous other limitations

Known bugs

SEE ALSO

AUTHOR

COPYRIGHT AND LICENCE

WARRANTY

Module Install Instructions