On Thu, Apr 8, 2010 at 9:33 AM, spacemarc wrote:
> 2010/4/7 Carsten Pedersen :
> > 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
2010/4/7 Carsten Pedersen :
> 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
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'
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 M
$ 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
, 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:
>
&
;
> 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
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 ;
It will list all the table information I need however, what I am trying to
figure out is how to
On 3/27/07, Tim Lucia wrote:
> -Original Message-
> From: Maciej Dobrzanski
> Sent: Tuesday, March 27, 2007 6:46 AM
> To: mysql@lists.mysql.com
> Subject: Re: Why doesn't the InnoDB count() match table status?
>
> MyISAM and InnoDB (and there are plenty mor
> -Original Message-
> From: Maciej Dobrzanski [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 27, 2007 6:46 AM
> To: mysql@lists.mysql.com
> Subject: Re: Why doesn't the InnoDB count() match table status?
>
> MyISAM and InnoDB (and there are plenty mor
In news:[EMAIL PROTECTED],
"Daevid Vincent" <[EMAIL PROTECTED]> wrote:
> Because we're a huge enterprise product, with 3 databases of > 200
> tables each.
>
> We are migrating from MYISM to INNODB and keeping track of that value
> isn't something we thought we'd need to do. Plus it seems like
> so
Hi Daevid,
Ugh. How about not going berserk on the public mailing list?
We can understand that you're upset that you didn't read the manual
before starting a MyISAM to InnoDB conversion. You didn't do your
research and now you're being hit by a very simple (and not really all
that unexpecte
On Mon, March 26, 2007 16:21, Daevid Vincent said:
>> You're about 5 years too late for this converation, but I recall it
>
> Really? People have just happily accepted this absurd limitation for
> _five_
> years? Wow.
>
>> having to do with the fact that when you're on a table that supports
>> tran
> You're about 5 years too late for this converation, but I recall it
Really? People have just happily accepted this absurd limitation for _five_
years? Wow.
> having to do with the fact that when you're on a table that supports
> transactions, you don't know exactly how many records a particular
> > Is mySQL planning on fixing this BUG. YES -- it is a BUG. A
> BIG FAT HARRY
> > ONE.
> >
>
> I think you mean 'hairy', not 'harry'. There are no 'harry'
> bugs, apart
LOL! Doh! Yeah. I was so blinded by rage that I forgot my spelling.
> > It's completely stupid that I can't query and
Daevid Vincent wrote:
Is mySQL planning on fixing this BUG. YES -- it is a BUG. A BIG FAT HARRY
ONE.
I think you mean 'hairy', not 'harry'. There are no 'harry' bugs, apart
from that British fool who's in line for the throne.
It's completely stupid that I can't query and get an accurat
ifferent? I could easily parse out the second query which is
> > > REDICULOUSLY faster. BTW, why doesn't mySQL just 'alias' the
> > > first query behind the scenes for us and parse out the count?
> >
> > SHOW TABLE STATUS simply returns a guess based on som
REDICULOUSLY
> faster. BTW, why
> > doesn't mySQL just 'alias' the first query behind the
> scenes for us and
> > parse out the count?
>
> SHOW TABLE STATUS simply returns a guess based on some index dives on
> InnoDB tables, and will actually change every ti
oesn't mySQL just 'alias' the first query behind the scenes for us and
> parse out the count?
SHOW TABLE STATUS simply returns a guess based on some index dives on
InnoDB tables, and will actually change every time you run the command
as it randomly picks different p
nes for us and
parse out the count?
mysql> SELECT COUNT(rsm_report_id) FROM Swordfish.rsm_reports;
+--+
| COUNT(rsm_report_id) |
+--+
| 277456 |
+--+
1 row in set (24.84 sec)
mysql> show table status wh
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
Heikki
thanks for filing that report. You can close it again.
I had a look at the create-table statements for these 3 tables.
As it turns out, the person who initially created those tables had a
create statement like "create table ... comment='InnoDB free: 6144 kB'"
for some tables.
All my
hat does SHOW TABLE STATUS show for other tables?
It shows 2 values for about 3 of 260 tables. So most tables are okay. It
does not seem to depend on table size, as the other tables only have a
few hundred rows.
> Are you using innodb_file_per_table?
Yes.
--
MySQL General Mailing List
Fo
what does SHOW TABLE STATUS show for other tables?
It shows 2 values for about 3 of 260 tables. So most tables are okay. It
does not seem to depend on table size, as the other tables only have a
few hundred rows.
Are you using innodb_file_per_table?
Yes.
--
MySQL General Mailing List
Dominik,
what does SHOW TABLE STATUS show for other tables?
Are you using innodb_file_per_table?
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
I recently deleted about 7.000.000 rows from a table, there are about
4.000.000 left.
So I want to know how much space is free in table space now and execute:
mysql> show table status like "table"\G
*** 1. row ***
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.
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 |
l 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, a
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
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
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 Mailin
Hi,
I've just converted my databases so they are using per_table
tablespaces.
When I do a show table status in that database the Comment line
still shows
the amount free, but I assume this doesn't mean anything now ?? It
doesn't
make sense ?
As innodb_file_per_table m
Hi,
I've just converted my databases so they are using per_table tablespaces.
When I do a show table status in that database the Comment line still shows
the amount free, but I assume this doesn't mean anything now ?? It doesn't
make sense ?
***
Hello all,
Is there a way to change a table's Create_time that is displayed when
doing a `SHOW TABLE STATUS FROM mydb LIKE 'foo'\G`?
I've tried using touch to modify the file dates directly and searched
the online docs with no luck.
The reason I want to do this is becaus
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 36
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
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
lto:[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
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 Mail
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
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
esday, 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 ear
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
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
I knew about COUNT(*) but thanks. I was mostly looking for a way to get
the time stamp of the last update or insert.
I did find a way using an ADODB.Command object. I was able to send the
command SHOW TABLE STATUS LIKE 'TABLENAME';
The results were returned as a recordset.
Have a
Hello.
You may use count(*) for counting rows.
"Robinson, Eric" <[EMAIL PROTECTED]> wrote:
> The mysql>SHOW TABLE STATUS command returns such things as the number or
> rows and time of the last update.=20
>
> Is it possible to get the same infor
The mysql>SHOW TABLE STATUS command returns such things as the number or
rows and time of the last update.
Is it possible to get the same information using a SELECT statement?
FYI, I am writing a script to monitor replication status by comparing
this information between the master and sl
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 Production
d 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 do
ome
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"
ption 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 cas
YPE 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&qu
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
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 databa
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 parti
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/
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 not your database's name. Try this:
SHOW TABLE STATUS FROM jeandatabase
and see if it works b
hen 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 wh
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 que
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
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
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:
&g
"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 mo
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
| 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 | L
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
> >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
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
choo
> >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
> >> >data
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&qu
>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 cas
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
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
I was wondering if anybody wth expereince with other
tables types than MyIsam would be able to fill in a few blanks for me:
I'm trying to find what file extentions are associated with what table types. And
which file types are associated with the Field's listed below of a SHOW TA
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
>Description:
If a thread grabs a lock on a table, the
__unlocked__ tables don't show properly in "show table status". In particular,
almost all fields (except for table name) are returned as NULL.
>How-To-Repeat:
show table status from mysql
ssion
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
day, 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
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 confir
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
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:
Ex
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 illeg
n
the table nears "capacity" (I have it looking at the "Data_length" and
"Index_length" columns of "Show table status like 'tablename'" and
compairing the sum to the max that I've hardcoded)..
But of course this requires that magic number in there
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;
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
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
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:
>
On Thu, 15 Feb 2001, Jay Lawrence wrote:
> Atle, your suggestion is for the last time a record was updated. I am
> interested in the entire table.
A temporary workaround could be select max(timestamp) from sometable.
Jan
-
Be
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
90 matches
Mail list logo