Re: Totals Across Multiple Records
Albert, Your spec isn't entirely clear to me (eg if sd1 sd2 were 'unique within each record', wouldn't there be zero rows where sd1=sd2?), but is this what you're looking for ... SELECT zz, COUNT(zz) as zzCount, SUM(IF(zz=sd1 AND sd1=sd2, 1, 0)) AS sd1sd2Count FROM ss GROUP BY zz; Peter Brawley http://www.artfulsoftware.com - Albert Padley wrote: I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.9.2/55 - Release Date: 7/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.13 OS X MAJOR I/O Degredation
On Jul 22, 2005, at 6:22 PM, Bruce Dembecki wrote: So it appears I am having an issue with 4.1.13 which I'm guessing is a bug... wanted some input before I file it... Setting up a new machine to take over for an old one, so it's clean, Operating System and some empty disks... the server does nothing other than MySQL so there are no other processes running. It has 16Gbytes of ram and the data disks are a 7 disk RAID5 array on a 2GBit/Sec Fiber Channel connection. If I create my data directories and copy the mysql database from another server with a simple copy (mysql is myisam so it's no issue) I am ready to launch mysqld... When mysqld launches it of course needs to create my InnoDB data files and log files before it comes up... I first did this under 4.1.13 Community edition and was SHOCKED by the results... one 2Gbyte shared data file for InnoDB, and 2 250Mbyte log files... what felt like an hour later it finished... Tried 4.1.13 Pro released today... same thing... Tried 4.1.12, better, still slower than I would expect, but better... let me quantify that a little. From the log files below you will see that the time to create the InnoDB files and get to the point of being ready to connect is: MySQL 4.1.13 Pro:54 minutes 51 seconds MySQL 4.1.12 Standard: 4 minutes 16 seconds Have you verified that actual queries are slow, or is it just the create? I'm wondering if this from the 4.1.13 changes could be the issue: InnoDB: When creating or extending an InnoDB data file, allocate at most one megabyte at a time for initializing the file. Previously, InnoDB used to allocate and initialize 1 or 8 megabytes of memory, even if a few 16-kilobyte pages were to be written. This fix improves the performance of CREATE TABLE in innodb_file_per_table mode. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
storing php pages with sql queries in a mysql database
Hi all. I'm writing a php script to store the contents of html and php pages in a data base, it works well until there are mysql queries in the pages source then give errors such as this one. Query failed: You have an error in your SQL syntax near 'temp' how do stop mysql from trying to interperate this data and blindly store it ?? Many Thanks -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) www.goeducation (support and admin) +27 72 524 8096 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Questions (was: Mac OS X Table Corruption)
I am all most ready to give up on MySQL at this point. I'm still getting regular table corruption on multiple installs of OS X. I went as far as reporting it as a bug: http://bugs.mysql.com/bug.php?id=12066 They seem to want more info but my requests for clarification have gone unanswered. So I'm trying a few obscure things: 1.) Move the MySQL data dir The default data dir was /usr/local/mysql/data/ I moved this to /var/mysql/data because subsequent MySQL installs messed with the previous path (i.e. creating a symlink from the latest install to /usr/local/mysql) I have copied the entire mysql data dir to /usr/local/data Just a hunch that it's some issue with being in /var/ On my previous MySQL systems I simply moved the data contents to the new location after each upgrade and restarted the server. I never had any issues here (it was also on MySQL 3.x) 2.) Try Innodb I have changed one of the tables that consistently gives my trouble to Innodb. I will continue to watch for corruption. Given that can anyone point out any big things to note about innodb tables. I've read that they are in general better than myisam tables but you loose the fulltext index option. Is there a typical work around for this?? I typically only have the odd tables that needs a full text search capability. I think what I have seen was to create a dummy myisam table with just the key and fields from the innodb table and add a fulltext index to search against. It would just be a matter of keeping the tables synced. I am also wondering about back-ups. The docs seem to indicate that you can still run mysqldump... but how does this effect restoring. In my case I can afford to shutdown the server in the wee hours of the morning and back-up the data dir manually. Any pointers would be appreciated. Thanks, Dan T On Jul 6, 2005, at 10:26 AM, [EMAIL PROTECTED] wrote: Dan Tappin [EMAIL PROTECTED] wrote on 07/06/2005 11:26:13 AM: I am running into repeatable table corruption with MySQL 4.x on Mac OS X 10.x. I previously had a MySQL install on 10.2 Client under 3.x and never had an issue or any major problems at all. I upgraded to MySQL 4.x and have subsequently installed MySQL 4.x (from the supplied pkg's) on my 10.2 client, a 10.3 Server and 10.4 Server (Tiger) install. The typical application here was standalone servers running MySQL, Apache and PHP 4/5 running a hand full of small websites. Things run along fine until with out warning my PHP / MySQL queries fail returning no data when there should be. As a temporary fix I created a cron job to run: /usr/local/mysql/bin/myisamchk -ov on all my tables every 4 hours. This fixes the data issues but the problem is I am loosing data: - recovering (with keycache) MyISAM-table '/var/mysql/data/ xxx.MYI' Data records: 17 Data records: 14 Now this was last night at midnight. I recall manually adding the 3 records that evening and the previous cron job had only 14 records. It seems that I the fix is flushing my new data down the toilet. The only common thread I can see is that I use MacSQL (an old version 2.6.3) for running test queries etc rather than the CLI. Can a mysql client cause this corruption? All my systems have different OS's, versions of MySQL and PHP. They all have UPS's. The old 10.2 system (soon to be retired) has only a single drive. The new 10.4 system has mirrored drives on a RAID set- up. The 10.3 install (recently wiped) had the same set-up. I think I can rule out a hardware issue and a OS / MySQL issue. It even show up on tables I never interact with directly i.e. my Moveable Type db for my blogs. I create an entry and the next day it's dropped out of the database. Here is the full myisamchk output on the subject table (after adding my data back in): Checking MyISAM file: /var/mysql/data/.MYI Data records: 18 Deleted blocks: 2 - check file-size myisamchk: error: Size of datafile is: 876 Should be: 1160 - check record delete-chain myisamchk: error: record delete-link-chain corrupted - check key delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found key at page 1024 that points to record outside datafile - check record links myisamchk: error: Record-count is not ok; is 15 Should be: 18 myisamchk: warning: Found 0 deleted space. Should be 80 myisamchk: warning: Found 0 deleted blocks Should be: 2 myisamchk: warning: Found 15 partsShould be: 20 parts MyISAM-table '/var/mysql/data/oilfielddepot_r6/subnavbar.MYI' is corrupted Fix it using switch -r or -o so I run: myisamchk -vr /var/mysql/data/xx.MYI - recovering (with sort) MyISAM-table '/var/mysql/data/ xx.MYI' Data records: 18 - Fixing index 1 - Searching for keys, allocating
libmySQL.so location
Using mysql 3.23 on Red Hat 9.0 partition, mysql 4.0.20 on Slack 10.0 partition. I have reviewed some code for a lisp mysql library using the Foreign Function Interface (exposing the C API). the shared object referenced in the code is libmySQL.so. I am not able to locate a file with that name on either of my linux partitions. The library developer is primarily a windows programmer and has told me that this file should be included with the distribution. Can anyone clarify this matter for me? Do I need a symlink? If so to what? thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
On Jul 23, 2005, at 11:56 AM, Dan Tappin wrote: I am all most ready to give up on MySQL at this point. I'm still getting regular table corruption on multiple installs of OS X. I went as far as reporting it as a bug: http://bugs.mysql.com/bug.php?id=12066 They seem to want more info but my requests for clarification have gone unanswered. Has your mysql crashed or have your restarted the machine without first shutting down mysql manually? We only saw this error when mysql was not shut down normally. You can look in your .err file which should be in your data directory. Every mysql sartup sequence should be preceded by a line saying mysqld ended. If not it's crashing and that's the issue to address. 2.) Try Innodb This is a MyISAM error, but if mysqld is crashing or being shutdown abnormally InnoDB too will have to go through a crash recovery at startup (it's just automatic, but you'll see it in the .err file). I have changed one of the tables that consistently gives my trouble to Innodb. I will continue to watch for corruption. Given that can anyone point out any big things to note about innodb tables. I've read that they are in general better than myisam tables but you loose the fulltext index option. I think it's more that they are different. InnoDB has some complexities related to backups and slightly different functionality, and the number of tips on the Internet are much fewer than for MyISAM. However, if you are doing lots of inserts with simultaneous selects you really have to use InnoDB to get reasonable performance. Is there a typical work around for this?? I typically only have the odd tables that needs a full text search capability. I think what I have seen was to create a dummy myisam table with just the key and fields from the innodb table and add a fulltext index to search against. It would just be a matter of keeping the tables synced. I think that's what most people do. I am also wondering about back-ups. The docs seem to indicate that you can still run mysqldump... but how does this effect restoring. In my case I can afford to shutdown the server in the wee hours of the morning and back-up the data dir manually. 1) Shut down and manually copy, but make sure you get all the InnoDB parts...data files and transaction logs. One is pretty much useless without the other. 2) mysqldump, look at the options. This with InnoDB is pretty nice b/c you can use --single-transaction and get a point in time copy of the table without disrupting other users. In particular the comments on the mysql documentation page for mysqldump detail a good set of flags for InnoDB. 3) The (non-free) ibbackup which takes a backup while running without disturbing users I don't think there's any fundamental issue with MySQL (either myisam or innodb) on OS X, but I do think that MySQL is something that doesn't do well when run only through Apple's front end. It has two many options, configuration details, etc... if you are doing anything more than a few small tables. Plus, if you are relying on it all you'll want to be able to take advantage of bug fixes faster than Apple's MySQL updates allow you too. Fortunately, it's easy enough to download the mysql binary and use/ configure it independently. We've had quite good luck with MySQL on OS X with a pretty big install, but there are a lot of details to learn so it can seem tough to work out at times. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmySQL.so location
I suspect a case problem ln -s /usr/lib/dbd/libmysql.so libmySQL.so # perhaps? /opt/lampp/lib/mysql/libmysqlclient.14.0.0 /opt/lampp/lib/mysql/libmysqlclient.14 /opt/lampp/lib/mysql/libmysqlclient.so.14 /opt/lampp/lib/mysql/libmysqlclient.so.14.0.0 /opt/lampp/lib/mysql/libmysqlclient.so /opt/lampp/lib/mysql/libmysqlclient /usr/lib/mysql/libmyisammrg.a /usr/lib/mysql/libmysys.a /usr/lib/mysql/libmysqlclient.so.10 /usr/lib/mysql/libmystrings.a /usr/lib/mysql/libmysqlclient.so.10.0.0 /usr/lib/mysql/libmysqlclient.a /usr/lib/mysql/libmysqlclient_r.so /usr/lib/mysql/libmysqlclient_r.a /usr/lib/mysql/libmyisam.a /usr/lib/mysql/libmysqlclient.so /usr/lib/mysql/libmysqlclient_r.so.10.0.0 /usr/lib/mysql/libmysqlclient_r.so.10 /usr/lib/mozilla-1.7.8/components/libmyspell.so /usr/lib/libmyodbc-2.50.39.so /usr/lib/ooo-1.1/program/libmysql2.so /usr/lib/libmyodbc.so /usr/lib/thunderbird-1.0.2/components/libmyspell.so /usr/lib/dbd/libmysql.a /usr/lib/dbd/libmysql.la /usr/lib/dbd/libmysql.so On 7/23/05, Tim Johnson [EMAIL PROTECTED] wrote: Using mysql 3.23 on Red Hat 9.0 partition, mysql 4.0.20 on Slack 10.0 partition. I have reviewed some code for a lisp mysql library using the Foreign Function Interface (exposing the C API). the shared object referenced in the code is libmySQL.so. I am not able to locate a file with that name on either of my linux partitions. The library developer is primarily a windows programmer and has told me that this file should be included with the distribution. Can anyone clarify this matter for me? Do I need a symlink? If so to what? thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- 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: storing php pages with sql queries in a mysql database
Gregory Machin wrote: Hi all. I'm writing a php script to store the contents of html and php pages in a data base, it works well until there are mysql queries in the pages source then give errors such as this one. Query failed: You have an error in your SQL syntax near 'temp' how do stop mysql from trying to interperate this data and blindly store it ?? Many Thanks You need to escape the string before you sent it to MySQL to be stored. The link below is for the C API function; whichever language API you're using has something equivalent. http://dev.mysql.com/doc/mysql/en/mysql-real-escape-string.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
On Jul 23, 2005, at 10:14 AM, Ware Adams wrote: Has your mysql crashed or have your restarted the machine without first shutting down mysql manually? We only saw this error when mysql was not shut down normally. You can look in your .err file which should be in your data directory. Every mysql sartup sequence should be preceded by a line saying mysqld ended. If not it's crashing and that's the issue to address. Nope... nothing like that... just lots of: [ERROR] Got error 127 when reading table No crashes at all. 2.) Try Innodb This is a MyISAM error, but if mysqld is crashing or being shutdown abnormally InnoDB too will have to go through a crash recovery at startup (it's just automatic, but you'll see it in the .err file). so the repair is manual on MyISAM and auto on start-up for Innodb? I am also wondering about back-ups. The docs seem to indicate that you can still run mysqldump... but how does this effect restoring. In my case I can afford to shutdown the server in the wee hours of the morning and back-up the data dir manually. 1) Shut down and manually copy, but make sure you get all the InnoDB parts...data files and transaction logs. One is pretty much useless without the other. 2) mysqldump, look at the options. This with InnoDB is pretty nice b/c you can use --single-transaction and get a point in time copy of the table without disrupting other users. In particular the comments on the mysql documentation page for mysqldump detail a good set of flags for InnoDB. 3) The (non-free) ibbackup which takes a backup while running without disturbing users Im my situation I'm ok with shutting everyone out (via PHP which the only access to the db) and running either type of back-up. So if I read you correctly mysqldump locks every one out and dumps. But with Innodb you can add '--single-transaction' and the dump the data at that point in time which users continue to work with the database? I don't think there's any fundamental issue with MySQL (either myisam or innodb) on OS X, but I do think that MySQL is something that doesn't do well when run only through Apple's front end. It has two many options, configuration details, etc... if you are doing anything more than a few small tables. Plus, if you are relying on it all you'll want to be able to take advantage of bug fixes faster than Apple's MySQL updates allow you too. Fortunately, it's easy enough to download the mysql binary and use/ configure it independently. We've had quite good luck with MySQL on OS X with a pretty big install, but there are a lot of details to learn so it can seem tough to work out at times. Well Apple does not really have a front end for MySQL at all. I've gone away from Apple's updates and install my own version from the source packages provided by MySQL. Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmySQL.so location
* sol beach [EMAIL PROTECTED] [050723 09:15]: I suspect a case problem Hello sol: I'm using a case insensitive seach. i.e.: [EMAIL PROTECTED] tim]$ locate -i libmysql.so [EMAIL PROTECTED] tim]$ same results as root thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
On Jul 23, 2005, at 1:58 PM, Dan Tappin wrote: On Jul 23, 2005, at 10:14 AM, Ware Adams wrote: Has your mysql crashed or have your restarted the machine without first shutting down mysql manually? We only saw this error when mysql was not shut down normally. You can look in your .err file which should be in your data directory. Every mysql sartup sequence should be preceded by a line saying mysqld ended. If not it's crashing and that's the issue to address. Nope... nothing like that... just lots of: [ERROR] Got error 127 when reading table No crashes at all. Then I don't know what to say about the MyISAM error, though I'd watch InnoDB pretty carefully as I think MyISAM is pretty robust on OS X too. We did also see these when a disk is failing, but I assume you've run disk utility. I guess it could be failing and not showing up in disk utility. If it's an XServe the SMART data might show you that, or if you have DiskWarrior I think it might monitor SMART data for you. 2.) Try Innodb This is a MyISAM error, but if mysqld is crashing or being shutdown abnormally InnoDB too will have to go through a crash recovery at startup (it's just automatic, but you'll see it in the .err file). so the repair is manual on MyISAM and auto on start-up for Innodb? Yes, but that's after a crash. When you start InnoDB after a crash you'll see: 050722 16:26:38 mysqld started 050722 16:26:40 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050722 16:27:09 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 2875 3750200540. InnoDB: Doing recovery: scanned up to log sequence number 2875 3755443200 ... InnoDB: Doing recovery: scanned up to log sequence number 2875 3878800255 050722 16:27:18 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 050722 16:30:11 InnoDB: Flushing modified pages from the buffer pool... 050722 16:30:33 InnoDB: Started; log sequence number 2875 3878800255 /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.1.12-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) But that's after a power failure when and InnoDB could tell it had issues when we restarted. The thing I would be concerned about in your case is what's going to happen if the same thing that causes issues for MyISAM has an impact on InnoDB. If it were crashes InnoDB can handle that pretty well, but if it's some underlying disk issue then InnoDB could wind up with it's table space corrupt just like MyISAM. The issue with InnoDB is that the tablespace files are more dependent on each other. So if you have a corrupt MyISAM table you could literally shut mysqld down, delete the files for that table and everything else is fine. You'd lose the data for that table but not anything else. With InnoDB you have the transaction logs, the shared tablespace files and if you are using file_per_table a tablespace (.ibd) file for each table. At least the first two of these are completely dependent on each other. If you wind up with a corrupted shared tablespace file due to a disk error you can potentially lose everything. The transaction logs can be replaced, but only if there are no uncommitted transactions in them. If you lose a .ibd file in file_per_table you would just lose that table. I probably didn't explain that very well, you might want to read the sections on InnoDB's various files in the InnoDB manual http://www.innodb.com/ibman.php In short, InnoDB deals better than MyISAM with crashes of the mysqld server or the OS, but I don't think it's necessarily any better (nor maybe could it be) with dealing with an underlying disk corruption issue that keeps occurring. I am also wondering about back-ups. The docs seem to indicate that you can still run mysqldump... but how does this effect restoring. In my case I can afford to shutdown the server in the wee hours of the morning and back-up the data dir manually. 1) Shut down and manually copy, but make sure you get all the InnoDB parts...data files and transaction logs. One is pretty much useless without the other. 2) mysqldump, look at the options. This with InnoDB is pretty nice b/c you can use --single-transaction and get a point in time copy of the table without disrupting other users. In particular the comments on
Re: libmySQL.so location
* Tim Johnson [EMAIL PROTECTED] [050723 10:42]: * sol beach [EMAIL PROTECTED] [050723 09:15]: I suspect a case problem Hello sol: I'm using a case insensitive seach. i.e.: [EMAIL PROTECTED] tim]$ locate -i libmysql.so [EMAIL PROTECTED] tim]$ Needed libdbi-0.6.5-5.i386.rpm and libdbi-dbd-mysql-0.6.5-5.i386.rpm Life Is Good . tj -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
On Jul 23, 2005, at 12:49 PM, Ware Adams wrote: Then I don't know what to say about the MyISAM error, though I'd watch InnoDB pretty carefully as I think MyISAM is pretty robust on OS X too. We did also see these when a disk is failing, but I assume you've run disk utility. I guess it could be failing and not showing up in disk utility. If it's an XServe the SMART data might show you that, or if you have DiskWarrior I think it might monitor SMART data for you. The part that keeps scaring me is that I have seen this on 10.2, 10.3 nd 10.4 all with separate hardware. My recent build has 2 drive on a mirror. I find it hard to believe that I have been that unlucky to have drive failures / failed filesystems on ALL these machines. My set-up ran rock solid on MySQL 3.x for years... never an issue. The problem came when I went of from Apple's default install and switched to 4.x. I was once asked about the shared libraies and if I had removed them. I cleared off the Apple 3.x build of MySQL but I'm wondering if I missed something that is conflicting with the new 4.1.x install and causing the conflicts. Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totals Across Multiple Records
Sol and Peter, Thanks for your feedback. Both of your suggestions got me going in the right direction and I was able to solve the problem using temporary tables and left joins. Thanks again. Albert Padley On Jul 23, 2005, at 11:00 AM, sol beach wrote: create table count_temp1 select id, count(id) count_id from table ss; create table count_temp 2 select id, count(tt) from ss where id = sd1 or id = sd2; should get you closer. yes? On 7/22/05, Albert Padley [EMAIL PROTECTED] wrote: I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions (was: Mac OS X Table Corruption)
So I've been poking around my system and I found /usr/lib/mysql/ /usr/share/mysql/ The mod dates on these are really old. Can I delete these safely? I'm guessing the are from the Apple install. These files seem to be in the /usr/local/mysql/ path also. Hmmm... I looked at my old Mac OS X 10.2 system with the same issues and these files are not there so I'm guessing this is not likely the issue. Dan T On Jul 23, 2005, at 1:35 PM, Dan Tappin wrote: I was once asked about the shared libraies and if I had removed them. I cleared off the Apple 3.x build of MySQL but I'm wondering if I missed something that is conflicting with the new 4.1.x install and causing the conflicts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
