| View previous topic :: View next topic |
| Author |
Message |
wac
Halfop
Joined: 10 Dec 2006
Posts: 80
Location: in my cardboard box
|
 Posted: Sat Apr 28, 2012 12:03 am Post subject: [SOLVED] MySQL error with INDEX_LENGTH and Auto_increment |
 |
|
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 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: | 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: | | set test [mysql::fetch $mysql(handle)] |
| Code: | 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: | 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)
}
|
_________________
I see j00!
Last edited by wac on Sat Apr 28, 2012 6:21 pm; edited 2 times in total |
|
| Back to top |
|
 |
nml375
Revered One
Joined: 04 Aug 2006
Posts: 2857
|
| Posted: Sat Apr 28, 2012 12:52 pm Post subject: |
|
|
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
_________________
NML_375, idling at #eggdrop@IrcNET |
|
| Back to top |
|
|
wac
Halfop
Joined: 10 Dec 2006
Posts: 80
Location: in my cardboard box
|
| Posted: Sat Apr 28, 2012 5:39 pm Post subject: |
|
|
| nml375 wrote: | 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.
_________________
I see j00! |
|
| Back to top |
|
|
nml375
Revered One
Joined: 04 Aug 2006
Posts: 2857
|
| Posted: Sat Apr 28, 2012 5:57 pm Post subject: |
|
|
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)]"
}
... |
_________________
NML_375, idling at #eggdrop@IrcNET |
|
| Back to top |
|
|
wac
Halfop
Joined: 10 Dec 2006
Posts: 80
Location: in my cardboard box
|
| Posted: Sat Apr 28, 2012 6:19 pm Post subject: |
|
|
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.
_________________
I see j00! |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
