Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts

    mysqldump messed up my Views

    Am migrating my "doubledee" MySQL database between my old and new Mac.

    I used mysqldump to do so, since phpMyAdmin turned out to be totally unreliable!!

    After doing an export to a .sql file on my Old MacBook, and then an import using the .sql file on my New MacBook Pro, I then did an export of the new database, and am using the application DeltaWalker to compare the original and new .sql files.

    Everything looks okay, except that my VIEWS seem to be getting trashed?!

    Here is what is happening...

    Original View
    Code:
    --
    -- Temporary table structure for view `prior_email_VIEW`
    --
    
    DROP TABLE IF EXISTS `prior_email_VIEW`;
    /*!50001 DROP VIEW IF EXISTS `prior_email_VIEW`*/;
    /*!50001 CREATE TABLE `prior_email_VIEW` (
      `id` int(10) unsigned,
      `email` varchar(80),
      `member_id` mediumint(8) unsigned,
      `ip` varchar(128),
      `host_name` varchar(128),
      `created_on` datetime,
      `updated_on` datetime
    ) */;

    New View
    Code:
    --
    -- Temporary table structure for view `prior_email_view`
    --
    
    DROP TABLE IF EXISTS `prior_email_view`;
    /*!50001 DROP VIEW IF EXISTS `prior_email_view`*/;
    SET @saved_cs_client     = @@character_set_client;
    SET character_set_client = utf8;
    /*!50001 CREATE TABLE `prior_email_view` (
      `id` tinyint NOT NULL,
      `email` tinyint NOT NULL,
      `member_id` tinyint NOT NULL,
      `ip` tinyint NOT NULL,
      `host_name` tinyint NOT NULL,
      `created_on` tinyint NOT NULL,
      `updated_on` tinyint NOT NULL
    ) ENGINE=MyISAM */;
    SET character_set_client = @saved_cs_client;

    What is up with all of those TINYINTs?! (WTF??)


    What is even more strange, is that when I click on any of the Views in phpMyAdmin, the records in the View seem to be okay...

    But how can the View work if it is trying to crunch all of those fields into TINYINTs????

    Sincerely,


    Debbie

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Wow. And another part I don't get: Why does it say "temporary table structure"???

    Can you find the dump of the view in the text of the datadump?? What does it say?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Wow. And another part I don't get: Why does it say "temporary table structure"???
    Was that sarcasm??


    Quote Originally Posted by Old Pedant View Post
    Can you find the dump of the view in the text of the datadump?? What does it say?
    I am just going through the File Compare sequentially...


    After my OP, I do see this at the end of the files...

    Original File
    Code:
    --
    -- Final view structure for view `prior_email_VIEW`
    --
    
    /*!50001 DROP TABLE IF EXISTS `prior_email_VIEW`*/;
    /*!50001 DROP VIEW IF EXISTS `prior_email_VIEW`*/;
    /*!50001 CREATE ALGORITHM=UNDEFINED */
    /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
    /*!50001 VIEW `prior_email_VIEW` AS select `prior_email`.`id` AS `id`,`prior_email`.`email` AS `email`,`prior_email`.`member_id` AS `member_id`,`prior_email`.`ip` AS `ip`,`prior_email`.`host_name` AS `host_name`,`prior_email`.`created_on` AS `created_on`,`prior_email`.`updated_on` AS `updated_on` from `prior_email` order by `prior_email`.`created_on` desc */;


    New File
    Code:
    --
    -- Final view structure for view `prior_email_view`
    --
    
    /*!50001 DROP TABLE IF EXISTS `prior_email_view`*/;
    /*!50001 DROP VIEW IF EXISTS `prior_email_view`*/;
    /*!50001 SET @saved_cs_client          = @@character_set_client */;
    /*!50001 SET @saved_cs_results         = @@character_set_results */;
    /*!50001 SET @saved_col_connection     = @@collation_connection */;
    /*!50001 SET character_set_client      = utf8 */;
    /*!50001 SET character_set_results     = utf8 */;
    /*!50001 SET collation_connection      = utf8_general_ci */;
    /*!50001 CREATE ALGORITHM=UNDEFINED */
    /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
    /*!50001 VIEW `prior_email_view` AS select `prior_email`.`id` AS `id`,`prior_email`.`email` AS `email`,`prior_email`.`member_id` AS `member_id`,`prior_email`.`ip` AS `ip`,`prior_email`.`host_name` AS `host_name`,`prior_email`.`created_on` AS `created_on`,`prior_email`.`updated_on` AS `updated_on` from `prior_email` order by `prior_email`.`created_on` desc */;
    /*!50001 SET character_set_client      = @saved_cs_client */;
    /*!50001 SET character_set_results     = @saved_cs_results */;
    /*!50001 SET collation_connection      = @saved_col_connection */;

    On a side note, I've never really understood VIEWS...


    I think it is correct to say, "A View is just a 'pre-compiled' SQL Query that makes it easier to see certain records, and it also provides extra security through 'data-hiding'..."


    But at least in phpMyAdmin, when I created Views and then went in to see the SQL, what I saw was gobbly-gook to me?!

    (When I click on a View object in phpMyAdmin, I would expect to see an SQL Query...)

    Sincerely,


    Debbie

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Maybe it's a phpMyAdmin thing.

    Using the command line, the dump of a view is almost identical to what I typed in when defining the view.

    Example:
    Code:
    mysql> create view ts as select t.team_id, sum(points) as points from team_members as t, team_member_scores as s where t.team_id = s.team_id group by t.team_id;
    Use the view:
    Code:
    mysql> select * from ts;
    +---------+--------+
    | team_id | points |
    +---------+--------+
    |       1 |    276 |
    |       2 |     99 |
    +---------+--------+
    2 rows in set (0.00 sec)
    Dump out the view, per MySQL:
    Code:
    mysql> show create view ts;
    +------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -----------------------------------------+----------------------+----------------------+
    | View | Create View
                                             | character_set_client | collation_connection |
    +------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -----------------------------------------+----------------------+----------------------+
    | ts   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `ts` AS select `t`.`team_id` AS `team_id`,sum(`s`.`points`) AS `points` from (`team_members` `t` join `team_member_scores` `s`) where (`t`.`team_id`
     = `s`.`team_id`) group by `t`.`team_id` | latin1               | latin1_swedish_ci    |
    +------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -----------------------------------------+----------------------+----------------------+
    In other words, MySQL transformed my code
    Code:
    create view ts as 
    select t.team_id, sum(points) as points 
    from team_members as t, team_member_scores as s 
    where t.team_id = s.team_id 
    group by t.team_id;
    to this
    Code:
    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `ts` AS 
    select `t`.`team_id` AS `team_id`,sum(`s`.`points`) AS `points` 
    from (`team_members` `t` join `team_member_scores` `s`) 
    where (`t`.`team_id` = `s`.`team_id`) 
    group by `t`.`team_id`
    Which is a LITTLE weird, but not too much.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Maybe it's a phpMyAdmin thing.
    Remember, I am here because phpMyAdmin let me down and I'm dumping it - no pun intended!!


    Quote Originally Posted by Old Pedant View Post
    Using the command line, the dump of a view is almost identical to what I typed in when defining the view.
    I did some more digging after your abrupt last response, and this is what I think I figured out...


    a.) There is indeed these "Temp Tables" for the Views. (Not sure why...)


    b.) If I dug through all of the comments and jazz that mysqldump created, I found this...
    Code:
    /*!50001 VIEW `prior_email_view` AS select `prior_email`.`id` AS `id`,`prior_email`.`email` AS `email`,`prior_email`.`member_id` AS `member_id`,`prior_email`.`ip` AS `ip`,`prior_email`.`host_name` AS `host_name`,`prior_email`.`created_on` AS `created_on`,`prior_email`.`updated_on` AS `updated_on` from `prior_email` order by `prior_email`.`created_on` desc */;
    Boy is that *UGLY*...


    c.) If I clean things up into a format that I would write myself, I get this...
    Code:
    VIEW `prior_email_view` AS 
    SELECT id, email, member_id, ip, host_name, created_on, updated_on
    FROM prior_email
    ORDER BY created_on DESC;
    And that makes much more sense to me!!

    It looks like a "Named Query", which is my understanding of what a View is...


    d.) With all of this being said, other than some minor conversations like these...
    Code:
    default ---> DEFAULT
    
    auto_increment ---> AUTO_INCREMENT

    Along with this kind of stuff...
    Code:
    /*!50001 SET @saved_cs_client          = @@character_set_client */;

    It looks like my "Backup & Restore" using mysqldump was successful!! (At least for my tiny "Dev" database.)


    Although I'm not sure how well mysqldump will work when I go to "Backup & Restore" my "Production" database with 10,000 Users and 200,000 Messages?!

    Sincerely,


    Debbie


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •