Skip to content

In case I'm not around.

Tag: mysql

MySQL drop all tables in database using queries

Tags: ,

During development I sometimes need to drop all tables in a database. When executed, all existing tables are dropped, so be careful executing it.

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

Via Stackoverflow

Leave a Comment

Set charset on PDO connection with mysql in PHP

Tags: ,

When using PDO to connect to MySQL it is wise to explicitly set the character set to utf8 (of course, only when using utf8 is the charset). In the MySQL or MySQLi extension I would normally execute the query SET NAMES utf8 to set it.

In PDO the charset can be specified in the connection string:

$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);

The charset option is only used since PHP 5.3.6, so take this into account when running an older version of PHP. In that case you should run the following statement after constructing the PDO object:

$conn->exec('SET NAMES utf8');

But you should’nt be running such an old version of PHP anyway.

Leave a Comment

Mysql remove on update current timestamp property

Tags:

The MySQL management tool I use automatically creates tables with column of the time ‘timestamp’ with the property
“ON UPDATE CURRENT_TIMESTAMP”. This property means that when a record is updated this column is automatically updated
to the current time. This behavior can be unwanted. You can check whether a column has this property by issuing a ‘desc’ command:

DESC `tag`

Which in this example will result in:

Field   Type    Null    Key Default Extra
id  int(10) unsigned    NO  PRI NULL    auto_increment
label   varchar(25) NO      NULL    
key varchar(25) NO      NULL    
specificDate    date    YES     NULL    ON UPDATE CURRENT_TIMESTAMP

The solution to this problem is to redefine the column manually without specifying this special property. It is possible to specify
a different default value than ‘CURRENT_TIMESTAMP’.

ALTER TABLE `tag`
    CHANGE `specificDate` `specificDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Based on stackoverflow.

Leave a Comment

MySQL skip replication

Tags:

Replication in MySQL is a useful tool to build a fast and reliable database infrastructure. But replication can go wrong, which leaves the MySQL slave unable to execute the queued query. This halts the entire replication process, replication commands after that are also not executed.

The cause for the replication problem can be determined by logging in and the slave and executing the command:

SHOW SLAVE STATUS\G

This shows the following output if replication is running correct, the important attribute is the Slave_IO_State:

mysql> show slave status\G
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 127.0.0.1
                    Master_User: replication
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000045
            Read_Master_Log_Pos: 53443450
                 Relay_Log_File: mysqld-relay-bin.001454
                  Relay_Log_Pos: 53443587
          Relay_Master_Log_File: mysql-bin.000045
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                Replicate_Do_DB:
            Replicate_Ignore_DB: mysql
             Replicate_Do_Table:
         Replicate_Ignore_Table:
        Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
                     Last_Errno: 0
                     Last_Error:
                   Skip_Counter: 0
            Exec_Master_Log_Pos: 53443450
                Relay_Log_Space: 53443587
                Until_Condition: None
                 Until_Log_File:
                  Until_Log_Pos: 0
             Master_SSL_Allowed: No
             Master_SSL_CA_File:
             Master_SSL_CA_Path:
                Master_SSL_Cert:
              Master_SSL_Cipher:
                 Master_SSL_Key:
          Seconds_Behind_Master: 0
    1 row in set (0.00 sec)

It may be that a certain CRUD command cannot be executed, this will be displayed in the same attribute that now shows everything is okay. After examination you may decide to skip that action, because it is not important or you decide to execute it manually. Execute the following command to skip 1 command:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

For more information on replication and other skipping possibilities read this article on ducea.com.

Leave a Comment