Re: does anyone else have problems sending mails to this list ?

[Olivier Nicole]

Bernd,

> : host lists-mx.mysql.com[137.254.60.71] said: 552 spam
> score exceeded threshold (#5.6.1) (in reply to end of DATA command)
>
> Any ideas ?

Whatever provider you are using to send mail has been blacklisted?

It happens with some ISP who are not diligent enough to remove the
spammers from their networks, legitimate customers get penalized.

Best regards,

Olivier

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: AW: Select one valuebut not the other

[Olivier Nicole]

Axel,

 Simply translated:

 select *  from table t1
 where t1.data_value=1 
 AND not exists(select * from table t2 
where t2.data_value=2
and   t2.item_number = t1.item_number)

Yes, but with t1 and t2 the same table.

best regards,

Olivier




 Axel Diehl
 __
 GIP Exyr GmbH
 Hechtsheimer Str. 35-37 | 55131 Mainz 

 Tel: +49 (0) 6131 / 80124 - 46 | Fax: +49 (0) 6131 / 80124 - 24
 E-Mail: axel.di...@gip.com | Web: www.gip.com http://www.gip.com/  

 Geschäftsführer: Dr. Bernd Reifenhäuser, Dr. Alexander Ebbes
 Handelsregister: HRB 6870 - Amtsgericht Mainz 

  


 -Ursprüngliche Nachricht-
 Von: Olivier Nicole [mailto:olivier.nic...@cs.ait.ac.th] 
 Gesendet: Mittwoch, 29. April 2015 07:21
 An: mog...@fumlersoft.dk
 Cc: mysql@lists.mysql.com
 Betreff: Re: Select one valuebut not the other

 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a 
 newbie question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one 
 data_value.

 If one item has several values, there are several records with the 
 same item_number and a different data_value.

 What is the command to select all the records where an item_number 
 has the data 1 but not the data 2?

 Thanks in advance,

 Olivier

 --
 Mogens Melander
 +66 8701 33224


 --
 This message has been scanned for viruses and dangerous content by 
 MailScanner, and is believed to be clean.

-- 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

Thank you, 

 SELECT * FROM test
 WHERE item_number in (SELECT item_number FROM test where data_value=1)
 AND item_number not in (SELECT item_number FROM test where data_value = 2);

That did it.

Olivier


 On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier


 -- 
 Mogens Melander
 +66 8701 33224


 -- 
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

Lucio, 

 I have a table where each record is made of one item_number and one
 data_value.
 You do not have any other column ? In particular you do not have any 
 unique key record identifier ? All my tables have a column with a record 
 sequence number seq int NOT NULL AUTO_INCREMENT which is also a key
 KEY auxiliary(seq). This is useful a posteriori to locate particular 
 records.

I do, but that was irrelevant to my question, as it is only counting the
records, it carries no information.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?
 1) by select you mean display at the terminal using the mysql line mode
 client, or locate all affected records for further work ?

I meant SELECT command, so a display I guess (but that would be the same
select in Perl).

 2) am I getting it correctly that you want to locate all the cases where
 a given item_number (any) has JUST ONE occurrence in the table ?

 In the line mode client this can be easily done with an additional table, 
 which can be a temporary table.

My idea was to do it in one single command, without using additional
table.

I ended up with something along the line of:

select handle, text_value from metadatavalue, handle where 
item_id in (select item_id from metadatavalue where metadata_field_id=64)
and item_id not in (select item_id from metadatavalue 
where metadata_field_id=27) 
and metadata_field_id=64 
and handle.resource_id=item_id 
and resource_type_id=2
order by item_id;

Maybe not the fastest nor the nicest, but as I need to run it only once,
it is enought.

Thank you,

Olivier

 Consider e.g. the following table (it has two columns, no seq column, and
 nothing else ... actually it is a table of seq pointers in two other 
 tables)

   select * from north33w1t7_ 

 | north33 | w1t7 |
 +-+--+
 |  21 |1 |
 |  21 |2 |
 |  24 |   20 |

 create temporary table temp1
 select north33,count(*) as c from north33w1t7_
 group by north33 order by north33;

 temp1 will contain something like this

 | north33 | c |
 +-+---+
 |  21 | 2 |
 |  24 | 1 |

 so it will tell you that item 21 has 2 counteparts, while item 24 
 has 1 counterpart.

 If you want to select (display) all cases in the main table with 1 
 counterpart do

 select north33w1t7_.*
 from temp1 join north33w1t7_  on 
 temp1.north33=north33w1t7_.north33
 where c=1 :

 | north33 | w1t7 |
 +-+--+
 |  24 |   20 |
 |  200013 |   93 |


 A different story would be if you want always to extract ONE record from 
 the main table, the single one if c=1, and the FIRST one if c1.

 What you define first it is up to you (the smallest data_value, the 
 highest data_value, a condition on other columns).

 Here in general I use a trick which involves one or two temporary tables 
 and a variable.  I initialize the variable to zero (or a value which is 
 not represented in the table, which shall be ordered on the columns as you 
 need. Then I test whether the item_number is the same as the variable, if 
 not I declare it to be first, then reset the variable in the same select 
 statement.

   set @x:=0;
   select north33w1t7_.*,
   if(@xnorth33w1t7_.north33,'FIRST','no') as flag,
   @x:=north33w1t7_.north33
   from temp1 join north33w1t7_  on temp1.north33=north33w1t7_.north33
   where c1 order by north33,w1t

 | north33 | w1t7 | flag  | @x:=north33w1t7_.north33 |
 +-+--+---+--+
 |  21 |1 | FIRST |   21 |
 |  21 |2 | no|   21 |
 |  22 |8 | FIRST |   22 |
 |  22 |9 | no|   22 |

 I can then save this select to a temporary table, and take my pick where 
 flag='FIRST'.


 of course you can also do without the join with temp1 if you want
 either the single or the first (i.e. c=1 and c1)

 select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from 
 north33w1t7_  order by north33,w1t7

 | north33 | w1t7 | flag  | @x:=north33 |
 +-+--+---+-+
 |  21 |1 | FIRST |  21 |
 |  21 |2 | no|  21 |
 |  22 |8 | FIRST |  22 |
 |  22 |9 | no|  22 |
 |  24 |   20 | FIRST |  24 |


 -- 
 
 Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
 For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
 
 Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Select one valuebut not the other

[Olivier Nicole]

Hi,

I am sure that it is feasible with MySQl, and I am sure that's a newbie
question, but my SQL skills are limited...

I have a table where each record is made of one item_number and one
data_value.

If one item has several values, there are several records with the same
item_number and a different data_value.

What is the command to select all the records where an item_number has
the data 1 but not the data 2?

Thanks in advance,

Olivier
-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

 SELECT * FROM table WHERE item_number=1;

Sorry if my question was not clear: what I am looking for is:

SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2

Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier

 -- 
 Mogens Melander
 +66 8701 33224


 -- 
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Help with REGEXP

[Olivier Nicole]

Paul,

You could look for a tool called The Regex Coach. While it is mainly
for Windows, it runs very well in vine. I fijd it highly useful to debug
regexps.

Best regards,

Olivier
-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

MySQL error to syslog

[Olivier Nicole]

HI,

I am running MySQL 5.5.31 on FreeBSD 9.2. I have a web server with a
miss-configured service that generates faulty connections. After a
while, MySQl blocks any connection from the web server.

At some stage, I had set-up a script that would browse syslog log and
look for a string like Host 'xxx' is blocked because of many connection
errors; unblock with 'mysqladmin flush-hosts' and would then issue a
mysqladmin flush-hosts.

But after an upgrade, MySQl stopped reporting to syslog.

MySQL process is:

databaseroot: ps auwwx | grep mysql
mysql 81063   0.0  0.1   9852  1172 ??  Is3Dec14  0:00.18 /bin/sh 
/usr/local/bin/mysqld_safe --defaults-extra-file=/database/mysql/my.cnf 
--user=mysql --datadir=/database/mysql 
--pid-file=/database/mysql/database.cs.ait.ac.th.pid --syslog
mysql 81386   0.0  2.0 314712 42180 ??  I 3Dec14  8:19.75
/usr/local/libexec/mysqld --defaults-extra-file=/database/mysql/my.cnf
--basedir=/usr/local --datadir=/database/mysql
--plugin-dir=/usr/local/lib/mysql/plugin --user=mysql
--pid-file=/database/mysql/database.cs.ait.ac.th.pid
--socket=/tmp/mysql.sock --port=3306

Any help will be greatly appreciated.

TIA,

Olivier

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql