Re: Last insert id problem/bug
on 7/22/04 10:56 PM, Scott Haneda at [EMAIL PROTECTED] wrote: I am not sure this is just how it is, my issue, or a bug, I searched the bugs database, but don't really know what to call this in order to properly look up the info: MySQL 4.0.18-standard Also note, I just tested this on 4.0.20-standard and I get the same results. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Use of FULLTEXT and/or LIKE?
MySQL wrote: I want searches for News to be on the author, headline and content fields. What is the best way to index and search this? I would suggest a Fulltext index on headline and content. And then do a search using MATCH() and AGAINST(). For searching on author's names you can use LIKE . From the manual `` The `MATCH()' function performs a natural language search for a string against a text collection (a set of one or more columns included in a `FULLTEXT' index). The search string is given as the argument to `AGAINST()'. The search is performed in case-insensitive fashion. For every row in the table, `MATCH()' returns a relevance value, that is, a similarity measure between the search string and the text in that row in the columns named in the `MATCH()' list. '' The results are sorted according to relevance, which I think is the best feature of the fulltext search. Oracle provides a thesauraus for Broader term and narrow term also. If you need something like that, you will not find it in MySQL. There is a workaround for the 3-char minimum search string. You can use a LIKE search query when the search string is less than 3 chars or use the BOOLEAN MODE when searching (availble in MySQL 4.0.1) Hope that helps Regards -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there any Performance Improvement / Tuning Software for MySQL?
Is there any kind of performance / tuning guide software for MySQL? I am looking for something which can look at the SHOW VARIABLES and SHOW STATUS commands, do some calculations and give some tips on how to improve the system? Any such product available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing my scenario, suggestions needed
There are three tables: This stores just a cosmetic name and a user_id which is used to find all the account holders campaigns... CREATE TABLE `campaigns` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `campaign_name` varchar(24) NOT NULL default '', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00', PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=4 ; SELECT campaign_name from campaigns where user_id = 123; would show me all the campaigns for a particular user. I have another table, addresses, which stores unique to a account holder, certain email addresses: CREATE TABLE `addresses` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `first_name` varchar(48) NOT NULL default '', `last_name` varchar(48) NOT NULL default '', `email_address` varchar(48) NOT NULL default '', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00', PRIMARY KEY (`id`), UNIQUE KEY `emailuid` (`email_address`,`user_id`), KEY `email_address` (`email_address`) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='das' AUTO_INCREMENT=351 ; The above email addresses can exist in more than one campaign, so I use a 3rd table to make that relationship: CREATE TABLE `addresses_incampaign` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `email_address` varchar(48) NOT NULL default '0', `campaign_id` int(11) NOT NULL default '0', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00', PRIMARY KEY (`id`), UNIQUE KEY `emailaddresscampaign` (`email_address`,`campaign_id`) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='das' AUTO_INCREMENT=246 ; From all this, I most often will need to find all email addreses owned by a user_id in a particular group, I am using the addresses_incampaign. email_address and addresses.email_address to link these together. I would have liked to use a auto_inc as the key to link them together, but for reasons that are too long to explain, that is not how the data is inserted. Is this a big deal? Or does mysql really not matter that a int is not in my field where I will be linking one record to another? Should I create any other indexes that I don't have here? I have made 2 joins, they both seem to get me the same result, I am not sure if either is optimum, can someone suggest some stuff to me: select a.first_name, a.last_name, a.email_address, c.campaign_name, i.campaign_id from addresses as a inner join addresses_incampaign as i on (a.user_id = i.user_id AND a.email_address = i.email_address) inner join campaigns as c on (i.campaign_id = c.id) WHERE i.campaign_id = '1' AND a.user_id = '1' AND i.user_id = '1' select a.first_name, a.last_name, a.email_address, c.campaign_name, i.campaign_id from addresses as a inner join addresses_incampaign as i on (a.email_address = i.email_address) inner join campaigns as c on (i.campaign_id = c.id) WHERE i.campaign_id = '1' AND a.user_id = '1' AND i.user_id = '1' Thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems transferring database
I previously had a server runnning RH 7.3, cPanel 9.41 and MySQL 4.0.20. I'm moving to a different server running Fedora 1, DirectAdmin and MySQL 4.0.17. I have a large database (200mb) and I'm trying to move it over. I made a dump using mysqldump -u USER -pPASSWORD DATABASE filename.sql, transferred it between servers via SCP, and tried importing it using mysql -u USER -pPASSWORD DATABASE filename.sql. After the last command runs for about an hour and a half, I get: ERROR 1067 at line 161570: Invalid default value for 'membersince' OK, so I look in the dump, and line 161570 has no reference to 'membersince'. The first occurance of that field is in line 326189. Any clue? Is this a bug with MySQL 4.0.17 and should I update? I've tried creating new dumps, transferring it directly to the server, transferring it from server - my pc - server, ftp, scp, etc. It makes this error everytime. Any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAM-usage and hardware upgrade 10gb RAM
David Griffiths wrote: We just put a new dual-Opteron server into our production environment. We ordered a Megaraid SCSI card and five 10k drives, and a 3Ware Escalade SATA card with six 7200 RPM drives (Maxtor) to see which ones were best. Our network guy did a bunch of benchmarking on the drives and found that SCSI-RAID5 was a bit faster than SATA-RAID0+1. The SATA was significantly cheaper (the 3Ware card was the same price as the Megaraid card, however). You might be able to tie a 10K SCSI rig if you went with the Western Digital Raptor drives. We ended up putting the SATA drives in production - some bug in the SCSI driver kept crashing MySQL on index-creation, etc. High Performance MySQL mentions that SCSI 15K drives are worth the extra money. Thanks David for your post, Does anybody else in this list have experience with SATA-RAIDs? After having done some research it looks like we'll go with a dual-Opteron an 8-12GB of RAM and a SATA-RAID10 with 8-10 250GB-SATA-discs. We are just waiting for the NCQ-SATA-drives to be available and for 2 colleagues to return from vacation since we want everybody to be here when we do that major change. (looks like we'll order the system in 2-3 weeks if the harddiscs are available) Our most important tables that get selects all the time and get updated up to 30 times a second each (or even more often depending on the time of the day) are of a total size of about 5-6 gigs. Is it realistic thinking that mysql/innodb would keep those tables totally in memory and reply to all selects without reading from the disc when we increase innodb_buffer_pool_size to 7 or 8 gigs (assuming we have 12gigs of RAM)? I just wanted to make sure nobody has hit problems with such systems. If you could just send a short We're doing something like that and it works fine I could definitly sleep better ;) thanks for all the posts so far and pointing me towards the right direction! Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum two column selects and put results in a different column
I hope this isn't a stupid question but this is my best place to ask... I have a database where I need to do a sum of a returned select... SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id=jdoe; and do a sum of another returned select... SELECT SUM(amount) from deposits WHERE customer_id=jdoe; So far so good. Above two statements work fine. The problem is I need to two sums added together and put into a third table/field (customers.balance). shorthand I would have expected the sql statement to look something like... UPDATE customers SET balance=( SUM( SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id=jdoe, SELECT SUM(amount) from deposits WHERE customer_id=jdoe ) ) WHERE customer_id=jdoe. Unfortunately it does not work. Does anyone know if what I want is possible? If so, would you be so kind as to provide sample sql statement? Thanks! Robert C. Best III - [EMAIL PROTECTED] District Technology Coordinator for N.E.R.I.C. at Potsdam Central School Phone: (315) 265-2000 x266 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About backup/restore solution
Matsunobu, Yoshinori [EMAIL PROTECTED] wrote: I'd like to ask about backup/restore(roll forward recovery) solutions using mysqldump and binary logs. ^[$B!^[(JVersion : 4.0.20 ^[$B!^[(JUsing 1 database.(ex. sample1db) ^[$B!^[(JAll tables are InnoDB. I'm considering the following solution. - backup: mysqldump --flush-logs --single-transaction sampledb1 dump1.dmp (ex. binary logs are generated hostname.001(binary log before mysqldump) hostname.002(binary log after mysqldump) ) roll forward: mysqlbinlog hostname.002 dump1.dmp mysql sampledb1 dump1.dmp - Use --master-data option of mysqldump. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL is not starting
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL is not starting Description: my mysqlserver is not starting again :-(. i have installed my mainpackage with all libraries but i become a failt if i start. the mysqld.log is as attachment in this mail. How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Sandro Dadaczynski Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.18 (Source distribution) C compiler: gcc (GCC) 3.3.3 (SuSE Linux) C++ compiler: g++ (GCC) 3.3.3 (SuSE Linux) Environment: machine, os, target, libraries (multiple lines) System: Linux PERSEUS 2.6.5-7.95-default #1 Thu Jul 1 15:23:45 UTC 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/local/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-suse-linux/3.3.3/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --enable-languages=c,c++,f77,objc,java,ada --disable-checking --libdir=/usr/lib --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i586-suse-linux Thread model: posix gcc version 3.3.3 (SuSE Linux) Compilation info: CC='gcc' CFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -Wall -DPIC -fPIC -fno-strict-aliasing' CXX='g++' CXXFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -Wall -fno-strict-aliasing -felide-constructors -fno-exceptions -fno-rtti -fPIC -DPIC' LDFLAGS='' ASFLAGS='' LIBC: -rwxr-xr-x 1 root root 1334147 Apr 5 17:16 /lib/libc.so.6 -rw-r--r-- 1 root root 16782802 Apr 5 17:16 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 5 17:16 /usr/lib/libc.so lrwxrwxrwx 1 root root 20 Jul 4 13:36 /usr/lib/libc-client.so - libc-client.so.2002e -rwxr-xr-x 1 root root 768036 Apr 6 03:50 /usr/lib/libc-client.so.2002e Configure command: ./configure '--disable-shared' '--enable-thread-safe-client' '--with-mysqld-ldflags=-static' '--with-client-ldflags=-static' '--without-berkeley-db' '--with-extra-tools' '--without-innodb' '--enable-assembler' '--enable-large-files' '--infodir=/usr/share/info' '--libdir=/usr/lib' '--libexecdir=/usr/sbin' '--localstatedir=/var/lib/mysql' '--mandir=/usr/share/man' '--prefix=/usr' '--sysconfdir=/etc' '--with-mysqld-user=mysql' '--without-debug' '--datadir=/usr/share' '--includedir=/usr/include' '--with-extra-charsets=complex' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-libwrap' 'CFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -Wall -DPIC -fPIC -fno-strict-aliasing' 'CXXFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -Wall -fno-strict-aliasing -felide-constructors -fno-exceptions -fno-rtti -fPIC -DPIC' SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL is not starting Description: my mysqlserver is not starting again :-(. i have installed my mainpackage with all libraries but i become a failt if i start. the mysqld.log is as attachment in this mail. How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Sandro Dadaczynski Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.18 (Source distribution) C compiler:gcc (GCC) 3.3.3 (SuSE Linux) C++ compiler: g++ (GCC) 3.3.3 (SuSE Linux) Environment: machine, os, target, libraries (multiple lines) System: Linux PERSEUS 2.6.5-7.95-default #1 Thu Jul 1 15:23:45 UTC 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/local/bin/cc GCC: Reading specs from
Re: Last insert id problem/bug
In article [EMAIL PROTECTED], Scott Haneda [EMAIL PROTECTED] writes: I am not sure this is just how it is, my issue, or a bug, I searched the bugs database, but don't really know what to call this in order to properly look up the info: MySQL 4.0.18-standard CREATE TABLE `addresses` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `first_name` varchar(48) NOT NULL default '', `last_name` varchar(48) NOT NULL default '', `email_address` varchar(48) NOT NULL default '', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00', PRIMARY KEY (`id`), UNIQUE KEY `emailuid` (`email_address`,`user_id`) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='foo'; CREATE TABLE addresses_incampaign ( id int(11) NOT NULL auto_increment, user_id int(11) NOT NULL default '0', address_id int(11) NOT NULL default '0', campaign_id int(11) NOT NULL default '0', updated timestamp(14) NOT NULL, added timestamp(14) NOT NULL default '00', PRIMARY KEY (id), UNIQUE KEY address_id (address_id) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='bar'; As you can see, I have a unique key on addresses.email_address and addresses.user_id, note the unique key on address_id in the second table. I insert the following data TWICE: INSERT IGNORE INTO `addresses` (`user_id`,`first_name`,`last_name`,`email_address`) VALUES (1, 'Claire', 'Altman', '[EMAIL PROTECTED]'); INSERT IGNORE INTO `addresses_incampaign` (`user_id`, `address_id`, `campaign_id`) VALUES (1,LAST_INSERT_ID(), 2); Notice I am picking up the last_insert_id in the second insert, I then insert that data again, since I am using insert ignore and since there are unique keys on this, I should only still have one records in each table. Results are as follows: mysql select * from addresses; +-+-++---+---+ | id | user_id | first_name | last_name | email_address | +-+-++---+---+ | 148 | 1 | Claire | Altman| [EMAIL PROTECTED] | +-+-++---+---+ 1 row in set (0.00 sec) mysql select * from addresses_incampaign ; +-+-++-+ | id | user_id | address_id | campaign_id | +-+-++-+ | 128 | 1 |148 | 2 | | 129 | 1 |149 | 2 | - SOULD NOT HAVE HAPPENED +-+-++-+ 2 rows in set (0.00 sec) Record 129 should not have been inserted, it should not have ever gotten a insert ID back from mysql, mysql said back 149, which is in fact the next record, but no new record was added to addresses so it really is not valid. Workarounds and suggestions? The behavior is indeed strange, but it's not a bug, since it's documented: If you use `INSERT IGNORE' and the record is ignored, the `AUTO_INCREMENT' counter still is incremented and `LAST_INSERT_ID()' returns the new value. The solution is, of course, not to use INSERT IGNORE at all. You should instead catch any error, check that it's ER_DUP_KEY and not try the second INSERT in that case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum two column selects and put results in a different column
I know there are more ways to solve this problem (I can think of at least 2 more) but I think the easiest approach is to break this into two stages. First stage, we compute the sums of the purchases and the credits: SELECT @purchases := SUM(amount_of_credit_used) FROM purchases WHERE customer_id='jdoe'; SELECT @deposits := SUM(Amount) FROM deposits WHERE customer_id='jdoe' Then we can do the update to the customers table UPDATE customers SET balance = @[EMAIL PROTECTED] WHERE customer_ID = 'jdoe'; A second method is to perform two UPDATES in sequence: UPDATE customers c INNER JOIN purchases p on c.customer_ID = p.customer_ID SET c.balance = SUM(p.amount_of_credit_used) WHERE c.customer_ID = 'jdoe'; UPDATE customers c INNER JOIN deposits d on c.customer_ID = d.customer_ID SET c.balance = c.balance + SUM(d.amount) WHERE c.customer_ID = 'jdoe'; The first update replaces the old value in customers.balance with the credit used total while the second update builds on the first value. Do either of these approaches make sense for you? I don't know how busy your database is but for a real-world application I would either lock the tables to prevent outside updates during the computation, or I would wrap the whole process with a transaction. That way you can avoid updating the balance with only part of the information. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rob Best [EMAIL PROTECTED] wrote on 07/23/2004 07:24:02 AM: I hope this isn't a stupid question but this is my best place to ask... I have a database where I need to do a sum of a returned select... SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id=jdoe; and do a sum of another returned select... SELECT SUM(amount) from deposits WHERE customer_id=jdoe; So far so good. Above two statements work fine. The problem is I need to two sums added together and put into a third table/field (customers.balance). shorthand I would have expected the sql statement to look something like... UPDATE customers SET balance=( SUM( SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id=jdoe, SELECT SUM(amount) from deposits WHERE customer_id=jdoe ) ) WHERE customer_id=jdoe. Unfortunately it does not work. Does anyone know if what I want is possible? If so, would you be so kind as to provide sample sql statement? Thanks! Robert C. Best III - [EMAIL PROTECTED] District Technology Coordinator for N.E.R.I.C. at Potsdam Central School Phone: (315) 265-2000 x266 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb assertion failure after binary backup-restore
Hi List, We are using 4.0.15a MySQL. We need to backup database at times and we employ the following method FLUSH TABLES WITH READ LOCK tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1 UNLOCK TABLES and restore is reverse. We cannot overwrite all databases, some of them needs to be unchanged across backup and restores called persistent databases (just 1% of full data) so we employ the following mysqldump on persistent databases stop mysql cd /usr/local/mysql/data tar -zxf backup.tar.gz start mysql destroy persistent databases run mysqldump sql files to get database back to shape This method works fine expect for the case where backup is taken immediately after table creation and population. When we start the mysql after restore of this backup. It comes up, but mysql connections to it asking for persistent database destroy hangs. When I do a processlist I get ++--+---++-+--+-+--+ | Id | User | Host | db | Command | Time | State | Info | ++--+---++-+--+-+--+ | 1 | root | localhost || Query | 55 | Waiting on cond | drop database persistent1| | 4 | root | localhost || Query | 0| | show processlist | ++--+---++-+--+-+--+ When I open up another mysql client and query some of the tables, they also hang and processlist at that time shows ++--+---+-+-+--+-+-+ | Id | User | Host | db | Command | Time | State | Info | ++--+---+-+-+--+-+-+ | 1 | root | localhost | | Query | 171 | Waiting on cond | drop database persistent1 | | 6 | root | localhost | data | Query | 59 | Opening tables | select * from my_data | | 11 | root | localhost | | Query | 0| | show processlist| ++--+---+-+-+--+-+-+ After some time mysql.err reports 040721 14:43:49 InnoDB: Assertion failure in thread 88 in file fsp0fsp.c line 2950 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950 InnoDB: Thread 30 stopped in file os0sync.c line 509 InnoDB: Thread 96 stopped in file sync0sync.ic line 109 InnoDB: Thread 29 stopped in file sync0arr.c line 126 Please help me in resolving the issue by giving directions on how to proceed. your help is much appreciated. Thanks for your time, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spatial data in MySQL
Hi List, I have been looking for solutions to my problem for a while and this morning someone told me that MySQL 5.1 might be the answer. In short what I need is an interactive map displayed in a web browser. Users should be able to click on any point in the map and add text. The map and the data added by the users must reside in a database so that I can run reports against it. FOr example,I will want to generate a report containing all the data added by users between two points on the map. Typically this will be a road and the data added by the users will include variables such as road conditions, location of road work crews, etc. Am I on the right track with MySQL or am I barking up the wrong tree? Thanks -- Lawrence Strydom Open Source Software Solutions Consultant email: [EMAIL PROTECTED] web: www.rpcdata.co.za www.midafrica.com Tel: +27 (0)44 8770453 Tel: +27 (0)21 4210882 Mobile: 0833809341 News and Info on the Garden Route at http://wildnews.gardenrouteinfo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there any Performance Improvement / Tuning Software for MySQL?
Your settings need to be based upon usage patterns. Some systems have lots of inserts, others are mostly selects, some have lots of joins, others don't. All these would have different settings. There are even cases where you would want a table to be InnoDB instead of ISAM or the other way around. It's all part of being a DBA, there is no holy grail where you can just push a button and magically have the best settings. Oracle and IBM (DB2) are just now coming out with self-tuning systems. So, you would need a program to analyze your query logs (over say a few weeks), analyze you current settings, compare them against the hardware it's running on (which will dictates the limits) and come up with a recommendation. Usage patterns may vary throughout the day, so the program would need to take into account important times, otherwise your backup may run very well, but everything else would be slow. A design change in the database could then completely change the usage patterns, so you would then need to start your analysis over. But then, a lack of an index on a field could be the one thing that's killing your performance, not your settings. Almost no settings would help a poorly designed database or poor query syntax. I know I didn't really help you, but I don't think what you are looking for exists. Best advice I could give is to look at your slow query log to see what's getting bogged down. On Jul 23, 2004, at 3:52 AM, Terence wrote: Is there any kind of performance / tuning guide software for MySQL? I am looking for something which can look at the SHOW VARIABLES and SHOW STATUS commands, do some calculations and give some tips on how to improve the system? Any such product available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb assertion failure after binary backup-restore
In some cases mysql crashes while restore is trying to destroy persistent databases and mysql.err says 000121 21:02:23 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 867418 InnoDB: Doing recovery: scanned up to log sequence number 0 867418 InnoDB: Page directory corruption: supremum not pointed to 000121 21:02:23 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex (all zeros and dots in ascii) ;InnoDB: End of page dump 000121 21:02:23 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page directory corruption: supremum not pointed to 000121 21:02:23 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex ...(all zeros and dots in ascii) ;InnoDB: End of page dump 000121 21:02:23 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer a1027ff8 InnoDB: buf pool start is at 2084, number of pages 512 000121 21:02:23 InnoDB: Assertion failure in thread 43 in file include/buf0buf.ic line 284 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=20 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 29183 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Writing a core file 000121 21:02:24 mysqld ended Thanks, Sp.Raja Original Message From: Sp.Raja [EMAIL PROTECTED] To: '[EMAIL PROTECTED] ' [EMAIL PROTECTED] Date: Fri, Jul-23-2004 7:13 PM Subject: Innodb assertion failure after binary backup-restore Hi List, We are using 4.0.15a MySQL. We need to backup database at times and we employ the following method FLUSH TABLES WITH READ LOCK tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1 UNLOCK TABLES and restore is reverse. We cannot overwrite all databases, some of them needs to be unchanged across backup and restores called persistent databases (just 1% of full data) so we employ the following mysqldump on persistent databases stop mysql cd /usr/local/mysql/data tar -zxf backup.tar.gz start mysql destroy persistent databases run mysqldump sql files to get database back to shape This method works fine expect for the case where backup is taken immediately after table creation and population. When we start the mysql after restore of this backup. It comes up, but mysql connections to it asking for persistent database destroy hangs. When I do a processlist I get ++--+---++-+--+-+--+ | Id | User | Host | db | Command | Time | State | Info | ++--+---++-+--+-+--+ | 1 | root | localhost || Query | 55 | Waiting on cond | drop database persistent1| | 4 | root | localhost || Query | 0| | show processlist | ++--+---++-+--+-+--+ When I open up another mysql client and query some of the tables, they also hang and processlist at that time shows ++--+---+-+-+--+-+-+ | Id | User | Host | db | Command | Time | State | Info| ++--+---+-+-+--+-+-+ | 1 | root | localhost | | Query | 171 | Waiting on cond | drop database persistent1 | | 6 | root | localhost | data | Query | 59 | Opening tables | select * from my_data | | 11 | root | localhost | | Query | 0| | show processlist| ++--+---+-+-+--+-+-+ After some time mysql.err reports 040721 14:43:49 InnoDB: Assertion failure in thread 88 in
Re: How do I import a .dmp file?
There is an easier path- you *could buy* a data migration tool such as ispirer http://www.ispirer.com/products HTH, Martin Gainty (cell) 617-852-7822 From: matt ryan [EMAIL PROTECTED] To: Martin Gainty [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: How do I import a .dmp file? Date: Thu, 22 Jul 2004 14:05:05 -0400 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc5-f29.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Thu, 22 Jul 2004 11:08:50 -0700 Received: (qmail 17882 invoked by uid 109); 22 Jul 2004 18:05:14 - Received: (qmail 17863 invoked from network); 22 Jul 2004 18:05:14 - Received: pass (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jH8exqBaPPNR7eb/wfYap36 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Archive: http://lists.mysql.com/mysql/169527 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] User-Agent: Mozilla Thunderbird 0.7a (Windows/20040614) X-Accept-Language: en-us, en References: [EMAIL PROTECTED] In-Reply-To: [EMAIL PROTECTED] X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 22 Jul 2004 18:08:50.0607 (UTC) FILETIME=[F0E0C3F0:01C47016] David Did you look at MYSQL LOAD DATA INFILE ??? doc is available at http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html regards, Load data infile only works with a text file going into a table, if the data is in another format, like raw oracle, or EBCDIC it wont work, you'll need to convert it to ascii first. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grants for non-root
I try to set *all* privileges to a user, which should not called 'root' but should have its privileges (I'm connected as root): mysql grant all on *.* to 'mysqladmin'@'localhost'; Query OK, 0 rows affected (0.00 sec) But now I get mysql show grants for 'mysqladmin'@'localhost'; ERROR 1141: There is no such grant defined for user 'mysqladmin' on host 'localhost' What does it mean? What's wrong? The problem occurs with MySQL-4.0.20 at Linux Fedora, it does not occur with MySQL-3.23. Thanks for any help. Regards, Wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial data in MySQL
Solo hablo espaol. (Que Problema) Estoy tratando de hacer lo mismo que tu. ya ingrese la parte grafica a MySQL. Ahora estoy tratando de Visualizarala por un browser. Sorry, I don't write English clearly. I speak Spanish (That is Problem) I am trying to do just like your. The map already enters to MySQL. Now I am trying To visualize it by browser. -Mensaje original- De: Lawrence Strydom [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 23 de Julio de 2004 09:09 a.m. Para: [EMAIL PROTECTED] Asunto: spatial data in MySQL Hi List, I have been looking for solutions to my problem for a while and this morning someone told me that MySQL 5.1 might be the answer. In short what I need is an interactive map displayed in a web browser. Users should be able to click on any point in the map and add text. The map and the data added by the users must reside in a database so that I can run reports against it. FOr example,I will want to generate a report containing all the data added by users between two points on the map. Typically this will be a road and the data added by the users will include variables such as road conditions, location of road work crews, etc. Am I on the right track with MySQL or am I barking up the wrong tree? Thanks -- Lawrence Strydom Open Source Software Solutions Consultant email: [EMAIL PROTECTED] web: www.rpcdata.co.za www.midafrica.com Tel: +27 (0)44 8770453 Tel: +27 (0)21 4210882 Mobile: 0833809341 News and Info on the Garden Route at http://wildnews.gardenrouteinfo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: grants for non-root
I have MySQL 4.0.18-standard on RH 9 and it works fine + mysql grant all on *.* to 'mysqladmin'@'localhost'; Query OK, 0 rows affected (0.06 sec) mysql show grants for 'mysqladmin'@'localhost'; +-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT ALL PRIVILEGES ON *.* TO 'mysqladmin'@'localhost' | +-+ 1 row in set (0.00 sec) + I would recommend checking the user table in mysql database and see what change your GRANT query did, if any. Let us see what it looks like, if there is any entry for the user you are GRANTing privileges. Regards Aman On Fri, 2004-07-23 at 09:50, Wolfgang Riedel wrote: I try to set *all* privileges to a user, which should not called 'root' but should have its privileges (I'm connected as root): mysql grant all on *.* to 'mysqladmin'@'localhost'; Query OK, 0 rows affected (0.00 sec) But now I get mysql show grants for 'mysqladmin'@'localhost'; ERROR 1141: There is no such grant defined for user 'mysqladmin' on host 'localhost' What does it mean? What's wrong? The problem occurs with MySQL-4.0.20 at Linux Fedora, it does not occur with MySQL-3.23. Thanks for any help. Regards, Wolfgang -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can not start mysql daemon
# perror 2 Error code 2: No such file or directory You sure that directory exists? On Tue, 2004-07-20 at 15:48, [EMAIL PROTECTED] wrote: Description: I can not start the mysqld daemon. I had it running for a long time, but then had to reboot. Then I could no longer connect. How-To-Repeat: followed the directions on http://wiki.amazon.com/?MySQLInstallation, but to no avail. When I run the first command there: sudo adduser -d /workplace2/mysql -c MySQL account mysql I am informed that this user already exists. This makes sense, but doing the daemon start: sudo -u mysql ./mysqld_safe --user=mysql --datadir=/workplace2/mysql/data failes in the correct directory because there is no executable called mysqld_safe at all. I tried setting up a new existense as mysql2, but to no luck. I also tried starting it via the output that was generated when I did a mysql_install_db. It said that it could not change directories: [EMAIL PROTECTED]/opt/third-party/depot/Linux-2.4c2.2-i686/mysql-3.23.55/libexec/mysqld: Can't change dir to '/opt/disco/third-party/Linux-2.4c2.2-i686/mysql-3.23.55/var/' (Errcode: 2) Fix: Submitter-Id:pohlhaut Originator: Paul Ohlhaut Organization: Seller Central MySQL support: [none | licence | email support | extended email support ] Synopsis:can not start mysql daemon Severity:critical Priority: Category:mysql Class: Release: mysql-3.23.55 (Source distribution) Environment: Linux Red Hat 7.2 System: Linux pohlhaut.desktop.amazon.com 2.4.21-2.3a #1 Fri Dec 5 04:53:08 PST 2003 i686 unknown Architecture: i686 Some paths: /opt/third-party/bin/perl /opt/third-party/bin/make /usr/bin/gmake /opt/third-party/bin/gcc GCC: Reading specs from /opt/third-party/depot/Linux-2.4c2.2-i686/gcc-2.95.3/lib/gcc-lib/i686-pc-linux-gnu/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='/opt/third-party/depot/Linux-2.4c2.2-i686/gcc-2.95.3/bin/gcc' CFLAGS=' -mcpu=pentiumpro -D_FILE_OFFSET_BITS=64' CXX='/opt/third-party/depot/Linux-2.4c2.2-i686/gcc-2.95.3/bin/g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 30 2003 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283964 Dec 8 2001 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314296 Dec 8 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 8 2001 /usr/lib/libc.so Configure command: ./configure '--prefix=/opt/third-party/depot/Linux-2.4c2.2-i686/mysql-3.23.55' 'CC=/opt/third-party/depot/Linux-2.4c2.2-i686/gcc-2.95.3/bin/gcc' 'CFLAGS= -mcpu=pentiumpro -D_FILE_OFFSET_BITS=64' 'CXX=/opt/third-party/depot/Linux-2.4c2.2-i686/gcc-2.95.3/bin/g++' -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial data in MySQL
Hi all! Estaba trabajando con el ArcView by ESRI, con una extension MySQL Connection http://arcscripts.esri.com/details.asp?dbid=12896 l) permitiendo poder exportar un Shapefile (Archivo Vectorial) a MySQL generandome el siguiente Peru.sql(que con gusto enviaria al correo que me preste su ayuda)mediante sentencias mySQL create basedata peru y c:\mysql\bin\mysql -u root peru.sql he llegado a ingresarlo. El Problema es que aun no puedo visualizarlo. Wilder Castelo Rojas Analista de Sistemas III Instituto Nacional de Estadistica Lima - Perú
MySQL 4.1 cluster, help with BUILD/compile-pentium-max
Hello, the following problem occurs when I want to configure MySQL 4.1: Building aclocal.m4 Running autoheader to build config.hin Running autoconf to build configure 645046 644916 Building RPC client/server files Building tags +++ cd innobase +++ aclocal +++ autoheader +++ aclocal +++ automake +++ autoconf +++ '[' -d gemini ']' +++ CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g +++ CXX=gcc +++ CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g +++ CXXLDFLAGS= +++ ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-readline --with-innodb --with-berkeley-db --with-embedded-server --enable-thread-safe-client --with-vio --with-raid --with-ndbcluster --enable-local-infile checking build system type... i686-pc-linux checking host system type... i686-pc-linux checking target system type... i686-pc-linux ./configure: line 1496: syntax error near unexpected token `mysql,' ./configure: line 1496: `AM_INIT_AUTOMAKE(mysql, 4.1.4-beta)' Zlib works, just tested. The System is Debian Sarge with the following tools/libs: groff-base 1.18.1.1-1 installed: No available version in archive mc 1:4.6.0-4.6.1-pre1-1 installed: No available version in archive pciutils 1:2.1.11-11 installed: No available version in archive m4 1.4.1-1 installed: No available version in archive file 4.09-1 installed: No available version in archive gettext 0.14.1-5 installed: No available version in archive gcc-3.3-base 1:3.3.4-5 installed: No available version in archive zlib1g-dev 1:1.2.1.1-3 installed: No available version in archive rpm 4.0.4-28 installed: No available version in archive sysv-rc 2.85-22 installed: No available version in archive zlib1g 1:1.2.1.1-3 installed: No available version in archive lynx 2.8.5-1 installed: No available version in archive libconsole 1:0.2.3dbs-52 installed: No available version in archive debconf-utils 1.4.29 installed: No available version in archive iputils-ping 3:20020927-2 installed: No available version in archive libstdc++5 1:3.3.4-2 installed: No available version in archive findutils 4.1.20-4 installed: No available version in archive libdb4.0 4.0.14-2 installed: No available version in archive libdb3 3.2.9-20 installed: No available version in archive libreadline4 4.3-11 installed: No available version in archive login 1:4.0.3-28.4 installed: No available version in archive libsigc++-1.2-5c102 1.2.5-1 installed: No available version in archive libpam-modules 0.76-22 installed: No available version in archive binutils 2.14.90.0.7-8 installed: No available version in archive cpp-3.3 1:3.3.4-5 installed: No available version in archive coreutils 5.0.91-2 installed: No available version in archive libtextwrap1 0.1-1 installed: No available version in archive patch 2.5.9-2 installed: No available version in archive grub 0.95+cvs20040624-3 installed: No available version in archive libwrap0 7.6.dbs-4 installed: No available version in archive libss2 1.35-6 installed: No available version in archive tasksel 2.03 installed: No available version in archive libusb-0.1-4 1:0.1.8-11 installed: No available version in archive netkit-inetd 0.10-9 installed: No available version in archive libpam0g 0.76-22 installed: No available version in archive libncurses5 5.4-4 installed: No available version in archive libgdbm3 1.8.3-2 installed: No available version in archive nano 1.2.4-1 installed: No available version in archive base-config 2.35 installed: No available version in archive perl 5.8.4-2 installed: No available version in archive sysvinit 2.85-22 installed: No available version in archive initscripts 2.85-22 installed: No available version in archive base-files 3.0.16 installed: No available version in archive pppoe 3.5-3 installed: No available version in archive console-common 0.7.45 installed: No available version in archive procps 1:3.2.1-2 installed: No available version in archive gawk 1:3.1.3-3 installed: No available version in archive libcomerr2 1.35-6 installed: No available version in archive psmisc 21.5-1 installed: No available version in archive libglib2.0-0 2.4.2-1 installed: No available version in archive manpages 1.67-1 installed: No available version in archive liblzo1 1.08-1 installed: No available version in archive grep 2.5.1.ds1-2 installed: No available version in archive bash 2.05b-15 installed: No available version in archive liblocale-gettext-perl 1.01-17 installed: No available version in archive libgnutls10 1.0.4-3 installed: No available version in archive libblkid1 1.35-6 installed: No available
connect to an embedded database
Hello, I made a small c++ program which uses the mysql embedded server. I would like to manually connect to the local database for faster debugging. Is it possible ? Thanks. Adrien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IMPORTING
Dear friends, When I import records fron csv file.I window pops up, stating total number of records imported, however when I check table nothing has been imported.Any guidance,
Re: IMPORTING
On Friday 23 July 2004 11:14 am, [EMAIL PROTECTED] wrote: Dear friends, When I import records fron csv file.I window pops up, stating total number of records imported, however when I check table nothing has been imported. You are using some kind of middle-man program to populate a database. In fact, your description sounds awfully similar to how Microsoft Access works. What is the program you use to do the import? Can you describe the precise steps you take? Any guidance, Tell us where you are so we can tell you how to get where you want to go. Robert J Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IMPORTING
You don't say what version you are running. Did you forget to commit? [EMAIL PROTECTED] wrote: Dear friends, When I import records fron csv file.I window pops up, stating total number of records imported, however when I check table nothing has been imported.Any guidance, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
no subject
Hi, I hope someone on the list can help me. Is there an easy and straightforward way way of displaying the data from three tables in a spreadsheet like format using MySQL 4.0? Table A has the column headings Table B has the row headings Table C is a line items file than cross relates the two Many thanks -- Aeon McNulty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum two column selects and put results in a different column
Thanks! that worked great!
I ended up using the first one, I just liked the look of it better.
Because the customer might not have any deposit records or any
purchases I changed the code to this:
SELECT @purchases := SUM(amount_of_credit_used)
FROM purchases WHERE customer_id=jdoe;
SELECT @deposits := SUM(amount)
FROM deposits WHERE customer_id=jdoe;
SELECT @purchases :=IFNULL(@purchases, 0);
SELECT @deposits :=IFNULL(@deposits, 0);
UPDATE customers
SET balance_cache = @[EMAIL PROTECTED]
WHERE customer_ID = jdoe;
Notice the IFNULL lines.
Thanks a million!!
I would nice to be able to run the above sql statement for all
customers in the 'customers' table but I found a work around (a big
.sql file that runs every night).
Something like:
SELECT @customers := customer_id from customers;
foreach @customers
{
SELECT @purchases := SUM(amount_of_credit_used)
FROM purchases WHERE customer_id=@customers;
...
...
}
But hey, I've got it working at least (thanks again!)
On Jul 23, 2004, at 9:05 AM, [EMAIL PROTECTED] wrote:
I know there are more ways to solve this problem (I can think of at
least
2 more) but I think the easiest approach is to break this into two
stages.
First stage, we compute the sums of the purchases and the credits:
SELECT @purchases := SUM(amount_of_credit_used)
FROM purchases
WHERE customer_id='jdoe';
SELECT @deposits := SUM(Amount)
FROM deposits
WHERE customer_id='jdoe'
Then we can do the update to the customers table
UPDATE customers
SET balance = @[EMAIL PROTECTED]
WHERE customer_ID = 'jdoe';
A second method is to perform two UPDATES in sequence:
UPDATE customers c
INNER JOIN purchases p
on c.customer_ID = p.customer_ID
SET c.balance = SUM(p.amount_of_credit_used)
WHERE c.customer_ID = 'jdoe';
UPDATE customers c
INNER JOIN deposits d
on c.customer_ID = d.customer_ID
SET c.balance = c.balance + SUM(d.amount)
WHERE c.customer_ID = 'jdoe';
The first update replaces the old value in customers.balance with the
credit used total while the second update builds on the first value.
Do either of these approaches make sense for you?
I don't know how busy your database is but for a real-world
application I
would either lock the tables to prevent outside updates during the
computation, or I would wrap the whole process with a transaction. That
way you can avoid updating the balance with only part of the
information.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Rob Best [EMAIL PROTECTED] wrote on 07/23/2004 07:24:02 AM:
I hope this isn't a stupid question but this is my best place to
ask...
I have a database where I need to do a sum of a returned select...
SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id=jdoe;
and do a sum of another returned select...
SELECT SUM(amount) from deposits WHERE customer_id=jdoe;
So far so good. Above two statements work fine.
The problem is I need to two sums added together and put into a third
table/field (customers.balance).
shorthand I would have expected the sql statement to look something
like...
UPDATE customers SET balance=(
SUM(
SELECT SUM(purchases.amount_of_credit_used) WHERE
customer_id=jdoe,
SELECT SUM(amount) from deposits WHERE customer_id=jdoe
)
) WHERE customer_id=jdoe.
Unfortunately it does not work.
Does anyone know if what I want is possible? If so, would you be so
kind as to provide sample sql statement?
Thanks!
Robert C. Best III - [EMAIL PROTECTED]
District Technology Coordinator
for N.E.R.I.C. at Potsdam Central School
Phone: (315) 265-2000 x266
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Robert C. Best III - [EMAIL PROTECTED]
Computer Coordinator @ Potsdam Central School
Contact Info At: http://rob.potsdam.k12.ny.us
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL user passwords and ColdFusion MX6.1
Helpful Hint: I had a major problem not being able to register a DSN (Data Source Name) with the CF Administrator, using username and password from a MySQL 4.1.3 beta user table. After much hair-pulling, I discovered that CF cannot apparently handle the new 41-character format of the passwords. Killing these off, restarting with --old-passwords and redoing them as 16-char passwords allowed CF to register the DSNs. Hope this might help someone else who's going prematurely bald. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: no subject
If I understand you correctly Basically you want to convert this query output: +---+---+---+ |A.value|B.value|C.value| +---+---+---+ | a1 | b1 | c1 | | a1 | b1 | c2 | | a2 | b1 | c3 | | a2 | b1 | c4 | | a1 | b2 | c5 | | a1 | b2 | c6 | | a2 | b2 | c7 | | a2 | b2 | c8 | | a1 | b3 | c9 | | ... | ... | ... | | a(j) | b(k) | c(n) | +---+---+---+ into something like: +---+--+--+--+---+ | |a1| a2 |... | a(j)| +---+--+--+--+---+ | b1 | f(c1,c2) | f(c3,c4) |... |...| | b2 | f(c5,c6) | f(c7,c8) |... |...| | b3 |f(c9,...) | ...|... |...| | ... | ...| ...|... |...| | b(k) | f(c1,c2) | f(c2,c3) |... |f(...,C(n))| +---+--+--+--+---+ Where f() represents one of the aggregate functions: SUM, AVG, STD, MIN, MAX, etc. Bad news: MySQL does not have an SQL-only solution for the general case (when you do not know the number of columns) Good news: it is usually quite simple to write a script to handle the general case. Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. If you would like help in writing a specific pivot table (cross-tab) query. Please post your tables' structures (I prefer the output of SHOW CREATE TABLE xxx) and tell us how you want your cross-tab report setup (column headers from where, row headers from where, and a formula to use for your cells) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 12:30:21 PM: Hi, I hope someone on the list can help me. Is there an easy and straightforward way way of displaying the data from three tables in a spreadsheet like format using MySQL 4.0? Table A has the column headings Table B has the row headings Table C is a line items file than cross relates the two Many thanks -- Aeon McNulty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking in MyISAM
Does MyISAM lock the whole table when doing SELECTs? How about if there are several processors? Is there locking?
Re: Returning Column and Row Headings Like a Spreadsheet
Hi David Thanks your response. I'm using Lasso but that's largely unimportant because I still need to get the data returned in a way that I can use it. I should not have said displaying - I meant returning. I need to return only one column from table A (there could be up to 14 records). I need to return the data in such a way that I can display the result horizontally, as column headings, with the data in table C still being correctly related to them. I need to return two columns from table B (there could be an unlimited number of records). I need to show this data vertically, with the first of the two columns as row headings. And they need to also correctly relate to the data in table C. I then need to return one column from table C (there could be an unlimited number of records) so that I can display them in their correct places as they relate to tables A and B. Table C has two foreign keys that relate to tables A and B respectively. I need to display something like this: A B C D E F G 1 1 XX 2 2 X 3 3X 4 4 The A, B, C data comes from table A, the numbers from table B and the Xs from table C. Sorry, I'm probably not explaining myself very well. Can you suggest anything? Many thanks -- Aeon McNulty On 23/7/04 5:39 pm, David Brieck Jr. [EMAIL PROTECTED] wrote: On Fri, 23 Jul 2004 17:30:21 +0100, Aeon McNulty [EMAIL PROTECTED] wrote: Hi, I hope someone on the list can help me. Is there an easy and straightforward way way of displaying the data from three tables in a spreadsheet like format using MySQL 4.0? Table A has the column headings Table B has the row headings Table C is a line items file than cross relates the two Many thanks What language are you using? MySQL provides data, not an end user display. If this just a one time thing you might try using MySQLFront, it will let you save a result set to CSV. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning Column and Row Headings Like a Spreadsheet
Hi Shawn Yes, that's pretty much it. I'm impressed that you managed to work that out from my previous email which was a bit thin on detail! Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. The number of columns are variable but they don't vary often so I can do a quick query first to give me the number and then use that number to specify the number of columns in the second query. Does that make sense? Please see my other post. Does that give you enough information? Many thanks -- Aeon McNulty On 23/7/04 6:36 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: If I understand you correctly Basically you want to convert this query output: +---+---+---+ |A.value|B.value|C.value| +---+---+---+ | a1 | b1 | c1 | | a1 | b1 | c2 | | a2 | b1 | c3 | | a2 | b1 | c4 | | a1 | b2 | c5 | | a1 | b2 | c6 | | a2 | b2 | c7 | | a2 | b2 | c8 | | a1 | b3 | c9 | | ... | ... | ... | | a(j) | b(k) | c(n) | +---+---+---+ into something like: +---+--+--+--+---+ | |a1| a2 |... | a(j)| +---+--+--+--+---+ | b1 | f(c1,c2) | f(c3,c4) |... |...| | b2 | f(c5,c6) | f(c7,c8) |... |...| | b3 |f(c9,...) | ...|... |...| | ... | ...| ...|... |...| | b(k) | f(c1,c2) | f(c2,c3) |... |f(...,C(n))| +---+--+--+--+---+ Where f() represents one of the aggregate functions: SUM, AVG, STD, MIN, MAX, etc. Bad news: MySQL does not have an SQL-only solution for the general case (when you do not know the number of columns) Good news: it is usually quite simple to write a script to handle the general case. Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. If you would like help in writing a specific pivot table (cross-tab) query. Please post your tables' structures (I prefer the output of SHOW CREATE TABLE xxx) and tell us how you want your cross-tab report setup (column headers from where, row headers from where, and a formula to use for your cells) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 12:30:21 PM: Hi, I hope someone on the list can help me. Is there an easy and straightforward way way of displaying the data from three tables in a spreadsheet like format using MySQL 4.0? Table A has the column headings Table B has the row headings Table C is a line items file than cross relates the two Many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Consolidating Data
Greetings! I have several (~12) web servers which all record web metrics to their own local mysql database. I would like to consolidate data from each web server database to one master DB to within the hour. I wish to avoid running multiple instances of mysql on the master server, so replication is not an option. What are the best practices for managing the consolidation of data? Is it best to export the data on each web server and perform frequent bulk loads on the master server? Or, is it better to have a robust Perl script on the master server that is responsible for pulling records from each web server? I estimate 10,000 to 30,000 records per web server, per day with the average row size of 100 Bytes. The web servers are all in remote locations. The end goal is to have all web metrics available on *one* server from which a reporting server (M$ SQL server). Lastly, are there any experts on this list willing and available to code and document this, given more details, of course? -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning Column and Row Headings Like a Spreadsheet
I will be more than happy to help you build a case-specific crosstab report. I have almost enough information. Could you post a 3 column-query that will actually produce from your data the information you would like to have reformatted? Column one needs to be your column names, column two needs to be your row headers, and the 3rd column needs to have the data you want to see merged into your cells (I won't need the data only the query). It should look something like: SELECT a.name, b.name, c.data FROM c INNER JOIN a on a.id = c.a_id INNER JOIN b on b.id = c.b_id WHERE . (any condition will do) I will also need a list of the potential column headers (this time I do need the data): SELECT DISTINCT name FROM a With that I will have enough information to pivot your query into a crosstab report. Hopefully you will be able to spot the pattern and be able to adjust it to fit your other situations as well. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 02:12:37 PM: Hi Shawn Yes, that's pretty much it. I'm impressed that you managed to work that out from my previous email which was a bit thin on detail! Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. The number of columns are variable but they don't vary often so I can do a quick query first to give me the number and then use that number to specify the number of columns in the second query. Does that make sense? Please see my other post. Does that give you enough information? Many thanks -- Aeon McNulty On 23/7/04 6:36 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: If I understand you correctly Basically you want to convert this query output: +---+---+---+ |A.value|B.value|C.value| +---+---+---+ | a1 | b1 | c1 | | a1 | b1 | c2 | | a2 | b1 | c3 | | a2 | b1 | c4 | | a1 | b2 | c5 | | a1 | b2 | c6 | | a2 | b2 | c7 | | a2 | b2 | c8 | | a1 | b3 | c9 | | ... | ... | ... | | a(j) | b(k) | c(n) | +---+---+---+ into something like: +---+--+--+--+---+ | |a1| a2 |... | a(j)| +---+--+--+--+---+ | b1 | f(c1,c2) | f(c3,c4) |... |...| | b2 | f(c5,c6) | f(c7,c8) |... |...| | b3 |f(c9,...) | ...|... |...| | ... | ...| ...|... |...| | b(k) | f(c1,c2) | f(c2,c3) |... |f(...,C(n))| +---+--+--+--+---+ Where f() represents one of the aggregate functions: SUM, AVG, STD, MIN, MAX, etc. Bad news: MySQL does not have an SQL-only solution for the general case (when you do not know the number of columns) Good news: it is usually quite simple to write a script to handle the general case. Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. If you would like help in writing a specific pivot table (cross-tab) query. Please post your tables' structures (I prefer the output of SHOW CREATE TABLE xxx) and tell us how you want your cross-tab report setup (column headers from where, row headers from where, and a formula to use for your cells) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 12:30:21 PM: Hi, I hope someone on the list can help me. Is there an easy and straightforward way way of displaying the data from three tables in a spreadsheet like format using MySQL 4.0? Table A has the column headings Table B has the row headings Table C is a line items file than cross relates the two Many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Logging on through ODBC w/o UN Password (Why is my query tool allowing this?)
I am able to log onto a database through ODBC without using a user name or password. I don't want this to be possible for obvious security reasons. What do I need to do to prevent this? This seems pretty freaky!
Re: Consolidating Data
You could consider another option: To have a robust script (daemon?) in each web server that periodically pushes metrics data into the master. I think this gives you better control over your data. If you lose connection or something goes wrong your web servers will be able to tell what DIDN'T make the transfer better than the master could. Also, considering the volume of information your master server will be asked to handle, it may be better to NOT require it to run 12 additional polling daemons (or however they will be written) on top of doing everything else. My 2 cents, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Marc Knoop [EMAIL PROTECTED] wrote on 07/23/2004 02:41:38 PM: Greetings! I have several (~12) web servers which all record web metrics to their own local mysql database. I would like to consolidate data from each web server database to one master DB to within the hour. I wish to avoid running multiple instances of mysql on the master server, so replication is not an option. What are the best practices for managing the consolidation of data? Is it best to export the data on each web server and perform frequent bulk loads on the master server? Or, is it better to have a robust Perl script on the master server that is responsible for pulling records from each web server? I estimate 10,000 to 30,000 records per web server, per day with the average row size of 100 Bytes. The web servers are all in remote locations. The end goal is to have all web metrics available on *one* server from which a reporting server (M$ SQL server). Lastly, are there any experts on this list willing and available to code and document this, given more details, of course? -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql grant privileges to many DB
Hello, MySQL gurus, I am wondering how I can grant a user the privileges to all the db except for mysql DB. THere are many databases so I don't want to grant one by one. But 'revoke all on mysql.* from user' does not work. Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Logging on through ODBC w/o UN Password (Why is my query t ool allowing this?)
Is the ODBC account still enabled on the database server? -Original Message- From: john sayre To: [EMAIL PROTECTED] Sent: 7/23/04 1:38 PM Subject: Logging on through ODBC w/o UN Password (Why is my query tool allowing this?) I am able to log onto a database through ODBC without using a user name or password. I don't want this to be possible for obvious security reasons. What do I need to do to prevent this? This seems pretty freaky! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql grant privileges to many DB
Have you tried: GRANT USAGE on mysql.* TO ... (fill in with your user's information). From the docs (http://dev.mysql.com/doc/mysql/en/GRANT.html) USAGE = Synonym for ``no privileges'' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ginger Cheng [EMAIL PROTECTED] wrote on 07/23/2004 02:58:31 PM: Hello, MySQL gurus, I am wondering how I can grant a user the privileges to all the db except for mysql DB. THere are many databases so I don't want to grant one by one. But 'revoke all on mysql.* from user' does not work. Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Logging on through ODBC w/o UN Password (Why is my query t ool allowing this?)
Your default anonymous account is still active. The Fine Manual describes how to fix this: http://dev.mysql.com/doc/mysql/en/Default_privileges.html Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Victor Pendleton [EMAIL PROTECTED] wrote on 07/23/2004 03:01:56 PM: Is the ODBC account still enabled on the database server? -Original Message- From: john sayre To: [EMAIL PROTECTED] Sent: 7/23/04 1:38 PM Subject: Logging on through ODBC w/o UN Password (Why is my query tool allowing this?) I am able to log onto a database through ODBC without using a user name or password. I don't want this to be possible for obvious security reasons. What do I need to do to prevent this? This seems pretty freaky! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning Column and Row Headings Like a Spreadsheet
Hi Shawn I hope this gives you what you need: SELECT member.Member_Initials, company.Company_Name, subscription.Subscription_Expiry_Date FROM subscription INNER JOIN member ON member.Member_URN = subscription.Member_URN INNER JOIN company ON company.Company_URN = subscription.Company_URN From member: ABS BV CCS DNV GL KR LR NK RINA RS CRS IRS Many thanks -- Aeon McNulty On 23/7/04 7:35 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I will be more than happy to help you build a case-specific crosstab report. I have almost enough information. Could you post a 3 column-query that will actually produce from your data the information you would like to have reformatted? Column one needs to be your column names, column two needs to be your row headers, and the 3rd column needs to have the data you want to see merged into your cells (I won't need the data only the query). It should look something like: SELECT a.name, b.name, c.data FROM c INNER JOIN a on a.id = c.a_id INNER JOIN b on b.id = c.b_id WHERE . (any condition will do) I will also need a list of the potential column headers (this time I do need the data): SELECT DISTINCT name FROM a With that I will have enough information to pivot your query into a crosstab report. Hopefully you will be able to spot the pattern and be able to adjust it to fit your other situations as well. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 02:12:37 PM: Hi Shawn Yes, that's pretty much it. I'm impressed that you managed to work that out from my previous email which was a bit thin on detail! Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. The number of columns are variable but they don't vary often so I can do a quick query first to give me the number and then use that number to specify the number of columns in the second query. Does that make sense? Please see my other post. Does that give you enough information? Many thanks On 23/7/04 6:36 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: If I understand you correctly Basically you want to convert this query output: +---+---+---+ |A.value|B.value|C.value| +---+---+---+ | a1 | b1 | c1 | | a1 | b1 | c2 | | a2 | b1 | c3 | | a2 | b1 | c4 | | a1 | b2 | c5 | | a1 | b2 | c6 | | a2 | b2 | c7 | | a2 | b2 | c8 | | a1 | b3 | c9 | | ... | ... | ... | | a(j) | b(k) | c(n) | +---+---+---+ into something like: +---+--+--+--+---+ | |a1| a2 |... | a(j)| +---+--+--+--+---+ | b1 | f(c1,c2) | f(c3,c4) |... |...| | b2 | f(c5,c6) | f(c7,c8) |... |...| | b3 |f(c9,...) | ...|... |...| | ... | ...| ...|... |...| | b(k) | f(c1,c2) | f(c2,c3) |... |f(...,C(n))| +---+--+--+--+---+ Where f() represents one of the aggregate functions: SUM, AVG, STD, MIN, MAX, etc. Bad news: MySQL does not have an SQL-only solution for the general case (when you do not know the number of columns) Good news: it is usually quite simple to write a script to handle the general case. Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. If you would like help in writing a specific pivot table (cross-tab) query. Please post your tables' structures (I prefer the output of SHOW CREATE TABLE xxx) and tell us how you want your cross-tab report setup (column headers from where, row headers from where, and a formula to use for your cells) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 12:30:21 PM: Hi, I hope someone on the list can help me. Is there an easy and straightforward way way of displaying the data from three tables in a spreadsheet like format using MySQL 4.0? Table A has the column headings Table B has the row headings Table C is a line items file than cross relates the two Many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
precision differences between Windows Linux?
I have come across a very strange occurrence while working on a client's system. I get different behavior between the two servers (one Windows, one Linux) when it comes to a column defined as double(5,4). The Windows system truncates new values (existing data in the table is not truncated) whereas the Linux system does not. On the Windows machine I have been unable to replicate the data that already exists in the table! -- e.g., a value of 179. exists in the table but I cannot insert that value back into the same column -- so that is why my statements below illustrate the issue by selecting from the table. Anyone witnessed this bizarre behavior or know if I should report this elsewhere? Kevin First, tech specs: Desktop: WinXP, MySQL 4.1.3b-beta-nt-max Server: Slackware Linux 9.0, MySQL 4.1.1-alpha Here are the statements I executed on both machines: SHOW COLUMNS FROM scene LIKE 'max_lon'; SELECT max(max_lon) FROM scene; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a double(5, 4) ); INSERT INTO t1 SELECT max(max_lon) FROM scene; SELECT a FROM t1; And here is the output: --- WINDOWS --- mysql SHOW COLUMNS FROM scene LIKE 'max_lon'; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | max_lon | double(5,4) | YES | | NULL| | +-+-+--+-+-+---+ 1 row in set (0.00 sec) mysql SELECT max(max_lon) FROM scene; +--+ | max(max_lon) | +--+ | 179. | +--+ 1 row in set (0.02 sec) mysql DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE t1 ( a double(5, 4) ); Query OK, 0 rows affected (0.09 sec) mysql INSERT INTO t1 SELECT max(max_lon) FROM scene; Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql SHOW WARNINGS; +-+--+-- + | Level | Code | Message | +-+--+-- + | Warning | 1264 | Data truncated; out of range for column 'a' at row 14437 | +-+--+-- + 1 row in set (0.00 sec) mysql SELECT a FROM t1; ++ | a | ++ | 9. | ++ 1 row in set (0.00 sec) --- LINUX --- mysql SHOW COLUMNS FROM scene LIKE 'max_lon'; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | max_lon | double(5,4) | YES | | NULL| | +-+-+--+-+-+---+ 1 row in set (0.00 sec) mysql SELECT max(max_lon) FROM scene; +--+ | max(max_lon) | +--+ | 179. | +--+ 1 row in set (0.00 sec) mysql DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE t1 ( a double(5, 4) ); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 SELECT max(max_lon) FROM scene; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql SHOW WARNINGS; Empty set (0.00 sec) mysql SELECT a FROM t1; +--+ | a| +--+ | 179. | +--+ 1 row 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]
building mysql-5.0
hi... i'm building mysql-5.0 from src for rh8.0. my question has to do with how/where i find the libmysqlclient.so.10 lib. when i've built the mysql-5.0 i haven't found this file. it appears that this file has been included in the mysql-shared-compat* file, but i can't find this file either for the mysql-5.0. the reason i appear to need this file is that when i try to use DBI in perl, the perl app complains that it can't find the libmysqlclient.so.10 file. so, i'm basically trying to figure out what i need to do/build/install to be able to use perl (5.8.0) and communicate with a mysql-5.0 database... all of this was working before i decided to test out the mysql-5.0 searching mysql/cpan/google hasn't shed any light on this issue... thanks... -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: building mysql-5.0 clarification
oops... clarification, i'm using mysql-4.1.3. the reason i can't use the binaries/rpms is that i have a dependency conflict with openssl..., so i need to be able to rebuild to make everything work. unless anyone has a different solution... thanks -bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Friday, July 23, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: building mysql-5.0 hi... i'm building mysql-5.0 from src for rh8.0. my question has to do with how/where i find the libmysqlclient.so.10 lib. when i've built the mysql-5.0 i haven't found this file. it appears that this file has been included in the mysql-shared-compat* file, but i can't find this file either for the mysql-5.0. the reason i appear to need this file is that when i try to use DBI in perl, the perl app complains that it can't find the libmysqlclient.so.10 file. so, i'm basically trying to figure out what i need to do/build/install to be able to use perl (5.8.0) and communicate with a mysql-5.0 database... all of this was working before i decided to test out the mysql-5.0 searching mysql/cpan/google hasn't shed any light on this issue... thanks... -bruce -- 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: Returning Column and Row Headings Like a Spreadsheet
Yep, just what I needed. SELECT c.CompanyName , MAX(IF(m.Member_Initials='ABS', s.Subscription_Expiry_Date, NULL))as 'ABS' , MAX(IF(m.Member_Initials='BV', s.Subscription_Expiry_Date, NULL))as 'BV' , MAX(IF(m.Member_Initials='CCS', s.Subscription_Expiry_Date, NULL)) as 'CCS' , MAX(IF(m.Member_Initials='DNV', s.Subscription_Expiry_Date, NULL)) as 'DNV' , MAX(IF(m.Member_Initials='GL', s.Subscription_Expiry_Date, NULL)) as 'GL' , MAX(IF(m.Member_Initials='KR', s.Subscription_Expiry_Date, NULL)) as 'KR' , MAX(IF(m.Member_Initials='LR', s.Subscription_Expiry_Date, NULL)) as 'LR' , MAX(IF(m.Member_Initials='NK', s.Subscription_Expiry_Date, NULL)) as 'NK' , MAX(IF(m.Member_Initials='RINA', s.Subscription_Expiry_Date, NULL)) as 'RINA' , MAX(IF(m.Member_Initials='RS', s.Subscription_Expiry_Date, NULL)) as 'RS' , MAX(IF(m.Member_Initials='CRS', s.Subscription_Expiry_Date, NULL)) as 'CRS' , MAX(IF(m.Member_Initials='IRS', s.Subscription_Expiry_Date, NULL)) as 'IRS' FROM subscription s INNER JOIN member m ON m.Member_URN = s.Member_URN INNER JOIN company c ON c.Company_URN = s.Company_URN GROUP BY c.CompanyName Now do you see why I needed you to send me the column names? It is a simple pattern but requires a little advance knowledge of the data. It should be quite easy for you to script a query shaped like this in order to return just the columns you get from any generic query. The reason everything lines up by rows is because of the GROUP BY statement. Because you gave me a date column to work with I was limited in my choice of aggregating function. You can replace the MAX() in the above query from any other function in this list (so long as it is compatible with the data you need to aggregate) : http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html This works because for each column, the aggregate function either sees a value or a NULL. That's what the IF() is doing, picking what is MAX()-ed and what isn't based on the value of Member_Initials. Have fun playing around with it! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 03:19:41 PM: Hi Shawn I hope this gives you what you need: SELECT member.Member_Initials, company.Company_Name, subscription.Subscription_Expiry_Date FROM subscription INNER JOIN member ON member.Member_URN = subscription.Member_URN INNER JOIN company ON company.Company_URN = subscription.Company_URN From member: ABS BV CCS DNV GL KR LR NK RINA RS CRS IRS Many thanks -- Aeon McNulty On 23/7/04 7:35 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I will be more than happy to help you build a case-specific crosstab report. I have almost enough information. Could you post a 3 column-query that will actually produce from your data the information you would like to have reformatted? Column one needs to be your column names, column two needs to be your row headers, and the 3rd column needs to have the data you want to see merged into your cells (I won't need the data only the query). It should look something like: SELECT a.name, b.name, c.data FROM c INNER JOIN a on a.id = c.a_id INNER JOIN b on b.id = c.b_id WHERE . (any condition will do) I will also need a list of the potential column headers (this time I do need the data): SELECT DISTINCT name FROM a With that I will have enough information to pivot your query into a crosstab report. Hopefully you will be able to spot the pattern and be able to adjust it to fit your other situations as well. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 02:12:37 PM: Hi Shawn Yes, that's pretty much it. I'm impressed that you managed to work that out from my previous email which was a bit thin on detail! Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. The number of columns are variable but they don't vary often so I can do a quick query first to give me the number and then use that number to specify the number of columns in the second query. Does that make sense? Please see my other post. Does that give you enough information? Many thanks On 23/7/04 6:36 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: If I understand you correctly Basically you want to convert this query output: +---+---+---+ |A.value|B.value|C.value| +---+---+---+ | a1 | b1 | c1 | | a1 | b1 | c2 | | a2 | b1 | c3 | | a2 | b1 | c4 | | a1 | b2 | c5 | | a1 | b2
Re: IMPORTING
On Friday 23 July 2004 03:02 pm, you wrote: sqlog program What is sqlog program? Google shows me nothing familiar: http://www.google.com/search?q=sqlog+program You need to provide more information if you want help. How do you run this? What OS? What options are you selecting? How do you connect the sqlog program to MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning Column and Row Headings Like a Spreadsheet
That's just perfect! Than you so much! -- Aeon McNulty On 23/7/04 8:50 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Yep, just what I needed. SELECT c.CompanyName , MAX(IF(m.Member_Initials='ABS', s.Subscription_Expiry_Date, NULL))as 'ABS' , MAX(IF(m.Member_Initials='BV', s.Subscription_Expiry_Date, NULL))as 'BV' , MAX(IF(m.Member_Initials='CCS', s.Subscription_Expiry_Date, NULL)) as 'CCS' , MAX(IF(m.Member_Initials='DNV', s.Subscription_Expiry_Date, NULL)) as 'DNV' , MAX(IF(m.Member_Initials='GL', s.Subscription_Expiry_Date, NULL)) as 'GL' , MAX(IF(m.Member_Initials='KR', s.Subscription_Expiry_Date, NULL)) as 'KR' , MAX(IF(m.Member_Initials='LR', s.Subscription_Expiry_Date, NULL)) as 'LR' , MAX(IF(m.Member_Initials='NK', s.Subscription_Expiry_Date, NULL)) as 'NK' , MAX(IF(m.Member_Initials='RINA', s.Subscription_Expiry_Date, NULL)) as 'RINA' , MAX(IF(m.Member_Initials='RS', s.Subscription_Expiry_Date, NULL)) as 'RS' , MAX(IF(m.Member_Initials='CRS', s.Subscription_Expiry_Date, NULL)) as 'CRS' , MAX(IF(m.Member_Initials='IRS', s.Subscription_Expiry_Date, NULL)) as 'IRS' FROM subscription s INNER JOIN member m ON m.Member_URN = s.Member_URN INNER JOIN company c ON c.Company_URN = s.Company_URN GROUP BY c.CompanyName Now do you see why I needed you to send me the column names? It is a simple pattern but requires a little advance knowledge of the data. It should be quite easy for you to script a query shaped like this in order to return just the columns you get from any generic query. The reason everything lines up by rows is because of the GROUP BY statement. Because you gave me a date column to work with I was limited in my choice of aggregating function. You can replace the MAX() in the above query from any other function in this list (so long as it is compatible with the data you need to aggregate) : http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html This works because for each column, the aggregate function either sees a value or a NULL. That's what the IF() is doing, picking what is MAX()-ed and what isn't based on the value of Member_Initials. Have fun playing around with it! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 03:19:41 PM: Hi Shawn I hope this gives you what you need: SELECT member.Member_Initials, company.Company_Name, subscription.Subscription_Expiry_Date FROM subscription INNER JOIN member ON member.Member_URN = subscription.Member_URN INNER JOIN company ON company.Company_URN = subscription.Company_URN From member: ABS BV CCS DNV GL KR LR NK RINA RS CRS IRS Many thanks On 23/7/04 7:35 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I will be more than happy to help you build a case-specific crosstab report. I have almost enough information. Could you post a 3 column-query that will actually produce from your data the information you would like to have reformatted? Column one needs to be your column names, column two needs to be your row headers, and the 3rd column needs to have the data you want to see merged into your cells (I won't need the data only the query). It should look something like: SELECT a.name, b.name, c.data FROM c INNER JOIN a on a.id = c.a_id INNER JOIN b on b.id = c.b_id WHERE . (any condition will do) I will also need a list of the potential column headers (this time I do need the data): SELECT DISTINCT name FROM a With that I will have enough information to pivot your query into a crosstab report. Hopefully you will be able to spot the pattern and be able to adjust it to fit your other situations as well. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aeon McNulty [EMAIL PROTECTED] wrote on 07/23/2004 02:12:37 PM: Hi Shawn Yes, that's pretty much it. I'm impressed that you managed to work that out from my previous email which was a bit thin on detail! Good news: For a specific case (when you know the number of columns) the query you seek to write follows a simple and predictable pattern and is not hard to write at all. The number of columns are variable but they don't vary often so I can do a quick query first to give me the number and then use that number to specify the number of columns in the second query. Does that make sense? Please see my other post. Does that give you enough information? Many thanks On 23/7/04 6:36 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: If I understand you correctly Basically you want to convert this query output: +---+---+---+ |A.value|B.value|C.value| +---+---+---+ | a1 | b1 | c1 | | a1 | b1 | c2 | |
Errcode: 27
Hi, I'm trying to run the following SQL (on mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc): CREATE INDEX timeindex ON internet_usage (time); CREATE INDEX urlindex ON internet_usage (urlid); but keep running into the following error: Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27) $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The create index gets as far as actually copying the 18GB internet_usage table, and then does some processing (not sure what), then falls over with the error 27. The message seems to be misleading because all the ulimit values are unlimited. Also the filesystem is large-file enabled as shown below: $ pwd /proxydb/mysql/data/proxy_logs $ ls -l total 58124344 -rw-rw 1 mysqlmysql 0 Jul 23 11:08 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jul 23 11:08 bulk_table.MYI -rw-rw 1 mysqlmysql 8970 Jul 09 14:05 bulk_table.frm -rw-rw 1 mysqlmysql18550068032 Jul 23 10:45 internet_usage.MYD -rw-rw 1 mysqlmysql5150286848 Jul 23 11:08 internet_usage.MYI -rw-rw 1 mysqlmysql 8856 Jul 09 14:05 internet_usage.frm -rw-rw 1 mysqlmysql380 Jul 09 14:08 url_actions.MYD File system name/proxydb NEW mount point[/proxydb] SIZE of file system (in 512-byte blocks) [131203072] Mount GROUP[] Mount AUTOMATICALLY at system restart? yes + PERMISSIONS read/write + Mount OPTIONS [] + Start Disk Accounting? no + Fragment Size (bytes) 4096 Number of bytes per inode 4096 Compression algorithm no Large File Enabled true Allocation Group Size (MBytes) 64 I'm not sure what else could be wrong. I've tried creating another table with the new keys and selecting * from internet_usage table but I still got the same error there. Here's some miscellaneous info which might be useful. mysql show indexes from internet_usage; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | internet_usage | 1 | uid |1 | uid | A | 23039 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.07 sec) ++++---++-+-+--+---++-+-+-+-+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++-+-+--+---++-+-+-+-+-+ | bulk_table | MyISAM | Dynamic| 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL| | | | internet_usage | MyISAM | Fixed | 579689626 | 32 | 18550068032 |137438953471 | 5150286848 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 10:45:04 | 2004-07-09 14:13:20 | max_rows=4294967295 | | | url_actions| MyISAM | Dynamic|18 | 21 | 380 | 4294967295 | 3072 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL| | | | url_categories | MyISAM | Dynamic|37 | 21 | 780 | 4294967295 | 3072 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL| | | | url_methods| MyISAM | Dynamic|37 | 20 |
Re: Errcode: 27
This should be a useful thread for you http://lists.mysql.com/mysql/167953 Suggests using ALTER TABLE MAX_ROWS=a big num On Fri, 23 Jul 2004 20:20:24 +, J S [EMAIL PROTECTED] wrote : Hi, I'm trying to run the following SQL (on mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc): CREATE INDEX timeindex ON internet_usage (time); CREATE INDEX urlindex ON internet_usage (urlid); but keep running into the following error: Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27) $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The create index gets as far as actually copying the 18GB internet_usage table, and then does some processing (not sure what), then falls over with the error 27. The message seems to be misleading because all the ulimit values are unlimited. Also the filesystem is large-file enabled as shown below: $ pwd /proxydb/mysql/data/proxy_logs $ ls -l total 58124344 -rw-rw 1 mysqlmysql 0 Jul 23 11:08 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jul 23 11:08 bulk_table.MYI -rw-rw 1 mysqlmysql 8970 Jul 09 14:05 bulk_table.frm -rw-rw 1 mysqlmysql18550068032 Jul 23 10:45 internet_usage.MYD -rw-rw 1 mysqlmysql5150286848 Jul 23 11:08 internet_usage.MYI -rw-rw 1 mysqlmysql 8856 Jul 09 14:05 internet_usage.frm -rw-rw 1 mysqlmysql380 Jul 09 14:08 url_actions.MYD File system name/proxydb NEW mount point[/proxydb] SIZE of file system (in 512-byte blocks) [131203072] Mount GROUP[] Mount AUTOMATICALLY at system restart? yes + PERMISSIONS read/write + Mount OPTIONS [] + Start Disk Accounting? no + Fragment Size (bytes) 4096 Number of bytes per inode 4096 Compression algorithm no Large File Enabled true Allocation Group Size (MBytes) 64 I'm not sure what else could be wrong. I've tried creating another table with the new keys and selecting * from internet_usage table but I still got the same error there. Here's some miscellaneous info which might be useful. mysql show indexes from internet_usage; +++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+-- -+-+--++--++-+ | internet_usage | 1 | uid |1 | uid | A | 23039 | NULL | NULL | | BTREE | | +++--+--+-+-- -+-+--++--++-+ 1 row in set (0.07 sec) ++++---++ -+-+--+---++--- --+-+-+ -+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++ -+-+--+---++--- --+-+-+ -+-+ | bulk_table | MyISAM | Dynamic| 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL | | | | internet_usage | MyISAM | Fixed | 579689626 | 32 | 18550068032 |137438953471 | 5150286848 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 10:45:04 | 2004-07-09 14:13:20 | max_rows=4294967295 | | | url_actions| MyISAM | Dynamic|18 | 21 | 380 | 4294967295 | 3072 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL |
Re: Last insert id problem/bug
on 7/23/04 5:30 AM, Harald Fuchs at [EMAIL PROTECTED] wrote: The behavior is indeed strange, but it's not a bug, since it's documented: If you use `INSERT IGNORE' and the record is ignored, the `AUTO_INCREMENT' counter still is incremented and `LAST_INSERT_ID()' returns the new value. The solution is, of course, not to use INSERT IGNORE at all. You should instead catch any error, check that it's ER_DUP_KEY and not try the second INSERT in that case. That's what I tried, I thought I was onto something, then I remembered... If record does exists In the first table, it does not necessarily mean it should or should not exist in the second table. In the case where it does not exist in the second table, I do need to insert it, but alas, with the insert id of the found duplicate in the first table. I would have to select the first table to get that id, which in this case, of hundreds of thousands of imported records in batch, it just not feasible. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another newbie question - using OR in WHERE clauses
Is there a simpler way to write something like: SELECT * FROM tablename WHERE columname = 'Bob' OR columname = 'Mike' OR columname = 'Betty' Clearly this does not work: WHERE columname = 'Bob' OR 'Mike' OR 'Betty' Thanks!
RE: Another newbie question - using OR in WHERE clauses
Paul,
You can do the following:
SELECT * FROM tablename WHERE columname IN ('Bob','Mike','Betty');
Kevin
-Original Message-
From: Paul Fine [mailto:[EMAIL PROTECTED]
Sent: Friday, July 23, 2004 3:01 PM
To: [EMAIL PROTECTED]
Subject: Another newbie question - using OR in WHERE clauses
Is there a simpler way to write something like:
SELECT * FROM tablename
WHERE columname = 'Bob' OR columname = 'Mike' OR columname = 'Betty'
Clearly this does not work:
WHERE columname = 'Bob' OR 'Mike' OR 'Betty'
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how the redhat 9.0 adds the mysql user:
j.rabbit [EMAIL PROTECTED] wrote: This is how the redhat 9.0 mysql.spec file adds the mysql user: ' useradd -M -o -r -d /var/lib/mysql -s /bin/bash -c MySQL Server -u = 27 mysql ' Anybody know why the shell is '/bin/bash' instead of '/sbin/nologin' = like other daemon users? Is this simply an oversite? I can't speak about Red Hat 9, but for MySQL itself there is no need to have a valid shell for the 'mysql' user. Thanks for the response. I just realized that slackware does this also, so there must be some reason for it. This link may offer some clues: http://www.unixfool.com/mysql-slack.shtml -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errcode: 27
Thanks but that's not v helpful. Actually that was my thread! The problem there was that I didn't have a large-file enabled filesystem. The problem I have now is being able to create another index on a table but getting an errcode 27 despite no ulimits and a large enabled filesystem. This should be a useful thread for you http://lists.mysql.com/mysql/167953 Suggests using ALTER TABLE MAX_ROWS=a big num On Fri, 23 Jul 2004 20:20:24 +, J S [EMAIL PROTECTED] wrote : Hi, I'm trying to run the following SQL (on mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc): CREATE INDEX timeindex ON internet_usage (time); CREATE INDEX urlindex ON internet_usage (urlid); but keep running into the following error: Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27) $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The create index gets as far as actually copying the 18GB internet_usage table, and then does some processing (not sure what), then falls over with the error 27. The message seems to be misleading because all the ulimit values are unlimited. Also the filesystem is large-file enabled as shown below: $ pwd /proxydb/mysql/data/proxy_logs $ ls -l total 58124344 -rw-rw 1 mysqlmysql 0 Jul 23 11:08 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jul 23 11:08 bulk_table.MYI -rw-rw 1 mysqlmysql 8970 Jul 09 14:05 bulk_table.frm -rw-rw 1 mysqlmysql18550068032 Jul 23 10:45 internet_usage.MYD -rw-rw 1 mysqlmysql5150286848 Jul 23 11:08 internet_usage.MYI -rw-rw 1 mysqlmysql 8856 Jul 09 14:05 internet_usage.frm -rw-rw 1 mysqlmysql380 Jul 09 14:08 url_actions.MYD File system name/proxydb NEW mount point[/proxydb] SIZE of file system (in 512-byte blocks) [131203072] Mount GROUP[] Mount AUTOMATICALLY at system restart? yes + PERMISSIONS read/write + Mount OPTIONS [] + Start Disk Accounting? no + Fragment Size (bytes) 4096 Number of bytes per inode 4096 Compression algorithm no Large File Enabled true Allocation Group Size (MBytes) 64 I'm not sure what else could be wrong. I've tried creating another table with the new keys and selecting * from internet_usage table but I still got the same error there. Here's some miscellaneous info which might be useful. mysql show indexes from internet_usage; +++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+-- -+-+--++--++-+ | internet_usage | 1 | uid |1 | uid | A | 23039 | NULL | NULL | | BTREE | | +++--+--+-+-- -+-+--++--++-+ 1 row in set (0.07 sec) ++++---++ -+-+--+---++--- --+-+-+ -+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++ -+-+--+---++--- --+-+-+ -+-+ | bulk_table | MyISAM | Dynamic| 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL | | | | internet_usage | MyISAM | Fixed | 579689626 | 32 | 18550068032 |137438953471 | 5150286848 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 10:45:04 | 2004-07-09 14:13:20 | max_rows=4294967295 | | | url_actions| MyISAM | Dynamic|18 | 21 | 380 | 4294967295 | 3072 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL | | | | url_categories
Re: Another newbie question - using OR in WHERE clauses
on 7/23/04 3:00 PM, Paul Fine at [EMAIL PROTECTED] wrote: SELECT * FROM tablename WHERE columname = 'Bob' OR columname = 'Mike' OR columname = 'Betty' This is perfectly accurate -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best field type for status codes
I often find I am using a field called 'status' and almost always store boolean 1 or 0 in it, sometimes I may need a 2 or 3, but never greater than 9 for sure, Is tinyint(1) pretty much the most efficient storage means for this type? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging on through ODBC w/o UN Password (Why is my query tool allowing this?)
In all likelihood in your database you have not setup any access privilege yet. You need to look at GRANT command and assign password to atleast root account and any other accounts which will be connecting over network. On Fri, 23 Jul 2004 11:38:06 -0700 (PDT), john sayre [EMAIL PROTECTED] wrote: I am able to log onto a database through ODBC without using a user name or password. I don't want this to be possible for obvious security reasons. What do I need to do to prevent this? This seems pretty freaky! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql /dbi issues
can anyone tell me why when i build/use perl/dbi it crashes because it's looking for libmysqlclient.so.10. i'm using perl 5.8.3 and mysql4.1.3. i've gotten the latest DBI (1.43). why does DBI look for libmysqlclient.so.10, instead of using libmysqlclient.so.14. is there anyway to force a change, and if i can't, then how can i rebuild libmysqlclient.so.10 such that it uses my version of openssl. i can't simply use the default rpm fom mysql thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Object pascal translation of mysql header
Dear All, Not long ago Jorge del Conde gave me his amazing translation of mysql C header file to object pascal unit Including the remade dll, It was able to solve my problem of migrating the apps from using mysql 3.x to 4.x But now I am facing a new difficulty of trying to implement mysql 4.1.3 prepared statement, I already tried to edit the mysql.pas on my own, adding the declaration of anything that has the word STMT on the header file.. But instead it crashed my app I also do some googling to find a lot of obsolote result, the mysql.pas for mysql 3.X Would some one please help me.. I never did any translation from C style to pascal before Or may be Jorge can help me providing the newest mysql.pas Thanks in advance Regards -- Leonardus Setiabudi IT Department PT Bina San Prima Jl Tamansari 10-12 022-4207725 #316 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
