Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 104 Next 30 Older Entries

Displaying posts with tag: Geek (reset)

pt-online-schema change and row based replication
+0 Vote Up -0Vote Down

The way online schema changes have historically worked with statement based replication is to create an empty table on a slave, setup triggers to capture changes to a log table, copy the old table to the new table, apply the changes from the log table and atomic rename. This process breaks when using statement based replication because triggers don’t fire on events replicated to the slave. Triggers will fire on the master and the row events for any modified tables just replicate to the slave. This makes traditional online schema change break with statement based replication.

The workaround I’ve seen a few times at the conference is to run schema changes on the master when using row based replication. This is means either significantly reducing replication capacity during a schema change or having the change run really slowly so replication

  [Read more...]
Select into outfile and load data infile are not complementary by default
+1 Vote Up -0Vote Down

Configuring the character set for MySQL is confusing. It is so confusing that there are roughly 25 different places to configure a character set. Don’t believe me? Add them up. The real number may be closer to 30. I realize a lot of this is due to the age of MySQL and the extent of it’s character set support. MySQL does support character set configuration in many different places which is usually a good thing.

I often complain about defaults that make no sense like lock_wait_timeout=1 year. In this case there is a default that makes absolutely no sense to me. The manual says that select into outfile is the complement of load data infile. It isn’t completely true. They differ in one key aspect, the default character set!. By default select into outfile now does the right thing by using binary character set and

  [Read more...]
How to get from MySQL SQL to C
+1 Vote Up -1Vote Down

Occasionally it is useful to know what a MySQL command is doing internally. Just looking into the MySQL source directory can be overwhelming. Knowing the basics of the handler interface and the sql parser can be a great start for reading the source code to understand what MySQL does under the hood. Here I will cover a little bit about how the SQL syntax is defined.

Everything starts with lex.h and sql_yacc.yy in the sql/ dir. lex.h contains all the functions and symbols used to make up the SQL syntax. The sql_yacc.yy file describes the relationships between these symbols and the C functions responsible for executing them. I’m not sure why some symbol definitions end in _SYM and others don’t. Looking in lex.h “FLUSH” is defined as FLUSH_SYM. To see all the places where flush is allowed in the SQL go back to sql_yacc.yy and grep for it.


  [Read more...]
Percona Live Conference Notes
+3 Vote Up -0Vote Down

This is the required post about things I observed during this years MySQL conference.

Things that are awesome:

  • The tables in sessions. I think these were here last year. They are still awesome this year.
  • The new style power plugs. They solved the problem of people tripping over daisy chained power strips and the strips being accidentally turned off.
  • Massive quantities of coffee and real cream.

Things that can be improved:

  • Lunch tickets. I overheard the same conversation a dozen times about people not being able to find their lunch tickets or not really knowing about them.
  • Make badges reversible. A badge under observation will be facing the wrong way.

Things that just bumped me:

  • The music is different this year. Now it makes me feel like a teenager struggling with a breakup.
  [Read more...]
My new favorite example of why it isn’t a good idea to use reserved words as column names.
+4 Vote Up -1Vote Down

Some show commands support a where clause. The column name that can be used in the expression for the where clause depends on the result of the show command. For example in show tables the column is Tables_in_foo where foo is the database name.

MariaDB [test]> show tables where Tables_in_test = ‘t';
| Tables_in_test |
| t |
1 row in set (0.00 sec)

This is a problem with the show databases command because databases aren’t really *in* anything. Database is a reserved word so this happens.

MariaDB [(none)]> show databases;
| Database

  [Read more...]
Shutting down with mysqld, mysqladmin, SIGTERM, or SIGKILL.
+0 Vote Up -0Vote Down

How do you shutdown mysqld? I tend to use SIGTERM. People where I work tend to use mysqladmin shutdown. When things get bad I use SIGKILL. These three methods will end up with a dead mysqld but the one you choose depends on the situation and can even result in lost data. On Linux the difference between SIGTERM and SIGKILL is significant and often times misunderstood.

Before processes start to die it is important to understand the relationship between mysqld_safe and mysqld. mysqld_safe is the watchdog script for mysqld. It is responsible for starting mysqld and keeping an eye on it. It does this by waiting for mysqld to exit then checking the return code. On a safe shutdown such as one done by mysqldadmin or a SIGTERM mysqld will return zero. When mysqld_safe sees a zero return code it will also exit. If the return code is anything else then

  [Read more...]
Percona Live MySQL Conference
+2 Vote Up -0Vote Down

These are things I like that I consider differences from last years conference. There are plenty of other things I like that don’t need to be listed here.

  • The overall tone and feel of the conference was much less marking and much more technical
  • Refreshingly honest keynotes. There was a lot of coming clean about the history of MySQL and the conference.
  • Percona is very technical but it is also a business. They are very good about bringing out the technical and not being pushy about the business.
  • No ice cream social. A thousand people shaking sticky hands with each other is never a good idea.
  • percona.tv
  • The conference was busy but never crowded

Now for the dislike:

  • Only one song before every session.
  • The chairs. Damn the chairs.
  • Wifi failed more often than it worked. Most of
  [Read more...]
MySQL 5.0 can deadlock when flush logs, show processlist, and a slave connection happen at the same time
+0 Vote Up -0Vote Down

[ Note: I haven't fully investigated this problem but the post has been hanging around in my queue for months. Rather than have it rot there I am publishing what I know in hopes that it helps someone else. ]

There are a lot of different kinds of locks in MySQL. Some of these locks are exposed to users such as intention locks, table locks, and row locks. There are other locks that aren’t exposed as well. These are mutexes that MySQL uses internally to protect resources from being modified by more than one thread at a time. These locks are numerous and complicated. When these locks deadlock mysql can stop dead in it’s tracks. The last deadlock I found happens when flush logs, show processlist, and a slave reconnect happen at the same time. I don’t have a core from the mysqld process,

  [Read more...]
Is group_concat_max_len in bytes or characters?
+1 Vote Up -0Vote Down

The manual says bytes but sometimes it is measured in characters. It seems like group_concat_max_len is in bytes when being passed through a temporary table and in characters otherwise. This works fine when using latin1 but when converting to utf8 mysql must reserve 3 bytes per character when setting types in a temporary table. This is yet another reason to dislike group_concat..

mysql> create table group_concat_bug (str1 varchar(255), str2 varchar(255), str3 varchar(255)) charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table group_concat_bug;

  [Read more...]
The #mysql drinking game
+1 Vote Up -1Vote Down

These are the rules of the Freenode #mysql drinking game.

  • Any non op posts a pastebin link either either the query and no error or the error and no query drink
  • Any non op posts a query with, “why doesn’t this work?” without any explanation about the results they want or what they’re getting drink
  • Domas trolls the channel with a legit issue drink
  • When a postgres guy answers a question about sqlite in #mysql 3 drinks
  • Someone answers a question with, “kill yourself” drink
  • Someone asks a phpmyadmin question
  • Someone asks a workbench question
  • Someone can’t figure out how to reset the root password
  • Someone says they are getting an access denied error but they insist the username and password are correct.
  • Someone asks a mssql question but tries to disguise it as
  •   [Read more...]
    Second update of modifying table statistics in MariaDB
    +1 Vote Up -1Vote Down

    Since my last post I’ve changed how the table statistics work quite a bit in MariaDB. I ran into a few problems with my original changes. In the TiVo 5.0 patch the show table_statistics command chose from one of three hash tables to read from depending on the flags. There is a global hash table for global stats and two in the thd object for session and query stats. Each time a non show query is executed the query statistics are reset. In 5.1 the implementation of show command changed from reading arbitrary data structures to constructing queries to run against information_schema tables. The information_schema tables are constructed on the fly, placed into a

      [Read more...]
    A bit on SANs and system dependencies
    +1 Vote Up -0Vote Down

    It’s fairly well known that I’m not a fan of SANs for mysql. I’m really not a fan of them in general but most of this is from not being a fan of them for mysql. Over the past few decades the server world has migrated from few large expensive servers to lots of small cheap servers. For the most part this is accepted as a good thing. Large mainframe computers were slowly replaced by relatively smaller yet still expensive servers which are now replaced by smaller cheaper servers. I apply this same logic and trend to storage. I think storage should be replaced by smaller local storage or storage services rather than large centralized storage.

    The idea of a SAN seems great on paper. You get a large pool of storage which can be sliced up into smaller pools as needed by different clients. If one client demands more storage it’s fairly straight

      [Read more...]
    Rare MyISAM failing to delete MYD file for temporary tables.
    +1 Vote Up -0Vote Down

    I manage a few systems that every hundred million disk temporary table deletions or so one of them will fail. MySQL will delete the MYI file but leave the MYD behind. It’s very strange. There isn’t an error in the error log but subsequent queries that try to use the same temporary table name will error because the MYD file still exists. The queries fail with an error like ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_25d1_0.MYD’. Fortunately the client error gives the temporary table name so it’s easy to clean up by deleted the MYD file. While I still don’t know what the root cause is but I was able to patch MySQL to fix the issue.

    The old mi_delete_table function which is responsible for deleting only the MYI and MYD file would delete the MYI file and leave the MYD file behind. This function

      [Read more...]
    Second draft of the per session row and index stats patch
    +3 Vote Up -0Vote Down

    I’ve taken the part of the tivo patch that includes table and index statistics and broken it out into it’s own patch. This patch includes the ability to do show [session | global ] table_statistics and the same for index_statistics. In this version the row stats are also logged in the slow query log. To log per query stats I had to track them separately from the per session stats. Because the tracking was already done for the slow query log I’ve modified the command to allow uses to access row stats for the previous query separate from the sum for the current session. The flush commands also act similarly.

    Along with changing the slow query log format I’ve also change it to log the timestamp with every query. This made it easier to do automated parsing of the

      [Read more...]
    max_allowed_packet replication and global variables
    +0 Vote Up -0Vote Down

    The max_allowed_packet variable is used to control the maximum size of a query sent to MySQL. It’s function is fairly well defined in the manual but there is a significant gotcha that exists when changing the size of max_allowed_packet while using replication.

    When the replication threads are created the global max_allowed_packet value is copied in to the thread context like doing a set session command in the slave connection. This is done because replication enforces max_allowed_packet a bit differently than other threads. It accounts for both the size of the packet and the overhead of the replication header. This makes the max_allowed_packet enforcement accurate in replication but it means that the slave thread won’t account for set global max_allowed_packet=N until replication is restarted. It should be possible to do the same

      [Read more...]
    How to be a MySQL DBA and the best MySQL book on the planet.
    +0 Vote Up -1Vote Down

    Recently there was a thread on the mysql mailing list discussing how to become a MySQL DBA. I’m not sure the MySQL DBA role exists in the same capacity that it does in Oracle. Historically the Oracle’s DBAs that I’ve met are focused purely on Oracle. They focus on maintaining Oracle based systems including managing migrations, upgrades, table space sizes and other tasks. MySQL DBAs tend to be filed in to two different buckets, people that work like developers and help with query optimization and people that work like sys admins and are focused on the operation of MySQL. There are very few people who can fill both roles and I think that’s why there are so many MySQL DBA jobs on the market. Companies are looking for one DBA when they should really be looking for two.


      [Read more...]
    EMT SVN now on Google code
    +0 Vote Up -0Vote Down

    Jeremy moved the emt svn repository to google code last night. This gives it better integration with the issues tracker, google’s kick ass source browser and gives me the ability to add more commit rights without giving people accounts on servers. Check out the new source tab. Especially the part that lists the field objects. EMT ships without about 100 metrics not counting dynamic sub fields including checks for mysql, apache, memcache, per process memory, network, and other system stats.

    First post using shap ShapeWriter input method on Android
    +0 Vote Up -0Vote Down

    I must say this is way faster than tapping. It’s surprisingly accurate even after only a few minutes of using it

    WordPress on Android
    +0 Vote Up -0Vote Down

    It’s like my own little twitter. I don’t think I will be publishing much from this but it’s great for creating stub posts.

    Machines Plus Minds: E-Blobs and NoSQL options
    +0 Vote Up -0Vote Down
    Machines Plus Minds: E-Blobs and NoSQL options
    Table statistics draft 2, the slow query log
    +1 Vote Up -0Vote Down

    I’ve posted a new table statistics patch which is the next version of the session table/index statistics patch This version of the patch adds slow query log output. If a query is logged to the slow query log it will have row count statistics added to it.

    I’m not sure about the format of the log which is why I’m posting this so early. The first format I tried was:

    # Time: 100119 19:24:37
    # User@Host: [ebergen] @ localhost []
    # Query_time: 10 Lock_time: 0 Rows_sent: 7 Rows_examined: 3
    # Rows_read: sbtest.foo:3, sbtest.bar:3,
    select * from foo a, bar b where sleep(1) = 0;

      [Read more...]
    First draft of per session table and index statistics
    +2 Vote Up -0Vote Down

    I had some free time over Thanksgiving so I decided to work on something I have been thinking about for quite some time. I hacked up Google’s show table_statistics patch to also track stats per connection. I say this is a first draft hack because I based it off of the v2 patch which uses a straight up hash table instead of the intermediate object cache.

    I’ve added the global/session key word to the existing show table_statistics command in the same way that show status works. This means that the default behavior of show table_statistics is to show session data instead of global data. This is different from the Google patch which only works globally. This has been running in production environments for a bit and seems stable. Note that these environments don’t run at the concurrency that motivated Google to update the patch to be

      [Read more...]
    Attempting to unwind the tangled web of pid file creation.
    +1 Vote Up -0Vote Down

    Previously I wrote about how late the mysql pid file is created in the startup process. At first glance it seemed like a relatively easy thing to fix. In main() there is a call to start_signal_handler(). The first instance of static void start_signal_handler() does only one thing. It checks !opt_bootstrap to make sure mysqld isn’t being called by mysql_install_db. I’m not sure why mysql_install_db can’t have a pid file created but that’s getting outside the scope of my investigation. It seems simple enough to move the call to start_signal_handler() above the call to init_server_components() in main() and have the pidfile created earlier. It turns out pidfile creation happens differently on different arches.


      [Read more...]
    mysqld_safe and pid file creation race condition
    +2 Vote Up -0Vote Down

    mysql_safe is responsible for restarting mysqld if it crashes and will exit cleanly if mysqld was shutdown. The way it determines if mysqld shutdown correctly is if the pid file is cleaned up correctly. MySQL does quite a few things before creating the pid file like initializing storage engines. It can take quite a while to initialize innodb if it has to roll forward the transaction log. During this time if mysqld crashes mysqld_safe won’t restart it. Normally this is ok because the server would just crash again but it can mess with your head a bit if you’re testing changes to mysqld_safe. Especially if those changes involve what mysqld_safe does if mysqld crashes. I think it makes sense to create the pidfile earlier and there is a bug for it. Chime in on the bug if this has burned you.

    InnoDB Deadlock Count Patch
    +5 Vote Up -0Vote Down

    InnoDB Deadlock Count Patch

    Deadlocks are a common occurrence in relational databases. They usually aren’t a problem until they start happening too frequently. Innodb can provide you with information about the latest deadlock in SHOW ENGINE INNODB STATUS. This can be useful for debugging but it’s almost impossible to get the rate at which deadlocks are occurring. This patch applies against MySQL 5.0.72sp1 and probably quite a few other versions. It adds a counter to show table status that tracks the number of deadlocks. In this example mysql-1> is connection 1 and mysql-2> is connection 2.


      [Read more...]
    Default log file name changes and replication breakage.
    +1 Vote Up -0Vote Down

    In a Great Magnet moment Trent Lloyd posted an excellent write-up on how to recover from relay log name changes on the same day I was going to write up a procedure to send to a client who had a similar issue. Thanks Trent! The problem goes a bit deeper than server hostname changes because there have been a few changes to how mysql handles default log file names in 5.0

    Prior to 5.0.38 the default log file name started with the hostname. The problem is, as Trent points out, that if the hostname of the server changes then mysql doesn’t generate default log file names correctly. The error message though is something like:

    090825 18:54:53 [ERROR] Failed to open the relay log ‘/mysql/old_hostname-relay-bin.000015′ (relay_log_pos

      [Read more...]
    OScon so far
    +0 Vote Up -0Vote Down
    Sunday morning, in the wee hours of the morning, while packing, I discovered that I didn't know where either my DL or my Passport is. After spending an hour searching, I gave up, and decided to throw myself on the mercy of the TSA. Which I did at the airport. The TSA supervisor checked my credit cards, FlyClear card, corporate ID card, and CostCo card, and then stamped my boarding pass. I only need to do this 3 more times on this trip, and then do a very deep search of my room when I get back.

    After I landed at SJC, I discovered that Tim Lord has shared the flight with me. If I had known that ahead of time, I would have let him share my taxi ride from Capitol Hill neighborhood to the SEA airport. As it was, I let him share my ride to San Jose Convention Center. I was actually too early to check into my hotel room. So instead I checked in my luggage, and then went off to find the

      [Read more...]
    fadvise syscall, myisam data file caching, and a lesson learned in debugging
    +1 Vote Up -0Vote Down

    fadvise is a system call that can be used to give Linux hints about how it should be caching files. It has a few options for caching, not caching, read ahead, and random access. I was looking into used fadvise because a client ran into an issue where some infrequently used myisam data files were being pushed out of the filesystem cache by binary logs and other activity. The files are used infrequently for queries but when they are used they need to be fast. When the files weren’t cached the particular query ran in about 30 seconds. When they were cached the query ran in .8 seconds — huge difference. The fix seemed pretty trivial, call fadvise on the myd files, they stay in cache, the queries are consistently faster and the problem is solved. It seemed simple but it wasn’t. I’ll cover the myisam issue in more

      [Read more...]
    Why Oracle can’t kill MySQL.
    +4 Vote Up -0Vote Down

    When Oracle agreed to acquire Sun there was some speculation that Oracle might try to kill MySQL. First this wouldn’t be a very prudent effort on Oracle’s part and second it’s not even possible. I think Monty has the best explanation from his comment on his blog:

    The simple fact is one can’t own an open source project. One can control it by controlling the people which are leading and developing it. When a company doesn’t take good care of their employees and those employees start to leave the company and work on the project elsewhere, that company has lost control of the project.

    The whole comment is worth reading. Monty does a good job of putting the Sun purchase into perspective with regards to MySQL and the developer community.

    Update of Google’s Sysbench patch to 0.4.12
    +3 Vote Up -0Vote Down

    [Update: I found the magic javascript links that show old releases of sysbench.]

    Sysbench is an application that can be used to benchmark different system parameters and also includes support for testing MySQL directly. Google has released a patch for sysbench that adds a lot of new OLTP tests. It’s great for testing MySQL and for drag races against Mark’s tests. Their patch seems to apply against sysbench 0.4.10. I was able to find sysbench 0.4.10 but it wasn’t easy so I’ve ported Google’s patch to sysbench 0.4.12.

    Grab the patch here.

    Showing entries 1 to 30 of 104 Next 30 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.