Function Not Working
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I do
a simple SELECT ProperCase('JESSE'); it returns "JESSE" on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns "Jesse" as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work properly?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld-nt Windows service: delay btwn svc running and accepting conx
Joerg Bruehe wrote: Yes, it does announce. I wrote "any external event ... that Windows *would* monitor" (emphasis added): Does the Windows ServiceManager really know which file to monitor for which entry to announce the service as "running" ? see below. in practice we find the service comes up to RUNNING within several seconds of when we announce its being started. this is the longest delay between RUNNING and ready-to-accept we have seen so far in testing, on the order of 10 seconds. I perfectly understand this delay is bad for you. actually, as i tried to say initially, its more an inconvenience than bad. we are presently simply polling by attempting to make connections and catching exceptions until we succeed. after which we consider the MySQL service up and running. If you find some documentation telling how the MySQL server could tell the Windows ServiceManager that it is now really ready to accept connections, then please file a bug (feature request) with a pointer to that info. well, your server already communicates using the SetServiceStatus function. for example, see the ServiceMain and SetStatus functions in nt_servc.cc. here is the MSDN docs on the function: http://msdn.microsoft.com/en-us/library/ms686241(VS.85).aspx For now, I assume the ServiceManager just monitors whether a process is running, and so an unknown time for the initialization phase cannot be handled exactly. the service tells the ServiceManager about its state. for some reason, your Windows programmers have decided to announce STATE_RUNNING before the engines are up and accepting connections. perhaps they have good reasons for doing that. the purpose of my email was to see what could be done, if anything. if you think it's worth a feature request, i will try that. thanks Les -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld-nt Windows service: delay btwn svc running and accepting conx
Hi Les, all, sorry - I attended to other things more than to this thread. Les Schaffer wrote: Joerg Bruehe wrote: I assume Windows reports the status as "running" when the process(es) got started, but that doesn't necessary imply they have passed their own initialization / startup phase. I doubt there is any external event (say, a file creation or some such) that Windows would monitor and check before reporting it as "running". the MySQL server log does contain a statement: 080605 23:29:12 InnoDB: Started; log sequence number 0 5087217 080605 23:29:14 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe: ready for connections. so the server does "announce" to the log. now here is from our own logs: Yes, it does announce. I wrote "any external event ... that Windows *would* monitor" (emphasis added): Does the Windows ServiceManager really know which file to monitor for which entry to announce the service as "running" ? 2008-06-05 23:28:57 windowsservice 156 INFO Starting service MySQL 2008-06-05 23:29:02 sql 102 CRITICAL MySQL OperationalError 2003 (2003, "Can't connect to MySQL server on 'localhost' (10061)") in practice we find the service comes up to RUNNING within several seconds of when we announce its being started. this is the longest delay between RUNNING and ready-to-accept we have seen so far in testing, on the order of 10 seconds. I perfectly understand this delay is bad for you. [[...]] If there is something visible externally, I don't know it - hope our Windows experts might have a hint. Windows ServiceManager reports the service state as in either STARTING or RUNNING. i don't know what the details are of the communication between the ServiceManager and and mysqld-nt, but if mysqld-nt reported itself as RUNNING (and not STARTING) only when its ready to accept connections, that would be fine with us ;-) I should have written my disclaimer much earlier: I am no Windows user. If you find some documentation telling how the MySQL server could tell the Windows ServiceManager that it is now really ready to accept connections, then please file a bug (feature request) with a pointer to that info. For now, I assume the ServiceManager just monitors whether a process is running, and so an unknown time for the initialization phase cannot be handled exactly. thanks for your note. You are welcome, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing UNIONs ?
(mysql 5.0.27 on SuSE Linux) I recently thought to use UNIONs to allow me to concatenate "vertically" some database tables with statements like this create or replace view combo as (select * from nov06) union (select *,ra_corr as ra_cor2,dec_corr as dec_cor2 from jul07) union (select *,ra_corr as ra_cor2,dec_corr as dec_cor2 from subaru where field<2100); (a show create view tells me this was created as CREATE ALGORITHM=UNDEFINED, and of course gives the full list of columns) The sense of the above is that "nov06" is a first release of an astronomical database containing a sky region, and the related catalogue has already been published (so it shall be mantained unchanged). "jul07" and "subaru" are two incremental additions in other sky regions, which I'd like to see "all together" as an union ... and at the same time to keep physically separate to ease maintenance. The three tables in the union have the same layout, except that nov06 has two columns more. To allow the unions these columns are "mimicked" as identical copies of two other columns in the other two tables (per CREATE statement above). I do not expect the CREATE makes any difficulty ... it is shown as a full list of columns. All three tables have two indices, an UNIQUE one on two columns (`field`,`id`), and another one on the single column "seq" (a sequence number which is also auto_increment ... but the tables are static once created). In particular seq runs sequentially from table to table so that the first jul07.seq is equal to the last nov06.seq + 1 and so on. So far so good ... ... those unions work nicely, only slightly slower than a single table . In the past I had (and still have) also some views which allow simultaneous "horizontal" access to more than one single table via a glorified correlation table (just a table of pointers, I hope the definition below illustrates the usage clearly enough) create ALGORITHM=TEMPTABLE VIEW XLSS as list of column aliases from glorlss06 left join nov06 on glorlss06.nov06 =nov06.seq left join nov06b on glorlss06.nov06b =nov06b.seq left join nov06cd on glorlss06.nov06cd=nov06cd.seq ; This is just an example with three "horizontal" members. I have more complex examples with up to 30 members, and lived satisfactorily with them. (the ALGORITHM=TEMPTABLE and sometimes the usage of SELECT STRAIGHT_JOIN are the only tricks required to improve efficiency, the latter was even discussed on this list ... ah the glorlss06 of course have a couple of indices, an unique one on (`seq`,`nov06`) and another on nov06 alone. Just for reference this is example of EXPLAIN SELECT on such view explain select Xcatname,Xseq,Xra,Xdec from XLSS where Xseq=13121; ++-++--+---+---+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+---+-+-+--+-+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL| 3385 | Using where | | 2 | DERIVED | glorlss06 | ALL | NULL | NULL | NULL | NULL| 3385 | | | 2 | DERIVED | nov06 | ref | auxiliary | auxiliary | 4 | lssdb.glorlss06.nov06 | 16 | | | 2 | DERIVED | nov06b | ref | auxiliary | auxiliary | 4 | lssdb.glorlss06.nov06b | 16 | | | 2 | DERIVED | nov06cd| ref | auxiliary | auxiliary | 4 | lssdb.glorlss06.nov06cd | 16 | | ++-++--+---+---+-+-+--+-+ In case this gets wrapped in the mail a copy can be seen at http://sax.iasf-milano.inaf.it/~lucio/temp/Temp/example.txt Such a statement (the select, not the explain) takes 0.19 seq . and now the trouble comes ... when I want to put everything together I create an "horizontal" view whose members are "vertical" unions create ALGORITHM=TEMPTABLE VIEW INTERIM as list of column aliases from glorcombo left join combo on glorcombo.combo =combo.seq left join combob on glorcombo.combob =combob.seq left join combocd on glorcombo.combocd=combocd.seq ; combo with its three members was illustrated above, and combob and combocd are fully equivalent unions with 3 members each. glorcombo is instead a physical table. A statement fully analogous to the previous one takes now 49 sec instead of a fraction. All the time is spent in the analysis phase of EXPLAIN select (which I report below, and, in case of wrap, at the URL given above e
Re: revoke all on *.* ??
[EMAIL PROTECTED] (Pawel Eljasz) writes: > there is a user with following grants: > GRANT USAGE ON *.* TO 'ff'@'localhost' IDENTIFIED BY PASSWORD > 'x' > is it possible to: > revoke all on *.* from $above_user OR revoke usage on *.* from $above_user > ? DROP USER [EMAIL PROTECTED]; Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: mysql query, min, max with where conditions
Well, if your particular problem has a well defined maximum minimum and minimum maximum (Ie the max(q) < 4294967296, because q is a 32 bit unsigned int, and min(q)> -1) then you can do it without any extra joins or sub selects. select a, b, min(IF(date <100, q, 4294967296)) as min_q, max(IF(date>100, q, -1)) as min_q, from a left join b left join c group by a.p - Original Message From: "CRISTEA, Adrian" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Monday, June 9, 2008 9:33:37 AM Subject: mysql query, min, max with where conditions Hello there, What is the corect syntax for selecting something like: select a, b, (min(q) where date<100), (max(q) where date>100) from a left join b left join c group by a.p i need min() max() values each of them with other WHERE clause. How can I do that? Adrian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: revoke all on *.* ??
Pawel- the syntax is GRANT USAGE ON DB.* to '[EMAIL PROTECTED]' IDENTIFIED BY PASSWORD 'NeverShowYourPasswordInEmail' http://dev.mysql.com/doc/refman/5.0/en/grant.html HTH Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Tue, 10 Jun 2008 12:45:24 +0100 > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > Subject: revoke all on *.* ?? > > dear all > mysql 5.0.51a, very basic config thus I don't think could be of any > relevance here > there is a user with following grants: > GRANT USAGE ON *.* TO 'ff'@'localhost' IDENTIFIED BY PASSWORD > '077042fb4932b5b5' > is it possible to: > revoke all on *.* from $above_user OR revoke usage on *.* from $above_user > ? > when I execute this I get no errors, but show grants still show there > are privileges > is it possible to make a user able to view and work only on one specific db? > I think of it in respect of phpmyadmin, like user can only access a > specific DB and no access to > anything else whatsoever? > > cheers > P. > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > _ Enjoy 5 GB of free, password-protected online storage. http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skydrive_062008
revoke all on *.* ??
dear all mysql 5.0.51a, very basic config thus I don't think could be of any relevance here there is a user with following grants: GRANT USAGE ON *.* TO 'ff'@'localhost' IDENTIFIED BY PASSWORD '077042fb4932b5b5' is it possible to: revoke all on *.* from $above_user OR revoke usage on *.* from $above_user ? when I execute this I get no errors, but show grants still show there are privileges is it possible to make a user able to view and work only on one specific db? I think of it in respect of phpmyadmin, like user can only access a specific DB and no access to anything else whatsoever? cheers P. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around 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: Index/Range Problem?
Hi, Thanks for the help; that makes sense I think you guys are right. Is it worth tuning such a thing? It seems to me like it would be much faster to use the index? Thanks again Dave > Dave schrieb: >> Hi all, >> I've been trying to optimize some of our queries against a large >> database >> and come up against an index problem I haven't been able to find any >> documentation on. I've cut the query down to the bare minimum, and found >> the following -- >> >> explain Select iname,domain,serv,time from log where date between >> '2008-05-10' and '2008-05-30'; >> ++-+---+---+---+--+-+--+ >> ---+-+ >> | id | select_type | table | type | possible_keys | key | key_len | >> ref >> | rows | Extra | >> ++-+---+---+---+--+-+--+ >> ---+-+ >> | 1 | SIMPLE | log | range | date | date | 4 | >> NULL >> | 45178 | Using where | >> ++-+---+---+---+--+-+--+ >> ---+-+ >> >> >> As you can see in the above query, it uses type "range" and the key date >> is used. If I change it to -05-01 to -05-30 though it does not : >> >> explain Select iname,domain,serv,time from log where date between >> '2008-05-01' and '2008-05-30'; >> ++-+---+--+---+--+-+--+- >> ---+-+ >> | id | select_type | table | type | possible_keys | key | key_len | ref >> | rows | Extra | >> ++-+---+--+---+--+-+--+- >> ---+-+ >> | 1 | SIMPLE | log | ALL | date | NULL | NULL| >> NULL >> | 353558 | Using where | >> ++-+---+--+---+--+-+--+- >> ---+-+ >> >> As you can see the type is now "ALL" and it doesn't work. > > as Ananda already wrote, it seems rows valid for '2008-05-01' to > '2008-05-30' are exceed the threshold when MySQL thinks it is faster to > scan > the table instead of scan the index and than read the table > > >> What could be the cause of this? It seems like its limited to a specific >> number of rows? The rows in explain appear to be wrong... >> mysql> Select count(id) from log where date between '2008-05-01' and >> '2008-05-30'; >> +---+ >> | count(id) | >> +---+ >> | 85232 | >> +---+ >> 1 row in set (0.97 sec) > > "rows" is how many rows MySQL thinks it must examine to execute the query, > not the number of rows possible returned > > http://dev.mysql.com/doc/refman/5.1/en/using-explain.html > > > -- > Sebastian Mendel > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index/Range Problem?
Dave schrieb: Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time from log where date between '2008-05-10' and '2008-05-30'; ++-+---+---+---+--+-+--+ ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+ ---+-+ | 1 | SIMPLE | log | range | date | date | 4 | NULL | 45178 | Using where | ++-+---+---+---+--+-+--+ ---+-+ As you can see in the above query, it uses type "range" and the key date is used. If I change it to -05-01 to -05-30 though it does not : explain Select iname,domain,serv,time from log where date between '2008-05-01' and '2008-05-30'; ++-+---+--+---+--+-+--+- ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- ---+-+ | 1 | SIMPLE | log | ALL | date | NULL | NULL| NULL | 353558 | Using where | ++-+---+--+---+--+-+--+- ---+-+ As you can see the type is now "ALL" and it doesn't work. as Ananda already wrote, it seems rows valid for '2008-05-01' to '2008-05-30' are exceed the threshold when MySQL thinks it is faster to scan the table instead of scan the index and than read the table What could be the cause of this? It seems like its limited to a specific number of rows? The rows in explain appear to be wrong... mysql> Select count(id) from log where date between '2008-05-01' and '2008-05-30'; +---+ | count(id) | +---+ | 85232 | +---+ 1 row in set (0.97 sec) "rows" is how many rows MySQL thinks it must examine to execute the query, not the number of rows possible returned http://dev.mysql.com/doc/refman/5.1/en/using-explain.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching subtables
Sebastian Mendel wrote: Werner Van Belle schrieb: Hello, You might find the following challenging -or- you might now the answer :-) Table Q Subtable, field, val, ID A 1 a42 A 2 b42 B 1 a78 B 2 t78 B 3 o78 C 1 u23 Table R Subtableid, field, val A 1 a A 2 b Table S Subtableid, field, val B 1 a B 2 t Table T Subtableid, field, val C 1 u A 1 a A 2 b We now want to check whether table R is fully contained in table Q and what the ID is. In this case the answer should be 42. However if we would use table S and mathc it against table Q, then we should not get 78 back since field 3 is missing in table S. Also, we might want to perform this operation in batch mode, where we provide a table such as T for which we then should get the return value Subtable, field, val, ID A 1 a42 A 2 b42 C 1 u23 Is there anybody that bumped into a similar query and was able to solve it satisfactory ? you can do a OUTER JOIN on subtableid, and than check for NULL values (with HAVING), which means that at least one field is missing in one of the tables with sub selects: untested: SELECT Subtable, ID FROM `T` WHERE ID NOT IN ( SELECT ID FROM T OUTER JOIN Q ON T.Subtable = Q.Subtable AND T.field = Q.field AND T.val = Q.val HAVING ISNULL(Q.ID) OR ISNULL(T.ID) ) Thanks for the hint. You query helped me along. Currently I have something like SELECT Q.subtable, Q.field, Q.id FROM Q JOIN T ON T.subtable=Q.subtable and T.field=Q.field and T.val=Q.val WHERE ID not in (SELECT distinct ID FROM Q LEFT OUTER JOIN T ON T.subtable=Q.subtable and T.field=Q.field and T.val=Q.val WHERE T.field is NULL) which does a large part of the job. Wkr, -- Dr. Werner Van Belle http://werner.sigtrans.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
