Bug #21957 Replication fails with "Error 'Invalid use of group function' on query."
Submitted: 31 Aug 2006 22:18 Modified: 1 Oct 2006 9:02
Reporter: Torrey Hoffman Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: ERROR 1111, replication

[31 Aug 2006 22:18] Torrey Hoffman
Description:
On a master/slave replication setup, replication breaks on a complex insert from select using a user-defined function.  The error message in the slave log doesn't seem to make sense.

The error message complains of invalid use of group function, but the query contains no "GROUP BY".  

There is no error on the master server server, so the query itself seems to be valid.

Furthermore, when I log into the server on the slave and execute the SQL query directly from the mysql command prompt, no error is returned: I get "Query OK, 21 rows affected, 714 warnings (0.38 sec) Records: 21  Duplicates: 0  Warnings: 0"

So this is something specific about replication.

Note that the user-defined "port_trunked" function used in the query only does SELECTS, it does not modify data.

The error from the slave log is below:
----
060831 21:09:46 [ERROR] Slave: Error 'Invalid use of group function' on query. Default database: 'Swordfish'. Query: '

INSERT INTO sal_dbus_map
(
      switch_instance_id,
      port_configuration_id,
      dbus_map_method,
      method_table_id
     )
     SELECT
      si.switch_instance_id,
      pc.port_configuration_id,
      'set_port_vlan',
      mt.method_table_id
     FROM
      sal_switch_instance si LEFT JOIN
      sal_port_configuration pc USING (switch_instance_id) LEFT JOIN
      sal_switch_instance_has_method_table simt USING (switch_instance_id) LEFT JOIN
      sal_method_table mt USING (method_table_id)
     WHERE
      port_configuration_id in (2,3,4,5,6,7,8,9,11,12,14,17,18,19,20,21,22,23,24,25,26) AND
      mt.method_table_name = IF( port_trunked( port_configuration_id ), 'set_trunk_port_native_vlan', 'set_port_vlan' )

', Error_code: 1111
060831 21:09:46 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'frodo.000003' position 73971

How to repeat:
This happens on our replication setup every time this particular query is executed.  I do not have a simpler repro case.

Suggested fix:
Replication should either not break, or, if something does break, the error message should return useful information.
[31 Aug 2006 22:43] MySQL Verification Team
Thank you for the bug report. Your server version is pretty older, could
you please test with the latest version and re-open this bug report if
you still get the same issue.

Thanks in advance.
[1 Sep 2006 0:39] Torrey Hoffman
I have upgraded a pair of test machines to 5.0.24a and so far, have not been able to reproduce the error.

So this may be fixed.

I will do more testing, and if all goes well I will close the bug.
[1 Oct 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".