using column comment to store metadata
Hi, I'm thinking of using the COMMENT of columns when creating tables to store some metadata about the column, for instance: valid_range of numbers, validation regexp, etc. I did some tests with including this information as a JSON (JavaScript Object Notation) string which can then be easily decoded into an actual data structure for the scripting language (Python in my case) being used. The setup seems to work well. One shortcoming is that the maximum size of a COMMENT is 256 characters. Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? I am currently using mysql 5.0.27-standard-log. Thanks in advance for your comments, Cheers, Ezequiel -- Ezequiel Panepucci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
Hi, I'm thinking of using the COMMENT of columns when creating tables to store some metadata about the column, for instance: valid_range of numbers, validation regexp, etc. I did some tests with including this information as a JSON (JavaScript Object Notation) string which can then be easily decoded into an actual data structure for the scripting language (Python in my case) being used. The setup seems to work well. One shortcoming is that the maximum size of a COMMENT is 256 characters. Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? Yes, in different storage engines, different stuff is returned. For example, in InnoDB, what you get returned from SHOW FULL COLUMNS is not what you stored in the Comment part. In short, I would say that the use of COMMENT isn't what it actually does. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ 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: using column comment to store metadata
On 9/17/07, Martijn Tonies [EMAIL PROTECTED] wrote: Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? Yes, in different storage engines, different stuff is returned. For example, in InnoDB, what you get returned from SHOW FULL COLUMNS is not what you stored in the Comment part. I currently use MyISAM, but I just tried a simple create(InnoDB)/show full columns from/ and it actually does return the JSON strings I put in. In short, I would say that the use of COMMENT isn't what it actually does. No arguing here. Thanks for you comment. Ezequiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? Yes, in different storage engines, different stuff is returned. For example, in InnoDB, what you get returned from SHOW FULL COLUMNS is not what you stored in the Comment part. I currently use MyISAM, but I just tried a simple create(InnoDB)/show full columns from/ and it actually does return the JSON strings I put in. Try it on columns with a foreign key constraint. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ 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: using column comment to store metadata
I currently use MyISAM, but I just tried a simple
create(InnoDB)/show full columns from/ and
it actually does return the JSON strings I put in.
Try it on columns with a foreign key constraint.
I did and it still works (maybe the behaviour is version
dependent?).
Here is what I did:
CREATE TABLE parent (
id INT NOT NULL comment '{com: the primary key of the parent.}',
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT comment '{com: the primary key}',
parent_id INT comment '{com: the foreing key}',
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
mysql show full columns from parent;
+---+-+---+--+-+-+---+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
Privileges | Comment
|
+---+-+---+--+-+-+---+-+---+
| id| int(11) | NULL | NO | PRI | | |
select,insert,update,references | {com: the primary key of the
parent.} |
+---+-+---+--+-+-+---+-+---+
1 row in set (0.00 sec)
mysql show full columns from child;
+---+-+---+--+-+-+---+-++
| Field | Type| Collation | Null | Key | Default | Extra |
Privileges | Comment|
+---+-+---+--+-+-+---+-++
| id| int(11) | NULL | YES | | NULL| |
select,insert,update,references | {com: the primary key} |
| parent_id | int(11) | NULL | YES | MUL | NULL| |
select,insert,update,references | {com: the foreing key} |
+---+-+---+--+-+-+---+-++
2 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
I currently use MyISAM, but I just tried a simple create(InnoDB)/show full columns from/ and it actually does return the JSON strings I put in. Try it on columns with a foreign key constraint. I did and it still works (maybe the behaviour is version dependent?). Hmm, could be. I remember all sorts of information being returned in the COMMENTS section, all that was different from my actual comment :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ 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: REVOKE ERROR
On 9/16/07, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi Baron, Still i am able to connect through the following command. mysql -h 192.168.1.10 -u test -p test although drop user can be used to drop the user but revoke command should work properly. I don't understand what is the issue. From the manual: http://dev.mysql.com/doc/refman/5.0/en/revoke.html REVOKE removes privileges, but does not drop user table entries. You must do that explicitly using DELETE or DROP USER (see Section 12.5.1.2, DROP USERSyntaxhttp://dev.mysql.com/doc/refman/5.0/en/drop-user.html ). Revoking all of a user's permissions takes everything but 'usage', which still allows them to connect to the database, though they can't do anything once they connect. -- Check out the Dallas Music Wiki http://www.digitaldarkness.com
Generating reports/exporting data?
Can anyone recommend an easy, free way to generate reports from a MySQL database in a nicely readable form? e.g. in plain text, HTML, PDF, or even visualised in the form of graphs and charts. For example, if I have constructed several queries and want to run them and export all of their results to a file. Or I have a query that runs over one row of a table by specifying a specific primary key value in the query and I want to run it on each row of the table by incrementing the primary key and then compute totals and averages on the results. In general I want to be able to run lots of queries, do simple calculations on their results, and display all these results alongside each other in one file. I'm aware that the mysql client can run in batch mode and has HTML and XML output options, but this is a little too simple for what I want. Currently I'm using Python's MySQLdb module and rolling my own scripts, but I was hoping there would be something a little faster. I started writing Python scripts to take user input from the keyboard and pass it to mysql, but then I discovered mysqlimport which was much quicker, so I'm hoping something similar exists for outputting reports. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock is Always Free
Hi,
John Comerford wrote:
Hi Folks,
My problem is that the IS_FREE_LOCK always returns true.
I have done some testing with two instances of the Query Browser
running. In one I execute:
SELECT GET_LOCK('testing',10)
1
In the second I execute :
SELECT IS_FREE_LOCK('testing');
1
Maybe I have misinterpreted the scope of the lock functions, my
understanding was that the GET_LOCK should create a lock and the lock
should remain until a RELEASE_LOCK command is issued or the process that
created it terminates, is this correct ? If this is true anybody got
any ideas why the IS_FREE_LOCK is always true ?
I am trying with the mysql command-line client and having no trouble:
mysql SELECT GET_LOCK('testing',10) ;
++
| GET_LOCK('testing',10) |
++
| 1 |
++
1 row in set (0.00 sec)
In another terminal,
mysql SELECT IS_FREE_LOCK('testing');
+-+
| IS_FREE_LOCK('testing') |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
Looks fine. So I suspect what's really happening is Query Browser is
closing its connection, or something like that.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large files on dif
In using drbd 8.0.5 recently, I have come across at least two instances where a bit on disk apparently flipped spontaneously in the ext3 metadata on volumes running on top of drbd. Also, I have been seeing regular corruption of a mysql database, which runs on top of drbd, and when I reported this as a bug since I also recently upgraded mysql versions, they question whether drbd could be responsible! All the volumes have been fscked recently and there were no reported errors. And, of course, there have been no errors reported from the underlying hardware. I have since upgraded to 8.0.6, but it's too early to say whether there is a change. I'm also seeing the backup server complain of not being files not comparing, though this may be a separate problem on the backup server. The ext-3 bit flipping: At 12:00 PM -0400 9/11/07, [EMAIL PROTECTED] wrote: I have come across two files, essentially untouched in years, on two different ext3 filesystems on the same server, Gentoo AMD 64-bit with kernel 2.6.22 and fsck version 1.40.2 currently, spontaneously becoming supremely large: Filesystem one Inode 16257874, i_size is 18014398562775391, should be 53297152 Filesystem two Inode 2121855, i_size is 35184386120704, should be 14032896. Both were discovered during an ordinary backup operation (via EMC Insiginia's Retrospect Linux client). The backup runs daily and so one day, one file must have grew spontaneously to this size and then on another day, it happened to the second file, which is on a second filesystem. The backup attempt generated repeated errors: EXT3-fs warning (device dm-2): ext3_block_to_path: block big Both filesystems are running on different logical volumes, but underlying that is are drbd network raid devices and underlying that is a RAID 6-based SATA disk array. The answer to the bug report regarding mysql data corruption, who is blaming drbd! http://bugs.mysql.com/?id=31038 Updated by: Heikki Tuuri Reported by: Maurice Volaski Category:Server: InnoDB Severity:S2 (Serious) Status: Open Version: 5.0.48 OS: Linux OS Details: Gentoo Tags:database page corruption locking up corrupt doublewrite [17 Sep 18:49] Heikki Tuuri Maurice, my first guess is to suspect the RAID-1 driver. My initial report of mysql data corruption: A 64-bit Gentoo Linux box had just been upgraded from MySQL 4.1 to5.0.44 fresh (by dumping in 4.1 and restoring in 5.0.44) and almostimmediately after that, during which time the database was not used,a crash occurred during a scripted mysqldump. So I restored and dayslater, it happened again. The crash details seem to be trying tosuggest some other aspect of the operating system, even the memoryor disk is flipping a bit. Or could I be running into a bug in thisversion of MySQL? Here's the output of the crash --- InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 533. InnoDB: You may have to recover from a backup. 070827 3:10:04 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex [dump itself deleted forbrevity] ;InnoDB: End of page dump 070827 3:10:04 InnoDB: Page checksum 646563254,prior-to-4.0.14-form checksum 2415947328 InnoDB: stored checksum 4187530870, prior-to-4.0.14-form storedchecksum 2415947328 InnoDB: Page lsn 0 4409041, low 4 bytes of lsn at page end 4409041 InnoDB: Page number (if stored to page already) 533, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 35 InnoDB: (index PRIMARY of table elegance/image) InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 533. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also InnoDB:http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with external connections
Hey guys, I'm using SSL encrypted MySQL connections between my Servers. Suddenly today, the connection to one of my servers stopped working. When I try to connect from the outside, I enter the password, and then nothing happens, it just stays blank: # mysql -h beta.kilu.de -p Enter password: Internal connections work fine, and the outside connections also worked like a charm until now. I didn't change anything in my config! And SSL is not the problem either (works fine with the same certificates on the other servers). When I restart MySQL it accepts outside connections for a moment, but after arround 2 minutes, it doesn't answer again! Where could the problem be and what Infos should I obtain (nothing occurs in syslog) ? Any help is appreciated! Regards, Samy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large files on
On Sep 17, 2007 13:31 -0400, Maurice Volaski wrote: In using drbd 8.0.5 recently, I have come across at least two instances where a bit on disk apparently flipped spontaneously in the ext3 metadata on volumes running on top of drbd. Also, I have been seeing regular corruption of a mysql database, which runs on top of drbd, and when I reported this as a bug since I also recently upgraded mysql versions, they question whether drbd could be responsible! Seems unlikely - more likely to be RAM or similar (would include cable for PATA/SCSI but that is less likely an issue for SATA). Shouldn't trip the ECC and produce machine check exceptions and ones that were unrecoverable? The disks are part of hardware RAID with a SATA II cableless backplane and SATA-SCSI controller, so there is a SCSI cable and SCSI HBA (LSI Logic). -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large files on
Hi Maurice,
If you're running into corruption both in ext3 metadata and in MySQL
data, it is certainly not he fault of MySQL as you're likely aware.
There are absolutely many places where corruption could occur between
MySQL and the physical bits on disk. The corruption you're seeing does
not appear to be just flipped bits, although I guess any corruption
could be called that. If you compare the two i_sizes you see from below:
Inode 16257874, i_size is 18014398562775391, should be 53297152
53297152:
0011 0010 1101 0100
18014398562775391:
0100
0011 0010 1101 0011 0001 0101
Differences: 10 x 0-1, 1 x 1-0.
Inode 2121855, i_size is 35184386120704, should be 14032896.
14032896:
1101 0110 0010
35184386120704:
0010
1101 0110 0001 1100
Differences: 4 x 0-1, 1 x 1-0
You can see that there are in fact many bits flipped in each. I would
suspect higher-level corruption than the actual disks (typical single
bit or double bit flips, and generally 1-0 only) but lower than the OS
(typical entire page corruptions of 4k-64k).
That leaves network, SATA controller, various system buses, and possibly
stupid errors in DRBD (although I'd call this unlikely).
Do note that data on e.g. the PCI bus is not protected by any sort of
checksum. I've seen this cause corruption problems with PCI risers and
RAID cards. Are you using a PCI riser card? Note that LSI does *not*
certify their cards to be used on risers if you are custom building a
machine.
Regards,
Jeremy
Maurice Volaski wrote:
In using drbd 8.0.5 recently, I have come across at least two
instances where a bit on disk apparently flipped spontaneously in the
ext3 metadata on volumes running on top of drbd.
Also, I have been seeing regular corruption of a mysql database,
which runs on top of drbd, and when I reported this as a bug since I
also recently upgraded mysql versions, they question whether drbd
could be responsible!
All the volumes have been fscked recently and there were no reported
errors. And, of course, there have been no errors reported from the
underlying hardware.
I have since upgraded to 8.0.6, but it's too early to say whether
there is a change.
I'm also seeing the backup server complain of not being files not
comparing, though this may be a separate problem on the backup server.
The ext-3 bit flipping:
At 12:00 PM -0400 9/11/07, [EMAIL PROTECTED] wrote:
I have come across two files, essentially untouched in years, on two
different ext3 filesystems on the same server, Gentoo AMD 64-bit with
kernel 2.6.22 and fsck version 1.40.2 currently, spontaneously
becoming supremely large:
Filesystem one
Inode 16257874, i_size is 18014398562775391, should be 53297152
Filesystem two
Inode 2121855, i_size is 35184386120704, should be 14032896.
Both were discovered during an ordinary backup operation (via EMC
Insiginia's Retrospect Linux client).
The backup runs daily and so one day, one file must have grew
spontaneously to this size and then on another day, it happened to
the second file, which is on a second filesystem. The backup attempt
generated repeated errors:
EXT3-fs warning (device dm-2): ext3_block_to_path: block big
Both filesystems are running on different logical volumes, but
underlying that is are drbd network raid devices and underlying that
is a RAID 6-based SATA disk array.
The answer to the bug report regarding mysql data corruption, who is
blaming drbd!
http://bugs.mysql.com/?id=31038
Updated by: Heikki Tuuri
Reported by: Maurice Volaski
Category:Server: InnoDB
Severity:S2 (Serious)
Status: Open
Version: 5.0.48
OS: Linux
OS Details: Gentoo
Tags:database page corruption locking up corrupt doublewrite
[17 Sep 18:49] Heikki Tuuri
Maurice, my first guess is to suspect the RAID-1 driver.
My initial report of mysql data corruption:
A 64-bit Gentoo Linux box had just been upgraded from MySQL 4.1
to5.0.44 fresh (by dumping in 4.1 and restoring in 5.0.44) and
almostimmediately after that, during which time the database was
not used,a crash occurred during a scripted mysqldump. So I
restored and dayslater, it happened again. The crash details seem
to be trying tosuggest some other aspect of the operating system,
even the memoryor disk is flipping a bit. Or could I be running
into a bug in thisversion of MySQL?
Here's the output of the crash
---
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 533.
InnoDB: You may have to recover from a backup.
070827 3:10:04 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
[dump itself deleted
forbrevity]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large files on
Hi Maurice, Do you mean a Serially-Attached SCSI aka SAS controller, I assume? Is this a custom build machine or a vendor integrated one? Regards, Jeremy Maurice Volaski wrote: On Sep 17, 2007 13:31 -0400, Maurice Volaski wrote: In using drbd 8.0.5 recently, I have come across at least two instances where a bit on disk apparently flipped spontaneously in the ext3 metadata on volumes running on top of drbd. Also, I have been seeing regular corruption of a mysql database, which runs on top of drbd, and when I reported this as a bug since I also recently upgraded mysql versions, they question whether drbd could be responsible! Seems unlikely - more likely to be RAM or similar (would include cable for PATA/SCSI but that is less likely an issue for SATA). Shouldn't trip the ECC and produce machine check exceptions and ones that were unrecoverable? The disks are part of hardware RAID with a SATA II cableless backplane and SATA-SCSI controller, so there is a SCSI cable and SCSI HBA (LSI Logic). -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large files
Hi Maurice, If you're running into corruption both in ext3 metadata and in MySQL data, it is certainly not he fault of MySQL as you're likely aware. I am hoping they are not related. The problems with MySQL surfaced almost immediately after upgrading to 5.0.x. [details deleted] You can see that there are in fact many bits flipped in each. I would suspect higher-level corruption than I initially thought this as well, but the explanation on the ext3 mailing list is that it really is just a lone flipped bit in both instances. The other differences are due to fsck padding out the block when it guesses what the correct size is. Do note that data on e.g. the PCI bus is not protected by any sort of checksum. I've seen this cause corruption problems with PCI risers and RAID cards. Are you using a PCI riser card? Note that LSI does *not* certify their cards to be used on risers if you are custom building a machine. Yes, there is a riser card. Wouldn't this imply that LSI is saying you can't use a 1U or a 2U box? It's kind of scary there is no end-to-end parity implemented somewhere along the whole data path to prevent this. It sort of defeats the point of RAID 6 and ECC. How did you determine this was the cause? Do you mean a Serially-Attached SCSI aka SAS controller, I assume? No, it's SATA to SCSI. Is this a custom build machine or a vendor integrated one? It is custom-built. Maurice Volaski wrote: In using drbd 8.0.5 recently, I have come across at least two instances where a bit on disk apparently flipped spontaneously in the ext3 metadata on volumes running on top of drbd. Also, I have been seeing regular corruption of a mysql database, which runs on top of drbd, and when I reported this as a bug since I also recently upgraded mysql versions, they question whether drbd could be responsible! All the volumes have been fscked recently and there were no reported errors. And, of course, there have been no errors reported from the underlying hardware. I have since upgraded to 8.0.6, but it's too early to say whether there is a change. I'm also seeing the backup server complain of not being files not comparing, though this may be a separate problem on the backup server. The ext-3 bit flipping: At 12:00 PM -0400 9/11/07, [EMAIL PROTECTED] wrote: I have come across two files, essentially untouched in years, on two different ext3 filesystems on the same server, Gentoo AMD 64-bit with kernel 2.6.22 and fsck version 1.40.2 currently, spontaneously becoming supremely large: Filesystem one Inode 16257874, i_size is 18014398562775391, should be 53297152 Filesystem two Inode 2121855, i_size is 35184386120704, should be 14032896. Both were discovered during an ordinary backup operation (via EMC Insiginia's Retrospect Linux client). The backup runs daily and so one day, one file must have grew spontaneously to this size and then on another day, it happened to the second file, which is on a second filesystem. The backup attempt generated repeated errors: EXT3-fs warning (device dm-2): ext3_block_to_path: block big Both filesystems are running on different logical volumes, but underlying that is are drbd network raid devices and underlying that is a RAID 6-based SATA disk array. The answer to the bug report regarding mysql data corruption, who is blaming drbd! http://bugs.mysql.com/?id=31038 Updated by: Heikki Tuuri Reported by: Maurice Volaski Category:Server: InnoDB Severity:S2 (Serious) Status: Open Version: 5.0.48 OS: Linux OS Details: Gentoo Tags:database page corruption locking up corrupt doublewrite [17 Sep 18:49] Heikki Tuuri Maurice, my first guess is to suspect the RAID-1 driver. My initial report of mysql data corruption: A 64-bit Gentoo Linux box had just been upgraded from MySQL 4.1 to5.0.44 fresh (by dumping in 4.1 and restoring in 5.0.44) and almostimmediately after that, during which time the database was not used,a crash occurred during a scripted mysqldump. So I restored and dayslater, it happened again. The crash details seem to be trying tosuggest some other aspect of the operating system, even the memoryor disk is flipping a bit. Or could I be running into a bug in thisversion of MySQL? Here's the output of the crash --- InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 533. InnoDB: You may have to recover from a backup. 070827 3:10:04 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex [dump itself deleted forbrevity] ;InnoDB: End of page dump 070827 3:10:04 InnoDB: Page checksum
RE: Generating reports/exporting data?
It's not free in that you have to buy the product, but it is free in that you make ONE report and get multiple export modules that you don't have to write yourself. That alone is awesome! http://www.jaspersoft.com :) -Original Message- From: chombee [mailto:[EMAIL PROTECTED] Sent: Monday, September 17, 2007 8:29 AM To: mysql@lists.mysql.com Subject: Generating reports/exporting data? Can anyone recommend an easy, free way to generate reports from a MySQL database in a nicely readable form? e.g. in plain text, HTML, PDF, or even visualised in the form of graphs and charts. For example, if I have constructed several queries and want to run them and export all of their results to a file. Or I have a query that runs over one row of a table by specifying a specific primary key value in the query and I want to run it on each row of the table by incrementing the primary key and then compute totals and averages on the results. In general I want to be able to run lots of queries, do simple calculations on their results, and display all these results alongside each other in one file. I'm aware that the mysql client can run in batch mode and has HTML and XML output options, but this is a little too simple for what I want. Currently I'm using Python's MySQLdb module and rolling my own scripts, but I was hoping there would be something a little faster. I started writing Python scripts to take user input from the keyboard and pass it to mysql, but then I discovered mysqlimport which was much quicker, so I'm hoping something similar exists for outputting reports. Thanks -- 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: Generating reports/exporting data?
Jasper does have a commercial offering, but it is still an open source project.. (readers of this list may be familiar with the arrangement) I'm not sure what license they are using these days (used to be GPL) but the web site is a little counter-intuitive. - michael On 9/17/07, Daevid Vincent [EMAIL PROTECTED] wrote: It's not free in that you have to buy the product, but it is free in that you make ONE report and get multiple export modules that you don't have to write yourself. That alone is awesome! http://www.jaspersoft.com :) -Original Message- From: chombee [mailto:[EMAIL PROTECTED] Sent: Monday, September 17, 2007 8:29 AM To: mysql@lists.mysql.com Subject: Generating reports/exporting data? Can anyone recommend an easy, free way to generate reports from a MySQL database in a nicely readable form? e.g. in plain text, HTML, PDF, or even visualised in the form of graphs and charts. For example, if I have constructed several queries and want to run them and export all of their results to a file. Or I have a query that runs over one row of a table by specifying a specific primary key value in the query and I want to run it on each row of the table by incrementing the primary key and then compute totals and averages on the results. In general I want to be able to run lots of queries, do simple calculations on their results, and display all these results alongside each other in one file. I'm aware that the mysql client can run in batch mode and has HTML and XML output options, but this is a little too simple for what I want. Currently I'm using Python's MySQLdb module and rolling my own scripts, but I was hoping there would be something a little faster. I started writing Python scripts to take user input from the keyboard and pass it to mysql, but then I discovered mysqlimport which was much quicker, so I'm hoping something similar exists for outputting reports. Thanks -- 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] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: excessive time spent in statistics status
On Fri, Sep 14, 2007 at 01:33:51AM -0700, Jeremy Cole wrote: Hi Shawn, Lucio, SELECT STRAIGHT_JOIN FROM ... LEFT JOIN ... WHERE ... ... Just to correct a point here... if a query uses only LEFT JOIN or RIGHT JOIN, the join order is fixed by the query's order itself, so using STRAIGHT_JOIN should have no effect whatsoever. True, because you say only left or right joins. Equally important, since the join order is fixed when you use LEFT JOIN, you *must* list the joins in the correct order when writing the query, otherwise you will see very poor performance. Only is missing from here, which could be misleading. MySQL will reorder the t0, t1 and t2 joins in: select ... fromt0 join t1 on ... join t2 on ... left join t3 on ... where ... MySQL's optimizer cannot reorder the joins because it has the potential to change the result of the query. Do have an example in mind? Thanks, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large files
Hi Maurice,
If you're running into corruption both in ext3 metadata and in MySQL
data, it is certainly not he fault of MySQL as you're likely aware.
I am hoping they are not related. The problems with MySQL surfaced
almost immediately after upgrading to 5.0.x.
It's possible that they are not related, but it could even be 5.0
specific but still not a MySQL bug. I.e. MySQL 5.0 could be doing
something that steps on the bug and causes it to occur. But, it's hard
to say anything for sure. Nonetheless, I generally don't bother
worrying about the possibility of MySQL bugs until I'm sure that the OS
and hardware are stable.
You can see that there are in fact many bits flipped in each. I
would suspect higher-level corruption than
I initially thought this as well, but the explanation on the ext3
mailing list is that it really is just a lone flipped bit in both
instances. The other differences are due to fsck padding out the
block when it guesses what the correct size is.
Interesting. Can you forward that mail to me personally, or summarize
for the list? I'd be interested to read the explanation.
Do note that data on e.g. the PCI bus is not protected by any sort
of checksum. I've seen this cause corruption problems with PCI
risers and RAID cards. Are you using a PCI riser card? Note that
LSI does *not* certify their cards to be used on risers if you are
custom building a machine.
Yes, there is a riser card. Wouldn't this imply that LSI is saying
you can't use a 1U or a 2U box?
Kind of. Presumably you would be buying a vendor integrated solution
where they have certified that the riser card and RAID card are
compatible. Presumably. You'll also notice that most vendors are
moving to controllers that aren't PCI{,-E,-X} slot based, and rather
connect directly to a low-profile integrated slot. This removes a few
variables. (And frees up some space.)
It's kind of scary there is no end-to-end parity implemented
somewhere along the whole data path to prevent this. It sort of
defeats the point of RAID 6 and ECC.
I agree, it's pretty damn scary. You can read about the story and the
ensuing discussion here:
http://jcole.us/blog/archives/2006/09/04/on-1u-cases-pci-risers-and-lsi-megaraid/
How did you determine this was the cause?
Isolating lots of variables. The customer in question had a workload
that could reproduce the problem reliably, although not in the same
place or same time to be able to track things down, and not under debug
mode (which likely slowed things down enough to not cause trouble).
I finally suggested that they isolate the riser card as a variable by
plugging it directly into the slot. Since it was a 1U machine, it
required taking the metal frame off the card and leaving the case open
(and hanging out into the datacenter aisle). it could then be shown
that with riser, corruption always occurred, and without the riser,
corruption never occurred.
Obviously, running the machines with cases open and cards plugged in
directly was not an option, so the only other possible option was
chosen: move to all new hardware with integrated RAID. (HP and their
integrated SmartArray/cciss controller was chosen as a vendor in this case.)
Do you mean a Serially-Attached SCSI aka SAS controller, I assume?
No, it's SATA to SCSI.
Interesting. I hadn't heard of such a thing until I just looked it up.
But in any case that adds yet another variable (and a fairly uncommon
one) to the mix.
Regards,
Jeremy
--
high performance mysql consulting
www.provenscaling.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: excessive time spent in statistics status
Just a quick note to add to this conversation: Pete Harlan wrote: On Fri, Sep 14, 2007 at 01:33:51AM -0700, Jeremy Cole wrote: MySQL's optimizer cannot reorder the joins because it has the potential to change the result of the query. Not in all cases. If a LEFT JOIN query also has a WHERE clause that disallows NULL rows from the right-hand table, it will (may? I'm not sure if it always will, but at least some times it will) treat it as an INNER JOIN, and it's therefore a candidate for reordering. I've also seen cases where a LEFT JOIN becomes a CROSS JOIN (http://bugs.mysql.com/bug.php?id=30842). All RIGHT JOIN are rewritten to equivalent LEFT JOIN, so the same optimizations can apply. As a side note, I've really benefited from attending Timour's talk on the optimizer at the conference this spring. His slides are good reading too: http://conferences.oreillynet.com/presentations/mysql07/katchaounov_timour.pdf Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote: Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. The only thing I could think of was possibly rollback would leave open transaction and its read view if you are running in REPEATABLE READ isolation mode, whereas commit begins a new transaction and discards the read view. But I just tested that, and both commands start a new transaction and discard the read view. That's a long way of saying they are functionally equivalent as far as I know, as long as there are no changes to discard. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with external connections
I'm noticing, that the problem lies with my server getting blocked, because of too many bad tries. Could that be, that after some time, this is not shown as an error anymore, and the client just waits there forever? It seems that I'm only getting blocked when the load is high on the server. I'm setting an connection timeout of 10 seconds. It seems, that under high load, some of the connections are dropped, and this causes the problem! I started logging the attempts and they seem to randomly fail (since the same host and password is used every time) ! Does this help a bit? Samuel Vogel schrieb: Hey guys, I'm using SSL encrypted MySQL connections between my Servers. Suddenly today, the connection to one of my servers stopped working. When I try to connect from the outside, I enter the password, and then nothing happens, it just stays blank: # mysql -h beta.kilu.de -p Enter password: Internal connections work fine, and the outside connections also worked like a charm until now. I didn't change anything in my config! And SSL is not the problem either (works fine with the same certificates on the other servers). When I restart MySQL it accepts outside connections for a moment, but after arround 2 minutes, it doesn't answer again! Where could the problem be and what Infos should I obtain (nothing occurs in syslog) ? Any help is appreciated! Regards, Samy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
While it is functionally equivalent I wonder if it the code paths taken
are the same. I suppose for both commit and rollback mysql would have to
look for any pending work, if there were none both would do nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards the
read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as I
know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
While it is functionally equivalent I wonder if it the code paths taken
are the same. I suppose for both commit and rollback mysql would have to
look for any pending work, if there were none both would do nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards the
read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as I
know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback on a Transaction with No Updates
Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large file
I guess I will watch it closely for now and if it trips up again failover to the drbd peer and see what happens there. I suppose I could even deattach the local disks and have it run using the peer over the wire. That should eliminate the local I/O subsystem. It's kind of scary there is no end-to-end parity implemented somewhere along the whole data path to prevent this. It sort of defeats the point of RAID 6 and ECC. I agree, it's pretty damn scary. You can read about the story and the ensuing discussion here: I wonder if drbd could help out with that. Interesting. I hadn't heard of such a thing until I just looked it up. But in any case that adds yet another variable (and a fairly uncommon one) to the mix. It's this one: http://www.acnc.com/02_01_jetstor_sata_416s.html. I thought units like it are very popular. -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Sure, but that wasn't really the question.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:56 PM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
While it is functionally equivalent I wonder if it the code paths
taken are the same. I suppose for both commit and rollback mysql would
have to look for any pending work, if there were none both would do
nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to
do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards
the read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as
I know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could drbd randomly flip bits? Was: Database page corruption on disk occurring during mysqldump on a fresh database and Was: Spontaneous development of supremely large file
I failed over the server and ran a short backup and there were no didn't compare errors where on the first server, they are there pretty reliably. I guess this confirms some hardware on the first server is flipping bits. Essentially, users could have any number of munged files (most files are binary) since the problem surfaced a few weeks ago, and there'd be know way to know. Unfortunately, the secondary server was off for a short time at one point, so even if the munging were taken place on the I/O subsystem and not in RAM, it is possible that some blocks got copied badly to the secondary server. Anyway, it seems the problem is definitely hardware and not due to either ext3, drbd or mysql! -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
