MySQL Forums - Information Schema

Output (no replies)

Rob Gudipudi — Thu, 12 Jul 2012 17:00:35 +0000

Hello,

Can someone give me the output to this

#!/bin/ksh
#Stolen from John Doe 08-Dec-2011
#syntax ./run_show_create_tables.ksh username password
#Socket is hard coded, might have to change it

connections=$(mysql -u$1 -p$2 --socket=/tmp/mysql.sock -e "use
information_schema;select TABLE_SCHEMA,TABLE_NAME from TABLES where
TABLE_SCHEMA not in ('mysql','information_schema') order by 1,2;" |grep -v
TABLE_NAME |awk '{print $1 "." $2}')
for table_name in $connections
do
mysql -u$1 -p$2 -v -e "show create table $table_name\G" >> RPTSERVER3_CREATE_TABLE.tx
done

Thanks!

Innodb warm up (no replies)

jongse park — Thu, 12 Jul 2012 04:49:14 +0000

I confuse whether inforamation_schema has index cardinality for tables in real time.
I read the manual MySQL 5.5 about "warm up".
(http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-many-tables.html)

I think that information_schema has index cardinality for each table after startup. so I think there is no need for "warm up script - select 1 from tables limit 1".

doesn't optimizer check information_schema.tables to get index cardinality in real time?
in the case of Innodb, what does innodb handler check to get index cardinality in real time?

Creating PDM (no replies)

Klara Rafa — Fri, 22 Jun 2012 11:50:08 +0000

I am trying to make a PDM software. I created some assemblies and drawings in SolidWorks, CATIA and AutoCAD. Now, my question is:
How can I connect CAD software (SolidWorks, CATIA and AutoCAD) with the future database which I attend to create in MySQL? More precisely, how to connect SolidWorks and MySQL?
Thanks.

Information Schema shows deleted columns (no replies)

Tom Byars — Fri, 06 Apr 2012 14:11:01 +0000

Hi, I'm using MySQL community server 5.01 & MySQL Query Browser 1.2.15 on windows vista. Any changes I make to a database are not being shown in the information schema.

e.g. if in the query browser I delete a column from a table then

SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='MyTableName'
and table_schema = 'MyDB'

is still showing the deleted column for that table. The only way I've found to correct this is to reboot the computer. Anyone know a fix?

Logging in issue (no replies)

Alicia Haraden — Sun, 26 Feb 2012 06:31:17 +0000

Hi! This is my first time using MySQL and it is for a class so please bare with me. I've downloaded and installed MySQL, logged in as root user, changed my database, created grants and created a user. Now when I try to log on as that user this is all I get...

mysql> mysql -h localhost -u deitel -p
->

What am I doing wrong?

Thanks,

Alicia

Case-sensitivity of table name is broken after an unique index is created. (no replies)

Shigeru Nakagaki — Sat, 18 Feb 2012 06:11:41 +0000

Hello

I added "lower_case_table_names=2" to my.ini (MySQL 5.5, Windows 7 64bit)
And I executed sql files.
Then I found that the case-sensitivity was not kept for some tables.
I tested sql and found the source of this trouble.

DROP TABLE IF EXISTS Hoge;
CREATE TABLE Hoge (
id VARCHAR(32) PRIMARY KEY,
foo VARCHAR(32) NOT NULL,
bar VARCHAR(32) NOT NULL
);
/* at this time, table name is 'Hoge' in INFORMATION_SCHEMA.TABLES. */

CREATE UNIQUE INDEX unique_foobar ON Hoge (foo, bar);
/* then, table name is 'hoge' in INFORMATION_SCHEMA.TABLES. */

Is this a bug? Or there is a workaround?

thanks

Shigeru Nakagaki

Data not stored (no replies)

david paul — Mon, 22 Aug 2011 11:25:36 +0000

I am a blogger user, it is quite strange issue but I really need help. I developed a web page using blogger it is working absolutely fine. When I enter some details as a user it shows “saved”, but when I check my database, I found it empty. I checked everything but couldn’t found the issue. Can anyone help me on this?

Get list of aggregate functions supported (3 replies)

AMAN MANOCHA — Fri, 03 Jun 2011 09:55:20 +0000

Is there any way to get list of aggregate functions supported by using some metadata/query?

copy AUTO_INCREMENT value (no replies)

pierre boutet — Fri, 27 May 2011 13:52:55 +0000

Hello !

I would like to do an sql request to copy the next auto_increment value of one table to another table.

I try :

SET @INCR =(SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'table_a' LIMIT 1);
ALTER TABLE `table_b` AUTO_INCREMENT = @INCR;

but it seems not accept variable in ATLER request;

I try :
UPDATE INFORMATION_SCHEMA.TABLES SET AUTO_INCREMENT = (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'table_a' LIMIT 1) WHERE table_name = 'table_b';

but even root has not permissions to do it.

Is it possible to do such stuff ?

Thanks

Information Schema plugin for Windows (no replies)

Forrai Krisztián — Tue, 17 May 2011 11:44:06 +0000

Hi,

I'm trying to compile an information schema plugin for MySQL 5.5 on Windows using VS2008, based on this article:
http://dev.mysql.com/tech-resources/articles/mysql_i_s_plugins_part1-2.html

I've changed some of the initial includes, to match the include files in MySQL 5.5.
(haad to use insted of and had to additionally include .)

If the plugin only creates an empty table (e.g.:mysql_is_hello_fill_table is empty), everything compiles, link, and install, and I can see the information_shema.mysql_hello table.

But if I try to fill the table, using table->field[0]->store, I get the following linker errors:

Error 7 error LNK2001: unresolved external symbol "__declspec(dllimport) struct charset_info_st * system_charset_info" (__imp_?system_charset_info@@3PAUcharset_info_st@@A) InformationSchemaExample.obj
Error 8 error LNK2001: unresolved external symbol "int __cdecl schema_table_store_record(class THD *,struct TABLE *)" (?schema_table_store_record@@YAHPAVTHD@@PAUTABLE@@@Z) InformationSchemaExample.obj

Where are these external functions defined?
Can anyone direct me to a working information schema example on windows?

thank you,
krisy

return incremntal value (no replies)

deming deng — Thu, 07 Apr 2011 16:29:52 +0000

hi all:

i am looking for help for my problem. is it possible to return a incremental value from a single query?

for example : i have count a number of times a subject appears at a specific time, but i also wanna count it at different interval, and show all the value in one table.

also, how can i set the query like "select*from xx where time_stamp between '2011' and '2012'" to use a change variable of time like "between time and time"

thx in advance

Access information_schema database via ODBC (MySQL 5.5) (no replies)

Jaume G — Fri, 25 Mar 2011 11:07:38 +0000

I want to connect to information_schema database using ODBC in a Access 2003 application.
When I try to link the tables, no tables are show in the table list.
I'm using MySQL 5.5.9
In previous version of MySQL I haven't this problem.

Any help appreciated ;)

Check for existence of non unique index (1 reply)

Stuart Fairful — Thu, 10 Feb 2011 21:02:55 +0000

Is there any way to discover any non unique indices that a table might have from INFORMATION_SCHEMA? There is no row in TABLE_CONSTRAINTS or KEY_COLUMN_USAGE unless the index is unique. Non unique indices are listed by SHOW INDEX but I want to use the result programatically - specifically to create the index if it is not there.

Any ideas anyone?

Regards, Stuart Fairful

importing data dictionaries from xls or csv (no replies)

Troy Perkins — Sun, 30 Jan 2011 07:00:17 +0000

I have data dictionary files that are currently in xls format. Is there an easy way to import these either by cl or phpmyadmin or Workbench?

The format is as follows:

c1, c2, c3, c4, c5, c6, c7
Column number, Column Name, Question Text, Response Code, Response Text, Data Type, Size

I've managed to be able to create tables via import by massaging a csv export into c2 headers for rows with a little awk, but would rather import it all as in the meta as well not just the Column Names.

I've figured out how to add meta comments via cl, does phpmyadmin or Workbench have an import for this?

# CREATE TABLE boo (
# id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'The KEY obviously',

Thank you much for your reply.

Cheers,
Troy

Get list of databases through query? (1 reply)

Crazy Tasty — Thu, 30 Dec 2010 15:48:19 +0000

I'd like to know how to get a list of databases through the use of an SQL query.

The "Show Databases" works from a command line, but I need a query and I'm having problems finding such references on the web.

Thanks folks...

ct

Queries on information schema allocating too much memory. (1 reply)

Nadir Lloret — Tue, 23 Nov 2010 11:18:23 +0000

Hi everybody.

I'm trying to figure out what is allocating so much memory at this situation:

SELECT COUNT(1) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='db_example';

On db_example, we have around ~550 innodb tables with 9 partitions each.
After query execution, I can see 700 Mbytes of memory have been allocated to mysql.

shell> ps axu | grep ^mysql | grep /usr/sbin | awk '{print $6;}'
349036
shell> mysql -uroot -p -e "SELECT COUNT(1) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='db_example'"
+----------+
| COUNT(1) |
+----------+
| 4189 |
+----------+
shell> ps axu | grep ^mysql | grep /usr/sbin | awk '{print $6;}'
1115952

1115952-349036 = 766916 (~700M)

show engine innodb status, discards my first theory... "memory was being used to load tables into data dictionary", but:

"Dictionary cache 65076408 (8851984 + 56224424)"

Dictionary is only about 62 Mbytes.. nothing compared to 700Mbytes used to execute query.

If I run same query again and again, no more memory is allocated, (that was what made me think of data dict)

Could anybody give me a clue on what is memory being used?

SO is Linux Ubuntu 10.4
Mysql version is Percona Server 11.1, based on mysql 5.1.47-rel11.1-log

Thanks in advance

Can stored procedure names have spaces in them? (1 reply)

David Thielen — Fri, 19 Nov 2010 07:59:24 +0000

I am using "show create procedure proc_name" to get the description to then parse that to get the parameters (that appears to be the best way to do that as there is no INFORMATION_SCHEMA.PARAMETERS).

Can a procedure name have spaces in it? If so, what do I surround it with? Is it "show create procedure [proc name]"?

thanks - dave

i have a Armstrong's axioms related question (no replies)

Junaid Najeeb — Thu, 23 Sep 2010 18:55:09 +0000

how can i prove Decomposition rule
If X \to YZ then X \to Y and X \to Z

i need to prove it using the 3 Armstrong's axioms which are:

Axiom of reflexivity
If Y subset X, then X \to Y

Axiom of augmentation
If X \to Y, then XZ \to YZ for any Z

Axiom of transitivity
If X \to Y and Y \to Z , then X \to Z

How to know whether the table was locked or not? (2 replies)

Kasthuri Gandhi — Tue, 29 Jun 2010 08:57:50 +0000

Hi ...

Using this..

lock table tabl_name read;

I locked the table...

Now I need the query to check whether that table was already locked or not ..

Please give some solutionss....

Problem with Information Schema in MySQL 5.1.45-3 (5.0.51a-24+lenny3 works) (1 reply)

Florian Bittner — Wed, 16 Jun 2010 11:58:37 +0000

Hi,

for developement i cloned my php (prado framework) application to a few servers and desktops. Everywhere it works find except on my laptop with sidux (debian sid).

The following query is handled different with my newer mysqld version:

Code:

SELECT CONSTRAINT_NAME as con, COLUMN_NAME as col, REFERENCED_TABLE_SCHEMA as fkschema, REFERENCED_TABLE_NAME as fktable, REFERENCED_COLUMN_NAME as fkcol FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = "status";

With mysqld 5.0.51a-24+lenny3 the response is:

"Empty set (0.00 sec)"

5.1.45-3 says:

"ERROR 1109 (42S02): Unknown table 'status' in information_schema"

Can anyone help on this?

Number of Pages of a Table - Where is? (no replies)

Hodgskin Hods — Tue, 01 Jun 2010 19:04:06 +0000

Hi, I studying Query Optimization and I want to know where MySQL stores (if it does) the number of pages/blocks of a table.

I've found the number of rows in Tables, but I was not able to find the number of pages.

If it its not stored, MySql compute some how? How it estimates the best plans in query optimization?

Thanks in advance, Hodgskin.

Data truncated for column 'TABLE_COMMENT' (5 replies)

- - — Fri, 28 May 2010 11:15:57 +0000

Hi,

I want to insert a record using the Auto_increment value read from information_schema database. It's the only way I found to insert twice the auto_increment. However, a warning is thrown about data truncation on information_schema.TABLES.TABLE_COMMENT column.

Test case:
(MySQL 5.0, sql_mode='')

CREATE TABLE sample (
id int(11) NOT NULL auto_increment,
name varchar(100),
link_id int(11) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT link_id_constraint FOREIGN KEY (link_id) REFERENCES sample (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO sample(name, link_id)
VALUES('x',(SELECT Auto_increment FROM information_schema.tables WHERE table_schema=SCHEMA() AND table_name='sample'));

Warning is:
Warning Code : 1265
Data truncated for column 'TABLE_COMMENT' at row 1

Value of TABLE_COMMENT column for this table is: "InnoDB free: 191488 kB; (`link_id`) REFER `ks_dbsynctest1/sample`(`id`) ON UPDAT"

If I remove "ON DELETE CASCADE ON UPDATE CASCADE" on CONSTRAINT creation script, all works since TABLE_COMMENT is shorter.

My question is: why do I get this error whereas I don't touch TABLE_COMMENT value ?

Is there a way to get rid of this warning ?

Subsidiary question: is there another way to insert the auto_increment twice ?

Thanks

Sylvain

MySQL Performance Schema (Worklog #2360) (no replies)

Edwin DeSouza — Fri, 26 Mar 2010 05:23:56 +0000

MySQL Performance Schema (Worklog #2360)

It monitors multitudinous low-level server events and provides them in tables inside a new “database” called, surprisingly, PERFORMANCE_SCHEMA.

Docs:
http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
http://dev.mysql.com/doc/performance-schema/en/index.html

Blogs:
http://marcalff.blogspot.com/2010/01/performance-schema-overview.html
http://www.markleith.co.uk/?p=318
http://www.markleith.co.uk/?p=112

http://blogs.mysql.com/peterg/2009/02/05/mysql-performance-schema/
http://blogs.mysql.com/peterg/2009/02/06/mysql-performance-schema-2/
http://blogs.mysql.com/peterg/2009/02/09/mysql-performance-schema-3/
http://blogs.mysql.com/peterg/2009/02/10/mysql-performance-schema-4/
http://blogs.mysql.com/peterg/2009/02/11/mysql-performance-schema-5/
http://blogs.mysql.com/peterg/2009/02/12/mysql-performance-schema-6/
http://blogs.mysql.com/peterg/2009/02/13/todo-mysql-performance-schema-7/

WL#2360: Performance Schema
http://forge.mysql.com/worklog/task.php?id=2360

WL#4674 PERFORMANCE_SCHEMA Setup For Actors
http://forge.mysql.com/worklog/task.php?id=4674

WL#4895 PERFORMANCE_SCHEMA Instrumenting Table IO
http://forge.mysql.com/worklog/task.php?id=4674

WL#4896 PERFORMANCE_SCHEMA Instrumenting Net IO
http://forge.mysql.com/worklog/task.php?id=4896

WL#4878 PERFORMANCE_SCHEMA Trace
http://forge.mysql.com/worklog/task.php?id=4878

Can't login from localhost if USER/GRANT created using 'username'@'%' (1 reply)

Martin Cloutier — Fri, 29 Jan 2010 14:45:04 +0000

Here is what I do :

Grant Select on *.* to 'User'@'%';

After that I try to login from localhost and I get this error :

Error number 1045
Access denied for user 'User'@'localhost' ...

I tried to login via another computer and it works fine.

I did not have this problem with version 5.1.40.

Now I upgrade to version 5.1.42 and this does not work.

Is this behavior a bug or is my configuration weak ?

Retrieving Database Schema Info (1 reply)

Pratik Kulkarni — Fri, 29 Jan 2010 14:26:12 +0000

Hey, I want to know the steps and the code needed to retrieve the entire database schema of a given mysql database through .net framework using c# as the implementation language. Can anybody help me with the code?

How to identify duplicate indexes (2 replies)

Lucien Jacquet — Thu, 07 Jan 2010 18:58:37 +0000

I am an experienced Oracle DBA, but new (one month) to MySQL, and I have been tasked with identifying duplicate indexes in our database. Here is the structure from one table:

PRIMARY KEY (`accountNum`),
UNIQUE KEY `acceptedProgramId` (`acceptedProgramId`),
UNIQUE KEY `mgageId` (`mgageId`),
UNIQUE KEY `autoId` (`autoId`),
UNIQUE KEY `helocId` (`helocId`),
UNIQUE KEY `hePreRefiId` (`hePreRefiId`),
UNIQUE KEY `origAcctPort` (`originalAccountNumber`,`portfolioId`),
UNIQUE KEY `ctAccountId` (`ctAccountId`),
UNIQUE KEY `accountDetailId` (`accountDetailId`),
KEY `FK1D0C220DB6EE652` (`agentId`),
KEY `FK1D0C220D250E40CC` (`agencyQueueId`),
KEY `FK1D0C220D6F9D1D1B` (`helocId`),
KEY `FK1D0C220D7062C296` (`claimAgentId`),
KEY `FK1D0C220DF5C3DF5F` (`defaultResponsibleId`),
KEY `FK1D0C220DCBD03E64` (`portfolioId`),
KEY `FK1D0C220D799222BA` (`agentQueueId`),
KEY `FK1D0C220DA2B8AD17` (`teamQueueId`),
KEY `FK1D0C220D8C14235C` (`divisionId`),
KEY `FK1D0C220D21D446FF` (`autoId`),
KEY `FK1D0C220D777A2B5D` (`acceptedProgramId`),
KEY `FK1D0C220DCDB05A99` (`mgageId`),
KEY `FK1D0C220D164CC611` (`hePreRefiId`),
KEY `FK1D0C220D916BE7D8` (`ctAccountId`),
KEY `FK1D0C220D15472F70` (`accountDetailId`)

If you look closely, you will notice that the following pairs of indexes use the same column; except that one uses the UNIQUE constraint, they are identical:
UNIQUE KEY `acceptedProgramId` (`acceptedProgramId`) = KEY `FK1D0C220D777A2B5D` (`acceptedProgramId`)
UNIQUE KEY `mortgageId` (`mgageId`) = KEY `FK1D0C220DCDB05A99` (`mgageId`)
UNIQUE KEY `autoId` (`autoId`) = KEY `FK1D0C220D21D446FF` (`autoId`)
UNIQUE KEY `helocId` (`helocId`) = KEY `FK1D0C220D6F9D1D1B` (`helocId`)
UNIQUE KEY `hePreRefiId` (`hePreRefiId`) = KEY `FK1D0C220D164CC611` (`hePreRefiId`)
UNIQUE KEY `citiAccountId` (`ctAccountId`) = KEY `FK1D0C220D916BE7D8` (`ctAccountId`)
UNIQUE KEY `accountDetailId` (`accountDetailId`) = KEY `FK1D0C220D15472F70` (`accountDetailId`)

That was fairly easy to do on this one table, but the database has 150 tables, and I want to write a script of some type to identify the duplicate pairs (triplets, etc.) across the entire database. I have tried several times to write something myself that uses the information in the KEY_COLUMN_USAGE and COLUMNS tables, but I cannot seem to get what I need.

Anybody have something that would work for me? Even something that would just list all indexes for all tables would be a good start; somehow I can't even seem to get that correctly from INFORMATION_SCHEMA. I can figure out how to identify the duplicates from there.

Trigger on information_schema.routines (4 replies)

Michal Koba — Wed, 24 Mar 2010 12:45:42 +0000

I need to develop functionality based on changes in information_schema.routines. But - when I try to add classic trigger on then object MySQL give me following error: Access denied for user 'root'@'%' do database 'information_schema'.

EDIT: After reading some other topics on this forum I learned that information_schema.routines is View type object.

Is there any chance to hook somewhere with trigger to catch changes in information_schema database ?

Workaround for slow information schema joins (no replies)

Willem Isserd — Wed, 09 Dec 2009 00:33:54 +0000

On my search why joins with information schema are slow, especially when having many tables, I came up with a solution which I'd like to share with you.

In my case, the join took several seconds (6-8) to execute on a heavily optimized server, which I managed to bring down to about half a second, which is more acceptable for a web-app.

Purpose of the join was to fetch data from a particular table and fieldname, nullable, datatype and especially ordinal position from i_s.COLUMNS for writing to a XML file.

The solution is to create partitioned views of the information you need from i_s. With partition I mean, on a database basis.

In my case I needed info on TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE AND DATA_TYPE.

Here's the code for view `vw_i_s_example` for database 'example'.
Substitute user `example`@`example.com`, view-name `partitioned_views`.`vw_i_s_example` and TABLE_SCHEMA accordingly.

CREATE ALGORITHM=TEMPTABLE
DEFINER=`example`@`example.com`
SQL SECURITY DEFINER
VIEW `partitioned_views`.`vw_i_s_example` AS
select `COLUMNS`.`TABLE_NAME` AS `TABLE_NAME`,
`COLUMNS`.`COLUMN_NAME` AS `COLUMN_NAME`,
`COLUMNS`.`ORDINAL_POSITION` AS `ORDINAL_POSITION`,
`COLUMNS`.`IS_NULLABLE` AS `IS_NULLABLE`,
`COLUMNS`.`DATA_TYPE` AS `DATA_TYPE`
from `information_schema`.`COLUMNS` where
(`COLUMNS`.`TABLE_SCHEMA` = 'example') order by `COLUMNS`.`TABLE_NAME`,`COLUMNS`.`ORDINAL_POSITION`;

For best performance, I choose a TEMPTABLE view.

HTH,
Willem

CRITICAL - cannot connect to information_schema. Access denied for user @x.x.x.x (using password: YES) (no replies)

Saju Murali — Mon, 05 Oct 2009 10:07:56 +0000

Hi all......
I am new here. I configured Nagios and installed check_mysql_health to monitor mysql and made appropriate changes in the nagios configuration files also. But when I type the command in /usr/local/nagios/libexec/
./check_mysql_health -H --user --password --mode
I am getting an error: CRITICAL - cannot connect to information_schema. Access denied for user @X.X.X.X (using password: YES). I had given all privileges to the user (grant all privileges on *.* to @;)

But I can connect to login to the mysql using the same username and password

(# mysql -u -p
enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 160
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> )

Hope you can understand the things.
Kindly give me a solution for this..
Thanks for all in advance........

Saju Murali

Identifying FK constraints in a table (1 reply)

Alexander Ratz — Sun, 11 Oct 2009 11:54:40 +0000

Hello,

I have tried searching for a solution but cannot find it.

I would like to know about a table FK constraints, meaning i would like to identify which column are a FK.

I am aware of the "SHOW INDEX FROM [TABLE_NAME]" and looking for something similar for FKs.

Are FKs considered indexes as well in this regard ?

Thank you in advance,

Alexander