You are here: Главная Новости MySQL Planet MySQL
Planet MySQL
Planet MySQL -

  • MySQL 8.0 (dev): what to look for
    This is an unstable release, please don’t use in production. It was rumored that the new MySQL version would be 8 and not as 5.8 as a lot of people thought, and it appears the rumors were true. Below are some of the features that caught my eye at first glance: Roles Although password expiration was implemented 5.7, the newer version bring a set of collective privileges as a Role. No need to have to copy paste that massive GRANT command you had when creating new users. UTF-8 as default Charset This is not yet the default charset coming with the server, but utf8mb4 will be the main charset instead of latin1, and the default collation will change from latin1_swedish_ci to utf8mb4_800_ci_ai. The plan is to do that before General Availability. Invisible Indexes Giving an index already exists, you can make it active or inactive. It is a toggling feature which enables the debugging work to see if an index really can be dropped (if it is not being used). This is for the search only, on write operations the index is still maintained. IPv6 and UUID Manipulation MySQL do not support those fields natively, however, it is recommended to store those items with the VARBINARY(16) type. MySQL now provides functions to manipulate textual representations of IPv6/UUID and to use bit-wise operations, to test, extract or compare. Having those function built in, you can use a generated column to index that data. Source A more comprehensive list can be found at the MySQL Server Blog. It is worth the read. ™MySQL is a trademark of Oracle.Filed under: Article Tagged: mysql, mysql 8

  • MySQL 8.0 - auto increment feature gets fixed
    How InnoDB initializes AUTO_INCREMENT counters is actually not a bug, but a documented mechanism. There were some complaints and even people who lost data over this. To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR

  • Visualizing the MySQL Bug Tide
    On the MySQL Bugs website there are some tide stats available. These show rate of bug creation.I've put them in a graph: I made these with this IPython Notebook. There are more detailed graphs per version in the notebook.

    A question which would come sometimes to mind when starting with MySQL is whether I should use DATETIME or TIMESTAMP data type since both appear to store same date and time component.Similarities between datetime and timestamp:1. Values contain both date and time parts.2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current date and time.But both differ in some ways as mentioned below:Differences between DATETIME and TIMESTAMP data types.DATETIME:1. Supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.2. Storage Required Before MySQL 5.6.4 was 8 bytes. *3. Storage Required as of MySQL 5.6.4 is 5 bytes + fractional seconds storage. *4. Preserves the textual representation of the date and time.5. A value in the supported range is saved as it is given to MySQL so lets say if you change the timezone of MySQL, the value remains same that is it stores no timezone information and is timezone independent.Example:By default MySQL uses the SYSTEM timezone as its timezone which is IST in my system.mysql> CREATE TABLE `dt` (  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cimysql> SELECT @@time_zone;+-------------+| @@time_zone |+-------------+| SYSTEM      |+-------------+mysql> INSERT INTO dt VALUES ('2016-09-12 12:12:00');mysql> SELECT * FROM dt;+---------------------+| dt                  |+---------------------+| 2016-09-12 12:12:00 |+---------------------+Now lets change the session timezone to CDT which has an offset -05:00 hours with respect to UTC.mysql> SET @@session.time_zone = '-05:00';mysql> SELECT * FROM dt;+---------------------+| dt                  |+---------------------+| 2016-09-12 12:12:00 |+---------------------+The result above is same irrespective of timezone.TIMESTAMP:1. Supported range is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. **2. Storage Required Before MySQL 5.6.4 was 4 bytes. *3. Storage Required as of MySQL 5.6.4 is 4 bytes + fractional seconds storage. *4. Preserves values relative to the timezone in use.5. A value in the supported range is saved in UTC timestamp value when the value is supplied to MySQL, so the value contains a timezone reference. While fetching the value again, MySQL will convert that value from UTC to the timezone specific value. If the timezone of MySQL is changed it has no effect on the UTC value stored but when the value is fetched it is displayed as per the current timezone of MySQL and not in the original timezone value which was stored first time. This occurs because the same time zone was not used for conversion in both directions.An example of this would be if timezone of MySQL is currently IST and I save a value of "2016-09-12 12:12:00" into the TIMESTAMP datatype field, so when I fetch this record value from MySQL I will get the same value "2016-09-12 12:12:00". Now if I change the timezone value to CDT and fetch this record value, I will get "2016-09-12 01:42:00" which is the CDT equivalent time of IST "2016-09-12 12:12:00".Example:By default MySQL uses the SYSTEM timezone as its timezone which is IST in my system.mysql> CREATE TABLE `ts` (  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cimysql> SELECT @@time_zone;+-------------+| @@time_zone |+-------------+| SYSTEM      |+-------------+mysql> INSERT INTO ts VALUES ('2016-09-12 12:12:00');mysql> SELECT * FROM ts;+---------------------+| ts                  |+---------------------+| 2016-09-12 12:12:00 |+---------------------+Now lets change the session timezone to CDT which has an offset -05:00 hours with respect to UTC.mysql> SET @@session.time_zone = '-05:00';mysql> SELECT * FROM ts;+---------------------+| ts                  |+---------------------+| 2016-09-12 01:42:00 |+---------------------+The result above is the CDT date time equivalent of IST date time "2016-09-12 12:12:00".References:-* As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.** Why is the TIMESTAMP datatype limited to 2038 years and not beyond?- Excerpt from"The Year 2038 problem is an issue for computing and data storage situations in which time values are stored or calculated as a signed 32-bit integer, and this number is interpreted as the number of seconds since 00:00:00 UTC on 1 January 1970 ("the epoch").[1] Such implementations cannot encode times after 03:14:07 UTC on 19 January 2038, a problem similar to but not entirely analogous to the "Y2K problem" (also known as the "Millennium Bug"), in which 2-digit values representing the number of years since 1900 could not encode the year 2000 or later. Most 32-bit Unix-like systems store and manipulate time in this "Unix time" format, so the year 2038 problem is sometimes referred to as the "Unix Millennium Bug" by association."- It is also known as the Y2K28 bug.A nice illustration from wikipedia showing the Year 2038 bug.Caution: While designing applications please consider the range limitation of TIMESTAMP datatype [1970-2038] before using it or consider using DATETIME datatype instead.

  • MySQL CDC, Streaming Binary Logs and Asynchronous Triggers
    In this post, we’ll look at MySQL CDC, streaming binary logs and asynchronous triggers. What is Change Data Capture and why do we need it? Change Data Capture (CDC) tracks data changes (usually close to realtime). In MySQL, the easiest and probably most efficient way to track data changes is to use binary logs. However, other approaches exist. For example: General log or Audit Log Plugin (which logs all queries, not just the changes) MySQL triggers (not recommended, as it can slow down the application — more below) One of the first implementations of CDC for MySQL was the FlexCDC project by Justin Swanhart. Nowadays, there are a lot of CDC implementations (see mysql-cdc-projects wiki for a long list). CDC can be implemented for various tasks such as auditing, copying data to another system or processing (and reacting to) events. In this blog post, I will demonstrate how to use a CDC approach to stream MySQL binary logs, process events and save it (stream to) another MySQL instance (or MongoDB). In addition, I will show how to implement asynchronous triggers by streaming binary logs. Streaming binary logs  You can read binary logs using the mysqlbinlog utility, by adding “-vvv” (verbose option). mysqlbinlog can also show human readable version for the ROW based replication. For example:# mysqlbinlog -vvv /var/lib/mysql/master.000001 BINLOG ' JxiqVxMBAAAALAAAAI7LegAAAHQAAAAAAAEABHRlc3QAAWEAAQMAAUTAFAY= JxiqVx4BAAAAKAAAALbLegAAAHQAAAAAAAEAAgAB//5kAAAAedRLHg== '/*!*/; ### INSERT INTO `test`.`a` ### SET ### @1=100 /* INT meta=0 nullable=1 is_null=0 */ # at 8047542 #160809 17:51:35 server id 1 end_log_pos 8047573 CRC32 0x56b36ca5 Xid = 24453 COMMIT/*!*/;Starting with MySQL 5.6, mysqlbinlog can also read the binary log events from a remote master (“fake” replication slave). Reading binary logs is a great basis for CDC. However, there are still some challenges: ROW-based replication is probably the easiest way to get the RAW changes, otherwise we will have to parse SQL. At the same time, ROW-based replication binary logs don’t contain the table metadata, i.e. it does not record the field names, only field number (as in the example above “@1” is the first field in table “a”). We will need to somehow record and store the binary log positions so that the tool can be restarted at any time and proceed from the last position (like a MySQL replication slave). Maxwell’s daemon (Maxwell = Mysql + Kafka), an application recently released by Zendesk, reads MySQL binlogs and writes row updates as JSON (it can write to Kafka, which is its primary goal, but can also write to stdout and can be extended for other purposes). Maxwell stores the metadata about MySQL tables and binary log events (and other metadata) inside MySQL, so it solves the potential issues from the above list. Here is a quick demo of Maxwell: Session 1 (Insert into MySQL):mysql> insert into a (i) values (151); Query OK, 1 row affected (0.00 sec) mysql> update a set i = 300 limit 5; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0Session 2 (starting Maxwell):$ ./bin/maxwell --user='maxwell' --password='maxwell' --host='' --producer=stdout 16:00:15,303 INFO Maxwell - Maxwell is booting (StdoutProducer), starting at BinlogPosition[master.000001:15494460] 16:00:15,327 INFO TransportImpl - connecting to host:, port: 3306 16:00:15,350 INFO TransportImpl - connected to host:, port: 3306, context: AbstractTransport.Context[threadId=9,... 16:00:15,350 INFO AuthenticatorImpl - start to login, user: maxwell, host:, port: 3306 16:00:15,354 INFO AuthenticatorImpl - login successfully, user: maxwell, detail: OKPacket[packetMarker=0,affectedRows=0,insertId=0,serverStatus=2,warningCount=0,message=<null>] 16:00:15,533 INFO MysqlSavedSchema - Restoring schema id 1 (last modified at BinlogPosition[master.000001:3921]) {"database":"test","table":"a","type":"insert","ts":1472937475,"xid":211209,"commit":true,"data":{"i":151}} {"database":"test","table":"a","type":"insert","ts":1472937475,"xid":211209,"commit":true,"data":{"i":151}} {"database":"test","table":"a","type":"update","ts":1472937535,"xid":211333,"data":{"i":300},"old":{"i":150}} {"database":"test","table":"a","type":"update","ts":1472937535,"xid":211333,"data":{"i":300},"old":{"i":150}} {"database":"test","table":"a","type":"update","ts":1472937535,"xid":211333,"data":{"i":300},"old":{"i":150}} {"database":"test","table":"a","type":"update","ts":1472937535,"xid":211333,"data":{"i":300},"old":{"i":150}} {"database":"test","table":"a","type":"update","ts":1472937535,"xid":211333,"commit":true,"data":{"i":300},"old":{"i":150}}As we can see in this example, Maxwell get the events from MySQL replication stream and outputs it into stdout (if we change the producer, it can save it to Apache Kafka). Saving binlog events to MySQL document store or MongoDB If we want to save the events to some other place we can use MongoDB or MySQL JSON fields and document store (as Maxwell will provide use with JSON documents). For a simple proof of concept, I’ve created nodeJS scripts to implement a CDC “pipleline”:var mysqlx = require('mysqlx'); var mySession = mysqlx.getSession({ host: '', port: 33060, dbUser: 'root', dbPassword: 'xxx' }); process.on('SIGINT', function() { console.log("Caught interrupt signal. Exiting..."); process.exit() }); process.stdin.setEncoding('utf8'); process.stdin.on('readable', () => { var chunk =; if(chunk != null) { process.stdout.write(`data: ${chunk}`); mySession.then(session => { session.getSchema("mysqlcdc").getCollection("mysqlcdc") .add( JSON.parse(chunk) ) .execute(function (row) { // can log something here }).catch(err => { console.log(err); }) .then( function (notices) { console.log("Wrote to MySQL: " + JSON.stringify(notices)) }); }).catch(function (err) { console.log(err); process.exit(); }); } }); process.stdin.on('end', () => { process.stdout.write('end'); process.stdin.resume(); });And to run it we can use the pipeline:./bin/maxwell --user='maxwell' --password='maxwell' --host='' --producer=stdout --log_level=ERROR | node ./maxwell_to_mysql.jsThe same approach can be used to save the CDC events to MongoDB with mongoimport:$ ./bin/maxwell --user='maxwell' --password='maxwell' --host='' --producer=stdout --log_level=ERROR |mongoimport -d mysqlcdc -c mysqlcdc --host localhost:27017 Reacting to binary log events: asynchronous triggers In the above example, we only recorded the binary log events. Now we can add “reactions”. One of the practical applications is re-implementing MySQL triggers to something more performant. MySQL triggers are executed for each row, and are synchronous (the query will not return until the trigger event finishes). This was known to cause poor performance, and can significantly slow down bulk operations (i.e., “load data infile” or “insert into … values (…), (…)”). With triggers, MySQL will have to process the “bulk” operations row by row, killing the performance. In addition, when using statement-based replication, triggers on the slave can slow down the replication thread (it is much less relevant nowadays with ROW-based replication and potentially multithreaded slaves). With the ability to read binary logs from MySQL (using Maxwell), we can process the events and re-implement triggers — now in asynchronous mode — without delaying MySQL operations. As Maxwell gives us a JSON document with the “new” and “old” values (with the default option binlog_row_image=FULL, MySQL records the previous values for updates and deletes) we can use it to create triggers. Not all triggers can be easily re-implemented based on the binary logs. However, in my experience most of the triggers in MySQL are used for: auditing (if you deleted a row, what was the previous value and/or who did and when) enriching the existing table (i.e., update the field in the same table) Here is a quick algorithm for how to re-implement the triggers with Maxwell: Find the trigger table and trigger event text (SQL) Create an app or a script to parse JSON for the trigger table Create a new version of the SQL changing the NEW.<field> to “data.field” (from JSON) and OLD.<field> to “old.field” (from JSON) For example, if I want to audit all deletes in the “transactions” table, I can do it with Maxwell and a simple Python script (do not use this in production, it is a very basic sample):import json,sys line = sys.stdin.readline() while line: print line, obj=json.loads(line); if obj["type"] == "delete": print "INSERT INTO transactions_delete_log VALUES ('" + str(obj["data"]) + "', Now() )" line = sys.stdin.readline()MySQL:mysql> delete from transactions where user_id = 2; Query OK, 1 row affected (0.00 sec)Maxwell pipeline:$ ./bin/maxwell --user='maxwell' --password='maxwell' --host='' --producer=stdout --log_level=ERROR | python {"database":"test","table":"transactions","type":"delete","ts":1472942384,"xid":214395,"commit":true,"data":{"id":2,"user_id":2,"value":2,"last_updated":"2016-09-03 22:39:31"}} INSERT INTO transactions_delete_log VALUES ('{u'last_updated': u'2016-09-03 22:39:31', u'user_id': 2, u'id': 2, u'value': 2}', Now() ) Maxwell limitations Maxwell was designed for MySQL 5.6 with ROW-based replication. Although it can work with MySQL 5.7, it does not support new MySQL 5.7 data types (i.e., JSON fields). Maxwell does not support GTID, and can’t failover based on GTID (it can parse events with GTID thou). Conclusion Streaming MySQL binary logs (for example with Maxwell application) can help to implement CDC for auditing and other purposes, and also implement asynchronous triggers (removing the MySQL level triggers can increase MySQL performance).