Lands with sea view and beaches for sale, it worth try to see. Thank You!
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; head meta http-equiv=Content-Type content=text/html; charset=utf-8 / titlenewsletter arpsworldproperties/title link href=news.css rel=stylesheet type=text/css / /head bodytable border=0 cellspacing=0 cellpadding=0 tr td colspan=3img src=imagens/header.jpg width=595 height=160 //td /tr tr td colspan=3 class=contactotituloOur contacts:/td /tr tr td colspan=3 class=contactospan class=contactoitalicotel:/span + 44 (0)208 200 60 20 |em span class=contactoitalicofax/span/emspan class=contactoitalico: /span+ 44 (0)208 200 10 49 | span class=contactoitalicom:/span + 44 (0)7938 056 756br / span class=contactoitalicoemail:/span a href=mailto:[EMAIL PROTECTED] class=contactoemail[EMAIL PROTECTED]/a | span class=contactoitalicowebsite:/span a href=http://arpsworldproperties.com; target=_blank class=contactositewww.arpsworldproperties.com/a/td /tr tr td colspan=3 class=textoitalicoWe are pleased in offering you 3 amazing opportunities for investment in Cape Verde.br / 3 amazing plots of land, exclusive to us for sale. br / We will assist in all aspects of the purchase until is completed. br / Before that, we will be too pleased in taking you there for a visit. /td /tr tr td colspan=3 class=textocallCall us now on (+ 44) 0208 200 60 20/td /tr tr td colspan=3 class=propriedadetituloCape Verde, Island of Santiago/td /tr tr td width=174 valign=top bgcolor=#DFFFBF class=imageimg src=imagens/property1.jpg width=174 height=129 //td td width=365 bgcolor=#DFFFBF class=propriedadesubtitulo1Tarrafal br / br / span class=propriedadesubtituloA 114,000 SQUARE METERS br / at â¬195 Euros/square meter /spanbr / span class=propriedadesubtituloBtotal price: â¬22,230,000/span/td td width=115 rowspan=2 bgcolor=#DFFFBF class=imageimg src=imagens/property1AA.jpg width=115 height=372 //td /tr tr td colspan=2 bgcolor=#DFFFBF class=textonormalp Payment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission. /p p span class=textoAlready Aproved development for resort: /span/p p class=texto Resort br / Casino br / Bungalowsbr / Hotel br / Apartments /p p Property has supply of Water, Electricity and Sewerage /p p class=texto At this time you cannot get a better deal for development than this, in Cape Verde /p/td /tr tr td colspan=3 valign=top bgcolor=#D2F4FF class=imageimg src=imagens/property2.jpg width=174 height=129 class=imageleft /span class=propriedadesubtitulo1Ponta Bomba/spanspan class=propriedadesubtituloitalicobr / (20 Km from Capital Praia)/spanbr / p class=propriedadesubtituloA322,5 Hectares (3,225,000 square meters) br / at just â¬12 Euros per square meter br / span class=propriedadesubtituloB total cost = â¬38,700,000/span/p p class=textonormalPayment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission./p/td /tr tr td colspan=3 valign=top bgcolor=#DFFFBFpimg src=imagens/property3.jpg width=174 height=129 class=imageleft /span class=propriedadesubtitulo120 KM from Capital Praia, /spanspan class=propriedadesubtituloitalico br / near the old town/span /p p class=propriedadesubtituloA900 hectares br / (can be divided in 2 plots of 450 hectares)br / span class=propriedadesubtituloB at only â¬3 Euros per square meter/span /p p class=textonormalAmazing plot of land with a total area of 900 hectares - 9,000,000 (nine million) square meters - in the vicinity of Porto Mosquito, Santiago Island, ideal for a super development in Cape Verde at a great price./p p class=textonormalPayment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission./p/td /tr tr td colspan=3nbsp;/td /tr tr td colspan=3 valign=top class=contactotituloDisclaimer:/td /tr tr td colspan=3 valign=top class=textoitalicospamThis email and its attachments have been sent by ARPS World Ltd and are not spam and may be confidential, being intended solely for the use of the individual to whom it is addressed. br / If you are not the intended recipient of this email and its attachments, you must take no action based upon them, nor must you copy or show them to anyone.br / If you believe you have received this email in error or if you wish to be excluded from our mailing list please send email to a href=mailto:[EMAIL PROTECTED] class=textoitalicospamemail[EMAIL PROTECTED]/a with subject title: âplease stop sending business mailsâ/td /tr /table /body /html -- MySQL General Mailing List For list archives:
enable and disable keys
Hi All, We are doing load data into a table using LOAD DATA INFILE process. Below is the method we are following. 1. create empty table with all the indexes. 2. disable keys 3. Load data using LOAD DATA INFILE, close to 99 Million records which takes around 3 hrs 4 . Enable keys Table size is around 19.5 gb There is one primary key, 2 non unique indexes and one FULLTEXT INDEX. Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. please let me know, this problem is causing lot of dealy for other process to run. regards anandkl
Problem with BLOB data.
Hi, I have a problem with the migration of a table that has a column with BLOB data. The source server is MySQL version: 4.0.16 and the destination server is MySQL version: 5.0.45 I tried with mysqldump and SELECT INTO but when import the data on the destination server, the BLOB data are corrupt. Someone can help me? Thanks -- Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma tel:0657060500 email:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lands with sea view and prived beaches for sale. It worth try to see it. TY!
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; head meta http-equiv=Content-Type content=text/html; charset=utf-8 / titlenewsletter arpsworldproperties/title link href=news.css rel=stylesheet type=text/css / /head bodytable border=0 cellspacing=0 cellpadding=0 tr td colspan=3img src=imagens/header.jpg width=595 height=160 //td /tr tr td colspan=3 class=contactotituloOur contacts:/td /tr tr td colspan=3 class=contactospan class=contactoitalicotel:/span + 44 (0)208 200 60 20 |em span class=contactoitalicofax/span/emspan class=contactoitalico: /span+ 44 (0)208 200 10 49 | span class=contactoitalicom:/span + 44 (0)7938 056 756br / span class=contactoitalicoemail:/span a href=mailto:[EMAIL PROTECTED] class=contactoemail[EMAIL PROTECTED]/a | span class=contactoitalicowebsite:/span a href=http://arpsworldproperties.com; target=_blank class=contactositewww.arpsworldproperties.com/a/td /tr tr td colspan=3 class=textoitalicoWe are pleased in offering you 3 amazing opportunities for investment in Cape Verde.br / 3 amazing plots of land, exclusive to us for sale. br / We will assist in all aspects of the purchase until is completed. br / Before that, we will be too pleased in taking you there for a visit. /td /tr tr td colspan=3 class=textocallCall us now on (+ 44) 0208 200 60 20/td /tr tr td colspan=3 class=propriedadetituloCape Verde, Island of Santiago/td /tr tr td width=174 valign=top bgcolor=#DFFFBF class=imageimg src=imagens/property1.jpg width=174 height=129 //td td width=365 bgcolor=#DFFFBF class=propriedadesubtitulo1Tarrafal br / br / span class=propriedadesubtituloA 114,000 SQUARE METERS br / at â¬195 Euros/square meter /spanbr / span class=propriedadesubtituloBtotal price: â¬22,230,000/span/td td width=115 rowspan=2 bgcolor=#DFFFBF class=imageimg src=imagens/property1AA.jpg width=115 height=372 //td /tr tr td colspan=2 bgcolor=#DFFFBF class=textonormalp Payment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission. /p p span class=textoAlready Aproved development for resort: /span/p p class=texto Resort br / Casino br / Bungalowsbr / Hotel br / Apartments /p p Property has supply of Water, Electricity and Sewerage /p p class=texto At this time you cannot get a better deal for development than this, in Cape Verde /p/td /tr tr td colspan=3 valign=top bgcolor=#D2F4FF class=imageimg src=imagens/property2.jpg width=174 height=129 class=imageleft /span class=propriedadesubtitulo1Ponta Bomba/spanspan class=propriedadesubtituloitalicobr / (20 Km from Capital Praia)/spanbr / p class=propriedadesubtituloA322,5 Hectares (3,225,000 square meters) br / at just â¬12 Euros per square meter br / span class=propriedadesubtituloB total cost = â¬38,700,000/span/p p class=textonormalPayment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission./p/td /tr tr td colspan=3 valign=top bgcolor=#DFFFBFpimg src=imagens/property3.jpg width=174 height=129 class=imageleft /span class=propriedadesubtitulo120 KM from Capital Praia, /spanspan class=propriedadesubtituloitalico br / near the old town/span /p p class=propriedadesubtituloA900 hectares br / (can be divided in 2 plots of 450 hectares)br / span class=propriedadesubtituloB at only â¬3 Euros per square meter/span /p p class=textonormalAmazing plot of land with a total area of 900 hectares - 9,000,000 (nine million) square meters - in the vicinity of Porto Mosquito, Santiago Island, ideal for a super development in Cape Verde at a great price./p p class=textonormalPayment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission./p/td /tr tr td colspan=3nbsp;/td /tr tr td colspan=3 valign=top class=contactotituloDisclaimer:/td /tr tr td colspan=3 valign=top class=textoitalicospamThis email and its attachments have been sent by ARPS World Ltd and are not spam and may be confidential, being intended solely for the use of the individual to whom it is addressed. br / If you are not the intended recipient of this email and its attachments, you must take no action based upon them, nor must you copy or show them to anyone.br / If you believe you have received this email in error or if you wish to be excluded from our mailing list please send email to a href=mailto:[EMAIL PROTECTED] class=textoitalicospamemail[EMAIL PROTECTED]/a with subject title: âplease stop sending business mailsâ/td /tr /table /body /html -- MySQL General Mailing List For list archives:
Function Still Not Working
Sorry for posting this again, but I got only one response last time, and I'm
still having the problem. I spent HOURS the other day manually going
through the data and Properizing these things by hand. I don't want to do
that again if I can avoid it. If anyone has any clues on this one, I would
appreciate it.
The only difference in this and what I have now is that someone suggested
changing it to Deterministic, which I did, and that didn't change the
output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER,
and that didn't make a difference either.
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: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time, and
I'm
still having the problem. I spent HOURS the other day manually going
through the data and Properizing these things by hand. I don't want to do
that again if I can avoid it. If anyone has any clues on this one, I
would
appreciate it.
The only difference in this and what I have now is that someone suggested
changing it to Deterministic, which I did, and that didn't change the
output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER,
and that didn't make a difference either.
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?
Any difference in default collation?
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Jesse schrieb:
Sorry for posting this again, but I got only one response last time, and
I'm still having the problem. I spent HOURS the other day manually
going through the data and Properizing these things by hand. I don't
want to do that again if I can avoid it. If anyone has any clues on
this one, I would appreciate it.
The only difference in this and what I have now is that someone
suggested changing it to Deterministic, which I did, and that didn't
change the output. I also changed SQL SECURITY DEFINER to SQL
SECURITY INVOKER, and that didn't make a difference either.
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.
does it work outside the function?
did you tried SUBSTRING(cInput, 2)?
did you tried with converting?
from the manual: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing IP blocks
Hi, I need to be able to store data from our Radius servers that includes IP addressing and IP blocks into a MySQL field that is easily searchable. Basic IP addreses I have sorted with INET_ATON(), but is there a way to store blocks of IP's as in 192.168.1.0/24 in a way that would allow it to be found in a search for say 192.168.1.100? Perhaps a method to take the block and insert the 1st and last IP's into seperate columns so I can check to see if it falls between these 2 values? The import of Radius into MySQL is performed by doing a 'LOAD DATA LOCAL INFILE' on a CSV file. Ideal would be a solution to insert the 1st and last IP address based on the IP block at the time of doing the LOAD? Thanks. Jim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enable and disable keys
Ananda Kumar wrote: Hi All, We are doing load data into a table using LOAD DATA INFILE process. Below is the method we are following. 1. create empty table with all the indexes. 2. disable keys 3. Load data using LOAD DATA INFILE, close to 99 Million records which takes around 3 hrs 4 . Enable keys Table size is around 19.5 gb There is one primary key, 2 non unique indexes and one FULLTEXT INDEX. Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. please let me know, this problem is causing lot of dealy for other process to run. regards anandkl barclay# perror 28 OS error code 28: No space left on device I would say you are running out of space. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enable and disable keys
We have 200GB of free space on the file system where our database is located. On 6/12/08, Ken Menzel [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi All, We are doing load data into a table using LOAD DATA INFILE process. Below is the method we are following. 1. create empty table with all the indexes. 2. disable keys 3. Load data using LOAD DATA INFILE, close to 99 Million records which takes around 3 hrs 4 . Enable keys Table size is around 19.5 gb There is one primary key, 2 non unique indexes and one FULLTEXT INDEX. Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. please let me know, this problem is causing lot of dealy for other process to run. regards anandkl barclay# perror 28 OS error code 28: No space left on device I would say you are running out of space.
Re: enable and disable keys
Ananda Kumar wrote: We have 200GB of free space on the file system where our database is located. On 6/12/08, Ken Menzel [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi All, We are doing load data into a table using LOAD DATA INFILE process. Below is the method we are following. 1. create empty table with all the indexes. 2. disable keys 3. Load data using LOAD DATA INFILE, close to 99 Million records which takes around 3 hrs 4 . Enable keys Table size is around 19.5 gb There is one primary key, 2 non unique indexes and one FULLTEXT INDEX. Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. please let me know, this problem is causing lot of dealy for other process to run. regards anandkl barclay# perror 28 OS error code 28: No space left on device I would say you are running out of space. What is the value of - show variables like tmpdir ? If you have not set it it will try to use your system /tmp, which does not appear to be big enough. http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html Hope this helps, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improve performance on FULLTEXT search.
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
Re: improve performance on FULLTEXT search.
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d H:i:s ?
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2008 6:23 AM
To: MySQL List
Subject: Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time,
and
I'm
still having the problem. I spent HOURS the other day manually
going
through the data and Properizing these things by hand. I don't want
to do
that again if I can avoid it. If anyone has any clues on this one,
I
would
appreciate it.
---8--- snip
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?
Any difference in default collation?
I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).
Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?
Just spit-balling...
Todd Boyd
Web Programmer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enable and disable keys
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1B row/50GB myisam w/5 indexes - build after or during load?
hey, as the sub says I have a dump of a 50GB (MYD/60GB MYI) table I need to load on another server. I guess the bigger question is how can I optimize this, particularly the index builds? I had always thought is was best in these cases to create the table w/o any indexes, load the rows then create the keys but watching what's happening on the filesystem makes me suspect MySQL's rebuilding the entire table and previously created indexes with each incremental index added - is this the case? I see that mysqldump puts all the keys in the create table statement (vs. Oracle exp which creates them post-rows) which would seem to imply it's better to extended insert with existing indexes vs creating them afterward... bottom line: how do I optimize this load (my.cnf settings, deferred index build [or not], etc)? I have the box completely to myself so nothing's off the table... thanks!
Re: enable and disable keys
/tmp has 16GB free space On 6/12/08, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Todd Boyd Web Programmer
Re: 1B row/50GB myisam w/5 indexes - build after or during load?
Sid, Do you still have them as myisam files (myd,myi)? If yes and your other server has the same mysql version and cpu architecture you can just copy the myisam binary files to the new server. If you have the dump only you can either load with the mysql client or parse it, create a csv filw with just the values of the tables column in order and then use load data infile. This will be much faster than loading the dump. As far as your indices go, I would create them before loading the data in any case. Olaf On 6/12/08 11:25 AM, Sid Lane [EMAIL PROTECTED] wrote: hey, as the sub says I have a dump of a 50GB (MYD/60GB MYI) table I need to load on another server. I guess the bigger question is how can I optimize this, particularly the index builds? I had always thought is was best in these cases to create the table w/o any indexes, load the rows then create the keys but watching what's happening on the filesystem makes me suspect MySQL's rebuilding the entire table and previously created indexes with each incremental index added - is this the case? I see that mysqldump puts all the keys in the create table statement (vs. Oracle exp which creates them post-rows) which would seem to imply it's better to extended insert with existing indexes vs creating them afterward... bottom line: how do I optimize this load (my.cnf settings, deferred index build [or not], etc)? I have the box completely to myself so nothing's off the table... thanks! - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unescaping strings with the C api
Using MySQL 5.0.45 on linux. For the first time I am using the C API directly. I am using mysql_real_escape_string() - see http://dev.mysql.com/doc/refman/5.1/en/mysql-real-escape-string.html I can not locate a C api function to _unescape_ strings. If there were one, I would use it. If I have overlooked something in the API, I would welcome comments. I am familiar with methods outside of the API for unescaping. Thanks Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
On 6/12/08, Sebastian Mendel [EMAIL PROTECTED] wrote:
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the
performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
no not in the same order
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
H:i:s ?
Good catch, mysql gives in the y-m-d H:i:s format
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
I need to check with developer
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
Also need to check with developer, Just guessing, they want for prince
between 0 and 999
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
Re: unescaping strings with the C api
Tim Johnson wrote: I can not locate a C api function to _unescape_ strings. Why do you believe you need one? You need to escape strings when building SQL query strings to avoid problems with quote characters, which are special in SQL. When MySQL returns the queried data to your program, it's not using a SQL query to do so. It just gives the data back in a directly usable form. You'd only need an unescape function if you were writing a SQL parser. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Sebastian Mendel [EMAIL PROTECTED] wrote:
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take
close
to 3 min for the same query to execute, any ways to improve the
performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
no not in the same order will this have any performance impact.
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
H:i:s ?
Good catch, mysql gives in the y-m-d H:i:s format
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
I need to check with developer
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
Also need to check with developer, Just guessing, they want for prince
between 0 and 999
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys
|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
Re: 1B row/50GB myisam w/5 indexes - build after or during load?
thanks! I can't do a physical copy as it's an upgrade from 32-bit 3.23 (don't ask - ancient shrink-wrap app) to 64-bit 4.0 (again, don't ask - least bad option for reasons you don't want to know). I'll may try the csv/load data though I've never understood what that should be faster than extended insert (mysqldump -e) - seems like they'd be calling the same routines/libraries. not saying that's not the case, just wonder why... what about my.cnf settings? I've jacked up key_buffer_size, bulk_insert_buffer_size myisam_sort_buffer_size for the load - are there others that would help? On Thu, Jun 12, 2008 at 11:31 AM, Olaf Stein [EMAIL PROTECTED] wrote: Sid, Do you still have them as myisam files (myd,myi)? If yes and your other server has the same mysql version and cpu architecture you can just copy the myisam binary files to the new server. If you have the dump only you can either load with the mysql client or parse it, create a csv filw with just the values of the tables column in order and then use load data infile. This will be much faster than loading the dump. As far as your indices go, I would create them before loading the data in any case. Olaf On 6/12/08 11:25 AM, Sid Lane [EMAIL PROTECTED] wrote: hey, as the sub says I have a dump of a 50GB (MYD/60GB MYI) table I need to load on another server. I guess the bigger question is how can I optimize this, particularly the index builds? I had always thought is was best in these cases to create the table w/o any indexes, load the rows then create the keys but watching what's happening on the filesystem makes me suspect MySQL's rebuilding the entire table and previously created indexes with each incremental index added - is this the case? I see that mysqldump puts all the keys in the create table statement (vs. Oracle exp which creates them post-rows) which would seem to imply it's better to extended insert with existing indexes vs creating them afterward... bottom line: how do I optimize this load (my.cnf settings, deferred index build [or not], etc)? I have the box completely to myself so nothing's off the table... thanks! - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you.
Re: unescaping strings with the C api
On Thursday 12 June 2008, Warren Young wrote: Tim Johnson wrote: I can not locate a C api function to _unescape_ strings. Why do you believe you need one? You need to escape strings when building SQL query strings to avoid problems with quote characters, which are special in SQL. When MySQL returns the queried data to your program, it's not using a SQL query to do so. It just gives the data back in a directly usable form. Not sure what you mean by directly usable. If I do an insert statement with a backslash, for example: headline\one, I will retrieve headline\\one, and that will need to be unescaped, because it is not a true representation of what was submitted by the original insert. tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enable and disable keys
From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 10:27 AM To: Boyd, Todd M. Cc: mysql Subject: Re: enable and disable keys /tmp has 16GB free space On 6/12/08, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Sorry if this question is stupid, but... okay, your /tmp folder has 16GB available for use. Is this the folder that MySQL has been configured to use? I understand that it should be by default, but it doesn't hurt to examine every facet of a problem with confusing roots. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enable and disable keys
yes, this is the default folder its configured. On 6/12/08, Boyd, Todd M. [EMAIL PROTECTED] wrote: From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 10:27 AM To: Boyd, Todd M. Cc: mysql Subject: Re: enable and disable keys /tmp has 16GB free space On 6/12/08, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Sorry if this question is stupid, but... okay, your /tmp folder has 16GB available for use. Is this the folder that MySQL has been configured to use? I understand that it should be by default, but it doesn't hurt to examine every facet of a problem with confusing roots. Todd Boyd Web Programmer
Re: improve performance on FULLTEXT search.
Hi Sebastian,
I tried to order the column as close as possible to the table structure and
removed all the formatn command and if conditions, but still it take 3 min
select
ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL,
BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG
from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and
CURNT_PRICE_USD between 0 and limit 1000
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Sebastian Mendel [EMAIL PROTECTED] wrote:
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take
close
to 3 min for the same query to execute, any ways to improve the
performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
no not in the same order will this have any performance impact.
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
H:i:s ?
Good catch, mysql gives in the y-m-d H:i:s format
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
I need to check with developer
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
Also need to check with developer, Just guessing, they want for prince
between 0 and 999
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys
|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
Re: Storing IP blocks
For whatever it's worth, some years ago I wrote a database that kept track of IPs. I found it very easy if I treated the IPs as numbers. Each IP is a quartet of digits, each in the range 0-255. This really means an IP is a 4-digit number in base 256 and you can use use standard conversion functions from base 256 to base 10 and back (although these are bigints). So range checking is easy: convert the range limits and the search IP to integers base-10, and do a normal compare. I found dealing with IPs any other way far too cumbersome. Jim On Thu, Jun 12, 2008 at 8:24 AM, Scusting [EMAIL PROTECTED] wrote: Hi, I need to be able to store data from our Radius servers that includes IP addressing and IP blocks into a MySQL field that is easily searchable. Basic IP addreses I have sorted with INET_ATON(), but is there a way to store blocks of IP's as in 192.168.1.0/24 in a way that would allow it to be found in a search for say 192.168.1.100? Perhaps a method to take the block and insert the 1st and last IP's into seperate columns so I can check to see if it falls between these 2 values? The import of Radius into MySQL is performed by doing a 'LOAD DATA LOCAL INFILE' on a CSV file. Ideal would be a solution to insert the 1st and last IP address based on the IP block at the time of doing the LOAD? Thanks. Jim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: unescaping strings with the C api
Tim Johnson wrote: Not sure what you mean by directly usable. I mean directly usable. :) If I do an insert statement with a backslash, for example: headline\one, I will retrieve headline\\one, and that will need to be unescaped, because it is not a true representation of what was submitted by the original insert. My perspective is a little different from yours: as the maintainer of MySQL++ (http://tangentsoft.net/mysql++/), I have never actually used the C API directly. I don't have any pure C sample code here to tweak to try things. Instead, I changed one of the MySQL++ examples to insert a string with a backslash into the DB, and on retrieving the rows, I get a single backslash. In the C++ code, the backslash is doubled due to C/C++ string parsing rules, but that's only one character in the underlying string data. Due to the way this example uses MySQL++, that string gets automatically escaped on DB insertion, so I presume it's sent over the wire as two backslashes, though I haven't verified it. Then when you retrieve rows through MySQL++, it returns a fairly direct copy of the data the C API gives you, with no real translation going on. MySQL++ doesn't have an unescape function, so I don't see why your program would need one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unescaping strings with the C api
On Thursday 12 June 2008, Warren Young wrote: Tim Johnson wrote: Not sure what you mean by directly usable. I mean directly usable. :) If I do an insert statement with a backslash, for example: headline\one, I will retrieve headline\\one, and that will need to be unescaped, because it is not a true representation of what was submitted by the original insert. My perspective is a little different from yours: as the maintainer of MySQL++ (http://tangentsoft.net/mysql++/), I have never actually used the C API directly. I don't have any pure C sample code here to tweak to try things. Me neither - not any more anyway, since I quit coding in C C++ years ago.. Furthermore, I am also working with a scripting language new to me. My experience is with python - where unescaping is _not_ an issue and rebol, where unescaping _is_ an issue. Python using the API (somewhere buried deep in the MySQLdb modules, and rebol using a direct socket connection on port 3306. Instead, I changed one of the MySQL++ examples to insert a string with a backslash into the DB, and on retrieving the rows, I get a single backslash. In the C++ code, the backslash is doubled due to C/C++ string parsing rules, but that's only one character in the underlying string data. Due to the way this example uses MySQL++, that string gets automatically escaped on DB insertion, so I presume it's sent over the wire as two backslashes, though I haven't verified it. Then when you retrieve rows through MySQL++, it returns a fairly direct copy of the data the C API gives you, with no real translation going on. I'm seeing the same that you are with the language (newlisp) that I am playing with. MySQL++ doesn't have an unescape function, so I don't see why your program would need one. I believe that you are correct. If not a single regex should handle it, and be fairly fast. Thanks for the input. I really appreciate it. Best regards tim(looking at MySQL++) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to create/change table to federated within MySQL Admin
Hi, I want to change an existing table to StorageType federated according to http://dev.mysql.com/doc/refman/5.1/en/federated-create.html I've just created this sample table, now I can't even have a look at the scheme with mySQL Administrator, only the mySQL Query Browser works and shows me the table. What can I do to get mySQLAdministrator up and running again (Could not get schema table status, ...) displaying the affected schema? How can I change an existing Table (ALTER TABLE ... ???) On my development system, I have mySQL-Server 5.1.23rc, the production system is a mySQL 5.0.37. Or, can I link within one mySQL server from one schema-table to another table. I need two tables with the same content... TIA Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unescaping strings with the C api
On 6/12/08, Tim Johnson [EMAIL PROTECTED] wrote: On Thursday 12 June 2008, Warren Young wrote: Tim Johnson wrote: Not sure what you mean by directly usable. I mean directly usable. :) If I do an insert statement with a backslash, for example: headline\one, I will retrieve headline\\one, and that will need to be unescaped, because it is not a true representation of what was submitted by the original insert. My perspective is a little different from yours: as the maintainer of MySQL++ (http://tangentsoft.net/mysql++/), I have never actually used the C API directly. I don't have any pure C sample code here to tweak to try things. Me neither - not any more anyway, since I quit coding in C C++ years ago.. Furthermore, I am also working with a scripting language new to me. My experience is with python - where unescaping is _not_ an issue and rebol, where unescaping _is_ an issue. Python using the API (somewhere buried deep in the MySQLdb modules, and rebol using a direct socket connection on port 3306. Instead, I changed one of the MySQL++ examples to insert a string with a backslash into the DB, and on retrieving the rows, I get a single backslash. In the C++ code, the backslash is doubled due to C/C++ string parsing rules, but that's only one character in the underlying string data. Due to the way this example uses MySQL++, that string gets automatically escaped on DB insertion, so I presume it's sent over the wire as two backslashes, though I haven't verified it. Then when you retrieve rows through MySQL++, it returns a fairly direct copy of the data the C API gives you, with no real translation going on. I'm seeing the same that you are with the language (newlisp) that I am playing with. MySQL++ doesn't have an unescape function, so I don't see why your program would need one. I believe that you are correct. If not a single regex should handle it, and be fairly fast. Thanks for the input. I really appreciate it. Best regards tim(looking at MySQL++) The same is true of mysql_real_escape_string() in PHP. You escape special characters upon update or insertion, but upon viewing the data in the DB or retrieving it programmatically, the data simply appear as it was originally before use of the function. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
I am curious about that, as well. It brings to mind a discussion that happened on this list last week (I believe) about case sensitive/insensitive use of LIKE. I believe the synopsis was that tables are either created as case-insensitive, or the search needs to be specified as case sensitive (with BINARY). Could this be a similar issue, perhaps? One table is specifically case-insensitive with regard to the function, and the other is not? In this particular case, we're not dealing with any tables. I have also tried adding BINARY to the mix as well, and it didn't make any difference. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
At 11:38 AM 6/12/2008, you wrote:
Hi Sebastian,
I tried to order the column as close as possible to the table structure and
removed all the formatn command and if conditions, but still it take 3 min
Are you sure when you are running the fulltext search, the table isn't
locked because you are building the index or altering the table?
Mike
select
ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL,
BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG
from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and
CURNT_PRICE_USD between 0 and limit 1000
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Sebastian Mendel [EMAIL PROTECTED] wrote:
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take
close
to 3 min for the same query to execute, any ways to improve the
performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
no not in the same order will this have any performance impact.
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
H:i:s ?
Good catch, mysql gives in the y-m-d H:i:s format
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
I need to check with developer
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
Also need to check with developer, Just guessing, they want for prince
between 0 and 999
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys
|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unescaping strings with the C api
On Thursday 12 June 2008, David Giragosian wrote: The same is true of mysql_real_escape_string() in PHP. You escape special characters upon update or insertion, but upon viewing the data in the DB or retrieving it programmatically, the data simply appear as it was originally before use of the function. Yup. The API takes care of it all . definitely a case for using the API over TCP/IP tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
no there is no index building or alter doing on.
On 6/13/08, mos [EMAIL PROTECTED] wrote:
At 11:38 AM 6/12/2008, you wrote:
Hi Sebastian,
I tried to order the column as close as possible to the table structure
and
removed all the formatn command and if conditions, but still it take 3 min
Are you sure when you are running the fulltext search, the table isn't
locked because you are building the index or altering the table?
Mike
select
ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL,
BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG
from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and
CURNT_PRICE_USD between 0 and limit 1000
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Ananda Kumar [EMAIL PROTECTED] wrote:
On 6/12/08, Sebastian Mendel [EMAIL PROTECTED] wrote:
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but
when
anyother jobs like Index creation or data load is happening its take
close
to 3 min for the same query to execute, any ways to improve the
performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql explain select
-
-
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
-
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
-
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
no not in the same order will this have any performance impact.
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
' %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
H:i:s ?
Good catch, mysql gives in the y-m-d H:i:s format
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
- AGAINST('BOOKS') 0 and
why 0 ?
I need to check with developer
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
Also need to check with developer, Just guessing, they want for prince
between 0 and 999
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys
|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext index -first query slow, subsequent queries fast
Hi List
I have a table with a fulltext index across five fields, with about 2.2
million records and a data size of about 5.6 GB (index another 3.5 GB).
When I test a query that uses fulltext matching, the first run takes
about 15-16 seconds to complete. The second run takes about 0.1 sec and
subsequent runs will all all ge around the 0.1 sec time.
The query that I use has a call to NOW() as one of the criteria, so i
know that the query results will not be cached. Yet, the times would
suggest that some sort of caching effect is being observed.
Is there something I can do that can return more consistent query
performance - hopefully with a time somewhere between the two extremes?
The structure of the table as well as the query are below.
All help will be appreciated.
CREATE TABLE `article` (
`article_id` int(11) NOT NULL auto_increment,
`site_id` int(11) NOT NULL default '0',
`article_code` varchar(80) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`publication_id` int(11) default '0',
`rating_id` int(11) NOT NULL default '0',
`status_id` int(11) NOT NULL default '0',
`section_id` int(11) NOT NULL default '-1',
`template_id` int(11) default NULL,
`headline1` varchar(255) default NULL,
`headline2` varchar(255) default NULL,
`headline3` varchar(255) default NULL,
`live` enum('Y','N') NOT NULL default 'N',
`modified_date` datetime NOT NULL default '-00-00 00:00:00',
`original_date` datetime NOT NULL default '-00-00 00:00:00',
`flash` enum('Y','N') default NULL,
`expiry_date` datetime default NULL,
`embargo_date` datetime default NULL,
`embargo_hour` int(11) default NULL,
`embargo_day` int(11) default NULL,
`message` varchar(255) default NULL,
`section_front` enum('Y','N') NOT NULL default 'N',
`front_page` enum('Y','N') NOT NULL default 'N',
`author_id` int(11) default '0',
`is_urgent` enum('Y','N') NOT NULL default 'N',
`live_date` datetime default NULL,
`page_number` int(11) NOT NULL default '0',
`is_free` enum('Y','N') NOT NULL default 'N',
`source_id` int(11) default NULL,
`edition` int(11) default NULL,
`master_article` int(11) default NULL,
`newspapersection_id` int(11) default NULL,
`blurb` text NOT NULL,
`body` text NOT NULL,
`is_indexed` enum('Y','N') NOT NULL default 'N',
`zone` varchar(255) NOT NULL default '',
`warning` varchar(255) NOT NULL default '',
`blurb_is_intro` enum('Y','N') default 'N',
PRIMARY KEY (`article_id`),
KEY `site_id` (`site_id`),
KEY `article_code` (`article_code`),
KEY `name` (`name`),
KEY `publication_id` (`publication_id`),
KEY `rating_id` (`rating_id`),
KEY `status_id` (`status_id`),
KEY `section_id` (`section_id`),
KEY `live` (`live`),
KEY `modified_date` (`modified_date`),
KEY `original_date` (`original_date`),
KEY `expiry_date` (`expiry_date`),
KEY `section_front` (`section_front`),
KEY `front_page` (`front_page`),
KEY `live_date` (`live_date`),
KEY `is_urgent` (`is_urgent`),
KEY `page_number` (`page_number`),
KEY `author_id` (`author_id`),
KEY `embargo_date` (`embargo_date`),
KEY `master_article` (`master_article`),
KEY `newspapersection_id` (`newspapersection_id`),
KEY `site_status` (`site_id`,`status_id`),
KEY `flash` (`flash`),
FULLTEXT KEY `blurb`
(`blurb`,`body`,`headline1`,`headline2`,`headline3`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2586;
SELECT SQL_CALC_FOUND_ROWS article_id AS iArticleId, MATCH(blurb, body,
headline1, headline2, headline3) AGAINST (microsoft) AS dRelevance
FROM article WHERE embargo_date = NOW() AND status_id IN (-1, -6, -10)
AND site_id = 45 AND MATCH(blurb, body, headline1, headline2, headline3)
AGAINST (microsoft) ORDER BY embargo_date DESC LIMIT 0, 25
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL DBA/Windows Server Administrator opportunity-Kansas City
We are currently looking for a contractor for 30 days in Kansas City that has experience doing MySQL DBA/Windows Server Administration work. This will be a production support role as the consultant will be covering for someone that will be out on vacation. The consultant needs to be available 24/7 to address any system issues. Our client needs to move quickly on this. Interested candidates should email their resume to Lori Kurovski at [EMAIL PROTECTED] or call at 913-422-0293. Lori Kurovski Solutions Now, Inc. 8695 College Blvd, Suite 220 Overland Park, KS 66210 913-422-0293 [EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 3:20 PM To: mysql Subject: Fulltext index -first query slow, subsequent queries fast Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. [JS] It may well be the file system that is doing the caching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 4:57 PM To: Jerry Schwartz; mysql Subject: Re: Fulltext index -first query slow, subsequent queries fast Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. [JS] I can't agree or disagree. I used to be a performance consultant, but that was in another life and I don't know anything about MySQL's own caching algorithms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
At 02:20 PM 6/12/2008, you wrote: Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. Is there something I can do that can return more consistent query performance - hopefully with a time somewhere between the two extremes? Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I dump procedures with mysqldump using --tabs?
I usually use the --tabs option on mysqldump. However, if a table has a function, procedure, or trigger, the code is not written to a file but rather to standard out. Is this the only way to dump them using the --tabs option? I thought of trying to use the Information Schema database somehow but that would make it a 2 step process. If redirecting to STDOUT is the only way to do it, are there any plans to change this? -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Function Still Not Working
does it work outside the function?
Yes, If I run:
select CONCAT(UPPER(SUBSTRING('JESSE',1,1)),LOWER(SUBSTRING('JESSE',2)));
replacing cInput with 'JESSE', it returns Jesse as it should.
did you tried SUBSTRING(cInput, 2)?
Tried replacing SUBSTRING(cInput FROM 2) with SUBSTRING(cInput, 2) and it
didn't make any difference.
did you tried with converting?
I have had issues with this in other areas before, but didn't think about it
this time. However, I tred CONVERT with UTF8 and latin1 as you suggested.
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
I converted the function over to use a variable, and got the same results.
Here's the new function:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
Declare str Text;
Declare cReturn Text;
Set @str=BINARY cInput;
Set @cReturn =
CONCAT(UPPER(SUBSTRING(@str,1,1)),LOWER(SUBSTRING(@str,2)));
RETURN @cReturn;
END;
Still doesn't work. This is driving me NUTz 8-p
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
natural sort via substrings
Hi, I am attempting to do a natural sort from within mysql, if possible. So, for example, jane2 would come before jane10, and normal strings would still sort as expected. I found some solutions, like using length for the numerical part, but that only works if the strings are the same length. Ideally, I would like to use substring_index, but stick a regexp in as the delimiter. So far, it seems you cannot do this. Does anyone know how to put a regexp as the delimiter in substring_index? For example, I want to do something like this: .order by substring_index(name, 'regexp [0-9]+', 1); Is this possible? -Emily
Re: enable and disable keys
increase bulk_insert_buffer_size and if possible split the bigger file into small chunks. It will help you. On Thu, Jun 12, 2008 at 1:51 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, We are doing load data into a table using LOAD DATA INFILE process. Below is the method we are following. 1. create empty table with all the indexes. 2. disable keys 3. Load data using LOAD DATA INFILE, close to 99 Million records which takes around 3 hrs 4 . Enable keys Table size is around 19.5 gb There is one primary key, 2 non unique indexes and one FULLTEXT INDEX. Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. please let me know, this problem is causing lot of dealy for other process to run. regards anandkl -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
