A little help with this select?
Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? Maybe I'm doing something wrong. THANKS! Best Regards, -- Tyler Longren J.D. Web Services, L.C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little help with this select?
On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? Maybe I'm doing something wrong. THANKS! Best Regards, -- Tyler Longren J.D. Web Services, L.C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Tyler The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. In terms of the syntax of your query, if you wish to return records from the worker table when the webprojectassign table is empty, you either have to use a LEFT JOIN (which willl include all records from workers whether they have a matching workerid in webprojectassign or not) or remove the condition from your query above. In both cases you are no longer doing what your query originally intended (which I assume is to list all workers currently assigned to projects). If you are just testing, I would suggest that a better bet would be to put dummt entries into the empty table rather than hacking the query. Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A little help with this select?
Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French characters ok with mysql in Windows, nok with mysql in Linux
From: Willy Dockx [EMAIL PROTECTED] Sent: Monday, January 05, 2004 7:23 PM Subject: RE: French characters ok with mysql in Windows, nok with mysql in Linux Hello, etc/sysconfig/i18n contains: LANG=en_US.UTF-8 SUPPORTED=nl_BE.UTF-8:nl_BE:nl:en_US.UTF-8:en_US:en SYSFONT=latarcyrheb-sun16 Is that ok? I can't remenber what was my conf, but google is you friend ... http://groups.google.fr/groups?hl=frie=UTF-8oe=UTF-8q=french+redhat+i18nsa=Ntab=wgmeta= http://groups.google.fr/groups?hl=frlr=ie=UTF-8oe=UTF-8q=accent+redhat+i18n Did you try to connect directly to MySQL through a standard MySQL client and make an insert with é à ù ... and see if the select is ok after the insert ? What concerns the 'driver connection url' : should I leave 'useUnicode=truecharacterEncoding=UTF-8' in it? Don't know, i'm not using java ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little help with this select?
!= and both mean not equal. To find workers with projects, you join the worker table to the webprojectassign table. To get all workers whether they have a project or not, you use LEFT JOIN, as Rory McKinley suggested. With a LEFT JOIN, missing rows from webprojectassign (the table on the right) are filled with NULLS. So, to find workers without projects, you do a LEFT JOIN and look for the NULLs, like this: SELECT worker.fname, worker.lname, worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC In this case, as the workerid column name is the same in both tables, I could have used USING (workerid) in place of the ON clause. Michael Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A little help with this select?
On 6 Jan 2004 at 9:31, Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Oooops...my apologies to all...I guess it helps if one actually reads properly. In this case, the solution to Tyler's problem will be a left join. The query should look something like this : SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC This will retrieve all the worker details where there are no matching entries in the webprojectassign table. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Re: FULLTEXT across two tables
You want each user with a match to show up once, regardless how many art
items match, right? How about
SELECT * from users INNER JOIN art ON (users.user_id = art.user_id)
WHERE MATCH (nickname, name_first, name_last, name_middle, city,
state, zip_area, country, bio_short, bio_desc) AGAINST ('kansas')
OR MATCH (title, medium, commentary) AGAINST ('kansas')
GROUP BY users.user_id;
Michael
Ladd J. Epp wrote:
I just tried SELECT DISTINCT and still the same problem -- returning all
entries for a matching user_id in art for each user where the word 'kansas'
is specified... any other thoughts? Thanks, lje
On Saturday 03 January 2004 16:58, you wrote:
Hi Ladd,
How about SELECT DISTINCT?
Hope that helps.
Matt
- Original Message -
From: Ladd J. Epp
Sent: Saturday, January 03, 2004 11:39 AM
Subject: FULLTEXT across two tables
Hello,
I would like to do a FULLTEXT search across two tables. I run an
artist
website, so I need to search across the user list and the users'
associated
art pieces list. I've come up with this query (fulltext indexes for
these
precise values have been created on both tables):
SELECT * from users INNER JOIN art ON (users.user_id = art.user_id)
WHERE MATCH (nickname, name_first, name_last, name_middle, city,
state,
zip_area, country, bio_short, bio_desc) AGAINST ('kansas')
OR MATCH (title, medium, commentary) AGAINST ('kansas');
This query is very close to what I need, except that it returns
redundant
rows. For example, if users.state='kansas' it returns every record
from art
where users.user_id=art.user_id. How do I return records that have
'kansas'
in either users, or art, or both, only once? I think a UNION might
help me
here, but my provider uses MySQL v.3.22 so that is not an option...
I apologize if I am not being clear about something ... If you need
more
detail I would be happy to provide it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is code and what's not?
your password should replace password Your hostname should replace `hostname` Hope this helps Thanks Emery - Original Message - From: Lost Idols [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 06, 2003 00:06 Subject: What is code and what's not? I found a script on this help page, that should be used when giving the root user a password, but I really don't understand what of this is words and what is just an example. /usr/local/mysql/bin/mysqladmin -u root password password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password Where in this script am I supposed to write my password and where is the word supposed to acctually be written as the word password? And what about hostname? Is that a command or should I write the host name of my computer? Since I'm a newbie it's hard to understand. Can someone please mark this for me? Is this the right way? /usr/local/mysql/bin/mysqladmin -u root password xyxyxyxy /usr/local/mysql/bin/mysqladmin -u root -h `this_is_me` password xyxyxyxy Weird question maybe... but I guess I'm not the only one here. It would've been easier with underscored or different colours here... I guess ;-) _ Hitta rätt köpare på MSN Köp Sälj http://www.msn.se/koposalj -- 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: MYSQLDump loses connection on large dump.
Pete, what does SHOW VARIABLES LIKE '%timeout%'; say? Does the MNRD mysqld server crash? Anything in the .err log? What do you have as max_packet_size in my.cnf? Could row: 13154861 be bigger than that? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Pete McNeil [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 06, 2004 1:27 AM Subject: MYSQLDump loses connection on large dump. Thanks in advance for any help. I'm using mysqldump to create snapshots of slave database servers. I am upgrading from 4.0.14 on Redhat 8 to 4.0.17 Fedora Core 2. I have a pair of 4.0.14 boxes MNR6 master -- MNR7 slave. I have a pair of 4.0.17 boxes MNRC master -- MNRD slave. In moving to the new boxes I have set up this replication sequence: MNR6-MNR7-MNRC-MNRD I am using this command to make backups at the slaves: mysqldump -A -Q --opt --user=xx --password=xx | gzip /mnt/drive-u/MySQL-Backups/`date -I`.mnrd-backup.sql.gz This is done in a script once per day on the slave of a pair. /mnt/drive-u is a samba file link to a central server for backups. When I run this script on MNR7 it runs flawlessly. When I run this script on MNRD I get the following error: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `RuleHistogram` at row: 13154861 RuleHistogram is in the largest InnoDB table. The configuration of MNR6 and MNR7 is underpowered for the application, but it works. One 2.4GHz P4, 1 GB Ram, 2IDE HD in raid 1. MNRC and MNRD were built for the application and have much better hardware: Two 2.4GHz XEON, 4GB Ram, 4 SATA Drives Raid 10 via 3Ware Escalade. MNRC and MNRD respond nicely to queries and show no signs of trouble except for the above. My Question: Why would the more powerful servers with the more advanced software stall during a mysqldump operation when the less powerful survers successfully perform an identical operation on identical data without a hitch? What can I do to solve this problem with mysqldump? (BTW: I just sat through another failure - watching top, mysqld goes to idle along with mysqldump... they wait a few seconds in this condition, then the error occurs and the script stops. The mysql server remains responsive to queries before and after the operation. No errors are reported in the .err file.) Thanks again in advance! _M Pete McNeil (Madscientist) President, MicroNeil Research Corporation Chief SortMonster, www.SortMonster.com VOX: 703-406-2016 FAX: 703-406-2017 -- 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: mediumtext crash on strings 1MB?
Chris, you should set max_packet_size in my.cnf. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Chris Seidel [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 05, 2004 11:07 PM Subject: mediumtext crash on strings 1MB? Hello, I have a table in which one of the column types has been declared as mediumtext to hold blocks of text averaging 250k in size. A mediumtext datatype should be able to hold 16 million characters. However, I found my inserts failing when some of my strings exceeded 1 MB (e.g. a string of 1125921 bytes fails, while a string of 1009684 bytes succeeds). I've been using this table for months, but recently some of my records grew to greater than 1 MB, and suddenly my inserts stopped working. Is there a bug with mediumtext such that it holds less than the advertised max? I'm using MySQL 3.23.56 on Linux RedHat 8, and have tried inserts via Perl DBI as well as via SQL command line. -Chris Seidel -- 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: innodb_buffer_pool_size / *_buffer_size relation
Roman, set-variable = sort_buffer_size=1M set-variable = read_buffer_size=1M set-variable = read_rnd_buffer_size=1M are relevant also for InnoDB. Only a small key_buffer for MyISAM is normally needed. MySQL's system tables are always MyISAM type, and in some rare cases MySQL may create MyISAM type temporary tables when processing DISTINCT or ORDER BY. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Roman Neuhauser [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 05, 2004 4:20 PM Subject: innodb_buffer_pool_size / *_buffer_size relation I've been trying to tune a MySQL installation a bit, and have a few questions regarding relations/interactions between InnoDB and MySQL when it comes to the various buffers... I wanted this mail to be more specific, but given the lack of info in the manual, I basically don't have much cannon fodder. My first question is: what is the interaction between MySQL's *_buffer_size and InnoDB's innodb_buffer_pool_size? IOW, are *_buffer_size tunables applicable to InnoDB tables? Would I need to tune e. g. key_buffer_size in a server with only InnoDB tables? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- 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: Pulling numbers out of a column for a query
Hal Vaughan wrote: I've found an odd problem in queries. I have a lot of data regarding vehicle speeds in a survey. All the data is in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43 miles per hour in a 55 miles per hour zone. 80/55 means we have a serious speed demon, going 80 in a 55 zone. I need to be able to pull out data depending on speeds in relation to the speed limit, for example, find out how many are less than 55/55 or 45/45, or how many are more than 5 miles over (like 61/55 or 32/25). I know how to use regular expressions and can SELECT only tables with the data in this form (many are easier and have an extra column filled in with the speed in relation to the speed limit), so I can pull out all the records with the formatting I'm discussing. Is there any way, within a SELECT, to pull out the numbers and subtract them? For instance, if a record has 45/55, I'd like to be able to separate them into 45 and 55 and subtract them, like 45-55 to get -10, which tells me the speed is 10 miles under the limit. I can program around this with Perl (another topic, which I'm not raising here), but that means selecting data, putting it into a table and going through each record individually, which will take MUCH longer than doing it by a SELECT statement within MySQL. Thanks for any help or ideas. Hal First, you really ought to separate your data into two columns, say clocked and speed_limit. Squeezing two values into one column is the cause of your problem. That said, you can do what you want with string functions. See http://www.mysql.com/doc/en/String_functions.html. You don't give the name of the table or the column, so I'll pretend they are 'speeds' and 'ratio', respectively. You need to find the '/', then separate accordingly and subtract. Like this: SELECT LEFT(ratio,INSTR(ratio,'/')-1) - SUBSTRING(ratio,INSTR(ratio,'/')+1) FROM speeds; Alternatively, you could fix the table. Something like: ALTER TABLE speeds ADD clocked INT, speed_limit INT; UPDATE speeds SET clocked = LEFT(ratio,INSTR(ratio,'/')-1), speed_limit = SUBSTRING(ratio,INSTR(ratio,'/')+1); Then your query becomes SELECT clocked - speed_limit FROM speeds; Hope that helps. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql error
Don, the InnoDB text below is only a help message, not an error, and is not dangerous. It is not associated with the timeout problems you have on Fedora. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Don Matlock [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 06, 2004 2:07 AM Subject: Mysql error Hi all, Larry Brown tried to help me out with this one (much appreciated Larry), anyways, when I start up fedora I get this error from mysql that says it has timed out...it still works, but this error shows up in the mysql logs: 040104 18:38:37 mysqld started Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html /usr/libexec/mysqld: ready for connections Everytime I restart fedora this is the log that is made. Mysql is still working as my phpBB2 dateabase is still functioning...but it is annoying. When I set fedora to run at run lvl 3 instead of 5...Mysql and apache stop working all together (the reason I know this is I wanted to stop X from starting every time I booted up...wanted it to boot into a text start up). Anyways...anythoughts on how I might be able to remedy this error...Larry suggested I check the rc.d files and see if mysql is pre-maturely starting...but I really don't know what to look for in those areas:( Anyways...any thoughts are greatly appreciated. Don -- 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: A little help with this select?
Rory, that's awesome. Exactly what I needed. After reading your first reply I wrote a query that was very similar to the one you just posted (included below). It didn't quite work right however. It was still a lot closer than I was before. Thanks again! SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC Tyler On Tue, 2004-01-06 at 02:22, Rory McKinley wrote: On 6 Jan 2004 at 9:31, Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Oooops...my apologies to all...I guess it helps if one actually reads properly. In this case, the solution to Tyler's problem will be a left join. The query should look something like this : SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC This will retrieve all the worker details where there are no matching entries in the webprojectassign table. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bet the Business
I've been playing with MySQL for a bit now (and it is playing) and I'm using it for a number of personal sites (ie not many users, and only I really post stuff). I've also adopted it at work, and we use it to dump Lotus Domino information into for more structured reporting. All is good. In the near future we face making a decision for a database backend, and the logical choice for us is MSSQL as we have one server already for Great Plains - but it would seem MySQL is much cheaper. This would be a line of business we application for around 200 people. Obviously, the details are vague here, I was just wondering if anyone had any stories, personal ones, rather than the press announcements, of adopting MySQL for line of business, critical stuff - what it involved, how it went, what issues they faced in getting it accepted and so on. I'm genuinly interested. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A little help with this select?
Hi Tyler You're welcome..I just wish I had got it right the first time :). On 6 Jan 2004 at 3:15, Tyler Longren wrote: Rory, that's awesome. Exactly what I needed. After reading your first reply I wrote a query that was very similar to the one you just posted (included below). It didn't quite work right however. It was still a lot closer than I was before. Thanks again! SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC Tyler On Tue, 2004-01-06 at 02:22, Rory McKinley wrote: On 6 Jan 2004 at 9:31, Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Oooops...my apologies to all...I guess it helps if one actually reads properly. In this case, the solution to Tyler's problem will be a left join. The query should look something like this : SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC This will retrieve all the worker details where there are no matching entries in the webprojectassign table. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pulling numbers out of a column for a query
On Mon, 5 Jan 2004, Hal Vaughan wrote: I've found an odd problem in queries. I have a lot of data regarding vehicle speeds in a survey. All the data is in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43 miles per hour in a 55 miles per hour zone. 80/55 means we have a serious speed demon, going 80 in a 55 zone. I need to be able to pull out data depending on speeds in relation to the speed limit, for example, find out how many are less than 55/55 or 45/45, or how many are more than 5 miles over (like 61/55 or 32/25). mysql SELECT SUBSTRING_INDEX(speed, '/', 1) AS clocked, - SUBSTRING_INDEX(speed, '/', -1) AS zone, ... - WHERE SUBSTRING_INDEX(speed, '/', 1) SUBSTRING_INDEX(speed, '/', -1) AS zone That is for finding all the speeders, for example. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
elminating filesort
Hello folks :) I'm trying to eliminate a filesort from a very simple query but having no luck. Even though I've read the manual section on when indexes are used in conjunction with ORDER BY, it seems I just can't get an index to be used. I'm running MySQL 3.23.58. The table: CREATE TABLE `minifatboy` ( `p1date` date NOT NULL default '-00-00', `p2date` date NOT NULL default '-00-00', `struct` char(120) binary default NULL, PRIMARY KEY (`p2date`,`p1date`) ) TYPE=MyISAM This table has just under 1 million rows. It has a big brother table called fatboy which has over 143 mil rows on which I'll be running the exact same query later, assuming I can get good performance out of this. The query? SELECT struct FROM minifatboy ORDER BY p2date, p1date; An explain gives me: mysql explain select struct from minifatboy order by p2date, p1date; ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | minifatboy | ALL | NULL | NULL |NULL | NULL | 999370 | Using filesort | ++--+---+--+-+--+++ 1 row in set (0.00 sec) I feel like I have to be missing something obvious here. I don't want to have to wait while MySQL performs a filesort to sort the data into the order already specified by the primary key. It doesn't matter much for minifatboy, but for fatboy this means performing a filesort on a 31gb table. Is it just because I'm not restricting rows and therefore MySQL thinks it should just do a table scan? I know I can try to force the use of an index with MySQL 4, but I'd rather not upgrade if I don't have to (USE INDEX doesn't help, btw). Thanks, --Ludwig Pummer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WEEKOFMONTH
I need a WEEKOFMONTH function for a calendar query. Does
anyone have such a function already?
What are the semantics?
What I need is the first Monday of each month type of function.
WHERE
DATE_FORMAT(.$this-objE-_table..cal_datetime, '%Y-%m-%d')
= DATE_FORMAT('$dt', '%Y-%m-%d') AND
WEEKDAY(.$this-objE-_table..cal_datetime) = WEEKDAY('$dt') AND
.$this-objER-_table..cal_end = '$dt' And
.$this-objER-_table..cal_type = 'monthlyByDay'
This select works except it returns a date for each weekday such as Wed.
For example, it would return the first Wed of the month like...
WHERE
DATE_FORMAT(.$this-objE-_table..cal_datetime, '%Y-%m-%d')
= DATE_FORMAT('$dt', '%Y-%m-%d') AND
WEEKOFMONTH(.$this-objE-_table..cal_datetime) = WEEKOFMONTH('$dt') AND
.$this-objER-_table..cal_end = '$dt' And
.$this-objER-_table..cal_type = 'monthlyByDay'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Ian O'Rourke wrote: I've been playing with MySQL for a bit now (and it is playing) and I'm using it for a number of personal sites (ie not many users, and only I really post stuff). I've also adopted it at work, and we use it to dump Lotus Domino information into for more structured reporting. All is good. In the near future we face making a decision for a database backend, and the logical choice for us is MSSQL as we have one server already for Great Plains - but it would seem MySQL is much cheaper. This would be a line of business we application for around 200 people. Obviously, the details are vague here, I was just wondering if anyone had any stories, personal ones, rather than the press announcements, of adopting MySQL for line of business, critical stuff - what it involved, how it went, what issues they faced in getting it accepted and so on. I'm genuinly interested. We have most of our data in MySQL now. Our main database has about 20 tables, the biggest one being 500,000 rows of telecommunications data. This database has about 30 concurrent users, all connecting via MS Access 2002. Access puts considerable load on databases, even with a small number of users. Actually the reason I got MySQL in the door to start with was because SQL Server was starting to barf on some tables that a lot of users had open at once. MySQL ( and InnoDB in particular ) handles Access quite well. Our sales database has 4 people connecting via Access, and 3 people connecting through the web server. We have a number of other special-purpose databases for importing customer's data that regularly get 1,000,000 rows of data dumped in them. I've been slowly migrating our existing SQL Server database to MySQL, and am basically waiting on stored procedure support before I move our billing data ( we have some pretty complicated queries which use views stored procedures to calculate billing info, which I _could_ do in MySQL, but would rather not until I have stored procedures ). Our migration has gone without a hitch at all. The performance seems to be about twice that of SQL Server 7's, and more so in some cases where the query cache makes it up to 10x faster. We are also running our web site: http://www.nusconsulting.com.au ( simple but effective ) off the same server - it's a LAMP server. Anyway, I'd say go for it, but use the production version. I say this not because the 4.1.x line is unstable, but because if something _does_ go wrong, you don't want to get caught out using an officially unstable version. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Ian ORourke said: In the near future we face making a decision for a database backend, and the logical choice for us is MSSQL as we have one server already for Great Plains - but it would seem MySQL is much cheaper. Perhaps. But supporting 2 database servers sounds like the most expensive option to me, so the question is if you can completely replace MS SQL server. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 Production Release
On Mon, 5 Jan 2004, Allen Weeks wrote: Hi All, Just a quick question, does anyone have a good estimate of when ver 4.1 will go production. When known bugs are fixed. You can read up on MySQL's release policy here: http://www.mysql.com/doc/en/Release_philosophy.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Escaping single quotes
I am on my first MySQL DB and it is very text heavy because it is a news site, therefore there is a great deal of use of the apostrophe or as MySQL would see it the single quote. I was hoping to be able to use double quotes to overcome the need to constantly have to escape the apostrophe/single quote, and where speech marks or a double quote is required, I was going to suggest that the editors of the site use two single quotes. Is this going to cause me problems? However... I have seen a message in the mailing lists that stated the use of double quotes locks me into MySQL for ever as most other DB packages use only single quotes to enclose data. I am concerned about this just incase my client decides that for some reason in the future they want me to use Oracle or MS SQL etc. On top of this is have also just seen messages about 'magic quotes', what is this then? A reply to the message also mentions the use of 'mysql_real_escape_string'!! Does this mean that when a user submits a html form with a name such as 'O'Brien' the name is automatically escaped to 'O\'Brien'? Would I be safer to use double quotes to enclose data, eg. O'Brien? I obviously can't ask all visitors to the site who enter their name to escape each quote. Are my prayers answered? I have been a bit concerned that I might not be able to completely fulfill my clients needs because of them being restricted to the use of single quotes. Thanks in advance Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Escaping single quotes
[snip] Are my prayers answered? I have been a bit concerned that I might not be able to completely fulfill my clients needs because of them being restricted to the use of single quotes. [/snip] Mat, What is your programming language for the site? If it is PHP you have a wealth of options for escaping and/or encoding for single and double quotes in database held text. Most other languages have provisions for this as well, using either built-in functions or regular expressions. Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
I'm lead developer for a company that writes custom software for the mining industry. We support MSSQL and MySQL. I've found that from a programming aspect (VB + ADO) there is relatively little difference between MSSQL and MySQL. There is some sight syntax differences and MySQL versions 5.0 do not support stored procedures. We use the InnoDB table type for MySQL as it provides row level locking and transactions. Our largest client has about 1 gig of data and averages 125 users. I've found that MySQL usually out performs MSSQL if you tune it properly and use good programming techniques. It is less integrated with Microsoft products though so if your clients will be accessing the data via MS Office applications then MSSQL will seem easier. We offer both platforms mainly because a lot of IT managers are convinced that Microsoft solutions are the best even when benchmarks say different. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction support
Direct your developer to www.vbmysql.com. This site is dedicated to supporting developers who want to use MySQL with VB. We will be happy to help him with anything he runs into while writing your system for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER Charset/collation for columns/tables
Hi, Is altering a table and column supported for the characterset/collation? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Current server charset?
Hi, In MySQL 4.1, is there a function to know what the current default server-wide characterset is? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: transaction support
[MySQL] 1: START TRANSACTION 2: WITHDRAW $50 from account 32146. 3: DEPOSIT $50 into account 12345. 4: LOG transfer (date/time/teller/etc...) for auditing. 5: COMMIT TRANSACTION DK It depends why step 3 failed. DK If you want to rollback the transaction to the beginning on any error, DK you simply use an 'on error' statement in VB to trap the error, and send DK a rollback command when the error is detected. And what if the server lost connection with client computer ? there must be automatic rollback after certain 'time-out' period Data integrity logic should be ensured by client application (when to call 'start transaction' and when to call 'commit'), the server task is to ensure that logic - i.e. that at any given moment users see the database in integrity state and not at some point within transaction processing. If user has not commited transaction the database integrity state is still at the point before start transaction was called. Not user but server is responsible for rolling back not commited transactions, user can only help relief the server from major part of this problem by trying to rollback as many failed transactions as he can. however user cannot be responsible for rolling back all the transactions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[3]: transaction support
correction: D Data integrity logic should be ensured by client application should be read as: Data integrity logic should be defined by client application -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Quoting robert_rowe [EMAIL PROTECTED]: I'm lead developer for a company that writes custom software for the mining industry. We support MSSQL and MySQL. I've found that from a programming aspect (VB + ADO) there is relatively little difference between MSSQL and MySQL. There is some sight syntax differences and MySQL versions 5.0 do not support stored procedures. We use the InnoDB table type for MySQL as it provides row level locking and transactions. Our largest client has about 1 gig of data and averages 125 users. I've found that MySQL usua lly out performs MSSQL if you tune it properly and use good programming techniques. It is less integrated with Microsoft products though so if your clients will be accessing the data via MS Office applications then MSSQL will seem easier. We offer both platforms mainly because a lot of IT managers are convinced that Microsoft solutions are the best even when benchmarks say different. I admit to dome degree I am one of the IT Managers - the it 'sounds to good to be true' syndrome I suppose. But I'm coming around. The decision will be for MSSQL Server due to us using other MS products and the supporting of one product, but I'm interested for future reference when it does become an option (probably other jobs). As an aside, stored procedures seem to be a big thing with some people, namely the MS people I encounter (the ASP.NET mantra of using stored procedures for all databases access and even processing tasks), yet people seem to get along with them fine, until recently, in MySQL. This makes me thing they may not be the holy grail people say they are...in MySQL, until recently, all SQL must have been done at the code level rather than at the database server level - is that a major issue? Does it even provide some advantages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Current server charset?
On Tue, 6 Jan 2004, Martijn Tonies wrote: Hi, In MySQL 4.1, is there a function to know what the current default server-wide characterset is? You can find all those with: SHOW VARIABLES LIKE 'character_set%' cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: transaction support
Quoting DeBug [EMAIL PROTECTED]: [MySQL] 1: START TRANSACTION 2: WITHDRAW $50 from account 32146. 3: DEPOSIT $50 into account 12345. 4: LOG transfer (date/time/teller/etc...) for auditing. 5: COMMIT TRANSACTION DK It depends why step 3 failed. DK If you want to rollback the transaction to the beginning on any error, DK you simply use an 'on error' statement in VB to trap the error, and send DK a rollback command when the error is detected. And what if the server lost connection with client computer ? there must be automatic rollback after certain 'time-out' period Data integrity logic should be ensured by client application (when to call 'start transaction' and when to call 'commit'), the server task is to ensure that logic - i.e. that at any given moment users see the database in integrity state and not at some point within transaction processing. If user has not commited transaction the database integrity state is still at the point before start transaction was called. My $0.02 I have to disagree - especially when money is involved. To have the client define, check data integrity, and manipulate data in the database is just asking for trouble. Call me paranoid, but to trust a client in a non-secure environment to directly access and manipulate the database is just asking for a reverse-engineered attack against the database where the data will be compromised. At the very least, you are sending out database connectivity information out with the client that can be stolen and used to access the database. Given the means and the business necessity, I feel that having some sort of middleware on a secured server to validate data and communicate with the database is a much more secure methodology. This is a trivial piece of middleware to write, but can add a much needed layer of security to transactions. Let the secure server hosted middleware host the database connections and let the client talk to the middleware. Not user but server is responsible for rolling back not commited transactions, user can only help relief the server from major part of this problem by trying to rollback as many failed transactions as he can. however user cannot be responsible for rolling back all the transactions -- 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: Current server charset?
Hi Tobias, On Tue, 6 Jan 2004, Martijn Tonies wrote: Hi, In MySQL 4.1, is there a function to know what the current default server-wide characterset is? You can find all those with: SHOW VARIABLES LIKE 'character_set%' Thanks, that'll do the trick. Is there any reason why variable character-sets-dir is with - instead of _s? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bet the Business
I've found, in my many years of app dev work with Oracle, MSSQL, and now a little MySQL, that stored procedures are a very mixed blessing. Depending on your application architecture, they be just what you want, a means of encapsulating a complicated, data-centric function. Database stored procedures are notiriously difficult to manage in terms of version management, mostly due to the advance of excellent GUIs for editing stored procedures directly in the DB (TOAD). The other major drawback is that unless your entire application in based in the database (e.g. Oracle's web toolkit) then it adds another language to your application, as well as another location for code. This obviously increases maintainance time/cost, which is acceptable sometimes, but not others. I am definately _for_ stored procedures. Especially in MySQL -- between SP and subqueries, most of the limiting features of MySQL are going away, and Oracle and MSSQL folks will have less and less ground from which to point fingers. Just remember that they are not a magic bullet, and try not to mix too much of your application and/or business logic directly in them. [stepping down from soap box] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 10:01 AM To: [EMAIL PROTECTED] Subject: Re: Bet the Business Quoting robert_rowe [EMAIL PROTECTED]: I'm lead developer for a company that writes custom software for the mining industry. We support MSSQL and MySQL. I've found that from a programming aspect (VB + ADO) there is relatively little difference between MSSQL and MySQL. There is some sight syntax differences and MySQL versions 5.0 do not support stored procedures. We use the InnoDB table type for MySQL as it provides row level locking and transactions. Our largest client has about 1 gig of data and averages 125 users. I've found that MySQL usua lly out performs MSSQL if you tune it properly and use good programming techniques. It is less integrated with Microsoft products though so if your clients will be accessing the data via MS Office applications then MSSQL will seem easier. We offer both platforms mainly because a lot of IT managers are convinced that Microsoft solutions are the best even when benchmarks say different. I admit to dome degree I am one of the IT Managers - the it 'sounds to good to be true' syndrome I suppose. But I'm coming around. The decision will be for MSSQL Server due to us using other MS products and the supporting of one product, but I'm interested for future reference when it does become an option (probably other jobs). As an aside, stored procedures seem to be a big thing with some people, namely the MS people I encounter (the ASP.NET mantra of using stored procedures for all databases access and even processing tasks), yet people seem to get along with them fine, until recently, in MySQL. This makes me thing they may not be the holy grail people say they are...in MySQL, until recently, all SQL must have been done at the code level rather than at the database server level - is that a major issue? Does it even provide some advantages? -- 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: Bet the Business
As I understand stored procedures: Stored procedures offer a level of performance that you normally can not replicate in code. The stored procedure actually has the query stored in the query parser ready to rumble. You do not have to invoke network or socket overhead in calling a long query and you do not have to invoke the query parser at each invocation of the procedure. Hopefully, the procedure takes advantages of the caches found in most database servers. Theoretically, the performance gains are tremendous if you can knock out network access and the CPU cycles of parsing the query. That being said, I am a fan of MySQL and would use it over MSSQL any day. Not having sub-selects, stored procedures, and until recently transaction support I have found programmatic methods to get around these issues. My experience has been this - let the application that is closest to the data do the manipulation. This is true if the primary purpose is to store, retrieve, and manipulate data. So, when MySQL supports stored procedures you will see, again, a jump in usability and performance. HTH Quoting [EMAIL PROTECTED]: Quoting robert_rowe [EMAIL PROTECTED]: I'm lead developer for a company that writes custom software for the mining industry. We support MSSQL and MySQL. I've found that from a programming aspect (VB + ADO) there is relatively little difference between MSSQL and MySQL. There is some sight syntax differences and MySQL versions 5.0 do not support stored procedures. We use the InnoDB table type for MySQL as it provides row level locking and transactions. Our largest client has about 1 gig of data and averages 125 users. I've found that MySQL usua lly out performs MSSQL if you tune it properly and use good programming techniques. It is less integrated with Microsoft products though so if your clients will be accessing the data via MS Office applications then MSSQL will seem easier. We offer both platforms mainly because a lot of IT managers are convinced that Microsoft solutions are the best even when benchmarks say different. I admit to dome degree I am one of the IT Managers - the it 'sounds to good to be true' syndrome I suppose. But I'm coming around. The decision will be for MSSQL Server due to us using other MS products and the supporting of one product, but I'm interested for future reference when it does become an option (probably other jobs). As an aside, stored procedures seem to be a big thing with some people, namely the MS people I encounter (the ASP.NET mantra of using stored procedures for all databases access and even processing tasks), yet people seem to get along with them fine, until recently, in MySQL. This makes me thing they may not be the holy grail people say they are...in MySQL, until recently, all SQL must have been done at the code level rather than at the database server level - is that a major issue? Does it even provide some advantages? -- 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: Escaping single quotes
Matthew Stuart said: I am on my first MySQL DB and it is very text heavy because it is a news site, therefore there is a great deal of use of the apostrophe or as MySQL would see it the single quote. I was hoping to be able to use double quotes to overcome the need to constantly have to escape the apostrophe/single quote, and where speech marks or a double quote is required, I was going to suggest that the editors of the site use two single quotes. Is this going to cause me problems? You will probably soon be cursed by the editors. Does this mean that when a user submits a html form with a name such as 'O'Brien' the name is automatically escaped to 'O\'Brien'? Most middelware offers some option to do this automatically. Whether it is called magic quotes, bind variables or prepared statements depends on what middleware you are using, but the gist is that you identify to the database driver that you are passing variables instead of a long string, and that the database driver uses that knowledge to properly escape the variables. Pseudocode: sql = SELECT perms FROM siteusers WHERE uname = ? and pword = ?; sql(1, 'string') = $user; sql(2, 'string') = $password; Since you have explicitly told the driver that you are passing a string to be used at the location of the placeholder, the driver will validate and escape it for you. That means you can't forget that you not only need to escape single quotes but also backslashes anymore :-) Would I be safer to use double quotes to enclose data, eg. O'Brien? Probably a 'best practices' implementation using the functionality of your middleware to automatically escape quotes and escape characters is the safest. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Query
This is basic, but need help anyway. I am using PHP and Mysql on a webpage. Here is my query which works: $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where mo nth(hdate)=$mymonth order by timein; now I need to modify it so that it ignores all empnums greater than or equal to . Here is what I tried, but it gives a parse error: $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where mo nth(hdate)=$mymonth and empnum order by timein; thanks for any help. -Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with Query
[snip] $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where mo nth(hdate)=$mymonth and empnum order by timein; [/snip] Change to single quotes around data $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where month(hdate)=$mymonth and empnum '' order by timein; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Query
Jay Blanchard wrote: [snip] $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where mo nth(hdate)=$mymonth and empnum order by timein; [/snip] Change to single quotes around data $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where month(hdate)=$mymonth and empnum '' order by timein; And if empnum is a number, rather than a string, drop the quotes altogether: $query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as timein FROM emp2 where month(hdate)=$mymonth and empnum order by timein; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Update Notification
Hi, I am trying to build an application in Java (1.4.2) which will have a notification mechanism to notify various components within the system when database changes occur. Since MySQL does not support triggers I am trying to figure out an efficient way of implementing this. How can I know when changes(updates/inserts/deletes) occur in the database? Any thoughts or ideas are welcome. Thanks for you help, Sashi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld crashes when closing a prepare/execute session
Description:
Maybe that I abuse the API. Nevertheless, the server should
not crash. I tried with a self-compiled kernel, from the 5.0
bitkeeper repository and with the pre-compiled version
mysql-standard-5.0.0-alpha-pc-linux-i686.
The message is:
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x863fc18
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe7f598, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80880d3
0x82df1d8
0x82dd68b
0x8307dda
0x82bc8b6
0x82bce99
0x808179d
0x807eaf9
0x8087cf8
0x8093c22
0x82dc98c
0x83124ca
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions
on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at (nil) is invalid pointer
thd-thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
My own kernel does not produce a useful backtrace, but
the standard kernel does:
0x80880d3 handle_segfault + 423
0x82df1d8 pthread_sighandler + 184
0x82dd68b pthread_mutex_lock + 11
0x8307dda free + 122
0x82bc8b6 my_no_flags_free + 22
0x82bce99 free_root + 149
0x808179d _._9Statement + 37
0x807eaf9 _._3THD + 517
0x8087cf8 end_thread__FP3THDb + 64
0x8093c22 handle_one_connection + 914
0x82dc98c pthread_start_thread + 220
0x83124ca thread_start + 4
How-To-Repeat:
Here is the test program. Call it as bug_prepared -q for using
mysql_query only (wich succeeds) or bug_prepared -p for using
mysql_prepare/mysql_execute. The program needs a database
test_7. It creates a table tab2, inserts a row, selects it
back, sleeps 5 seconds to prove that the crash comes during
closing the session, closes and exits.
/* bug_prepared.c
*
* Copyright 2004 Ingo Struewing
*
* Show a server bug during close after executing prepared SQL.
*/
static char*USAGE[] =
{
Usage: bug_prepared -p|-q \n,
0};
#define ARGS(x) ( ((x) == 1) )
#include my_global.h
#include my_sys.h
#include m_string.h
#include mysql.h
#include mysqld_error.h
char*program ;
MYSQL mysql_structure ;
MYSQL *mysql = mysql_structure ;
void
check_error ( char *message ,
unsigned int acceptable_1 ,
unsigned int acceptable_2 ,
unsigned int acceptable_3 );
/*==*/
int
main( int argc ,
char **argv )
{
int argn ;
int use_prepared ;
char*host ;
char*user ;
char*passwd ;
char*db ;
unsigned intport ;
char*unix_socket ;
unsigned long client_flag ;
char*query ;
MYSQL_STMT *stmt ;
MYSQL_BIND bind[4] ;
long intproducer ;
unsigned long amount_length ;
unsigned long size_length ;
unsigned long fruit_length ;
MYSQL_RES *result_set ;
MYSQL_ROW row ;
/*
* Get options.
*/
argn = 1 ;
if ( ! ARGS(argc - argn) )
{
for ( argn = 0 ; USAGE[argn] ; argn ++ )
(void) fprintf ( stderr , %s , USAGE[argn] );
return ( 1 );
}
if ( ! strcmp ( argv[argn] , -p ) )
{
use_prepared = 1 ;
}
else if ( ! strcmp (
Re: Bet the Business
Obviously, the details are vague here, I was just wondering if anyone had any stories, personal ones, rather than the press announcements, of adopting MySQL for line of business, critical stuff - what it involved, how it went, what issues they faced in getting it accepted and so on. I'm genuinly interested. We started a web site with MySQL in 1999 or so. The site ran on a modest little server, sharing it with dozens of other web sites. With success traffic grew and we always figured in 6 months MySQL would never be able to handle the load and we'd have to shell out mega bucks and mega pain for something like Oracle. Our site generated perhaps 5 queries a second back in the day. Every time performance started to drop we've always been able to solve the problem more easily than imagined. Besides throwing hardware at it (which can only go so far), we could always better optimize a query, do some kind of preprocessing, tweak some database settings, etc. I've stopped worrying. It's 2004 and our single server now processes 2000+ queries a second, and the site today is just as fast as it was in 1999 (fast!) What I like best about MySQL is not all of the features that it comes shrink-wrapped with, but that with a little creativity and a few hours of work you can get it to do almost anything you want. MySQL finds a good balance between what the RDBMS should do and what it should leave alone. Maybe this was an accident, maybe it was a sign of technical brilliance--ignoring marketing demands to achieve technical superiority. Regardless, it's to a great effect. I suspect all of the clever and hard work in the world won't get MS SQL Server to do the unconventional. Also, insert obligatory benefits of open source boilerplate here. -- Michael Bacarella24/7 phone: 1-646-641-8662 Netgraft Corporation http://netgraft.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Update Notification
MySQL 5.0 does support triggers. It is still alpha though. You might try using a timestamp field and polling your tables at intervals. It won't be real time but it might be close enough. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suggest improvement for mysqlbug
Description:
I had some difficulties to send a bug report by mysqlbug. It
inserted my UNIX user name as From: and used some default mail
agent, which used my UNIX username @ local domain as the
sender address. While the first is merely uncomfortable, the
latter inhibited the mail from being delivered.
I improved mysqlbug so that it respects two environment
variables, if present (see the diffs below):
REAL_EMAIL - From: address. This is used by bitkeeper too.
MAIL_AGENT - Program to use for sending mail (I have one that
uses the From: address as sender).
How-To-Repeat:
Fix:
Here my changes (output of bk -r diffs -c):
= scripts/mysqlbug.sh 1.6 vs edited =
*** /tmp/mysqlbug.sh-1.6-1881 Fri Dec 20 13:58:17 2002
--- edited/scripts/mysqlbug.sh Tue Jan 6 18:07:16 2004
***
*** 44,57
# How to read the passwd database.
PASSWD=cat /etc/passwd
! if test -f /usr/lib/sendmail
then
! MAIL_AGENT=/usr/lib/sendmail -oi -t
! elif test -f /usr/sbin/sendmail
! then
! MAIL_AGENT=/usr/sbin/sendmail -oi -t
! else
! MAIL_AGENT=rmail $BUGmysql
fi
# Figure out how to echo a string without a trailing newline
--- 44,60
# How to read the passwd database.
PASSWD=cat /etc/passwd
! if test x$MAIL_AGENT = x
then
! if test -f /usr/lib/sendmail
! then
! MAIL_AGENT=/usr/lib/sendmail -oi -t
! elif test -f /usr/sbin/sendmail
! then
! MAIL_AGENT=/usr/sbin/sendmail -oi -t
! else
! MAIL_AGENT=rmail $BUGmysql
! fi
fi
# Figure out how to echo a string without a trailing newline
***
*** 208,214
SEND-PR: Lines starting with \`SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in \`' and \`').
SEND-PR:
! From: ${USER}
To: ${BUGADDR}
Subject: $SUBJECT_C
--- 211,217
SEND-PR: Lines starting with \`SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in \`' and \`').
SEND-PR:
! From: ${REAL_EMAIL:-${USER}}
To: ${BUGADDR}
Subject: $SUBJECT_C
Submitter-Id: submitter ID
Originator:MySQL Development
Organization: private
MySQL support: none
Synopsis: Suggest improvement for mysqlbug
Severity: non-critical
Priority: medium
Category: mysql
Class: change-request
Release: mysql-5.0.0-alpha (Source distribution)
C compiler:gcc (GCC) 3.3.3 20031206 (prerelease) (Debian)
C++ compiler: gcc (GCC) 3.3.3 20031206 (prerelease) (Debian)
Environment: PC, Debian GNU/Linux (sid), Pentium 4
libc62.3.2.ds1-10
zlib1g 1.2.1-3
System: Linux chilla 2.6.0-test11 #1 Sun Jan 4 17:04:51 CET 2004 i686 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.3/specs
Konfiguriert mit: ../src/configure -v
--enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr
--mandir=/usr/share/man --infodir=/usr/share/info
--with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib
--enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu
--enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc
i486-linux
Thread model: posix
gcc-Version 3.3.3 20031229 (prerelease) (Debian)
Compilation info: CC='gcc' CFLAGS='-g -march=pentium4' CXX='gcc' CXXFLAGS='-g
-march=pentium4 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 2003-11-15 12:44 /lib/libc.so.6 -
libc-2.3.2.so
-rw-r--r--1 root root 1243076 2003-11-05 20:17 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2537670 2003-11-05 20:18 /usr/lib/libc.a
-rw-r--r--1 root root 204 2003-11-05 20:07 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock' '--with-debug' 'CFLAGS=-g
-march=pentium4' 'CXXFLAGS=-g -march=pentium4 -felide-constructors -fno-exceptions
-fno-rtti' 'CXX=gcc'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RELOAD privilege is global, but FLUSH TABLE name is local.
Description: FLUSH TABLE name requires a global RELOAD privilege, even though it only operates on a table in a given database. RELOAD privileges cannot be granted on specific databases or even tables. How-To-Repeat: mysql grant reload on test.* to [EMAIL PROTECTED]; ERROR 1221 (HY000): Wrong usage of DB GRANT and GLOBAL PRIVILEGES mysql grant reload on test.T to [EMAIL PROTECTED]; ERROR 1144 (42000): Illegal GRANT/REVOKE command. Please consult the manual which privileges can be used Fix: Current work around is to GRANT RELOAD ON *.*, but this isn't very satisfying. Submitter-Id: submitter ID Originator:Lukas Knecht Organization: EraGen Biosciences Inc. MySQL support: licence Synopsis: RELOAD privilege is global, but FLUSH TABLE is local. Severity: non-critical Priority: medium Category: mysql Class: change-request Release: mysql-4.1.1-alpha-max (Official MySQL-max binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux barks 2.4.18-4GB #1 Tue Dec 2 18:01:08 GMT 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-O2 -mpentiumpro -DBIG_TABLES' CXX='gcc' CXXFLAGS='-O2 -mpentiumpro -felide-constructors -DBIG_TABLES' LDFLAGS='' ASFLAGS='' LIBC: -rwxr-xr-x1 root root 1394302 Mar 27 2003 /lib/libc.so.6 -rw-r--r--1 root root 25362104 Mar 27 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex' '--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-berkeley-db' '--with-raid' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mpentiumpro -DBIG_TABLES' 'CXXFLAGS=-O2 -mpentiumpro -felide-constructors -DBIG_TABLES' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XML Datatype for MySQL?
I poked around the docs and mailing list for a bit, and didn't see any references to ideas/future of this? I'm not looking so much just to store xml in MySQL, obviously I can just use a text column for that. I was more thinking along the lines of eXist and dbXML and the XML:DB initiative in particular. Being able to effectively query the content of the XML document, relatating it to other standard column types. Other possibilities is to name an xml schema during column creation, to force validation of the incoming data. Specialized indexes would be needed, as likely you would need to not just index the column, but an xml path (e.g. XPath) within the data itself. Then of course, updating part of the data based on paths you get the idea Anyway, I was wondering if MySQL or the community was thinking/doing anything like this... I would volunteer to help, but as my strong suit is Java, I don't think I'd be too much help... ;) Thanks, Dan Greene
Re: transaction support
Morten Gulbrandsen wrote: http://www.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8q=MySQL+toy+marston Subject: Re: Can MySQL table handle 3 million+ entries? Newsgroups: comp.lang.php Date: 2003-04-11 15:20:10 PST MySQL is NOT a toy database - it is far superior to many I have used in my long career. The lack of constraints is NOT a weakness. It is eminently possible to create reliable applications without the need for database constraints - I should know because I have designed and built many applications that did not use database constraints (mainly because they were not available). Developers only rely on database constraints to circumvent their sloppy code. Anything that can be done within the database can also be done within application code. As an application developer, I don't trust employees (including myself) to enter the data correctly and hence don't let it pass unchecked but validate it in the application layer. I belief that is a rather common mindset amongst application developers, which matches best practice recommendations: http://www-106.ibm.com/developerworks/library/l-sp2.html As a database administrator, why should I trust application developers (including myself) to validate the data correctly and let the data pass unchecked? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pulling numbers out of a column for a query
Thanks for the responses on this. I've gotten so used to using the ref
materials I have on hand that, unfortunately, I forgot to check online
manuals from time to time. (My measly sources had helped with what I needed
until now and my best source, MySQL Cookbook, is on loan to a friend.)
I neglected to mention that sometimes the column with the speeds in them has
extra data (like 22/25 School Zone, or School Zone 22/25), so it took
some extra work, like adding RIGHT() and LEFT(). (Full query below...)
I had not split the data into separate columns because 1) sometimes the data
is just one number, like -3 for 3 miles under the limit, or 5 for 5 miles
over (and sometimes in other forms), 2) I often need to make sure the actual
line of data is included, so I didn't want to separate out figures. At this
point, I'm adding an extra column to the table, SpeedOver (since most of the
differences are over the speed limit). Since the data is already in the
table, using the query help I got here, I can easily drop the new data into
the new column. If I get more data (likely), I'll be able to process it with
Perl to include the extra column (easier than in queries, since Perl is so
good with regexes and can handle 3 digit speeds better).
On Monday 05 January 2004 09:37 pm, Hal Vaughan wrote:
I've found an odd problem in queries.
I have a lot of data regarding vehicle speeds in a survey. All the data is
in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43
miles per hour in a 55 miles per hour zone. 80/55 means we have a serious
speed demon, going 80 in a 55 zone.
I need to be able to pull out data depending on speeds in relation to the
speed limit, for example, find out how many are less than 55/55 or 45/45,
or how many are more than 5 miles over (like 61/55 or 32/25).
Here's what I used as a query to create 2 columns, the first is the actual
speed info and the 2nd is the difference between the speed limit and clocked
speed. I do not, in this case, take into account 3 digit speeds. In this
example, I'm pulling out all records where the speed exceeded the speed limit
by 20 miles per hour (since that is legally reckless driving, and one data
point we're looking for):
SELECT Speed, RIGHT(SUBSTRING_INDEX(Speed, '/',
1),2)-LEFT(SUBSTRING_INDEX(Speed, '/', -1),2) AS Diff FROM TrafficSurvey
WHERE Speed REGEXP '.*[0-9]{1,2}/[0-9]{1,2}.*' AND
(ABS(RIGHT(SUBSTRING_INDEX(Speed, '/', 1),2)-LEFT(SUBSTRING_INDEX(Speed, '/',
-1),2)) 20);
Thanks for the help!
Hal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binaries for 4.x
Kool, there are no longer GPL binaries of MySQL for SCO operating systems. Only commercial binaries that you have to buy from MySQL AB. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: kooldude [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, January 06, 2004 4:34 PM Subject: Binaries for 4.x Does anyone have an idea where I could obtain binaries for MySQL 4.x for either SCO openserver release 5 or AIX 4.2.1 The binaries avalable from MySQL AB stop at version 3.22.32 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt MYD table - can not repair
At 19:04 6/1/2004 +, Mike Brickman wrote:
If you are able for to build myisamchk from the 4.1 bk tree
it will permit you to set a max row length for to skip them
and continue the recovery.
Hi,
I have a client who has managed to corrupt an MYD table so that it can
not be repaired. I have tried:
Myisamchk -re {table}
And this crashes out with:
myisamchk: error: Not enough memory for blob at 3960 (need 1090519040)
and then quits.
There is no complete backup of this file so I would like to recover as
much data as possible.
1) Are there any tools which will allow me to salvage some data?
2) Why does myisamchk not fix the problem?
3) Are the internal structures of MYD files documented anywhere
(url please)?
4) What exactly does the error mean?
Any help or clues will be appreciated.
Mike Brickman
Radical Solutions
--
Regards,
For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/
Miguel Angel Solórzano [EMAIL PROTECTED]
São Paulo - Brazil
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sleeping Processes
I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+--+ --+ | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+--+ --+ | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sleeping Processes
Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- 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: Sleeping Processes
Dan: Thanks for the suggest. I will check it out. The strange thing is that as I mentioned, the PHP code that I am using is straightforward, and I have never had any problems with the database not disconnecting properly with PHP, but that being said, the possibility that it is a db connector problem I guess would be OS specific rather than PHP or MYSQL specific necessarily? We will try upgrading PHP to 4.3.4 to see if that helps, and it may be also that a MYSQL 4 upgrade is also on the way. Talking to the host provider, they did mention that the 3 disk array is served only by 1 controller, and they will be adding more, perhaps the bottleneck is in the disk writes, but I cannot see that being a big issue on the relativly low traffic (only around 2GB/month web traffic...) Any other ideas? On 1/6/04 2:29 PM, Dan Greene [EMAIL PROTECTED] wrote: Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade
Re: Bet the Business
soapbox Back to stored procedures for a second. I use them with MSSQL. For certain tasks they greatly improve performance because I don't have to move all the data client side. For the vast majority of tasks though stored procedures yeild little performance gain. I've seen systems that (designed as Microsoft suggests) wrapped all SQL usage in stored procedures. This is nonsense. Also, stored procedure have two very important liabilities: they break the n-tier model and they expose source code to the customer (if you sell your app). If you change your back end then you get to re-write all your stored procedures (I had to do this once). I routinely get calls from IT managers who have looked at my stored procedures and have questioned why I did so and so. This forces me to research it and come up with a reasonable answer. In my opinion the performance gain of stored procedures are rarely worth the headaches so their lack of them in MySQL is really not a problem. Will I use them in MySQL 5.0? Probably, but only when justified. /soapbox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Query
Try single quotes around instead of double quotes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Original Message - From: robert_rowe [EMAIL PROTECTED] Will I use them in MySQL 5.0? Probably, but only when justified. /soapbox So what are the justifications? What makes a wise stored procedure and an unwise stored procedure? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 Production Release
Any chance that there's a quarterly strategic roadmap published somewhere? I have projects that sometimes depend on a feature in the next rev' or some such, and I need to plan out for my organization... Difficult to answer my boss when the dependencies are released when they're ready. -Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 5:25 AM To: Allen Weeks Cc: MySQL List Subject: Re: MySQL 4.1 Production Release On Mon, 5 Jan 2004, Allen Weeks wrote: Hi All, Just a quick question, does anyone have a good estimate of when ver 4.1 will go production. When known bugs are fixed. You can read up on MySQL's release policy here: http://www.mysql.com/doc/en/Release_philosophy.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
All IMHO of course... Will I use them in MySQL 5.0? Probably, but only when justified. /soapbox So what are the justifications? What makes a wise stored procedure and an unwise stored procedure? I've used them a lot with Oracle. The n-tier days weren't really alive when I was doing that project. We had several reasons to use Stored Procedures: 1) the same set of complicated processing to be called by multiple applications and updating that particular piece of code without having to update the (client) apps 2) dragging a lot of data across the network was slow. With a stored procedure, the processing was A LOT faster. 3) without a middle tier: having to use a particular piece of code in different programming languages 4) Oracle Jobs require a single statement or procedure to run. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaping single quotes
You could always write your own function to do the escaping for you. This following link is an example written in VB that you could adapt to whatever language you are using. http://www.vbmysql.com/samplecode/stripquote.html Here is another way of doing it if you can link to the libmySQL.dll (or equivilent in your OS). http://www.vbmysql.com/samplecode/mysql_escape_string.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user d/b access
Just fired up mysql 3.23.53 * Created a d/b, and then created an admin user for that d/b, using GRANT ALL ON URCMS.* TO urcms_admin IDENTIFIED BY 'changeme' WITH GRANT OPTION; Ok, it never prompts me for a password, nor did it let me, as that user, use URCMS. I looked in mysql.db, and the host for that user was %, which according to the reference, *should* let me do that from any host. At any rate, it only let me go to the d/b after, as root, I did GRANT ALL ON URCMS.* TO [EMAIL PROTECTED] WITH GRANT OPTION; I would assume that % should include localhost. Am I missing something here? mark * Please don't tell me to upgrade to 4.x. I'm working on some software, and I do *not* want it to require Only The Latest Version. Since many distros are still shipping with 3.23, I want it to be compatable with what they're running, and not force them to upgrade, *unless* this is a known bug. In that case, I have no problem upgrading to the next patch level. -- resident, Imperial Christian States of America, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: elminating filesort
In the last episode (Jan 06), Ludwig Pummer said: I'm trying to eliminate a filesort from a very simple query but having no luck. Even though I've read the manual section on when indexes are used in conjunction with ORDER BY, it seems I just can't get an index to be used. Sometimes a filesort is truly the most efficient solution. The table: CREATE TABLE `minifatboy` ( `p1date` date NOT NULL default '-00-00', `p2date` date NOT NULL default '-00-00', `struct` char(120) binary default NULL, PRIMARY KEY (`p2date`,`p1date`) ) TYPE=MyISAM ... SELECT struct FROM minifatboy ORDER BY p2date, p1date; An explain gives me: mysql explain select struct from minifatboy order by p2date, p1date; ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | minifatboy | ALL | NULL | NULL |NULL | NULL | 999370 | Using filesort | ++--+---+--+-+--+++ 1 row in set (0.00 sec) I feel like I have to be missing something obvious here. I don't want to have to wait while MySQL performs a filesort to sort the data into the order already specified by the primary key. It doesn't matter for minifatboy, but for fatboy this means performing a filesort on a 31gb table. Is it just because I'm not restricting rows and therefore MySQL thinks it should just do a table scan? I know I can try to force the use of an index with MySQL 4, but I'd rather not upgrade if I don't have to (USE INDEX doesn't help, btw). Your two choices are: walk the primary index in order and then do random disk seeks through the table to fetch the records, or do a sequential pass through the entire table, sorting the data (either in memory or in a tempfile). A million random I/Os, assuming say 90% of them are cache hits (very optimistic), on an average disk than can do 175 I/Os per second, would take around 100*.10/175=571 seconds. A full table scan plus a sort, assuming worst mergesort case of 2 sets of tempfiles, on a million 128-byte records, on a slow disk that does 10MB/sec would take around 100*128/(10*1024*1024)*5=61 seconds. If 'struct' was a varchar with lots of blank padding, the table scan would go even faster. If your table was created in p2date, p1date order, then the random I/Os would really be sequential and would go fast, but mysql doesn't know the physical ordering of the records. I think USE INDEX should have worked, actually. If this is the most common query, and other queries also have WHERE clauses involving p2date, you might want to test this as an InnoDB table. They store table data inside the primary index, so it should just have to walk the index to get 'struct' sorted correctly. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user d/b access
Hi mark, I would recommend to use GRANT ALL ON URCMS.* TO 'urcms_admin'@'localhost' IDENTIFIED BY 'changeme' WITH GRANT OPTION; if you only need to connect to URCMS database from localhost. HTH Michael mark wrote: Just fired up mysql 3.23.53 * Created a d/b, and then created an admin user for that d/b, using GRANT ALL ON URCMS.* TO urcms_admin IDENTIFIED BY 'changeme' WITH GRANT OPTION; Ok, it never prompts me for a password, nor did it let me, as that user, use URCMS. I looked in mysql.db, and the host for that user was %, which according to the reference, *should* let me do that from any host. At any rate, it only let me go to the d/b after, as root, I did GRANT ALL ON URCMS.* TO [EMAIL PROTECTED] WITH GRANT OPTION; I would assume that % should include localhost. Am I missing something here? mark * Please don't tell me to upgrade to 4.x. I'm working on some software, and I do *not* want it to require Only The Latest Version. Since many distros are still shipping with 3.23, I want it to be compatable with what they're running, and not force them to upgrade, *unless* this is a known bug. In that case, I have no problem upgrading to the next patch level. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reference to a command that I can not find AND Foriegn Key information
There was a user comment under the Foriegn Key section of the documentation reading: To restore from a mysqldump file that uses foreign keys: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE your_dump_file; mysql SET FOREIGN_KEY_CHECKS = 1; The command I am looking for is the call SOURCE. Where is this in the documentation? Also, will this FOREIGN_KEY_CHECKS ignore the definitions while creating the tables, as in not create them, or just merely not check for its consitancy? Is any of the above limited to the InnoDB type? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user d/b access
On Tuesday 06 January 2004 03:27 pm, Michael Wittmann wrote: I would recommend to use GRANT ALL ON URCMS.* TO 'urcms_admin'@'localhost' IDENTIFIED BY 'changeme' WITH GRANT OPTION; if you only need to connect to URCMS database from localhost. The problem is that I'm writing software, not just something for myself. I want to have this be generic, so that if someone wants to install this package on one system, and log into mysql *either* from the same machine that it's running on, *or* from another system. mark -- Have you noticed that, when we were young, we were told that everybody else is doing it was a really stupid reason to do something, but now it's the standard reason for picking a particular software package? -- Barry Gehm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 Production Release
On Tue, 6 Jan 2004, Knepley, Jim wrote: Any chance that there's a quarterly strategic roadmap published somewhere? I have projects that sometimes depend on a feature in the next rev' or some such, and I need to plan out for my organization... Difficult to answer my boss when the dependencies are released when they're ready. This is a snip from the 5.0.0 release message: THe MySQL 4.1 branch seams to be relatively stable and we will, if we don't find any new unexpected hard bugs that will require new design decisions, make a beta release of 4.1 in January followed by a gamma release ASAP. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
store procedure in MySQL 5
Hi, I downloaded the 5.0 binary for windows and installed it, but I can't create store procedure using MySQL Control Center, does the 5.0 alpha-binary have store procedure feature?. TIA, Ulises
Re: user d/b access
At 01:56 PM 1/6/2004, you wrote: Just fired up mysql 3.23.53 * Created a d/b, and then created an admin user for that d/b, using GRANT ALL ON URCMS.* TO urcms_admin IDENTIFIED BY 'changeme' WITH GRANT OPTION; Yup. Well known privileges problem. The short answer is that the default MySQL install (why) creates an anonymous user who can log on from anywhere. Until you get rid of that user, who has no privileges to read any database, you can't set up any other users who can log in from anywhere. So, USE mysql; DELETE FROM user where User=; FLUSH PRIVILEGES; and all will work. ari -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reference to a command that I can not find AND Foriegn Key information
On Tue, 6 Jan 2004, Luc Foisy wrote: There was a user comment under the Foriegn Key section of the documentation reading: To restore from a mysqldump file that uses foreign keys: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE your_dump_file; mysql SET FOREIGN_KEY_CHECKS = 1; The command I am looking for is the call SOURCE. Where is this in the documentation? http://www.mysql.com/doc/en/Batch_Commands.html Also, will this FOREIGN_KEY_CHECKS ignore the definitions while creating the tables, as in not create them, or just merely not check for its consitancy? Just doesn't check if there's a valid value in the parent table that it refers to and will not give an error. Is any of the above limited to the InnoDB type? Both -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction support
At 09:37 PM 1/5/2004, Bryan Koschmann - GKT wrote: Hi, I'm trying to get a software designer to write us some software using MySQL as the database server (he currently requires MS SQL). It is all windows based software (written in VB). So far his arguments against it are this (not my words): -No explicit transactional support -MySQL is still buggy -MyODBC is buggy and not used in production environments -Only way to connect using ODBC is third party drivers that cost over half as much as MS SQL This is just for our current software, the new software he is bidding on says he would use .NET so that supposedely causes other problems. Now, I know there are a few discrepancies there but I just don't know enough to argue it. I * need* to use MySQL as the server because of cost reasons. I *WANT* to use MySQL because I don't care for MS choose not to run their products. If you can give me any information to help me argue this I would really appreciate it. Thanks, Bryan I have previously used Borland's Delphi for various programs I needed to write in the M$ environment. This program was very solid and had very good ODBC support. While it has been awhile since I used it (version 2.0, they are now at or beyond 6.0) I have had many individuals claim that the Borland programming languages/compilers are the best and most stable compilers running under M$ Windows. Borland has various Visual programming languages available. you might want to suggest your programming try one of Borland's programs over the MS programs, if you really don't care to use M$ products. When I am forced to use M$ platform, I use every means at my disposal to use non-M$ products to accomplish the job. I use Apache for Web Servers, Perl for Web Scripting, ColdFusion for dynamic web sites accessing various DBs, Bind for DNS, MySQL for all new DBs, etc. For those of us that prefer to not use M$, but are forced to use the OS, I always try my best to make sure I can at least program in something other than MS. Whenever I am forced to program using a M$ compiler, I always add a line to all agreements, that code broken is not the fault of the programmer, but rather the fault of M$ and their constant change in procedures, compilers, and bugs. I have never heard and programmer who uses M$ C Compiler ever say they have had there program work right and stay working after each and every patch, or upgrade of the M$ compiler and operating system. Most programmers who use M$ C compiler constantly complain they must write code that works around a bug in the compiler or OS, only to have to rewrite the code after M$ tries to fix their bug. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 Production Release
Hi! I have a hobby of predicting release dates. It took 17 months for 4.0 to be declared 'production'. That gives us an estimate that 4.1 will be declared 'production' in September 2004. But since 4.1.1 already seems to be a very stable release, I expect it to happen sooner. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Tobias Asplund [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 06, 2004 10:41 PM Subject: RE: MySQL 4.1 Production Release On Tue, 6 Jan 2004, Knepley, Jim wrote: Any chance that there's a quarterly strategic roadmap published somewhere? I have projects that sometimes depend on a feature in the next rev' or some such, and I need to plan out for my organization... Difficult to answer my boss when the dependencies are released when they're ready. This is a snip from the 5.0.0 release message: THe MySQL 4.1 branch seams to be relatively stable and we will, if we don't find any new unexpected hard bugs that will require new design decisions, make a beta release of 4.1 in January followed by a gamma release ASAP. cheers, Tobias -- 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: Corrupt MYD table - can not repair
Hi!
On Jan 06, Mike Brickman wrote:
Hi,
I have a client who has managed to corrupt an MYD table so that it can
not be repaired. I have tried:
Myisamchk -re {table}
And this crashes out with:
myisamchk: error: Not enough memory for blob at 3960 (need 1090519040)
and then quits.
There is no complete backup of this file so I would like to recover as
much data as possible.
4) What exactly does the error mean?
It means that myisamchk tries to allocate 1090519040 bytes to read a
blob of this size - and malloc() fails.
3) Are the internal structures of MYD files documented anywhere
(url please)?
To my knowledge - no.
2) Why does myisamchk not fix the problem?
Because the problem is Out Of Memory error - this isn't something
myisamchk can fix :)
What happens is, myisamchk finds a something that looks like row header,
but in fact it is part of your data. Interpreting this row header
myisamchk thinks it belongs to a 1Gb blob.
It's unavoidable - when you ask to recover as much as possible,
myisamchk does it, literally, and you may get spurious rows in the
result.
The negative effect is that spurious row header can abort the repair
process. To fix it, in 4.1.1 we added a new command-line option to
myisamchk: --max-record-length. If you happen to know that no row in
your table is longer than N, you may tell myisamchk that all rows longer
than that are false matches and should be ignored.
1) Are there any tools which will allow me to salvage some data?
myisamchk as of 4.1.1.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: French characters ok with mysql in Windows, nok with mysql in Linux
Hello, I changed etc/sysconfig/i18n to: LANG=fr_FR SUPPORTED=fr_FR:fr SYSFONT=lat0-sun16 SYSFONTACM=iso15 and everything works perfectly. Thank you very much for your help. Greetings, Willy Dockx -Original Message- From: David Bordas [mailto:[EMAIL PROTECTED] Sent: dinsdag 6 januari 2004 8:39 To: Willy Dockx Cc: [EMAIL PROTECTED] Subject: Re: French characters ok with mysql in Windows, nok with mysql in Linux From: Willy Dockx [EMAIL PROTECTED] Sent: Monday, January 05, 2004 7:23 PM Subject: RE: French characters ok with mysql in Windows, nok with mysql in Linux Hello, etc/sysconfig/i18n contains: LANG=en_US.UTF-8 SUPPORTED=nl_BE.UTF-8:nl_BE:nl:en_US.UTF-8:en_US:en SYSFONT=latarcyrheb-sun16 Is that ok? I can't remenber what was my conf, but google is you friend ... http://groups.google.fr/groups?hl=frie=UTF-8oe=UTF-8q=french+redhat+i18n; sa=Ntab=wgmeta= http://groups.google.fr/groups?hl=frlr=ie=UTF-8oe=UTF-8q=accent+redhat+i 18n Did you try to connect directly to MySQL through a standard MySQL client and make an insert with é à ù ... and see if the select is ok after the insert ? What concerns the 'driver connection url' : should I leave 'useUnicode=truecharacterEncoding=UTF-8' in it? Don't know, i'm not using java ... Bye David -- 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: Sleeping Processes
Mike Morton said: Dan: Thanks for the suggest. I will check it out. The strange thing is that as I mentioned, the PHP code that I am using is straightforward, and I have never had any problems with the database not disconnecting properly with PHP, but that being said, the possibility that it is a db connector problem I guess would be OS specific rather than PHP or MYSQL specific necessarily? We will try upgrading PHP to 4.3.4 to see if that helps, and it may be also that a MYSQL 4 upgrade is also on the way. Talking to the host provider, they did mention that the 3 disk array is served only by 1 controller, and they will be adding more, perhaps the bottleneck is in the disk writes, but I cannot see that being a big issue on the relativly low traffic (only around 2GB/month web traffic...) Any other ideas? On 1/6/04 2:29 PM, Dan Greene [EMAIL PROTECTED] wrote: Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. -
compiling mysql with ssl
Hi! I have some trouble tring to compile Mysql 4.0.17 with de options: --with-vio --with-openssl, I try with some recomedation about it but none successful. Compiling with: ./configure --with-vio --with-openssl=/path/to/openssl --with-openssl-includ e=/path/to/include/ssl.h --with-openssl-lib=/path/to/lib/libssl.a Everything is fine during de compilation but when i install the new database, it's show my variable have_openssl=no and the showing Status SSL=not in use *When i compile with: ./configure --with-vio --with-openssl --with-openssl-include=/path/to/includ e/ssl.h --with-openssl-lib=/path/to/lib/libssl.a during the compilation i get the following error: ../lib/mysql/.libs/libmysqlclient.so: undefined reference to openssl_add_all_algorithms collect 2: ld returned 1 exit status Can someone help me?, I don't know that to do or what i should do for now?? Thanks in advance, Cion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Null Values Foreign Keys
Here're a couple of tables: create table company(CompanyID varchar(32),...); create table contact(CompanyId varchar(32),... INDEX (CompanyID), FOREIGN KEY (CompanyID) REFERENCES company(CompanyID); Now, my expectation is that if contact has a CompanyID but it does not match one already existing in company, an insert fails. But, I also have the expectation that if contact does not have a companyID, the insert should succeed. Is this right or wrong? Second, (oh no, it's that null question again!) what constitues not having a company ID? Does the field have to be set specifically to NULL or does an empty string work? Right now, my code is taking a value from a form and assigning it to a field object. A connection object then does an insert using the field object as a parameter. Therefore, if the form company ID field is blank, the value assigned to the field object is an empty string... not the null \N. In fact, I'm not sure I can even change this Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Please Help: Can you restore a dropped database
Greetings All, I have made a terrible newbie stupid mistake and I am wondering if there is anything I can do to save my bacon I made a backup of a database which failed, but mysql didn't tell me :-( Stupidly a dropped the database, as you do and then the restore failed . because there was nothing in the file ... I read http://www.mysql.com/doc/en/DROP_DATABASE.html and it sounds like there is no hope for me But if anyway has any ideas or anything I can do, PLEASE let me know... .. Thanks, G.
Please Help: Can you restore a dropped database
Greetings All, I have made a terrible newbie stupid mistake and I am wondering if there is anything I can do to save my bacon I made a backup of a database which failed, but mysql didn't tell me :-( Stupidly a dropped the database, as you do and then the restore failed . because there was nothing in the file ... I read http://www.mysql.com/doc/en/DROP_DATABASE.html and it sounds like there is no hope for me But if anyway has any ideas or anything I can do, PLEASE let me know... .. Thanks, G.
MySQL 4.0 Installation Problem
I have 3.23 that comes with my Linux distribution, now when I tried to do a rpm installation of 4.0, I got messages like: warning: MySQL-server-4.0.17 ... .rpm: V3 DSA signature: NOKEY, key ID ..., file /usr/bin/mysql from install of MySQL-client-4.0.17.0 conflicts with file from package mysql-3.23.52-3 ... file /usr/shar/man/man1/ ... from install of ... conflicts with file from package mysql-3.23... ... and the conflict list goes on. The installation manual does not seem to discuss about such issues. Do I need to remove my 3.23 before I run the installation? -- but that's not what the manual says, though. The first warning msg, is that a problem, too? Thanks.
Re: Corrupt MYD table - can not repair
You can use navicat-pro tool (free ware) to repair
the corrupted MYD files, I was repaired with the same
TOOL.
-Aman.
miguel solorzano wrote:
At 19:04 6/1/2004 +, Mike Brickman wrote:
If you are able for to build myisamchk from the 4.1 bk tree
it will permit you to set a max row length for to skip them
and continue the recovery.
Hi,
I have a client who has managed to corrupt an MYD table so that it can
not be repaired. I have tried:
Myisamchk -re {table}
And this crashes out with:
myisamchk: error: Not enough memory for blob at 3960 (need 1090519040)
and then quits.
There is no complete backup of this file so I would like to recover as
much data as possible.
1) Are there any tools which will allow me to salvage some data?
2) Why does myisamchk not fix the problem?
3) Are the internal structures of MYD files documented anywhere
(url please)?
4) What exactly does the error mean?
Any help or clues will be appreciated.
Mike Brickman
Radical Solutions
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
LOAD DATA INFILE from any directory
Hi everyone, I am trying to get the following statement to work from any directory besides [ /usr/local/mysql/data/testdb ] : LOAD DATA INFILE 'data.txt' INTO TABLE sometbl TERMINATED BY '|' LINES TERMINATED BY '\n'; I see ERROR 1105: File './testdb/data.txt' not found (Errcode: 2) I also started MySQL as follows: mysql --local-infile --user=username databasename Thanks in advance, -gohaku
Re: Bet the Business
So what are the justifications? What makes a wise stored procedure and an unwise stored procedure? Use them when there is a real benefit to using stored procedures. I will give one example when I would have used them: With a CRM solution I once wrote, I wanted to get a list of n randomly selected customers to issue a survey to. The generation and retreival of the customers was fairly painless, you simply add ORDER BY RAND() to your query with a LIMIT n clause. The catch is that I also wanted to add a row to a log table for each of these people to show that thay were going to be part of this particular random mailing. Now of course at this point these people were going to be on the client side. The solution is to SELECT the random people into a temporary table, then mangle the table to be able to INSERT it into the log table. If I would have had access to stored procedures, I would have had the stored procedure retrieve the people and both return them to the client app AND INSERT a row to the log, saving me time and trouble. One thing to remember is that every stored procedure you execute steals resources that would otherwise go to handling queries, so you do not necesscarily see a performance boost by using stored procedures for everything, in fact a MySQL server handling a lot of stored procedures could very well show poorer performance than a proper n-tier application. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem
Hi I understand the concept of master n slave relationship but have certain queries regarding this... 1) In case the master is down i do i configure my system so that a present active slave becomes the master or any other way to tackle this situation.How does the clients know about this new server being set as the master . 2)The master is run as a myslq demon . Now the slaves though being servers ..are run as clients of the master server with a definate usernme who has been granted REPLICATION SLAVE PERMISSion set at the master server?? i.e do i run the slaves as mysql -h xxx -u -p ?? --mysql client ? 3)Is it necessary t take a snapshot of the datadir at the server , for the first time i set up replication /? Plz Help Me out Thnx Varun Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
