MySQL error with double on match

felsen

Hello,

I have encounter the following error when trying to execute the following query:

SELECT *, (
         MATCH(a.x) AGAINST ( '2311' IN BOOLEAN MODE ) 
         + 
         MATCH(a.y) AGAINST ( '2311' IN BOOLEAN MODE )
         ) AS score 
     FROM
         a 
     JOIN
         b 
             ON b.id = a.id 
     WHERE
         (
             b.status = 'WELCOMED'
         ) 
         AND (
             MATCH(a.serial_num) AGAINST ( '2311' IN BOOLEAN MODE ) 
             OR MATCH(a.additional_info) AGAINST ( '2311' IN BOOLEAN MODE )
         ) 
     ORDER BY
         score LIMIT 0,10 ;

Error Code: 1690. DOUBLE value is out of range in '((match `foo`.`a`.`serial_num` against ('2311' in boolean mode)) + (match `foo`.`a`.`additional_info` against ('2311' in boolean mode)))' 0.000 sec

This is reproduced at:

innodb_version 5.7.11
protocol_version 10
slave_type_conversions 
tls_version TLSv1,TLSv1.1
version 5.7.11-log
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os Win64

I could not understand what is the problem with this? I suspect that the values return by match when added, are out of bound of the double on select. On exclusive execution of two additive factors the results are ok without any error.  

Thank you very much and i appreciate your help

lefred-MySQL-Oracle

Hi,

What's is the size of max_allowed_packet ? and would it be possible that this user is sending more data than this limit ?

Also, you can set log_warnings to 2 to see in error log the aborted connections. And finally you can use Performance_Schema to identify the user having this problem using the following query for example:

Accounts not properly closing connections:

SELECT ess.user, ess.host

, (a.total_connections - a.current_connections) - ess.count_star as not_closed

, ((a.total_connections - a.current_connections) - ess.count_star) * 100 /

   (a.total_connections - a.current_connections) as pct_not_closed

  FROM performance_schema.events_statements_summary_by_account_by_event_name ess

  JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)

WHERE ess.event_name = 'statement/com/quit'

   AND (a.total_connections - a.current_connections) > ess.count_star;

Best regards,