wrong field size on query like 'show slave status'
I found out that when I run a query like 'show slave status' mysql I get wrong size of the data field. The component I using to read the field are using the field length by the db to allocate the space for data, so the mismatch cause the read of truncated data. Is this bug already known, and if yes there is a planning to solve it ? I'm using mysql 5.0.18 for windows. Regards, Enzo Arlati [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Semi-complicated delete
4.0.18-standard-log I have a table cart and a table products Key is as follows: products.id = cart.prod_id The problem I have is we have decided to store the users cart, so when they come back it is still in the same state they left it. Pretty usual stuff so far. Two things can possible happen that would make this bad: 1) product has been deleted 2) product has been disabled. Just before I am checking the user out, I want to fix this scenario with a delete statement. What I need to do is: DELETE FROM cart where cart.prod_id does not exist in the products table, or where 'online' = 0. There is a user_id that I match on as well, but that does not entirely matter to this question. Deleting where online = 0 is simple, but deleting where there is a lack of a matching product has me stumped. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Semi-complicated delete
IN MySQL 5 you could use a sub query( http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html). However, i would try using a left join between cart and products and then bring back the results where the products.id field is 'NULL'. There may be a better way of doing this but that is what comes to mind first. Ade Scott Haneda wrote: 4.0.18-standard-log I have a table cart and a table products Key is as follows: products.id = cart.prod_id The problem I have is we have decided to store the users cart, so when they come back it is still in the same state they left it. Pretty usual stuff so far. Two things can possible happen that would make this bad: 1) product has been deleted 2) product has been disabled. Just before I am checking the user out, I want to fix this scenario with a delete statement. What I need to do is: DELETE FROM cart where cart.prod_id does not exist in the products table, or where 'online' = 0. There is a user_id that I match on as well, but that does not entirely matter to this question. Deleting where online = 0 is simple, but deleting where there is a lack of a matching product has me stumped. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHAR() or NCHAR(1) in MySQL 5.0.18 for Wondows
Hello. MySQL 5.0.18 for Wondows. CREATE TABLE test (CHAR(1)). Not assigned 0x80 - 0xFF for CHAR(). When use: mysql_stmt_prepare(), mysql_stmt_bind_param(), mysql_stmt_execute(). For NCHAR()successfully assigned0x80 - 0xFF. Successfully executed: INSERT INTO test (h) VALUES(CHAR(128)) Vladimir Khodakov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BIT datatype and prepared statements
Hi there, Still tracking down the BIT problems. For a simple statement, prefixing it with b'binary value' works (as written in the documentation). Even with the STRICT mode turned ON. However, for prepared statements, how do you bind a BIT(1) or BIT(n)? This doesn't list BIT at all: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html Any pointers? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Server crashes saying Page directory corruption
Hi all, Server version :4.0.26 OS: FeeBSD-4.10 The mysql server(slave) crashes with the following messages in its error log, and gets restarted, after which rollbacks and starts replicating from master Page directory corruption: supremum not pointed to 051226 10:02:58 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex .(many 0's) ;InnoDB: End of page dump 051226 10:02:58 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer 0xc9c1bff8 InnoDB: buf pool start is at 0x52a4000, number of pages 70400 051226 10:02:58InnoDB: Assertion failure in thread 20491 in file ../../innobase/page/../include/buf0buf.ic line 286 InnoDB: We intentionally generate a memory trap InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; it is a slave server replicating from a master. There are no reads happening while server crashed. This has happened couple of times. we tried restoring mysql with a backup of master. But it goes through fine for few days(2-3 days) , after which the same error reiterated. what does this error mean? Please help me in figuring out this error. Thanks , Ravi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] List of countries, regions and cities
Hello list, I am in a need of a database that contains the list of all countries, regions and cities in the world, in RUSSIAN language. Does anybody know where can I find it? I searched Google but all of the databases are in English. Have a great day, Denis S Gerasimov Web Developer Team Force LLC Web:http://www.team-force.org/ www.team-force.org RU Int'l: +7 8362-468693 email:[EMAIL PROTECTED]
Experienced MySQL Admin Needed
I am looking for an experience MySQL administrator in the Connecticut area to help us with our MySQL configuration. ideally, we need someone experienced with configuring automated backups, server optimization, query optimization, replication and troubleshooting. We operate OS X (10.3.9)/ MySQL version 4.0.21 Complete MySQL from Server Logistics. We would be looking for the administrator to come to our location in Branford, CT to view our configuration and make modifications and suggestions. If you are available, contact me off list with your location and what your fees would be as well as your work history/experience. Thanks. Rick Rick Dwyer Computer Operations Manager Quick Link Information Services --- [EMAIL PROTECTED] ph: 203-483-2922 fx: 203-483-2920 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I keep character_set_client value after MyODBC auto reconnect?
I wouldn't hold your breath for any new 3.xx releases or updates. MySQL AB has moved through developing and releasing and almost retiring the 4.0.x and 4.1.x versions of its server line and is actively promoting v5.0.x while still working on v5.1.x. The 4.1.x version is being supported with patches (if the issue still affects that versions) but I cannot remember if 4.0 still is. No new features are planned for any versions lower than 5 that I am aware of. I think now might be a good time to upgrade your server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine 古雷 [EMAIL PROTECTED] wrote on 01/10/2006 09:49:04 PM: Thanks a lot. I needs SET AUTOCOMMIT=0, SET NAMES utf8 and SET TRANSACTION ISOLATION LEVEL READ COMMITTED after a new connection. Further more, MyODBC3.51.10 reconnects automatically when connection lost but MyODBC3.51.12 doesn't. I think automatic reconnection is not bad when it can keep variables' value or can give me a chance to run more than one SQL statement to init my session. Another question: when will new verison of MyODBC3.5 be released? One of our application often disconnect and connect MySQL and then MyODBC often core dump. Best regards, gulei - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: 古雷 [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Tuesday, January 10, 2006 6:34 PM Subject: Re: How can I keep character_set_client value after MyODBC auto reconnect? Hi, did you try SET CHARACTER SET utf8 as Initial Statement under Connection Options of your MyODBC DSN? HTH, Dusan - Original Message - From: 古雷 [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, January 09, 2006 10:19 AM Subject: How can I keep character_set_client value after MyODBC auto reconnect? Hello: I found that MyODBC use mysql_ping to check connection and reconnect. After reconnect by using mysql_ping character_set_client, character_set_connection and character_set_results go back to latin1. But I need them to be utf8. What can I do with MyODBC? Regards, gu lei 祝 事业有成，家庭和睦，身体健康，一切吉祥 古雷 --- 中企动力科技集团 技术事业发展部___技术架构部 \__企业IP通讯部 电话：010 58022278-302 地址：北京亦庄经济技术开发区北工大软件园 （地盛北街1号）A区3号楼 邮编：100176
move to 64 bit
Hi! What must I do to move our mysql-database from 32-bit binaries to 64-bit (mac os x). Is it as simple as just changing binaries for must I prepare the data-files somehow? Thanks in advance Roland Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wrong field size on query like 'show slave status'
AESYS S.p.A. [Enzo Arlati] [EMAIL PROTECTED] wrote on 01/11/2006 03:00:37 AM: I found out that when I run a query like 'show slave status' mysql I get wrong size of the data field. The component I using to read the field are using the field length by the db to allocate the space for data, so the mismatch cause the read of truncated data. Is this bug already known, and if yes there is a planning to solve it ? I'm using mysql 5.0.18 for windows. Regards, Enzo Arlati [EMAIL PROTECTED] We can't read your mind. If you don't tell us WHICH component you are using and HOW you are using it to read your data, we can't recommend anything to help you with your problem. Please respond with more details describing your system, your setup, what commands you use, and how your current results differ from your expectations. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: mysql 5 - disk bound - fixed
Hi All, Just another follow up on my emails. What this came down to was sheer number of records and indices in my tables causing it to take several minutes to insert 10-20K records via load data ... table1 has probably 15 million rows, 60 fields per row, 1 index table2 has 7 million rows, 33 fields per row, 5 indices This is approx 2 months worth of data 2006-01-11 06:37:11 : begin import into table1 2006-01-11 06:43:14: end import into table1 records (17315) deleted : (0) skipped (0) warnings:(0) 2006-01-11 06:43:42 : begin import into table2 2006-01-11 06:48:19 : import into table2 records: (16197) deleted:(0) skipped: (0) warnings:(0) This morning, I created 2 new tables, renamed the original tables and rotated the new tables in. 2006-01-11 08:46:16 : begin import into table1 2006-01-11 08:46:17: end import into table1 records (18853) deleted : (0) skipped (0) warnings:(0) 2006-01-11 08:46:52 : begin import into table2 2006-01-11 08:46:55 : import into table2 records: (17615) deleted:(0) skipped: (0) warnings:(0) This is mysql 5.0.18, running on a HP Proliant, dual P4 3 GHZ, 4 GB RAM, running Suse 9.3, so I do not believe it is hardware related I have to work on an automatic way to rotate these tables every week. Is there an easy way with SQL to create a new table based on the schema of an existing table? -- Thanks! George Law
Re: mysql 5 - disk bound - fixed
CREATE TABLE blah LIKE old_table On 1/11/06, George Law [EMAIL PROTECTED] wrote: Hi All, Just another follow up on my emails. What this came down to was sheer number of records and indices in my tables causing it to take several minutes to insert 10-20K records via load data ... table1 has probably 15 million rows, 60 fields per row, 1 index table2 has 7 million rows, 33 fields per row, 5 indices This is approx 2 months worth of data 2006-01-11 06:37:11 : begin import into table1 2006-01-11 06:43:14: end import into table1 records (17315) deleted : (0) skipped (0) warnings:(0) 2006-01-11 06:43:42 : begin import into table2 2006-01-11 06:48:19 : import into table2 records: (16197) deleted:(0) skipped: (0) warnings:(0) This morning, I created 2 new tables, renamed the original tables and rotated the new tables in. 2006-01-11 08:46:16 : begin import into table1 2006-01-11 08:46:17: end import into table1 records (18853) deleted : (0) skipped (0) warnings:(0) 2006-01-11 08:46:52 : begin import into table2 2006-01-11 08:46:55 : import into table2 records: (17615) deleted:(0) skipped: (0) warnings:(0) This is mysql 5.0.18, running on a HP Proliant, dual P4 3 GHZ, 4 GB RAM, running Suse 9.3, so I do not believe it is hardware related I have to work on an automatic way to rotate these tables every week. Is there an easy way with SQL to create a new table based on the schema of an existing table? -- Thanks! George Law -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5 - disk bound - fixed
2006/1/11, George Law [EMAIL PROTECTED]: Hi All, [snip] I have to work on an automatic way to rotate these tables every week. Is there an easy way with SQL to create a new table based on the schema of an existing table? I believe CREATE TABLE newtbl SELECT blah... is what you're after : http://dev.mysql.com/doc/refman/5.0/en/create-table.html FTFM : You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: wrong field size on query like 'show slave status'
I'm using c++builder 6 and MYDAC 3.55 component with MySQL client version: 4.1.3 Direct The MYDAC support told me that MyDAC reserves memory for field data on describing fields, before getting values from server. Other products, which ignore performance, can detect end of string values by #0 terminator. It's seems , are least that is what MYDAC support told me, that MySQL API function mysql_fetch_field_direct returns Field.Length = 14 for Slave_IO_state field. while the field should be bigger. So, the field Slave_IO_State, returned by 'show slave status' command, should be as belowe: Slave_IO_State: Waiting for master to send event Instead I only get a value: 'Waiting for ma' I got the same problem for more other fields as Last_Error and Replicate_Ignore_Table. Do you have some hints, maybe there is no compatibility between mysql server 5 and client mysql 4.1.3 ? -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato: mercoledi 11 gennaio 2006 16.20 A: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Mysql. Com (E-mail) Oggetto: Re: wrong field size on query like 'show slave status' AESYS S.p.A. [Enzo Arlati] [EMAIL PROTECTED] wrote on 01/11/2006 03:00:37 AM: I found out that when I run a query like 'show slave status' mysql I get wrong size of the data field. The component I using to read the field are using the field length by the db to allocate the space for data, so the mismatch cause the read of truncated data. Is this bug already known, and if yes there is a planning to solve it ? I'm using mysql 5.0.18 for windows. Regards, Enzo Arlati [EMAIL PROTECTED] We can't read your mind. If you don't tell us WHICH component you are using and HOW you are using it to read your data, we can't recommend anything to help you with your problem. Please respond with more details describing your system, your setup, what commands you use, and how your current results differ from your expectations. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
script error in program.
Having an error in a script that I cannot figure out why it's not working. The
problem is on line 15 while ($row = mysql_fetch_object ($result)).
Movie DatabaseBR
pre
?php
// Connect to MySQL server
// User name is root and password is blank
$link = mysql_connect('127.0.0.1','root','mmtnet');
// Select the database
$db = mysql_select_db(movie_library, $link);
// Query the database for all fields from table 'movies'
$result = mysql_query(SELECT * FROM movies, $link);
// Loop through all the rows
while ($row = mysql_fetch_object($result))
{
// print the object for each row
print_r($row);
}
?
/pre
/BODY
/HTML
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE
I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13)
Re: script error in program.
Jon Miller wrote: Having an error in a script that I cannot figure out why it's not working. The problem is on line 15 while ($row = mysql_fetch_object ($result)). What, exactly is the error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding data from one table to another
The easiest way to do that would be to CREATE TABLE new_table_name SELECT (query you use to join the tables) The old tables then become redundant. On 11 Jan 2006, at 17:30, 2wsxdr5 wrote: I have two tables with data on people in them. Table A is a subset of table B, However, there is data about these people in table B that is not in table A. with a simple select I can do a join and get a result set with all the data I need to show, but what I would like to do is change table A so it also has one of the fields from table B. Adding the field to table A is trivial , but how do I then populate that new field with data from the table B? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: script error in program.
A couple of things.
first: as documented in http://ca3.php.net/mysql_connect, the third
(optional) parameter is supposed to be the password, so if the password
for root has been left blank, this connect statement cannot succeed. It
may well be the only immediate problem.
second: I notice that after your connect, the select_db and the query,
you are not checking the return values for errors (which would probably
have turned up item 1 immediately. Never assume that interactions with
external systems (which the database is) are successful, even if you are
sure the parameters are correct... there are always a million things
that could go wrong at runtime. Each of those php mysql_XXX calls will
return a null or false on failure .. if you check every time you will
save yourself a lot of hassle in the long run,
- michael dykman
- [EMAIL PROTECTED]
On Thu, 2006-01-12 at 01:15 +0800, Jon Miller wrote:
Having an error in a script that I cannot figure out why it's not working.
The problem is on line 15 while ($row = mysql_fetch_object ($result)).
Movie DatabaseBR
pre
?php
// Connect to MySQL server
// User name is root and password is blank
$link = mysql_connect('127.0.0.1','root','mmtnet');
// Select the database
$db = mysql_select_db(movie_library, $link);
// Query the database for all fields from table 'movies'
$result = mysql_query(SELECT * FROM movies, $link);
// Loop through all the rows
while ($row = mysql_fetch_object($result))
{
// print the object for each row
print_r($row);
}
?
/pre
/BODY
/HTML
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in subject
One huge problem with this approach. The new table doesn't have any of the indexes that were present in the previous table. You may be better off to build a script that renames the current tables followed with the full CREATE TABLE statement(s). Do a SHOW CREATE TABLE current table; Then take that output and put it in a script RENAME TABLE current_table TO new_table_name; CREATE TABLE . ON 5.0 you can use PREPARED STATEMENTS if you want to control the new table names. See 13.7. SQL Syntax for Prepared Statements Beginning with MySQL 4.1.3, an alternative interface to prepared statements is available: SQL syntax for prepared statements. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level: -Original Message- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 9:47 AM To: MySQL General Subject: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in subject 2006/1/11, George Law [EMAIL PROTECTED]: Hi All, [snip] I have to work on an automatic way to rotate these tables every week. Is there an easy way with SQL to create a new table based on the schema of an existing table? I believe CREATE TABLE newtbl SELECT blah... is what you're after : http://dev.mysql.com/doc/refman/5.0/en/create-table.html FTFM : You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE (SOLVED!)
For any of you that run into this problem I found this: After receiving some help from a friend it turns out that one of the directories within the path to the target destination was not flagged 755, and because of that one ( which happened to be 3 levels back) it didn't allow mysql to properly execute what it needed. So if you run into this simply check each directory within the path and make sure of it's settings -Original Message- From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] Sent: Wed 1/11/2006 11:17 AM To: mysql@lists.mysql.com Subject: LOAD DATA INFILE I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13)
RE: [SPAM] - Adding data from one table to another - Bayesian Filter detected spam
You probably want a multi table update assuming you are running at least 4.0.x. Add the column(s) to A with an ALTER TABLE UPDATE A INNER JOIN B ON (... SETA.col_name = B.col_name, A.col_name_2 = B.col WHERE .. You can copy the join structure from the FROM section of your SELECT statement and paste it in the UPDATE section and move the WHERE section of the SELECT to the UPDATE. -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 11:30 AM To: mysql@lists.mysql.com Subject: [SPAM] - Adding data from one table to another - Bayesian Filter detected spam I have two tables with data on people in them. Table A is a subset of table B, However, there is data about these people in table B that is not in table A. with a simple select I can do a join and get a result set with all the data I need to show, but what I would like to do is change table A so it also has one of the fields from table B. Adding the field to table A is trivial , but how do I then populate that new field with data from the table B? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.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: LOAD DATA INFILE
Do chmod -R 755 on datapath Thanks Praj - Original Message - From: Jay Paulson (CE CEN) [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 11, 2006 10:47 PM Subject: LOAD DATA INFILE I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding data from one table to another
2wsxdr5 [EMAIL PROTECTED] wrote on 01/11/2006 12:30:10 PM: I have two tables with data on people in them. Table A is a subset of table B, However, there is data about these people in table B that is not in table A. with a simple select I can do a join and get a result set with all the data I need to show, but what I would like to do is change table A so it also has one of the fields from table B. Adding the field to table A is trivial , but how do I then populate that new field with data from the table B? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com Assuming you are using a recent version of MySQL, you have the ability to do a multi-table update. It looks just like a multi-table select with a few pieces rearranged UPDATE TABLEA a LEFT JOIN TABLEB b ON b.some_value = a.some_value SET a.new_field = b.field_with_other_data; The FROM clause becomes the UPDATE clause (complete with joins). The SELECT clause transforms itself into the SET clause. I didn't need a WHERE clause because the ON conditions took care of that this time. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Problem using IN statement MySQL 5
Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- Paul Nowosielski Webmaster 2401 Broadway St Boulder, Co 80304 Tel: 303.440.0666 ext:219 Cell: 303.827.4257 www.celebrityaccess.com www.protouronline.com www.boxofficenetwork.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Your select has two tables, but you don't join them. Can you provide the table info, and a subset of the data that should be pulled back by this query? David Paul Nowosielski wrote: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help crafting an SELECT
Hi List! Please forgive me if I'm asking the wrong list (and please guide me to the right place). I have two tables (simplified for this example) pics and tags like so: pics: picid photo 1Mustang 2Apple 3Rock 4Beetle tags: tagid picidtag 11 Car 21 Red 32 Red 43 Quartz 54 Car 62 Food 71 1979 I'm trying to craft a query which will join the tables by the picid column and return the entries which match multiple tags. For instance I want to query for items that are both red and car (to get the records: Mustang and Beetle in this case). I assume I should start with something like: SELECT * FROM pics JOIN tags ON pics.picid=tags.tagid WHERE . and that's where I get stuck. tag=red AND tag=car isn't right and neighter is tag=red OR tag=car. I think this should be simple ... but I'm too new to SQL. I've messed around with GROUPs, UNIONs, etc. but I'm just out of my league. Can someone help, or point me to a good tutorial/explanation that would help? Thanks! MikeMartin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: move to 64 bit
Hi! Essentially this is true. However let me qualify that a little... Clearly to take advantage of 64 to you'll want to change your memory settings and allocate something in my.cnf over 2G. Also you'll want to be using OS X 10.4.n, 10.3 and earlier don't really support 64 bit. Now that the truly basics are done... Beware, we encountered a problem with OS X 10.4.n, any 64 Bit MySQL and InnoDB... Under the right mix of conditions when you allocate Innodb more than 2Gbytes of memory it is possible at some point to hang the machine, and it will restart 5 minutes later when the system watchdog processes cycle the power supply to fix itself. Turns out its an OS bug, and it's the sort of thing where you need the Sun, Moon and 18 planets to align together to happen, which made it a little difficult to track down at Apples end. The good news is I'm told they found it and nuked it, and we will likely see the fix included in OS X 10.4.5... just wanted to warn you in case you are using InnoDB and you start to see the Sun line up with the Moon and... Other than that, go for it, it all fits together beautifully, changing the binary is all you need to do. Best Regards, Bruce On Jan 11, 2006, at 7:19 AM, Roland Carlsson wrote: Hi! What must I do to move our mysql-database from 32-bit binaries to 64-bit (mac os x). Is it as simple as just changing binaries for must I prepare the data-files somehow? Thanks in advance Roland Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Paul, SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) First, IN() accepts either a literal value list, or a subquery which returns a value list. Just passing it a table name won't work. Second, your join looks suspicious. Is this what you mean?... SELECT * FROM encore AS enc INNER JOIN article AS art USING (article_id) WHERE enc.encore_id= 10; PB - Paul Nowosielski wrote: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.17/226 - Release Date: 1/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Paul Nowosielski [EMAIL PROTECTED] wrote on 01/11/2006 02:41:05 PM: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- Paul Nowosielski Webmaster 2401 Broadway St Boulder, Co 80304 Tel: 303.440.0666 ext:219 Cell: 303.827.4257 www.celebrityaccess.com www.protouronline.com www.boxofficenetwork.com They look similar but an actual list of numeric values is not the same as a string containing a list of numeric values. Your `articles` column in your `encore` table contains the single string 43,44,45,46 which converts to a single numeric value of 43. That's why you only see the one row returned. What you want to be able to do is do treat that string as separate values. One way to do this with MySQL is with the function FIND_IN_SET() but that will negate the use of indexes. Check out the other SET and string related functions as parts of alternate solutions. http://dev.mysql.com/doc/refman/5.0/en/set.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html The better solution is to re-organize your data so that there is a third table that matches up encores to articles. This two-column table could contain millions of entries and you might think this will slow things down but the engine will be able to use indexes and your queries will actually move MUCH faster. Even on a few hundred entries you will be able to notice that FIND_IN_SET() will be slow. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help crafting an SELECT
[EMAIL PROTECTED] wrote on 01/11/2006 02:42:27 PM:
Hi List!
Please forgive me if I'm asking the wrong list (and please guide me to
the right place).
I have two tables (simplified for this example) pics and tags like
so:
pics:
picid photo
1Mustang
2Apple
3Rock
4Beetle
tags:
tagid picidtag
11 Car
21 Red
32 Red
43 Quartz
54 Car
62 Food
71 1979
I'm trying to craft a query which will join the tables by the picid
column and return the entries which match multiple tags. For
instance I want to query for items that are both red and car (to
get the records: Mustang and Beetle in this case).
I assume I should start with something like:
SELECT * FROM pics JOIN tags ON pics.picid=tags.tagid WHERE .
and that's where I get stuck. tag=red AND tag=car isn't right and
neighter is tag=red OR tag=car.
I think this should be simple ... but I'm too new to SQL. I've messed
around with GROUPs, UNIONs, etc. but I'm just out of my league. Can
someone help, or point me to a good tutorial/explanation that would
help?
Thanks!
MikeMartin
One way to do this is to run a query that counts how many tags that belong
to a picture match your list of criteria. Run this and you will see how
many pictures have either or both of the tags you are looking for:
SELECT p.id, count(t.tagid) matches
FROM pics p
LEFT JOIN tags t
ON p.picid = t.picid
AND t.tag in ('red','car')
GROUP by p.id;
There are three ways to declare which rows of data participate in a query:
The ON clauses of your JOINs, the WHERE clause, and the HAVING clause.
JOINs are evaluated first and define what you want the query to look at as
its source data. The WHERE clause refines the rows identified by the JOINs
so that you continue evaluation with only those rows you want. The GROUP
BY is the next part of the query to be evaluated next. That means you
can't WHERE the results of a GROUP BY. The GROUP BY is where you count how
many matches you had so we need to use the third option, the HAVING
clause, to limit your results to just those pics with 2 matches against
your 2 search terms. Against your simplified tables, the query looks like
this:
SELECT p.id, count(t.tagid) matches
FROM pics p
LEFT JOIN tags t
ON p.picid = t.picid
AND t.tag in ('red','car')
GROUP by p.id
HAVING matches=2;
There are other ways to solve this type of query problem but they suffer
from the inability to scale easily to multiple terms. If you need to match
2 of 3 terms, just put all three terms in the ON clause and leave the
HAVING clause to look for just 2 matches. This pattern is very flexible
and simple to learn.
Hope this helped,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Help crafting an SELECT
Mike,
You're in the right place. If I understand your requirement correctly,
you want entries which...
match on id,
match a selection from ('Mustang' or 'Beetle'),
match multiple values in tags (eg 'red' and 'car').
Here's one solution:
SELECT p.photo, t.tag
FROM pics p
INNER JOIN tags c USING (picid)
WHERE p.photo IN('Mustang','Beetle')
AND t.tag IN('car','red');
Here's a slightly different-looking version of the same answer:
SELECT
p.photo,
GROUP_CONCAT(t.tag)
FROM pics p
INNER JOIN tags c USING (picid)
WHERE p.photo IN('Mustang','Beetle')
AND t.tag IN('car','red')
GROUP BY p.photo;
PB
-
Mike Martin wrote:
Hi List!
Please forgive me if I'm asking the wrong list (and please guide me to
the right place).
I have two tables (simplified for this example) pics and tags like so:
pics:
picid photo
1Mustang
2Apple
3Rock
4Beetle
tags:
tagid picidtag
11 Car
21 Red
32 Red
43 Quartz
54 Car
62 Food
71 1979
I'm trying to craft a query which will join the tables by the picid
column and return the entries which match multiple tags. For
instance I want to query for items that are both red and car (to
get the records: Mustang and Beetle in this case).
I assume I should start with something like:
SELECT * FROM pics JOIN tags ON pics.picid=tags.tagid WHERE .
and that's where I get stuck. tag=red AND tag=car isn't right and
neighter is tag=red OR tag=car.
I think this should be simple ... but I'm too new to SQL. I've messed
around with GROUPs, UNIONs, etc. but I'm just out of my league. Can
someone help, or point me to a good tutorial/explanation that would
help?
Thanks!
MikeMartin
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.17/226 - Release Date: 1/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BIT datatype and prepared statements
Hello. This doesn't list BIT at all: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html Have you tried MYSQL_TYPE_BIT? It is listed among others in mysql_com.h. Martijn Tonies wrote: Hi there, Still tracking down the BIT problems. For a simple statement, prefixing it with b'binary value' works (as written in the documentation). Even with the STRICT mode turned ON. However, for prepared statements, how do you bind a BIT(1) or BIT(n)? This doesn't list BIT at all: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html Any pointers? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure work badly with binlog
Hello.
This is a bug:
http://bugs.mysql.com/bug.php?id=16378
AESYS S.p.A. [Enzo Arlati] wrote:
I found a problem using stored procedure and bin-log enabled.
Suppose I stored procedure like this:
==
DELIMITER $$;
DROP PROCEDURE IF EXISTS `pmv_manager`.`pAggiornaStatusNotificaPMV`$$
CREATE PROCEDURE `pAggiornaStatusNotificaPMV`( ipAddrPMV varchar(16),
ipAddrST varchar(16), ipAddrSNMP varchar(16))
BEGIN
declare ifound int default -1;
-- --
select ipAddrPMV, ipAddrST, ipAddrSNMP;
-- --
select count(ip_addr_pmv) into ifound from status_notifica_pmv
where ip_addr_pmv = ipAddrPMV;
if( ifound = 0 ) then
insert into status_notifica_pmv ( ip_addr_pmv, ip_addr_srv_st,
ip_addr_srv_snmp, dt_mod )
values( ipAddrPMV, ipAddrST , ipAddrSNMP,
current_timestamp );
else
update status_notifica_pmv
set ip_addr_srv_st = ipAddrST,
ip_addr_srv_snmp = ipAddrSNMP,
dt_mod = current_timestamp
where ip_addr_pmv = ipAddrPMV;
end if;
END$$
DELIMITER ;$$
==
where tablke status_notifica_pmv are defined as:
==
CREATE TABLE `status_notifica_pmv`
`ip_addr_pmv` varchar(16) NOT NULL,
`ip_addr_srv_st` varchar(16) default NULL,
`ip_addr_srv_snmp` varchar(16) default NULL,
`dt_mod` timestamp NULL default NULL,
PRIMARY KEY (`ip_addr_pmv`),
CONSTRAINT `status_notifica_pmv_ibfk_1` FOREIGN KEY
(`ip_addr_pmv`) REFERENCES `lista_pmv` (`IPAddress`) ON DELETE CASCADE ON
UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
==
if I call the procedure the task is performed as expected
it create a new record or modify it if present with the right value
==
call pAggiornaStatusNotificaPMV( '192.168.200.222', '192.168.200.218',
'192.168.200.218' );
- ipAddrPMVipAddrST ipAddrSNMP
--- --- ---
192.168.200.222 192.168.200.218 192.168.200.218
==
but if I look inside the binlog file I found corrupted data
==
pmv_manager_log_bin.011864 Query1 644
use `pmv_manager`; update status_notifica_pmv
set ip_addr_srv_st = NAME_CONST('ipAddrST',4оQÐоQDÑQ$Ñ_
WRONG DATA
==
==
Maybe I wrong something or should be a bug ?
Enzo Arlati
[EMAIL PROTECTED]
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing table
Hello. MySQL privilege tables can be created with mysql_install_db script. I recommend you to backup your mysql database, before playing with them. Are you sure that mysql.user table is not exist? Jon Miller wrote: In a new setup I had the users table in MySQL. A few hours later I went = to setup a new account and found out that the user table is missing. Is = there a way to get it back? I can still login as root and myself. I wanted to add another user to the database. Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: localhost vs. 127.0.0.1 error: 'Lost connection to MySQL server during query'
Hello. [EMAIL PROTECTED]:~# telnet localhost 43306 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. Connection closed by foreign host. I had an experience with similar symptoms which was caused by TCP wrapper. Check your /etc/hosts.allow. Lance Rochelle wrote: OK here goes I've had a problem for a few days that I can't resolve. At the OS level If I ping the name localhost I get a good return and if I ping 127.0.0.1 I also get a good return as noted below. [EMAIL PROTECTED]:~# ping localhost -c 2 PING localhost (127.0.0.1) 56(84) bytes of data. 64 bytes from localhost (127.0.0.1): icmp_seq=3D1 ttl=3D64 time=3D0.034 ms 64 bytes from localhost (127.0.0.1): icmp_seq=3D2 ttl=3D64 time=3D0.028 ms --- localhost ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 999ms rtt min/avg/max/mdev =3D 0.028/0.031/0.034/0.003 ms [EMAIL PROTECTED]:~# ping 127.0.0.1 -c 2 PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data. 64 bytes from 127.0.0.1: icmp_seq=3D1 ttl=3D64 time=3D0.031 ms 64 bytes from 127.0.0.1: icmp_seq=3D2 ttl=3D64 time=3D0.029 ms --- 127.0.0.1 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 999ms rtt min/avg/max/mdev =3D 0.029/0.030/0.031/0.001 ms when I run the restart script I get the following in syslog Jan 10 15:35:51 www mysqld[2334]: 060110 15:35:51 [Note] /usr/sbin/mysqld: Normal shutdown Jan 10 15:35:51 www mysqld[2334]: Jan 10 15:35:51 www mysqld[2334]: 060110 15:35:51 InnoDB: Starting shutdow= n... Jan 10 15:35:54 www mysqld[2334]: 060110 15:35:54 InnoDB: Shutdown completed; log sequence number 0 43884 Jan 10 15:35:54 www mysqld[2334]: 060110 15:35:54 [Note] /usr/sbin/mysqld: Shutdown complete Jan 10 15:35:54 www mysqld[2334]: Jan 10 15:35:54 www mysqld_safe[2590]: ended Jan 10 15:35:55 www mysqld_safe[2649]: started Jan 10 15:35:55 www mysqld[2652]: 060110 15:35:55 InnoDB: Started; log sequence number 0 43884 Jan 10 15:35:55 www mysqld[2652]: /usr/sbin/mysqld: ready for connections. Jan 10 15:35:55 www mysqld[2652]: Version: ' 4.1.11-Debian_4sarge2-log' socket: '/var/run/mysqld/mysqld.sock'=20 port: 43306 Source distribution All looks good so far here is my my.cnf file [EMAIL PROTECTED]:/etc/mysql# grep -v ^# my.cnf [client] port=3D 43306 socket=3D /var/run/mysqld/mysqld.sock [mysqld_safe] socket=3D /var/run/mysqld/mysqld.sock nice=3D 0 [mysqld] user=3D mysql pid-file=3D /var/run/mysqld/mysqld.pid socket=3D /var/run/mysqld/mysqld.sock port=3D 43306 basedir=3D /usr datadir=3D /var/lib/mysql tmpdir=3D /tmp language=3D /usr/share/mysql/english skip-external-locking key_buffer=3D 16M max_allowed_packet=3D 16M thread_stack=3D 128K query_cache_limit=3D 1048576 query_cache_size=3D 16777216 query_cache_type=3D 1 log-bin=3D /var/log/mysql/mysql-bin.log max_binlog_size=3D 104857600 skip-bdb [mysqldump] quick quote-names max_allowed_packet=3D 16M [mysql] [isamchk] key_buffer=3D 16M here is the variables from mysqladmin -u root -p variables +- +--+ | Variable_name | Value=20 | +-+= --+ | back_log| 50 =20 | | basedir | /usr/=20 | | bdb_cache_size | 8388600 =20 | | bdb_home| =20 | | bdb_log_buffer_size | 0=20 | | bdb_logdir | =20 | | bdb_max_lock| 1=20 | | bdb_shared_data | OFF =20 | | bdb_tmpdir | =20 | | binlog_cache_size | 32768=20 | | bulk_insert_buffer_size | 8388608 =20 | | character_set_client| latin1 =20 | | character_set_connection| latin1 =20 | | character_set_database | latin1 =20 | | character_set_results | latin1 =20 | |
Re: Help crafting an SELECT
On 1/11/06, Gordon Bruce [EMAIL PROTECTED] wrote:
I assume you are looking for both red and car.
You can not use IN because that is implicitly an IN.
You hae to join the tags table to itself using 2 different aliasis.
{If you required 3 things then you would join it 3 times etc..}
SELECT *
FROM pics
INNER JOIN tags AS t1
ON (pics.picid=t1.picid
AND t1.tag = 'Car'
)
INNER JOIN tags AS t2
ON (pics.picid=t2.picid
AND t2.tag = 'Red'
)
Brilliant!
Not only does it work but I can easily piece it together for multiple tags.
Thanks!
MikeMartin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help crafting an SELECT
[EMAIL PROTECTED] wrote on 01/11/2006 03:53:26 PM:
On 1/11/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote on 01/11/2006 02:42:27 PM:
Hi List!
Please forgive me if I'm asking the wrong list (and please guide me
to
the right place).
I have two tables (simplified for this example) pics and tags
like
so:
pics:
picid photo
1Mustang
2Apple
3Rock
4Beetle
tags:
tagid picidtag
11 Car
21 Red
32 Red
43 Quartz
54 Car
62 Food
71 1979
I'm trying to craft a query which will join the tables by the picid
column and return the entries which match multiple tags. For
instance I want to query for items that are both red and car
(to
get the records: Mustang and Beetle in this case).
I assume I should start with something like:
SELECT * FROM pics JOIN tags ON pics.picid=tags.tagid WHERE .
and that's where I get stuck. tag=red AND tag=car isn't right
and
neighter is tag=red OR tag=car.
I think this should be simple ... but I'm too new to SQL. I've
messed
around with GROUPs, UNIONs, etc. but I'm just out of my league. Can
someone help, or point me to a good tutorial/explanation that would
help?
Thanks!
MikeMartin
One way to do this is to run a query that counts how many tags that
belong
to a picture match your list of criteria. Run this and you will see
how many
pictures have either or both of the tags you are looking for:
SELECT p.id, count(t.tagid) matches
FROM pics p
LEFT JOIN tags t
ON p.picid = t.picid
AND t.tag in ('red','car')
GROUP by p.id;
There are three ways to declare which rows of data participate in a
query:
The ON clauses of your JOINs, the WHERE clause, and the HAVING clause.
JOINs
are evaluated first and define what you want the query to look at as
its
source data. The WHERE clause refines the rows identified by the JOINs
so
that you continue evaluation with only those rows you want. The GROUP
BY is
the next part of the query to be evaluated next. That means you can't
WHERE
the results of a GROUP BY. The GROUP BY is where you count how many
matches
you had so we need to use the third option, the HAVING clause, to
limit your
results to just those pics with 2 matches against your 2 search terms.
Against your simplified tables, the query looks like this:
SELECT p.id, count(t.tagid) matches
FROM pics p
LEFT JOIN tags t
ON p.picid = t.picid
AND t.tag in ('red','car')
GROUP by p.id
HAVING matches=2;
There are other ways to solve this type of query problem but they
suffer
from the inability to scale easily to multiple terms. If you need to
match 2
of 3 terms, just put all three terms in the ON clause and leave the
HAVING
clause to look for just 2 matches. This pattern is very flexible and
simple
to learn.
Hope this helped,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Shawn,
Thanks for your (very fast!) response. Thanks also for you explanation.
I think there's a problem with using the count though. Will it not
count ALL the tags, not just the ones that matched the IN? Will it
not include Apple in the response since red is in the tag list for
Apple and there are 2 tags (red and food). Similarly will it
not exclude Mustang since it will count 3 tags (red, car,
1979)?
I want to ask for red and car and get back: Mustang, Beetle.
WAIT!!! I forgot an entry in the data -- the Beetle is red too!
Here's the data again with the fix:
pics:
picid photo
1Mustang
2Apple
3Rock
4Beetle
tags:
tagid picidtag
11 Car
21 Red
32 Red
43 Quartz
54 Car
62 Food
71 1979
84 Red
Thanks for the help!
Look more closely at WHAT is being counted The first query will show
you how many tags (of the particular type(s) you are looking for) exist
for each and every item. There will be values of 0, 1, or 2 in the matches
column. If you check out which items only have 2 matches you will see that
they are the items you are trying to identify. The second query picks out
just those with 2 matches. Is it not working?
Now, this technique will have a problem if you ever allow a single pic to
have more than one copy of a particular tag. Say for instance that Apple
had two 'red' tags, it would count both of them and it would be a false
positive. Good data produces good results.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
problems with old isam tables
I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New install--access denied
OK, I've just installed the MySQL server and client version 5.0.18 on RH EL4. I was following through the manual and it says I should test connectivity by entering mysqladmin version. I tried this, but got this error: connect to server 'localhost' failed error: 'Access denied for user 'webuser' @localhost (using password:NO)' I haven't set up any accounts or anything, because I'm following the manual in order. What should I do here to set up an account and get this working? Thanks. Thom Hehl Heavyweight Software for Heavyweight Needs www.heavyweightsoftware.com -- In every revolution, there is one man with a vision.--Jerome Bixby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I keep character_set_client value after MyODBC auto reconnect?
Hi, Shawn Green Thanks for your reply. But I expect new version of MyODBC(one of mysql-connectors), not of mysqld. We now use mysql5.0. The newest version of MyODBC is 3.51.12. Regards, gulei - Original Message - From: [EMAIL PROTECTED] To: 古雷 [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com; Dušan Pavlica [EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 11:16 PM Subject: Re: How can I keep character_set_client value after MyODBC auto reconnect? I wouldn't hold your breath for any new 3.xx releases or updates. MySQL AB has moved through developing and releasing and almost retiring the 4.0.x and 4.1.x versions of its server line and is actively promoting v5.0.x while still working on v5.1.x. The 4.1.x version is being supported with patches (if the issue still affects that versions) but I cannot remember if 4.0 still is. No new features are planned for any versions lower than 5 that I am aware of. I think now might be a good time to upgrade your server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine 古雷 [EMAIL PROTECTED] wrote on 01/10/2006 09:49:04 PM: Thanks a lot. I needs SET AUTOCOMMIT=0, SET NAMES utf8 and SET TRANSACTION ISOLATION LEVEL READ COMMITTED after a new connection. Further more, MyODBC3.51.10 reconnects automatically when connection lost but MyODBC3.51.12 doesn't. I think automatic reconnection is not bad when it can keep variables' value or can give me a chance to run more than one SQL statement to init my session. Another question: when will new verison of MyODBC3.5 be released? One of our application often disconnect and connect MySQL and then MyODBC often core dump. Best regards, gulei - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: 古雷 [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Tuesday, January 10, 2006 6:34 PM Subject: Re: How can I keep character_set_client value after MyODBC auto reconnect? Hi, did you try SET CHARACTER SET utf8 as Initial Statement under Connection Options of your MyODBC DSN? HTH, Dusan - Original Message - From: 古雷 [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, January 09, 2006 10:19 AM Subject: How can I keep character_set_client value after MyODBC auto reconnect? Hello: I found that MyODBC use mysql_ping to check connection and reconnect. After reconnect by using mysql_ping character_set_client, character_set_connection and character_set_results go back to latin1. But I need them to be utf8. What can I do with MyODBC? Regards, gu lei 祝 事业有成，家庭和睦，身体健康，一切吉祥 古雷 --- 中企动力科技集团 技术事业发展部___技术架构部 \__企业IP通讯部 电话：010 58022278-302 地址：北京亦庄经济技术开发区北工大软件园 （地盛北街1号）A区3号楼 邮编：100176
left join record in one joined table and not in another
I have three tables joined on key fields: delivery is joined with invoice_detail on delivery_id and with location on loc_id. I want to return records of deliveries that have corresponding records in the location table, but have no corresponding records in the invoice table. Here's the query I'm attempting to use: select d.co_id, co.name, count(*) from company co, delivery d left join ( invoice_detail id, location loc ) on ( d.delivery_id = id.delivery_id and d.loc_id = loc.loc_id ) where d.co_id = co.co_id and d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name If I take out either one of the table references in the left join and criteria, it works fine. For example, I can either return deliveries that have corresponding locations or don't have corresponding invoice records. But I can't return records that have locations and don't have invoice records. I get 0 records in the result set. I'm sure I can get this to work if I use something like d.loc_id in ( select ... from location ..., but I want to avoid that if possible because I think that subquery retrieves the entire contents of the table for comparison. Does anyone have any idea why my query isn't working? Any help or ideas are greatly appreciated.
Re: left join record in one joined table and not in another
Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/11/2006 11:33:38 PM: I have three tables joined on key fields: delivery is joined with invoice_detail on delivery_id and with location on loc_id. I want to return records of deliveries that have corresponding records in the location table, but have no corresponding records in the invoice table. Here's the query I'm attempting to use: select d.co_id, co.name, count(*) from company co, delivery d left join ( invoice_detail id, location loc ) on ( d.delivery_id = id.delivery_id and d.loc_id = loc.loc_id ) where d.co_id = co.co_id and d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name If I take out either one of the table references in the left join and criteria, it works fine. For example, I can either return deliveries that have corresponding locations or don't have corresponding invoice records. But I can't return records that have locations and don't have invoice records. I get 0 records in the result set. I'm sure I can get this to work if I use something like d.loc_id in ( select ... from location ..., but I want to avoid that if possible because I think that subquery retrieves the entire contents of the table for comparison. Does anyone have any idea why my query isn't working? Any help or ideas are greatly appreciated. First problem: you are mixing implicit CROSS JOINS and explicit LEFT JOINS. As of 5.0.12 that is a recipe for trouble. Here is a repaired version: select d.co_id, co.name, count(*) from company co INNER JOIN delivery d ON d.co_id = co.co_id left join invoice_detail id ON d.delivery_id = id.delivery_id LEFT JOIN location loc ON d.loc_id = loc.loc_id where d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name That may have just fixed it... Try it out and let us know. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
LOAD DATA INFILE and Extended ASCII....
Hey yall,
I have a delimited text file I'm trying to load into my db. The problem is
that for whatever reason (totally beyond my control) the line and field
delimiters include an Extended ASCII char (0xA0) like so:
05HIUT841427BLCA á Dell Computer áOne *Dell* Way, *Round
Rock*Texas 78682 áDELL á TX áEastern
Operations
á áá
Is there any way I can set the FIELD TERMINATOR and LINE TERMINATOR to
include this character so my LOAD DATA INFILE works?
I tried using a variable and mysql didn't like it:
set @field:=concat(' ',char(160),'\t');
set @line:=concat('\t',char(160),char(160),'\n');
load data infile
'C:\\Documents and Settings\\jpeavy1\\Desktop\\codes.20060109-
112400.txt'
into table
t.codes
fields
terminated by @field
optionally enclosed by ''
lines
terminated by @line
ignore
7 lines;
TIA,
jp
[mysql v5.0.16-nt on WinXP]
Re: left join record in one joined table and not in another
On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/11/2006 11:33:38 PM: I have three tables joined on key fields: delivery is joined with invoice_detail on delivery_id and with location on loc_id. I want to return records of deliveries that have corresponding records in the location table, but have no corresponding records in the invoice table. Here's the query I'm attempting to use: select d.co_id, co.name, count(*) from company co, delivery d left join ( invoice_detail id, location loc ) on ( d.delivery_id = id.delivery_id and d.loc_id = loc.loc_id ) where d.co_id = co.co_id and d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name If I take out either one of the table references in the left join and criteria, it works fine. For example, I can either return deliveries that have corresponding locations or don't have corresponding invoice records. But I can't return records that have locations and don't have invoice records. I get 0 records in the result set. I'm sure I can get this to work if I use something like d.loc_id in ( select ... from location ..., but I want to avoid that if possible because I think that subquery retrieves the entire contents of the table for comparison. Does anyone have any idea why my query isn't working? Any help or ideas are greatly appreciated. First problem: you are mixing implicit CROSS JOINS and explicit LEFT JOINS. As of 5.0.12 that is a recipe for trouble. Here is a repaired version: select d.co_id, co.name, count(*) from company co INNER JOIN delivery d ON d.co_id = co.co_id left join invoice_detail id ON d.delivery_id = id.delivery_id LEFT JOIN location loc ON d.loc_id = loc.loc_id where d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name That may have just fixed it... Try it out and let us know. That fixed it... Thank you! But I don't understand how. Isn't my implicit inner join the same as the explicit one you used? I read Join Syntax in the ref. manual. Is it related to the new order of precedence for the JOIN operator? Shawn Green Database Administrator Unimin Corporation - Spruce Pine The other difference with my original one was that I had both join tables in one left join section. I played with it a little bit. If I separate them like yours, it still works with the implicit cross/inner join... from company co, delivery d left join invoice_detail id on d.delivery_id = id.delivery_id left join location loc on d.loc_id = loc.loc_id Does this method also have a potential problem? Thanks, Vince
Re: question about sql security
thank you gleb. But,the sql securityd description in the manual is not particular. I mistaked the concept. I understand the limit is procedure limit but not database object limit. So,i believe this concept should more definitude if you add the example in this part of manual. - CREATE PROCEDURE p26 () SQL SECURITY INVOKER SELECT COUNT(*) FROM t // CREATE PROCEDURE p27 () SQL SECURITY DEFINER SELECT COUNT(*) FROM t // GRANT INSERT ON db5.* TO peter; // - - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 5:22 PM Subject: Re: question about sql security Hello. In my opinion, a good description can be found here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html wangxu [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
