From 88c79031efd681aaf96c2148de52221baadc1fb7 Mon Sep 17 00:00:00 2001 From: gennyble Date: Sun, 8 Dec 2024 06:53:59 -0600 Subject: 2024-12-08 06:53 CST --- served/words/debugging-my-sql-query.html | 82 ++++++++++++++++++++++++++++++++ 1 file changed, 82 insertions(+) create mode 100644 served/words/debugging-my-sql-query.html (limited to 'served/words/debugging-my-sql-query.html') diff --git a/served/words/debugging-my-sql-query.html b/served/words/debugging-my-sql-query.html new file mode 100644 index 0000000..e9f5643 --- /dev/null +++ b/served/words/debugging-my-sql-query.html @@ -0,0 +1,82 @@ +--- +template=post +title=Debugging My SQL Query +style=/styles/post.css +style=writing.css + +published=2024-11-13 7:24pm + +description=Tiny retelling about debugging a weird SQL problem. +art=images/awoo.png +art_alt=Some("no such table: awoo") +--- + +working on a project i call naily (or just "numbers") and i had an sql problem :( + +i was trying to load a page that ran an sql query. it was this: + +
SELECT creation_date, state, game, result
+FROM double_dodge
+WHERE player_id = ?1 AND state = ?2;
+ +the error in my terminal read: no such column: creation_date. +so i went and looked at my create table. it looked fine! the relevant bits: + +
CREATE TABLE IF NOT EXISTS double_dodge (
+	...,
+	creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	...,
+);
+ +so i tried not selecting the creation_date column, but then it got mad at me about +the state column. i can assure you that state, too, was a real column. + +i opened the database in sqlite3. like this, sqlite3 onl.db. after trying to do +\d double_dodge and it telling me that it didn't know what \ meant, +i ran .help like it suggested i did. + +haha, no i didn't. i went to google and searched, apparently, "sqlite cli" which pulled up the +Command Line Shell For SQLite page. i scrolled for a +few dozen seconds and saw .schema which looked promising. +
sqlite> sqlite> .schema double_dodge
+CREATE TABLE double_dodge (
+	(lines redacted for brevity)
+	creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ +see, it is real. i'm not loosing it. well, i might be. + +next i tried to not run that sql query at all until there was data in it. would that matter? i +didn't know, i'd never used sqlite before, but i thought it wouldn't matter. mostly because that +wouldn't make sense for it too, but also because i did something greatly similar elsewhere in +the project. + +i commented the code out and was able to use the webpage to put data into the table without issue. +more evidence it was real. + +i then uncommented the code and it started to panic again. and it was still poisoning the mutex that +held my database connection (not great probably, the mutex, but). + +does it just say that if it doesn't think the table is real? surely the table is real. we have so +much evidence it is. i changed the table name. +
SELECT creation_date, state, game, result FROM awoo;
+ +
Some("no such table: awoo")
+ +okay, so it knows the table is real. it just doesn't think any of the columns are real. +i tried removing my where clause. + +aha! it ran. + +what's wrong with my where clause? let's look at it again +
SELECT creation_date, state, game, result FROM double_dodge WHERE player_id = ?1 AND state = ?2
+ +those are all columns that exist, even if sqlite does not believe me. oh, i'm not selecting +player_id. that's probably not good. + +
SELECT player_id, creation_date, state, game, result FROM double_dodge WHERE player_id = ?1 AND state = ?2;
+ +success :) + +i am normally good about remembering to select what i where, but i forgot this time. +i wish it gave me an error that made sense, though, as the where was for +player_id which was not anything it was telling me about. \ No newline at end of file -- cgit 1.4.1-3-g733a5