MySQL: How to Re-Sync (Refresh) Replication Properly

The following steps illustrate how to refresh MySQL replication in a single master-replica (slave) scenario:

1) Connect to MySQL on the master server

2) Issue the following commands:

RESET MASTER;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Take note of the values reported in the row returned by SHOW MASTER STATUS. In my case they look like this:

File	        Position	Binlog_Do_DB	Binlog_Ignore_DB
bin.000001	107		

3) Use mysqldump to perform a full backup of all databases on the master. For example:

mysqldump -h[master-hostname-or-ip] --routines -uroot -pxxxxxxx --verbose --all-databases > all-databases.sql

If you add the –master-data command-line option to mysqldump, the CHANGE MASTER TO command (described in step 6) is written into the dump file, thus saving execution of that statement after importing the dump file into the replica.

Execute the following command, to allow users to begin using the master again:

UNLOCK TABLES;

4) Execute the dump file you just created (all-databases.sql) against the replica (slave) server. Copy the file to the replica server (for faster execution), or execute it locally (whichever is appropriate for your situation).

For example:

mysql -h[replica-hostname-or-ip] --comments -uroot -pxxxxxxx < all-databases.sql

5) Connect to MySQL on the replica (slave), and execute the following commands, replacing the values bin.000001 and 107 with the values output by the SHOW MASTER STATUS command you executed in step 2 (above):

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=107;

6) Next, execute the following commands on the replica:

START SLAVE;

7) Finally, test that the replica’s IO and SQL replication threads are both working again, by executing (on the replica):

SHOW SLAVE STATUS;

Successful completion of the task will be indicated by the following output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

IE BUG: CSS Limit of 31 Link Tags and 4K Style Rules (versions 6-9)

This was so hard to believe I just had to blog it. Quoting from the Microsoft KB for the bug:

• All style tags after the first 31 style tags are not applied.
• All style rules after the first 4,095 rules are not applied.
• On pages that uses the @import rule to continously import external style sheets that import other style sheets, style sheets that are more than three levels deep are ignored.

Affects IE versions 6-9. Lovely. Here’s the source: http://support.microsoft.com/kb/262161

MySQL: INSERTing a row IF NOT EXISTS — Yes, it’s possible!

Here’s how:

INSERT INTO mdr_school_new
(PID, PPID, NAME, MSTREET, MCITY, MZIPCODE, id, entity_creation_timestamp, entity_version, last_updated_timestamp) 
SELECT *
FROM (SELECT '00000011', '00000000', 'NGSP School', '5 Maxwell Drive', 'Clifton Park', '12065', UUID(), NOW(), 0, CURRENT_TIMESTAMP()) as T
WHERE NOT EXISTS(SELECT * FROM mdr_school_new WHERE NAME = 'NGSP School');

MySQL:Old-Style JOIN Syntax is Required for Correlated Subqueries

Notice in the query below, the JOIN/ON syntax we’re all comfortable with does NOT work for correlating subqueries. You must use the old WHERE/= style of joining tables. For example:

SELECT    
    (        # The old-style join syntax used here is REQUIRED for the correlated subquery:
            SELECT COUNT(DISTINCT uus.id) as uus_count
            FROM user_unit_subscription uus, sims_organization so, user u, sims_user su         
            WHERE so.id = c.sims_organization_id
            AND uus.license_unit_id = lu.id
            AND u.id = uus.user_id
            AND su.id = u.sims_id
            AND su.enable = 1
            AND so.deleted = 0
            AND su.organization_id = so.id         
            GROUP BY uus.license_unit_id
     ) as correct_sub_count
FROM customer_order_subscription cos
JOIN customer_order co 
ON cos.customer_order_id = co.id
JOIN customer c 
ON co.customer_id = c.id
JOIN license_program lp 
ON co.license_program_id = lp.id
JOIN license_unit lu 
ON cos.license_unit_id = lu.id
JOIN customer_unit_subscription cus 
ON cos.license_unit_id = cus.license_unit_id 
AND cus.customer_id = c.id
WHERE cos.customer_order_id = customer_order_id;

MySQL MERGE and TEMPTABLE Algorithms for Views

I’ve learned a bit more about views in MySQL, and it’s worth mentioning that in some cases they can be far less efficient than their underlying query.

One thing to try to do with any view you build is add the following to the CREATE portion:

CREATE ALGORITHM = MERGE VIEW v_whatever

If you get a warning that MySQL can’t use the MERGE algorithm, be sure to test your view for performance. In this case, it uses the TEMPTABLE algorithm.

Some restrictions on using MERGE are when you have an aggregate in the SELECT, and also when using ORDER BY.

MySQL Transactions — The Right Way

DROP PROCEDURE IF EXISTS mytestproc;
 
DELIMITER $$
 
CREATE PROCEDURE mytestproc()
BEGIN
    START TRANSACTION;
    SAVEPOINT x;
    
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION    
        BEGIN
            ROLLBACK TO SAVEPOINT x; -- Rollback on exception    
            RESIGNAL; -- Rethrow the error (if you want to raise it upstream, i.e., in .NET/java code)
        END;
        
        #SAVEPOINT x; #named savepoint      
        
        SELECT 1;
    #Other statements here
    END;
    
    COMMIT;
    # Clean Up
    RELEASE SAVEPOINT x;
END$$    
 
DELIMITER ;
 
CALL mytestproc();

MySQL How-To: Match a GUID in a MySQL Regex (Regular Expression)

Here’s a handy regular expression you can use to match guids (values generated by MySQL’s UUID function or by other code) in a MySQL column:

SELECT *
FROM your_table
WHERE guid_column REGEXP '[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}';

Apache Tip : Add a Custom HTTP Response Header

Here’s a handy tip for adding a custom header of your design to every response.

Open your Apache config file (name is usually httpd.conf), usually found in one of these locations (depending on your distro):

/etc/apache2/
/etc/httpd/conf/

Tip: You can also discover the location of this file by executing one of the following:

apache2 -V

or (depending upon your distro):

httpd -V

To find httpd (or apache2, or any other program), use this:

locate httpd

Anyway, to add a header of your choosing to each response, add the following to your config file:

<IfModule mod_headers.c>
        Header set MyHeader "%D %t"
</IfModule>

In the example above, a custom response header named MyHeader is sent along with the value of the current date (%D) and time (%t).

MySQL How-To: Which SQL statements are running right now?

Here’s the basics:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Now these results can be a bit daunting, so you might want to refine your lookup — especially if you have at least a vague idea of what your expecting to find:

SELECT * 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE INFO REGEXP 'FROM too_many_rows_why_did_i_do_this';

As you may know, the output is the same as executing the following:

SHOW FULL PROCESSLIST;

However, unlike the above, you can run full SELECT queries against it.

Note: applies only to MySQL versions >= 5.1.17