Re: moving data
Hi Shawn, Thanks for helping me out again! I've cut and pasted the answers below your questions: I would love to say how adequate your hardware should be but I have forgotten what hardware you have, sorry! Would you mind re-posting all of the specs for your DB server? 2 x 500Mhz CPUs 8GB Memory mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc Also could you post the results of : EXPLAIN select * From t20040908 where uid=454 to make sure the query is using the `uid` key (it should be but it never hurts to check). mysql EXPLAIN select * From t20040909 where uid=454; +---+--+---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--+-+ | t20040909 | ref | uid | uid | 4 | const | 4275 | Using where | +---+--+---+--+-+---+--+-+ 1 row in set (0.07 sec) Could you also respond with the results of : SHOW TABLE STATUS LIKE 't20040908' mysql SHOW TABLE STATUS LIKE 't20040909'; +---+++--++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--++-+-+--+---++-+-+-++-+ | t20040909 | MyISAM | Fixed | 25209614 | 32 | 806707648 |137438953471 |222495744 | 0 | NULL | 2004-09-09 14:24:41 | 2004-09-10 01:01:35 | 2004-09-09 14:25:40 || | +---+++--++-+-+--+---++-+-+-++-+ 1 row in set (0.01 sec) Part of the time it takes to query any table is the time it takes to load the correct index in from the disk so... maybe? you hardware could be too slow for this volume of information? I just can't say with any degree of confidence one way or the other yet. I did notice that this query returned in only about 11% of the time it took a similar query on the full dataset to return (6.87 seconds vs. 61 seconds a similar lookup on internet_usage). Better but not exactly linearly related to the reduction in the data size. I would think this is related to the fact that the time it takes to find records using a B-TREE index degrades roughly logarithmically. I thought about putting a HASH index on that column but those are only available to the MEMORY table type (bummer). BTW, if you and I take this thread back into the list everyone else will also have a chance to review your hardware specs and throw in their two cents, too! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/09/2004 04:48:47 AM: Hi Shawn, I'm working at the moment on the new database! I had a quick question for you. Here's my table structure: t20040908 | CREATE TABLE `t20040908` ( `uid` int(10) unsigned NOT NULL default '0', `time` timestamp(14) NOT NULL, `ip` int(10) unsigned NOT NULL default '0', `urlid` int(10) unsigned NOT NULL default '0', `timetaken` smallint(5) unsigned default '0', `cs_size` int(10) unsigned default '0', `sc_size` int(10) unsigned default '0', `method_ID` tinyint(3) unsigned NOT NULL default '0', `action_ID` tinyint(3) unsigned NOT NULL default '0', `virus_ID` tinyint(3) unsigned NOT NULL default '0', `useragent_ID` smallint(5) unsigned NOT NULL default '0', KEY `uid` (`uid`) ) TYPE=MyISAM and it currently has 15263552 rows. I ran the command: select * from t20040908 where uid=454; which came back with 8347 rows in set (6.87 sec) Does 6.87 seconds seem rather slow to you ? It could be the box is just too old and slow. But I just wanted to check in case there was anything else I should check? Cheers, JS. There are two ways that I can think of that will combine smaller tables into one longer (not wider) table: MERGE tables and UNION queries. A third option is to manually add rows from multiple source tables to a staging table using INSERT...SELECT... statements. Since we never know from day to day what you will need to query on, creating one big MERGE table would probably be as impractical as your existing setup. That leaves the other two options available to run reports against. Since your information comes in daily
delay_key_write usage
According to the documentation, when delay_key_write is active the index keys arent written to disk until the table(s) are closed. When is a table closed? Is it only when a connection is closed? Thank you, Dan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.737 / Virus Database: 491 - Release Date: 8/11/2004
Dump question: transactions vs. locking
Hi,
I think I have some problems understanding how transactions work, and
how they relate to locking. Can someone please explain?
The question is this:
I have a table where I have a column into which I insert numbers in
sequencial order - it is not set to auto_increment, though, because the
field is only unique with two other columns. The fields are:
mainid,subid and userid (in the table invoices).
Before I changed to InnoDB, using MyISAM I used to lock the table, get
MAX(subid), do an insert, then unlock. It was something like this (this
is Perl):
# lock the table
$dbh-do('LOCK TABLES invoices WRITE');
# next subid is MAX(subid)
my ($nextsubid) = $dbh-selectrow_array('SELECT MAX(subid) FROM invoices
WHERE userid=? AND mainid=?', undef, $userid, $mainid);
# increment by 1
$nextsubid++;
# insert all stuff with new $subid
$dbh-do('INSERT INTO invoices '); # set subid here
# unlock
$dbh-do('UNLOCK TABLES');
So what if I change to transactions? Should I simply just substitute
LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the
later, what is the correct order: BEGIN and then LOCK, or LOCK and then
BEGIN? Also, am I right that as in transactional mode I always need a
commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple
LOCK/UNLOCK?
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I avoid warm-up time?
Hi, We are now in the last phases of testing a new system on Mysql. We are using all MyISAM tables, on Linux. We found that after the mysql server is restarted, our application is very slow for a couple of minutes. We understand that this is because the caches have to fill up first - the mysql key cache (we made it big enough to hold all the indexes) and the OS cache. My question: is there any way we can preload the caches so that we don't experience this warm-up time? Also, we found that if the server is not active for some time, say overnight, then again we experience the warm-up time, even though the mysql server was not restarted. This leads to totally unpredictable performance. At very low loads, the times are the worse!!! If anyone had a similar experience and/or possible suggestions on how to solve this problem, it will be greatly appreciated. Thanks, Tamar Rosen www.gurunet.com
Open Source Survey
Dear all, I am conducting a survey on open source software. This is for my coursework at the Curtin Uuniversity of Technology. It will be great to have your participation in this survey. Below are the links: Brief introduction page: http://web.singnet.com.sg/~mikecck/opensource/Introduction1.htm Questionnaire 1(Open Source Development): http://web.singnet.com.sg/~mikecck/opensource/WebFormA1.htm Questionnaire 2(Open Source and IT/IS Cost): http://web.singnet.com.sg/~mikecck/opensource/WebFormB1.htm I would like to have the participation of your members who use open source software. Can you share this information with them? Thanks. Mike Chan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: state my question more clearly Re: WHY this query keeps failure?
The name of the table is temp. Could that mean it's a TEMPORARY TABLE or is it just a regular table with the name temp? If it IS a temporary table and you are trying to read its contents from a connection different than the one that created it, you won't be able to. Temporary tables are specific to the connections (sessions) that create them. Thirty users can all have their own table called temp if it is declared as a TEMPORARY TABLE. None of them would be able to read any of the other's data. Could this be your situation? Just trying to eliminate possibilities. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/09/2004 05:40:43 PM: I don't think your problem has anything to do with your Update statement or Select statements, assuming you reported them accurately. Could another user of the system have emptied your table? Could you have inadvertently executed a statement or a script that would have emptied it? Those seem like the obvious explanations to me. If neither of those is the cause, you may have stumbled on a really serious bug. Rhino - Original Message - From: Monet [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 4:02 PM Subject: state my question more clearly Re: WHY this query keeps failure? Yes, you're right. Let me explain it more clearly. Before UPDATE, there are 45 records in table temp and I updated 9 of them. Mysql returns how many rows were affected which is 9 rows. Then, I opened the table temp and found that table is empty!No records at all. Therefore, that is why I feel so wired. after a simple update, all records has been erased. does anyone have same problem before? Thanks, Monet --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Monet [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 2:13 PM Subject: WHY this query keeps failure? Hello, I was working on a table, doing a simple update on table. Query is: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well, shows how many rows was affected. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; There is no records return. The table is empty. Do you mean that your *result set* from the query is empty? Or that the *table* you are reading from (temp) is empty? You said 'table' but I *think* you mean 'result set', right? If temp is empty, your result set from the Select will certainly be empty; that should be obvious: the question is WHY temp is empty. Your table, temp, should not be empty as a result of your update statement because Update does not remove rows and your Update didn't change the 'qid' value. If Update changed 9 rows and MySQL told you that 9 rows were changed, you should still have at least those 9 rows in the table after the update has completed. You can verify that by doing: select count(*) from temp; immediately after running the update. If it returns a value of 0, your table is empty. Otherwise there are rows in the table. This happened second time. So I'm wondering it might have some problem with my query. I don't see anything in the Update or the Select that explains this problem. Rhino __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcheck --check --extended --auto-repair
Hello, the mysqlcheck manpage and the MySQL Manual is unclear about repair options when issuing a mysqlcheck with --auto-repair. For example mysqlcheck --check --extended --auto-repair db It seems to be clear that mysqlcheck does an extended check. But: if there are corrupt tables, will the repair be extended, too, or will the repair performed as mysqlcheck --repair db tablename without --extended? (as the manual states a repair with --extended can produce a lot of garbage rows, which we definitely dont want) Thanks in advance! -- cu Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl with MySQL
This is the 3rd time you have asked this. Perhaps you should take it to a perl list where it belongs. Kirti S. Bajwa wrote: Hello: I am trying to install Perl support with MySQL. After installing MySQL (v4.0.20)I run the following commands: % echo $PATH % perl -MCPAN -e shell Note: Answer no to auto-configure perl. cpan install Data::Dumper (Upto this point. Following commands are not run yet.) cpan install Bundle::DBI cpan install Bundle::DBD::mysql cpan quit Today, when I tried to install Perl using the above sequence of commands. However, after I entered the third command cpan install Data::Dumper;, a message was displayed indicating that there is a new version of perl it canbe installed by using the command cpan install Bundle::CPAN. Well, I changed the commands to as follows: % echo $PATH % perl -MCPAN -e shell Note: Answer no to auto-configure perl. cpan install Data::Dumper cpan install Bundle::CPAN cpan install Bundle::DBI cpan install Bundle::DBD::mysql cpan quit I am not sure if the above command sequence is correct or not? I know about Perl as much as I know about brain surgery. However, I am willing to read if I know where. Thanks in advance. Kirti PS: I have no idea id I posted this or not. So if it is duplicate, please ignore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I avoid warm-up time?
Tamar, The only way to fill the caches up is to execute queries. If there is a delay between your server coming up and the application being used, try executing the queries that your application will use from a start-up script (you'll need to work these out carefully. This way the caches will be full of the correct data when the application makes it's first queries. If the application starts straight away at boot time, you're stuck. As for, the system gong slow after being inactive, this sounds like your system could be swapping. This could be caused by your caches taking up all or most of the available memory, leaving none for the operating system and other things running. When your application goes idle, the operating system will copy its cache memory into swap space on the disk, and read its own and other applications memory back from the swap space into memory. The solutions to this are 1) Add more memory 2) Reduce the size of your caches so there is enough memory left for the rest of the system. Of course, the initial delay could be related to swapping too. Andy -Original Message- From: Tamar Rosen [mailto:[EMAIL PROTECTED] Sent: 10 September 2004 12:56 To: [EMAIL PROTECTED] Subject: How can I avoid warm-up time? Hi, We are now in the last phases of testing a new system on Mysql. We are using all MyISAM tables, on Linux. We found that after the mysql server is restarted, our application is very slow for a couple of minutes. We understand that this is because the caches have to fill up first - the mysql key cache (we made it big enough to hold all the indexes) and the OS cache. My question: is there any way we can preload the caches so that we don't experience this warm-up time? Also, we found that if the server is not active for some time, say overnight, then again we experience the warm-up time, even though the mysql server was not restarted. This leads to totally unpredictable performance. At very low loads, the times are the worse!!! If anyone had a similar experience and/or possible suggestions on how to solve this problem, it will be greatly appreciated. Thanks, Tamar Rosen www.gurunet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto_increment
Dears, I need to define a column within my table.I want to name it key.Also i need to it is auto_increment not null.Also i want to it is primary key. Please tell its statement. Yours,Mohsen = -DIGITAL SIGNATURE--- ///Mohsen Pahlevanzadeh Network administrator programmer My home phone is: +98213810146 My email address is m_pahlevanzadeh at yahoo dot com My website is: http://webnegar.net __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: moving data
How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? On another thoughtI was under the impression that you were trying to create a single daily table for testing purposes (hence the date in the table name) yet you seem to have 25M+ entries? Are you sure that's just one day's worth of information? In fact from the time you sent the earliest message in this post until you posted your table status, you added about 10M new rows. Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/10/2004 05:56:12 AM: Hi Shawn, Thanks for helping me out again! I've cut and pasted the answers below your questions: I would love to say how adequate your hardware should be but I have forgotten what hardware you have, sorry! Would you mind re-posting all of the specs for your DB server? 2 x 500Mhz CPUs 8GB Memory mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc Also could you post the results of : EXPLAIN select * From t20040908 where uid=454 to make sure the query is using the `uid` key (it should be but it never hurts to check). mysql EXPLAIN select * From t20040909 where uid=454; +---+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+-- +-+ | t20040909 | ref | uid | uid | 4 | const | 4275 | Using where | +---+--+---+--+-+---+-- +-+ 1 row in set (0.07 sec) Could you also respond with the results of : SHOW TABLE STATUS LIKE 't20040908' mysql SHOW TABLE STATUS LIKE 't20040909'; +---+++--+ +-+-+--+--- ++-+- +-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--+ +-+-+--+--- ++-+- +-++-+ | t20040909 | MyISAM | Fixed | 25209614 | 32 | 806707648 |137438953471 |222495744 | 0 | NULL | 2004-09-09 14:24:41 | 2004-09-10 01:01:35 | 2004-09-09 14:25:40 || | +---+++--+ +-+-+--+--- ++-+- +-++-+ 1 row in set (0.01 sec) Part of the time it takes to query any table is the time it takes to load the correct index in from the disk so... maybe? you hardware could be too slow for this volume of information? I just can't say with any degree of confidence one way or the other yet. I did notice that this query returned in only about 11% of the time it took a similar query on the full dataset to return (6.87 seconds vs. 61 seconds a similar lookup on internet_usage). Better but not exactly linearly related to the reduction in the data size. I would think this is related to the fact that the time it takes to find records using a B-TREE index degrades roughly logarithmically. I thought about putting a HASH index on that column but those are only available to the MEMORY table type (bummer). BTW, if you and I take this thread back into the list everyone else will also have a chance to review your hardware specs and throw in their two cents, too! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/09/2004 04:48:47 AM: Hi Shawn, I'm working at the moment on the new database! I had a quick question for you. Here's my table structure: t20040908 | CREATE TABLE `t20040908` ( `uid` int(10) unsigned NOT NULL default '0', `time` timestamp(14) NOT NULL, `ip` int(10) unsigned NOT NULL default '0', `urlid` int(10) unsigned NOT NULL default '0', `timetaken` smallint(5) unsigned default '0', `cs_size` int(10) unsigned default '0', `sc_size` int(10)
RE: How can I avoid warm-up time?
You didn't mention you release number, but on 4.1 you can use LOAD INDEX INTO CACHE. You can also use options like --init-file and enter your startup sql there for your warmup. Ed -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 7:36 AM To: Mysql List Subject: RE: How can I avoid warm-up time? Tamar, The only way to fill the caches up is to execute queries. If there is a delay between your server coming up and the application being used, try executing the queries that your application will use from a start-up script (you'll need to work these out carefully. This way the caches will be full of the correct data when the application makes it's first queries. If the application starts straight away at boot time, you're stuck. As for, the system gong slow after being inactive, this sounds like your system could be swapping. This could be caused by your caches taking up all or most of the available memory, leaving none for the operating system and other things running. When your application goes idle, the operating system will copy its cache memory into swap space on the disk, and read its own and other applications memory back from the swap space into memory. The solutions to this are 1) Add more memory 2) Reduce the size of your caches so there is enough memory left for the rest of the system. Of course, the initial delay could be related to swapping too. Andy -Original Message- From: Tamar Rosen [mailto:[EMAIL PROTECTED] Sent: 10 September 2004 12:56 To: [EMAIL PROTECTED] Subject: How can I avoid warm-up time? Hi, We are now in the last phases of testing a new system on Mysql. We are using all MyISAM tables, on Linux. We found that after the mysql server is restarted, our application is very slow for a couple of minutes. We understand that this is because the caches have to fill up first - the mysql key cache (we made it big enough to hold all the indexes) and the OS cache. My question: is there any way we can preload the caches so that we don't experience this warm-up time? Also, we found that if the server is not active for some time, say overnight, then again we experience the warm-up time, even though the mysql server was not restarted. This leads to totally unpredictable performance. At very low loads, the times are the worse!!! If anyone had a similar experience and/or possible suggestions on how to solve this problem, it will be greatly appreciated. Thanks, Tamar Rosen www.gurunet.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]
Breaking table columns into separate rows
I have a table that has a structure similar to this | KeyField | Attrib1 | Attrib2 | Attrib 3 | What I need to do for a report that I need to generate is break this into multiple rows, as | KeyField | Attrib1 | | KeyField | Attrib2 | | KeyField | Attrib3 | Key field is not unique, and obviously I'd prefer if the result table was actually | KeyField | Attrib | so that I can suppress rows where Attrib contains no data. I can think of several ways to do this, but they're all too convoluted for the system we're using here. Ideally, it should be in a single SELECT statement. I've tried subqueries and temporary tables but I've been unable to make this work. Any pointers that anyone can give me? -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hello, A have two tables: City: CityID CityName Client: ClientID ClienteName CityID How can I make a SELECT that returns me only the cities that have more than 30 (example) clients? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto_increment
I hate to dampen your spirits but I think you have made a poor design choice. BEGIN GLOOM and DOOM The word key is a reserved word in MySQL. In order to assign to a database object a name that is either a reserved word or a name that uses a special character, you must surround that with a pair of backticks, ``. Those are not to be confused with a pair of single quotes, ''. Read here for details: http://dev.mysql.com/doc/mysql/en/Reserved_words.html http://dev.mysql.com/doc/mysql/en/Legal_names.html BE VERY WARNED: once you create this table you will ALWAYS need to refer to that column by surrounding its name with backticks. May I suggest you use an alternate spelling (like key_) or an alternate name (like id) for your column to avoid needing backticks? Anyway, if you _must_ keep your current design choice: CREATE TABLE backticktest `key` int auto_increment, data varchar(100) null, PRIMARY KEY(`key`) ) Just to add to my warning, here is a sample select: SELECT `key`,data from backticktest where `key`=15; If you forget those backticks, your query WILL FAIL. END GLOOM and DOOM :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Pahlevanzadeh Mohsen [EMAIL PROTECTED] wrote on 09/10/2004 09:49:00 AM: Dears, I need to define a column within my table.I want to name it key.Also i need to it is auto_increment not null.Also i want to it is primary key. Please tell its statement. Yours,Mohsen = -DIGITAL SIGNATURE--- ///Mohsen Pahlevanzadeh Network administrator programmer My home phone is: +98213810146 My email address is m_pahlevanzadeh at yahoo dot com My website is: http://webnegar.net __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Breaking table columns into separate rows
When you say report what exactly do you mean? In the client., have you looked at using \G and not ; to terminate your queries? Why not transform your data structure and keep only one column of data, as you suggested? Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/10/2004 10:11:05 AM: I have a table that has a structure similar to this | KeyField | Attrib1 | Attrib2 | Attrib 3 | What I need to do for a report that I need to generate is break this into multiple rows, as | KeyField | Attrib1 | | KeyField | Attrib2 | | KeyField | Attrib3 | Key field is not unique, and obviously I'd prefer if the result table was actually | KeyField | Attrib | so that I can suppress rows where Attrib contains no data. I can think of several ways to do this, but they're all too convoluted for the system we're using here. Ideally, it should be in a single SELECT statement. I've tried subqueries and temporary tables but I've been unable to make this work. Any pointers that anyone can give me? -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
SELECT CityName, Count(ClientID) as ClientCount FROM City INNER JOIN Client on City.CityID = Client.CityID GROUP BY CityName HAVING ClientCount 30; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ronan Lucio [EMAIL PROTECTED] wrote on 09/10/2004 11:14:37 AM: Hello, A have two tables: City: CityID CityName Client: ClientID ClienteName CityID How can I make a SELECT that returns me only the cities that have more than 30 (example) clients? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Shawn SELECT CityName, Count(ClientID) as ClientCount FROM City INNER JOIN Client on City.CityID = Client.CityID GROUP BY CityName HAVING ClientCount 30; Thank you very much, It should solve by problem... :-) Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dump question: transactions vs. locking
Maybe MyISAM is still a better choice for this use...?
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary
column (or three columns in your case) in a multiple-column index. In
this case, the generated value for the AUTO_INCREMENT column is
calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix.
This is useful when you want to put data into ordered groups.
See the link:
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html
Ed
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 4:53 AM
To: [EMAIL PROTECTED]
Subject: Dump question: transactions vs. locking
Hi,
I think I have some problems understanding how transactions work, and
how they relate to locking. Can someone please explain?
The question is this:
I have a table where I have a column into which I insert numbers in
sequencial order - it is not set to auto_increment, though, because the
field is only unique with two other columns. The fields are:
mainid,subid and userid (in the table invoices).
Before I changed to InnoDB, using MyISAM I used to lock the table, get
MAX(subid), do an insert, then unlock. It was something like this (this
is Perl):
# lock the table
$dbh-do('LOCK TABLES invoices WRITE');
# next subid is MAX(subid)
my ($nextsubid) = $dbh-selectrow_array('SELECT MAX(subid) FROM invoices
WHERE userid=? AND mainid=?', undef, $userid, $mainid);
# increment by 1
$nextsubid++;
# insert all stuff with new $subid
$dbh-do('INSERT INTO invoices '); # set subid here
# unlock
$dbh-do('UNLOCK TABLES');
So what if I change to transactions? Should I simply just substitute
LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the
later, what is the correct order: BEGIN and then LOCK, or LOCK and then
BEGIN? Also, am I right that as in transactional mode I always need a
commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple
LOCK/UNLOCK?
Thank you,
- Csongor
--
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_config missing - already read much docs
I just upgraded Mysql to 4.1.4-0 on linux using the following: MySQL-server-4.1.4-0.i386.rpm MySQL-client-4.1.4-0.i386.rpm So when i went to upgrade my perl DBI DBD::mysql packages, it noticed that mysql_config was missing. My understanding is that mysql_config should be installed with the client libraries. I looked at the /usr/bin directory before and after installing the client libs, and mysql_config is not there. Here is a diff output of 'ls -l /usr/bin/my *' both before and after the client rpm was installed. -rwxr-xr-x1 root root 1696220 Aug 28 02:54 mysql -rwxr-xr-x1 root root 111478 Aug 28 02:54 mysqlaccess -rwxr-xr-x1 root root 1516376 Aug 28 02:54 mysqladmin -rwxr-xr-x1 root root 1550488 Aug 28 02:54 mysqlbinlog 6a11 -rwxr-xr-x1 root root 1512908 Aug 28 02:54 mysqlcheck 10a16,17 -rwxr-xr-x1 root root 1533448 Aug 28 02:54 mysqldump -rwxr-xr-x1 root root 4986 Aug 28 02:54 mysqldumpslow 11a19 -rwxr-xr-x1 root root 2653 Aug 28 02:54 mysql_find_rows 14a23 -rwxr-xr-x1 root root 1510200 Aug 28 02:54 mysqlimport 17a27,28 -rwxr-xr-x1 root root 1510316 Aug 28 02:54 mysqlshow -rwxr-xr-x1 root root13647 Aug 28 02:54 mysql_tableinfo 19a31 -rwxr-xr-x1 root root 680464 Aug 28 02:54 mysql_waitpid So the client rpm did install a bunch of things, but not the mysql_config executable. I'm rather confused by this. Perhaps i should go back to installing source distros. Anybody seen anything like this? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
search a field in all the tables?
Many times I need to do this. I know the name of a specific field. But I don't know which table has this field. There usually are a lot tables in one database. So the question is how I can find out which table or tables has this field? If it can be done within one database, can it also be done across all the databases? Thanks in advance for any help. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize index building
Hi, We are using following steps to re-load and re-build a table every day: truncate the table disable index load data enable index What are the important parameters I should be watching out to make the ebale index step faster? Out table is over 10million rows and has two fulltext indexes. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). mysql show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ 30 rows in set (0.01 sec) Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? Not sure how to check the system bus but this is the info on the CPUs: RS/6000 pSeries Details CPU Architecture=PowerPC Implementation=RS64-III, 64 bit Machine has 2 CPUs (2 CPUs activated) CPU Level 1 Cache is Combined Instruction=131072 bytes Data=131072 bytes Level 2 Cache size=4194304 AIX 4.3.3.86 On another thoughtI was under the impression that you were trying to create a single daily table for testing purposes (hence the date in the table name) yet you seem to have 25M+ entries? Are you sure that's just one day's worth of information? In fact from the time you sent the earliest message in this post until you posted your table status, you added about 10M new rows. I was tailing the proxies from about lunchtime yesterday and piping the output into the database. By the end of the day there were 25m row. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Breaking table columns into separate rows
First, I think your data structure is wrong. Like data should be in a single column, like an attribute column. This provides scalability (no limit on attributes) and efficiency (no empty columns). But, since you are probably stuck with what you have, try something like this: SELECT CONCAT(KeyField,|,Attrib1,\n) AS Attrib1, CONCAT(KeyField,|,Attrib2,\n) AS Attrib2, CONCAT(KeyField,|,Attrib3,\n) AS Attrib3 FROM dbname ... Depending on your platform you would change \n (new line) to \r (return) or both. This will give you a sudo new line. I just threw the pipe in as a separator, but that could be anything you want. On Sep 10, 2004, at 10:11 AM, [EMAIL PROTECTED] wrote: I have a table that has a structure similar to this | KeyField | Attrib1 | Attrib2 | Attrib 3 | What I need to do for a report that I need to generate is break this into multiple rows, as | KeyField | Attrib1 | | KeyField | Attrib2 | | KeyField | Attrib3 | Key field is not unique, and obviously I'd prefer if the result table was actually | KeyField | Attrib | so that I can suppress rows where Attrib contains no data. I can think of several ways to do this, but they're all too convoluted for the system we're using here. Ideally, it should be in a single SELECT statement. I've tried subqueries and temporary tables but I've been unable to make this work. Any pointers that anyone can give me? -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
J S wrote: How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. See www.baarf.com for comments against RAID 5 Colin Bull -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Breaking table columns into separate rows
I'm going to be creating a printed report for the users using a report writing application (developed in-house by me, so I could,at some point, change it to do more t han it does, but there are only so many hours in a day) The table structure is not under my control; it's a legacy table structure from our mainframe and my suggestion that we change the table structure in the move was...urm...not accepted. The output report will be something like = HEADER INFORMATION: Name Address Key Value --- KEY Attrib KEY Attrib KEY Attrib --- Total Attrib-total = Significantly more complex than that, but in general, that's how it should work. - Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 7:18 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Breaking table columns into separate rows When you say report what exactly do you mean? In the client., have you looked at using \G and not ; to terminate your queries? Why not transform your data structure and keep only one column of data, as you suggested? Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/10/2004 10:11:05 AM: I have a table that has a structure similar to this | KeyField | Attrib1 | Attrib2 | Attrib 3 | What I need to do for a report that I need to generate is break this into multiple rows, as | KeyField | Attrib1 | | KeyField | Attrib2 | | KeyField | Attrib3 | Key field is not unique, and obviously I'd prefer if the result table was actually | KeyField | Attrib | so that I can suppress rows where Attrib contains no data. I can think of several ways to do this, but they're all too convoluted for the system we're using here. Ideally, it should be in a single SELECT statement. I've tried subqueries and temporary tables but I've been unable to make this work. Any pointers that anyone can give me? -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
SAN --- I think that's the key piece to this puzzle. It doesn't matter how fat your network pipes are, you will always encounter some network lag when reading or writing data from and to a SAN. There is an abstraction layer that must bundle your request to the disk then translate it from the device that will translate into additional CPU overhead PER DISK TRANSACTION. Most people won't notice the difference when they are moving their personal files but a database certainly does. The number of disk reads and writes per second normally handled by hardware translates into network round-trips per second when you are using a SAN. Multiply each round trip (thousands, millions?) by the average lag and you have one source of your slowdown. Add just 1 millisecond to each read request and it quickly adds up to several seconds per query for tables your size. I just became really pressed for time today so must I defer to others on the list to interpret your settings. Please everyone, pitch in on this one, OK? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/10/2004 10:55:23 AM: How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). mysql show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ 30 rows in set (0.01 sec) Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? Not sure how to check the system bus but this is the info on the CPUs: RS/6000 pSeries Details CPU Architecture=PowerPC Implementation=RS64-III, 64 bit Machine has 2 CPUs (2 CPUs activated) CPU Level 1 Cache is Combined Instruction=131072 bytes Data=131072 bytes Level 2 Cache size=4194304 AIX 4.3.3.86 On another
Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table
Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone familiar with dbQwiksite Pro
Wondering if there is anyone on the list who knows this product . Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Implementing xml/xpath UDF with libxml2
I'm trying to implement a UDF that supports xpath evaluation on a column using the gnome libxml2 library. The library is thread-safe EXCEPT for the initialization and deinitialization routines which need to be called on a pre-process basis. My question: 1. Is there a good place (or any place??) to call per-process initialization and deinitialization routines for UDF's? - or - 2. Is there another way to do this? Thanks. Joel
RE: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- 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: search a field in all the tables?
- Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 10, 2004 10:52 AM Subject: search a field in all the tables? Many times I need to do this. I know the name of a specific field. But I don't know which table has this field. There usually are a lot tables in one database. So the question is how I can find out which table or tables has this field? If it can be done within one database, can it also be done across all the databases? Thanks in advance for any help. Do you know Java? If you use the getColumns() method in the DatabaseMetaData interface, you can determine the names of columns in a MySQL database even if you don't know the name of the table. You can even specify that you only want column names whose name follows a particular pattern. For example: ResultSet rs = dbMeta.getColumns(myCatalog, %, %, C%); requests information about all of the columns in your database where the column name starts with C and you don't know or care about the schema name or the table name. I just knocked together a little sample Java program to prove that this works. I'm not sure if there is any other way to get the information. In DB2, the database that I know best, you can query the database catalog from the command line or a program just like you can with a regular user table and determine column names that way; I haven't figured out any way to do that in MySQL. If anyone can tell me any way to query the catalog tables in MySQL directly, rather than via Java, I'd love to know! (I tried looking in the MySQL manual but couldn't find anything like that.) Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date question..
i have a tbl with the following... foo date1 foo2 date2 etc... how can i do a select to get the last or 1st row based on the (latest/earliest) date? can't seem to get it to work.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's Faster? MySQL Queries or PHP Loops?
I've been meaning to follow up on this post. Can either Peter or someone expand and provide an example of get all information in a single query... Thank you , Stuart --- Peter Lovatt [EMAIL PROTECTED] wrote: Hi What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: date question..
bruce wrote: i have a tbl with the following... foo date1 foo2 date2 etc... how can i do a select to get the last or 1st row based on the (latest/earliest) date? SELECT * FROM tbl order by datecolumn DESC LIMIT 1; SELECT * FROM tbl order by datecolumn ASC LIMIT 1; can't seem to get it to work.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search a field in all the tables?
Thanks so much for taking time replying, Rhino. Unfortunately, I don't know Java much. But I got your point. Yeah, I wish it could be done within MySQL. Bing - Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 10, 2004 10:52 AM Subject: search a field in all the tables? Many times I need to do this. I know the name of a specific field. But I don't know which table has this field. There usually are a lot tables in one database. So the question is how I can find out which table or tables has this field? If it can be done within one database, can it also be done across all the databases? Thanks in advance for any help. Do you know Java? If you use the getColumns() method in the DatabaseMetaData interface, you can determine the names of columns in a MySQL database even if you don't know the name of the table. You can even specify that you only want column names whose name follows a particular pattern. For example: ResultSet rs = dbMeta.getColumns(myCatalog, %, %, C%); requests information about all of the columns in your database where the column name starts with C and you don't know or care about the schema name or the table name. I just knocked together a little sample Java program to prove that this works. I'm not sure if there is any other way to get the information. In DB2, the database that I know best, you can query the database catalog from the command line or a program just like you can with a regular user table and determine column names that way; I haven't figured out any way to do that in MySQL. If anyone can tell me any way to query the catalog tables in MySQL directly, rather than via Java, I'd love to know! (I tried looking in the MySQL manual but couldn't find anything like that.) Rhino -- 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: What's Faster? MySQL Queries or PHP Loops?
I assume he means selecting every entry in a given table (select column from table). You should be able to easily find the answer to this by inserting some code that records times as you run. My bets are on SQL. Cheers, Brian -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 10:49 AM To: Peter Lovatt; Brent Baisley; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: What's Faster? MySQL Queries or PHP Loops? I've been meaning to follow up on this post. Can either Peter or someone expand and provide an example of get all information in a single query... Thank you , Stuart --- Peter Lovatt [EMAIL PROTECTED] wrote: Hi What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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:
where oh where, help!
I have this query (below) that on it's own , directly into the database seems to return all the records just fine. Apparently though feeding it into a php loop, the lack of where is causing great distress(at least to the author) I'm wondering where to put it though. Before each join ? And then what does the from imply. Hope I make sense here, delirium is setting in. :) Select VendorJobs.JobID, VendorJobs.PostStart, VendorJobs.JobTitle, VendorJobs.AreaCode, VendorJobs.PayRate, VendorJobs.Contact, VendorJobs.Conmail, VendorSignUp.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, staTaxTerm.TaxTerm, staTravelReq.TravelReq From (VendorJobs VendorJobs INNER JOIN VendorSignUp VendorSignUp ON (VendorJobs.VendorID = VendorSignUp.VendorID)) INNER JOIN StaIndTypes StaIndTypes ON (VendorJobs.Industry = StaIndTypes.CareerIDs)) LEFT OUTER JOIN StaUSCities StaUSCities ON (VendorJobs.LocationCity = StaUSCities.CityID)) INNER JOIN USStates USStates ON (VendorJobs.LocationState = USStates.StateID)) INNER JOIN staTaxTerm staTaxTerm ON (VendorJobs.TaxTerm = staTaxTerm.TaxTermID)) INNER JOIN staTravelReq staTravelReq ON (VendorJobs.TravelReq = staTravelReq.TravelReqID) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advanced Query Wizard
This is not a sales post. I have been struggling with JOINed SELECT statements for a while now. The syntax just seems to elude me. However, I just found a Win Dreamweaver extension that rocks. The Advanced Query Wizard allows me to very quickly and easily write sophisticated, joined queries with conditionals and sorting from many tables at once. It has made my life considerably easier so I wanted to pass on the information. https://www.advancedextensions.com/products.htm Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Local variables
Hello all: I need to make certain operations like this: select tbl_no as 'Number' , @sal1 := tbl_db - tbl_hb + @sal1 as 'Total' I'm using the C API; so I make it through mysql_query() Problem is than having `tbl_db' and 'tbl_hb' with certain values no operation is carried out, and `Total' is NULL. What am I doing bad ? Please, help me Thanks all Carlos Savoretti [EMAIL PROTECTED] ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The UNION makes us strong^H^H^H^Hcrazy
In a further followup to my needing to combine colummns, I have the two following SELECTS that each work when I do them alone select empssn,paycode_1,payrate_1 from paympe where not paycode_1 = '000' select empssn,paycode_2,payrate_2 from paympe where not paycode_2 = '000' However, when I do select empssn,paycode_1,payrate_1 from paympe where not paycode_1 = '000' union all select empssn,paycode_2,payrate_2 from paympe where not paycode_2 = '000' I get a syntax error According to the docs, this should work in versions past mySQL 4, and I seem to be running a version rather later than that mysql Ver 11.18 Distrib 3.23.52, for pc-linux (i686) What am I doing wrong here? I have two valid SELECT statements; the field sizes and types are the same (indeed, empssn is the same field). This SHOULD provide me with what I'm looking for, but... -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The UNION makes us strong^H^H^H^Hcrazy
In a further followup to my needing to combine colummns, I have the two following SELECTS that each work when I do them alone select empssn,paycode_1,payrate_1 from paympe where not paycode_1 = '000' select empssn,paycode_2,payrate_2 from paympe where not paycode_2 = '000' However, when I do select empssn,paycode_1,payrate_1 from paympe where not paycode_1 = '000' union all select empssn,paycode_2,payrate_2 from paympe where not paycode_2 = '000' I get a syntax error According to the docs, this should work in versions past mySQL 4, and I seem to be running a version rather later than that mysql Ver 11.18 Distrib 3.23.52, for pc-linux (i686) Isn't this MySQL version 3.23? Try a: select version() What am I doing wrong here? I have two valid SELECT statements; the field sizes and types are the same (indeed, empssn is the same field). This SHOULD provide me with what I'm looking for, but... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Keyword Searches
Anyone created one? I've got a project that consists of a product catalog which the client wants to be keyword searchable. I have thought through several possible construction scenarios... 1) Table of products consisting of a single record per product. Each record contains a field in which they can enter a list of keywords. Search this field using MATCH/AGAINST. Pros: easy table construction, simple forms. Cons: possibly introduce typographical errors when entering keywords, tons of keywords have to be entered for each product, large table sizes. 2) Table of products consisting of a single record per product. Table of keywords consisting of a single record per keyword. Product table contains a field in which some kind of list (PHP Array, comma-delimited list, etc.) of keyword record IDs is stored. Search this field with MATCH/AGAINST or something similar. Pros: Simple table construction, avoids redundant keyword entering Cons: How to create list of keyword record IDs in insert form, how to list keywords from which to choose on insert form (list, text area, checkboxes, etc.), how to search keyword list field in product record efficiently. 3) Table of products consisting of a single record per product. Table of keywords consisting of a single record per keyword. Product table contains a field in which a comma-delimited list of the actual keywords selected is stored. Search this field with MATCH/AGAINST. Pros: Simple non-related table construction, avoids redundant keyword entering Cons: How to create list of keywords in insert form, how to list keywords from which to choose on insert form (list, text area, checkboxes, etc.), correction of mistyped keywords later Anyone else's suggestions are welcome. I'm trying to decide how best to construct this site. Also, if anyone knows of an online or published tutorial on this type project, it would be welcome. Thanx, -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: The UNION makes us strong^H^H^H^Hcrazy
Ah, so it is. Thanks. -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-3160when you do call for them? -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 12:24 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: The UNION makes us strong^H^H^H^Hcrazy In a further followup to my needing to combine colummns, I have the two following SELECTS that each work when I do them alone select empssn,paycode_1,payrate_1 from paympe where not paycode_1 = '000' select empssn,paycode_2,payrate_2 from paympe where not paycode_2 = '000' However, when I do select empssn,paycode_1,payrate_1 from paympe where not paycode_1 = '000' union all select empssn,paycode_2,payrate_2 from paympe where not paycode_2 = '000' I get a syntax error According to the docs, this should work in versions past mySQL 4, and I seem to be running a version rather later than that mysql Ver 11.18 Distrib 3.23.52, for pc-linux (i686) Isn't this MySQL version 3.23? Try a: select version() What am I doing wrong here? I have two valid SELECT statements; the field sizes and types are the same (indeed, empssn is the same field). This SHOULD provide me with what I'm looking for, but... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The UNION makes us strong^H^H^H^Hcrazy
On Fri, 2004-09-10 at 14:12, [EMAIL PROTECTED] wrote: According to the docs, this should work in versions past mySQL 4, and I seem to be running a version rather later than that mysql Ver 11.18 Distrib 3.23.52, for pc-linux (i686) What am I doing wrong here? I have two valid SELECT statements; the field sizes and types are the same (indeed, empssn is the same field). This SHOULD provide me with what I'm looking for, but... The command: mysql --version shows the version of the mysql client program. You are running version 11.18 of the mysql client program from the MySQL 3.23.52 distribution, or what is commonly called MySQL 3. The syntax allowed in queries is dependent on the version of the mysqld (and other) binaries that the SERVER users. I suspect you are running mysqld from the MySQL 3.23.52 distribution also. This a version that is earlier than MySQL 4, and doesn't support UNIONs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Local variables
Carlos Savoretti wrote: Hello all: I need to make certain operations like this: select tbl_no as 'Number' , @sal1 := tbl_db - tbl_hb + @sal1 as 'Total' I'm using the C API; so I make it through mysql_query() Problem is than having `tbl_db' and 'tbl_hb' with certain values no operation is carried out, and `Total' is NULL. And what values are these that cause pproblems? Any field with a NULL value will result in a NULL total. What am I doing bad ? Please, help me Thanks all Carlos Savoretti [EMAIL PROTECTED] ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where oh where, help!
Stuart Felenstein wrote: I have this query (below) that on it's own , directly into the database seems to return all the records just fine. Apparently though feeding it into a php loop, the lack of where is causing great distress(at least to the author) I'm wondering where to put it though. Before each join ? And then what does the from imply. The manual gives you the syntax of a select. The WHERE goes after the joins, and before ORDER BY, HAVING, and LIMIT. Hope I make sense here, delirium is setting in. :) Select VendorJobs.JobID, VendorJobs.PostStart, VendorJobs.JobTitle, VendorJobs.AreaCode, VendorJobs.PayRate, VendorJobs.Contact, VendorJobs.Conmail, VendorSignUp.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, staTaxTerm.TaxTerm, staTravelReq.TravelReq From (VendorJobs VendorJobs INNER JOIN VendorSignUp VendorSignUp ON (VendorJobs.VendorID = VendorSignUp.VendorID)) INNER JOIN StaIndTypes StaIndTypes ON (VendorJobs.Industry = StaIndTypes.CareerIDs)) LEFT OUTER JOIN StaUSCities StaUSCities ON (VendorJobs.LocationCity = StaUSCities.CityID)) INNER JOIN USStates USStates ON (VendorJobs.LocationState = USStates.StateID)) INNER JOIN staTaxTerm staTaxTerm ON (VendorJobs.TaxTerm = staTaxTerm.TaxTermID)) INNER JOIN staTravelReq staTravelReq ON (VendorJobs.TravelReq = staTravelReq.TravelReqID) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: date question..
the earlier suggestion didn't work, as it only returned a single value... here's the entire issue i have... i have a select: mysql select - u1.urltype as type, - p1.name as fname, - p1.fileID as ID, - h1.itemID as hitem, - h1.process as process, - h1.status as status, - h1.tblType as tbl, - h1.date as date - from university_urlTBL as u1 - left join parsefileTBL as p1 - on p1.university_urlID = u1.ID - left join historyTBL as h1 - on h1.itemID = p1.fileID - where h1.tblType = '3' - and (u1.urltype = '3' or urltype = '4') - and u1.universityID='40'; which returns: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 0 |3 | 2004-09-11 12:23:15 | |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ 3 rows in set (0.11 sec) i'd like to figure out how to get the query to return the row for the tbl/type based on the latest/earliest date... so for the latest date, the query would return: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ i was thinking that a subselect might work, but couldn't get it to work... thanks for any pointers/comments regards, -bruce -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 11:12 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: date question.. bruce wrote: i have a tbl with the following... foo date1 foo2 date2 etc... how can i do a select to get the last or 1st row based on the (latest/earliest) date? SELECT * FROM tbl order by datecolumn DESC LIMIT 1; SELECT * FROM tbl order by datecolumn ASC LIMIT 1; can't seem to get it to work.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Analyze Dreaweaver's PHP/MySQL code
I'm trying to inform myself about exactly what Dreamweaver's PHP/MySQL code
is doing when creating a recordset and repeat region. Please help if you
can.
DW MX generates the following to create a simple recordset...
Line 1) require_once('Connections/TBA.php');
Line 2) mysql_select_db($database_TBA, $TBA);
Line 3) $query_RS_CourseEnrollment = SELECT * FROM tba_courseenrollment;
Line 4) $RS_CourseEnrollment = mysql_query($query_RS_CourseEnrollment,
$TBA) or die(mysql_error());
Line 5) $row_RS_CourseEnrollment = mysql_fetch_assoc($RS_CourseEnrollment);
Line 6) $totalRows_RS_CourseEnrollment =
mysql_num_rows($RS_CourseEnrollment);
I understand (or think I understand) the following...
Line 1 grabs the information from an external file to connect to the
database.
Line 2 creates the connection to the database using the data obtained in
line 1.
Line 3 creates the actual query and saves it to a variable.
Line 4 executes the query and saves the result to a variable (I think).
Line 5 grabs the currently pointed to record (usually the first) and
assigns its contents to a PHP Array.
Line 6 stores the number of records in the recordset to a variable.
In a repeat region DM MX writes...
Line 1) ?php do { ?
Lines 2 - ?) put something on the page
Line Last) ?php } while ($row_RS_CourseEnrollment =
mysql_fetch_assoc($RS_CourseEnrollment)); ?
I understand (or think I understand) the following...
Line 1 starts the loop.
Lines 2 through ? draws stuff on the page.
Line Last restarts the loop by grabbing the next record in the recordset.
Here are my questions...
1) Do I understand what's going on?
2) When I ECHO the variable created in Line 4 of the connection I get
Resource id #4. Why don't I get Array or something I can read?
3) Because the last line of the repeat region includes
mysql_fetch_assoc($variableDefiningTheQuery) does this mean that the
database is queried every time through the loop?
4) I can't find mysql_fetch_assoc() in the MySQL documentation. What's
this command do?
I know this is a long post and a lot of quesitons. Just trying to
understand what's going on. And, maybe your answers will help others on the
board.
Thanx,
--
Robb Kerr
Digital IGUANA
Helping Digital Artists Achieve their Dreams
http://www.digitaliguana.com
http://www.cancerreallysucks.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date question..
The manual describes 3 solutions to this problem http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. Michael bruce wrote: the earlier suggestion didn't work, as it only returned a single value... here's the entire issue i have... i have a select: mysql select - u1.urltype as type, - p1.name as fname, - p1.fileID as ID, - h1.itemID as hitem, - h1.process as process, - h1.status as status, - h1.tblType as tbl, - h1.date as date - from university_urlTBL as u1 - left join parsefileTBL as p1 - on p1.university_urlID = u1.ID - left join historyTBL as h1 - on h1.itemID = p1.fileID - where h1.tblType = '3' - and (u1.urltype = '3' or urltype = '4') - and u1.universityID='40'; which returns: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 0 |3 | 2004-09-11 12:23:15 | |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ 3 rows in set (0.11 sec) i'd like to figure out how to get the query to return the row for the tbl/type based on the latest/earliest date... so for the latest date, the query would return: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ i was thinking that a subselect might work, but couldn't get it to work... thanks for any pointers/comments regards, -bruce -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 11:12 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: date question.. bruce wrote: i have a tbl with the following... foo date1 foo2 date2 etc... how can i do a select to get the last or 1st row based on the (latest/earliest) date? SELECT * FROM tbl order by datecolumn DESC LIMIT 1; SELECT * FROM tbl order by datecolumn ASC LIMIT 1; can't seem to get it to work.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Keyword Searches
I've recently been doing a lot of work on keyword searches. The methodology I've adopted consists of generating a list of keywords based on the product information (while removing noise words), and creating a table with one keyword per line, relating to the product ID (many rows per product). The keyword search runs off of the keyword table, and no keyword specific information is stored in the product table. In my experience, there are a couple of downsides to this way of handling it. For example, complex keyword queries (in my case, 4 or 5 specific words) start taking a long time to run, around 5-10 seconds on a 15,000,000 row keyword table (since I have to join the table with itself). I also am not doing any kind of substring searching, it only matches whole words. I haven't even begun to look at doing a fulltext search on a description field, I've assumed that it would be a more expensive operation. Good luck, and I'd like to hear if you run into any good ideas. On Fri, 10 Sep 2004 14:26:22 -0500, Robb Kerr [EMAIL PROTECTED] wrote: Anyone created one? I've got a project that consists of a product catalog which the client wants to be keyword searchable. I have thought through several possible construction scenarios... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Keyword Searches
On Fri, 10 Sep 2004 14:24:23 -0700, Andrew Kreps wrote: I've recently been doing a lot of work on keyword searches. The methodology I've adopted consists of generating a list of keywords based on the product information (while removing noise words), and creating a table with one keyword per line, relating to the product ID (many rows per product). The keyword search runs off of the keyword table, and no keyword specific information is stored in the product table. In my experience, there are a couple of downsides to this way of handling it. For example, complex keyword queries (in my case, 4 or 5 specific words) start taking a long time to run, around 5-10 seconds on a 15,000,000 row keyword table (since I have to join the table with itself). I also am not doing any kind of substring searching, it only matches whole words. I haven't even begun to look at doing a fulltext search on a description field, I've assumed that it would be a more expensive operation. Good luck, and I'd like to hear if you run into any good ideas. On Fri, 10 Sep 2004 14:26:22 -0500, Robb Kerr [EMAIL PROTECTED] wrote: Anyone created one? I've got a project that consists of a product catalog which the client wants to be keyword searchable. I have thought through several possible construction scenarios... I'm assuming that you've already got product descriptions that you can parse and pull out keywords. This is a brand new project with brand new products (actually clipart and photos). I don't even have a starting place for generating a keyword list. I'm gonna have to have some type of form for uploading images and choosing the associated keywords. And, then of course, if the keywords are in a separate table, although easy to do, I've still got to create a form for adding possible keywords. I'll let you know if someone responds with a better structure. Thanx, -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Local variables
Carlos Savoretti wrote: Hello all: I need to make certain operations like this: select tbl_no as 'Number' , @sal1 := tbl_db - tbl_hb + @sal1 as 'Total' I'm using the C API; so I make it through mysql_query() Problem is than having `tbl_db' and 'tbl_hb' with certain values no operation is carried out, and `Total' is NULL. What am I doing bad ? Please, help me Thanks all Carlos Savoretti [EMAIL PROTECTED] @sal1 is NULL until you assign it a value. Your assignment is self-referential. @sal1 := tbl_db - tbl_hb + @sal1 On the first pass, then, that's @sal1 := tbl_db - tbl_hb + NULL Anything plus NULL is NULL, so you get @sal1 := tbl_db - tbl_hb + NULL on every subsequent pass, as well. You need to start with SET @sal1 = 0; first. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: date question..
sorry... still couldn't see how to apply that particular page to my question... still trying to get an answer/solution.. -bruce -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 2:18 PM To: [EMAIL PROTECTED] Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: date question.. The manual describes 3 solutions to this problem http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. Michael bruce wrote: the earlier suggestion didn't work, as it only returned a single value... here's the entire issue i have... i have a select: mysql select - u1.urltype as type, - p1.name as fname, - p1.fileID as ID, - h1.itemID as hitem, - h1.process as process, - h1.status as status, - h1.tblType as tbl, - h1.date as date - from university_urlTBL as u1 - left join parsefileTBL as p1 - on p1.university_urlID = u1.ID - left join historyTBL as h1 - on h1.itemID = p1.fileID - where h1.tblType = '3' - and (u1.urltype = '3' or urltype = '4') - and u1.universityID='40'; which returns: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 0 |3 | 2004-09-11 12:23:15 | |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ 3 rows in set (0.11 sec) i'd like to figure out how to get the query to return the row for the tbl/type based on the latest/earliest date... so for the latest date, the query would return: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ i was thinking that a subselect might work, but couldn't get it to work... thanks for any pointers/comments regards, -bruce -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 11:12 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: date question.. bruce wrote: i have a tbl with the following... foo date1 foo2 date2 etc... how can i do a select to get the last or 1st row based on the (latest/earliest) date? SELECT * FROM tbl order by datecolumn DESC LIMIT 1; SELECT * FROM tbl order by datecolumn ASC LIMIT 1; can't seem to get it to work.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: The UNION makes us strong^H^H^H^Hcrazy
Why not just do it like this. You will need to compare the results yourself to make sure. But the numbers should be the same. And you don't have to do a union. select empssn,paycode_1,payrate_1 from paympe where paycode_1 != '000' or paycode_2 != '000' That might need to be an and and not an or. Donny -Original Message- From: Andy Bakun [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 2:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: The UNION makes us strong^H^H^H^Hcrazy On Fri, 2004-09-10 at 14:12, [EMAIL PROTECTED] wrote: According to the docs, this should work in versions past mySQL 4, and I seem to be running a version rather later than that mysql Ver 11.18 Distrib 3.23.52, for pc-linux (i686) What am I doing wrong here? I have two valid SELECT statements; the field sizes and types are the same (indeed, empssn is the same field). This SHOULD provide me with what I'm looking for, but... The command: mysql --version shows the version of the mysql client program. You are running version 11.18 of the mysql client program from the MySQL 3.23.52 distribution, or what is commonly called MySQL 3. The syntax allowed in queries is dependent on the version of the mysqld (and other) binaries that the SERVER users. I suspect you are running mysqld from the MySQL 3.23.52 distribution also. This a version that is earlier than MySQL 4, and doesn't support UNIONs. -- 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: Compilation Error
--- Jim Grill [EMAIL PROTECTED] wrote: Hi, I am trying to compile MySQL. I know that it is possible to install this as a binary, that is not my goal. My environment consists of the following: If there are other tools that I need to specify please let me know. gcc-3.2-7 libgcc-3.2-7 gcc-c++-3.2-7 I run configure as specified at the MySQL site: From config.log: ./configure --prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static When I run make I receive the following output: libmysql.c:1850: warning: passing arg 5 of `gethostbyname_r' from incompatibleointer type libmysql.c:1850: too few arguments to function `gethostbyname_r' libmysql.c:1850: warning: assignment makes pointer from integer without a castmake[2]: *** [libmysql.lo] Error 1 I have googled this error message and the result include the following recommendation from MySQL: This is known problem with RedHat. In order to build MySQL you need to have g++ installed from separate RPM In RedHat 8.0 RPM in quesiton is gcc-c++-3.2-7.i386.rpm It appears that I have installed gcc-c++-3.2-7.i386.rpm as you can see above from my build environment obtained by: rpm -qa | grep gcc Any help or pointers would be greatly appreciated. Andrew Looks like you have the right stuff. Have you tried the following: (run make distclean before configuring again to be sure you get a clean start.) CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mcpu=pentiumpro \ -felide-constructors -fno-exceptions -fno-rtti \ ./configure --your options here change -mcpu=pentiumpro to suite your system (i386, i486, i586, i686, pentium, pentiumpro, k6, or athlon). Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] OK: I ran make distclean I then ran CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mcpu=pentiumpro \ -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static and got the following error: checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets $(MAKE)... (cached) yes checking for gawk... (cached) gawk checking for gcc... gcc checking for C compiler default output... configure: error: C compiler cannot create executables See `config.log' for more details. When I look at config.log I don't see any hints that I understand. Here is the part of the file that seems to offer clues to the initiated. gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) configure:2590: $? = 0 configure:2592: gcc -V /dev/null 5 gcc: argument to `-V' is missing configure:2595: $? = 1 configure:2619: checking for C compiler default output configure:2622: gcc O3 -mcpu=i686conftest.c 5 gcc: O3: No such file or directory configure:2625: $? = 1 configure: failed program was: | #line 2598 configure Again any help or hints would be greatly appreciated. Andrew __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Local Master replication issue
Hello All, I am seeing a small problem in Ring replication where one slave is acting as Local Master. See below M - Super Master S1/LM1 - Slave of super Master and act as Local Master for S2 S2 - slave of LM1 I ran one Insert on M, it showd up on S1/LM1 but it did not showed up in S2. What I can see that S1 I/O thread bring that transaction in relay log on S1 and apply it but it did not consider as write on s1/LM1 resulting that binlog do not have record of it. Since binlog do not hast it, so it did not replicate to S2. Am I missing anything here? As per our requirement that transaction should also showed up in S2 too? It's obivious to think that make S2 as direct slave of M but it is not accepted because things r bit complicated here. Is there any specific configuration thing to acheive a slave as local master for another slave. Any help will be highly appreciable. Regards,
ORDER BY problem with JOINs
I've got a SELECT statement that is returning the data I want, but not in the right order (and I don't know why...). Let's say there are two tables, People and History. Some records in People have corresponding records in History, but not all--so I need a LEFT JOIN TO connect history.people_id to people.id. So far, so good. But I want to order the list according to the timestamp column in history (history.time_sec), and this does not happen: Records are returned, but not in the right order. Here's my query: SELECT * FROM people LEFT JOIN history ON people.id = history.people_id GROUP BY people.id ORDER BY history.time_sec DESC It seems I can sort correctly on a field in people, but not on a field in historyis that because it is a left-joined table? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drop database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In mysql 4.0.18 I have a database with innodb tables and foreign keys. I need to drop / recreate the database for testing purposes. When I enter the command drop database xxx I get an error message indicating a foreign key violation. As I am dropping the entire database, this makes no sense. Is there a way around this, or is it simply a bug in the mysql logic? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBQjXFjeziQOokQnARAjpcAKCnWPAaYgU3uZbFci1a/yIecNXHFgCgmnWu sA+NH+4v2SY70PayCV1GksU= =1ptQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY problem with JOINs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 10 September 2004 18:00, Ren Fournier wrote: I've got a SELECT statement that is returning the data I want, but not in the right order (and I don't know why...). Let's say there are two tables, People and History. Some records in People have corresponding records in History, but not all--so I need a LEFT JOIN TO connect history.people_id to people.id. So far, so good. But I want to order the list according to the timestamp column in history (history.time_sec), and this does not happen: Records are returned, but not in the right order. Here's my query: SELECT * FROM people LEFT JOIN history ON people.id = history.people_id GROUP BY people.id ORDER BY history.time_sec DESC It seems I can sort correctly on a field in people, but not on a field in historyis that because it is a left-joined table? I think it's because you're trying to sort on missing data. How can it sort on a field that isn't always there? Remember NULL doesn't compare as less than, equal *OR* greater than another value. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBQjZljeziQOokQnARAidWAJ9zr+/x6EWJ8xTYCsmbvQVy5gMOIACgku3v KGWramLsfIBe7zwm8csGvwM= =hRZV -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Analyze Dreaweaver's PHP/MySQL code
--- Robb Kerr [EMAIL PROTECTED] wrote:
I'm trying to inform myself about exactly what
Dreamweaver's PHP/MySQL code
is doing when creating a recordset and repeat
region. Please help if you
can.
DW MX generates the following to create a simple
recordset...
Line 1) require_once('Connections/TBA.php');
Line 2) mysql_select_db($database_TBA, $TBA);
Line 3) $query_RS_CourseEnrollment = SELECT * FROM
tba_courseenrollment;
Line 4) $RS_CourseEnrollment =
mysql_query($query_RS_CourseEnrollment,
$TBA) or die(mysql_error());
Line 5) $row_RS_CourseEnrollment =
mysql_fetch_assoc($RS_CourseEnrollment);
Line 6) $totalRows_RS_CourseEnrollment =
mysql_num_rows($RS_CourseEnrollment);
I understand (or think I understand) the
following...
Line 1 grabs the information from an external file
to connect to the
database.
Line 2 creates the connection to the database using
the data obtained in
line 1.
Line 3 creates the actual query and saves it to a
variable.
Line 4 executes the query and saves the result to a
variable (I think).
Line 5 grabs the currently pointed to record
(usually the first) and
assigns its contents to a PHP Array.
Line 6 stores the number of records in the recordset
to a variable.
In a repeat region DM MX writes...
Line 1) ?php do { ?
Lines 2 - ?) put something on the page
Line Last) ?php } while ($row_RS_CourseEnrollment =
mysql_fetch_assoc($RS_CourseEnrollment)); ?
I understand (or think I understand) the
following...
Line 1 starts the loop.
Lines 2 through ? draws stuff on the page.
Line Last restarts the loop by grabbing the next
record in the recordset.
Here are my questions...
1) Do I understand what's going on?
2) When I ECHO the variable created in Line 4 of the
connection I get
Resource id #4. Why don't I get Array or
something I can read?
3) Because the last line of the repeat region
includes
mysql_fetch_assoc($variableDefiningTheQuery) does
this mean that the
database is queried every time through the loop?
4) I can't find mysql_fetch_assoc() in the MySQL
documentation. What's
this command do?
I know this is a long post and a lot of quesitons.
Just trying to
understand what's going on. And, maybe your answers
will help others on the
board.
Thanx,
--
Robb Kerr
Digital IGUANA
Helping Digital Artists Achieve their Dreams
I think some people on this list would consider that
more of a PHP question than MySQL. You might get
better responses on one of the Dreamweaver lists; I've
asked similar questions there.
Unfortunately, I can't answer your question, because I
never did figure this stuff out. I'm still relatively
new to PHP and MySQL, and I found it all very
confusing. I finally decided to ditch Dreamweaver's
PHP functions and hand code everything. I think that
was a good choice.
I still don't have a good perspective on everything,
but I THINK Dreamweaver's PHP functions are unfamiliar
to many PHP coders who don't use Dreamweaver. So if
you want to use the same language, it's better to
learn to speak plain PHP.
But I could be mistaken. Get some feedback from other
groups, and if I'm wrong, please let me know!
__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group by issue...??
hi... if i do this... select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4'); i get a results table with ~5000 rows... if i add the 'group by' sql, select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4') group by tblType; i get a single line for each tblType... i was under the impression that i should get the same number of rows returned, but that they would be grouped together. can someone provide an explanation as to how this should work, and what's going on thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Local Master replication issue
--log-slave-updates did the job. Regards, -Original Message- From: Sanjeev Sagar [mailto:[EMAIL PROTECTED] Sent: Fri 9/10/2004 3:36 PM To: [EMAIL PROTECTED] Subject: Local Master replication issue Hello All, I am seeing a small problem in Ring replication where one slave is acting as Local Master. See below M - Super Master S1/LM1 - Slave of super Master and act as Local Master for S2 S2 - slave of LM1 I ran one Insert on M, it showd up on S1/LM1 but it did not showed up in S2. What I can see that S1 I/O thread bring that transaction in relay log on S1 and apply it but it did not consider as write on s1/LM1 resulting that binlog do not have record of it. Since binlog do not hast it, so it did not replicate to S2. Am I missing anything here? As per our requirement that transaction should also showed up in S2 too? It's obivious to think that make S2 as direct slave of M but it is not accepted because things r bit complicated here. Is there any specific configuration thing to acheive a slave as local master for another slave. Any help will be highly appreciable. Regards,
Re: ORDER BY problem with JOINs
At 17:00 -0600 9/10/04, René Fournier wrote: I've got a SELECT statement that is returning the data I want, but not in the right order (and I don't know why...). Let's say there are two tables, People and History. Some records in People have corresponding records in History, but not all--so I need a LEFT JOIN TO connect history.people_id to people.id. So far, so good. But I want to order the list according to the timestamp column in history (history.time_sec), and this does not happen: Records are returned, but not in the right order. Here's my query: SELECT * FROM people LEFT JOIN history ON people.id = history.people_id GROUP BY people.id ORDER BY history.time_sec DESC It seems I can sort correctly on a field in people, but not on a field in history-is that because it is a left-joined table? We might be able to give you an answer if you show some results and indicate why you believe they are incorrect. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by issue...??
At 16:27 -0700 9/10/04, bruce wrote: hi... if i do this... select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4'); i get a results table with ~5000 rows... if i add the 'group by' sql, select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4') group by tblType; i get a single line for each tblType... i was under the impression that i should get the same number of rows returned, but that they would be grouped together. can someone provide an explanation as to how this should work, and what's going on If you use GROUP BY with selecting the value of any aggregate functions, you achieve the same effect as SELECT DISTINCT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by issue...??
paul forgive me for being a neophyte!!! but i have no idea how what you said helps me get to how i can actually get a grouping is there anyway to get a grouping... is there some other way to accomplish this..? am i totally lost! thanks.. ps. i'm really trying to figure out how to get the last record from a select/join without having to go through multiple queries/selects... here's the entire issue i have... i have a select: mysql select - u1.urltype as type, - p1.name as fname, - p1.fileID as ID, - h1.itemID as hitem, - h1.process as process, - h1.status as status, - h1.tblType as tbl, - h1.date as date - from university_urlTBL as u1 - left join parsefileTBL as p1 - on p1.university_urlID = u1.ID - left join historyTBL as h1 - on h1.itemID = p1.fileID - where h1.tblType = '3' - and (u1.urltype = '3' or urltype = '4') - and u1.universityID='40'; which returns: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 0 |3 | 2004-09-11 12:23:15 | |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ 3 rows in set (0.11 sec) i'd like to figure out how to get the query to return the row for the tbl/type based on the latest/earliest date... so for the latest date, the query would return: +--+---+--+---+-++--+--- --+ | type | fname | ID | hitem | process | status | tbl | date | +--+---+--+---+-++--+--- --+ |3 | | 159 | 159 | 1 | 1 |3 | 2004-09-11 12:25:15 | |4 | | 160 | 160 | 1 | 0 |3 | 2004-09-11 12:23:15 | +--+---+--+---+-++--+--- --+ i was thinking that a subselect might work, but couldn't get it to work... thanks for any pointers/comments regards, -bruce -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 4:36 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: group by issue...?? At 16:27 -0700 9/10/04, bruce wrote: hi... if i do this... select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4'); i get a results table with ~5000 rows... if i add the 'group by' sql, select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4') group by tblType; i get a single line for each tblType... i was under the impression that i should get the same number of rows returned, but that they would be grouped together. can someone provide an explanation as to how this should work, and what's going on If you use GROUP BY with selecting the value of any aggregate functions, you achieve the same effect as SELECT DISTINCT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, 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]
Copy a record
hi, a quick question. is there a way to duplicate a record (without knowing fields in advance) in sql alone? the only thing is know is that `id` is a primary key (autonumber) something of this sort: INSERT INTO the_table SELECT * FROM the_table WHERE id=the_number this fails due duplicate entry for the primary key. Is there way to select * minus the id field? thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by issue...??
At 16:51 -0700 9/10/04, bruce wrote: paul forgive me for being a neophyte!!! but i have no idea how what you said helps me get to how i can actually get a grouping is there anyway to get a grouping... is there some other way to accomplish this..? am i totally lost! If you just want to sort things, you can use ORDER BY rather than GROUP BY. thanks.. ps. i'm really trying to figure out how to get the last record from a select/join without having to go through multiple queries/selects... Sort in reverse and use LIMIT 1? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy a record
At 16:59 -0700 9/10/04, Dan Sashko wrote: hi, a quick question. is there a way to duplicate a record (without knowing fields in advance) in sql alone? the only thing is know is that `id` is a primary key (autonumber) something of this sort: INSERT INTO the_table SELECT * FROM the_table WHERE id=the_number this fails due duplicate entry for the primary key. Is there way to select * minus the id field? You can name all the columns except that one. But why do you have a primary key if you want to allow duplicate values? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by issue...??
At 16:58 -0700 9/10/04, bruce wrote: paul i do a select ID from hTBL group by type and i still only get a single row for each type, where i would expect to get the ~5000 rows, grouped around the 3 different types. Hmm. I see my previous answer said with and should have said without. If you use GROUP BY without selecting the value of any aggregate functions, you achieve the same effect as SELECT DISTINCT. what's going on thanks... -bruce -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 4:36 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: group by issue...?? At 16:27 -0700 9/10/04, bruce wrote: hi... if i do this... select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4'); i get a results table with ~5000 rows... if i add the 'group by' sql, select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4') group by tblType; i get a single line for each tblType... i was under the impression that i should get the same number of rows returned, but that they would be grouped together. can someone provide an explanation as to how this should work, and what's going on If you use GROUP BY with selecting the value of any aggregate functions, you achieve the same effect as SELECT DISTINCT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by issue...??
paul i do a select ID from hTBL group by type and i still only get a single row for each type, where i would expect to get the ~5000 rows, grouped around the 3 different types. what's going on thanks... -bruce -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 4:36 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: group by issue...?? At 16:27 -0700 9/10/04, bruce wrote: hi... if i do this... select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4'); i get a results table with ~5000 rows... if i add the 'group by' sql, select h1.itemID as hitem, h1.process as process, h1.status as status, h1.tblType as tbl, h1.date as date from historyTBL as h1 where (h1.tblType = '3' or h1.tblType = '4') group by tblType; i get a single line for each tblType... i was under the impression that i should get the same number of rows returned, but that they would be grouped together. can someone provide an explanation as to how this should work, and what's going on If you use GROUP BY with selecting the value of any aggregate functions, you achieve the same effect as SELECT DISTINCT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, 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: mysql_config missing - already read much docs
At 9:39 -0500 9/10/04, sean c peters wrote: I just upgraded Mysql to 4.1.4-0 on linux using the following: MySQL-server-4.1.4-0.i386.rpm MySQL-client-4.1.4-0.i386.rpm So when i went to upgrade my perl DBI DBD::mysql packages, it noticed that mysql_config was missing. My understanding is that mysql_config should be installed with the client libraries. Yes, but neither of those RPMs you list above contain the client libraries. The -client- RPM contains the client programs. You want to install the -devel- RPM to get libraries and mysql_config for programming support. I looked at the /usr/bin directory before and after installing the client libs, and mysql_config is not there. Here is a diff output of 'ls -l /usr/bin/my *' both before and after the client rpm was installed. -rwxr-xr-x1 root root 1696220 Aug 28 02:54 mysql -rwxr-xr-x1 root root 111478 Aug 28 02:54 mysqlaccess -rwxr-xr-x1 root root 1516376 Aug 28 02:54 mysqladmin -rwxr-xr-x1 root root 1550488 Aug 28 02:54 mysqlbinlog 6a11 -rwxr-xr-x1 root root 1512908 Aug 28 02:54 mysqlcheck 10a16,17 -rwxr-xr-x1 root root 1533448 Aug 28 02:54 mysqldump -rwxr-xr-x1 root root 4986 Aug 28 02:54 mysqldumpslow 11a19 -rwxr-xr-x1 root root 2653 Aug 28 02:54 mysql_find_rows 14a23 -rwxr-xr-x1 root root 1510200 Aug 28 02:54 mysqlimport 17a27,28 -rwxr-xr-x1 root root 1510316 Aug 28 02:54 mysqlshow -rwxr-xr-x1 root root13647 Aug 28 02:54 mysql_tableinfo 19a31 -rwxr-xr-x1 root root 680464 Aug 28 02:54 mysql_waitpid So the client rpm did install a bunch of things, but not the mysql_config executable. I'm rather confused by this. Perhaps i should go back to installing source distros. Anybody seen anything like this? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Analyze Dreaweaver's PHP/MySQL code
You have a pretty good handle on what's happening there, I hope I can add some clarity. The Resource Id you're trying to echo is a pointer to a MySQL result set, you aren't actually working with the data yet. That's where the mysql_fetch functions come in. The mysql_fetch_assoc function fetches the next line of the result set and stores it in an associative array, so that you can reference your results by their database field name, i.e. $row_RS_CourseEnrollment['Id_field']. Otherwise, using a similar function like mysql_fetch_row, you'd end up with $row_RS_CourseEnrollment[0] for the first field, [1] for the second, and so on. If you ever change your database and you're using select *, this can get messy. Additionally, the mysql_fetch functions are simply returning a portion of the result of your previously executed query, so the database isn't re-queried every time you loop through it. Hope this helps! On Fri, 10 Sep 2004 16:12:06 -0500, Robb Kerr [EMAIL PROTECTED] wrote: Here are my questions... 1) Do I understand what's going on? 2) When I ECHO the variable created in Line 4 of the connection I get Resource id #4. Why don't I get Array or something I can read? 3) Because the last line of the repeat region includes mysql_fetch_assoc($variableDefiningTheQuery) does this mean that the database is queried every time through the loop? 4) I can't find mysql_fetch_assoc() in the MySQL documentation. What's this command do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange group/max date question...
hi... i have the following select... select itemID, process, status, tblType, max(date) from historyTBL where (tblType = '3' or tblType = '2') group by tblType; it seems to work, in that it gives me the rows for the two types that i'm grouping by. the problem is that i want the row that contains the max date. the query is only returning the max date, with the other elements in the row coming from who knows where in the table. any idea on how i can achieve the complete row containing the max date??? i've looked through google/mysql with no luck thanks. -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange group/max date question...
Have you tried this: select ..., max(date) as mdate from ... where ... group by ... order by mdate desc ? I haven't tested it, but it should order the results by max date descending, giving you the greatest date first. On Fri, 10 Sep 2004 18:16:00 -0700, bruce [EMAIL PROTECTED] wrote: hi... i have the following select... select itemID, process, status, tblType, max(date) from historyTBL where (tblType = '3' or tblType = '2') group by tblType; it seems to work, in that it gives me the rows for the two types that i'm grouping by. the problem is that i want the row that contains the max date. the query is only returning the max date, with the other elements in the row coming from who knows where in the table. any idea on how i can achieve the complete row containing the max date??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to get Access DB structures into MySQL ??
What is the best way to take the structure of multiple tables in Access and get them re-created in MySQL without doing that all by hand? Is there a way to do the equivalent of a Show Create Table in Access, that I could then use in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary
Re: strange group/max date question...
bruce wrote: hi... i have the following select... SELECT itemID, process, status, tblType, MAX(date) FROM historyTBL WHERE (tblType = '3' or tblType = '2') GROUP BY tblType; it seems to work, in that it gives me the rows for the two types that i'm grouping by. the problem is that i want the row that contains the max date. the query is only returning the max date, with the other elements in the row coming from who knows where in the table. any idea on how i can achieve the complete row containing the max date??? i've looked through google/mysql with no luck thanks. -bruce This is a frequently asked question. It seems natural to expect that this query should return the row with the maximum date column value, but that's not how GROUP BY works. What if 2 rows both have the maximum value of date? Which should be returned? Now, consider the following query: SELECT tblType, MAX(date), MIN(date), AVG(date) FROM historyTBL GROUP BY tblType; Which row should be returned now? You see? The aggregate functions return descriptive statistics about the data in each group, not rows from the table. Many systems would not even allow a query such as yours, in which columns (rather than aggregate functions) which are not grouped are selected. MySQL allows this as a convenience to save typing, but you are warned http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html not to include columns whose values are not unique per group. If you do, you get essentially random data. (Actually, I think in the interest of speed you get the values in the first row found per group). So, how do you solve the problem? Essentially, you must look at the table twice. First you get the max value for each group, then you look again to find the rows which have those values. You can achieve that with 2 queries and a temporary table, or with one query with a subquery if you have mysql 4.1. There is also a trick where you append the extra columns to the date, find the maximum value of that, and break the maximum back into pieces -- the MAX-CONCAT trick. I'm not sure why you started a new thread on this, but all 3 methods are explained in the manual page I referenced in my earlier reply http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. For example, LOCK TABLES historyTBL READ; CREATE TEMPORARY TABLE maxdates SELECT tblType, MAX(date) mdate FROM historyTBL GROUP BY tblType; SELECT h.itemID, h.process, h.status, h.tblType, h.date AS Max_Date FROM historyTBL h, maxdates m WHERE h.tblType = m.tblType AND h.date = m.mdate; UNLOCK TABLES; DROP TABLE maxdates; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
