egghelp/eggheads community

[SOLVED] MySQL error with INDEX_LENGTH and Auto_increment

2012-04-28T18:19:34-04:00

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 count(*) from db.table

Will display the actual records still in the database.

Statistics: Posted by wac — Sat Apr 28, 2012 6:19 pm

[SOLVED] MySQL error with INDEX_LENGTH and Auto_increment

2012-04-28T17:57:42-04:00

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:

...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)]"}...

Statistics: Posted by nml375 — Sat Apr 28, 2012 5:57 pm

[SOLVED] MySQL error with INDEX_LENGTH and Auto_increment

2012-04-28T17:39:19-04:00

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:

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

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

Statistics: Posted by wac — Sat Apr 28, 2012 5:39 pm

[SOLVED] MySQL error with INDEX_LENGTH and Auto_increment

2012-04-28T12:52:14-04:00

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

Statistics: Posted by nml375 — Sat Apr 28, 2012 12:52 pm

[SOLVED] MySQL error with INDEX_LENGTH and Auto_increment

2012-04-28T18:21:11-04:00

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:

bind pub - !size sizeproc 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:

bind pub - !records recordsproc 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:

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

Code:

bind pub - !size sizeproc 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:

bind pub - !records recordsproc 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)}

Statistics: Posted by wac — Sat Apr 28, 2012 12:03 am