Re: From Oracle to the MySQL Database Server
Hi Tony, - Original Message - From: "Tony Buckley" <[EMAIL PROTECTED]> > Coming from an Oracle background, I started off hating mySQL. So far, I > have only 'played' with it, but now have enough confidence to start using it > for real. It is super fast and my test loads (as much as I can throw at it > from several machines/sources/threads) have yet to fail. Does the job very > nicely. Yes there are the usual gripes about views, transactional > processing, and locking, but if you know about these at the design stage > they can be managed. I'm a fan! Good! But eh, what are those gripes about transactions and locking? Are you aware that the MySQL server with the InnoDB table handler (since over a year now!) features full transactional capabilities including row level locking? You just need to grab a 3.23-Max version, or compile 3.23 from source, or any 4.0 version. For more info on the InnoDB table handler, see http://www.mysql.com/doc/I/n/InnoDB.html in the online manual. Views are on the TODO for around version 4.1. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Annoying article about MySql
Hi, - Original Message - From: "Richard S. Huntrods" <[EMAIL PROTECTED]> > Basically, the "mysqldump" output is missing the "use xxx" command to > change to the target database. I have a perl script that does that (and > extracts the table defs to another file for documentation). Have a look at the options you can use for mysqldump (mysqldump --help)... --databases allows you to specify specific databases, and the output will then contain USE commands too. (--all-databases is a shortcut for --databases with all dbs specified.) You can use --no-data to get a dump with ONLY the table defs. Easy! ;-) Also see the mysqldump section in the manual (http://www.mysql.com/doc/m/y/mysqldump.html). > I have database mysqldump outputs that were 15 megabytes [...] > I have never had any problem. Now, perhaps 10-15 megs is not considered > "large". Well, we started in August with a 2 meg database, and we're > adding users every day. We currently have 775 users, but may expand to > over 1 before this time next year. What lots of people regard as 'large' or 'huge' is actually pretty small, considering there are terabyte size production installations of MySQL servers ;-) Importing a dump file is simply executing a list of CREATE DATABASE/TABLE followed by INSERT statements. It's nothing special. If if causes problems, there are a meriad of possibilities including source bad compiles and operating system problems. I'm sure anyone would agree that a quick&sloppy installation from source by a disinterested ISP who is not knowledgable about MySQL, is a sure recipe for disaster However, the article provides no info on it so it's really impossible no tell. We don't have operating system info, MySQL version info, compile info (if installed from source), configuratation, or anything else. Most unfortunate. > I'll keep this list posted of any problems, but so far the performance > has been exceptional. Indeed, posting a problem on the list, together with all relevant info as described above, is very useful. Often the issue is resolved quickly with info from another user. And occasionally, a bug IS found, which can then be fixed by the developers. > However, once I had downloaded and installed MySQL, I became a convert. > MySQL is very robust, VERY fast, and has a very tiny footprint compared > to other database products. Thanks! ;-) Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: are my queries bloated?
Hi Erik, - Original Message - From: "Erik Price" <[EMAIL PROTECTED]> > I see. If I mentally apply this advice to the rest of my database, it > seems that I should index (or apply a prefix index) to any column that > holds search criteria -- such as files.file_name or > subprojectweb.subproject_name.I would assume that I should really > only do this with the most-commonly-searched columns, because indexing > every column would be unwieldy. As well as useless: the server can only use 1 index per table for any particular query. A good rule would be: find the most limiting factor in your search criteria, a field that is tested for something that will most limit the number of rows returned. > Perhaps I should just go with what I have, and once the database (which > will only have 30 users or so for now) is cooking, I can go in and > analyze the queries and the optimizer and determine where best to place > the indexes. In theory that is fine. However, when your user numbers grow, if you find later that you want to redesign some table structure (beyond simply adding indexes) for better results, it will be more tricky since it involves copying the db and putting new scripts into place. So it IS quite important to get the basic design right before your userbase starts to grow. > For now, the only indexes I have are the PRIMARY KEYs on > the "*_id" columns (for unique ID numbers) and the UNIQUE INDEX on the > "middle table (foreign key)" between "files" and "projects". Well, as noted, you need to verify that an index is actually being used, by looking at EXPLAIN SELECT In case of multiple indexes on a table, you'll also want check that the best one has been chosen. > I read in my book that one way to do this testing is to just reconstruct a table > using CREATE TEMPORARY TABLE and go ahead and make changes to this > temporary table, and anything that seems to run faster can be later > applied to the actual table. Sure. But adding/removing an index on a small data set is easy enough, you can try that kind of stuff on the main tables. > > Re-order the list of tables in the FROM, and put the > > "subprojectweb.subproject_name = 'shoeshine.com' " bit first > > after the WHERE, that will also make it clearer for you to read. > Is this just for my own personal clarity? I was under the impression > that the exact order of the JOINs wouldn't matter very much, but I > haven't found any evidence of this yet. Join order *does* matter, the table with the search criteria should generally be checked first, most limiting the number of rows. Then the other tables are joined into the result using their foreign keys. You don't want te query to be performed based on the foreign keys, with the search criteria being applied last! For a regular join with commas, the optimiser will try to work out an optimal join order. However, you will need to have a look at the output from EXPLAIN, which will show the tables in the order they are joined. If it is non-optimial, you could modify your query by using STRAIGHT_JOIN or other tricks to get an optimal join order. > Thanks very much for responding to my questions about this, Arjen. You're quite welcome. This kind of stuff (optimising) is an important subject in MySQL training courses (www.mysql.com/training/). Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: are my queries bloated?
Hi Erik, - Original Message - From: "Erik Price" <[EMAIL PROTECTED]> > mysql> SHOW COLUMNS FROM subprojectweb; > +-+---+--+-+-+ > | Field | Type | Null | Key | Default | > +-+---+--+-+-+ > | subproject_id | mediumint(8) unsigned | | PRI | NULL| > | subproject_name | varchar(64) | | | | > +-+---+--+-+-+ > 2 rows in set (0.01 sec) > > So if I want to find out which files were used in a web subproject > called "shoeshine.com", do I really use the following query? > > SELECT files.file_name > FROM files, projfile, projects, subprojectweb > WHERE files.file_id = projfile.file_id > AND projfile.project_id = projects.project_id > AND projects.subprojectweb_id = subprojectweb.subproject_id > AND subprojectweb.subproject_name = 'shoeshine.com' ; > > This is a HUGE query. Well, maybe it's not -- maybe there are lots of > queries this big. The point is, I rarely see reference to a query this > big on this list or in my book. Is this the norm? Or am I doing > something weird, am I "not getting" this relational database concept? Well, books give examples that need to be clear. A 3 or 4-way join is theoretically the same as a 2-way join, the same 'rules' apply. The above query is actually quite small ;-) There is a prob with the above query though, in that it will turn out to be very slow when your tables grow. When you look at the WHERE clause, most of it just deals with the table relationships. That's fine. There is also the one part that limits your selection (shoeshine.com). So that is very important. I've quoted the table structure of the subprojectweb table above, and the 'subproject_name' field is not indexed. Therefore the server will have to do a table scan on the subprojectweb table, to find the rows that match the specified name. So, add an index on that field, or at least a prefix. You don't need to index all 64 chars of it. Re-order the list of tables in the FROM, and put the "subprojectweb.subproject_name = 'shoeshine.com' " bit first after the WHERE, that will also make it clearer for you to read. Then, run the query with EXPLAIN in front of the SELECT, and look at the output. With the EXPLAIN command, the server will tell you how the optimiser has organised query execution: in which order will the tables be joined, and which indexes can and will be used. You'll want to set up your query so that the most limiting stuff is done first, and then the other tables are just used based on their primary key, one for each matching row in the first table. For this type of query, that'd be the fastest route. If that's not what happens (see EXPLAIN) you can rephrase your query so that it is (for instance by using STRAIGHT_JOIN). There's quite a bit of background info involved in the above, which I can't possibly post in a short message. So, it's just some things to point you in the right direction By the way, this is the kind of cool stuff you learn as part of a MySQL training course (www.mysql.com/training/), the bit we call "Make The Dolphin Fly" ;-) Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sort_buffer size
Hi Drew, - Original Message - From: "Drew Toussaint" <[EMAIL PROTECTED]> > We have a system in place that is a LAMP application. The > system we have it running on is a FreeBSD 4.3 on a Pentium 3 > 1Ghz with 500MB of RAM. > > A mysqladmin -i5 stat returns: > Uptime: 44254 Threads: 24 Questions: 779657 Slow queries: 531 > Opens: 213 Flush tables: 1 Open tables: 207 Queries per second > avg: 17.618 > > We have a sort_buffer of 32M! Is this to big? Does anyone have any > recommendations of what size it should be? sort_buffer is allocated per thread: memory required for sort_buffer = threads * sort_buffer, so 24 * 32 = 768 MB. Already more than your RAM (and that's without any of the other memory requirements!), plus I'm guessing that 24 threads is not your peak load. Basically, the same rules would apply to sort_buffer as they would for record_buffer, and I know you've got notes for that one ;-) The setting shouldn't necessarily be exactly the same, but similar considerations apply. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: optimize for SELECTs on multiple large tables
Hi again Florin, - Original Message - From: "Florin Andrei" <[EMAIL PROTECTED]> > SELECT event.cid, iphdr.ip_src, iphdr.ip_dst, tcphdr.tcp_dport FROM > event, iphdr, tcphdr WHERE event.cid = iphdr.cid AND event.cid = > tcphdr.cid AND tcphdr.tcp_flags = '2'; Your only search condition is tcphdr.tcp_flags=2. So you will want to put the tcphdr first in the join and have this condition evaluated first (re-order your query and do EXPLAIN SELECT ), otherwise the server will still have to do a full table scan on the first two tables. > The actual table structure is this: > CREATE TABLE event ( sid INT UNSIGNED NOT NULL, > cid INT UNSIGNED NOT NULL, > [...] > PRIMARY KEY (sid,cid), Each of the three tables has this. Now look at your SELECT and the join condition, purely on the field cid. So, MySQL actually has no index to work with. The (sid,cid) can't be used since cid is not the first field; you can use the first part of a key on its own, not the last part (since the last part will have no ordering independently the first part). So, you will want to add an index on cid alone, for the event and iphdr tables (tcphdr table doesn't matter since your select will use the tcp_flags index to limit down the first part of the join, as described above). Making sense? Please post the output from EXPLAIN if the above info does not have the desired effect. Then we can look at exactly what the server is thinking. And of course we also want to hear from you when it's ligning fast! > (it's the typical Snort database, and i don't want to change it, because > many applications related to Snort expect it to be this way) Adding an index shouldn't affect other apps, so that's easy. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: optimize for SELECTs on multiple large tables
Hi Florian, - Original Message - From: "Florin Andrei" <[EMAIL PROTECTED]> > I have this SQL query: > SELECT tbl1.col1, tbl2.col2 FROM tbl1, tbl2 WHERE \ > tbl1.col3 = tbl2.col4 AND tbl1.col5 = '123'; > (well, maybe there are more than two columns selected, and maybe a > little more than two tables, but you got the idea...) > The tables have 5...10 columns, every column is an integer type, and > they have A LOT of rows (the total amount of space occupied by those > tables on disk is 2 GB). > The problem is, MySQL-3.23.46 takes forever to return from SELECT (i let > it run over night, in the morning i still didn't got any results, so i > killed the query). Well, you have to realise that the server can only use a one index per table (for obvious reasons). So, the first question is, which fields are indexed, and which index does the server use for this query. To find out the latter, as well as find out in which order the server joins the tables, use EXPLAIN with your query. The way you'll want to set up a join (applies to any SQL server) is to limit the number of rows as fast as possible. For instance, if a table has a male/female column, putting an index on that will only cut the number of rows to be searched by half, so there'll still be heaps of rows that need to be checked. In a select join, that might not be very efficient. So, first think about these things, to set up your indexes and the query properly. It IS possible to tweak the server for a particular query, if you find that the optimiser isn't picking the best index. If you use STRAIGHT_JOIN instead of the commas, you can specify the join order. And you can also direct the server to use (or ignore) specific indexes (see manual for details). Those are just a few things. > Splitting the big tables into small ones will not help, because i still > want to be able to run the SELECT across _all_ data (and it's not > convenient for my application). > I cannot modify the structure of the tables (the application requires a > certain table structure). You can actually split a table into multiple identical ones, by using MERGE tables (see manual). But that just makes managing them easier (and inserts into one table faster), it wouldn't speed up your SELECT. > So i wonder if there's any way to optimise MySQL so that i will be able > to actually do a SELECT on those tables. > Maybe tweak parameters like join_buffer_size? table_cache? Anyone has > some experience with these?... What's the best way to optimize MySQL for > running SELECTs on multiple large tables? Yes, server settings are important, and the 'right' settings depend on your system (amount of RAM, etc) as well as on your database and the type of queries that you run most often. There is no magic wand, I'm afraid. But... you may find it advantagious to do a MySQL training course (www.mysql.com/training/). Optimisation is a very important part of the course material, in this message I've just touched briefly on a few little items. > This is the only thing that keeps me from deploying MySQL in production > now. > > (Not to start a flamewar, just to acknowledge a fact: i ran the same SQL > statement, with the same data set, on "some other SQL servers", and i > got the result in 5 minutes, as opposed to MySQL still giving back > nothing after several hours. But i want to use MySQL because i need > something that can do INSERTs very fast, and MySQL is very, very good at > this. The only problem is, it is slow when it comes to SELECTs on > multiple large tables, and i'm not sure how to optimize it for that.) I'm confident that the MySQL server can do it just as fast, if not faster. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrade MySQL
Hi, - Original Message - From: "Michael Tam" <[EMAIL PROTECTED]> > I am using MySQL 3.23.41 under Win2000. I wonder what is the easiest > way to upgrade the MySQL to 3.23.46 with my existing data and grant table? > >From what I see is that I need to dump the data tables as files, > uninstall the service, uninstall MySQL ( with the my.ini remains in the > system folder). Then install the latest version import the data back and > manually set the grant tables again. > am I write about this?? No. Well, it's always good to make a backup! But you can simply stop the service (no need to uninstall the service or mysql), and run the setup.exe of the new version. Then you can start the service again. It's that easy. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Problem with GROUP BY ... DESC
Hi, - Original Message - From: "AJ" <[EMAIL PROTECTED]> > > > Ver 11.15 Distrib 3.23.46, for pc-linux-gnu (i686) > > > The problem that has cropped up I have is that in all GROUP BY column DESC > > > statements, the DESC is now not being recognized, and the query is being > > > returned in ascending order. If I use ORDER BY... DESC that works fine. > >I'm seeing the same bug on 3.23.45 (and maybe others but I can't remember > >all the versions I've played with recently). It does the same thing where > >it accepts the syntax but does not respect it (and returns rows in the > >default ASC ordering). > > The problem is apparently solved if you kluge with e.g. > select field, count(field) from table group by field order by field desc > but it IS a kluge. That wouldn't not be a kludge actually, since the SQL-99 standard does not allow ASC/DESC on GROUP BY at all. It is an extention in the MySQL server (see http://www.mysql.com/doc/S/E/SELECT.html), and basically it is a shortcut for exactly what you describe: an ORDER BY with the same fields as the earlier GROUP BY. I tried it too on a 3.23, and indeed it does appear to not be working. Contrary to what is described above though, there is no ordering at all in my output, not ascending either. So basically it is as you would expect it to be without any ORDER BY operation. Maybe the output others got was just sheer luck, as it depends on the order the server reads the data rows Anyway, I've sent a note to the developers about this, including a sample reproducing it. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL access denied from localhost
Hi, - Original Message - From: "mweb" <[EMAIL PROTECTED]> > I know this is a probably a FAQ, and have read > http://www.mysql.com/doc/A/c/Access_denied.html, but everything looks OK to > me. If I look at the mysql db from the monitor I get all the privileges OK > for user "testuser" Log in as root and do SHOW GRANTS FOR testuser@localhost; This will show you all the info used by the server for that user@host combination, which should be easier to read than the raw grant tables. Consider that the server sorts the entries by host, with the most specific host entry first (i.e. wildcards come last). A few notes: - User=" " (blank) is not recommended: corresponds to ALL (any) users. Should only be used for testing. - Do set up and use passwords for all users. - And if you edit privileges directly in the mysql db tables, you have to do FLUSH PRIVILEGES to have the server reload that info; if you use GRANT/REVOKE statements, that is not necessary. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Gemini compile error
Hi Ashley, - Original Message - From: "Ashley M. Kirchner" <[EMAIL PROTECTED]> > While compiling MySQL+Gemini-3.23.41 i received the following error: > [...] > System is RedHat 7.1, with their GCC 2.96-85 No idea about Gemini, but we do have an advisory against using gcc 2.96 on RedHat to compile MySQL, it appears to produce faulty code. We currently use gcc 2.95 to create our binary distribution. > While I'm on the subject, does BDB tables provide the same type of > locking that Gemini does? Is there any reason to favor Gemini over BDB? BDB locks on page level. You'll want to have a look at the InnoDB tables, which provide row-level locking and many other features. See http://www.mysql.com/doc/I/n/InnoDB.html If you're just starting out, I would recommend you grab the latest version from www.mysql.com (3.23.44 or 4.0.0 at the time of writing this message, but 3.23.45 will be out soon). Also, only compile from source if you really need to, otherwise it is recommended to use the binaries as provided by MySQL AB which have been compiled with optimal settings, and tested with our test suite before release. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: possible to loop through increments when end reached?
Hi Tom, - Original Message - From: "Tom Beidler" <[EMAIL PROTECTED]> > I have a database of sites for a grounds maintenance company. I want to flag > sites for service. Each site has a service order, 1-875. I want to keep 25 > sites flagged for service. Once a technician works on a site and posts a > service report, I set that sites flag to 'no' and would like to flag the > next one in line to 'yes'. Not a direct answer to your question, but just thinking of another way to make your system work and also circumvent the issue with service order # wrapping. How about you store a "date of last service" for each site, that is updated when a technician has visited a site? Then you can always select the sites with the 25 oldest dates (ORDER BY last_service_date LIMIT 25) without the need for trickery with flags. Of course you'll need some safeguards, but that applies to the other method too. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: localhost problem
Hi, - Original Message - From: "Pafo" <[EMAIL PROTECTED]> > i am having this problem, ( http://www.pafo.net/mysql.gif, a gif file that illustrates the problem ) > i am using a firewall and i am connected to the internet all the time. The latter shouldn't be an issue, since according to your screendump you want to run the MySQL server locally and connect to it locally as well? Anyway, the prob is that you are not starting the server properly, and you may also want to adjust your configuration. Just follow http://www.mysql.com/doc/W/i/Windows_installation.html and next pages, for instructions all the way. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Getting Started
Hi Dan, - Original Message - From: "Dan Buckner" <[EMAIL PROTECTED]> > I am trying to get MySQL 3.22 (off a CD from a manual) running on Windows > 98. I have installed it on my C drive and am trying to run the following > command from DOS: > c:\mysql\bin>mysqld --standalone > I can get to the mysql\bin directory, but when I type the > mysqld --standalone, I get a Bad Command or file name error. > Any suggestions on how I resolve this problem and continue?? Well the bin directory should contain a mysqld.exe, but you don't report whether you've verified this. Anyway, if you're only just starting out, I would suggest you grab the latest 3.23 or 4.0 version via www.mysql.com and start with that. Follow the instructions of the manual included with that version, or online at http://www.mysql.com/doc/W/i/Windows_installation.html No sense in starting out with such an old version Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: dec is treated as a reserved word.
Hi Carsten, Tom, - Original Message - From: "Carsten H. Pedersen" <[EMAIL PROTECTED]> To: "Tom McGlynn" <[EMAIL PROTECTED]> > > >Description: > > The name dec does not work as an unqualified name > > when creating tables on inside queries. A syntax error > > is given. If escaped as `dec`, no error is given. dec is > > not on the list of reserved words. Thus this could be > > either a documentation or software bug. > It's a documentation bug. Although not on the list of reserved > words, the manual does say: > --- > 6.2.1 Numeric Types > ... and the keyword DEC is a synonym for DECIMAL. > --- Yea. We use a script to grab the reserved words from the source; it's not a straightforward dump of all symbols since many are in fact allowed. Based on your info I checked the script and made some changes: 2 keywords and all 15 synonyms had been missing. DEC was one of the synonyms, of course. The online manual should reflect this change soon. So, thanks for the feedback! Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question!!!
Hi, - Original Message - From: "Webmaster" <[EMAIL PROTECTED]> > I have Mysql,Php and apache webserver running on Freebsd 4.3 RELEASE, I > need to connect via Web to an application done in php but that requests me > user and password. You may wish to be more specific about exactly what you are trying to do, posting relevant PHP code and the error message you get. Most likely you have not set up your permissions in the MySQL server yet, but it's a bit hard to tell with so little info! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to handle circular references if DB enforces ref. integrity
Hi Bennett, - Original Message - From: "Bennett Haselton" <[EMAIL PROTECTED]> > In a database that enforced referential integrity, if you say that a field > in one table is a foreign key referencing another table, then any value in > the foreign key field in the first table must reference an existing row in > the second table. (Right?) Yea. > But suppose you have a database storing, I dunno, buses, their occupants, > and the driver of each bus, so you have a table BUS, and one of the fields, > "driver", is a foreign key field specifying the SSN of the person who > drives the bus. Then you have another table, "person", with a foreign key > field "BUSNUMBER" that references the VIN number of the bus that the person > is on. Assume that every bus has people on it and every person is on a > bus, so neither foreign key field can be NULL. (Stupid example in terms of > relation to the real world, but I can't think of anything better.) In > general, you have a one-to-many relationship from one group to another, and > then a one-to-one relationship from the second group back to the first one, > and the constraints of the "real world" that you're modeling suggest that > neither one of these can be NULL. > > The problem is, how would you add a new bus and a new driver to the > database? Whichever one is added first, you're going to get an error > because its counterpart doesn't exist yet, violating referential integrity. > > You could always relax the NOT NULL constraint, but that might not be > desirable if you want to be consistent with the real-world situation that > your database describes. Is there a way to update two tables at the exact > same time so that referential integrity never gets violated? You can do this by putting the two updates inside a transaction, using the InnoDB table type instead of MyISAM. No problem. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do you find out which table fields are foreign keys?
Hi Bennett, - Original Message - From: "Bennett Haselton" <[EMAIL PROTECTED]> > Just wondering if anyone who knew the answer to this might have missed it > before -- sorry to keep nagging but I really need to find out how, or > whether, you can determine which fields in a table are foreign keys > referencing another table. > > Is it not possible to determine, after a table is created, what fields in > the table are foreign keys? For a MyISAM table (where referential > integrity is not enforced), is this information even retained? Not yet for MyISAM, see http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html See also the info about InnoDB, which may do what you want. >From a coder's point of view, I always give such columns a special name so I know it references another table, and perhaps also also which one (and through which column). I think that's good coding practice. This just keeps the situation very clear to me, while coding, without having to look up the table schema or rely on constraints. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: pdf and html-split manuals
Hi Michael, - Original Message - From: "Michael A. Peters" <[EMAIL PROTECTED]> > I'm looking for the pdf and the html-split manuals for MySQL-3.23.44 > The one page manual is included in the source distribution, but all I can > find on the website for split and pdf versions are for MySQL-4.0 alpha It's basically the same thing, what you find in a distribution is simply a snapshot of the manual at the time of the release. Changes and enhancements are continuously made in the manual, so it is definitly good to grab the latest one from the website, or use the online searchable manual (www.mysql.com/doc/). If there is a change in server behaviour since a specific version, this is also documented, so there should be no confusion. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: connection question
Hi, - Original Message - From: "Brent Simpson" <[EMAIL PROTECTED]> > I've been reading up on this most of the day and still can't get it! You'll say DOH! later ;-) > I need to use PHP to make connections to MySQL. When I use my actual > host name to make the connection I get this: > Warning: Access denied for user: '[EMAIL PROTECTED]' (Using > password: YES) in /var/www/html/php/db_mysql.inc on line 73 > Database error: pconnect(foo.bar.ucla.edu, mysql, $Password) failed. > MySQL Error: () > Session halted. Do you actually see $Password in the error message? In that case I would think (but can't be sure since you didn't post your PHP code - please do that next time!) that you may have used singe quotes around $Password in your pconnect() call as well as for your error printing. Either remove the single quotes, or change them to double quotes. PHP does not expand variables for single-quoted strings. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug Report - SELECT Does Not Recognize Column Alias
Hi Rick, - Original Message - From: "Rick Emery" <[EMAIL PROTECTED]> > Description: > The SELECT statement fails to recognize that an alias (mycol) was set for a > column. Note the column name is created from a table alias (c1) > > mysql> select c1.id as mycol from colors c1 right join colors c2 on > c1.color=c2.color AND c1.id != c2.id where mycol is not null; > > ERROR 1054: Unknown column 'mycol' in 'where clause' This is actually correct and documented behaviour, see: http://www.mysql.com/doc/P/r/Problems_with_alias.html Quoted from there: [snip] ANSI SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. For example, the following query is illegal: SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id; [snap] I have now put an explicit reference to this section in the explanation of the SELECT statement in the manual, as I'm sure you're not the first to trip over this one ;-) Thanks for your feedback! Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Solution for testing PHP/MS Access on Linux
Hi, - Original Message - From: "mweb" <[EMAIL PROTECTED]> > some days ago I asked help on this list because I have to develop > and test on Linux/Apache some PHP pages that will have to run on > on an IIS/NT box. Regarding wrappers, have a look at http://php.weblogs.com/ADODB The ADODB wrapper (PHP) is fast, and has many very good features. That way you can get to Access or MySQL directly (or via ODBC if you really want to ;-) Going to MySQL through ODBC incurs a performance penalty (because of the ODBC layer) that might actually be greater than going through a good wrapper. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: automatic index for not null unique columns?
Hi Steve, - Original Message - From: "Steve Fink" <[EMAIL PROTECTED]> > > I couldn't find an explanation of this behavior in the docs. When I > > use the following CREATE command: > > CREATE TABLE D6 (id int(10) not null unique); > > it appears to automatically create an index for me: > > Argh! Never mind. Learn something new every day -- the 'unique' > keyword *is* a definition of an index. Weird. > > So maybe I should change this to a feature request: when MySQL hits > one of the kajillion wacky parts of SQL that implicitly name indexes, > could it check whether its default name is a keyword before creating > it? That's what breaks the reasonable expectation of mysql being able > to read its own dump files: you create a table with an indexed column > named 'version', and mysqldump renders that as 'KEY version(version)', > which bombs, instead of (eg) 'KEY (version)', which would be fine. I just tried it with mysqldump 3.23.41, and there is no problem there because mysqldump puts a space between version and (. If you do SHOW CREATE TABLE from a client, the output uses backticks like `version` which also prevents any probs. So, sneaky though it may be, it will actually work! ;-) You said you were using 3.23.32 if you get different out there from described above, it has been fixed since. Okidoki? Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: OT: Securing username and password in script file
Hi Tim, - Original Message - From: "Tim Hewitt" <[EMAIL PROTECTED]> > It would be nice if mySQL supported some form of encrypted > login where the username and password could be decrypted > internally somehow. Security through obscurity isn't REALLY safe. It just hides it a bit. Anyone could still get to it, with a bit of effort. > Perhaps a test on what directory the script is running from... > I don't know, I'm reaching here. It just seems that between the lack > of a unique username running Apache in a directory on a virtual > server, and the common practice for usernames and passwords > to be stored in plain text for use by scripting languages, a more > systematic approach to securing access would be useful. > Maybe I'm missing something else I could be doing here. Any ideas? You can make PHP real picky by using safe_mode and other security settings (see PHP manual). For example, when safe_mode is on, PHP checks to see if the owner of the current script matches the owner of the file to be operated on by a file function. In other words, with PHP's global configuration set like that, other PHP users will not be able to even read your files. Problem 100% solved. I used to go the easy road and use a fairly generic config for PHP, with register_globals and so on however, this is insecure both from the inside as well as the outside. For instance, people will be able to feed your scripts some extra global variables by simply putting them in the URL! They could stumble upon one that you actually use but forget to initialise. Now there's potential! So, now I use safe_mode and a whole lot of other stringent settings. You will need to rewrite your PHP code a bit, but it is good practise. Regarding the example above: I also develop my code with all warnings on (level 15), so PHP warns about using an uninitialised variable. Below are some PHP configuration settings that you might want to use: [snip] ; After this your current scripts won't run, that's for sure! ;-) ; You will need to start using $HTTP_POST_VARS[] instead of the global vars, and possibly heaps more. ; But it's worth it! ; The settings are not only for security, but also to enforce some good code design behaviour ; (which will indirectly be good for security, too!) ; If you use these settings and have a suggestion or other feedback, ; please e-mail [EMAIL PROTECTED] (Arjen Lentz). asp_tags=Off short_open_tag=Off allow_url_fopen=Off warn_plus_overloading=1 ;doc_root =... ;open_basedir=... ignore_user_abort =1 max_execution_time=60 display_errors=1 ; Set to 0 for production. log_errors=1 error_log =/logs/phperror.log error_reporting=15 html_errors=1 define_syslog_variables=Off ;variables_order="EGPCS" magic_quotes_gpc=1 register_globals=Off register_argc_argv=Off safe_mode=1 disable_functions=readfile,system enable_dl=Off file_uploads=Off; Enable if you actually use this. post_max_size=32K [snap] Remember that websites are open to the world. There's always some nutcase out there with too much time on their hands; and that's just the harmless ones! If someone really wants to do damage, I wouldn't want to make it too easy for them Right, and now back to our MySQL topic! ;-) We'll be sure to include this kind of security information in our "Using PHP with MySQL" courses that are being developed. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL has transactions, foreign keys, and an option for NO DEFAULT
Hi Kodrik, - Original Message - From: "Kodrik" <[EMAIL PROTECTED]> To: "Franklin Schmidt" <[EMAIL PROTECTED]> > * Transaction > * Subselects > * Foreign keys > * Stored Procedures > Do you actually believe that the lack of NO DEFAULT is much worst than those > features above, which cannot be as easily worked around? Please allow me to clarify a few items here, since we don't want misunderstandings to linger around forever 1) MySQL supports transactions since 3.23.34a, with the InnoDB and BDB table types. Using the the InnoDB table handler you have commit, rollback, crash recovery capabilities, row level locking, consistent non-locking read, and much more... See http://www.mysql.com/doc/I/n/InnoDB.html and elsewhere in the manual for heaps of info. 2) Foreign keys are supported by the InnoDB table handler from 3.23.44. http://www.mysql.com/doc/S/E/SEC427.html 3) The other items you mentioned are already part of the 4.x development track. http://www.mysql.com/doc/N/u/Nutshell_4.1_development_release.html For the curious: implementation of these new features should not adversely affect your performance if you don't use the features. In simple terms: trust Monty! ;-) 4) Finally, MySQL does in fact have a NO DEFAULT option. This was kindly pointed out by Paul DuBois a few days ago. The DONT_USE_DEFAULT_FIELDS compile time option has existed since as early as version 3.20.14. >From the manual: "You can configure MySQL not to use DEFAULT column values for non-NULL columns (that is, columns that are not allowed to be NULL). This causes INSERT statements to generate an error unless you explicitly specify values for all columns that require a non-NULL value." See http://www.mysql.com/doc/c/o/configure_options.html I do believe this option addresses the issue put forward by Frank and Shelby. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Active maintenance of 3.23 tree
Hi, - Original Message - From: "DownloadFAST.com" <[EMAIL PROTECTED]> To: "Ben Gollmer" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> > Or does MySQL actively support, debug, fix, and go back and > maintain older releases? That is indeed the case. The 3.23 and 4.0 branches co-exist, and we still do 3.23 releases for bugfixes, as you can see in the change log (http://www.mysql.com/doc/N/e/News-3.23.x.html). There have already been 3.23 releases since the release of 4.0.0. The two branches are quite inter-related. Fixes to one are also applied to the other, where appropriate. Do read some more about MySQL's design and development philosophy, there is lots of information about this throughout the manual (http://www.mysql.com/doc/). You can also download a recent version for offline perusal (http://www.mysql.com/documentation/mysql/alternate.html), for instance in HTMLs, or PDF format (if you want to print it for comfy reading). Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Licensing question
Hi Shankar, - Original Message - From: "Shankar Unni" <[EMAIL PROTECTED]> > What are the rules about "bundling" now? If we distribute a (standalone) > copy of MySQL with our product, does that expose our product to the GPL? Or > is it just like distributing a copy of Emacs with your OS? (I.e. as long as > you make the source available, it doesn't automatically GPL the rest of > your OS?) > What if the product is designed to work with many databases, but we want to > distribute MySQL only as a "default database" (i.e. it doesn't depend on > MySQL for its functionality - it's merely a convenience)? Does that change > the GPL liability on our product? There is a recently rewritten section of the manual about this: http://www.mysql.com/doc/L/i/Licensing_and_Support.html The licensing subsection has some examples on when a commercial license is required and when it is not. You will also find contact info there, in case you have more questions. Hope this helps. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Actively contributing to MySQL development
Hi Shelby, You wrote: > I would just want to be a contributor. Excellent! You, and anyone else, can be a contributor to MySQL development. There is some special info in the manual about this: http://www.mysql.com/doc/M/y/MySQL_internals.html See also http://www.mysql.com/doc/I/n/Installing_source_tree.html We frequently receive very good code patches, and as the users know these are much appreciated. We also get other useful feedback such as issues for the manual. You may find a list of some of the contributors in the past at http://www.mysql.com/doc/C/o/Contributors.html With regard to the copyright and licensing of MySQL, you can find more information here: http://www.mysql.com/doc/C/o/Copyright.html Amongst other things, it is important to note that the manual is not distributed under GPL. We look forward to seeing your contributions! Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The schedule of MySQL 4.1
Hi Jaska, - Original Message - From: "Jaska A" <[EMAIL PROTECTED]> > I'm interested in the subqueries, and thus interested in the schedule of the > 4.1. So, even if there is not (?) any precise information, what do MySQL > developers/managers think that when will it be released? The aim is around December/January for alpha release. See http://www.mysql.com/doc/N/u/Nutshell_Stepwise_Rollout.html Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: throughput
Hi Bob, > I am looking for some information on throughput > (I.E. transactions per second) for MySQL. > Anyone have some or some suggestions? > I realize there is no hardware listed. Nor anything else ;-) Performance also depends on your database, type of query, etc. Generally speaking, MySQL is extremely fast. If queries become slow, it's generally time to look at your table and query design, and MySQL configuration settings. Only in the last instance would one look at hardware. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: merging fields
Hi Brendan, > i have a HUGE database (30+megs) which has just been converted > from microsoft access to mysql (yay!) .. Indeed yay! ;-) But eh, 30+ megs isn't huge these days, you have to go into gigabytes for that ;-) And yes, there are serious MySQL users out there that have multi-gigabyte databases! No problem for MySQL > the original designer had five separate fields for keywords > ie keyword1, keyword2, keyword3,keyword4,keyword5 > I would like to create a new field called 'keywords' (very original i > know) which has the value from each of these fields separated by a comma. > ie. keywords=' keyword1, keyword2, keyword3,keyword4,keyword5'; Ehm, what are you trying to do, exactly? See, it might be better to create an extra table with two fields, one ID reference (to the entries in the first table) and your keyword. Then, for each keyword (unlimited number), you simple have an idref,keyword entry in the keyword table! SELECT can grab the two together easily And it will be way faster too: depending on how you do your search of the keywords, you may be able to do without a LIKE '%key%' statement. In your 'keyword,keyword,...' solution, you'd be pretty much stuck with that. It could be a plain indexed search! > i need to loop through the entire database and do this for every row.. > i could write a php script to do this but it would probably cause a cgi > timeout .. so i would rather do it from the mysql command line.. (i am > on win2k) Well that's no problem! You can modify the timeout in a PHP script with the set_time_limit() function. You may also want to look at the INSERT ... SELECT style statement, that could provide a very simple solution. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: databases
Hi Benjamin, Andre, > > i have a mysql server on a dual xenon with 2GB ram > > with 300 Databases (for each customer one) > > but in the meantime a simple query takes ages to complete >10s Generally, slow queries are caused by inadequate configuration of the server and/or badly formed queries (sometimes in combination with table design). These two things should always be looked at first. Even if you need to get some additional training or expert support, it will save you heaps of money no need for heavier hardware. If you post your table structure and sample query on the list, we could have a quick look at what things can be optimized. I have recently sent a reply to another user on the list which also talks about the basics of server configuration. You may find it of help for your situation too Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple field separators
Hi Charles, - Original Message - From: "Charles Lewis" <[EMAIL PROTECTED]> > I have the following command: > mysqlimport -d -c, --columns=DATE,TIME,MSG --fields-terminated-by=, > --fields-enclosed-by=, dbase c:\file.txt > This reads the following from a text file: > 26 Sep 2001, 05:42:24 : Text message to be read. Of course the unix freaks are right about the abundance of nice tools there, but this particular problem can be resolved quite easily on DOS/Windows too: Load the text file into a text editor (DOS' EDIT.EXE will do) or any word processor, and do a global search&replace as follows: - Search for comma followed by space, replace with comma. - Search for space colon space, replace with comma. Save the text file (in case of a word processor, make sure it stays plain text). Now all your fields are separated with a , and you don't need to specify any inclosure characterrs. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: full-text search problem
Hi Anton, - Original Message - From: "Tichawa Anton" <[EMAIL PROTECTED]> > I have problems with full-text search. My table contains text lines from > computer-generated log files rather than documents written by humans, > and so my first problem is that I need an exact match rather than a > relevance value, i. e. if the search string is 'ai', I really need all > rows containing 'ai' to appear in the result, even if this includes > 99 % of all rows. Using MySQL FULLTEXT index doesn't appear suitable for your particular application. I would suggest simply using the LIKE function. For more fancy stuff, use REGEXP (RLIKE) with regular expressions. > Also, I would need to switch between 'case-sensitivity yes/no' and 'search > for whole words only yes/no'. Case sensitivity is possible with LIKE by for instance specifying BINARY. For regular expressions it simply depends on the pattern you specify. Search whole words yes/no can again be done fairly easily by having to different patterns. > Currently, I load all rows to the client and do the search on the client. > Is there, or will there be, a way to do this kind of text search on the > server? Shouldn't be a problem. See above. Another suggestion since the logfiles are computer generated, they might have a very fixed format. In that case, instead of storing them as text, you may want to parse the text lines and store the information in separate fields. Then you can index individual parts (timestamps, command groups) which will speed up searching as well as enhance your other search capabilities (summaries for a certain period, etc) by actually utilizing the facilities instead of simply using the database for storage. RDBMS are very good at summarizing, selecting sets of data according to search parameters, etc. But you have to design your tables with this in mind, otherwise you miss out on all the benefits. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Use of MySQL with large tables
Hi Stephen, - Original Message - From: "Stephen Faustino" <[EMAIL PROTECTED]> > We are encountering two issues when using MySQL with large tables > (by large, we're talking > 1 million rows). Our application is written in > Java and we are using the mm.mysql JDBC driver. We run our > applications using both Oracle and MySQL. Below are issues that > we have experienced. I'll leave the Java API interface issues to the techies, but there are some things you can do that should always be beneficial in these kinds of situations: > [...] Unfortunately, each LIMIT query takes longer > and longer to return results, which means the overall select proceeds in > exponential time. As an example, we tried to query all of the rows for a > table containing 18 columns with 1.8 million rows (on a PII 450 w/ 128M Additional RAM on the server is always good for performance. 128MB isn't very much, and given the cost of RAM these days, it's a cheap path In this particular case, the MySQL server may not be able to keep the entire result set in memory, so it will have to be swapped to disk. I would guess this is at least in part responsible for the slower response times on the later LIMIT statements, since the way you have set it up, the server has to put together the result set first (including any ordering), swapping parts to disk (also during ordering), and then has to pick a chunk of rows from the middle, involving yet more swapping. Depending on the size of your rows, adding more RAM may allow the server to do the whole operation in memory. Of course you will want to look at mysqld options like record_buffer, sort_buffer and temp_table_size, as well as key_buffer size for the indexes. See the manual for information on these and other options. Another thing you might want to consider is first storing the large 1.8 million row result set (ordered and all) in a temporary table (if you do this with INSERT SELECT or CREATE TABLE ... SELECT it can be done without any data transfer between server and client). Then you will be able to do very fast simple SELECT ... LIMIT queries, particularly if you add a good (primary) index to allow the server to process the LIMIT clause even faster. >From a programmer's perspective, you may want to wrap all this in a simple class that uses a select statement to create the temporary table and allow you to pick (sets of) rows at will. FYI, optimization techniques are an important part of MySQL training courses (http://www.mysql.com/training/). Regards, Arjen. -- Get Official MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Window-based client program for MySQL (server in Linux)
Hi, - Original Message - From: "Biniam Gebremichael" <[EMAIL PROTECTED]> > I have MySQL server installed in my linux box. And want a windows based > client program that can be used to create and modify table designs and > other functions. > Is there any PHP client for such purpose A very nice tool is phpMyAdmin which can be found at www.phpwizard.net/projects/phpMyAdmin/ > or any library to enable Microsoft Access to interface the server. This can be done with MyODBC (download from www.mysql.com). See also the MySQL manual for more details. Finally, there is MySQLfront (www.mysqlfront.de), a native Windows tool that can connect directly to MySQL (ie. no MyODBC layer required). Regards, Arjen. -- Get Official MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggressive dolphin?
- Original Message - From: "Theo Richel" <[EMAIL PROTECTED]> > I am a very dedicated user of Mysql, but I am somewhat disappointed that you > chose the dolphin for your logo because of the good nature of this animal. > [...] > Unlike most animal killers, which eat their prey, dolphins seem to have > murderous urges unrelated to the need for food'. I suppose that with intelligence also comes the concept of "having a bad day". ;-) You will agree though that in the public's perception, dolphin's are very much liked and perceived as good natured animals. Of course this is a "cute & fuzzy" view of reality. But so what? For instance, do you like koalas? Of course you do. They're cute, soft, furry animals, right? Wrong. They're smelly, bad tempered dudes, their fur is quite rough, and the only reason they don't scratch or bite you straight away (and they have serious claws!) is that their foodsource (eucalypt) does not leave them with a lot of energy, so they're pretty dopey most of the time. Still, everybody loves them! As for choosing logos, how about that philosophical GNU gnu? Have you ever met a real gnu face to face? They're bad tempered, meeting them is bad news. You do not want to meet one, and if you do, there's no time for philosophy: you back away and get out. But, isn't it a great logo! Ditto for the Perl camel. Everybody knows they have moods and bad attitude. This doesn't seem to reflect badly on Perl, does it? There is some public image for every animal, and nearly all of them will be warped (ie. factually wrong). Acknowledging that, is there a real problem with it? Shouldn't kids play with those stuffed toys because it might give them the wrong idea? And, shouldn't we use any animal logos whatsoever for the same reason? That seems a bit harsh especially since marketing is all about perception. I like the new MySQL logo, simply on artistic grounds. It looks good. Disregarding the fuzzy/biology argument for the moment, don't you think it looks good? Regards, Arjen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Logging connections
- Original Message - From: "Tadej Guzej" <[EMAIL PROTECTED]> > How would I log connections to MySQL server? > I need the time user connects and the time user disconnects. It's a workaround, but the general MySQL log actually has this information. Here's a sample: /usr/sbin/mysqld, Version: 3.23.40-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument 010314 21:56:41 3 Connect [EMAIL PROTECTED] on [...] 010314 22:00:00 3 Quit 010314 22:31:23 4 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 010314 22:32:02 5 Connect [EMAIL PROTECTED] [...] 010314 22:33:30 5 Quit As you see you have the time and date, then the connection ID, then the command. You just pick the Connect and Quit commands (probably you need client aborts as well) for each connection ID with the date/time, and pick the address from the connect line. If you do log rotation, you can use a simple script to analyse the old log and stuff the results into a database table (just don't forget that the connection ID will be reset if you restart the server so you can't use it as a unique key in the table). Chances are someone has already done something like this so you may just be able to find the script on the net! Will this do what you require? Regards, Arjen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
