Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 1035 10 Older Entries

Displaying posts with tag: General (reset)

MariaDB 10.1.1: Compound statements
+0 Vote Up -0Vote Down

Every now and then there is a need to execute certain SQL statements conditionally. Easy, if you do it from your PHP (or Java or whatever) application. But if all you have is pure SQL? There are two techniques that MariaDB and MySQL use in the mysql_fix_privilege_tables.sql script (applied by mysql_upgrade tool).

  • Create a stored procedure with IF statements inside, call it once and drop it. This requires the user to have the CREATE ROUTINE privilege and mysql.proc table must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
  • Use dynamic SQL, like
    SET @str = IF (@have_csv = 'YES',
                   'CREATE TABLE IF NOT EXISTS general_log (
                      event_time TIMESTAMP(6) NOT NULL,
                      user_host MEDIUMTEXT NOT NULL,

  •   [Read more...]
    MariaDB 10.1.1: no more .frm’s for performance_schema tables
    +0 Vote Up -0Vote Down

    Yes! In MariaDB 10.1.1 tables in PERFORMANCE_SCHEMA do not use .frm files. These files are not created, not read — in fact, PERFORMANCE_SCHEMA tables never touch the disk at all.

    This became possible due to a lesser-known feature of MariaDB — new table discovery (“old table discovery” was implemented in MySQL for NDB Cluster in 2004), implemented in MariaDB 10.0.2. Instead of reading and parsing .frm files, MariaDB simply asks PERFORMANCE_SCHEMA table, what structure it has, and because these tables always have a fixed structure, the table directly returns it to MariaDB with no need for any external data dictionary.

    It also means, you never need to upgrade PERFORMANCE_SCHEMA tables, they

      [Read more...]
    MariaDB 10.1.1: default roles
    +1 Vote Up -0Vote Down

    As you all know MariaDB supported roles since the MariaDB release 10.0.5. They were implemented almost exactly as specified in the SQL Standard 2003, features T331 “Basic roles” and T332 “Extended Roles”.

    But we were often hearing complains, users were not satisfied with purely standard set of features. In particular, the standard specified that one had to do

    SET ROLE foobar;

    to be able to use privileges, granted to the role foobar. This was not always convenient and sometimes not even possible (imagine, you need to grant role privileges to an account used by a closed-source application). There had to be some way to enable a given role automatically, when a user connects.

    To solve this issue we have introduced the concept of a default role. A default role for given user is

      [Read more...]
    Update on Performance measurement on MariaDB 10.1 and MySQL 5.7.4-labs-tplc
    +0 Vote Up -0Vote Down
    Introduction

    This blog is a follow up to my original blog in https://blog.mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/ . First of all I would like to thank for all the comments I received. Based on the comments there was a concern if the differences seen on performance was due to different configuration setup. Furthermore, I did not know that there was a configuration variable to get similar multi-threaded flush mechanism on MySQL as there is on MariaDB. To find out if the different configuration variables or different defaults were the reason for different performance, I ran several rounds of new tests.

    Test 1

    Changing number of buffer pool instances or value of innodb thread concurrency do not

      [Read more...]
    A GeoSpatial Search Odyssey
    +0 Vote Up -0Vote Down
    This post walks through some examples of using the geospatial functions provided by MySQL and Sphinx. Enjoy! Getting Started First of all, here are some helpful resources: We wrote about geospatial search with Sphinx in this blog post. It has a more Sphinx-specific focus than this post. Alexander Rubin wrote these helpful articles: “Creating Geo [...]
    MariaDB Galera Cluster 10.0.13 now available
    +0 Vote Up -0Vote Down

    Download MariaDB Galera Cluster 10.0.13

    Release Notes Changelog What is MariaDB Galera Cluster?

    MariaDB APT and YUM Repository Configuration Generator

    The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 10.0.13. This is a Stable (GA) release.

    See the

      [Read more...]
    Query Sphinx with Node.js
    +0 Vote Up -0Vote Down
    You probably already know that Sphinx supports MySQL binary network protocol. But, if you haven’t heard– Sphinx can be accessed with the regular ol’ MySQL API. Pretty much any language you use to query MySQL can be used to query Sphinx. So, in this post, to demonstrate how using ‘SphinxQL’ (our subset of SQL) makes [...]
    Performance evaluation of MariaDB 10.1 and MySQL 5.7.4-labs-tplc
    +0 Vote Up -0Vote Down

    Introduction

    Evaluating the performance of database systems is a very demanding task. There are a lot of hard choices to be made, e.g.:

    • What operating system and operating system version is to be used
    • What configuration setup is to be used
    • What benchmarks are to be used and how long are the warm-up and measure times
    • What test setups are to be used
    • What version of the database management system is used
    • What storage engine is used

    While performance evaluation is mostly machine time, there is still a lot of hard work for the human monitoring the tests. In this blog post we have made following choices:

    • We’re using an Intel Xeon E5-2690 @ 2.9GHz CPU containing 32-cores and Linux 3.4.12 with 132G main memory. The database is stored on a Fusion-IO ioDrive2 Duo 2.41TB
      [Read more...]
    Sphinx Searches Gmail with MySQL and PHP
    +0 Vote Up -0Vote Down
    There may come a time when you want to collect some of your emails, put them into a database, and search them with Sphinx. If that time has come, you may enjoy this blog post. It will outline one way to get emails from Gmail into MySQL (with PHP’s IMAP extension) so that they become [...]
    Sphinx in Docker. The basics.
    +0 Vote Up -0Vote Down
    With an ear to the interwebs, you’ll hear a few things about Docker. Docker is an open platform for developers and sysadmins to build, ship, and run distributed applications. In this blog post, I’m going to outline a very basic example of how to use Sphinx from within a Docker container. What is Docker? This [...]
    Showing entries 1 to 10 of 1035 10 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.