[Solved] MySQL as backend behind Base?

[MSPhobe]

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

===
The rest of this is my original post in this thread, whittled down in the light of lessons learned.

===
I've used Base for years, but simply using the embedded database engine, apart from some "playing" years ago with MySQL, both on its own, and in conjuction with Base.

(By the way: There is a "new" "native" connector/ extension...
http://extensions.services.openoffice.o ... _connector
... which makes some material in some old posts about using MySQL via OpenOffice no longer relevant/ true/ useful)

For a variety of reasons, I am restless.

I want to have my data in a MySQL database, and access it using Base as a front end. ("Wanted" when I first wrote the post. I can do that now.)

First simplification: I do all my work in Windows, on a mix of XP and Win7 boxes. (Though part of the attraction of Base/ MySQL is that someday I may throw off my shackles and move to Linux!! (With Base/MySQL, I am building "Linux" skills as I progress with Windows Base/MySQL.))

Second simplification: For now, I only want one person using the database at any one time. I only want it active on one machine. (I eventually learned that the database can reside (in MySQL) on a server, and be accessed remotely, i.e. across LAN or WAN, with database in one machine, user (interacting with Base) on another. Multi-user access is also possible. Making users restricted as read-only avoids some hassles, but isn't "required".)

I "migrate" between machines from time to time. I had hoped that could be done as follows...
a) Save files, shut things down
b) Using ordinary OS file tools, make copies of....
i) Something from Base... .odb file?.. which would be what Base needs to have in order to
connect to....
ii) A folder full of MySQL "stuff"... being my data, forms, tables, etc.

But (March 13 "hindsight" edit) it turns out that a different... but workable... solution is necessary. I use MySQL tools to export the data from the machine I am leaving, and then import the data into the machine I am moving to.

c) (Go to other machine, transfer the files to the relevant locations, putting "fresh" copies, only, in place of anything from previous sessions, and then Just Use It. (I can dream.))

===
When I say "use the database", the essential thing is to be able to add records to tables, run queries & reports, look at things via forms. I'd hope to be able to create new tables, forms, etc, and edit existing ones, from within Base, as I do such things now. (March 13: Yes. Can do.)

==================
So! Any thoughts on whether I am starting down a nightmare which will end in a dead end/ cul de sac would be welcome. Guidance on how I should be thinking about this project welcome. So often, "the answer" isn't what a newbie needs... it is being told what questions to ask!

Thanks for what you can offer.

===
Oops... PS... One of those questions, of course, is "Is MySQL the way to go?" I lean towards it as it seems to have some "momentum". My webhost lets me have MySQL databases on his servers, for instance (Projects for another time!) But I read something about MySQL not enforcing foreign keys. Referential integrity is important to me... I often have the primary key of one table restricted to values found in a field of another table, and love Base's ability... when using native engine... to cascade updates and deletions. Is this going to be a problem if I put the database in MySQL? Will I still be able to view and manage the links between tables as I have of old?

(March 13: The only other suggested "solution" was to move to a split-database configuration, and that idea didn't get a lot of support. MySQL seems to own the opensource, multi-platform database server field.)

[rudolfo]

Hm, that's rather a lot of things that you mention in your thread. I am not even sure if I can break down them in an appropriate way. Anyways, I start with the basic statement. MySQL is a database server, while Base is a database client/frontend to databases (if you are looking only on this area there is no conceptual difference from a Java Swing application with JDBC or a PHP application running in the webserver) except that Base has the additional features to allow you to integrate the database structures into your office documents (Calc, Writer, ...)
While I said that Base is a frontend it is surely not a frontend for databases adminstration tasks. To create additional tables you'd better not rely on Base. Instead their are several MySQL Products that do a better job in that, and even the mysql command line tool mysql does a better job in terms of dataabase administration than Base.

If you have all you data on one Mysql server you have to think only about backups of the database content, but not about replicating data. All the data will be at one place at the mysql sever. No matter if you connect from Windows or Linux, you will see the samedata.

[MSPhobe]

Very helpful! Thank you...

Yes! I can live with altering the schema via purpose built MySQL tools.

No... I odn't want only ever to have the database on one server. I'd rather work locally, i.e. not across the net, and I have two places of work. Happily, the jobs I have in mind would only have to "migrate" from time to time, not frequently.

Have had a horrendous day just trying to get a MySQL service running in my Windows 7 machine... but think I am there now.

Other thoughts welcome. Yes... sorry, original post was long, many things that might be addressed in it... but it is the state of my newbie (to this "solution" to need-for-database) Still looking for guidance from "the experts"... (grin)

[FJCC]

An additional comment - You mention having a folder full of your stuff: tables, data and forms. It is important to understand that the tables with their data will be part of the MySQL server but the forms will be in the Base document as will any queries created in Base. You can save queries on the MySQL side as Views.

[rudolfo]

No... I don't want only ever to have the database on one server. I'd rather work locally, i.e. not across the net, and I have two places of work. Happily, the jobs I have in mind would only have to "migrate" from time to time, not frequently.

Although I remember that MySQL had replication tools since version 3.23 to keep several databases in sync, I am pretty sure that MySQL is not the right tool for you. It's a database server, with a detailed access control system several options to connect, user management, etc. If you don't want to work with a server, but instead prefer to have an independent database on both of your systems you should rather use a file based database.

I would recommend SQLite if I wouldn't know that it doesn't work well with Forms and Subforms. The advantage of file based databases is that all data is in one file. Simply copy the file from one computer to the other (ideally you do this with rsync or unison and not manually). One file makes serialization for parallel access quite difficult. That's easier to implement in a server based architecture. But what I have read so far you don't seem to use your database for parallel access.

[MSPhobe]

Simply copy the file from one computer to the other (ideally you do this with rsync or unison and not manually).

Hmmm.

While my prime requirement is as stated, the chance that I will be able to go further if I meet that has me hoping that MySQL... or something else... will suit my immediate needs.

I don't need to have a "24 x 7" system. So when the time comes to migrate, I can shut the system down, to simplify things. Copying multiple files holds no terror... as long as the database program is "open" about what it does where... and preferably keeps all of the files of a given data set in a single folder.

I believe in open source software... not just because I am parsimonious... although it contributes!... but I've had years of "playing" with obscure things that came and went. Hence, I'd lean towards something big, something with "critical mass", something with a good community of users.

If I can get this working for my immediate needs, it would be nice if further work and study I go go further with the same product. I would love to be able to deploy a database which could be accessed by multiple users across a LAN, or maybe even across the internet. The fact(?) that many web hosting services offer MySQL servers as part of their packages is one of the things that had me leaning towards MySQL. Oracle's backing is another big plus. And it's inclusion on WAMP/ LAMP packages. But I'm not fixated on that option. Are there other packages frequently offered?

Crude... but maybe useful... I went off to trends.google.com. Depressing to see the steady decline in interest in MySQL! Years ago, during similar discussion, PostgreSQL was suggested as a candidate, but, again from trends.google, I learn that it has but a fraction of the interest MySQL has, and the same pattern of declining interest. Hmmm... after about 90 minutes happy browsing, mostly Wikipedia driven, it seems that MySQL is head and shoulders ahead of the others in terms of momentum, community, etc....

So. Can I master using it with OpenOffice...?

[Arineckaig]

You may find some relevant, hopefully helpful, and general comments at:
http://forum.openoffice.org/en/forum/vi ... 46#p262646
In my experience MySQL works reliably and well with Base PROVIDED the limitations of the latter are recognised and accepted.

[rudolfo]

If you were running a Linux system I would happily recommend you to work with MySQL. That's because the match together very well. But under MS Windows you have to find a method for log rotating, you have to rethink the filesystem permissions and several other things. It is only since version 5 that MySQL is binary compatible between the different OSs -- meaning that you can exchange database files typically located in /var/lib/mysql under Linux and (yes, where are they under MS Windows ... especially if you run a 64 bit Windows, while MySQL is only 32bit, most probably somewhere below "C:\Program Files (x86)"?). This works with MyISAM database engine files, but probably not with InnoDB files.

But still MySQL is together with PostgreSQL and after HDBSQL one of the databases that works best with OOo Base. Oracle with JDBC is a nightmare, because it can't handle numeric values and always converts them to currencies. The SQLite ODBC driver fails with some special inner join conditions which you need when working with master detail relations.

[MSPhobe]

MANY THANKS to all of you for the very helpful guidance.

After several long days of bitter struggle, I think I'm far enough along that I can start re-building the database that didn't seem "happy" in pure (embedded engine) Open Office Base.

I have been very impressed by the great wealth of information out there about MySQL. I think I have been lucky that I didn't start this task sooner... I get the impression that version 5 of MySQL was a leap forward, a break with a less well crafted past. Whew.

It has been an "interesting" few days, with an awful lot of new bits all to be mastered at once. I will try to create a tutorial to help the next newbie... but at the moment, I am somewhat cross-eyed from getting to the stage I've finally reached.

[MSPhobe]

After many MORE hours working on this, and learning a lot more about MySQL than ever I knew before, I think I have a way forward.

A few points you need to be aware of, if you want to try similar...

It really makes a lot of sense to have "twin" users on the two machines... two users with the same names and access priviledges.

It makes sense to think of the copies on BOTH machines as "scratch", and a copy of the database... more on that in a moment... on a thumbdrive as the "prime", "definative" copy. But you update that at the time of each migration with the new state the scratch copy has been advanced to.

"The database" is in two parts. "Within" the MySQL server is to be found your tables and relationships. "Within" the .odb document are your forms for access to the tables. Also within the .odb is the specification of how you connect to the MySQL server. As long as the advice about twin users has been followed, it doesn't become an issue that the two servers are different in many ways. They will each contain a copy of the database you are migrating, and that's what matters.

I know of no simple way to "copy the files" holding the contents of the tables, the table definitions, etc. The Way To Go, for me, seems to be to use MySQLdump... a program you run from a OS command line interface... NOT from within a MySQL command line client session. Getting straight on when you do one, when the other is... "interesting". Four batch files make it all easy... two on machine "A", two on machine "B". A "dump the data" and a "refill the database from dump" batch on each.

Final advice: Persevere with learning to use the simple MySQL command line client. It will do many things quickly, and almost easily, once you know how. One of my two machines was XP, the other Windows 7. The MySQL GUI interfaces were problematic... I had to use MySQL Administrator... not easy to find.. on one, MySQLWorkbench on the other. Oh joy... more things to master. Two tools to do one set of jobs. Sigh.