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.
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…
Log into the MySQL CLI, and set the following command
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..
Paste the if exists drop statement and execute
In the mysql prompt, select the dB and enter the following prompt
. This is basically to go into editor mode. Press “i”, to get into the insert mode and paste the actual SP Content
Press Escape key, to go back in normal mode and then press “:wq” to save and exit
Enter “//” (the command delimiter) and press Enter, that should either create the proc, or return with the errors.
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
#### Replication ####
# Note.... On rewrite, the
Lets look at it carefully.
This is to tell the slave the commands that it has to execute will be having the same server-id as its own.
To log only database db1
Let the slave know that whatever command is for db1 it has to execute on db2
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.
There could be many reasons why a connection to MySQL server can fail, like
Server itself could be down
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.
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 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)
…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.
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