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

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

On 24 Jul 2006 04:55:56 -0700, Steve wrote:
> 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

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.

> 2 Will all my existing queries work?

Should. I can't remember anyplace offhand where functionality was
removed or a significant change was made to how a thing operates.

--
86. I will make sure that my doomsday device is up to code and properly
grounded.
--Peter Anspach's list of things to do as an Evil Overlord

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

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

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

On Sat, 29 Jul 2006 19:02:49 -0700, Bill Karwin wrote:
> 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.

They have at least once, after all...

> 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

They're breakable, but it's still not entirely trivial, and depending on
the application, it may not be worth worrying about. Hashes are plenty
secure for protecting your vacation photo gallery and the like.

>> 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 http://dev.mysql.com/doc/refman/5.0/en/join.html for this.

Oooh, that kind of thing makes my head hurt... One style of join per
query is plenty for me.

[...]
> 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.

The password issue shows up whether you do one big jump or several
little ones. One big one *MAY* cause more fussiness with the parameters
to be used for the dump, but the solution to THAT issue is to not tear
down the old installation until after you've gotten the data migrated.

--
52. I will hire a team of board-certified architects and surveyors to
examine my castle and inform me of any secret passages and abandoned
tunnels that I might not know about.
--Peter Anspach's list of things to do as an Evil Overlord