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

Displaying posts with tag: table (reset)

The Dangers in Changing Default Character Sets on Tables
Employee_Team +5 Vote Up -0Vote Down

The ALTER TABLE statement syntax is explained in the manual at:

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

To put it simply, there are two ways you can alter the table to use a new character set.

1. ALTER TABLE tablename DEFAULT CHARACTER SET utf8;

This will alter the table to use the new character set as the default, but as a safety mechanism, it will only change the table definition for the default character set. That is, existing character fields will have the old character set per column. For example:

mysql> create table mybig5 (id int not null auto_increment primary key,      
    -> subject varchar(100) ) engine=innodb default charset










  [Read more...]
Temporary Tables and Replication
+2 Vote Up -0Vote Down

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

  • Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
  • Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
  • Master: INSERT INTO rpltmpbreak VALUES (1);
  • Slave: SHOW SLAVE STATUS \G
  • If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but

      [Read more...]
    Online ALTER TABLE in MySQL 5.6
    Employee_Team +1 Vote Up -0Vote Down
    This is the low-level view of data dictionary language (DDL) operations in the InnoDB storage engine in MySQL 5.6. John Russell gave a more high-level view in his blog post April 2012 Labs Release – Online DDL Improvements.

    MySQL before the InnoDB Plugin

    Traditionally, the MySQL storage engine interface has taken a minimalistic approach to data definition language. The only natively supported operations were CREATE TABLE, DROP TABLE and RENAME TABLE. Consider the following example:

    CREATE TABLE t(a INT);
    INSERT INTO t VALUES (1),(2),(3);
    CREATE INDEX a ON t(a);
    DROP TABLE t;

    The CREATE INDEX statement would be executed roughly as follows:

    CREATE TABLE temp(a INT, INDEX(a));
    INSERT INTO
      [Read more...]
    implementing table quotas in MySQL
    +6 Vote Up -0Vote Down
    I have just seen Limiting table disk quota in MySQL by Shlomi Noach, and I could not resist.
    You can actually implement a disk quota using an updatable view with the CHECK OPTION.
    Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.

    drop table if exists logs;
    create table logs (t mediumtext) engine=innodb;

    drop function if exists exceeded_logs_quota ;
    create function exceeded_logs_quota()
    returns boolean
    deterministic
    return (
    select CASE
    WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024)














      [Read more...]
    What is this MySQL file used for?
    +7 Vote Up -0Vote Down

    MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

    This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

    When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

    You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

    Before you try to work with one of

      [Read more...]
    [MySQL] Deleting/Updating Rows Common To 2 Tables – Speed And Slave Lag Considerations
    +1 Vote Up -0Vote Down

    Introduction

    A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

    Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

    Solutions Suggested By Others

    DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
    DELETE a FROM a INNER JOIN b on a.id=b.id;
    DELETE FROM a WHERE id IN (SELECT id FROM b)

    The Problem With  [Read more...]

    Showing entries 1 to 6

    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.