Re: Unique ID's across multiple databases
This is actually more for failover scenarios where databases are spread in multiple locations with unreliable internet connections. But you want to keep every single location working even when they are cut off from the other databases. The primary purpose is not load distribution. On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel n...@dinagon.com wrote: offset + increment thingy is good if you know in advance that you'll have a limited number of servers. But if you have no idea that you will have 2, 20, or 200 servers in your array in the future, you just can't pick an optimal What benefit do you think you will reap from that many masters ? Don't forget that every write still has to be done on every server, so you're not actually distributing that load; while for reads you only need simple slaves. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Unique ID's across multiple databases
Hell, yeah. :) Actually, the ID system I described below works quite well according to my tests. I feel very comfortable with it both from primary key size and dynamically increasable database number point of views. What I actually don't like in it is the concatenated unique ID (ID + SID) pairs. To use two fields for primary and foreign keys is not the most convenient to say the least. :) I am just wondering if anyone has any better idea to fulfill the requirements (small index size, dynamically increasable numbe of databases in the array, incremental-like ID's are optimal for the MySQL indexing engine) and avoid this silly drawback. :) On Mon, Sep 13, 2010 at 1:26 PM, Johan De Meersman vegiv...@tuxera.bewrote: Hmm, that's a very interesting scenario, indeed. One bad connection will break the chain, though, so in effect you'll be multiplying the disconnecting rate... I think you'd be better of with a star topology, but MySQL unfortunately only allows ring-types. This is gonna require some good thinking on your part :-) On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel n...@dinagon.com wrote: This is actually more for failover scenarios where databases are spread in multiple locations with unreliable internet connections. But you want to keep every single location working even when they are cut off from the other databases. The primary purpose is not load distribution. On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel n...@dinagon.com wrote: offset + increment thingy is good if you know in advance that you'll have a limited number of servers. But if you have no idea that you will have 2, 20, or 200 servers in your array in the future, you just can't pick an optimal What benefit do you think you will reap from that many masters ? Don't forget that every write still has to be done on every server, so you're not actually distributing that load; while for reads you only need simple slaves. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Unique ID's across multiple databases
Well, thanks, but I'm afraid using UUID's (even with hex compression) is kind of a suicide, when it comes to performance. This is a good summary about the issues: http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/ So, some kind of auto_increment or sequencing must be the optimal solution here. On Mon, Sep 13, 2010 at 2:05 PM, Fish Kungfu fish.kun...@gmail.com wrote: I had some coffee and realized that actually, using a UUID might be something to look at. There have been quite a few discussions about using a UUID as a unique id and it does have some gotchas. Just Google: mysql uuid Have a great day ~~Fish~~ On Mon, Sep 13, 2010 at 7:30 AM, Fish Kungfu fish.kun...@gmail.com wrote: I could be way off here, but how about letting your unique id be a calculated column of the the server's MAC address concatenated with an auto-increment id column? I hope this helps... ~~Fish~~ On Mon, Sep 13, 2010 at 7:26 AM, Johan De Meersman vegiv...@tuxera.be wrote: Hmm, that's a very interesting scenario, indeed. One bad connection will break the chain, though, so in effect you'll be multiplying the disconnecting rate... I think you'd be better of with a star topology, but MySQL unfortunately only allows ring-types. This is gonna require some good thinking on your part :-) On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel n...@dinagon.com wrote: This is actually more for failover scenarios where databases are spread in multiple locations with unreliable internet connections. But you want to keep every single location working even when they are cut off from the other databases. The primary purpose is not load distribution. On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel n...@dinagon.com wrote: offset + increment thingy is good if you know in advance that you'll have a limited number of servers. But if you have no idea that you will have 2, 20, or 200 servers in your array in the future, you just can't pick an optimal What benefit do you think you will reap from that many masters ? Don't forget that every write still has to be done on every server, so you're not actually distributing that load; while for reads you only need simple slaves. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Unique ID's across multiple databases
Well, that would be the plan, yes. :-) Anyway, I'll get over the problem sooner or later. :-) On Mon, Sep 13, 2010 at 8:46 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Kiss Dániel [mailto:n...@dinagon.com] Sent: Monday, September 13, 2010 11:49 AM To: Jerry Schwartz Cc: Johan De Meersman; Max Schubert; mysql@lists.mysql.com; replicat...@lists.mysql.com Subject: Re: Unique ID's across multiple databases Well, not exactly. I do not own all the databases. Some of them are placed at customers, some of them are at my data warehouse. So, neither NAS or Fibre Channel is a solution in this case. [JS] Then you have a mess on your hands. Are you going to be mirroring these databases separately for each customer? I wish you well. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com On Mon, Sep 13, 2010 at 4:30 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, September 13, 2010 7:27 AM To: Kiss Dániel Cc: Max Schubert; mysql@lists.mysql.com; replicat...@lists.mysql.com Subject: Re: Unique ID's across multiple databases Hmm, that's a very interesting scenario, indeed. One bad connection will break the chain, though, so in effect you'll be multiplying the disconnecting rate... I think you'd be better of with a star topology, but MySQL unfortunately only allows ring-types. This is gonna require some good thinking on your part :-) [JS] It sounds like you are trying to protect against a regional disaster. This is precisely the type of scenario for which NAS or FibreChannel is used. You let the storage medium take care of replication. Typically you'd only need two units, perhaps on opposite sides of the country, using FibreChannel over IP. I've been out of this market (sales/support side) for many years, so I don't know what the current technology costs, but if you can afford it that is the way to go. It will make your life much simpler. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel n...@dinagon.com wrote: This is actually more for failover scenarios where databases are spread in multiple locations with unreliable internet connections. But you want to keep every single location working even when they are cut off from the other databases. The primary purpose is not load distribution. On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel n...@dinagon.com wrote: offset + increment thingy is good if you know in advance that you'll have a limited number of servers. But if you have no idea that you will have 2, 20, or 200 servers in your array in the future, you just can't pick an optimal What benefit do you think you will reap from that many masters ? Don't forget that every write still has to be done on every server, so you're not actually distributing that load; while for reads you only need simple slaves. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Unique ID's across multiple databases
Hi, I'm designing a master-to-master replication architecture. I wonder what the best way is to make sure both databases generate unique row ID's, so there won't be ID conflicts when replicating both directions. I read on forums about pro's and con's using UUID's, also about setting the *auto-increment-increment *and *auto-increment-offset* system variables. I'm not a fan of UUID's for this purpose. They are two big, slow to generate, and they don't 100% guarantee global uniqueness anyway. On the other hand, the *auto-increment-offset *trick limits the number of possible databases in the system in advance, which is not so nice. So, I'm thinking about a composite unique ID system, where the value of the *server-id *system variable is always a part of the ID. The server-id has to be set uniquely anyway for any replication architecture in MySQL. A sample would look like this: CREATE TABLE SampleParents ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, SID SMALLINT UNSIGNED NOT NULL, SampleData VARCHAR(50) NOT NULL, PRIMARY KEY (ID, SID)) ENGINE=InnoDB; CREATE TABLE SampleChildren ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, SID SMALLINT UNSIGNED NOT NULL, SampleParentID INT UNSIGNED NOT NULL, SampleParentSID SMALLINT UNSIGNED NOT NULL, SampleData VARCHAR(50) NOT NULL, PRIMARY KEY (ID, SID), KEY (SampleParentID, SampleParentSID), CONSTRAINT FOREIGN KEY (SampleParentID, SampleParentSID) REFERENCES SampleParents (ID, SID)) ENGINE=InnoDB; Where SID is always filled with the value of the @@server_id global variable according to the server where the record is created. How I see it is that it has the following advantages. - The ID+SID pair guarantees pripary key uniqueness within the replication array. - Auto_increment is a magnitude faster than generating UUID's. - SID adds only 2 bytes in this case to the size of the primary key item. It can be even 1 byte if I'm sure I'll never exceed maximum 255 servers. But anyhow, it is still way smaller than the 16 byte of a UUID field, even if using BIGINT's. - Keeps the efficiency of the primary key indexing, because the key is still very close to a strictly incremental value. Well, you can see what the obvious disadvantage is: - Primary and foreign keys are always double fields. This is not so convinient when you are joining tables and add the WHERE clauses to your queries. It might even negatively affect the evaluation speed of join conditions, although I have no idea yet, how much. (My gut feeling is that it's not a big issue, due to the good query optimizer of MySQL.) My question is. Does anyone have any better idea, how to approach this problem? Thanks,
Re: Unique ID's across multiple databases
You may be right. I'm not arguing that offset + increment is working. I'm just wondering if that's the optimal solution when you do not know how many servers you will have in your array in the future. In my view, the offset + increment thingy is good if you know in advance that you'll have a limited number of servers. But if you have no idea that you will have 2, 20, or 200 servers in your array in the future, you just can't pick an optimal increment value. It just doesn't scale well enough to me. If you go with BIGINT ID's, you may have a big enough interval to be generous and pick a big increment value and allow 200 or even 2000 to make sure you cover worst case scenarios. I'm just not sure if it's worth it to use up 8 bytes for a primary key, when in general 4/5/6 is more than enough. Any thoughts on this? On Sun, Sep 12, 2010 at 9:32 PM, Max Schubert m...@webwizarddesign.comwrote: Server offset + increment works really well, is simple, and well documented and reliable - not sure why you would want to re-invent something that works so well :). -- MySQL Replication Mailing List For list archives: http://lists.mysql.com/replication To unsubscribe: http://lists.mysql.com/replication?unsub=n...@dinagon.com
Different versions are the same?
Hi, I've downloaded mysql-max-4.0.2-alpha-win.zip (Anyway, the size of this file is the same as mysql-max-4.0.1-alpha-win.zip) When I unzip this file and start to install it, I can see in the setup window that it is MySQL-max-4.0.1 What is the problem. Guys at MySQL did a mistake, or I downloaded it from a wrong mirror site, or what??? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MD5
Hi everyone, Can anyone tell me how to create an MD5 checksum on a file. I tried to do this by using the MySQL MD5 function, but it does not work on too big files (above 650MB), even if I set max_allow_packet size very big. Is there any small program to do this, anyway? Thx Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: database modelling tool
Try the Zeos Database Designer. It could be pretty good, but unfortunatelly it's full of bugs, and there's no newer version a long time ago. http://sourceforge.net/projects/zeoslib/ At 13:03 2002.06.13.s +0300, you wrote: Hello, Is there any database modelling tool for mysql? -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: not able to edit table:((
Greetings, Stop the MySQL server and check the table files. I think they are read only if you use Win32, or under Linux the user 'mysql' does not have the write right to the tables or its directories In the last case: chmod 660 * and/or chown mysql:mysql * At 12:39 2002.06.11. -0400, you wrote: Hi, I am not able to edit a table: the sql queries i tried are follows: delete form documents where id = 134;gives the following error: error: table documents is read only repair doesnt seem to work repair table documents; Table Op Mst_typeMsg_text mydb.documentsrepair errorthe 'documents' is read only Can someone help me thanx anil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign key constraints
First of all the referenced key must be on PRIMARY KEY.
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
...
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
...
At 10:59 2002.06.10. +0300, you wrote:
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(10) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(80) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Foreign keys in query optimization
I studied the MySQL and InnoDB manual, but I did not find anything about the internal usage of foreign keys. I mean that I would like to know if foreign keys are used for query optimizations or functions like that. Because I think foreign keys should be used not only for keeping the data integrity of the DB. Thanks, Daniel Kiss - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Are FOREIGN KEYs used?
I studied the MySQL and InnoDB manual, but I did not find anything about the internal usage of foreign keys. I mean if I define a foreign key it is good for me, because the InnoDB engine does not let the database to get integrity errors. But I think the foreign key definitions could be used in query optimizations, but I did not find anything about it. So, are foreign keys used in query optimizations in case of InnoDB databases to speed up performance? Thanks, Daniel Kiss - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SV: building tree view in mysql?
This problem is already solved in Oracle. It it the CONNECT BY PRIOR
statement which recursively queries a table.
The good news is that I read in the MySQL manual, that this feature will be
implemented in MySQL in the Real Near Future :-).
(MySQL manual: 1.8.2 Things That Must be Done in the Real Near Future,
page 47 in the PDF manual.)
At 15:57 2002.06.06. +0200, you wrote:
Hello
I think, there's a better solution:
Use one database - field for sorting (for example SORT). Then you need
to fill in
values in this field, that satisfy your needs:
Root - Item
1. Hello You
1.1. test1
1.2. test2
2. cool
2.1 cool i
2.2 cool ii
...
The values of the sort - fields will be:
1. 0001:0
1.1 0001:0001:0
1.2 0001:0002:0
2. 0002:0
2.1. 0002:0001:0
2.2 0002:0002:0
now you only need something like that:
select * from ... where ... order by SORT
that's very fast, witch is far more important that the overhead you have
to fill in
the SORT - values. There are other advantages:
If you count the :, denn you can see how deep you are in the Tree. If
your user
clicked on the tree and you know the value of the sort - fields the user
clicked on,
you know, whether the item you are outputting is on the way to your
selected item.
If it is, then it is a prefix of the item the user clicked on (you need
to remove
0 for that).
hope that helps?
greetings from Switzerland
Patrick
Carsten Gehling wrote:
Sorry I wasn't really awake, and didn't notice which list you'd posted
on
:-) Here's an example in PHP (not tested):
==
$cat_list = array();
$sql = select id, parent_id, category from your_table where order by
parent_id, category ;
$res = mysql_db_query($dbName, $sql) or die(Fejl ved query: $sqlhr
.
mysql_error());
while ($row = mysql_fetch_assoc($res))
$cat_list[] = $row;
showcattree(0, 0, $cat_list);
function showcattree($parent_id, $niveau, $cat_list) {
$tabStr = str_repeat(nbsp;, $niveau*3);
for ($i=0; $icount($cat_list); $i++) {
$local_id = $cat_list[$i][id];
$local_parent_id = $cat_list[$i][parent_id];
$local_navn = $cat_list[$i][titel];
if ($local_parent_id == $parent_id) {
echo $tabStr$local_navnbr;
showcattree($local_id, $niveau+1, $cat_list);
$count++;
}
}
}
==
Please note that cat_list is given as a by-reference parameter.
Otherwise
you'd be copying the whole array for each recursive function call.
The trick is, that to make a tree you always need to use recursive
functions. The performance is gained by loading the complete list from
database at once (instead of calling a mysql SELECT statement for each
branch of the tree).
- Carsten
-Oprindelig meddelelse-
Fra: Carsten Gehling [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:33
Til: [EMAIL PROTECTED]
Emne: SV: building tree view in mysql?
Well you'll neew to do some coding in your application language.
What are you using to connect to MySQL? Perl? PHP? Java?
Tell me, and I'll give you an example where you only query the
database once (for optimum performance).
- Carsten
-Oprindelig meddelelse-
Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:50
Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Emne: Re: building tree view in mysql?
Yeah, thats exactly what I wanted to do. But how do I make MySQL
sort it
like that? Show the parent first, and then all its childs, so the
application can determite if it's subcategory by checking if
the parent is
was changed.
Sagi
From: olinux [EMAIL PROTECTED]
create a table like this:
id | parent_id | category
parent_id points to the parent categories id. Top
level categories get parent_id of '0'
ex:
1 | 0 | auto
2 | 1 | repair
3 | 2 | body
4 | 2 | windshields
5 | 1 | detailing
6 | 2 | tires
Tree would look like:
auto
repair
body
windshields
tires
detailing
olinux
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the
Re: sub-queries
I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB Hot Backups... ALL OR NOTHING ???
First of all, there are many aspects of your problem. 1. The InnoDB uses transaction safe table types, and uses the log files to restore if anything goes wrong during the tsanasction. So it is almost impossible to have a permanent database error, that cannot be repaired by InnoDB itself. If MySQL crashes, you just have to restart it, and InnoDB repaires automatically all the tables containing errors. 2. In spite of the first section, its a good idea to create backups of your InnoDB tablespace, because it can happen that the hard disk you have your tablespace files fails and in a case like this you don't have anything else, just your backup files. It's a little bit difficult to save all the InnoDB tablespaces onto another backup disk, because they can be very big, although they are compressable very well, because the empty spaces inside the tablespace contain zeros. A simple solution is not to backup directly the InnoDB tablespaces. Just convert the InnoDB tables into MyISAM and backup the MyISAM version of them. 3. The third aspect is that you can have many tablespaces, not only one big. For example instead of an InnoDB initialization command in my.cnf like this innodb_data_file_path = ibdata1:2000M you can use this innodb_data_file_path = ibdata1:200M;ibdata2:200M;ibdata3:200M;..ibdata10:200M Good luck, Daniel At 15:21 2002.06.06. -0600, you wrote: Can you backup/restore just one MySQL database with InnoDB hot backup? From what I gather it's an all or nothing proposition. As I understand it, there's only one tablespace (with any number of data files) and all database tables of type 'InnoDB' are comingled in the one tablespace. Therefore, if a single datafile becomes corrupt, all the databases with InnoDB type tables are down and you have to restore everything. Is that right? If so are there any plans to have multiple named tablespaces? We have a single server with 150+ databases (one for each hosted customer). If one customer database goes down then we can restore the MYISAM type tables without affecting the 24X7 availability for the other 149 customers. However, if we convert tables to type InnoDB and a data file is corrupted or lost, then all databases are down and we have to restore EVERYTHING. Is this correct? Sincere thanks in advance... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: WinZeos and Transactions
If WinZeos you've written about is the Zeos Database Objects (http://www.zeoslib.org), then I think I know the solution for your problem. First of all you don't have to BEGIN a transaction because when using an InnoDB connection, you always have an apened transaction, and when you close it by COMMIT or ROLLBACK InnoDB will automatically start a new one for you. There must be a TZMySQLTransact component on your palette which must be linked to your TZMySQLDatabase object. And all your TZMySQLQueryes and TZMySQLTable-s have to be linked to it too by their Transaction property. Now you have to AutoCommit property to False and TransactSafe property to True (of the TZMySQLTransact object). And that's all you have to do. Anyway the TZMySQLTransact has it's own Commit and Rollback methods. Use them. The proper documentation of how InnoDB tranasction types work, you can find it in the MySQL manual. Bye, niel At 09:38 2002.06.04.s +0200, you wrote: Hi all. I use WinZeos 3.0.3 to access MySQL 3.23.49 with Delphi 5.0. I'm using InnoDB tables and I want to use transactions. I try to begin a transaction with BEGIN and SET AUTOCOMIT=0 but doesn't work I can see the rows inserted in the database with another connection. What is the problem? The WinZeos components is too old? Iago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
