top
logo


Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • MariaDB 10.3.4 and MariaDB Connector/C 3.0.3 and 2.3.5 now available
    The MariaDB project is pleased to announce the availability of MariaDB 10.3.4, the second beta release in the MariaDB 10.3 series, as well as MariaDB Connector/C 3.0.3, the latest stable release in the MariaDB Connector/J 3.0 series, and MariaDB Connector/C 2.3.5, the latest stable release in the MariaDB Connector/C 2.3 series. See the release notes […] The post MariaDB 10.3.4 and MariaDB Connector/C 3.0.3 and 2.3.5 now available appeared first on MariaDB.org.

  • A tale of Corrupt InnoDB table, MySQL crash & recovery
    I’m going to narrate you a story that happened around a crashing MyQL, Corrupted InnoDB table and finally the recovery by table restore. We will see how our database administrator detected the issue and what he did to resolve it. A day in MySQL Database Consultant’s day was taking its shape while a friend called […]

  • Now with System Versioned Tables - Announcing MariaDB Server 10.3 Second Beta
    Now with System Versioned Tables - Announcing MariaDB Server 10.3 Second Beta RalfGebhardt Thu, 01/18/2018 - 07:19 We are happy to announce the second beta release of MariaDB Server 10.3, the fastest growing open source relational database. Beta is an important time in our release and we encourage you to download this release today! Please note that we do not recommend running beta releases in production. MariaDB Server 10.2 added enhancements like Window Functions, Common Table Expressions, JSON functions and CHECK constraints. MariaDB Server 10.3 is the next evolution. For MariaDB Server 10.3 a lot of effort has been spent on database compatibility enhancements, especially for stored routines. This will allow easier migration of stored functions and better usability of stored functions in general. With System Versioned Tables we are now adding a new powerful enhancement to MariaDB Server, the ability to process temporal data. This opens new use cases to MariaDB Server, like retrospective and trend data analysis, forensic discovery, or data auditing. System Versioned Tables could  be used for compliance, audit, risk analysis, or position analysis.  Enabling the System Versioned Tables feature is as easy as altering an existing table: ALTER TABLE products ADD SYSTEM VERSIONING; or when creating a new table: CREATE TABLE products ( pname VARCHAR(30), price decimal(8,2) ) WITH SYSTEM VERSIONING; System versioned tables are storing timestamps for when data has been added until it has been updated or deleted. This allows to query the data "as of" a given time, or to compare the data "as of" a different date and time. SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP @t1; Now, with MariaDB Server 10.3.4 beta, several significant features and enhancements are available for our users and customers, including: Temporal Data Processing System Versioned Tables store information relating to past and present time Database Compatibility Enhancements PL/SQL Compatibility for MariaDB Stored Functions: The server now understands a subset of Oracle's PL/SQL language instead of the traditional MariaDB syntax for stored routines New option for CURSOR in stored routines: A CURSOR can now have parameters used by the associated query New data types for stored routines: ROW data type, TYPE OF and ROW TYPE OF anchored data types Generation of unique primary keys by SEQUENCES: As an alternative to AUTO INCREMENT It is now possible to define names sequence objects to create a sequence of numeric values Operations over result sets with INTERSECT and EXCEPT: In addition to the already existing UNION an intersection and subtraction of result sets is now possible Define Columns to be invisible: Columns now can be defined to be invisible. There exist 3 levels of invisibility, user defined, system level and completely invisible Window Function Enhancement: percentile and median window functions have been added User Flexibility User Defined Aggregate Functions: In addition to creating SQL functions it is now also possible to create aggregate functions Lifted limitations for updates and deletes: A DELETE statement can now delete from a table used in the WHERE clause. UPDATE can be the same for source and target Performance/Storage Enhancements Add columns to a InnoDB table instantly: New columns can be appended  instantly to a InnoDB Table Statement based timeouts: Via WAIT and NOWAIT the lock wait timeout can be explicitly set for a statement Column based Compression: Compression is now possible per column for most data types. The compression is storage engine independent Storage Engine Enhancements Spider Storage Engine: The partitioning storage engine has been updated to the newest release of the Spider Storage engine to support new Spider features including direct join support, direct update and delete, direct aggregates Proxy Layer Support for MariaDB Server: Client / Server authentication via a Proxy like MariaDB MaxScale using a Server Proxy Protocol Support Try out MariaDB Server Beta software and share your feedback! Download MariaDB Server 10.3.4 Beta Release Notes Changelog What is MariaDB Server 10.3?   Community Developer MariaDB Releases We are happy to announce the second beta release of MariaDB Server 10.3, the fastest growing open source relational database. With System Versioned Tables we are now adding a new powerful enhancement to MariaDB Server, the ability to process temporal data. This opens new use cases to MariaDB Server, like retrospective and trends data analysis, forensic discovery or data auditing. Login or Register to post comments

  • Replication Will Not Start On RDS – MariaDB 10.2
    Briefing the recent encounter on a Replication issue with RDS MariaDB 10.2. Problem Statement: After the upgrade of replicas to MariaDB 10.2 on RDS, Once replication is stopped manually through “call mysql.rds_stop_replication;” or replication failed due to some error. Replication cannot be started back using “call mysql.rds_start_replication;” and there is no straightforward way or documented process to start the replication back. Also, most of the replication related RDS commands like skip errors etc will not work. This is due to the implementation of replication handling in RDS. Summary: On Jan 5, 2018, RDS announced support for MariaDB 10.2. Release Notes From Amazon To test the release we tried upgrading one of our read replicas used for development and testing on RDS from 10.1.23 to 10.2.11. Reproducible Test Case: ### I have a running slave upgraded to MariaDB 10.2 mysql> select @@version; +-----------------+ | @@version | +-----------------+ | 10.2.11-MariaDB | +-----------------+ mysql> pager grep -i 'Running:\|Seconds' mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 ### Stopping replication manually mysql> call mysql.rds_stop_replication; +----------------------------------------------------------------------------+ | Message | +----------------------------------------------------------------------------+ | Slave has encountered an error. Run SHOW SLAVE STATUS\G; to see the error. | +----------------------------------------------------------------------------+ 1 row in set (1.39 sec) Query OK, 0 rows affected (1.54 sec) ### RDS threw error info here because, even after RDS performing “STOP SLAVE”, it sees threads owned by “system user”, Which we detailed in the later section. ### Let’s check the slave is actually stopped. mysql> pager grep -i 'Running:\|Seconds' mysql> show slave status\G Slave_IO_Running: No Slave_SQL_Running: No Seconds_Behind_Master: NULL ### Slave is stopped, Let’s try to startup. mysql> call mysql.rds_start_replication; Query OK, 0 rows affected (0.48 sec) ### Call returns OK, Let’s check the status. mysql> pager grep -i 'Running:\|Seconds' mysql> show slave status\G Slave_IO_Running: No Slave_SQL_Running: No Seconds_Behind_Master: NULL ### Call returned OK, Nothing happened. If “call mysql.rds_start_replication” is failing or not working, there is no other documented way to start back the replication in RDS. Let’s take a deep look at RDS implementation. RDS Implementation: As most of us know we will not be granted super privileges on RDS, To manage replication we have to use the procedures used by RDS. This is how the procedure is implemented. Procedure: mysql.rds_start_replication CREATE DEFINER=`rdsadmin`@`localhost` PROCEDURE `rds_start_replication`() BEGIN DECLARE v_mysql_version VARCHAR(20); DECLARE v_threads_running INT; DECLARE v_called_by_user VARCHAR(50); DECLARE v_sleep int; DECLARE sql_logging BOOLEAN; select @@sql_log_bin into sql_logging; Select user() into v_called_by_user; Select version() into v_mysql_version; SELECT COUNT(1) into v_threads_running FROM information_schema.processlist WHERE user = 'system user'; if v_threads_running = 0 then set @@sql_log_bin=off; update mysql.rds_replication_status set called_by_user=v_called_by_user,action='start slave', mysql_version=v_mysql_version where action is not null; commit; select sleep(1) into v_sleep; START SLAVE; SELECT COUNT(1) into v_threads_running FROM information_schema.processlist WHERE user = 'system user'; if v_threads_running = 2 then insert into mysql.rds_history (called_by_user,action,mysql_version) values (v_called_by_user,'start slave', v_mysql_version); commit; Select 'Slave running normally.' as Message; else Select 'Slave has encountered an error. Run SHOW SLAVE STATUS\\G; to see the error.' as Message; end if; else if v_threads_running = 2 then Select 'Slave may already running. Call rds_stop_replication to stop replication;' as Message; end if; end if; set @@sql_log_bin=sql_logging; END Procedure In Words: – Get count of threads getting executed by the user ‘system user’ – Only if it’s 0 starts the replication. This is how replication traditionally worked, we will have two threads with the system user, one is IO thread and another is SQL thread. So what has changed in MariaDB 10.2: mysql> show processlist; +----+-------------+-----------------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | MariaDB 10.2 made background processes (InnoDB purge threads / InnoDB shutdown handler) as threads executed by the system user. By default, innodb_purge_threads = 4, 1 shutdown handler thread. So we always have 5 threads running as system user. Issue: As most of the replication handling procedures on RDS “rds_stop_replication, rds_start_replication, rds_skip_repl_error, etc” operates based on the count of the threads ran by “system user”, this update broke the complete implementation of RDS replication handling. I honestly believe RDS should implement more robust validations and fix this bug soon. Temporary Hack: Though it’s not documented I just tried doing some kind of hack after reading the procedure and succeeded starting the stopped replication back. Disable read-only on the replica. update mysql.rds_replication_status set action=’start slave’; Wait for the next minute to cross, Replication will be started. mysql> update mysql.rds_replication_status set action='start slave'; Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from mysql.rds_replication_status; +----+---------------------+-------------------------+-------------+-----------------+-------------+-------------+ | id | action_timestamp | called_by_user | action | mysql_version | master_host | master_port | +----+---------------------+-------------------------+-------------+-----------------+-------------+-------------+ | 1 | 2018-01-18 08:29:55 | mydbops@122.166.223.194 | start slave | 10.2.11-MariaDB | NULL | NULL | +----+---------------------+-------------------------+-------------+-----------------+-------------+-------------+ mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-01-18 08:30:04 | +---------------------+ mysql> pager grep "Running:\|Seconds" mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 ### Voila

  • Donkey System
    Donkey system is a fully automatic MySQL database change system. It gives a great help both to the release of the business and the company’s automated operation and maintenance. Donkey.pptxDonkey_intro.pdf


bottom