| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Comments:
<Jax> is it just me, or is using the primary key as a way to sort stuff stupid
<dean> It's just you.
<AaronCampbell> Jax: I think that depends on how the primary key is used right?
<AaronCampbell> Especially if the primary key is a multi-column index
<dean> Especially if the primary key is a clustered index and you are grabbing sequential data
<Jax> nah it's a one column index. primary key, auto_increment
<dean> YOU'RE THE ONE THAT'S STUPID
<dean> hehe
<Jax> but just thinking when you delete something, and add something else.. you can't really change sequence..
<dean> That's the problem with surrogate keys
<AaronCampbell> as long as the data has never been manually changed, and you never need them in reverse order...it probably doesn't matter
<Jax> hey.. i didn't design this database.. i've just been optimising this shiznit for the past couple years >;/
<firewire> Jax you can use that primary key to see the order things were added
<firewire> or see the last X things very quickly
<jessejoe> can anyone help me change a table id column to auto_incrment and start at 1 instead of 0?
<jessejoe> i know its with alter table, i just cant the correct syntax
<gleam_> alter table mytabl euato_increment=X
<gleam_> create table ( .... ) auto_increment=X
<jessejoe> gleam, that didnt seem to work. i deleted all the values in the table, and added a new one in and it still started the id at 0
<jessejoe> i did alter table plogger_comments auto_increment=1;
<jessejoe> shouldnt i have to say what column i want to change?
<gleam_> what do you mean by started at 0?
<gleam_> it should start at 1 anyway
<jessejoe> the id column starts at 0 instead of one
<gleam_> that's very odd.
<gleam_> how was the table created?
<gleam_> how are you inserting data into the table?
<jessejoe> see the thing is i exported a mysql 5 database to comply to mysql 3 and then imported it. all tables seem to work except this one. im inserting the data via the plogger image gallery
<jessejoe> it stores comments for the images in this table
<gleam_> yikes
<jessejoe> yeah i know
<gleam_> when you use --compatible it doesn't keep the auto_increment attribute.
<jessejoe> everything else is working though, lol
<gleam_> your id column isn't auto increment
<jessejoe> yeah thats what i wanted to alter
<gleam_> yeah you'll need to modify the column then
<gleam_> !m jessejoe alter
<SQL> jessejoe: (ALTER PROCEDURE and ALTER FUNCTION) : http://dev.mysql.com/doc/mysql/en/ALTER_PROCEDURE.html
<gleam_> look at "MODIFY"
<gleam_> err
<gleam_> !m jessejoe alter table
<SQL> jessejoe: (ALTER TABLE Syntax) : http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
<jessejoe> i tried reading that and got totally lost. i guess ill try again
<gleam_> modify mycol int unsigned auto_increment primary key
<gleam_> that should work
<jessejoe> i did "alter table plogger_comments modify id int unsigned auto_increment primary key;"
<jessejoe> i got ERROR 1068: Multiple primary key defined
<jessejoe> i took out the primary key value and it seems to have worked
<gleam_> aight
<jessejoe> woooo! that worked gleam, thanks so much!
<gleam_> i hate that about the --compatible option.
<jessejoe> yeah, if mysql 3 can use auto_increment, why remove it?
<gleam_> because of the sql mode it sets
<gleam_> it'd be nice to have a sql mode to emulate 3.23 or 4.0 or 4.1
<jessejoe> i see. I g/g though, thanks again gleam
<nanotech> can anyone see an error with this ?
<nanotech> i can paste 2 lines right?
<gleam_> if htey're short
<nanotech> they dont wrap on my screen..
<nanotech> $clientuname = stripslashes($_POST['login']);
<gleam_> go ahead
<nanotech> $q = 'SELECT p***word FROM users WHERE username = "'.$clientuname.'" AND active = 1';
<gleam_> that's php
<gleam_> not mysql
<nanotech> oh :|
<gleam_> show us the actual query without variables (echo $q) and then the error it generates
<Epilog> nanotech: you should use mysql_real_escape_string in all the sql
<Epilog> and speciallty post and get vars
<gleam_> that too
<gleam_> or at the very least addslashes
<gleam_> but why do that when you can do m_r_e_s
<mohadib> is it possible to have mysql verify data , for instance that a strinng being inserted has to be 32 chars long?
<gleam_> verify in your app
<nanotech> ya, its most likely a php question, cause the i get: Error: Unknown column 'username' in 'where clause'
<nanotech> when i know the input im putting in is in the database
<gleam_> does users have a column named "username"
<gleam_> ?
<nanotech> yes
<gleam_> oh, also use ' instead of " around strings just incase you've got the ansi_quotes sql mode
<gleam_> and because it's the right way to do it
<nanotech> thanx, realing the manual right now :)
<LedHed> for a user to be able to change his/her p***word, do they need special permissions for the mysql schema?
<cappiz> i got 2 tables... one 'rls' and one 'nukes'. is it possible to make a query that would do a "join" of these tables ONLY if rls.nuke = TRUE (boolean) ?
<Duesentrieb> err?
<bigfoot-> just put this in the WHERE condition?
<Duesentrieb> what do you mean "make the join only if"?
<cappiz> rls.nuke is either 0 or 1. if rls.nuke = 1 i want to select * from rls, nukes....
<cappiz> but only IF one of the rows i get as an result has rls.nuke = 1
<cappiz> like if it was possible it would be something like this: select * from rls, IF rls.nuke = 1, then select * from rls, nukes
<bigfoot-> This does not really make sense... rls.nuke is one field which can have another value for every row...
<bigfoot-> But this "join or not join" decision is one which has to be made for all rows.
<bigfoot-> And I doubt you want to do a cross join.
<EyeCue> why are they in seperate tables
<EyeCue> why not put a nuke field in the main release table?
<cappiz> bigfoot- rls.nuke is one field which can have another value for every row... thats true
<whafro> I'm looking to copy a row column for column with the exception of the auto_increment column 'id' ... is there a way to do that in a single query without having to get the first row and then populate an insert statement with php or something?
<cappiz> EyeCue, cause i was thinking it would take less resources if i had less columns in the releases table
<bigfoot-> whafro: look at INSERT ... SELECT syntax.
<EyeCue> but youve got a whole new table?
<EyeCue> and the overhead of a join?
<cappiz> and nuke = 1 is not gonna be true for many of the records
<EyeCue> the only time you want to create a new table, is if standard normalisation rules say you should
<CellSeeker> hi
<EyeCue> ie duplication and redundancy
<bigfoot-> cappiz: well. is there some id in the rls and nuke table which connects those two?
<EyeCue> so what if its not true
<EyeCue> if its only gonna be either 0 or 1
<cappiz> bigfoot- yeah
<CellSeeker> if table A has an id column and a b_id column, and b_id is a foreign key to table B
<EyeCue> use it as a field in the original table
<CellSeeker> then how do I optimize the query: select B.id from B where B.id not in (select A.b_id from A) ?????
<bigfoot-> cappiz: then just do a LEFT JOIN with rls.nukeid = nuke.id AND rls.nuke = 1
<cappiz> rls.id = PK
<cappiz> nuke.id FK
<cappiz> but i dont want to have a query that from the start takes it for granted that the rls.nuke = 1
<cappiz> i only want to look up the nukes table if rls.nuke = 1
<cappiz> :P
<bigfoot-> yes. left join...
<cappiz> hehe... like you would guess... im totally new to this
<bigfoot-> CellSeeker: SELECT B.id FROM B LEFT JOIN A ON A.b_id = B.id WHERE A.b_id IS NULL;
<cappiz> *read what left join means*
<CellSeeker> bigfott, hummmm
<CellSeeker> bigfoot, why would that be faster?
<Therion> IT'S A JOIN THAT GOES LEFT OMG
<CellSeeker> aren't JOINs expensive?
<alphablue> it's the answer to the often asked question.."How's it joining?" ..
<alphablue> $56/join
<Therion> If you aren't using joins you probably shouldn't be using an RDBMS at all
<bigfoot-> CellSeeker: because it can be done without storing much temporary data... in doubt just benchmark it (with some larger amounts of rows in both tables)
<cappiz> this is the DB i made: http://pastebin.com/738222 might be the ****tiest thing you have ever seen :P
<cappiz> bigfoot- if you could please take a look :>
<cappiz> i guess there has to be some trigger made aswel :P
<Fifoxez> how would I delete every row from table1 WHERE table1.RowID = table2.RowID AND table2.Delete = 'Yes'; ?
<gleam_> delete table1 from table1 join table2 on table1.rowid=table2.rowid and table2.delete='yes'
<eIf> hello
<bluelotus> If a table has a column called state_id (for say) and has 10.000 companies, is it possible to select one company from each state? I.E. Select all from companies where state_id has not been found as of yet?
<eIf> i try to create an user in mysql: mysql> create user cybershop; ERROR 1064 (42000): 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 'user cybershop' at line 1
<eIf> can you tell why do i get this error?
<bluelotus> eIf: try grant
<eIf> i see
<Meaw> hello
<bluelotus> eif grant all priveleges on database.* to user identified by p***word
<bluelotus> or maybe it's "p***word"
<Meaw> i have dialupadmin php interface with mysql-server , when i try to add new user i got sql error
<cappiz> 'p***word'
<bluelotus> and I think I spelt priveleges wrong, too.
<Meaw> Could not connect to SQL database
<eIf> bluelotus it's workin, thanks a lot
<bluelotus> np. Goodluck eIf
<cappiz> eIf: grant all PRIVILEGES on db.* to 'user'@'host' IDENTIFIED by 'somep***word';
Return to
#mysql
or
Go to some related
logs:
wizzomafizzo
xhost+ FC
apt-get stdlibc
error while getting interface flags + vmware
ubuntu change firefox taskbar icon
#kde
waveforce gentoo
dovecot printscreen
yapgvb pydot
#perl
|
|
