| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Comments:
<inviso> zardinuk: I *do it in MyISAM. A single statement is atomic anyway
<zardinuk> with innodb, you really don't have to worry about things getting screwed up
<KimmoA> GAH!
<zardinuk> inviso: no it isn't
<inviso> zardinuk: sure it is. You can't select and then issue an update, but there are certainly ways to get it done
<inviso> Same sort of thing applies to stock counts for inventory
<zardinuk> inviso: only manual locking
<[sr]> hi people
<[sr]> where can i find mysql icons?
<zardinuk> and that isn't a single statement
<mmcgrath> anyone know how to fix my broken slave?
<inviso> zardinuk: yeah, not necessary to use more than one statement typically :)
<fred87> is it possible to find the value of a primary key auto_increment field for a row you've just inserted into a table?
<inviso> !m fred87 last_insert_id
<SQL> fred87: (Information Functions) : http://dev.mysql.com/doc/mysql/en/Information_functions.html
<fred87> thanks
<KimmoA> So you mean that "transactions" ensure that WHILE I'm executing a SELECT query, for example, and somebody does something to the data I'm fetching, these queries will know about each other and "wait"/catch up with each other?
<KimmoA> Sounds like a hell to code...
<zardinuk> use wikipedia to look up MVCC
<zardinuk> not too complicated
<inviso> KimmoA: yup, thus why it's slower. If your app needs that kind of guarentee, use transactions
<KimmoA> This should only be NEEDED for stuff where money is involved, yes?
<zardinuk> better said "if your tables are read only, you shouldn't bother with concurrency"
<KimmoA> Is that a joke?
<Jared555> will providing mysql with an very large insert statement (thousands of rows) cause it to disconnect the client?
<KimmoA> Or do you mean that the entries are "read only"?
<zardinuk> KimmoA: if your not using innodb, you have to write extra code to account for deadlocks and what not
<zardinuk> not deadlocks, but you have to implement your own concurrency and locking
<Jivedue> or just use artificial locks
<Jivedue> that time out
<Jared555> getting the error on php: the mysql server has gone away
<KimmoA> zardinuk: ***uming it's a critical multi-billion dollar bank table, right? :)
<zardinuk> if you ever write to a table that is read and vise versa, you should use the built-in concurrency (innodb), if allyou ever do is write or read, not both, then you don't need to bother
<KimmoA> Well... you mean write to existing data, right?
<zardinuk> KimmoA: you give me an example and I'll tell you how it'll break
<KimmoA> In other words: UPDATE.
<KimmoA> OK. The table I have is constantly SELECTed from, and sometimes INSERTed to.
<inviso> zardinuk: update inventory set stock_count = stock_count - 3 where id=24; What's wrong with that?
<KimmoA> Not updated unless I need to fix something manually.
<KimmoA> It's also DELETEd.
<juanca7777> hello everyone
<eskywill> hello one
<zardinuk> inviso: one user runs the exact same query at the exact same time, it's like only one ran
<KimmoA> inviso: Quote 24.
<zardinuk> inviso: err, two users ran the same query, rather
<KimmoA> Are you speaking to the right person?
<inviso> zardinuk: not true.
<juanca7777> I have a question concerning merging specialized tables
<zardinuk> KimmoA: what is selected from it
<KimmoA> zardinuk: The application?
<zardinuk> inviso: yes it's true, explain yourself
<inviso> zardinuk: the statement is atomic. Both will decrement by 3
<zardinuk> KimmoA: yeah, what are you selecting?
<KimmoA> zardinuk: Let's just get things nice and sparkling clean: This "transactions ****up" can only happen on non-transactions tables that do UPDATE queries, right?
<juanca7777> I have around 44 tables and most of them constitute a arborescence of specialized tables, the "father" one represents properties
<zardinuk> inviso: in order to decrement by 3, you have to select the initial value, then decrement it, then write it back... you see where the error creeps in?
<inviso> zardinuk: that's not how execution happens. It sets count = (count - 3)
<zardinuk> right...
<KimmoA> (I thought he aked for syntax help.)
<zardinuk> and that's broken down into 3 steps
<KimmoA> s/aked/asked/
<inviso> zardinuk: nope, it's atomic
<zardinuk> inviso: only if you're using innodb, you get it?
<zardinuk> it's not atomic with isam
<Garf> Is there any sane reason why this can happen: http://www.morbo.org/blog/2006/04/14/its-2-2/ ?
<juanca7777> I tried using inner joins to select one branch of the specialization and then a left join of two of those individual branches, but I am stuck with the syntax
<zardinuk> http://www.dbazine.com/oracle/or-articles/kyte1/
<zardinuk> there you go inviso
<inviso> zardinuk: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html Please read under the section """You can also use functions to update records in a single operation"""
<inviso> zardinuk: that's an oracle article
<zardinuk> oracle and mysql use the same concurrency mechanism
<inviso> and the lost updates part is crap. That's not how atomic statements are executed. If I issued a select and then an update, yes. But, not in one statement
<inviso> zardinuk: just read the doc I posted you :(
<zardinuk> inviso: you don't understand, one statement is one transaction
<inviso> """You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:"""
<inviso> """Modify columns relative to their current value."""
<inviso> Is that not clear?
<zardinuk> you could do select @@var for update; @@var+=3; update @@var;
<inviso> that would give you concurrency problems
<zardinuk> inviso: that article is talking about "statements" as it relates to InnoDB
<zardinuk> with ISAM, a statement is vulnerable to lost updates
<zardinuk> with innodb, one statement is one transaction, (auto-commit)
<inviso> zardinuk: dude.... Pause your mouth and read that part of the article I linked. They explicitly give an example like I gave you "UPDATE tablename SET pay_back=pay_back+125;" and explain that it is atomic.
<zardinuk> ACID isn't only talking about begin and commit, it's practically everything you do with the db
<salle> zardinuk: ISAM? In year 2006?
<inviso> Any mods around?
<zardinuk> inviso: yes, it is ACID compliant IF YOU'RE USING INNODB, you get that?
<inviso> zardinuk: you're wrong.
<zardinuk> haha, you're wrong dude, what makes you think you know what you're talking about?
<inviso> please read the article
<zardinuk> I know exactly what you're trying to say, and you don't realize the difference between a transaction and a statement
<salle> zardinuk: You better read first :)
<inviso> zardinuk: is UPDATE tablename SET pay_back=pay_back+125; atomic across MyISAM?
<zardinuk> no it's not
<inviso> zardinuk: The article states that it is. Read it.
<salle> zardinuk: Can you prove it?
<zardinuk> inviso: i'm reading it, I don't see where it says it's ATOMIC
<salle> zardinuk: I know the answer. I wonder if You know it or you just talk because you heard someone else saying it
<zardinuk> besides, I know these things, I've been studying database design for years
<inviso> zardinuk: """This is very efficient and works even if another client has changed the values in the pay_back or money_owed_to_us columns."""
<zardinuk> k inviso, genius, now read the first sentence of that article
<inviso> Following are some techniques for working with non-transactional tables:
<zardinuk> and report back to me
<moj0rising> Does anyone here know of a web based application I can install to enable users to view mysql databases?
<inviso> It's under that section :(
<JasonSetNine> The other non-transactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called atomic operations.
<inviso> The part about non-transactional tables, IE MyISAM
<moj0rising> Kind of like a database client on the web.
<inviso> moj0rising: phpmyadmin
<inviso> !tell moj0rising about gui
<SQL> Alrighty.
<moj0rising> Thanks! I like and use phpmyadmin..
<zardinuk> inviso: have you accepted that I'm right yet?
<juanca7777> anyone here could explain me how to merge multiple tables?
<moj0rising> I amlooking for someting like...
<zardinuk> You can generally code around the need for row-level locking. Some situations really do need it, and InnoDB tables support row-level locking. Otherwise, with MyISAM tables, you can use a flag column in the table and do something like the following:
<salle> moj0rising: Many people like phpmyadmin
<eskywill> inviso, What?
<moj0rising> ..AdoDB on the web.
<KimmoA> This feels like TCP VS UDP.
<moj0rising> Like the openoffice DB application, only web based.
<KimmoA> "UDP can do what TCP provides but you have to code extra"...
<salle> zardinuk: You can use LOCK TABLES instead of flag, but that does not mean you know the answer of the question: "Is UPDATE MyISAM_table ... atomic or not?"
<cj> UDP: the data transmision for real men
<cj> inviso: how'd you get ops?
<inviso> cj: You'd be surprised :)
<KimmoA> cj.com ****s.
<zardinuk> salle: it's atomic with innodb, not with isam
<KimmoA> They want me to LOGIN to get my p***word back...
<inviso> !kick zardinuk Please learn to read
<salle> zardinuk: In some cases it is guaranteed to eb atomic while in other cases it is not. It's quite obvious when by the way :)
<salle> inviso: isam?
<moj0rising> Does anyone know where I could get an application like that (above)?
<inviso> awww, he doesn't love me :)
<salle> inviso: Who uses ISAM nowadays?
<fred87> inviso: nice :)
<zardinuk> inviso: why are you trying to get me kicked? I'm trying to help you understand something here
<inviso> zardinuk: Please read that article. All the way through.
<KimmoA> I don't get this...
<inviso> salle: heh, no one :)
<zardinuk> inviso: I just did... now what do you have to say?
<KimmoA> inviso kicked himself by typing that bot command
<KimmoA> ?
<inviso> KimmoA: yeah, forgot the bot doesn't know me yet :)
<salle> inviso: Correct. Many people use MyISAM however
<zardinuk> salle: help inviso understand that update table set value = value + 125 is actually a read and a write, not some magic silver bullet operation
Return to
#mysql
or
Go to some related
logs:
dd: reading `/dev/hdd': Input/output error ubuntu
ubuntu keeps asking keyring password
#mysql
riceandmeat
#linux
#physics
vga acroynm
administrator mode ubuntu Su returned with an error
irssi list themes
php 5.12
|
|
