0

I am trying to run below query in Maria DB (10.1.26-MariaDB-0+deb9u1 Debian 9.1)

with ind as 
(
   select index_name, 
          table_name, 
          group_concat(column_name) column_name, 
          index_type 
   from   information_schema.statistics  
   where  table_schema = 'mydb' 
   and    index_name != 'primary' 
   group by 1,2,4
) 
select concat('create index ', ind.index_name, ' on ', ind.table_name,
              '(', ind.column_name,') using ', ind.index_type, ';') 
from   ind;

Im running the above query and getting some syntax error. What I am missing?

  • What error do you get? – ypercubeᵀᴹ Jul 3 '18 at 15:15
  • And what version are you using? MariaDB implemented CTEs in version 10.2 – ypercubeᵀᴹ Jul 3 '18 at 15:16
  • I'm getting from ind as till end. – Spike Jul 3 '18 at 15:28
  • 2
    If you are in 10.1, no CTEs. That's the reason ;) Upgrade – ypercubeᵀᴹ Jul 3 '18 at 16:00
  • Yeah, It works after upgrading to 10.2. Thanks. – Spike Jul 4 '18 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.