Re: [ISCS-devel] Database bug

[John A. S. I. <jsu...@op...>]

Come to think of it, there may be deployments on 3.23 so we need to find
a way to do this without subqueries.  The only currently supported
database is MySQL although that is not the long term plan.  Is there
even an MySQL specific way to do it? Can values be saved to some
variable populated via a select statement and then the variable used in
an update statement? Thanks - John

On Wed, 2007-12-19 at 18:07 -0500, John A. Sullivan III wrote:
> Alas, mysql 4.x returns an error:
> 
> mysql> update all_res_ipranges set containsbestmatch=0 where actualrange
> in (select actualrange from all_res_ipranges left join best_match_res_ip
> on lowbinary=superlow and highbinary=superhigh where containsbestmatch=1
> and superlow is null);
> ERROR 1064 (00000): You have an error in your SQL syntax near 'select
> actualrange from all_res_ipranges left join best_match_res_ip on
> lowbinar' at line 1
> 
> Do you or anyone happen to know what the proper subclause syntax is for
> MySQL < 5.x? Thanks - John
> 
> On Wed, 2007-12-19 at 18:33 -0300, Daniel Medeiros wrote:
> > At first, I forgot confirming that you were right about the problem.
> > In an UPDATE statement you cannot reference more than one table.
> > 
> > I'm at work now and the CVS to outside is blocked, but if you don't
> > mind I'd to guess your answer. 
> > 
> > I may confess I am not so used to SQL:2003 (I have worked more with
> > SQL99), and I am not sure if it will work in every database you have,
> > you'll need to test them. But, even without seeing your database, just
> > by your previous comments and commands, I'd guess these commands: 
> > 
> > update accessors_ip set containsbestmatch=0 
> > where actualrange in ( select actualrange from accessors_ip left join
> > best_match_acc_ip on lowbinary=superlow and
> >          highbinary=superhigh where
> >          containsbestmatch=1 and superlow is null); 
> > 
> > and 
> > 
> > update all_res_ipranges set containsbestmatch=0 
> > where actualrange in ( select actualrange from all_res_ipranges left
> > join best_match_res_ip on lowbinary=superlow and
> >    
> >  highbinary=superhigh where 
> >          containsbestmatch=1 and superlow is null);
> > 
> > 
> > 
> > 
> > On Dec 19, 2007 5:24 PM, John A. Sullivan III
> > <jsu...@op...> wrote:
> >         That would be great, Daniel.  The ERDs and table definitions
> >         are in the
> >         CVS under devel-docs/Database.
> >         
> >         So how is the world going for you? Planning anything special
> >         for
> >         Christmas? Take care - John
> >         
> >         
> >         On Wed, 2007-12-19 at 15:32 -0300, Daniel Medeiros wrote:
> >         > Hey, John,
> >         >
> >         > I guess I can help you, even being out of the project. Could
> >         you send
> >         > me the database schema? I'll take a look and answer you
> >         ASAP. 
> >         >
> >         > - Daniel
> >         >
> >         > On Dec 19, 2007 3:08 PM, John A. Sullivan III <
> >         > jsu...@op...> wrote:
> >         >         Hello, all.  While writing the edit Resource code we
> >         need for 
> >         >         a current
> >         >         project, I came across a bug in the code which may
> >         have made
> >         >         some
> >         >         erroneous database entries however I do not have the
> >         SQL
> >         >         knowledge to 
> >         >         know how to write an SQL statement to find and fix
> >         the
> >         >         erroneous
> >         >         entries.  Can someone suggest how we can write a
> >         patch for the
> >         >         next
> >         >         release to repair the database? 
> >         >
> >         >         The problem is that we erroneously returned a value
> >         of true
> >         >         for
> >         >         containsbestmatch when it should have been false.
> >          To find the
> >         >         errant
> >         >         rows in the all_res_ipranges table (for Resources)
> >         or the 
> >         >         accessors_ip
> >         >         table (for Accessors) we can do:
> >         >
> >         >         select actualrange, containsbestmatch from
> >         all_res_ipranges
> >         >         left join
> >         >         best_match_res_ip on lowbinary=superlow and 
> >         >         highbinary=superhigh where
> >         >         containsbestmatch=1 and superlow is null;
> >         >
> >         >         and
> >         >
> >         >         select actualrange, containsbestmatch from
> >         accessors_ip left
> >         >         join 
> >         >         best_match_acc_ip on lowbinary=superlow and
> >         >         highbinary=superhigh where
> >         >         containsbestmatch=1 and superlow is null;
> >         >
> >         >         But how do we find the rows and then fix them in a
> >         single SQL 
> >         >         statement?
> >         >         Something like:
> >         >
> >         >         update accessors_ip set containsbestmatch=0 left
> >         join
> >         >         best_match_acc_ip
> >         >         on lowbinary=superlow and highbinary=superhigh
> >         where 
> >         >         containsbestmatch=1
> >         >         and superlow is null;
> >         >
> >         >         does not work since I think one can have only one
> >         table in an
> >         >         update
> >         >         statement.  I'd like to fix this ASAP so any help
> >         would be 
> >         >         greatly
> >         >         appreciated.  Thanks - John
> >         >         --
> <snip>
> > 
-- 
John A. Sullivan III
Open Source Development Corporation
+1 207-985-7880
jsu...@op...

Financially sustainable open source development
http://www.opensourcedevel.com