mysql-net / mysqlconnector Goto Github PK
View Code? Open in Web Editor NEWMySQL Connector for .NET
Home Page: https://mysqlconnector.net
License: MIT License
MySQL Connector for .NET
Home Page: https://mysqlconnector.net
License: MIT License
When Old Guids=true
is set in the connection string, GUIDs will be stored as BINARY(16)
instead of CHAR(36)
. Consequently, GUID parameters will need to be encoded as a hex literals in queries sent to MySQL.
Handle DECIMAL
and NUMERIC
. This is required for #4.
http://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
First of all, I'd like to give a big THANKS for putting in the effort to write the community a MySQL driver. This is a life saver!
I'm running into some odd behavior with decimal types.
My table is this:
CREATE TABLE `t_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`currency` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
My C# is this:
var conn = new MySqlConnection("Server=127.0.0.1;Database=ccdev;Uid=root;Pwd=password;");
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select * from t_test;";
var reader = cmd.ExecuteReader();
var testItems = new List<Test>();
while(reader.Read())
{
var t = new Test{
id=reader.GetInt64(0),
name=reader.GetString(1),
currency=reader.GetDecimal(2)
};
testItems.Add(t);
}
When I insert a row like so:
insert into t_test(name, currency) values('Sarah', 85.3);
The currency value comes back from reader.GetDecimal(2) wrong like this: 44415315.52
It's not consistent but it's always wrong and always a really big number.
Am I doing something wrong?
The consumer should be able to cancel an active reader via MySqlCommand.Cancel()
or the CancellationToken
passed to ExecuteReaderAsync
etc.
This probably needs to be implemented via KILL QUERY
which may require a separate connection. It may be helpful to implement connection pooling #2 first and keep a spare connection around to issue this command.
Implement support for DATE
, DATETIME
, TIME
, TIMESTAMP
, YEAR
.
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
Required for #4.
I have tested two of my models with the same result. Here is the exception for one of the models:
Result Message:
System.NullReferenceException: Object reference not set to an instance of an object.
Result StackTrace:
at MySql.Data.MySqlClient.MySqlDataReader.get_FieldCount()
at Dapper.SqlMapper.GetColumnHash(IDataReader reader, Int32 startBound, Int32 length)
at Dapper.SqlMapper.<QueryImpl>d__125`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
at DbManager.Data.Repositories.StationRepository.Update(Station station) in E:\svn-edric\ProofOfConcept\DbManager\DbManager.Data\Repositories\StationRepository.cs:line 70
at DbManager.Tests.SqlConnectorTests.SqlConnector_UpdateItem() in E:\svn-edric\ProofOfConcept\DbManager\DbManager.Tests\SqlConnectorTests.cs:line 67
Sql query string:
"UPDATE stations SET name=@name,stationType_SID=@stationType_SID,geoPosition_SID=@geoPosition_SID,service_start=@service_start,service_end=@service_end,deleted=@deleted,created_on=@created_on,externalWebsite=@externalWebsite,externalTitle=@externalTitle WHERE SID=@SID"
Dapper execution:
_db.Query<Station>(queryString, station);
Model:
public class Station
{
public ulong? SID { get; set; }
public string name { get; set; }
public ulong stationType_SID { get; set; }
public ulong? geoPosition_SID { get; set; }
public DateTime? service_start { get; set; }
public DateTime? service_end { get; set; }
public bool? deleted { get; set; }
public DateTime created_on { get; set; }
public string externalWebsite { get; set; }
public string externalTitle { get; set; }
}
If the JSON tests (and any other tests that require MySQL Server 5.7) were optional, then it would be easier to run the tests against MySQL Server 5.6 and MariaDB (to verify compatibility with those servers).
Respect the CommandTimeout
property.
In some cases, the MySQL statement timeout feature could be a way to implement this?
As per dotnet/efcore#1584, detect if someone is trying to use an ambient transaction and explicitly throw to let them know it won't actually work.
ExecuteNonQuery
returns the number of affected rows; this is correct according to the documentation, but doesn't match the default behaviour of MySql.Data.
According to MySQL bug 44194, the MySQL connector will return the number of found rows unless you explicitly set Use Affected Rows=true
in the connection string.
Let's add support for this option to the connection string, default it to true
and fail if it's explicitly set to false
. (We should prefer to match the ADO.NET standard; if the "buggy" behaviour is truly needed, we can add support for Use Affected Rows=false
later.)
Add connection pooling support.
It seems like we should be able to reuse any connection (to the same server and port) as long as we send a COM_CHANGE_USER
packet.
Run the tests on as many .NET Core platforms as possible.
For compatibility with MySql.Data, MySqlDataReader.GetGuid
should succeed whenever the column value could be interpreted as a GUID, e.g., a byte[16]
or a varchar
containing a string that's parseable as a GUID, etc.
Reading a blob longer than 16M (i.e., split across multiple MySQL packets) throws the following exception:
System.ArgumentOutOfRangeException : value must be between 0 and 16777215
Parameter name: value
Stack Trace:
MySqlConnector\ByteArrayReader.cs(33,0): at MySql.Data.ByteArrayReader.set_Offset(Int32 value)
MySqlConnector\MySqlClient\MySqlDataReader.cs(85,0): at MySql.Data.MySqlClient.MySqlDataReader.ReadAsyncRemainder(PayloadData payload)
MySqlConnector\MySqlClient\MySqlDataReader.cs(67,0): at MySql.Data.MySqlClient.MySqlDataReader.<ReadAsyncAwaited>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
MySqlConnector\MySqlClient\MySqlDataReader.cs(41,0): at MySql.Data.MySqlClient.MySqlDataReader.Read()
at Dapper.SqlMapper.<QueryImpl>d__124`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
The data reading code needs to loop while receiving more packets.
According to MySQL Documentation:
ClearPool clears the connection pool that is associated with the connection. If additional connections associated with connection are in use at the time of the call, they are marked appropriately and are discarded (instead of being returned to the pool) when Close is called on them.
Connections that are active when ClearPool
is called should be flagged to be cleared as soon as they are returned.
Hi,
If the client has ipv6 but the mysql machine has ipv4, the socket connection fails with "Address System.Net.Sockets.SocketException: An address incompatible with the requested protocol was used error".
I've solved the issue, and it's a simple fix:
Replace line in MySqlSession.cs:
socket = new Socket(SocketType.Stream, ProtocolType.Tcp);
With:
socket = new Socket(ipAddress.AddressFamily, SocketType.Stream, ProtocolType.Tcp);
This is the minimum to reproduce the bug :
try
{
using (var connection = new MySqlConnection(<ConnectionString>))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT <ColumnName>FROM <Table> WHERE <ColumnName>LIKE ':id';";
var cmdParam = cmd.CreateParameter();
cmdParam.DbType = DbType.String;
cmdParam.ParameterName = ":id";
cmdParam.Value = $@"%{<StringToMatch>}%";
cmd.Parameters.Add(cmdParam);
using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
while (reader.Read())
{
Console.WriteLine(reader[<ColumnName>]);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
In the results view of the reader
Message: "Specified method is not supported." string
Source: "MySqlConnector" string
StackTrace: " at MySql.Data.MySqlClient.MySqlDataReader.GetEnumerator()\r\n at System.Linq.SystemCore_EnumerableDebugView.get_Items()" string
Switch to the new .NET Platform Standard TFM, specifically netstandard1.3
(which includes the System.Net.Sockets
types we need).
Get the project building with dotnet build
instead of dnu build
and dnx test
. This will require xunit support to be merged: xunit/coreclr.xunit#1.
The following code causes a memory leak on Linux:
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
using (var command = new MySqlCommand(connection, transaction))
{
command.CommandText = "SOMESQL";
command.ExecuteNonQuery();
command.CommandText = "SOME OTHER SQL";
command.ExecuteScalar();
transaction.Commit();
}
}
I couldn't investigate it further, but lldb with the SOS plugin shows System.String containing the queries accumulating in the heap.
Please let me know if you would like some more details.
Handle FLOAT
and DOUBLE
. This is required for #4.
http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html
Add MySqlConnectionStringBuilder.ConnectionTimeout
setting (aliases: Connect Timeout, Connection Timeout, ConnectionTimeout). According to https://dev.mysql.com/doc/connector-net/en/connector-net-connection-options.html it has a default value of 15.
A "Timeout expired" MySqlException
should be thrown when:
Right now, synchronous methods are implement as:
Task.GetAwaiter().GetResult()
This causes the running thread to schedule a background thread to do the work, and block until the result is available. This can lead to thread pool starvation, especially in the case of a web server.
For example, if the thread pool size is 25 and a web request comes in, there are 24 available threads. Then Connection.Open
gets synchronously called, and that calls Connection.OpenAsync().GetAwaiter().GetResult()
. The current thread blocks and starts a background thread to open the connection, so now there are 23 available threads.
If a flood of more requests come in, there can be a situation where there are no available threads to schedule the Connection.OpenAsync().GetAwaiter().GetResult()
calls on. The app completely locks up at this point.
I think we should look for a way to run these synchronous tasks on the currently executing thread if possible. Either that or schedule the Async work to run on a reserved background thread. @bgrainger , what's your opinion of the best approach?
You can see the thread pool starvation happen if you run the performance stress test with synchronous targets. I get lockups around 200+ Requests Per Second.
var conn = new MySqlConnection("Server=localhost;Database=mydb;Uid=user1;");
conn.Open();
gets:
An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in System.Private.CoreLib.ni.dll
Additional information: Access denied for user 'user1'@'localhost' (using password: YES)
If a packet that is larger than max_allowed_packet
(a server-defined variable) is sent, the server will reset the connection and send an error packet with the response Got a packet bigger than 'max_allowed_packet' bytes
. However, once this happens the connection is destroyed and the client can't recover.
Additionally, it's a waste of time to send the query to the server when we know it'll be rejected. By running SHOW VARIABLES
when the connection is established, we could get the value of max_allowed_packet
, and check packets to ensure they don't exceed that length before sending them.
Maybe i have found bug.
Reproduction:
using (MySqlConnection connection = await <OpenConnectionFunction()> )
using (MySqlCommand command = new MySqlCommand("select 1; select 1 from mysql.abc;select 1;", connection))
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
<Some code...>
await reader.NextResultAsync();
<Some code...>
await reader.NextResultAsync();
<Some code...>
}
The 2nd select in the CommandText is invalid, because the table "abc" does not exist, but I get this exception:
(InvalidOperationException) "Invalid state: None".
Reason:
while(NextResult()){}
Hi. Great work with the project! I have found that there is no DbProviderFactory implementation. I feel this would be useful integrating with ORM's and other existing libraries that require a System.Data.Common.DbProviderFactory.
To get the ball rolling I suggest we copy how Microsoft.Data.Sqlite have done theirs. SqliteFactory.cs
An example would be
public class MySqlClientFactory : DbProviderFactory
{
public static readonly MySqlClientFactory Instance = new MySqlClientFactory();
private MySqlClientFactory()
{
}
public override DbCommand CreateCommand()
=> new MySqlCommand();
public override DbConnection CreateConnection()
=> new MySqlConnection();
public override DbConnectionStringBuilder CreateConnectionStringBuilder()
=> new MySqlConnectionStringBuilder();
public override DbParameter CreateParameter()
=> new MySqlParameter();
}
The DbConnection
documentation says
Any instance members are not guaranteed to be thread safe.
This is particularly true of this implementation, because there is a one-to-one mapping between MySqlConnection
(and MySqlSession
) and a Socket
open to the MySQL server. It is not safe to perform concurrent operations on the MySqlConnection
(e.g., executing commands); however, it's actually more likely to happen with this connector because it is truly async and will yield back to the caller, allowing another DB operation to be initiated.
A simple fix may be to track whether there is an inflight operation and throw early as soon as another one is attempted. We don't want to break legal patterns, though, e.g., creating and caching multiple MySqlCommand
objects (but not executing them concurrently).
As per String Literals, _binary
can be prepended to a string literal to indicate that its charset is binary.
This would allow blobs to be send over the wire with half as many bytes as are currently used by the connector.
To implement this, the MySqlStatementPreparer
would need to build a byte[]
(rather than a string
) so that literal bytes could be appended to the output without having to go through a subsequent UTF-8 encoding.
See also the "TODO" here.
Current version of MySqlCommand only supports CommandType = Text. Supporting stored procedure commands is a must for any realistic migration from the official MySQL Connector.
ps: nice work so far!
Ensure that fatal exceptions (IOException
, SocketException
, etc.) are wrapped in MySqlException
.
There are some uses of FormatException
and InvalidOperationException
that should probably be wrapped in this way, too.
On desktop (full) .NET, implement support for auto-enlisting in ambient transactions. This will enable .NET 4.5 code that uses TransactionScope
to be ported to this connector without having to rewrite it. (This connector will throw when #12 is implemented, even if the underlying platform could support System.Transactions
.)
See related issues for EF: dotnet/efcore#1530, dotnet/efcore#5595.
Note that System.Transactions
isn't supported in .NET Core: dotnet/corefx#2949
Hi,
I tried your connector out. It worked in a small demo App ("select name from users").
When I launched a load test (I wanted to test .NET Core performance on Swisscom cloud), It ended up crashing quite often with "Not more connections available".
Maybe I am doing something wrong, here is my code:
public interface IDb
{
Task<IList<string>> GetUserNames();
}
public class Db : IDb
{
public string ConnectionString { get; private set; }
public Db(string connectionString)
{
this.ConnectionString = connectionString;
}
public async Task<IList<string>> GetUserNames()
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
var results = await connection.QueryAsync<string>("select Name from Users");
return results.AsList();
}
}
}
See PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#40 for background.
Modify Pomelo.EntityFrameworkCore.MySql.Tests
to use MySqlConnector
and see if all the tests pass; create issues for test failures.
Clone the MySqlBulkLoader
API (specified here: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html).
As an extension, support a Stream
property instead of a FileName
. Behind the scenes, create a GUID and pass that to MySQL as the LOAD DATA LOCAL INFILE
file name (https://dev.mysql.com/doc/refman/5.7/en/load-data.html).
When the LOCAL_INFILE_Request
is received with that GUID as the name, stream the in-memory data to the server.
1. Implement DefaultCommandTimeout
in the connection string
Connection String Documentation
2. Implement CommandTimeout
in MySqlCommand
MySqlCommand cmd = new MySqlCommand();
cmd.CommandTimeout = 60;
Implementation Notes:
MySQL Connector/Net 6.2 introduced timeouts that are aligned with how Microsoft handles SqlCommand.CommandTimeout. This property is the cumulative timeout for all network reads and writes during command execution or processing of the results. A timeout can still occur in the MySqlReader.Read method after the first row is returned, and does not include user processing time, only IO operations. The 6.2 implementation uses the underlying stream timeout facility, so is more efficient in that it does not require the additional timer thread as was the case with the previous implementation.
It sounds like Oracle's implementation of CommandTimeout
is setting Socket.SendTimeout and Socket.ReceiveTimeout. They start at the timeout value (e.g. 30 seconds) and subtract only time spent in I/O. This becomes the send or receive timeout for the next socket command.
From the discussion on #65:
It appears that resetting the connection also resets the code page used for the connection. The fix is probably to send a SET NAMES
command after pulling a connection off the pool and resetting it.
Need to test with alternative MySQL Server implementations (e.g., MariaDB and Percona) to verify this server-level behaviour.
MySql.Data provides the following APIs on MySqlConnection
:
public static void ClearPool(MySqlConnection connection)
public static void ClearAllPools()
public Task ClearPoolAsync(MySqlConnection connection)
public Task ClearPoolAsync(MySqlConnection connection, CancellationToken cancellationToken)
public Task ClearAllPoolsAsync()
public Task ClearAllPoolsAsync(CancellationToken cancellationToken)
It's not clear to me why the sync methods are static method, while the async methods are instance methods (that sometimes take a MySqlConnection
object as a parameter).
Nevertheless, for compatibility we should probably clone this API as-is (and deprecate MySqlHelper
in this library).
API documentation:
Calling reader.GetName(0) throws "InvalidOperationException". The source of the exception is in the function VerifyHasResult. The state of the reader is "State.AlreadyReadFirstRow".
private void VerifyHasResult()
{
VerifyNotDisposed();
if (m_state != State.ReadResultSetHeader && m_state != State.ReadingRows && m_state != )
throw new InvalidOperationException("There is no current result set.");
}
Work through the list of MySQL data types and implement the missing ones. These include DATE
, TIME
, DATETIME
, DECIMAL
, FLOAT
, DOUBLE
and more.
When a MySqlDataReader
is created, but not all rows are read (e.g., with while (reader.Read())
), closing the reader and continuing to use the connection will throw an InvalidOperationException: Packet received out-of-order.
.
This happens because MySQL will keep sending rows of data until the statement completes. It may be necessary to KILL QUERY
to stop this happening (while still reading the remaining data from the socket until the query is killed).
PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#52 suggests that other MySQL connectors allow MySqlDataReader.GetInt32
to be called on a decimal
column. Investigate this and add missing implicit numeric conversions.
Wrong description, but when committing a transaction, the SqlTransaction's Connection-property turns null. I depend on this behavior (perhaps I shouldn't), but for this library, the connection is not nulled on the transaction. Maybe this is something you would want to change to mimic the SqlTransactions' behaviour more.
Create new SideBySide test cases for common MySQL operations and verify that this connector performs the same as the official one. File bugs for discrepancies.
When sending more than 16MB (see https://dev.mysql.com/doc/internals/en/mysql-packet.html) the following exception is thrown:
System.InvalidOperationException: MySqlSession is not connected.
at MySql.Data.Serialization.MySqlSession.VerifyConnected() in C:\Code\Projects\MySqlConnector\src\MySqlConnector\Serialization\MySqlSession.cs:line 114
at MySql.Data.Serialization.MySqlSession.TryAsync(Func`2 func, CancellationToken cancellationToken) in C:\Code\Projects\MySqlConnector\src\MySqlConnector\Serialization\MySqlSession.cs:line 137
at MySql.Data.Serialization.MySqlSession.ReceiveReplyAsync(CancellationToken cancellationToken) in C:\Code\Projects\MySqlConnector\src\MySqlConnector\Serialization\MySqlSession.cs:line 101
at MySql.Data.MySqlClient.MySqlDataReader.<ReadResultSetHeaderAsync>d__56.MoveNext() in C:\Code\Projects\MySqlConnector\src\MySqlConnector\MySqlClient\MySqlDataReader.cs:line 631
Implement support for ENUM.
http://dev.mysql.com/doc/refman/5.7/en/enum.html
Required for #4 and PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#36
Docs: https://dev.mysql.com/doc/internals/en/compression.html
MySqlConnectionStringBuilder.UseCompression
already exists, but must be set to false
; relax this restriction.
System.InvalidCastException: System.InvalidCastException: Unable to cast object of type 'System.UInt64' to type 'System.Int32'..
Stack Trace:
at MySql.Data.MySqlClient.MySqlDataReader.GetInt32(Int32 ordinal)
at lambda_method(Closure , DbDataReader )
at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagation(Int32 commandIndex, DbDataReader reader)
at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Consume(DbDataReader reader)
at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Execute(IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.