“Schools are places where you send your children to be brought up by other children.”
Random rows in MySQL with Reasonable Performance
Almost every answer on Stack Overflow for this is terrible on moderately complex or large real data – https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast.
Bad: ORDER BY RAND() LIMIT N
This means executing your query for the entire resultset and then ordering it and then chopping off the number you need. This can have truly dire performance.
Bad: SELECT MAX(id) FROM table
Don’t pick random numbers from 1 to MAX(id) – deleted rows will be null or result in you getting less rows than you want. Who says your id is even sequential/numeric?
OK: SELECT id FROM table
Then pick N at random (removing those already chosen if you don’t want duplicates) from the resultset in your chosen programming language.
If you just want one row from the db you can do this in SQL only as shown here: https://stackoverflow.com/a/31066058/375262. Doing N unique rows this way is left as an exercise for the reader.
If you have a gargantuan resultset even SELECT COUNT(*) might be slow. What would you do then?
How to call a method on an object originating in Java called “in”, which is a kotlin reserved word
Backticks around the reserved word.
I found this working with JOOQ’s implementation of the SQL IN predicate. You can’t write myJavaLibObject.in(…)
in Kotlin. You’ll get “Element expected” because in
is a reserved word in Kotlin. Instead you need backticks: myJavaLibObject.`in`(…)
will do the trick.
Convert java.sql.Timestamp to java.time.OffsetDateTime
Hard to believe but I think this is the simplest you can manage:
long millisSinceEpoch = timestamp.getTime(); Instant instant = Instant.ofEpochMilli(millisSinceEpoch) OffsetDateTime dt = OffsetDateTime.ofInstant(instant, ZoneId.of("UTC"))
Yikes.
Top 50 Rising Programming Technologies
(Based on Stack Overflow tag count and upward trend as proportion of Stack Overflow questions. So it might just be the 50 most difficult-to-learn rising programming technologies!)
The List
- Python
Interpreted high-level programming language for general-purpose programming
- React
JavaScript library for building user interfaces
- Laravel
PHP web framework (“for web artisans”)
- Pandas
Data structures and data analysis tools for Python
- TypeScript
Superset of JavaScript that adds static typing (“JavaScript that scales”)
- Amazon Web Services
On-demand cloud computing platform
- API
The interface between two programs.
- Azure
Cloud computing service
- Powershell
Commandline shell and associated scripting language
- Firebase
Mobile and web application development platform
- Selenium
- Spring Boot
- Docker
- React Native
- DataFrame
- Unity 3D
- Elasticsearch
- Matplotlib
- Go
- Jenkins
- Selenium Web Driver
- Gradle
- Machine Learning
- Amazon S3
- vue.js
- ggplot2
- Flask
- ASP.NET Core
- npm
- Webpack
- Mongoose
- tkinter
- Google Apps Script
- Web Scraping
- Spring Security
- filter
- https
- Woo Commerce
- Xamarin Forms
- Web Socket
- Android Recycler View
- Kotlin
- Redux
- Google Sheets
- Excel Formula
- SASS
- Hive
- Java 8
- Redis
- CMake
The Top Ten
Chart of the top 10:
The Top Ten Without Python
Python dwarfs everything else so here’s a look without Python:
The Nearly Men
These tags were eliminated from the list solely on the basis of a 2018-only downward trend: R (would have been 2nd), Node.js (2nd), PostgreSQL (4th), numpy (12th), Express (14th), Apache Spark (14th), Tensorflow (18th), nginx (20th), Github (21st), Amazon EC2 (31st), ECMAScript 6 (39th), ffmpeg (46th)
Programming Languages That Make the List
- Python
- TypeScript
- Go
- Kotlin
A Rising Python Lifts All Python Libraries
python-3.x actually makes second place on the list but I rolled it into Python rather than make a redundant entry.
Django and Django Models were eliminated from the list despite being on an upward trend because they have not yet exceeded their previous peak in 2010. This resurgence, Pandas in fourth place and the presence of tkinter on the list speaks to the general rising of Python.
Methodology
Load all tags on StackOverflow by count descending.
Put each of them into StackOverflow Trends and judge by eye if they are currently trending up.
Where Do These Technologies Sit in the Overall List?
In the whole list of tags by count Python is sixth overall, Firebase (10th place in this list) is 90th, and CMake (50th) is 442nd.
Minimal Implementation of graphql-kotlin
This will bring up a GraphQL endpoint at http://localhost:8080/graphql and the GraphiQL query tool at http://localhost:8080/graphiql if you run ./gradlew bootRun
build.gradle.kts
plugins { id("io.spring.dependency-management") version ("1.0.6.RELEASE") // Pull in dependencies automatically. id("org.jetbrains.kotlin.jvm") version ("1.3.10") id("org.jetbrains.kotlin.plugin.spring") version ("1.3.10") id("org.springframework.boot") version ("2.1.0.RELEASE") } tasks.withType(org.jetbrains.kotlin.gradle.tasks.KotlinCompile::class.java).all { kotlinOptions { freeCompilerArgs = listOf("-Xjsr305=strict") // Enable strict null safety. jvmTarget = "1.8" } } repositories { mavenCentral() } dependencies { implementation("com.expedia.www:graphql-kotlin:0.0.23") // Generate GraphQL schema directly from code. implementation("com.graphql-java-kickstart:graphiql-spring-boot-starter:5.1") // Get the /graphiql page for free. implementation("com.graphql-java-kickstart:graphql-spring-boot-starter:5.1") implementation("org.springframework.boot:spring-boot-devtools") testImplementation("org.springframework.boot:spring-boot-starter-test") }
Application.kt
package {yourpackagehere} import com.expedia.graphql.schema.SchemaGeneratorConfig import com.expedia.graphql.TopLevelObjectDef import com.expedia.graphql.toSchema import com.fasterxml.jackson.module.kotlin.KotlinModule import graphql.schema.GraphQLSchema import graphql.schema.idl.SchemaPrinter import graphql.servlet.GraphQLErrorHandler import graphql.servlet.GraphQLInvocationInputFactory import graphql.servlet.GraphQLObjectMapper import graphql.servlet.GraphQLQueryInvoker import graphql.servlet.ObjectMapperConfigurer import graphql.servlet.SimpleGraphQLHttpServlet import javax.servlet.http.HttpServlet import org.slf4j.LoggerFactory import org.springframework.boot.autoconfigure.SpringBootApplication import org.springframework.boot.runApplication import org.springframework.boot.web.servlet.ServletRegistrationBean import org.springframework.context.annotation.Bean @SpringBootApplication class Application { private val logger = LoggerFactory.getLogger(Application::class.java) @Bean fun schema(): GraphQLSchema { val schemaConfig = SchemaGeneratorConfig( supportedPackages = listOf("{yourpackagehere}"), topLevelQueryName = "YourQuery", topLevelMutationName = "YourMutation" ) val schema = toSchema( queries = listOf(TopLevelObjectDef(YourQuery())), mutations = emptyList(), config = schemaConfig ) println(SchemaPrinter().print(schema)) return schema } @Bean fun graphQLObjectMapper(): GraphQLObjectMapper = GraphQLObjectMapper.newBuilder() .withObjectMapperConfigurer(ObjectMapperConfigurer { it.registerModule(KotlinModule()) }) .withGraphQLErrorHandler(GraphQLErrorHandler { it }) .build() @Bean fun graphQLServlet( invocationInputFactory: GraphQLInvocationInputFactory, queryInvoker: GraphQLQueryInvoker, objectMapper: GraphQLObjectMapper ): SimpleGraphQLHttpServlet = SimpleGraphQLHttpServlet.newBuilder(invocationInputFactory) .withQueryInvoker(queryInvoker) .withObjectMapper(objectMapper) .build() @Bean fun graphQLServletRegistration(graphQLServlet: HttpServlet) = ServletRegistrationBean(graphQLServlet, "/graphql") } fun main(args: Array ) { runApplication (*args) }
Chart.js Charts from HTML Data Attributes
Example HTML
<canvas class="chart" data-type="horizontalBar" data-labels="["White", "Blue", "Black", "Red", "Green", "Colorless"]" data-series="[13, 11, 5, 2, 2, null]"></canvas>
JavaScript
$('.chart').each(function () { var id = $(this).attr("id"), type = $(this).data("type"), labels = $(this).data("labels"), series = $(this).data("series"), options = $(this).data("options"), ctx = this.getContext("2d"); new Chart(ctx, { 'type': type, 'data': { labels: labels, datasets: [{ data: series }] }, options: options }); });
1205, ‘Lock wait timeout exceeded; try restarting transaction’
Recently we had an issue with very long-lived locks on certain rows in MySQL/MariaDB. Certain people (each row representing a person) would get locked out of updates for hours at a time. Using Flask/uWSGI/MariaDB/mysqldb.
SHOW ENGINE INNODB
didn’t report any recent deadlocks or other issues but SHOW PROCESSLIST
showed some very long sleeping processes and when these were killed the locks would go away. SELECT trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx t JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id;
was also interesting showing some unclosed transactiosn that corresponded.
We seem to have cleared the problem up by explicitly closing the database connection on teardown_request.
Atomic Transactional Replacement of a Table in MySQL
Even with AUTOCOMMIT off a DROP TABLE or CREATE TABLE statement will cause an implicit commit in MySQL.
So if you drop your table of (say) aggregated data and then create a new one even if you’re theoretically in a transaction there will be time when clients of the database see no table and time when they see an empty table.
The solution is to use RENAME TABLE.
CREATE TABLE replacement_table (...) AS SELECT ... FROM ...; CREATE TABLE IF NOT EXISTS current_table (id INT); -- Just in case this is the first run and the table doesn't exist yet so RENAME TABLE doesn't fail. RENAME TABLE current_table TO old_table, replacement_table TO current_table;
No client of the database will ever see a database that doesn’t contain an existing and populated current_table
.
Sharing Terminal kill-ring with system clipboard on OS X
I managed to get vim in the terminal using the system clipboard by using a version compiled with +clipboard and using set clipboard=unnamed
in .vimrc. But I wanted to go one further and have the last entry in my emacs-like kill-ring in bash go to the system clipboard too. So that when I hit Ctrl-k on the commandline I can Cmd-v that text into my text editor (or anywhere). Turns out this is pretty tricky.
The best solution I have so far is courtesy of user3439894 on the Apple Stack Exchange. It requires me to use Ctrl-Shift-k to kill instead of Ctrl-k but otherwise does what I want pretty well. You can find the gory details at https://apple.stackexchange.com/a/336361/301884.