Professional Web Applications Themes

SHOW TABLES and CHECKSUM TABLE statements - MySQL

Hello, (thanks in advance for any replies) If I run SHOW TABLES; or CHECKSUM TABLE <tbl name>;, the result gets displayed in mysql client. Now, is there any way I can get the result into a variable or a table? I am wondering if I can do: 1. get list of tables 2. create a table containing checksums for these tables. 3. upgrade to 5.0. 4. repeat #1 and #2. 5. compare the checksums before and after. Does this make sense? Thanks, -Steve...

  1. #1

    Default SHOW TABLES and CHECKSUM TABLE statements

    Hello, (thanks in advance for any replies)

    If I run SHOW TABLES; or CHECKSUM TABLE <tbl name>;, the result gets
    displayed in mysql client.
    Now, is there any way I can get the result into a variable or a table?

    I am wondering if I can do:
    1. get list of tables
    2. create a table containing checksums for these tables.
    3. upgrade to 5.0.
    4. repeat #1 and #2.
    5. compare the checksums before and after.

    Does this make sense?
    Thanks,

    -Steve

    estebanjang@gmail.com Guest

  2. #2

    Default Re: SHOW TABLES and CHECKSUM TABLE statements

    > If I run SHOW TABLES; or CHECKSUM TABLE <tbl name>;, the result gets 


    I don't know if this makes sense, but you can do it in shell:

    $ mysql -u root -proot test -B -N -e "show tables" | while read tname
    do
    printf "%s\n" "checksum table $tname;" | mysql -u root -proot
    test -B -N
    done > before_upgrade.txt

    $ cat before_upgrade.txt
    test.myemployees 93242333
    test.numer 1558177361
    test.t 0
    test.t3 677972025
    test.test 1446160993

    (inserted some records here, just to simulate a change)

    $ mysql -u root -proot test -B -N -e "show tables" | while read tname
    do
    printf "%s\n" "checksum table $tname;" | mysql -u root -proot
    test -B -N
    done > after_upgrade.txt

    $ diff before_upgrade.txt after_upgrade.txt
    3c3
    < test.t 0
    --- 


    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: SHOW TABLES and CHECKSUM TABLE statements

    Thanks for your reply, which is kind of what I would have ended up
    doing.
    However, a more general intent of my question was:

    What is the return value is when I run something like "CHECKSUM TABLE"
    statement in SQL? Is it possible to capture the result of such this
    statement into an SQL variable?

    Thanks!

    -Steve


    Radoulov, Dimitre wrote: 
    >
    >
    > I don't know if this makes sense, but you can do it in shell:
    >
    > $ mysql -u root -proot test -B -N -e "show tables" | while read tname
    > do
    > printf "%s\n" "checksum table $tname;" | mysql -u root -proot
    > test -B -N
    > done > before_upgrade.txt
    >
    > $ cat before_upgrade.txt
    > test.myemployees 93242333
    > test.numer 1558177361
    > test.t 0
    > test.t3 677972025
    > test.test 1446160993
    >
    > (inserted some records here, just to simulate a change)
    >
    > $ mysql -u root -proot test -B -N -e "show tables" | while read tname
    > do
    > printf "%s\n" "checksum table $tname;" | mysql -u root -proot
    > test -B -N
    > done > after_upgrade.txt
    >
    > $ diff before_upgrade.txt after_upgrade.txt
    > 3c3
    > < test.t 0
    > --- 
    >
    >
    > Regards
    > Dimitre[/ref]

    estebanjang@gmail.com Guest

  4. #4

    Default Re: SHOW TABLES and CHECKSUM TABLE statements

    Top-post fixed below.
     
    >>
    >>
    >> I don't know if this makes sense, but you can do it in shell:
    >>
    >> $ mysql -u root -proot test -B -N -e "show tables" | while read tname
    >> do
    >> printf "%s\n" "checksum table $tname;" | mysql -u root -proot
    >> test -B -N
    >> done > before_upgrade.txt
    >>
    >> $ cat before_upgrade.txt
    >> test.myemployees 93242333
    >> test.numer 1558177361
    >> test.t 0
    >> test.t3 677972025
    >> test.test 1446160993
    >>
    >> (inserted some records here, just to simulate a change)
    >>
    >> $ mysql -u root -proot test -B -N -e "show tables" | while read tname
    >> do
    >> printf "%s\n" "checksum table $tname;" | mysql -u root -proot
    >> test -B -N
    >> done > after_upgrade.txt
    >>
    >> $ diff before_upgrade.txt after_upgrade.txt
    >> 3c3
    >> < test.t 0
    >> --- [/ref][/ref]
     

    I don't know.
    I tried with dynamic sql but I got error:

    mysql> select version();
    +---------------------+
    | version() |
    +---------------------+
    | 5.0.26-community-nt |
    +---------------------+
    1 row in set (0.03 sec)

    mysql> delimiter //
    mysql> create procedure p1(in_tab_name varchar(128)) begin
    -> set stmt := concat('checksum table ',in_tab_name);
    -> prepare stmt from stmt;
    -> execute stmt;
    -> deallocate prepare stmt;
    -> end;
    -> //
    Query OK, 0 rows affected (0.00 sec)
    mysql> delimiter ;
    mysql> call p1('T');
    ERROR 1295 (HY000): This command is not supported in the prepared statement
    protocol yet


    Regards
    Dimitre





    Regards
    Dimitre


    Radoulov, Guest

  5. #5

    Default Re: SHOW TABLES and CHECKSUM TABLE statements

    >>> > If I run SHOW TABLES; or CHECKSUM TABLE <tbl name>;, the result gets [/ref][/ref]
    [...] [/ref]
    [...]

    I think you can't:

    http://bugs.mysql.com/bug.php?id=17009


    Regards
    Dimitre


    Radoulov, Guest

  6. #6

    Default Re: SHOW TABLES and CHECKSUM TABLE statements


    Thanks, this is the answer I was looking for.

    -Steve

    Radoulov, Dimitre wrote: [/ref]
    > [...] [/ref]
    > [...]
    >
    > I think you can't:
    >
    > http://bugs.mysql.com/bug.php?id=17009
    >
    >
    > Regards
    > Dimitre[/ref]

    estebanjang@gmail.com Guest

Similar Threads

  1. dumping SHOW TABLES to a variable?
    By yawnmoth in forum MySQL
    Replies: 1
    Last Post: May 9th, 08:51 PM
  2. Tables don't show in Design View
    By pwalters in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: January 23rd, 05:00 PM
  3. No tables show up under databases/connections
    By Sunder27 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: March 24th, 03:52 AM
  4. Problem with Show tables; :(
    By Adrian Majchrzak in forum PHP Development
    Replies: 2
    Last Post: January 14th, 10:35 PM
  5. SQL-Statements / full-table scans
    By Jonathan Lewis in forum Oracle Server
    Replies: 0
    Last Post: December 7th, 01:18 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139