Mysql Max_User_Connection And Cron Job

[cmh]

Helo. I apologize for my English.

I have 2 questions:

---- I'm trying to use Mysql in my program.

If 2 or more users simultaneously try to login to the database, then the second one will have an error.

I understand that a rare case, but I would like to be able to more than one connection.

For example, I started 2 programs and the second could not connect. With the message 'User Some_user_name already has more than' max_user_connections 'active connections'.

How can I fix it?

--- Also How to make CronJob for modify Table of Mysql Every 24 hours

For example:

DB = DBUser

User = DBuserName

Pasword = Pwd

Table = Client

Colum1

ColumAtr

DateColum

If ColumAtr = 'SomeText' and DateColum <DateNow then Colum1 = 'Modify'

How can I do it?

Edited June 5, 2017 by cmh

[Krydos]

Well, the most common reason for the max_user_connections error is if you don't close your open mysql connections. For instance, right now you have four open connections that haven't transfered any data in almost two and a half hours! If you would just close those connections after the query then you wouldn't get that error. Don't worry I closed them all for you so you shouldn't be getting that error right now (until you open them all up again without closing them.)

 

As far as the cronjob goes I would create the cron with a command similar to

23 23 * * * /bin/bash /home/cmh/public_html/cron.sh

That means at 23:23 each day it will run that command and email you any output.

 

And inside cron.sh put something like

#!/bin/bash

mysql --host=localhost --user=cmh_user --database=cmh_db --password=bestpasswordever --execute="update mytable set mytable.somedata = '1' where mytable.id = '1'"

Let us know if you have any other questions.

[cmh]

Thanks. How can I see the number of connections? this?

SHOW PROCESSLIST 

And how do I close them? (Myself)

[Krydos]

Thanks. How can I see the number of connections?

Log in to mysql and run the query:

show processlist;

And how do I close them? (Myself)

Depends on the language you're using. In php you could do something like

// open the connection
$con = new mysqli('localhost', $db_user, $db_pass, $db_data);

// query

// close the connection
$con->close();

[cmh]

I mean close all connections (force)

If I see an error again?

[Krydos]

The show processlist command will show something like this:

mysql> show processlist;

+---------+----------+-----------+----------+---------+------+-------+------------------+
| Id      | User     | Host      | db       | Command | Time | State | Info             |
+---------+----------+-----------+----------+---------+------+-------+------------------+
| 6039687 | cmh_user | localhost | cmh_data | Sleep   | 9001 |       | NULL             |
| 6039688 | cmh_user | localhost | cmh_data | Sleep   | 9002 |       | NULL             |
| 6275255 | cmh_user | localhost | cmh_data | Sleep   | 9003 |       | NULL             |
| 6396731 | cmh_user | localhost | cmh_data | Sleep   | 9004 |       | NULL             |
+---------+----------+-----------+----------+---------+------+-------+------------------+

Then to close the connection type

kill 6039687;

Where that number is the ID of the connection.

 

However, if you already have the maximum number of connections open you won't be able to connect to issue those commands... So, just close your connections in your code.

[Luigi123]

Moved to Customer Service.

[cmh]

mysql --host=localhost --user=cmh_user --database=cmh_db --password=bestpasswordever --execute="update mytable set mytable.somedata = '1' where mytable.id = '1'"

 

When i run this script i get warning via Mail Warning: Using a password on the command line interface can be insecure.

How then is it safe to connect?

Without the command line?

[Krydos]

Edit your command so it has ">/dev/null 2>&1" at the end like this:

mysql --host=localhost --user=cmh_user --database=cmh_db --password=bestpasswordever --execute="update mytable set mytable.somedata = '1' where mytable.id = '1'" >/dev/null 2>&1

[cmh]

Thanks. Also, May i get some log if Script are update/Delete fields?

[cmh]

i add Cronjob

 

59 13 * * * php /home/cmh/sh/cron.php >> /home/cmh/sh/cron.log >/dev/null 2>&1

 

But everyday list is empty Why?

[Krydos]

As it explains on the cron job page, you are only allowed to run two cron jobs per day. That means you can run one cron job every 12 hours, or two cron jobs every 24 hours. For instance on June 23rd you ran more than two cron jobs so all of your cron jobs were deleted. Internal cron jobs are limited to two per day, but external cron jobs can be run as often as every five minutes. Let us know if you need help setting up an external cronjob.

[cmh]

i have problem. i trying to get external log, but it always empty. in cron tab i write something like

php /home/cmh/sh/cron.php >> /home/cmh/sh/cron.log 

ps please reset my jobs, when i tested external log, i created more then 2 jobs

[Krydos]

You're only allowed to run 2 cron jobs per day. If you exceed 2 then all of your cron commands will be deleted, and you run the risk of getting your account suspended. If you need to run cron more than twice a day let us know and we can help you set up an external cron. There is no such thing as resetting your jobs.