Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. This title may be suitable for the new age MySQL Users. Because in 5.7 onwards its already supported to enable GTID online. But still few of my mission critical databases are in 5.6 and handling 70k QPS. So I know enabling GTID needs downtime for this. But in my case, the GTID has been already implemented. But still the replication is using Binlog file name and position for replicating the data. This is my slave status. You can see the GTID has been enabled but the Auto_Position is still 0 which means still my replication is binlog filename and position. No issues with the replication. But the MySQL world already moved to GTID for better control on replication and Failover. Master_Server_Id: 10010 Master_UUID: c924545b-a3e3-11e8-8a39-42010a280410 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: c924545b-a3e3-11e8-8a39-42010a280410:1021245412-5365162807 Executed_Gtid_Set: c924545b-a3e3-11e8-8a39-42010a280410:4975917719-5053294256:5053294258-5365162769 Auto_Position: 0 Im using MySQL Orchestrator for Failover. And we need to use either MySQL GTID for pesudo GTID for failover. I did googling for how can I change this auto position to 1 without breaking the replication. Initially I thought just get the last executed the GTID set from the slave status and purge it. Then change the auto position to 1. But it was a bad idea. WHY? I stopped the slave thread and got this last executed GTID from the slave status. c924545b-a3e3-11e8-8a39-42010a280410:3501467-3659834 Then I ran, set global gtid_purged='c924545b-a3e3-11e8-8a39-42010a280410:3501467-3659834'; So what happened here is, it only purges ID 3501467 and 3659834. Then if I start the slave thread, it’ll get the below error message. Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' MySQL tried to ignore the IDs But we want to purge all the Is till 3659834. Its not a big deal who all are familiar with GTID. It just a logical way thinking :) Just try to purge the GTID set beginning from 1. Follow these steps to convert binlog position based to auto position. DISCLAIMER: This command invokes RESET MASTER. If you have any application or the slave is acting as a master for another slave(Cascading replication), then you will be fired from your organization. So make sure this is only a slave role and its binlog is not used for any streaming or other replication process. mysql> stop slave; Query OK, 0 rows affected (0.05 sec) Get the binlog filename, position, GTID. So if something goes wrong we can resume the replication with binlog file name and position. mysql> show slave status\G; Master_Log_File: mysql-bin.012187 Read_Master_Log_Pos: 450020919 Relay_Log_File: mysqld-relay-bin.007983 Relay_Log_Pos: 450002463 Relay_Master_Log_File: mysql-bin.012187 Slave_IO_Running: No Slave_SQL_Running: No Exec_Master_Log_Pos: 450002253 Relay_Log_Space: 450021421 Retrieved_Gtid_Set: c924545b-a3e3-11e8-8a39-42010a280410:1021245412-5365162807 Executed_Gtid_Set: c924545b-a3e3-11e8-8a39-42010a280410:4975917719-5053294256:5053294258-5365162769 Auto_Position: 0 Reset the master and enable the GTID. mysql> SHOW GLOBAL VARIABLES LIKE 'gtid%'; +---------------+----------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+----------------------------------------------------------------------------------+ | gtid_executed | c924545b-a3e3-11e8-8a39-42010a280410:4975917719-5053294256:5053294258-5365162769 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +---------------+----------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> reset master; Query OK, 0 rows affected (17.23 sec) mysql> set global gtid_purged='c924545b-a3e3-11e8-8a39-42010a280410:4975917719-5053294256:1-5365162769'; Query OK, 0 rows affected (0.06 sec) mysql> change master to master_auto_position=1; Query OK, 0 rows affected (0.15 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G Executed_Gtid_Set: c924545b-a3e3-11e8-8a39-42010a280410:1-5365162769 Auto_Position: 1
  2. Earlier I wrote about check constraints when MySQL 8.0.16 was released. But this week I noticed two different folks having similar problems with them. And sadly it is 'pilot error'.The first was labeled  MYSQL 8.0.17 CHECK not working even though it has been implemented and a cursory glance may make one wonder what is going on with the database.The table is set up with two constraints. And old timers will probably mutter something under their breath about using ENUMs but here they are: JOB_TITLE varchar(20) CHECK(JOB_TITLE IN ('Lecturer', 'Professor', 'Asst. Professor', 'Sr. Lecturer')),   DEPARTMENT varchar(20) CHECK(DEPARTMENT IN ('Biotechnology', 'Computer Science', 'Nano Technology', 'Information Technology')),  And if you cut-n-paste the table definition into MySQL Workbench or MySQL Shell, it is perfectly valid DDL.  So the table is good.  What about the query?INSERT INTO Employee (FNAME, MNAME, LNAME, BIRTHDATE, GENDER, SSN, JOB_TITLE,  SALARY, HIREDATE, TAX, DEPARTMENT ) VALUES  ('Sangeet', 'R', 'Sharma', date '1965-11-08', 'M', '11MH456633', 'Prof', 1200900, date '1990-12-16', 120090, 'Computer');At first glance the query looks good.  But notice the use of 'Prof' instead of 'Professor' and 'Computer' instead of 'Computer Science'.  The two respective constraints are are working as they are supposed to. That is why you see the error message ERROR: 3819: Check constraint 'employee_chk_2' is violated.So how to fix?  Well you can re-write the DDL so that 'Prof' and 'Computer'.  Or you can make the data match the specifications. If you are going to the trouble to add a constraint you are sabotaging your own work by doing things like this.The Second Issue In another Stackoverflow post someone with this table CREATE TABLE Test( ID CHAR(4),     CHECK (CHAR_LENGTH(ID) = 4) );  was wondering why constraint checks were be a problem with insert into Test(ID) VALUES ('12345');    And the error you get if you try the above? ERROR: 1406: Data too long for column 'ID' at row 1!Well, this is not a case where a constraint check is behaving badly.  Look at the table definition.  ID is a four (4) CHAR column.  And the length of '12345' is not four! Now in the past MySQL was lax and would truncate that extra character and provide a warning.  And those warnings were often ignored.  MySQL had a bad reputation for doing that truncation and the SQL mode of the server was changed to a default setting that does not allow that truncation. Now the server tells you the data is too long for that column.  The constraint checks have not come into play at that stage as the server sees you trying to shove five characters into a four character space. So how to fix? 1) make ID a CHAR(5) and rewrite the constraint, 2) change the SQL mode to allow the server to truncate data, or 3) do not try to put five characters into a space you designed for four.My Gripe It is frustrating to see something like constraint checks that are a really useful tool being abused.  And it is frustrating as so many people search the web for answers with keywords and will not look at the manual.  In both of the examples above five minutes with the manual pages would have save a lot of effort.
  3. Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL Notifier version 1.1.8, the latest addition to the MySQL Installer for Windows. MySQL Notifier enables developers and DBAs to easily monitor, start and stop all their MySQL database instances. It provides a familiar Microsoft SQL Server look and feel and integrates with MySQL Workbench. MySQL Notifier is installed using the MySQL Installer for Windows. The MySQL installer comes in 2 versions – Full (400 MB) which includes a complete set of mysql products with   their binaries included in the download or – Web (18 MB – a network install) which will just pull the MySQL   Notifier over the web and install it when run. You can download MySQL Installer from our official Downloads page athttp://dev.mysql.com/downloads/installer/. Changes in MySQL Notifier 1.1.8 (2019-08-23)      * Functionality Added or Changed      * Bugs Fixed Functionality Added or Changed      * MySQL Notifier now supports the following connection        features:           + Strong password authentication to MySQL 8.0 servers             with the default caching_sha2_password             authentication plugin enabled.           + SSH connections that can be created directly in             MySQL Notifier or MySQL Workbench.           + Connections made using SSH tunneling and an existing             SSH server. These connections are created             transparently without also requiring intermediate             software to create the tunnel. Bugs Fixed      * A monitored MySQL instance or Windows service opened the        MySQL Workbench home tab rather than the Administration        tab when the instance or service was selected for        management. (Bug #29891284)      * The operation associated with clicking Add in the Manage        Monitored Items window opened a secondary context menu.        This fix modifies the operation to perform a single        action related directly to the current tab, without        showing a secondary menu. (Bug #29868378)      * The Check for Updates action did not perform the expected        operation, but started MySQL Installer instead. The        action now checks for updated software as intended. In        addition, the related Automatically check for MySQL        updates option replaces the previous period between        update checks (in weeks) with days. The existing value        set for the option is converted from weeks to days        automatically with the installation of this release. The        Automatic check for MySQL updates option requires MySQL        Installer 1.4.31 or higher (see Upgrading MySQL Installer        (https://dev.mysql.com/doc/refman/8.0/en/mysql-installer-catalog-dashboard.html#upgrade-mysql-installer)). (Bug#29868329)      * The general option to Run at Windows Startup, after the        check box was selected, did not remain selected between        sessions or add startup entries when the entry list was        saved. In addition, the Cancel button in the Options        dialog box did not discard the changes made since the        dialog box was last opened. (Bug #29740645, Bug#95250)      * MySQL Router was not monitored even if the router was        configured to run as a Windows service. (Bug#28277456)      * Previously, services and instances could not be monitored        when the remote connection was created using MySQL        Workbench with SSH tunneling. Now, monitoring is enabled        for SSH connections made directly from MySQL Notifier or        MySQL Workbench. (Bug #26446338, Bug #87079)      * An exception was generated when attempting to start MySQL        Installer from MySQL Notifier and then selecting No in        the dialog box to the question about permitting changes.        Now, selecting No performs no action as expected. All        other startup errors are displayed at runtime and logged.        (Bug #26301469, Bug #86738) You can access the MySQL Notifier documentation athttp://dev.mysql.com/doc/refman/5.6/en/windows-notifier.html. You can find our team’s blog at http://blogs.oracle.com/MySQLOnWindows. You can also post questions on our MySQL Notifier forum found athttp://forums.mysql.com/. Enjoy and thanks for the support! On Behalf of Oracle/MySQL Release Engineering Team Prashant Tekriwal
  4. Percona announces the release of Percona Server for MySQL 5.7.27-30 on August 22, 2019 (downloads are available here and from the Percona Software Repositories). This release merges changes of MySQL 5.7.27, including all the bug fixes in it. Percona Server for MySQL 5.7.27-30 is now the current GA release in the 5.7 series. All of Percona’s software is open-source and free. Bug Fixes: Parallel doublewrite buffer writes must crash the server on an I/O error occurs. Bug fixed #5678. On a server with two million or more tables using foreign keys and AUTOINC columns, the shutdown may take a measurable length of time. Bug fixed #5639 (Upstream #95895). If large pages are enabled on the MySQL side, the maximum size for  innodb_buffer_pool_chunk_size is effectively limited to 4GB. Bug fixed #5517 (Upstream #94747). The TokuDB hot backup library continually dumps TRACE information to the Server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850. Key rotation is redesigned to allow  Select rotate_system_key("percona_redo"). The currently used key version is displayed in the innodb_redo_key_version status. Bug fixed #5565. The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748. A multi-table  DELETE with a foreign key breaks replication. Bug fixed #3845. A  TRUNCATEwith any table and interfacing with Adaptive Hash Index (AHI) can cause server stalls due to the interaction with AHI, whether the AHI is enabled or not. Bug fixed #5576 (upstream #94610). In specific configurations and with log_slow_verbosity set to log InnoDB statistics, memory usage increases while running a stored procedure. Bug fixed #5581 Thread pool ability to track network I/O was disabled. Bug fixed #5723. An instance started with the default values but setting the redo log to encrypt without specifying the keyring plugin parameters does not fail or throw an error.  Bug fixed #5476. Setting the encryption to ON for the system tablespace generates the encryption key and encrypts system temporary tablespace pages. Resetting encryption to OFF , all subsequent pages are written to the temporary tablespace without encryption. To allow any encrypted tables to be decrypted, the generated keys are not erased. Modifying the innodb_temp_tablespace_encrypt does not affect file-per-table temporary tables. This type of table is encrypted if ENCRYPTION =’Y’ is set during the table creation. Bug fixed #5736 After resetting the innodb_temp_tablespace_encrypt to OFF during runtime, the subsequent file-per-table temporary tables continue to be encrypted. Bug fixed #5734 Other bugs fixed: #5752, #5749, #5746, #5744, #5743, #5742, #5740, #5711, #5695, #5681, #5669, #5645, #5638, #5593, #5532, #3970, #5696, #5689, #5146, #5715, #5662, #5420, #5149, #5686, #5688, #5697, #5716, #5725, #5773, #5775, #5820, and #5839. Find the release notes for Percona Server for MySQL 5.7.27-30 in our online documentation. Report bugs in the Jira bug tracker.
  5. Do you want to run Galera Cluster in the Microsoft cloud? Why not learn to setup a 3-node Galera Cluster using Microsoft Azure Compute Virtual Machines, and run it yourself. In this webinar, we will cover the steps to do this, with a demonstration of how easy it is for you to do. In addition, we will cover why you may want to run a 3-node (or more) Galera Cluster (active-active multi-master clusters) instead of (or in addition to) using Azure Database for MySQL or MariaDB. We will also cover cost comparisons.  Join us and learn about storage options, backup & recovery, as well as monitoring & metrics options for the “roll your own Galera Cluster” in Azure. EMEA webinar 30th July 1-2 PM CEST (Central European Time)JOIN THE EMEA WEBINAR USA webinar 30th July 9-10 AM PDT (Pacific Daylight Time)JOIN THE USA WEBINAR Presenter: Colin Charles, Galera Cluster Chief Evangelist, Codership