phelixCould someone help me figure out why this query isn't actually sorting from highest to lowest price?
phelixSELECT * FROM `Listings` WHERE L_CITY="Somewhere" ORDER BY `L_SystemPrice` ASC LIMIT 30
thumbsphelix: SHOW CREATE TABLE Listings\G
password4joh
phelix`L_SystemPrice` varchar(255)
password4I just want to chack something , if I left join a table , lets say my base table is named 'a' , and the table I'm leftjoining is 'b' , if the join is on a.id = b.id , if b.id have more than one entry for each a.id , what happens?
password4doe the lines of a get duplicated for each value of b?
password4*does
thumbsphelix: and you wonder why sorting with a ... varchar column produces incorrect results?
thumbsphelix: SELECT '100' < '1009';
phelixYes, I just realized this.. however from what i was able to research something like this should work.
phelixSELECT * FROM `Listings` WHERE L_CITY="Boise" ORDER BY CONVERT(`L_SystemPrice`, INTEGER) ASC LIMIT 30
thumbsphelix: that'll perform poorly.
mishehuah prices as varchars.
mishehuwhy not decimal type?
thumbsbecause database planning is for wimps.
mishehuand commies?
phelixI didn't start this project. I just have to modify someone elses project. So I kinda need to make it work how it is
thumbsphelix: I hope this isn't a large table, then.
phelixno
phelixits not that big
password4lol , our coding standards here are 'all columns are to be of type varchar except for primary id and datechanged ' , :(
thumbspassword4: multiple tuples for a given value of a.id
thumbsnow, It's 1AM and I've been working since 6 AM, so I think I'll take a break.
password4so left joining can in fact increase the amount of rows returned
thumbsso can a INNER JOIN
password4thumbs: wow
password4i just thought i found a difference between mysql andmsswl
thumbsare you sure?
password4i just thought i found a difference between mysql and mssql*
password4sure about what?
thumbsthe difference.
password4well does not seem different now
thumbsinner and outer joins are pretty basic.
password4yeah
mishehuthumbs: 6am to 1am... I thought htey say sleep is for the weak, no?
password4thumbs: working for a company?
password4i dont have the motivation to work that many hours
password4maybe if the company i worked for double my pay :P
konradosMorning again, I'll try again. This is about terminology. Say we have this scheme: table Users, then tables "Posts" and "Whatever" with FKs to Users, so they are directly related to Users. And we have another scheme - table Users, then table Posts (with FK pointing to 'Users') and then Comments, with FK to Posts. Same number of tables. But ... now I'm looking for the right term, the "complexity" is different, i.e. in the first case it's
konradosthe "horizontal" complexity and in the second one, it's "vertical"? Or maybe, it's about the "depth" of relations? How to name it? The second scheme is more.... what?
jkavalik!t me about dynamic sql
ubiquity_botjkavalik: SET @sql := CONCAT('SELECT * FROM ', @tableName); PREPARE mySt FROM @sql; EXECUTE mySt;
jkavalikaaand, dynamic sql is not allowed in a function..
konradosHey, I was disconnected, did someone answer my question?
socommI've a table which has duplicate primary key is it possible to remove one?
jkavaliksocomm, are you sure? mysql should not allow specifying two primary keys
jkavaliksocomm, "show create table <tablename>;" - pastebin it if not sure
jkavaliksocomm, or possibly do you mean one primary key covering (containing) two columns?
no_gravityIs there a way to make bash show nice tables? Something like "echo 'select ..' | mysql | shownicetable"?
no_gravitycolumn -t !!!! That's it!
XgcI would have simply: SELECT * FROM nice;
MmikeHi, lads. How do I check from where mysqld reads it's configuration? I only see /usr/sbin/mysqld running, with no options or anything
MmikeI'm suspecting someone compiled-in the 'odd' defautls, but not sure how to check this
nscpis it possible to query the column names of a query?
nscpi mean, getting the column names as a resultset
Xgcnscp: See information_schema... or as an example of API specific behavior, see JDBC's meta data access mechanisms.
nscpXgc: is information_schema not only for tables?
Xgcnscp: You asked more than one question and they were not asking the same thing.
nscpXgc: what i meant to ask was: i have a query, and i wish to produce a resultset that contains the column names of that query
Xgcnscp: If you want metadata related to some result-set (generated by your query), JDBC provides an API for that. I don't know what API you are using.
nscpim just querying directly from HeidiSQL (not sure what API that is?)
Xgcnscp: SQL does not provide that direct level of access. But you could create a table using that query as the source of data and then look at the column names of that new table. Information_schema would allow to get that column name list.
nscpXgc: ok, i thought there might have been some other way. thanks for the swift help :)
Xgcnscp: I'm sure you noticed that the column names generated are shown by heidisql. It probably uses data returned by the database. Most APIs have a mechanism to obtain that detail, as I described with JDBC.
nscpfor anyone wondering, one way to do this using the "HeidiSQL UI API" is exporting the query resultset with column names :)
greekpicklickSELECT * FROM clients WHERE MATCH(search_text) AGAINST(? IN NATURAL LANGUAGE MODE) LIMIT 5
greekpicklickI have this query, but it only matches exact words
greekpicklickhow can I make it to match partial words
greekpicklickfor example match foo to foobar
jkavalikgreekpicklick, 'foo*' should match 'foobar'
jkavalikbut not sure about the mode specifics
phil22hello
phil22is it possible to get only 1 row every n in a query?
phil22i.e. row MOD 2
greekpicklickjkavalik: I was looking for some solution that would still allow the natural language mode work
greekpicklickwhich is kind of complicated I guess
phil22I can do "where id % 2 =0", but I "id" is unreliable
jkavalikphil22, a bit hard without rank(), which is not supported yet - there might be a way with user variables, but that is not reliable either, even when it is used often
phil22ok thanks
Xgcphil22: When MySQL implements window functions or if you swap over to MariaDB, it's trivial.
phil22Xgc: I have mariadb 15
jkavalikstill not sure how it is implemented internally - if rank() is taking order by into account, you have to have two WHERE checks? one before ordering and one after?
jkavalikphil22, quite futuristic ;) I think newest is 10.2 which is not yet GA
phil22ahhaha ok
phil22mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1
phil22I think it's 5.5 then
jkavalikthen not available
phil22ok
phil22so I'll stick client side filtering
phil22with*
Xgcphil22: You will like 10.2 when you're ready to upgrade.
phil22yeah, in centos timezone, maybe 3-4 years :P
phil22(I can manually upgrade of course)
phil22I thing I would in mariadb is transactions support for schema change
phil22it would be great
tanjphil22: that's planned i think for 10.3
markzendo database aliases exist in recent mysql versions?
markzeneg I want to select * from dbalias.table
markzenand dbalias actually point to somewhere else
toddynhoexists a class of functions or a module to add suport for json search in olders mysql versions ? like 5.0 ..
tkxxxHi. I'm looking to make a query to return a table from the following data. http://prntscr.com/ehgqbm I've tried lots of different ways, but I want it to basically display the lastname, points, timestamp from the ranking_logs table. If anybody could give advice it would be much appreciated.
jkavalik!t tkxxx joins
ubiquity_bottkxxx: Very basic join information: http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
tkxxxjkavalik: Will a basic join suffice? Since I think I need to 'go through' the mdl_ranking_points
jkavaliktkxxx, multiple joins may be needed, I am not sure which exactly from your description
sisveIs there anyway I can add a new nullable column, with a default value of null, quickly? It's a rather large table, ~2 million rows, and it takes 50 minutes for me to do it using the normal alter table stuff.
jkavalik!m sisve online ddl
ubiquity_botsisve: See http://dev.mysql.com/doc/refman/5.7/en/online-ddl-partitioning.html
sisveoh, those are my two favorite keywords at the moment!
jkavalikno, sorry
jkavaliksisve, https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
jkavalikthis one says that it should be possible (since 5.6 ?) in innodb tables
pilahello how can i optimize this query? select ((preis-(SELECT preis FROM history WHERE artnr = items.artnr order by id desc limit 1))) from items where artnr = 381779868560
jkavalikpila, show the EXPLAIN
sisvejkavalik: i've started a "ADD COLUMN column_name INT NULL, ALGORITHM=INPLACE", and while it is online, it's not very instant. ;)
jkavalikpila, the subquery is "correlated" (aka "dependent") - that means that it is executed fro each row the outer query visits - you might try to rewrite it as a join instead
pilahttp://pastebin.com/wvWRiuSh
jkavaliksisve, no idea about the speeds of it - for 2 millions of rows even "online" add index takes some time
sisvejkavalik: i'm adding a column, not an index.
sisvejkavalik: but yes, i thought a nullable column with a null default value could be a metadata change that says "the field exists, but if it's not present in the data row use null"
sisvejkavalik: a fantasy world. I'll just have to bother people at the miniature pingpong table while running the query.
jkavaliksisve, yes, that was just as a comparison, index is like 'select column, sort, write index', adding the column will probably be a bit more substantial even with this, but I have no idea how exactly it is being done
jkavaliksisve, I believe tokudb allows some such magic, innodb not so much
jkavalikpila, according to the explain, your query is executing 48millions of these subqueries because index on artnr is not used, what datatype is artnr?
pilavarchar 32
jkavalik!t pila about quotes
ubiquity_botpila: Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option).
jkavalikpila, "where artnr = '381779868560' "
pilayeah i forgot that
pilayou are correct
jkavalikpila, now the query has to read all the varchars, try to parse them as number and only then check if they match the constant
pilawhat type would be best?
jkavalikpila, column type depends on what you need - you just have to take it into account in WHERE comparisons, quote strings, do not quote numbers - you are comparing to string so the 381779868560 should be quoted as a string, because being numeric only does not matter in that case
pilajkavalik wow
pilathat reduced from 23s to 49ms o.0
jkavalikand the explain estimates from 48M to single row?
jkavalikhm, probably not single, but a very low number
pilayeah
iskorptixI've got database and table charset/collation set to utf8 but I still don't see arabic characters and I get a warning if I try UPDATE row with arabic character. What else needs to be changed in order for arabic characters to work ?
pilahttp://pastebin.com/PuPREnN8
jkavalikiskorptix, your connection - did you try commandline mysql client?
pilawhen i have something like : "select 1+2 as eq...." can i use that eq in where somehow?
jkavalikiskorptix, and the column may have a different charset than the table, so if it was already set previously, you did not change it by altering the table itself
iskorptixjkavalik: i think it does not matter as I try to update via mysql client and through phpmyadmin too, same issue
pilai don't want to repeat the calculation
jkavalikiskorptix, "show create table <name>;" to see the details
jkavalik!t pila alias
ubiquity_botpila: aliases established in the SELECT clause are not available until the resultset is returned (can be used in GROUP BY, ORDER BY and HAVING clauses). Aliases established in the FROM clause are available earlier (can be used in the WHERE clause).
iskorptixjkavalik: so all rows must be updated to follow new charset, is this what you are saying ?
pilaokay having eq > 0 worked
jkavalikpila, so no, you have to repeat it or work around it (if the expression is really long, making a derived table might be worth it - it might even be optimized away in newer versions - the optimizer learned to "expand" the aliases
jkavalikpila, HAVING cannot use indexes etc properly, and should not be used without GROUP BY... it often works in mysql but you should not depend on it
jkavalikiskorptix, "columns" mainly - but there might be some specific procedure to properly convert current data - you might instead try to create an empty copy of the table and copy the rows there - that way you can check if it is right before getting rid of the old data
iskorptixok thanks jkavalik , I've solved the issue
XatenevHi
XatenevI wanna select replace() as xyz
Xatenevbut with multiple replaces
Xatenevis there a more beautiful solution than nested replaces?
snoyesnot unless you create your own function
Xatenevokey dokey
paissadhello, how can i retrieve the name of the instance of my mysqld server ?
paissadthanks in advance
thumbspaissad: you mean the server id?
paissadi found it :) thanks .. i did a ps -edf | grep mysql <== and i retrieve it
pissfroghello
pissfrogi'm trying to decide on which data type to use in order to store UPC numbers
pissfrogany suggestions?
ss23int(12)
pissfrogthanks
ss23Just remember about zerofill
ss23!t pissfrog zerofill
ubiquity_botpissfrog: The optionally defined number after an INT data type represents the display width when used in conjunction with ZEROFILL. Please refer to: http://hashmysql.org/wiki/Zerofill
pissfrogour starts with a zero
ss23Just meaning that you can store integers different than just 12 digits worth in there. Some people aren't aware
pissfrogss23, thanks
pissfrogi just need to store 12 digits in there
pissfrogthat's it
pissfrogi need it to be fixed
pissfrogto avoid any extra characters from being entered
pissfrogss23, will it strip the leading zero?
ss23int stores integers, that's it. storing "000001" is the same as storing "1"
pissfrogi need it to store 012345678901
pissfrogi cannot have it drop the leading zero
ss23It will store 12345678901, and you can zerofill it to however you want
ss23Did you read the link on zerofill, pissfrog?
pissfrogi have
pissfrogthanks
snoyespissfrog: don't use an int.
snoyesit's a string.
snoyesso use char(12).
snoyesIt's a string that happens to only contain digits, like a phone number, but it's not an integer because you don't do math with it.
jbrueheYou are so right, but I fear that's like Sysiphos' task.
domasoh
domasOracle added sharding!!!! http://www.oracle.com/technetwork/database/features/availability/sharding/overview/index.html
thumbsdomas: oh my
gajusRight, its been a while. I want to get a list of movies sorted by the distance to the nearest location where the movie is playing. https://gist.github.com/gajus/4e6ac19cc2d791ac86fc098abb3163c5#file-test-sql-L19-L20 This query will throw an error about sql_mode=only_full_group_by. I understand the reason for the error. I don't remember whats the solution.
gajusI am sure this is going to be something FAQ of #mysql, just waiting for the right !reference.
passagegajus: every column in the select that isn't in the group by has to have an aggregate function
gajusoh, wait, so MIN should just work.
passagegajus: you can use any_value(col) around offenders, but the value returned is just that - meaningless
passagegajus: the default sql_mode for group by used to allow the meaningless return on its own :-)
gajusisn't MIN an aggregate function? based on my quick experiement, it isn't
passage!m gajus aggregate
ubiquity_botpassage: Sorry, I have no idea about that manual entry.
passagegajus: sure it is
passagegajus: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
passagegajus: you can find any_value() at https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
pissfrogsnoyes, thanks for pointing that out. i have made the changes
pissfrogit wasn't working anyway
gajushttps://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html
gajusso the only solution is either subquery or a left join
passage!t gajus about groupwise max
ubiquity_botgajus: http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html
Xgcgajus: So did you actually try MIN? If you had problems, post the URL of your new attempt.
Xgcgajus: The only problem with the question you initially asked is that you forgot to use MIN. You just wanted the MIN distance for each movie. No need for groupwise max for that specific query.
Xgcgajus: Later, if you decide you want other detail that is not functionally dependent on the movie_id, you can dive into groupwise max.
gajus> <Xgc> gajus: The only problem with the question you initially asked is that you forgot to use MIN. You just wanted the MIN distance for each movie. No need for groupwise max for that specific query.
gajusNever mind.
gajusSorry.
gajusI see.
gajusI messed up while debugging.
gajusI have added MIN, but I have also added v1 into the SELECT. Which is when I tried the query I got the error and it left me confused.
snoyesgajus: It is mandatory that I warn you that storing longitude and latitude as POINTs is an abomination.
gajussnoyes: huh, how come?
snoyesa POINT reflects a place in cartesian coordinate space.
gajuswhich is expected
snoyeslatitude and longitude is not a cartesian coordinate
snoyesthey are angles
gajusRight. I will need to read up to comment on that.
snoyesI guess if you're careful to use ST_Distance_Sphere like you're doing, it's designed to handle it
gajusI was about to say... I've been using it for quite a long time in production. To think we'd have noticed issues.
snoyesbut if you try to use those points for something else, like with bounding rectangle, you'll run into problems if points are near the international date line, or poles.
gajusjust to add to my reading list, whats the strategy for storing cartesian coordinate data and querying upon it?
snoyesUTM, perhaps
snoyesuniversal trans mercator
snoyesWhen we advance to where ST_SRID is properly set up and enforced and everything, then it might be ok, because you'll be able to clearly show that this POINT contains angles and not meters and the functions will handle it properly.
tkxxxI'm so stuck on this query - I'm not good with databases. I'm one query away from getting a leaderboard working for students in our school to track points when they answer questions. I need a query to return the users' name, points and the date/time it occured. If anybody could help it would be much appreciated. Here are my tables (id's are related)
tkxxxhttp://prnt.sc/ehgqbm
snoyesand what's the query you've attempted?
ThePendulumhi!
ThePendulumI'm looking to remove a row from either of two tables (it will exist in one of them, not both, but which one is unknown)
thumbs!n ThePendulum coalesce
ubiquity_botThePendulum: See http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce
ThePendulumDELETE `foo` , `bar` FROM `foo`, `bar` WHERE `foo`.`id` IN ('12345') OR `bar`.`id` IN ('12345') <-- should this not work, theoretically, to remove a row with ID 12345 from both/either table?
thumbserm
XgcThePendulum: It's non-standard.
ThePendulumthumbs: I'm not quite sure I'm following the relevance of coalesce here
thumbsThePendulum: premature response.
ThePendulum:p
ThePendulum!next
XgcThePendulum: A potential problem is if the <table reference list> involves matches for only one of the table and null for the other. It's not clear the delete would succeed in that case. Have you tested it?
ThePendulumit'll always have a match for one table and not the other though
XgcThePendulum: You really don't want to use that DELETE. Trust me.
ThePendulumit's that you have those pretty blue eyes to trust
XgcThePendulum: You are about to lose all the columns in one table.
XgcAll the rows, that is.
XgcThePendulum: You have a cross join. You might not want to test that on important data.
XgcThePendulum: Try looking at the corresponding SELECT result first.
ThePendulumI don't test /anything/ on important data, lol
XgcThePendulum: Think about what happens with ... x cross join y WHERE x.x = 1 OR y.y = 2 and only x matches.
XgcThePendulum: When x matches, x joined with all rows of y will be returned.
Xgcx = 1 paired with all rows of y.
XgcThePendulum: I suspect you didn't want that.
ThePendulumI'm surprised it's this challenging to essentially treat 2 tables as one extended table for an operation
XgcThePendulum: That's not 2 tables as 1.
XgcThePendulum: That would be a UNION. That's also an indication of a design mistake.
ThePendulumI know the cross join isn't; what I'm aiming for is, roughly
ThePendulumI understand mysql doesn't have a partial index
XgcThePendulum: You asked if there is any reason the cross join version should not work, suggesting you think it would work. Well, it might *work*, but also eliminate some rows in one table and all rows in the other table. At least one table will be empty at the end, if any match is found.
XgcThePendulum: So now the question is, what did you intend, since that SQL doesn't represent your requirement.
XgcThePendulum: The only potential way is to use an outer join of some kind. But you probably wanted a full outer join. Remember, the standard doesn't support multi-table delete at all.
XgcThePendulum: The intent of that support in MySQL is that you will have a relationship and related rows in each table to delete. Anything else is probably dangerous.
ThePendulumI didn't predict a cross join is what that'd effectively do
XgcThePendulum: x JOIN y WHERE x.x = 1; (assume y would fail for a moment). This is a kind of cross join.
XgcI just left off the y criteria, since it's false for the sake of this part of the discussion.
ThePendulumYeah, I actively avoided using JOINs because of that behavior
XgcWhen you have no effective relationship between the tables, it becomes a cross join.
ThePendulumright
XgcThePendulum: x, y is a cross join.
ThePendulumalright, that explains some of the results
XgcIt's a form you should never use.
ThePendulumessentially using two separate queries would really be the easiest?
ThePendulumit's a suggestion I came across that seemed more than reasonable, my bad
XgcThePendulum: *nod* That's probably your only option, assuming you don't want to redesign.
ThePendulumI could redesign and I do have a couple of other wonderings that might lead me to that anyway, but atm that's not the first option indeed
XgcIf the tables are really unrelated, the deletes should be separate.
ThePendulumessentially they're supposed to be the next best thing to a partial index; one table contains notifications, the other contains queued notifications, and a periodic job moves them from the queued table to the other one; the idea being that I don't need to find all the queued jobs inbetween all the old ones
ThePendulumhowever it seems like a pretty insignificant thing to consider given that when a user fetches their notifications, they're doing exactly that (finding relevant notifications in a big pile of old ones) except with their user ID
XgcSo one might be a kind of historical store.
ThePendulumyes
XgcSeparate deletes it is, unless you have some other FK relationship that might drive the delete.
ThePendulumnot at the moment, since the two tables exist essentially in parallel with nothing linking them other than similarity in design
ThePendulumoutside the context of the application they have no relation
ThePendulumI'll have the delete count from the first query so if that's 1 I know I don't have to run the second one anymore
ThePendulumsince they should never exist in both tables at once, that would imply a bigger problem
arthurlhi guys- can someone help me understand what this error means? ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (`reporting`.`sales_records`, CONSTRAINT `sales_records_ibfk_3` FOREIGN KEY (`sales_record_master_id`) REFERENCES `sales_record_masters` (`id`))
snoyesyou're trying to insert a value into reporting.sales_records, and there's no matching value in sales_record_masters
carpediembabyHello, I have a problem with mysql(5.6) installation on Windows 10. I am unable to login to the server but it is unclear to me what the cause may be. I have tried to reset the password using this link : https://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html however i get the following output: http://pastebin.com/W8TbqnYb
snoyescarpediembaby: the important lines are: InnoDB: .\ibdata1 can't be opened in read-write mode
snoyesInnoDB: The system tablespace must be writable!
carpediembabyMy suspicion is that something very wrong happened when the machine upgraded from windows 8 to windows 10. I cannot think of anything else as I haven't used the machine much.
snoyesmodern installations on Windows put the data directory in ProgramData instead of in Program Files so that the Windows account management control thingy doesn't cause that problem.
carpediembabysnoyes: I am looking this up. I didn't suspect there could be anything wrong with respect to this, since I didn't change anything.
snoyesIF you already have data in that datadir, you can move it and update the .ini file accordingly, or you can go disable whatever that bit is in Windows which prevents MySQL from writing inside of Program Files
carpediembabysnoyes: I have data that I would very much like to recover
snoyeseither approach should get it back.
tkxxxsnoyes: I think I have finally got the query I needed, but it wont sort! Is it because I've done something wrong on the query? http://pastebin.com/Ef0LDvVD
snoyesyes, you have a semicolon on line 11 that should not be there.
tkxxxsnoyes: wow! I definitely need some practice to get better. Thank you for your help though snoyes! Can finally get our inter-school competition running!
carpediembabysnoyes: 1. I don't understand what the location of this data directory is 2. I don't know why mysql can't write to it.
snoyesYour data directory is C:\Program Files\MySQL\MySQL Server 5.6\data\
snoyesI would move that to C:\ProgramData\MySQL
snoyesand then edit C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini and change the datadir line to point to the new location.
carpediembabysnoyes: I will try that. But there seems to be something wrong. That file is 12MB and I have a lot more data than that in the database..
snoyescarpediembaby: It's also possible you're using the wrong my-default.ini
snoyesmy-default.ini is supposed to be an example config file. You might normally run with a different one.
snoyesmy.ini, maybe someplace else.
snoyesExamine the mysql service in the control panel and see what it uses when it starts.
carpediembabysnoyes: Ah, you are right. it uses a different file and a different data directory is mentioned inside it: defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" and it says datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data\"
snoyesthat's more sensible
snoyesso use that defaults-file in your command.
carcrashHey, Say I have a table with a column containing 'A' or 'U' how easy/possible would it be to set up a query to select 'Available' or 'Unavailable' without altering the table or doing some sort of join?
carpediembabysnoyes: it works better. I only get one warning about timestamp but nothing more. And I am still not able to login with the newly set password
snoyescarcrash: SELECT CASE column WHEN 'A' THEN 'Available' WHEN 'U' THEN 'Unavailable' ELSE column END AS column FROM table;
carcrashsnoyes, Thanks!
tkxxxsnoyes: how would I define returning a 'timecreated' column, when both tables I'm querying from have that field? I've commented the issue http://pastebin.com/yBhk6FrJ
snoyestablename.fieldname
snoyesor tablealias.fieldname
tkxxxsnoyes: Perfect!
carpediembabysnoyes: I was wrong (again). In fact mysqld was already running so nothing was happening. I managed to run it with the command from the documentation but I am still not able to connect. I get access denied when I try connecting with the new password while mysqld is running with the command from the documentation...
snoyescarpediembaby: use the method at the bottom of the page, where you stop mysql and restart it with skip-grant-tables
carpediembabysnoyes: I am able to start mysqld using that method and then I can login and change the password as instructed. However, when I stop it and start the mysql service, I cannot connect with the new password
snoyesis there some strange character in the password that might be eaten by the shell?
snoyeshave you double checked your user name?
carpediembabysnoyes: i am setting it to a mundane combination root:password
carpediembabyi can change it later once i get access to the server
snoyeswhich version of MySQL?
carpediembaby5.6
snoyeswhen you restart with skip-grant-tables, FLUSH PRIVILEGES; SHOW GRANTS FOR 'root'@'localhost';
carpediembabyyes, i did all of that as is mentioned in the documentation. one strange thing is that i can't stop the server properly when I start it with skip-grant-tables
snoyeswhat does it say when you do that show grants?
carpediembabyi get a warning regarding implicit timestamps. which is normally not present since explicit timestamps are enabled in the ini file
carpediembabysnoyes: http://pastebin.com/PVLx2T9P
snoyesok, that looks fine
snoyesnow restart normally, and show the full attempt to login
carpediembabysnoyes: now, I have to end the process. the command-line where i issued the command 'mysqld --skip-grant-tables' is not responsive anymore
snoyesopen a new command prompt
snoyesmysqladmin shutdown
carpediembabyand then restart the service?
snoyesyes
snoyesof course, I would have just put skip-grant-tables into the [mysqld] section of the my.ini anyway, and restarted the service.
carpediembabyonce i restart the service, and say: 'mysql -h localhost -u root -p' and then put the password, i get access denied for user 'root'@'localhost'
snoyescarpediembaby: Since it's Windows, rebooting is sometimes the answer, although I don't know why it should matter. Other possibilities are that you have more than one instance running and you're connecting to the wrong one, or that the password has been typed incorrectly
domohi everyone. I have a table of "users" and there may be 1 or more rows per user_id on a given Y-m-d. I'm looking to grab the latest record for each user for a given date. I've tried a bunch of different queries, but nothing seems to satisfy besides a union all.
domohere's what works: http://pastebin.com/TZChfBBj
domoif I take this path, I would need to generate a select for each user ID that I want which seems wrong
domoany ideas/input would be appreciated
thumbs!t domo groupwise max
ubiquity_botdomo: http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html
salledomo: SELECT user, MAX(date) FROM your_tbl GROUP BY user;
domohaha maybe I'm just tired.. sigh thanks. let me try that
domois it OK to keep rewinding a mysql pointer?
domowhat's the performance cost of doing that
aradapilotyou mean a cursor?
aradapilotlike in a procedure?
passagea cursor on a fishing reel?
passageor a VHS cassette?
aradapilotit's common to, say, have a cursor reading from a temp table and reopen it for different sets
aradapilotor not