| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
<AaronCampbell> Leithal: any other ideas? levenshtein seems to have an init and deinit function...meaning that point shouldn't apply
<_k> Hi
<notx> whats the best format to store usernames and p***words in?
<_k> char() or varchar() for usernames
<_k> and char() or varchar() with binary option
<notx> whats the difference between char and varchar?
<notx> binary option?
<_k> CHAR(10) will always store 10 bytes, while VARCHAR(10) will only store as many bytes as required, plus 1 byte for the lenght of the string
<AaronCampbell> char sets aside a specific amount of bytes for data. Varchar uses only what it needs UP TO a maximum...at the cost of a byte
<_k> binary means that "foo" and "FOO" will be different p***words.
<_k> If your table will only contain CHAR() and INT columns, each row will eat the same amount of disk space. This will make operations faster.
<fizzle> when i created my table i used LONGTEXT and i was wondering if its possible to change it to TEXT without losing all my information
<Tac_work> is there a way to return all affected rows PK's after an update?
<Julian|Work> fizzle, make sure none of the data is longer than a TEXT column can hold
<Julian|Work> If not, you're fine
<fizzle> Julian|Work, would this work
<fizzle> ALTER TABLE cards MODIFY description TEXT
<_k> fizzle: Yes, looks ok.
<_k> If you got colums with more than 64K of text data, the data will be truncated.
<fizzle> k
<_k> LONGTEXT can take up to 4GB
<notx> _k: how will this using the same amount of disk space make operations faster?
<_k> notx: Because all rows will be stored at an offset which is a multiple of each row lenght.
<_k> So, ***uming 30 Bytes for each row, the 10th row can be found at file position 300.
<notx> ahh.. this has been tested etc?
<notx> wow i didnt know any of these..
<_k> Sure
<notx> you own _k
<notx> :)
<_k> Also, when you delete such a row, and you insert a new row, the new row can fill the old gap.
<notx> I see..
<_k> so, fragmentation won't happen.
<notx> so keep all fields as int or char would help a lot?
<notx> but if you mix it with varchar + char etc it wont help?
<_k> if you have VARCHAR columns, each row might have different lenghts. You probably get the idea.
<notx> yep
<notx> so have a table either use all varchar or all char?
<_k> Finding entries will take more operations, also filling the gaps
<notx> with specified lengths
<_k> Well, if you use varchar, you will save space, with char, you will save time
<_k> btw, this all only applies for MyISAM tables.
<notx> ahh. what about innodb?
<notx> what would good practice be for that?
<Julian|Work> You can mix and match all you want, but if you're trying to extreme performance and you know you'll have a lot of records stored, these are good practices.
<_k> Hi Arjen
<arjenAU> hey funny nick
<arjenAU> you could just be _ as the shortest wildcard. or %
<arjenAU> inviso: I'll kick him in two weeks, in Frankfurt.
<_k> You will?
<arjenAU> www.opendbcon.com
<inviso> arjenAU: perfect :)
<arjenAU> _k: well at least I can, at that point. being there.
<_k> Arjen: Is there any reason to do you?
<arjenAU> inviso: getting out is easier than coming in. which is curious.
<_k> s/you/so/
<inviso> arjenAU: heh, thanks for the warning :) Maybe I'll just stay put
<arjenAU> nah
<praseodym> how would I delete all rows from two tables where gid = 1?
<notx> how would you insert key value pairs into a table? and what format wouldyou use?
<arjenAU> inviso: if I were in I'd prolly wanna be out. and being out, I have no particular inclination to be in. permanently that is.
<inviso> praseodym: use the join syntax for delete
<inviso> !m praseodym delete
<SQL> praseodym: (DELETE Syntax) : http://dev.mysql.com/doc/mysql/en/DELETE.html
<inviso> arjenAU: the gr*** just *looks greener. I know the real truth. Someone dumped a boatload of green paint on "the other side"
<praseodym> inviso: ty
<inviso> praseodym: np
<_k> notx: Depends on the type of your keys and values :)
<AaronCampbell> Does anyone know why I might get this error when trying to execute a CREATE FUNCTION statement: ERROR 2013 (HY000): Lost connection to MySQL server during query
<_k> CREATE TABLE config (keey CHAR(32) PRIMARY KEY NOT NULL, value CHAR(32));
<_k> INSERT INTO config (keey, value) VALUES ('name of key', 'value of value');
<AaronCampbell> I saw the note about pre .10 and release .10, but the function DOES have an _init and _deinit
<AaronCampbell> MySQL version 4.1.13
<AaronCampbell> it's the levenshtein function from joshdrew.com
<praseodym> inviso: actually joining isnt working properly
<praseodym> inviso: what I want is to combine DELETE FROM t1 WHERE id = 1; DELETE FROM t2 WHERE id = 1 to one query
<firewire`> praseodym yes
<firewire`> you can join in a delete just like in a select
<praseodym> how'd I do this?
<firewire`> see the syntax in the manual
<firewire`> mysql.com/delete syntax
<jeffzz> can anyone think of how to restore all tables in a database EXCEPT one ? db.sql is the source... I just gotta skip one table that will take 2 hours to restore
<praseodym> firewire: couldnt get one working
<firewire`> praseodym which version of mysql are you using?
<praseodym> firewire`: 5.0 currently but it needs to deploy on 4.0
<firewire`> ok
<arjenAU> AaronCampbell: that looks like a crash. check your mysql server's errorlog for a trace. if it's not the UDF, resolve the stack trace and report it. if it's the UDF, fix it ;-)
<firewire`> 4.0 supports it
<_k> DELETE t1, t2 FROM t1, t2 WHERE t1.id=t2.id
<_k> Something like this (from memory)
<firewire`> and t1.id = 1
<_k> firewire: of course, thanks
<firewire`> ;)
<AaronCampbell> arjenAU: Ok, I'll see what I can find. I use the same UDF on 3 other boxes...works fine.
<arjenAU> AaronCampbell: ok. same architecture/distro?
<AaronCampbell> CentOS 4 on all
<arjenAU> AaronCampbell: if it's like a loose pointer, behaviour can be erratic.
<AaronCampbell> I think the new one might be 64 though :|
<arjenAU> ah that fake distro thing
<praseodym> _k, firewire`: that doesnt delete anything when there is no rows with id=1 in t2
<arjenAU> 64 is a significant difference ;-)
<_k> praseodym: correct. Try an outer join
<AaronCampbell> yep, new server is AMD 64
<_k> DELETE t1, t2 FROM t1 LEFT JOIN t2 USING (id) WHERE t1.id=1
<arjenAU> AaronCampbell: did you build the UDF shared lib from source?
<AaronCampbell> yeah, I get the .cc from joshdrew
<AaronCampbell> http://joshdrew.com
<praseodym> _k: best I could get was this: DELETE jtcsubscribe_groups, jtcsubscribe_choices FROM jtcsubscribe_groups LEFT JOIN jtcsubscribe_choices USING gid WHERE jtcsubscribe_groups.gid =7
<praseodym> _k: resulting in: #1064 - 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 'gid WHERE jtcsubscribe_groups.gid =7' at line 4
<firewire`> using (gid)
<floppyears> hi is there a way that I can get a printout from phpmyadmin about the size of a database column ?
<_k> yeah, parentheses
<_k> floppyears: SHOW CREATE TABLE tablename
<praseodym> _k: works now. thanks!
<_k> To get the definition of the table.
<floppyears> _k: it didn't work, it just shows a T with an arrow
<_k> What is "it"?
<floppyears> _k: the "show create table tablename"
<notx> <_k> binary means that "foo" and "FOO" will be different p***words.? _k how do you make a field with char do that?
<_k> notx: SELECT "foo" = "FOO";
<_k> notx: SELECT BINARY "foo" = "FOO";
<will> notx: Usually people use md5 to store p***words.
<firewire`> sha1
<wamd> Hello all.
<wamd> Does anyone know why when looking for a string of '1' I get any result with a one in them?
<[RainMkr]> wamd: what?
<wamd> Well I am doing a SELECT * FROM table WHERE INSTR(CONCAT_WS(',',f1,f2,f3,f4,f5,f6),'1')
<wamd> I just want want to find rows with 1 in them
<syle2> if a field is empty
<syle2> what does row[blah] return
<syle2> this would be for C API
<wamd> afk
<syle2> guess i could default it to 0 but then i would have to strcmp on on it unless i sscanf it into an int
<spirit16> hi. can anyone help me in here
<spirit16> anyone?
<wamd> spirit16: What do you need help with specifically?
<TheWarden> spirit16: tell us the problem....
<spirit16> i get this error
<spirit16> cannot use database blah_forum
<spirit16> Error: Access denied for user: 'blah_manage@localhost' to database 'blah_forum'
<spirit16> Error number: 1044
<spirit16> Date: Thursday 27th of October 2005 10:38:10 PM
<spirit16> i have no p*** on blah_forum btw
<dotstar> Is there a character based version of "MYSQL Query Browser"?
<wamd> !perror 1044
<spirit16> !perror 1044
<spirit16> what
Return to mysql
or
Go to some related
logs:
twistn freenode
confirm shortcut delete annoying
dragorn
|
|