MySQL 5.0 character sets
Hi, I'm new on this list, so a very short intro about me: I'm Andras Barthazi, a Hungarian web developer. I like MySQL very much, I'm using it since 3.x versions, so I think I know it very well. But... So, I started learning, how MySQL 5.0 handles character sets. It is, what I think about it: character_set_client | latin1 This is, what the client thinks about itself, determined from the environment. It will be used for autoconverting character data sent from the client. character_set_connection | latin1 This is, what the client thinks about the server, but I'm not sure, how it will be determined, and what exactly is it for. As the documentation says, it will be used for autoconverting character data sent to the server. character_set_database | latin1 It's the general database default character set, used when you create a table. No more additional meaning. ? character_set_results| latin1 It is, what the client thinks about itself, and will be used for converting character data coming from the server. character_set_server | latin1 Pass. character_set_system | utf8 Pass. Am I right? Am I miss something? I think the documentation is not clear enough in this area. And another strange thing I found: you can set the table's (default) character encoding, but I see no effect. The table data - of course - won't be converted (that's another alter table command), the results I got won't be changed - so I don't know, what exactly default table character set is good for. Can you point me to the documentation, or can you explain your experiments? Thanks, Andras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How Can I upgrade TPC-C performance test result for mysql
George, there seem to be two bottlenecks: 1) InnoDB has contention on the buffer pool mutex; 2) the workload is also disk-read-bound. For 1), we might have an improvement available in the future. We must let the threads leave the 'wait array' in sync0arr.c without reserving the wait array mutex. Also, we need to study if we can reduce the code that is executed when we have the buffer pool mutex reserved. To reduce 2), you can try increasing innodb_buffer_pool_size further. But be careful that Windows will not start swapping. What does the Task Manager say about CPU usage during the test? Regards, Heikki Oracle/Innobase ... Dear Heikki, we change the system's environment. now we use RAID0 disk to store the InnoDB data file,and other disk to store log file. follow is system's information: CPU: 4X2.8GHz RAM: 1G Disk: 4X36G the SHOW INNIDB STATUS\G result is: = 051021 10:51:59 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1416712, signal count 577079 --Thread 2364 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c line 560 for 0.00 seconds the semaphore: S-lock on RW-latch at 01471B80 created in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 139 a writer (thread id 2400) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c line 560 Last time write locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 489 wait has ended --Thread 2408 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 1286 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2400 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 489 for 0.00 seconds the semaphore: X-lock on RW-latch at 01471B80 created in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 139 a writer (thread id 2400) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c line 560 Last time write locked in file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c line 489 --Thread 2332 has waited at ../innobase/include\log0log.ic line 315 for 0.00 seconds the semaphore: Mutex at 014740D0 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\log\log0log.c line 744, lock var 1 waiters flag 1 --Thread 2420 has waited at ../innobase/include\log0log.ic line 315 for 0.00 seconds the semaphore: Mutex at 014740D0 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\log\log0log.c line 744, lock var 1 waiters flag 1 --Thread 2404 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2380 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2348 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2424 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 1088 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 380 has waited at e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 1088 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0 waiters flag 0 wait is ending --Thread 2392 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00 seconds the semaphore: Mutex at 01471880 created file e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c line 531, lock var 0
Adding stop words table in german language
Hi there, as mysql docs describe, there is a stop words table by default: http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html Does anybody know how to add a german table ( I guess there is an equivalent to the engl. one)? Does this also work with MySQL 4.0.18? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding stop words table in german language
Hi Merlin, you can create your own stopword file (one word per line) and activate it in my.cnf like this: # The MySQL server [mysqld] set-variable= ft_stopword_file=/etc/my.stopwords HTH, Thomas On Fri, 28 Oct 2005, Merlin wrote: Hi there, as mysql docs describe, there is a stop words table by default: http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html Does anybody know how to add a german table ( I guess there is an equivalent to the engl. one)? Does this also work with MySQL 4.0.18? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: will a cluster be faster than a heap-table?
Hi Brent, Wow, it seems like you are going to extremes. To jump from myisam to heap is a big step. Did you try using InnoDB? It would handle locking issues much better since it doesn't lock the table. Heap tables can be pretty dangerous since it's all in memory. If the machine crashes, you'll lose the data. I know that, but I do regular (cornjobs) backups to myisam-tables and I can reconstruct the whole table from the machines that insert/update the data. Based on your information, you want to get the best disk I/O you can. You won't get that out of a single IDE drive, even if it is one of the latest SATA based with command queuing. I don't think you'll get anything faster than heap tables and tons of RAM. But there is certainly finite scalability because of the use of RAM. Clusters may be the way to go for scalability, but I would work on getting your data disk based for maximum scalability. For my case, scalability means more updates/second and more selects/second. Not larger tables. At least not much larger, and this table is using less than 300 MB of memory right now. So I see no point in using anything disk-based. 2 years ago we started with myisam, then changed to innodb, found out it wouldn't give any better performance in our case and switched back to myisam since that makes the setup of replications much easier. Then we changed it again 6 months ago and now use memory-tables. I would try InnoDB and maximize you disk setup. I don't know how many disks you have in your RAID and if it's hardware or software based. More disks will add speed by splitting the load across more disks. Just keep in mind the limits of your SCSI card too. You may need to add a card to split the load. These two systems have Hardware-RAID (SCSI storage controller: LSI Logic / Symbios Logic (formerly NCR) 53c1030 (rev 07)) width 2 disks in raid1 and the DB is myaybe 40 Gb of size. I have no performance trouble on any other table. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug? Set Null Value in NOT NULL field...
Hi! LMS wrote: Jeff Smelser escribió: On Wednesday 26 October 2005 04:24 pm, LMS wrote: Hi, I have this structure: --- CREATE TABLE tabla ( id int(10) unsigned NOT NULL auto_increment, nombre varchar(100) NOT NULL default '', because your defaulting it to ''.. so null = '' on insert.. Jeff Ok thanks for your answer, but I can't set any other property, and I need that it gives back an error, because it would have thus to be. This seems to be a problem of history: It was not done in previous versions, so introducing it now might break old applications ... Please see the manual, especially sections 1.8.6.2 and 5.3.2. AIUI, using a strict mode (new in 5.0) is the way for you. Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting: maximum size of status variable
Greetings, I've been keeping track of Bytes_sent and Bytes_received for a while in the fashion of 'mysqlreport': divide those values over Uptime in order to obtain a data rate (bytes/sec). The resulting graphs look like this: | | | /| /| |/ |/ | | / | / | / | / | /| / |/ |/ +-- time - Bewildered I started troubleshooting, and I think I have found the cause: the value of Bytes_* has a maximum value of around 4GB, or the size of an INT UNSIGNED. Can anyone confirm that this is the max value for status variables? -- Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to use a conditional in this UPDATE
You can nest the IF statement, putting another where 'soon' is like Jasper suggested. Or you can use the CASE WHEN THEN construct if you have a lot of conditions you need to check for. On Oct 28, 2005, at 12:22 AM, Scott Haneda wrote: on 10/27/05 6:34 PM, Scott Haneda at [EMAIL PROTECTED] wrote: Right now, my update works as follows, and works fine, however, I want to do a condition to say if products.ship_status = 1 then set cart_test.shipping_status to 'now' else set it to 'soon' UPDATE cart_test, products SET cart_test.shipping_status = products.ship_status WHERE products.id = cart_test.product_id AND cart_test.session_id = '5511' Is this even possible, if so, can someone help me out a bit. Thanks I think I partly got it: UPDATE cart, products SET cart.ship_status = IF(products.ship_status = 1, 'now', 'soon') WHERE products.id = cart.product_id AND cart.session_id = 5511 However, I need a ELSE in there, I can not always assume other than '1' is 'soon', is there some way to add in a ELSE? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
random permission denied issues
I am having a very odd issue with one of my mysql 4.1.14 servers.
Randomly, the server returns permission denied to queries even if
identical queries succeeded within the same connection.
To test the problem, I setup a script that connects to the database, and
issues the same query 50,000 times. Out of the 50,000 attempts, about 10
fail, but the exact number varies. The error message in all cases is:
SELECT command denied to user 'user'@'host' for table 'example'
There are two web servers that use the same database. Both web servers
have this problem.
Here is a test script I use to illustrate the problem:
[EMAIL PROTECTED]($host,$user$password);
while ($i5) {
$x=mysql_query($query);
if ( $x) {
print $i worked!BR;
} else {
print $i ERROR! .mysql_error().BR;
}
$i++;
}
I did not set up any mysql rate limits. The database is 'busy' but not
extreme (mytop shows about 100 queries/sec, 99.91% key efficiency)
usually about 10-20 threads. Query cache is enabled (and the above query
should take advantage of it). Database and Web Server are on the same
LAN separated by a firewall.
The system is a RedHat ES system, with MySQL rpms from mysql.com (i386),
recent 2.4 kernel.
Any ideas?
signature.asc
Description: OpenPGP digital signature
GROUP BY Destroys 2nd Function
I have a PHP script that displays data like this: Eurasia Eurasiasupisland/sup Africa Where Eurasia and Africa are mainland parents of ecological regions and Eurasiasupisland/sup is a parent of an ecological system that is associated with a continent. For example, Borneo would be Eurasiasupisland/sup. The finished script will display an animal species' distribution. Obviously, I don't want to say it lives in Eurasia Eurasia. Instead, I want to group them together, so an Old World species like the leopard might look like this: Eurasia Africa ...no matter how many ecological regions it inhabits on either continent, mainland or island. The problem is that when I add GROUP BY to my command, I lose the superscripts. It appears to favor a particular row, and if that particular row represents a mainland ecoregion, then EVERYTHING is defined as mainland. My script also displays footnotes that will eventually name the islands it's native to. These, too, disappear when I use the GROUP BY command. Is there a simple solution you can think of? If not, can you think of some sort of workaround, like a separate table listing islands that I can somehow plug into the system? Normalization isn't a priority; what I'm doing is already over my head, and my primary goal is user friendly - simply coming up with something that works. Below are some simple diagrams of my tables. Thanks. ANIMALS TABLE Canis_lupus | wolf Panthera_tigris | tiger JOIN TABLE SPECIES | ECOREGION Canis_lupus | NA1008 Canis_lupus | NA1010 ECOREGIONS TABLE ID | NAME | Geog | Geog2 NA1008 | Alaska tundra | na | na IM1003 | Philippine rainforest | eur | phl (Note that mainland ecoregions feature the continental ID in each of the last two columns, while island ecoregions feature the island's ID in the last column.) GEOGRAPHY TABLE ID | NAME na | North America phl | Philippines __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very Strange command denied to user errors
Hi, I've got a very strange problem with one of my MySQL servers. I've got 2 dedicated servers with MySQL 4.0.21, all is fine there. My new server with MySQL-4.1.14 give me some headakes ! Sometimes i've got errors like UPDATE command denied to user 'MyUser'@'192.168.0.4' for table 'MyTable'. But 99% of time, theses queries are OK. MySQL privilges haven't changed since several days. Plateform description : Severals RedHat 9.0 webservers with apache, glibc, MySQL-devel-4.0.21-0.rpm and MySQL-client-4.0.21-0.rpm. Client program is a C language program built against mysql librairies (MySQL-devel-4.0.21-0.rpm). All webservers can access to severals MySQL servers against Lan. SQL1 and SQL2 are running MySQL 4.0.21 (mysql-standard-4.0.21-pc-linux-i686.tar.gz), all is fine. The new one, SQL3 (RedHat 9.0) is running MySQL-4.1.14 (mysql-standard-4.1.14-pc-linux-gnu-i686-icc-glibc23.tar.gz). Here is my.cnf : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-name-resolve skip-host-cache skip-locking old-passwords key_buffer=256M query_cache_size = 0 record_buffer=2M sort_buffer=2M max_allowed_packet=1M max_connections=800 max_connect_errors=100 table_cache=1800 net_read_timeout=180 net_write_timeout=180 wait_timeout=360 thread_concurrency=4 log-bin server-id = 1 innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 1G innodb_data_file_path = ibdata1:1000M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=4M set-variable= write_buffer=4M [mysqld_safe] open-files-limit = 8192 Did someone know why queries can worked 99% of the time and sometimes give me command denied to user ? Perhaps because C client is built against 4.0.21 librairie and connexion are done to a 4.1.14 server ? But old-passwords prevent me from having troubles with new passwords format. Thanks. _ Apprenez à lutter contre le spam ! http://go.msn.fr/10-channel/80-security/spam/default.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very Strange command denied to user errors
156 Oud wrote: Hi, I've got a very strange problem with one of my MySQL servers. I've got 2 dedicated servers with MySQL 4.0.21, all is fine there. My new server with MySQL-4.1.14 give me some headakes ! Sometimes i've got errors like UPDATE command denied to user 'MyUser'@'192.168.0.4' for table 'MyTable'. But 99% of time, theses queries are OK. MySQL privilges haven't changed since several days. Looks just like the problem I just posted (see subject random permission denied issues). ;-)... For me as well, the trouble started after switching from 4.0 to 4.1 I am using the 'old style' passwords still. I forgot to post my my.cnf: [mysqld] master-host=x master-user=repl master-password=xx master-port=3306 server-id=25 set-variable=max_connections=1000 set-variable=max_allowed_packet=50M set-variable=key_buffer=100M set-variable=join_buffer_size=10M set-variable=sort_buffer=50M set-variable=record_buffer=10M set-variable=table_cache=200 set-variable=thread_concurrency=4 set-variable=long_query_time=7 set-variable=interactive_timeout=1000 set-variable=binlog_cache_size=10 set-variable=tmp_table_size=100M set-variable=log_slow_queries=ON set-variable=wait_timeout=25000 log-bin log-warnings log-slave-updates innodb_data_home_dir=/var/lib/mysql innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:1000M:autoextend set-variable=innodb_buffer_pool_size=700M set-variable=innodb_additional_mem_pool_size=50M innodb_log_group_home_dir=/var/lib/mysql innodb_log_arch_dir=/var/lib/mysql innodb_log_archive=0 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=200M set-variable = innodb_log_buffer_size=10M innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=60 set-variable= query_cache_limit=10M set-variable= query_cache_size=50M set-variable= query_cache_type=1 # set-variable = max_relay_log_size=100M # set-variable = relay_log_space_limit=300M set-variable= max_binlog_size=100M skip-name-resolve safe-show-database signature.asc Description: OpenPGP digital signature
Re: MySQL and dates puzzle
Mike, you did a wonderful job at analysis (identifying the 6 cases) but I
think a series of visual clues would have made a simpler query more
obvious. Here's how I understand the issue (I am a more visual thinker)
Start with the case of needing to see if a new record (NR) overlaps with
an exisiting record (ER). In this case both NR and ER would have start
dates and end dates so intermediate calculations are avoided (speed
boost).
If the ER is scheduled to finish BEFORE The start of the NR, it the
situation looks like this:
fig a.
ER: |-|
NR: |---|
If the ER is scheduled for a time AFTER the NR the situation looks like
this:
fig b.
ER: |--|
NR: |--|
Leaving us with the 4 kinds of overlaps. Overlaps 1 and 2 are when the NR
starts before the ER but ends within the ER's scheduled time and vice
versa
fig c.
ER: |---| or |-|
NR: |--| |-|
Overlaps 3 and 4 are if one schedule is completely surrounded by the
other:
fig d.
ER: || or |--|
NR:|-||-|
Using these visual aids, we can notice a pattern of all of the situations
where a conflict exists:
a) the starting date of one event is BEFORE the ending date of the other
AND
b) the ending date of one event is AFTER the starting data of the other.
In either situation where BOTH terms are NOT true, there will be no
overlap. So to find your overlapping events check for both comparisons to
be true. Notice that sequencing doesn't matter so long as you compare
opposite ends of the events.
That simplifies the original query to:
SELECT count(TAID) as total
FROM tbl_schedule
WHERE (TAID = 1)
AND '2005-10-27 17:30' ends
AND '2005-10-27 18:10' starts;
I have also seen this analysis stated more rigorously using Boolean
Algebra. I don't have a link to it but it may be interesting for some of
you if you took the time to go find it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael McFadden [EMAIL PROTECTED] wrote on 10/27/2005 08:24:54 PM:
Hi C.F.
I'm new to the list, so please excuse me if I'm
answering out-of-turn from the pro's here.
I think the answer is to also check if the
BusyTime_start is between start and end of the
attempted scheduled.
ie:
WHERE
(TAID = 1) AND
(('2005-10-27 17:30' BETWEEN starts AND ends) OR
('2005-10-27 18:10' BETWEEN starts AND ends) OR
(starts BETWEEN '2005-10-27 17:30' AND '2005-10-27
18:10))
I think you only need to check starts (see case 5
below) because the other case is picked up by the
first two checks. (see case 6 below)
The way I see it, you have 6 cases, but some
simplification can be done, as I have noted:
1) attempted schedule time lies outside of busy times
(return 0 = NOT BUSY)
2) attempted schedule end time lies between busy times
(return !0 = busy)
3) attempted schedule begin time lies between busy
times (return !0 = busy)
4) attempted schedule begin AND end time lie between
busy times (return !0 = busy)
[this case is a special case of #2 and #3, so it
really disappears!]
5) Busy Time begin time lies between attempted
schedule begin and end times (the problem)
6) Busy Time end time lies between attempted schedule
begin and end times (the problem)
You must check either case 5 or 6 to be sure to catch
the 'attempted schedule wraps busy schedule' case. If
not, you obviously see the problem.
I think that only case 5 or 6 needs to be checked (not
both) because if one of those is not true, then case
1, 2, 3 [or 4] (the only one's left!) must be true.
Hope that helps, and wasn't too confusing!
-Mike McFadden
--- C.F. Scheidecker Antunes [EMAIL PROTECTED]
wrote:
Hello all,
I have a TA table to record TA UNAVAILABLE times.
This table is quite simple. It has a TAID number, a
start date and an
end date.
tbl_schedule {
TAID integer,
starts datetime,
ends datetime }
A valid entry would be a TA whose id is 1 and
between 17:00 and 18:00 he
is busy.
So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'
Each ta can have more than one entry per day. He
might be a busy TA and
have a lot
of meetings scheduled. The meetings do not have to
be 1 hour length,
they can be 5 or 10 minutes.
So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'
Now, I need to check, given a start and end dates,
if that would overlap
with some record already present
in the database.
If I want to know if the TA is busy between 17:30
and 18:10 I could I
issue something like this:
SELECT count(TAID) as total FROM tbl_schedule where
(TAID = 1) AND
(('2005-10-27 17:30' BETWEEN starts AND ends) OR
('2005-10-27 18:10'
BETWEEN starts AND ends))
It would return a number not zero as total if the
dates are between the
registered database. However, this does not work
Re: MySQL and dates puzzle
Sean, you definately got me :)
Your solution is simpler and much more elegant!
Had the visual aids here on paper, but I'm not as good
with the ascii art as you.
I definately learned something!
Thanks!
-Mike
--- [EMAIL PROTECTED] wrote:
Mike, you did a wonderful job at analysis
(identifying the 6 cases) but I
think a series of visual clues would have made a
simpler query more
obvious. Here's how I understand the issue (I am a
more visual thinker)
Start with the case of needing to see if a new
record (NR) overlaps with
an exisiting record (ER). In this case both NR and
ER would have start
dates and end dates so intermediate calculations are
avoided (speed
boost).
If the ER is scheduled to finish BEFORE The start of
the NR, it the
situation looks like this:
fig a.
ER: |-|
NR: |---|
If the ER is scheduled for a time AFTER the NR the
situation looks like
this:
fig b.
ER: |--|
NR: |--|
Leaving us with the 4 kinds of overlaps. Overlaps 1
and 2 are when the NR
starts before the ER but ends within the ER's
scheduled time and vice
versa
fig c.
ER: |---| or |-|
NR: |--| |-|
Overlaps 3 and 4 are if one schedule is completely
surrounded by the
other:
fig d.
ER: || or |--|
NR:|-||-|
Using these visual aids, we can notice a pattern of
all of the situations
where a conflict exists:
a) the starting date of one event is BEFORE the
ending date of the other
AND
b) the ending date of one event is AFTER the
starting data of the other.
In either situation where BOTH terms are NOT true,
there will be no
overlap. So to find your overlapping events check
for both comparisons to
be true. Notice that sequencing doesn't matter so
long as you compare
opposite ends of the events.
That simplifies the original query to:
SELECT count(TAID) as total
FROM tbl_schedule
WHERE (TAID = 1)
AND '2005-10-27 17:30' ends
AND '2005-10-27 18:10' starts;
I have also seen this analysis stated more
rigorously using Boolean
Algebra. I don't have a link to it but it may be
interesting for some of
you if you took the time to go find it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael McFadden [EMAIL PROTECTED] wrote on
10/27/2005 08:24:54 PM:
Hi C.F.
I'm new to the list, so please excuse me if I'm
answering out-of-turn from the pro's here.
I think the answer is to also check if the
BusyTime_start is between start and end of the
attempted scheduled.
ie:
WHERE
(TAID = 1) AND
(('2005-10-27 17:30' BETWEEN starts AND ends) OR
('2005-10-27 18:10' BETWEEN starts AND ends) OR
(starts BETWEEN '2005-10-27 17:30' AND '2005-10-27
18:10))
I think you only need to check starts (see case 5
below) because the other case is picked up by the
first two checks. (see case 6 below)
The way I see it, you have 6 cases, but some
simplification can be done, as I have noted:
1) attempted schedule time lies outside of busy
times
(return 0 = NOT BUSY)
2) attempted schedule end time lies between busy
times
(return !0 = busy)
3) attempted schedule begin time lies between busy
times (return !0 = busy)
4) attempted schedule begin AND end time lie
between
busy times (return !0 = busy)
[this case is a special case of #2 and #3, so
it
really disappears!]
5) Busy Time begin time lies between attempted
schedule begin and end times (the problem)
6) Busy Time end time lies between attempted
schedule
begin and end times (the problem)
You must check either case 5 or 6 to be sure to
catch
the 'attempted schedule wraps busy schedule' case.
If
not, you obviously see the problem.
I think that only case 5 or 6 needs to be checked
(not
both) because if one of those is not true, then
case
1, 2, 3 [or 4] (the only one's left!) must be
true.
Hope that helps, and wasn't too confusing!
-Mike McFadden
--- C.F. Scheidecker Antunes
[EMAIL PROTECTED]
wrote:
Hello all,
I have a TA table to record TA UNAVAILABLE
times.
This table is quite simple. It has a TAID
number, a
start date and an
end date.
tbl_schedule {
TAID integer,
starts datetime,
ends datetime }
A valid entry would be a TA whose id is 1 and
between 17:00 and 18:00 he
is busy.
So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'
Each ta can have more than one entry per day. He
might be a busy TA and
have a lot
of meetings scheduled. The meetings do not have
to
be 1 hour length,
they can be 5 or 10 minutes.
So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'
Now, I need to check, given a start and end
dates,
if that would overlap
with some record already present
in the
Re: random permission denied issues
What is max_connections set to (my.cnf?)? How many connections are
there at a time? (show processlist) That would result in a too many
connections error, but it's worth a shot.
What is the thread cache set to?
-Sheeri
On 10/28/05, Johannes B. Ullrich [EMAIL PROTECTED] wrote:
I am having a very odd issue with one of my mysql 4.1.14 servers.
Randomly, the server returns permission denied to queries even if
identical queries succeeded within the same connection.
To test the problem, I setup a script that connects to the database, and
issues the same query 50,000 times. Out of the 50,000 attempts, about 10
fail, but the exact number varies. The error message in all cases is:
SELECT command denied to user 'user'@'host' for table 'example'
There are two web servers that use the same database. Both web servers
have this problem.
Here is a test script I use to illustrate the problem:
[EMAIL PROTECTED]($host,$user$password);
while ($i5) {
$x=mysql_query($query);
if ( $x) {
print $i worked!BR;
} else {
print $i ERROR! .mysql_error().BR;
}
$i++;
}
I did not set up any mysql rate limits. The database is 'busy' but not
extreme (mytop shows about 100 queries/sec, 99.91% key efficiency)
usually about 10-20 threads. Query cache is enabled (and the above query
should take advantage of it). Database and Web Server are on the same
LAN separated by a firewall.
The system is a RedHat ES system, with MySQL rpms from mysql.com (i386),
recent 2.4 kernel.
Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY Destroys 2nd Function
David Blomstrom wrote: I have a PHP script that displays data like this: Eurasia Eurasiasupisland/sup Africa Where Eurasia and Africa are mainland parents of ecological regions and Eurasiasupisland/sup is a parent of an ecological system that is associated with a continent. For example, Borneo would be Eurasiasupisland/sup. You've lost me here ;-) I know quite a bit about animals, but ecological regions and their parents are not my cup of tea. ANIMALS TABLE Canis_lupus | wolf Panthera_tigris | tiger JOIN TABLE SPECIES | ECOREGION Canis_lupus | NA1008 Canis_lupus | NA1010 ECOREGIONS TABLE ID | NAME | Geog | Geog2 NA1008 | Alaska tundra | na | na IM1003 | Philippine rainforest | eur | phl (Note that mainland ecoregions feature the continental ID in each of the last two columns, while island ecoregions feature the island's ID in the last column.) GEOGRAPHY TABLE ID | NAME na | North America phl | Philippines I miss quite a bit of data here and there are no queries mentioned. Where can I find Eurasia and Africa here? Maybe you can start by rewriting the problem is pseudo queries: I want a list of the NAMEs from the GEOGRAPHY table for a certain species from the JOIN table for which the ecoregions and the geog are listed in the ecoregions table. Each NAME should only appear once. Or something like that. With such a natural language pseudo query you're often more than half way towards building a real query. At least you're far enough for others to help you build the actual SQL. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random permission denied issues
sheeri kritzer wrote: What is max_connections set to (my.cnf?)? How many connections are there at a time? (show processlist) That would result in a too many connections error, but it's worth a shot. Max connections: 2000 typically 10-20 used (hardly ever 100). What is the thread cache set to? | thread_cache_size | 0 | signature.asc Description: OpenPGP digital signature
Does MySQL 3.23.58 Support UNION
I'm trying to run this : SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and B.U_Username = 'user1' UNION SELECT DISTINCT C.B_IP FROM w3t_Posts C , w3t_Users D WHERE C.B_PosterID = D.U_Number and D.U_Username = 'user2' and it's failing saying SQL Error: You have an error in your SQL syntax near 'UNION SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID ' at line 2 SQL Error #: 1064 Query: SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and B.U_Username = 'Simon' UNION SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and B.U_Username = 'MartyX' I've been using DB2 SQL for ages.
Re: Does MySQL 3.23.58 Support UNION
Simon Longstaff [EMAIL PROTECTED] wrote on 10/28/2005 10:50:24 AM: I'm trying to run this : SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and B.U_Username = 'user1' UNION SELECT DISTINCT C.B_IP FROM w3t_Posts C , w3t_Users D WHERE C.B_PosterID = D.U_Number and D.U_Username = 'user2' and it's failing saying SQL Error: You have an error in your SQL syntax near 'UNION SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID ' at line 2 SQL Error #: 1064 Query: SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and B.U_Username = 'Simon' UNION SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and B.U_Username = 'MartyX' I've been using DB2 SQL for ages. According to the book for your version: http://dev.mysql.com/doc/refman/4.1/en/index.html The UNION keyword: http://dev.mysql.com/doc/refman/4.1/en/union.html Union is supported starting with version 4.0.0. Version 5.0 is the current production version of MySQL; it may be time to consider an upgrade. What do you think? If you are not interested in upgrading all the way to 5.0, 4.1 was the leading production version until 5.0 was released. You may want to upgrade to just that http://dev.mysql.com/doc/refman/4.1/en/upgrade.html or http://dev.mysql.com/doc/refman/5.0/en/upgrade.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 5.0 character sets
Hi, Is it a so hard, or a so easy question, that nobody answers it? :) Or just should wait some more hours, and don't hurry so much? ;) Bye, Andras So, I started learning, how MySQL 5.0 handles character sets. It is, what I think about it: character_set_client | latin1 This is, what the client thinks about itself, determined from the environment. It will be used for autoconverting character data sent from the client. character_set_connection | latin1 This is, what the client thinks about the server, but I'm not sure, how it will be determined, and what exactly is it for. As the documentation says, it will be used for autoconverting character data sent to the server. character_set_database | latin1 It's the general database default character set, used when you create a table. No more additional meaning. ? character_set_results| latin1 It is, what the client thinks about itself, and will be used for converting character data coming from the server. character_set_server | latin1 Pass. character_set_system | utf8 Pass. And another strange thing I found: you can set the table's (default) character encoding, but I see no effect. The table data - of course - won't be converted (that's another alter table command), the results I got won't be changed - so I don't know, what exactly default table character set is good for. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE and INDEX using same field.
When creating an index in phpMySQL I get a warning message about having 2 indexes using the same field. The table is simply a product comment table. The first index is simply an INDEX on the product_id to speed up displaying them when someone wants to lookup the comments for that product. The second is a UNIQUE constraint on the product_id and the user_id to make sure a user does not insert more than one comment per product. phpMySQL shows me the following warning: *UNIQUE and INDEX keys should not both be set for column `product_id` *If that really is bad to do... besides programatically how would I do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disable engines
I want to permit only MyISAM tables to be created in my server. How can I disable all the other engines? Flávio Gonçalves
Re: GROUP BY Destroys 2nd Function
--- Jigal van Hemert [EMAIL PROTECTED] wrote:
ANIMALS TABLE
Canis_lupus | wolf
Panthera_tigris | tiger
JOIN TABLE
SPECIES | ECOREGION
Canis_lupus | NA1008
Canis_lupus | NA1010
ECOREGIONS TABLE
ID | NAME | Geog | Geog2
NA1008 | Alaska tundra | na | na
IM1003 | Philippine rainforest | eur | phl
(Note that mainland ecoregions feature the
continental
ID in each of the last two columns, while island
ecoregions feature the island's ID in the last
column.)
GEOGRAPHY TABLE
ID | NAME
na | North America
phl | Philippines
Maybe you can start by rewriting the problem is
pseudo queries:
I want a list of the NAMEs from the GEOGRAPHY table
for a certain
species from the JOIN table for which the ecoregions
and the geog are
listed in the ecoregions table. Each NAME should
only appear once.
Or something like that.
Well, I've already made one major change. I can see
that this is going to be way too complex for me no
matter what, so I split it into TWO queries. This
query displays the native continents:
?
$query = SELECT * FROM gwecoareasexp AS GW
LEFT JOIN geog101exp as GG on GG.ID101 = GW.Geog
WHERE IDX = 'IM0123' OR IDX = 'PA0408' OR IDX =
'AT1011' GROUP BY Geog;
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$Geog[] = $row['Geog2'];
echo($row['Name101']).'';
echo 'br /';
}
?
For example, if the species discussed is the yak, it
would display this:
Eurasia
The giraffe page would display this:
Africa
The puma:
North America
South America
For species that are native only to continental
mainlands, that's all there is to it. I don't even
have to worry about them in the second query.
The second query so far looks like this:
?php
$footnote = SELECT * FROM gwecoareasexp AS GW
LEFT JOIN geog101exp as GG on GG.ID101 = GW.Geog2
WHERE IDX = 'IM0123' OR IDX = 'IM0123' OR IDX =
'PA0408' OR IDX = 'AT1011';
$result = mysql_query($footnote);
while($row = mysql_fetch_array($result)) {
$Geog[] = $row['Geog2'];
echo($row['Geog2']).'';
echo($row['Name101']).'';
echo 'br /';
}
?
Its purpose is to identify species that are native to
islands, then display a text message depending on
whether or not that animal is also native to a
continent.
For example, the following data tells us that we can
forget about the yak, which is a purely mainland
species.
yak | eur | eur
Philippine eagle | eur | phl
tiger | eur | eur
tiger | eur | bal
The Philippine eagle page might display a message like
this:
Eurasia*
*Philippines only
The tiger page might display this:
Eurasia*
*Including Bali
I haven't even plugged in my animal species table yet.
I'm just using the WHERE clause to select groups of
ecoregions from my table gweocoregions and experiment
with them.
It's easy to eliminate mainland species, because their
values in the fields Geog and Geog2 are identical; eur
| eur for the Eurasia yak, for example.
For my second query, I would join Geog2 to the field
ID101 in my geography table. So if Geog2 contains
phl, it would display Philippines from the
geography table.
If that's still too complex, I might just hand code
arrays grouping each continent's islands together and
somehow draw the data from them.
For example...
'
$Eurasia = array(Philippines, Borneo, Sumatra)
Then my PHP script could say something like, Display
this message if any name in this array appears and
there is also a mainland region - like eur | eur - but
display the other message if a name in this array
shows up but there's no mainland ecoregion.
It's hard to even explain it, but that's a start.
Thanks.
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
Jigal van Hemert wrote: Stefan Kuhn wrote: Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet: Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit : I'm using it with four machines (geographically separate) and it works fine. Stefan And can writes on each server in simultaneous time ? I don't understand the question. Replication is pretty fast, but is not guaranteed to happen in a certain time (not real-time). Stefan, I think Raphaël wants to know if a user can use any server in the circle to update or insert records and that the changes will be replicated to all other servers. In another thread he stated that it was for maintaining student information on various remote locations (a student can login into the system on any of the locations). What happens if a record is updated on two servers and the changes are forwarded to the other servers in the circle? I dont' think that this would occur often with student information, but both the student and the administration department might update a record simultaniously (in comparison to the speed of replication with several remote locations). Regards, Jigal. Any time you are running circular replication, it is possible for a situation to arise where two servers receive conflicting updates at nearly the same time; this can cause replication to stop on both servers at the point where they read the other server's conflicting update from their master's binary log. For example, imagine 4 servers, A-B-C-D-A, and imagine that A and C both receive an insert statement containing the same unique key. Replication would stop when A reads C's insert from D's binary log, and when C reads A's insert from B's binary log. When this type of conflict happens, it can be difficult or impossible to restore data integrity between all servers. Another example, imagine that A had received an update like SET $val = $val + 5 while C had received SET $val = $val * 2. These would not conflict - in the sense that replication would continue uninterrupted - but data integrity would be lost. In short, any time you are running circular replication, you have to ensure that updates are processed in the same order on all servers, or know that the order is irrelevant to your data integrity. Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting: maximum size of status variable
Martijn van den Burg wrote: Greetings, I've been keeping track of Bytes_sent and Bytes_received for a while in the fashion of 'mysqlreport': divide those values over Uptime in order to obtain a data rate (bytes/sec). The resulting graphs look like this: | | | /| /| |/ |/ | | / | / | / | / | /| / |/ |/ +-- time - Bewildered I started troubleshooting, and I think I have found the cause: the value of Bytes_* has a maximum value of around 4GB, or the size of an INT UNSIGNED. Can anyone confirm that this is the max value for status variables? -- Martijn Yes, bytes_sent and bytes_received are type unsigned long (4 bytes), so max value is 4.2G. -Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE and INDEX using same field.
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/28/2005 11:28:42 AM: When creating an index in phpMySQL I get a warning message about having 2 indexes using the same field. The table is simply a product comment table. The first index is simply an INDEX on the product_id to speed up displaying them when someone wants to lookup the comments for that product. The second is a UNIQUE constraint on the product_id and the user_id to make sure a user does not insert more than one comment per product. phpMySQL shows me the following warning: *UNIQUE and INDEX keys should not both be set for column `product_id` *If that really is bad to do... besides programatically how would I do this. Thanks, Mike A UNIQUE *is* an INDEX with an added twist. It's a special kind of INDEX that only allows single combinations of the values in the columns that compose the INDEX. A regular INDEX does not have this restriction. Defining UNIQUE(product_id, user_id) creates an INDEX. Any index that uses more than one column also acts as an index for each left-most sets of columns, including just the first one. Your UNIQUE and your INDEX overlap and the INDEX is not necessary. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
re: why wont this work?
hey,
Could you tell me someone please why this simple setup wont work!
I am using SuSE9.3 and trying to set this up:
$ mysql -uroot -einsert into user(Host,User,Password)
values('localhost','guestbook',password('guestbook') mysql -p
this is the error:
$ ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '' at line 1
could someone please shed some light on this, Maybe the answer is so obvious I
cant see it?
Thankyou very much
Ben
:')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY Destroys 2nd Function
Everything snipped David, is it at all intuitive to organize your geography into a tree-type structure? Here is an example: Western Hemisphere (hemisphere) C. America (continent) Guatemala (country) N. America (continent) Canada (country) Manitoba (state/province) Moose Elk Cuba (country) Cuba (island) Aligator United States (country) Montana (state/province) Moose Florida (state/province) Dade (county/parish) Aligator Mexico (country) Chihuahua (state/province) Desert Rat S. America (continent) Brazil (country) Amazon (river) Pirhana Peru (country) Argentina (country) Galapagos Is (island) Galapagos Tortise With this kind of structure, it is simple to answer questions like: a) List all of the places to find Aligators b) Which animals can be found on Islands in the Western Hemisphere? c) List all animals found in Brazil. Any type of question that deals with containment can be answered from a data structure like this. Your flat table model will not work for this type of information. (List the continents in the Western Hemisphere, list the countries on the continent S. America,...) You cannot make your site easy to manage with just a flat data structure. Sorry, but that is my professional opinion. For instance, to solve the question of what countries will I find an Aligator, you find all of the nodes for Aligator then move up the tree until you find a country node for each one. You may need to move up 1 or 2 or more nodes until you get to a country but you will eventually get to one. Keep a list of the countries you find. When you are done, simplify your list so that each country only appears once. There's the answer. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
stoppin mysql/gave up waiting
We have MySQL running on a Redhat server (RHEL 3.2). We issued a service mysql restart yesterday and for some reason MySQL didn't shut down properly. The init script said it gave up waiting and deleted the PID file anyway. Since we issued a restart, I suspect a second copy of MySQL got started. InnoDB complained that it was not shut down normally and started doing recovery. We let the recovery process finish, stopped the second instance of mysql and killed the first instance (using kill -9). The server seems OK now (luckily its not in production yet), but I don't know what would cause MySQL to not shut down properly. Has this happened to anyone else? Please let me know if you have any theories or suggestions. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'GROUP BY' behavior
All, In the following query, some of the values are averaged over several rows, but some are not: SELECT hostname, volname, qtreename, round(avg(used/allocated*100),0), round(avg(used)), allocated, available FROM quota_entries WHERE date_sub(now(), interval 1 day) timestamp GROUP BY qtreename,hostname My questions: From which of the several averaged rows do 'allocated' and 'available' come in the results? Can I control this? I would like the row with maximum timestamp. Thanks, Bill +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | | PRI | NULL| auto_increment | | hostname | varchar(32) | | | || | volname | varchar(32) | | | || | qtreename | varchar(32) | | | || | allocated | int(10) unsigned | | | 0 || | used | int(10) unsigned | | | 0 || | available | int(10) unsigned | | | 0 || | files | int(10) unsigned | YES | | 0 || | timestamp | datetime | YES | MUL | NULL|| +---+--+--+-+-++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why wont this work?
Ben wrote:
hey,
Could you tell me someone please why this simple setup wont work!
I am using SuSE9.3 and trying to set this up:
$ mysql -uroot -einsert into user(Host,User,Password)
values('localhost','guestbook',password('guestbook') mysql -p
$ mysql -uroot -einsert into user (Host,User,Password)
values ('localhost','guestbook',password('guestbook') mysql -p
this is the error:
$ ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '' at line 1
could someone please shed some light on this, Maybe the answer is so obvious I
cant see it?
Thankyou very much
Ben
:')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crashes and restarts on connect attempt
Hello. Follow links which have been meant in the error log. Resolve the stack trace and send it to the list. See: http://dev.mysql.com/doc/refman/5.0/en/crashing.html Don Doumakes wrote: I'm installing MySQL 4.1.14 on a new Gentoo box. When I try to connect to the mysqld daemon, it crashes and restarts itself, which just doesn't seem right. I enter these commands: cd /usr; /usr/bin/mysqld_safe mysqladmin -u root password 'foobar' and get this response: mysqladmin: connect to server at 'localhost' failed error: 'Lost connection to MySQL server during query' Number of processes running now: 0 051027 08:09:52 mysqld restarted mysqld.err contains this: InnoDB: !! UNIV_DEBUG switched on !!! InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 051027 8:09:23 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 051027 8:09:23 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 8 MB InnoDB: Database physically writes the file full: wait... 051027 8:09:24 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 8 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 051027 8:09:35 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.14-debug-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-4.1.14 mysqld got signal 4; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 233980 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8c8ecd8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xb27db898, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x81455c1 0xb7dcbe55 0x814b1ed 0x8155ebf 0x815688a 0xb7dc613d 0xb7c361ba New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. InnoDB: !! UNIV_DEBUG switched on !!! 051027 8:09:53 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051027 8:09:53 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 051027 8:09:53 InnoDB: Flushing modified pages from the buffer pool... 051027 8:09:56 InnoDB: Started; log sequence number 0 43634 /usr/sbin/mysqld: ready for connections. Version: '4.1.14-debug-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-4.1.14 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding stop words table in german language
Hello. Use ft_stopword_file system variable. See: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Merlin wrote: Hi there, as mysql docs describe, there is a stop words table by default: http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html Does anybody know how to add a german table ( I guess there is an equivalent to the engl. one)? Does this also work with MySQL 4.0.18? Thank you for any help, Merlin -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Problem
Hello. Do you have your Slave is still trying to reconnect? If yes, are you sure that you don't have network problems? I have a script checking for when the replication fails but that does not include this type problem. Is this a bug in replication or do I need to update my script to check for this ? Replication fails sometimes. I haven't found similar bug, but if you able perform an upgrade to the latest release. Often it helps. If you want to debug the problem make a research of the binary logs to find out the weird even, which causes Slave to reconnect. See: http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html Marvin Wright wrote: Hi, I found this morning that the slave replication thread seem to have hung. Below I have pasted in my show slave status, it seems to be stuck trying to connect to the master. To fix this I issued a stop slave and start slave, my slave is about 3 weeks behind now. I have a script checking for when the replication fails but that does not include this type problem. Is this a bug in replication or do I need to update my script to check for this ? I'm running version 4.1.12 on Redhat AS3. Many Thanks Marvin. mysql show slave status\G *** 1. row *** Slave_IO_State: Reconnecting after a failed master event read Master_Host: prdmysql01.prd.lastminute.com Master_User: web Master_Port: 3306 Connect_Retry: 60 Master_Log_File: prdmysql01-bin.000118 Read_Master_Log_Pos: 15561995 Relay_Log_File: prdmysql02-relay-bin.01 Relay_Log_Pos: 80882143 Relay_Master_Log_File: prdmysql01-bin.000118 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 15561995 Relay_Log_Space: 80882143 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble running mysql_install_db
Hello. What am I missing? I installed MySQL using the installer package. Usually installer has run mysql_install_db, but if you still want to rerun it, invoke mysql_install_db with --user=mysql option under the root account, and fix possible issues with the rights using 'chmod' later. '/usr/local/mysql-standard-5.0.15-osx10.3-powerpc/data/' (Errcode: 13) Really the problem is in the permissions, check the rights for /usr/local/mysql-standard-5.0.15-osx10.3-powerpc directory. Have you tried just to start MySQL Server? See: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html Bruce Martin wrote: Hello all, I just installed MySQL 5 on Mac OS 10.3.9. Now when I try to create a user using any method it does not work. I read I may have to run the mysql_install_db script so I did, but I get these results: mkdir: ./data/mysql: Permission denied chmod: ./data/mysql: Permission denied mkdir: ./data/test: Permission denied chmod: ./data/test: Permission denied Installing all prepared tables 051027 5:29:02 [Warning] Can't create test file /usr/local/mysql-standard-5.0.15-osx10.3-powerpc/data/24.lower-test ./bin/mysqld: Can't change dir to '/usr/local/mysql-standard-5.0.15-osx10.3-powerpc/data/' (Errcode: 13) 051027 5:29:02 [ERROR] Aborting 051027 5:29:02 [Note] ./bin/mysqld: Shutdown complete ./bin/mysql_create_system_tables: line 766: 674 Broken pipe cat END_OF_DATA use mysql; set table_type=myisam; $c_d $i_d $c_h $i_h $c_u $i_u $c_f $i_f $c_t $c_c $c_ht $c_hc $c_hr $c_hk $c_tzn $i_tzn $c_tz $i_tz $c_tzt $i_tzt $c_tztt $i_tztt $c_tzls $i_tzls $c_p $c_pp END_OF_DATA Installation of system tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! I then checked the permissions for the /usr/local/mysql/data/ directory and it shows: drwxr-x--- 13 mysql wheel What am I missing? I installed MySQL using the installer package. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why wont this work?
Yes, you need to add a second close parenthesis at the end -- you
close the parenthesis for password('guestbook') but not for the
values('localhost',. . .
-Sheeri
On 10/28/05, Ben [EMAIL PROTECTED] wrote:
hey,
Could you tell me someone please why this simple setup wont work!
I am using SuSE9.3 and trying to set this up:
$ mysql -uroot -einsert into user(Host,User,Password)
values('localhost','guestbook',password('guestbook') mysql -p
this is the error:
$ ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '' at line 1
could someone please shed some light on this, Maybe the answer is so obvious I
cant see it?
Thankyou very much
Ben
:')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'GROUP BY' behavior
Bill Adams [EMAIL PROTECTED] wrote on 10/28/2005 01:49:28 PM: All, In the following query, some of the values are averaged over several rows, but some are not: SELECT hostname, volname, qtreename, round(avg(used/allocated*100),0), round(avg(used)), allocated, available FROM quota_entries WHERE date_sub(now(), interval 1 day) timestamp GROUP BY qtreename,hostname My questions: From which of the several averaged rows do 'allocated' and 'available' come in the results? Can I control this? I would like the row with maximum timestamp. Thanks, Bill +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | | PRI | NULL| auto_increment | | hostname | varchar(32) | | | || | volname | varchar(32) | | | || | qtreename | varchar(32) | | | || | allocated | int(10) unsigned | | | 0 || | used | int(10) unsigned | | | 0 || | available | int(10) unsigned | | | 0 || | files | int(10) unsigned | YES | | 0 || | timestamp | datetime | YES | MUL | NULL|| +---+--+--+-+-++ I do not think you are posing a single-statment question to the database (I count subqueries as second statements). You would like to find the averages of (used/allocated) and (used) for all (qtreename,hostname) pairs. That's one question. The second question is to return the row with the greatest timestamp for each (qtreename,hostname) pair along with the averages calculated in the first question. To find the answer your first question is a simple GROUP BY query. To find the answer to your second takes a max-of-group-pattern query: http://dev.mysql.com/doc/refman/4.1/en/examples.html If it were me, I would use a temporary table for each stage and combine them to form the final report. If you need more help, just come back. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: random permission denied issues
I'm not sure if this will help you, but it might: http://jeremy.zawodny.com/blog/archives/000173.html (brief excerpt) So the moral of the story is this: If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. Your CPU will thank you. The only other thing I can think of is if you have a lock on the table (if it's MyISAM) at the same time, so the select is denied. Updates have preference in MySQL over reads when there's a lock, so a read query would wait for a write query. But then you'd get a timeout error. . .. SHOW STATUS LIKE 'Table%'; will show you the lock contention, if there is any. What else is going on in the database? Are you doing maintenance, like OPTIMIZE TABLE? That is a very odd situation. -Sheeri On 10/28/05, Johannes B. Ullrich [EMAIL PROTECTED] wrote: sheeri kritzer wrote: What is max_connections set to (my.cnf?)? How many connections are there at a time? (show processlist) That would result in a too many connections error, but it's worth a shot. Max connections: 2000 typically 10-20 used (hardly ever 100). What is the thread cache set to? | thread_cache_size | 0 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why wont this work?
OMG,
Very sorry for my stupidness
I new it would be something stupid, it was cut and pasted from a website so I
dont have
to take all the blame as it should have just worked, aye...aye?
lol, thankyou very much
Ben
;-)
Yes, you need to add a second close parenthesis at the end -- you
close the parenthesis for password('guestbook') but not for the
values('localhost',. . .
-Sheeri
On 10/28/05, Ben [EMAIL PROTECTED] wrote:
hey,
Could you tell me someone please why this simple setup wont work!
I am using SuSE9.3 and trying to set this up:
$ mysql -uroot -einsert into user(Host,User,Password)
values('localhost','guestbook',password('guestbook') mysql -p
this is the error:
$ ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '' at line 1
could someone please shed some light on this, Maybe the answer is so
obvious I cant see it?
Thankyou very much
Ben
:')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why wont this work?
Thankyou sheeri kritzer
You win an all expenses paid trip around David Launge :-)
hahaha
cheers
Ben
OMG,
Very sorry for my stupidness
I new it would be something stupid, it was cut and pasted from a website so
I dont have
to take all the blame as it should have just worked, aye...aye?
lol, thankyou very much
Ben
;-)
Yes, you need to add a second close parenthesis at the end -- you
close the parenthesis for password('guestbook') but not for the
values('localhost',. . .
-Sheeri
On 10/28/05, Ben [EMAIL PROTECTED] wrote:
hey,
Could you tell me someone please why this simple setup wont
work! I am using SuSE9.3 and trying to set this up:
$ mysql -uroot -einsert into user(Host,User,Password)
values('localhost','guestbook',password('guestbook') mysql -p
this is the error:
$ ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '' at line 1
could someone please shed some light on this, Maybe the answer is so
obvious I cant see it?
Thankyou very much
Ben
:')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY Destroys 2nd Function
--- [EMAIL PROTECTED] wrote: Everything snipped David, is it at all intuitive to organize your geography into a tree-type structure? Here is an example: Western Hemisphere (hemisphere) C. America (continent) Guatemala (country) N. America (continent) Canada (country) Manitoba (state/province) Moose Elk I see what you're saying, but I'm not sure if it's workable. I'd need a separate row for every state, province and nation the moose is native to. Ditto for each of hundreds of species of rodents and bats. Instead, I'm linking species to ecoregions, then linking the ecoregions to geographic regions - which may just be another version of what you're suggesting. In fact, I have all the animal species organized into a tree structure, and I already have a separate tree structure for nations, states, etc. Maybe I can combine them somehow. Thanks. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disable engines
Flavio Gonçalves wrote: I want to permit only MyISAM tables to be created in my server. How can I disable all the other engines? Put: skip-innodb skip-bdb in your my.cnf file, [mysqld] section. Mihail -- Mihail Manolov Government Liquidation, LLC Special Projects Leader 202 558 6227 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE and INDEX using same field.
[EMAIL PROTECTED] wrote: Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/28/2005 11:28:42 AM: When creating an index in phpMySQL I get a warning message about having 2 indexes using the same field. The table is simply a product comment table. The first index is simply an INDEX on the product_id to speed up displaying them when someone wants to lookup the comments for that product. The second is a UNIQUE constraint on the product_id and the user_id to make sure a user does not insert more than one comment per product. phpMySQL shows me the following warning: *UNIQUE and INDEX keys should not both be set for column `product_id` *If that really is bad to do... besides programatically how would I do this. Thanks, Mike A UNIQUE *is* an INDEX with an added twist. It's a special kind of INDEX that only allows single combinations of the values in the columns that compose the INDEX. A regular INDEX does not have this restriction. Defining UNIQUE(product_id, user_id) creates an INDEX. Any index that uses more than one column also acts as an index for each left-most sets of columns, including just the first one. Your UNIQUE and your INDEX overlap and the INDEX is not necessary. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks for the answer. I know it is an index... I did not know that an index using 2 columns will be used when calling a statement that uses only one of those columns. Just for the fun of it I will trace a query to see if uses it. Thanks again, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crashes and restarts on connect attempt
Gleb Paharenko wrote: Follow links which have been meant in the error log. Resolve the stack trace and send it to the list. See: http://dev.mysql.com/doc/refman/5.0/en/crashing.html I of course attempted to do that before asking for help. Though I compiled mysql with debug enabled, there don't seem to be any symbols in the executable: # nm -n /usr/sbin/mysqld nm: /usr/sbin/mysqld: no symbols Nor is there a file anywhere on the system named mysqld.sym.gz. -- Don Doumakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to use a conditional in this UPDATE
on 10/28/05 5:52 AM, Brent Baisley at [EMAIL PROTECTED] wrote: You can nest the IF statement, putting another where 'soon' is like Jasper suggested. Or you can use the CASE WHEN THEN construct if you have a lot of conditions you need to check for. Can you show me an example of the CASE method, I tried and it would error, the docs are a wee bit confusing in that regards. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'GROUP BY' behavior
Shawn, That's a very reasonable answer. Thanks for pointing me to the examples. This one addresses the second question: http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row. html . There is no example answering both questions in one query. Regards, Bill From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 28, 2005 11:14 AM I do not think you are posing a single-statment question to the database (I count subqueries as second statements). You would like to find the averages of (used/allocated) and (used) for all (qtreename,hostname) pairs. That's one question. The second question is to return the row with the greatest timestamp for each (qtreename,hostname) pair along with the averages calculated in the first question. To find the answer your first question is a simple GROUP BY query. To find the answer to your second takes a max-of-group-pattern query: http://dev.mysql.com/doc/refman/4.1/en/examples.html If it were me, I would use a temporary table for each stage and combine them to form the final report. If you need more help, just come back. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 character sets
On Oct 28, 2005, at 1:06 AM, BÁRTHÁZI András wrote: I'm new on this list, so a very short intro about me: I'm Andras Barthazi, a Hungarian web developer. I like MySQL very much, I'm using it since 3.x versions, so I think I know it very well. But... I'm new to everything, but I can tell you what I think that manual says. character_set_client | latin1 This is, what the client thinks about itself, determined from the environment. It will be used for autoconverting character data sent from the client. It is what the client tells the server about the queries that the client sends. character_set_connection | latin1 This is, what the client thinks about the server, but I'm not sure, how it will be determined, and what exactly is it for. As the documentation says, it will be used for autoconverting character data sent to the server. It is what the server uses internally. The server will convert queries from character_set_client to character_set_connection character_set_database | latin1 It's the general database default character set, used when you create a table. No more additional meaning. ? That is my guess. character_set_results| latin1 It is, what the client thinks about itself, and will be used for converting character data coming from the server. When the server sends results to the client it will send the results in character_set_results. That is the server will convert from character_set_connection to character_set_results when sending results. character_set_server | latin1 Pass. Fogolmam sincs. (I can't figure it out from the documentation either). character_set_system | utf8 Pass. Sincs (nor here). And another strange thing I found: you can set the table's (default) character encoding, but I see no effect. The table data - of course - won't be converted (that's another alter table command), the results I got won't be changed - so I don't know, what exactly default table character set is good for. I've also been having some difficulty with this. I recommend using utf8 for everything, unless the window in which you run the client can't handle utf8. Remember, those accent marks matter. After all Szárba szökik just isn't the same without them. Minden jót. -j -- Jeffrey Goldberghttp://www.goldmark.org/jeff/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld in an eternal loop
Hi listers 1. environment [EMAIL PROTECTED] ~ uname -a Linux myhost.mydom.tld 2.6.12-1.1390_FC4_cubbi4_swsusp2 #1 Sat Jul 9 12:34:47 CEST 2005 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~ 2. mysql [EMAIL PROTECTED] ~ rpm -qa |grep -i mysql MySQL-server-4.1.14-0 MySQL-shared-4.1.14-0 MySQL-devel-4.1.14-0 MySQL-client-4.1.14-0 [EMAIL PROTECTED] ~ 3. mysql tables two tables: localhost.addresses2 describe first_table; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | name | text | YES | | NULL || | title| text | YES | | NULL || | phone| text | YES | | NULL || | desc | text | YES | | NULL || | comment | text | YES | | NULL || | status | text | YES | | NULL || | url | text | YES | | NULL || | businesscategory | text | YES | | NULL || | address | text | YES | | NULL || | kanton | text | YES | | NULL || | reply_1 | text | YES | | NULL || | reply_2 | text | YES | | NULL || | reply_3 | text | YES | | NULL || | reply_date | datetime | YES | | NULL || | ip_address | text | YES | | NULL || | firm | text | YES | | NULL || | served | datetime | YES | | NULL || | addon| text | YES | | NULL || | givenname| text | YES | | NULL || | history | text | YES | | NULL || | favorit | text | YES | | NULL || | last_update | timestamp| YES | | -00-00 00:00:00 || | task_link| int(11) | YES | | NULL || | counter | int(10) unsigned | | PRI | NULL | auto_increment | +--+--+--+-+-++ 24 rows in set (0.00 sec) localhost.addresses2 localhost.addresses2 describe second_table; +--+--+--+-+---++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---++ | name | text | YES | | NULL || | title| text | YES | | NULL || | phone| text | YES | | NULL || | desc | text | YES | | NULL || | comment | text | YES | | NULL || | status | text | YES | | NULL || | url | text | YES | | NULL || | businesscategory | text | YES | | NULL || | address | text | YES | | NULL || | kanton | text | YES | | NULL || | reply_1 | text | YES | | NULL || | reply_2 | text | YES | | NULL || | reply_3 | text | YES | | NULL || | reply_date | datetime | YES | | NULL || | firm | text | YES | | NULL || | served | datetime | YES | | NULL || | addon| text | YES | | NULL || | givenname| text | YES | |
Thank you ... Help on writing a sql statement
Hi Shawn: Just wanted to publicly thank you for the time you took to help me. I think that it is important that guys like yourself who take time out from your busy work be appreciated when their solution made a huge difference. I was developing an ASP application along with Crystal reports for a customer which was reporting from million of rows of data and my original approach resulted in very poor performance .. reports being VERY sluggish (45 mins to run some). I followed your concept and the same report that took 45 mins to run took 40 seconds Unfortunately I could not implement it using MySql because the current version of MySql ODBC (3.51) did not expose the stored procedures to CR and I could not find an OleDb provider that will work for MySql. However, I ended up using Sql Server but I followed you suggestion and gained tremendous performance improvements. Keep up the good work. Best regards Imran Solution --- Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a certain date. If I rephrased that correctly, here is how I would build your query. Step 1 is to perform the (date,customer,product) summations. By minimizing the number of rows, columns, and/or tables we need to summarize against, we improve performance. So I do this part of the analysis before I join in the other tables. Note: Date, time, and datetime literals are represented by single-quoted strings. You do not need the DATE() function to create a date literal. CREATE TEMPORARY TABLE tmpTotals ( key(CustNo) , key(ProdNo) ) SELECT PostingDate , CustNo , ProdNo , sum(Cost) as costs , sum(Sales) as sales FROM salesmaster WHERE PostingDate = '2005-09-01 00:00:00' GROUP BY PostingDate, CustNo, ProdNo; Step 2: collect the rest of the information for the report. SELECT CustNo , c.Name as custname , ProdNo , p.Name as prodname , costs , sales , PostingDate FROM tmpTotals tt LEFT JOIN customerintermediate c ON c.CustNo = tt.CustNo LEFT JOIN productintermediate p ON p.ProdNo = tt.ProdNo ORDER BY ... your choice... ; Step 3: The database is not your momma. Always clean up after yourself. DROP TEMPORARY TABLE tmpTotals; And you are done! The only trick to doing a sequence of statements in a row (like this) is that they all have to go through the same connection. As long as you do not close and re-open the connection between statements, any temp tables or @-variables you create or define remain in existence for the life of the connection. Depending on your connection library, you might be able to execute all three statements from a single request. Most likely, you will need to send them in one-at-a-time. Does this help you to organize your thoughts? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
