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.

Issue with transferring MySQL views during account transfer

Discussion in 'General Discussion' started by BraenDead, Dec 29, 2009.

  1. BraenDead

    BraenDead Member

    Dec 8, 2008
    Likes Received:
    Trophy Points:

    I have been migrating a few dozen accounts from one cpanel/whm server to another, both running cPanel 11.25.0-R42399. I have experienced some issues with VIEW's being transferred correctly. The issue only affects views which include other views as part of their definition. Here is a sample which shows what is happening (assume that test1 is a table that already exists):

    CREATE VIEW view1 AS SELECT * FROM `test1`;
    CREATE VIEW view2 AS SELECT * FROM `view1`;

    In this scenario, the views must be restored in a specific order (view1 first, then view2), or else an error will occur when creating the second view and the view will not be generated. This is what is currently happening.

    The solution to this problem is to do the following during MySQL migration:

    - First create "dummy" tables on the destination MySQL schema for each view that will be transferred. These dummy tables are of the same name as the views, and contain the same columns. The column definitions are irrelevant and the "dummy" table does not need to contain any data.
    - Next transfer the views one by one. Before each view is created, the dummy table should be deleted.

    So, in my sample case, it should be transferred like so:

    // First create all of the dummy tables
    CREATE TABLE `view1`;
    CREATE TABLE `view2`;

    // Next move each account one at a time
    DROP TABLE `view1`;
    CREATE VIEW `view1` AS SELECT * FROM `test1`;

    DROP TABLE `view2`;
    CREATE VIEW `view2` AS SELECT * FROM `view1`;

    Doing it like this prevents any errors from being thrown, even if view2 is created before view1.

    Hopefully this is something that can be addressed, as currently I have to go into accounts after a transfer and check on all the views and manually restore the ones that are missing.



Share This Page