Tag: row

Feed: Planet MySQL; Author: Peter Gulutzan; Let's start by combining one new MariaDB feature -- VALUES (5) which is the standard equivalent of the old non-standard "SELECT 5" -- with another new feature in MariaDB 10.3.5, the FOR ... END FOR statement. mariadb>BEGIN NOT ATOMIC > DECLARE v INT DEFAULT 0; > FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR; > SELECT v; >END; Error 2013 (HY000) Lost connection to MySQL server during query Oops. However, the MariaDB folks now know about this, it's bug MDEV-15940" and they know about the other bugs that ... Read More

Feed: Planet MySQL; Author: Tony Darnell; MySQL Enterprise Edition Database Firewall – Control and Monitor SQL Statement Executions April 23, 2018 Leave a comment As of MySQL 5.6.24, MySQL Enterprise Edition includes MySQL Enterprise Firewall, an application-level firewall (it runs within the mysql database process) that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics. Each MySQL account registered with the firewall has its own ... Read More

Feed: Planet MySQL; Author: Federico Razzoli; NOWAIT, WAIT and SKIP LOCKED are syntaxes added in MySQL 8.0 and MariaDB 10.3. The idea came from AliSQL (MySQL fork by Alibaba). It was revisited in MySQL, and I am not aware if MariaDB used the original implementation. While MySQL and MariaDB syntaxes are similar, there are important differences and the compatibility is only apparent. This article discusses these differences. WAIT This syntax is only available in MariaDB. It means that, if a row or table that we want to read is write-locked, we can wait up to the specified number of seconds ... Read More

Feed: Planet MySQL; Author: MySQL Server Dev Team; The MySQL Development Team is proud to announce the second GA release of InnoDB cluster! We listened carefully to the feedback we got from the previous major release (1.0 GA) and the last release candidate (8.0.4 RC) and incorporated many of the suggested changes. And that’s not all, we have introduced exciting new features and fixed several bugs to reach the desired maturity state. Here are the highlights of this release! MySQL Shell 8.0 For this release, we have, as always, focused on the usability of InnoDB cluster as a whole. With ... Read More

Feed: Planet MySQL; Author: Kedar Vaijanapurkar; Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario. The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table. As Linux players we already know of some tools and techniques to export ... Read More

Feed: MariaDB Knowledge Base Article Feed. Author: . XtraDB和InnoDB存储引擎中的AUTO_INCREMENT处理方式有着显著的性能改进。系统变量innodb_autoinc_lock_mode决定了auto-increments的锁模式。 传统的锁模式 设置innodb_autoinc_lock_mode=0，此为传统锁模式。在INSERT操作过程中，它会一直持有表级锁，直到插入语句结束。 连续锁模式 设置innodb_autoinc_lock_mode=1，该连续锁模式对大批量插入语句(例如LOAD DATA INFILE、INSERT ... SELECT语句)生效，对于这些插入语句，它会持有表锁直到插入操作结束。而对于单独的INSERT语句，它不会持有表锁这样重量级的锁，而是持有一个轻量级的、粒度更细的互斥锁，这显然更适合绝大多数插入环境。这是默认设置。 隔行交叉扫描锁模式 设置innodb_autoinc_lock_mode=2，插入操作不会持有任何表级锁。这是最快的插入模式，但对于statement-based replication来说不够安全。 初始化 直到MariaDB 10.2.3，InnoDB/XtraDB使用一个存储在内存中的自增长计数器。当mysqld服务重新启动的时候，计数器会重置，因此所有表的AUTO_INCREMENT = N也会重置。从MariaDB 10.2.4开始，该限制已经被移除了，AUTO_INCREMNT的值可以是持久的。虽然持久，但不意味着是事务安全的，某些情况下AUTO_INCREMENT的值会出现断层。例如，当INSERT IGNORE忽略了某些错误记录时，手动执行了ROLLBACK或ROLLBACK TO SAVEPOINT操作。 例如： CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, i INT, UNIQUE (i)) ENGINE=InnoDB; INSERT INTO t1 (i) VALUES (1),(2),(3); INSERT IGNORE INTO t1 (pk, i) VALUES (100,1); Query OK, 0 rows affected, 1 warning (0.099 sec) SELECT * FROM t1; +----+------+ | pk | i | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ SHOW CREATE TABLE t1G *************************** 1. row ... Read More

Feed: Planet MySQL; Author: René Cannaò; As we all know, MySQL supports using SSL to secure connections by encrypting the data in transit and protecting it from snooping on the wire. As of now, since version v1.2.0e, ProxySQL supports SSL connections to the backends. Frontend SSL is enabled by default from version 2.0, which is under development.Even if frontend SSL is not available before 2.0 version , there is a way to secure frontend traffic by deploying ProxySQL with app hosts and use sockets instead of tcp. This document will cover how to integrate ProxySQL Backend SSL with MySQL to ... Read More

Feed: Planet MySQL; Author: Michael McLaughlin; I’ve started building the new image for the database courses. This one needs to include Oracle, MySQL, Cassandra, Hive, and MongoDB databases; and include examples for C, C++, Java, Perl, PHP, Python, R programming languages. Installing R was a surprise when I saw how many packages there are for it. It’s a standard yum command from the repository, but it will install 256 packages. The command is: yum install -y R Once you install it, you simply start the R interpreter, which is part of the Comprehensive R Archive Network (CRAN). Any installation of ... Read More

Feed: Planet MySQL; Author: MySQL Performance Blog; Borys Belinsky  | April 12, 2018 |  Posted In: MySQL, Percona Monitoring and Management, PMM PREVIOUS POST Percona announces the release of Percona Monitoring and Management 1.9.1. PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible. This release contains bug fixes only and supersedes Percona Monitoring and Management 1.9.0. This release effectively solves the problem in QAN when the Count column actually displayed the number of queries per minute, not per second, as ... Read More

Feed: Planet MySQL; Author: Valeriy Kravchuk; I stopped reviewing MySQL Release Notes for quite a some time, but major GA releases of MySQL do not happen often, so I decided to make an exception and write about some bugs from Community users fixed in MySQL 8.0.11 GA.I'll start with good news about MySQL 8.0.11 GA! You can get sources at GitHub, and I had no problems to build on Fedora 27 on my good old QuadCore box, using the following cmake command line: [openxs@fc23 mysql-server]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/openxs/boost -DENABLE_DOWNLOADS=1 -DWITH_UNIT_TESTS=OFF -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/8.0...[openxs@fc23 mysql-server]$ time make -j ... Read More