Forum:

JDBC and Relational Databases

* Welcome Joel Murach

Tim Cooke

Sheriff

Posts: 5555

326

I like...

posted 3 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

This week, we're delighted to have Joel Murach helping to answer questions about the new book Murach's MySQL.

The promotion starts Tuesday, February 20th, 2024 and will end on Friday, February 23rd, 2024

We'll be selecting four random posters in this forum to win a free copy of the book provided by the publisher, Murach.

Image from https://www.murach.com

Please see the Book Promotion page to ensure your best chances at winning!

Posts in this welcome thread are not eligible for the drawing, and should be reserved for welcoming the author. Questions posted in this topic are subject to removal.

Tim Driven Development | Test until the fear goes away

Campbell Ritchie

Marshal

Posts: 79394

377

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

We only have two people posting about the book to date, but one of them is one of the most ornery customers ever seen on this website: me.
Good to see you here Welcome

Tim Holloway

Saloon Keeper

Posts: 27851

196

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Anyone know if Swedish is still the code default on MySQL? I migrated my Recipe Manager app last week from SQLite and had to go in and change all the tables to collate UTF-8. Some of the copy/paste ingredient lists I was extracting from recipe websites contain things that don't fit the Swedish code page.

The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.

You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Howdy to all you code ranchers out there, ornery or not!

To answer Tim's question: With MySQL 8.0 and later, the utf8mb4 character set is the default, and it uses the utf8mb4_0900_ai_ci collation by default. So, the Swedish collation is no longer the default.

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Tina Wolf

Greenhorn

Posts: 2

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hey, I had this book years ago when I was a COBOL programmer! It was super helpful! Great SQL reference book!

syed shaheeruddin

Greenhorn

Posts: 3

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Awesome! I would love to read this book. Trying to learn SQL and I'm sure this book will help me get there!

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Tina,

I'm glad to hear that our MySQL book worked well for you as a SQL reference!

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Joel Murach

Author

Posts: 142

posted 2 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

Hi Syed,

We designed this MySQL book with the goal of making it easier than ever to learn SQL to work with a MySQL database. Hopefully it can help you. And once you learn how to use SQL to work with MySQL, you can use most of the same SQL skills on other databases too.

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Tina Wolf

Greenhorn

Posts: 2

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Joel Murach wrote:Hi Tina,

I'm glad to hear that our MySQL book worked well for you as a SQL reference!

Hi Joel!

My teenager has taken up coding! We'll get him one of your books, too!

Bill Pulling

Greenhorn

Posts: 2

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

HI Joel.

I teach at a community college in London, Ontario, Canada, and for the past several years I have used MySQL as the back-end database for some JDBC work in my fourth semester Java course. I have never done it in the course yet, but I would like to incorporate picture storage as BLOBS into the database. Does your book deal with this area?

Bill Pulling,
Fanshawe College
London, ON

Stephan Peters

Greenhorn

Posts: 5

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hey, Joel!

I haven't posted here at the ranch for a while.
I saw you were here in an email.

I'm using the 3rd edition of your book for a course right now.
I really like the section on stored procedures and functions (Chapter 15).

It's one of the textbooks I intend to keep.

Unless, of course I win a 4th ed by posting here...
It's an excellent resource.

I'm curious,
Will the databases and examples in your book work with MariaDB?
I've been playing with MariaDB on a Linux system where all the repository MySQL downloads are MariaDB.
Workbench won't even connect to it, I've been using DBeaver and DataGrip.

-Stephan
CSU Global CS student

Liutauras Vilda

Marshal

Posts: 8880

638

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Stephan Peters wrote:Unless, of course I win a 4th ed by posting here...

Very unlikely, because questions asked in this thread aren't eligible for winning.

This thread is reserved for welcoming Author only. Please ask question(s) in the forum book promotion is happening.

Cagatay Oezbay

Greenhorn

Posts: 2

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Given the evolving landscape of database management and development, what are the most significant updates or new features in the latest edition of Murach's MySQL that address contemporary challenges in the field?
Yeah, I am the new guy.

Campbell Ritchie

Marshal

Posts: 79394

377

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Welcome to the Ranch

Please note that you can only win the book if you post on a separate thread.

Tim Holloway

Saloon Keeper

Posts: 27851

196

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Joel Murach wrote:Howdy to all you code ranchers out there, ornery or not!

To answer Tim's question: With MySQL 8.0 and later, the utf8mb4 character set is the default, and it uses the utf8mb4_0900_ai_ci collation by default. So, the Swedish collation is no longer the default.

Thanks. I hope you've stressed that the "ci" part of that means case-insensitive. One of the things that blew up in my database migration was that I had ingredient types like "Baking Powder", "baking powder", and "Baking powder", and the lookup got annoyed at finding 3 matches where only one was expected. An unfortunate artefact of the original data. I had to remove the "ci" from the collation to make the port work.

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Bill,

Aside from a brief mention of the various BLOB data types supported by MySQL, our book doesn't show how to deal with this issue. I think you can probably find a tutorial online that shows how to do this.

Sorry I couldn't be of more help on this issue!

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Joel Murach

Author

Posts: 142

posted 2 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

Hi Stephan,

Thanks for the kind words about the 3rd Edition of our book. I'm glad you like the section on stored procedures and functions.

I haven't tried it myself, but I think the databases and examples presented in our book will also work with MariaDB. That's because, at the time that we developed and tested this code, MariaDB's stated goal was to be a "drop-in" replacement for MySQL. I don't think that's true anymore, or at least not for advanced features, but our book focuses on basic features, so I think it should work with MariaDB.

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Cagatay,

To be honest, except for a few bug fixes and minor changes, most of the 4th Edition of this book is the same as the 3rd Edition. That's because MySQL has been really stable in recent years, which is good. The only significant change is that we added a chapter that shows how to host a MySQL database with AWS (Amazon Web Services). We added this chapter to address the trend in the tech industry of moving away from using local servers to host databases and websites towards using servers in the cloud.

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Tim Holloway wrote:I hope you've stressed that the "ci" part of that means case-insensitive.

We did!

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Stephan Peters

Greenhorn

Posts: 5

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Joel Murach wrote:

I haven't tried it myself, but I think the databases and examples presented in our book will also work with MariaDB.

Alright, I'll give it a shot this Sunday and run some of the scripts from the book and let you know how it went.

The Linux platform is Arch.

Running gives you MariaDB.

-Stephan

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Stephan Peters wrote:Alright, I'll give it a shot this Sunday and run some of the scripts from the book and let you know how it went.

That would be great. Thanks!

I find it a bit funny that including "mysql" in the pacman command gives you MariaDB. But I think that just shows that they are mostly interchangeable.

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Cagatay Oezbay

Greenhorn

Posts: 2

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Joel Murach wrote:Hi Cagatay,

To be honest, except for a few bug fixes and minor changes, most of the 4th Edition of this book is the same as the 3rd Edition. That's because MySQL has been really stable in recent years, which is good. The only significant change is that we added a chapter that shows how to host a MySQL database with AWS (Amazon Web Services). We added this chapter to address the trend in the tech industry of moving away from using local servers to host databases and websites towards using servers in the cloud.

Thank you for sharing these insights. The new chapter on AWS hosting is particularly interesting as it aligns with the industry's shift towards cloud services. I'm looking forward to diving deeper into this aspect.

Tim Holloway

Saloon Keeper

Posts: 27851

196

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Bill Pulling wrote:HI Joel.

I teach at a community college in London, Ontario, Canada, and for the past several years I have used MySQL as the back-end database for some JDBC work in my fourth semester Java course. I have never done it in the course yet, but I would like to incorporate picture storage as BLOBS into the database. Does your book deal with this area?

Bill Pulling,
Fanshawe College
London, ON

Bill, you might want to look at this example: https://gogs.mousetech.com/mtsinc7/gourmetj-springboot

It's a port to Spring Boot of an application that originally ran in Python on the desktop. The desktop version has been very useful to me over the years, but Python isn't as durable as Java when it comes to system components being upgraded and when Python2 went out, basically so did the app. I wrote a functional equivalent using JavaServer Faces that should age better, especially since I just brought the platform and libraries up to date.

The app keeps both large and thumbnail copies of uploaded recipe pictures in the database as BLOBs. It's using Spring JPA, so I hope that's not a problem. The current live demon at https://gourmetj.mousetech.com is running against a MariaDB database. The database code is essentially the same as it was when I used SQLite, which was the Python app's original database (Yay, JPA!)

If I don't have the MySQL schema file in there, I do have it scheduled to commit shortly.

Galen Benson

Greenhorn

Posts: 11

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

I am working with some forum software with a MySQL back end. I have created a test system on my local machine and I'm trying to debug a problem in the database. I've created some queries that produce the problem but I'm not successful at getting the debugger to work in MySQL. Is there some trick or setting I need to turn on. Does your book have a section on the debugger.

Also... I'm trying to learn about Redis and if it will speed up my database queries (searches seem to take a long time) are there any tools you recommend to tune MySQL and can I learn tuning from your book.

Thanks!!!
//gb

Liutauras Vilda

Marshal

Posts: 8880

638

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Posts in this welcome thread are not eligible for the drawing, and should be reserved for welcoming the author. Questions posted in this topic are subject to removal.

Joel Murach

Author

Posts: 142

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Galen Benson wrote:I'm not successful at getting the debugger to work in MySQL. Is there some trick or setting I need to turn on. Does your book have a section on the debugger.

Hi Galen - Unfortunately, our book doesn't have a section on the debugger.

Galen Benson wrote:Also... I'm trying to learn about Redis and if it will speed up my database queries (searches seem to take a long time) are there any tools you recommend to tune MySQL and can I learn tuning from your book.

As for as tuning goes, our book only covers some basic guidelines for improving performance such as when to create an index on a column and how to write a query that doesn't retrieve more data than necessary. That's because our book focuses on teaching the basics of working with SQL and relational databases and only teaches some basic database administration skills, not much on tuning or in-memory storage. Sorry about that. I wish you the best in speeding up your database searches!

Joel Murach
author of Murach's Java Programming
author of Murach's MySQL

Stephan Peters

Greenhorn

Posts: 5

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Joel Murach wrote:

I find it a bit funny that including "mysql" in the pacman command gives you MariaDB. But I think that just shows that they are mostly interchangeable.

Joel,

I'll be playing with it tomorrow. I'm going to download the scripts for the 4th edition and try them out.

As far as Arch replacing MySQL with MariaDB, it looks like they've been doing this for a while.

See this 2013 (MySQL 5 era) Arch news message:

https://archlinux.org/news/mariadb-replaces-mysql-in-repositories/

Have fun!
Stephan.

Tim Holloway

Saloon Keeper

Posts: 27851

196

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

MySQL is a proprietary product of Oracle Corporation. And presumably also a trademark. Ask Xerox® how that worked out.

MySQL was originally an independent company offering its product in both free open-source and paid support. Until Oracle bought their assets.

I'm not sure if Oracle has a free MySQL and less if it is open-source anymore, but FOSS OS distros are extremely reluctant to bundle in proprietary licensed code as part of their package archives. Note that you can't find Oracle's Java in those archives either, and instead have to download it straight from Oracle or use OpenJDK.

MariaDB was forked off from MySQL when Oracle ate MySQL and is completely FOSS. So far, the two products are essentially interchangeable. It remains to be seen how long that continues. Similar stuff happened when they bought OpenOffice and LibreOffice spun off.

Campbell Ritchie

Marshal

Posts: 79394

377

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Didn't Sun take over MySQL before Oracle took them over?

Tim Holloway

Saloon Keeper

Posts: 27851

196

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Campbell Ritchie wrote:Didn't Sun take over MySQL before Oracle took them over?

It's possible, since my comprehension of time is notoriously feeble. But I don't think so. After all, Oracle is a database company but Sun was not.

Stephan Peters

Greenhorn

Posts: 5

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Joel,

I didn’t do it yesterday, but I did run your scripts on EndeavourOS using DBeaver.

I used JetBrains DataGrip to take a Udemy course on MySQL by Tim Buchalka, but that is not free. I am on a student license.

I was going to make logs, etc. but it was taking a minute and so I’m just giving you highlights.

I made a video of some what I did (8 minutes, real-time).
https://drive.google.com/file/d/1puLBO2V1V8_x571U-DLidbkOg6u8LF3S/view?usp=sharing

Most of it works just fine.

It didn’t like that the charset wasn’t defined when creating the databases, but it still created them.

It didn’t like 1-14, something wrong with the foreign keys when deleting from a table.

Then I jumped to the scripts near the end. Most of it worked. There were missing logs and triggers, that might have worked if I had run the exercises sequentially.

It REALLY didn’t like the user creation stuff from chapter 18. I think this might be very different in MariaDB. I wasn’t logged in as root, but the user I was logged in as has all privileges.

The .mwb files will open with MySQL Workbench if it is installed. Workbench will NOT connect to a localhost MariaDB. You can do some tweaks to have it connect to a remote MariaDB (MariaDB SkySQL services) host, but I have a feeling that ability might disappear in the future, too.

Thanks for a great book!

-Have Fun!

-Stephan

Tim Holloway

Saloon Keeper

Posts: 27851

196

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Stephan,

A lot of our authors don't hang around after the promotions, so you might want to send a Purple Moosage to Joel. It will forward to the email account he joined the Ranch under.

As you might have noticed, I had questions about code pages and collating sequences as well. They have changed since the original MySQL and I think more than once/depending on whether you're talking Oracle MySQL or MariaDB. Generally, I've been OK with whatever the default was, but sometimes it does indeed make a difference, as my latest project discovered to its sorrow!

Stephan Peters

Greenhorn

Posts: 5

I like...

posted 2 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Tim Holloway wrote:Stephan,

A lot of our authors don't hang around after the promotions, so you might want to send a Purple Moosage to Joel. It will forward to the email account he joined the Ranch under.

Thanks, Tim!
If he doesn't show up to claim this brilliant excellently edited custom video [lol], I'll try sending it to him at Murach Books--- if his email follows their standard convention, I know it already (if he checks that one).

I think he even included it as a user in one of the databases for 3ed.

If he doesn't respond I'll send him a Purple Moosage as well.

The book is really a good book.

- Stephan

Don't get me started about those stupid light bulbs.