On Nov 13, 2007 3:32 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> I found the Oracle reference and it says that locks can never lock
> queries, so reading a table is possible in any case.
No, you just have to use FOR UPDATE and it will block.
- Perrin
--
MySQL General Mailing List
For list arc
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote:
> -- cxn 2
>
> set autocommit=0;
> begin;
> select * from t1;
> -- hangs
Delete my last message. I just did it again and now it works, too. I
have no idea what I did a couple of minutes ago, but it must have been
wrong.
Okay. Works, too. I was do
On 13.11.2007 20:57 CE(S)T, Baron Schwartz wrote:
> It will absolutely lock SELECTs. Are you sure autocommit is set to 0
> and you have an open transaction? Are you sure your table is InnoDB?
> I'm doing this right now:
>
> -- cxn 1
> mysql> set autocommit=0;
> mysql> begin;
> mysql> select *
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote:
> Yves Goergen wrote:
>> I assume that at this point, any SELECT on the table I have locked
>> should block. But guess what, it doesn't. So it doesn't really lock.
>>
>
> What kind of lock are you using?
>
> -- cxn 1
>
> set autocommit=0;
> begin
Yves Goergen wrote:
On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote:
"You can use next-key locking to implement a uniqueness check in your
application: (...)
http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html
This doesn't help my problem either. It may lock new INSERTs to the
On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote:
> "You can use next-key locking to implement a uniqueness check in your
> application: (...)
> http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html
This doesn't help my problem either. It may lock new INSERTs to the
table, but it won
Thank you so much!
On Nov 13, 2007 2:40 PM, Craig Huffstetler <[EMAIL PROTECTED]> wrote:
> No, but a table lock or two may be expected. This is to PREVENT data loss
> (which you were also worried about).
>
> The mysqldump process will most likely be quick and painless (quick being a
> relative ter
Yves Goergen wrote:
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote:
It's more complicated than that. You can use them together, you just
have to do it like this:
set autocommit = 0;
begin;
lock tables;
-- you are now in a transaction automatically begun by LOCK TABLES
.
I assume that
On a *nix box, it is also traditional to configure IPTABLES or similar to
restrict TCP/UDP connections based on IP and/or adapter.
It seems likely based on your description that the box has two network
connections.
Dave.
On 11/13/07, Michael Dykman <[EMAIL PROTECTED]> wrote:
>
> In my.cnf, you c
No, but a table lock or two may be expected. This is to PREVENT data loss
(which you were also worried about).
The mysqldump process will most likely be quick and painless (quick being a
relative term, depending on the amount of data in your database(s)).
Craig
On Nov 13, 2007 2:35 PM, Naufal Sh
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote:
> It's more complicated than that. You can use them together, you just
> have to do it like this:
>
> set autocommit = 0;
> begin;
> lock tables;
> -- you are now in a transaction automatically begun by LOCK TABLES
> .
I assume that at this
So is it safe to take the dump while database is running. I mean is
there any loss of data expected because of taking dump while a
database is running.
On Nov 13, 2007 2:26 PM, Michael Dykman <[EMAIL PROTECTED]> wrote:
>
> On Nov 13, 2007 2:11 PM, Naufal Sheikh <[EMAIL PROTECTED]> wrote:
> > Hello
On 11/13/07, Hiep Nguyen <[EMAIL PROTECTED]> wrote:
>
> hi there,
>
> i have a text file that i prepare:
>
> insert into `sa2007` (`id`,`amount`,`state`) values
> ('','1.00','oh'),
> ('','2.00','il'),
> ('','4.00','ks')
>
> how do i import this file to sa2007 table from the command line? i tried
>
On Nov 13, 2007 2:11 PM, Naufal Sheikh <[EMAIL PROTECTED]> wrote:
> Hello everyone,
>
> Few conceptual questions which I can't understand. If any one can
> please gimme a a quicky!
>
> Am I correct when I say that "mysqldump' only works when the database
> is up and running? and if it is true can a
Hello everyone,
Few conceptual questions which I can't understand. If any one can
please gimme a a quicky!
Am I correct when I say that "mysqldump' only works when the database
is up and running? and if it is true can any one please tell me that
does taking a dump when a database is running is ok
In my.cnf, you can specify a 'bind-address'. When used it will cause
the listener to only be available to host on that same network
ie. one of your database host's ip binding is 10.10.10.66/255.255.0.0
# this will list the server to respond only to hosts in the 10.10.x.x
range, all other (includ
On Nov 13, 2007 11:39 AM, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> InnoDB can also lock the gap, which will prevent new rows that would
> have been returned by the SELECT. The manual has more info on this in
> the section on consistent reads in InnoDB. FOR UPDATE will do what you
> need.
Inte
hi there,
i have a text file that i prepare:
insert into `sa2007` (`id`,`amount`,`state`) values
('','1.00','oh'),
('','2.00','il'),
('','4.00','ks')
how do i import this file to sa2007 table from the command line? i tried
via phymyadmin, but it doesn't work (300 seconds timeout).
thnx,
T.
Yves Goergen wrote:
(Damn I hate those lists that don't come with a Reply-To to the list!
Resending...)
On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote:
Yves Goergen wrote:
Row level locking can only lock rows that exist. Creating new rows (that
would have an influence on my MAX value) are st
On Nov 13, 2007, at 1:25 AM, yaya sirima wrote:
Hi,
CREATE TABLE Test (
COL1INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
COL2VARCHAR(10) NOT NULL,
COL3VARCHAR(10) NOT NULL,
UNIQUE(COL2, COL3); --(not that)
FULLTEXT(col1,col2)
);
(Damn I hate those lists that don't come with a Reply-To to the list!
Resending...)
On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote:
> Yves Goergen wrote:
>> Row level locking can only lock rows that exist. Creating new rows (that
>> would have an influence on my MAX value) are still possible and
> Dave:
> There are no uncommented entries in /etc/hosts.deny
>
> Baron:
> The all servers have a unique server-id in their respective my.cnf's
>
> When I try to connect directly from the slave to the new master, I get:
>
> ERROR 2003 (HY000): Can't connect to MySQL server on '1xx.1xx.1xx.xx'
(11
David Campbell wrote:
> [EMAIL PROTECTED] wrote:
>> I'm in process of upgrading a master server from 4.0.24-log to
5.0.22-log
>> in a single master-slave environment. I've previously upgraded the
slave
>> to 5.0.22-log and restarted replication without issue.
>>
>> The current master is runn
Yves Goergen wrote:
On 13.11.2007 16:37 CE(S)T, mark addison wrote:
As your using InnoDB, which has row level locking a SELECT ... FOR
UPDATE should work.
http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html
e.g.
BEGIN TRANSACTION
new_id := (SELECT MAX(id) FROM table FOR UPDATE) +
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote:
> It's more complicated than that. You can use them together, you just
> have to do it like this:
>
> set autocommit = 0;
Is this the key that I have missed? I thought that setting autocommit =
0 is pointless when I issue statements within a tra
On 13.11.2007 16:37 CE(S)T, mark addison wrote:
> As your using InnoDB, which has row level locking a SELECT ... FOR
> UPDATE should work.
> http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html
> e.g.
>
> BEGIN TRANSACTION
> new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1
> -- s
thanks,
T. Hiep
On Tue, 13 Nov 2007, Baron Schwartz wrote:
Hi,
Hiep Nguyen wrote:
hi there,
this seems so easy, but i'm out of sql for a long time and need help
i have:
id,amount,state
1,2.00,il
2,2.00,oh
3,1.00,il
4,1.00,ks
5,3.00,ks
6,4.00,oh
how do i construct a sql statement that
[EMAIL PROTECTED] wrote:
I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log
in a single master-slave environment. I've previously upgraded the slave
to 5.0.22-log and restarted replication without issue.
The current master is running on RH9 and the slave is running on C
Thanks for the reply. It helped alot, since I did not know where to look.
I read the documentation regarding the issue and some more pages google
turned up for me.
I did increase my table cache to 16k and my open files are at 30k. I do
run debian etch 32-bit. How would I determine what reasona
Hi,
Hiep Nguyen wrote:
hi there,
this seems so easy, but i'm out of sql for a long time and need help
i have:
id,amount,state
1,2.00,il
2,2.00,oh
3,1.00,il
4,1.00,ks
5,3.00,ks
6,4.00,oh
how do i construct a sql statement that results as following:
il,3.0
oh,6.0
ks,4.0
sum (amount) all the
Hi,
[EMAIL PROTECTED] wrote:
I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log
in a single master-slave environment. I've previously upgraded the slave
to 5.0.22-log and restarted replication without issue.
The current master is running on RH9 and the slave is running
I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log
in a single master-slave environment. I've previously upgraded the slave
to 5.0.22-log and restarted replication without issue.
The current master is running on RH9 and the slave is running on CentOS 5,
which is what I'
hi there,
this seems so easy, but i'm out of sql for a long time and need help
i have:
id,amount,state
1,2.00,il
2,2.00,oh
3,1.00,il
4,1.00,ks
5,3.00,ks
6,4.00,oh
how do i construct a sql statement that results as following:
il,3.0
oh,6.0
ks,4.0
sum (amount) all the same state.
thank much,
Baron Schwartz wrote:
Yves Goergen wrote:
(For the record... I missed the mailing list recipient - again!!)
On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote:
On Nov 12, 2007 5:58 PM, Yves Goergen <[EMAIL PROTECTED]>
wrote:
First I find a new id value, then I do several INSERTs that need to be
>
>, as far as i can see, from mysql 5.0 and upper it is possible create
>index using functions.
>
>http://www.faqs.org/docs/ppbook/r24254.htm
>
>But i keep having problems with the exemple from the link. Is there any bug
>in mysql 5.0.24a-log?
The above website says:
"Practical PostgreSQL"
, as far as i can see, from mysql 5.0 and upper it is possible create
index using functions.
http://www.faqs.org/docs/ppbook/r24254.htm
But i keep having problems with the exemple from the link. Is there any bug
in mysql 5.0.24a-log?
2007/11/13, Martijn Tonies <[EMAIL PROTECTED]>:
>mysql>
Dario Hernan wrote:
Hi all I need to delete some fields from a table but in the where
clause I need to put a select command.
For instance, delete from table1 where id=(select id from table2 where
date
Not until 4.1. What you can do instead is run the select into a temp
table and then run the d
Hi
I've created a function that return a float value the code for it is :
create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7
varchar(20),pin9 varchar(20),MOL varchar(20)) returns float
DETERMINISTIC
begin
declare output float;
hi,
Tanks for your help, finally i found the "source" command. It work like
this:
mysql> source /Lhome/geruppa/mhc/Pack_Ref_web/prova.sql
2007/11/9, Michael Gargiullo < [EMAIL PROTECTED]>:
>
> On Fri, 2007-11-09 at 13:22 +0100, Pau Marc Munoz Torres wrote:
>
> > Hi everybody
> >
> > I'm writi
Hi.
I'm considering a situation where I have a number of child/client servers,
each of which are running local apps that feed into a local mysql db/tbl. In
order to manage the data, I want to copy all the mysql db/tbl data from the
chil/client systems, to a single central/master db.
I do not want
>mysql> create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13'));
>
>But i Get the following error:
>
>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
>that corresponds to your MySQL server version for the right syntax to use
>near ''P1','P4','P6','P7','P9','HLA-DRB1
Hi
I've created a function that return a float value the code for it is :
create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7
varchar(20),pin9 varchar(20),MOL varchar(20)) returns float
DETERMINISTIC
begin
declare output float;
Let's say I have the following table:
CREATE TABLE `Users` (
`id` blahblah,
`firstName` blahblah,
`lastName` blahblah,
`phone` blahblah,
`fax` blahblah,
`email` blahblah
);
If I do "SELECT id, firstName, lastName, email FROM Users, my result set is
returned as follows:
++---+
Yves,
> >> Damn, I found out that I need table locking *and* transactions.
> >
> > What makes you say that?
>
> BEGIN TRANSACTION
> SELECT MAX(id) FROM table
> INSERT INTO table (id) VALUES (?)
> INSERT INTO othertable (id) VALUES (?)
> COMMIT
>
> First I find a new id value, then I do several INS
Hi all I need to delete some fields from a table but in the where
clause I need to put a select command.
For instance, delete from table1 where id=(select id from table2 where
datehttp://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On Nov 13, 2007 4:53 AM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> From that page:
> > Sometimes it would be useful to lock further tables in the course of
> > a transaction. Unfortunately, LOCK TABLES in MySQL performs an
> > implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES
> >
Hi,
John Dba wrote:
Hi
I am running 5.0.46-enterprise-gpl-log version in linux (Red Hat Enterprise
Linux AS release 4 (Nahant Update 5).
In my.cnf i have configured innodb as :
innodb_data_file_path =
datafile1:500M;datafile2:500M;datafile3:500M;datafile4:500M;datafile5:500M;datafile6:500M
Yves Goergen wrote:
(For the record... I missed the mailing list recipient - again!!)
On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote:
On Nov 12, 2007 5:58 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially
Hello,
I'm using CentOS 4.5 with MySQL 4.1.20. I've got 2G RAM on the system,
and am running an x86_64 kernel (2.6.9-55.0.9.EL). When I try to use
mysqldump, MySQL crashes (then restarts). Here is the output from
mysqldump, and what happens in /var/log/mysql.log.
>From mysqldump:
-
On Thu, 2007-11-08 at 17:56 -0800, Lucky Wijaya wrote:
> Yes, the trigger code is works. Many thanks !!
> Now I understand the use of delimiter command. Thanks again... =)
>
> My next question is, do we able to view the triggers that has been created ?
> And how ?
>
> David Schneider-Joseph <[E
I am trying to use mysqlhotcopy on 2 different machines and I am having trouble
on both of them.
On the first machine, which is a Sun Solaris running mysql 4.0.15a, when I give
the mysqlhotcopy command, I get the following error:
DBD::mysql::db do failed: File './zemed/form_342.MYD' not found (E
(For the record... I missed the mailing list recipient - again!!)
On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote:
> On Nov 12, 2007 5:58 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
>> First I find a new id value, then I do several INSERTs that need to be
>> atomic, and especially roll back compl
Kelly Opal wrote:
Hi
Is there any way to restrict access to the tcp port on mysql. I only
want my 5 class C's to be able to access the port but it is a public
server.
Iptables
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:htt
On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote:
> On Nov 12, 2007 6:47 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
>> From what I've read about MySQL's table locks and InnoDB, you cannot use
>> LOCK TABLES with transactions. Either of them deactivates the other one.
>> Beginning a transaction unl
Hi
I am running 5.0.46-enterprise-gpl-log version in linux (Red Hat Enterprise
Linux AS release 4 (Nahant Update 5).
In my.cnf i have configured innodb as :
innodb_data_file_path =
datafile1:500M;datafile2:500M;datafile3:500M;datafile4:500M;datafile5:500M;datafile6:500M;datafile7:500M
and da
Hi,
>
>
> CREATE TABLE Test (
> COL1INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
> COL2VARCHAR(10) NOT NULL,
> COL3VARCHAR(10) NOT NULL,
> UNIQUE(COL2, COL3); --(not that)
FULLTEXT(col1,col2)
);
>
>
Try this property FULLTEXT
--
Yaya SI
56 matches
Mail list logo
