<gtowey> !tell civilordergone about rank <ubiquity_bot> civilordergone: set @a:=0; select @a:=@a+1, … from table order by int_col <civilordergone> hey gtowey: is that variable that’s created a temporary one? <civilordergone> and local scoped to the person who called it <gtowey> session scoped, yes <civilordergone> thanks <Elise001> Hi. <nocturne7> does the order of joins matter sometimes ? <gtowey> nocturne7: only with outer joins <gtowey> otherwise mysql with rearrange the order for efficiency where needed <nocturne7> I am reading “The Art of SQL” and it is saying that the order of joins matter as well as the order of conditions in the where clause <ebergen> that is wrong <ebergen> the optimizer will reorder things as it sees fit <nocturne7> it is suggesting to attack the biggest branch first <gtowey> wow <ebergen> when was that book written? <nocturne7> in 2006 <gtowey> nocturne7: can you return it? <gtowey> it sounds like it is filled with bad information <nocturne7> gtowey: company bought it for me <nocturne7> I find some useful information in it now and then in term of schema design, in particular <nocturne7> is “SQL Tunning” the book to get to write efficient queries ? <Braden`> Hello <Braden`> I have replication running. I have a master and a slave <Braden`> For some reason (maybe because the binary log exceeded the max size) the master switched binary files. When I switch them on the slave, do I specify the master log pos, or 106? <gtowey> Braden`: you don’t have to do that <gtowey> Braden`: the slave will follow the master automatically <Braden`> The slave is giving me the error: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’ <nocturne7> how do people usually forward their “select ” queries to slave in their app ? <gtowey> Braden`: that sounds like you purged the master’s logs before the slave was done reading them <gtowey> nocturne7: with code. create two database connections (master and slave), use the appropriate one in your app code <Braden`> On the slave: Master_Log_File: mysql-bin.000076 – Relay_Log_File: braden-relay-bin.000093 – Relay_Master_Log_File: mysql-bin.000076 — On the master: File: mysql-bin.000099 – Position: 2115850 <gtowey> Braden`: ok, answer is the same. You probably purged master’s logs that the slave was trying to use still <Braden`> I don’t see how. I haven’t touched them <gtowey> Braden`: what’s the output of SHOW MASTER LOGS; on the master? <Braden`> let me check <gtowey> pastebin it, please <Braden`> http://pastebin.com/NEdKM32A <gtowey> Braden`: so the earliest log your master has is 91, your slave was back at 76. Now the log is gone <gtowey> Braden`: what’s SELECT @@expire_log_days; give you? <Braden`> ERROR 1193 (HY000): Unknown system variable ‘expire_log_days’ <Braden`> But isn’t my relay log pos at 93? <gtowey> Braden`: oops, try select @@expire_logs_days; <Braden`> 7 <gtowey> Braden`: your binlog are deleted automatically after 7 days <Braden`> ouch <Braden`> I will have to reconfigure that <Braden`> man <Braden`> My database is 20 gigs <gtowey> Braden`: the bigger problem is why was your slave 7 days behind? <Braden`> I wish there was a way to do a diff on the two databases <Braden`> gtowey: I forgot to set the slave to start automatically on reboot <gtowey> Braden`: it’s far easier just to rebuild the slave <gtowey> Braden`: where’s your monitoring? =) <Braden`> Yea, I will transfer the db again <Braden`> gtowey: Yea, this shows how my implementation is flawed on a planning and design level <gtowey> Braden`: you should set up something to email you when the slave isn’t running … <Braden`> ok <Braden`> Thank you, I will do that <Braden`> What is the best way to export a large database without impacting the people using it? <Braden`> Let me dig up the mysqldump string I am using <jny5> Can someone point me in the right direction, i have a simple inner join that i would like to add a unique column that has a count of the total rows <gtowey> Braden`: if you’re using all innodb tables, then you can use mysqldump â€â€single-transaction, and it won’t lock the database for the dump <gtowey> jny5: look at GROUP BY <gtowey> !m jny5 group by functions <ubiquity_bot> jny5: See http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html <jny5> http://tinypaste.com/883f0df6 <jny5> that is the query im running now <Braden`> gtowey: If I do that, how can I syncronously also get the position of the log file? <jny5> i would like to basically have a uniqe column 0,1,2,3,4,etc for each list <gtowey> Braden`: add â€â€master-data, and mysqldump will get it for you <Braden`> Oh nice <Braden`> Thank you <federated_life1> Hi guys, I have a weird character set bug, how can I insert data to force latin1 <jny5> Im not understanding how group by is going to help me in my situation honestly <gtowey> jny5: I don’t understand what you want. try adding an example result set in your pastebin <jny5> imagine having a unique index and adding it to my inner join. thats all im looking for, so i can recall the data by the unique_id <jny5> ie row 0, 1, 2, 3, 4 etc <gtowey> jny5: no, I actually can’t imagine that =/ <jny5> 1 second ill try to post up a picture or something of that nature <gtowey> “adding an index to an inner join” doesn’t make any sense in SQL <jny5> it does when you are trying to count rows and put them into smarty templates <jny5> http://i40.tinypic.com/5klf9w.jpg <gtowey> jny5: what does that number mean? is it just the number of the row in the result set? <jny5> that is correct <gtowey> !tell jny5 about rank <ubiquity_bot> jny5: set @a:=0; select @a:=@a+1, … from table order by int_col <jny5> very close, i had tried that before however the issue i get is it wants to add up each time it goes resulting in numbers as high as 62 <gtowey> jny5: ok, then it’s not going to work. I would just add the numbers in your application then <jny5> since im grouping items it doesnt result in an incremental number that related to the rows that are given <jny5> un fortunately i dont have that option with this shopping cart software im using <wer0ckz> hi guys please help. <wer0ckz> using mysql command, how can i select specific columsn that appends into a txt file. > results.txt <wer0ckz> something like. mysql SELECT column1 FROM table1 > results.txt <gtowey> wer0ckz: mysql -e “select 1″ > results.txt <wer0ckz> thank you <wer0ckz> 
<wer0ckz> 1 more thing please <wer0ckz> how to select specific database? <wer0ckz> wer0ckz: mysql -e “select 1″ > results.txt <gtowey> mysql -e “query” db > results.txt <wer0ckz> thanks <RDove> I know ibdata1 doesn’t shrink as it stands but the recommendations show that configuring innodb_file_per_table=1 may be useful. Do these files per table shrink or do they have the same same situation? <gtowey> RDove: same situation, execept that if you drop them, then the tablespace is deleted from disk and you get the space back <gtowey> RDove: many alter operations involve dropping the old table as well <RDove> such a pain <gtowey> RDove: disk is cheap! =) <RDove> during a mysqldump single-transaction, do inserts still continue? <ebergen> with innodb they do <gtowey> RDove: to innodb tables, yes. to myisam table, no <RDove> if you delete records from the troublesome tables, will that ibdata1 stop growing? does it use space inside of it if you clean things up? (like delete old records) <gtowey> RDove: space from deleted records will be reused, yes <RDove> i noticed mine was still growing a little, is there something else that would be growing it? does logs get written internally to it? anything that can be cleaned up with that? <RDove> or would internal logs re-use the deleted row-space too <gtowey> there isn’t a 1:1 ratio of data in the table, and space used on disk <RDove> (deletes/cleanup are still occuring), we had a year worth of data that ended up being 1 billion records (600GB ibdata1 file) and im doing an 90 day age off to keep it within 90 days instead of a year <gtowey> data is stored on pages, so it’s also possible if you deleted some random pattern of data, from one table, then inserted data into another table, it wouldn’t actually be able to re-use the data pages <RDove> when i came onto the project they only had like 650GB available disc and never set the requirement to clean it up so it grew out of control <gtowey> RDove: if you delete data, then optimize the table, you might see better space re-use <RDove> im worried about the downtime on inserts associated with optimizing tables and the risk of the optimize command using double space during the otpimization <gtowey> RDove: also, SHOW TABLE STATUS ‘foo’ can show how much space is free in the global tablespace <RDove> ok ill check that <gtowey> RDove: yes, those are possibilities <RDove> if i could stop the data growth of ibdata1, i would be satisified until we go to our new server <RDove> at that point i can do mysqldump to new server and re-do the tablespace <RDove> ok well thanks for the help i think i understand what needs to be done <hanstech> Hi All, when i try to drop a foreign key constraint, it gives me error <hanstech> ERROR 1025 (HY000): Error on rename of <gtowey> hanstech: what’s the full error? <hanstech> ERROR 1025 (HY000): Error on rename of ‘./dbname/table_name’ to ‘./dbname/#sql2-4e0-2b’ (errno: 152) <gtowey> !tell hanstech about 150 <ubiquity_bot> hanstech: MySQL error code 150: Foreign key constraint is incorrectly formed –> To see the error: SHOW ENGINE INNODB STATUSG and look for the Latest Foreign Key Error –> More information and FK requ<RDove> gtowey, i had one more idea i wanted to cross your path. What if I fix my replication server table space issues by removing the ibdata1 file from replication server, mysqldumping from the master with single-tr<RDove> after cleaning up the records <gtowey> RDove: mostly, although I would make a slight change in that plan <gtowey> RDove: once you rebuild the slave, just point your application config at it, and make it the new master <hanstech> gtowey, that says there is no foreign key. so another question. when i create a table using create table like, it won’t copy foreign key ? <gtowey> RDove: then you can take the other machine and rebuild it as a slave <RDove> hmm true <gtowey> RDove: less risk of problems that way <gtowey> RDove: since you’re always working on a machine that isn’t in production <RDove> ok thanks for the advice!! <gtowey> RDove: no downtime either =) <gtowey> hanstech: not sure. SHOW CREATE TABLE tablename G <hanstech> it says, KEY ‘author’ (‘author’) <hanstech> what exactly that means ? <gtowey> that’s an index, not a foreign key <gtowey> !m hanstech create table <ubiquity_bot> hanstech: See http://dev.mysql.com/doc/refman/5.5/en/create-table.html <adaro_> Recently i had a problem using innodb, that a single database connection seemed to deadlock itself, can someone explain how this could happen? <adaro_> show full process list showed only one connection active, but it could not do a simple insert on a table because it was waiting for a lock <gtowey> adaro: a deadlock is different than waiting for a lock .. do you have the SHOW ENGINE INNODB STATUS G output from that case? <adaro_> gtowey: yes i did that and that told me that it was intact waiting for a lock <adaro_> infact* <adaro_> eventually it would timeout <gtowey> adaro: can you pastebin the output? <adaro_> hmm is this channel logged? i put it on paste bin earlier in this channel <adaro_> but that was friday <hanstech> does show create table list FOREIGN KEY constraints ? <gtowey> hanstech: yes <hanstech> it list with the text ‘Foreign Key’ ? <gtowey> hanstech: yes … this is all described on the manual page I linked to you <nocturne7> what is the purpose of select null ( usually used with EXISTS) ? <adaro_> gtowey: seems the channel is not logged or i can’t find it , and i can’t access the relevant logs from home, but essentially what i saw was what’s below the when a transaction is waiting for a lock section of <adaro_> with zero tables locked before doing some operation on a java service, and 1 table in use 1 locked when the operation starts <adaro_> with the end result of RX HAS BEEN WAITING xx SEC FOR THIS LOCK TO BE GRANTED: <gtowey> adaro: doesn’t help … of course I know what the innodb status output looks like … the information specific to your case is the important part. <adaro_> gtowey: what information would you be looking for in the output, because the problem i was having when trying to understand what was going on was that it basically just told me there was a lock, with no extra <adaro_> gtowey: ah found it! good thing google spiders paste bin: http://pastebin.com/xjLYMuFW <gtowey> adaro: hmm, that’s still not enough info… it should say what transaction holds the lock it’s waiting for … <adaro_> gtowey: it doesn’t though, is that something you need to create the innodb_lock_monitor table for ? <gtowey> adaro: yes; this article is good for help on how to find the issue: http://www.chriscalender.com/?tag=innodb-lock-monitor <adaro_> gtowey: could 2 transactions still show up as a single connection in the process list ? <gtowey> adaro: no <adaro_> gtowey: hmm :/ i am pretty sure that when i debugged it i only saw 1 connection in the full process list while the it was waiting on the lock <adaro_> and since it was waiting for like 2 mins i had plenty of time to check <gtowey> adaro: you had to see at least two, because you were connected as well… <adaro_> gtowey: well 1 that was running a query <adaro_> the other ones where in mode sleep? or something like that <gtowey> adaro_: doesn’t have to have an active query to be holding a lock <Xgc> adaro_: http://pastebin.com/xjLYMuFW <gtowey> adaro: the can still hold locks <Xgc> Ooops. You already found it. <gtowey> adaro: which is why you need more info to debug this <adaro_> Xgc: yeah , thanks anyway <adaro_> gtowey: hmm but would that connection be in mode sleep then, even though it’s holding a lock ? <gtowey> adaro: yes <gtowey> adaro: transactions could span multiple queries over a very long time and hold locks <adaro_> gtowey: hmm and is there any way to see in the full process list that it’s sleeping but not available ? <gtowey> adaro: SHOW ENGINE INNODB STATUS, the lock monitor and other sources will give more info <gtowey> adaro: show processlist doesn’t show a complete picture of what’s going on. it was never really meant to <adaro_> gtowey: but innodb status is ? <adaro_> the thing is i installed innotop when i was looking at this last friday, and i think it uses this output, and in it’s transaction view it only showed one transaction iirc <gtowey> adaro_: that’s not going to help you fix this <gtowey> adaro: especially your memory of what you saw… <adaro_> yeah it’s kind of unfortunate that i can’t access the relevant logs etc from here <adaro_> and that i can’t reproduce it <adaro_> but it’s bugging me to no end that i can’t find a conclusive reason for this <gtowey> adaro: don’t let it bug you, just prepare to gather more detailed info for the next time it happens <adaro_> will do, thanks for your help, much appreciated <weakref> Hi guys. I wanna make a query to choose rows from a table filter by a time. <weakref> And example of my query: <weakref> SELECT * FROM ALARMS WHERE TIME(interval_start) >= ’10:48:00′ AND TIME(interval_end) <weakref> I’ve a row where interval_start is 2012-02-12 10:45:45 and interval_end 2012-02-12 11:00:45. However query returns nil <weakref> any idea? <weakref> ops <weakref> doh <weakref> my fault 
<bark3x1> hello guys <bark3x1> im using mysql 14.14 <bark3x1> is it possible in mysql.. a normal user… to have hes own schema which doesnt display the other databases, i mean his very own…. bacause im seeing all databasees with show databases with a normal user… w<bark3x1> is that possible? <bark3x1> the only thing that worked was , in root i create the object .. then i just apply the privelege s for that normal user.. <bark3x1> this is so tedious… what if i just want to have him create the schema… the thing is i just dont want him seeing the other databases…. <bark3x1> that’s my problem… his seeing the other databases…. <bark3x1> TIA <salle> bark3x1: There is no MySQL 14.14 <kuru> I’m trying to start a replication server and it keeps saying; Slave_IO_Running: No <kuru> Where do I look for in the logs to see what is happening? <bark3x> salle: sorry it’s mysql version 5.5.14 <bark3x> do you know how to hide the databases guys? <Percoles> Hide? <bark3x> just want to just hide all of the other schema which they donesnt own.. i mean they can only see their own schema, the others they should be hidden <Percoles> Oh <Percoles> GRANT <diverdude> How do i convert a timestamp like this: 1329047672899391 to a readable timeformat? <bark3x> i mean , the normal user will have a global grant of all right.. but when they do ‘show databases’ , they should only be seeing their own… <bark3x> but can you able to hide the schema to other users , at the same time they a CRUD grant? <TehShrike> diverdude: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime <bark3x> why is mysql this way… <TehShrike> bark3x: so only give them permissions to their own databases <TehShrike> bark3x: and don’t give them global permissions <bark3x> yup but the thing is… <bark3x> if i dont give them global right…. and they set the permisssions for specific databases… they cannot able to create FREELY their own databases, am i correct on this? <TehShrike> bark3x: there are permissions that they must have before they can create databases. <bark3x> what i want really is to hide schema that they own, and they can create objects by them selves??? is this possible? <TehShrike> ffs <TehShrike> bark3x: http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-privileges <bark3x> no try it <bark3x> it cant <bark3x> please enlighten me guys.. <TehShrike> You want to hide schema from the users that own that schema? <bark3x> i cant believe mysql, why is it like this <bark3x> lol <bark3x> yes <TehShrike> That makes no sense <bark3x> and at the same time… freely they can create their own objects and set it? <bark3x> huh? <bark3x> read again what you just type <bark3x> “You want to hide schema from the users that own that schema?” <TehShrike> Yes <TehShrike> That makes no sense <bark3x> how did you say that? <Vacuity> bark3x: there is no “ownership”. there are privileges. <bark3x> you mean to say.. if i login user A, i should be able to see user’s B schema? <bark3x> this is the requirement… <bark3x> what i want really is to hide schema that they own, and they can create objects by them selves??? is this possible? <Vacuity> bark3x: if you want to allow several users to create and use databases with arbitrary names (*.*), they have access to everything <bark3x> that’s it nothing more <bark3x> TIA <bark3x> just confused… <bark3x> you mean to say there’s no OWNERSHIP in mysql? <TehShrike> bark3x: click that link I posted above <bark3x> but can you tell me if this is what by design? but you dindt have a requirement wherein you need to segregate groups of people in one mysql instance???? <Vacuity> no. there are privileges, aka access control <TehShrike> bark3x: please, stick to one question mark per question <Vacuity> bark3x: GRANT ALL PRIVILEGES ON db1.* TO user1@localhost; <Vacuity> bark3x: that user can only access db1 <bark3x> yup that;s correct… but user1 cannot and wont be able to create freely his own database <bark3x> if he has the grant option, user1 would be able to snoop on all the databases? <bark3x> am i correct? <bark3x> thank you guys for replying <bark3x> i’ll ask one at a time.. ty <bark3x> waaaah i hope the future of mysql… sill follow the ownership style of oracle <Vacuity> bark3x: if you want to allow your users to create their own databases without interfering with others, use prefixed grants. GRANT ALL PRIVILEGES ON `user01_%`.* TO user01@localhost identified by ‘foo’; <Vacuity> GRANT ALL PRIVILEGES ON `user02_%`.* TO user02@localhost identified by ‘foo’; <bark3x> i cant believe it <bark3x> http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm <bark3x> Unlike in Oracle, there is no concept of role in MySQL. Thus, in order to grant a group of users the same set of privileges, the privileges have to be granted to each user separately. Alternately, though less <bark3x> how come the other softwares can do it… <bark3x> ahhh.. they do it in the application level… <bark3x> i mean just to hide (sort of like ownership) <bark3x> because im creating an explorer guys <bark3x> Vacuity: sorry, but what is ‘_%’ mean ? <Vacuity> bark3x: whatever. if you are really interested in how it works in mysql, start reading here: http://dev.mysql.com/doc/refman/5.5/en/privilege-system.html <bark3x> -% undocumented? <Vacuity> bark3x: it means, that user will have full access (CREATE, DROP, etc) for all databases (schema) starting with user01_ <bark3x> thanks for the links.. will check it out <bark3x> but i think i already read that <bark3x> Vacuity: what version is that supported? <bark3x> ok wait i go search about that <bark3x> ty <Vacuity> i don’t know the earliest version. but I just tested it in 5.0.x <angelazou> hi, can anyone point me to some resource about how to self join an already joined table? <angelazou> I’m using too much subqueries, and it’s slowing down the whole process <TehShrike> angelazou: you just need to alias the new instances of the table using AS
Feb 162012
