micesi delete all rows from a table and then try to import data from a dumpfile and mysql always says duplicate entry for key 'PRIMARY'
micesi look at the sql file and it shows alter table disable_keys before and enable_keys after so i dunno what's going on
micesanyone can help
micesthumbs: ^
jkavalikmices, innodb table? disable keys is for secondary keys (and iirc myisam only anyway), primary key in innodb has to be checked and anyway it would be really bad if the duplicate key were inserted
jkavalikmices, check the dump for the duplicate value
micestheres no duplicate values in the dump it happens on the first insert
micesjkavalik ^
jkavalikmices, well.. then there has to be something left in the table - did you delete the rows in a transaction? did you commit it?
micesi deleted all rows i didn't commit
micescould it be because mysql is autoincrementing as the records are being inserted?
micesand then the file conflicts with mysql trying to insert the same value
micesshould i set autoincrement off before the import
jkavalikif you did not commit and it was inside active transaction, the rows are not deleted yet - you can check by connecting in a different window and checking the contents of the table
micesjust type commit?
jkavalikdump takes the original autoincrement value and uses that when importing, not generating any new
jkavalikfirst try the different connection to see what is in the table
micesnothing in the tables;
micessame thing, Duplicate entry '0' for key 'PRIMARY'
miceswhen i dumped the database i used --skip-triggers could that be the problem?
jkavalik0 is not an usual autoinc or primary value..
jkavalikwell, if there was some (before insert) trigger which managed the primary key then it might
kegsterall i have is SFTP access- is there a way i can run mysqldump?
raymondkegster: No.
DmitryVhello, friends! Could someone please advice me: what is impact of enabling partitioning in mysql? I've tried twice and got huge CPU consuption boost, making partitioned tables in particular unusable even for select - are there any known pitfalls?
DmitryVIt should not be like this in normal world, and I don't know where to look - something is wrong with my particular setup, but I can't understand what. Here is the guide I've used for partitioning enable, if that's relevant: https://www.zabbix.org/wiki/Docs/howto/mysql_partition
jkavalikDmitryV, why do you want partitioning?
jkavalikDmitryV, http://mysql.rjweb.org/doc.php/ricksrots#partitioning
mrpackethead_man i'm totally gutted.
DmitryVjkavalik, I want partitioning because old data removel costs too much. thanks for the link, reading it.
DmitryVI have constant stream of data coming in and constant need to delete old data. I want to make old data removal process worthless.
jkavalikDmitryV, ok, thats a good reason, read the guide - problem with partitions is that if your queries hit many partitions at once, it takes more time than with a normal table, so maybe you might need to fix the queries first
DmitryVI can't, I have third-party application. About many partitions: I have something like 14 partitions, only first have data (I cleared table beforehand) - does querying 13 empty partitions could do any trouble?
DmitryVI have 150Gb table and it works ok, and 3Gb table after recreating and partitioning give me slow queries, not because of IO but because of CPU
DmitryVMySQL version is 5.7.17-11, Percona.
DmitryVAFAIK there are no known performance issues with Percona MySQL version, only boosts. And I use InnoDB tables.
jkavalikwell, check the queries - use EXPLAIN to see what they are doing, maybe you are just missing some index (or some current one became noneffective after the split)
DmitryVthanks! I'll try.
thms_I have a database that I try to export
thms_but it contains relationship to other base. I would like to fully dump this base and the relations so I can import it on it's own on another server, without needing access to other base while importing
thms_https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer as it seems
tinyhippoI have a query that I can explain in words but I can't seem to express in SQL - could someone help? I have a table of successful logins, and I want to list all of the rows that have had more than one successful login for that user id, within the last 10 minutes
yegzselct userid, count(successful) from table where time>=systime-10 group by userid having count(successful) > 1 , then match with the table.. something like that perhaps? (dirty, am sure lotsa better ways to do it)
tinyhippoyegz: almost what I want
aienaI was reading http://stackoverflow.com/questions/4788644/select-latest-record-in-table-datetime-field . One PaulC states 'This answer has the same problem as the first. Basically, you are assuming that every record has a different date, which will work until the database gets busy. ' what does he mean by that
aienawhen the database gets 'busy' can two dates be identical
DmitryVjkavalik, here is result of explain. very simple select takes 6 seconds: http://pastebin.com/ZqkyzbuU
jkavalikaiena, when you get so many inserts that there is probability of two happening in the same second
DmitryVaiena, that's the case I guess - two entries same date is higly likely event for any DB with load
aienaDmitryV, So it is better if the app generates the datetime as opposed to now()
jkavalikDmitryV, sounds like just one partition is being read, that should be good.. and "rows" is 1
DmitryVaiena, it depends on what you want. If your target is to get unique "date", you could as well insure it in your app.
jkavalikaiena, requiring unique date is not the way to go imho - when you get so many users that the average gets over one insert per second, you just won't have enough values to use (with second precision at least)
DmitryVjkavalik, I've got 100% cpu usage after switch to partitions. If I'll clear tables and recreate them from scratch without partitioning, CPU will get back to <5%.
jkavalikDmitryV, well, even just multiple partitions scanning should be IO constrained most of the time, not cpu; cpu often shows when lot of rows get "generated" by joins.. can you check stacktraces at random intervals during the query? (gdb etc)
aienaDmitryV, actually I dont expect there to be only one second difference. I am planning to have an admin interface where we have 3 columns service_req_id(FK),assinged_to(FK), assigned_from(datetime) then the admin can assign a task to who ever and it gets recorded. Since there only the admin can assign staff to a request I think load on insert into this table will be low
DmitryVI'll check, jkavalik. following seems to reduce CPU usage to 70% (compare to pre-partitioned 5%!): set optimizer_switch='index_condition_pushdown=off'; set optimizer_switch='engine_condition_pushdown=off';
DmitryVaiena, with such low load jsut use SQL function for date and don't mind.
DmitryVaiena, and answer from stackoverflow page you mentioned will work well.
aienawhich of those answers the subquery one by PaulC
aienabasically since we will maintain a record of current and past assigned people to the task I wanted to see if there is a possibility to get the most recently assigned staff
aienaso I was researching first for the design
DmitryVaiena, everything depends a lot on how your application is designed. If you are not afraid to spend some time, I suggest to look into opensource projects which does same thing inside them and see how they do it. For Python - Django could do, for example.
DmitryVjkavalik, could you please give me a clue on what I should look for in debug output?
jkavalikDmitryV, not sure myself, was just an idea
jkavalikDmitryV, if ICP seems to be the problem maybe try different index which could help the query?
DmitryVjkavalik, that's pretty messed up. I've thought I could manage to find the problem without so low-level tools. Problem with indexes and everything - schema and indexes on it are standard for this software, and noone around me have such problem.
DmitryVI could try to introduce other indexes, but it's unlikely will fix the problem - noone else have problems with base in current state after introducing partitioning.
jkavalikDmitryV, what is the mysql version? maybe something which was solved already?
DmitryVPercona Server, mysql Ver 14.14 Distrib 5.7.17-11, for debian-linux-gnu (x86_64) using readline 6.3
DmitryVit's latest you can get.
DmitryVhttp://stackoverflow.com/questions/39023651/mysql-5-7-strange-perfomance-reduction-with-order-by-asc-desc-on-partitioned-tab might affect me or might not.
aienaDmitryV, thanks
DmitryV"Based on MySQL 5.7.17, including all the bug fixes in it, Percona Server 5.7.17-11 is the current GA release in the Percona Server 5.7 series."
jkavalikDmitryV, does the explain change a lot when you disable ICP ?
DmitryVI've reverted it to clear non-partitioned scheme now to see which spikes come from app on clear DB and which are should be credited to partitioning itself. I'll check it later and will tell you.
rclsilverhello, with MySQL Community 5.7, I initialized an instance with the command `mysqld --defaults-file=/etc/mysql.d/11-mysql01.cnf --user=mysql01 --initialize-insecure`. This instance is managed by Puppet, and I need to have a 'root'@'localhost' without password. When i try to connect to the isntance, without password, i've an access denied: Access denied for user 'root'@'localhost' (using password: NO) -- Any idea about that?
takane2rclsilver: Are you using a rpm install?
rclsilvertakane2: yes, and i'm using mysqld_multi
rclsilver(this server hosts severals mysql instances)
takane2I had this issue when I was trying to make an ansible playbook to install mysql 5.7. The rpm has a post install script that initializes it automatically. So I masked the mysqld unit before I installed mysql.
takane2Then I was able to initialize it my way, then unmask the systemd unit.
rclsilverin my case, my datadir is different from the default (/var/lib/mysql01) then, the initialization done by the postinstall scripts has no effect
rclsilverand in logs, i've the line "[Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option." but, when i try to login whtout a password, i've access denied
takane2And you don't see anywhere else in the log the random password correct?
rclsilvernothing, yes
rclsilveronly this message
takane2Maybe puppet is trying to connect with Maybe that user isn't being created?
rclsilverby hand it doesn't works too
rclsilverand in logs, access denied is "'localhost'" :/
takane2oh yeah, durp.
rclsilverstrange !
takane2Yeah, this is a odd one.
takane2Have you tried connecting with TCP?
rclsilvertcp & socket yes
takane2Can you get in with TCP and see what's up with the socket user?
rclsilveri can't login neither with tcp
takane2Well shoot, I am stumped. Maybe someone else knows something else you can try? Look for bug reports too.
Squarismanyone used the json format?
Squarismis it production ready or experimental?
Squarism(assuming mysql 5.7.x
jbrueherclsilver: Start the MySQL server with "--skip-grant-tables", in that status a connect as root without password should work. Then, do "select user, host, authentication_string from mysql.user where user = 'root'" and check/show the result. That said, IMO a passwordless root account seems to be gross negligence.
passage--skip-grant-tables gives any connection unlimited privileges
passage!t rclsilver about reset root
ubiquity_botrclsilver: Stop the mysqld server. Edit my.cnf or my.ini, add skip-grant-tables under [mysqld]. Restart mysqld. Connect with mysql -u root and do FLUSH PRIVILEGES. If 5.7+, do: ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPassword'; If 5.6 or earlier, do: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newPassword'); Remove the added line from my.cnf and restart mysqld.
DmitryVjkavalik, this fixed the problem it seems: set optimizer_switch='index_condition_pushdown=off,engine_condition_pushdown=off';
DmitryVjkavalik, this bug is strange;( Who knew what a bugfix could introduce something like that?
jkavalikDmitryV, well, you could try to report that to percona (they can decide if it is their code or upstream, unless you can test original mysql 5.7 too)
DmitryVit's already reported
jkavalik"using index condition" is supposed to be better than "using where" in almost all cases (definitely when the same index is being used), but with partitioning there may be weird optimizer bugs still
DmitryVhttp://stackoverflow.com/a/41215319/961092, https://bugs.mysql.com/bug.php?id=84107
jkavalikah, https://bugs.mysql.com/bug.php?id=83470
waqstarWill this variable: https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_tmp_table_size - Limit the size of tmp tables created during a large joining select statement?
kegsterIf I have a database that only will import to 5.6, can i export it a certain way that will work with 5.5?
LyndsySimonI have a JSON object that represents settings for a front-end widget. I'm running MySQL 5.6.x. Is there anything I should think about if I just shove it into a TEXT column?
snoyesLyndsySimon: if you don't intend to manipulate it at all within MySQL, then it doesn't matter, although I might shove it in a blob instead of a text so there's no concern with character sets.
snoyesIf you want to be able to search it, modify it, or index it within MySQL, then consider upgrading to 5.7 so you can use the native json type.
LyndsySimonsnoyes: Thanks. I don't anticipate any issues with non-latin characters, but I don't see what it would hurt shoving it into a blob.
LyndsySimonYeah, I agree. I just spoke with our devops folks, and they're not ready to do that yet. I don't think this particular use case justifies rushing the change.
LyndsySimonNot to be sacrilegious in this channel, but I prefer Postgres, and us hstore quite a bit. This is for a Rails app though, and MySQL is the Rails Way™.
BrskiI have an array(101,166,102,167) and I want to get all entries that contain any of the values in the array. Is there a way to do this dynimically as the array changes?
green-is it possible to modify tmp_table_size and max_heap_table_size for a single query (other than issuing queries to modify those variables before and after)?
kegsterwhat ver of mysql ships with centos 7.3?
tanjgreen-: no, you can only modify system variables with SET SESSION ... =
green-kegster: pretty sure maria ships w/ centos
tanjkegster: mysql is no longer in centos. MariaDB is the default DB
kegsterok cool
kegsterit should handle a 5.6 impor then
tanjwithout problems
green-tanj: and so if I up tmp_table_size via SET SESSION, that change will only apply to queries issued ebfore the connection is closed?
green-okay, that’s much preferable to running big tmp_tables as a default, only really needed for these large GROUP BY queries
snoyesBrski: MySQL doesn't have arrays, so this array must exist in some application. That application must send a query when the array changes, but you can do SELECT * FROM someTable WHERE field IN (101, 166, 102, 167); as often as you like.
Brskisnoyes, Yeah, I just found out about IN. Works like a charm
passageBrski: but there are limits to the size of the IN() statement. max_allowed_packet limits
BrskiUsing PHP: implode(',', array_map('intval', $myarr[0]));
Brskipassage, Ohhh okay
passageBrski: it's large, though. if you get really big, use a temp table and a join query
Brskipassage, I guess I'm not going to hit that limit with less than 100 integers+
passageBrski: show variables like 'max_allowed%';
passageBrski: it's a configurable variable, measured in bytes.
passageBrski: with a hard-coded limit of 1GB.
Brskipassage, I'll never hit that limit. If I do, I'm doing something very wrooong.-
passageBrski: what IN() does is get translated into WHERE x = 1 OR x = 2 OR x = 3...
Brskipassage, Yeah, that's great. Exactly what I wanted, but I didn't wanna make a function to generate the statement
passage Brski fine. remember that NOT IN() is also useful :-)
Brskipassage, Thanks!
Brskipassage, WHERE `cid` IN (101,166,102,167) + other AND statements?
BrskiCan I do WHERE `cid` IN (101,166,102,167) + other AND statements?
passageBrski: sure. just watch your logic. parens are helpful
Brskipassage, Alright :)
passageBrski: where cid in(...) OR (a=y AND b=z);
passageBrski: where (cid in(...) OR (a=y AND b=z) AND foo=bar);
Brskipassage, so for example WHERE (`cid` IN (104,172,173)) AND (`start_date` >= 2017-03-01) AND (`end_date` <= 2017-04-01) ?
era-1Our company recently lost access to the Administrator password for our server. We are able to log in to SQL Management Studio though using 'sa' account. Can someone point me in the right direction to add a local administrator account through here or is that not possible? Thank you
passageBrski: I'm not sure your counted your left and right parens correctly, but you can figure it out
Brskipassage, I might have left some out :P
passage!t era-1 about reset root
ubiquity_botera-1: Stop the mysqld server. Edit my.cnf or my.ini, add skip-grant-tables under [mysqld]. Restart mysqld. Connect with mysql -u root and do FLUSH PRIVILEGES. If 5.7+, do: ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPassword'; If 5.6 or earlier, do: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newPassword'); Remove the added line from my.cnf and restart mysqld.
era-1passage: Thank you
passageera-1: is this mysql or mssql?
era-1We are using Windows though and can't stop the MySQL server -- we were hoping we could scroll to 'Security > Logins' and create new Admin user there for local remote desktop access?
passageera-1: I'm confused. you've lost access to the operating system admin account?
era-1Yes sir
era-1But we can log into Management Studio with 'sa' account still it has a different password that we fortunately had
passageera-1: wow. well, surely there's a solution to that if you have console access. google is your friend
era-1No console access, the server is hosted at Amazon EC2
era-1The person also lost the servers 'keypair' for all the other servers -- we are trying to figure out what to do
passageera-1: outside my wheelhouse, sorry. good luck.
era-1Thank you
passageACTION sails about in a very constrained wheelhouse
nighthawk663I'm seeing something I've never seen before. I dumped a user's database with the normal mysqldump command, and now I'm restoring it back with the standard input < method
nighthawk663And somewhere mid-dump, suddenly it stops restoring to the database and starts spewing out onto the console
nighthawk663So I'm guessing something is somehow escaping the dump and then just vomiting all over my CMD session.
nighthawk663Any ideas how this could happen and how I can get the schema to restore properly?
takane2nighthawk663: It is reproducible?
takane2I bet there is some funny control character in the dump.
nighthawk663Yeah, I've done it a couple times now but only with that schema
nighthawk663I actually thought it was a problem with my restore script at first because I was restoring a number of schemas. Luckily, that one was the last one so it was much easier to spot :-P
nighthawk663Then I just restored that one on its own, outside of a script, and the exact same thing happened
snoyeswhat exactly is showing on the console?
nighthawk663Dump contents.
snoyesso you're seeing INSERT INTO... statements?
nighthawk663Much of the database is blob-type data, so it's just a stream of that junk
snoyesAh. I'm inclined to agree with takane2, and suggest using the --hex-blob option to mysqldump next time.
snoyesbyte matched EOF or something.
nighthawk663sorry, byte matched EOF?
snoyesone of the bytes in the blob is the "end-of-file" character
takane2grep '[[:cntrl:]]' dumpfile.sql
takane2^ try that and I bet it will find some
nighthawk663sorry; windows.
nighthawk663(not my fault)
takane2Well any similar utility should work. Or you could just try making the dump again with the --hex-blob option snoyes mentioned.
snoyeswindows has "findstr" rather than "grep"
snfgfcan somebody tell me where in this manual page, it says the name of the variable to set in my.cnf for the error log?
snfgfeverything i see are options to be passed to server
ss23snfgf: Hmm, uh
ss23Normally it's the same without the --'s, but I'll see if I can find a page
ss23The syntax for specifying options in an option file is similar to command-line syntax (see Section 5.2.4, “Using Options on the Command Line”). However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line. For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines in an option
ss23file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.
ss23snfgf: ^^
snfgfss23, thanks :)
green-if adding a few extra fields to the GROUP BY clause takes a query from 5 mins to 18 minutes, what’s the most likely culprit? Sorting time? This was my first guess but moving the sorting to disk by upping the tmp_table_size is only shaving off 2-3 minutes.
thumbs!m green- group by op
ubiquity_botgreen-: See http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html
green-thumbs: there’s a covering index in play here, but the two added GROUP BY columns aren’t columns, at least not exactly. They’re values derived from columns— in this case year(datetime col), month (datetime col). So I guess that’s causing a beating.
thumbsgreen-: so what does EXPLAIN say?
green-index, where, temp table
green-had filesort as well, but i got rid of that via ORDER BY NULL
green-as I don’t need mysql doing the sorting
thumbsgreen-: can you show us?
thumbs!t green- idfk
ubiquity_botgreen-: Please paste your query, the EXPLAIN select.., and the relevant SHOW CREATE TABLE/s in one pastebin with the sql formatted so it is readable
green-sure. Need to double check I’m not wrong about the extra group by cols causing the issue. re-running queries now to double check
green-which means it’ll be at least 20-25 minutes :)
green-honestly this comes down to me losing track of this query’s evolution. during simply testing in basic form i was getting reliable 5 minute completion times, then I added a few things I considered inconsequential to get it ready for use in an application and now can’t get result times better than 16-18 minutes … only now I can’t seem to isolate what caused the jump
thumbsgreen-: without seeing the idfk items, it's impossible to comment further
green-thumbs: understood. I’m just thinking out loud because I’m an idiot and it soothes me
thumbsgreen-: I don't think the running commentary is helping much.
green-also understood
snfgfYou think it's a bad idea to have a website that SELECTs all top level pages, then loops through each of these and perform separate SELECTs for their children? Is this a normal approach for a website? Because this is running about 50 SELECTs for a simple page refresh.
thumbssnfgf: yes, it's a terrible idea.
thumbssnfgf: read about joins.
snfgfthumbs, because all of these Pages records are in the same table, I was thinking of using IN followed by a list
thumbssnfgf: as long as you don't run 50 selects.
spkdjoins from a table to itself are probably more efficient than joins between two tables
spkdalthough it's a strange database design
spkdgood morning thumbs
takane2joins to the same table can be useful if you are modeling containers or something like that. Like shipments,pallets,boxes
sallespkd: What makes you think self join is more efficient than join between different tables?
Xgcsngfg should have looked into recursive CTE, even if MySQL doesn't yet have it. It's nice to know what can be done with the right support.
janat08is "it" some operator?
janat08select typename,it.typeid " here it refers to typename?
snoyesno, that would be a table named "it" or aliased as "it".
Xgcjanat08: : Table.
thumbsjanat08: no, it's the table prefix.
snoyes... FROM it, or FROM someTable AS it
Xgcor FROM (SELECT ... ) AS it or WITH it AS (SELECT ...) SELECT it.blah ... FROM it, etc.
janat08select typename,it.typeid from dgmTypeAttributes dta join invTypes it on it.typeid=coalesce(dta.valueint,dta.valuefloat) where dta.typeid=23919 and attributeid in (182,183,184,1285,1289,1290);
janat08select it from it doesn't work
thumbsjanat08: you can't select tables. You have to specify a column.
janat08so it's something of an alias which isn't defined in above command
thumbsinvTypes it
thumbsjanat08: the AS keyword is optional. JOIN invTypes AS it is equivalent.
janat08"invTypes it" is assign alias statement?
thumbsjanat08: it is the alias name.
snoyesYes, that means "there's a table named `intTypes`, and I will call that table `it` within this query
janat08oh lol
janat08i got it
qakhanhi all, i have these queries to get Today, Yesterday, Current Week, Last Week, Current Month, Last Month and Current Year total records. is it possible we can combine them in 1 query and get Today, Yesterday, Current Week, Last Week, Current Month, Last Month and Current Year total records in columns
qakhanhere are the queries http://pastebin.com/nRi31SU4
Xgcqakhan: Sure.
Xgcqakhan: The COUNT(expression) can be more specific, with the same full range.
Xgcqakhan: COUNT(CASE WHEN ... END) ... and the CASE expression can use date logic to narrow the meaning.
Xgcqakhan: Sometimes SUM is a little more convenient. But the result is the same.
Xgcqakhan: As a simple example: SELECT SUM(t1.date > some_date), SUM(t1.date > other_date), COUNT(*) AS full_count ... FROM ...
Xgcqakhan: The logic can be as complex as you need to pick the right range to SUM or COUNT.
Xgcqakhan: Basically, you don't choose the rows to count in the WHERE clause. The WHERE clause would only have logic that is applicable to all ranges you care about. The SUM/COUNT expressions would have the rest of the date criteria.
Xgcqakhan: or just don't add date logic in the WHERE clause, if that provides acceptable behavior / performance.
XgcACTION taps the mic
snoyesThe result is not the same for COUNT and SUM
snoyesunless your condition returns NULL instead of 0/FALSE
XgcYes. The logic is different when you use SUM vs COUNT. But the result can be the same.
qakhanthanks Xgc it working
qakhanthanks Xgc its working
Xgcqakhan: You're welcome.
hdonhi all :) i'm importing a huge csv of unknown properties. i started by just creating a text column for everything and using LOAD INTO. i'm going through each column now and using mysql to determine what kind of data is in a given column before deciding how to redefine the column. is there any real advantage to changing a text column to a varchar column?
NwSHeya guys a noob q. Is it wise to encrypt DB fields? I was thinking something like php-encryption
hdonNwS, dunno what php-encryption is -- but understand that proceeding naively may harm your ability to actually use the data. for instance, indexing an encrypted column.
hdonNwS, but if you data needs to be encrypted, then it needs to be encrypted.
hdonNwS, just be careful of where you are willing to store your decryption keys. if the keys are stored in the same place as your database authentication credentials, then anyone who gains connection to your database probably will find them, too, and just decrypt your data.
hdonNwS, OTOH if you keep no spare copies of your decryption keys, then your users may lose them (assuming this is user data you're encrypting)
hdonNwS, what is your use case?
NwShdon, it is an encryption library. I know you can also use encrypt/decrypt (never used them before) directly on mysql but yeah my main concern is how do you browse through those fields.
NwSI am building a small app where I am storing some sensitive financial information
NwSAnd I'm trying to figure out what is the best way to protect that data while in the same time being able to work with them through the panel
salleNwS: You don't "browse through fields" with MySQL
hdonNwS, what do you mean by browse?
salleNwS: From whom you want to protect your data?
hdonbb 15 minutes
NwSPart of that data will be searchable and viewed through tables (mini list) to specific users to complete their work faster.
carcrashhello, is there a mysql command (not an external tool) to check if the syntax of a statement is correct?
raymondcarcrash: No, not that I'm aware of.
sallecarcrash: You can use EXPLAIN, but it has different purpose
carcrashsalle, I may have to repurpose it then
carcrashthanks for the tip
carcrashraymond, thanks
sallecarcrash: It is much better to create test database and test everything there
sallecarcrash: Query with proper syntax can be doing something very wrong
sallecarcrash: DELETE FROM some_very_important_table; without WHERE clause is perfectly fine as syntax, but you better don't use it in production
carcrashYes understood. The input only allows for at most to have a unmatched bracket.
carcrashThat is really all i need to test for
carcrashon select statments
carcrashso I think I should be ok
tkxxxHi. I've made a query that creates a table. Is there a way to have it 'constantly running' or anything, so I dont have to call the query loads?
carcrashtkxx you want to constantly create tables?
htmlendIm told this has a syntax error UPDATE caths4_catha.wp_heat_options
htmlendsite_url = 'development.testsite.com',
htmlendhome = 'development.testsite.com'>
htmlendWHERE site_url = 'http://localhost/www'
htmlendaany clue what the syntax error is
carcrashhtmlend, use a pastebin please
snoyesthe >
snoyesstill the >
carcrashsnoyes, is right
htmlendso if i remove the ">" i should be good?
hdonNwS, any luck?
tkxxxcarcrash: I'm not sure the best way. Is it better to just run the query when I need it?
NwShdon, still searching around. There is an issue with storing the/a key on the server (obviously) so I'm still looking
snoyestkxxx: this query loads a table from what? from other tables, or does a LOAD DATA INFILE, or something else?
tkxxxsynthroid: It loads data from two tables, thats all.
tkxxxsnoyes: It just doesn't seem to be the most efficient way of doing things.
snoyestkxxx: something you could do with a view instead?
tkxxxsnoyes: Ahh, I will look into views. I'm not sure what they are yet, but I'm certain there is a more efficient way and I imagine a 'view' is what im looking for. Thanks.
tkxxxIs it possible to 'pivot' the rows/columns output, as currently I'd like the columns to be the rows! http://pastebin.com/NLxRWSar
snoyesWhat you show is normally the sort of pivoting people are after. To do it the other way, it's just SELECT lastname, SUM(points), COUNT(*) FROM table GROUP BY lastname;
hdonNwS, if we weren't talking about a database but just storing encrypted data in a multi-user environment where multiple users share access to different resources, the way to do it is to create a key for each element in the cartesian product (access clearance, user). when the user decides to access something new, it is checked if they have a decryption key for the given access. that key itself is encrypted and needs to be decrypted with the user's key.
hdonNwS, the user's key is probably also encrypted on the server, and requires their password to decrypt it. but if the user loses their password, all of their keys are also lost.
hdonNwS, alternatively you can also do it hierarchically: Alice has "Bus-riders" access which is implies she also has access "Carriages" -- to authenticate as Alice she must enter a password which successfully decrypts the key user-Alice. when she attempts to access data requiring clearance "Carriages," it is found that she has access via "Bus-riders," so her key (decrypted using her password) is used to decrypt the key "Alice_Bus-riders" and that key in turn is
hdonused to decrypt "Bus-riders-Carriage-riders"
domasimproving mysql, one linked list at a time - https://bugs.mysql.com/bug.php?id=85331
hdonNwS, another thing worth considering is, if your data is really sensitive, maybe it is never decrypted on the server at all. you'd need client-side logic to perform the decryption. the benefits here are a bit dubious since a MITM attack or an attack on the server will give the attacker all the resources he needs to attack the client. but that is the nature of the web.
thumbsdomas: it wouldn't be a Monday without you breaking MySQL!
hdondomas, linear search ftw :)
NwShdon, ty for the ton of info! xD
domastbh, I saw that charset lookup on 'change database' too
hdonNwS, np :)
hdonNwS, there are also some great research papers out there on indexing encrypted data :) but it can get pretty hairy!
domasLSMs are great for indexing encrypted data
domasif every case is a worst case you want to have write-optimized system like MyRocks!
ss23What I do is encrypt the data, and store it alongside the unencrypted version. Taht way people can search on the unencrypted v ersion, but I can use the encrypted version to return the results to my app, which decrypts it. That way, I get the best of both worlds!
snoyesI do it the other way around.
domasss23: lol
domasI prefer not storing any data
snoyesMake them search on encrypted values, and then spray the plaintext across the network.
ss23domas: /dev/null is fast as hell
hdonss23, i stayed in a motel with complementary wifi behind a captive portal authentication thing. it was actually encrypting data with rot13 :D
domassnoyes: well, in mysql world your encryption preferences are silently ignored, https://bugs.mysql.com/bug.php?id=85213
hdongranted it was an extra convoluted rot13 but it amounted to rot13 nonetheless
snoyesrot13, twice
domashdon: I hear many encryption algorithms are rot13 nowadays!
hdonsnoyes, lol
hdondomas, rot13 will never die. neither with the roman empire!
hdonok so, i have this csv. columns 4-7, 8-11, 12-15 all contain the same sort of data, so i'm splitting it out to a second table, and replacing each group of four columns with a foreign key. my question is, what's the best way to go about it? i originally wanted to just put a unique index across the entire new table, but i hit the key length limit of 767 bytes again.
hdonit would also be nice if mysql would tell me what my proposed key length is
hdonone idea is to key some field and have an insert trigger that inserts a hash into that field. hmm
dandamanhi all, im trying to connect to my local mysql db through a nodejs library and i get this error "ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)"
dandamanI am able to connect to the user and db through the CLI though
dandamancan anyone help me figure out what permissions I might be missing?
tkxxxsnoyes: Thanks. I've changed the query and here is what is displayed in the table. How would I select ALL users from the users table WHERE the 'academy' is the same as the first column of the row?? http://prntscr.com/egtxe5
snoyesUm. What?
hdondandaman, what mysql connector module are you using?
dandamanhdon: knex
hdondandaman, can i see your connector code?
dandamanone sec
tkxxxsnoyes: The count of users actually comes from another table. Is that possible?
damascenoHello. I'm using mysql community server 5.7 and php-mysql 5.4 (latest version on CentOS) I'm trying to connect to it to insert a simple record, this is my simple php code: http://pastebin.com/aksjngrj. The error: "Headers and client library minor version mismatch. Headers:50550 Library:50631". Does anyone know what to do?
dandamanhdon: https://gist.github.com/dsauerbrun/adff9fec891e0bc8acac18ca13817c44
tkxxxCurrently, it only counts the users of people who have points awarded
tkxxxI'd like it to select everybody
hdondandaman, pretty much identical to mine https://gist.github.com/hdon/d61cf152a9cc1a1f7121ba0d1baf24a5
snoyestkxxx: use a LEFT join instead of an INNER join
tkxxxsnoyes: http://prntscr.com/egu34j
tkxxxsnoyes: Still only selects users who have points awarded it seems
dandamanhdon: i know, im convinced it's an issue with my config
dandamanbut i have no idea what config to change, i already have my user accepting connections from %
hdondandaman, can you run as an unprivileged user the following command: mysql -u root -p giby_rubber # then type "pass" at prompt and hit enter
hdondandaman, if you're root to run as an unprivileged user it may suffice to preface your command with "sudo -u nobody"
hdondandaman, try "-h" i think to force mysql to connect via tcp
dandamanhdon: no i got the same error
hdondandaman, ok so your problem is not in node/knex then :)
hdondandaman, did you flush privileges after you added new authentications?
hdondandaman, don't close your root mysql shell though after you flush privileges. you may not be able to get back in and it'll be more of a pain to set up root authentication again if you messed it up
dandamanhdon: ok hold on, so i should go into the cli, flush privileges
dandamanthen try running the node server?
hdondandaman, i believe on debian it is default to bypass authentication by connecting on a unix socket file that only root has access to open
hdondandaman, you first objective is connecting with command-line mysql client. this eliminates any other possible complications.
dandamanhdon: i can do that if i leave out the database
dandamanoh wait, wtf
dandamannow i cant anymore...
hdondandaman, fyi you probably don't want to be connecting to your database with root user
hdondandaman, i mean, you probably don't want your node process connecting to your mysqld as your root mysql user -- you should create another user for your node application -- but that's a separate issue
dandamanyeah i know, i just want to get connected and figure out this issue first
hdondandaman, remember to flush privileges when you make changes to authentications -- create user, drop user, update passwords, update grants, etc.
dandamanwtf, i was able to access the cli earlier
dandamandunno what happened
hdondandaman, 1) privileges hadn't been flushed so now you can't access
hdondandaman, or 2) you are now doing something slightly different than you were before and nothing else has changed
dandamanhdon: well now i cant get back into root :\
dandamanany advice?
snoyestkxxx: how do you know that it's not that there is no mdl_course for those?
snoyeswhat table does lastname come from?
hdondandaman, try to run just "mysql" as root linux user
hdondandaman, that may allow you to bypass mysql authentication
tkxxxsnoyes: Oh. Very good point. Should I do a LEFTJOIN on that one too?
dandamanhdon: nope, still same access denied issue :\
hdondandaman, alright well now you have to reset your mysql root password
snoyestkxxx: you might also have the tables backwards. If you want all users, and the sum of their points if they have any, then it's FROM users LEFT JOIN points
hdondandaman, what distro?
snoyesif you want all points, and their users if they exist, then it's FROM points LEFT JOIN users
tkxxxsnoyes: mdl_user
dandamanhdon: community
snoyesok, then probably need to swap the tables.
tkxxxsnoyes: thank you for your help - I'll give it a go.
hdondandaman, which linux distro?
dandamanoh, im on osx
hdondandaman, what you have to do now is stop mysqld, run mysqld in authentication bypass mode, connect to it as root, reset your root mysql password, then shut down mysqld and restart it in regular mode
hdondandaman, idk how to do it on osx specifically
hdondandaman, good luck
GrandPa-GI am trying to use FTPLib and read a text file into a variable in my program. When I run it I get a AttributeError: 'NoneType' object has no attribute 'sendall'
GrandPa-GThe 2 lines that set it up are r = StringIO()
GrandPa-G ftp.retrlines('RETR mumble.sh', r.write)
GrandPa-G Any help?
hdonGrandPa-G, #python
GrandPa-Gso sorry
tkxxxsnoyes: It worked. Thank you!
hdondoes mysql have on duplicate key ignore?
hdonwhat version did it get it?
hdonoh thanks snoyes
hdonand this just ignores duplicate key error, right snoyes ? nothing else?
XgcLong ago. Don't remember. It's the default. It's like do nothing, which causes an error. It doesn't mean ignore the error. If yo take no action by ignoring, you get an error.
snoyeshm, might also ignore things like bad values
snoyeslike when you try to stick a string into an integer
hdonok thanks for the heads up :)
snoyeshdon: See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#ignore-strict-comparison
floogyHi, I'm running mysql 5.7.17-0ubuntu0.16.04.1 and wanted to move the akonadi mysql databases to a ssd, and symlink it back, but thqat does not seem to work (symlinking to another filesystem or drive). Where can I define the database location in mysql.conf (akonadi config folder)?
hdonhmm... is it a limitation of mysqld or mysql client that i can only see 64 warnings? is there a variable i can set somewhere? also, can i ignore "Duplicate entry" warnings?
hdonthanks snoyes
snoyesfloogy: https://dev.mysql.com/doc/refman/5.7/en/symbolic-links-to-databases.html
hdoni'm very surprised to see this warning
hdon| Warning | 1364 | Field 'sha2_224' doesn't have a default value |
snoyesIf you want to actually move the datadir, shut down MySQL, move the entire directory, change datadir in my.cnf, restart.
hdoni have set up this trigger:
hdonunder what conditions would that trigger not fill out a value when i'm inserting and cause that warning to show up?
hdonACTION grumbles
floogysnoyes, thank you. I have to look over the akonadi configure directory structure, there is a mysql.conf in such a folder. I think it's akonadi mysql server my.cnf file. I'm not sure though.
floogysnoyes, how can I tell from where the mysql process reads it's configuration aka my.cnf? --defaults-file=# Only read default options from the given file #.
snoyesfloogy: if you provide a --defaults-file, then it reads that one. Otherwise, it looks through a set list that is platform dependent, but listed with: mysqld --help --verbose | grep -A 2 "^Defaults"
snoyesremove that last s
floogysnoyes, the defaults file replaces then the my.cnf, right?
floogyif there is no datadir in my.cnf, then it uses the same place, the --defaults-file is located?
floogysnoyes, ok, I see. The process starts with --default-file and --datadir and --socket option, but I don't know where this may be defined for akonadi
snoyesIf it provides those values on the command line, then that's what it uses, not what is in any config file.
floogyyes, but I want to change it. I think, it must read such lines from a config file on startup, does it?
snoyesNot from anything MySQL controls.
floogycat /usr/sbin/mysqld-akonadi : exec /usr/sbin/mysqld $@
snoyesI can't tell you what akonadi does.
floogycan I maybe add there a new datadir at the end that may then overwrite the old one?
snoyesI'd go ask #akonadi where it gets that value.
floogy /usr/sbin/mysqld $@ --datadir=new_location_overwrites_first_datadir_option_from_@
floogyOr would that not work at all to start mysqld with two --datadir options in one line?
snoyesIt will work, but there's probably a clenaer option.
floogyOk, I'll ask at #akonadi then.
Jonas__will a later version of mysql be able to read the data file from an earlier version?
snoyesyes, within limits
Jonas__alright, it's worth a try then, thanks :)
snoyes5.7.y can read 5.7.x
snoyesusually 5.7 can read 5.6, although should generally follow the upgrade procedure
Jonas__I'm going from 5.6.29 to 5.7.17
Jonas__worst case scenario I can use a 5.6.29 docker and mysqldump tho
snoyesThen you can start it up and run mysql_upgrade, and after that it should work.
floogysnoyes, but that would speed mysql/innodb up, to have the datadir located on a ssd, right?
snoyesA qualified yes. There are plenty of benchmarks and discussion on the web.
floogyok, thank you. I expected that. Just to clarify that.
jshaI have a MySQL instance on a machine with 1 TB of physical memory, 800 GB of InnoDB buffer pool. Active threads steady around 30-40, and QPS steady around 6k. But its memory usage (RSS as measured by OS) keep growing unboundedly over time. It's currently at 975GB. Any ideas how to debug the cause of the memory
jshaTotal database size is 809GB, so it's not a matter of continually trying to page in more of the DB.
hdonwhy is my max key length 767? just curious why this specific number.
passagehdon: diff for innodb and myisam. that's the innodb limit. and it's bytes, not characters
hdonmmm... okay. why is it innodb's default? also... does the table engine also dictate the implementation of table indexes? i would have thought key length limits would be a property of table indexes and that indexes were mostly independent of table driver (i'm mysql noob, please feel free to correct me any time i make a mistake!!)
passagehdon: you can make it longer uinder certain conditions. see https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
hdonACTION clicks
hdonok i see, but
hdonmy curiosity remains unsatisfied. why is it 767?
passagehdon: I neither know nor care. it has to be something
passagehdon: maybe ask #mysql-dev
hdonok thanks anyway :)
dandamanhey hdon you still around?
dandamani keep getting "ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)" when trying to connect to my db through my nodejs library… I am able to connect using "mysql -u root -p giby_rubber" in the cli no problem
thumbsdandaman: why is nodejs trying to use the root user?
dandamanbecause im too lazy to create a new user
thumbsdandaman: create a new user.
dandamani just want to get connected to the db and ill deal with that part later
thumbsdandaman: take the 2 minutes it takes to create a new user.
dandamanthumbs: i tried that in my last go(before i scrapped mysql and reinstalled)
dandamani still got the same error
thumbsdandaman: show your new attempt then
dandamanok, created the new user
dandamanER_ACCESS_DENIED_ERROR: Access denied for user 'dantest'@'localhost' (using password: YES)
dandamani can connect via CLI though
thumbsdandaman: so the issue is with your application, or connector.
thumbsdandaman: how did you create the user?
dandamanyeah, i deduced that as well
dandamani used mysql workbench
thumbsyou should have used the mysql CLI and GRANT
dandamannot super familiar with mysql syntax(I mainly use pg)
thumbsdandaman: did you create it as @'%' or @'localhost' ?
thumbsdandaman: as root, SHOW GRANTS FOR 'dantest'@'localhost';
dandaman"GRANT ALL PRIVILEGES ON `giby_rubber`.* TO 'dantest'@'localhost'" and "GRANT USAGE ON *.* TO 'dantest'@'localhost' "
dandamangiby_rubber is my db
thumbsno IDENTIFIED BY ?
thumbsthat's the problem.
thumbs!m dandaman set password
ubiquity_botdandaman: See http://dev.mysql.com/doc/refman/5.7/en/set-password.html
thumbs!m dandaman alter user
ubiquity_botdandaman: See http://dev.mysql.com/doc/refman/5.7/en/alter-user.html
dandamanSET PASSWORD FOR 'dantest'@'localhost' = PASSWORD('mypass');
dandamanthat's what im running as root
dandamani still dont see the password in the grants
passagedandaman: version dependent
passagedandaman: 5.7?
dandamanyeah 5.7
passagedandaman: try the alter user link
dandamani mean, i already have a password for my "dantest" user
dandamani need it to access the mysql cli if i login as dantest
passagethumbs: still can't figure out what problems authentication plugins were designed to solve. :-(
thumbspassage: POSIX integration.
passagethumbs: or voodoo
dandaman*sigh* any ideas here guys? I've been struggling here for the last 4 hours
thumbs!t us root as root
ubiquity_bot#mysql: If "mysql -u root" only works as the system root user, you might be using the auth_socket plugin. SELECT user, host, plugin FROM mysql.user; and see https://dev.mysql.com/doc/mysql-security-excerpt/en/socket-authentication-plugin.html
thumbsSELECT user, host, plugin FROM mysql.user WHERE user = 'dantest';
dandamanthumbs: dantest is user, localhost is host, and mysql_native_password is plugin
passageuser, host, password, plugin
thumbsthere's the problem.
dandamanunknown column 'password'
thumbserr, eait.
passagedandaman: I don't think so
dandamanpassage: do you mean 'authentication_string'
passagedandaman: no, I meant password.
dandamani did select * and don't see that as a column
passagedandaman: select user, host, password, plugin from mysql.user;
dandamanpassage: that yields "ERROR 1054 (42S22): Unknown column 'password' in 'field list'"
passage(damn, 5.7)
passagenm, good luck
thumbsdandaman: well, are you using an old connector?
dandamanthumbs: that was my first thought, but i tried another one, knex, which is a well supported one
dandamando i need to flush privileges; after granting privilege?
dandamanim so desperate :(
dandamani have no idea what this error actually means :(
dandamanER_ACCESS_DENIED_ERROR: Access denied for user 'dantest'@'localhost' (using password: YES)
thumbsdandaman: pray tell, what database is your connector trying to use?
dandamangiby_rubber is the name of the database
dandamanis that what you mean?
thumbsdandaman: make sure your connector or application tries to use a database it has access to.
dandamanthumbs: well, it's connecting to the db through the mysql user, right?
dandamanand the user has access, so the application should have access...
salledandaman: The error message says you are providing wrong password for user 'dantest'@'localhost'
dandamansalle: that would make sense; however, when i try to log in with mysql -u dantest -p
dandamani type in my password and it's fine
Zeutronwhat would be the easiest way to allow users to edit data in a mysql table from my wordpress front end?
Volundturns out I have one last question.
VolundSo I have Four tables. Table A, B, C, and D. Table C's rows represent unique combinations of A and B via a UNIQUE(a.id,b.id). Table D has a foreign key pointing at Table C.
Volundlet's say that I want to see all rows in D where b.id=whatever. I need to do a LEFT JOIN across two tables, linking C and B.
VolundMy question is
Volundsince I am doing select from D left join <blah> and then the where clause is checking the values on a completely different table, is this markedly less efficient than if I were to have an indexed column on D pointing directly at B?
salle!t Volund idfma
ubiquity_botVolund: IDFMA - Insufficient Data For Meaningful Answer :: You have not provided nearly enough information for us to help you. Write up a clear description of your issue, provide structures and sample data in a pastebin with results and expected results.
VolundI'm not sure what kind of more information you would need for this. I can draft up the example schema?
salleVolund: You have some query. Nobody knows what it looks like.
salleVolund: Nobody knows what exactly are you trying to select.
Volundlemme go write the example schema then
VolundKinda quickly slapped this together as an example, the real tables have far more columns obviously.
salleVolund: So what? Remember nobody else can know what are you trying to select until you show it
Volundsince all of the fields on display here are what i'm selecting by, ahem
salleVolund: Bad example also doesn't help
Volund... fine, one moment
salleVolund: It is quite unlikely that the primary keys are all you are going to select
Volundhrm, gonna have to hastebin this, sqlfiddle is giving me an unhelpful error.
salleVolund: Why the hell you are example contains surrogate primary key for table ex_c? It is utterly stupid to use such PK for such table
Volundsurrogate primary key?
salleUNIQUE(exa_id,exb_id) is clearly the best candidate key there and is perfect enough for PK. Why the hell you need that auto_increment id which you are not going to use in any JOIN?
Volundbecause I wasn't aware of any better way to do Primary Keys.
Volundand I don't know how to make D reference C properly otherwise
salleVolund: PRIMARY KEY(exa_id,exb_id) is the way to go with such many-to-many reference table
VolundSo gimme a moment here to explain what I'm actually doing in brief. Ahem.
salleVolund: Nope
salleVolund: Integer primary key which is not referenced by any other table is utter nonsense. If you didn't have that you probably would not ask that initial question at all because you would notice why it makes no sense to ask such question.
wilornelHey #mysql! I just asked in #sphinxsearch but it seems like noone is active there right now
wilornelI just installed sphinxsearch, and ran `sudo index --all`
wilornelThen, when I try to run their `SELECT * FROM test1 WHERE MATCH('my document');
wilornel`, I get an invalid syntax error
wilornelDoes anyone know about sphinx? Is it supposed to expose a new set of syntax to the mysql client?
raymondVolund: You're *still* at it?
raymondwilornel: Hum, no.
raymondwilornel: If you configure mysql to use the sphinx engine you can use SQL to send sphinx queries to it.
Volundin my original table design, there was no table C. Table D just had many many rows where there could be any combination of A and B there. The problem therein is that in order for me to figure out how many distinct kinds of A were in a specific B, I had to SELECT DISTINCT a.id from D where b.id=whatever. And D has 181,000 rows in it, and poor indexing. I'm currently in the middle of a massive revamp, and I came up with the idea of
Volundcreating Table C which creates a VERY useful row representing a unique combination of A and B rows. So useful that I realized I could use IT instead of having D directly reference A and B. NOW I have absolutely no trouble figuring out how many distinct A correspond with B, it's a single query against a comparatively small and well-indexed table and doesn't even need to aggregate. ON THE OTHER hand, if I want to SELECT <basically
Volundeverything> from D where B is a specific thing I need to join it to C. But that means I'm now doing SELECT <stuff> FROM D LEFT JOIN C on D.actor_id=C.actor_id WHERE c.scene_id=<whatever>, and I just wonder if this means that the SELECT will be doing a lot more work than if I simply bad a B column in D.
VolundRaymond: ehhhh. Not really. This is just one question I'd like to understand an answer to idly. I'm like 99% done.
VolundGot distracted by Zelda: Breath of the Wild over the weekend.
raymondVolund: Eh... can't say that I'm too impressed with the Switch.
wilornelnvm, got it working!
VolundI'm hearing mixed things about it yeah. But I have a Wii U
Volundso I played BotW on my Wii U.
raymondVolund: Ah, my son got a Switch to "replace" his Wii U.
Volundhah. hahahahaha.
Volundwell, the Switch definitely has an interesting lineup and will probably get more developer attention down the road than the Wii U.
Volundthe Wii U ended up being kind of a dead end because not enough developers jumped on board with it and had little idea what to do with the gamepad. Nintendo's focus on quirky and innovative control schemes occasionally backfires like that.
VolundAs you can see raymond it's still the same situation as before, I'm just pondering this final tweak.
Volundbecause having to do a left join on a HUGE table against a smaller one to filter by a condition on the smaller table sounds like it -could- be stupid. I'm not sure -why-, I just have this naggling feeling that it is.
Volundleft join a tiny table to a huge one
VolundWell, selecting by all indexed integer columns so maybe it isn't?
Volundmy brain is just doing mild flips because like... I came to understand the almighty power of indexes, but then I'm not sure how much power that an index has when you do a JOIN and suddenly that neat little index covering 1,000 rows is stretched out over 181,000 corresponding rows and... does it still help in that condition?
VolundI would imagine that it would -have- to. I just don't... know.
EGreghi quick question
EGregI accidentally removed this file:
EGregit was the latest one, being appended to today.
EGregI thought I broke replication
EGregbut the slaves are already running and SLAVE STATUS seems ok
EGregMySQL docs say this:
EGregThis statement is safe to run while slaves are replicating. You need not stop them. If you have an active slave that currently is reading one of the log files you are trying to delete, this statement does nothing. In MySQL 5.7.2 and later, it fails with an error in such cases. (Bug #13727933) However, if a slave is not connected and you happen to purge one of the log files it has yet to read, the slave will be unable to replicate a
EGregfter it reconnects.
EGregI did not use PURGE in mysql, I deleted using the OS
EGreglooks like I dodged a bullet
EGregthe replication is going
EGregbut when will the next binlog file will start?
EGregit's not ther anymore
tavlI need fast way to generate a list of integers, from 1 to a unknown number (known only at runtime)... Anyway to do that without a temporary table?
Xgctavl: Derived table is one way.
Xgctavl: Cross join a few UNION SELECTs. Pretty simple.
Volundokay I'm having a brainfart.
VolundSELECT count(r.id) FROM $REPORT$ as r LEFT JOIN $REPORT_HANDLER$ as h ON r.id=h.report AND h.player=? LEFT JOIN $ACCOUNTREAD$ as acc ON acc.report=r.id AND h.account=? LEFT JOIN $CATEGORY$ as c ON c.id=r.category WHERE r.mode=? AND c.objid=? AND (r.date_modified>h.check_date OR h.check_date IS NULL)
Volundthis last part in the WHERE clause
Volundwhere <thing> and <thing> AND <compound thing>
Volund... hmmm. never mind, I'm just going to write this to my satisfaction, one moment
XgcVolund: If you don't need an outer join, you should probably start with an inner join. See: AND c.objid = ? which breaks the outer join behavior.
XgcVolund: That will cause you to lose all the r.* rows that have no c matches.
XgcVolund: It's possible you don't quite understand outer joins and don't really want that behavior.
VolundI probably don't understand them, no
VolundI do understand the idea of what a left join does but the rest confuse me.
XgcVolund: Ok. But you used a LEFT JOIN and then proceeded to break it.
VolundI don't understand what you mean there, which probably means I need to run more tests to see what joins really do again.
Volundbecause it still returns exactly what I want to see.
XgcVolund: r LEFT JOIN c ON ... WHERE c.objid = 5 will lose all the r rows that have no c match, which is an inner join.
passageVolund: if you put a where clause on the right-hand table of a left join, the join acts like an inner join
XgcVolund: The main point of that LEFT JOIN is to return all the r rows (with c) and null for c where no match is found.
XgcVolund: If you're getting the result you expect, you didn't want a LEFT JOIN with the c table.
Volundwill read momentarily. I am not ignoring. dealing with another thing too, ugh
fooI have a site that keeps getting hacked/sql injection/etc. I basically want to make it read only, and only allow write access at certain times. Can I manually do this by changing perms on mysql user? I'm thinking that may be the best way
thumbsfoo: heh?
passage /me votes for static html files