Error when creating Index for table Mysql

Question

I'm working on create index for table with mysql.
I've 2 tables:
1. account

2. x_activity (x is the account_id related to "account" table, EX: 1_activity, 2_activity).
So i've created an "Index" for activity table:
Here are my code:

DROP PROCEDURE if exists update_index_for_table;
DELIMITER $$
CREATE PROCEDURE update_index_for_table()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE accountid INT;  
  --
  -- GET ALL ACCOUNT ID
  --

  DECLARE accountids CURSOR FOR SELECT account_id FROM account;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  --
  -- LOOP 
  --  

  OPEN accountids; 
  read_loop: LOOP 
    FETCH accountids INTO accountid;
    IF done THEN 
      LEAVE read_loop;
    END IF;   

    --
    -- INDEX FOR ACTIVITY 
    --  

        SET @update_activity_table_1 = CONCAT("
        IF (
            SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE `TABLE_SCHEMA` = DATABASE() AND TABLE_NAME='",accountid,"_activity' AND 
            INDEX_NAME='IDX_",accountid,"_ACTIVITY_ACTIVITY_ID'
            ) != 1
        THEN
            ALTER TABLE ",accountid,"_activity 
                ADD KEY `IDX_",accountid,"_ACTIVITY_ACTIVITY_ID` (`activity_id`);
        END IF;
        ");
    PREPARE stmt from @update_activity_table_1; 
    EXECUTE stmt;

    END LOOP; 
  CLOSE accountids;
END$$
DELIMITER ;
CALL update_index_for_table();

But then, for some php/mysql version (i think), its cause an error like this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF ( SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE `TABLE_SCHEM' at line 1

I've tested this code and its work fine:

SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE `TABLE_SCHEMA` = DATABASE() AND TABLE_NAME='",accountid,"_activity' AND 
INDEX_NAME='IDX_",accountid,"_ACTIVITY_ACTIVITY_ID'

Here are my php/sql version:
phpmyadmin: 4.8.5, php version: 7.2.7, mysql: 5.6.45

Please help, thanks.

Use single quotes instead of double quotes, as it is the ANSI standard way. Most likely, different versions of MySQL have different default mode for ANSI_QUOTES — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 26, 2019 at 8:33
thanks, i'll try it out. and report to you as soon as posible. — fudu
– fudu, Commented Sep 26, 2019 at 8:38
i've replace double quotes with single quote and replace single quote with double quotes but it still show the error. — fudu
– fudu, Commented Sep 26, 2019 at 8:40
@update_activity_table_1; will end up with a statement which starts IF.. which can only be used in a stored program. — P.Salmon
– P.Salmon, Commented Sep 26, 2019 at 10:21
@P.Salmon i don't understand what you mean. Can you say more clear? — fudu
– fudu, Commented Sep 26, 2019 at 10:24

P.Salmon · Accepted Answer · 2019-09-30 08:34:40Z

There are a couple of constraints on what you are trying to do here 1) you cannot run an if statement outwith a stored program 2)if you pass a query to dynamic sql and the query does not find anything the continue handler will be invoked and the loop will terminate (unexpectedly) early. The approach then is to split the functionality to first check existence by amending the 'find' to insert a value to a user defined variable and at the same time ensure the handler is not hijacked by a not found by including a look up on a table which will definitely contain something (in this case information.schema_tables.

So given

DROP PROCEDURE if exists p;
DELIMITER $$
CREATE PROCEDURE p()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE accountid INT;
  --
  -- GET ALL ACCOUNT ID
  --
  DECLARE accountids CURSOR FOR SELECT account_id FROM account;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  --
  -- LOOP 
  --  
  OPEN accountids; 
  read_loop: LOOP 
    FETCH accountids INTO accountid;
    select accountid;
    IF done = true THEN 
        select accountid, 'leaving';
      LEAVE read_loop;
    END IF;   
    --
    -- INDEX FOR ACTIVITY 
    --  
   SET @test := 0; 
    SET @update_activity_table_1 :=
        (concat('SELECT case when index_name is null then 1 else 0 end into @test FROM 
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss ',
         ' on iss.table_schema = it.table_schema and iss.table_name = it.table_name and ',
         'INDEX_NAME=',char(39),'IDX_',accountid,'_ACTIVITY_ACTIVITY_ID',char(39),
        ' WHERE it.TABLE_SCHEMA = ', char(39),'test',char(39), ' AND ',
        'it.TABLE_NAME=',char(39),accountid,'_activity', char(39),
        ';'
        )
        )
    ;
   select @update_activity_table_1;

    PREPARE stmt from @update_activity_table_1;
   EXECUTE stmt;
    deallocate prepare stmt;

    if @test = 1 then
        select 'Did not find index for ' , accountid, '_extract';
    else
        select 'Found index for ' , accountid, '_extract';
    end if;

    END LOOP; 
   CLOSE accountids;
END $$
DELIMITER ;
call p();

I'll leave you to build the alter statement and insert into the if statement.

given

    use test;
    drop table if exists account,`1_activity`,`2_activity`,`64_activity`;

    create table account (account_id int);
    create table `1_activity`(id int);
    create table `2_activity`(id int);
    create table `64_activity`(id int);

    insert into account values (1),(2),(64);

MariaDB [test]> call p();
+-----------+
| accountid |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @update_activity_table_1                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT case when index_name is null then 1 else 0 end into @test FROM
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss  on iss.table_schema = it.table_schema and iss.table_name = it.table_name and INDEX_NAME='IDX_1_ACTIVITY_ACTIVITY_ID' WHERE it.TABLE_SCHEMA = 'test' AND it.TABLE_NAME='1_activity'; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+-------------------------+-----------+----------+
| Did not find index for  | accountid | _extract |
+-------------------------+-----------+----------+
| Did not find index for  |         1 | _extract |
+-------------------------+-----------+----------+
1 row in set (0.28 sec)

+-----------+
| accountid |
+-----------+
|         2 |
+-----------+
1 row in set (0.30 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @update_activity_table_1                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT case when index_name is null then 1 else 0 end into @test FROM
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss  on iss.table_schema = it.table_schema and iss.table_name = it.table_name and INDEX_NAME='IDX_2_ACTIVITY_ACTIVITY_ID' WHERE it.TABLE_SCHEMA = 'test' AND it.TABLE_NAME='2_activity'; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.30 sec)

+-------------------------+-----------+----------+
| Did not find index for  | accountid | _extract |
+-------------------------+-----------+----------+
| Did not find index for  |         2 | _extract |
+-------------------------+-----------+----------+
1 row in set (0.47 sec)

+-----------+
| accountid |
+-----------+
|        64 |
+-----------+
1 row in set (0.49 sec)

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @update_activity_table_1                                                                                                                                                                                                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT case when index_name is null then 1 else 0 end into @test FROM
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss  on iss.table_schema = it.table_schema and iss.table_name = it.table_name and INDEX_NAME='IDX_64_ACTIVITY_ACTIVITY_ID' WHERE it.TABLE_SCHEMA = 'test' AND it.TABLE_NAME='64_activity'; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.50 sec)

+-------------------------+-----------+----------+
| Did not find index for  | accountid | _extract |
+-------------------------+-----------+----------+
| Did not find index for  |        64 | _extract |
+-------------------------+-----------+----------+
1 row in set (0.66 sec)

+-----------+
| accountid |
+-----------+
|        64 |
+-----------+
1 row in set (0.67 sec)

+-----------+---------+
| accountid | leaving |
+-----------+---------+
|        64 | leaving |
+-----------+---------+
1 row in set (0.67 sec)

Query OK, 0 rows affected (0.69 sec)

Hi, I've run your code but the result only go through account with id = 35 and 36. other account won't come in, and i got account from 1 to 64.
If the procedure is not finding some tables then those tables cannot be named 'correctly' or don't exist. See edit.
i don't get it, the "_activity" table have the same structure which created by code, not by hand. Then how even possible there are some activity work and some not ..
No idea. I can only suggest you do the select from information_schema.tables and information_schema.statistics for some of the tables which aren't found and try to figure it out from there.
May i ask what is char(39) you put in is use for? And the code "WHERE it.TABLE_SCHEMA = ', char(39),'test',char(39), ' " too. I don't understand this part. Thanks.

phemieny7 · Accepted Answer · 2019-09-26 08:26:59Z

0

Firstly, i believe accound_id and activity_id are both your unique keys, but what am not sure if you are auto incrementing it, check to see if the auto increment is check.

answered Sep 26, 2019 at 8:26

phemieny7

8317 silver badges24 bronze badges

1 Comment

fudu Over a year ago

Yes it does auto increment.

Collectives™ on Stack Overflow

Error when creating Index for table Mysql

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related