March 22, 2014 at 3:15 pm (PHP)
Tags: mysql, too many connections
Some days before, after reached my office, i tried to access my web application, its show me a error message “Too many connections“, so I immediately logged in mysql server and checked the mysql service, it shows me “Running…” and then i connect mysql using
“mysql -u root -p”
its connected successfully. And then i checked current processlist using the below command,
its shows me around 370 queries waiting in the queue which is more than max_connections. I had bad memory to remember everything, i just forget what maximum connection i set to this variable. To find this i edit the my.cnf file and it was 360. Actually this max_connections is a system variable and its default value is 151 which provides better performance. For my application, 151 is not enough, so we set to 360. But on that day we didn’t except this will go more than 360 and mysqld always support max_connection+1(super user). So that when the situation like this comes, we can easily solve the issue or kill the process which are waiting in the queue using the super user account.
When i try to find the number of users logged-in in the site using query, the last connection which i have used(super user account) get hanged and then i couldn’t able to connect mysql. SO now there is no other way for me to connect mysql. i need to wait for the queue to process. I waited for some 30 to 45 minutes but the queue is not processing…so finally i restarted the mysql service and all the queries in the queue get killed and then i able to logged into my application.
The bad thing is we couldn’t able to trace out the issue why this happened? There is no traffic to site and all the servers are very cool. This is one more very good challenge for me after i come back to office from weekend. I succeeded without knowing the root cause of the issue. But after many hours, we found its because of we took DB Dump in the morning without putting the site offline. Mysqldump will lock all the tables while running and queries that request to the server put in the queue and thats the reason why we got this too many connections.
So whenever you taking mysqldump, please put your site down and then take mysqldump. This will avoid these type of issues in feature. This is very good learning for me.
October 27, 2013 at 7:02 am (PHP)
Tags: EB Bill
It is very to easy to register your EB Account in online. Please follow the below steps to register it…
If you don’t have account previously, then follow the below steps,
- Go to https://www.tnebnet.org/awp/userRegister?execution=e1s1
- Clicking the Select button to find your Region
- Find your Region and click on select button.
- Now Enter your consumer number, you can get this number in your EB card at right side top(Ex: xxx-xxx-xxx). Here enter these number without hyphen(-)
- Click the Check Details button
- Once the details came, it will ask you confirmation and click the confirm button to proceed further.
- Now fill your details and click the Register button and it will send you the confirmation email to your registered email ID.
- Go to registered email id and click the confirmation link,
- Now login into site with registered account and password.
- You will find the EB Account that you registered.
You can also register more than one EB account, by clicking the Add Account
October 20, 2013 at 9:21 am (PHP)
Tags: database, ib_logfile0, mysql, Shutdown complete, STORAGE ENGINE failed
Few days back i have mistakenly deleted one database folder directly from the mysql data directory but still the site was working fine. I just wondering how its working?
So i restarted the mysql service. It stopped but didn’t start and getting below error in the log file
[root@localhost html]# tail -f /var/log/mysqld.log
InnoDB: Error: log file ./ib_logfile0 is of different size 0 84992 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
131001 17:54:45 [ERROR] Plugin ‘InnoDB’ init function returned error.
131001 17:54:45 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
131001 17:54:45 [ERROR] Unknown/unsupported storage engine: InnoDB
131001 17:54:45 [ERROR] Aborting
131001 17:54:45 [Note] /usr/libexec/mysqld: Shutdown complete
131001 17:54:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
As usual i started searching google and didn’t find any solution and final got one solution. It’s to delete the ib_logfile0, and i deleted it and trying to start the mysql service and it start working by recreating this file automatically…
What i understand from this, if you going to re-install the mysql or wamp server and to take backup of these databases, you have to follow below instructions,
- First stop the mysql service
- Copy the database which you need to have backup from the mysql data directory
- Re-install / Install either Mysql or Wamp Server
- Stop the mysql service
- Paste the database backup in the mysql data directory which you have taken previously
- Start the mysql service
This will keep the old database backup easily without any problem and this will avoid importing the database again. May be imporing small database is not a problem, but thing when you have some 1GB or @ GB size of db, then it will take more time to import the database. Following the above steps, will save your time.
September 1, 2013 at 5:43 am (PHP)
Tags: drupal, mysql, PHP
The site is currently not available due to technical problems. Please try again later. Thank you for your understanding.
If you are the maintainer of this site, please check your database settings in the
settings.php file and ensure that your hosting provider’s database server is running. For more help, see the handbook, or contact your hosting provider.
There are two things you need to check.
1. Is mysqli is installed or not?
2. Is server able to connect database?
Is mysqli installed or not:
Check info.php file is mysqli is listed in this page, if not then your server will support only mysql not mysqli. If your host doesn’t have mysqli installed, you can it change to use mysql. (MySQLi stands for MySQL Improved.)
Open settings.php change $db_url from mysqli to mysql (settings usually in /sites/default-folder in your drupal installation).
$db_url = ‘mysqli://user:password@hostname/table’;
$db_url = ‘mysql://user:password@hostname/table’;
This means that you can either install MySQLi with your PHP install OR you can simply make the changes to your settings.php file as detailed above.
Is server able to connect database?
Check whether you have passed right username, password and database name and also check whether you have given full permission to access the database from the server.
Hope this will be useful for those having similar problems.
August 24, 2013 at 1:15 pm (PHP)
Tags: mysql, mysql like, query
MySql query doesn’t properly support formatted string replacing when using with LIKE operator in PHP,
db_query(‘select * from users u where u.mail LIKE ‘%%s%’,’smith’);
is not returning any result even though the table has one record firstname.lastname@example.org for the search word ‘smith’ in u.mail field.
I have tried one solution to fetch the result for the like operator as below
db_query(‘select * from users u where u.mail LIKE ‘%%%s%%’,’smith’);
this ‘%%%s%%’ is working fine with the search word if it is *not* started with character ‘s’.
So I have added the % before and after the search word and applied with the query as below
db_query(‘select * from users u where u.mail LIKE ‘%%%s%%’,’%smith%’);
Now this query supports properly and fetch records for all of search words starting with any char.
Hope this would help you on some time.