Re: SHOW TABLE STATUS
2010/4/7 Carsten Pedersen cars...@bitbybit.dk: AFAIR, MySQL 4.x supports LIKE, e.g. SHOW TABLE STATUS LIKE 'tab_%' yes, but if the tables have different names (table1, tab_2, abcd... ) your syntax will not work. How to do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SHOW TABLE STATUS
On Thu, Apr 8, 2010 at 9:33 AM, spacemarc spacem...@gmail.com wrote: 2010/4/7 Carsten Pedersen cars...@bitbybit.dk: AFAIR, MySQL 4.x supports LIKE, e.g. SHOW TABLE STATUS LIKE 'tab_%' yes, but if the tables have different names (table1, tab_2, abcd... ) your syntax will not work. How to do? Multiple statements. Remember, show table status is not SQL, it's a command to the mysql server. Regular SQL constructs do not apply. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
SHOW TABLE STATUS
hi all,
in MySQL 4.1.x i want to obtain the status of more tables with one only query.
In 5.x i use SHOW TABLE STATUS WHERE Name IN ('tab_1', tab_2, 'tab_3')
In 4.1.x i tried to use but it doesn't works: how to set the query?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SHOW TABLE STATUS
AFAIR, MySQL 4.x supports LIKE, e.g.
SHOW TABLE STATUS LIKE 'tab_%'
/ Carsten
spacemarc skrev:
hi all,
in MySQL 4.1.x i want to obtain the status of more tables with one only query.
In 5.x i use SHOW TABLE STATUS WHERE Name IN ('tab_1', tab_2, 'tab_3')
In 4.1.x i tried to use but it doesn't works: how to set the query?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SHOW TABLE STATUS constantly wildly fluctuating
$ watch -d mysqlshow --status myDB #shows the count of rows is constantly fluctuating for some tables, even though the database is offline. There ought to be a note about it here and on HELP SHOW TABLE STATUS; Must use o --count Show the number of rows per table. (Which also should mention that it also shows number of columns.) Release: mysql-5.1.32-1 ((Debian)) C compiler:gcc (Debian 4.3.3-5) 4.3.3 C++ compiler: g++ (Debian 4.3.3-5) 4.3.3 Environment: System: Linux jidanni2 2.6.26-1-686 #1 SMP Sat Jan 10 18:29:31 UTC 2009 i686 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make Compilation info (call): CC='gcc' CFLAGS='-O3 -DBIG_JOINS=1 ' CXX='g++' CXXFLAGS='-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='gcc' CFLAGS=' -O3 -DBIG_JOINS=1-DUNIV_LINUX' CXX='g++' CXXFLAGS=' -O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti-fno-implicit-templates -fno-exceptions -fno-rtti' LDFLAGS=' -rdynamic ' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 11 2009-03-21 08:20 /lib/libc.so.6 - libc-2.9.so -rwxr-xr-x 1 root root 1310924 2009-03-19 02:16 /lib/libc-2.9.so Configure command: ./configure '--build=i486-linux-gnu' '--host=i486-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-1' '--with-comment=(Debian)' '--with-system-type=debian-linux-gnu' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-pstack' '--with-fast-mutexes' '--with-big-tables' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-ssl' '--without-docs' '--with-extra-charsets=all' '--with-plugins=max' '--without-ndbcluster' '--with-embedded-server' '--with-embedded-privilege-control' 'build_alias=i486-linux-gnu' 'host_alias=i486-linux-gnu' 'CC=gcc' 'CFLAGS=-O3 -DBIG_JOINS=1 ' 'LDFLAGS=' 'CPPFLAGS=' 'CXX=g++' 'CXXFLAGS=-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti ' 'FFLAGS=-g -O2' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to extract data from the show table status?
After a pretty long time I have returned to being a mysql DBA again after spending a lot of time with Oracle and MSSQL. I have several databases that I need to to work on, all various source builds from 4.1.16 to 4.0.20 and the average database contains some 200 or more tables. Some are clustered some are stand alone. There is an overall project to upgrade and consolidate some of these and to add in belt and braces redundancy and to add database backups. Most server installation are for just a single version, some installations have just 2 or 3 databases some have 20 or more. The first task I need to run is to determine the database table storage engines (all make use of MyISAM, InnoDB and Archive), then update frequency and row numbers. Now if I use: show table status from database name; It will list all the table information I need however, what I am trying to figure out is how to get access to the data this produces directly. The data I want is name, engine, rows, avg_row_length, max_data_length, create_time and update_time. I can do it manually but that is a bit mind numbing and leads to inaccuracy if this were Oracle I could query one of the V$ views and get this immediately. If this were version 5 I might use INFORMATION_SCHEMA. Obviously it has been far too long and I have forgotten completely. I do not want to go down the perl if I can help it. Is there a way to do this internally using sql I seem to remember there wasn't? Can anyone advise? Dom __ Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract data from the show table status?
Hi Dom, On Jan 14, 2008 10:49 AM, Dominic Baines [EMAIL PROTECTED] wrote: After a pretty long time I have returned to being a mysql DBA again after spending a lot of time with Oracle and MSSQL. I have several databases that I need to to work on, all various source builds from 4.1.16 to 4.0.20 and the average database contains some 200 or more tables. Some are clustered some are stand alone. There is an overall project to upgrade and consolidate some of these and to add in belt and braces redundancy and to add database backups. Most server installation are for just a single version, some installations have just 2 or 3 databases some have 20 or more. The first task I need to run is to determine the database table storage engines (all make use of MyISAM, InnoDB and Archive), then update frequency and row numbers. Now if I use: show table status from database name; It will list all the table information I need however, what I am trying to figure out is how to get access to the data this produces directly. The data I want is name, engine, rows, avg_row_length, max_data_length, create_time and update_time. I can do it manually but that is a bit mind numbing and leads to inaccuracy if this were Oracle I could query one of the V$ views and get this immediately. If this were version 5 I might use INFORMATION_SCHEMA. Obviously it has been far too long and I have forgotten completely. I do not want to go down the perl if I can help it. Is there a way to do this internally using sql I seem to remember there wasn't? There's no way to do it from SQL, but try this, using a tool from Maatkit: mk-find --printf '%D.%N %E %S %A %M %C %U\n' Welcome back to MySQL :-) You can get Maatkit from http://sourceforge.net/projects/maatkit/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to extract data from the show table status?
Dominic Baines wrote:
After a pretty long time I have returned to being a mysql DBA again after
spending a lot of time with Oracle and MSSQL.
I have several databases that I need to to work on, all various source builds
from 4.1.16 to 4.0.20 and the average database contains some 200 or more
tables. Some are clustered some are stand alone.
There is an overall project to upgrade and consolidate some of these and to
add in belt and braces redundancy and to add database backups.
Most server installation are for just a single version, some installations
have just 2 or 3 databases some have 20 or more.
The first task I need to run is to determine the database table storage
engines (all make use of MyISAM, InnoDB and Archive), then update frequency
and row numbers.
Now if I use:
show table status from database name;
It will list all the table information I need however, what I am trying to
figure out is how to get access to the data this produces directly.
The data I want is name, engine, rows, avg_row_length, max_data_length,
create_time and update_time.
I can do it manually but that is a bit mind numbing and leads to inaccuracy
if this were Oracle I could query one of the V$ views and get this
immediately. If this were version 5 I might use INFORMATION_SCHEMA.
Obviously it has been far too long and I have forgotten completely.
I do not want to go down the perl if I can help it. Is there a way to do this
internally using sql I seem to remember there wasn't?
Can anyone advise?
Dom
On Linux you could try these two options:
$ mysql -u root -p -e SHOW TABLE STATUS FROM dbname | awk '{print
$1, $2, $5, $6, $8, $12, $13}' | column -t
$ mysql -u root -p -e SHOW TABLE STATUS FROM dbname\G | egrep
Name:|Engine:|Rows:|Avg_row_length:|Max_data_length:|Create_time:|Update_time:
The first option will create a nice formatted table. Here is an example
showing a few columns:
$ mysql -u root -p -e SHOW TABLE STATUS FROM world | awk '{print $1,
$2, $5, $6, $12}' | column -t
Name Engine Rows Avg_row_length Create_time
City MyISAM 4079 67 2008-01-14
Country MyISAM 239 261 2008-01-14
CountryLanguage MyISAM 984 39 2008-01-14
Werner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql cluster show table status
Hi, we are using MySQL Cluster 5.0.27 on Solaris 9 Sparc. The packages are from mysql.com. Doing an show table status on a database with the ndb engine returns 0 for rows count, average row length etc. Hm, i have read that such a bug existed and has been fixed with 5.0.3. So i am wondering what i might have done wrong. Has anybody a hint for me? No, the table is not empty :) It contains about 1 rows. thanks --lars -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0 probleam with show table status
I have a serius probleam , my backup system see the FK's with show table status like 'tablename' , and read the Comment column... in mysql 4.x works fine... but em 5.x the FK's has cuted ex: | apresentante | InnoDB | 9 | Redundant | 44 |372 | 1 6384 | 0 |81920 | 0 | 50 | 2005-08-04 11:19:15 | NULL| NULL | latin1_swedish_ci | NULL | | InnoDB free: 01952 kB; (`Ap_UsuarioAlteracao`) REFER `sqlreg3/usuario` (`Us_I | --- * HERE IS THE PROBLEM ** But , the show create table command shows: | apresentante |CREATE TABLE `apresentante` ( `Ap_Id` int(4) unsigned NOT NULL auto_increment, `Ap_Nome` varchar(200) NOT NULL default '', `Ap_Endereco` varchar(200) NOT NULL default '', `Ap_Telefone` varchar(30) NOT NULL default '', `Ap_EMail` varchar(200) NOT NULL default '', `Ap_DataInclusao` date NOT NULL default '-00-00', `Ap_HoraInclusao` time NOT NULL default '00:00:00', `Ap_DataAlteracao` date NOT NULL default '-00-00', `Ap_HoraAlteracao` time NOT NULL default '00:00:00', `Ap_UsuarioInclusao` int(4) unsigned default NULL, `Ap_UsuarioAlteracao` int(4) unsigned default NULL, UNIQUE KEY `Ap_Id` (`Ap_Id`), KEY `IAp_Nome` (`Ap_Nome`), KEY `iAp_UsDtHoInclusao` (`Ap_DataInclusao`,`Ap_HoraInclusao`), KEY `iAp_UsDtHoAlteracao` (`Ap_DataAlteracao`,`Ap_HoraAlteracao`), KEY `iap_UsuarioInclusao` (`Ap_UsuarioInclusao`), KEY `iap_UsuarioAlteracao` (`Ap_UsuarioAlteracao`), CONSTRAINT `apresentante_ibfk_1` FOREIGN KEY (`Ap_UsuarioAlteracao`) REFERENCE S `usuario` (`Us_Id`), CONSTRAINT `apresentante_ibfk_2` FOREIGN KEY (`Ap_UsuarioInclusao`) REFERENCES `usuario` (`Us_Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | I'm using MySQL 5.0.18-PRO on Linux Debian Box with kernel 2.6.14-2-686-smp on Xeon 2.4 machine with 1GB of RAM Using InnoDB tables... Tnks in advance Innodb,MySQL,user,hlllppp -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://javacoffe.blogspot.com*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 probleam with show table status
Hi, I have a serius probleam , my backup system see the FK's with show table status like 'tablename' , and read the Comment column... in mysql 4.x works fine... but em 5.x the FK's has cuted This didn't work fine in 4.x at all. The comments columns was being misused to report FKs. Even more: it was being mis-used AND it was unreliable. ex: | apresentante | InnoDB | 9 | Redundant | 44 |372 | 1 6384 | 0 |81920 | 0 | 50 | 2005-08-04 11:19:15 | NULL| NULL | latin1_swedish_ci | NULL | | InnoDB free: 01952 kB; (`Ap_UsuarioAlteracao`) REFER `sqlreg3/usuario` (`Us_I | --- * HERE IS THE PROBLEM ** But , the show create table command shows: --8-- snip KEY `iap_UsuarioAlteracao` (`Ap_UsuarioAlteracao`), CONSTRAINT `apresentante_ibfk_1` FOREIGN KEY (`Ap_UsuarioAlteracao`) REFERENCE S `usuario` (`Us_Id`), CONSTRAINT `apresentante_ibfk_2` FOREIGN KEY (`Ap_UsuarioInclusao`) REFERENCES `usuario` (`Us_Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | This is is the only way to get the FK data. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL db size using show table status
Qus 2. Is there any other way to compute the db size (other than disk quota). du -s mysql_data_directory If you want to know the size of a /single/ database (i.e. schema) then this method works if there's just one database in the mysql_data_directory. If there are multiple databases and they only contain MyISAM tables then `du -s mysql_data_directory/database_name` does the trick, but if a database also contains InnoDB tables then you're out of luck, since these are stored in the InnoDB tablespace, which is 'shared' by all InnoDB tables from all databases/schemas. To measure the size of my databases I use the 'show table status' command and I ignore .frm file size. Kind regards, -- Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL db size using show table status
Jaspreet Singh wrote: Hi, I am trying to compute the MySQL db size using show table status command. It gives me the size of .MYD and .MIY files, but not .frm which is typically 12k (using 4.1.9 version of MySQL) Qus 1. is there any way to deterministically compute the value of .frm file using a command line tool should do this. Usually, .frm table definition files are negligible to the size of the actual database. Qus 2. Is there any other way to compute the db size (other than disk quota). du -s mysql_data_directory Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL db size using show table status
Hi, I am trying to compute the MySQL db size using show table status command. It gives me the size of .MYD and .MIY files, but not .frm which is typically 12k (using 4.1.9 version of MySQL) Qus 1. is there any way to deterministically compute the value of .frm file Qus 2. Is there any other way to compute the db size (other than disk quota). Thanx in anticipation, Jaspreet Singh -- Don't Walk as if you own the world, Walk as if you don't care who owns it. Jaspreet Singh Software Engineer, Ensim India. [EMAIL PROTECTED] +91 9890712226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show table status
Hello, Does anyone know where (c-api functions perhaps) SHOW TABLE STATUS gets its info from? Specifically, the new columns added in 4.1.2 and 4.1.3, are they the result of underlying c-function changes, new functions, or something else. Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show table status extremely slow
Hi, there, We have a huge database (84 tables of about 360 G of data in MyISAM tables). Recently, we converted the entire database to InnoDB (in one table space) and set up replication. Then we experienced some slower performance. For example, show table status on the master took more than 90 seconds if the database connection was made from local server, and terribly 400 seconds if the connection was from a remote server. At the moment show table status was issued, there were about 5 other queries running. However, if the test was done on the slave, show table status took approximately 90 seconds for each of the connections from the local and remote servers, while only the slave thread was running in the meanwhile. Our questions are: 1. Is show table status generally extremely slow for InnoDB tables? 2. Does connection from the local or remote server affect the speed of show table status? 3. Does the fact one server a master another a slave affect the speed of show table status? Your reply would be greatly appreciated! Thank you in advance. Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show table status extremely slow
My experience with innodb is that show table status is slow. It's better to do show table status like 'my_table' -Eric On Thu, 10 Feb 2005 16:47:41 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, We have a huge database (84 tables of about 360 G of data in MyISAM tables). Recently, we converted the entire database to InnoDB (in one table space) and set up replication. Then we experienced some slower performance. For example, show table status on the master took more than 90 seconds if the database connection was made from local server, and terribly 400 seconds if the connection was from a remote server. At the moment show table status was issued, there were about 5 other queries running. However, if the test was done on the slave, show table status took approximately 90 seconds for each of the connections from the local and remote servers, while only the slave thread was running in the meanwhile. Our questions are: 1. Is show table status generally extremely slow for InnoDB tables? 2. Does connection from the local or remote server affect the speed of show table status? 3. Does the fact one server a master another a slave affect the speed of show table status? Your reply would be greatly appreciated! Thank you in advance. Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show table status
hello everybody, Mysql version:4.1.8 OS:windows XP data_file_path:ibdata1:10M in my.ini file /* which is default value*/ As every one knew the data and index files of all innodb tables are stored in ibdata1(in this case). We created some user defined tables(innodb type).The number of user defined tables does not exceed 10.The size of each table also does not exceed 10 rows. We executed 'show table status' command and it gave innodb freespace:4092 KB. What does that mean?How come system tables and user defined tables(which are not more than 10) occupy 6MB?Does mysql does 10MB ibdata1 into partitions of 6MB and 4MB?If any one knew anything regarding this issue please answer immediately. Thanking you, your's sincerely, sirisha. Yahoo! India Matrimony: Find your life partneronline.
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
Spenser, the bug report was a direct hit in the sense that it spoke about the problem I am having, but it was actually wrong and the suggested FLUSH TABLES workaround did not work. On my servers (4.0.13-nt running on Windows 2000 Pro) FLUSH TABLES had no effect at all on the Update_time. I quit trying to use Update_time to track replication status. Now I do the following: SHOW MASTER STATUS on the master and record the binlog file name and position. SHOW SLAVE STATUS on the slave and record the Master_log_file and Exec_master_log_position. If these match, then I assume replication is up to date. Is this an okay assumption? BTW, I am aware that starting with 4.1.1 there is a Seconds_behind_master field that could be helpful, but our medical application currently only supports up to MySQL 4.0.18. -- Eric Robinson -Original Message- From: Spenser [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 29, 2004 6:23 PM To: Robinson, Eric Cc: Mikael Fridh; mysql@lists.mysql.com Subject: RE: SHOW TABLE STATUS: Update_Time Is Wrong? Eric, I'm glad that last answer worked, but I'm wondering what exactly you did to resolve the problem? I see the bug report and work around. But what specifically did you do, what did you type to fix it? By the way, what operating system are you using for your servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
The time zones are correct. Besides, the difference is not an even hour, and it varies. Sometimes the master server shows an update_time that is only a few minutes different, other times it is 510 seconds or 633 seconds or 6056 seconds different. Eventually it catches up, but it sometimes takes an hour or two. Other times it catches up immediately after an update. I cannot discern a pattern. The only thing I know is that update_time seems to match the .MYD file's timestamp in Windows. -- Eric Robinson -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 28, 2004 8:15 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: SHOW TABLE STATUS: Update_Time Is Wrong? Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS: Update_Time Is Wrong?
Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. The time zones are correct. Besides, the difference is not an even hour, and it varies. Sometimes the master server shows an update_time that is only a few minutes different, other times it is 510 seconds or 633 seconds or 6056 seconds different. Eventually it catches up, but it sometimes takes an hour or two. Other times it catches up immediately after an update. I cannot discern a pattern. The only thing I know is that update_time seems to match the .MYD file's timestamp in Windows. http://bugs.mysql.com/bug.php?id=4164 -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
That looks like a direct hit. Thanks! -- Eric Robinson -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 29, 2004 1:06 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: SHOW TABLE STATUS: Update_Time Is Wrong? Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. The time zones are correct. Besides, the difference is not an even hour, and it varies. Sometimes the master server shows an update_time that is only a few minutes different, other times it is 510 seconds or 633 seconds or 6056 seconds different. Eventually it catches up, but it sometimes takes an hour or two. Other times it catches up immediately after an update. I cannot discern a pattern. The only thing I know is that update_time seems to match the .MYD file's timestamp in Windows. http://bugs.mysql.com/bug.php?id=4164 -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
Eric, I'm glad that last answer worked, but I'm wondering what exactly you did to resolve the problem? I see the bug report and work around. But what specifically did you do, what did you type to fix it? By the way, what operating system are you using for your servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLE STATUS: Update_Time Is Wrong?
When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS: Update_Time Is Wrong?
Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VIEWS and SHOW TABLE STATUS in MySQL 5.0.1
Hi, Currently, a VIEW lists NULL as value for the Engine column in the SHOW TABLE STATUS resultset. Is this the right behaviour? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
Hi all, I just noticed that the MySQL 4.1.latest version handles a SHOW TABLE STATUS different from 4.1.1! Instead of a field Type that holds the table type, it's now Engine. Just a quick question: who makes up these changes in a minor minor (x.x.x) release What do they expect from third party developers? In short: this is a stupid change. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
Hi Jocelyn, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. A mistake then? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I just noticed that the MySQL 4.1.latest version handles a SHOW TABLE STATUS different from 4.1.1! Instead of a field Type that holds the table type, it's now Engine. Just a quick question: who makes up these changes in a minor minor (x.x.x) release What do they expect from third party developers? In short: this is a stupid change. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
In the last episode (Jul 28), Martijn Tonies said: Hi Jocelyn, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. Well, 4.1 is still in Beta, so I don't see any problem with changes like this before it goes Stable. Remember that MySQL 3.x went through 23 minor releases and changed a whole lot more :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
At 14:36 +0200 7/28/04, Martijn Tonies wrote: Hi Jocelyn, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. The current manual does document it: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. A mistake then? A decision you disagree with. It's hard to win on this kind of thing. If we don't make changes, people say development is too slow. If we do, development is said to be arbitrary. Anyway, it's documented now. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I just noticed that the MySQL 4.1.latest version handles a SHOW TABLE STATUS different from 4.1.1! Instead of a field Type that holds the table type, it's now Engine. Just a quick question: who makes up these changes in a minor minor (x.x.x) release What do they expect from third party developers? In short: this is a stupid change. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
Paul, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. The current manual does document it: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html It documents Engine instead of Type, but I cannot find the part about deprecating Type in favor of Engine. Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. A mistake then? A decision you disagree with. A decision that has been taken way too lightly. If you expect third party developers to tune their applications to every minor sub-release, you will have a hard time gaining their thrust and expecting them to support the latest and the greatest. Would you expect us to stay away from 4.1 until it's become stable? It's hard to win on this kind of thing. If we don't make changes, people say development is too slow. If we do, development is said to be arbitrary. Anyway, it's documented now. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show table status problem
Hello, everyone I am new on MySQL. I created several tables by command: %mysql jeandatabase -u root -h localhost -p jean1.sql and jean1.sql is as follows: use jeandatabase; drop table if exists jean1; create table jean1( id int not null, field1 char(9), primary key(id) ); But, when I try to take a look on the table1 status by: mysql show table status from jean1; It gave me the following error: ERROR 12: Can't read dir of './jean1/' (Errcode: 2) I think it supposes that there would be a sub-directry under .../jeandatabase/, and named jean1. But when I cd to /usr/local/mysql/data/jeandatabase/ There are three files: jean1.MYD jean1.MYI jean1.frm I wonder if the way I import the table is not correct. Could anyone give me some ideas about this problem? Thank you very much. Jean __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show table status problem
Jean, Here is the format for the SHOW TABLE STATUS command from http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html: SHOW TABLE STATUS [FROM db_name] [LIKE wild] You are using FROM TABLENAME not your database's name. Try this: SHOW TABLE STATUS FROM jeandatabase and see if it works better ;) Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jean Zhong [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: Fax to: 06/10/2004 11:04 Subject: show table status problem AM Hello, everyone I am new on MySQL. I created several tables by command: %mysql jeandatabase -u root -h localhost -p jean1.sql and jean1.sql is as follows: use jeandatabase; drop table if exists jean1; create table jean1( id int not null, field1 char(9), primary key(id) ); But, when I try to take a look on the table1 status by: mysql show table status from jean1; It gave me the following error: ERROR 12: Can't read dir of './jean1/' (Errcode: 2) I think it supposes that there would be a sub-directry under .../jeandatabase/, and named jean1. But when I cd to /usr/local/mysql/data/jeandatabase/ There are three files: jean1.MYD jean1.MYI jean1.frm I wonder if the way I import the table is not correct. Could anyone give me some ideas about this problem? Thank you very much. Jean __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show table status problem
Jean Zhong wrote: mysql show table status from jean1; It gave me the following error: ERROR 12: Can't read dir of './jean1/' (Errcode: 2) In SHOW TABLE STATUS, the thing after the FROM is a database name. See here: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html You want SHOW TABLE STATUS FROM jeandatabase; or maybe SHOW TABLE STATUS LIKE 'jean'; -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show table status problem
Jean, The from jean1 is looking for a database named jean1, it is not the table name. See http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html If you are already connected to the database, just use: Show Table Status; If you want status for a particular table then try: Show Table Status like jean1; or Show Table Status like jean%; if you have more than 1 table starting with jean. Mike At 10:04 AM 6/10/2004, you wrote: Hello, everyone I am new on MySQL. I created several tables by command: %mysql jeandatabase -u root -h localhost -p jean1.sql and jean1.sql is as follows: use jeandatabase; drop table if exists jean1; create table jean1( id int not null, field1 char(9), primary key(id) ); But, when I try to take a look on the table1 status by: mysql show table status from jean1; It gave me the following error: ERROR 12: Can't read dir of './jean1/' (Errcode: 2) I think it supposes that there would be a sub-directry under .../jeandatabase/, and named jean1. But when I cd to /usr/local/mysql/data/jeandatabase/ There are three files: jean1.MYD jean1.MYI jean1.frm I wonder if the way I import the table is not correct. Could anyone give me some ideas about this problem? Thank you very much. Jean __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show table status problem
Hello, Thank you very much, everyone. Yes, I want to know the table jean1 status. I tried: show table status like jean1 It works. Thanks a lot. Jean --- mos [EMAIL PROTECTED] wrote: Jean, The from jean1 is looking for a database named jean1, it is not the table name. See http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html If you are already connected to the database, just use: Show Table Status; If you want status for a particular table then try: Show Table Status like jean1; or Show Table Status like jean%; if you have more than 1 table starting with jean. Mike At 10:04 AM 6/10/2004, you wrote: Hello, everyone I am new on MySQL. I created several tables by command: %mysql jeandatabase -u root -h localhost -p jean1.sql and jean1.sql is as follows: use jeandatabase; drop table if exists jean1; create table jean1( id int not null, field1 char(9), primary key(id) ); But, when I try to take a look on the table1 status by: mysql show table status from jean1; It gave me the following error: ERROR 12: Can't read dir of './jean1/' (Errcode: 2) I think it supposes that there would be a sub-directry under .../jeandatabase/, and named jean1. But when I cd to /usr/local/mysql/data/jeandatabase/ There are three files: jean1.MYD jean1.MYI jean1.frm I wonder if the way I import the table is not correct. Could anyone give me some ideas about this problem? Thank you very much. Jean __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show table status query
Daniel Kasak wrote: Andrew Barnes wrote: When I run the SHOW TABLE STATUS query against a database with 4 tables, 3 of the tables come have the correct row count, but 1 table changes the No of rows every time I run the query. The correct row count for this table is 313, but the query returns anywhere from 97 to 574. I am running 4.0.13 on MAC OS/X 10.3 and the table is an INNODB table. Has anybody else ever seen this? Regards Andy Yes that's normal behaviour for an InnoDB table. It's in the docs somewhere. Note that the statistics SHOW gives about InnoDB tables are only approximate. They are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though. http://dev.mysql.com/doc/mysql/en/Using_InnoDB_tables.html Regards, Stefan Hinz -- Stefan Hinz [EMAIL PROTECTED] iConnect e-commerce solutions GmbH Taunusstr. 27, 12161 Berlin, Germany -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show table status query
Hi When I run the SHOW TABLE STATUS query against a database with 4 tables, 3 of the tables come have the correct row count, but 1 table changes the No of rows every time I run the query. The correct row count for this table is 313, but the query returns anywhere from 97 to 574. I am running 4.0.13 on MAC OS/X 10.3 and the table is an INNODB table. Has anybody else ever seen this? Regards Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show table status query
Andrew Barnes wrote: Hi When I run the SHOW TABLE STATUS query against a database with 4 tables, 3 of the tables come have the correct row count, but 1 table changes the No of rows every time I run the query. The correct row count for this table is 313, but the query returns anywhere from 97 to 574. I am running 4.0.13 on MAC OS/X 10.3 and the table is an INNODB table. Has anybody else ever seen this? Regards Andy Yes that's normal behaviour for an InnoDB table. It's in the docs somewhere. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create a table from SHOW TABLE STATUS ?
I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Thanks, Richard A. DeVenezia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a table from SHOW TABLE STATUS ?
Richard A. DeVenezia [EMAIL PROTECTED] wrote: I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? No, but you can use output of SHOW CREATE TABLE command. Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a table from SHOW TABLE STATUS ?
On Fri, 2004-04-30 at 06:03, Richard A. DeVenezia wrote: I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? Not from SHOW TABLE STATUS, but you can duplicate a table. From the create table docs (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html): In MySQL 4.1, you can also use LIKE to create a table based on the definition of another table, including any column attributes and indexes the original table has: CREATE TABLE new_tbl LIKE orig_tbl; CREATE TABLE ... LIKE does not copy any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table. Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Thanks, Richard A. DeVenezia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOCK TABLES and SHOW TABLE STATUS FROM - deadlock? (3.23.58)
Hi,
I'm trying to understand LOCKing mechanism, but it is not enough described in the
manual, so I have to test it myself.
To see what happens when I use tables I haven't locked, I created a PHP script like
this:
$sql = USE fakturace; if(!$res =
mysql_query($sql,$spoj)){ decho(' A '.$sError = sql_error(mysql_error(),$sql)); }
$sql = LOCK TABLES $gt_firmy WRITE; if(!$res = mysql_query($sql,$spoj)){ decho('
A '.$sError = sql_error(mysql_error(),$sql)); }
$sql = SELECT * FROM $gt_firmy AS f;if(!$res = mysql_query($sql,$spoj)){
decho(' B '.$sError = sql_error(mysql_error(),$sql)); }
$sql = INSERT INTO test SET id = NULL; if(!$res = mysql_query($sql,$spoj)){
decho(' C '.$sError = sql_error(mysql_error(),$sql)); }
$sql = 'UNLOCK TABLES'; if(!$res = mysql_query($sql,$spoj)){
decho(' Z '.$sError = sql_error(mysql_error(),$sql)); }
The result is expected:
B MySQL error: Table 'f' was not locked with LOCK TABLES
SQL: SELECT * FROM firmy AS f
C MySQL error: Table 'test' was not locked with LOCK TABLES
SQL: INSERT INTO test SET id = NULL
The output doesn't change, until PhpMyAdmin sends
SHOW TABLE STATUS FROM fakturace
This query hangs on the server and no other query to database fakturace can be done.
Is that ok?
That's not all. When I did SHOW PROCESSLIST, I got this:
++---+---+---+-+--+++
| Id | User | Host | db| Command | Time | State | Info
|
++---+---+---+-+--+++
| 1 | fakturace | localhost | fakturace | Sleep | 716 || NULL
|
| 2 | fakturace | localhost | fakturace | Sleep | 606 || NULL
|
| 5 | fakturace | localhost | fakturace | Sleep | 549 || NULL
|
| 7 | fakturace | localhost | fakturace | Sleep | 472 || NULL
|
| 9 | root | localhost | zona3d| Query | 410 | Locked | SHOW TABLE STATUS
FROM `fakturace` |
| 10 | root | localhost | NULL | Query | 47 | Locked | SHOW TABLE STATUS
FROM fakturace |
| 11 | fakturace | localhost | fakturace | Query | 30 | Locked | SELECT * FROM
firmy AS f |
| 12 | root | localhost | NULL | Query | 0| NULL | show processlist
|
++---+---+---+-+--+++
8 rows in set (0.00 sec)
So I killed that threads what locked the table:kill 1; kill 2; kill 5; kill 7;
++---+---+---+-+--+++
| Id | User | Host | db| Command | Time | State | Info
|
++---+---+---+-+--+++
| 1 | fakturace | localhost | fakturace | Killed | 1036 || NULL
|
| 2 | fakturace | localhost | fakturace | Killed | 926 || NULL
|
| 5 | fakturace | localhost | fakturace | Killed | 869 || NULL
|
| 7 | fakturace | localhost | fakturace | Killed | 792 || NULL
|
| 9 | root | localhost | zona3d| Query | 730 | Locked | SHOW TABLE STATUS
FROM `fakturace` |
| 10 | root | localhost | NULL | Query | 367 | Locked | SHOW TABLE STATUS
FROM fakturace |
| 11 | fakturace | localhost | fakturace | Query | 350 | Locked | SELECT * FROM
firmy AS f |
| 12 | root | localhost | NULL | Query | 0| NULL | show processlist
|
++---+---+---+-+--+++
But the queries SHOW TABLE STATUS are still hanging and after a while whole computers
gets stunned, nothingchanges in processlist and the processor runs at 100% (P4 1.5
GHz). While writing this mail, thread id # 11 changed to Reopen tables, then Waiting
for tables, then NULL.
I don't understand it at alll as I am not familiar fwith locking, but i think this
behavior is bad. Should I report this as a bug? I am using 3.23.58 and I DON'T want to
install newer, because 3.x is running on servers I use for hosting.
Thanks, Ondra
no create/update time for InnoDB from SHOW TABLE STATUS?
It looks as though, when I go into my databases, and use the command SHOW TABLE STATUS, that InnoDB tables do not have some information. I am on MySQL 4.1.0-alpha-debug. Has this been fixed in later versions? Example: mysql show table status; +--+++-++- +-+--+---+ +-+-++--- ++-+ | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Charset | Create_options | Comment | +--+++-++- +-+--+---+ +-+-++--- ++-+ | cur | InnoDB | Dynamic| 325214 | 2279 | 741294080 | NULL |114163712 | 0 | 337810 | NULL | NULL| NULL | latin1_swedish_ci | pack_keys=1| InnoDB free: 6601728 kB | | old | InnoDB | Dynamic| 1233005 | 10405 | 12830375936 | NULL |455147520 | 0 |1545059 | NULL | NULL| NULL | latin1_swedish_ci | pack_keys=1| InnoDB free: 6601728 kB | +--+++-++- +-+--+---+ +-+-++--- ++-+ 2 rows in set (2.78 sec) Notice the Create_time and Update_time data is NULL. Any reason? thanx - ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
On Nov 25, 2003, at 1:10 AM, Jozsa Boti wrote: Please reply to the list, not to me personally, so that others can follow this discussion. Thanks. Sorry, Don't feel bad. The list admins could easily set up the list so that the default action when replying is the correct one, but apparently choose not to, based on some purist notion of how email should and should not work. Every list I subscribe to that chooses this route constantly has posts such as this, asking that replies be kept on-list, or other replies asking not to send duplicate posts (the default behavior for Reply All functionality). So as long as the list admins configure the list this way, you have to expect a large number of these sort of problems. It's their choice to fix it or not. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
At 8:10 +0200 11/25/03, Jozsa Boti wrote: Hi! Please reply to the list, not to me personally, so that others can follow this discussion. Thanks. Sorry, No apology required. It's just that others may have something to contribute. I was able to duplicate your results and raised the question with the developers. An inefficiency was identified with memory allocation for this particular situation. I have filed a bug report. You can track its status here: http://bugs.mysql.com/bug.php?id=1952 At 13:21 +0200 11/21/03, Jozsa Boti wrote: Hi! How an i get the last Check-time of a specific table without using LIKE statements? There isn't another way. There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Slow? How many tables are we talking about? I'm talking about nearly 20 000 tables in a database. In this case a SHOW TABLE STATUS commande takes about 20-30 seconds. Hm, that is pretty slow. How long does an ls command in the database directory take? About 1-2 seconds. Boti -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
Hi! Please reply to the list, not to me personally, so that others can follow this discussion. Thanks. At 13:21 +0200 11/21/03, Jozsa Boti wrote: Hi! How an i get the last Check-time of a specific table without using LIKE statements? There isn't another way. There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Slow? How many tables are we talking about? I'm talking about nearly 20 000 tables in a database. In this case a SHOW TABLE STATUS commande takes about 20-30 seconds. Hm, that is pretty slow. How long does an ls command in the database directory take? Thanks, Boti -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
Hi, I have noticed also that SHOW TABLE STATUS became slower in 5 times at least (from 1 sec to 5-6 secs). Since I've switched to version 3.23.53 to version 4.0.14. But I have changed type of all my tables (~30 tables) from MyISAM to InnoDB as well. How long does an ls command in the database directory take? less than 1 sec. Thanks, Mikhail. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Jozsa Boti [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, November 24, 2003 5:36 PM Subject: Re: SHOW TABLE STATUS without LIKE Hi! Please reply to the list, not to me personally, so that others can follow this discussion. Thanks. At 13:21 +0200 11/21/03, Jozsa Boti wrote: Hi! How an i get the last Check-time of a specific table without using LIKE statements? There isn't another way. There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Slow? How many tables are we talking about? I'm talking about nearly 20 000 tables in a database. In this case a SHOW TABLE STATUS commande takes about 20-30 seconds. Hm, that is pretty slow. How long does an ls command in the database directory take? Thanks, Boti -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
Hi! Please reply to the list, not to me personally, so that others can follow this discussion. Thanks. Sorry, At 13:21 +0200 11/21/03, Jozsa Boti wrote: Hi! How an i get the last Check-time of a specific table without using LIKE statements? There isn't another way. There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Slow? How many tables are we talking about? I'm talking about nearly 20 000 tables in a database. In this case a SHOW TABLE STATUS commande takes about 20-30 seconds. Hm, that is pretty slow. How long does an ls command in the database directory take? About 1-2 seconds. Boti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLE STATUS without LIKE
Hi! How an i get the last Check-time of a specific table without using LIKE statements? There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
At 13:21 +0200 11/21/03, Jozsa Boti wrote: Hi! How an i get the last Check-time of a specific table without using LIKE statements? There isn't another way. There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Slow? How many tables are we talking about? Thanks -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug: MySQL 4.0.13 crashes during simultaneous execution ALTER TABLE ... ENABLE KEYS and SHOW TABLE STATUS statements.
Hi all, My MySQL crashed during simultaneous execution of ALTER TABLE ... ENABLE KEYS and SHOW TABLE STATUS statements. There were 2 threads: #3 and #4. In thread #4 I executed: 4 Query DROP TABLE IF EXISTS History 4 Query CREATE TABLE History ... 4 Query ALTER TABLE History DISABLE KEYS 4 Query LOCK TABLES History WRITE 4 Query INSERT INTO History VALUES ... 4 Query ALTER TABLE History ENABLE KEYS In thread #3 I executed: 3 Query SHOW TABLE STATUS And it looks like thread #3 was locked until INSERT INTO History VALUES ... finished. After that SHOW TABLE STATUS statement executed and when Mysql tried to execute ALTER TABLE History ENABLE KEYS it crashed. Below there are error-log and query-log files: == /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.13-standard-log' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 sort_buffer_size=2097144 max_used_connections=3 max_connections=100 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87631a8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe1e958, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80702cb 0x8282488 0x8283a23 0x8280ca4 0x827ed89 0x80d028f 0x80d1636 0x807b487 0x807e166 0x80797ad 0x80791ed 0x8078a0f 0x827fc3c 0x82b53fa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8773ae8 = ALTER TABLE History ENABLE KEYS thd-thread_id=4 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 4 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 030606 09:00:37 mysqld restarted 030606 9:00:37 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1885531442 InnoDB: Doing recovery: scanned up to log sequence number 0 1885549589 030606 9:00:37 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 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 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 45770, file name ./saturn-bin.001 030606 9:00:38 InnoDB: Flushing modified pages from the buffer pool... 030606 9:00:38 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.13-standard-log' socket: '/tmp/mysql.sock' port: 3306 == /usr/local/mysql/bin/mysqld, Version: 4.0.13-standard-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument 4 Query DROP TABLE IF EXISTS History 4 Query CREATE TABLE History (...) TYPE=MyISAM 4 Query ALTER TABLE History DISABLE KEYS 4 Query LOCK TABLES History WRITE 4 Query INSERT INTO History VALUES (...) 3 Query SHOW TABLE STATUS 030606 9:00:37 4 Query ALTER TABLE History ENABLE KEYS /usr/local/mysql/bin/mysqld, Version: 4.0.13-standard-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument == Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error executing 'show variables' and 'show table status from [table]'
hello, I'm running version 4.0.3-beta-max-log on SunOS 5.8 (Generic_108528-15 sun4u sparc SUNW,Sun-Fire-280R) output from mysqlshow -V: mysqlshow Ver 9.4 Distrib 4.0.3-beta, for sun-solaris2.8 (sparc) I'm finding that executing either 'show variables' from a mysql session or 'mysqladmin -variables' from a shell prompt crashes the database. the database then restarts. this appears to be 100% repeatable on my install. Also, I cannot execute 'show table status from [tablename]'. All of my tables happen to be InnoDB tables. However, executing only 'show table status' or 'mysqlshow --status' works fine. any help you all can give wold be appreciated. thanks jeff mathis -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SHOW TABLE STATUS InnoDB Tables
From the manual : Note that the statistics SHOW gives about InnoDB tables are only approximate: they are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though. CB. -Original Message- From: Crercio O. Silva [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 03, 2002 01:51 To: [EMAIL PROTECTED] Subject: BUG: SHOW TABLE STATUS InnoDB Tables Hi, I have just noticed that SHOW TABLE STATUS is returning different number of rows each time you execute the query. This seams to affect only InnoDB Tables (Although I have tested only with ISAM/MyISAM/InnoDB Tables). I'm using MySQL 3.23.49Max on WIN2K. How to repeat the problem: Execute the statement SHOW TABLE STATUS many times. In some times (not all) it will return different number of rows for the same table. Have anyone else experienced this with other versions? PS: SELECT COUNT(*) FROM table is working just fine, but on InnoDB tables with large number of rows (my tables have -+ 30 records each) it take a few seconds to get the results. This is not a big issue for me, but I'd like to confirm that. Thanks, []'s Crercio O. Silva - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BUG: SHOW TABLE STATUS InnoDB Tables
Hi, I have just noticed that SHOW TABLE STATUS is returning different number of rows each time you execute the query. This seams to affect only InnoDB Tables (Although I have tested only with ISAM/MyISAM/InnoDB Tables). I'm using MySQL 3.23.49Max on WIN2K. How to repeat the problem: Execute the statement SHOW TABLE STATUS many times. In some times (not all) it will return different number of rows for the same table. Have anyone else experienced this with other versions? PS: SELECT COUNT(*) FROM table is working just fine, but on InnoDB tables with large number of rows (my tables have -+ 30 records each) it take a few seconds to get the results. This is not a big issue for me, but I'd like to confirm that. Thanks, []'s Crercio O. Silva - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW TABLE STATUS InnoDB Tables
Crecrio, - Original Message - From: Crercio O. Silva [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, July 03, 2002 3:49 AM Subject: BUG: SHOW TABLE STATUS InnoDB Tables Hi, I have just noticed that SHOW TABLE STATUS is returning different number of rows each time you execute the query. This seams to affect only InnoDB Tables (Although I have tested only with ISAM/MyISAM/InnoDB Tables). I'm using MySQL 3.23.49Max on WIN2K. How to repeat the problem: Execute the statement SHOW TABLE STATUS many times. In some times (not all) it will return different number of rows for the same table. Have anyone else experienced this with other versions? this is the documented behavior. For InnoDB tables it returns an estimate of the row count, based on 8 random dives into the clustered index tree. For MyISAM it returns the accurate number, because MyISAM separately keeps and stores the row count of a table. PS: SELECT COUNT(*) FROM table is working just fine, but on InnoDB tables with large number of rows (my tables have -+ 30 records each) it take a few seconds to get the results. This is not a big issue for me, but I'd like to confirm that. Thanks, []'s Crercio O. Silva Best regards, Heikki Innobase Oy (sql database) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: BUG: SHOW TABLE STATUS InnoDB Tables
sql,query At 21:50 -0300 7/2/02, Crercio O. Silva wrote: PS: SELECT COUNT(*) FROM table is working just fine, but on InnoDB tables with large number of rows (my tables have -+ 30 records each) it take a few seconds to get the results. This is not a big issue for me, but I'd like to confirm that. Yes, this will happen with both InnoDB and BDB tables, which requlre a full table scan to evaluate COUNT(*), even with no WHERE clause. MyISAM and ISAM optimize that special case and return almost instantaneously. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DBug Test Signal/Illegal Op received with Show table status comand
hello, i'm using mysql 3.23.51-max on windows 98. It worked fine up until the point where I started using a PHP app that used the command SHOW TABLE STATUS FROM tablename. Everytime this is executed Mysql gives me a dialog window that says Test Signal with an OK button, then I get an illegal op. I can do a regular SHOW STATUS w/o problems as well as other SHOW statements. I have no idea why this particular one is causing problems. I really need to figure this out as the PHP app I use depends on this command it seems. It seems that trying to use the other binaries included in the mysql package (mysqld-max, mysqld-nt, etc.) all but freeze up when I attempt a SHOW TABLE STATUS command; other commands work fine though. With the other binaries I'm not given the small dialog window that says Test Signal. I'm just given the illegal op window. Can anyone help? Thanks brandon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
show table status
Hi, my problem is that when i do a show table status like al% one of my innodb tables (named 'aluno') says to have 255 rows. But, in fact, it have 286 rows, by: select count (*) from aluno; Someone can help me ? __ Quer ter seu próprio endereço na Internet? Garanta já o seu e ainda ganhe cinco e-mails personalizados. DomíniosBOL - http://dominios.bol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
show table status command in MySQL
Hi, For show table status command in MySQL, which field is indicate the table size? Or, Which command is to find out the database size. Thanks in advance mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: show table status command in MySQL
Hi, For show table status command in MySQL, which field is indicate the table size? Or, Which command is to find out the database size. The 'Data_length' field in the SHOW TABLE STATUS output will tell you how big the data is (in bytes). Bear in mind this doesn't give the actual size taken up on disk, because you have your index file and table definition file too. I think the 'Index_length' field will tell you how big your indexes are too, though. Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table timestamps? More specific control of SHOW TABLE STATUS command
Why don't you : select timestampfield from mytable order timestampfield decs limit 1 Jay Lawrence wrote: Atle, your suggestion is for the last time a record was updated. I am interested in the entire table. The closest that I have seen thus far is: SHOW TABLE STATUS The Update_time field is most likely what I am after. However I was hoping to do something more like select Update_time from table(x) status Giving me one value back - the Update_time for table "x" of current database. Perhaps this is a candidate for function extension? Jay You might be able to use this, depending on your needs: from http://www.mysql.com/doc/D/A/DATETIME.html [snip] Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. [/snip] .. Atle On Wed, 14 Feb 2001, Jay Lawrence wrote: Hey all, Is there a way to quickly obtain the last time a table was updated/touched? In my app I am caching queries so long as the table data has not changed. I'd like a quick check to see if a table has changed since the query was first executed. My perusal of documentation plus a few searches on mailing lists has not uncovered this matter - but I could have missed it. TIA, Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table timestamps? More specific control of SHOW TABLE STATUS command
Atle, your suggestion is for the last time a record was updated. I am interested in the entire table. The closest that I have seen thus far is: SHOW TABLE STATUS The Update_time field is most likely what I am after. However I was hoping to do something more like select Update_time from table(x) status Giving me one value back - the Update_time for table "x" of current database. Perhaps this is a candidate for function extension? Jay You might be able to use this, depending on your needs: from http://www.mysql.com/doc/D/A/DATETIME.html [snip] Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. [/snip] .. Atle On Wed, 14 Feb 2001, Jay Lawrence wrote: Hey all, Is there a way to quickly obtain the last time a table was updated/touched? In my app I am caching queries so long as the table data has not changed. I'd like a quick check to see if a table has changed since the query was first executed. My perusal of documentation plus a few searches on mailing lists has not uncovered this matter - but I could have missed it. TIA, Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
