carpiiare there any tools to help migrate a codebase to start using mysql's STRICT_MODE? Ideally a permissive approach, but one which would log to disk so it could be reviewed
Trioxinhow can I do a select in an insert like: insert into table set field1 = 'this', field2 = 'this' SELECT something, something WHERE this = that?
Trioxinjust want my insert to return the row id
carpiiinsert into table a (f1, f2, 3) select 1, '2', (select foo from bar)
Trioxincarpii, can I use set?
carpiithe row id as in auto_increment id ?
Trioxinyeah
carpiiwhat language ?
carpiiare you calling mysql from
Trioxinphp
Trioxinpdo
carpiiusing PDO?
carpiihttp://php.net/manual/en/pdo.lastinsertid.php
Trioxinthxs
carpiidont return it from the query, use the API features to fetch it
Trioxinactually looks like my pdo class I'm using has a $db->lastId();
carpiiah, its a fairly common feature so wouldnt be surprised
carpiicommon requirement, not feature
Trioxini'm using UserSpice. very convenient script
Trioxinresponsive bootsrap site with login system in a box
carpiihavent heard of it, but Im a bit anal about SQL frameworks, I tend to just handcode all my queries
carpiibut whatever works for you :)
Trioxinwell it has a pdo class but all my queries are done by hand
carpiiah oki
carpiinice :)
Trioxinit just takes care of instantiating the database object and all that and does some safety checking I guess
carpiiits ORM's I dont like personally
TrioxinI've never been a fan of any framework
carpiiheh yup me too, but id never tell someone to avoid them completely because they do have their uses
carpiiesp for rapid dev
Trioxina lot of times I've been brought onto projects where they were using zend or whatever and I see them doing things that are like "Man, it's so much better my way"
Trioxinmaybe I would use a framework if I weren't so indecisive
Trioxini could never pick one lol
TrioxinCI, zend, yii, cake
carpiii do hear good things about Symphony, but since I just run one well established site, I dont have a compelling reason to migrate to it (whereas if I was a contractor kncoking up new sites every week, I think itd be more useful)
Trioxinsymphony, probably newer ones now too
carpiiyeah, theres new frameworks every month it seems. If I really had to choose one Id go with a mature one though
carpiior at least one that isnt going to disappear and leave you up the creek
Trioxinwell, I don't have my own framework but there's a lot to be said for repeating design patterns
carpiithink codeignotor did that, not sure
carpiisure, id agree with using patterns
TrioxinI have a way of doing things. I always setup an API which usually begins with a switch of actions. then I have a job queue table in mysql and a script running in an infinite loop to spawn threads to complete the jobs
carpiinot that my site uses them throughout
carpiilegacy code and all that :p
Trioxinso my js hits the API and a PHP thread fires to do whatever
carpiiive similar requirements in some bits of my site. You should look into a job queue rather than infinite script ideally though
Trioxinunless it's something really simple and fast then I just have a function run straight from the api
carpiisomething like gearman or one of the more modern ones
Trioxinwell it is a job queue
carpiiyeah but I just mean, a job queue is fairly generic, so you could save yourself a lot of dev resource by just using commodity s/w
carpiiand let them worry about bugs etc
Trioxinyeah I
TrioxinI've never looked into one already built
carpiii used to do what you do, initially a infinite script, then a minutely cron, and finally a proper job queue
Trioxinit's a simple concept though I'm sure there's room for optimization
carpiiwouldnt go back to home grown stuff for that personally
Trioxini just have a keep alive script on cron
carpiiproblem is, if its working for you, youve no real incentive to suddenly revamp it
carpiiuntil some time when it shits the bed :)
carpiiand then you just emergency hack it, to get it up and running again, hehe
Trioxinidk I mean it's light on resources. I'd have to see the features of some alternatives
carpiiah ok, maybe its overkill for what you need
carpiimain benefits are robustness really, ability to recover from failure and just decoupling the job running from the rest of your codebase
carpiiso you can just 'fire and forget'
carpiiknowing the job will run at some point
Trioxinhmm
carpiibut for scheduled stuff, I do still use some crons
carpiiso it doesnt replace all that entirely
Trioxinif one of my jobs failed it would keep trying I suppose
carpiiyeah you can configure all that, try 3 times then fail, or just give up immediately etc
Trioxinthat would be like, an http timeout happening
Trioxinfor some remote api call
Trioxinthe job manager would keep spawning new threads for the job as soon as the first attempt failed until one succeeded
carpiii think in gearman you could defer it for a minute or two, and then gradually try again with longer intervals if it keeps failing
carpiibut not 100% sure on that
Trioxinshould probably add an email alert for that
carpiibut that would be difficult to do using conventional scripting
carpiibeanstalkd php
carpiinever mind, that was me googling :)
carpiii think beanstalk is the more modern one, gearman is a bit long in the tooth now
Trioxinsurprised I never heard of gearman since it's part of php
carpiithe client library is
carpiithe server is an project by apache foundation i think
Trioxinmaybe I'll publish mine on github. sort of like how userspice is a non-framework. I'll have jobspice
Trioxingearman looks like it does a lot mine doesn't
carpiiyeah its rpetty flexible i think, but admittedly I only use the bare features of it
carpiiI use it to send mailshots, and also process user uploaded images (thumbnailing, replication etc)
Trioxinyeah, when I was talking about frameworks for instance this one zend project I was overseeing they had all kinds of time consuming code running on apache when the user hit a page. stuff like updating the database with site-wide things
Trioxini wanted to smack them
Trioxinit was scraping sites for site-wide data each time a user hit a page
carpiithats my issue with frameworks really, its easy and convenient, but often produces a lot of excessive crap
carpiiwhich is ok for some projects, but if youre trying to write a lean website which is sticking around for some time
carpiiyou end up just creating more work for yourself later
Trioxinso you pass gearman a function and that's the job?
Trioxinand a callback i think
carpiiso the way I use it, I have a PHP worker script which does all the actual work, and I register tha with gearman, say 'send_newsletter' or whatever
carpiithen have a client PHP script which gathers the data it needs, and submits the task to gearman as a 'send_newsletter' job
carpiiand it contains all the data it needs for the worker to complete its task
Trioxinand here I thought my job queuery was clever :P
carpiiso its a bit more cumbersome that just inlining it into your site code, but thats kinda the point. It decouples it all so you just fire a job and be safe in the knowledge it wiill either complete, or you get a callback at some point to say it couldnt
carpiihehe
carpiiyou can also rate limit it, so if you suddenly get into a situation where oyu need to send 500 signup emails, the 500 jobs all get fired at gearman, and it works it way through them
carpiiinstead of your site trying to do them all and getting into difficulty
Trioxinas it stands now if my queue got hit with say 1000 at once it would wind up firing up 1000 php processes at once. my server could handle that but like 100,000 might be bad
carpiiyeah thats the problem, its not scaleable
Trioxinwell, on one of my sites I added a thread count
carpiiits all just home-rolling stuff though, Ive been down similar routes in the past myself :p
carpiias it stands now, if my tasks ever started causing a performance problem, I could just move gearman onto a new server or spin up a vm to do it. Change a couple of lines of code and Ive scaled the site to deal with it
Trioxini'll wind up using gearman now then probably refine my job code and put it on github to either be useful or laughed at
carpiilook into beanstalkd too, I mention gearman simply because it was a few years ago I started using it
carpiiIf I was starting from scratch now, its possible Id choose beanstalk instead
Trioxinthere is something to be said for simplicity too so long as it can scale
carpiiyup
carpiiand decoupling it all into seperate services means when you get a problem, it doesnt bring your whole world crashing down
Trioxinyeah. it all stemmed from my initial obsession with CLI
Trioxinwhich most people who learn PHP as their first language (Which admittedly is a terrible idea) don't ever mess with.
carpiiyeah true
carpiiI love CLI too
carpiiyou can still send jobs to gearman via CLI or cron though
carpiijust have a script with php as its interpreter, instead of the usual #!/bin/sh
TrioxinI would probably keep a gearman manager script running
carpiiprobably no need in that case, when you submit a job it returns instantly
Trioxinactually, if I were to make my thing a public project I'd probably recode much of it in Nim
carpiiif it were me, I doubt id publish your current stuff as a public project
carpiiit would have to be very good for it to be a viable alternative
carpiiso you just end up getting slated for it :p
Trioxinmaybe though it's served me well for a good 10 years now
Trioxinsome high traffic sites. I've never had to farm out jobs to multiple servers though
carpiiyeah but if you werent familiar with it
carpiiand downloaded it
carpiiyou might not be as keen :p
Trioxintrue
carpiiive a bunch of code like that
carpiisome quite cool stuff, but not stuff Id wanna present to people as a project
Trioxinhave you ever done encryption? I use libsodium and a key derivation function to store encrypted data in mysql but I don't yet know how to do key exchanges. would be nice for exchanging data between servers without each side having to know the key before
carpiiwhats the problem youre trying to solve?
carpiiprotect traffic from web server to mysql server in transit ?
Trioxincommunicate securely with a server that doesn't support SSL
carpiia mysql server?
Trioxinyea
Trioxinwell, mysql is running on it and in use
Trioxinand yeah that's the main idea, communicate with a script that's going to put data in mysql
carpiiive not found a decent way to do this personally. Even if you can configure MySQL to accept SSL connections, for some reason its performance bombs
Trioxinright so I have this code. one sec...
carpiiI ended up finding a solution via my hosting infrastructure instead, but its not ideal
Trioxinhttps://pastebin.ca/3750901
carpiiyeah this is beyond what Ive ever done
carpiiyou may try looking at the encryption routines in mysql itself, there are some assymetrical ones available
carpiihttp://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html
Trioxinwell that's good stuff there. just need to magic up some key exchange business
Trioxini can always store the keys on the servers
Trioxinbut for completeness...
carpiiif youre happy for them to be the same key each time then yeah i guess so
Trioxinright. need authentication if I want better.
carpiiif a hacker gets access to that key, then youre already owned anyway
carpiiso doesnt matter what you do in code :p
Trioxinyeah so there are ideas on splitting up the key
Trioxinpart in the code, part in mysql, part on disk out of html/
Trioxinwhich is actually a recommended practice. still feels kinda dirty but how else?
Trioxinthe machine has to have the key in memory at some point no matter what
Trioxinand as I can't sit at my keyboard and type it each time a user visits the site lol
carpiiyeah I think id spread it across a few places simply to reduce surface area
carpiibut im no expert so I wouldnt take advice form me on this :p
speer12341234is a ODBC connection from Excel to MySQL secure?
speer12341234[23:28] <speer12341234> How would I check?
speer12341234whoops
Trioxinjust trying to think of more places to hide pieces
speer12341234http://imgur.com/a/RSAgb
Trioxinand authentication but that's just stuff I need to read up on
carpiienvironment variable is another option
Trioxinah yes.. but one thing is bothering me. everyone says hide the pieces in all these places but if someone gets at the code they'll know everywhere to look
Trioxinoh wait! i have an ioncube license
Trioxinand version 9 has no been cracked
Trioxinnot*
carpiitrue, but in some cases they might somehow get access to your php code through some web server exploit, but still might not be able to dump environment etc
carpiibut youre right, in most cases youre already losing at that stage
carpiiwhich I think is why most people would recommend just encrypting the connection itself
Trioxinwell, ioncube eliminates that which is the only possible single point of complete failure
carpiievem tho mysql sucks at it
Trioxinunless they're really 1337 and digging through the ram
carpiii wouldnt trust ioncube personally
Trioxinwhy not?
carpiiultimately it has to be unobfuscated at some point, so Zend can interpret it
carpiiand that would be the weak point
Trioxintrue and there's dezend service but they haven't been able to crack version 9 (yet)
Trioxinso they play their game of cat and mouse and I just upgrade each time
carpiiah ok, I dont know about that
Trioxinit's been quite some time too. whatever ioncube did in version 9 they really messed up the Chinese crackers
carpiiwhats the impact on performance if it has to decrypt it everytime?
Trioxini think it improves performance actually (Somehow)
carpiicos if its very quick to decrypt, then its easier for crackers to brute force
carpiihrm
carpiiid be surprised :p
carpiiat least, now that PHP has its own opcode cache. Previously it was an extension you had to install
Trioxinyea
Trioxinhttps://www.ioncube.com/benchmarks.php
carpiilooks interesting
carpiibut
carpiiphpMyAdmin 2.3.2 suggest these benchmarks were done years ago (preior to opcode caching)
carpiiso this is where most performance gains would come from I think
carpiiphpmyadmin is up to 4.6.5.2 now
carpiibut ive babbled too much. I siltl have a problem to solve :)
carpiicame here for advice on STRICT mode but nobody could help heh
Trioxinidk what that is other than what it sounds like
carpiiit turns mysql into a proper database instead of just allowing any old crap
carpiiwhich is good except
carpiimy codebase was written with it allowing any old crap, and now Ive no way of knowing which queries might be a problem
carpiinulll dates, invalid group bys etc, the old mysql just worked around them
Trioxinmaybe phpstorm has a notion of strict
Trioxinor uhm
carpiithink its gonna have to be done on the mysqlserver somehow
Trioxindatagrip
carpiiwas hoping itd allow everything as it does now, but start logging any issues
carpiibut so far havent found a way to do that
Trioxinstrict sounds like something that should be on by default
carpiiit is now :p
carpiijust in mysql prior to 5.7 it wasnt
TrioxinI did a terrible thing yesterday in an attempt to get a module of my project out the door. I used the mysql shim
Trioxinon some legacy code
carpiiwhat sort of shim?
Trioxinhttps://github.com/dshafik/php7-mysql-shim
Trioxinit's for code with the old mysql_ functions
carpiimysqli ?
carpiior mysql?
Trioxinit takes all the old mysql_ and makes them work again using mysqli
carpiiah, do what you gotta do i suppose. I was using mysql_* funcs and migrated to PDO. It was no overnight job
Trioxinsince you can't just switch out the calls to mysqli and have them just work, a shim is needed.
carpiitook about 3 weeks
Trioxini'll do it eventually for this code. thing about this code is it's not public facing
Trioxinit's on a server cluster that my API communicates with
Trioxinso all data is passing through pdo first anyway
carpiiyea
carpiicobblers children and all that :p
devisi am getting this error while trying to connect my oracle server => ORA-12560: TNS:protocol adapter error, but yesterday i was successfully accessing this server without any problem so how come it is not working after a day?
efffeMy laptop crashed and now my life consists of fighting with innodb corruption. Is there a db that doesn't do that?
efffeI don't even care that I've lost all my data. I learned to just not care about it
efffehttps://dba.stackexchange.com/questions/54608/innodb-error-table-mysql-innodb-table-stats-not-found-after-upgrade-to-mys this is my problem. nothing seems to solve it
efffeactually screw it i'll just reformat my entire computer... maybe then i'll be allowed to use mysql
entelechyi'm amazed at how fast people give up re: efffe
TrioxinA beautiful woman just boarded a plane on her way to me. I'm going to INSERT many rows INTO her `naughty` table
strixUKhi all; am looking at somebody else's install of mybb and i see that most of the tables are myISAM.
strixUKAIUI, there is no obstacle to changing these to innodb unless a table uses a FULLTEXT key because mysql 5.5 innodb doesn't support these (is that right?)
strixUKso my question is: is there anything in information_schema or elsewhere that indicates the use of fulltext keys that I can do a SELECT on?
strixUKor do i need to dump the CREATE TABLEs for the db and grep?
sallestrixUK: Both can work
sallestrixUK: Look at i_s.statistics table
dbclkguys..any ideas what's causing this-> Lock wait timeout exceeded; try restarting transaction
dbclkIdk what's locking a table from being inserted
salledbclk: Another transaction is holding some row locks
salledbclk: SHOW ENGINE INNODB STATUS; will tell you more
dbclkidk...supervistor starts the queue...8 threads to be exact
dbclkand for some reason..it locks the table it seems
bryanruizhi there.. i'm trying to proxy a mysql connection through a intermediary for development purposes, I see mysql-proxy isn't updated much anymore, should I use that or something else?
sallebryanruiz: Maxscale, ProxySQL
bryanruizthanks salle
jnewthttps://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html i don't understand the LEFT JOIN example. why is the WHERE s2.article IS NULL; needed. there are no rows where article is null.
jnewtI would think the WHERE clause would always be false and therefore return nothing.
Naktibaldavalues are NULL when there is no matching table in the right table and LEFT JOIN is used
DjKadhappy new year <3
DjKadINSERT INTO YOURLIFE SELECT All.BESTTHINGS FROM LIFE
dbclkfolks..need some assistance with this configuration
dbclki'm trying to set my root password to empty -> https://gist.github.com/dbclkclk/275fb3a79f8a48931260e28b7b502312
dbclki change the configuration to my mariadb but it isn't working
DjKadSET PASSWORD FOR root@localhost=PASSWORD('');
DjKadhave you tried that dbclk?
dbclknope..let me try
DjKadif it doesn't work you can try from command line
DjKadmysqladmin -u root -pType_in_your_current_password_here password ''
dbclki wanted to set the mysql root password to empty
dbclkDjKad: I can't set the password without the root password
DjKadok now i've understood. You've lost your root password and you are tryin to set to empty for later changes?
dbclkwell no..I just install a new mysql/mariadb instance
dbclkusing this -> https://gist.github.com/dbclkclk/275fb3a79f8a48931260e28b7b502312
dbclki just wanted to update the mysql password to set it to null
BenderRodriguezhow does mysql handle the leap second if timestamps are being updated every few miliseconds?
BenderRodriguezare there going to be records with identical timestamps?
DjKaddbclk: in that script it seems that root password is empty
dbclkit should be
dbclkthat's what i'm trying to do
dbclkbut, when i log in without a password
dbclkit doesn't work
DjKaddbclk: i would set a password to the root and change later to empty
DjKadBenderRodriguez: if you use the timestamp to a column that have Unique set you wont have problem with duplicated
efffeShould a db contain InnoDB tables as well as MyISAM tables?
efffeGot a dump from production that has a ton of "InnoDB: Error: Table "mysql"."innodb_table_stats" not found." errors...
DjKadhave you upgraded mysql to 5.6?
efffeI got the dump from production
efffeI suspect the production dump is on an old version of MariaDB
efffeAnd I imported it locally using Sequel Pro (MySQL 5.6.33).
DjKadtry creating manually those 5 tables
DjKadlike described in this
DjKadhttp://dba.stackexchange.com/questions/54608/innodb-error-table-mysql-innodb-table-stats-not-found-after-upgrade-to-mys
efffeI have a few times, but it doesn't help.
efffeYeah, I followed those exact instructions.
efffeI can try again.
efffehttps://imgur.com/BihzD5q
efffeIt's strange. It shows the tables under the `mysql` db in the GUI, but when I click on them it says they don't exist.
efffeI've tried refreshing and same.
DjKadtry to do a select
DjKadon that tables
DjKadefffe: if you watch in the link i gave
DjKadyou can try the 3rd answer
efffeOkay thanks, I'll investigate.
DjKadtry dropping those 5 table and then delete from the filesystem .idb files, restart and try again the 5 CREATE commands
efffeOkay, I will.
efffeI installed MAMP and MariaDB separately so it'll be fun tracking down those idb files
DjKadprobably in /etc/local/mysql/data
efffeTablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT.
efffeI can't wait until I leave OSX...
efffeAnd turn into a real Linux user...
socommefffe: Linux is overrated.
efffeOh yeah?
efffetbh, I'm just anxious to learn how to use vagrant/docker/all that.
efffeI want to be able to spin up local envs that match production, and even get into deployment and such...
efffeSpend 99% of my time messing with broken crap that isn't even code...
efffeJust want to be able to spin up boxes, work, commit app code, destroy boxes...
effferemoving files and crap didn't work. going to get into vagrant or something.
dbclkI can't login as root without sudo
dbclkany ideas?
dbclkI have no password fyi
dbclkmy password is blank
socommdbclk: mysql -u root -p
socommdbclk: 'mysql -u root' rather
dbclki did
dbclkdoesn't work
dbclki have to use sudo
dbclkplus I can't login using my website using root user
dbclki'm on a VM
thumbsdbclk: easy on the enter key . It's not a substitute for actual punctuation.
thumbs!tell dbclk root as root
ubiquity_botdbclk: 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