[SOLVED] MySQL error with INDEX_LENGTH and Auto_increment

[wac]

What I'm trying to get the code to do is return the table size in the database but all it does is return "1" when the command is run, I'm not quite sure what I'm missing here.
Also trying to get how many records are in the database but it also only returns "1".

Code: Select all

bind pub - !size size
proc size {nick uhost handle chan arg} {
        global mysql libMySQLTcl
        set mysql(handle) [mysqlconnect -host $mysql(host) -user $mysql(user) -password $mysql(pass) -port $mysql(port) -db $mysql(db)]
        mysqluse $mysql(handle) $mysql(db)
        set test [mysqlsel $mysql(handle) "SELECT    SUM((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024)    FROM  INFORMATION_SCHEMA.tables    WHERETABLE_SCHEMA = 'mydatabase'    AND   TABLE_NAME   = 'mytable';"]
        putserv "privmsg $chan : $test "
 mysqlclose $mysql(handle)

Code: Select all

bind pub - !records records
proc records {nick uhost handle chan arg} {
        global mysql libMySQLTcl
        set mysql(handle) [mysqlconnect -host $mysql(host) -user $mysql(user) -password $mysql(pass) -port $mysql(port) -db $mysql(db)]
        mysqluse $mysql(handle) $mysql(db)
        set test [mysqlsel $mysql(handle) "SELECT    SUM(Auto_increment) FROM  INFORMATION_SCHEMA.tables    WHERE TABLE_SCHEMA = 'mydatabase'    AND   TABLE_NAME   ='mytable';"]
        putserv "privmsg $chan :$test"
 mysqlclose $mysql(handle)
}

EDIT: Adding this line about putserv makes the query work thanks to nml375 for the help.

Code: Select all

        set test [mysql::fetch $mysql(handle)]

Code: Select all

bind pub - !size size
proc size {nick uhost handle chan arg} {
        global mysql libMySQLTcl
        set mysql(handle) [mysqlconnect -host $mysql(host) -user $mysql(user) -password $mysql(pass) -port $mysql(port) -db $mysql(db)]
        mysqluse $mysql(handle) $mysql(db)
        set test [mysqlsel $mysql(handle) "SELECT    SUM((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024)    FROM  INFORMATION_SCHEMA.tables    WHERETABLE_SCHEMA = 'mydatabase'    AND   TABLE_NAME   = 'mytable';"]
        set test [mysql::fetch $mysql(handle)]
        putserv "privmsg $chan : $test "
 mysqlclose $mysql(handle)

Code: Select all

bind pub - !records records
proc records {nick uhost handle chan arg} {
        global mysql libMySQLTcl
        set mysql(handle) [mysqlconnect -host $mysql(host) -user $mysql(user) -password $mysql(pass) -port $mysql(port) -db $mysql(db)]
        mysqluse $mysql(handle) $mysql(db)
        set test [mysqlsel $mysql(handle) "select count(*) from db.table;"]
        set test [mysql::fetch $mysql(handle)]
        putserv "privmsg $chan :$test"
 mysqlclose $mysql(handle)
}

[nml375]

That's because you did not use the --list or --flatlist option, and your queries returned 1 rows each in their result. In essence, you'll either have to use the --list or --flatlist options for in-line results, or use ::mysql::fetch to fetch the results (one row at a time) after your call to ::mysql::sel.

As for your second query, that will not generate the number of rows in the table, unless you have a table column named "Auto_increment" that has the value 1 for each row... More likely, you're thinking of using the COUNT() aggregator instead

[wac]

That's because you did not use the --list or --flatlist option, and your queries returned 1 rows each in their result. In essence, you'll either have to use the --list or --flatlist options for in-line results, or use ::mysql::fetch to fetch the results (one row at a time) after your call to ::mysql::sel.

As for your second query, that will not generate the number of rows in the table, unless you have a table column named "Auto_increment" that has the value 1 for each row... More likely, you're thinking of using the COUNT() aggregator instead

Code: Select all

        set test [mysql::fetch $mysql(handle)]

Worked for both queries, there is a column named id that runs auto_increment. Thanks for your help.

[nml375]

Looking deeper, I notice that you actually query the information_schema-tables. The "TABLES"-table would contain one row for each table in the database, where the AUTO_INCREMENT column would hold the value for the current auto_increment counter for that table.

Thus, using SUM() here makes no sense, since you'd only return one row in the first place. Secondly, the auto_increment counter is not guarantee'd to be a sequential enumeration, there may be gaps for various reasons. Further, the AUTO_INCREMENT counter actually holds the next value to be used, not the last one, so unless you explicitly initiated the counter to 0 (default is 1), you'd get the wrong number anyway.

If you do intend to use the information_schema tables, I'd recommend you look at the TABLE_ROWS column instead:

Code: Select all

...
set test [::mysql::sel $mysql(handle) "SELECT `TABLE_ROWS` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='mydatabase' AND `TABLE_NAME`='mytable'"
if {$test >= 1} {
  putserv "privmsg $chan :[::mysql::fetch $mysql(handle)]"
}
...

[wac]

Yes I found that auto_increment does not display the actual total records just the number that have been added and not that are necessarily still there.

Code: Select all

select count(*) from db.table

Will display the actual records still in the database.