Forum Stats

  • 3,875,358 Users
  • 2,266,907 Discussions
  • 7,912,179 Comments

Discussions

MySQL error with double on match

felsen
felsen Member Posts: 10
edited Jun 13, 2016 8:27AM in MySQL Community Space

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

Answers

  • lefred-MySQL-Oracle
    lefred-MySQL-Oracle Member Posts: 13 Employee
    edited Jun 13, 2016 8:27AM

    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,