Re: geometry with MySQL-5.0
Hello Gilles, RE: I am discovering the Spatial Extensions features of MySQL-5.0 Is there a way to use a spherical geometry ? (instead of Euclidean) like for navigation, or on sky coordinates,... As far as MySQL-5.0, I have not found any such feature. I believe it is available in the recent PostGres, but have not tried it. In any case, so far we have quite disappointing experience with MySQL-5.0 and geometry indexing. First, the point() geometry sub-type (which is basically two doubles glued together) is not fixed length, thus the table in which you have a geometry column will be 'dynamic', and indexing and searching for other columns becomes very slow. You can get around this by separate table with geometry info, and join on tables. Second, for big astronomical catalogues (10 million or more records), we found that simple ascii/binary implementation on the disk in a well arranged structure leads to much faster results. For reference: given a catalogue of 38million records. geometry info is in a separate table, and it is indexed. join is done on a primary key we select data in a 10x10deg area on dual opteron 2.0GHZ computer this is 5 to 20 seconds. With an ascii catalogue manipulator this is at most 5sec, but usually 1-2 seconds. Third: indexing on geometry is very slow. With *_buffer = 1Gb size (sort buffer = 256Mb) 100 millions record indexing will take up to a week or more. And finally, you can expect weird messages from mysqladmin. E.g. mysqladmin --sort-index on the geometry table works for some tables, and returns with unknown error for others (usu. when the num of records is more than 10 million ?) See a previous posting of mine. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sort-index on geometry always fails
Hi, John, RE: Seems like myisamcheck --sort-index does not work with spatial indexing? or you have a disk error. I tried it on another computer, and -- unfortunately -- the same effect. Also tried it on the same computer, and different partition of the RAID-5 array. I kind of believe there is some exception here. Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: geometry with MySQL-5.0
Hi, RE: And finally, you can expect weird messages from mysqladmin. E.g. mysqladmin --sort-index on the geometry table works for some I meant to say myisamchk, and not mysqladmin. Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sort-index on geometry always fails
Hi, I have a table that stores geometry information in one column in point type. After freshly uploading the table, myisamcheck --sort-index reports an error: - Sorting index for MyISAM-table 'TEST_I14_GEOM' myisamchk: Unknown error 126 myisamchk: error: Can't read key block from filepos: 71995659058108416 MyISAM-table 'TEST_I14_GEOM' is not fixed because of errors === OK, so I go ahead, and fix it: myisamchk --analyze Checking MyISAM file: TEST_I14_GEOM Data records: 37852738 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check record links MyISAM-table 'TEST_I14_GEOM' is usable but should be fixed myisamchk --verbose --recover - recovering (with keycache) MyISAM-table 'TEST_I14_GEOM' Data records: 37852738 The recovery exists with 0, and is seemingly successful. Then I go back to myisamcheck --sort-index, and I get the same error; myisamchk: Unknown error 126 myisamchk: error: Can't read key block from filepos: 71995659058108416 MyISAM-table 'TEST_I14_GEOM' is not fixed because of errors Seems like myisamcheck --sort-index does not work with spatial indexing? All this is under FC3 on x86_64 AMD opteron, MySQL 5.0.22. Any ideas? Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fixed or dynamic row sizes with point
Hi, RE: we have a table with many (~0.5 billion) records and a geometry field which was defined as a simple point. The `show table status` shows that the row format is dynamic, however, a simple point in the GIS representation has a fixed format (see: WKB: 21 bytes: 1 for MSB/LSB, 4 for type and 2x8 for the two doubles). We experienced that in this case, when we include this point field all `select`ions and indexing (alter table ... add [spatial] index ...) are much slower rather than if we exclude this point object. I made a couple of new tests related to this. To give further examples, if the table contains a point-type column (MMpoint) at the end, selection based on another column (e.g. MMI) is about 2 to 3x slower (with or without indexing on MMI). Splitting of geometry information from the table to a separate table, and using join, greatly increases speed, especially if done on 10million or more records. All in all, it is a great pity that the geometry.point type can not be chosen as fixed width. Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated (fwd)
-- Forwarded message --
Date: Mon, 12 Jun 2006 11:39:11 -0400 (EDT)
From: Gaspar Bakos
To: Barry [EMAIL PROTECTED]
Subject: Re: my-huge.cnf quite outdated
Hello, Barry,
RE:
Guess we would answer to everyone on the list who wishes to optimize his
cnf.
I don't guess, and don't even expect that you answer to everyone.
Oh, i have add super X RAMs with latencies of blah blah. Please i
think my cnf is outdated can somone help me? Or: Oh, i have added a
HD with 2times more rounds per/m can you update my cnf PLZ?
These are not what I asked, they are pretty negative exaggarations.
And yes. You can tweak the shit out of the mysql.cnf files.
You have to test yourself on your system.
This is what I am doing, and in the meantime, looking for experience,
and also sharing mine.
And btw. the cnf files wrk with even bigger tables than you have.
Not optimal but okay.
How big?
Every special server needs special handling. there is no the one and
only you have to do it this way way
OK, so why is there a my-{small,large,huge}.cnf ?
They are guidelines for typical systems and applications.
But they are quite outdated, as typical systems changed.
All in all: I was looking for _typical_ configs for 4GB+ machines and
100Gb+ tables.
G
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated (fwd)
Hi, Daniel, RE: I've had a big time looking for configs over the net and manuals, ended search with this: http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html [...] Thanks for the links, I will check them again. I read most of those that are available on the web. their way easily... They are not for typical systems, they are specially SAFE and minimally optimized configs so your server won't crash, but still use some of the resources of the machine. Keep in I see. To re-iterate the question, I think the missing info is 1. the useful domain of system parameters. For example, read_buffer 64M does not help at all because of other limitations. (the default is 8K if I recall correctly). 2. the relation between the parameters, as they have a complicated relation. It is usually quite meaningless to increase/decrease a sinle parameter without changing others. So later on I will probably ask or share experience on the reasonable range on specific parameters. because I don't think anyone would give you a config set that may crash/overload/put in risk your server. Simply grab a set of features and play in a test database. There are some stress tests for mysql over the web... Good idea to google for stress tests. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated
Hi, Keith, RE: This seems to be the way things are with mysql nowdays. Is it not time for the developers to take a serious look into culling all the outdated and multiple ways of accomplishing the same thing from mysql and the documentation? This is a somewhat different subject. But you are right about it. On the other hand, I have been using MySQL since 2001, and I enjoy looking at the old syntax, and seeing how it changed helps me understanding what the new syntax means. Back to my-huge.cnf, I am sure there are many people reading the list who run MySQL on big-big servers, and they must have figured out how to optimize it. I am curious about their advice. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
example when indexing hurts simple select?
Hello, There is a table (TEST) with ~100 million records, 70 columns (mostly integers, some doubles, and a few short fixed char()), and has a ~100Gb size. The table has a single (not unique) index on one integer column: MMi. If I invoke a simple select based on MMi, then the selection is VERY slow: nohup time mysql CAT -u catadmin -p$MPWD -e create table test2 select * from TEST where MMi 9000; ( this selects only ~0.5 % of the table, by the way, so test2 is a small table, and the time is not spent with writing it on disk) Time used: 47 minutes: 0.00user 0.00system 47:17.37elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+416minor)pagefaults 0swaps If I do the same but ignore the index, the select time drops to 1/5th !!! nohup time mysql CAT -u catadmin -p$MPWD -e create table test3 \ select * from TEST ignore index (MMi) where \ MMi 9000; Time used: 11 minutes: 0.00user 0.00system 11:08.23elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+415minor)pagefaults 0swaps Without the index, MySQL does a simple thing; it reads in sequentially the 100Gb database, and while reading, it parses the lines, and determines if the MMi is 9000. This is done with about 16Mb/s speed. With the index, it performs a large number of random seeks. The data (.MYD) is probably not organized on the disk according to sorted MMi. Questions; 1. Is there a way to decrease random seeks? E.g. mysqld config parameters, increase some buffer/cache sizes? 2. Optimize table: is there a way to rearrange data so that random seeks are minimized? 3. If we have to live with large number of random seeks does anyone know how the underlying FS can be optimized? Cheers, Gaspar ( All this TEST DB is kept on FC3 Redhat + MySQL 5.0.22, XFS filesystem on a 1.5Tb RAID-5 array of 4 SATA disks (served by a 3ware RAID controller). The computer is running on two opteron 2.0GHZ CPUs and 4Gb RAM. ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: example when indexing hurts simple select?
Hi, Philip, RE: What is the EXPLAIN output of each? OK, first I naively typed: explain create table test2 select * from TEST where MMi 9000; but of course, this does not work. The simple select that uses MMi_m as index (and takes up to an hour): mysql explain select * from TEST where MMi_m 9000; ++-+---+---+---+---+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--++-+ | 1 | SIMPLE | TEST | range | MMi_m | MMi_m | 3 | NULL | 406649 | Using where | ++-+---+---+---+---+-+--++-+ The select with ignoreing the index (takes only 11 minutes) mysql explain select * from TEST ignore key (MMi_m) where MMi_m 9000; ++-+---+--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+---+-+ | 1 | SIMPLE | TEST | ALL | NULL | NULL | NULL| NULL | 470992970 | Using where | ++-+---+--+---+--+-+--+---+-+ Have you tried analyze table x; optimize table x; Not yet. As regards optimize table, I thought it would not make too much sense, because: OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). But I have just uploaded this table from ASCII, and made no changes. Nevertheless, I will give a try, maybe there is some feature of OPTIMIZE TABLE I don't know of. What did you think of? Is it MyISAM or Innodb ? MyISAM. I'll keep you posted. I am very curious about how this can be resolved. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dropping primary key - 99%CPU
Hi, Could someone explain why dropping a primary key of a table (of ~1million rec) may take up to minutes of time and 99%CPU? Naively, I would have thought that this involves only updating the index file. However, all the .MYD, MYI and .frm files are duplicated (#sql-*), and then something happens for quite a bit of time. Is this related to the fact that I have more indexes in the table, and those are retained? I guess MySQL is doing something intelligent, but I did not find a source that explains the procedure ... what exactly happens. Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dropping primary key - 99%CPU
Hi, Dan, RE: Currently mysql handles all ALTER TABLE commands by rebuilding the entire table and all indexes. OK, so an add index is mapped to alter table, and the alter table rebuilds everything. This means if I have a table with multiple indexes, it does not make too much sense to create the indexes separately? For example: alter table add index name1 (name1); alter table add index name2 (name2); alter table add index name3 (name3); Instead, something like: alter table add index name1 (name1), add index name2 (name2), \ add index name3 (name3); should work better. I am dealing with a table of ~100Gb size that will be purely readonly, and i am trying to optimize the reads by creating indexes on the columns that are most popular in the where statements. G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my-huge.cnf quite outdated
Hi, Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated? It says for systems with 512Mb RAM or more. Nowdays this is pretty basic setup, and 'huge' is probably something in excess of 4Gb RAM. I wonder if anyone has a recommendation for truly huge systems. For example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is fully devoted to serving the mysql daemon. The config I have (see below) has been tuned to be optimal for creating indexes on a large (100Gb+) single database table. It works fine (although not satisfactory), but I worry that some parameters may have an optimal value or range, and it does not make sense to increase them like crazy. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M # Cheers, # Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
.TMD files
Hello, Can someone point me to the docs that explain what the .TMD files are? We are using MySQL 5.0.22 under RH FC3. We have a massive table of the size ~100Gb. It already has a couple of indexes. We are adding a new index on the first 8 character of a column: alter table CAT add index(des(8)) This operation has been running now for a day, and it is painstakingly slow. As opposed to the previous indexing, it is slowly filling up a .TMD table. My guess is that this .TMD may be the des(8) extracted from the table. Let me know if you have a clue. Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
building index table, duplicate .MYD
Hi, I wonder if anyone could explain the rational behind the following: (MySQL-5.0.22) We have a MyISam table with ~100Gb size. We start creating an index. The procedure starts making temporary files: -rw-rw 1 mysql mysql11430 Jun 7 10:27 #sql-79d6_cc.frm -rw-rw 1 mysql mysql 1024 Jun 7 10:27 #sql-79d6_cc.MYI -rw-rw 1 mysql mysql 80824238080 Jun 7 11:35 #sql-79d6_cc.MYD (this is while the indexing is still running) I understand the first two, but i was surprised that the entire 100Gb .MYD gets duplicated during the indexing. At the end, I believe the #sql-79d6_cc.MYD table replaces the original. Is indexing also performing an optimize table at the same time? Naively i would have tought that when we index, only an extra index file is created, and the original table data is not touched. Gas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: procedure analyse() returns bad type?
Hi, Jeremy, RE: Looks like a bug. The code in question is: if (num_info.dval -FLT_MAX num_info.dval FLT_MAX) sprintf(buff, FLOAT(%d,%d), num_info.integers, num_info.decimals); else sprintf(buff, DOUBLE(%d,%d), num_info.integers, num_info.decimals); OK, thanks for the info. Seems like procedure analyse() has some other bugs, for example despite controlling the number of possible enum fields to e.g. 16, it returns an enum with 256 elements. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
procedure analyse() returns bad type?
Hi, The following command returns recommended type FLOAT(3,6) for a double(10,6) column: mysql CAT -t -u catadmin -p -e \ select * from temp procedure analyse(16,8192) temp.log Optimal_fieldtype = FLOAT(3,6) NOT NULL. The trouble is that FLOAT(3,6) does not make sense, with FLOAT(M,D), M D+2 should hold. Is procedure analyse(16,8192) old? Or I am doing sth wrong? I have MySQL 5.0.22 under FC3 Linux on AMD64bit. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
char(17) vs. char(18)
Hi, Do you think there is any difference in the speed of select() statements done on a big table depending on whether one of the columns of this table is defined as char(17) or char(18)? That is, will the select speed depend on the parity of the number of chars? More generally, are char(2^n) width columns better to use? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increm, forced step back
Hi, My understanding is that with mysql = 3.23 versions the last value of an auto_increm column is stored, thus even if records are deleted, when a new one is inserted (as NULL), values will not be re-used. This is a fine attribute, but is there any way to override it? That is, to bump back the counter by force? E.g. I have test: 1 2 3 4 and then delete 3 and 4: 1 2 and when inserting a new record by saying insert into test NULL , I would like it to become 3. Maybe I need a new table creation, and copy of current table in that, etc.? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: move column position
Hi, RE: I believe this is what you are looking for: ALTER TABLE Images MODIFY Imcreatedat datetime NOT NULL default '-00-00 00:00:00' AFTER Imccdtemp; Thanks, Tom for the clue. Yes, this is what I tried, but it did not work. Seems like modify does not accept the AFTER part. I tried it again, and the same failure: ERROR 1064: You have an error in your SQL syntax Maybe this is due to mysql-3.23.52 (i.e. not mysql-4)? Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create ... select fails
Hi, I am trying to making a copy of a table called Sections into a new table (just to be created) called CamCfgSec. This CamCfgSec has different column names (but represent the same data, and in the same column order). I tried to achieve this with the create table ... select syntax, but I failed. Maybe you have an enlightening idea. Here are my trials: --- 1. drop table if exists CamCfgSec; CREATE TABLE CamCfgSec select * from Sections; Works, but _column names_ are copied verbatim from Sections table, and they are not what I want, --- 2. I try to create the CamCfgSec with the favoured column names, and then insert the data: drop table if exists CamCfgSec; CREATE TABLE CamCfgSec ( CMCSECid int(1) NOT NULL default '0', CMCSECname char(30) NOT NULL default '', PRIMARY KEY (CMCSECid) ) TYPE=MyISAM select * from Sections; ERROR 1062 at line 2: Duplicate entry '0' for key 1 Why? --- 3. Given the caveats mentioned in the DuBois book p. 150: CREATE TABLE CamCfgSec ( CMCSECid int(1) NOT NULL default '0', CMCSECname char(30) NOT NULL default '', PRIMARY KEY (CMCSECid) ) TYPE=MyISAM select SectionID as CMCSECid, SectionName as CMCSECname from Sections; ERROR 1060 at line 2: Duplicate column name 'CMCSECid' Why? -- 4. Of course, this works: drop table if exists CamCfgSec; CREATE TABLE CamCfgSec ( CMCSECid int(1) NOT NULL default '0', CMCSECname char(30) NOT NULL default '', PRIMARY KEY (CMCSECid) ) TYPE=MyISAM; insert into CamCfgSec select * from Sections; I am sure that I am screwing up something with the syntax. Any idea would be most welcome. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
move column position
Hi, Is there a solution for moving a column in a table from a position to another? I tried various alter table configurations. The only solution i found was (trying to move IMcreatedat): # Add temporary column: Alter table Images add column temp datetime NOT NULL default '-00-00 00:00:00' after IMccdtemp; # Copy values from IMcreatedat update Images set temp = IMcreatedat; # Delete original column alter table Images drop column IMcreatedat; # Rename temporary col: alter table Images change column temp IMcreatedat datetime NOT NULL default '-00-00 00:00:00'; Any shorter solution? Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unique key - primary key
Hi, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says ... PRIMARY KEY (IMstid,IMfnum) What is the difference? Is there any? Table1: mysql desc table1; ... | HONstid | int(3)| | PRI | 1| | | HONname | char(20) | | PRI | standard | | ... Table2: mysql desc table2; ... | IMstid | int(3) | | PRI | 0 | | | IMfnum | int(6) | | PRI | 0 | | ... A general question: is there a difference between a primary key and a unique key, or they are synonims? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unique key - primary key
Hi, RE: And columns in primary keys must be NOT NULL. Columns in unique keys can be NULL (if they are NOT NULL, then the unique key is functionally the same as a primary key). OK, thanks guys for the explanation. Then the result of mysqldump table definition part: UNIQUE KEY HONstid (HONstid,HONname) means that I have two unique keys: HONstid and also the (HONstid,HONname) combination? This does not make too much sense; if I can have only one HONstid in my table, then of course i can have only one kind of (HONstid,HONname) combination. In the actual table, I have multiple occurences of values (HONstid,HONname)in the HONstid column, i.e. it does not seem like HONstid in itself was a unique key. Only the (HONstid,HONname) combination is unique. Cheers Gaspar p.s.: Paul, I very much enjoy your MySQL book. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create set column with possible values 1 to 64
Hi, Is there any elegant way of saying the following statement in a shorter format: create table dummy ( col1 set(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,..64) ); That is, I would use all the 64 possible values that are offered by the SET column structure, and I am lazy to write out all of them. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert into table select ...
Hi, I am trying to duplicate a row of a table in the following way: insert into Observer select * from Observer where OBid = 4; ERROR 1066: Not unique table/alias: 'Observer' It is a unique table; I mean there can't be another table named in the same way. Any pointer would be welcome. Probably trivial, but got stuck. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to include count(*) in selection criteria
Dear all, I am trying the following mysql statement (probably it will be obvious that I am a newcomer): select IMdirectory, IMljd, count(*) as N from Images where IMstid = 5, N 10 group by IMdirectory order by IMljd; ERROR 1054: Unknown column 'N' in 'where clause' My question is: how could I select only those grouped entries that have a count number greater than e.g. 10? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to include count(*) in selection criteria
Hi, RE: In other words, you cannot use values that are determined from the selected rows to determine which rows to select. :-) Sounds very logical. In fact I was not surprised that my query did not work, I just had no idea about the workaround. Thanks again! Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
Hi, Joe, I have exactly the same scenario. Did you get any valuable response you could share with me? I haven't seen any on the list. In fact, my case is slightly more complicated; I have N computers, all having their local databases, and have an additional computer, which I call the central one, having a central copy of the database. I'd like to sync all the N+1 databases continuously, so they are identical. A---C---B | D That is, if I change anything on any PC's DB (e.g. A), it replicates itself to the central DB (C), and then migrates to B and D. This means that the A-C connection is such that A is a master and C is a slave, and the C-B is such that C is a master and B is a slave. On the other hand, if I change something on another local DB than A, e.g. B or C itself, I'd like this to migrate to A, i.e. this case the A-C connection is such that A is the slave. Altogether, I'd need a continuous master-master replication between all DBs and C. Similar to a RAID-1 array, just in the world of databases, and over TCP. Maybe there is a problem with my concept, and this solution of pushing for reliability will eventually cause chaos. The Mysql manual does not mention master-master replication: Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
high availability sync of 3 hosts
Hello, I am trying to find a solution for the following scenario. The topic is replication/high-availability/redundancy and sync. I have three computers, A, B and C. A and B are operating important hardware (astronomical telescopes TEL_A and TEL_B), and the operation parameters as well as logs of operations are stored in a MySQL database. I prefer having a central database (DB_C) for this purpose, in fact located on C, from which I can overview parameters and logs of A and B simultaneously. During normal operation, A and B reads/writes only DB_C: C - DB_C | | -- - | | A - TEL_A B - TEL_B (|) (|) (|) (|) DB_A DB_B However, if for some reason the central C host goes down, or internet connection is broken, I would like to achieve the following: A and B to continues standalone operation (read-write) with the freshest possible local DB: C - DB_C | | X X X X X X X X X X X | | A - TELESCOPE_AB - TEL_B | | DB_A DB_B I guess this has to be done with continuous slave-master replication between DB_A -- DB_C and DB_B -- DB_C while the internet connection is alive. When the internet connection is resumed, or the central DB is available again, I would like [to realize that DB_C is available again, and] DB_C to be syncronized with the changes made in DB_A and DB_B (the changes, of course, are different in the two replicas). In other words, we have a synced system of A, B and C, and we have the possibility of A and B slowly diverging from C temporarily. Then we'd like to sync up the system again. What would you recommend? Maybe there is a problem with my concept. All the best, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
