Archive for the 'MySQL @en' Category

Setting up MySQL server on Windows from a

Carl on mei 1st 2014

Extract the archive to your prefered location. Start the server with:
Stop the server with:
shell> {DRIVE:\PATH TO MySQL SERVER DIRECTORY NAME}\bin\mysqladmin -u root shutdown

If you want to have MySQL’s data directory in a different location or on another disk, move the directory there. If doing so, you’ll need to use an option file (my.ini or my.cnf), and start the server with:
shell> {DRIVE:\LOCATION[\..\..]\MySQL SERVER DIRECTORY NAME}\bin\mysqld --defaults-extra-file={DRIVE:\PATH TO}\[my.ini or my.cnf]
Stopping server as mentioned above.

Common problems

If you’re using InnoDB and you get an error on startup like below, you can fix it easily by removing the ibdata1, ibdata2, and the ib_logfile* files from the data directory. After restarting you’ll see newly generated files in mysql’s data directory.

InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
120112 5:16:30 [ERROR] Default storage engine (InnoDB) is not available
120112 5:16:30 [ERROR] Aborting

If you get an error like:

140430 22:27:43 [ERROR] [PATH TO......]\bin\mysqld: unknown variable mysql-data-dir=[DIRECTORY TO SPECIFIED DATA DIR]

You are using an old server parameter name. Replace ‘mysql-data-dir’ with ‘datadir’ in your my.ini/.cnf and start your server again

Server Settings

To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:
mysqld --verbose --help
To get an overview of all server parameters, go to: Tuning Server Parameters. The page will be helpfull with optimizing your server settings.

Option file

Have a look at this page: Using Option Files

Starting MySQL as a service

Before installing MySQL as a Windows service, you should first stop the current server if it is running by using the following command:
shell>{DRIVE:\PATH TO MySQL SERVER DIRECTORY NAME}\bin\mysqladmin -u root shutdown

Install the MySQL server as a service
To do so use this command:
shell>{DRIVE:\PATH TO MySQL SERVER DIRECTORY NAME}\bin\mysqld --install
or if you are using an option file:
shell> {DRIVE:\PATH TO MySQL SERVER DIRECTORY NAME}\bin\mysqld --install MySQL --defaults-file={DRIVE:\PATH TO OPTIONS FILE}
To start the service manual replace --install with --install-manual

Remove service

To remove a server that is installed as a service, first stop it if it is running by executing NET STOP MySQL. Then use the –remove option to remove it:
shell> {DRIVE:\PATH TO MySQL SERVER DIRECTORY NAME}\bin\mysqld --remove

Filed in MySQL @en | Reacties uitgeschakeld

ADODB-change database

Carl on aug 9th 2011

In ADODB you cannot use multiple database connections simultaneously. If you need to work cross database, you have two possibilities:

  • Close the existing connection en setup a new one so you can retrieve the required data from the database;
  • Change database.

1. New Connection

$dbhandle = new ADONewConnection( $DSN_1 );
... queries etc. ...
$dbhandle->close ();
$dbhandle = new ADONewConnection( $DSN_2 );
... queries etc. ...
etc, etc

2. Change Database

$dbhandle = new ADONewConnection( $DSN_1 );
... queries etc. ...
$dbhandle->SelectDB ( $databasename );
... queries etc. ...

At first sight the differences do not seem to be very big. However, if your database server has a heavy load, it will certainly make a difference. The first option initialises a new connection every time (even with pconnect) and though you closed the previous connection, the latter still needs to timeout on the server. As a database server is no different then e.g. a web server in handling connections, this means you will be running out of connections (max connections in the server configuration), thus your code will be waiting for a response as the requested connections are being queued. Result for the web application … page timeouts.
The second option re-uses the existing connection. It will only switch from one database to the next. This, however, is only possible if the databases you use can be read by the same user from the same host, as you will understand. The bottleneck of this method is your service provider: Does he, if you are not the one providing the service, allow you to select/update/delete in your multiple databases with one account.

Filed in MySQL @en,PHP @en | Reacties uitgeschakeld