Table of Contents

• Udbedring af databasefejl
• MySQL: Efficient manipulation of large amounts of data
• Howto use MySQL databases via ODBC

Udbedring af databasefejl

Det kan i meget sjældne tilfælde ske at en eller flere tabeller i en database ikke længere fungerer som de skal. Det er ekstremt sjældent at databasens data bliver ødelagt, men det kan forekomme at tabellernes indexerings-oplysninger ("stikordsregistret") bliver korrupte.

Fejlen udmønter sig typisk i fejl i denne stil:

1016 - Can't open file: 'sessions.MYI' (errno: 144)

Der kan være forskellige fejlkoder, men fælles for dem er at der refereres til en "MYI"-fil.

Problemet kan i langt de fleste tilfælde afhjælpes på følgende måde:

Log ind i PlanetControlleren viawww.goldenplanet.dk:

http://www.goldenplanet.dk/pclogin.html

Klik på "Admin"-ikonet ud for webhotellet for det problemramte domænenavn og rul ned til menupunktet "MySQL". Klik på dette og klik derefter på "Admin" helt ude til højre i skærmbilledet (kolonnen "Funktioner"). Et nyt vindue åbner med phpMyAdmin-værktøjet.

Klik på database-navnet i venstre side (hvis du kun har én databse vises den over listen med tabeller, har du flere databaser vælges den aktuelle database fra en rullemenu) så listen med tabeller vises i det store indholdsvindue.

Du kan nu vælge at sætte et "flueben" ud for den tabel som ifølge fejlmeddelelsen har problemer, eller du kan simpelthen rulle ned i bunden af skærmbilledet og klikke på "Afmærk alt" for at vælge alle tabeller. Klik derefter på "Med det markerede" og vælg "Reparér tabel" for at reparere tabellen eller tabellerne.

Der sker ikke noget ved at reparere en tabel som ikke har fejl (hvis du valgte at reparere alle tabeller).

Når processen er gennemført (afhængig af situationen kan det tage fra et par sekunder til nogle få minutter) kommer der et skærmbillede med en oversigt over resultatet, og her kan man konstatere om det var muligt at reparere alle tabellerne; successkriteriet er at der under " Msg_text" står "OK" ud for alle tabelnavnene. Problemet skulle i givet fald være løst - check at hjemmesiden fungerer igen.

For en god ordens skyld kan det anbefales at slutte af med en optimering af tabellern: En reparation sørger alene for at tabellernes index-filer kan læses, men en optimering rydder desuden op i oplysningerne, sorterer dem og er i det hele taget en god metode til at holde problemer fra døren med. Store tabeller kan desuden blive markant hurtigere at arbejde med, hvilket vil betyde en mere kvik hjemmeside.

Optimeringen foretages på samme måde som reparationen, men i stedet for "Reparér tabel" vælges "Optimér tabel" fra menuen "Med det markerede". Bemærk at det kan tage adskillige minutter at optimere en tabel med f.eks. 5 millioner poster, og i den tid vil din database (og dermed hjemmesiden) være utilgængelig for besøgende. Omvendt tager det et par sekunder at reparere 100 tabeller med 5-10.000 poster, så i de fleste tilfælde er det ikke noget problem.

MySQL: Efficient manipulation of large amounts of data

When the MySQL function is selected the first time you would properly be met by the following message:

Status: The MySQL function is not included in your present configuration, it is possible to upgrade to include this function.

You can upgrade to this function.

This means that you didn't choose to install a MySQL database when you ordered your web hotel, it is however possible to perform an upgrade. Click the link and go through the upgrade procedure.

If you have 1 MySQL database (but not using any of them) in your present configuration you will be met by the following message:

Status: There are no MySQL database for this domain

Would you like to add a MySQL database?

A click on "No" brings you back to the main menu, while a click on "Yes" makes the system create an administrative account on the database and assign a password to the account. The information on the newly created database will be shown on the screen - in the example below there are 4 databases installed, 1 for the phpBB, 1 for Typo3, 1 for osCommerce and finally 1 for the PHProjekt web application:

In the listing you can see the database name, administrative user name and password (cleared in the picture above) and what connection types are allowed. It is also possible to delete the database or go to the administration interface for the database (you can create tables, etc.).

Connect from:

Webserver (default):
As standard you can only connect to the database from the web server itself - this means that it is only possible to gain access via a PHP scrips that is on the web server, but this can be changed. You have 2 possible solutions:

Anywhere:
Users with the correct user name and password can connect to the database from absolutely anywhere, as long as you have a working internet connection. This setting can be dangerous, since user names and passwords are send unencrypted between you and the server. This makes it possible to sniff the password and thereby gain access to the database. And since everybody is allowed to connect from anywhere they can sit on the other side of the world and do some very bad things to the database.

Specific hosts:
Here you can put specific IP addresses that are allowed to access the database (example "195.41.114.1"). Notice, that this only opens for the POSSIBILITY to make a connection to the database, you still need the correct user name and password to get full access to the database. With this set all other IP's are denied access, so from a security point of view this is the best solution if you need access from outside the web server.

Please notice: If "Webserver (default)" is chosen again all entered IP addresses under "Specific hosts" are erased!

Options
A click on the "Admin" link starts up phpMyAdmin, a very popular web based administrations application for MySQL.

Howto use MySQL databases via ODBC

MySQL is an extremely efficient database system, and can be bought as an addition to your installation. Please contact support for further information regarding this or simply see the upgrade function in the Planet Controller.

It is possible to use a local database (example: Access-database) on your own computer as a "user interface" to a MySQL database that is located on the web server, but the setup requires some knowledge on ODBC and what possibilities your local database supports.

To connect 2 databases together, ODBC must be installed and configured on both your local machine and the server - we have already taken care of the server side, but you have to handle the client side setup.
Start by downloading the lasted version of the MyODBC for Windows at the MySQL site - there are different versions for Windows 9x and Windows NT/2000/XP, but the functionality and procedures are the same. Download "Driver Installer", not the "Source" or "Driver DLLs only".

Please notice that there are also a MyODBC for different Unix types, but the installation requires a fair bit of knowledge on Unix and ODBC - it is NOT recommended for novices.

Download MyODBC from here:
http://mysql.com/downloads/api-myodbc.html

Run the installation program by double clicking the file. Click "Next" a few times to complete the installation, after the installation has completed open up the ODBC Data sources control in the administrative control panel, click the "Add.." button.

a window will now appear, give the source a name (DSN) in the "Data Source Name" field and a description in the "Description" box.

The other important boxes are:

• Host/Server name (or IP): This is the name of the database server you are using on your web server, for example venus.dbservers.dk
  
• Database name: The name of the database, that we have created for you. This will often be related to your domain name, for example testdom_dk as in this case.
  
• User: Your user name for the database - this was supplied to you when the database was created.
  
• Password: The password for the database - same conditions as "User:".

Please notice that the user name, password and all other information can be found in the Planet Controller, you have to enable connections to all places or specifically to your internet connection's external IP.

Now click on "Test Data Source" to do a connection test, if there are any problems with the connection it will be revealed here. Click "OK" to complete the configuration and close the MyODBC Data Source window, then click the "OK" button to close the ODBC Data source manager.

Left is "only" to get your local database program to use the ODBC connection that we just created, in it self a simple task, but it can cause problems because of the way ODBC works. There are a few points that require some attention:

• You can not create a new database via ODBC on the remote server, only connect to an existing database.
  

• You can not open an existing database directly on the remote server - only link the tables in the MySQL database to an existing local database.
  

• You can not always change the design of an existing table on the remote server - only put data in it. You can however design in the local database and export it to the remote database. When the table is exported it can no longer be changed, this is not a problem on all database types, so test as you go a long.

In rough terms the procedure to get a connection to MySQL via ODBC is the following:

• Create a local database in Access, StarBase, Paradox, Approach or similar program. This is simply a template for your future database.
  

• Create your tables and get the design as close to complete as possible - since that when they are created they can no longer be altered, but you can delete tables and export a changed version, but you loose the data in the process.
  

• Export tables to your MySQL database (ODBC is right at the end of the list of export options) and close the local database.
  

• Create a new local database (this will be your working database) and link to the tables in the MySQL database. You can now manipulate the data in the tables, make request, generate reports, statistics, etc. from your local database program, and any change of data will instantly be replicated to the remote database.

This is just a quick introduction to ODBC and databases that is meant to help get the basic infrastructure into focus. It is required that you get further knowledge on the design and usage of databases to get the full benefit from such systems. A good start for those interested in SQL is the site below:

http://sql.org/

- especially look at "Online resources", there you will see a line of links to tutorials, manuals, etc.