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

Steve 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.

Regards,
Bill K.

Bill Karwin wrote:

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

Peter H. Coffin 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.

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

Steve wrote:
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
http://bugs.mysql.com/bug.php?id=13174

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 http://dev.mysql.com/doc/refman/5.0/en/join.html 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:
http://dev.mysql.com/doc/refman/5.0/en/old-client.html
http://dev.mysql.com/doc/refman/5.0/...d-hashing.html

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

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.
http://dev.mysql.com/doc/refman/5.0/...l-upgrade.html

Regards,
Bill K.