Re: Innodb table full
The size is already set to 2000M, and I may be wrong, but the autoextend feature is not support in mysql version earlier that 4. Nils Valentin wrote: Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003 7 8 22:45mixo : How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
Hi Mixo, How about adding a second innodb file and set the first one to a fixed size ? ...If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of `ibdata1', round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of `ibdata1' explicitly in innodb_data_file_path. After that you can add another datafile: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend... (taken from the manual http://www.mysql.com/doc/en/InnoDB_start.html ) Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:19mixo : The size is already set to 2000M, and I may be wrong, but the autoextend feature is not support in mysql version earlier that 4. Nils Valentin wrote: Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003 7 8 22:45mixo : How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
duplicate records check
Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: duplicate records check
Title: RE: duplicate records check Hi Neil, try something like this SELECT your_field FROM your_table GROUP BY your_field HAVING COUNT(*) 1 Mit freundlichen Grssen Frank Kalis Asset Management ProACTIV___ CiV Versicherungen * PB Versicherungen * PB Pensionsfonds AG Neustrae 62, 40721 Hilden tel +49 (0) 21 03-34 - 7282 fax +49 (0) 21 03-34 - 7098 mailto:[EMAIL PROTECTED] internet: www.proactiv.de -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 09, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: duplicate records check Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- 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: Faster reindexing
Sorry, my mistake. It's reindexing then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1. And the responses of the slaves also great, too. Anyway, im using the standard my-medium.cnf setup. The huge table/db also located on another drive/partition. Me fail English? That's unpossible ###___Archon___### - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Dominicus Donny [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:22 AM Subject: Re: Faster reindexing At 11:23 +0700 7/9/03, Dominicus Donny wrote: Try analyze your table(s). What information will this yield to make indexing faster? Me fail English? That's unpossible ###___Archon___### - Original Message - From: electroteque [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; Florian Weimer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: sql error : 1036
HA. Mooduto [EMAIL PROTECTED] wrote: dear all, what is the problem sql error : 1036 ...table is read only. please help me.. This table is compressed with myisampack or MySQL server doesn't have permissions on the table files. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server ?
*This message was transferred with a trial version of CommuniGate(tm) Pro* i got an error when i try to connect mysql server with mysqlcc : error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server . does anyone know any solution ? thanX. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Good Database Philosophy Book?
Database Systems: Design, Implementation, and Management, Fifth Edition by Peter Rob, Carlos Coronel. In 1997 I bought used the 3rd edition of this book. And from an educational, theoretical and practical analysis, design, implementation perspective it is very comprehensive. See info / reviews: http://www.amazon.com/exec/obidos/tg/detail/-/061906269X/qid=1057733082/sr=- 1/ref=sr__1_etk-books/102-2736743-5299308?v=glances=booksn=283155 With respect to SQL, a recommendation: SQL Clearly Explained, Second Edition by Jan L. Harrington (Paperback - April 2003). See info / reviews: http://www.amazon.com/exec/obidos/search-handle-form/ref=dp_sr_00/102-273674 3-5299308 -Original Message- From: David Thompson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:21 PM To: 'MySQL Mailing List' Subject: Good Database Philosophy Book? Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideasthanks... Regards, David Thompson President Provoix Corporation - Business Web Solutions www.provoix.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from.
Re: Get equivalents via SQL?
You need a LEFT JOIN. Check the manual for more info and let us know if you have any more questions. Edward Dudlik Becoming Digital www.becomingdigital.com Did I help you? Want to show your thanks? www.amazon.com/o/registry/EGDXEBBWTYUU - Original Message - From: motorpsychkill [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 08 July, 2003 22:35 Subject: Get equivalents via SQL? I have a table like this (table_gender): gender_id gender_description --- 0 female 1 male I also have a table like this (table_post): post_id post_gender1 post_gender2 --- --- 21 0 1 22 0 0 23 1 0 Is there any way that I can pull a singe row from table_post (for e.g. row 21) and have the results look like: post_id gender1 gender2 --- 21 female male Thank you very much! -- 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: error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server ?
fatih olcer wrote: *This message was transferred with a trial version of CommuniGate(tm) Pro* i got an error when i try to connect mysql server with mysqlcc : error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server . does anyone know any solution ? thanX. Have you looked at the manual ? http://www.mysql.com/doc/en/Access_denied.html Regards, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb logfiles
Is it possible to manually delete the logfiles created in innodb tables and then do a 'touch logfilename' to recreate it? If this is not the proper way, can somebody help me on this; there's no specific explanations in the mysql manual.. Thanks. BRgds, -- Richard Bornay Test Product Engineering Test Data Management Group ST Assembly Test Services 6824-1367
recursive sql statement
hi i searched the mysql doc for support of recursive sql statements, but found nothing. i am right that mysql does not support such kind of statements? best regards benny
Ideal setup
Perfomance matters the most. So, what would be the ideal setup for permomance in Mysql? Machine specs: PIII 1.0 GHz 640 MB Ram 36 Gig Hd partions as follows: /dev/sda1 linux swap1 Gig /dev/sda2 / 11 Gigs /dev/sda3 /var 11 Gigs /dev/sda4 Free 11 Gigs /dev/sda4 is not currently mounted, so that in future when redhat has new releases, the insallation can be made without changing the existing installtion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ideal setup
As much memory as you can get you hands on (we have 4 gig). As fast disks as you can get. Then a good multi threading operating system (64 bit would be nice). Simon -Original Message- From: mixo [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 08:58 To: [EMAIL PROTECTED] Subject: Ideal setup Perfomance matters the most. So, what would be the ideal setup for permomance in Mysql? Machine specs: PIII 1.0 GHz 640 MB Ram 36 Gig Hd partions as follows: /dev/sda1 linux swap1 Gig /dev/sda2 / 11 Gigs /dev/sda3 /var 11 Gigs /dev/sda4 Free 11 Gigs /dev/sda4 is not currently mounted, so that in future when redhat has new releases, the insallation can be made without changing the existing installtion -- 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: duplicate records check
Hi Neil, 1) You could ask for the count of each listed item in a column. something like: SELECT item, count(item) from parts GROUP BY item; 2) you could then use SELECT id,item from parts WHERE item = 'xxx'; to review the double entries. This is just a general sample, perhaps if you give more details than we can be more specific. ;-) Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:42Neil Tompkins : Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server ?
Hi Faith, You will have to check your user account privileges. Make sure that the Host you are connecting from is entitled to connect to the server. Login to the mysql server with the command line tool mysql and do this: mysql show grants for user; +-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT SELECT, SHOW DATABASES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxx' | +-+ 1 row in set (0.08 sec) Then set the privileges f.e. with GRANT SELECT, SHOW DATABASES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxx' This will allow root to connect from any host. Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:57fatih olcer : *This message was transferred with a trial version of CommuniGate(tm) Pro* i got an error when i try to connect mysql server with mysqlcc : error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server . does anyone know any solution ? thanX. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem When Mysql runs on a Machine with multiple NIC.
When Mysql runs on machine with multiple NIC's. Mysql server takes the IP Address that we specify in the configuration file. This IP Address may or may not be a primary IP Address of that machine. So we specify secondary IP Address for the Mysql server. It listens in that IP Address. When Mysql client from the same machine connects to that server, Mysql Client is treated,as if the connection is coming from the different host. i.e. The Rights of the Mysql client will not be same as the rights of [EMAIL PROTECTED] instead it will be equal to the rights of the [EMAIL PROTECTED] Is this behaviour correct or a bug. Thanks, Srinivasulu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server ?
Hi Faith, Damn, did I post a stupid GRANT command !! Doesn't seem to be my day today ;-) Don't use the GRANT command from the previous e-mail, use the one below. GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION; Sorry I must have been completely out of my mind for the previous posting. I hope you didn't have a chance yet to try the command from the previous e-mail. Best regards Nils Valentin Tokyo/Japan 2003 7 9 17:15Nils Valentin : Hi Faith, You will have to check your user account privileges. Make sure that the Host you are connecting from is entitled to connect to the server. Login to the mysql server with the command line tool mysql and do this: mysql show grants for user; +-- ---+ | Grants for [EMAIL PROTECTED] +-- ---+ | GRANT SELECT, SHOW DATABASES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxx' | +-- ---+ 1 row in set (0.08 sec) Then set the privileges f.e. with GRANT SELECT, SHOW DATABASES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxx' This will allow root to connect from any host. Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:57fatih olcer : *This message was transferred with a trial version of CommuniGate(tm) Pro* i got an error when i try to connect mysql server with mysqlcc : error 1130 : Host xxx.xxx.xxx.xxx not allowed to connect this server . does anyone know any solution ? thanX. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Temporary Table
Didier ROS [EMAIL PROTECTED] wrote: I am a newbie I want to create a temporary table and I get the following error message : mysql CREATE TEMPORARY TABLE tempemp AS SELECT * FROM emp; ERROR 1044: Access denied for user: '@localhost' to database 'test1' You must have CREATE TEMPORARY TABLES privilege. -- 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]
RE: Faster reindexing
Check out the EXPLAIN command EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order. With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records. http://www.mysql.com/doc/en/EXPLAIN.html Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 5:23 AM To: Dominicus Donny; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 11:23 +0700 7/9/03, Dominicus Donny wrote: Try analyze your table(s). What information will this yield to make indexing faster? Me fail English? That's unpossible ###___Archon___### - Original Message - From: electroteque [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; Florian Weimer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: recursive sql statement
Benny: What do you mean by 'recursive sql statements'? If you are talking about triggers and stored procedures, the answer is no. Gerald Jensen - Original Message - From: Bernhard Schmidt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 2:58 AM Subject: recursive sql statement hi i searched the mysql doc for support of recursive sql statements, but found nothing. i am right that mysql does not support such kind of statements? best regards benny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Temporary Table problem
As a way of getting around the lack of subselect (I'm aware this is coming soon) we're parsing sql queries, running subselects and storing their results in a temporary table and replacing the subselect in the sql with the temporary table name. This has been working fine, but on upgrading to v4.0.13 the CREATE TEMPORARY TABLE some sql part is failing with this error: Access denied for user: '@localhost' to database 'uclan_database' Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table problem
Further to this, I should point out everything works fine in mysql-front or
at the mysql console.
The problem shows up when using mysql++, a BadQuery exception is thrown.
query.reset();
query CREATE TEMPORARY TABLE sTemporary TYPE=HEAP
MAX_ROWS=1 subselect;
try
{
query.parse();
query.execute();
}
- Original Message -
From: Phil Bitis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, July 09, 2003 12:41 PM
Subject: Create Temporary Table problem
As a way of getting around the lack of subselect (I'm aware this is coming
soon) we're parsing sql queries, running subselects and storing their
results in a temporary table and replacing the subselect in the sql with
the
temporary table name.
This has been working fine, but on upgrading to v4.0.13 the CREATE
TEMPORARY TABLE some sql part is failing with this error:
Access denied for user: '@localhost' to database 'uclan_database'
Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursive sql statement
Hi Benny, May I guess that you are talking about nested queries or subseects ? If yes than they are supported from Version 4.1. If no, than I have no clue what you are talking about ;-) Best regards Nils Valentin Tokyo/Japan 2003 7 9 20:26Gerald R. Jensen : Benny: What do you mean by 'recursive sql statements'? If you are talking about triggers and stored procedures, the answer is no. Gerald Jensen - Original Message - From: Bernhard Schmidt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 2:58 AM Subject: recursive sql statement hi i searched the mysql doc for support of recursive sql statements, but found nothing. i am right that mysql does not support such kind of statements? best regards benny -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dropping table using Select result
I have a shopping cart which creates temporary tables in the format of zorder_phpsessionid; These tables are used to hold a customers order while they shop and after checking out, it is dropped. The values of the shopping cart are put together in an HTML table and stored as their history. What I want to do is to run a cron job to delete the orders which were left undone once a day at about midnight. Here is my current query SELECT table_name FROM orders WHERE RIGHT(started,8) RIGHT(NOW(),8) AND completed 1 AND LEFT(started,10) = LEFT(NOW(),10); I was hoping to do something like this: DROP TABLE (SELECT table_name FROM orders WHERE RIGHT(started,8) RIGHT(NOW(),8) AND completed 1 AND LEFT(started,10) = LEFT(NOW(),10)); But from my understanding, MySQL does not support nested queries. Is there a query-based way to do this... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.7 Queries from Twin Project Taken from the Documentation tutorial
Dear mysql users, in the tutorial of the documentation I found an interesting example of a complicated non trivial sql query: URL: http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html# example-Maximum-column-group-row 3.7 Queries from Twin Project 3.7.1 Find all Non-distributed Twins However, I can find no databases with tables to run the queries on. What I need is some dummy tables with example randome data To run the queries on. person_data lentus twin_project twin_data informant_data harmony postal_groups It is no problem for me to create a database, the problem is that I have no idea about which structure the tables has, the added URL : http://www.imm.ki.se/TWIN/TWINUKW.HTM relinked to http://www.mep.ki.se/twin/index.html has a malfunction I don't need sensitive personal data, but the table structure and some example dummy data would be higly appreciated. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dropping table using Select result
Hi jvilla, I know that MySQL supports some simple nested queries from 3.23 -xx, and even includes subselects from 4.1. However I am not clear if any of the 2 queries will work straight away in 4.1 (once its stable). Best regards Nils Valentin Tokyo/Japan 2003 7 9 21:00Jonathan Villa : I have a shopping cart which creates temporary tables in the format of zorder_phpsessionid; These tables are used to hold a customers order while they shop and after checking out, it is dropped. The values of the shopping cart are put together in an HTML table and stored as their history. What I want to do is to run a cron job to delete the orders which were left undone once a day at about midnight. Here is my current query SELECT table_name FROM orders WHERE RIGHT(started,8) RIGHT(NOW(),8) AND completed 1 AND LEFT(started,10) = LEFT(NOW(),10); I was hoping to do something like this: DROP TABLE (SELECT table_name FROM orders WHERE RIGHT(started,8) RIGHT(NOW(),8) AND completed 1 AND LEFT(started,10) = LEFT(NOW(),10)); But from my understanding, MySQL does not support nested queries. Is there a query-based way to do this... -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursive sql statement
for me a recursive sql statement is when the result of a query can be used for a following query. the statement has start and stop condition. such kind of queries is useful for tree traversal. sql is based on relational algebra that does not allow to write such expressions. but as mentioned by rudy metzger some databases defines extensions to sql, oracle uses connect by. db2 has another apporach using with. best regards benny
Re: Good Database Philosophy Book?
David, If you want to learn about Databases and normalization and Relational Algebra, a really good book is: Fundamentals of Database Systems, by Elmasri and Navathe and Understanding Relational Database Query Languages by SW Dietrich I have studied the first book, cover to cover, and I still use it for reference. It is the best books for understanding Relational Algebra and Normalization. The second book was written by my Professor Dr. Dietrich and is also a very good book. Saqib Ali - http://www.xml-dev.com On Wed, 9 Jul 2003, David Thompson wrote: Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideasthanks... Regards, David Thompson President Provoix Corporation - Business Web Solutions www.provoix.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- 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: Good Database Philosophy Book?
On Wed, Jul 09, 2003 at 04:20:52AM -0700, David Thompson wrote: Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. I too have a favourite. :-) Relational Database Design Clearly Explained, by Jan L. Harrington. ISBN 0-12-326425-1 (Academic Press). It goes through the basics (normalized forms, ER diagrams, CASE tools, Codd's Rules, yadda), explains *why* you'd use them, and (refreshingly) doesn't assume you'll take the author's word as gospel. It has examples which it builds on throughout the book, and seems to advocate simplicity of design over showing off gadgetry. I recommend it. -- Paul Chvostek [EMAIL PROTECTED] Operations / Abuse / Whatever it.canada, hosting and development http://www.it.ca/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to code an IS - a relationship ?
Hello MySQL programmers, suppose we have an Enhanced ER diagram, with entities as classes/ subclasses connected through some IS-A relationship. How can this be Coded in MySQL Please? My prerequisites are the basic database texts from http://www-db.stanford.edu/~ullman/dscb.html http://www.aw-bc.com/info/database/elmasri.html Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursive sql statement
Hi Benny, Thank you for the explanation, but I believe thats out of my reach to give any advice here ;-) Best regards 2003 7 9 21:30Bernhard Schmidt : for me a recursive sql statement is when the result of a query can be used for a following query. the statement has start and stop condition. such kind of queries is useful for tree traversal. sql is based on relational algebra that does not allow to write such expressions. but as mentioned by rudy metzger some databases defines extensions to sql, oracle uses connect by. db2 has another apporach using with. best regards benny -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk loading data
On Tue, Jul 08, 2003 at 09:36:11PM -0700, cmars wrote: I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures. ... How can I load data directly into the database at a lower more direct level than LOAD FILE? What are my options to integrate my data parser with MySQL? Other than patching mysqld, I can't see how you'd get an additional interface to the database files. I have s similar (though certainly not identical) issue with data I'm dealing with -- about 4 million records twice a week. Source data is in files with fixed-length fields with a newline after each record, and contains some data that must be updated, and some that must be inserted. (I.e. I receive replacements not diffs.) My solution was lowbrow, but works well. I pipe the source file through a huge honkin' sed script that grabs the fields and converts them into an INSERT statement, with sed's output piped through the mysql text client. (sed 's/^(...)(..)()/ etc etc/') Each record turns into two statements, first an UPDATE (which fails if the record doesn't exist), second an INSERT (which fails if the record does exist). I obviously run the mysql client with the -f option. This method lets me keep the database live while the update runs, whereas LOAD FILE would require that I flush the data before inserting. Sure, I'm pushing far more goop through the pipe than the data I'm updating, but it's just a pipe, and the run happens at 4AM when nobody is watching. My db server is a 400MHz P-I and my disks are slow, but it takes me over an hour to run the file so I'm not sure how this solution would fare a hundred or a thousand times a day -- Paul Chvostek [EMAIL PROTECTED] Operations / Abuse / Whatever it.canada, hosting and development http://www.it.ca/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13 in a file operation
Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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]
Re: recursive sql statement
Bernhard Schmidt [EMAIL PROTECTED] wrote: for me a recursive sql statement is when the result of a query can be used for a following query. the statement has start and stop condition. such kind of queries is useful for tree traversal. sql is based on relational algebra that does not allow to write such expressions. but as mentioned by rudy metzger some databases defines extensions to sql, oracle uses connect by. db2 has another apporach using with. CONNECT BY currently is not supported, but it's in TODO list: http://www.mysql.com/doc/en/TODO_future.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Can mysql handle this load?
I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bulk loading data
Paul, Did you try using REPLACE instead of UPDATE/INSERT? Could give you some more speed enhancement. Cheers /rudy -Original Message- From: Paul Chvostek [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 15:21 To: cmars Cc: [EMAIL PROTECTED] Subject: Re: Bulk loading data On Tue, Jul 08, 2003 at 09:36:11PM -0700, cmars wrote: I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures. ... How can I load data directly into the database at a lower more direct level than LOAD FILE? What are my options to integrate my data parser with MySQL? Other than patching mysqld, I can't see how you'd get an additional interface to the database files. I have s similar (though certainly not identical) issue with data I'm dealing with -- about 4 million records twice a week. Source data is in files with fixed-length fields with a newline after each record, and contains some data that must be updated, and some that must be inserted. (I.e. I receive replacements not diffs.) My solution was lowbrow, but works well. I pipe the source file through a huge honkin' sed script that grabs the fields and converts them into an INSERT statement, with sed's output piped through the mysql text client. (sed 's/^(...)(..)()/ etc etc/') Each record turns into two statements, first an UPDATE (which fails if the record doesn't exist), second an INSERT (which fails if the record does exist). I obviously run the mysql client with the -f option. This method lets me keep the database live while the update runs, whereas LOAD FILE would require that I flush the data before inserting. Sure, I'm pushing far more goop through the pipe than the data I'm updating, but it's just a pipe, and the run happens at 4AM when nobody is watching. My db server is a 400MHz P-I and my disks are slow, but it takes me over an hour to run the file so I'm not sure how this solution would fare a hundred or a thousand times a day -- Paul Chvostek [EMAIL PROTECTED] Operations / Abuse / Whatever it.canada, hosting and development http://www.it.ca/ -- 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: recursive sql statement
hi victoria sorry for this mysql newbie question, but what means planned for the near future? ist this weeks, months or years away? best regards benny
RE: recursive sql statement
Everything from month to years. Might even be tomorrow... /rudy -Original Message- From: Bernhard Schmidt [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:07 To: [EMAIL PROTECTED] Subject: Re: recursive sql statement hi victoria sorry for this mysql newbie question, but what means planned for the near future? ist this weeks, months or years away? best regards benny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join help
One of these days I will maybe understand... Using MYSQL 4.0.13, debian linux create table members ( id unsigned int autoincrement, name ) create table activity ( id unsigned int autoincrement, description ) create table history ( id unsigned in autoincrement, date date, member_id unsigned int, activity unsigned int ) What I need: 1) only records for a particular date 2) there should be at least one record for each activity 3) there may be multiples of the same activity on a given date 4) there may be multiples of the same member on a given date 5) not all members will be listed 6) the members.name result field may be NULL SELECT history.date, activity.description, members.name ??? WHERE history.date = '-MM-DD' -- A little inaccuracy sometimes saves tons of explanation. -- H. H. Munro (Saki) Rick Pasotto[EMAIL PROTECTED]http://www.niof.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld max
Hi I am trying to make mysqld max so i can create innodb tables but I am getting the following error when i run mysqld No Variable Match for: -0 'innodb_buffer_pool_size = 40M' ./mysqld version 3.23.56 for pc-linux on i686 I am unable to figure out what to do. This shows that it reads the my.cnf file but how can I get rid off this error??. My computer have just 64MB ram. Do u think that less ram is causing this error??. Please help thanx Kamran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld shows high cpu usage over extended time, restart = normal
Occasionally in checking one of the servers, I noticed that mysql shows 85% + of cpu usage essentially leaving the server at 0% idle. After monitoring it for a few hours, the status did not change. After a stop and start of mysql, things progessed normally. Checking back a few days later I noticed it was once again sitting up there at 95% (or thereabouts) and doing nothing of value from what i could tell. Have restarted MySQL during peak usage times for that server and its database, and it has showed normal loads and CPU usage (approx 20% CPU with .1 to .3 load). Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)? Is there something I should check when next I notice the high CPU usage? Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13 in a file operation
Hello Egor, You give me hope but... I attempted to set up permissions but it appears that I need to start up MySQL in order to do that. That brings me right back to my original problem of not being able to connect. I am thinking of reinstalling MySQL to see if I missed something in the original install. Any more suggestions please, I am at a loss. Mark D. On Wednesday, July 9, 2003, at 09:26 AM, Egor Egorov wrote: Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join help
Rick Pasotto wrote: One of these days I will maybe understand... A left join (t1 LEFT JOIN t2 ON condition) is defined as follows. For each row in t1, find all matching rows in t2 and return the combination of t1 and t2 found. If there are no t2s for a t1, leave the t2 values NULL in the result. Try: SELECT * FROM (history h LEFT JOIN members m ON h.member_id = m.id) LEFT JOIN activity a ON h.activity = a.id WHERE h.date = whatever.; Bruce Feist create table members ( id unsigned int autoincrement, name ) create table activity ( id unsigned int autoincrement, description ) create table history ( id unsigned in autoincrement, date date, member_id unsigned int, activity unsigned int ) What I need: 1) only records for a particular date 2) there should be at least one record for each activity 3) there may be multiples of the same activity on a given date 4) there may be multiples of the same member on a given date 5) not all members will be listed 6) the members.name result field may be NULL SELECT history.date, activity.description, members.name ??? WHERE history.date = '-MM-DD' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can mysql handle this load?
i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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: mysqld shows high cpu usage over extended time, restart = normal
hi, load). Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)? its been a known issue for quite a long time use linuxthreaded version and it should work fine. although much of work has been done on threads implementation, there are still such problems with mysql. it happens even on freebsd 5.0 regards, terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to dump images into a database?
Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can mysql handle this load?
Adam, Mysql will easily handle this. This certainly doesn't constitute a large database. Correctly indexing the database should see you doing speedy queries on years worth of data. Sounds like you've used access in the past :-) Andy -Original Message- From: Adam Gerson [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 14:47 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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: mysqld max
Please note that InnoDB is included as from MySQL 4.0 in the standard release. So the easiest way to use InnoDB would be to grab a RPM or binary distribution and install it on your system. No need to compile one yourself then. However, if for some reasons you must have 3.x or are on a not supported system, above answer does of course not apply (but I still would use 4.0 even if you have to compile it yourself) /rudy -Original Message- From: azamka [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:27 To: [EMAIL PROTECTED] Subject: mysqld max Hi I am trying to make mysqld max so i can create innodb tables but I am getting the following error when i run mysqld No Variable Match for: -0 'innodb_buffer_pool_size = 40M' ./mysqld version 3.23.56 for pc-linux on i686 I am unable to figure out what to do. This shows that it reads the my.cnf file but how can I get rid off this error??. My computer have just 64MB ram. Do u think that less ram is causing this error??. Please help thanx Kamran -- 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: Can mysql handle this load?
Is it better to set multiple primary keys or to set one key and index the other columns? If I have a primary key as a field in another table should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, [EMAIL PROTECTED] wrote: i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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: Is it possible to dump images into a database?
take a look at: http://www.php4.com/forums/viewtopic.php?t=6 or search the mailing list archive.. there are plenty of threads talking about this: For list archives: http://lists.mysql.com/mysql On Wed, 9 Jul 2003, Dan Anderson wrote: Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- 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: mysqld shows high cpu usage over extended time, restart = normal
On Wed, 2003-07-09 at 10:31, Dave [Hawk-Systems] wrote: Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)? Is there something I should check when next I notice the high CPU usage? I used to see the same kind of behavior a while back with a MySQL installation I did for a client. It turned out that what was happening was a poorly designed client/server app was touching off this really huge query (lots of rows and lots of columns across multiple tables) and then disconnecting before the result could be returned. They fixed their app and the problem went away. Drove me nuts troubleshooting it. Anyway. Make sure you don't have something similar going on. -- Peter L. Berghold[EMAIL PROTECTED] Dog event enthusiast, brewer of Belgian (style) Ales. Happiness is having your contented dog at your side and a Belgian Ale in your glass. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB: Operating system error number 13 in a file operation
If you got the error message during creation of the innoDB files (e.g. during install) you HAVE TO DELETE all files and restart the installation. There is no way around this! However, if the file got corrupted after installation (e.g. you already used it for days) you can repair it (maybe someone changed the file permissions and/or ownership). You can change filepermissions on unix (linux) with chmod (type 'man chmod') on the command line, and change ownership with chown (man chown). You can delete files with 'rm' or 'unlink' (again, see the man pages for help) Cheers /rudy -Original Message- From: Mark Depenbrock [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:35 To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: InnoDB: Operating system error number 13 in a file operation Hello Egor, You give me hope but... I attempted to set up permissions but it appears that I need to start up MySQL in order to do that. That brings me right back to my original problem of not being able to connect. I am thinking of reinstalling MySQL to see if I missed something in the original install. Any more suggestions please, I am at a loss. Mark D. On Wednesday, July 9, 2003, at 09:26 AM, Egor Egorov wrote: Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 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: Can mysql handle this load?
Well that all depends. The real reason for a primary key is to prevent duplicates, therefore, if a combination of fields needs to be unique, then a multiple primary key makes sense, especially if other tables will reference the field combination (for example, detail items on an invoice where the invoice number will not be unique, and the detail number will not be unique, but the combination of the two will be). As for question #2, if two fields, one on each table, will be used to join tables together, both fields should be indexed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Adam Gerson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 9:09 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? Is it better to set multiple primary keys or to set one key and index the other columns? If I have a primary key as a field in another table should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, [EMAIL PROTECTED] wrote: i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to dump images into a database?
Yes it is. If you are using VB look at www.vbmysql.com/articles/blobaccessvb.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 8:49 AM To: [EMAIL PROTECTED] Subject: Is it possible to dump images into a database? Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can mysql handle this load?
Why using int for date? Better suited would be DATE or DATETIME (or even TIMESTAMP, depending how you want to use it). For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp when you make them UNSIGNED. For status I would choose CHAR(1), you can put a lot of information into that, which also stays (a bit) human readable. Also enums would be ok but are a mess to change later (in the application). Do yourself a favor and use a master detail relation for this, eg: CREATE TABLE student_status ( Status CHAR(1) NOT NULL,/* short status flag, eg. A */ Verbose VARCHAR(20) NOT NULL, /* verbose description, e.g. ABSENT */ PRIMARY KEY(status) ) Maybe keep 'verbose' on char to force fixed line size and thus faster access. Cheers /rudy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:42 To: [EMAIL PROTECTED]; Adam Gerson Cc: [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13 in a file operation
Rudy, Egor, Victoria, Thank you for keeping an eye on me. I ended up doing a reinstall of MySQL and have successfully connected. I feel I have broken through a wall only to find an infinite number of walls yet to break through. Such is the adventure I have chosen. Be at peace, Mark Depenbrock On Wednesday, July 9, 2003, at 11:13 AM, Rudy Metzger wrote: If you got the error message during creation of the innoDB files (e.g. during install) you HAVE TO DELETE all files and restart the installation. There is no way around this! However, if the file got corrupted after installation (e.g. you already used it for days) you can repair it (maybe someone changed the file permissions and/or ownership). You can change filepermissions on unix (linux) with chmod (type 'man chmod') on the command line, and change ownership with chown (man chown). You can delete files with 'rm' or 'unlink' (again, see the man pages for help) Cheers /rudy -Original Message- From: Mark Depenbrock [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:35 To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: InnoDB: Operating system error number 13 in a file operation Hello Egor, You give me hope but... I attempted to set up permissions but it appears that I need to start up MySQL in order to do that. That brings me right back to my original problem of not being able to connect. I am thinking of reinstalling MySQL to see if I missed something in the original install. Any more suggestions please, I am at a loss. Mark D. On Wednesday, July 9, 2003, at 09:26 AM, Egor Egorov wrote: Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 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: Is it possible to dump images into a database?
Use LOAD DATA INFILE if you want to import it from the filesystem or use INSERT/UPDATE if you do it from an application (e.g. perl, php). Make sure that the column which stores them is defined as BLOB and not as TEXT. Look up the BLOB definition for size limitation and variations on BLOBs. For getting them out of the DB use SELECT .. INTO DUMPFILE to get them onto the file system or normal SELECT for applications. HINT: normally it is MUCH better to store the image on the file system and only store the path to the image in the database. But this again depends on what you want to achieve. Cheers /rudy -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:49 To: [EMAIL PROTECTED] Subject: Is it possible to dump images into a database? Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- 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: Can mysql handle this load?
MySQL allows only ONE primary key per table, so you can only use one. However, you can also define UNIQUE INDEXES. The major difference here is that primary key columns may not contain NULL values, however UNIQUE KEY columns may contain NULL values. If your question now is: Is it better to use a multi part/column key or split it up in multiple keys, I would suggest that you always should make the primary key on the lowest number of columns you can get. So no need to define a primary key on (studentid,gender) if (studentid) by itself is already unique/primary. Please also note that on multipart keys the key is only used if you provide at least the leading columns. So on a PK(a,b,c) the key is not taken when you search for b or c or b and c (but is taken if you search for a / a,b / a,b,c and even a,c (but then only a is taken). So to answer your question: it depends on your situation. In general (for 90% of the cases), make a primary key as short as possible and add indexes as you need them (use EXPLAIN to see how the optimizer is parsing the kwiri). But keep in mind that although indexes speed up kwiries (SELECTS) they slow down UPDATES/INSERTS/DELETES. Cheers /rudy ps: as always, exceptions confirm the rule :) -Original Message- From: Adam Gerson [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 17:09 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? Is it better to set multiple primary keys or to set one key and index the other columns? If I have a primary key as a field in another table should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, [EMAIL PROTECTED] wrote: i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query across multiple tables
I have four tables I need to query for information; acc,accmemo,aard,sdtik where acc contains basic information about a customer (1 rec per acctno) accmemo contains multiple Account Memo messages (0 or more recs per acctno) aard contains credit card(s) information (0 or more recs per acctno) sdtik contains customer sales information (0 or more recs per acctno) all tables have acctno as a key I need to select the following information: acc.strref, acc.acctno, acc.namelast, acc.namefirst, acc.adddate, accmemo.memo, aard.credcardtype, aard.credcardnum, count(distinct(sdtik.datein)) as visits, sum(if(sdtik.voidreason0,0,sdtik.amt)) as sales I need the following conditions met: 1) all accts where strref=1 2) all accmemo.memo fields, if any, for each acct 3) all credcardtype credcardnum from aard, if any, for each acct 4) count sum from sdtik to be only records where datein=20020701 Is there anyway to right this query? I am still fairly new in writing queries. At present, I am using perl to do this in multiple steps and queries. 1) select info from acc 2) for each acctno, query accmemo, 3) for each acctno, query aard, 4) for each acctno, query sdtik, 5) display information. I know that 1 single query is more efficient than looping and processing 3 additional queries per acctno. Any/All help will be greatly appreciated. Thanks in advance. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can mysql handle this load?
Mike, #2 is not 100% correct. Only the columns in the lookup table (table2) need to be indexed. Why? The optimizer first gets a limited set of table1 and then checks if it can join this subset with a key from table2. So for the join condition only table2 needs to be indexed. However, you are right that in most of the cases you also want to have a subset out of table one first. For that you also should use an index, but in most cases this will be a different index. Example: CREATE TABLE table1 ( AINT NOT NULL, BINT, CINT, PRIMARY KEY(A), UNIQUE INDEX uidx1(B) ); CREATE TABLE table2 ( CINT NOT NULL, DINT, PRIMARY KEY(C) ); SELECT table2.D FROM table1, table2 WHERE table1.B = 25 AND table1.C = table2.C The optimizer now would first find all rows from table1 having B = 25 by using the INDEX uidx1 and then join table 2 via the columns table1.C = table2.C using the index PRIMARY KEY from table2. So for the JOIN you only need to have table2 indexed, there is no need to put an index on the column C on table1. BTW you can always see what the optimizer plans to do by setting EXPLAIN right before SELECT (EXPLAIN SELECT ...) What the former post was more about is REFERENTIAL INTEGRITY, which is something that is not (yet) include in MySQL (at least for MyISAM, for InnoDB it is coming with 5.0 I think). Cheers /rudy -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 17:19 To: Adam Gerson; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Can mysql handle this load? Well that all depends. The real reason for a primary key is to prevent duplicates, therefore, if a combination of fields needs to be unique, then a multiple primary key makes sense, especially if other tables will reference the field combination (for example, detail items on an invoice where the invoice number will not be unique, and the detail number will not be unique, but the combination of the two will be). As for question #2, if two fields, one on each table, will be used to join tables together, both fields should be indexed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Adam Gerson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 9:09 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? Is it better to set multiple primary keys or to set one key and index the other columns? If I have a primary key as a field in another table should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, [EMAIL PROTECTED] wrote: i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list
order of table joins or where clauses relevant?
As we're on this topic in another thread right now: Say I have a SELECT query from more than one table and with some conditions, does it matter in what order I enter the tables in the FROM clause and in what order the WHERE conditions appear in my query? Or does it make any difference if I use WHERE or HAVING? (I see that MS Access likes those HAVINGs...) Of course my tables contain (maybe very much) more than some 100 records and are well-indexed, I believe but that's not my question for now. I guess, the MySQL optimizer reads the table and column names in the specified order and tries to process them the same way, right? Or it joins the tables in my given order... And when are the resulting records reduced by matching against my conditions? Maybe someone can tell me a little bit about performance gains just by doing some 'manual query optimization' :) Yves Goergen www.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Faster reindexing
Maybe increasing #use for when mysql is doing a check or repair set-variable= myisam_sort_buffer_size=64M to a higher value will make the index happen faster on the fly. But, for a 100 million row table doing a dump and adding that dump back to the db might be your fastest method. Building the index at insertion for a self balancing tree is a faster in some cases (I believe this is the case) then building one on the fly. My 2 cents. 100 million rows WOW. ---Original Message- --From: Paul DuBois [mailto:[EMAIL PROTECTED] --Sent: Tuesday, July 08, 2003 8:09 PM --To: Florian Weimer; [EMAIL PROTECTED] --Subject: Re: Faster reindexing -- --At 9:39 +0200 7/7/03, Florian Weimer wrote: --I've got a table with 100 million rows and need some indexes on it --(one row is 126 bytes). -- --I'm currently using MyISAM and the indexing proceeds at an --astonishingly low rate: about 200 MB per hour. This is rate is far --too low; if we had to recover the database for some reason, we'd have --to wait for days. -- --The table looks like this: -- --CREATE TABLE flows ( --versionCHAR NOT NULL, --router CHAR(15) NOT NULL, --src_ip CHAR(15) NOT NULL, --dst_ip CHAR(15) NOT NULL, --protocol TINYINT UNSIGNED NOT NULL, --src_port MEDIUMINT UNSIGNED NOT NULL, --dst_port MEDIUMINT UNSIGNED NOT NULL, --packetsINTEGER UNSIGNED NOT NULL, --bytes INTEGER UNSIGNED NOT NULL, --src_if MEDIUMINT UNSIGNED NOT NULL, --dst_if MEDIUMINT UNSIGNED NOT NULL, --src_as MEDIUMINT UNSIGNED NOT NULL, --dst_as MEDIUMINT UNSIGNED NOT NULL, --src_netCHAR(1) NOT NULL, --dst_netCHAR(1) NOT NULL, --direction CHAR(1) NOT NULL, --class CHAR(1) NOT NULL, --start_time CHAR(24), --end_time CHAR(24) --); -- --Indexes are created using this statement: -- --mysql ALTER TABLE flows -- - ADD INDEX dst_ip (dst_ip, src_ip), -- - ADD INDEX dst_port (dst_port, start_time), -- - ADD INDEX src_ip (src_ip, start_time), -- - ADD INDEX time (start_time); -- --In theory, we could represent the columns router, src_ip, dst_ip, --start_time, end_time using integers of the appropriate size, but this --would make ad-hoc queries harder to type (and porting our applications --would be even more difficult). -- --Perhaps, but as a test, you might add a couple of extra columns to --the table, then populate them like this after loading the table: -- --UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = --INET_ATON(dst_ip); -- --Then try creating the indexes using int_src_ip and int_dst_ip rather --than src_ip and dst_ip. -- --If it's significantly faster, you may want to reconsider whether it might --not be worth using INET_ATON(X) in your queries rather than X. -- -- --Should I switch to another table type? -- --It's easy enough to convert the table to, e.g., InnoDB and then --create the indexes, so an empirical test should not be difficult. -- --Paul DuBois, Senior Technical Writer --Madison, Wisconsin, USA --MySQL AB, www.mysql.com -- --Are you MySQL certified? http://www.mysql.com/certification/ -- -- --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: Faster reindexing
Dathan Vance Pattishall [EMAIL PROTECTED] writes: #use for when mysql is doing a check or repair set-variable= myisam_sort_buffer_size=64M to a higher value will make the index happen faster on the fly. Oops. I only adjusted the key_buffer value. Probably I should set myisam_sort_buffer_size to several hundred megabytes. If InnoDB indexing doesn't finish either, I'll give it a try (I still hope that MyISAM tables are more light-weight than InnoDB tables and result in higher throughput in a many reads/rare bulk updates scenario). However, the indexes must be maintenance-free once created (no creeping index syndrome). Can it occur that index pages get lost during deletion? But, for a 100 million row table doing a dump and adding that dump back to the db might be your fastest method. Building the index at insertion for a self balancing tree is a faster in some cases (I believe this is the case) then building one on the fly. I don't think this matters much, as reindexing seems to reload the database anyway. My 2 cents. 100 million rows WOW. I initially hoped to store even a bit more. 8-/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext - stop words!?
Hi! I'm running MySQL 4.0.13 and have problem with fulltext search. I have a column of type 'text' which I fulltext index. I want to search for an e-mail address but I don't get any matches. I assume characters like @ . are ignored (stop words). How can I create an index which contains e-mail addresses? Eg. How can I edit the list of stop words? Which are the current stop words? I'm running MySQL on both Windows and Linux. Best regards /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqld shows high cpu usage over extended time, restart = normal
load). Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)? its been a known issue for quite a long time use linuxthreaded version and it should work fine. although much of work has been done on threads implementation, there are still such problems with mysql. it happens even on freebsd 5.0 Thanks Terry... gave me enough information to google the following which went into further detail regarding this issue specifically on FreeBSD http://jeremy.zawodny.com/blog/archives/000203.html Cheers, Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqld shows high cpu usage over extended time, restart = normal
Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)? Is there something I should check when next I notice the high CPU usage? I used to see the same kind of behavior a while back with a MySQL installation I did for a client. It turned out that what was happening was a poorly designed client/server app was touching off this really huge query (lots of rows and lots of columns across multiple tables) and then disconnecting before the result could be returned. They fixed their app and the problem went away. Drove me nuts troubleshooting it. Anyway. Make sure you don't have something similar going on. Will check again next time it occurs, but I don't recall seeing any other processes running at the time via mysqladmin -u root -p processlist After reading terry's post, I did come across the following resource though; http://jeremy.zawodny.com/blog/archives/000203.html thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext - stop words!?
On 9 Jul 2003 at 19:07, Peter Engström wrote: I want to search for an e-mail address but I don't get any matches. I assume characters like @ . are ignored (stop words). A stop word is a *word* that is ignored in indexing -- usually things like the and and. What you're talking about is changing the set of characters that are considered to be parts of words -- usually letters and numbers. In order to have e-mail addresses indexed as single words, you would have to define every character that could occur in an e-mail address as a word character. In particular, that would mean . would have to be a word character, and I seriously doubt you would want that. Doing that would mean that searching for numbers wouldn't find this message, which contains numbers. (with a period at the end). E-mail addresses would normally be searched for as phrases, so [EMAIL PROTECTED] would be equivalent to peten714 student liu se. To search for a phrase you need to use Boolean mode. See the documentation: http://www.mysql.com/doc/en/Fulltext_Search.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB: Operating system error number 13
Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RESETTING AUTO_INCREMENT
Hi everyone: Does anyone know how to reset the auto_increment value of certain table. Any ideas or sugestions Greetings in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESETTING AUTO_INCREMENT
Miguel: Wednesday, July 9, 2003, 3:07:33 PM, você escreveu: ---[inicio]-- MP Hi everyone: MP Does anyone know how to reset the auto_increment value of certain table. MP Any ideas or sugestions MP Greetings in advance MP _ MP Únete al mayor servicio mundial de correo electrónico: MP http://www.hotmail.com ---[cortar]-- alter table TABLE_NAME auto_increment = 1; mysql,innodb,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RESETTING AUTO_INCREMENT
Why are you looking to reset it? If you mean resetting when there is no data in a table, a truncate table should start the auto_increment over again. If you are referring to recovering some auto_increment values that were previously used by no rows now use them, it is better to avoid this. That way you can prevent some potential conflicts. You can reset the auto_increment with ALTER TABLE tablename AUTO_INCREMENT = 1; but know what you are doing when you do. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Miguel Perez [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:08 PM To: [EMAIL PROTECTED] Subject: RESETTING AUTO_INCREMENT Hi everyone: Does anyone know how to reset the auto_increment value of certain table. Any ideas or sugestions Greetings in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Hello Nick, You and I are just about on the same page here. However, I just turned the page you are now baffling over. Let me ask, Did you removed the old Macintosh HD/Library/Receipts/mysql-standard-4.0.13.pkg file before you did the new install. If I am not mistaken, this is what cinched it for me. God bless, be at peace, Mark D On Wednesday, July 9, 2003, at 02:00 PM, Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- 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]
mysql startup script problem
Hello all - I'm having a small problem with the mysql startup script that ships with MySQL-3.23.56-1. I'm running on RedHat Linux. It works fine, but I have a backup server that runs a script that passes these commands remotely through ssh: (1) ssh dbsys-dc sudo /etc/init.d/mysql stop (2) ssh dbsys-dc sudo tar czpf - /var dbsys-dc.var.$(date -I).tgz (3) ssh dbsys-dc sudo tar czpf - /db dbsys-dc.db.$(date -I).tgz (4) ssh dbsys-dc sudo /etc/init.d/mysql start Essentially, what I'm doing is stopping the mysql server, then backing up the directories, the starting the server again. The problem is that I have additional commands in my backup script following line 4 above (backup additional filesystems and server, then write all the tarballs to tape), but the mysql start script does not exit properly after starting the mysql server, and I come in the next morning and find that my backup script is stuck on line 4 above, so the rest of my filesystems and servers never get backed up, nor get written to tape. If I do a ps aux and find the PID for that task and kill it, then the rest of my script will proceed. mysql stop seems to exit fine -- it's just mysql start that seems to keep the shell locked. Does anybody know how to fix this or a workaround? Thanks so much. Duane Winner [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: recursive sql statement
See http://www.mysql.com/doc/en/TODO_future.html Oracle-like CONNECT BY PRIOR ... to search tree-like (hierarchical) structures. Whatever their definition of The Near Future is... I'd guess v5 J - Original Message - From: Bernhard Schmidt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 2:58 AM Subject: recursive sql statement hi i searched the mysql doc for support of recursive sql statements, but found nothing. i am right that mysql does not support such kind of statements? best regards benny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RESETTING AUTO_INCREMENT
hi mike you suggested the following If you are referring to recovering some auto_increment values that were previously used by no rows now use them, it is better to avoid this. how can you prevent this? best regards benny
RE: recursive sql statement
If you are looking for ways to retrieve and store hierarchical data (employees and managers, for instance) I'll also recommend looking at the Nested Set hierarchy. Search news groups for it, and look into Joe Celko's book _SQL For Smarties_, which describes the technique. You can store a complete hierarchical tree in mySQL, get the list of managers for an employee (in descending/ascending order) etc.. It's a powerful structure. I've used it to create powerful structures that operate, with the same SQL, on oracle, mysql, sql server, and others. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:35 AM To: [EMAIL PROTECTED] Subject: re: recursive sql statement See http://www.mysql.com/doc/en/TODO_future.html Oracle-like CONNECT BY PRIOR ... to search tree-like (hierarchical) structures. Whatever their definition of The Near Future is... I'd guess v5 J - Original Message - From: Bernhard Schmidt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 2:58 AM Subject: recursive sql statement hi i searched the mysql doc for support of recursive sql statements, but found nothing. i am right that mysql does not support such kind of statements? best regards benny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select not producing desired results
I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddatefsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple mysql statements in single php request
Is it possible to issue multiple sql statements in a single php request? (Probably not since I get a syntax error. mysql 4.0.13). For example: $result = mysql_query(drop table if exists tmptab; create temporary table tmptab select * from othertab; select * from tmptab where whatever); Or do I need to do 'mysql_query' three times? (I know the temp table is not necessary for the example but the real query is more complex and does need it.) My problem is that I've got a query similar to the above that works correctly when I call it from the command line as 'mysql database query.sql' but gives a wrong answer when I issue it from php. I've triple and quadruple checked that the queries are the same. The only difference I've been able to see is the single versus multiple calls. Any other ideas? BTW, it's only one row of the result set that's wrong. -- A little inaccuracy sometimes saves tons of explanation. -- H. H. Munro (Saki) Rick Pasotto[EMAIL PROTECTED]http://www.niof.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. mysql doesn't have permissions for this file. It is probably owned by root, or its directory is. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select not producing desired results
fsttik has dashes in it and your having does not. Either add dashes to your having or change the alias to min(datein+0) as fsttik. Patrick Shoaf wrote: I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddate fsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select not producing desired results
Thanks, worked perfectly! At 04:17 PM 7/9/2003, gerald_clark wrote: fsttik has dashes in it and your having does not. Either add dashes to your having or change the alias to min(datein+0) as fsttik. Patrick Shoaf wrote: I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddate fsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- 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]
NEWBIE how can i store images in a database?
hello, i'm just changing from openbase SQL to MySQL, so i'm new here. in openbase i had images stored in a database. the 'type' was set to 'object'. i can't find something like that here and also not in the manual. what should i use? thanx for advance cheers christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQLDriverCS Data Typing problem in tables
Glad I can help. The home page for the product is www.bytefx.com, however you can post bugs/patches/suggestions at www.sourceforge.net/projects/mysqlnet until I get those features implemented at my site. Reggie -Original Message- From: Kimberly Clark [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: Re: MySQLDriverCS Data Typing problem in tables I don't know anything about MySQLDriverCS but if you continue to have problems, you might try my provider at www.bytefx.com Reggie, You've ended a week's worth of frustration for me! Thanks a million! You've got a great product, even if it's not a release version yet! FYI, I only had to change about 4 lines of code to change over from MySQLDriverCS to your library. --K -- 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: NEWBIE how can i store images in a database?
You'd want to use a BLOB (binary large object) if that's what your goal is. But if you're using this for a web application I'd highly suggest that you instead save images to a directory and make a reference to the path in the database. If you have many BLOB inserts or selects on a site, your database will be A LOT slower than pulling a string URL and then the browser simply retrieving the image from the directory specified. -M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 4:22 PM To: [EMAIL PROTECTED] Subject: NEWBIE how can i store images in a database? hello, i'm just changing from openbase SQL to MySQL, so i'm new here. in openbase i had images stored in a database. the 'type' was set to 'object'. i can't find something like that here and also not in the manual. what should i use? thanx for advance cheers christian -- 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: multiple mysql statements in single php request
Never mind. I've solved my problem. My create statement had an unquoted date in the select part. It works correctly now. On Wed, Jul 09, 2003 at 03:29:11PM -0400, Rick Pasotto wrote: Is it possible to issue multiple sql statements in a single php request? (Probably not since I get a syntax error. mysql 4.0.13). For example: $result = mysql_query(drop table if exists tmptab; create temporary table tmptab select * from othertab; select * from tmptab where whatever); Or do I need to do 'mysql_query' three times? (I know the temp table is not necessary for the example but the real query is more complex and does need it.) My problem is that I've got a query similar to the above that works correctly when I call it from the command line as 'mysql database query.sql' but gives a wrong answer when I issue it from php. I've triple and quadruple checked that the queries are the same. The only difference I've been able to see is the single versus multiple calls. Any other ideas? BTW, it's only one row of the result set that's wrong. -- A little inaccuracy sometimes saves tons of explanation. -- H. H. Munro (Saki) Rick Pasotto[EMAIL PROTECTED]http://www.niof.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Any fool can criticize, condemn, and complain -- and most fools do. -- Dale Carnegie Rick Pasotto[EMAIL PROTECTED]http://www.niof.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Yep, that was it, along with what Mark said. On Wednesday, Jul 9, 2003, at 15:10 US/Central, gerald_clark wrote: Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. mysql doesn't have permissions for this file. It is probably owned by root, or its directory is. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- 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]
help me please .... deplome project
Hi every one : i'm trying to make a db program using Mysql cbuilder6 . on my table a have fields named patient_Id . its an auto_increment value but i got problem . the problem is when i try to get the value of that fields i got nothing just 0 although i execute the commands SQLClientDataSet-Append(); SQLClientDataSet--Insert(); how can i solve this problem ??
Cbuilder
You are going to have to give us more information on what those functions do If at all possible capture the query itself and let us know more about the details Also what type of connection to the Database ODBC, JDBC are you implementing. Regards, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql startup script problem
Contents are Direct Alliance Corporation CONFIDENTIAL - Duane, Does mysql actually start back up? Or just get hung on step(4)? I know I have had problems running scripts over ssh because the sudo environment user wasn't being executed as root. (where a cron_tab was actually executing the script). Maybe make sure the env is trying to start the script as the correct user. -Original Message- From: Duane Winner [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:27 AM To: [EMAIL PROTECTED] Subject: mysql startup script problem Hello all - I'm having a small problem with the mysql startup script that ships with MySQL-3.23.56-1. I'm running on RedHat Linux. It works fine, but I have a backup server that runs a script that passes these commands remotely through ssh: (1) ssh dbsys-dc sudo /etc/init.d/mysql stop (2) ssh dbsys-dc sudo tar czpf - /var dbsys-dc.var.$(date -I).tgz (3) ssh dbsys-dc sudo tar czpf - /db dbsys-dc.db.$(date -I).tgz (4) ssh dbsys-dc sudo /etc/init.d/mysql start Essentially, what I'm doing is stopping the mysql server, then backing up the directories, the starting the server again. The problem is that I have additional commands in my backup script following line 4 above (backup additional filesystems and server, then write all the tarballs to tape), but the mysql start script does not exit properly after starting the mysql server, and I come in the next morning and find that my backup script is stuck on line 4 above, so the rest of my filesystems and servers never get backed up, nor get written to tape. If I do a ps aux and find the PID for that task and kill it, then the rest of my script will proceed. mysql stop seems to exit fine -- it's just mysql start that seems to keep the shell locked. Does anybody know how to fix this or a workaround? Thanks so much. Duane Winner [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This message is for the designated recipient(s) only and contains Direct Alliance Corporation privileged and confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of this email is prohibited.
Re: NEWBIE how can i store images in a database?
thanks a lot!!! cheers christian You'd want to use a BLOB (binary large object) if that's what your goal is. But if you're using this for a web application I'd highly suggest that you instead save images to a directory and make a reference to the path in the database. If you have many BLOB inserts or selects on a site, your database will be A LOT slower than pulling a string URL and then the browser simply retrieving the image from the directory specified. -M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 4:22 PM To: [EMAIL PROTECTED] Subject: NEWBIE how can i store images in a database? hello, i'm just changing from openbase SQL to MySQL, so i'm new here. in openbase i had images stored in a database. the 'type' was set to 'object'. i can't find something like that here and also not in the manual. what should i use? thanx for advance cheers christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NEWBIE how can i store images in a database?
Mike Brum wrote: I've been preparing some notes for my students on how to do this (using PHP/MySQL), if you're interested have a look at http://www.scit.wlv.ac.uk/~jphb/sst/php/extra/images1.html - I've only just put these notes together so if you can see any errors please let me know before I mislead next year's students ;-) You'd want to use a BLOB (binary large object) if that's what your goal is. But if you're using this for a web application I'd highly suggest that you instead save images to a directory and make a reference to the path in the database. If you have many BLOB inserts or selects on a site, your database will be A LOT slower than pulling a string URL and then the browser simply retrieving the image from the directory specified. -M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 4:22 PM To: [EMAIL PROTECTED] Subject: NEWBIE how can i store images in a database? hello, i'm just changing from openbase SQL to MySQL, so i'm new here. in openbase i had images stored in a database. the 'type' was set to 'object'. i can't find something like that here and also not in the manual. what should i use? thanx for advance cheers christian -- From Peter Burden, [EMAIL PROTECTED] http://www.scit.wlv.ac.uk/~jphb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select not producing desired results
Hi, If you send a table def (mysqldump would be good) I will be able to experiment and come up with an answer... Cheers, Andrew -Original Message- From: Patrick Shoaf [mailto:[EMAIL PROTECTED] Sent: Wednesday 09 July 2003 20:30 To: [EMAIL PROTECTED] Subject: Select not producing desired results Importance: High I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddatefsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- 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]
MySQL performance question..
Hi, Can anyone help? I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? Will I run into any performance/storage issues when querying this data? Hope you can help, Andrew sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance question..
On 9 Jul 2003 at 23:14, Andrew Braithwaite wrote: I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? One way would be to convert him to a text representation following RFC 1437 and store him in a HUMONGOUSTEXT column. There's an example of sending Dan Quayle by e-mail in the RFC: http://www.faqs.org/rfcs/rfc1437.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
glibc 2.3.2 compatibility?
I'm trying to upgrade the glibc on some machines that do not yet have glibc2 in order to support 1000 threads. It seems that some of the instructions concerning setting a pthread maximum for older glibc's are no longer pertinent. I wonder if the glibc 2.3.2 is known to be compatible with MySQL 4 and also if it is known to support 1000 threads? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: glibc 2.3.2 compatibility?
James B. Wetterau Jr. wrote: I'm trying to upgrade the glibc on some machines that do not yet have glibc2 in order to support 1000 threads. It seems that some of the instructions concerning setting a pthread maximum for older glibc's are no longer pertinent. I wonder if the glibc 2.3.2 is known to be compatible with MySQL 4 and also if it is known to support 1000 threads? I'm using glibc-2.3.2 and MySQL-4.0.13 under Gentoo Linux ( at home, not on a production server ), and it seems to work fine. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table problem
Further to this, I should point out everything works fine in mysql-front
or at the mysql console.
The problem shows up when using mysql++, a BadQuery exception is thrown.
query.reset();
query CREATE TEMPORARY TABLE sTemporary TYPE=HEAP
MAX_ROWS=1 subselect;
try
{
query.parse();
query.execute();
}
This is simple to solve.
As recommended in MySQL++ manual, use stream only for queries returning
result set. For the queries like above use exec() method.
Point taken, changed it to just use exec() and the same problem occurs. It
was working previously with the code above though (mysql 3), and it works
fine entered at the mysql console.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unexpected empty table performance problem with MySQL and
Hello Heikki, Thanks for your response. Hmmm... When I run 'show processlist', I get something like the following: ++--++--+-+-+---+--+ | Id | User | Host | db | Command | Time | State | Info | ++--++--+-+-+---+--+ | 2 | root | 127.0.0.1:1589 | swpadata | Query | 0 | NULL | show processlist | | 82 | root | leroy.con.somedomain12345.com:3448 | swpadata | Sleep | 1735789 | | NULL | | 83 | root | leroy.con.somedomain12345.com:3450 | swpadata | Sleep | 1735912 | | NULL | | 84 | root | 127.0.0.1:1050 | swpadata | Sleep | 1746236 | | NULL | | 85 | root | 127.0.0.1:1051 | swpadata | Sleep | 1746236 | | NULL | | 86 | root | 127.0.0.1:1052 | swpadata | Sleep | 59 | | NULL | | 87 | root | 127.0.0.1:1053 | swpadata | Sleep | 1746214 | | NULL | | 88 | root | 127.0.0.1:1055 | swpadata | Sleep | 44 | | NULL | | 89 | root | 127.0.0.1:1057 | swpadata | Sleep | 293 | | NULL | | 90 | root | 127.0.0.1:1058 | swpadata | Sleep | 44 | | NULL | ++--++--+-+-+---+--+ 10 rows in set (0.00 sec) (Sorry about the formatting...) What's this showing me? That all of these transactions are outstanding and not committed? Since there's nothing other than 'NULL' in the 'Info' column, I'm having a hard time figuring out which transaction is problematic. I've used the client port addresses to figure out which process the transactions belong to, but, unfortunately, that in itself doesn't provide me any insights. Could you offer any tips on how to use this information? Also, what would I have to have done to get into that state? If I have auto-commits off, and then try to perform some command that fails (therefore unexpectedly exiting the relevant section of my code without either a commit or a rollback), would that do it? And if I then continue issuing other queries or updates over the same connection, would they still work fine, therefore hiding the fact that a transaction's been left dangling? If so, what's the right thing to do here: issue a rollback? Thank you for your help with this. Best regards, Alex Alex, ---TRANSACTION 0 125987852, ACTIVE 1217449 sec, OS thread id 1712 you have transactions which have been active 1.2 million seconds, that is, 15 days! You should commit those transactions. You can also use SHOW PROCESSLIST to show those open sessions. Regards, Heikki __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql startup script problem
On Wednesday 09 July 2003 17:49, Cory Lamle wrote: Contents are Direct Alliance Corporation CONFIDENTIAL - Duane, Does mysql actually start back up? Or just get hung on step(4)? Yes, mysql starts back up ok. I do a /etc/init.d/mysql start and the daemon starts just as it should. I know I have had problems running scripts over ssh because the sudo environment user wasn't being executed as root. (where a cron_tab was actually executing the script). Maybe make sure the env is trying to start the script as the correct user. At first I suspected similiar problems, but then I realized that even if I'm sitting at the actual server console running mysql and do a /etc/init.d/mysql start, I get the same thing: I dont get bash shell command line returned to me. I guess that maybe this a bash scripting problem more than anything else? I actually tried to edit the mysql startup script and played around with the 'start' routine logic by inserting an 'exit', 'done' and 'break', but nothing here seems to work. Unfortunately I don't know enough about bash scripting that I should. -Original Message- From: Duane Winner [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:27 AM To: [EMAIL PROTECTED] Subject: mysql startup script problem Hello all - I'm having a small problem with the mysql startup script that ships with MySQL-3.23.56-1. I'm running on RedHat Linux. It works fine, but I have a backup server that runs a script that passes these commands remotely through ssh: (1) ssh dbsys-dc sudo /etc/init.d/mysql stop (2) ssh dbsys-dc sudo tar czpf - /var dbsys-dc.var.$(date -I).tgz (3) ssh dbsys-dc sudo tar czpf - /db dbsys-dc.db.$(date -I).tgz (4) ssh dbsys-dc sudo /etc/init.d/mysql start Essentially, what I'm doing is stopping the mysql server, then backing up the directories, the starting the server again. The problem is that I have additional commands in my backup script following line 4 above (backup additional filesystems and server, then write all the tarballs to tape), but the mysql start script does not exit properly after starting the mysql server, and I come in the next morning and find that my backup script is stuck on line 4 above, so the rest of my filesystems and servers never get backed up, nor get written to tape. If I do a ps aux and find the PID for that task and kill it, then the rest of my script will proceed. mysql stop seems to exit fine -- it's just mysql start that seems to keep the shell locked. Does anybody know how to fix this or a workaround? Thanks so much. Duane Winner [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
