each morning I need to dump and replace database data with the new stuff. There are between 50k and 50 milion records in the main table, and few hunderes to few thousand in the additional supporting l tables. The data is in cvs format before it is inserted and it takes a while to error check it and process it. I woud like to know your experience with similar tasks and mysql.

In postrgess I would do "BEGIN WORK" and then empty all tables, and then do inserts and then do "END WORK" , and rollback would be available if something goes wrong. I am looking at mysql LOCK tables stuff, but it does not seem like it provides similar functionality.

Comments? Suggestions?

I don't believe MySQL supports transactions at all in STABLE releases.

The 4.0 beta versions are supposed to implement transactions. Whether they have them in now or not, I am not sure.

OK, so what would be the workaround. I am thinking now that maybe I could create additional set of temporary tables and when update populates tables they just get renamed to the original tables' names. This way I would avoid any downtime.

I stand corrected. In looking at MySQL's site (www.mysql.com) I see that 4.0 is considered production/stable.

You can read more here: http://www.mysql.com/downloads/mysql-4.0.html

Transactions are supported via the InnoDB table format (which was available in 3.23.x but I didn't think it was a "shipping" format).

You can see a little more here:
http://www.mysql.com/doc/en/InnoDB.html

So, you could possibly:
1. backup your data
2. recreate the tables as InnoDB tables
3. re-import the current data
4. import the daily/csv data with transaction support :)

You can tell how much I have been using MySQL lately, eh? :)

Thank you, I am aware of this. I have to work with shared hosting account and mysql 3.23, so I can not go ahead and install v.4 or recompile this one with inno-db support. If I would have those options, I would not use mysql at all.

ouch ok.

I don't know of too much you can do on a shared host w/o InnoDB :(