[Solved] Connecting Base to mySQL

[however]

Hello everyone,
i believe I am at the point where I now need to connect my finished DB, with all the necessary forms and tables, to mySQL and I was browsing around to find out tips and advice.
I have read in this forum that I would need a mySQL-connector to do such task, however I cannot find any more details suggesting whether I actually need to create a database with mySQL or not.
So in short my questions are:
1) Will I need to create a database with mySQL with the exact same tables I have on Base?
2) do i need also to do something about the forms? os that is something handled by Base alone?
3) is there any tutorial out there that can lighten my way to completing my clinic database?
4) I might still need to create a few reports and queries; will i still be able to do so once I connect to mySQL? or should I completely FINISH it with Base before worrying about mySQL?
As always, many thanks for any inputs.
Regards,

[rudolfo]

I just want to remind you again that Base is not a database, but a database frontend.
While this is often not easy to understand for novices who just want to run a desktop database in the same way as they would keep their data in a spreadsheet where the User-Interface and the data content are the same, the beauty of the architecture starts to shine when you want to migrate to another database engine. You can keep your forms, queries and reports (all 3 are frontend objects) and you just have to change the backend.
But again, Base is only a database frontend it is not a database administration tool. Even the mysql command line terminal that comes with every MySQL installation on Linux is better than Base (well, personally I think nothing can beat the mysql terminal, except for maybe running the mysql terminal inside GNU emacs in interactive sql-mode). In other words, you'd better grab some mysql admin tool to create the tables. Maybe you export the HSQLDB database structure with SCRIPT (search for this phrase in this forum) and then use your favorite text editor to adjust the types of your columns (autoincrement and tinyint and some other might cause trouble, it's good to have the HSQLDB and MySQL manual at your hands for this) and feed the modified text file with SQL commands (without those that inserts data) to the mysql terminal (after you have created a database with CREATE DATABASE your_db_name). You will probably haver errors in this step. Investigate them and make the appropriate changes to the SQL statements in the text file and run it again.

To make your Base Forms access the new MySQL backend you need a driver for MySQL. That's either the native MySQL connector from SUN/Oracle which requires a MySQL server version of 5.1 or above, the ODBC driver or the JDBC driver. Once one of the drivers is working you can change the underlying connection in the .odb Base file when you go the menu Edit -> Database -> Properties. But it is a good idea to create a new .odb file (don't need any forms or reports in it), just to see what you have to enter as database name, port, user, etc. to succesfully connect.

With some drivers you have to use schema names together with the tables to address the tables correctly: my_db_name.my_table. This might be a thing of the past, at least I have seen it for one of the earlier 3.x versions of OOo. But this is only something you might attack and check if your forms are not working as they supposed to.

Just make your attempts and collect some experience and come back later with detailed questions. But I am also sure that I have seen a tutorial on how to install Mysql and connect OOo to it. Just do a search on the forum, there is plenty of information on Mysql.

[however]

thank you very much for your elegant explanation.
I am already on the hunt! I will keep you updated on my progress/regress
Regards

[rudolfo]

Hm, I see the thread is already marked as Solved. Well, never mind, for the sake of completeness I have found the thread about the MySQL installation and configuration that I had in mind: HowTo: Move from Embedded to MySQL Backend Just in case you haven't come across it.

[however]

HI All again,

firstly, I apologies to the moderator for opening a second topic with the same title of another topic that I opened in the past, but since I dismissively marked the first topic as solved, I was afraid that no one would bother reading it of giving advise.

I have been trying to connect Base to mysq 5.1.56. (my OS is Slackware 13.37).

After several packages installed I managed to get mysql and mysql-workbench running on my box.

Now, I don't even remember how many failures I have had but during all this tries something weird happened: before I could start mysql from the command line with: mysql root -u user -p
then something happened (obviously I did something wrong) and mysql didn't start anymore with the same command

After hours of search I found this other command from the console: mysql root -h localhost -p
and by magic, mysql asked my password and then started again

Could someone tell me what happened and how?

Nevertheless, despite being able again to start the mysql server from the command line, the main problem is that I cannot connect Base to it.

I have installed the mysql-connector extension in Base.

What I have done:
1)'Connect to an existing database
2) choose 'MySql from the drop down list
3) connect directly
4) Database Name (clinic.odb, creatd previously with OOBase)
- Server / Port: ticked
Server: localhost
Port: 3306
5) User Name: root
Password Required
6) test connection
- 'Can't connect to MySQL server on 'localhost' (111)

After, another coupe of hours of browsing..., I have checked my.cnf file and 'skip-networking' is commented out and I have no 'bind-address' line.

So, assuming that a GUIi interface admin tool for mysql would have solved the problem I downloaded and installed mysql-workbench. Again after many tries&errors I managed to start it:
1) I have created a Server Instance (mysql@localhost, appears in the Sever Administration section of workbench main window)
2) I have created a New Connection (localhost, appears in the Open Connection to Start Querying section of the main window)
3) I have also managed to create an EER Model (ClinicDB, appears in the Open Existing EER Model section of the main window)
4) I have recreated all the tables that I had in my ClinicDB created with OOBase.

But after that I got stuck because every time I try to stop the server I get this error message: 'A permission error occurred while attempting to stop the server.
Administrator password was possibly wrong.'

Now, that was the same password that let me start the mysql server before, so how could possibly be wrong?

Obviously, something is wrong somewhere and therefore before continuing the wrong way, I decided to come here and beg for help.
This, surely, must be simpler than that!
Probably, after all this trying I must have swapped, created, flushed and dropped several databases and passwords that I am not sure why the same password works in some cases and not in other.
Getting really frustrated!

Any light anywhere?

[rudolfo]

It seems like you got a bit confused by the client and server parts of mysql.
In general mysql is a daemon on a UNIX system in the same way as you mail server is a daemon. This means the server (daemon) is started by a initV script when your computer is booted. You can easily check this if you ask the OS for a list of running commands and filter these commands for those that contain the word mysql (a simple unprivileged user is good enough for this):

Code: Select all

ps aux | grep mysql
USER ....  Start  time cmd
root ....  May07  0:00 /bin/sh /usr/bin/mysqld_safe
mysql ...  May07  0:48 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql \
                        --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

mysqld_safe is (a script) executed by root and drops its superuser privieleges somewhere to work only with the privileges of the user mysql. This non-prvileged user is used to start the actual daemon process mysqld (with a 'd' at the end for daemon!). The important point here is, that although this is the very core of your database you don't need a password to start it (that's the same for your mail server).
You only need a password if you want to connect to your server. This means each client must provide a password. If you want to send emails through your (or someone elses) mail server you have to authenticate. And if you want to connect to the database with the client mysql (no d at the end!) you need to authenticate, as well. The mysql client is clever enough to choose the best possible connection path to the server. First it will look for the UNIX socket (/var/run/mysqld/mysqld.socket on my Debian box as you see above), if this fails it will try to establish a TCP connection to port 3306 on the same computer. Only if you have specified the command line parameter -h it will directly try to connect to the host(name) or IP address that follows the -h.

To do something meaningful with your MySQL database you need to create a new database instance. This is initially done by the database superuser root (just happens to have the same name as the UNIX superuser):

Code: Select all

$ mysql -p -u root -h localhost
mysql> CREATE DATABASE my_db_name;
mysql> CREATE USER your_name identified by 'the-password';
mysql> GRANT ALL ON my_db_name.* TO your_name;

Also a user with all rights on this database is created. So this new user your_name will be used from now on to create tables, views, etc. or in short to connect to the database and work with the database.

And again the .odb file is not the database. It is the file where the instructions on how to connect to the database are stored, but it is not the database! No matter which driver is asking you for a database name, it is never the .odb file. It is always the name that you have given when you executed CREATE DATABASE.

[however]

Hi Rudolfo,
thanks again for your refined explanation.

I know that one would need years of academic learning to master these skill, and it really seems it to be quite a complex topic. Nevertheless, I managed to get get 'connected' to mysql through the command line with 'mysql -p -u root -h localhost'

However, I decided that it would be easier to create a DB instance using a GUI interface such as 'mysql-workbench'

I have now created a DB instance called 'mysqld@localhost' (which appears under the Server Administration box of the mysql-workbench window).
I also seem to have automatically created a new connection to the DB called 'localhost' (as it appears under the Open Connection to Start Querying box of the home window).

As far as I understand, everything is as it should be.

Now the question is:
1) do i need to re-create all the tables manually through mysql-workbench? or is there a way to import the file/data called /home/ClinicDB.odb? (either way, i don't mind to recreate all tables),
* (I read the link you suggested, but it seems hard core and even disapproved by some of the forum 'superuser's

I have found the thread about the MySQL installation and configuration that I had in mind: HowTo: Move from Embedded to MySQL Backend Just in case you haven't come across it.

2) Once it's done, I still have troubles connecting Base to mysql, and this is what I have tried so far:
- downloaded and installed the MySQL Connector 1.0.1. extension for OOBase
- open OpenOffice -> New Database -> Connect to an Existing Database -> Choose MySQL from the drop down list -> (Next Page) Connect Direclty -> (Next Page) Database Name: not sure what to put in here but I have tried with mysqld, CLinicDB, /home/ClinicDB.odb all with same result - Ticked Server/Port: Server: localhost / Port: 3306 -> (Next Page) User Name: Root - Requires Password is ticked -> Test Connection: Requires Password=mypassword -> Can't connect to MySQL server 'localhost' (111).

It seems that no matter what I input on the line 'Server' it will still return with the same error message.

What am i missing?

Regards,

[Villeroy]

I know that one would need years of academic learning to master these skill, and it really seems it to be quite a complex topic.

[Amazon] SQL for Dummies
One or two weeks of intensive learning and excercising to master the fundamental SQL commands for 95% of all database types.
Create appropriate tables, relations and indices.
Insert, edit and delete table data.
Retrieve information from the database by means of queries.

Once you've got this, you can utilize frontend tools more easily because you understand what these tools expect from you before the tools can do what you want from them and Base will look like a very cheap, overly simplistic, nevertheless useful plastic toy.

[Arineckaig]

Being a mean Scot, I got started with Sams Teach Yourself MySQL in 10 Minutes: cheaper, more specific and less irritating than the 'Dummies' series. Do not be fooled by the title: it takes at least 10 minutes to read each chapter and there are 25 of them plus two appendices.

[however]

OK folks,
I have been reading MySQL section in various books and the one that I am using the most is: Wiley-MySQL Administrator Bible(2009).

I am now convinced that the problem is not mysql, password or anything else a part from OpenOffice Base. I must be missing something to be done in OOBase.

I have created a DB using mysql-workbench; tested both form workbench GUI and the command line: the DB that i created (ClinicDB) is there, responding always to the same password.

Nevertheless, when I tr to connect OOBase to mysql i get the same error over-and-over,
SQL Status: HY000
Error code: 2003

Can't connect to MySQL server on 'localhost' (111)).

Initially, I thought I was confusing the correct DB name and/or using the wrong user name, however I have tried all the combination possible and still the same.

I am wondering if beside the MySQL Connector I need some extra packages/extension to be added to OpenOffice in order to get it connected to mysql. According to MySQL Connector 1.0.1 there should be no need to download any further drivers such as ODBC or JDBC.

Anyone has come to this problem? and if so, how do I fix it?
Do i need to enable any of the connection pool in >Tools -> Options ->OpenOffice.org Base -> Connections?

Many thanks for any inputs.

Regards,

[however]

Hello everyone,

I have GREAT news! I solved the problem!

After days of tries I got suspicious about the hostname of my pc and after a cpl of hours of search I found out that the hostname of my slackware box wasn't localhost. I changed it through the command line with root privileges using: hostname localhost.

And da da! OpenOffice is now connecting to my ClinicDB sitting on the MySQL server.

I would like to thank you everyone for their inputs and suggestions which obviously have helped me to search around further, and I hope that this solution can help others who experience the same problem.

Ciao