Category Archives: MySQL

MySQL: Efficient Stored Procedure Editing

This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and “Create Stored Routite…” This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

  • In your ~/.bashrc add the following…
    export EDITOR=vim VISUAl=vim
    
  • Create / Edit ~/.vimrc file, and add the following…
    set noai
    set nonu
    
  • Log into the MySQL CLI, and set the following command
    delimiter //

The Developer now is ready on the server side to create/edit the procs. On the client side I assume the Developer has a Notepad equivalent text editor, or even better, I suggest Kate (if you are on Linux box).

In the editor…

  • Write an if exists drop procedure statement…
  • With few carriage returns following…, write the whole content (SP)
  • If a specific user/s need execute, write the grant execute permission after few carriage returns…

The dev is now ready to Create the proc..

  1. Paste the if exists drop statement and execute
  2. In the mysql prompt, select the dB and enter the following prompt
    \e

    . This is basically to go into editor mode. Press “i”, to get into the insert mode and paste the actual SP Content

  3. Press Escape key, to go back in normal mode and then press “:wq” to save and exit
  4. Enter “//” (the command delimiter) and press Enter, that should either create the proc, or return with the errors.
  5. Execute the Execute Grant statements

Done, Simple.

PS: Copy Paste @ rescue yet again !!! 😀

Replication: Same Server, Rewrite database

MySQL support same-server replication into another database, Its quite a weired requirement, but in reality weired is common.

Consider a server 192.168.5.70, which has 2 databases db1 and db2
Now we shall set up replication for two tables on db1, ie. table1 and table2.

Here is the my.cnf

[mysqld]
server-id=1
#### Replication ####
report-host=master-is-slave-host
log-bin=192.168.5.70-binlog
relay-log=192.168.5.70-relaylog

replicate-same-server-id=1

binlog-do-db=db1

# Note.... On rewrite, the  command is changed into buffer
# so the replicate-do-db and replicate-do-table should have the
# re-written db name.
replicate-rewrite-db=db1->db2
replicate-do-table=db2.table1
replicate-do-table=db2.table2

Lets look at it carefully.

replicate-same-server-id=1
This is to tell the slave the commands that it has to execute will be having the same server-id as its own.

binlog-do-db=db1
To log only database db1

replicate-rewrite-db=db1->db2
Let the slave know that whatever command is for db1 it has to execute on db2

replicate-do-table=db2.table1
replicate-do-table=db2.table2

Note how the table in db2 is replicated, as against the general thinking of db1.table1, MySQL could be using some buffered string and then evaluating the replicate-do-table rule.

MySQL Connection Errors

There could be many reasons why a connection to MySQL server can fail, like

  • Networking Problem
  • Server itself could be down
  • Authentication Problems
  • Maximum Connection Errors allowed.

Of all the errors, this thread will discuss Maximum Connection Errors.
This particular parameter max_connect_errors defines the no. of connection errors a particular host can make before it is banned. Yes Banned! This is a feature that MySQL provides to limit erroneous clients.

By default 10 maximum connection errors are allowed per host. You can check using foll. command.

mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
+--------------------+-------+
1 row in set (0.02 sec)

You can set the above variable in the same manner as setting max_connections, ie in my.cnf file.

[mysqld]
max_connect_errors=100
...

Once particular host exceeds the no. of errors, a particular command is required to be given to the server to reset the host connect errors and indirectly allow the hosts to connect to the server. This command flush hosts will help you do that.

In our scenario, where there are 300 connections per/second, and each connection-query is taking some processing time before connection is closed, There could be a time where the Server is busy, and starts to queue processes, in such a case the connection would be still held until its query is processed. So when a new connection is required, the server may reject it considering the connections that are currently active. ie. We’d set max_connections to 300, so every new connection request could be denied adding to the connection errors, which could mean the host could be blocked. Considering a Web Server + MySQL Server scenario, the web server’s host could be banned until a flush hosts is fired at the MySQL Server.

So in such a case, it is necessary to increase the no. of simultaneous connections that can be made to the server by changing the max_connections parameter. Still if the load on MySQL server is high, and you are getting Connection errors, its time to load balance the Server.

MySQL Connections

MySQL will allow n number of connections at a given point of time, To find out that n no of connections run the following command.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

So this server will allow max of 100 connections at any given point of time.

Before we change the following variable, its necessary to understand how MySQL uses connections and tables. Consider that for every connection on your server, you are querying 3 tables. ie. MySQL will open 3 file descriptors for each connection (1 for each table)

MySQL Doc
…the table is opened independently by each concurrent thread. …

Considering your site has a about 300 connections per/second, 200 of those connections will be dropped since your setting is 100. So changing the setting to 300, MySQL will open (300 x 3 = 900) file descriptors. A file descriptor is nothing but a connection to that file (programmers,sysads will know what I mean). Now its necessary to check what does your OS suuport, normally OS will support about X no. of file descriptors at a given point of time. So increasing your connection settings will not help solve the issue. You need to consult your OS’s manual before changing the settings on a production server.

Fine, Lets assume that every param is under the limit, we can tweak around, As in MySQL almost every parameter can be controlled by configuration file /etc/my.cnf in Linux or [MySQL INSTALL DIR]\my.ini in Windows.

Open the file and add the following under mysqld section max_connections=300. So it should be something like this.

[mysqld]
max_connections=300
...

Restart MySQL Server after changing the file. You can alternatively run a Command while the server is running, but the setting won’t be maintained if the server restarts. The following command can be executed

mysql> set global max_connections=300;

References

  1. Show Variables
  2. Server System Variables
  3. Server Parameters
  4. Too many connections
  5. Table Cache