Bug in MySql .NET Connector 6.9.8: Nested transactions are not supported

There is bug in MySQL connector for .NET version 6.9.8, that it will throw an error: “Nested transactions are not supported”

You have likely run into this bug in MySQL Connector/NET.

What triggers this bug:

1. Code calls for execution of query A
2. Transaction 1 for query A is started
3. Query A is executed and causes an error in MySQL
4. Transaction 1 is NOT rolled back
5. Code calls for execution of query B
6. Transaction 2 for query B is started
7. MySQL Connector/NET throws the exception

The bug is point 4: transaction 1 is left open after an error (or at least the connector is still convinced it's left open). Because of connection pooling, the code calling for query A and query B can be completely unrelated. Also, if the time between point 4 and 5 is long enough, the transaction is rolled back, hence the rareness and randomness.

Unfortunately there's no fix by MySQL yet. The only workaround that I know of is downloading the source code of Connector/NET and fixing/building it yourself.

Otherwise, stick to the older stable version 6.8.7

Thanks: AronVanAmmers

Reference: http://stackoverflow.com/a/29265962/1931848

sql_mode "only_full_group_by" in MySql

If you are getting one of the following errors, you have this problem;

1. Error Code: 1055. 'test.somefield' isn't in GROUP BY
2. SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.somefield' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Check the current sql_mode in your mysql server with the following command to see if the only_full_group_by is enabled;

SELECT @@sql_mode;

This will give a list of sql_modes enabled in the server. Example as follows,

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

As you can see from the above result, you have a value called "only_full_group_by". That means if you have a group by clause in your sql statement, only the fields which are listed in a group by clause can be used in the select clause or order by clause, unless you aggregate the fields. i.e, using aggregate functions such as min(), max() etc

NOTE: As of MySQL Server version 5.7.5, the only_full_group_by is enabled in the sql_mode by default.

Temporary Fix:
Temporarily you can fix this issue by disabling this check in the server by globally and/or only in the active session by issuing the following command respectively.

SET GLOBAL sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

SET SESSION sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Permanent Fix:
- Make sure nonagregated field list in select or order by clause are in group by
- Restructure the sql statement

MySql prepare historic data for reporting purpose

-- Prepare a table for days to get sequence of numbers from 1 to 32

CREATE TABLE ids (
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO ids SET id = 1;           #      1      1
INSERT INTO ids SELECT NULL FROM ids; #      2      2
INSERT INTO ids SELECT NULL FROM ids; #      4      4
INSERT INTO ids SELECT NULL FROM ids; #      8      8
INSERT INTO ids SELECT NULL FROM ids; #     10     16
INSERT INTO ids SELECT NULL FROM ids; #     20     32

set @enddate = "2015-12-01";
set @days = 15;

SELECT date(qb.date) as date, COALESCE(sum(net_amount), 0) as total from invoice qa
right join
(SELECT
(SELECT @enddate FROM invoice qc LIMIT 1) + INTERVAL 1 DAY - INTERVAL a.id DAY AS date
FROM test.ids a
    ) as qb
on date(qa.date) = qb.date
where qb.date > DATE_SUB(@enddate, INTERVAL @days day)
group by qb.date
order by qb.date asc;

Basic Auditing in MySQL

Introduction

Keeping track of a record about the time it was created and the time it was last updated, may be important when you want that information.

This document explains how to implement this in any of the given table.

Implementation

There are two ways of implementing this in the database table. First method is to use ALTER TABLE and TRIGGER. The other method is to define in the ALTER TABLE itself. The second method will only work in MySQL 5.6.5 and later versions.

Before following the steps below, make sure you are selected the correct database and make sure the column names you use for created time and modified time do not already exists in the table.

Method 1

ALTER TABLE tblname

ADD COLUMN modifiedtime TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

ADD COLUMN createdtime DATETIME;

DELIMITER //

DROP TRIGGER IF EXISTS tblname_insert_trigger//

CREATE TRIGGER tblname_insert_trigger

BEFORE INSERT ON tblname

FOR EACH ROW

BEGIN

IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN

SET NEW.CreatedTime = NOW();

END IF;

END;//

DELIMITER ;

Method 2

This method will only work in MySQL 5.6.5 and later versions.

ALTER TABLE tblname

ADD COLUMN modifiedtime TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

ADD COLUMN createdtime DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

Update Existing Records

If you want to keep existing records’ audit fields as null to differentiate that those records were created and modified before implementing audit fields, execute the following statements just after the above steps.

SET SQL_SAFE_UPDATES = 0;  -- bypass the workbench safe mode, if you need

UPDATE tblname SET modifiedtime = null, createdtime = null;

References

1.       Automatic Create and Modified timestamps in MySQL
https://www.marcus-povey.co.uk/2013/03/11/automatic-create-and-modified-timestamps-in-mysql

2.       Having both a Created and Last Updated timestamp columns in MySQL 4.0

http://stackoverflow.com/a/267675/1931848