Deletion based on the result of a 3 table right joins select query (MySQL 3.23)

[Steve]

I am using MySQL 3.23

I have a relatively complex database with a number of Many to Many
relationships (using link tables).

I want to delete all items in the NewsItems table that would be
returned by this select query:

SELECT NewsItems.Id
FROM FeedItemLink RIGHT JOIN (MemberItemSaveFile RIGHT JOIN NewsItems
ON MemberItemSaveFile.ItemID = NewsItems.Id) ON FeedItemLink.ItemID =
NewsItems.Id
where MemberItemSaveFile.ItemID is null and FeedID=54

Any thoughts?

(sorry if this is really easy - I'm relatively new to it all).

Steve

[Bill Karwin]

Steve wrote:

> I am using MySQL 3.23
>
> I want to delete all items in the NewsItems table that would be
> returned by this select query:

Since you're using MySQL 3.23, you don't have access to multi-table
delete syntax. That was introduced in MySQL 4.0.

If you can't upgrde, I'd recommend saving the output of the query, and
then use it as a list of id values in an "IN" predicate.

Regards,
Bill K.

[Steve]

Bill Karwin wrote:

> Since you're using MySQL 3.23, you don't have access to multi-table
> delete syntax. That was introduced in MySQL 4.0.
>
> If you can't upgrde, I'd recommend saving the output of the query, and
> then use it as a list of id values in an "IN" predicate.

Thanks Bill. I do have full server access so could upgrade - but I'm
terrified of screwing my server up.

I recently had to rebuild the server because of some autmatic update
YUM on the server control panel.

I have two questions:

1 Is upgrading relatively painless

2 Will all my existing queries work?

Cheers.

Steve

[Steve]

Peter H. Coffin wrote:

> Mostly it is, if you don't have a huge number of client applications to
> be rebound to new libraries. While it is *possible* to make most pre 4.1
> applications work with post 4.1 servers, the means of doing so is
> somewhat brittle (Basically, the password hash changed and while you can
> force old sytle passwords into the authentication you have to be Very
> Aware of the new/old difference forever until you get the clients
> updated.)
>
> The other usual issue is the routine "use mysqldump to make your backup,
> don't just restore files". Basically, you're not upgrading, you're
> removing the old server and putting a new one into place, and loading
> the data.

Thanks. I may have a go at the weekend. My log in scripts do use the
MySQL PASSWORD() function though. I'll have a think about it.

I DO need to do some complex-ish queries (sub queries etc) and not
being able to use IN is a bit of an irritation. So I really should
upgrade.

Would you advise going the whole hog from 3.23 to 5?

Thanks for your tips.

Steve

[Bill Karwin]

Steve wrote:

> Thanks. I may have a go at the weekend. My log in scripts do use the
> MySQL PASSWORD() function though. I'll have a think about it.

Note in the docs for the PASSWORD() function, they state that this
function should be used only by the MySQL internals, and they reserve
the right to change its implementation. You should use a standard
hashing function instead.

MD5() and SHA1() used to be recommended, but recently these functions
have been shown to be breakable. Now SHA-256 is the hashing function
recommended by NIST, but MySQL doesn't have an implementation built-in
yet. MySQL AB is prioritizing this. See
[url]http://bugs.mysql.com/bug.php?id=13174[/url]

> Would you advise going the whole hog from 3.23 to 5?

There's one notable place I can think of where queries may break when
upgrading from 3 to 5. That's the case in which you mix "comma-style"
joins with "SQL-92 JOIN styl" joins. For example
SELECT ...
FROM t1, t2, LEFT JOIN t3 ON ...
See [url]http://dev.mysql.com/doc/refman/5.0/en/join.html[/url] for this.

I agree with Peter, back up your databases into a portable format using
mysqldump. Then restore them after the software upgrade. There have
been some bugs reported, related to using old databases as-is after an
upgrade to 5.0.

Peter also covered the password issue, which is documented in these two
pages:
[url]http://dev.mysql.com/doc/refman/5.0/en/old-client.html[/url]
[url]http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html[/url]

You should also read other upgrading issues before doing an upgrade:
[url]http://dev.mysql.com/doc/refman/4.1/en/upgrade.html[/url]
[url]http://dev.mysql.com/doc/refman/5.0/en/upgrade.html[/url]

It is recommended by some people to upgrade one release at a time. That
is, 3.23 to 4.0, 4.0 to 4.1, 4.1 to 5.0. I'm not sure I agree that this
is necessary; I'd recommend going straight to 5.0, but make sure to
restore the data from a mysqldump backup.

Also read about the "mysql_upgrade" program, included in MySQL 5.0.
[url]http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html[/url]

Regards,
Bill K.