Re: Certification Test Questions
* Bob Ramsey [EMAIL PROTECTED] [2004-01-18 22:54 +0100]: So how reflective of the real test is the sample test at mysql.com? They've got sample questions for the core certification only. Their level looks similar, but the real exam offers answers like none of the above and all of the above, which makes things much harder when you are not sure or running out of time. -- Johannes Franken Professional unix/network development mailto:[EMAIL PROTECTED] http://www.jfranken.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libgcc_so.1 open failure problem
Mysql doesn't run correctly.Because libgcc_so.1 can not be found.I restored the library,but i had the same problem.Why does this problem occur?Thanks for your helps. _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Uninstalling
Hello List! I forgot that OSX Server had mysql installed already. So in installed another version on top of it. How in the user/local/ folder I have two versions. And when I try to connect to which ever oneis running, is says I cannot connect to the mysql.sock socket. So I am thinking I need to start over. What is the best way to uninstall both versions and start over? Should I just trash the folders that contain the programs and then start from scratch? Where can I look for answers? Thanks for your help. Mark Weiss Vancouver, WA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Euro Symbol ()
Hassan Shaikh wrote: Hi, I've inserted the following currecy sumbols in my table (structure mentioned below). All symbols are displayed properly when I do select, expect for the Euro symbol, which just shows a question mark (?). Any idea why is it behaving like that? I am using MySQL 4.0.17 on Windows XP Pro. My production server is Linux based. The euro symbol displayed correctly in your e-mail and in the list of mail items in my inbox (I'm using Mozilla/W2K) so this must, almost certainly, be a problem with the locale of your XP Pro set up - most probably you're using a font that doesn't include this symbol, you may also have set up the system with unusual national settings - try telling it you're in the UK or Germany or Italy. Euro Yen Pound Sterling $Dollar +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | CYRANK | decimal(3,0) unsigned zerofill | | | 999 | | | CYSYMBOL | varchar(5) | YES | | | | | CYNAME | varchar(50)| | | | | | CYCODE | char(3)| | PRI | | | +--++--+-+-+---+ Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification Test Questions
On Sun, 2004-01-18 at 20:51, Johannes Franken wrote: * Marc Dver [EMAIL PROTECTED] [2004-01-18 18:30 +0100]: 1. What is the format of the test questions? I.e., are they multiple choice, free answer, essay, etc.? They are multiple-choice (but very tricky) and fill-in-the-gaps. For example: Assume a column in a MyISAM table has type VARCHAR, BLOB, or TEXT. Which of the following specifies this table type correctly to save space? Mark all answers that are wrong: [ ] ROW_FORMAT=Static [ ] ROW_FORMAT=default [ ] ROW_FORMAT=dynamic [ ] ROW_FORMAT=compressed [ ] all of the above [ ] none of the above I think I need to clarify a few details regarding this: First of all, we've done all that we possibly could do to remove double negatives in the exam questions. We very rarely ask which of the following are NOT true. Only when the question or answer texts would become much more complicated without this structure, have we submitted to it. And when we do so, we make it very clear what we are asking. There are indeed All/None of the above answers for a few question items. However, because of the way the exams are structured, I can promise you that if you were to go to the exam today, you would only see one (if any) of this type of questions during the entire exam. Now, I'm not saying all this to put Johannes or his observations down -- going to an exam *is* a stressful experience and people do come out with (amazingly) different accounts of what they thought was going on during the exam. During internal testing of the exams, I had MySQL employees be subjected to the exact same set of questions. Afterward, they gave me completely different accounts of their experience: some felt that more than half the questions had been on import/export (it was in fact around 5%); others were saying that they thought there were too many fill-in the blanks questions (on that particular test, they were subjected to *two* such questions during the exam). Are they theory based or is it fact based, i.e., memorize the books to pass? For the CORE certification, it's balanced. In many cases they show you some tables's contents and you have pick either the statements which do some job, or the result if you rune some statement. For this type of questions, memorizing the books is just not enough. You need to *understand* the facts. For the PROFESSIONAL certification, it's different. Here you don't need SQL, but you should memorize every aspect of performance- and security-tuning. ...and again, you need to *understand* how things work. Mere fact memorization won't see you through (which is not contrary to Johannes' statement; I just think it's important to highlight it). I have heard from some candidates that they regard the Core exam as being more of a memorization exam than Professional. Which only goes to show, once again, that perceptions differ quite a bit as people come out of the testing room. Best regards, / Carsten -- Carsten H. Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To Re-write complex query with JOINS
Hello friends, I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDate othertableidvalue - - 1 2004-01-10 1 10 2 2004-01-20 1 20 3 2004-01-20 2 70 4 2004-01-10 2 80 Now I want to use a single SQL query to find a result where there exist one record for each unique othertableid where the record selected for the othertableid should be the recent one with regard to EffectiveDate. That is from the above records, I want to select Records with RecordId = 2 and 3 because they are the recent one for othertableid = 1 and 2 respectively. Please be sure that I want to retrive all fields including RecordId. The result should not depend on any other fields but EffectiveDate only. I am using MySQL 4.0.12 and it does not support SUBQUERIES which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any SUBQUERY SQL statement can be written with proper JOINS. Can you help me. Vijay. __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory leaks using MySQL C Api
try this, i found this when solving similar problem.There is another method
to make and free connection, here is just one change.
The trick is that you dont call mysql_close( ) on mysql_connection , but
on return of mysql_real_connect();
Try it, i think that this solve your is problem.
static MYSQL myMYSQL ;
static MYSQL *myData ;
mysql_init(myMYSQL);
if ( myData=mysql_real_connect( myMYSQL, localhost, puberusr,
puber1234, pubernew, MYSQL_PORT,
NULL, 0 ) )
{
if ( mysql_select_db( myData, pubernew ) 0 ) {
DisplayException(mysql_error(myMYSQL));
mysql_close( myData ) ;
}
}
else {
DisplayException(mysql_error(myMYSQL));
mysql_close( myData ) ;
}
- Original Message -
From: John McCaskey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, January 18, 2004 8:31 AM
Subject: Re: Memory leaks using MySQL C Api
Agreed, I am not calling mysql_store_result(). I attempted to add
my_free() but the function does not seem to exist, it is also not listed
in the API docs for the c api. As such it still seems that there should
be no leak, but yet I do get one. Thanks for the idea anyway Chris,
maybe you can clarify for us once more.
John McCaskey
On Sat, 2004-01-17 at 12:44, Aftab Jahan Subedar wrote:
Hey wait a minute. Where did you get the my_free(), may be you are
trying to say mysql_free(), but then that is used only if result set is
used/called.
But the code does not show any result set call. ie. mysql_use_result()
or mysql_store_result().
So, the question now, how come there is a leak here. I dont see any,
does anyone see any?
Chris Nolan wrote:
Hi!
You're looking for the function my_free(). Enjoy!
Regards,
Chris
John McCaskey wrote:
I have the following code:
//try the mysql connection
mysql_init(mysql_connection);
if(!mysql_real_connect(mysql_connection, db_host, db_user,
db_pass,
db_db, 0, NULL, 0)) {
flockfile(stderr);
fprintf(stderr, %s: Failed to connect to database:
Error:
%s\n, timestamp, mysql_error(mysql_connection));
funlockfile(stderr);
mysql_close(mysql_connection);
return(2);
}
mysql_close(mysql_connection);
This code is creating a memory leak. Am I missing some cleanup
calls?
I'm
under the impression all I should need to do is call mysql_close to
clean up
the connection? I'm testing this using mtrace, if I place a return
directly
above the code segment it reports no leaks, if I place it direcly
below the
fragment there are several variables reported as not being freed.
Any
ideas?
John A. McCaskey
--
Aftab Jahan Subedar
CEO/Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.SubedarTechnologies.com
http://www.DhakaStockExchangeGame.com/
http://www.CEOBangladesh.com/
http://www.NYSEGame.com
tel://+88027519050
EMail://[EMAIL PROTECTED] - Directly to my notebook
--
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 server has gone away
Hello mysql, the message MySQL server has gone away appears during upload to db mediumblob file of size 2M i tried to set max_allowed_packet=16M in my.cnf (and it shows me 16M in mysqladmin variables), but error still appears any ideas? used mysql4.1 (mysql3.23.58) on FreeBSD -- Best regards, goblin mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
passing socket info to mysql_real_connect()
Hi all, Herewith I want to confirm that the info passed to the Socket parameter of mysql_real_connect() is correct or not? we have C API function to handle MySQL database using C programs. MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, --- this Argument. unsigned long client_flag) I have used C API from W2K machine to the Database in Linux server MySQL DB version is 4.0.17-max. I can easily pass NULL to this argument, when I works with C it works fine... :) But I am in a position to works under COBOL to connect MySQL to COBOL. I have used the external subroutine features of COBOL to call the C API routines from COBOL. Under this situation I cann't able to pass NULL from COBOL. Shall I pass mysql.sock instead of NULL, I have collected this data mysql.sock from the variable list of MySQL. we can see this data for the variable socket by issuing SHOW VARIABLES command in the MySQL Prompt. any suggestions are welcome... thanks Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table info
Mike Mapsnac [EMAIL PROTECTED] wrote: Is it possible to find out when the table was created? How to get information about the table? SHOW TABLE STATUS command provides info about table: http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.1 not an update or upgrade of 4.1.0
Hi group, I have discovered this morning a strange behaviour of MySQL 4.1.1 When the SUB-SELECT contains more than one field, it does not work (this works very well with 4.1.0 ) For example: SELECT field1 FROM Table1 WHERE (field2,field3) IN (SELECT field2,field3 FROM Table2) The above query works as expected with mysqld-nt.exe 4.1.0 (on WinXP) but it does not work with mysqld-nt.exe 4.1.1 (on WinXP). This problem causes unexpected results as the recordset comes empty (on 4.1.1) while it comes with rows with 4.1.0 and no error is returned!!! Maybe it is a known bug! Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
Sorting by more than 1 column
I didn't know if this was possible and haven't tried yet. My boss wants me to sort results by 3 columns (city, county, price.) He would like city and county in alphabetical order a-z and have price from highest to lowest. I told him I didn't think it was possible to sort two different fields one acending and one descending. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table info
I think that main disadvantage of this command is thah it works for the database but not for specific table. So if a database has 200 tables, find result for specific table is not an easy task. From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: table info Date: Mon, 19 Jan 2004 14:10:37 +0200 Mike Mapsnac [EMAIL PROTECTED] wrote: Is it possible to find out when the table was created? How to get information about the table? SHOW TABLE STATUS command provides info about table: http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Rethink your business approach for the new year with the helpful tips here. http://special.msn.com/bcentral/prep04.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting by more than 1 column
ORDER BY city, price DESC - Original Message - From: Ed Curtis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 2:00 PM Subject: Sorting by more than 1 column I didn't know if this was possible and haven't tried yet. My boss wants me to sort results by 3 columns (city, county, price.) He would like city and county in alphabetical order a-z and have price from highest to lowest. I told him I didn't think it was possible to sort two different fields one acending and one descending. Thanks, Ed -- 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: Sorting by more than 1 column
ORDER BY city, price DESC - Original Message - From: Ed Curtis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 2:00 PM Subject: Sorting by more than 1 column I didn't know if this was possible and haven't tried yet. My boss wants me to sort results by 3 columns (city, county, price.) He would like city and county in alphabetical order a-z and have price from highest to lowest. I told him I didn't think it was possible to sort two different fields one acending and one descending. Thanks, Ed -- 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: Sorting by more than 1 column
Hi, I told him I didn't think it was possible to sort two different fields one acending and one descending. But, of course, it is possible :) http://www.mysql.com/doc/en/SELECT.html SELECT ... ORDER BY city, county, price DESC Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting by more than 1 column
I didn't know if this was possible and haven't tried yet. My boss wants me to sort results by 3 columns (city, county, price.) He would like city and county in alphabetical order a-z and have price from highest to lowest. I told him I didn't think it was possible to sort two different fields one acending and one descending. All info you need is here: http://www.mysql.com/doc/en/Sorting_rows.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table info
I think that main disadvantage of this command is thah it works for the database but not for specific table. So if a database has 200 tables, find result for specific table is not an easy task. Easy: SHOW TABLE STATUS LIKE 'table_name' Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with python connect.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm running the SuSE 8.2 distro with MySQL 4.1.0-0 and python-mysql-0.9.1-430. On Saturday, I had to do a reinstall of everything (long story). The software listed above is the same as it was before the reinstall. The only difference is that this time I installed 4.1 directly, before I had done an upgrade from 4.0 to 4.1. I try to connect to MySQL as follows: import MySQLdb db = MySQLdb.connect(user=michael, passwd=*, db=backups) When I do this, I get the following exception: _mysql_exceptions.OperationalError: (1249, 'Client does not support authentication protocol requested by server. Consider upgrading MySQL client') Tables are INNODB, but we're not to that point yet. The MySQL client is the one with the 4.1 beta release - and worked before the reinstall. Can anyone offer me any help?? tia - ---Michael -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAC9q/jeziQOokQnARAkufAJ9UA6XbYpY+dFF8+z8dhaRu89vbJACdG0Qa nz4/d30F6+bINSLQvGdf804= =/fMz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade or Reinstall Question
I have Mysql 4.0.13 installed. I need to know if upgrading is an option or if installing the latest binary is a better option. To be more candid, I have finished the install with the following config. Not so much content yet...Just some pictures that can be uploaded again. Windows 2000 Advanced Server Apache 2.0.47 PHP 4.3.4 Mysql 4.0.13 Geeklog 1.3.8sr3 The Geeklog part is my weblog that I used to design the site. However the problem after I finished the installation was that people were not getting images loaded on their browser from the internet. One person in particular said they got a POSIX ERROR 54. and I really do not know what this error meant and neither did the other person. So I have decided to start the whole thing from scratch and take it one step at a time, but in doing so, install the latest stable version of all the softwares that I used before. Now the question is can mysql be upgraded the way it is now or would an uninstall be better and installing the latest stable version of mysql? Secondly, if the 4.0.13 is very stable and recommended, would uninstalling apache and installing the 2.0.48 of Apache without doing anything to mysql be okay? Thanks to all in advance Ola
Re: table info
On Mon, 19 Jan 2004, Mike Mapsnac wrote: I think that main disadvantage of this command is thah it works for the database but not for specific table. So if a database has 200 tables, find result for specific table is not an easy task. SHOW TABLE STATUS LIKE 'tablename'\G cheers, Tobias From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: table info Date: Mon, 19 Jan 2004 14:10:37 +0200 Mike Mapsnac [EMAIL PROTECTED] wrote: Is it possible to find out when the table was created? How to get information about the table? SHOW TABLE STATUS command provides info about table: http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Rethink your business approach for the new year with the helpful tips here. http://special.msn.com/bcentral/prep04.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table info
Mike Mapsnac [EMAIL PROTECTED] wrote: I think that main disadvantage of this command is thah it works for the database but not for specific table. So if a database has 200 tables, find result for specific table is not an easy task. If you want to get info about specific table, you can just do: SHOW TABLE STATUS LIKE 'table_name'; From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: table info Date: Mon, 19 Jan 2004 14:10:37 +0200 Mike Mapsnac [EMAIL PROTECTED] wrote: Is it possible to find out when the table was created? How to get information about the table? SHOW TABLE STATUS command provides info about table: http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Null value in mediumtext datytype
I create a Visual C++ COM object that makes use of ADO Recordset for database queries. I need to execute an update query in MySQL. I am using MyODBC-3.51.06 and mysql-4.1.1-alpha. My table has a field with datatype as mediumtext. One record consists the value as NULL for this field. But, the ADO returns this value as '' (An Empty String) instead of null during the Select query for the same. The Update populates the where clause of this query from the recordset obtained from the Select query. But the Update fails because of the empty string instead of null for the above field. Please suggest a solution to obtain proper value (for null values) during select for the mediumtext datatypes. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade or Reinstall Question
I have Mysql 4.0.13 installed. I need to know if upgrading is an option or if installing the latest binary is a better option. To be more candid, I have finished the install with the following config. Not so much content yet...Just some pictures that can be uploaded again. Windows 2000 Advanced Server Apache 2.0.47 PHP 4.3.4 Mysql 4.0.13 Geeklog 1.3.8sr3 The Geeklog part is my weblog that I used to design the site. However the problem after I finished the installation was that people were not getting images loaded on their browser from the internet. One person in particular said they got a POSIX ERROR 54. and I really do not know what this error meant and neither did the other person. So I have decided to start the whole thing from scratch and take it one step at a time, but in doing so, install the latest stable version of all the softwares that I used before. Now the question is can mysql be upgraded the way it is now or would an uninstall be better and installing the latest stable version of mysql? Secondly, if the 4.0.13 is very stable and recommended, would uninstalling apache and installing the 2.0.48 of Apache without doing anything to mysql be okay? Note that Apache 2 is not recommended for use with PHP. You should be using Apache version 1.3.28 with PHP. I just did a quick check of the PHP site -- http://www.php.net/ -- in hopes of providing a link to that recommendation, but could not find anything. However, I've seen it mentioned several times on the PHP general discussion list. HTH -- Lowell Allen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with python connect.
I received a suggestion that the problem might be in the privilege tables. Note that this server has never been at a level less than 4.1. The password field in user is 45 char long, and passwords already begin with a *. The suggestion was a good one based on the symptom, but it doesn't apply here. On Monday 19 January 2004 07:25, Michael Satterwhite wrote: I'm running the SuSE 8.2 distro with MySQL 4.1.0-0 and python-mysql-0.9.1-430. On Saturday, I had to do a reinstall of everything (long story). The software listed above is the same as it was before the reinstall. The only difference is that this time I installed 4.1 directly, before I had done an upgrade from 4.0 to 4.1. I try to connect to MySQL as follows: import MySQLdb db = MySQLdb.connect(user=michael, passwd=*, db=backups) When I do this, I get the following exception: _mysql_exceptions.OperationalError: (1249, 'Client does not support authentication protocol requested by server. Consider upgrading MySQL client') Tables are INNODB, but we're not to that point yet. The MySQL client is the one with the 4.1 beta release - and worked before the reinstall. Can anyone offer me any help?? tia ---Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial types
I tried it with an 854kb WKT block with out any problems. A total of 64k
points in fact. Two things, did you correctly close the polygon (first
and last points must be the same). Did you use the astext() function to
display the polygon in your select statement?
CREATE TABLE big (
g geometry NOT NULL default '',
spatial index (g(12))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Sample sql was 854k bytes (took 136 sec. to insert):
insert into big (g)
values(
GeomFromText('polygon((
1 1,
2 2,
3 3,
...
63999 63999,
64000 64000,
64000 1,
1 1
))')
)
The Query:
select astext(g) from big
Sample Results (took .5 sec. to return):
POLYGON((1 1,2 2,3 3,4 4,5 5,6 6,7 7,8 8,9 9,10 10,
11 11,12 12,13 13,14 14,15 15,16 16,17 17,18,19 19,
63998 63998,63999 63999,64000 64000,64000 1,1 1))
The big.MYD was 1meg in size.
Ed
-Original Message-
From: James S reid [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 5:59 AM
To: [EMAIL PROTECTED]
Subject: spatial types
hi - ive been playing with the OGC support for WKT but cant find data
type size constraints for GEOMETRY types. does anybody know what they
are? Ive a polygon with 140,000 bytes as WKT but inserts all produce a
NULL geometry...
any ideas?
regards
Programming today is a race between software engineers striving
to build bigger and better idiot-proof programs, and the Universe
trying to produce bigger and better idiots.
So far, the Universe is winning.
Rich Cook
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with python connect.
The password column is 41 bytes in 4.1, except for 4.1.0 which uses 45 bytes. If I understand you, though, you reinstalled 4.1.0, so that isn't the problem. Perhaps you started mysqld with --old-passwords before but not after the reinstall? Leaving that out would prevent connections from python unless it was compiled with the mysql lib from 4.1.0. See http://www.mysql.com/doc/en/Password_hashing.html for more on the various password hash sizes and their interactions with different version clients. Michael Michael Satterwhite wrote: I received a suggestion that the problem might be in the privilege tables. Note that this server has never been at a level less than 4.1. The password field in user is 45 char long, and passwords already begin with a *. The suggestion was a good one based on the symptom, but it doesn't apply here. On Monday 19 January 2004 07:25, Michael Satterwhite wrote: I'm running the SuSE 8.2 distro with MySQL 4.1.0-0 and python-mysql-0.9.1-430. On Saturday, I had to do a reinstall of everything (long story). The software listed above is the same as it was before the reinstall. The only difference is that this time I installed 4.1 directly, before I had done an upgrade from 4.0 to 4.1. I try to connect to MySQL as follows: import MySQLdb db = MySQLdb.connect(user=michael, passwd=*, db=backups) When I do this, I get the following exception: _mysql_exceptions.OperationalError: (1249, 'Client does not support authentication protocol requested by server. Consider upgrading MySQL client') Tables are INNODB, but we're not to that point yet. The MySQL client is the one with the 4.1 beta release - and worked before the reinstall. Can anyone offer me any help?? tia ---Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification Test Questions
Carsten Pedersen wrote: I have heard from some candidates that they regard the Core exam as being more of a memorization exam than Professional. Which only goes to show, once again, that perceptions differ quite a bit as people come out of the testing room. Carsten, Do you know when the Professional exam will be coming out of its beta status? Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Removing default MySQL install
I've been trying to install a later version of MySQL (4.0.16). The Redhat default install is 3.x, and using rpm -e fails to remove the default packages due to 'dependencies'. How can I safely and successfully uninstall the default mysql packages (server and client) so I can start fresh? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with python connect.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 19 January 2004 08:56, you wrote: The password column is 41 bytes in 4.1, except for 4.1.0 which uses 45 bytes. If I understand you, though, you reinstalled 4.1.0, so that isn't the problem. Perhaps you started mysqld with --old-passwords before but not after the reinstall? Leaving that out would prevent connections from python unless it was compiled with the mysql lib from 4.1.0. Actually, I've never used the --old-passwords option. I'd even forgotten that it existed. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAC/SzjeziQOokQnARArN0AJ9B9b88Z2i3OoYTZIvedIUqL0bJVACgmWKN aMIeKFxHc8UTpidKIsnD+RM= =OEaA -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Removing default MySQL install
Try using this command instead: rpm -e --nodeps (the RPM you want to remove) Eve Atley wrote: I've been trying to install a later version of MySQL (4.0.16). The Redhat default install is 3.x, and using rpm -e fails to remove the default packages due to 'dependencies'. How can I safely and successfully uninstall the default mysql packages (server and client) so I can start fresh? Thanks, Eve -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too slow recovering mysqldump files
Hi list, does anyone know a faster way to recover a mysqldump file cause When I recovered one dump file it took 26 hours ! to finish, I think it's too slow. Thnx in advanced, greetings MySQL server 3.23.58 RedHat 7.3 4GB RAM 2 scsi disk via fiber channel (333GB each) 2 processor Xeon 1.6GHZ dump file size: 2.5 GB ibdata: 11GB innodb tables key_buffer=850M innodb_buffer_pool_size=850M table_cache=1500 _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with Replication in 4.0.17
Neil Gunton wrote: Since I didn't get any replies to my previous message (see below), I am trying to compile MySQL myself, to see if it results in a more stable system when using replication. However this is failing consistently with the following error: make[4]: Entering directory `/usr/src/mysql-4.0.17/sql' source='sql_lex.cc' object='sql_lex.o' libtool=no \ depfile='.deps/sql_lex.Po' tmpdepfile='.deps/sql_lex.TPo' \ depmode=gcc3 /bin/sh ../depcomp \ gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW -DDEFINE_CXA_PURE_VIRTUAL -c -o sql_lex.o `test -f sql_lex.cc || echo './'`sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX*, unsigned int, bool)': sql_lex.cc:171: `get_hash_symbol' undeclared (first use this function) I have tried this with the gcc compiler that comes with RedHat 7.3 (2.96), and also with gcc 3.2.3 (built from source, because of the advice on the MySQL website that says 2.96 might be unstable). I tried building 2.95 from source, but it didn't seem to be able to recognize my system and I couldn't see from the documentation how to fix this. However given that 2.96 (rpm) and 3.2.3 (src) both give exactly the same result, I wouldn't have high hopes for 2.95. I have tried several different invocations for ./configure, including the following three: ./configure --prefix=/usr/local/mysql CFLAGS=-O2 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static CXX=gcc ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared The latter two were from section 2.1.2.5 of the MySQL manual. Searching google for the error message I can see that other people have had a similar problem with this, but I have seen no solutions. Does anyone have the secret incantation to successfully build MySQL 4.0.17? I fully realize that MySQL AB recommends using the binary rpms supplied by them, but since I am having no luck with finding out what's wrong with replication, I think it makes sense to try and build it myself and see if that makes any difference. Any advice much appreciated... Further to the above problem, I eventually found a clue in the internals mailing list archives for MySQL: http://lists.mysql.com/internals/7702 I found that I could go into the sql directory and manually make gen_lex_hash, and then generate lex_hash.h by hand: Starting in the main MySQL source build directory, shell cd sql shell make gen_lex_hash shell ./gen_lex_hash lex_hash.h shell cd .. shell make This seemed to make it work, and the compile then finished successfully. Since then I have upgraded to the latest gcc 3.3.2 (built from source), and rebuilt on both my server and my workstation - and, for some reason, this time around the build went without error on the workstation, even having deleted and re-unpacked the MySQL source directory (i.e. no chance of the manual makes being left over from before). I don't know what's different this time around, a real mystery there! I built using the first example config given in the MySQL docs: CFLAGS=-O2 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static I also found I had to add /usr/local/lib to /etc/ld.so.conf (and then do ldconfig) in order for certain so libs in the new gcc to be found. On my workstation, I found I had to set socket=/var/lib/mysql for both mysqld and client in my.conf, because for some reason Perl DBI (in mod_perl) was looking for it there, whereas MySQL was creating it in /tmp/. Strangely, I didn't have to make this tweak on my server, which is strange since they both run pretty standard Red Hat 7.3 installations. Obviously something is different, but I don't know what. On the workstation, at any rate, it was enough to throw off the 'make' process for MySQL, and it's not just me - I found quite a few references on google to problems building sql_lex.cc. It must be some
Re: ISP and users
Donald Henson said: On Fri, 2004-01-16 at 17:05, Bryan Koschmann - GKT wrote: Hello, I run an ISP where our web customers have access to the MySQL server. When they want a database, they request it through me and I add it. I was just curious if this is the common way it is done, or if there is a safe way they can add their own? Thanks, Bryan That's the way my ISP does it. Of course, that doesn't make it right. :-) Don Henson I too run an ISP and normally run it that way. We have one customer who can use a web page to create databases so they meet certain criteria, but giving direct create and destroy rights to our customers is a bit of a security hole IMHO. And yes the web page is on a secured server. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF SELECT query spoils the results
Hi all, I've got strange thing with my UDF. My UDF receives 2 text columns as parameters - fields of type text (protein or nucleo sequences). It makes by pair comparison and the result is char* - set of output sequences (aligned). Input table is seqTbl: id | seq_type | seq For example, I run my UDF with nucleo chains: SELECT pairalign (q1.seq, q2.seq) from seqTbl as q1, seqTbl as q2 where q1.seq_type = nucl and q2.seq_type = nucl. It works ok. After I run it with protein sequences: SELECT pairalign (q1.seq, q2.seq) from seqTbl as q1, seqTbl as q2 where q1.seq_type = prot and q2.seq_type = prot. The result is ok, also. But when I run the first SELECT again, the results are different (incorrect). They are incorrect even if I exit mysql and then run it again. I have to drop my UDF, exit mysql, run it again and create function again to get the same, correct results for my first SELECT. What's going on? Looking forward for your answers... Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expressions
Regarding the following query: SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y'),SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 Okay, I've looked in the manually up and down, as I know how to do it in Access, but I can't find it. I want to set an expression so I can give the Date_Format function a handy name - so it returns the name of the column as ArticleDate, for instance. I'm missing something simple :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql version 4.1.2
I need MySQL version 4.1.2 build for Windows. Does anyone have a built version of it, or know when it will be released? Thanks for your help, Kate
SQL Query Question
Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expressions
you were so close http://www.mysql.com/doc/en/SELECT.html SELECT COLUMNNAME AS WHATEVER FROM TABLENAME; Marty Gainty - Original Message - From: Ian O'Rourke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 2:16 PM Subject: Expressions Regarding the following query: SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y'),SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 Okay, I've looked in the manually up and down, as I know how to do it in Access, but I can't find it. I want to set an expression so I can give the Date_Format function a handy name - so it returns the name of the column as ArticleDate, for instance. I'm missing something simple :) -- 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: Expressions
I think you can just put the alias after the field like so, select id,author,date_format(entrydate,'%d %m %y') ArticleDate, SectionId,Title,Summary... On Monday, January 19, 2004, at 02:16 PM, Ian O'Rourke wrote: Regarding the following query: SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y'),SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 Okay, I've looked in the manually up and down, as I know how to do it in Access, but I can't find it. I want to set an expression so I can give the Date_Format function a handy name - so it returns the name of the column as ArticleDate, for instance. I'm missing something simple :) -- 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: 100,000,000 row limit?
mysql select count(*) from atoms_in_universe; +-+ | count(*)| +-+ | 30204541410292874012341 | +-+ 1 row in set (0.07 sec) Daevid Vincent http://daevid.com -Original Message- From: Will Lowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 8:38 PM To: Andrew Braithwaite Cc: 'Paul DuBois'; 'Andres Montiel'; '[EMAIL PROTECTED]' Subject: Re: 100,000,000 row limit? I don't believe this. I'm going to write a script to disprove this theory right now.. We have a lot more than 100,000,000 more than that in a single MyISAM table at work: mysql select count(*) from probe_result; +---+ | count(*) | +---+ | 302045414 | +---+ 1 row in set (0.00 sec) -- thanks, Will -- 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: Expressions
rom: sulewski [EMAIL PROTECTED] I think you can just put the alias after the field like so, select id,author,date_format(entrydate,'%d %m %y') ArticleDate, SectionId,Title,Summary... You were correct - the version using AS does not work (I'd tried that before mailing). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expressions
On Mon, 19 Jan 2004, Ian O'Rourke wrote: Regarding the following query: SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y'),SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 Okay, I've looked in the manually up and down, as I know how to do it in Access, but I can't find it. I want to set an expression so I can give the Date_Format function a handy name - so it returns the name of the column as ArticleDate, for instance. I'm missing something simple :) SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y') AS something, SectionID, Title, Summary, Content FROM articles ORDER BY EntryDate DESC LIMIT 10 (The AS isn't mandatory, you can just state the alias if you want, the AS can be good for readability, however) cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expressions
Ian O'Rourke wrote: Regarding the following query: SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y'),SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 I'm still new to Mysql so I'm not sure on this but I think the problem is with your ORDER BY field. Since you are using the date format function I think the output will not sort correctly. So I think I would try this: SELECT ID,Author, DATE_FORMAT(EntryDate,'%d %m %y') FormatedDate, EntryDate, SectionID,Title,Summary,Content FROM articles ORDER BY EntryDate DESC LIMIT 10 Then just don't use the EntryDate column from the query. It's worth a try. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Shared Physical Database Question
Could someone please tell me if tyhe following is possible or if a solution accomplishing the same thing is available? I would like to build a database using two MySQL servers accessing the same physical file on a common Drive attached to each computer via a SAN. Can this be done or is there data integrity issues and database file locking issues?. Our main goal is to provide the data gathered on a secure network and allow it to be seen on an insecure network. I do understand that this can be accomplished using firewalls and other networking tools, but our management has been very firm in their insistence that a user has no direct network access to our internal/secure LAN. Thanks for any help anyone can provide. Matthew Harris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Let me post the question this way, MyTable --- pointerid valueid 811 54 811 63 812 100 813 200 814 300 815 400 I want all the records in MyTable where (valueid=54 and valueid=63) or valueid=400 group by pointerid Which means I would get the records whose pointer id is 811 and 815 Thanks, Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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: Too slow recovering mysqldump files
Mikel, have you set the size of the InnoDB log files as recommended in the manual? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 19, 2004 7:25 PM Subject: Too slow recovering mysqldump files Hi list, does anyone know a faster way to recover a mysqldump file cause When I recovered one dump file it took 26 hours ! to finish, I think it's too slow. Thnx in advanced, greetings MySQL server 3.23.58 RedHat 7.3 4GB RAM 2 scsi disk via fiber channel (333GB each) 2 processor Xeon 1.6GHZ dump file size: 2.5 GB ibdata: 11GB innodb tables key_buffer=850M innodb_buffer_pool_size=850M table_cache=1500 _ MSN. Ms til Cada Da http://www.msn.es/intmap/ -- 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]
Fwd: SQL Query Question
The ands are killing you in regards to what no data returned is that what you mean. Yes, I'm not getting any data on a return. Because the vid can only be one value not both. because (vid=54 and vid=65) which you already know means both have to succeed and if there is no data to match that criteria then you get nothing and the query will move to or vid=100 . You got it. Question just to make sure but you only want records from tab1 that match tab2 and have (vid=54 and vid=65) . In the case of (vid=54 or vid=65) it short curcuits and finds one or the other so yes it will succeed most likely evertime in your case. tab1tab2 id = 1 id = 1 vid = 54 id = 1 vid = 64 this above is what you mean by many to one correct. Yes this is correct. And yes, to make the match it's a simple join. Shouldn't this be a simple join? just for starters here is simple example select tab1.vals from tab1,tab2 where (tab2.vid = 54 and tab2.vid = 65) and tab1.id = tab2.id; This doesn't work because vid can only be one integer per record. So your example doesn't work because no vid field can be both 54 and 65. It's one or the other. I want to find all records in table 1 that will link to a single record in tab2 with a value of 54 and another record with the value 64. so this says return all records from tab2 where vid = 54 and 65 which return specific ID'S from tab2 which go with tab2 vid . so this is a subset of data from table2 which will act like a filter and return the correct matches from table1 when we join ID columns of both tables in an equijoin. Is this along the line of what you are already doing , please comment and maybe I can help somemore. I think you get what I want to do. So how do I do it? :) - Original Message - From: sulewski [EMAIL PROTECTED] To: Jamie Murray [EMAIL PROTECTED] Sent: Monday, January 19, 2004 4:41 PM Subject: Re: SQL Query Question I asked a similar question but this time it's different. Last time I was looking for places where the record in table 1 didn't have a link to table 2. Now I wish to find all the records in table 1 that contain multiple links to table 2. The trouble is that I wish to and and or these links together. So I want to say, find all the records in table 1 where table 2 has the following values (vid=54 and vid=65) or vid=100 etc. Before it was finding one missing link this time it's finding many links. It works fine with or'ed values. find al the records in table 1 where table 2 has the follwing values (vid=54 or vid=65). It's the ands that are killing me because the vid is an integer field and i'm not really trying to find two values in the same record but two values in different records. Does this make better sense? Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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]
group by with hidden fields
Is there a way to tell mysql witch row (from a non unique value column) return when you do a group by omitting some fields from the ones that apear in the select list? Thanks in advance Please reply to this addres to. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.563 / Virus Database: 355 - Release Date: 17/01/2004
Re: SQL Query Question
On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) I *THINK* you're asking for the case where it has one of these values. In that case try Select Table1.* from Table1 t1 JOIN Table2 t2 on t1.ID = t2.rdid Where t2.vid in (46, 554) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Feature request Relating To max_questions
Hi, I rely on max_questions to balance the load on my servers. I'd like to tune them more effectively than just a ballpark guess. During the course of an hour (the question count resets each hour) is it possible to retrieve the question count for a particular user? Obviously it must be stored somehwere in order to implement this feature. If it is not currently possible, I would find it very useful. Something like SELECT current_question_count(theusername) Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Or, if I'm not mistaken, you could do something like: SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id = t2.rdid AND t2.vid IN (46, 554) ; That should work, but the joining thing should too. I can never get the joining straight, so I always enjoy a shorter route. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 4:39 PM To: [EMAIL PROTECTED] Subject: Re: SQL Query Question Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Jochem, I believe this works. This is also easy to build dynamically. The query is going to be generated based upon some user input. Thank you very much, Joe On Monday, January 19, 2004, at 04:38 PM, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- 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: 100,000,000 row limit?
There is an issue in ISAM/MyISAM tables of needing to have correctly sized row pointers. I submitted this as a bug report (it's really a documentation bug), but don't know if it will be fixed. I was happy to get an answer, though, so will share it with you :-) If you get an error like this: ERROR 1030: Got error 136 from table handler. Try: ALTER TABLE tablename MAX_ROWS=[very large value]; INNODB tables do not have the same limitation. I do not know about BDB tables. My large table: mysql select count(*) from inv0web02; +---+ | count(*) | +---+ | 498093481 | +---+ -- Greg On Mon, Jan 19, 2004 at 11:24:19AM -0800, Daevid Vincent wrote: mysql select count(*) from atoms_in_universe; +-+ | count(*)| +-+ | 30204541410292874012341 | +-+ 1 row in set (0.07 sec) Daevid Vincent http://daevid.com -Original Message- From: Will Lowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 8:38 PM To: Andrew Braithwaite Cc: 'Paul DuBois'; 'Andres Montiel'; '[EMAIL PROTECTED]' Subject: Re: 100,000,000 row limit? I don't believe this. I'm going to write a script to disprove this theory right now.. We have a lot more than 100,000,000 more than that in a single MyISAM table at work: mysql select count(*) from probe_result; +---+ | count(*) | +---+ | 302045414 | +---+ 1 row in set (0.00 sec) -- thanks, Will Dr. Gregory B. Newby, Research Faculty, Arctic Region Supercomputing Center University of Alaska Fairbanks. PO Box 756020, Fairbanks, AK 99775 e: newby AT arsc.edu v: 907-474-7160 f: 907-474-5494 w: www.arsc.edu/~newby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Lincoln Milner said: Or, if I'm not mistaken, you could do something like: SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id = t2.rdid AND t2.vid IN (46, 554) ; That should work No. You are back to square one where there should only be one record in t2 with a vid of either 46 or 554. What is requested is a solution where there are 2 records in t2, one with a vid of 46 and one with a vid of 554. I can never get the joining straight, so I always enjoy a shorter route. Just make sure the shorter route gives the same results. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). If you absolutely need to do it with a join you might try Select t1.* from Table1 t1 JOIN Table2 t2 on (t1.id = t2.rdid and (t2.vid = 46 or t2.vid = 554)) I don't think this is as clear as a simple join with a Where, but there are a lot of ways to get the same result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? If you absolutely need to do it with a join you might try Select t1.* from Table1 t1 JOIN Table2 t2 on (t1.id = t2.rdid and (t2.vid = 46 or t2.vid = 554)) I don't think this is as clear as a simple join with a Where, but there are a lot of ways to get the same result. Build the tables, run the queries, compare the results. There are even more ways to get a different result, and yours is one of them. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer join question
Hi, I'm just getting into MYSQL after nearly 12 years away from relational databases and I'm trying to blow the cobwebs away. So please bear with me if this is a simple question! I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. I want to select the title of each movie, along with the corresponding leading actor and/or actress name, but a straightforward join will only return those movie titles that have NOT NULL values in BOTH the acto_id and actr_id fields in the DVD table. My grey-haired memory tells me that an outer join for both the actor table and the actress table is the answer, in that the query will return all titles *even if* one or both fields are NULL. (At least that was the case when I was using Oracle!) So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! Bjorn Barton-Pye Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer join question
Hi, I'm just getting into MYSQL after nearly 12 years away from relational databases and I'm trying to blow the cobwebs away. So please bear with me if this is a simple question! I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. I want to select the title of each movie, along with the corresponding leading actor and/or actress name, but a straightforward join will only return those movie titles that have NOT NULL values in BOTH the acto_id and actr_id fields in the DVD table. My grey-haired memory tells me that an outer join for both the actor table and the actress table is the answer, in that the query will return all titles *even if* one or both fields are NULL. (At least that was the case when I was using Oracle!) So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! Bjorn Barton-Pye Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! i have not really used outer join, i'm still trying to fine tune my sql aswell but to get null values i use left join, to return not null values i use inner join, i select from the first table first though, is it better to select it at the end ? so soemthing like select * from dvd left join actresses actr on actr.actr_id=dvd.actr_id left join actors acto on acto.acto_id=dvd.acto_id or by dvd.title -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
so soemthing like select * from dvd left join actresses actr on actr.actr_id=dvd.actr_id left join actors acto on acto.acto_id=dvd.acto_id or by dvd.title totally forgot, to get a really good query especially when you use Innodb it doesnt like null values on foreign keys, i'd setup a row in the actors and actresses table like No Actor or No actress and then use that key for the null values and use INNER JOIN, check EXPLAIN aswell , it'll show what indexes are being used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
Bjorn Barton-Pye wrote: I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. If you want to learn about outer joins this is a fine model. If you want to index your DVD collection, you should consider the following data model: DVD: dvd_ID PRIMARY KEY title NOT NULL Actor: actor_ID PRIMARY KEY name NOT NULL gender NOT NULL DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Add more tables if you want to plan for 1 DVD having more as 1 movie or 1 movie having more as 1 DVD ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? what exactly is the references keyword ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
[EMAIL PROTECTED] wrote: DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? Yes. what exactly is the references keyword ? It indicates a foreign key. Full syntax is something like: dvd_ID CONSTRAINT dvc_fk FOREIGN KEY REFERENCES DVD (dvd_ID) Read the manual *very* carefully before using foreign keys in MySQL when you have an Oracle background. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
[EMAIL PROTECTED] wrote: DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? Yes. what exactly is the references keyword ? It indicates a foreign key. Full syntax is something like: dvd_ID CONSTRAINT dvc_fk FOREIGN KEY REFERENCES DVD (dvd_ID) Read the manual *very* carefully before using foreign keys in MySQL when you have an Oracle background. Jochem Ok i'm setting up the foreign key relations in the main table is that bad ? I use a join table for a one to many situation. The cool thing about setting it up in the main table is, say you delete an actor you can setup a cascade delete to delete all the dvd titles aswell ;) I'm using sqlyog which has a nice relationship setup feature. -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of, and it's irrelevent. Table1 (t1) isn't used at all in the join parameters. As records from table1 are required in the result, this won't work as desired. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Euro Symbol ()
I've inserted the following currecy sumbols in my table (structure mentioned below). All symbols are displayed properly when I do select, expect for the Euro symbol, which just shows a question mark (?). Any idea why is it behaving like that? I am using MySQL 4.0.17 on Windows XP Pro. My production server is Linux based. +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | CYRANK | decimal(3,0) unsigned zerofill | | | 999 | | | CYSYMBOL | varchar(5) | YES | | | | | CYNAME | varchar(50)| | | | | | CYCODE | char(3)| | PRI | | | +--++--+-+-+---+ Test to use varchar(5) binary field type for CYSYMBOL field (if you store symbols here). Petr Vileta, Czech republic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To Re-write complex query with JOINS
hi vijay, You can use order by group by commands in the queries.. Thanks and Regs, - Aman. On Mon, 19 Jan 2004, Vijay Patel wrote: Hello friends, I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDate othertableidvalue - - 1 2004-01-10 1 10 2 2004-01-20 1 20 3 2004-01-20 2 70 4 2004-01-10 2 80 Now I want to use a single SQL query to find a result where there exist one record for each unique othertableid where the record selected for the othertableid should be the recent one with regard to EffectiveDate. That is from the above records, I want to select Records with RecordId = 2 and 3 because they are the recent one for othertableid = 1 and 2 respectively. Please be sure that I want to retrive all fields including RecordId. The result should not depend on any other fields but EffectiveDate only. I am using MySQL 4.0.12 and it does not support SUBQUERIES which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any SUBQUERY SQL statement can be written with proper JOINS. Can you help me. Vijay. __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- 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: 4.1.1 not an update or upgrade of 4.1.0
Hi, I also faced this kind of error in sub query, any solution for this?: -Aman. On Mon, 19 Jan 2004, Director General: NEFACOMP wrote: Hi group, I have discovered this morning a strange behaviour of MySQL 4.1.1 When the SUB-SELECT contains more than one field, it does not work (this works very well with 4.1.0 ) For example: SELECT field1 FROM Table1 WHERE (field2,field3) IN (SELECT field2,field3 FROM Table2) The above query works as expected with mysqld-nt.exe 4.1.0 (on WinXP) but it does not work with mysqld-nt.exe 4.1.1 (on WinXP). This problem causes unexpected results as the recordset comes empty (on 4.1.1) while it comes with rows with 4.1.0 and no error is returned!!! Maybe it is a known bug! Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Archiving Mail on mysql
Has anyone done any programming relating to using mysql as a mail archive? I'd like to play around with mysql, and I happen to have alot of old mail that I don't need. I'm using Mozilla, so it shouldn't be hard to extract mail from the mail folders and input them into a database. I'm a newbie at databases, so I'm wondering how would I handle messages that have binary attachments. I could send the whole thing as a blob, but then I would have problems with full text searching. I'm willing to work with other people on this and share whatever code results from it. Sincerely, Marc DVer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: transaction support
Hi Chris, My apologies, I didn't correctly explain what I was looking for. I mean more of a solution to creating a client program. I'm thinking of php-gtk but not sure how well this works under windows, especially for printing reports. Thanks, Bryan On Sat, 17 Jan 2004, Chris Nolan wrote: Hmm...have you looked at Rekall? www.total-rekall.co.uk Also, you might want to check out OpenOffice.org's database interface features Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
