You get the above error when mySQL is unable to store the database tables in the temporary directory ( /tmp ).
So remove some files from the /tmp folder and make some space that will fix the above problem.
We often see Unwanted features displaying on cPanel. PostgreSQL Databases is one of them.
Usually one can disable these features from WHM => Packages => Feature Manager. But you do not find the PostgreSQL Databases there.
To disable PostgreSQL Databases from displaying on the Cpanel
1) Login to server using ssh
You can find some of the following disabled features in the file /var/cpanel/features/disabled
ssh=0
pgp=0
interchange=0
phppgadmin=0
lists=0
boxtrapper=0
searchsubmit=0
nettools=0
emailscripts=0
setlang=0
sslmanager=0
sslinstall=0
2) Open the file using the vi editor and insert the following line.
postgres=0
3) Save the file. that’s it.
Note : no need to restart the Cpanel.
I think it will be good to Uninstall PostgreSQL Databases completely from the server. We will soon find that too
f you get the error with error no 145
ex : can’t open file users.MYI ( errno : 145 )
At the mysql prompt
Go to that particular database
ex : mysql> use <<database name>>
Then give the following command
mysql> repair table <<table name>>;
This will fix that error.
You may see lot of queries in “sleep” mode when you run below command on many servers.
# mysqladmin proc
It may show you below output
============================================================
726 | gabgov_root | localhost | gabgov_gab | Sleep | 1490 | | |
| 927 | gabgov_root | localhost | gabgov_gab | Sleep | 2091 | | |
| 957 | gabgov_root | localhost | gabgov_gab | Sleep | 1178 | | |
| 968 | gabgov_root | localhost | gabgov_gab | Sleep | 1686 | | |
| 1190 | gabgov_root | localhost | gabgov_gab | Sleep | 424 | | |
===========================================================
What extactly “Sleep” command ? Will it create any problem on mysql load ?
If you have php script and want to connect database the
typical dialog should be in below format.
open db,
work with,
and close connections. If you do not close them, they become persist.
The “sleep” status processes you see in your mysql, is a connection
made to your database from a web server.
Note that the amont of “idle connections” (or sleep commands as you
call them) cannot crash the mysql or overload it, the only problem
that might happen is reaching the maximum opened connections you allow
to your mysql.
by default php will use persistent connections to you mysql which
means, that it will first try to use an existing connection for a new
database access, if not it will open a new one, since apache use
different threads it will allways open a new connection while the
other one is idle and it cannot use that one.
you can minimize it by not allowing persistent connections in your php.ini file
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=Off
and restart you web server daemon.
this way the conection “SHOULD” be closed after the script has finished running.
hope it helps
NOTE: This is for informative purpose only. Don’t implement on any server as we have to support “persistent connections from php”.
Whenever you get a error like
=========================================================
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sweptlin/public_html/index.php on line 342
=========================================================
Go to “/home/sweptlin/public_html/index.php” and open it .
Include @ to all the line which contain “mysql_fetch_array”.
Fo example:-
“while ($row = mysql_fetch_array($result)) { ”
is one of the line in the file “/home/sweptlin/public_html/index.php”
You have to write the above line as
while ($row = @mysql_fetch_array($result)) {
The binary update log is activated by placing log-bin[=binary_update_log_filename] in mysql configuration file /etc/my.cnf The filename is optional, and if you do not supply it, the log will be named hostname-bin, with hostname being the name of the host. Any extension (the last 3 letters after the dot) you supply will be dropped, as MySQL creates its own, starting at 001. This log stores all updates to the database in an efficient binary format (the deprecated Update Log stores the same data in a less compressed format). The binary log stores each SQL statement that makes a change to the database in the same order as they were executed, and for this reason is useful for restoring backups, or for replication. MySQL comes with an executable binary that allows you to view the contents of the binary update log; (since it is a binary format, viewing it in a text editor is no use). It’s called mysqlbinlog, and can be accessed as follows, to view the sample binary update log.
# mysqbinlog hostname-bin.001
A new binary update log is created each time the server is restarted, or the database server is flushed (with FLUSH LOGS, mysqladmin flush-logs or mysqladmin refresh), as well as when a single update log becomes too big (determined by the value of max_bin_log_size, which you can set in your config file. Each time a new one is created, the extension increments by one, from 001 to 002 and so on. The highest number will then always be the most recent log. The names of the update logs are stored in a binary update log index file. This is given the same name as the other binary update logs, but with the extension .index. A sample index file looks as follows:
./hostname-bin.001
./hostname-bin.002
./hostname-bin.003
./hostname-bin.004
./hostname-bin.005
./hostname-bin.006
./hostname-bin.007
./hostname-bin.008
./hostname-bin.009
As these Binary log files occupy a huge disk space on our servers we need to disable these binary log. Just comment-out or remove the line log-bin from the configuration file. You can also disable server-id=1 which is not useful on our servers.
To disable the Binary Update Log
1) su to root
2) vi /etc/my.cnf
find the following lines and comment-out :-
log-bin
server-id=1
Save the file and restart mysql using
3) /scripts/restartsrv_mysql
The common error we sometimes come across while updating the web stats manually for a domain is as given below.
Error while running #/scripts/runweblogs can be fix.
————–
DBD::mysql:b do failed: Can’t open file: ’smtp.MYI’. (errno: 145) at /usr/local/cpanel/cpanellogd line 1924.
DBD::mysql::st execute failed: Can’t open file: ’smtp.MYI’. (errno: 145) at /usr/local/cpanel/cpanellogd line 1926.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/local/cpanel/cpanellogd line 1927.
DBD::mysql:b do failed: Can’t open file: ’smtp.MYI’. (errno: 145) at /usr/local/cpanel/cpanellogd line 1930.
DBD::mysql:b do failed: Can’t open file: ’smtp.MYI’. (errno: 145) at /usr/local/cpanel/cpanellogd line 1931.
DBD::mysql:b do failed: Can’t open file: ’sends.MYI’. (errno: 145) at /usr/local/cpanel/cpanellogd line 1932.
————–
You need to run following command on shell as a root user and and this should fix the error. But take the backup of the
table you are repairing before running the command.
#myisamchk -r /var/lib/mysql/eximstats/smtp.MYI
#myisamchk -r /var/lib/mysql/eximstats/sends.MYI
If you get any error while running the above command use below options.
–safe-recover (-o), the –force (-f) option or by not using the –quick (-q) flag
Once this is done run
#/scripts/runweblogs <usernmae>
To upgrade the mysql server from 4.0 to 4.1, we need to follow the following procedure. First take complete backup of the current databases that are there in the /var/lib/mysql using the “cp -varf” command. Also take backup of the /var/lib/mysql/mysql database into the sql file using the command mysqldump and keep it safely as it is required to revert back from 4.1 to 4.0 incase. Next open the WHM and go to MySQL section under the Tweak Settings page. Click on the 4.1 option and also make sure to select the check box “Use old style (4.0) passwords with mySQL 4.1″ and then scroll down and click on the save box.
With the above steps we are ready to upgrade from 4.0 to 4.1. Next open the shell and then run the command “/scripts/mysqlup –force” and wait for it to finish. Once it is finished, run easyapache and make sure to take backup of httpd.conf and php.ini files before running easyapache. You should not get any problem regarding the databses. If you see any high load being generated and php is taking more resources it should the php.ini file issue but not the mysql upgrade issue ( this was the case of server 79 when it was upgraded from 4.0 to 4.1 )
Though the port 3306 is blocked in the firewall, we see in all the servers as if mysqld is listening on port 3306. This is because of the my.cnf configuration not telling it to stop listening the network. We can specifically tell it to stop listening on the port 3306 with the following entry in the my.cnf under [mysqld] section. Just add
skip-networking
in the above section and then restart the mysql service. Thats it, mysqld will stop listening on the port 3306.
There is a great deal of literature about how to properly secure MySQL
depending on your intended use and circumstances. The MySQL Reference
Manual is an excellent place to start.
In these instructions, we assume that MySQL should only be accessible from
the local host (not by other clients over the network), and the absolute
minimum of ‘root’ level access.
1. Always start mysql for local access only. As root, edit
/etc/init.d/mysql, and find the line that actually runs mysqld_safe.
Add the “–skip-network” option. The result should look something
like:
$bindir/mysqld_safe –datadir=$datadir –pid-file=$pid_file
–skip-networking &
Stop and restart mysql:
/etc/init.d/mysql stop
/etc/init.d/mysql start
2. Limit root and non-essential access. Change the default (empty)
root password. Delete any non-localhost ‘root’ users, and any ‘blank’
users.
mysqladmin -u root password new_password
mysql -u root –password=new_password
mysql> use mysql;
mysql> delete from user where host = ‘%’;
mysql> delete from user where user = ”;
mysql> delete from db where user = ”;
mysql> select user, host from user;
If you see any ‘root’ users other than root@localhost, delete them
– e.g.
mysql> delete from user where user = ‘root’ and host=’myhost.com’
Flush the privileges.
mysql> flush privileges;
mysql> quit