|
Janitor Prime posted:Maybe it's just the nerd in me being overly strict, but I have zero tolerance for MySQL's default behavior. I don't know if this has changed, but the last time I used it with a complicated GROUP BY query that included some columns that weren't part of the GROUP BY clause it just happily returned some random data from the matching rows instead of throwing an error like Postgres and Oracle do. This lead to a subtle bug in production that wasn't caught during testing because our test data always happened to return the same rows and nothing seemed amiss. This makes it sound like the PHP of databases.
|
# ? Jul 17, 2018 22:13 |
|
|
# ? Jun 8, 2024 06:33 |
|
It pretty much is. It also used to have some pretty glaring holes in the query language until fairly recently (IIRC it lacked both CTE's and window functions until, like, last year?). It's been improved, but if you actually want a fully featured relational database with actual relational integrity and support for a wide variety of datatypes, you should really just use Postgres. Or, if you're in a pure Microsoft shop, MSSQL, which has pretty much the same feature set but is more expensive. You can actually run MSSQL on Linux these days though.
|
# ? Jul 17, 2018 22:20 |
|
https://grimoire.ca/mysql/choose-something-elsequote:Considering MySQL? Use something else. Already on MySQL? Migrate. For every successful project built on MySQL, you could uncover a history of time wasted mitigating MySQL's inadequacies, masked by a hard-won, but meaningless, sense of accomplishment over the effort spent making MySQL behave.
|
# ? Jul 17, 2018 23:41 |
|
Ruggan posted:Doesn’t MSSQL integrate just fine with PHP and Python? I think there are drivers for both. Yeah but then I'd have to run a Windows Server.
|
# ? Jul 18, 2018 00:15 |
|
kiwid posted:Yeah but then I'd have to run a Windows Server.
|
# ? Jul 18, 2018 00:31 |
|
Janitor Prime posted:Maybe it's just the nerd in me being overly strict, but I have zero tolerance for MySQL's default behavior. I don't know if this has changed, but the last time I used it with a complicated GROUP BY query that included some columns that weren't part of the GROUP BY clause it just happily returned some random data from the matching rows instead of throwing an error like Postgres and Oracle do. This lead to a subtle bug in production that wasn't caught during testing because our test data always happened to return the same rows and nothing seemed amiss. I don't mind it personally but version 8 adheres to the standard, finally. Janitor Prime posted:Another stupid behavior I ran into was where date columns that contained NULL values would return 0000-00-00 as the value instead of NULL and breaking our ORM mapping. You can fix it by setting a property on the connection string or turning on Strict mode in the server config, but this is something that's so rear end backwards it boggles my mind. Then there's all the historically broken Unicode support that it shipped with. You can turn this behaviour off with sql mode NO_ZERO_DATE (and STRICT_ALL_TABLES / STRICT_TRANS_TABLES) since forever. This is deprecated and will be rolled in as one of the behaviours of strict mode somewhere in the future. Janitor Prime posted:The final nail in the coffin was all the licensing hassle that Oracle tried to impose on us. Oracle licenses the JDBC drivers under the GPL and when we tried to purchase an enterprise license to ship the driver with our product their legal team went crazy claiming that the way we used the product would require us to be a 3rd party reseller and wanted us to have minimum commitments and other crap like that.
|
# ? Jul 19, 2018 22:26 |
|
8 has cte's and window functions, so this list needs updating.I did not read any further since I know nothing about 8 that has not been said in a seminar i attended a few weeks ago. Kinda poo poo that he did not put a date on it, but oh well. I wish we ran on a better database but it's not the end of the world.
|
# ? Jul 19, 2018 22:29 |
|
Have been messing around with Google Big Query recently and have a quick question. There are two columns titled Unix 1 and Unix 2. When I pull the data, I want to create a third column titled Unix_Difference that subtracts Unix 1 from Unix 2. However, some of the time, both columns are null. How should I go about this? Use some sort of safe cast?
|
# ? Jul 23, 2018 19:00 |
|
Busy Bee posted:Have been messing around with Google Big Query recently and have a quick question. There are two columns titled Unix 1 and Unix 2. When I pull the data, I want to create a third column titled Unix_Difference that subtracts Unix 1 from Unix 2. However, some of the time, both columns are null. You mean like https://cloud.google.com/dataprep/docs/html/COALESCE-Function_57344756 ?
|
# ? Jul 23, 2018 20:05 |
|
karms posted:Kinda poo poo that he did not put a date on it, but oh well. 2013 https://github.com/grimoire-ca/bliki/blob/master/wiki/mysql/choose-something-else.md
|
# ? Jul 23, 2018 21:41 |
|
mysql is okayish for really basic relational data but if you have any queries that use like CTEs, `INSERT INTO ... SELECT ... FROM ...` or any kind of transactions postgres is about a million times better
|
# ? Jul 23, 2018 22:24 |
|
Munkeymon posted:You mean like https://cloud.google.com/dataprep/docs/html/COALESCE-Function_57344756 ? Nah, he wants https://cloud.google.com/dataprep/docs/html/IFNULL-Function_66194064 IFNULL on each column, which mirrors more convention SQL NVL. So IFNULL(Unix1, 'Empty') - IFNULL(Unix2, 'Empty'), or however the actual transform works in bigquery, COALESCE appears to be purely for columns, and doesn't look like it lets you fake it with a string literal.
|
# ? Jul 23, 2018 23:59 |
|
Moonwolf posted:Nah, he wants https://cloud.google.com/dataprep/docs/html/IFNULL-Function_66194064 IFNULL on each column, which mirrors more convention SQL NVL. IFNULL sounds like ISNULL from T-SQL, which I do prefer over COALESCE so that's cool, but it sounds like the OP was doing math on the columns (version numbers?) so I'm not sure why you'd default to a string literal?
|
# ? Jul 24, 2018 13:40 |
|
You're right, I'm not sure why I picked that, going to blame heat. Either way I think from the looks of the bigquery coalesce it doesn't allow non-column values, where ifnull does. Might just be a gap in my reading of the docs though, bigquery does a bunch of stuff quite different from classic SQL style from their own examples.
|
# ? Jul 25, 2018 01:08 |
|
I've got data in MySQL 8.0.11 now. I've decided that as I'm going to use Django I would rather move to PostgreSQL. This is proving super fun. I've tried using pgloader to directly move across but get the error documented here https://github.com/dimitri/pgloader/issues/782 even having changed the authentication method as the dev suggested. My idea now is to use mysqldump, which works fine, until I try to use --compatabile option which is documented https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_compatible which to my mind is clearly lies. I decided to test them all out, and it turns out that only ansi works. Or I''m a moron and doing it wrong. code:
|
# ? Jul 25, 2018 10:46 |
|
Any reason to not just export to CSV and then import via pg_dump?
|
# ? Jul 25, 2018 10:56 |
|
Mark Larson posted:Any reason to not just export to CSV and then import via pg_dump? I didn't know it was possible. I ended up resolving it by getting rid of MySQL 8, installing MySQL 5.7, re-creating the structure there and then pgloader worked perfectly.
|
# ? Jul 25, 2018 11:51 |
|
Sad Panda posted:I didn't know it was possible. lol MySQL
|
# ? Jul 25, 2018 14:34 |
|
What is the best GUIs for postgres out there?
|
# ? Jul 25, 2018 14:42 |
DataGrip is nice.
|
|
# ? Jul 25, 2018 14:46 |
|
Shy posted:DataGrip is nice. I've been eyeing this for a while. Guess I'll check it out.
|
# ? Jul 25, 2018 14:53 |
|
Also, at least some Jetbrains IDEs have a lot (some?, a little? I don't know) of Datagrips functionality built-in. PyCharm for example.
|
# ? Jul 25, 2018 17:54 |
|
kiwid posted:What is the best GUIs for postgres out there? Work pays for Navicat which I like and works for multiple DBs
|
# ? Jul 25, 2018 18:15 |
|
Munkeymon posted:lol MySQL Yup. It's like how the regular version of MySQLWorkbench still can't connect to a MySQL 8 database because they changed encryption format but it doesn't support it or something. All nonsense. I've nearly finished migrating. PostgreSQL seems to treat my byte strings differently. Reading them in Python they read as bytes when I did it in MySQL but in PostgreSQL it reads them as MemoryView because ??. b'gAAAAABbWL0EpMokI1IbCKcew==' is my byte string that I'm trying to put in the database. I'm storing it in a field of type bytes. This SQL is failing although it worked just fine on MySQL. Not quite worked out why. SQL code:
If I try to use SQL code:
Sad Panda fucked around with this message at 19:36 on Jul 25, 2018 |
# ? Jul 25, 2018 19:11 |
|
Wild guess from a Oracle user, but use single quotes around the entire string and escape by doubling them up?
|
# ? Jul 25, 2018 19:49 |
|
Sad Panda posted:Yup. It's like how the regular version of MySQLWorkbench still can't connect to a MySQL 8 database because they changed encryption format but it doesn't support it or something. All nonsense.
|
# ? Jul 25, 2018 20:00 |
|
anthonypants posted:Did you mean the datatype is bytea? You probably need to convert your base64 string into hex or binary first; b'string' notation is for bit-strings, and expects a binary string. Yes, my table has it storing in the bytea format. It was in Blob format under MySQL. Python uses some crypto to generate a token and outputs in bytes format. The way that it needs it back is b'gAAAAABbWMemnqAyGm2nJqU-gBqmAxPc2wfKNw' else it won't decode and give me my password. I managed to get the following SQL to execute and it stored in a way that could be decoded without problem. SQL code:
|
# ? Jul 25, 2018 20:25 |
|
Sad Panda posted:Yes, my table has it storing in the bytea format. It was in Blob format under MySQL. I don't know the extent or complexity of your data, but I think I'd create your Django models for your data and then just write a script to read the data from your old database and create your Django models from that. That way you circumvent all the stuff you're going through here. (It's possible you'll have other, different issues on this path, but I don't think that's going to be the case)
|
# ? Jul 25, 2018 20:50 |
|
kiwid posted:What is the best GUIs for postgres out there? After wrestling with the abortion that is current pgAdmin, I decided to run a docker container of Metabase for querying needs. Redash works too.
|
# ? Jul 25, 2018 20:57 |
|
Thermopyle is right, you just need to store the thing as a BinaryField and then if you need to represent that as a BASE64 encoded string you can do that in a separate getter method for in your object. e: You could also just change it to a TEXT type and store it as that Base64 encoded string from the start
|
# ? Jul 25, 2018 20:58 |
|
Thermopyle posted:I don't know the extent or complexity of your data, but I think I'd create your Django models for your data and then just write a script to read the data from your old database and create your Django models from that. The data all moved across fine and is being read into my Django models too. I've got a supremely ugly frontend that just displays a picture and balance from the database for each of my accounts. That part seems to be working well enough now. I need to write more functionality, but that's for the Django thread. That SELECT statement is used to update login information. This script only has ~15 logins so I use it to get teething things working before I move the same logic over to my other project which uses a lot of the same logic but a lot more logins. As you and Janitor Prime are saying, I need to maybe consider storing it in a different format. It's just frustrating, because the current logic worked perfectly with MySQL before I decided to use PostgreSQL instead.
|
# ? Jul 25, 2018 21:28 |
|
Sad Panda posted:The data all moved across fine and is being read into my Django models too. I've got a supremely ugly frontend that just displays a picture and balance from the database for each of my accounts. That part seems to be working well enough now. I need to write more functionality, but that's for the Django thread. I feel like I'm not understanding you completely (which isn't surprising...I'm feeling kinda thick today). While I use raw SQL statements in Django fairly frequently, I don't see any reason you need to be writing any SQL here...you can just use the Django ORM via Python.
|
# ? Jul 25, 2018 21:33 |
|
Thermopyle posted:I feel like I'm not understanding you completely (which isn't surprising...I'm feeling kinda thick today). I'm probably just explaining myself badly. A quick overview of the project might help. It's a piece of software that scrapes account balances and stores in a database. There are 2 relevant tables in the database. One for information about each bank, and one for each account. Each account has its login information stored in the database in an encrypted form using Python's cryptography library and a Fernet key that's stored on my computers keyring (hashing not an option). Instead of encrypting each of the important items individually, I created a list which is formatted as such. Python code:
Passwords change, so I have a method that passes that list through the process and write the b'whatever' to my database. That is what the SQL command earlier was trying to do. This project exists outside Django, and I'd prefer for the core functionality like that to function outside of the Django ORM. The method that I was trying to use is... Python code:
That got a bit long, but hopefully it makes sense. I'm a coding novice so there are almost certainly have been a number of bad choices along the way baffling decisions in the logic that I just explained. edit - Writing it out, I then started looking at the docs and think that instead of storing b'whatever', if I store token.decode() to store 'whatever' and then use str.encode(token) as an extra step then it should resolve the issues. Sad Panda fucked around with this message at 22:12 on Jul 25, 2018 |
# ? Jul 25, 2018 21:52 |
|
You'll find you have an easier time if you structure your database around Django. If you want to be able to access your data outside of Django, you can read the tables and stuff that Django creates with raw SQL any time you want. The way you're doing it now, you'll have a bunch of fragile SQL that breaks as soon as you take advantage of stuff like Django's migrations. If you add a field or a model or reorganize your data model in any way, you have to go around and edit a bunch of SQL strings everywhere. If you're using Django's ORM and you add a column, rename a model, change the type of a column...Django will automatically update your database schema with migrations, and your IDE will automatically refactor your code.
|
# ? Jul 25, 2018 22:49 |
|
Long time no post; I wouldn't call this an urgent issue since I know that I can get something out of it, but I thought I'd check how you guys would approach this. I basically have a bunch denormalized data in two columns. The columns are:code:
code:
code:
code:
|
# ? Jul 26, 2018 00:08 |
|
Scaramouche posted:So like: Recursive CTE. It takes a little bit of playing; eg you'll add a few fields to track the "overall path" and/or depth level depending on your aggregation/reporting needs.
|
# ? Jul 26, 2018 00:44 |
Sad Panda posted:I've got data in MySQL 8.0.11 now. I've decided that as I'm going to use Django I would rather move to PostgreSQL. This is proving super fun. For future reference you should also look at the built-in dumpdata and loaddata scripts in Django. Their whole purpose is to export/import in json in a backend-agnostic way so you can move from one DB type to another seamlessly.
|
|
# ? Jul 26, 2018 01:18 |
|
Thermopyle posted:You'll find you have an easier time if you structure your database around Django. What do you mean about structuring my database around Django? At the moment the way it is structured, I created a database called project_scrape and then there is a schema called banking which contains the imported data from the previous incarnation and all the things that Django created. I used inspectdb to auto-generate the models based on those tables. As far as my earlier problem, I wrote a script to convert all my passwords from binary format to text which hopefully makes this better. My plan from here on is to integrate my original code into Django as at the moment all my Django does is read from the database and display some of the information prettily. Any advice on the best way to integrate it?
|
# ? Jul 26, 2018 20:15 |
|
Sad Panda posted:What do you mean about structuring my database around Django? At the moment the way it is structured, I created a database called project_scrape and then there is a schema called banking which contains the imported data from the previous incarnation and all the things that Django created. I used inspectdb to auto-generate the models based on those tables. For some reason I thought you were not using the ORM. If that's not the case, there's no benefit for you to write raw SQL like you did in that function you shared.
|
# ? Jul 26, 2018 20:39 |
|
|
# ? Jun 8, 2024 06:33 |
|
PhantomOfTheCopier posted:Recursive CTE. It takes a little bit of playing; eg you'll add a few fields to track the "overall path" and/or depth level depending on your aggregation/reporting needs. Hmm, I'm not having a lot of luck with this but I'm kind of half-assing it between other calls/tasks. It seems like if there is only layer of embed it just returns the Parent -> Components, but if there's multiple layers (e.g. one of the components is also a parent further on down the list) I run into the Maximum Recursion problems. So this: code:
|
# ? Jul 26, 2018 20:53 |