Westsworld.dk

Tag: MySQL

  • Handle replication errors on Azure Database for MySQL flexible server

    At work we have just recently started moving our data to Azure Cloud. We still have the old on-premise setup running, with databases and web servers.

    To make the move to Azure Cloud, I have used a lot of time prepping the terraform scripts to handle our servers, but I have only recently been able to make a copy of the current database (with a whopping 1.7TB of data) and copy it to the cloud.

    It took a few days to insert the data into Azure MySQL, so after getting a replication error today, I was not ready to drop the replica database, to insert a fresh dump.

    Calling the following select, I could read the error:

     select * from performance_schema.replication_applier_status_by_worker;

    It was a create table error… The master database had created a new table using MyISAM, which Azure DB does not allow.

    Normally, you would increase the global counter, using:

    SET GLOBAL sql_slave_skip_counter = N;

    Azure does not support this, as it requires SUPER privileges (whatever that means). Thankfully Azure have implemented a set of methods to call, where this one exists: (remember to stop the replication first)

    CALL mysql.az_replication_skip_counter;

    This increases the global counter by 1. Then i took the created table, changed the statement to use innodb instead and ran it on the slave. Then i called:

    CALL mysql.az_replication_start;

    And the slave skipped the table create statement from the master, and continued with the replication.

    Link to Azure docs: https://docs.microsoft.com/en-us/azure/mysql/single-server/reference-stored-procedures

  • Problems connecting to unix:///var/mysql/mysql.sock

    In a previous post I talked about MySQL 5.5 and Mac OSX.

    In this post I’ll go through fixing the problems with PHP and connecting to your local mysql install, using “localhost”.

    The problems began a while back, with lots of errors in my apache error log saying:

    [error] [client ::1] PHP Warning:  mysql_connect(): [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock) in ...

    In the rush I was in, I quickly changed my mysql connection to use: 127.0.0.1, which is the IP of your localhost. So basically the same thing.

    Today I’m doing some freelance work for a customer, who has some problems with his server, after the PHP version was upgraded. I decided to fetch all his php files to my local Mac and then run through his webshop, fixing any errors I might see in the log etc.
    When starting the my apache and running the website, I quickly found the MySQL connect error again.

    Since this project should be a “search and fix” mission, I didn’t have time to change the mysql_connect(xxx) statements in the code (yes yes, not my code, so I didn’t create the mess…), so instead, I wanted to fix my local PHP->MySQL connection.

    The fix was relatively easy, and only contains 1 to 2 steps:

    Step 1 (if needed)

    If you haven’t activated php.ini on your local install, open a Terminal and write the following command:

    sudo cp /etc/php.ini.default /etc/php.ini

    This copies the default php settings to the php.ini file, which the apache server uses.

    Then restart your apache server. (Using System Preferences->Sharing->Web sharing)

    Your PHP is now using the php.ini file.

    Step 2

    Open /etc/php.ini file using your favorite text editor.

    Goto line 1216 (or search for “mysql.default_socket = ” without the quotes) and change /var/mysql/mysql.sock to /tmp/mysql.sock

    Restart your apache server and you should now be able to connect to localhost again.

    Still have problems?

    If you still have problems, then try the following:

    Open Terminal and write:

    mysqladmin version

    It should print something like this:

    mysqladmin  Ver 8.42 Distrib 5.1.53, for apple-darwin10.3.0 on i386
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		5.1.53
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			2 hours 52 min 6 sec

Threads: 3  Questions: 58  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.5

    The path in the UNIX socket is the “localhost” connection point. So go back to Step 2 and use that path instead.