#mysql
25 October 2007
Total 27 pages. You are browsing page 5/27.
First :: Prev :: [...] [3] [4] [5] [6] [7] [...] :: Next :: Last
02:49 <****> Fortune cookies.
02:49 <****> ow!
02:49 <****> Simetrical: thanks!
02:50 <****> im brazilian, here is "Biscoito da Sorte"
02:50 <****> :)
02:53 <****> TinyText is limited to 255?
02:54 <****> freebox, yes, like VARCHAR(255).
02:56 <****> hm
02:57 <****> o.O
02:59 <****> I need to format a variable to be at least 4 characters, if not pad it with leading 0 what should I look for ?
03:01 <****> Billium: I assume you are talking about selecting a data type for a column?
03:01 <****> yes sorry
03:01 <****> column = id varchar[15]
03:02 <****> Billium: a numeric type can use zerofill and something like 'int(4)' to have leading zeroes.
03:02 <****> but this isn't numeric, it may have an alpha char, but needs to be at least 4 chars, I could lead it with anything, but 0 seemed best
03:05 <****> Billium: might be easier to do in your application code. Check length, concatenate some value...
03:06 <****> update table set id = right(concat('0000',id), 4) WHERE length(id) < 4 how about that ?
03:08 <****> That sounds correct.
03:08 <****> Billium: if you're using latin1 charset.
03:09 <****> Billium: or some charset that uses one byte per character.
03:10 <****> I'm using MySQL Administrator and I can't seem to select users to edit. I can select them, but they're not loaded in the right pane
03:10 <****> and I'm connected as root
03:11 <****> it won't let me delete users either :/
03:11 <****> how do i update a password with md5 encryption with 10 character random salt?
03:12 <****> let's say i want to run a query something like: --- update login set password = md5('hello')
03:13 <****> but i want to add salt on that
03:15 <****> squirrell: would that be md5(salt, text) ?
03:18 <****> i already something in oscommerce. i tried changning password from backend
03:18 <****> salt should be random ten character
03:19 <****> i already screwd i meant
03:20 <****> I have a table that represents settings for users... columns are user_id, group_id, setting, value. I'd like to in a single query pull out the setting/value pairs for the user first, then the user's group, then the defaults... I've been trying to join the table to itself to pull this off, but since there's always a match for t1.setting != t2.setting, I end up with all user settings for that user, all settings for that group, and all the defaults (user_id = NUL
03:21 <****> is there a way to filter that out? so if the user had setting1 set, I'd get the user's value, but if there was no row that matched that, it'd check for user_id = NULL and group_id = 1, and then fall back to user_id = NULL and group_id = NULL ?
03:24 <****> is there a way to see if a row exists in a table? Like I want to do 'SELECT * FROM foo WHERE bar = 1' but it seems dumb to select the entire row if all I want to know is if it is there
03:25 <****> so just select one column :)
03:25 <****> k
03:25 <****> you could do a count() I guess
03:25 <****> that will always return 1 or 0
03:30 <****> penius: not exactly sure... but do you know about IFNULL?
03:30 <****> or I guess COALESCE() is prefered
03:31 <****> ToeBee: checking that out now, thanks
03:31 <****> hi
04:06 <****> can you left join more then one table?
04:09 <****> yes
04:09 <****> I believe we determined that you can join up to 61 tables
04:11 <****> no, 62.
04:11 <****> oh :(
04:18 <****> thx
04:19 <****> any idea why this isn't working right? http://pb.theoverclocked.com/122
04:19 <****> it returns a row but not the one I want
04:19 <****> ToeBee, but if that's not enough, you can nest subqueries right up until MySQL runs out of stack space. :D
04:20 <****> hi :) I have the following query: select * from foo where fd_1 like '%bar%' collate latin1_general_ci AND fd_2 like '%foo%' collate latin_1_general_ci order by fd_2; I'd like to set the collation only once to all matches. When I put it after 'order by' command it gives me the following error: "ERROR 1253 (42000) COLLATION latin1_general_ci is not valid for CHARACTER SET 'binary'". How can I set it once then? thanks in advance.
04:21 <****> any one knows any software which can backup mysql live?
04:21 <****> something like a snapshot??
04:21 <****> s4v: You set it on the column or the table instead instead (or even the db).
04:22 <****> orly.
04:22 <****> qkit, mysqldump can for InnoDB. For MyISAM you have to either do it at the filesystem level with LVM or similar, or flush and lock the tables.
04:22 <****> Jonathon: It'd be better if I could set it in the query actually. I know it is less 'portable' since I'm not sure whether the character set is compliant, but in my context it really makes sense to set it in query
04:24 <****> s4v: You want to set the collation once for the query? You only run the query once - either that or do it as a prepared statement?
04:24 <****> Simetrical:i'm using myisam, if i flush and lock the tables, wouldnt it similar like just stop the database and dump the backup out?
04:25 <****> qkit, yes, essentially. If you're using mysqlhotcopy that might take little enough time to be acceptable. Otherwise you'll need either InnoDB or LVM-ish stuff.
04:25 <****> qkit, or, of course, a slave server that you can take out of rotation whenever you like.
04:26 <****> qkit, which might be running on the same computer as your master server, theoretically.
04:26 <****> ToeBee: Correct. 61 seems to be the limit.
04:27 <****> qkit: Essentially all of them require some form of locking, but it is only for writes, not for reads. This is where it is different to stopping the database. Snapshots can be done depending on your filesystem, or binary dumps using commerical tools.
04:28 <****> ERROR 1116 (HY000) at line 4: Too many tables; MySQL can only use 61 tables in a join
04:28 <****> hah! suck it thumbs! :p
04:28 <****> and the error message is not a bug. The # is actually 61. :)
04:31 <****> ToeBee: darnit.
04:31 <****> Subqueries are still unlimited.
04:32 <****> Except by stack space and similar concerns that I pray to God do not occur often by people not looking to screw with MySQL.
04:32 <****> s/by/for/
04:32 <****> Good night.
04:32 <****> Jonathon: thanks
04:34 <****> thx for the info. :P
04:37 <****> Simetrical: Right. Just replacing that 61st table with a derived table can add another 61 tables. Each derived table is probably limited to 61 directly joined tables. I'm not about to test that one.
04:39 <****> can I have two conditions for a LEFT JOIN? Like "ON po.aid = a.aid AND ON po.name = a.name"?
04:42 <****> jstarcher: Sure.
04:42 <****> jstarcher: ON x=y AND y=z
04:42 <****> Xgc, nice thx
04:43 <****> jstarcher: Just one ON clause per JOIN.
04:44 <****> Xgc, whoa I dont get any result now
04:44 <****> hmm
04:45 <****> jstarcher: Post the SQL to rafb.net/paste
04:45 <****> Xgc, this works: http://pb.theoverclocked.com/123
04:45 <****> jstarcher: Additional criteria can easily cause that.
04:46 <****> but the data from uc_attribute_advanced does not align correctly
04:46 <****> jstarcher: Can't help you with php. Just post the actual SQL.
04:46 <****> jstarcher: You're probably not generating the SQL you think.
04:47 <****> Xgc, http://pb.theoverclocked.com/124
04:48 <****> jstarcher: That's fine. Now post the SQL you are having trouble with.
04:49 <****> jstarcher: Did you know that you'll get no rows if ao is null?
04:49 <****> Xgc, no, i was wondering that
04:49 <****> jstarcher: Don't test the right table of a left join for equality or you lose the outer join behavior.
04:50 <****> jstarcher: Move that test into the ON clause.
04:50 <****> jstarcher: LEFT JOIN uc_attribute_advanced AS ao ON a.aid = ao.aid AND ao.aid IN ('1');
04:51 <****> Xgc, so get rid of the WHERE?
04:51 <****> jstarcher: Note that you could have also tested a.aid IN ('1') in the WHERE clause.
04:52 <****> jstarcher: The first suggestion requires no WHERE clause.
04:52 <****> Xgc, prolly better off, I know a.aid will have the value
04:52 <****> jstarcher: Correct
Total 27 pages. You are browsing page 5/27.
First :: Prev :: [...] [3] [4] [5] [6] [7] [...] :: Next :: Last