Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2023-11-14

Channels

#babashka
<
2023-11-14
>
johanmynhardt14:11:29

Oh hi. I don't know if this is a babashka (mysql pod) or a next.jdbc "issue" I'm observing (or neither). I've been happily inspecting MySQL 5.7 information_schema results, where it contains namespaced keywords like :VIEWS/* :TABLES/* and :COLUMNS/* and using destructuring to access specific keys (eg.: {:TABLES/keys [DATA_LENGTH INDEX_LENGTH]}) and then today my wheels came off when things didn't all seem quite alright. I'm working on schema migration from 5.7 to 8.x by querying table and column character set and collations and generate statements for the columns that require conversion. It would seem that for MySQL 8.0.22 connections, much of the information that was previously available and in a rather generic + consistent form, is less so on 8.x. The output in the message-thread is a clojure.data/diff of the keys from one row from each of 5.7 vs 8.0 connections, produced by the following query: 

SELECT c.*,
   t.*
FROM information_schema.TABLES t
  JOIN information_schema.`COLUMNS` c ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'mysql'
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND c.TABLE_SCHEMA = 'mysql'
  AND (c.COLLATION_NAME != 'utf8mb4_0900_ai_ci'
       OR c.CHARACTER_SET_NAME NOT IN ('utf8mb4'))
ORDER BY c.TABLE_NAME, c.COLUMN_NAME;
Do I just have to make peace with this reality or did I miss an option? 😅

✅ 1
borkdude14:11:21

I'm not familiar with mysql specifics but do you get differences by executing the raw query in both versions?

johanmynhardt14:11:22

I'd rather just bring the diff in here: 

[#{:AUTO_INCREMENT
   :AVG_ROW_LENGTH
   :CHARACTER_MAXIMUM_LENGTH
   :CHARACTER_OCTET_LENGTH
   :CHARACTER_SET_NAME
   :CHECKSUM
   :CHECK_TIME
   :COLLATION_NAME
   :COLUMN_COMMENT
   :COLUMN_NAME
   :CREATE_OPTIONS
   :DATA_FREE
   :DATA_LENGTH
   :DATA_TYPE
   :ENGINE
   :EXTRA
   :GENERATION_EXPRESSION
   :INDEX_LENGTH
   :IS_NULLABLE
   :MAX_DATA_LENGTH
   :NUMERIC_PRECISION
   :NUMERIC_SCALE
   :PRIVILEGES
   :TABLE_COMMENT
   :TABLE_ROWS
   :UPDATE_TIME
   :VERSION
   :catalogs/TABLE_CATALOG
   :collations/TABLE_COLLATION
   :columns/COLUMN_DEFAULT
   :columns/COLUMN_KEY
   :columns/COLUMN_TYPE
   :columns/DATETIME_PRECISION
   :columns/ORDINAL_POSITION
   :columns/SRS_ID
   :schemata/TABLE_SCHEMA
   :tables/CREATE_TIME
   :tables/ROW_FORMAT
   :tables/TABLE_NAME
   :tables/TABLE_TYPE}
 #{:COLUMNS/CHARACTER_MAXIMUM_LENGTH
   :COLUMNS/CHARACTER_OCTET_LENGTH
   :COLUMNS/CHARACTER_SET_NAME
   :COLUMNS/COLLATION_NAME
   :COLUMNS/COLUMN_COMMENT
   :COLUMNS/COLUMN_DEFAULT
   :COLUMNS/COLUMN_KEY
   :COLUMNS/COLUMN_NAME
   :COLUMNS/COLUMN_TYPE
   :COLUMNS/DATA_TYPE
   :COLUMNS/DATETIME_PRECISION
   :COLUMNS/EXTRA
   :COLUMNS/GENERATION_EXPRESSION
   :COLUMNS/IS_NULLABLE
   :COLUMNS/NUMERIC_PRECISION
   :COLUMNS/NUMERIC_SCALE
   :COLUMNS/ORDINAL_POSITION
   :COLUMNS/PRIVILEGES
   :COLUMNS/TABLE_CATALOG
   :COLUMNS/TABLE_NAME
   :COLUMNS/TABLE_SCHEMA
   :TABLES/AUTO_INCREMENT
   :TABLES/AVG_ROW_LENGTH
   :TABLES/CHECKSUM
   :TABLES/CHECK_TIME
   :TABLES/CREATE_OPTIONS
   :TABLES/CREATE_TIME
   :TABLES/DATA_FREE
   :TABLES/DATA_LENGTH
   :TABLES/ENGINE
   :TABLES/INDEX_LENGTH
   :TABLES/MAX_DATA_LENGTH
   :TABLES/ROW_FORMAT
   :TABLES/TABLE_CATALOG
   :TABLES/TABLE_COLLATION
   :TABLES/TABLE_COMMENT
   :TABLES/TABLE_NAME
   :TABLES/TABLE_ROWS
   :TABLES/TABLE_SCHEMA
   :TABLES/TABLE_TYPE
   :TABLES/UPDATE_TIME
   :TABLES/VERSION}
 nil]

borkdude14:11:37

raw query in some mysql console I mean

johanmynhardt14:11:44

@U04V15CAJ yes, that's correct, same query, different results.

johanmynhardt14:11:55

Oh, I'll double-check that.

johanmynhardt14:11:31

Yes, using DBeaver, the visual/grid and returned columns are the same. I suspect it's something with the returned metadata from MySQL 8.x 😔

borkdude14:11:27

I don't get it. If the columns + data are the same, what does "metadata" mean?

borkdude14:11:18

if you export both query results to a csv and then diff the csv, it should be the same. why should it be different to next.jdbc?

johanmynhardt14:11:19

MySQL returns ResultSet metadata which is used to retrieve the column labels and other info about the current data returned from the server.

borkdude14:11:07

this driver is used in the pod: https://github.com/babashka/babashka-sql-pods/blob/92d2eedb5afcb517880d9d4158f7ee5b843b5231/project.clj#L25 don't know if that makes sense. you could try in JVM Clojure too with next.sql and see if you get the same results

👌 1
johanmynhardt14:11:09

This is something I found really useful in the more I worked with next.jdbc where you don't have to alias your columns if you have two tables with conflicting names, it would just use a namespaced key and you'll have :table_name/column_name formatted keys.

johanmynhardt14:11:41

that's a good shout, I'll try that too, thank you.

borkdude14:11:45

also next.jdbc version: com.github.seancorfield/next.jdbc "1.2.753"

borkdude14:11:49

might be a bit behind

johanmynhardt15:11:43

🤯 Adding the ORDER BY-line is causing this. I've stripped it barebones. deps.edn project with next.jdbc and connector-j latest versions. (fun times, they changed the coordinates for connector-j) I started the query from scratch and went line-by-line 😆 Removing the order-by yields the following diff, showing only a new :COLUMNS/SRS key in MySQL 8.x. 

[#{:COLUMNS/SRS_ID}
 nil
 #{:COLUMNS/CHARACTER_MAXIMUM_LENGTH
   :COLUMNS/CHARACTER_OCTET_LENGTH
   :COLUMNS/CHARACTER_SET_NAME
   :COLUMNS/COLLATION_NAME
   :COLUMNS/COLUMN_COMMENT
   :COLUMNS/COLUMN_DEFAULT
   :COLUMNS/COLUMN_KEY
   :COLUMNS/COLUMN_NAME
   :COLUMNS/COLUMN_TYPE
   :COLUMNS/DATA_TYPE
   :COLUMNS/DATETIME_PRECISION
   :COLUMNS/EXTRA
   :COLUMNS/GENERATION_EXPRESSION
   :COLUMNS/IS_NULLABLE
   :COLUMNS/NUMERIC_PRECISION
   :COLUMNS/NUMERIC_SCALE
   :COLUMNS/ORDINAL_POSITION
   :COLUMNS/PRIVILEGES
   :COLUMNS/TABLE_CATALOG
   :COLUMNS/TABLE_NAME
   :COLUMNS/TABLE_SCHEMA
   :TABLES/AUTO_INCREMENT
   :TABLES/AVG_ROW_LENGTH
   :TABLES/CHECKSUM
   :TABLES/CHECK_TIME
   :TABLES/CREATE_OPTIONS
   :TABLES/CREATE_TIME
   :TABLES/DATA_FREE
   :TABLES/DATA_LENGTH
   :TABLES/ENGINE
   :TABLES/INDEX_LENGTH
   :TABLES/MAX_DATA_LENGTH
   :TABLES/ROW_FORMAT
   :TABLES/TABLE_CATALOG
   :TABLES/TABLE_COLLATION
   :TABLES/TABLE_COMMENT
   :TABLES/TABLE_NAME
   :TABLES/TABLE_ROWS
   :TABLES/TABLE_SCHEMA
   :TABLES/TABLE_TYPE
   :TABLES/UPDATE_TIME
   :TABLES/VERSION}]
What a rollercoaster today was!

borkdude15:11:28

so it's working in bb now as well?

johanmynhardt15:11:02

100% 😹 Just tested it.

borkdude15:11:17

good news! :)

johanmynhardt15:11:37

I never doubted it! Thanks for being awesome.

Nundrum18:11:51

I have a weird nrepl setup problem. After going back to a project that was working before and still works if I invoke it directly with bb , I start bb nrepl and then vim. But every action in vim gives me `No namespace: dripnet found user-9e2826a9-4409-4164-a052-7a032ccef658`.

Nundrum19:11:03

{:tasks {nrepl                                                                                                                                                                                    
        {:requires ([babashka.fs :as fs]                                                                                                                                                         
                    [babashka.nrepl.server :as srv])                                                                                                                                             
         :task (do (srv/start-server! {:host "localhost"                                                                                                                                         
                                       :port 1327})                                                                                                                                              
                 (spit ".nrepl-port" "1327")                                                                                                                                                     
                 (-> (Runtime/getRuntime)                                                                                                                                                        
                   (.addShutdownHook                                                                                                                                                             
                     (Thread. (fn [] (fs/delete ".nrepl-port")))))                                                                                                                               
                 (deref (promise)))}}                                                                                                                                                            
 :paths ["bb"] }

Nundrum19:11:42

The source file is at bb/dripnet.clj

Nundrum19:11:01

I suspect something I did to get Shadow-cljs running broke it, but can't figure out what.

borkdude21:11:49

Where do you load this dripnet namespace?

Nundrum21:11:21

I don't have an ns declaration in the file. And it still doesn't work if I put one in.

Nundrum21:11:20

I've been trying to get neovim + conjure up and going today. That's working just fine as-is.

Nundrum21:11:42

It must be some weird cider problem.

borkdude21:11:22

> Where do you load this dripnet namespace?

borkdude21:11:25

I can try your project with CIDER + emacs. Does CIDER work with nvim? I didn't know

Nundrum21:11:35

Originally I was using vim with fireplace. And then tried to set up vim with cider to work on some cljs. I must've broken something along the way.

Nundrum21:11:16

Thanks for the offer, but I don't think it's necessary. If you just want to try, it's at https://github.com/robbieh/dripnet/

borkdude21:11:46

In my CIDER + emacs the nREPL session starts in namespace: 

user-d643aa37-468b-49b7-9c49-c53e8535c1ca>
This is an auto-generated namespace for bb.edn

borkdude21:11:56

perhaps the tooling can't handle that, but I think this is a bug in nvim

borkdude21:11:27

@U02UHTG2YH5 You can probably workaround this with: 

(binding [*ns* (create-ns 'dripnet)]
                    (srv/start-server! {:host "localhost"
                                        :port 1327}))

Nundrum22:11:58

Thanks! I'll give that a try.