logs archiveChat DB / Freenode / #mysql / 2015 / November / 14 / 1
mathys
the lock of the tables is used in mysam or innodb storage engine?
Miesco
Hi. What is expr in count(expr)?
danblack
from the output select count(0); select count(1); i'd assume its a silly expr. probably requires further analysys however
Miesco
Is this giving a foreign key? `create table boat (sid int references sailor(sid));`?
Is there a difference between that and `create table boat (sid int, foreign key sid references sailor(sid));`?
If there is a table called 'sailor' with an age column, how would I select the youngest sailor(s)?
Vacuity
Miesco: to get one of the youngest sailors: SELECT ... FROM sailor ORDER BY age LIMIT 1;
Miesco: if you need all sailors that share the youngest age, you can use a derived table. SELECT ... FROM sailor S JOIN (SELECT min(age) as age FROM sailor) as Y ON S.age=Y.AGE;
Miesco
Vacuity: Okay. I see now. It didn't work with this: select age from sailor group by age having age=min(age);
purefan
Hi! Im having problems setting up replication, ran Change Master To... said Query Ok, did Show Slave Status, only shows empty, if I try Start Slave it says error 1200
danblack
purefan: make sure the server-id is non 0 and different from there master's server-id
!perror 1200
ubiquity_bot
MySQL error code 1200: (ER_BAD_SLAVE): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
purefan
thanks danblank! 1 microsecond ago I realized someone issued a reset slave here and we lost the master_user and master_password, that was the problem :)
danblack
ack. taht would be the other casue. 'shows empty' should of recongnised that.
purefan
it would be nice if mysql were more descriptive about this error, checking and saying something like "your replication doesnt have a user set up" or something along those lines
yeah...
minor detail but mysql isnt great with errors sometimes
danblack
its the server-id mismatch that's harder to diagnose than an empty show slave status :-)
Miesco
Just to be clear. If you add 'references ...' onto a column definition, this will do nothing. Correct?
In order for it to work you need to type ', foreign key column references ...'. Correct?
"MySQL parses but ignores inline REFERENCES specifications (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification."
Would it work if I did an inline reference and then later did ', foreign key sid);'?
Vacuity
!t Miesco tias
ubiquity_bot
Miesco: Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok
Xgc
Miesco: Use innodb and a foreign key constraint. That's all.
Miesco: A foreign key constraint has requirements related to the target table / key.
Miesco: Make sure those requirements are also met.
Miesco: Tha's all you need ... and this table.
Miesco
Xgc: So you need to explicitly put the words 'foreign key'?
Xgc
Miesco: What does the documentation tell you about creating a foreign key constraint?
Miesco
Xgc: It says to use 'foreign key', but I just want to clarify this. Specifically that using 'references' without a 'foreign key' will do nothing.
Xgc
Miesco: The documentation is correct. There are some [optional] keywords. Some of the keywords are not [optional].
Miesco: Just look at the top of the documentation page to see the [optional] terms.
Miesco: There are other methods of creating foreign key constraints. MySQL doesn't support them.
Miesco: This case is similar to the ways a primary key can be created. col_name type PRIMARY KEY ... and ... col_name type, PRIMARY KEY(col_name) ... MySQL supports both of those forms. For FOREIGN KEY constraints, MySQL only supports one of the two general forms and only properly with the InnoDB engine.
Miesco
Xgc: How do you tell what a table's foreign key is?
Xgc: I type `describe stock` and it shows a MUL when I use (in create table) 'natcode int references ...' and it also shows MUL when I use 'foreign key (natcode) references ...'
snoyes
Miesco: use SHOW CREATE TABLE instead of DESCRIBE.
Miesco
snoyes: Does a foreign key actually do anything?
snoyes
Yes. If you attempt to insert an orphan row, it will error
If you attempt to delete or change a parent row, it will either error or cascade, depending on what you have defined.
Miesco
snoyes: And if you use references without 'foreign key' (e.g., `, int sid references sailor (id)`) then it won't attempt to insert an orphan row, etc?
snoyes
the "inline references" syntax is parsed but ignored
thus sayeth the manual
Miesco
snoyes: And a orphan record is a record with a foreign key to which it's parent table has been deleted?
snoyes
it's corresponding row within the parent table, yes
Miesco
Oh I see
snoyes
So if you delete a row in the parent table, the foreign key will either say, "no, can't do that, still have a child row", or "going to delete all the children too", or "parent is gone, we'll set the parent field in the child to NULL"
Miesco
I get it now.
snoyes
If you were to write a statement to JOIN the parent and child tables, you would probably use the same fields that the foreign key uses, but don't assume that the foreign key either prevents you from having to specify those fields in the join, nor that it is required that you only join on those fields.
It's not uncommon to see people think, "I have a foreign key, so it will automatically join on those fields." Not true.
LBV_User
hi all, I have some ids, and for each id I have data to update in a column. Is there any way I can make such update relating the id to the data, or do I need to make each update individually?
snoyes
depends on the update
LBV_User
the update is like 'update table set col=<data> where id=<id>'
snoyes
probably easiest to do separate updates, unless the data is something that can be calculated from the id
LBV_User
but I have a lot of ids, and for each I have unique data
snoyes
you _can_ do it all in one with something like, update table set col = case id when 1 then 'foo' when 2 then 'bar' when 3 then 'bat' end
salle
LBV_User: If lots means thousands you can insert the new stuff into temporary table and do an UPDATE with join
LBV_User: If it is about 5-10 rows CASE ... can be easier
LBV_User
well, I'm using the c client, maybe I can prepare once, and bind+execute for each pair
salle
LBV_User: If you are concerned with performance and you update lot of rows multi-table UPDATE can be much faster with proper indexes
LBV_User
salle: idk how many can it be, it is based on something user filters, so it can be 1 record, or the whole table
salle
LBV_User: Doing it in c would mean separate update for each row
LBV_User: Hm how does the user supply these unique values for each row then?
LBV_User
salle: the user selects the rows to be processed, and the client processes the rows and need to store back the access key to the database for each of these rows
client application, that is
salle
LBV_User: So user can select 500 rows and then he has to provide values for each of them?
LBV_User
salle: no, he can select 500 rows, but the application will calculate which value goes to each of them
salle
LBV_User: Aha! What calculation is that?
LBV_User: If it is based on other columns you have it can be done with single UPDATE
LBV_User
it is a bank calculation, and each bank as way to do so, something not easilly done in the database
not even with procedures/functions
salle
LBV_User: Why not?
LBV_User: If the information is stored in the database it is certainly possible.
LBV_User: Bank calculations are trivial from math point of view you know. No calclus needed\
LBV_User
salle: if it was trivial to do from the database, I would, but things can even go through a smartcard
well, anyway, there is no way to make such update then
salle
LBV_User: It depens on what exactly you want to update
purefan
Im trying to SET GLOBAL max_allowed_packet but even after logging out and back in the value doesnt change, Im trying to avoid editing my.ini and restarting, any idea?
salle
!t purefan doesn't change
!t purefan doesn't work
ubiquity_bot
purefan: Doesn't work is not a helpful statement. Was there an error? Unexpected results? Does it sit on the couch all day eating all your cheetos and ignoring the classifieds? Be specific!
LBV_User
salle: my whole problem with it is actually not the update
salle
purefan: SELECT @@GLOBAL.max_allowed_packet; SET GLOBAL max_allowed_packet = ... ; SELECT @@GLOBAL.max_allowed_packet;
LBV_User
some of our customers had the brightest idea of install the database on windows, and it got a really bad performace
purefan
salle: There was no error (said Query successful 0 rows affected). I expected that doing a show variables like max_allowed_packet would show the new value but it showed the old value
salle
purefan: Do what I told you
purefan
salle: Im on it
salle
purefan: SET .. always returns 0 rows affected
purefan: SET .. does not change any table so it can not report any affected rows
purefan
salle: I am aware of that, here is a screenshot of how it went: http://postimg.org/image/4ah6ia2e5/
salle
purefan: You didn't care to read the warning :)
purefan
Im googling how to
salle
purefan: SHOW WARNINGS every time you see warning
purefan: Why googling for god sake??? There is MySQL Manual
!m purefan show warnings
ubiquity_bot
purefan: See http://dev.mysql.com/doc/refman/5.6/en/show-warnings.html
purefan
salle: you know that SHOW WARNINGS will give you the info about warnings, but I didnt, so I need to ask "how to read mysql warnings"
salle
purefan: You better learn that command from day one you start using MySQL which in your case was long ago I believe
1 2 3 next »