The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help, No table_name.ibd file when innodb_file_per_table = 1

Discussion in 'Workarounds and Optimization' started by gengle53022, Nov 21, 2010.

  1. gengle53022

    gengle53022 Registered

    Joined:
    Nov 21, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    I followed the instruction here,innodb_file_per_table - Converting to Per Table Data for InnoDB - cPanel Forums
    Made necessary changes and restarted mysql, and altered all Innodb tables.
    But it seems that there aren't any table_name.ibd files created for existed Innodb tables after I altered all Innodb tables.
    When I created a new table with Innodb engine, mysql would create a table_name.idb file automatically.
    Is is ok?

    --
    Stephen
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    If .ibd files aren't forming on InnoDB table creation and the tables you converted didn't form .ibd files either, then there would be an issue. When I converted my tables that were InnoDB using the commands in that guide originally (and I just retested doing it on another server of mine), the /var/lib/mysql/databasename/table_name.ibd files did form, so something either didn't get properly converted or didn't get properly set.

    Please check the following:

    Code:
    mysqladmin var|grep file_per
    It should show ON for the setting.

    Next, please provide the exact commands you ran in MySQL command line. You should be able to see the command history in /root/.mysql_history file.
     
  3. gengle53022

    gengle53022 Registered

    Joined:
    Nov 21, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Thanks for your kindly reply.

    I just check the "innodb_file_per_table", it's "On".

    And I ran the command:
    Code:
    select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';
    As the result, it did show hundreds of lines like:
    Code:
    alter table database_x.table_x ENGINE=InnoDB;
    and I check the /var/lib/mysql/database_x/, there aren't any ibd files.

    By the way, I use VPS.

    --
    Stephen
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    When you receive the output for the command with the alter lines, you need to then run the alter command lines. The initial command is getting a list of tables to alter, then the series of commands you receive need to be run to actually perform the alterations. Your tables didn't convert due to not having run the second set of commands.
     
  5. gengle53022

    gengle53022 Registered

    Joined:
    Nov 21, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    oops...

    I see, it just lists all "alter" commands...
    Thank you.

    By the way, I have another issue,
    when I tried to add some innodb options into my.cnf (like innodb_buffer_pool_size, I need to increase it for optimization), it always failed to restart mysql....what's the matter?

    ---
    Stephen
     
    #5 gengle53022, Nov 23, 2010
    Last edited: Nov 23, 2010
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Did you check the error log for MySQL when you try to add the option and perform the restart?

    Code:
    tail -f /var/lib/mysql/hostname.err
    If there's something preventing it from starting up with that option, it should appear in the error log.
     
Loading...

Share This Page