| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Comments:
<0> everything is in mysql group
<1> Hey all.
<2> mysql runs as mysql:mysql
<0> i ran bin/mysqd_safe --user=mysql &
<0> as instructed in INSTALL_BINARY
<1> Is there a way to put in a query some sort of IF statement? I have an application that will perform a given SQL INSERT query if a script variable is set as true. I want it to only INSERT if say, username doesnt start with '00'. Is that possible?
<3> hey... i'm trying to track down mysql deciding not to start, but i'm not sure really what to look at. its kind of a broad thing to google. when i do a safe_mysql command, it says its starting, and then it ends immediately.
<3> what type of things should i be doing to start. i'm really not a mysql administrative wizard
<0> where does mysql by default try to write its PID, /tmp right?
<1> like "INSERT INTO (username, date) VALUES ('$username',NOW()) IF (<username doesnt start with '00'>);
<4> well backups save the day
<3> does anyone have any idea where i should start looking
<3> this is a fresh upgrade from mysql3 to mysql5
<1> ouch.
<3> yeah, i'm new here, and i had to start upgrading
<1> did you check the log?
<3> hmm, no i didn't -- i expected that it wasn't generating a log yet but i was wrong. its complaining about a missing /usr/libexec/mysqld
<0> well i had to chmod the data dir 777 but i got it working
<3> i could just symlink that appropriately, but is there a reason a freshly RPM installed mysql wouldn't put its files in the right places?
<1> paste the error
<1> i dunno, never used an rpm before
<3> nohup: cannot run command `/usr/libexec/mysqld': No such file or directory
<3> oh
<3> nevermind me
<3> those entries are old
<1> =]
<3> so, that's not the problem
<3> which means, i'm back to having no idea what the problem is.
<1> theres nothing in the log file?
<3> nope
<1> then i cant help you
<3> when i do safe_mysqld, i get:
<3> Starting mysqld daemon with databases from /var/lib/mysql
<3> STOPPING server from pid file /var/run/mysqld/mysqld.pid
<3> 060524 07:53:59 mysqld ended
<5> bytenik: check your error log
<3> and that's it, with no log entries generated
<5> bytenik: either in /path/to/datadir/<hostname>.err or in /var/log/mysql
<6> could anybody help me with a noob-ish question on joins?
<3> shabbs: I have a /var/log/mysqld.log, and the innodb transaction logs in the data dir, and that's it
<6> please?
<3> bentob0x: sure, go for it
<6> http://pastebin.com/734970
<3> what's the issue?
<6> basically
<6> I need to return rows both empty for the content that don't exist
<5> bytenik: maybe try specifying a log file in the config so you know exactly where to look?
<6> and rows that match id=value
<3> shabbs: err-log=/var/log/mysqld.log
<5> hrm
<3> shabbs: That's the error log... its just not outputting anything to it
<3> bentob0x: I'll take a look in a bit -- i have to fix my issue first
<6> thank you bytenik
<5> bytenik: then I'm not sure. it's odd not to have an error message when it fails. does --verbose help at all?
<3> shabbs: Nope
<3> oh hey, there we go
<3> mysql user didn't have access to the /var/lib/mysql dir
<3> don't know why it didn't log that
<3> but when I did: su -c mysqlmanager mysql
<3> i got permission denied
<6> have to go bytenik
<5> so is it working now?
<6> will be back later
<3> shabbs: yep
<3> shabbs: Thanks though.
<7> ok morning
<7> I dont get this error [nativecode=1136 ** Column count doesn't match value count at row 1]
<8> Tier_1: Often occurs in an INSERT when you don't provide the correct number of values .vs. columns.
<7> explain
<9> he just did
<8> Tier_1: Hint: Always provide an explicit columns list.
<10> hey all, I think I have what is an obvious perf-related question, but I'm having trouble finding some wisdom via google
<10> I have an ISAM table with a few million rows, and I need to do some deletions on it -- I have two methods of doing so:
<10> I want to know which should perform better, in general terms
<9> .... isam or myisam?
<10> myisam :) sorry
<9> k
<9> phew
<7> I have
<10> so the rows are all timestamped and each also has a varchar(32) containing a string which applies to about 60-100 rows
<10> there is also another key indicating the source of the data (say 13 different ones for the entire table)
<10> so now I can either DELETE FROM mytable WHERE channelid='whatever' AND datetime<'....'
<10> OR
<10> I can go through and do many DELETE FROM mytable WHERE guid='....', which would delete 100 rows or so at a time but would need to repeat many times
<2> by the time you have asked us it could have been done
<10> archivist: I'm writing software that will be doing this, not actually *doing* this right now :)
<10> in broad strokes, is one BIG delete from better than many small DELETE FROM's
<10> factoring out things like opening the connections and prepared statements; this I do anyways
<10> (i.e. reusing connection anyways so there's no connect/disconnect cost)
<11> idanWork: depends on indexes. lots of small deletes not using indexes will **** (many table scans). one big delete doing a table scan will be a LOT better.
<2> yes but.. one big delete may lock others out for a while
<10> well, traffic on this table mainly consists of insert requests, roughly one per channel per second or two
<10> so say 40 channels = 40 INSERT's a second
<11> idanWork: although many small deletes using indexes could turn out better as you hold the write lock on the table for less time.
<10> which would need to continue while the delete is going on
<11> idanWork: check if any of your indexes will be used
<10> IC -- the lock would lead to a pileup of queued insert requests
<2> but may ultimatley be faster
<10> sigh, sounds like perf testing will be necessary, I was hoping there would be a "Clearly, you should X" answer :)
<12> http://paste.lisp.org/display/20375 how would I redesign so that mutual foreign keys between CONTRACT and RATE disappears?
<2> idanWork, you could also consider changing the inserts to batch inserts
<10> I think the "channel" field is indexed, or could be made into one -- it contains one of say 40 discrete values so it's a natural fit
<10> I'm sorry if I use terminology incorrectly, I'm fairly new to MySQL
<13> !man set
<14> (The SET Type) : http://dev.mysql.com/doc/mysql/en/SET.html
<11> idanWork: do an EXPLAIN on the query
<13> idanWork: is your 'channel' field an enum?
<10> Darien, no FK on another table
<10> oh, forgot to mention salient information:
<10> MySQL 4.1.x
<13> ok
<10> MacPlusG3: ok, one sec
<10> archivist: when you say batch inserts, you are talking about INSERT DELAYED ?
<13> he's talking about extended inserts, afaik
<9> (value list),(value list),(value list)
<9> instead of just (value list)
<13> INSERT INTO table VALUES (a,b,c),(d,e,f),(g,h,i);
<10> Darien: ah
<11> idanWork: the output will tell you what's going on.
<2> for my data its 100* faster
<13> to insert three rows at a time
<13> yeah, extended inserts are MUCH faster
<10> probably not practical as my app wants to get as many rows to the DB in the shortest time possible in case of failure
<15> i have a m***ive performance problem (resulting in a lost connection) when running this query:
<15> delete from eadb_attrs where referer like 'GC\_%' and referer not in (select cell from admin_allcells)
<10> and because they may be coming from very different sources
<15> the subselect has ~100 rows, the eadb_attrs table ~100000 rows
<15> any ideas?
<10> ok, one sec while I play with explain, brb (and THANK YOU all, this is very helpful)
<4> is it safe to kill an optimize table process?
<10> MacPlusG3: uh, I can't EXPLAIN delete -- so I guess I should EXPLAIN SELECT * from ... with the same conditions as DELETE FROM?
<9> yes
<10> ok, so it looks like the big delete is examining only 1 row
<10> vs the small delete examining all of the rows
<4> im running Optimize Table on a tabel i just imported from backups, its stuck in "repair by sorting"
<4> what exactlyd oes that mean
<4> looks like it locked the table and the query queue is builing up
<9> it means it's optimizing the table
<4> shoot
<16> what is the best way of getting data back from a table where you have a primary key and a "parent_id" for rows below that parent id, say I want to get back a list of rows with their children below that row?
<9> this is what optimize does. it locks and sorts the data+keys
<4> yay i hope it finishes up
<10> also the small delete is type ALL vs big delete of type REF
<9> for what it's worth, at least it doesn't say "repair with keycache"
<4> theres a full text index on group of 4 fields and few other indexes
<9> what you'll discover is most people don't run optimize very oftne :)
<10> MacPlusG3: any wisdom to spill on the results? it seems like the big query would process significantly faster according to the EXPLAIN
<4> i just imported the table from a dump, it seemed to run slow
<4> so i ran the optimization :(
<9> if it came from a dump it is generally sorted already
<4> its been running for almost 700 seconds now
Return to
#mysql
or
Go to some related
logs:
gentoo tty agetty switch
#debian
#sql
symfony sqlite problem
#perl
#bind
#linuxhelp
#css
#web
#kde
|
|