cPanel Hosting Stuff

nnoDB.

innoDB is a table type in mySQL.

InnoDB provides MySQL with a transaction-safe storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row. These features increase multi-user concurrency and performance. There is no need for lock escalation in InnoDB because row-level locks in InnoDB fit in very little space.

advantages of innoDB table type are :

* InnoDB tables are transactional: they provide rollback and commit capabilities.
* InnoDB is the only table type in MySQL which supports foreign key constraints.
* InnoDB tables are fast, even faster than MyISAM tables in many simple benchmarks. See the benchmark page.
* InnoDB tables have row level locking: they allow higher concurrency than MyISAM tables which use table level locking, or BDB tables, which use page level locking. High concurrency is reflected in high multiuser performance.
* InnoDB tables provide an Oracle-style consistent read, also known as multiversioned concurrency control. SELECTs do not need to set any locks and need not interfere with inserts and updates to the same table. No other MySQL table type has this property.
* There is a true hot backup tool available for InnoDB, which allows you to make backups of a running database in background, without setting any locks or disturbing database operation.
* Multiversioning also allows you to dump tables from your database with SELECT INTO OUTFILE without setting locks on the tables: the database can keep working while a backup is made.
* InnoDB tables have automatic crash recovery. You do not need to repair your tables if the operating system or the database server crashes, when there is no disk image corruption.
* InnoDB tables can be any size, also on those operating systems where file size is restricted to < 2 GB.

Enabling and Checking innoDB type in mySQL

innoDB type is supported my all mySQL versions starting from mySQL 4.0.x

Go to the file /etc/my.cnf file and either comment out the line or delete the line ’skip-innodb’ and innodb will be enabled.

You can check either your mySQL supports the innoDB type by giving the following command at mysql prompt.

mysql> show variables like ‘have_innodb’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_innodb | YES |
+—————+——-+
1 row in set (0.00 sec)

MySQL and databases

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.

MySQL and databases

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

MySQL and databases

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.

MySQL and databases

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”.

MySQL and databases

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)) {

MySQL and databases

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

MySQL and databases

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>

MySQL and databases

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 )

MySQL and databases

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.

MySQL and databases