GROUP BY error installing 1.3

GoogleCodeExporter commented 8 years ago

Here's the output:

# mysql < common_schema-1.3.sql 
ERROR 1055 (42000) at line 9006: 'routine_privileges.ROUTINE_TYPE' isn't in 
GROUP BY

I'm installing into MySQL 5.6 with the following SQL_MODE:

ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_
ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Original issue reported on code.google.com by baron.schwartz on 28 Jan 2013 at 8:02

GoogleCodeExporter commented 8 years ago

Thanks. This goes beyond just this particular error -- my last build's tests 
did not watch over TRADITIONAL,ONLY_FULL_GROUP_BY -- and will need to be 
revised.

To fix your particular case, please change 
  ROUTINE_TYPE AS object_type,
to
  MAX(ROUTINE_TYPE) AS object_type,
in _sql_grants_components as follows: 

-- 
-- (Internal use): GRANTs, account details, privileges details   
-- 
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
SQL SECURITY INVOKER
VIEW _sql_grants_components AS
  (
    SELECT 
      GRANTEE,
      '*.*' AS priv_level,
      'user' AS priv_level_name,
      '' AS object_type,
      NULL AS object_schema,
      NULL AS object_name,
      'USAGE' AS current_privileges,
      MAX(IS_GRANTABLE) AS IS_GRANTABLE, 
      1 AS result_order
    FROM
      INFORMATION_SCHEMA.USER_PRIVILEGES
    GROUP BY
      GRANTEE
  )
  UNION ALL
  (
    SELECT 
      GRANTEE,
      '*.*' AS priv_level,
      'user' AS priv_level_name,
      '' AS object_type,
      NULL AS object_schema,
      NULL AS object_name,
      GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS current_privileges,
      MAX(IS_GRANTABLE) AS IS_GRANTABLE, 
      2 AS result_order
    FROM
      INFORMATION_SCHEMA.USER_PRIVILEGES
    GROUP BY
      GRANTEE
    HAVING
      GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE) != 'USAGE'
  )
  UNION ALL
  (
    SELECT
      GRANTEE,
      CONCAT('`', TABLE_SCHEMA, '`.*') AS priv_level,
      'schema' AS priv_level_name,
      '' AS object_type,
      NULL AS object_schema,
      TABLE_SCHEMA AS object_name,
      GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS current_privileges,
      MAX(IS_GRANTABLE) AS IS_GRANTABLE, 
      3 AS result_order
    FROM 
      INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
    GROUP BY
      GRANTEE, TABLE_SCHEMA
  )
  UNION ALL
  (
    SELECT
      GRANTEE,
      CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS priv_level,
      'table' AS priv_level_name,
      'table' AS object_type,
      TABLE_SCHEMA AS object_schema,
      TABLE_NAME AS object_name,
      GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS current_privileges,
      MAX(IS_GRANTABLE) AS IS_GRANTABLE, 
      4 AS result_order
    FROM 
      INFORMATION_SCHEMA.TABLE_PRIVILEGES
    GROUP BY
      GRANTEE, TABLE_SCHEMA, TABLE_NAME
  )
  UNION ALL
  (
    SELECT
      GRANTEE,
      CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS priv_level,
      'column' AS priv_level_name,
      '' AS object_type,
      TABLE_SCHEMA AS object_schema,
      TABLE_NAME AS object_name,
      GROUP_CONCAT(column_privileges ORDER BY column_privileges SEPARATOR ', ') AS current_privileges,
      MAX(IS_GRANTABLE) AS IS_GRANTABLE, 
      5 AS result_order
    FROM 
      _columns_privileges
    GROUP BY
      GRANTEE, TABLE_SCHEMA, TABLE_NAME
  )
  UNION ALL
  (
    SELECT
      GRANTEE,
      CONCAT('`', ROUTINE_SCHEMA, '`.`', ROUTINE_NAME, '`') AS priv_level,
      'routine' AS priv_level_name,
      MAX(ROUTINE_TYPE) AS object_type,
      ROUTINE_SCHEMA AS object_schema,
      ROUTINE_NAME AS object_name,
      GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS current_privileges,
      MAX(IS_GRANTABLE) AS IS_GRANTABLE, 
      6 AS result_order
    FROM 
      routine_privileges
    GROUP BY
      GRANTEE, ROUTINE_SCHEMA, ROUTINE_NAME
  )
;

--
--
--

Original comment by shlomi.n...@gmail.com on 29 Jan 2013 at 7:06

Changed state: Started

GoogleCodeExporter commented 8 years ago

This issue was closed by revision r421.

Original comment by shlomi.n...@gmail.com on 29 Jan 2013 at 7:07

Changed state: Fixed

GoogleCodeExporter commented 8 years ago

Fix released in 1.3.1

Original comment by shlomi.n...@gmail.com on 3 Feb 2013 at 8:49

dream120 / common-schema

GROUP BY error installing 1.3 #41