encrypt data
Dear Friends is their any way to encrypt data saved to an mysql server? Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Executing multiple queries at a time in Query Browser
Does anyone know how to separate queries so that I can run multiple insert statements at once? For example, from within MySQL Query Browser, I want to load a file containing multiple inserts and then run it. It errors however on trying to run the following queries: insert into project_type (project_type_id, code, name) values (1, 'W', 'Web Site'); insert into project_type (project_type_id, code, name) values (2, 'Y', 'Interactive Application / Flash'); insert into project_type (project_type_id, code, name) values (3, 'B', 'Brochure'); The seperator ;¹ doesn¹t seem to be enough. Is there some other command that I need to add between queries? Thanks, Colin
Re: How to compose index?
Hello. In my opinion, something changes in how MySQL handles the query when you overrun the 100,000 number of rows. If EXPLAIN shows the difference for queries with LIMIT below and higher than 100,000? Do you have key_buffer (innodb_buffer_pool_size) big enough? Please send you queries to the list as well. thanks.. now it uses index by default.. it uses now idx_delete_phoneoffice_assigneduserid.. and it is fast for LIMITS up to 100,000 but.. after 100,000 it takes more than a second.. at LIMIT 70,20 it takes 9 seconds... ehhh Marko Knezevic 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]
Re: Critical Error!
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html Zan wrote: Hello all! I was going through my forums yesterday, when suddenly I got a critical error. The message was 'could not connect to database' so I went to restart MySQL by going into /usr/local/mysql/var/ and sending a kill `cat mydomain.com.pid`. and then backed up into the /bin/ and sent a './mysqld_safe ' to start up the server again. But this time it said 'mysql ended 0'.. I then I checked through phpMyAdmin and got a 2002 error : could not connect through .sock file. I checked for that and it wasn't there (because mysql didn't start up properly I assume). Why would this suddenly happen? Thanks for any advice in advance! Version : 4.0.36 Jail : FreeBSD 4.8 -Zan -- 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: full text table query issues
Hello.
Please could you provide a repeatable test case for this issue? FT
search works fine for me even if one of the columns has empty values.
'Yemi Obembe wrote:
using the a sql statement like ds:
select *, match(url, title, comment) against ('movies') as score from dir
where match(url, title, comment) against ('movies')
where dir is a fulltext table of url, titlke and comment
i however found out that if the comment column is empty it will return an
empty result even if there is a result in the url and/or title column. how
can i get around tis?
-
A passion till tomorrow,
Opeyemi Obembe | ng.clawz.com
-
Yahoo! DSL Something to write home about. Just $16.99/mo. or less
--
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: encrypt data
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html Luiz Rafael Culik Guimaraes wrote: Dear Friends is their any way to encrypt data saved to an mysql server? Regards Luiz -- 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]
SQL HAVING statement ?
Hi, Im having a bit of a problem with the following query: SELECT CONCAT(people2.First_Name, , people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = FACULTY HAVING zNAME LIKE ORDER BY events.date_start DESC Specifically, the HAVING statement Im trying to perform a search against the concatenated result of first_name/last_name and not getting very far. Can anyone point me in the right direction ??? M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL HAVING statement ?
- Original Message - From: m i l e s [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Saturday, November 26, 2005 6:39 PM Subject: SQL HAVING statement ? Hi, Im having a bit of a problem with the following query: SELECT CONCAT(people2.First_Name, , people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = FACULTY HAVING zNAME LIKE ORDER BY events.date_start DESC Specifically, the HAVING statement Im trying to perform a search against the concatenated result of first_name/last_name and not getting very far. Can anyone point me in the right direction ??? First of all, when posting a question like this it is very hard to help if you don't supply a specific error message - if there is one! - or at least describe what is wrong with the answer you are getting, i.e. how it differs from what you wanted. Second, you're using the HAVING clause incorrectly in your query. HAVING only works with GROUP BY, i.e. you MUST have a GROUP BY to be able to use HAVING. (Note: You can have a GROUP BY without using HAVING but you cannot use HAVING unless a GROUP BY is present.) Since you have no GROUP BY, there is no way that this query will ever work. Now, before you try to add a GROUP BY, I don't think you need GROUP BY _or_ HAVING to fix your query! From the nature of your question, I'm going to assume that you are quite new to SQL; please forgive me if I'm wrong. In a nutshell, GROUP BY is used to cause summaries of data and HAVING is used much like a WHERE clause but for the _groups_ formed by GROUP BY. I don't think you need either one. Here are a few quick examples of GROUP BY and HAVING to illustrate their use. Imagine a table that contains employee records, with one row for each employee. Each row contains an employee number, a last name, a first name, the number of the department for which the employee works, the employee's sex and annual salary. EMPNOFIRSTNMELASTNAMEWORKDEPTSEXSALARY 1Bob SmithA00M 5.00 2Mary JonesB01F 55000.000 3Fred GreenB01M 48000.00 4EdnaMurphy A00F 51000.00 5James Willis B01M 35000.00 6IngridSteele C01F 62000.00 This query would display all of the rows of the table: select empno, lastname, workdept, sex, salary from employee EMPNOFIRSTNMELASTNAMEWORKDEPTSEXSALARY 1Bob SmithA00M 5.00 2Mary JonesB01F 55000.000 3Fred GreenB01M 48000.00 4EdnaMurphy A00F 51000.00 5James Willis B01M 35000.00 6IngridSteele C01F 62000.00 This query would report the total salary paid out to all people in each department but would omit department C01: select workdept, sum(salary) as sum_salary from employee where workdept 'C01' group by workdept WORKDEPTSUM_SALARY A00101000.00 B01138000.00 See? The query is doing a summarization: instead of showing the details of each individual in each department, we show only the total salary paid to the people in the department and the department number. (If we omitted the department number from the SELECT clause but left it in the GROUP BY clause, the query would work but would show only the SUM_SALARY column; we wouldn't know which department was associated with each sum, making the result pretty useless.) Now, let's add a HAVING. Suppose we only wanted to show a department in the result set if the total salary paid out to the department exceeded 12.00. Here's the query: select workdept, sum(salary) as sum_salary from employee where workdept 'C01' group by workdept having sum(salary) 12 WORKDEPTSUM_SALARY B01138000.00 The HAVING is similar in concept to the WHERE but it applies to the rows that result from the GROUP BY. This query does the exact same work as the previous query but, this time, an additional step is done: MySQL looks at the result so far, then applies the HAVING and realizes that the row for department A00 needs to be omitted from the final result. Okay, if you followed me this far, I think you'll agree that you're not doing any summarization and that you don't need to use HAVING (or GROUP BY) in the first place. Therefore, file your new knowledge about GROUP BY and HAVING away
Re: encrypt data
Also, do you know if there is any way at all to do the tiniest amount of research on my own to find out even the most basic information about MySQL without bothering an entire worldwide mailing list with my trivial questions?
Re: SQL HAVING statement ?
Rhino, Thanks for all the info. It was as simple as %% in the statement... DUH! I can't believe I missed that. Sorry to have bothered. Thanks anyway. M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL HAVING statement ?
Rhino [EMAIL PROTECTED] wrote: Second, you're using the HAVING clause incorrectly in your query. HAVING only works with GROUP BY, i.e. you MUST have a GROUP BY to be able to use HAVING. (Note: You can have a GROUP BY without using HAVING but you cannot use HAVING unless a GROUP BY is present.) Since you have no GROUP BY, there is no way that this query will ever work. I disagree. SELECT CONCAT(people2.First_Name, ' ', people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = 'FACULTY' AND zNAME LIKE '%' ORDER BY events.date_start DESC This wouldn't work. select userfirstnm, userlastnm, concat(userfirstnm, ' ',userlastnm) as zname from FMS.WebUsers_sql where zname Like %brett% results in Unknown column 'zname' in 'where clause'. There where must be on the real column name. Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html However, select userfirstnm, userlastnm, concat(userfirstnm,' ',userlastnm) as zname from FMS.WebUsers_sql having zname Like %brett% works. Having must come after any grouping, but a group by is not required. Per the documentation A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. (Standard SQL requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.) The concat is the aggregate function. However, it works on just aliases also. SELECT userfirstnm as first, userlastnm, from FMS.WebUsers_sql having first Like %brett% What made this work was simply using the % he had forgotten SELECT CONCAT(people2.First_Name, , people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = FACULTY HAVING zNAME LIKE %% ORDER BY events.date_start DESC -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
Re: Executing multiple queries at a time in Query Browser
Colin Shreffler [EMAIL PROTECTED] wrote on 11/26/2005 04:44:26 PM: Does anyone know how to separate queries so that I can run multiple insert statements at once? For example, from within MySQL Query Browser, I want to load a file containing multiple inserts and then run it. It errors however on trying to run the following queries: insert into project_type (project_type_id, code, name) values (1, 'W', 'Web Site'); insert into project_type (project_type_id, code, name) values (2, 'Y', 'Interactive Application / Flash'); insert into project_type (project_type_id, code, name) values (3, 'B', 'Brochure'); The seperator Œ;¹ doesn¹t seem to be enough. Is there some other command that I need to add between queries? Thanks, Colin Query Browser has two types of areas to put SQL statements into query tabs and script tabs. The area that opens by default is a query tab and only allows for single statements. A script tab allows for sequences of statements, like what you want to do. File-New Script Tab or File-Open Script DISCLAIMER: I am not defending this design, just helping you to work with it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: encrypt data
Jake Peavy [EMAIL PROTECTED] wrote on 11/26/2005 07:54:22 PM: Also, do you know if there is any way at all to do the tiniest amount of research on my own to find out even the most basic information about MySQL without bothering an entire worldwide mailing list with my trivial questions? RTFM? http://dev.mysql.com/doc/ Search the archives of the mailing list? http://lists.mysql.com/ Search the forums? http://forums.mysql.com/ STFW? http://www.yahoo.com/ http://www.google.com/ http://www.msn.com/ The research is easy as all of these sources have search features. If you come to an actual question that you could not answer from any of these sources, you are most welcome to bother us. I think we all remember what it's like to be newbies and most of us didn't have the web or emails or much else to rely on to learn from. We are happy to help but we do hope for a minimum of effort from the person asking the questiono to find the answer on their own first. Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for life. (Chinese proverb) We give away a lot of fish on this list but I think most of us try to teach as we go. See you soon! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SQL HAVING statement ?
- Original Message - From: Brett Harvey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 26, 2005 9:45 PM Subject: Re: SQL HAVING statement ? Rhino [EMAIL PROTECTED] wrote: Second, you're using the HAVING clause incorrectly in your query. HAVING only works with GROUP BY, i.e. you MUST have a GROUP BY to be able to use HAVING. (Note: You can have a GROUP BY without using HAVING but you cannot use HAVING unless a GROUP BY is present.) Since you have no GROUP BY, there is no way that this query will ever work. I disagree. SELECT CONCAT(people2.First_Name, ' ', people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = 'FACULTY' AND zNAME LIKE '%' ORDER BY events.date_start DESC This wouldn't work. select userfirstnm, userlastnm, concat(userfirstnm, ' ',userlastnm) as zname from FMS.WebUsers_sql where zname Like %brett% results in Unknown column 'zname' in 'where clause'. There where must be on the real column name. Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html Okay, I admit I was crossing my fingers a bit with that answer: I assumed that the alias would work in the WHERE; apparently, I was overly optimistic. However, select userfirstnm, userlastnm, concat(userfirstnm,' ',userlastnm) as zname from FMS.WebUsers_sql having zname Like %brett% works. Having must come after any grouping, but a group by is not required. Per the documentation A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. (Standard SQL requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.) The concat is the aggregate function. However, it works on just aliases also. SELECT userfirstnm as first, userlastnm, from FMS.WebUsers_sql having first Like %brett% Okay, once again I stand corrected. In my own defense, I should say that I am primarily a DB2 user and only use MySQL sporadically. In 20 years of working with DB2, I have never seen a query with a HAVING but no GROUP BY work. Since MySQL behaves like DB2 in virtually every case I've seen, I just assumed that rule also applied to MySQL. Apparently, I'm wrong in this case. What made this work was simply using the % he had forgotten SELECT CONCAT(people2.First_Name, , people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = FACULTY HAVING zNAME LIKE %% ORDER BY events.date_start DESC Well, at least I got _that_ right ;-) Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: How to import data into a table?
Hello folks, What is the best way to import an excel spreadsheet, or text file for that matter into a table? Is there a way to do this with PHPMyAdmin? Thank you so much for your kind help. Joe
