How to encrypt Text and still be able to use full text search? 3rd Attempt ++
I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data infile and character set
Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal
Re: load data infile and character set
Caleb Racey wrote: Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? It is indeed buggy and badly documented. It depends on the current database's character set instead. Try this: SET NAMES utf8; SET character_set_database=utf8; LOAD DATA INFILE... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
On 26 Oct 2007 at 9:17, mos wrote: I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? Hi, This is quite a difficult one, and as usual in the field of security depends on how valuable the data is and how difficult you want it to be for an attacker to obtain it. If you let us know what type of data this is and how well it has to be protected, maybe we can help more. security reasons is a bit vague, but I can understand that you don't want to give too much away. I can say one thing though, in order for the data to be indexed by MySQL , it has to be in an unencrypted form somewhere in the database. There is no way I know to get around this, but I hope someone can correct me :) Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Caleb Racey wrote: Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust If you haven't already, issue this first: SET CHARACTER SET utf8; SET NAMES utf8; LOAD DATA INFILE ... b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
I don't think it will be any better to count distinct values. I think the query is just slow because the index lookups are slow. Is the 'word' column really 150 bytes? That's probably the culprit. How slow is this, by the way? 370k rows in one table, verifying the non-existence of index records in a 4M-row table with 150-byte index values... what does slow mean for your application? How big is the index for the 4M-row table (use SHOW TABLE STATUS)? Russell Uman wrote: There's no using distinct, but there is not exists, and in fact no rows are returned. Slow query log reports #Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 370220 EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 150 NULL 338451 Using index 1 SIMPLE t2 ref word word 150 t2.field 4 Using where; Using index; Not exists These are two search tables (hence the large key_len i believe), one with ~400K rows, one row per search term the other with ~4M rows, relating search terms to content. Perhaps I could optimize by doing a count(distinct) on each table and only running the expensive query if the counts don't match? Would I see any benefit by making these InnoDB tables? Thanks for your help with this! Baron Schwartz wrote: Hi, That is the right way, but if you show us the exact output of EXPLAIN we can help more. In particular, does it say Using distinct/not exists in Extra? Russell Uman wrote: howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing keys in two tables
Greetings! I have a problem that it seems would best be solved using subqueries. However, I am working on a server that is running MySQL 3.23.58, so subqueries are not available to me. What I am trying to do: I have two tables, each containing approximately 37,000 records. I want to compare the index field in Table A to the index field in Table B. I would like to see all records where the index in Table A does not exist in Table B. Thoughts? Suggestions? Much appreciated! -Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Aaron Fischer wrote: Greetings! I have a problem that it seems would best be solved using subqueries. However, I am working on a server that is running MySQL 3.23.58, so subqueries are not available to me. What I am trying to do: I have two tables, each containing approximately 37,000 records. I want to compare the index field in Table A to the index field in Table B. I would like to see all records where the index in Table A does not exist in Table B. Use an exclusion join: SELECT ... FROM tblA LEFT OUTER JOIN tblB ON col=col WHERE tblB.col IS NULL; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Aaron An exclusion join: SELECT a.col FROM a LEFT JOIN b ON a.col=b.col WHERE b.col IS NULL; PB Aaron Fischer wrote: Greetings! I have a problem that it seems would best be solved using subqueries. However, I am working on a server that is running MySQL 3.23.58, so subqueries are not available to me. What I am trying to do: I have two tables, each containing approximately 37,000 records. I want to compare the index field in Table A to the index field in Table B. I would like to see all records where the index in Table A does not exist in Table B. Thoughts? Suggestions? Much appreciated! -Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Thanks Peter and Baron, these both worked well. The left join on took .1919 seconds and the left outer join as took .1780 seconds. =) On Oct 26, 2007, at 11:37 AM, Peter Brawley wrote: Aaron An exclusion join: SELECT a.col FROM a LEFT JOIN b ON a.col=b.col WHERE b.col IS NULL; PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Aaron Fischer wrote: Greetings! I have a problem that it seems would best be solved using subqueries. However, I am working on a server that is running MySQL 3.23.58, so subqueries are not available to me. What I am trying to do: I have two tables, each containing approximately 37,000 records. I want to compare the index field in Table A to the index field in Table B. I would like to see all records where the index in Table A does not exist in Table B. You want to select from A, not from B, yes? SELECT a.* FROM foo AS a LEFT OUTER JOIN bar AS b ON a.index_field = b.index_field WHERE b.index_field IS NULL; brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Aaron Fischer wrote: Thanks Peter and Baron, these both worked well. The left join on took .1919 seconds and the left outer join as took .1780 seconds. They are synonymous in MySQL. The only difference is 6 extra characters in the query text. The difference was probably due to caches. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Rob Wultsch wrote: On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: Aaron Fischer wrote: Thanks Peter and Baron, these both worked well. The left join on took .1919 seconds and the left outer join as took .1780 seconds. They are synonymous in MySQL. The only difference is 6 extra characters in the query text. The difference was probably due to caches. Baron Out of curiosity which cache are you referring to? 3.23 does not have query cache. Are you referring to key, table, or some other cache? (or all of the above?) I would think that the key and table cache would not be effected by the addition of an optional word that does not affect how the query is processed... Where is my understanding incorrect or incomplete? I'm referring to the operating system's caches. Really such a small difference isn't significant anyway -- who knows what was happening on the server at that time. But running a query, then running it again, will often be at least a tiny bit faster the second time because the data has been read from disk into the OS caches. The query cache (in newer versions) wouldn't help because the queries aren't byte-for-byte identical. If one did a proper benchmark on these two queries and found any difference at all aside from six extra bytes, I'd be very surprised. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
Aaron The left join on took .1919 seconds and the left outer join as took .1780 seconds. Caching :) PB - Aaron Fischer wrote: Thanks Peter and Baron, these both worked well. The left join on took .1919 seconds and the left outer join as took .1780 seconds. =) On Oct 26, 2007, at 11:37 AM, Peter Brawley wrote: Aaron An exclusion join: SELECT a.col FROM a LEFT JOIN b ON a.col=b.col WHERE b.col IS NULL; PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
Ian, At 09:36 AM 10/26/2007, you wrote: On 26 Oct 2007 at 9:17, mos wrote: I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? Hi, This is quite a difficult one, and as usual in the field of security depends on how valuable the data is and how difficult you want it to be for an attacker to obtain it. If you let us know what type of data this is and how well it has to be protected, maybe we can help more. security reasons is a bit vague, but I can understand that you don't want to give too much away. The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. I can say one thing though, in order for the data to be indexed by MySQL , it has to be in an unencrypted form somewhere in the database. There is no way I know to get around this, but I hope someone can correct me :) I hope so too. :) There are quite a few databases out there that have transparent encryption (Blowfish, AES etc.) and I'm wondering why MySQL haven't implemented it, especially now with the new laws that make the company liable for security breaches on the web. On the other databases I've used, I haven't noticed any speed decrease if the table is encrypted. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing keys in two tables
On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: Aaron Fischer wrote: Thanks Peter and Baron, these both worked well. The left join on took .1919 seconds and the left outer join as took .1780 seconds. They are synonymous in MySQL. The only difference is 6 extra characters in the query text. The difference was probably due to caches. Baron Out of curiosity which cache are you referring to? 3.23 does not have query cache. Are you referring to key, table, or some other cache? (or all of the above?) I would think that the key and table cache would not be effected by the addition of an optional word that does not affect how the query is processed... Where is my understanding incorrect or incomplete?
Re: load data infile and character set
Caleb Racey wrote: On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: Caleb Racey wrote: It is indeed buggy and badly documented. It depends on the current database's character set instead. Try this: SET NAMES utf8; SET character_set_database=utf8; LOAD DATA INFILE... Baron Thanks for the suggestion I'm afraid i get the same behaviour when i try this approach accented values still truncate at the accent when i use load data infile. OK, the next suggestion is to use the 'binary' character set. By the way, I accidentally omitted quotes above; I should have typed SET character_set_database='utf8'; You should verify your connection's character sets with SHOW VARIABLES LIKE '%character%'; Try it with 'binary' and see if that works. Baron thanks again I'm afraid 'binary' achieved the same result as did using utf8 with quotes. I'm out of ideas, then. I wish I could help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
[EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Rent a T1 line for $500/mo and charge customers what the data is worth. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Rent a T1 line for $500/mo and charge customers what the data is worth. Baron I also agree, however for the sake of argument could we assume that the order of the wording in the entry probably imparts a significant amount of it's value? If that is the case, I would think creating a second column of unencrypted text (with a full text index) which would be nothing more than copy of the the text with the words in a random order might provide a bit of the protection that the user is looking for.
Re: Concat alternative
On 10/24/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Gerard wrote: Currently I am running a concat statement to combine a field with a user name and domain to create and email address. In testing it looks like running the concat is a very slow command to run. The select statement currently looks like this. select concat(user,'@',domain),servername,port from database where concat(user,'@',domain)='[EMAIL PROTECTED]'; Why do CONCAT() twice? Couldn't you just do: WHERE user = 'username' AND domain = 'domain.com' Or am i missing something? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This is done because the application is not flexible. I can only put one condition in which goes for the where and select statement.
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
At 12:31 PM 10/26/2007, you wrote: [EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Out of curiosity, why can't you use an ISP if the table is encrypted on a dedicated server (using a database other than MySQL, say SQLite)? The pw would be entered via VPN and is not stored in memory or in any file. The data on disk is always encrypted and the selected rows are only decrypted in memory on the fly. The ISP administrator never sees the pw or the unencrypted data. Rent a T1 line for $500/mo and charge customers what the data is worth. Yes, that will solve the problem. I'd have to incur more up front costs but security would be under my control. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
At 01:47 PM 10/26/2007, you wrote: On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: mos wrote: The data is quite valuable because there is a lot of competition in this particular marketplace and my competitors would like to get their hands on it. I've spent 5 years writing the software and generating the data. Let's say for the sake of argument the data is worth $1 million. How do I stop my competitor from bribing some flunky at the ISP into turning over the backup of my data or just e-mailing the MySQL password file to him? Also I don't want anyone at the ISP viewing the data or changing it because I'd be liable for any data errors. Host the machines in-house. I think that could be done for less than a million bucks for a smallish setup. Of course, I've only ever been a bystander with that sort of project, so the figures may be a lot higher than I'm guessing. For instance, you'd want a beefy connection installed, of course. And then there's the salary for someone to administer to everything. I agree. If you're using shared hosting, forget about encryption. Physical access to the machines ALWAYS trumps every other kind of security, so you can't do what you're trying to do (secure data in an insecure environment). Rent a T1 line for $500/mo and charge customers what the data is worth. Baron I also agree, however for the sake of argument could we assume that the order of the wording in the entry probably imparts a significant amount of it's value? If that is the case, I would think creating a second column of unencrypted text (with a full text index) which would be nothing more than copy of the the text with the words in a random order might provide a bit of the protection that the user is looking for. Good point. I hadn't thought of that. :) I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
If you are going to rely on obfuscation to protect valuable data, you might want to consider not posting the particular method you will use on a public mailing list. I think any method you implement will lower the overall security of the system. But, if you must search for encrypted text, you could have another representation of the text salted and hashed word for word. Then salt and hash each search word and search for it in the hashed text. You're still leaking information about word popularity if you do this which may help a determined attacker. - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: mos [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, October 26, 2007 3:54:11 PM Subject: Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++ I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. How much will obfuscation save you? Are you saving nickels and dimes to protect millions of dollars? I've seen people get burned by rolling their own encryption (I could tell you a great war story about a consultant I worked with who invented encryption for SSNs in a database). An insurance policy is something else to consider. Heck, buy the insurance and do weak obfuscation, then get the insurance money and go to Mexico. ... I could put strychnine in the guacamole... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. How much will obfuscation save you? Are you saving nickels and dimes to protect millions of dollars? I've seen people get burned by rolling their own encryption (I could tell you a great war story about a consultant I worked with who invented encryption for SSNs in a database). An insurance policy is something else to consider. Heck, buy the insurance and do weak obfuscation, then get the insurance money and go to Mexico. ... I could put strychnine in the guacamole... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Install on OS X Leoperd
Hi There, Has anyone been successful installing and running MySQL (current version) on the new Mac OS (Leopard)? __ Craig Hoffman iChat/AIM: m0untaind0g __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Install on OS X Leoperd
This worked -- To fix the mySQL socket error, start mysql in terminal by doing this... sudo /usr/local/mysql/bin/safe_mysqld close Terminal, then open it again and put in these two... sudo mkdir /var/mysql/ sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock Restart Apache Should work until they fix the pref pane. __ Craig Hoffman iChat/AIM: m0untaind0g __ On Oct 26, 2007, at 3:59 PM, Craig Hoffman wrote: Hi There, Has anyone been successful installing and running MySQL (current version) on the new Mac OS (Leopard)? __ Craig Hoffman iChat/AIM: m0untaind0g __ -- 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]
Counting number of associated many-to-many items
Hello! I have three tables, mapping out a n:n relationship of authors and the books they worked on: table 1: authors (id, name) table 2: authorships (author_id, book_id) table 3: books (id, name, bestseller tinyint) Here's two different queries I want to run: 1. Select each author, and how many books he has worked on. 2. Select each author, and how many bestseller books (bestseller = 1) he has worked on. Not exactly sure how to do this, can someone help me out with this? Thank you, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting number of associated many-to-many items
Robert MannI wrote: Hello! I have three tables, mapping out a n:n relationship of authors and the books they worked on: table 1: authors (id, name) table 2: authorships (author_id, book_id) table 3: books (id, name, bestseller tinyint) Here's two different queries I want to run: 1. Select each author, and how many books he has worked on. SELECT a.id, a.name, COUNT(b.id) AS oeuvre FROM authors AS a LEFT JOIN authorships AS asp ON asp.author_id = a.id LEFT JOIN books AS b ON asp.book_id = b.id GROUP BY a.id; This will also take into account books whose authorship is shared. 2. Select each author, and how many bestseller books (bestseller = 1) he has worked on. SELECT a.id, a.name, COUNT(b.id) AS bestsellers FROM authors AS a LEFT JOIN authorships AS asp ON asp.author_id = a.id LEFT JOIN books AS b ON asp.book_id = b.id WHERE b.bestseller = 1 GROUP BY a.id; Only added the WHERE clause and changed the 3rd column name. HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
