REvoke select rights.
Hi All, I have created a database and created a user with select rights to that database, there is a table name xyz in that database that I don't want that user to view, that's why i want to revoke that select rights from only that table, can anyone tell me what syntax of revoke would be used here. Sincerely, --shasan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Records where date is LESS THAN today, AND...
Is there a BETTER way to do this? Yes, store a DATE instead of seperate day/month/year values. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REvoke select rights.
Sohail Hasan [EMAIL PROTECTED] wrote: I have created a database and created a user with select rights to that database, there is a table name xyz in that database that I don't want that user to view, that's why i want to revoke that select rights from only that table, can anyone tell me what syntax of revoke would be used here. You can't revoke SELECT privilege from the table if you grant privileges on the database. You can grant SELECT privilege on each table that you want to be visible to user instead. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE VIEW
I can't find this syntax in mysql tutorial and the program does not accept it. Is there another method of achieving this ? Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW
Hi, I can't find this syntax in mysql tutorial and the program does not accept it. Well, that should give you a hint, shouldn't it? Is there another method of achieving this ? Views are going to be supported in MySQL 5, I believe. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I really have no_wait row-locks in MySQL+InnoDB?
Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks to All
Thanks to you all, In regards to my question about the retreiving the last few rows in a database your help was really appreciated. Chris thanks for that tip and YEP work for me we will read it into a array then read it back in reverse. Thaks again GUYS and I can't wait to be of help to some one as well Matthew Richardson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
Hi, Dmitry Anikin schrieb: Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing What you describe is basically not a deadlock situation! A deadlock means that two sessions wait for each other in such a way that neither can proceed before the other one has finished its transaction. Regards, Frank. algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Dr. Frank Ullrich wrote: Hi, Dmitry Anikin schrieb: Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing What you describe is basically not a deadlock situation! A deadlock means that two sessions wait for each other in such a way that neither can proceed before the other one has finished its transaction. Regards, Frank. algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- 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: Questions about indexing
On Sun, Dec 14, 2003 at 03:53:00PM -0500, Dan Anderson wrote: I have a database I'm using for a MMORPG (well, it isn't very MM because I'm something of a noob), and I have a few questions about indexing. I am storing world data in a database. In order to keep everything as swift as possible, I have indexed everything. And I really mean, everything -- a few dozen columns on a half dozen tables. My question is, is this the right way? I figure that since Not necessarily. You should analyze your SELECTS individually and create the indexes that are going to be used. You may also see where multi-column indexes could give you even more speed if you do this (you might be able to benefit from 'using index' to pull all rows from an index) The only other thing you should be worried about is the size of the indexfile; bigger indexfiles take longer to seek through; but since you say they'll all fit in mem this is only a concern when you're going to be updating them I suppose. With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issues with count(), aliases, and LEFT JOINS
Greetings, This is driving me crazy. I'm running MySQL 4.0.15-standard. I've got a db that tracks switches, servers they connect to, and connections between switches. Schema for server: +--+ --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+-- --+ | server_id| int(10) unsigned | | PRI | NULL| auto_increment | | customer_id | int(10) unsigned | YES | MUL | NULL| | | rack_id | int(10) unsigned | | MUL | 0 | | | distance_from_bottom | int(10) unsigned | YES | | NULL| | | switch_id| int(10) unsigned | | MUL | 0 | | | switch_port | int(10) unsigned | | | 0 | | | size | int(10) unsigned | YES | | NULL| | | label| varchar(50) | | | | | | base_hostname| varchar(50) | | | | | | base_ip | varchar(50) | | | | | | access_info | text | YES | | NULL| | | monitor | tinytext | YES | | NULL| | | hardware | text | YES | | NULL| | | addsoft | tinytext | YES | | NULL| | +--+ --+ Schema for switch: +--+--+--+-+-+-- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+-- --+ | switch_id| int(10) unsigned | | PRI | NULL| auto_increment | | rack_id | int(10) unsigned | | MUL | 0 | | | name | varchar(10) | YES | | NULL| | | ports| int(10) unsigned | YES | | NULL| | | mrtg_prefix | varchar(30) | YES | | NULL| | | size | int(10) unsigned | YES | | NULL| | | distance_from_bottom | int(10) unsigned | YES | | NULL| | +--+--+--+-+-+-- --+ Schema for switch_connect: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | switch_id| int(10) unsigned | | MUL | 0 | | | switch_port | int(10) unsigned | | | 0 | | | switch2_id | int(10) unsigned | | MUL | 0 | | | switch2_port | int(10) unsigned | | | 0 | | +--+--+--+-+-+---+ The switch_connect table tracks connections between two switches: mysql select * from switch_connect; +---+-++--+ | switch_id | switch_port | switch2_id | switch2_port | +---+-++--+ | 1 | 29 | 2 |1 | | 1 | 28 | 5 |1 | | 1 | 30 | 4 |1 | | 1 | 32 | 3 |1 | +---+-++--+ Now, I'm trying to get a count of used ports on switches. I was getting some weird results, and have broken it down this far: mysql SELECT - switch.switch_id, - switch.rack_id, - switch.ports, - COUNT(switch_connect1.switch_id) AS left_port_count, - COUNT(switch_connect2.switch2_id) AS right_port_count, - COUNT(server.server_id) AS server_port_count - FROM switch - LEFT JOIN - switch_connect AS switch_connect1 ON (switch.switch_id = switch_connect1.switch_id) - LEFT JOIN - switch_connect AS switch_connect2 ON (switch.switch_id = switch_connect2.switch2_id) - LEFT JOIN - server ON (switch.switch_id = server.switch_id) - GROUP BY switch.switch_id; +---+-+---+-+--+ ---+ | switch_id | rack_id | ports | left_port_count | right_port_count | server_port_count | +---+-+---+-+--+ ---+ | 1 | 1 |48 | 4 |0 | 0 | | 2 | 2 |24 | 0 |3 | 3 | | 3 | 49 |24 | 0 | 17 | 17 | | 4 | 43 |24 | 0 | 19 | 19 | | 5 | 45 |24 | 0 | 19 | 19 | | 6 | 3 |24 | 0 |0 | 9 | | 7 | 4 |24 | 0 |0 | 9 | | 8 | 5 |
Re: Temporary tables rights
Matt, thanks for your reply,this weekend I was thinking the way to do this work, because I use many times the same temporary table name for differentscriptswith differenttable structure.
All my temp tables begin with 'TMP', I thought grant something like 'TMP%' on tables_priv.table_name but looking in mysql's manual this doesn't work.
But there is a tricky,if you know thenames of your temp tables you can make an insert on tables_priv with the table name:
INSERT INTO tables_priv (host, db, user, table_name, grantor, table_priv, column_priv)VALUES ('host.localdomain','db1','user1','TMP_Liq', USER(), 'Select,Insert,Update,Delete,Alter,Drop,Index','')
and this work!
Alejandro
---Mensaje original---
De: Matt W
Fecha: sábado 13 de diciembre de 2003 22:36:54
A: adburne; [EMAIL PROTECTED]
Asunto: Re: Temporary tables rights
Hi Alejandro,
Yeah, this issue has come up before. It's not possible to GRANT DROP on
temp tables without GRANTing DROP on the whole database.
The temp tables will be dropped when the client disconnects you know,
right? And if you want to empty the table or reuse it, you should be
able to TRUNCATE it, I think.
And actually, if you can TRUNCATE the other tables (if the DELETE
privilege allows it), isn't that just as bad as DROPping them? :-)
Matt
- Original Message -
From: adburne
To: [EMAIL PROTECTED]
Sent: Friday, December 12, 2003 11:31 AM
Subject: Temporary tables rights
Hi, I'm granting users to use temporary tables as:
GRANT CREATE TEMPORARY TABLES ON db1.* TO user1;
and having grants on many other tables as:
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table1 TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table2 TO user1;
.
but how make this work
CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1;
ok
SELECT * FROM tmp1;
Error: select command denied to user: [EMAIL PROTECTED] for table tmp1
also:
DROP TABLE tmp1;
Error: drop command denied to user: [EMAIL PROTECTED] for table tmp1
I don't want grant select and "drop" global privilege over db1 but I
want use temporary tables, there is a way to do this work?
Alejandro
.
_ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: foreign keys.
Mofeed Shahin [EMAIL PROTECTED] wrote: On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Paul's example works fine for me. What version of MySQL do you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NOT EXISTS
Guys Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id = BATCH.batch_id) You have an error in your SQL syntax near 'EXISTS (select * from TXN where TXN.batch_id = BATCH.batch_id)' at line 1 thanks * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE VIEW
Hi, Subject: CREATE VIEW I can't find this syntax in mysql tutorial and the program does not accept it. Is there another method of achieving this ? About Views: http://www.mysql.com/doc/en/ANSI_diff_Views.html http://www.mysql.com/doc/pt/Unnamed_views.html Workarounds: With google you'll will easily find discussions about using temporary or heap tables as a workaround for views. It depends on your project, if that's applicable. Best Regards Michael Daheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EXISTS
On Mon, Dec 15, 2003 at 10:59:32AM -, Curley, Thomas wrote: Guys Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id = BATCH.batch_id) You have an error in your SQL syntax near 'EXISTS (select * from TXN where TXN.batch_id = BATCH.batch_id)' at line 1 It's a subselect, and subselects aren't implemented in 4.0 - you need 4.1. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EXISTS
Curley, Thomas [EMAIL PROTECTED] wrote: Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id =3D BATCH.batch_id= ) You have an error in your SQL syntax near 'EXISTS (select * from TXN wher= e TXN.batch_id =3D BATCH.batch_id)' at line 1 NOT EXISTS is supported from v4.1.0. You can rewrite your query using LEFT JOIN: http://www.mysql.com/doc/en/Rewriting_subqueries.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: NOT EXISTS
Many thanks Chris -Original Message- From: Chris Elsworth [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 11:22 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: NOT EXISTS On Mon, Dec 15, 2003 at 10:59:32AM -, Curley, Thomas wrote: Guys Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id = BATCH.batch_id) You have an error in your SQL syntax near 'EXISTS (select * from TXN where TXN.batch_id = BATCH.batch_id)' at line 1 It's a subselect, and subselects aren't implemented in 4.0 - you need 4.1. -- Chris * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange warning when using an IF statement
[snip]
I keep getting the following error when I try to run an if statement
Warning: 2 is not a valid MySQL-Link resource in then give the filename
Here is what I am trying to do.
if ($bumpnumber4) {
print (display this);
}else {
print (display that);
}
mysql_close ($Link);
Anyone have any idea what I may be doing wrong?
[/snip]
This code is not your problem, but the line that says
mysql_close($Link); may be. Do you have a MySQL resource in $Link?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Haw to get specific result?
Hello, I have table like this: id | product 0 | switch 1 | switch 2 | hub 3 | hub 4 | hub 5 | wire 6 | wire 7 | wire 8 | wire 9 | wire . . I`m looking for query which give me result as array: 0 - count of 'switch' 1 - count of 'hub' 2 - count of 'wire' Haw to do it ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Haw to get specific result?
'SELECT product, COUNT(product) FROM table GROUP BY product' will do this, I believe. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Pawel Filutowski [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 7:49 AM To: [EMAIL PROTECTED] Subject: Haw to get specific result? Hello, I have table like this: id | product 0 | switch 1 | switch 2 | hub 3 | hub 4 | hub 5 | wire 6 | wire 7 | wire 8 | wire 9 | wire . . I`m looking for query which give me result as array: 0 - count of 'switch' 1 - count of 'hub' 2 - count of 'wire' Haw to do it ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Haw to get specific result?
[snip]
id | product
0 | switch
1 | switch
2 | hub
3 | hub
4 | hub
5 | wire
6 | wire
7 | wire
8 | wire
9 | wire
.
.
I`m looking for query which give me result as array:
0 - count of 'switch'
1 - count of 'hub'
2 - count of 'wire'
[/snip]
SELECT COUNT(product), product FROM table GROUP BY product;
Now, this doesn't give you an array, but you can use this query to
populate an array in the programming language that you are using, such
as PHP...
$sql = SELECT COUNT(`product`), `product` FROM `table` GROUP BY
`product` ;
if(!($result = mysql_query($sql, $connection_string))){
echo MySQL Error: . mysql_error() . \n;
exit();
}
$sqlArray = mysql_fetch_array($result); // returns a one row array, just
loop through to access all rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Hi, So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are possible candidates. Does anyone know why we should or should not use any of these? Does anyone know of other possibilities? I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files What gave you that idea? Firebird (and InterBase of course) use a at least 1 file per database, but that's all. Can you define regular files? OK, there is a network-server component, but it really has nothing to do with an enterprise-DB. There's a server side process waiting for incoming connections just like with MySQL, MS SQL Server, Oracle etc etc... What exactly are you missing in Firebird? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Haw to get specific result?
Thanks for Joshua and Jay. The querys is that I expect. Best regards, Pawel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SEQUENCES
I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excluding Tables from mysqldump
Gordon [EMAIL PROTECTED] wrote:
We have 2 tables which are roughly half the size of the entire database
{ ~1.5GB}. These 2 tables are rarely changed {1-2 times a quarter}.
Is there a way in mysqldump to exclude these two tables without
specifically naming all of the tables { 100} we want to include?
No. You should specify list of tables in this case.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
[snip] I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. [/snip] I was wondering if you had tried it in a test table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My SQL setup issue
More information please ... what Operating System, where are the MySQL binaries and data directories on your machine, did you create a my.ini, is MySQL installed as a service - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, December 14, 2003 1:51 PM Subject: My SQL setup issue Hello, I am unable to start MySQL 4.0. I tried installing it from a mirro image. All looks OK, but when I try to initiate it, I reveive ERROR moving data -103 server. Then I initiate WinMySQLAdmin and I believe I am supposed to be prompted for Name and Password. I actually get a window with several tabs across the top of screen; Environment, Start Check, Server, My .ini Setup, Err File. but no login. Am I doing something wrong? Thank you. William __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- 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: Duplicate combination
Chris Nolan wrote: Hi! There are many ways, depending on whether you want the database to handle it, or you want your application to handle it. What you want is a UNIQUE index on surname_original and name_original. Assuming the table already exists: ALTER TABLE names ADD UNIQUE(name_original,surname_original); This will ensure that, for every row, the combination of name_original and surname_original is unique for the table, assuming that the table is called names. To do it at the app level, you could either check before each insertion (no good unless you're willing to lock the table while you check for existance - not recommended) or add another column that contains a unique hash (a bit quicker in terms of database operations, but probably not as nice as the DB-side solution). Hope this helps! Regards, Chris fr0g wrote: I have a table with peoples names in 3 different languages. Fields are like: id, surname_english, name_english, surname_original, name_original, surname_greek, name_greek. What I want is to check if a person has been entered twice in that table. The key is ID but I can't have any other field unique, as names and surnames are not unique. I only want the combination of two fields of the same language to be unique. How can I check this? Any possible solution? Thanks for all your help. It's been really helpfull. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE..
Hi everyone, I am currently trying to run the following command: LOAD DATA INFILE D:\mysql\sql\CountryData.txt INTO TABLE cou FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (id, country); Example data in file is: country, id AFGHANISTAN,4 ALBANIA,8 ALGERIA,12 AMERICAN SAMOA,16 ANDORRA,20 ANGOLA,24 The table the data is being inserted into is: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | cou_id | int(11) | | PRI | 0 | | | cou_name | text| | | | | +--+-+--+-+-+---+ I am getting the following error message and i am not sure why? Error 1054: Unknown column 'id' in 'field list' Any help would be appreciated. Thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SEQUENCES
Hi, As far as I know, definitely not. However, you could use an AUTO_INCREMENT field as the independent variable for some application-level function you use to generate the values in the sequence. Best regards, Chris Graham Little wrote: I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
I looked in the documentation but could not find any mention of SEQUENCES. The AUTO_INCREMENT documentation seems to say that you can change a server variable to adjust the incremented count, but unless i can put an equation into their, i don't see how changing that would help. thanks Graham -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:03 To: Graham Little; [EMAIL PROTECTED] Subject: RE: SEQUENCES [snip] I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. [/snip] I was wondering if you had tried it in a test table. This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
Try Insert INTO `table` ( `inc_field` ) values (10) the auto inc field will then generate the next sequential numbers HTH Peter -Original Message- From: Graham Little [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:01 To: '[EMAIL PROTECTED]' Subject: SEQUENCES I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- 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: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are possible candidates. Does anyone know why we should or should not use any of these? Does anyone know of other possibilities? I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files OK, there is a network-server component, but it really has nothing to do with an enterprise-DB. It is a mystery to me how the PostGreSQL work. I cannot recomm to use any feature discovered in PostGreSQL since some of the more uncommon feature are broken. I have only recently started these evaluations. BTW, my own background is from the Oracle DBA world. Well, the DBMS comparable to Oracle is neither MySQL nor Firebird. It's MaxDB. MySQL is certainly popular and seems to have very good performance, but I am concerned that the lack of Triggers, Stored Procedures, User-Defined Functions, and Views (to a lesser degree ) will be a disadvantage. And foreign-keys are a feature you shouldn't miss to. MySQL does offer them by patching it with InnoDB. MaxDB appears to be more feature-rich and possibly more industrial-strength. How does its performance and stability compare to the others? I'm using MaxDB and it's running 24/7 without problem on a web-server with a Java-WebApp. Before using MaxDB you should first look at the limits that MaxDB has. For example a row in a table may only store data up to 8KB. BLOP and CLOP columns don't count, but even for varchar-columns 8KB is a bit few. MaxDB has Unicode-support (UCS2) which is extremely important for Java-Clients. If using Unicode, 8KB means 4000chars. CLOB and BLOB-columns in MaxDB aren't comparable. You cannot use like or ,,= on them. MaxDB even doesn't have a FullText-Search, but to me it's not that important since i can replace them with Java-based search-engines like Lucene. MaxDB has a mechanism to backup your database without breaking anything. MaxDB supports Server-side prepared statement. The JDBC-driver is of good quality. Bugs are fixed relativly fast. My favourite DBMS could be PostGreSQL if i only knew which features are stable and which are not. A strange thing is, that the PostGreSQL-people decided to use UTF-8 for their unicode-support. In my eyes that makes it different to calculate string-lengthts and comparisons. But their argument is, that UTF-8 usually causes less disk-io. But does varchar(400) now mean 400bytes or 400chars? i don't know. I didn't take a close look at PostGreSQL yet, so all features that i mentioned about MaxDB might apply to PostGreSQL too. On both, MaxDB and PostGreSQL, you need to perform regular tasks. You must update the optimizer statistics for MaxDB, and run VACUUM for PostGreSQL. MySQL, well, i wouldn't know how to backup it, except by using a dump. How can i dump a table with binary data? I don't wanna know. A dump is not a backup, it's crap. Than there's the lack of Unicode-support, Foreign Keys, prepared statements (emulated by client-lib if available as far as i know), ... That all doens't make me feel comfortable about MySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
It is a mystery to me how the PostGreSQL work. I cannot recomm to use any feature discovered in PostGreSQL since some of the more uncommon feature are broken. Would you care to elaborate? I've used both PostgreSQL and mySQL, but certainly not all features, and I'm curious about what issues you had with PGSQL. Cheers, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Huh? Not know how to backup a MySQL database? *sigh* Every night, I do a backup of our MySQL database server that's holding all of our mail and various other things (20GB+). I set the isolation level to READ_REPEATABLE and use mysqldump | bzip2 to get the result. I've tested the restore and it's fine! A uni project I was working on this year that stored images and other binary stuff in BLOB fields was successfully backed up in a similar fashion. This all applies to InnoDB tables. I've heard cases of needing to use READ_COMMITED as the isolation level in some cases, as one can run into issues with memory consumption / table space problems if your traffic is high. To backup those funky sleek MyISAM tables, you could just issue a LOCK TABLE statement or two (LOCK DATABASE?), do a FLUSH and copy the files. If I recall correctly, (and I'm sure sirs DuBois and Zawodny will find out where I live and deal with me if I don't :-) ), this might be how the mysqlhotcopy script works. Regarding Interbase/Firebird - you're talking about a database with a very long and detailed history. The fact there is one file per database doesn't mean much - InnoDB has one file per server should you want to configure it that way and no stable release allows you to break up your databases or tables into files in any fashion other than what InnoDB itself decides to do with the ibdata* files you allow it to have. Interbase is still one of the most popular embedded databases in the world! Even more popular than (ick) FileMaker Pro and (vomit) FoxPro! Just looking at the number of third party tools out there that support development using it is a pretty decent vote of it's industry acceptance. I'm curious as to why you're not comfortable with MySQL. It truly features the best of all worlds when it comes to performance options and I've found the community support (Read: The MySQL mailing list) to be far more valuable than any course I've looked at or taken. I'd be very interested in hearing more from you, as I'm betting a few other readers on both lists would. Best regards, Chris Sven Köhler wrote: So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are possible candidates. Does anyone know why we should or should not use any of these? Does anyone know of other possibilities? I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files OK, there is a network-server component, but it really has nothing to do with an enterprise-DB. It is a mystery to me how the PostGreSQL work. I cannot recomm to use any feature discovered in PostGreSQL since some of the more uncommon feature are broken. I have only recently started these evaluations. BTW, my own background is from the Oracle DBA world. Well, the DBMS comparable to Oracle is neither MySQL nor Firebird. It's MaxDB. MySQL is certainly popular and seems to have very good performance, but I am concerned that the lack of Triggers, Stored Procedures, User-Defined Functions, and Views (to a lesser degree ) will be a disadvantage. And foreign-keys are a feature you shouldn't miss to. MySQL does offer them by patching it with InnoDB. MaxDB appears to be more feature-rich and possibly more industrial-strength. How does its performance and stability compare to the others? I'm using MaxDB and it's running 24/7 without problem on a web-server with a Java-WebApp. Before using MaxDB you should first look at the limits that MaxDB has. For example a row in a table may only store data up to 8KB. BLOP and CLOP columns don't count, but even for varchar-columns 8KB is a bit few. MaxDB has Unicode-support (UCS2) which is extremely important for Java-Clients. If using Unicode, 8KB means 4000chars. CLOB and BLOB-columns in MaxDB aren't comparable. You cannot use like or ,,= on them. MaxDB even doesn't have a FullText-Search, but to me it's not that important since i can replace them with Java-based search-engines like Lucene. MaxDB has a mechanism to backup your database without breaking anything. MaxDB supports Server-side prepared statement. The JDBC-driver is of good quality. Bugs are fixed relativly fast. My favourite DBMS could be PostGreSQL if i only knew which features are stable and which are not. A strange thing is, that the PostGreSQL-people decided to use UTF-8 for their unicode-support. In my eyes that makes it different to calculate string-lengthts and comparisons. But their argument is, that UTF-8 usually causes less disk-io. But does varchar(400) now mean 400bytes or 400chars? i don't know. I didn't take a close look at PostGreSQL yet, so all features that i mentioned about MaxDB might apply to PostGreSQL too. On both, MaxDB and PostGreSQL, you need to perform regular tasks. You must update the optimizer statistics for MaxDB, and run VACUUM for PostGreSQL. MySQL, well, i wouldn't know how to backup it, except by using a dump. How can
RE: SEQUENCES
Hi Chris, Thanks for your help, i will find another way around it. Graham -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:13 To: Graham Little Cc: '[EMAIL PROTECTED]' Subject: Re: SEQUENCES Hi, As far as I know, definitely not. However, you could use an AUTO_INCREMENT field as the independent variable for some application-level function you use to generate the values in the sequence. Best regards, Chris Graham Little wrote: I was wondering whether it was possible to make and AUTO_INCREMENT field instead of always adding 1 and starting at zero, into a SEQUENCE type field so that it is say a 10 digit integer and numbers are created according to the SEQUENCE. thanks Graham This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files What gave you that idea? Firebird (and InterBase of course) use a at least 1 file per database, but that's all. Can you define regular files? My idea of Firebird is the following: There a library that can access a file and use it as a database. that very much like using the MS-Jet-Engine which is the backend to MS-Access. OK, there is a network-server component, but it really has nothing to do with an enterprise-DB. There's a server side process waiting for incoming connections just like with MySQL, MS SQL Server, Oracle etc etc... Well, the network-server seemed to me like an application that uses the library i mentioned above. It doesn't seem to me like a big application like MySql or MaxDB. In other words: Firebird seems to be light weight DBMS. MySQL and MaxDB have a multi-threaded kernel that maintains its own cache, coordinates locks, etc. I don't think that Firebird's architecture is like that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.1.1-alpha-win.zip - Missing setup file
I noticed that too. Does anyone know the reason? May the force be with you all! nelson hotmail [EMAIL PROTECTED] 12/14/03 01:53pm Clear DayEvening, No setup file is included in the ZIP mysql-4.1.1-alpha-win.zip Do you know why ?? Cheers Mark
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Hi Sven, I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files What gave you that idea? Firebird (and InterBase of course) use a at least 1 file per database, but that's all. Can you define regular files? My idea of Firebird is the following: There a library that can access a file and use it as a database. that very much like using the MS-Jet-Engine which is the backend to MS-Access. Actually, this is Firebird Embedded. Indeed, a single DLL (with some additional DLLs if you want additional character set support) that acts like the engine. Firebird Embedded is single user. OK, there is a network-server component, but it really has nothing to do with an enterprise-DB. There's a server side process waiting for incoming connections just like with MySQL, MS SQL Server, Oracle etc etc... Well, the network-server seemed to me like an application that uses the library i mentioned above. Not at all. It's the other way around: the embedded version is almost the same as the server-side engine process, but wrapped into a library. It doesn't seem to me like a big application like MySql or MaxDB. In other words: Firebird seems to be light weight DBMS. Light weight it sure is. Very modest on memory requirements, for example. A bit too modest sometimes :-) MySQL and MaxDB have a multi-threaded kernel that maintains its own cache, coordinates locks, etc. I don't think that Firebird's architecture is like that. On the contrary, Firebirds architecture is exactly like that. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
On Mon, 15 Dec 2003, Peter Lovatt wrote: Try Insert INTO `table` ( `inc_field` ) values (10) the auto inc field will then generate the next sequential numbers HTH Peter Or just use ALTER TABLE table AUTO_INCREMENT=10 That way you don't have to enter a record just to set the AUTO_INCREMENT value. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE..
Graham Little [EMAIL PROTECTED] wrote: I am currently trying to run the following command: LOAD DATA INFILE D:\mysql\sql\CountryData.txt INTO TABLE cou FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (id, country); Example data in file is: country, id AFGHANISTAN,4 ALBANIA,8 ALGERIA,12 AMERICAN SAMOA,16 ANDORRA,20 ANGOLA,24 The table the data is being inserted into is: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | cou_id | int(11) | | PRI | 0 | | | cou_name | text| | | | | +--+-+--+-+-+---+ I am getting the following error message and i am not sure why? Error 1054: Unknown column 'id' in 'field list' There is no column 'id' in the table cou. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE..
on 15/12/03 12:13 pm, Graham Little at [EMAIL PROTECTED] wrote: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | cou_id | int(11) | | PRI | 0 | | | cou_name | text| | | | | +--+-+--+-+-+---+ I am getting the following error message and i am not sure why? Error 1054: Unknown column 'id' in 'field list' Your field is called 'cou_id' not 'id' (and 'cou_name' not 'country'). Roddie Grant [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE..
On Mon, 15 Dec 2003, Graham Little wrote: doing selective quoting below. LOAD DATA INFILE D:\mysql\sql\CountryData.txt INTO TABLE cou (id, country); See how you try to load from a file into the columns id and country in the cou table? The table the data is being inserted into is: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | cou_id | int(11) | | PRI | 0 | | | cou_name | text| | | | | +--+-+--+-+-+---+ And those columns doesn't exist. I am getting the following error message and i am not sure why? Error 1054: Unknown column 'id' in 'field list' Any help would be appreciated. Hope that helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Sven Köhler wrote: I was very disappointed by Interbase/Firebird. It seemed to me like a MS-Access: a database-engine that works on regular files What gave you that idea? Firebird (and InterBase of course) use a at least 1 file per database, but that's all. Can you define regular files? My idea of Firebird is the following: There a library that can access a file and use it as a database. that very much like using the MS-Jet-Engine which is the backend to MS-Access. OK, there is a network-server component, but it really has nothing to do with an enterprise-DB. There's a server side process waiting for incoming connections just like with MySQL, MS SQL Server, Oracle etc etc... Well, the network-server seemed to me like an application that uses the library i mentioned above. It doesn't seem to me like a big application like MySql or MaxDB. In other words: Firebird seems to be light weight DBMS. MySQL and MaxDB have a multi-threaded kernel that maintains its own cache, coordinates locks, etc. I don't think that Firebird's architecture is like that. Hmmmyou'll find that the SQL products worth mentioning that are like Access are the following: SQLite, FoxPro, FileMaker I could be wrong about the last two on some of these points, but all of the above are accessed via a library and are not wrapped in a server process (FileMaker Server is available, and it seems to alleviate this). Additionally, you won't get much in the way of write concurrency with the above products (which isn't a problem with SQLite, as it's designed for embedded stuff). Firebird/Interbase have all those nice things like row-level locking (although it doesn't seem to have multiversioning like InnoDB, PostgreSQL or Oracle), deadlock detection, prepared statements, views, stored procedures, automatic index management, proper SQL-92 isolation levels and funky caches for indexes, rows and other weird and wonderful metadata. Here's something you might want to do to see if my research is correct: 1. Install Firebird. Dump massive amounts of data in it and then do something like this: ALTER TABE test_table ADD INDEX(some_column) while the DB is being accessed. 2. Install some FoxPro or Jet application (or ACT! You'll either laugh or cry after trying this with ACT!). Dump lots of data into it and attempt a reindex whle the DB is being accessed. Guess which DB comes out alive? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE..
That is brilliant thank you. I was doing it the wrong way around when i was naming the fields. Thank you for your help graham -Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 14:46 To: Graham Little Cc: '[EMAIL PROTECTED]' Subject: Re: LOAD DATA INFILE.. On Mon, 15 Dec 2003, Graham Little wrote: doing selective quoting below. LOAD DATA INFILE D:\mysql\sql\CountryData.txt INTO TABLE cou (id, country); See how you try to load from a file into the columns id and country in the cou table? The table the data is being inserted into is: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | cou_id | int(11) | | PRI | 0 | | | cou_name | text| | | | | +--+-+--+-+-+---+ And those columns doesn't exist. I am getting the following error message and i am not sure why? Error 1054: Unknown column 'id' in 'field list' Any help would be appreciated. Hope that helps. This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone using dirty reads?
Hi all, I was sitting here thinking to myself (which can be quite dangerous) and was wondering if anyone on the list actually uses dirty reads in their apps. If so, what advantages do you get from using this isolation level? I can't think of any myself (damned limited brain...) Best regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documentation bug?
On Mon, Dec 15, 2003 at 02:12:01PM +1100, Chris Nolan wrote: It seems slightly ambiguous - updates are redirected and stalled. The fact that the two statements are in different sentences threw me off slightly. Oh, okay. If you can suggest a more clear version, perhaps Paul will update the manual with it? Jeremy On Mon, 2003-12-15 at 03:55, Jeremy Zawodny wrote: On Mon, Dec 15, 2003 at 02:58:53AM +1100, Chris Nolan wrote: Hi all, while reading through some of the MySQL docs, I noticed the following paragraph: |ALTER TABLE| works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While |ALTER TABLE| is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready. This seems a bit confusing. On one hand, it says that updates don't fail, but on the other hand it says they are stalled until ALTER TABLE is done executing. Am I going blind/loosing my mind (a possibility I am open to) or do others agree with me? What exactly is the discrepancy you see? Can you be explicit? The manual describes the way ALTER TABLE works accurately. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb in production
Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Firebird/Interbase have all those nice things like row-level locking (although it doesn't seem to have multiversioning like InnoDB, PostgreSQL or Oracle), deadlock detection, prepared statements, views, Yes it DOES have multi-versioning. Actually, I believe it was the first (InterBase that is) multi-versioning engine around! With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documentation bug?
Jeremy Zawodny wrote: On Mon, Dec 15, 2003 at 02:12:01PM +1100, Chris Nolan wrote: It seems slightly ambiguous - updates are redirected and stalled. The fact that the two statements are in different sentences threw me off slightly. Oh, okay. If you can suggest a more clear version, perhaps Paul will update the manual with it? Jeremy Hmm...how about something like: ALTER TABLE workds by making a temporary copy of the original table. The alteration is performed on the copy, after which the original table is deleted and the copy renamed. During this process, updates do not explicitly failed but are instead stalled until the alteration is complete. Clients wishing to read from a table currently being altered are able to do so as per usual. My background is in software design, not documentation. Additionally, one of my clients asked me to fix a SCO OpenServer crash for them today and wouldn't let me use a chainsaw or sledgehammer, so I'm a bit out of it. Best regards, Chris On Mon, 2003-12-15 at 03:55, Jeremy Zawodny wrote: On Mon, Dec 15, 2003 at 02:58:53AM +1100, Chris Nolan wrote: Hi all, while reading through some of the MySQL docs, I noticed the following paragraph: |ALTER TABLE| works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While |ALTER TABLE| is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready. This seems a bit confusing. On one hand, it says that updates don't fail, but on the other hand it says they are stalled until ALTER TABLE is done executing. Am I going blind/loosing my mind (a possibility I am open to) or do others agree with me? What exactly is the discrepancy you see? Can you be explicit? The manual describes the way ALTER TABLE works accurately. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
InnoDB is extremely stable! I have a single InnoDB database that's currently holding about 20GB (with about 95% of that in a single table). All of this database is contained inside a single InnoDB tablespace file. In the last 12 months, the only command I've thrown at it by hand was ALTER TABLE messageblks TYPE = InnoDB, and only because I wanted to see how long it would take. Hope this helps! Regards, Chris Nicolas Ross wrote: Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
Is innodb stable enough to use un mass production environement ? Yes, it's. We use it on a 24x7 system (replicated), with 20GB w/no issues. We're using 4.0.16 on NetWare6.5. Eduardo - Original Message - From: Nicolas Ross [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:13 PM Subject: Innodb in production Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- 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: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Martijn Tonies wrote: Firebird/Interbase have all those nice things like row-level locking (although it doesn't seem to have multiversioning like InnoDB, PostgreSQL or Oracle), deadlock detection, prepared statements, views, Yes it DOES have multi-versioning. Actually, I believe it was the first (InterBase that is) multi-versioning engine around! With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Really? I feel smarter already! Do you have any documentation regarding Firebird's workings that you would recommend looking through (of similar or deeper detail than PostgreSQL's, MySQL's and the stuff in the internals.texi file that comes with 4.1.1). If I can avoid reading source code though, I'd like to at this point (wxWindows programming has been getting to me lately). Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
Hi, How are you doing backups??? Since it's replicated, I stop the slave and copy the entire database directory. Then, I restart the server and the slave will get all the queries from the master... Works very well! Eduardo - Original Message - From: Arnoldus Th.J. Koeleman [EMAIL PROTECTED] To: 'Eduardo D Piovesam' [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:38 PM Subject: RE: Innodb in production How are you doing backups??? -Original Message- From: Eduardo D Piovesam [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 10:31 AM To: Nicolas Ross; [EMAIL PROTECTED] Subject: Re: Innodb in production Is innodb stable enough to use un mass production environement ? Yes, it's. We use it on a 24x7 system (replicated), with 20GB w/no issues. We're using 4.0.16 on NetWare6.5. Eduardo - Original Message - From: Nicolas Ross [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:13 PM Subject: Innodb in production Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- 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]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Hi Chris, Firebird/Interbase have all those nice things like row-level locking (although it doesn't seem to have multiversioning like InnoDB, PostgreSQL or Oracle), deadlock detection, prepared statements, views, Yes it DOES have multi-versioning. Actually, I believe it was the first (InterBase that is) multi-versioning engine around! Really? I feel smarter already! Do you have any documentation regarding Firebird's workings that you would recommend looking through (of similar or deeper detail than PostgreSQL's, MySQL's and the stuff in the internals.texi file that comes with 4.1.1). If I can avoid reading source code though, I'd like to at this point (wxWindows programming has been getting to me lately). Well, there's usually lots of documentation available at www.ibphoenix.com but the site seems to be down for some reason. For a quick reference on the history of InterBase (and Firebird): http://www.cvalde.net/IbRoadmap.htm And for fun reading: http://www.cvalde.net/misc/how_appeared.htm Wanna know who invented BLOBs? http://www.cvalde.net/misc/blob_true_history.htm As you perhaps know, Firebird is relatively new to the Open Source market, but as it started from the InterBase 6 source code, it does have quite the history :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Replication
I sent this out friday, but didn't see it come through to the list, so sorry if it comes up twice if the original is lost in lala land at the moment. -Original Message- From: Luc Foisy Sent: Friday, December 12, 2003 4:17 PM To: MYSQL-List (E-mail) Subject: Replication The scenario we wish to accomplish SERVER1 - Logging DB1 SERVER2 - Logging DB2 Logging DB3 Replicating DB1 from SERVER1 - Logging DB1 SERVER3 - Replicating DB1 from SERVER2 Replicating DB2 from SERVER2 Replicating DB3 from SERVER2 What I am asking is for confirmation that the following my.cnf files would do that. SERVER1 [mysqld] log-bin binlog-do-db=DB1 server-id=1 SERVER2 [mysqld] log-bin master-host=SERVER1 master-user=SERVER2 master-password=password binlog-do-db=DB2 binlog-do-db=DB3 log-slave-updates server-id=2 SERVER3 [mysqld] master-host=SERVER2 master-user=SERVER3 master-password=password binlog-do-db=DB1 binlog-do-db=DB2 binlog-do-db=DB3 server-id=3 To do the initial setup of the slaves I would do the following? Dump DB1 from SERVER1 Start logging of DB1 on SERVER1 Load Dump of DB1 onto SERVER2 Start replication of DB1 on SERVER2 (add replication parameters to my.cnf and restart) Dump DB2 from SERVER2 Dump DB3 from SERVER2 Load Dump of DB1 onto SERVER3 Load Dump of DB2 onto SERVER3 Load Dump of DB3 onto SERVER3 Start replication of DB1 on SERVER3 (add replication parameters to my.cnf and restart) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting data
I have an order taking system where the tables are store in a MySql database. I need to develop a select statement to output all new orders to a .csv formatted file. Is this possible to do in MySql. I would try looking this up, however, I am not even sure what to look for in the documentation. Any help that I could get to lead me in the right direction would be appreciated. Thanks. Mark Roberts
Re: Exporting data
On Mon, 15 Dec 2003, Roberts, Mark (Tulsa) wrote: I have an order taking system where the tables are store in a MySql database. I need to develop a select statement to output all new orders to a .csv formatted file. Is this possible to do in MySql. I would try looking this up, however, I am not even sure what to look for in the documentation. Any help that I could get to lead me in the right direction would be appreciated. Thanks. Mark Roberts http://www.mysql.com/doc/en/SELECT.html Check out the bit about INTO OUTFILE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
I set the isolation level to READ_REPEATABLE and use mysqldump | bzip2 to get the result. I've tested the restore and it's fine! So how does mysqldump handle binary data? If it does embed the data into the SQL-statement somehow, that's crap, since SQL-Statements are limited in length. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Are they? Shoving in rows that are several meg in size didn't pose any problems. The restore procedure looked like this: bunzip2 dumpfile | mysql -u db_grunt -p projectdb May I ask where the limitation you mentioned is documented? Maybe the situations we were using it in didn't come close to the limit. Regards, Chris Sven Köhler wrote: I set the isolation level to READ_REPEATABLE and use mysqldump | bzip2 to get the result. I've tested the restore and it's fine! So how does mysqldump handle binary data? If it does embed the data into the SQL-statement somehow, that's crap, since SQL-Statements are limited in length. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am Sonntag, 14. Dezember 2003 20:59 schrieb Jerry Apfelbaum: Hello. I have been tasked with evaluating open source databases for a large upcoming project: e-commerce, B2B, high availability. So, you should choose SapDB 7.3 or 7.4 due License issues. 7.3 ands 7.4 are GPL/LGPL, since 7.5 aka MaxDB LGPL is dropped, so you are forced to pay for Userlicences. Refer to http://www.mysql.com We are thinking about to fork the SapDB 7.3/7.4 Brances to continue in GPL/LGPL. mfG Jürgen automatiX Linux Support Crew - -- Jürgen Sauer - AutomatiX GmbH, +49-4209-4699, [EMAIL PROTECTED] ** ** Das Linux Systemhaus - Service - Support - Server - Lösungen ** ** http://www.automatix.deICQ: #344389676 ** -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQE/3cjVW7UKI9EqarERAn3DAJ9Z7J+gSVYRc+l+tTKuV5hbgnq15gCfZsVw hoX59ewH2XefYSMIn5rNGGY= =TMeG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Sven Köhler wrote: I set the isolation level to READ_REPEATABLE and use mysqldump | bzip2 to get the result. I've tested the restore and it's fine! So how does mysqldump handle binary data? If it does embed the data into the SQL-statement somehow, that's crap, since SQL-Statements are limited in length. On Tue, 16 Dec 2003, Chris Nolan wrote: Are they? Shoving in rows that are several meg in size didn't pose any problems. The restore procedure looked like this: bunzip2 dumpfile | mysql -u db_grunt -p projectdb May I ask where the limitation you mentioned is documented? Maybe the situations we were using it in didn't come close to the limit. Regards, Chris This is limited by the max_packet_size variable. In 3.23.x it's limited to 16Mb, in 4.0+ it is limited by 2Gb or the amount of physical memory the machine has, whichever is less. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Tobias Asplund wrote: Sven Köhler wrote: I set the isolation level to READ_REPEATABLE and use mysqldump | bzip2 to get the result. I've tested the restore and it's fine! So how does mysqldump handle binary data? If it does embed the data into the SQL-statement somehow, that's crap, since SQL-Statements are limited in length. On Tue, 16 Dec 2003, Chris Nolan wrote: Are they? Shoving in rows that are several meg in size didn't pose any problems. The restore procedure looked like this: bunzip2 dumpfile | mysql -u db_grunt -p projectdb May I ask where the limitation you mentioned is documented? Maybe the situations we were using it in didn't come close to the limit. Regards, Chris This is limited by the max_packet_size variable. In 3.23.x it's limited to 16Mb, in 4.0+ it is limited by 2Gb or the amount of physical memory the machine has, whichever is less. I don't think any of our rows were more than 10 - 20 MB, and we were using 4.0.13 or higher the entire time. If I recall correctly, the way around this is to use InnoDB Hot Backup for InnoDB tables and mysqlhotcopy for MyISAM tables. I guess you're on your own for BDB tables! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to get value of autoincriment after inserting NULL?
Greetinsg. If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? It is possible that additional rows may have been added during the small wait. Ie. Col 1 Col 2 Col 3 AA# SmallText SmallText 123 Foo Bar 124 GoodGuy So my script (PHP) for adding records to the database inserts NULL,text,text into the table but I need to echo back the # that was created! My thought was to maybe grab the last entry in the database before the insert, perform the insert and then query where the file # is greater than that last entry and where the text matches the columns appropriately. I imagine there has to be a better way! Thanks for any help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime index in 4.1.1
Seems like a bug in 4.1.1-alpha using datetime as index.
mysql create table foo (d datetime, index (d));
Query OK, 0 rows affected (0.01 sec)
mysql insert into foo values ('2003-12-15 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql select * from foo where d like '2003%';
Empty set (0.00 sec)
mysql select * from foo;
+-+
| d |
+-+
| 2003-12-15 00:00:00 |
+-+
1 row in set (0.00 sec)
--
Tatsuhiko Miyagawa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
Hi, I imagine there has to be a better way! Yes :) Take a look at the LAST_INSERT_ID() function. Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication inconsistency questions
Hello, I'm currently testing out replication on a high volume innodb based database. This has been working great for several weeks, but when I came in this morning I found my slave had the following error: 031212 23:30:24 Slave: query 'UPDATE monitor_tunnel_cisco_phase_2 SET counter=0, timestamp=NOW() WHERE monitor_id=3735 AND monitor_server_id=1 AND phase_2_id=6290' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; . Error_code: 1053 031212 23:30:24 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'slave_test_binlog.169' position 6404579 I was able to resolve this easily by running the query on the slave, setting the skip counter to 1, and restarting the slave. However I would like to know why this error was caused. I can find no log of any such error on my master. Should a failed query on the master have shown up in my /var/log/mysql/mysql.err? What would cause the query to have partially completed and aborted? Everything seems fine on the master so I'm not sure why the query was aborted. My master is running 4.0.13 and my slave is running 4.0.16 could this be the cause of the error? John A. McCaskey Software Development Engineer IP Sciences, Inc. [EMAIL PROTECTED] 206.633.0449
Re: Questions about indexing
With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. Well the database is going to be like 200MB and executing several hundred queries a minute. Thus my concern about speed. Is a P4 w/ 1GB RAM going to choke and die, or will indexing help out? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime index in 4.1.1
Oops, I didn't mean datetime as index, but like with datetime is
broken in 4.1.1.
On Tue, 16 Dec 2003 03:12:20 +0900
Tatsuhiko Miyagawa [EMAIL PROTECTED] wrote:
Seems like a bug in 4.1.1-alpha using datetime as index.
mysql create table foo (d datetime, index (d));
Query OK, 0 rows affected (0.01 sec)
mysql insert into foo values ('2003-12-15 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql select * from foo where d like '2003%';
Empty set (0.00 sec)
mysql select * from foo;
+-+
| d |
+-+
| 2003-12-15 00:00:00 |
+-+
1 row in set (0.00 sec)
--
Tatsuhiko Miyagawa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Tatsuhiko Miyagawa [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
since you're using PHP, you can also get this via the php function mysql_insert_id(). directly after your insert, i think another insert would be nearly impossible to get int he middle of these two. $insert = mysql_query(insert stuff into table); $last_id = mysql_insert_id($res_link); (resource link is optional). hth Jeff Aleksandar Bradaric To: Paul Fine [EMAIL PROTECTED] [EMAIL PROTECTED]cc: [EMAIL PROTECTED] net Subject: Re: Best way to get value of autoincriment after inserting NULL? 12/15/2003 01:13 PM Please respond to Aleksandar Bradaric Hi, I imagine there has to be a better way! Yes :) Take a look at the LAST_INSERT_ID() function. Take care, Aleksandar -- 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: Questions about indexing
Find out the real way: Use EXPLAIN and BENCHMARK() commands to get the speed of your operations with and without an index. That's the only way to know for certain. Cheers, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:18 PM To: Chris Elsworth Cc: [EMAIL PROTECTED] Subject: Re: Questions about indexing With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. Well the database is going to be like 200MB and executing several hundred queries a minute. Thus my concern about speed. Is a P4 w/ 1GB RAM going to choke and die, or will indexing help out? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello, Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Yes, it could. It is the optimization that wasn't implemented yet. (but it's in the TODO) Once again, thanks for the response. Could you give an estimation (even if rough) of how soon this optimization will be implemented? Thanks, TK Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
On Mon, 15 Dec 2003, Paul Fine wrote: If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? If you insert a row LAST_INSERT_ID() will return the primary key value in this setup. The other way is if you're not inserting a record, but just want to know the next value that will be used. SHOW TABLE STATUS LIKE 'tablename'; It is possible that additional rows may have been added during the small wait. It is possible another value is inserted in between the query for the next number and an insert afterwards. To stop this from happening you can place a read-lock on the table, this won't allow any other processes to insert rows until you release the lock. LOCK TABLE tablename READ; Get Auto-increment value Do your stuff... UNLOCK TABLES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELLLLP! Databases No Longer Accessible
I seem to be locked out. The trouble started when I deleted the Any user while in phpMyAdmin. After doing that, I reloaded MySQL. MySQL seems to load from the command line using the command mysqld_safe, except the command prompt doesn't return unless I hit control Z. Once I ^Z myself back to the command line, I can access MySQL via only one username. And when I exit as root, the stopped jobs message appears. When I exit all the way, MySQL unloads and the Web sites become inaccessible. I tried following the procedures Paul DuBois' book MYSQL, including bypassing the GRANT tables. I know I must be missing something basic. Help would be appreciated! Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Status of bugs
Hello, Last month I posted here a question (with no responses) about a problem I am having with myisamchk (myisamchk Error 22 WinServer 2003 Large table). My problem seems to be identical to that described here: http://bugs.mysql.com/bug.php?id=779 However, that bug was for the 3.x version of MySQL and I am experiencing the problem with 4.x. The bug has been marked as closed, with the comment that the fix has been incorporated in the code. Is there any way short of paying $1500 to find out what the status of this bug is in version 4? I'm not exactly asking for charity - my company has bought 4 licenses. I know that the source code is available for me to fix it myself, but I'm not a database programmer, or even a C programmer, so that isn't a reasonable option. I don't have any kind of C development environment set up. -Jim Gallagher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best way to get value of autoincriment after inserting NULL?
Thanks (to all who replied) If I lock the table however, if another user is trying to insert (via php page) another record they will get an error right and I will need to make a wait+retry script? Thanks! -Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 12:56 PM To: Paul Fine Cc: [EMAIL PROTECTED] Subject: Re: Best way to get value of autoincriment after inserting NULL? On Mon, 15 Dec 2003, Paul Fine wrote: If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? If you insert a row LAST_INSERT_ID() will return the primary key value in this setup. The other way is if you're not inserting a record, but just want to know the next value that will be used. SHOW TABLE STATUS LIKE 'tablename'; It is possible that additional rows may have been added during the small wait. It is possible another value is inserted in between the query for the next number and an insert afterwards. To stop this from happening you can place a read-lock on the table, this won't allow any other processes to insert rows until you release the lock. LOCK TABLE tablename READ; Get Auto-increment value Do your stuff... UNLOCK TABLES; -- 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: Status of bugs
At 11:34 15/12/2003 -0800, Jim Gallagher wrote: Hi, Hello, Last month I posted here a question (with no responses) about a problem I am having with myisamchk (myisamchk Error 22 WinServer 2003 Large table). My problem seems to be identical to that described here: http://bugs.mysql.com/bug.php?id=779 I can confirm you that the bug #779 was fixed. However you are saying that is similar and maybe it isn't. Please be free for to open a bug case, explaining your case with the structure of the tables, what it is exactly the error message you got and if possible with a repeatable test case. -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
On Mon, Dec 15, 2003 at 01:42:30PM -0600, Paul Fine wrote: Thanks (to all who replied) If I lock the table however, if another user is trying to insert (via php page) another record they will get an error right and I will need to make a wait+retry script? _if_ you lock the other threads will wait. But you really do not need to lock the whole table. last_insert_id() works (just as the corresponding PHP function) on a _per connection_ base. See the manual @ http://mysql.com/doc/en/Miscellaneous_functions.html - LAST_INSERT_ID good luck, Harmen Thanks! -Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 12:56 PM To: Paul Fine Cc: [EMAIL PROTECTED] Subject: Re: Best way to get value of autoincriment after inserting NULL? On Mon, 15 Dec 2003, Paul Fine wrote: If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? If you insert a row LAST_INSERT_ID() will return the primary key value in this setup. The other way is if you're not inserting a record, but just want to know the next value that will be used. SHOW TABLE STATUS LIKE 'tablename'; It is possible that additional rows may have been added during the small wait. It is possible another value is inserted in between the query for the next number and an insert afterwards. To stop this from happening you can place a read-lock on the table, this won't allow any other processes to insert rows until you release the lock. LOCK TABLE tablename READ; Get Auto-increment value Do your stuff... UNLOCK TABLES; -- The Moon is Waning Gibbous (60% of Full) tty.nl - ericsson.2dehands.nl: 109313 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELLLLP! Databases No Longer Accessible
This may sound crazy, and maybe paul would kill me but try this :) 1.- backup your data base files, just for moment, while we recover the users data base 2.- erase your data base file completly 3.- run mysql_install_db 4.- restore your data bases, only _YOUR_ data bases, do not restore mysql internal data bases 5.- recreate your users using the normal procedure it seems you delete ALL mysql users, this will give you a fresh restart, lossing users, but not data Hope this works! On Mon, 2003-12-15 at 15:06, Bob Cohen wrote: I seem to be locked out. The trouble started when I deleted the Any user while in phpMyAdmin. After doing that, I reloaded MySQL. MySQL seems to load from the command line using the command mysqld_safe, except the command prompt doesn't return unless I hit control Z. Once I ^Z myself back to the command line, I can access MySQL via only one username. And when I exit as root, the stopped jobs message appears. When I exit all the way, MySQL unloads and the Web sites become inaccessible. I tried following the procedures Paul DuBois' book MYSQL, including bypassing the GRANT tables. I know I must be missing something basic. Help would be appreciated! Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date_format and DECODE Problem
Hi, i have a problem with getting the right return values from field purrdato. All records exept a few are set with date and the rest is NULL. The field allows Null values. when using: DATE_FORMAT(purrdato, '%d.%m.%Y') AS purrdato ...it even returns '00.00.' on null values. Is there a way of solving this with DECODE??? I vould like it to return '' instead of '00.00.000' for null values. This is the sql: SQL = SELECT OrgNr, Firmanavn, Kontakt, Sted, Telefon, Ansatte, eier, Slettet, aktiv, DATE_FORMAT(purrdato, '%d.%m.%Y') AS purrdato, TIME_FORMAT(purrtid, '%H:%i') AS purrtid FROM Kunder WHERE eier LIKE ' Node.Key ' ORDER BY Slettet, purrdato, purrtid, aktiv, Postnr, FirmaNavn Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom
RE: integer not being inserted correctly
If an INT has a fixed range, then what is the point of giving it scale? As in, int(12). In Oracle, a NUMBER(12) indicates how many digits you could have (in this case, 999 would be the max value). Would an int(2) allow -99 to 99, or -2147483648 to 2147483647? That's because in Oracle NUMBER is a primitive or internal type and has 38 digits of precision. Everything else including INTEGER is a subtype of NUMBER. So in Oracle, you must limit the subtype or you'll have an INTEGER that can store a number up to like 10^125. For example, the subtype BINARY_INTEGER is a signed int with range -2147483648 to 2147483647. -- Michael Brando Senior Manager of Engineering Applied Biosystems 3833 North First Street San Jose, CA 95134-1701 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Zeos, MySQL problem
Good Afternoon! We're building an application with Delphi 7.0, the Zeos controls and MySQL v.4.0.15 which runs on a Red Hat 7.3 server. We are experiencing a problem where when a person lets the application sit for a while, we're losing dataset connectivity that doesn't restore itself. I've been studying the documentation and I'm not finding any settings that would stop the timeout from happening. If anyone is aware of what to change, or has experienced the same problem, please let me know. BTW, I wasn't sure that this would be the place to post this sort of question and would appreciate some guidance where I should go instead if necessary. Thanks! David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Using matrix.
Hi, is it possible to represent on MySQL a Matrix as data type into a table creation? Thanks, Nivaldo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Zeos, MySQL problem
You can disable connection timeouts at the MySQL server level. You should check the docs to be sure, but I think the relevant variable is wait_timeout -- setting it to 0 should disable connection timeouts. -JF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 12:28 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Zeos, MySQL problem Good Afternoon! We're building an application with Delphi 7.0, the Zeos controls and MySQL v.4.0.15 which runs on a Red Hat 7.3 server. We are experiencing a problem where when a person lets the application sit for a while, we're losing dataset connectivity that doesn't restore itself. I've been studying the documentation and I'm not finding any settings that would stop the timeout from happening. If anyone is aware of what to change, or has experienced the same problem, please let me know. BTW, I wasn't sure that this would be the place to post this sort of question and would appreciate some guidance where I should go instead if necessary. Thanks! David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: foreign keys.
Hi, I don't know much about the foreign key syntax, but I would think it should reference a field in a different table. If I'm wrong, I'm sorry. :-) Bob -Original Message- From: Mofeed Shahin [mailto:[EMAIL PROTECTED] Sent: Sunday, December 14, 2003 9:56 PM To: Paul DuBois; [EMAIL PROTECTED] Subject: Re: foreign keys. On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Mof. -- 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: Zeos, MySQL problem
Thanks, Jon. I'll try that and report back. -Original Message- From: Jon Frisby [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 3:06 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Zeos, MySQL problem You can disable connection timeouts at the MySQL server level. You should check the docs to be sure, but I think the relevant variable is wait_timeout -- setting it to 0 should disable connection timeouts. -JF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 12:28 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Zeos, MySQL problem Good Afternoon! We're building an application with Delphi 7.0, the Zeos controls and MySQL v.4.0.15 which runs on a Red Hat 7.3 server. We are experiencing a problem where when a person lets the application sit for a while, we're losing dataset connectivity that doesn't restore itself. I've been studying the documentation and I'm not finding any settings that would stop the timeout from happening. If anyone is aware of what to change, or has experienced the same problem, please let me know. BTW, I wasn't sure that this would be the place to post this sort of question and would appreciate some guidance where I should go instead if necessary. Thanks! David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
select last_insert_id(); or in php use the mysql_insert_id() eg: $somevalue = mysql_insert_id(); print (The last auto incremented number was: $somevaluebr\n); Cheers Curtis Paul Fine said: Greetinsg. If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? It is possible that additional rows may have been added during the small wait. Ie. Col 1 Col 2 Col 3 AA# SmallText SmallText 123 Foo Bar 124 GoodGuy So my script (PHP) for adding records to the database inserts NULL,text,text into the table but I need to echo back the # that was created! My thought was to maybe grab the last entry in the database before the insert, perform the insert and then query where the file # is greater than that last entry and where the text matches the columns appropriately. I imagine there has to be a better way! Thanks for any help! -- 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]
Performance Question
How hard would it be to modify the MySQL code, and what sort of performance hits would I take, if I was to try to run a hundred thousand MySQL DBs on the same server? Obviously, some tweaks would be necessary to be able to break up the directory structure of /var/lib/mysql, and I may be souding uninformed, but would there be much to do beyond that? Kind of a pie in the sky question, I know. -Rob- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SORTing / LIMITing on max(fieldname) blah-blahT
. My apologies for taking so long to get back to you. The MasterMind in charge of the project kept me too busy to experiment. Now that I have, thank you! It went well, and all is now sweetness and light. Yrs, Iain. fatblokeonbike wrote: . I'm seeing double with this, and I just KNOW it's got to be simple - The table images contains columns: id | reference_number | image | width | height That's all it contains, nothing else. id is auto-increment reference_number is usually set anew by the operator to input new data, but sometimes he returns to an earlier one to change data. Each new reference_number will not always be higher than the previous one. The reference_number being worked will not always be the highest in its sequence. There are always eight images per reference_number, numbered 01 to 08, with their different widths and heights. All types are integer. As the operator inputs new data for the reference_number he's just newly set, or returned to, I want his monitor to display, on-the-run, all the data he's input for the reference_number he's working (even if the reference_number is newly set, he's just input image 01 and there are no images 02 to 08 yet) and order it by increasing image number. Things like: SELECT id, image, width, height, max(id) AS top FROM images WHERE reference_number=top ORDER BY image one of a seeming hundred variants that I've tried, plus others with LIMIT and HAVING, just give MySQL indigestion. (Perhaps I'm over-egging the pudding with the description, but it's helping me make sure I have it right.) I really need a holiday... Any thoughts? (No, not where I should go for the holiday!) T.I.A. If I understand you correctly, you need the id of the last insert/update so you can pull all the rows with the same reference_number. You can get the last inserted/updated id with the LAST_INSERT_ID function, use that to recover the reference_number, then get the desired rows. Try this: SELECT @ref:=reference_number FROM images WHERE id=LAST_INSERT_ID(); SELECT * FROM images WHERE [EMAIL PROTECTED] ORDER BY image; Or, you could do it in a single select with a join: SELECT im1.id, im1.image, im1.width, im1.height FROM images AS im1, images AS im2 WHERE im1.reference_number = im2.reference_number AND im2.id = LAST_INSERT_ID() ORDER BY i1.image; See http://www.mysql.com/doc/en/Miscellaneous_functions.html for more on LAST_INSERT_ID(). Hope that helps. If that's not what you meant, give an example showing how the output should look to help us see what you want. Michael -- 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: Performance Question
On Mon, Dec 15, 2003 at 02:31:16PM -0800, Rob Brackett wrote: How hard would it be to modify the MySQL code, and what sort of performance hits would I take, if I was to try to run a hundred thousand MySQL DBs on the same server? Obviously, some tweaks would be necessary to be able to break up the directory structure of /var/lib/mysql, and I may be souding uninformed, but would there be much to do beyond that? Or you could use a filesystem that's smarter about large directories. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 92 days, processed 3,790,567,928 queries (472/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
On Mon, 15 Dec 2003 09:22 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Paul's example works fine for me. What version of MySQL do you use? yeah, sorry Paul's example works here as well. But the actual create statement I'm using here is failing. My create statement is stightly different, and I didn't think it would make a difference (ooops!!). So here is the actual create statement that is failing ; CREATE TABLE Blah ( ID INT PRIMARY KEY, Fname VARCHAR (50), Lname VARCHAR (50), UNIQUE (Fname, Lname) ) TYPE=INNODB; CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; Once again sorry for the confusion. It must be a problem with adding the second foreign key. Mof. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Hi, I've used both PostgreSQL and MySQL on a Linux server and found both setting up (using RPM) and maintaining them very easy (MySQL was slightly easier to set up but I set it up after I was already proficient on PostgreSQL, so perhaps comparing the ease of setup is unfair). Both are well documented and each have very active mailing lists where list subscribers are helpful and quick to respond. I have no idea how the commercial support services compare in quality and price. I have not used the more 'advanced' features, that you mentioned, of either (such as: 'Triggers, Stored Procedures, User-Defined Functions') so can't comment on them. Backups on both are straightforward using pg_dump and mysqldump. Both of these allow you to dump databases as files containing the relevant sql commands to recreate the entire database quickly and easily. Another responder mentioned a possible problem dumping MySQL databases containing binary data, I have no idea whether this is an issue with pg_dump as well, as I have no tables at all with binary data, but suspect dumping these kind of backup files is generally inconsistent with binary data (unless the dumping utilities do something clever, which I don't know about, like uuencoding binary data). Anyway both servers can be backed up by copying the actual database table files (on the local linux filesystem). To achieve this the PostgreSQL server must be shut down (making it an inferior backup technique to dumping which does not require a shutdown). MySQL, however, has something called 'mysqlhotcopy' which will lock and flush tables and copy the files using 'cp' and does not require a server shutdown, it allows queries by different threads to continue, blind to the backup in progress. --I must admit to being a bit wary of this as I don't know enough about the underlying file systems and so personally have no intention of using mysqlhotcopy. --Perhaps someone that knows more can explain whether this is always safe to use and why?? Both PostgreSQL and MySQL have free GUI frontends (pgAdmin and Control Center, respectively) that I have used on Windows 2000; both have been adequate for my basic needs, EG. Quick database and table creation and deletion, sql commands, and basic administration such as vacuuming (PostgreSQL) and optimizing (MySQL). Of these PostgeSQL's is better (far more features), while MySQL's is still at 0.9.3 beta (stable but feature poor). My use of both has been low scale and is client program orientated using .net and C# from Win2000. The ODBC drivers for Windows both function well. The only annoying thing I can think of, from a programming perspective, is MySQL's lack of a Boolean type - the manual says use TINYINT(1) which works fine but is slightly annoying because of the extra type conversion needed every time you use it. Apparently MySQL will be implementing the Boolean type soon in accordance with whatever SQL standard requires it. Quite why it still has not been implemented, even though MySQL is into version 4, I have no idea - as a programmer I find this a staggering omission but presumably they have their reasons and perhaps most people are happy with TINYINT(1), but for clarity of code TINYINT(1) is inferior to a Boolean type. I hope this helps, ..matthew Jerry Apfelbaum wrote: Hello. I have been tasked with evaluating open source databases for a large upcoming project: e-commerce, B2B, high availability. The O/S is most likely to be Linux, although FreeBSD could possibly be used (lower probability). So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are possible candidates. Does anyone know why we should or should not use any of these? Does anyone know of other possibilities? Id very much appreciate hearing your comments and recommendations. I have only recently started these evaluations. BTW, my own background is from the Oracle DBA world. MySQL is certainly popular and seems to have very good performance, but I am concerned that the lack of Triggers, Stored Procedures, User-Defined Functions, and Views (to a lesser degree ) will be a disadvantage. MaxDB appears to be more feature-rich and possibly more industrial-strength. How does its performance and stability compare to the others? Many Thanks. Jerry Apfelbaum Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Matthew Stanfield said:
Hi,
Usually, i'll use enum('0','1') in place of a boolean type.
Curtis
[snip]
well. The only annoying thing I can think of, from a programming
perspective, is MySQL's lack of a Boolean type - the manual says use
TINYINT(1) which works fine but is slightly annoying because of the
extra type conversion needed every time you use it. Apparently MySQL
will be implementing the Boolean type soon in accordance with whatever
SQL standard requires it. Quite why it still has not been implemented,
even though MySQL is into version 4, I have no idea - as a programmer I
find this a staggering omission but presumably they have their reasons
and perhaps most people are happy with TINYINT(1), but for clarity of
code TINYINT(1) is inferior to a Boolean type.
I hope this helps,
..matthew
Jerry Apfelbaum wrote:
Hello.
I have been tasked with evaluating open source databases for a large
upcoming project: e-commerce, B2B, high availability.
The O/S is most likely to be Linux, although FreeBSD could possibly be
used (lower probability).
So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird
are possible candidates.
Does anyone know why we should or should not use any of these? Does
anyone know of other possibilities?
Id very much appreciate hearing your comments and recommendations.
I have only recently started these evaluations. BTW, my own
background is from the Oracle DBA world.
MySQL is certainly popular and seems to have very good performance,
but I am concerned that the lack of Triggers, Stored Procedures,
User-Defined Functions, and Views (to a lesser degree ) will be a
disadvantage.
MaxDB appears to be more feature-rich and possibly more
industrial-strength. How does its performance and stability compare
to the others?
Many Thanks.
Jerry Apfelbaum
Toronto
--
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: Anyone using dirty reads?
On Tue, Dec 16, 2003 at 01:55:49AM +1100, Chris Nolan wrote: Hi all, I was sitting here thinking to myself (which can be quite dangerous) and was wondering if anyone on the list actually uses dirty reads in their apps. If so, what advantages do you get from using this isolation level? I can't think of any myself (damned limited brain...) I almost did once. The idea was to provide a way for someone to use a web interface to monitory what's happening *right now* rather than only stuff that's finished. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb in production
On Mon, Dec 15, 2003 at 10:13:44AM -0500, Nicolas Ross wrote: Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. FYI, that restriction is gone in MySQL 4.1.x. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Question
In the last episode (Dec 15), Jeremy Zawodny said: On Mon, Dec 15, 2003 at 02:31:16PM -0800, Rob Brackett wrote: How hard would it be to modify the MySQL code, and what sort of performance hits would I take, if I was to try to run a hundred thousand MySQL DBs on the same server? Obviously, some tweaks would be necessary to be able to break up the directory structure of /var/lib/mysql, and I may be souding uninformed, but would there be much to do beyond that? Or you could use a filesystem that's smarter about large directories. InnoDB may help here also. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Records where date is LESS THAN today, AND...
Okay, that sounds good and all, but how does that help me, since the date is chosen from the javascript calander in this format: mm/dd/ So then, when I'm selecting a date of at least todays value, or less in the database how would I do it, since it's in mm/dd/? Thanks, Richard - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 2:47 AM Subject: Re: Getting Records where date is LESS THAN today, AND... Is there a BETTER way to do this? Yes, store a DATE instead of seperate day/month/year values. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- 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: Performance Question
You may have to increase the size of the table cache, and you will most probably need to do something about ensuring that the mysqld process can open about 1 billion files at the same time. There was a discussion along these lines not so long ago focusing on having massive numbers of tables that was encountering problems around performance due to the above reasons. Regards, Chris Jeremy Zawodny wrote: On Mon, Dec 15, 2003 at 02:31:16PM -0800, Rob Brackett wrote: How hard would it be to modify the MySQL code, and what sort of performance hits would I take, if I was to try to run a hundred thousand MySQL DBs on the same server? Obviously, some tweaks would be necessary to be able to break up the directory structure of /var/lib/mysql, and I may be souding uninformed, but would there be much to do beyond that? Or you could use a filesystem that's smarter about large directories. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about indexing
Even if your database fits entirely in memory, not having indexes in place would not be a good idea. In an interview Monty did regarding in-memory databases, he very specifically made the point that where your database is sitting will never remove the need for various types of index. From some extremely boring computer science/software engineering subject I did a few years back: 1. If there's no index to play with, you'll need to do a full table scan to find the rows matching the conditions predicated in your WHERE clause. 2. If there is an index or two to play with, you'll at the very least cut down the number of rows that will be read from disk for further inspection should the optimiser choose to use them (look up FORCE INDEX(...) in MySQL) 3. For B-Tree and B+Tree indexes, you're looking at an average of 1-2 probes (with a probe being roughly the same expense I/O-wise as reading a row) for finding an item matching an = condition and the same for finding the beginning/end of a range for range-style queries (eg: ... WHERE a 20 AND a 40). Hope this helps! Regards, Chris Joshua Thomas wrote: Find out the real way: Use EXPLAIN and BENCHMARK() commands to get the speed of your operations with and without an index. That's the only way to know for certain. Cheers, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:18 PM To: Chris Elsworth Cc: [EMAIL PROTECTED] Subject: Re: Questions about indexing With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. Well the database is going to be like 200MB and executing several hundred queries a minute. Thus my concern about speed. Is a P4 w/ 1GB RAM going to choke and die, or will indexing help out? Thanks in advance, Dan -- 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]
