[Solved] Looking at MySQL server from Base

[MSPhobe]

After MANY hours stuggle, I have a MySQL (ver 5.5) service running on my Windows 7 computer. On same computer: Open Office (3.4.1). Connecting Base to the server via the OpenOffice "native" MySQL connector (1.0.1) gives me something I find weird. Is it "normal" for working on MySQL databases from Base? Seems wrong to me.

In the usual Base main project manager window (i.e.http://sheepdogguides.com/fdb/parts/mainprjmgm.htm), if in the left hand pane I choose "Tables", in the lower right pane I see a hierarchical tree, with a node for every database on the MySQL server. If I open a node, then I see the tables in that database.

As I say... is this normal? And even if it is, is there no way to "uncomplicate" things? No (simple) way to connect to one database at a time. ("Database", as I mean it: A collection of tables, probably containing relationships, all about the same data. I.e., I might have one "database" about books I've read, another with the postal addresses of people I sometimes write. (Of course, in addition to the tables, somewhere there will be associated forms, etc, but that, I gather, is another story if I want to use Base to look at a MySQL database. No problem. (I hope!))

===
PS, 15 Mar 13: Comprehensive account of all I learned about MySQL servers with OO clients:
http://sheepdogguides.com/srv/s0MySqlDoInst.htm

[Arineckaig]

It all depends on the 'Privileges' granted to a 'User'. If you wish Base to show tables from only one database, it is necessary for the 'User' of that particular Base file, or front-end, to be granted access only to the relevant database. MySQL is designed as a multi-user RDMS so has a wide variety of 'privileges' that can be granted or denied to individual 'users'.

Typically for the "root" user (the database administrator) it is safe to allow all privileges and thus tables from all databases within the MySQL RDMS will be displayed in the Base table frame.

[MSPhobe]

Ah! That makes sense.... I think. (I have been trying to get to grips with MySQL, and have seen the extensive user rights configuration settngs.) If I've got it right, maybe the way forward, in my one user circumstances is to have two "users" defined... a super user for occasional use, when something "big" needs to be done, and a "minor user", shut out of most things, with access just to the databases I want to use regularly. Does what I've said make it sound like I've made sense of things?

[MSPhobe]

In the following, I use the term "database" to refer to, say, a collection of tables with information about books I've read, or (second "database") postal addresses of people I write to the old fashioned way.

===
I've been doing some more work. Now it "feels okay" that the main project window, WHEN CONNECTED TO AN EXTERNAL SERVER, would show "several databases". But I think I'm still missing something. If you can spot anything wrong in the following, I'd love to know where I'm confused.

And I've discovered, in the Base main menu: "Tools | Table Filter", which I hoped would allow me to (temporarily) "turn on" just one of the databases on the server.

Sadly, I still get all the databases showing, even if I've said via the filter that I only want, at this time, to work with one.

The other BIG problem, related, is this: When I use, again from the main Base menu, "Tools | Relationships", I get the usual window with a graphical representation of tables and relationships between fields. However, I see relationships in a database I haven't selected. ("sakila")... and only that database. Maybe there aren't any in the other databases which come with the system? But even if so, I don't want the clutter of "sakila"'s relationships when I'm not working on sakila! I'm also a bit worried about where any new relationships are stored... are they in the same folder as the data of the database they refer to??

PS... Ah! An "answer"... of sorts. The table filter does what I had hoped... sort of... if I...

a) Make a selection of the database (folder) I want to work with.
b) Save the "database"... i.e. the Base .odb file
c) Close Base. (But there doesn't seem to be a need to close the server down.)
d) Re-open Base.

NOW when, say, I invoke Tools | Relationships, I only see what I expect to.

A pretty clumsey and easily-got-wrong workaround! Is there something I could do differently to streamline the process??

I'm also worried about WHERE relationships I've defined are stored... I don't see any likely files in the database's folder, and I do see "ibdata1" changing... but it is a file in the root of the data path. I hope it doesn't hold things from multiple databases??

[Arineckaig]

I can comment only in general terms. There is a risk you are pushing Base beyond its design remit. Base is not an RDMS but merely a bridge to permit other elements of AOO to commuicate with a variety of data sources. By contrast MySQL is designed to be a multi-user RDMS located on a server, where the server can even be located on the same machine as a client front end. Unlike MS Access or the supplied embedded HSQL in Base, the MySQL RDMS is quite distinct from Base: hence if working with two computers it is, IMHO, far simpler to leave MySQL located as a single server that each computer can communicate with than it is to copy MySQL to each computer hoping to synchronize the various copies of the multiple 'server' programs.

Essentially Base communicates with an RDMS using SQL. The Base GUI tries to simplify/hide the underlying SQL but, again IMHO, I suggest one should not ask too much of Base when working with MySQL. For many administrative tasks it is easier and safer to use the MySQL utilities rather than rely on the essentially primitive Base GUI. On the other hand the Base GUI handles straightforward tasks of getting information in and out of MySQL quite satisfactorily.

[r4zoli]

For AOO 3.4.1 use 1.1.0 connector: http://code.google.com/a/apache-extras. ... o-my-sdbc/

[rudolfo]

I'm also worried about WHERE relationships I've defined are stored... I don't see any likely files in the database's folder, and I do see "ibdata1" changing... but it is a file in the root of the data path. I hope it doesn't hold things from multiple databases??

ibdata1 is the main file for the innoDB engine of MySQL. It is the one that supports foreign keys ... in contrary to the MyISAM engine. innoDB replaced MyISAM as a default with MySQL Version 5.1 or 5.5 (not really sure about this, but google should have precise answers on it). If you create relationships in Base they will be stored as foreign keys in the database backend, in this case in the innoDB engine and hence in the file ibdata1. And yes this file holds every table (including its relationships) that is using the innoDB engine. If you use MyISAM you have a better separation of databases: Each database has its own directory and in this directory each table consists of a set of 3 files. Typically you should find a directory mysql which is the database for the administrative data (user permissions, etc.) of mysql. It would be very weird if they changed that administrative database to use innoDB, as well. In this case you won't find the mysql directory. On Linux only a superuser can enter this directory and view its files. This is on purpose. In might be something similar on MS Windows ... but I doubt this.

[MSPhobe]

Well...

MANY hours later, I think I am content to start down the road of doing at least one of my database jobs with MySQL as the back end, OpenOffice for the front end, at least for day to day.

It has been a nightmare. I finally got "everything" working on a Windows 7 machine, and then set about "simply" re-doing all of the MySQL set-up again on a separate machine... an XP machine... and looking at whether I'd be able to migrate.

Not easily. My rather crude... but not a lot to go wrong... "answer", at least for now, is to use MySQLDump to dump the database out of one maching into a file. I then "pour" that into the second machine with a command line session. Oh. And then copy across, as a separate exercise, the OO front end.

Not pretty, but I think it works. It would have helped if the admin tool "MySQLWorkbench" had worked in both environments. On the XP machine... ironically on MySQL5.6 when the Win7 machine is only 5.5... I use MySQLAdministrator to manage users, and the crude MySQL command line client for other things.

My hopes and dreams of "simple" separation of the data in separate databases was not to be. No doubt the complications make fancy things possible... but I didn't want fancy, just robust. But learning where to find things, how to adapt my plans to fit how the software does things.

I WILL master all of this one day!...

THANK YOU for all those whose help and encouragement was a large part of how I got this far.

[rudolfo]

The commandline tool mysqldump is the first choice for copying databases. Although it doesn't feel like that. If you copy the files in the mysql data directory and its subfolder, you should really make sure that the database service is stopped during this time.
mysqldump has a lot of options, you will find this out while doing what's suitable for you. In most cases you will only need copy the data, new records in the tables and also updated records. But it is not easy to keep track of this, so that you can do an incremental copy. In most cases you will simply delete everything from a table and then fill it again completely with the newer data from the other database.
The DROP TABLE commands that mysqldump includes by default are not needed.
Auto-incrementing columns are a big problem during such replication work. Well, they are only a big problem if you rely on their numerical value (which you should never do, you don't need to know the value of autoincrement columns, you only need to know that they are unique, that's their main and only purpose). Just play around a bit with the files that dump produces with the different options. Use a good diff tool to see where the options take effect.

[MSPhobe]

As a newbie (to MySQL), I eventually settled on using the MySQL Command Line client as much as possible. For one thing, Murphy's Law was alive and well... on one of the two machines I use one GUI works, and on the other the other works. Neither works on both. (There's the old-and-being-retired MySQL Admin... not the most Google-friendly name! And the latest-greatest-but-won't work for me on XP (which it doesn't promise to support) MySQL Workbench) Both are "wonderful"... if you can get them under control. Powerful.

And, not only is the MySQL Command Line client great for many things, but there are sundry things I, as a newbie, prefer to do with good old DOS commands... i.e. using the WIndows "Command Prompt"... It looks very like the MySQL Command Line client... white text on black, not GUI. (If you do "dir", you get a list of files in the current directory.)

Becoming clear as two which command window you use for different tasks is a neat little bit of learning curve.

MySQLdump is a great tool... you use it from the Windows Command Prompt.

A somewhat blunt instrument... but it turns your whole database into a file you can read with a text editor, and that file contains everything you need to "fill" and empty database with what you had on the machine from which you are migrating. It also gives you a human readable backup of everything there is to know about the database.

[rudolfo]

Thanks MSPhobe for sharing this. Others might find this helpful when they come across this thread in two or three months. The console in MS Windows is often estimated as inferior compared to Unix shells. But at least for some annoyances there is a work around.
A very useful improvement for me was to start the console with:
cmd.exe /k "chcp 1252 && set LANG=de_DE"
Together with a True-Type font like "Consolas" or "Lucida Console" you will see the Umlauts and maybe even the Euro sign in the correct character encoding.

[Arineckaig]

MsPhobe:

And the latest-greatest-but-won't work for me on XP (which it doesn't promise to support) MySQL Workbench)

FWIW, I have yet to find problems using MySQL Workbench with Windows XP: quite the contrary, it is a powerful administrative utility that needs to be used with care to avoid inadvertent damage to the database.