Rational Relational

In 5.5 we extended the concept of "name lock" in t...

2010-02-18T15:55:16.325+02:00

In 5.5 we extended the concept of "name lock" in the server to be able to lock any kinds of names -- including database names.
So in 5.5 we will be able to, so to speak, latch a single database name, without affecting any other database.

ALTER DATABASE does nothing like that currently, and is race-prone. Since it does very little, the effects of the race are not observable externally.

@Kedar: The problem with this method, which I ment...

2010-02-18T15:29:06.840+02:00

@Kedar:
The problem with this method, which I mentioned in the start of the post is the length of the operation. If the database is several tens of GBs in size, it can take quite a while to dump & re-import it - not worthwhile just for changing the name of the schema.

How about: mysql -uroot -ppassword -e "drop data...

2010-02-18T14:47:09.265+02:00

How about:
mysql -uroot -ppassword -e "drop database DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;" | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword DB_TO_BE_CREATED

further with 1 more pipe you may delete db as well :)

Thank you for your comments! @Tom,@gtowey: You're...

2010-02-18T11:47:19.879+02:00

Thank you for your comments!

@Tom,@gtowey: You're right, I did ignore triggers and Stored Procedures, the reason was that the focus for me was saving time moving tables around schemas, when the tables are 10s of GBs in size. Procedures and Triggers were no problem to re-create :)
And I like the atomic rename-all-at-once!

@kostja: Regarding point #1 - how is that going to be performed in 5.5? A new type of lock on the database level? What is done now in case someone uses "ALTER DATABASE" to change the collation?
And regarding point #2 - For innodb, the change would not really matter, since the table dictionary is in the ibdata file anyway. We only have things to gain in removing the frm files, like they are doing in Drizzle.

Two reasons we still don't have RENAME DATABASE st...

2010-02-18T11:28:47.405+02:00

Two reasons we still don't have RENAME DATABASE statement:
- there is no way to lock out an entire database. You can only lock individual tables in the server, and that is race-prone if you do a RENAME DB: while you are renaming the tables you see, someone may add new tables.
- our data dictionary is not in a transactional storage, thus a rename becomes a huge hassle: you need to move the .frm files for tables (i.e. rename the individual tables), then move the trigger files, then change trigger definitions to point at the new names, then move stored procedures, functions and events (in other words, everything stored in mysql.* system tables).

Now, in MySQL 5.5 we're quite close to solving problem #1. But a solution for problem #2 will cause screams, since many people like the fact that we keep tables in .frms :( and not in an internal data dictionary, like InnoDB.

Don't forget stored procedures, functions and trig...

2010-02-17T21:59:21.789+02:00

Don't forget stored procedures, functions and triggers!

Also, you might as well make it one atomic operation, instead of separate statements:

SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT( table_schema,'.',table_name, ' TO ','new_schema.',table_name,' '),';') as stmt FROM information_schema.TABLES WHERE table_schema LIKE 'old_schema' GROUP BY table_schema;

I have used this technique in the past and it work...

2010-02-17T19:46:31.095+02:00

I have used this technique in the past and it works quite well.

However, if you have triggers, you will need to drop them and create them on the new schema after everything is moved.

It isn't free (~$19.50), but I've used j-write to ...

2009-12-14T23:54:11.306+02:00

It isn't free (~$19.50), but I've used j-write to effortlessly open and edit files that were 7+GB in size.

Another (open source) editor you might want to throw a very large file at is metapad.

you need to account for no values - SELEC...

2009-11-06T23:08:16.962+02:00

you need to account for no values -

SELECT table_name,
case when LEN(column_names) > 0
then LEFT(column_names,LEN(column_names) - 1)
else '' end AS column_names
FROM (SELECT table_name,
(SELECT column_name + ',' AS [text()]
FROM information_schema.columns AS internal
WHERE internal.table_name = table_names.table_name
FOR xml PATH ('')
) AS column_names
FROM (SELECT table_name
FROM information_schema.columns
GROUP BY table_name) AS table_names) AS pre_trimmed;

fantastic. worked for us on our netbook for our he...

2009-10-17T21:05:25.628+02:00

fantastic. worked for us on our netbook for our hebrew songs in windows xp. Thanks!

Amazing program. Thank u very much. Installed, cos...

2009-10-16T05:36:07.088+02:00

Amazing program. Thank u very much. Installed, cose my Music folder and converted almost all of the Hebrew music. thanks again.
Arie

Thanks.

2009-08-26T16:21:40.909+03:00

Thanks.

Totach! for the second time this week!

2009-08-21T12:49:47.800+03:00

Totach! for the second time this week!

On the other hand, this means using vi/vim, which ...

2009-08-19T17:03:37.571+03:00

On the other hand, this means using vi/vim, which in my book is marginally preferable to clawing your own eyes out. But what really amazes me is that the GNU head tool has no "skip n lines first" feature.

@water outbreaks I agree it's not ideal (mostly si...

2009-08-14T23:33:40.057+03:00

@water outbreaks
I agree it's not ideal (mostly since it rewrites the file), but it's the best available for Windows. Of course I would use "less" or "cat" if it was Linux...
I have a friend that says "Windows is for little girls", maybe he's right this time :)

What's that editor? Is it downloadable someplace?

No Vim isn't good enough for this kind of work eit...

2009-08-14T18:08:31.296+03:00

No Vim isn't good enough for this kind of work either. Maybe better than some, but still not ideal.

If you just want to view the file ( often the case when its a large text file) just cat the file into less.

Find the spot you want to edit, and write a quick script to make the edit.

Or better yet, you could write a striped down editor that is specifically tailored to this problem, like I did :)

@swanhart: Well, back then, they built things to l...

2009-08-13T13:16:14.078+03:00

@swanhart: Well, back then, they built things to last :P

@Karlsson: Scintilla is a great editor, but not meant for such work. I would assume it's because of the syntax parsing it does, etc.
Other editors like SQLyog are using their built-in editors, btw. SQLyog manages to open large files, but doesn't do that as gracefully as Vim though.

My MyQuery tool is currently using SCintilla. And ...

2009-08-13T01:56:06.272+03:00

My MyQuery tool is currently using SCintilla. And I agree that what you say is an issue, and something that I wanted to adress in MyQuery. The Scintilla base to an extent limites what MyQuery can do, bt still, I do my own interpretation of scripts, so I have a few things I want to do eventually, beyond editing large files in Scintilla.
One idea I have is to be able to run a script, statement by statement, bringing up each statement in Scintilla. Another is running statement by statement til we have an error, allow you to fix that that single statment using the Scintilla based editor, and then continue from there.

Just one more reason why vi (and vim in specific) ...

2009-08-12T23:41:55.487+03:00

Just one more reason why vi (and vim in specific) is still my favorite editor of all time. I spend at least a few hours per day in vim and I wouldn't trade it for anything in the world.

I do like notepad++ for quick editing on windows and for short files and scripts though. I always have two or three notepad++ open for jotting down notes, and for using as a temporary clipboard repository.

thank you very much, worked like a charm

2009-07-24T21:07:46.012+03:00

thank you very much, worked like a charm

gadol

2009-02-02T09:29:32.287+02:00

gadol

HI, I have a bunch of songs on an external hard dr...

2009-01-05T04:59:15.943+02:00

HI, I have a bunch of songs on an external hard drive that are tagged in Hebrew lettering & I would like to import them to itunes but it cannot read Hebrew. Can some one please help me? I am using a PC & windows XP.

It's written in Java, perhaps you don't have it in...

2008-12-09T14:44:13.452+02:00

It's written in Java, perhaps you don't have it installed?

I downloaded the utility but haven't been able to ...

2008-12-09T05:46:11.132+02:00

I downloaded the utility but haven't been able to run it - does it need to run within another program?

Thanks for the tip.

2008-11-29T00:19:59.811+02:00

Thanks for the tip.