MySQL Workbench Issue on Windows 10

Windows 10 launched recently supports all features that Windows 7 and Windows 8.1 do. But this is not the case with MySQL. If you have a lower version of Windows installed on your machine with MySQL installed and it's been upgraded to Windows 10 then you're surely going to face a problem while launching your MySQL Workbench.

Here is the error you will receive,

There is a dll present in the Workbench installation folder named "HtmlRenderer.dll" which is required to launch the Workbench. After upgrading my machine to Windows 10, I've faced this error and did an extensive internet surfing for the solution. Finally, in a forum I got the solution saying this dll needs to be updated. A person has suggested to compile a source code from codeplex and asked to copy the dll in the Workbench installation folder. Another more kind person have compiled it and shared the dll over there. To make it handy to most of the people who cannot search for the forum, I'm providing that file for download in my blog.

To have the dll, click here.

After downloading, copy and replace the existing "HtmlRenderer.dll" with the new one and then your Workbench should be fine.

For more information regarding this error, here is the MySQL Forum's link which had discussed about it,

http://bugs.mysql.com/bug.php?id=75673

Hash Partitioning in MySQL

Hash Partitioning is a special type of partitioning which ensures even distribution of data among all created partitions. This should be done explicitly while creating table in other partitioning types like "Range" and "List" partitions.

Hash Partitioning is of two kinds,

• Regular Hash Partitioning.
• Linear Hash Partitioning.

In this article, we'll see each partitioning method in detail,

Regular Hash Partitioning

Let's create a table with Hash Partitioning,

CREATE TABLE employees (

     id INT NOT NULL,

     fname VARCHAR(30),

     lname VARCHAR(30),

     hired DATE NOT NULL DEFAULT '1970-01-01',

     separated DATE NOT NULL DEFAULT '9999-12-31',

     job_code INT,

     store_id INT

)

PARTITION BY HASH( YEAR(hired) )

PARTITIONS 4;

This statement creates a tables with four partitions namely p0,p1,p2 and p3. This can be noticed by running the below query against "information_schema",

SELECT TABLE_SCHEMA,

       TABLE_NAME,

       PARTITION_NAME,

       PARTITION_ORDINAL_POSITION,

       PARTITION_EXPRESSION,

       TABLE_ROWS

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_SCHEMA = 'yashwanth' AND TABLE_NAME = 'employees';

The names of partitions cannot be changed as it is decided by MySQL. Now let's insert some rows into it to see how Hash Partitioning works,

INSERT INTO employees VALUES

(1,'Yashwanth','Aluru','2013-09-11','2015-09-11',1,1),

(2,'Yashwanth','Aluru','2014-09-11','2015-09-11',1,1),

(3,'Yashwanth','Aluru','2015-09-11','2015-09-11',1,1);

Observe that we partitioned the table based on the Year part of "hired" column. So we inserted rows with three different years. To see how these rows are inserted into different partitions, run the above query against "information_schema" again,

Three partitions p1,p2 and p3 has one row each. To allocate like this, MySQL has a calculation which is modular function. Suppose the expression defined for partition is "expr" and the number of partitions made is "n" then the partition in which the inserted row is allocated can be calculated as,

N = mod(expr,n)

Here,

For Row - 1, N = mod (2013,4) = 1

For Row - 2, N = mod (2014,4) = 2

For Row - 3, N = mod (2015,4) = 3

The values 1,2,3 does not imply "Partition Ordinal Position" obtained from above query. Instead it implies partition name p(n). This means the row with year 2013 is allocated in p1, 2014 in p2 and 2015 in p3.

This is how Hash Partition works. We've seen how to create a table with Hash Partition. To partition an existing table with Hash Partition,

ALTER TABLE employees PARTITION BY HASH(YEAR(hired)) PARTITIONS 4;

Linear Hash Partitioning

Linear Hash Partitioning differs from Regular Hash Partitioning in the algorithm used for allocating Partition for inserted rows. As Regular Hash Partitioning uses modular function, this uses "Linear Power of Two" Algorithm.

To demonstrate this, I'm using the same "employees" table used above,

CREATE TABLE employees (

     id INT NOT NULL,

     fname VARCHAR(30),

     lname VARCHAR(30),

     hired DATE NOT NULL DEFAULT '1970-01-01',

     separated DATE NOT NULL DEFAULT '9999-12-31',

     job_code INT,

     store_id INT

)

PARTITION BY LINEAR HASH( YEAR(hired) )

PARTITIONS 4;

Insert some rows in it,

INSERT INTO employees VALUES

(1,'Yashwanth','Aluru','2013-09-11','2015-09-11',1,1),

(2,'Yashwanth','Aluru','2014-09-11','2015-09-11',1,1),

(3,'Yashwanth','Aluru','2015-09-11','2015-09-11',1,1);

Run the following query,

SELECT TABLE_SCHEMA,

       TABLE_NAME,

       PARTITION_NAME,

       PARTITION_ORDINAL_POSITION,

       PARTITION_EXPRESSION,

       TABLE_ROWS

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_SCHEMA = 'yashwanth' AND TABLE_NAME = 'employees';

As said above, Linear Hash Partitioning applies "Linear Power of Two" algorithm. Let's see how it works,

• Suppose the partition expression is "expr" and number of partitions made to the table is "n".
• Now, let the partition in which a record is inserted be "P".
• Find the next power of 2 greater than num. We call this value V; it can be calculated as:
• V = POWER(2, CEILING(LOG(2, num)))
• Set N = F(column_list) & (V- 1).
• While N >= num:
• Set V = CEIL(V/ 2)
• Set N = N & (V- 1)
• Now, the record inserted has year "2015" based on which the partition allocated for these records out of 4 partitions made, can be followed,
• Here n=4, hence V = power(2,ceiling(log(2,4))) => V = 4.
• N = 2015 & (4-1) => N = 2015 & 3 => N = 3.
• Here the expression 3>= 4 is false. So, no need of entering while loop which means the records with year (hired) "2015" are allocated in partition p3.

This is how Linear Hash Partitioning works. To alter an existing table with Linear Hash Partitioning,

ALTER TABLE employees PARTITION BY LINEAR HASH(id) PARTITIONS 5;

Some Operations on Hash Partitioning

Coalesce Partitions

Hash Partitioning supports coalescing the partitions. This means, if there are 4 partitions for a table and you want to decrease the number by 2 then you can apply this operation on the table. Here is the command,

ALTER TABLE employee COALESCE PARTITION 1;

With this command, the number of partitions in the table "employees" are decreased by 1. If there are any rows present in that partition then they are merged into the remaining available partitions. Partitions are decreased from the last partition. You cannot delete a particular partition.

This coalescing applies to Hash and Key Partitioning only because in these partitioning methods, the partitions are created and rows are allocated by MySQL whereas in other partitioning methods, user defines allocating partitions to rows explicitly.

Advantages of Hash Partitioning

• Distribution of data is even among all partitions without the intervention of user.
• Partitions can be coalesced to decrease number of files.
• Can be used in Sub-Partitioning.
• No need of defining a primary key on the partition expression column.

Limitations of Hash Partitioning

• Partitioning expression should represent only integer values. Other types of values cannot be present. For example, you cannot Hash Partition a table on a VARCHAR column.
• Individual partitions cannot be dropped.
• Merge, CSV, NDB Cluster, Federated storage engines do not support Hash Partitioning.
• Two columns cannot be used in partition expression. Instead the sum of two column values can be hashed in Hash Partitioning. For example, PARTITION BY HASH (id+year(hired)) is valid in the CREATE statement of above table "employees". In such case, Hashing is applied to the sum of both the column values with the number of partitions.

For more information on Hash Partitioning, follow the below link,

https://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html

Replication in MySQL - Part - 2

This post is the sequel of my first post about Replication in MySQL. In that post, I've explained how to set up Replication. In this post, I'll discuss about different options in Replication.

As I said in my previous post, as soon as the replication is set up with that procedure, it is a server level replication. All changes made at the master is replicated in the slave including operations like creating databases etc. But that may not be a feasible option in all the environments. So, there are options to implement replication in different scenarios. All these options should be availed at the slave.

Replicating a Single Database

Shut down the slave and include the following option in its configuration file and start it again,

replicate-do-db=db_name

Now test this configuration by making any changes in the master database and observing them getting replicated at the slave database. If any database is created at the master then it won't be created at the slave.

Replicating Data Between Databases with Different Names

There is no rule or restriction that replication should happen among the databases with same names. If you wish to replicate data among the databases with the different names then here is the option which should be included in the slave's configuration file,

replicate-rewrite-db=master_db_name->slave_db_name

Turn on the slave server and notice any changes made in the master database is replicated in the slave database whose name is different from that of master.

Ignore a Particular Database for Replication

There is also an option to ignore a particular database while having a server level replication. Just include this option in your slave's configuration file,

replicate-ignore-db=db_name

Replicating Data Within Same Sever

If you do not have two separate MySQL instances, you can check how replication works by implementing it within the same instance. This means, a single instance can work as a master as well as a slave.

To do this, follow the same procedure you follow when two instances are available but include the following option in your master's configuration file,

replicate-same-server-id

This option takes in Boolean values and the default value is set to 0. This prevents the infinite circular loops in replication. As master is also the slave here, it skips the events happening at the slave part. If this option is set to 1 then it doesn't skip writing the slave events to the binary log which have the slave server id. This results in high increase of disk space usage due to the larger log file size.

While doing replication like this, remember to include the following option in addition to the above one,

replicate-rewrite-db=master_db_name->slave_db_name

As we know that replication configured first is a server level replication and whatever the operation done at master is replicated at the slave. Here the master and slave is only one, if you create a database then the slave cannot create it and this error is shown in the Last_IO_Error column of the following statement,

show slave status;

In order to see the data getting replicated from one database to another database, the above option should be included in the configuration file.

Replicating Data at Object Level

Replication can be even done at the object level. In other RDBMS products like SQL Server, Replication is confined only to object level, for database level there are options like Log Shipping, Mirroring etc. In MySQL, there are no many high availability solutions, replication is the most used option.

The following are the different object level replication options to be included at the slave,

• If you need a single table to be replicated then use replicate-do-table=table_name. If multiple tables are needed then use this option for multiple times with different table names.
• If a table should be ignored from getting replicated then use replicate-ignore-table=table_name. If multiple tables have to be ignored then use this option for multiple times with different table names.
• If your database has tables starting with the same naming convention and you want to restrict updates on those tables from getting replicated then use the option replicate-wild-do-table=table_name. Suppose you don't want to replicate the updates happening on the tables starting with the name fact_ then use this option as replicate-wild-do-table=fact_. "_" and "%" are the patterns that can be used here.
• If you want to ignore updates on tables with a naming convention then use replicate-wild-ignore-table=name.

To know more options about replication, click here.

This is the basic information regarding Replication in MySQL. I'll come up with more options and other stuff in my future articles.

Replication in MySQL - Part - 1

Replication is one of the high availability services offered by MySQL. In this article, I'm going to show how to set up replication in MySQL.

In MySQL, you can do everything with the configuration file only. Never mind whether it is on Windows or in Linux, configuration file is the heart of the product. By default, MySQL offers server to server replication.

To start setting up replication, we need to know some things. MySQL Replication terminology has two kinds of instances, one is Master and the other is Slave. All operations performed on the Master are written to a Binary Log File which are processed on the slave. To identify the Master and Slave uniquely, each should be assigned with a server_id. This is also done in the configuration file.

So, the first step in setting up replication is to ensure that binary logging is enabled in your instance and it has a server_id. If they are not enabled by default, stop the Master instance and include the following statements in its configuration file and start it again,

log-bin=yashwanth

server-id=1

The phrase "yashwanth" is the name of the binary log file and it can be anything. If you are using InnoDB Storage Engine then you need to include more three options in your configuration file, 

innodb_flush_log_at_trx_commit=1

sync_binlog=1

innodb-safe-binlog

If the "innodb_flush_log_at_trx_commit" is set to "1" then the log buffer of InnoDB is written out to the log file whenever a transaction is committed and the "flush to disk" operation is performed on the log file.

If "sync_binlog" is set to "1" then the log is flushed to the disk after 1 statement (if autocommit option is enabled) or one transaction. This is the safer option as there will be only one statement loss if there is a crash in the server. If it is set to "0" then logs are flushed based on the operating system's feasibility. It can be set to any value.

Though the "sync_binlog" is set to "1", if transactions are used then the log is synced with the disk only after its commit. If the crash occurs between starting the transaction and committing it then it is rolled back at InnoDB but not at the log file. This causes an inconsistency in the transaction. To avoid this innodb-safe-binlog is enabled to maintain consistency among the transactions. But this option is not needed in MySQL higher than 5.0.3.

Now, assign a server_id to your Slave instance by stopping it and including the following statement in its configuration file, 

server-id=2

The values assigned to the server_ids is as per your wish but just ensure that both do not have the same values.

There are different formats to write statements to the Binary log file which are called Replication Formats. There are three Replication Formats namely,

• Statement Based Replication (SBR) - If this format is enabled, the SQL statements are written to the Binary Log which are propagated from Master to Slave. Those SQL statements are executed at the Slave.
• Row Based Replication (RBR) - Unlike SBR, the events of how the rows are changed at the Master are recorded in the Binary Log rather than recording the SQL statements regarding the changes.
• Mixed Format Replication (MFR) - This is the combination of both the above formats. MySQL takes Statement Based format by default but switches to Row Based format automatically based on the situation.

To know the advantages and disadvantages of above Replication Formats, click here.

To set the desired replication format, include the following option in your configuration file, 

binlog-format=STATEMENT

This is the pre-setup of replication and now comes the actual part. To have the data replicated, the slave should get connected to the master often to get its binary log data. For this, the slave needs a user account of the master. Any existing account can be used if it has a REPLICATION SLAVE privilege. But the suggestion is to create a new account in the master and grant the above privilege to it because the binary log of master is in text format and whenever the slave gets connected to it, the username and password are written in the text format over there. This may be harmful if an existing user account is used for replication. So, now create a user for slave in the master, 

create user 'replication'@'<slave_ip_address>' identified by '<password>'; 

grant replication slave on *.* to 'replication'@'<slave_ip_address>';

Please note that like other privileges, MySQL doesn't allow giving replication slave privilege on a single database. It should be given on the whole instance.

After enabling the binary logging and creating a user at the master, its binary coordinates should be obtained which contain name of the master's binary log file and its position, needed for the slave. It keeps on changing with the changes made at the master. So, pause your operations for a moment with this command,

FLUSH TABLES WITH READ LOCK;

This command doesn't allow any updates on tables. Now execute the below command, 

SHOW MASTER STATUS;

Record the values appeared in the first and second columns as they are useful for the slave. By now, the master configuration is done completely.

Next comes slave configuration out of which the first step is to create a database snapshot of master at the slave. This can be done in two ways depending upon your database size. One is creating using mysqldump and the other is to create using raw data files.

If your database size is lesser then you can have its dump restored at the slave, using mysqldump utility. If the database is larger then you have to copy the raw data files from master to slave using either cold backup or warm backup or hot backup, depending on your application. For more information about backup strategies, click here.

After creating a snapshot in your slave, run execute the following command on it,

CHANGE MASTER TO

MASTER_HOST='master_host_name',

MASTER_USER='replication_user_name',

MASTER_PASSWORD='replication_password',

MASTER_LOG_FILE='recorded_log_file_name',

MASTER_LOG_POS=recorded_log_position;

Now start the slave with the following command to have the replication started,

START SLAVE;

The last step of the replication is to unlock the tables at master for write operations with the following command, 

UNLOCK TABLES;

So your MySQL instances have been configured with replication. Test it by making some changes at Master and observing them getting replicated at the Slave. What we've done is Server level Replication. I don't want to test your patience by making this post much lengthier, I'll write about more replication options in my next post.

MySQL Service Control in Windows

Maintaining MySQL on Windows is easier when compared to that of in Linux. Windows offers an advantage to install it as a Windows Service which can be controlled easily with a click.

Starting or stopping of MySQL service on Windows can be done through Windows Service Manager. To do that, open Run and type services.msc which opens the WIndows Service Manager. In that search for MySQL and right click on it. Then you can do whatever operation you want to do. But this can be done only if you have administrative privileges.

There is a facility to start or stop MySQL on Windows even if you are not an administrator. This cannot be done through GUI and should be done through command line. Follow the following steps to control the MySQL Service without non-administrative privileges,

1) To stop the MySQL Service from the command line, open the command prompt and then go the base directory or the installation directory of the MySQL, generally

C:\Program Files\MySQL\MySQL Server 5.6\bin

2) Now run the following command to stop the MySQL Server,

mysqladmin -u root shudown

    If you have a password to the 'root' account then the command will be,

mysqladmin -u root -p<password> shutdown

3) To start the service again, the general suggested command that should be run in the same installation directory is as follows,

mysqld

But this doesn't work if the server's configuration file is present in a location other than the installation directory. In such case, the above command should be appended with the location of the configuration file, as follows,

mysqld --defaults-file="<Location of the file>"

If you have MySQL Workbench installed, it provides an option to start or stop the server in its INSTANCE section of Management. It issues the above shell commands automatically when clicked either on "Stop Server" or "Start Server" button.

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

The title of this post was the most bugging error for me while working with MySQL on Linux. I installed MySQL Server 5.6 on my Ubuntu system manually. Then I installed and configured MySQL Workbench. Then I restarted my system. When I tried to connect to MySQL Server through command line, I got the error,

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

This means the service MySQL is not running on system and it has to be started. So I tried starting the server with the following command,

shell> sudo /usr/local/mysql/support-files/mysql.server start

Then I got a new error,

couldn't find mysql server (/usr/bin/mysqld_safe)

I understood that it is looking a wrong directory for mysqld_safe because I installed in another location. This can be set in the file my.cnf. It is located in the folder /etc/mysql/. I went to that location and changed the base directory and data directory locations in that file and attempted to start the server, but it was of no use.

I surfed a lot to find a solution for this problem but no blog suggested me the right one. May be, my search was wrong. Some blogs suggested to check whether the directory mysqld exists in the location /var/run/ so that the socket file mysqld.sock can be created while starting the server. Following this suggestion, I created a directory in the location /var/run/ with the name mysqld. Then I tried to start the service and it resulted in a new error,

Starting MySQL... ERROR! The server quit without updating PID file

I didn't know what to do with this error. I searched online for its solution. Many blogs suggested me to kill the process with the name mysql but it didn't help me. Getting frustrated, I removed and re-installed MySQL multiple times on my system. But nothing was fruitful.

Then I observed something. When I installed just MySQL Server, there was no file with name my.cnf in the directory /etc/mysql. It was created only when I installed MySQL Workbench. Before it was installed, the socket mysql.sock was created in the directory /tmp. So, as a last trail, I deleted the file my.cnf from the directory /etc/mysql/ using the following commands, as a root user,

shell> cd /etc/mysql/

shell> rm my.cnf

Then I tried starting MySQL Service with this command,

shell> sudo /usr/local/mysql/support-files/mysql.server start

Surprisingly, MySQL Service got started and I could connected to it through command line. The socket was created in the directory /tmp. Then I found the location of pid file by issuing the following query in the mysql command prompt,

mysql> show variables where variable_name like '%pid%';

Now my MySQL Server is working fine when connected through command line and MySQL Workbench too. Now I can start or stop the server and can play with it! :)

Installing & Troubleshooting MySQL 5.6 Enterprise on Linux

Though newer versions of Linux Operating Systems came up with GUI to help their users, Linux Commands have their own importance. One of such scenarios which I've undergone few days back is installing MySQL on Linux.

I had to work with MySQL on Linux Platform. The operating system given to me was Ubuntu 14.04 LTS. On Ubuntu, MySQL can be installed from the source with a single line command,

shell> sudo apt-get install mysql-server5.6

But I was asked to install MySQL Server 5.6.19 Commercial on the Ubuntu system. Then I downloaded it and tried to install. Though it comes with install instructions, it's difficult to understand the commands for a Windows user like me. Later surfing through internet helped me with the information from different blogs and documents. Thanking all of them, I'm compiling them in this post. In this post, I'll show you how to install MySQL Server 5.6 from a tar file downloaded. 

tar means Tape Archive. It was used in older days of UNIX where files are stored on tapes.

Installing MySQL Server 5.6

Download MySQL Server 5.6 from Oracle Website. As I specified about tar file, download the tar file which is compatible to your OS (32 bit or 64 bit).

After the download gets completed, unzip it.

Open Terminal and switch to Super User account with the following command,

shell> sudo su -

Authenticate yourself by providing your password and switch to root account.

Now copy the extracted .tar file to a location like /usr/local with the following command,

shell> cp <location of the file>/<Name of the file>.tar.gz /usr/local

After doing this, create a group with name mysql and a user mysql in that group. Note that naming the group and user mysql is not a compulsion, it's a convention. You can name them anything.

shell> groupadd mysql

shell> useradd -r -g mysql mysql

Here option -r specifies that a system account is created whose password never expires. Option -g specifies the initial group to which the user belongs to. There must exist a group which is already created.

Now go the location where the tar file is copied and it has to be unzipped to carry out the remaining part of installation. Run the following commands in a sequence,

shell> cd /usr/local

This takes you to the directory /usr/local.

shell> tar zxvf <Name of the tar file>.tar.gz

This command unzips the tar file. The option zxvf means z(unzipping), x(extract), v(print file names verbosely), f(the following argument is the name of file). The tar command is used to create, modify and access files of tar archive.

shell> ln -s <Name of the folder extracted from tar> mysql

In this command, ln means link. It means a link is created between source folder and target folder. This link should be created with an option. Here the link -s specifies that a symbolic link is created between the source folder (extracted tar folder) and the target folder (mysql).

Now a target folder is created with name mysql. Open that folder,

shell> cd mysql

shell> chown -R mysql .

In the above command, chown means Change Ownership. Here the ownership of folder mysql is given to the user mysql. The option -R specifies that this user performs operations on the folder recursively. Do not forget to run the command along with dot(.) at the end.

shell> chgrp -R mysql .

In this command, chgrp means Change Group. This means the folder mysql is now changed its group to mysql. As said above, the option -R specifies that group mysql performs operations on the folder recursively. This command is said to be the sister command of chown. Do not forget to run the command along with dot(.) at the end.

shell> scripts/mysql_install_db -user=mysql

If you observe the directory mysql, there exists a file with name mysql_install_db which contains a script. To install mysql, this should be executed. In the above command, it is executed with the privileges of user mysql.

If you're installing MySQL Server on your machine for the first time, here comes an error that a library libaio1 cannot be found. You need to install this library by using the following command,

sudo apt-get install libaio1

After this library gets installed, run the previous command again which starts installing MySQL on your system.

shell> chown -R root .

This command changes the ownership of the folder to root account.

shell> chown -R mysql data

This command changes the ownership of the folder data to user mysql.

shell> bin/mysqld_safe --user=mysql &

Here mysqld is the service of mysql. The '&' at the end specifies that the command before it should be run in background. The whole command means, the service mysqld present in bin folder must be run with the user mysql in the background.

Next comes an optional command which copies configuration file to a location in /etc folder,

shell> cp support-files/mysql.server /etc/init.d/mysql.server

Now MySQL Server is installed in your system

Connecting to MySQL Server 5.6

To connect to the above installed MySQL Server, 

Open Terminal and switch to root account by authenticating yourself,

shell> sudo su -

Go to the location where MySQL was installed,

shell> cd /usr/local/mysql/bin

Run the executable mysql with the command 

shell> ./mysql

This takes you to mysql command line. To check, run the following query which shows the databases present in the server,

mysql> show databases;

Troubleshooting MySQL Server on Ubuntu

MySQL is easier to install on Ubuntu and annoys that much too. Sometimes when machine is restarted or under any situation, you cannot get connected to the MySQL Server installed on your machine. When you try to get connected, it shows the following error,

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

This means server is not running at local machine unless you didn't do any changes in the configuration file. So, to start the service again, run the following command,

shell> sudo /usr/local/mysql/support-files/mysql.server start

To stop the server, replace start with stop in above command,

shell> sudo /usr/local/mysql/support-files/mysql.server stop

To restart the server, replace start with restart,

shell> sudo /usr/local/mysql/support-files/mysql.server restart

Now play with your MySQL Server on Linux. Hereby I thank all the bloggers who helped me with their educative posts.