Re: Query to get count of ages
But you can't get a group by from an alias. That surprises me. I tried putting the calculation of age in a subquery but that didn't work, either. And I've never quite figured out how to create a temporary table for intermediate results. There has _got_ to be a way to do this, even an awkward way. A friend suggested the following which is rather straight forward: SELECT ROUND(DATEDIFF(CURDATE(), dateofbirth)/365) AS age, COUNT(1) FROM person GROUP BY age; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to get count of ages
If your dateOfBirth is a date field, It is. you can do this: select (substring(curdate(),1,4)-substring(dateofbirth,1,4))- (substr(curdate(),5)substr(dateofbirth,5)) as age from myTable; Thanks. That's a little more accurate (thought that doesn't quite matter in my case). But you can't get a group by from an alias. That surprises me. I tried putting the calculation of age in a subquery but that didn't work, either. And I've never quite figured out how to create a temporary table for intermediate results. There has _got_ to be a way to do this, even an awkward way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to get count of ages
I'm working on MySQL v5.0 and I have a table with dateOfBirth and I want a histogram of ages at a point in time. I tried something like: select round(datediff(curdate(), dateofbirth)/365) as age, count(age) from myTable group by age; but MySQL Query Browser says: Unknown column 'age' in 'field list' 1054 What am I doing wrong? How can I count ages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get v5.0.22 to work;alternatives?
n http://dev.mysql.com/downloads/ Look for older releases Thanks but I want an older _build_ of the 5.0 release, not an older release. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't configure instance w/ 5.0.22 instance wizard
No matter what I do, it fails at the step where it's supposed to install and start the 'Windows service with an error 0. Is this a known issue? It sure would be nice to get more information about the failure from the wizard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scheduled Backups Issue...
I am attempting to set and and schedule automated MySQL backups using the MySQL Administrator Tool downloaded from mysql.com. All of my back ups work fien when I perform them manually. However none of the scheduled backups ever get done. I am not sure where the glicth is but here is what the details are... I have a remote server (*nx box colocated at an offsite location). MySQL 5.0.21 is on the box. Logged in as root to the MySQL. Created a Backup Project. Added Schema to it. Under Advanced left all options at default EXCEPT changed to Lock All Tables. Set a local path to save the back ups. Set backup to run daily. Saved and said to use the root as the user to perform the back up. Nada... Please advise if I am being a toal DFU or what. There's an issue with MySQL Administrator creating bad scheduled items in 'Windows. I found a note about it but not a bug report or a fix. Try going to the Windows Scheduler, opening the backup item and removing -c (including the quotes) from the middle of the command to run. (I found it helpful to copy the command to Notepad, edit it there, and copy back as it is quite long.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warnings while trying to restore database
I'm trying to restore a MySQL database in v5.0 (that minor number is in the teens, I don't have it at hand). I get a bunch of warnings like: Warning: Do not know how to handle this statement at line 28: CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized. Warning: Do not know how to handle this statement at line 86: CREATE TEMPORARY TABLE `COLLATIONS` ( `COLLATION_NAME` varchar(64) NOT NULL default '', `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `ID` bigint(11) NOT NULL default '0', `IS_DEFAULT` varchar(3) NOT NULL default '', `IS_COMPILED` varchar(3) NOT NULL default '', `SORTLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized. I've searched the list archive and the bug database without finding a clue. What's this about? How do I work around it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple engines in one DB a problem?
I'm trying to find a weird performance problem in a MySQL database. I use MySQL v5.0 but the db was migrated forward from a v4.1(?) system. Looking at the schema in a recent backup, I was surprised to find different engines used for different tables: ... CREATE TABLE `comment` ( `id` int(10) unsigned NOT NULL default '0', `entered` date default NULL, `author` varchar(20) default NULL, `about` mediumint(8) unsigned NOT NULL default '0', `comment` text, KEY `bySubject` (`about`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `country` ( `abbrev` char(2) NOT NULL default '', `name` varchar(45) default NULL, PRIMARY KEY (`abbrev`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ... Is this OK? If not, what's the solution? Backup, change all the enginges to the same one, and restore? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Some queries use 100% CPU after restore
I'm moving a database to a new server. I'm using MySQL v5.0.16 on 'Windows. I used the MySQL Administrator to backup on the old system and restore on the new one. Everything is fine _except_ on the new server, some queries take 2-3 minutes with MySQL using 100% of the CPU. I've dropped unneeded views, done maintenance, verfied that all the indices I expect are there, etc. What's very, very strange is that it seems that the long queries are for old records and records created since the move are OK. I realize this is a vague request but I've been poking at this for a long time without getting any good clues or making any real headway. Any kind of brainstorming on things to check would be very welcome. Thanks. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Some queries use 100% CPU after restore
Have you tried Repair table or if InnoDB ALTER TABLE ENGINE=InnoDB; Sometimes I've noticed after a restore or after adding lots of rows performance is slow. REPAIR or the ALTER TABLE fixes it. I'll try that. Thanks. What's curious is that I've got one backup I can restore and have fine performance and another, later backup that I can restore and get crappy performance. I can switch back and forth all day long with quite consistent results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to design one to many queries
Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. ... Maybe I'm really missing something here but I think that your querying problem arises from bad data design. This is what I'd do: Product table: ProductID some other stuff Attribute table: ProductID Attribute which gives: SELECT Product.something Attribute.Attribute FROM Product LEFT JOIN Attribute ON (Product.ProductID = Attribute.ProductID); Or Product table: ProductID product stuff ProdAttr table: ProductID AttributeID Attribute table: AttributeID other attribute stuff Which makes the query a little more complicated but the attributes a little more flexible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Best way to design one to many queries
In this case, cnelson, you can only have a one to one relationship. Why? If the Attribute table keyed on (ProductID,Attribute), surely a 1:M is possible. He wants to allow a one to many relationship. Yes, I know. Using one field to store all the attribute ids in the product table is what he is trying to do and it is not the best idea. I agree! Better use a table in the middle, with product ids and attribute ids, this way he can have one more than one attribute per product. That's what I was getting at with my second suggestion. It allows more than one attribute per category of attribute. For instance, languages available in a DVD: DVD titles: idtitle 1 2 3 Attributes: idtype_id description 1 1 English 2 1 French 3 1 Spanish Attribute types: iddescription 1 language attribute links: dvd_idattribute_id 1 1 1 2 1 3 To list all the languages in plain English for DVD #1: Select attributes.description From attributes_links On attributes_links.attribute_id = attributes.id Where attributes_links.dvd_id = 1 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 5:41 AM To: Scott Haneda Cc: MySql Subject: Re: Best way to design one to many queries Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. ... Maybe I'm really missing something here but I think that your querying problem arises from bad data design. This is what I'd do: Product table: ProductID some other stuff Attribute table: ProductID Attribute which gives: SELECT Product.something Attribute.Attribute FROM Product LEFT JOIN Attribute ON (Product.ProductID = Attribute.ProductID); Or Product table: ProductID product stuff ProdAttr table: ProductID AttributeID Attribute table: AttributeID other attribute stuff Which makes the query a little more complicated but the attributes a little more flexible. -- 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]
Encrypted column example
When 5.0 came out, I saw an article or white paper or something with an example of a stored procedure (I think, maybe a trigger) that allowed you to transparently encrypt a column so that even if a database backup was stolen, that column wouldn't be exposed to the thieves. I believe that the example was for a SSN or credit card number. Now that I want it, I can't find it again. I've searched MySQL.com without success. Does anyone else remember it? Can someone point me to it? Thanks. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help?
I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. I'd need a clearer spec to offer advise. What results would you want from the example data? 2354 is there twice but with different vendor numbers. And 522 is there twice with different IDs. One interpretation of [records with] the same ID and vendor number is 0 because no record has both the same as any other. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: working w/UK postcodes
I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating a view from a join
I have two tables with quite a few fields and I want to create a view into a limited join of the table. I have a query like: select * from person inner join student on person.id=student.id where person.dormant != 'Y'; The select works fine but when I try to use MySQL Query Browser to create a view from that query, it says that there's a duplicate column, id. Is there a way that I can say something like: select * except student.id from ... Aside from explicitly listing all columns, is there an easy way to accomplish this view? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to MySQL v5.0.15 on 'Windows XP
I've just installed MySQL v5.0.15 on my 'Windows XP system and most ways I want to connect to it fail. I don't see what I'm doing wrong. I've searched the manual, the web, and the forums without seeing anything that clearly addressed my problem. Help, please! If I navigate through Programs / MySQL / MySQL Server v5.0 / MySQL Command Line Client, I'm prompted for a password and when I enter the password, I am connected and can work fine. If I try to run mysqlshow, I see: C:\Documents and Settings\Chrismysqlshow sql mysqlshow: Access denied for user 'ODBC'@'localhost' (using password: NO) If I run the command interpreter (cmd) and type mysql -uroot -pmypwd, it works now but I swear it didn't 10 minutes ago. Arg. My real problem is that I've used MySQLKeeper with MySQL v4 and have backups created with it that I want to restore into MySQL v5.0 and when I try to connect with MySQLKeeper, I get: CONNECT Result: Connection error Now, it may be that MySQL Keeper is trying to connect via ODBC and that's why it's failing but I didn't think that's how it connected, it's an MySQL-specific tool, why would it use ODBC? And shouldn't mysqlshow work out of the box? How could I have installed MySQL to screw up mysqlshow? Thanks for any pointers. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backward-compatible authentication in v5.0?
I'm trying to upgrade MySQL from v4.x to v5.0 under an existing Tcl-based application that uses MySQLTcl and I'm getting: mysqlconnect/db server: Client does not support authentication protocol requested by server; consider upgrading MySQL client Is there something I can do to MySQL v5 server to make it compatible with older clients? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Backward-compatible authentication in v5.0?
Hi! It is a common error, have a look here: http://dev.mysql.com/doc/refman/5.0/en/old-client.html I have had the same error. Tbanks. I found that -- eventually -- in the manual. It took several searches to find it but it seems to work now, not only for my Tcl application but for MySQL Keeper, too! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
