Re: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 2:57 AM, Gwynne Raskind wrote: I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. I've discovered more information about this problem since, as posted in the bug report: I've tracked down this bug such that it only happens when the table in question is accessed via INFORMATION_SCHEMA after being created. A FLUSH TABLES command eliminates the error without need for any other intervention. I use INFORMATION_SCHEMA this way: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=TestData This also happens with any other table accessed in this way; based on the output of mysqladmin extended-status it looks like the server isn't closing the table definition correctly after accessing it via INFORMATION_SCHEMA. If anyone has any information about how to deal with this problem, or fix it, please let me know; it's very annoying. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
force row to appear at top of results using order by
Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? Many thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queues on MySQL?
-Original Message- From: Waynn Lue [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2008 8:01 PM To: Jerry Schwartz Cc: MySQL List Subject: Re: Queues on MySQL? Thanks for the suggestion--is that essentially using http://www.php.net/posix_mkfifo within PHP? [JS] Yes, that's what I had in mind. Be warned, though: I've never used this from PHP; and, although I found no references to this in the PHP documentation, I couldn't find the POSIX functions in a Windows installation. On Mon, Jun 30, 2008 at 7:11 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: -Original Message- From: Waynn Lue [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2008 6:04 AM To: MySQL List Subject: Queues on MySQL? Hey, I'm looking to write a queue backed by MySQL to enable batch updates of a system I'm writing. Essentially what I wanted to do was every time I want to enqueue, I just INSERT a new row into a table. Then I [JS] Just a bit of a warning: in my (very old) experience, this is not the most effective way of implementing a queue. Does your environment support fifo files, or can you emulate them? They have a lot less overhead. have a cron job that runs every X minutes, selects all the existing rows and does the actions, then deletes the rows that have been selected. It's a fairly simple algorithm, and not hard to implement, but I'm wondering if there are existing tools out there that do queues that will have additional functionality in case I ever want to change it. A quick Google search turned up links like http://q4m.31tools.com/, but I'm not sure what's a good one. Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: force row to appear at top of results using order by
Many thanks for the quick replies! This solution appears the most elegant: -- Forwarded message -- From: Markus Grossrieder [EMAIL PROTECTED] Date: 2008/7/1 Subject: Re: force row to appear at top of results using order by To: Andrew Martin [EMAIL PROTECTED] Andrew, something like this (air code) SELECT events_groups_id, events_groups_name, IF(events_groups_name='Personal Events', 1, 2) as sort_elem FROM events_groups; ORDER BY sort_elem, events_groups_name; Regards, Markus - Original Message - From: Andrew Martin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 01, 2008 3:19 PM Subject: force row to appear at top of results using order by Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? Many thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.3/1528 - Release Date: 7/1/2008 7:26 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: force row to appear at top of results using order by
SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY IF(events_groups_id=1,0,1),events_groups_name ASC; -Original Message- From: Andrew Martin [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 9:20 AM To: mysql@lists.mysql.com Subject: force row to appear at top of results using order by Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? Many thanks, Andy -- 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: force row to appear at top of results using order by
-Original Message- From: Andrew Martin [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 9:20 AM To: mysql@lists.mysql.com Subject: force row to appear at top of results using order by Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? [JS] IMHO, you are better off approaching this in a different way. Add a third column that is used only as a sort key, so that you can rearrange your sort order whenever you want. I would use a sort key that has two decimal places, so that you can easily add an entry that needs to be sorted between 1.00 and 2.00 just by giving it a sort key of 1.50. This will give you complete flexibility, and should use minimal overhead. Many thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge-table Question
Hello, I remember some activity regarding some flakiness when using merge-tables. I searched out and reviewed this: http://bugs.mysql.com/bug.php?id=26881 Using 5.0.51a on RHEL 4 box, I'm still seeing similar issues. The INFORMATION_SCHEMA shows the merge table as follows TABLE_CATALOG: NULL TABLE_SCHEMA: Scheduler TABLE_NAME: Job_all TABLE_TYPE: BASE TABLE ENGINE: NULL VERSION: NULL ROW_FORMAT: NULL TABLE_ROWS: NULL AVG_ROW_LENGTH: NULL DATA_LENGTH: NULL MAX_DATA_LENGTH: NULL INDEX_LENGTH: NULL DATA_FREE: NULL AUTO_INCREMENT: NULL CREATE_TIME: NULL UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: NULL CHECKSUM: NULL CREATE_OPTIONS: NULL TABLE_COMMENT: Unable to open underlying table which is differently defined or of non-MyISAM ty and one of the underlying table as follows. There are nine of these each one is exactly the same. TABLE_CATALOG: NULL TABLE_SCHEMA: Scheduler TABLE_NAME: Job_08 TABLE_TYPE: BASE TABLE ENGINE: MyISAM VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 2780476 AVG_ROW_LENGTH: 102 DATA_LENGTH: 285847500 MAX_DATA_LENGTH: 281474976710655 INDEX_LENGTH: 42443776 DATA_FREE: 0 AUTO_INCREMENT: 12532051 CREATE_TIME: 2008-04-03 02:47:36 UPDATE_TIME: 2008-07-01 18:44:24 CHECK_TIME: 2008-07-01 04:18:03 TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: I find it interesting that the engine for the merge table is NULL (probably irrelevant). Bottom line is when I try to access the merge table I get ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist The above output busts the myth of it not a MyISAM table. You'll have to take my word that they are identical. This was working fine in 4.1.22. I tried repairing all the underlying tables and recreating the merge table. No Luck. Any ideas ? Thanks, Michael -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running 2 versions of MySQL on same server
Hi: I've been running 3.23 for years, but several users are clamoring for new versions, as many php apps are using newer features, so I downloaded the source for 5.0.51b to run on a different TCP port. I found a reference on a couple of ways to do this here: http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html It looked pretty straightforward to me. Since I prefer to do as much non-standard stuff at runtime as possible, rather than compile it in, the only configure option I changed was --with-prefix=/usr/local/mysql50 (3.23 is installed in /usr/local from FreeBSD Ports). The rest, from what I could gather from the page above could be done thusly: #!/bin/bash /usr/local/mysql50/bin/mysqld_safe --socket=/tmp/mysql50.sock \ --port=(new port) --basedir=/usr/local/mysql50 --datadir=/var/db/mysql50 I did the datadir as well, because I had read that sometimes accessing existing 3.23 databases using 5.0 can cause problems, so I thought I'd start fresh. I don't run a my.cnf as yet. However, the server still seems hopelessly confused. If I telnet to port 3306, I get the old version as I should and when I telnet to the new port I get the new version as I should, but if I do a: mysqladmin -P (new port) variables I get the old variables. Similarly, if I install phpMyAdmin and specify the new port and socket, it still sees the old databases and version of MySQL. Start and stop scripts also seem confused. What did I miss? James Smallacombe PlantageNet, Inc. CEO and Janitor [EMAIL PROTECTED] http://3.am = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queues on MySQL?
Thanks for the suggestion--is that essentially using http://www.php.net/posix_mkfifo within PHP? [JS] Yes, that's what I had in mind. Be warned, though: I've never used this from PHP; and, although I found no references to this in the PHP documentation, I couldn't find the POSIX functions in a Windows installation. Hm, I've spent some more time investigating this, and I'm not sure it'll work quite as expected. It looks like it's a blocking write to the pipe, which means it'll block until a reader actually acts on the pipe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 2 versions of MySQL on same server
Hi James, If I telnet to port 3306, I get the old version as I should and when I telnet to the new port I get the new version as I should, but if I do a: mysqladmin -P (new port) variables I get the old variables. I'd suspect mysqladmin's using the unix socket, and ignoring your -P option; try adding the option --protocol=TCP. You may well find a similar issue with phpmyadmin - there's a configuration option: $cfgServers[...]['connect_type'] = 'tcp'; which might solve this. hth, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ONLY IN
Possible duplicate, sorry if so. Schema below. I am trying to select from a many-to-many relationship all edition_ids that have records for, and only for, certain run_id values. I have no idea how to do this outside of a subquery, which I'd rather avoid. Any thoughts? Here's what I have now, which is giving me edition_ids that contain run_id values, but not ONLY those values. SELECT erm.edition_id FROM edition_run_map AS erm WHERE erm.run_id IN ( 2376, 2377, 2378) AND erm.is_active = 1 GROUP BY edition_id; CREATE TABLE `edition_run_map` ( `edition_id` int(11) unsigned NOT NULL, `run_id` int(11) unsigned NOT NULL, `is_active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`edition_id`,`run_id`); Thanks in advance, kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 2 versions of MySQL on same server
On Tue, 1 Jul 2008, Kevin F. O'Riordan wrote: Hi James, If I telnet to port 3306, I get the old version as I should and when I telnet to the new port I get the new version as I should, but if I do a: mysqladmin -P (new port) variables I get the old variables. I'd suspect mysqladmin's using the unix socket, and ignoring your -P option; try adding the option --protocol=TCP. That would appear to be the case...I subsequently as able to get it to give me the new variables by using -S /path/to/new.socket. I just tried your way and it works, too...strange, the man page for mysqladmin didn't mention that option, but then again, I could be looking at the man page for 3.23 :-/ You may well find a similar issue with phpmyadmin - there's a configuration option: $cfgServers[...]['connect_type'] = 'tcp'; which might solve this. I had tried that from the start with no luck (it brought up the old server). After running the new server's mysql_secure_installation script, to reset the password and secure everything, I can no longer authenticate, whether cookie or config. I'm getting access-denied. It almost seems as if phpMyAdmin is using the old client, but I see no what of telling it where to look or about prefixes. It is also just about impossible to stop the new mysql using the mysql-safe script, even if you specify port and socket on the command line, without messing up the old MySQL. I would think this would be a relatively simple thing to do. I guess I'm going to try to build MySQL with the config options instead of invoking at runtime...both versions seem hopelessly confused. James Smallacombe PlantageNet, Inc. CEO and Janitor [EMAIL PROTECTED] http://3.am = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 2 versions of MySQL on same server
On Tue, 1 Jul 2008, [EMAIL PROTECTED] wrote: I would think this would be a relatively simple thing to do. I guess I'm going to try to build MySQL with the config options instead of invoking at runtime...both versions seem hopelessly confused. Well, I rebuilt and installed 5.0 again using custom port and socket options, in addition to prefix. I was able to connect to the server as root using mysqladmin and mysql, but not through phpMyAdmin (access denied for [EMAIL PROTECTED] using password=yes). I then tried to run the mysql_secure_installation, which also lets you reset the root password, but I got access denied that way as well. I then nuked the mysql database and ran: ./mysql_install_db --user=mysql --ldata=/var/db/mysql50 killed and restarted mysql 5.0 and now root no longer works with or without a password. I was under the impression that nuking the db and installing it again started you anew as far as root goes (no password, you set it). James Smallacombe PlantageNet, Inc. CEO and Janitor [EMAIL PROTECTED] http://3.am = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
