Feature Request - Database relocation

[PowrrrPlay]

Ok, I will drop the postgresql thing.  The rhetoric is getting out of control and not what this post is about, so lets just agree to disagree.

So, the original question, database relocation.  Is that a viable request?  I want database relocation/separation from the core OS.

[royw]

I did a google on "moving mysql database" and found what looks like good instructions:

  http://www.vbulletin.com/forum/showthread.php?t=185701

I'd suggest giving it a try and if it works, create a lmce specific howto on the wiki...

HTH,
Roy

[PowrrrPlay]

tschak909, I am laughing because the admin or somebody with authority is removing my posts.  Nice community.

[hari]

tschak909, I am laughing because the admin or somebody with authority is removing my posts.  Nice community.

talked to the other devs.. nobody deleted your post. Maybe you hit the delete link by mistake?

If you don't like our community, nobody forces you to take part.

best regards,
Hari

[RichardP]

tschak909, I am laughing because the admin or somebody with authority is removing my posts.  Nice community.

Could it be that you're not using the Post button after you Preview?

[Zaerc]

tschak909, I am laughing because the admin or somebody with authority is removing my posts.  Nice community.

That would very much surprise me as well as I've never seen a post or thread just disappear before, and trust me I have said some things before that were pretty damn rude (and in all honesty: not always called for).  At most threads get deleted or locked by the person who started the thread.  Never saw a single complaint about missing posts before either.  So I also think you are mistaken, no offence intended. 

[PowrrrPlay]

RichardP/Zaerc;

Was not sure what was happening after a post disappeared and then all the flaming started. Thank you for the professional response. My other comments come from years of experience and I was just trying to convey my concerns for such a young aggressive project.

Royw:  Good information, thank you.  I will be spending more time dissecting MCE and its components.  Just trying to avoid some "got-yah's".

Cheers.

[RichardP]

In order for the database to be relocatable, there needs to be, in LMCE, a single configuration file which points to the DB location, and from which all other scripts get the DB location when then need to access the DB. Does anyone know if this is how it is done?
If any of the scripts hold the DB location within them, then this will be a larger task than simply moving the database.

[royw]

In order for the database to be relocatable, there needs to be, in LMCE, a single configuration file which points to the DB location, and from which all other scripts get the DB location when then need to access the DB. Does anyone know if this is how it is done?
If any of the scripts hold the DB location within them, then this will be a larger task than simply moving the database.

First, I'm not a db kind of guy, but I've been forced (kicking and screaming) to use them occasionally.   

LMCE is using mysql.  Mysql runs as a daemon.  When mysql daemon is started, it reads it's configuration from /etc/mysql/my.cnf.  On LMCE 0710B3, my.cnf has datadir = /var/lib/mysql.  Looking in /var/lib/mysql you will notice directories that correspond to the various databases.

Scripts, programs, etc, should not be overriding my.cnf (that's a no no).  Also they should never access the files directly (that's a big NO NO).

During my, admittedly limited, browsing of the source, I've seen just the expected mysql connection calls.

When you use a database, you open a connection, do your queries, then close the connection.  When you open the connection, you actually query the daemon for a handle.  This handle is then passed to the daemon with each of your db calls (queries).  So you never know (nor need to know) the physical location of the data on disc.  So moving the location of the database should be transparent to all applications.

The primary concern I would have is LMCE's bad habit of "restoring" config files.  I have not found any list of the files that LMCE expects you not to mess with.  I know from experience that LMCE "restores" domainname (to "pluto") and from the forum it apparently does not play nice with xorg.conf.  I would suggest doing an innocent change to my.cnf, rebooting, then see if my.cnf was "restored" or not...

If you are really concerned about messing something up by moving the database, then why not just mount your raid partition on /var/lib/mysql?  The only config change would be adding the mount to /etc/fstab.  Simply create your partition, format it, mount it somewhere temporary, stop mysql daemon, copy /var/lib/mysql/* to it (preserving permissions, ownership,...), then umount it and remount it on /var/lib/mysql, and restart the mysql daemon.

HTH,
Roy


Have fun,
Roy

[RichardP]

LMCE is using mysql.  Mysql runs as a daemon.  When mysql daemon is started, it reads it's configuration from /etc/mysql/my.cnf.  On LMCE 0710B3, my.cnf has datadir = /var/lib/mysql.  Looking in /var/lib/mysql you will notice directories that correspond to the various databases.

Scripts, programs, etc, should not be overriding my.cnf (that's a no no).  Also they should never access the files directly (that's a big NO NO).

During my, admittedly limited, browsing of the source, I've seen just the expected mysql connection calls.

I was thinking of a different problem. When you use a DB from a script, you open a connection to a DB to access tables, stored procedures and the like. You open a connection by specifying the server (like 192.168.80.1 or localhost or dcerouter.com) and a DB name.

Lets say LMCE opens a connection to a the DB from 50 different scripts. The three possibilites are:

• All 50 scripts get the server and DB from a central point
• All 50 scripts each specify the server and DB
• Some scripts do it one way and some scripts do it another

Can you see the problem we will face if LMCE does not already follow the first option? Moving the DB will have a lot of scripts breaking if they assume the Servername and DB Name

Best Regards,
Richard.

[royw]

Ah, but you are moving the database location on the core, we are not talking about moving the daemon (server).  The servername for all the scripts and programs is the core, whether it is referenced as 127.0.0.1, core_ip_address (ex, 192.168.80.1), localhost, or dcerouter.  All the scripts and programs then talk to the daemon running on the core.  The daemon then handles the physical location stuff behind the scenes.

In other words the database daemon IS the central point, i.e., your first option.

Also what we've been talking about is moving the location of ALL the databases (/etc/mysql/my.cnf:datadir).  So again, not an issue to the clients, just the daemon.

HTH,
Roy

[colinjones]

Yes, would only be an issue if you wanted to move the MySQL off to a completely different computer. Otherwise the server/db names/tables/ports etc all stay the same...

[Zaerc]

Nobody found /etv/pluto.conf yet?

# Pluto config file
MySqlHost = localhost
MySqlUser = root
MySqlPassword =
MySqlDBName = pluto_main
MySqlPort = 3306
...

Oh darn, now everybody knows I don't have a root password on my database.

[Matthew]

Ok, I will drop the postgresql thing.  The rhetoric is getting out of control and not what this post is about, so lets just agree to disagree.

There are other good reasons for running LMCE on Postgres rather than MySQL. One good reason is if the site already runs Postgres, and adding LMCE's database to it would allow integrating other apps that aren't part of LMCE right at the data tier, with joins etc.

One technique could be to use DB-Link/DBI-Link to let a MySQL DB (local or networked) just front for a Postgres DB that actually contains the data.

But the SQL code in LMCE that I've seen doesn't expoit any specific MySQL features. A developer could just switch the /etc/pluto.conf and DSN strings to point directly at a Postgres DB with the same schema as the MySQL one, then regression test to be sure everything still works.

[hari]

But the SQL code in LMCE that I've seen doesn't expoit any specific MySQL features. A developer could just switch the /etc/pluto.conf and DSN strings to point directly at a Postgres DB with the same schema as the MySQL one, then regression test to be sure everything still works.

sqlCVS does.. And some other pieces use e.g. "show tables"..

best regards,
Hari