MySQL is broken
Okay, now that I have your attention. :-) What I actually mean is,
Spatial Extensions in MySQL 5.0.16 (the version I'm on) seem so slow
as to appear broken. Why:
I'm trying to speed up a query that fetches the nearest 10 records
(essentially, latitude/longitude pairs, stored in a Geometry point
column) to a given point (latitude/longitude). Here's the query I'm
using right now, which works but is slow:
SELECT latitude, longitude, GLength(LineStringFromWKB(LineString
(AsBinary(coordinates), AsBinary(GeomFromText('POINT(51 -114)')
AS distance FROM places ORDER BY distance ASC LIMIT 10
latitudelongitude distance
51.00137160 -114.00182421 0.0022823296615694
50.99412759 -114.00182513 0.0061494958106356
51.00859980 -114.00181734 0.0087897260887692
...
It takes about 2.3 seconds to execute on a MacBook Pro. Now, the
table is big--over 800,000 rows. And the above query is a one-second
improvement over this original one:
SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - 51) , 2 )
+ POW( 69.1 * (-114 - longitude) * COS( latitude / 57.3 ) , 2 ) ) AS
distance FROM places ORDER BY distance ASC LIMIT 0,10
But I expected much better. I created a spatial index on the
coordinates column, but it is not being used (I did EXPLAIN). This is
not surprising, since there's a calculation that needs to be
performed on every single row. But is there a faster way to fetch the
closest records to a given point? The MySQL docs are incredibly terse
and I can't find any other examples or code to copy.
Any help is much appreciated.
...Rene
PS: For clarity, here is the table structure:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `latlng` (`coordinates`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=845891 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Removing space characters ... char(160)? ... char(194)?
Hi all.
I'm trying to weed out garbage that comes from copying and pasting stuff
from a web page.
Some of the data has spaces, but a *different* kind of space ... a
char(160) kind ... I think ... I figured this out by copying the space
character and pasting it into mysql thus:
select ascii(' ');
... where the space was pasted in.
So I'm using:
update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set Service_Number =
replace( Service_Number, char(160), '' );
... but this returns:
Query OK, 0 rows affected (0.00 sec)
Rows matched: 313 Changed: 0 Warnings: 0
So it's not finding char(160) in Service_Number. If I try another way to
get at the space character, I get a different result:
select ascii( right( Service_Number, 1 ) ) from
tmp_AAPT_OnlineAnalyser_ChargeTypeSummary;
... gives me a big set of results, all 194 ( ie char(194) ). But when I
compare both the characters:
select char(160), char(194);
... I get:
+---+---+
| char(160) | char(194) |
+---+---+
| A0 | C2 |
+---+---+
... and both the A0 and C2 results are in reverse video. The A0
*looks* like the stuff I'm getting at the end of fields when I just do a
select from the table in the MySQL command-line client, eg the 1st
record has Service_Number:
0298437600A0
( A0 is reversed ).
Lastly, maybe I shouldn't add this, but when I construct the space
character from a Perl app running under Windows 2000:
my $space_character = chr(160);
When I do: perl -e print chr(160);
I get: á
This is also with Win2K and ActiveState.
I've been following several threads on character sets and collation as
well. I have a database that contains accented data (Canadian French)
that doesn't render correctly in a browser window. I'm going to try
converting it and the tables to utf8 Unicode. Then make sure the
character set for the HTML is also utf8.
and then insert it into the SQL:
my $sql = update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set
Service_Number = replace( Service_Number, ' . $space_character . ', '' );
it works! But the *exact* same Perl code running on a Linux client fails
( doesn't update the field anyway ). It defies logic.
Who knows what's going on?
--
Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
We make web sites work!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways replication
Here are some links for you... http://www.howtoforge.com/mysql_master_master_replication http://sequoia.continuent.org/ http://www.howtoforge.com/loadbalanced_mysql_cluster_debian_p5 --- Rilawich Ango [EMAIL PROTECTED] wrote: Hi all, I know it is an old question and I have read from the mysql website about the topic. Until now, mysql still doesn't support 2 ways replication, quoted from mysql website. As I have multiple location and each location will have a DB. Most of all need to read and write to the database. In my case, 2 ways replication is the most direct way to do it. Any other solution is suitable for me to implement if 2 ways replication is not a good way? Anyone has successfully implement 2 ways replication? Any suggestion? 1PC-read/update-DB(a) --- 2 ways replication -- DB(b)-read/update-PC2 ango -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update failing with error 1062
Hi Michael Here is the table schema : DROP TABLE IF EXISTS `clients`.`calendarentry`; CREATE TABLE `clients`.`calendarentry` ( `idCalendarEntry` int(10) unsigned NOT NULL auto_increment, `Sujet` varchar(80) NOT NULL, `Debut` datetime NOT NULL, `Fin` datetime NOT NULL, `Notes` varchar(2048) default NULL, `Location` varchar(1023) default NULL, `ContactName` varchar(110) default NULL, `Structure` varchar(80) default NULL, `Telephone` varchar(30) default NULL, `ClientId` int(10) unsigned default NULL, `AllDayEvent` tinyint(4) NOT NULL, `IsMeeting` tinyint(4) NOT NULL, `HasReminder` tinyint(4) NOT NULL, `NextReminder` datetime default NULL, `ReminderMinutesBeforeStart` int(11) default '0', `ReminderIsMinutes` tinyint(4) default '0', `CEOid` int(10) unsigned default '0', `Repeats` tinyint(4) default '0', `RepeatPatternId` int(10) unsigned default '0', PRIMARY KEY (`idCalendarEntry`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; As you can see, there are no other unique keys here. In addition, there are no foreign keys which point here either. Any ideas? Simon Michael Dykman wrote: Simon, send in the schema for the table in question, that should show something. The only condition I can think of off the top of my head which might do that is if you have another unique key in your structure and that is the one this error is complaining about. On 2/12/07, Simon Giddings [EMAIL PROTECTED] wrote: Good morning, I issue an update statement containing a where clause on the primary index, so as to update a single record. Howerver this is failing with Duplicate entry '6' for key 1 - update clients.calendarentry set Subject = 'presentation' where idCalendarEntry = 6; In the table, the field 'idCalendarEntry' is declared as : `idCalendarEntry` int(10) unsigned NOT NULL auto_increment The server version of MySql I am using is 5.0.24 The client version of MySql I am using is 5.0.11 Is anyone able to help? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Call for Submissions: ICIMP 2007 ICDT 2007 ICGDBC 2007, Silicon Valley, July 1-6, 2007
Call for Submissions: ICIMP 2007 ICDT 2007 ICGDBC 2007, Silicon Valley, July 1-6, 2007 SUBMISSION DEADLINE: FEBRUARY 20 Invitation: Please consider contributing to the conferences ICIMP 2007 , ICDT 2007 and ICGDBC 2007 as well as to the associated workshops listed below. Date: July 1-6, 2007 - Silicon Valley, USA Please forward the Call for Submissions to the appropriate group. ^ === CALL FOR PAPERS , TUTORIALS, PANELS ICDT 2007, The Second International Conference on Digital Telecommunications http://www.iaria.org/conferences2007/ICDT07.html ICIMP 2007, The Second International Conference on Internet Monitoring and Protection http://www.iaria.org/conferences2007/ICIMP07.html ICGDBC 2007, The First International Conference on Global Defense and Business Continuity http://www.iaria.org/conferences2007/ICGDBC07.html __ Place: Silicon Valley, California Important deadlines: Full paper submissionFebruary 20, 2007 Author notification March 21, 2007 Registration and Camera ready March 31, 2007 __ Featuring the workshops: ICIMP 2007 Workshops: oSYVUL 2007: The First International Workshop on Systems Vulnerabilities oSYDIA 2007: The First International Workshop on Systems Diagnosis oCYBER-FRAUD 2007: The First International Workshop on Cyber-Fraud ICDT 2007 Workshops: oSARP 2007: The First International Workshop on Software Architecture Research and Practice oSTREAM 2007: The First International Workshop on Data Stream Processing ICGDBC 2007 Workshop: oTRACK 2007: The First International Workshop on Tracking Computing Technologies __ Conference Topics: ICIMP 2007 Tracks: • TRASI: Internet traffic surveillance and interception • IPERF: Internet Performance • RTSEC: Security for Internet-based real-time systems • DISAS: Disaster prevention and recovery • EMERG: Networks and applications emergency services • MONIT: End-to-end sampling, measurement, and monitoring • REPORT: Experiences lessons learnt in securing networks and applications • USSAF: User safety, privacy, and protection over Interne ICDT 2007 Tracks: • MULTE: Multimedia Telecommunications • SIGNAL: Signal processing in telecommunications • DATA: Data processing • AUDIO: Audio transmission and reception systems • VOICE: Voice over packet networks • VIDEO: Video, conferencing, telephony • IMAGE: Image producing, sending, and mining • SPEECH: Speech producing and processing • IPTV: IP/Mobile TV • MULTI: Multicast/Broadcast Triple-Quadruple-play • CONTENT: Production, distribution • HXSIP: H-series towards SIP • MEDMAN: Control and management of multimedia telecommunication ICGDBC 2007 Tracks: • BUSINESS: Business continuity • RISK: Risk assessment • DISASTER: Emergency services and disaster recovery • TRUST: Privacy and trust in pervasive communications • RIGHT: Digital rights management • BIOTEC: Biometric techniques -- View this message in context: http://www.nabble.com/Call-for-Submissions%3A-ICIMP-2007---ICDT-2007---ICGD-BC-2007%2C-Silicon-Valley%2C-July-1-6%2C-2007-tf3220013.html#a8942543 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways replication
Dear all, We can view interesting tipic into : http://www.onlamp.com/lpt/a/6549 Regards, Juan Eduardo On 2/12/07, Atle Veka [EMAIL PROTECTED] wrote: 2 way replication, also referred to as dual master replication, has been available for quite some time. However implementation can be tricky. Look for the Dual master section in chapter 7 of the High Performance MySQL book: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html If you need more than 2 masters, then all bets are off.. Atle On Mon, 12 Feb 2007, Rilawich Ango wrote: Hi all, I know it is an old question and I have read from the mysql website about the topic. Until now, mysql still doesn't support 2 ways replication, quoted from mysql website. As I have multiple location and each location will have a DB. Most of all need to read and write to the database. In my case, 2 ways replication is the most direct way to do it. Any other solution is suitable for me to implement if 2 ways replication is not a good way? Anyone has successfully implement 2 ways replication? Any suggestion? 1PC-read/update-DB(a) --- 2 ways replication -- DB(b)-read/update-PC2 ango -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
At the moment we are using mysql 3.23.58. It would appear I can't use UNION in this version ? Do I have any other options, without upgrading the database server version ? Regards, Neil From: Nils Jünemann [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Query Two Databases Date: Thu, 25 Jan 2007 12:01:48 +0100 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Neil Tompkins schrieb: Not sure if this is possible or not. But I've two identical tables in two different databases. Is it possible to retrieve data from the different tables in one query ? (SELECT * FROM db1.table) UNION (SELECT * FROM db2.table) If db2 on a other mysql server, it is possible to use the federated storage engine of MySQL. - -- Nils Jünemann Database and System Administration studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715 www.studiVZ.net | fax +49-(0)30-28093887 | cell +49-(0)175-9331740 www.estudiLN.es | www.studentIX.pl | www.studiQG.fr | www.studiLN.it -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz hJRxd5b07AxIlFP8/RBKQx0= =G/4h -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Hotmail is evolving check out the new Windows Live Mail http://ideas.live.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
Following on from the email below, if I run the query SELECT * FROM database1.table, database2.table I get the data back, but all the data is in the same row. How can I seperate the records ? Regards Neil ~~ At the moment we are using mysql 3.23.58. It would appear I can't use UNION in this version ? Do I have any other options, without upgrading the database server version ? Regards, Neil From: Nils Jünemann [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Query Two Databases Date: Thu, 25 Jan 2007 12:01:48 +0100 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Neil Tompkins schrieb: Not sure if this is possible or not. But I've two identical tables in two different databases. Is it possible to retrieve data from the different tables in one query ? (SELECT * FROM db1.table) UNION (SELECT * FROM db2.table) If db2 on a other mysql server, it is possible to use the federated storage engine of MySQL. - -- Nils Jünemann Database and System Administration studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715 www.studiVZ.net | fax +49-(0)30-28093887 | cell +49-(0)175-9331740 www.estudiLN.es | www.studentIX.pl | www.studiQG.fr | www.studiLN.it -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz hJRxd5b07AxIlFP8/RBKQx0= =G/4h -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Hotmail is evolving check out the new Windows Live Mail http://ideas.live.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query help for Select
Hi All, I need help in this procedure, i am limiting the result to single row by using 'limit' in select statement. But i am using a variable here, mysql is throwing error as i am using variable, please give some suggestions. create procedure Report_login_Activity2() begin declare count,i int DEFAULT 0; set i = 0; select count(*) into count from playersloginfo; while(i = count) do select user_name, concat(logindate, ' ', logintime), concat(logoutdate, ' ', logouttime), TIMEDIFF(concat(logoutdate,' ', logouttime), concat(logindate,' ', logintime)) from playersloginfo *limit i,1;* end while; end;
Re: Query Two Databases
Neil Tompkins wrote: Following on from the email below, if I run the query SELECT * FROM database1.table, database2.table I get the data back, but all the data is in the same row. How can I seperate the records ? Regards Neil Barring an upgrade, it seems your best bet would be to use a scripting solution, such as PHP or perl, that could accomplish the same thing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: search issue]
hi to all!
I'm trying to get some products from products table using fulltext search
but something doesn't work correctly - and can't find what.
this is table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9119 ;
table products has 5168 records.
when search for products that hav 'cap' in product name, using smple LIKE
function:
select prod_id, prod_no, prod_name, prod_status
from products
where prod_name like '%cap%'
I get 79 rows.
when try:
select prod_id, prod_name, prod_no
from products
where MATCH (prod_name) AGAINST ('+cap' in boolean mode)
I don't get any record as result.
What I'm doing wrong?
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: search issue]
hi afan,
depending on your mysql conf you have a minimum word length. check
your config for:
ft_min_word_len
and change it to 3.
hth: lars
On 2/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
hi to all!
I'm trying to get some products from products table using fulltext search
but something doesn't work correctly - and can't find what.
this is table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9119 ;
table products has 5168 records.
when search for products that hav 'cap' in product name, using smple LIKE
function:
select prod_id, prod_no, prod_name, prod_status
from products
where prod_name like '%cap%'
I get 79 rows.
when try:
select prod_id, prod_name, prod_no
from products
where MATCH (prod_name) AGAINST ('+cap' in boolean mode)
I don't get any record as result.
What I'm doing wrong?
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Removing space characters ... char(160)? ... char(194)?
The character set used by Windows is not the same as UTF-8. That causes
problems when you feed Windows text into an interface that is expecting
UTF-8. I know it drives me crazy.
If you pull up a web page that is in French, and check the page encoding in
your browser, you can try changing it from UTF-8 to Windows or vice versa.
You should see that the accented characters change, so you'll have an
example in front of you.
The browser will typically render the page according to the character set
specified in the HTML header (I think), or it makes a best guess, or it uses
its default. Although this only affects the rendering of the page, so far as
the browser is concerned, it does affect copy and paste. If you copy from a
page that is rendered in the Windows character set, and paste it into an
interface (even another browser window) that is UTF-8, then you'll get
unexpected (garbage) characters.
The same thing applies with editors. Although even Notepad allows saving a
file as UTF-8, I don't know what that accomplishes because it doesn't
actually do any character translation.
To make matters worse, a console window uses (by default) yet another
character set (ANSI).
In any case, what I have been doing with my applications is to translate the
incoming text from Windows to UTF-8. First, though, I check to see if the
text is already UTF-8 by doing a dummy translation from UTF-8 to UTF-8; if
the results are unchanged, then I know that particular text was already
UTF-8 and that it shouldn't be remapped.
You will also run into this problem if you copy and paste from a PDF, I
suspect.
This whole thing gives me a headache. I hope someone else who really
understands this stuff will respond, so we can both learn.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Amer Neely [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 13, 2007 3:53 AM
To: mysql@lists.mysql.com
Subject: Re: Removing space characters ... char(160)? ... char(194)?
Hi all.
I'm trying to weed out garbage that comes from copying and
pasting stuff
from a web page.
Some of the data has spaces, but a *different* kind of space ... a
char(160) kind ... I think ... I figured this out by
copying the space
character and pasting it into mysql thus:
select ascii(' ');
... where the space was pasted in.
So I'm using:
update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set
Service_Number =
replace( Service_Number, char(160), '' );
... but this returns:
Query OK, 0 rows affected (0.00 sec)
Rows matched: 313 Changed: 0 Warnings: 0
So it's not finding char(160) in Service_Number. If I try
another way to
get at the space character, I get a different result:
select ascii( right( Service_Number, 1 ) ) from
tmp_AAPT_OnlineAnalyser_ChargeTypeSummary;
... gives me a big set of results, all 194 ( ie char(194)
). But when I
compare both the characters:
select char(160), char(194);
... I get:
+---+---+
| char(160) | char(194) |
+---+---+
| A0 | C2 |
+---+---+
... and both the A0 and C2 results are in reverse
video. The A0
*looks* like the stuff I'm getting at the end of fields
when I just do a
select from the table in the MySQL command-line client, eg the 1st
record has Service_Number:
0298437600A0
( A0 is reversed ).
Lastly, maybe I shouldn't add this, but when I construct the space
character from a Perl app running under Windows 2000:
my $space_character = chr(160);
When I do: perl -e print chr(160);
I get: á
This is also with Win2K and ActiveState.
I've been following several threads on character sets and
collation as
well. I have a database that contains accented data (Canadian French)
that doesn't render correctly in a browser window. I'm going to try
converting it and the tables to utf8 Unicode. Then make sure the
character set for the HTML is also utf8.
and then insert it into the SQL:
my $sql = update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set
Service_Number = replace( Service_Number, ' .
$space_character . ', '' );
it works! But the *exact* same Perl code running on a Linux
client fails
( doesn't update the field anyway ). It defies logic.
Who knows what's going on?
--
Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
We make web sites work!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Borland C++ Builder 2006 DLL Woes
We just purchased The Borland Developer Studio 2006 IDE and are having significant problems using dbExpress objects to communicate with MySQL servers (both 4 and 5). Curiously, we can perform inserts but not selects, even though identical code in C++ Builder 6 worked just fine. The DLL in C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues? Thanks, David David P. Giragosian, Psy.D. Database and Software Developer MD Anderson Cancer Center Houston, TX 713-792-7898
Re: NOT EMPTY, like NOT NULL
Hello Nils and Jerry, Thanks you for your quick reply! IMO, using trigger looks cleaner and prettier than Excel-like if(char_length) hack so I will probably give triggers a try. Searching on the net, I found some articles on check constraints in MySQL. http://gilfster.blogspot.com/2005/11/check-constraints-in-mysql-50.html http://db4free.blogspot.com/2006/01/emulating-check-constraints.html http://db4free.blogspot.com/2006/01/emulating-check-constraints-with-views.html regards. On 2/13/07, Nils Meyer [EMAIL PROTECTED] wrote: Hi Js, js wrote: Is there any easy way to implement 'NOT EMPTY' constraint? There currently is no support for CHECK Constraints in MySQL, at least to my knowing. So you'd have to go with a trigger. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Fwd: search issue]
Don't forget that LIKE %cap% will find captain, recapture, and
anything else that has the substring cap in it. Your Boolean match against
+cap will only find the word cap. Make sure that's what you want.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 13, 2007 9:43 AM
To: mysql@lists.mysql.com
Subject: [Fwd: search issue]
hi to all!
I'm trying to get some products from products table using
fulltext search
but something doesn't work correctly - and can't find what.
this is table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9119 ;
table products has 5168 records.
when search for products that hav 'cap' in product name,
using smple LIKE
function:
select prod_id, prod_no, prod_name, prod_status
from products
where prod_name like '%cap%'
I get 79 rows.
when try:
select prod_id, prod_name, prod_no
from products
where MATCH (prod_name) AGAINST ('+cap' in boolean mode)
I don't get any record as result.
What I'm doing wrong?
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Fwd: search issue]
Right. And +cap will not find caps - whats downside too.
Though, my marketing director (who is in charge for this) will rather
acept that then ANY word that contains cap, as you mentioned.
:)
But, Lars was right, the problem was in ft_min_word_len. It's by default 4
and I have to change to 3.
:)
Thanks.
-afan
Don't forget that LIKE %cap% will find captain, recapture, and
anything else that has the substring cap in it. Your Boolean match
against
+cap will only find the word cap. Make sure that's what you want.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 13, 2007 9:43 AM
To: mysql@lists.mysql.com
Subject: [Fwd: search issue]
hi to all!
I'm trying to get some products from products table using
fulltext search
but something doesn't work correctly - and can't find what.
this is table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9119 ;
table products has 5168 records.
when search for products that hav 'cap' in product name,
using smple LIKE
function:
select prod_id, prod_no, prod_name, prod_status
from products
where prod_name like '%cap%'
I get 79 rows.
when try:
select prod_id, prod_name, prod_no
from products
where MATCH (prod_name) AGAINST ('+cap' in boolean mode)
I don't get any record as result.
What I'm doing wrong?
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump on Freebsd 5 -- out of memory error
The error: /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Platform details: Client: mysqldump Ver 10.9 Distrib 4.1.12, for portbld-freebsd5.3 (i386) # /usr/local/bin/mysqldump -u dba -h seisei -p rt3 --set-variable=max_allowed_packet=256M Attachments Attachments.sql Server: FreeBSD seisei.mydomain.net 5.3-RELEASE-p9 FreeBSD 5.3-RELEASE-p9 mysql-server-4.0.24_1 (from ports) I have kern.maxdsiz=751619277 #750 MB kern.dfldsiz=751619277 kern.maxssiz=134217728 # 128MB in /boot/loader.conf and mysql_enable=YES mysql_limits=YES in /etc/rc.conf and max_allowed_packet = 256M in /etc/my.cnf Currently: 90035 mysql 200 503M 239M kserel 41:04 0.00% 0.00% mysqld So, I can see it hitting the 512M limit, but I had thought that the commands in /boot/loader.conf were supposed to fix that (as per http://dev.mysql.com/doc/refman/4.1/en/freebsd.html) Any suggestions as to where I've missed something would be great. Thanks, Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Graham Dunn wrote: The error: /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Platform details: Client: mysqldump Ver 10.9 Distrib 4.1.12, for portbld-freebsd5.3 (i386) # /usr/local/bin/mysqldump -u dba -h seisei -p rt3 --set-variable=max_allowed_packet=256M Attachments Attachments.sql Try adding the -q option. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication stability w/5.0.27
all, I recently completed upgrading the core database pool for our site from 4.0.18 (32-bit) to 5.0.27 (64-bit) but am now experiencing intermittent replication instability. we replicate ~20M DMLs/day across 18 DB nodes in three datacenters. about once/week I'm getting a 2013 error (error reading packet from server) but only on the two slaves whose master is in a different datacenter (never once among intra-datacenter nodes). this would make me suspicious of the network (at least WAN links/devices) except this never happened once in two years w/4.0.18. when it happens I am able to fix it by doing a slave stop/change master (to last execute)/slave start but I would like to find the root of the problem. is anyone aware of any reported replication stability issues w/5.0.27? are their any my.cnf parameters I can change to minimize the frequency? does this sound like a network issue and if so why did 4.0.18 not fail in this way? it's not critical at this point but it's extremely annoying so any advice would be appreciated...
How to generate efficient backups?
Hi, Yesterday I had a problem in a InnoDB table that I needed to DROP and reimport the table. When I tryed to reimport the table, most of the data was lost. So, I´ve tryed to reimport the data from the backups (created via mysqldump) and these datas were still corrupted. So, it brings to my mind some doubts: 1) Is it common? Is difficult to have to trust in database that can´t gives you a real security about the data ingrity. 2) Is there a way to look for the corrupted tables in the whole database, automaticaly? I´d like to create some monitor where it would send me an alert when a table is corrupted 3) Is there a way to check the backup to be sure it´s OK? I can´t depends on a backup that I´m not sure it´s OK. I´m running MySQL-4.1.7 I´ve had some problems with big MyISAM tables, but in these cases REPAIR and OPTIMIZE table have solved. Yesterday the problem happened in a InnoDB table. Any help would be appreciated, Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL docs: Replication Chapter Revamped
Replication Chapter Revamped We have completely reworked the replication chapter for the 5.1 manual. This targets a number of issues, including a major redesign (to make it easier to read and find topics), some updates to the information and processes, and incorporation of numerous bug fixes. You can see the new chapter here: * http://dev.mysql.com/doc/refman/5.1/en/replication.html The main features: * New layout. We've ripped apart the old chapter format and replaced it with a new one. No information has been lost, although a lot of it has been moved around. * We now have four distinct sections: - Replication Configuration - includes details on setup (including How To notes), options and variables, replication formats, and a new section on common replication tasks. - Replication Solutions - this is designed to feature specific scenarios where replication is used. For example, it contains the scale-out solution that was in the FAQ, along with specific notes and guides on backups, splitting replication, and SSL. - Replication Notes and Tips - this collects together sections that were spread about the old structure, including upgrades, compatibility, known features and issues and the FAQ. - Replication Implementation - the innards of the replication system and how it works. In all cases we've either rewritten or hugely expanded the information, and there are also new illustrations with a consistent look and feel to describe layouts and architecture. The new structure will make it easier to add new functionality, scenarios and background information. For example, one other section that is planned, but not in the current documentation yet, is Replication Topologies. Other planned mprovements, such as the MBR/SBR/RBR decision table and implicit commit tables now have a more suitable home in the Replication Implementation section. Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead. Berlin, Germany (UTC +1:00) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
Perhaps, you can use the CONCAT function, SELECT CONCAT(a.Fields, - , b.Fields) FROM database1 a, database2 b On 13/02/07, Neil Tompkins [EMAIL PROTECTED] wrote: Following on from the email below, if I run the query SELECT * FROM database1.table, database2.table I get the data back, but all the data is in the same row. How can I separate the records ? Regards Neil ~~ At the moment we are using mysql 3.23.58. It would appear I can't use UNION in this version ? Do I have any other options, without upgrading the database server version ? Regards, Neil From: Nils Jünemann [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Query Two Databases Date: Thu, 25 Jan 2007 12:01:48 +0100 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Neil Tompkins schrieb: Not sure if this is possible or not. But I've two identical tables in two different databases. Is it possible to retrieve data from the different tables in one query ? (SELECT * FROM db1.table) UNION (SELECT * FROM db2.table) If db2 on a other mysql server, it is possible to use the federated storage engine of MySQL. - -- Nils Jünemann Database and System Administration studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715 www.studiVZ.net | fax +49-(0)30-28093887 | cell +49-(0)175-9331740 www.estudiLN.es | www.studentIX.pl | www.studiQG.fr | www.studiLN.it -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz hJRxd5b07AxIlFP8/RBKQx0= =G/4h -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Hotmail is evolving – check out the new Windows Live Mail http://ideas.live.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Henrique Dallazuanna Curitiba-Paraná Brasil
ODBC connector 3.51/5.0
Dear developers from MySQL, can i know when the MySQL connector/ODBc 5.0 will be released. I am facing few major problems regarding 3.51.12 on windows as application crash and wrong data display. please reply thanks, CPK -- Keep your Environment clean and green.
Re: MySQL docs: Replication Chapter Revamped
Very cool - looking forward to reading it with the new replication options in 5.1 David Stefan Hinz wrote: Replication Chapter Revamped We have completely reworked the replication chapter for the 5.1 manual. This targets a number of issues, including a major redesign (to make it easier to read and find topics), some updates to the information and processes, and incorporation of numerous bug fixes. You can see the new chapter here: * http://dev.mysql.com/doc/refman/5.1/en/replication.html The main features: * New layout. We've ripped apart the old chapter format and replaced it with a new one. No information has been lost, although a lot of it has been moved around. * We now have four distinct sections: - Replication Configuration - includes details on setup (including How To notes), options and variables, replication formats, and a new section on common replication tasks. - Replication Solutions - this is designed to feature specific scenarios where replication is used. For example, it contains the scale-out solution that was in the FAQ, along with specific notes and guides on backups, splitting replication, and SSL. - Replication Notes and Tips - this collects together sections that were spread about the old structure, including upgrades, compatibility, known features and issues and the FAQ. - Replication Implementation - the innards of the replication system and how it works. In all cases we've either rewritten or hugely expanded the information, and there are also new illustrations with a consistent look and feel to describe layouts and architecture. The new structure will make it easier to add new functionality, scenarios and background information. For example, one other section that is planned, but not in the current documentation yet, is Replication Topologies. Other planned mprovements, such as the MBR/SBR/RBR decision table and implicit commit tables now have a more suitable home in the Replication Implementation section. Regards, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC connector 3.51/5.0
I HAVE TO TRY IT OUT. BUT THE PROBLEM IS THAT I HAVE MYSQL 5.0.17 AND I THINK 3.51.06 CAN NOT CONNECT TO 5.0.17. BUT I HAVE TO TRY. THANKS AND REGARDS CPK On 2/14/07, Ron Alexander [EMAIL PROTECTED] wrote: CPK, I'm not from MySQL but I had the same issue with MyODBC-3.51.12. The way I resolved the problem was to rollback to MyODBC-3.51.06. It resolved the issue. I hope this helps. Ron -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 1:01 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: ODBC connector 3.51/5.0 Dear developers from MySQL, can i know when the MySQL connector/ODBc 5.0 will be released. I am facing few major problems regarding 3.51.12 on windows as application crash and wrong data display. please reply thanks, CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Re: mysqldump on Freebsd 5 -- out of memory error
Gerald L. Clark wrote: Graham Dunn wrote: The error: /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Platform details: Client: mysqldump Ver 10.9 Distrib 4.1.12, for portbld-freebsd5.3 (i386) # /usr/local/bin/mysqldump -u dba -h seisei -p rt3 --set-variable=max_allowed_packet=256M Attachments Attachments.sql Try adding the -q option. Same problem: # /usr/local/bin/mysqldump -u dba -h seisei -p rt3 -q --set-variable=max_allowed_packet=256M Attachments Attachments.sql Enter password: /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Thanks, Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Graham, /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Did you changed the values of MAXDSIZ in the kernel file? I use this: options MAXDSIZ=(1536UL*1024*1024) for MySLQ can you up to 1,5 Gb of RAM memory. I realy don´t know if it will solve your problem, but, if MySQL is tring to use more RAM memory than the permited, it seems to be the case. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Ronan Lucio wrote: Graham, /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Did you changed the values of MAXDSIZ in the kernel file? I use this: options MAXDSIZ=(1536UL*1024*1024) for MySLQ can you up to 1,5 Gb of RAM memory. I realy don´t know if it will solve your problem, but, if MySQL is tring to use more RAM memory than the permited, it seems to be the case. Ronan I was under the impression that in FreeBSD 5, you didn't need to change the kernel, just the entry in /boot/loader.conf... Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Graham, I was under the impression that in FreeBSD 5, you didn't need to change the kernel, just the entry in /boot/loader.conf... Even if you use kern_securelevel=1? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Ronan Lucio wrote: Graham, I was under the impression that in FreeBSD 5, you didn't need to change the kernel, just the entry in /boot/loader.conf... Even if you use kern_securelevel=1? Ronan I'm running kern.securelevel: -1 Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC connector 3.51/5.0
C K wrote: I HAVE TO TRY IT OUT. BUT THE PROBLEM IS THAT I HAVE MYSQL 5.0.17 AND I THINK 3.51.06 CAN NOT CONNECT TO 5.0.17. BUT I HAVE TO TRY. THANKS AND REGARDS CPK LESS CAPITALS THANKS. If you want to use MyODBC-3.51.x you should use 3.51.12.2. If that doesn't work for you, then you should go back to the previous stable version ( 3.51.06 ) as recommended, but you will have to also go back to MySQL-4.0.x or tell your post-4.0.x server to use the old authentication method. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binlog file changes its name
Hi, I have also found that the master bin log file changing its name from myhostname-bin. to mysql.XXX and to other different names. I am running mysql Ver 11.12 Distrib 3.23.33, for hp-hpux11.00 (hppa2.0w). When I did sar -v 5 I found that inod-sz is on maximum: 14:39:05 text-sz ov proc-sz ov inod-sz ov file-sz ov 14:39:10 N/A N/A 185/2048 0 2248/2248 0 7789/10010 0 Is there any relation between inod size and the changing of the master bin log file? Any help would be appreciated. Regards, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways replication
Thanks for the links. It seems helpful for me. I will read it then. BTW, does anyone implement multi-master replication successfully? I have read a lot of document and they all don't recommend to do it. On 2/13/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Dear all, We can view interesting tipic into : http://www.onlamp.com/lpt/a/6549 Regards, Juan Eduardo On 2/12/07, Atle Veka [EMAIL PROTECTED] wrote: 2 way replication, also referred to as dual master replication, has been available for quite some time. However implementation can be tricky. Look for the Dual master section in chapter 7 of the High Performance MySQL book: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html If you need more than 2 masters, then all bets are off.. Atle On Mon, 12 Feb 2007, Rilawich Ango wrote: Hi all, I know it is an old question and I have read from the mysql website about the topic. Until now, mysql still doesn't support 2 ways replication, quoted from mysql website. As I have multiple location and each location will have a DB. Most of all need to read and write to the database. In my case, 2 ways replication is the most direct way to do it. Any other solution is suitable for me to implement if 2 ways replication is not a good way? Anyone has successfully implement 2 ways replication? Any suggestion? 1PC-read/update-DB(a) --- 2 ways replication -- DB(b)-read/update-PC2 ango -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing a table
Ian Barnes wrote: Im having another problem with a large db. I have a 160Gig drive dedicated to the database partition and I have on database in particular that is taking up the space. My .MYD is 78Gig and my .MYI is 34 gig. Thats fine i have a couple of gig left, but whenever i try and do anything on it, the disk fills up and I have to run a repair table tablename quick to get it going and that takes forever since its quite large. What kind of filesystem is this? I suggest running Linux, and using a sensible filesystem like XFS, possibly Repairing tables take a long time usually (Falcon is meant to fix this)... Do you know why the disk is filling up so fast? What MySQL version is this? And how much *actual* free space do you have? -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL spatial is glacial.....ly slow
Spatial Extensions in MySQL 5.0.16 (the version I'm on) seem really
slow. I must be missing something, but can't figure out what. Some
background:
I'm trying to speed up a query that fetches the nearest 10 records
(essentially, latitude/longitude pairs, stored in a Geometry point
column) to a given point (latitude/longitude). Here's the query I'm
using right now, which works but is slow:
SELECT latitude, longitude, GLength(LineStringFromWKB(LineString
(AsBinary(coordinates), AsBinary(GeomFromText('POINT(51 -114)')
AS distance FROM places ORDER BY distance ASC LIMIT 10
latitudelongitude distance
51.00137160 -114.00182421 0.0022823296615694
50.99412759 -114.00182513 0.0061494958106356
51.00859980 -114.00181734 0.0087897260887692
...
It takes about 2.3 seconds to execute on a MacBook Pro. Now, the
table is big--over 800,000 rows. And the above query is a one-second
improvement over this original one:
SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - 51) , 2 )
+ POW( 69.1 * (-114 - longitude) * COS( latitude / 57.3 ) , 2 ) ) AS
distance FROM places ORDER BY distance ASC LIMIT 0,10
But I expected much better. I created a spatial index on the
coordinates column, but it is not being used (I did EXPLAIN). This is
not surprising, since there's a calculation that needs to be
performed on every single row. But is there a faster way to fetch the
closest records to a given point? The MySQL docs are incredibly terse
and I can't find any other examples or code to copy.
Any help is much appreciated.
...Rene
PS: For clarity, here is the table structure:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `latlng` (`coordinates`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=845891 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master - Slave/Master - Slave problem.
I'm working with two distinct databases on four different boxes. Databases A on Server A needs to be present everywhere (Server B, C and D). Database B needs to be present on Server C. So I setup replication from Server A to Server B and Server D and then I setup replication from Server B to Server C (to include the tables that were replicated from Server A). So when a change is made in Database A I see it roll over to B and C. But nothing replicated to B ever gets re-replicated to D. It would be optimal if I could slave from two distinct masters but from reading this doesn't seem possible. Server A: log-bin=/datastore/mysql-log/repl binlog-do-db=clients server-id=1 Server B: log-bin=/datastore/mysql-log/repl binlog-do-db=clients binlog-do-db=datastore server-id=21 master-host=testdba.local master-user=testdbareader master-password=passworda master-connect-retry=60 replicate-do-db=clients Server C: server-id=22 master-host=testdba.local master-user=testdbareader master-password=passworda master-connect-retry=60 replicate-do-db=clients Server D: server-id=24 master-host=testdbb.local master-user=testdbbreader master-password=passwordb master-connect-retry=60 replicate-do-db=clients replicate-do-db=data what can I do to fix this? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_upgrade shows errors
Yves Goergen wrote: Hello, I noticed that the current MySQL 5.0 release is not available as binary, so I downloaded the source and compiled it on my testing machine. Compilation went fine and I can connect to the new MySQL server version. But then I tried to run the mysql_upgrade script to fix possible issues and here's what it gave me: The MySQL 5.0 release is available as a binary and is currently at 5.0.27. Grab it at: http://dev.mysql.com/downloads/mysql/5.0.html#downloads ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' @hadGrantPriv:=1 1 1 ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type' ERROR 1061 (42000) at line 66: Duplicate key name 'Grantor' ERROR 1054 (42S22) at line 102: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 124: Duplicate column name 'type' @hadShowDbPriv:=1 1 1 (and some more similar stuff) I tried it twice. At the first time, all other tables had an OK besides them, at the second time, those lines didn't show up anymore. MySQL server is version 5.0.33, OS is Debian Linux 3.1, previous MySQL version was 5.0.17, installed from the binary release. Whats wrong with using the version via apt-get? Debian has very sensible packaging, and its currently at version 5.0.32 afaik What do the above error messages mean? The upgrade script picked up on the fact that you had duplicate column names -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up multiple daemons
Devi wrote: Hi MySQLeers, How can I setup multiple daemons, One daemon for one database? So that they can act independenly. What might be the pitfalls over here? In what situation one can opt for multiple daemons? What about maximum_connections. Is it for all the server instances? You might consider reading: http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html There is also documentation if you wanted to do this on Windows -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
