Room 105 Friday Mar. 06 - SCaLE 23x

This session explores the usage of large language models (LLMs) for generating SQL queries, with a focus on common query tasks like retrieving city populations and creating customer data tables. The speaker reviews how various LLMs such as Grock, Google Gemini, and ChatGPT perform in generating appropriate SQL commands, comparing their outputs for efficiency and accuracy. He highlights the strengths and limitations of each model in dealing with SQL queries for different database systems, particularly MySQL and PostgreSQL. Additionally, the speaker discusses the role of AI in optimizing queries, including the considerations necessary when integrating AI tools within data management workflows, and emphasizes the importance of validating AI-generated SQL before implementation in production environments.

the names of the cities and their populations and I was wondering how well they're doing. I've done this previously with SQL light and Postgress and got some very good results. So I went out to Grock and I said that prompt and as you see at the top it says to find all the cities in the Texas district and their populations from that database. You can query the

city table by filtering the district column for Texas and they give you an explanation. They give you the query and they give an explanation. So if you're learning SQL, this is a a great way to learn how to assemble a query. If you were a senior programmer, you had a junior programmer came with this query, you'd be fairly happy with it. So that was the first one.

Next one was Google. its outputs a little bit shorter and it basically gives you the uh a smaller explanation and it does something a little bit differently. It says let's identify the country code for the United States and then filter the city table based on that country code and the Texas district. So it knows that Texas is in the US and therefore you have to search for

the US and Texas. Olama, which is a little different in that you self-host it or you can self-host it. Most people do. As you can see, it has more of an old CLI type interface. And I put in the prompt up here and it comes back with pretty much the explanation of the same thing with the query. It says select city population has a comment. This specifies

the columns wants to retrieve. Um, pretty much the same as the first one from from Grock and I thought it was, you know, a good solid B plus A minus answer. Uh, chat GPT is interesting. Um, one of the problems I have with chat GPT using Postgress is it doesn't know any version of Postgress past 14. We're now on Postgress um 19 of 18 going on 19.

So I was very relieved to see it gave a nice explanation saying to get all the cities in Texas and populations you need to only read the city table and it gives the columns and the explanation down there talks you through why it picked the certain columns. Now, as you noticed, all of them had a core query. The select up to the wear statement is all the

same, which very simple query. You'd expect that uh where they vary is that rock, chat, GPT, and lama all just stop there and say district equals Texas. And as I mentioned earlier, Google wants to be a little especially refined because they want to make sure that you're also getting everything from the country of the United States. So that way we won't be fooled if Mexico suddenly decides

to have a district called Texas again. So my conclusions for this one was that for his example used a well-known database to feed to the LLM and it gave a fairly good predictable answer. did a a good journeyman job. Uh the responses were all pretty consistent. All produced good SQL that did the job. Now the trouble is well-known database that's been out there for 30 years. It's

kind of hard not to know how to do that. It's like the recipe for ice. Even I can find that on Google these days. So second example, write SQL to create a table on a MySQL 8.0. I'm specifying the database and the version to store customer data. And then I specify the data, a first name, a last name, an address, a birth date, and ask it to

use a UU ID primary key. Some of you longtime MySQL are kind of saying UU ID and 80 might be an image match there. I say partition the data by the year of birth and produce test data of at least 10 records. Um, by the way, if you're a fan of Dilbert and you recognize this character, I've seen dozens of companies that have someone who's a an

SQL coder who gets asked to write queries like this on a regular time from a pointyhaired boss. So my comment, so to do this, I also had some goals. I did not want to write any SQL. No SQL, not NoSQL, but no space SQL. I want to do absolutely no coding if possible. And I also use the Daver AI explained fix to correct issues with this and

you'll see why in a minute. Uh, by the way, the AI explained fix. If you haven't tried this yet, come by the booth. I'll give you a full demo. Uh, this is really cool. It opens an AI chat, takes your query and your error message and said something syntactically is wrong here. Tell me what it is. And uh for the following examples, I'm using Open AI with

the GPT4 model. Uh your mileage may vary depending on what you're using. So uh once again, come by the booth for a demo and we'll be happy to accommodate you. A quick example of how it works. I have a query where I'm selecting customer ID, name, and last name from a table and I get the error message. There's an unknown column name. I put in name instead

of first name for the second column that I'm asking for. So I feed all this into the AI by using the explain and fix button and it comes back with an uh the error message indicates that there's no column name name in the customer's table. You should use first name instead of name. Here's the correct query. You copy that query onto the stage and run it and

you get your results. So it's a quick way to double check what you're doing. So how what do they do? Well, as I mentioned earlier, I was a little disappointed. Works much better with fairly standard versions of Postgress and SQL Server. Does fairly well with MSSQL server. Uh but MySQL kind of shocked me that it wasn't quite there. Grock came up with this where it's basically um

you have a table named customers. Uh remember I wanted the ID to be a UYU ID. So it carves out uh a 36bit car column. First name and last name are VC car 100s. Address is a var 255 and birth data to start as a date. Notice that they're all not null. Bonus points for there. And it sets up the primary key is the ID and the

birthday. And it also says you can optionally uh index if you frequently search by name and how to do it. And then it has partitions. Partitions pay attention to in the next couple examples because they're kind of weird. Uh what they did is they went for earlier 1970, the 1980s, 90s, 2020, the 2030s, which we haven't come up yet. So it's kind of planning for the future.

And then anything past that. And then you see that it has test data. If you're a Texan, you'll be happy to to note that they used a wide variety of addresses. I don't know if it's left over from the world database, but we have Austin, Dallas, San Antonio, Fort Worth, Plano, Arlington, and others. So, you go to run this and oh, here's another alternative way to load

the data to get the UU ID. So, you go to run this and you get an error. Uh, the error message indicates that there's a syntax error near engine equals NODB row format equals dynamic default car set UTF8 MP4. and uh you send that off through the AI and it comes back and it corrects your code. Uh you notice that basically removing the chunk chunk down here

to there. Google Gemini um very interesting that they have a 16bit binary ID. They've changed the var down to a varcar 50. Uh the address is still a varcar 255 and birth date is still a date. And then the partitioning is kind of odd. 2010, 2011, 2012, 2013. Um I guess they're expecting a bunch of young people to be using this instead of us old geysers. Um

the the data that it has, it actually uh has a call to a uh unhex replace UU ID to get the ID number. Um the the cities are any town, other town, some town, townsville. So good data. Now when it comes back and says the partitioning function is based on birth date but the column is not included in the primary key and you have to do that.

So you ask it to fix it and changes the primary key over to ID and birth date. By the way you have questions please please let me have them. Okay. Olama the the one self-hosted. Um this was interesting in that um the customer ID is now a UU ID marked as a primary key with a default UU ID function call. Uh first name and last name of

the Varcar 255s as is the address. Uh birth date is a date and birth year is a used for partitioning and it's has an index set up on it. Now, anyone here who hasn't had a cup of coffee, I asked you to explain this uh sequence over here, um I looked at that for a good 10 minutes when I first saw it and could not make heads

or tails of it. It's one of those things where um it's an obvious blunder by the AI. Uh once again, is in the test data, we have a bunch of people from any town, USA. Uh when you go to run this um there's a problem with UUID data type and its default value. MySQL does not support UUID data type. Instead you should use char 36. assumption and

when you correct everything that's what it it looks like. Uh notice the partitions are 2002 2006 2011 2016 and 2021. A little different than using every year or by going by decade. And what was also interesting is I tried to put in the data and it caught it that there wasn't a default value for the the uh ID field. So it we had to go back and

and fix that. Wasn't set up as an auto. Okay, the last one chat GPT. Uh this one once again customer ID is a car 36. First name is back down to VR car 50. The last name is also 50. Address still says 255. Uh birth date is a date. And notice it uses a generated column to come up for the year for the birth year. And then

it's doing the primary key on that generated column of birth year and customer ID. Partitions are decades starting in the 60s running through the 2020s. Thought that was interesting. Also interesting was that they actually used Texas cities for the cities when I ran this go through there. Uh the provided snippet seems to be part of a create table statement but the beginning of the statement is missing

primary key and partition by cause should be part of the create table. So when you run it through the AI fix, it comes back with this and it's a little bit changed but all that much. But it does do something weird. It creates a customer partition So this is a thing where if a junior programmer handed this to you, you take them aside with a whiteboard and

you explain how you don't want to do that. So two out of the four kind of did B minus work. The other two kind of you need to be watching uh to compare them all. Um ids ended up being either 36-bit chars or binary 16. Uh names ranged from barcar 50s to 255s. They were all listed as not null which is good for keeping your data clean.

Birth date were all date fields and primary keys all eventually ended up being the ID and the birth year. So and the other thing is I I want to mention is the approaches to partitioning. Uh Grock had eight by decades, Google had 13 by year. Uh five partitions for a llama and eight by decade for chat GPT. So if you're a teacher at a community college in

Arizona and you handed out this assignment and these students hand it back, you'd understand they understood the concept. They may not be great on the execution but uh the students at least have a grip on how things are working. So the conclusion when creating a new table by an LLM right now it's kind of a mixed bag with MySQL. Um all four LMS as I mentioned earlier

do better with other databases. The partitioning schemes are kind of dramatically wild from each other. If this was a team competition you'd probably want to sit down and hash out which one you'd want to stick with. uh they're all workable to a certain degree other than doing it by year. And also, you'd probably want to have a good discussion on how big is a name or an

Oops. So um the the the company I work for now DBER is a tool for working with multiple databases. Uh it has several great features including its built-in AI assistant. Now our competition also has similar AI assistants and I'm going to talk about DB beavers just because I work for them and I'm much more familiar with them. But if you want to talk about Jet Brains or

someone else's product that can do that way DBver's product worked is before you send data to an AI, you have to confirm that you know that you're sending data to the AI because you might have something that's company sensitive or classified or highly privileged. You don't want going down that great party line that is the internet. In that case, you might want to use something like Oama

for your LLM to hold your data. Uh, also with DBaver, you have the option to send your keys, primary keys, unique keys to the LLM to help it determine what it can do. Also, you can send sample data. You don't have to send any data at all, but you can use it to send sample data. So, if you want to create a query and all it knows

is the metadata, the the table definition, you're safe there. Also, you can exclude what tables to use and exclude others. uh, the default is to only send metadata and you have to decide whether you want to send anything else. And what's really handy is I think now we support seven LLMs in the pro version and five in the community version if that's right. >> Yeah. And uh

once again, you control your data before you send anything over. uh you have to say yes or no. In this case, I'm trying to use the MySQL Squila database and it get and it asks me do you confirm AI usage for this table. If you have something that is highly classified or is insensitive, do not send it over without checking with your security folks. Now, one of

the interesting things about our product is that you can type amperand AI and type a question or a question into the AI that'll be turned into SQL. By the way, we support like 12 languages, uh, French, German, Spanish, Basque, and several others. Um, and here I just typed AI. Who are the top five customers and how much revenue do they generate? Now, if you're the data guy

and your head of marketing comes in and says, "Hey, give me this information," you're going to spend a lot of time going out to that table uh trying to figure out all the things, all the columns you need to gather to be able to put that into a query. But the good thing here is that uh you put in your prompt, it will generate the SQL for

you and you'll see the results faster than you can to go out there and look at the various columns. there's another feature we have is that you can describe the SQL you want. The last one I said the AI, you know, give me the top five customers. In this one, I'm type I'm typing dash dash, which is a comment space top five cities for revenue. And I

type in select because I want to use a select statement. And then I hit control alt right arrow and it will generate the SQL for me. So, for those you who are busy and you don't want to dig through the the uh DDL for your tables, this is a faster way to do stuff. And we even have a pretty printer. So, if you want to clean up

what you see on the left, that will do that for you, too. Uh, one of the problems that I always had where I worked is that we always had these projects that I called thrown over the wall projects. Hey, um, so and so down receiving came up with this database that's running on a You're smiling because I can tell you you suffer through this. Um, he he's

off on vacation, but we need to do this to keep the business running. Could you could you take this spreadsheet and turn it into a database or take this dbase 2 file and turn into something we can use? Um, if you haven't had the pleasure of this, it it's terrifying. So, one of the things that DBER can do for you is you can ask it the bottom

there in the red arrow, describe the database object. And in this case, we have a new database and it's our DBver's uh example database. And I say, well, tell me about this database. And it comes back and says database schema consists of several inter interconnected tables that store information about a music store. We issue you young folks. We used to have to go to stores to buy

our music. You just couldn't download it. You you actually had to go someplace and go through these things called records and read through them to find the one cut on the album that you wanted to buy. You just couldn't pick the one you want. Um the album table holds information about music albums including an album ID. track table contains details. So this is a handy tool to

figure out you have this new schema. What does it actually do for me? Um also works with tables. I've always been lucky that I've inherited wonderful tables that were done by someone who was at the prime of what they were doing and knew they were out the door soon and the tables were not always exactly relevant. So here I'm asking for a description of the table called

artist. The artist table's database schema is a key entity that stores information about various artists. It has two columns. Artist ID which is an integer and serves as a primary key and name which is a variable character field that holds the name of the artist. So not only is this great for elucidating what's going on with what you find documentation. How many here people love doing documentation?

Yeah. one somewhat armed. Uh, another thing is occasionally someone will hand you a query and say, "We have no idea what this does. It used to work. Um, and the guy that has it is off with a broken back in the hospital for the next six months. Um, could you take a look at it?" So, in this case, we we have this query and I'm asking it

what does this query do? And I put in the query which is select C.name, C.astame, last name count distinct D and rather than going line by line and join by join as you can see here there's what four joins um I can ask the AI what it does and it says this query is designed to retrieve information about the top five customers who have rented films from

the most distinct categories and have the highest total spending. Now, where this also is handy is you could come in here and ask the AI to expand this query. Okay, only cover California, Arizona, and Nevada. Boom. It'll change the query for you. Um, here's another one where I'm I'm changing a query on the fly. I have this query as the last one. I say in the prompt,

please list by state and list of revenue. it will correct that query for me and away it goes. Very handy, very smooth. Uh query builder. This is also something that's very handy. Uh I'm an old-timer. I grew up in the age of fortran cobalt and the early days of C. And uh having to design code with drag and drop was something that happened well after my educational

experience had ended. Uh for DBEver, what you can do is you um open up a window and you click that thing to the right arrow where it says builder. And then from there, you drag and drop the tables you want. Uh in this case, I've dragged and dropped the customer table, the stores table, and the rental table. And you notice these blocks in between the various tables

up there. You can change the join. So if you want a left join or right join, you can adjust that. And then as you notice down here, as I drop stuff in there, it's already setting up the basic part of the query on what joins where. And from there, I can select by clicking boxes on each of the tables what columns I want. So if I want

return date, I click on there and it automatically adds that to the query. Very handy. So the uh big premise of this was uh talking to a lot of DBAs and professional data professionals and folks who manage data for a living. you know, is this a step forward? Because AI has been overhyped. And this is one of those things where you play with the D Beaver product

and you find out how valuable it is because it saves you time and effort and from digging a lot of stuff. And for quick and dirties, it's absolutely amazing. And for the more complex queries, it can get you started on the right foot. So, do you need an AI assistant? Well, I'm making the case for that. Uh, so please give it a try. I think it's like

going from VI or Emacs into an IDE. It's going to be the thing where you're used to having keyword completion. Now you're going to have query completion pop up for you. So you'll be that one more step ahead. So what do you get when you do this? Well, can write queries for you. It can do it multiple languages. It can explain the structures of queries or datas

for you. Uh it can augment making queries doing it a lot faster. you can probably do it by yourself. And the great thing is you can do as much with it or as little as it as you need. Uh if you never want to write SQL again, we're there. Uh if you love writing SQL but need someone to look over your shoulder trying to figure out what

that one syntax error is, it will do it for you. Um one of the things I have is I look at our trouble tickets every morning and we have a bunch of labels to class classify. Is it a Postgress error? Is it a UI error? is something with the Java that we're we're embedding and stuff on and those are all in one big string and I say

okay find me the label that says MySQL and tell me all the fat last entries on that. It does it for me. I don't have to remember how to go out in Postgress and break down a string and chop it up and bring it back peacemail. It will do that for me. questions, answers, comments, complaints. What do you all have? Yes, sir. >> Um, let me let

me point out Scott in the back who um is on the MySQL community team where I used to be. Will my skill go away? Um, no. I think the last two things we turn off in this world will be a Cobalt application and a MySQL57 database in that order. Uh I don't think it's going away. Oracle is appearing to steer away from um future development of a

lot of the neat stuff they used to do for MySQL, but they've been making noises this week. They had a what two and a half hour webinar talking about how they're going to uh bring some stuff from the paid versions of MySQL into the community versions. So, knock on wood, um, we get another good 15 years of stewardship out of Oracle. Um, reality is right now there's

a lot of folks who are very confused and don't know which way to lead. Um, I have a blog I can show you later where I broke down what the various options are, including, you know, don't do anything because it will still run to changing databases, changing vendors, um, you know, maybe let's just letting the cloud providers run everything for you. But you do have options out

there. But my school going away, no. Um, I can actually find parts for my neighbor Studebaker car online. Uh, they're not as common as they used to be, but they're still out there. Yes, Well, I'm lucky in that the pro versions of DBER support nearly 190 different databases, including all the ones you mentioned. Um, our community edition, I think, doesn't support NoSQL databases or graph databases, but

if it has an ODBC connector, we can talk to it. So, I'm lucky in that I have a tool that lets me talk to just about every database out >> Yes, sir. Yes. Um, drop by the booth. I have enterprise and community on my machine. You can play with both. Uh, you're a little little more limited to I think Google Gemini. U tell you what my mouse

working the the funny thing come by the booth we'll talk about pricing. It's kind of embarrassing because we're we're really low cost. Um and by the way this is 26.0 the version that just came out. Uh, download a driver real quick. So, if I want to use the community here we go. Open AAI and GitHub Copilot. I'm sorry. Uh, the enterprise version has a much richer much

richer set. Um, this is one of the things where if you're paying for a big paying for a big bill on an LLM, um, we're not going to be your we're not going to be your biggest cost there. Let me try enterprise edition. Can't lock because I have two additions. And we are adding more LLMs in the future. I just don't know where. I can't mention what.

no, that's Hold on. Let me get rid of the community edition. Okay. Enterprise edition. This is um Enterprise Edition. um we have Amazon Bedrock, Anthropos, Claude, Open AI, Copilot, Gemini, Obama, and Open AI. So depending on what you want to play with, if you're just starting out, uh try the enterprise and see what you like. And then by the way, you get a few free two-week trial

of any of our pro versions if you're interested and drop by the booth. We'll explain it. Any other questions? Yes, Scott. >> Correct. >> Yeah. So, if for the the recording, Scott was reiterating that AI's generating tables from scratch is kind of rough right now, but if you already have the tables out there, it does a lot better generating stuff from that. You're shaking your head. Have

you done any of this stuff with AI? >> And what are your feelings about having Okay. And and these are your three generative selves that you've uh created. Yeah. >> Well, congratulations. You are entering a very interesting world. Uh any other questions, comments, concerns or Well, let me go back. if you're a speaker, if you're one of these people, um, we have Scott back there, Matias, uh,

we have Phil. Any other speakers here that I'm unfortunately not recognizing? Iiggore, right? Igigore. >> He's, matter of fact, he's next. Uh, so watch out for Gabriel. And, um, that's it. And with that, we've kicked off 23X. if any of the questions. I'll be here for a little bit. Um I and Shannon who's our head of marketing will be over in the booth and she's got the

official DBaver outfit. Um >> big fan. And uh by the way, if you haven't tried DBver, it's the only product I've ever rep where people just come up and rave about it. And the complaints are usually feature requests, not oh my god, you guys screwed up everything. It's a lot easier than working for MySQL or Perona. And with that, no other questions. Uh thank you all for

attending. Hope you have a great Sound check. Nice. Okay. Hello. Can you guys hear me? How are you guys? So, I will make sure to make this as smooth as possible. I am happy to be here with you guys at the scale 23X. It's very nice to see you all here. My name is Shannon. I will be introducing you guys to Igor Donovski. Um he is from

Planet Scale and he will be talking to us about Invidas for newbies and it's scaling with um my SQL the YouTube way. If you have any questions or any thing that you want to talk about, you can go ahead and let me know. We can go ahead and do that afterwards. But let's give a round of applause to Igor Donovski. Thank you. Good morning everyone. Um this

talk is about betest for newbies. Uh scaling my scale the YouTube way. Uh my name is Igor and I come as enterprise customer engineer at planet scale. have some a little bit more than 20 years uh IT experience primarily working with databases for the last 15 years. Uh primarily MySQL and MongoDB and few years uh also with posgress SQL. Uh on a personal note, husband and father

um traveler speaking at conferences. and I have uh addiction to play these LinkedIn puzzle games every single day. I don't know if anyone else is doing So on today's agenda, um the talk is about my scale. So what are the MySQL limits? Uh how do we do the disaster recovery for MySQL, horizontal or vertical scaling, query management, online DDL, high availability and how all this fits inside

the test starting with MySQL and what are some of the limits that you may hit. So even if you are running your database on a single server and let's assume that single server is with 100% uptime that you don't need to restart that server for any time. You don't need to apply any patches. There is no hardware crash. You will still hit some limits when your database

grows. Those limits are that you cannot grow a single server beyond at as of today 32 terabytes RAM memory. You can have up to eight socket CPU each with 144 cores per socket. And this server alone will cost you nearly half a million dollars uh without any storage. So the storage is on top of that price. If you are hosting this some in you know cloud provider

then you you have to also think that you will be paying for bandwidth and uh maybe other maintenance uh tasks. The the MySQL server itself doesn't have a limit on the database size. So uh in theory the database size can grow um indefinitely. Uh however there is a limit on the table space size and a single table space size can grow up to 64 terabytes with the

default uh 16 kilobytes page size in MySQL and that is uh a limit that you can hit if you have a single table unpartitioned. Uh the MySQL server has a limit of 100,000 connections. So that's a hard limit. from my personal experience I have not seen any ser any MySQL server that's running close to to this limit but uh also at the same time my cannot handle

100,000 threats running unless there is some thread pooling uh or a proxy um in front of the server. Uh if we speak about how many rows you can store in a single table that is using big integer as a primary key that is uh something like 18 quintilion rows uh which should allow you to generate uh out increments for centuries. the query management in MySQL is improving

uh version over version. So we have some of the things uh as listed here. For example, you can set a query timeout in MySQL and the query will die if the the engine doesn't uh response in 5 seconds for example. Uh you can also use optimizer hints. Um so you can tell the optimizer uh this is the index that uh I want you to use for this

query plan. Um, one thing that's lacking today in MySQL is that if you are running a query that doesn't have where condition uh actually doesn't have a limit on the rows returned, you you may saturate your uh bandwidth and uh just select star from a table will potentially have a big impact on your application. But uh there is uh option to uh fix this by just limiting

the the server response. So n number of rows will be streamed even if you run just uh select star from users for example. And also another thing that uh you you will need to plan in your application uh if you're using just plain uh MySQL if you run the same query like select start from videos where video ID equal one that query if it's run 100,000 times

every single time it will uh run inside MySQL. So the application should be smart enough to run the query once and if there are a bunch of requests coming at the same time then the same result should be returned to the application. Another uh thing that is improving inside MySQL is running DDL. uh MySQL is evolving and instant DDL uh is now a feature that for example

if you want to add a column in MySQL that operation will be instant. Um however some operations are still not instant and even with instant uh you still have to to do uh metadata lock for other things like adding a secondary index uh you will be locking the the entire table and that operation is also resource greedy. So uh if you run it on a single MySQL

instance, it will use all of the resources on the server and that's just for a single server. It's not revertible. If you run alter table, then you cannot uh stop it and and revert it. So there are community tools uh that help with this like pline schema change, ghost, spirit. The way how these tools work is they don't touch the original table. They create a shadow table

uh on the side and start coping rows to the shadow table. And with this there is control with throttling. So you don't take all the resources on the server but you slowly copy all the data from the source to the destination and at the same time if there is a modification on the existing rows that are copied it will apply the changes to the target table. When

it's done, then it will just block the operations briefly, start uh rename rename the original table to something else and the destination table to the original table and that's basically swapping the tables. These tools are also evolving. So resuming on failures is something that uh is in progress. uh but also there is important aspect for this uh change to be able to revert the the DDL. So

if you don't like the change after you did this uh this community tools doesn't really allow you to uh swap back high availability in MySQL we can run on a single server but that's not always uh ideally uh ideal single servers crash and we need to to be highly available for production systems. Most production systems uh take high availability seriously. Uh the way how this is done

is that we need to have a copy of our existing database uh somewhere else. Uh that can be active uh region or it can be passive. So if region A dies then the application will switch and start using region B. Um currently in MySQL we can do this by cloning uh region A uh into region B and making region B to be a replica of region A.

If we want to switch from region A to region B, the application needs to change the endpoint from region A to region B. And at changing that endpoint, you're switching all your read and write operations. Or if you're using some then you'll be you should be able to switch just the reads start reading from region B or just uh switch portion of your reads so that you

warm up region B and then you switch uh also your rights from A to B. Uh so my scale doesn't uh do that for you. Um this is something that you need to plan and make your application with all these changes. Handling node failures. Uh again for high availability uh it's important that has uh the information what is on the backend side. So what is the primary

node? what are the replicas for this primary node? What would be the logic that the application will use? Uh when my primary dies, then I need to uh promote one of the replicas to become the primary. That promotion is not something that the application can do. So you will have to do third party. uh the community tools that exist today is something like um orchestrator or this

uh this even might be MySQL InnoDB cluster. So DNODB cluster itself can promote uh a new primary uh but it's important uh to mention that uh with losing the primary the application may lose transactions. So the in-flight transactions when the primary dies uh the application should have that logic hey I didn't get a response I need to retry this transaction as well as if a replica dies

then the the read operations that uh were sent to that replica will also return um error resolve is the application cannot speak directly to the replica set. Uh we need a router. So the router has all the information for the back end. uh the logic if there is a third party application like uh orchestrator will take care of what is the primary what are the replicas in

case the primary dies then a new replica will be promoted and it will update the router at the same time. So your application doesn't need to know what's happening on the back end. uh it's it talks to the router and the router gets the information from the back end and this can be uh asynchronous replication it can also be semi-synchronous replication or it can be inb cluster

with group and also uh a small touch here is that uh a single router is also a single point of failure so you will probably need to uh deploy your router to be highly available. And this is the solution from MySQL that works relatively well for high availability and disaster recovery. So here we have all of the single regions in a highly available setup where we have

uh at least three nodes per region. We have three different regions for disaster recovery. So if any of these regions goes down then the application can speak to uh the primary promoted in the other But the the issue here is that uh we mentioned at the beginning this single server is a MySQL server with limits. So you scale this server up to a certain limit limit and

then uh that's all you you cannot scale uh more than that. In some cases the applications need scaling and forc scalability we can add multiple replicas. So adding multiple replicas allows us to uh kind of horizontally scale our reads. But the rights are also something that uh needs to scale eventually and scaling the rights can only be done on a single server by vertically scaling the the

instance and growing the hardware. So throwing hardware uh until we we eventually hit the hardware limits for doing read operations. Uh there are different replica types that you can use. For example, for OLTP workloads, you can have replicas that just serve uh OLTP. Uh you can have analytics replicas that your application sends analytic uh analytical queries just to those replicas. There is option to also use delayed

replica in MySQL. Uh which can be useful also for disaster recovery. uh we can even go uh further that one of the replicas can be intermediary master uh intermediary primary and we can add additional replicas behind. So uh scaling rits is somewhat easier but let's take one uh actual uh example uh by a simple application that is a gym tracker. So something like these devices that we

use and wear daily. We have users table. We have exercises and exercises table is something that has relatively small number of rows. So there can be one 200 different exercises and as we have users in this database users table grows but what actually grows here is the exercise logs. Every user is generating tens if not hundreds of exercises daily and the exercises logs is what we have

here as maybe 90% of the entire data set. Now if we have this on a single MySQL server and the application talks to this single server we know that after some time the exercises logs will just be uh so big that this single server cannot no no longer fit. So we we can grow this single server but that is not uh the actual solution for scaling One

thing that might work and we we have seen this there is a functional sharding called uh splitting the databases into their own shards by torturing the application and having a router to go and query the users into its own users database and the exercises logs will be on a separate um database so that we can scale both independently. Maybe the user table doesn't grow uh that uh

fast but the exercises logs will be. And now if we look at this we have to query the user's database once and if the user is requesting their exercise logs then we have to go and jump on the other side and query the exercise logs for that user. So we may have to go back and forth depending uh how we design this application and what what information

we want to bring to the users. the next thing that we we can do uh scaling this is splitting the exercise logs into their own charts. So we have the users chart that is somewhat stable but these exercises logs start growing and this is where we actually need to have our application know where is the exercises logs from one to 100,000 where are the exercise logs for

users 100,000 to 200,000 and so so on and so. So for this the application will have to use some metadata that the metadata will uh be uh up to date information. Where are the exercise logs for these users? If something changes, it needs to update the metadata. So the application doesn't need to have all this complexity but the router will pull the metadata and it will uh

keep up to date. So this is not just about where are the records on what chart we need to query. We also need to know and update the metadata whether the primary in chart one died or there was a replica that took over the primary. So all these changes inside the cluster we need to update the metadata and the application when sending the queries. We'll need to

know uh actually the router uh that the application connects to. We'll need to know uh what is the primary node and where the data lives. going a bit uh deeper, we have the option to collocate the the users and the exercise logs into the same chart. If we split these tables and partition by in this case let's say the user ID. So the user ID is something

that exists in the exercises logs. So if a user with ID 10 is trying to query their data, the exercises logs for the same user ID will be on the same chart. So we don't need to go and jump in between shards, but the actual user data will always be on the same shard because we are partitioning and sharding this data based on the user ID. Now

if you notice in this case the exercises table doesn't have user ID and it's while it's relatively small we can afford to copy the exercises table on every single chart. So when we query shard one, we have the user ID 10, we have the exercises logs for user ID 10 and we have all the exercises. So when that user wants to query their data, everything is on

the same chart and we don't need to scatter across all the charts. So this will be efficient query. Now the metadata uh needs to have all this information and we need to update uh the metadata so the router gets all the details and how do we do that? Well, all of these boxes that are MySQL, they are being controlled by additional process that knows what is the

MySQL uh role in this cluster, whether it's primary, whether it's replica, uh how that uh what is the the user hash, what are the the user ids and what is the exercise log. ids that live there and every single change that happens that single process is updating uh the topology server the the metadata. So how that uh looks like uh this is actually what a test cluster

looks like. So we have routers in the middle that are stateless. The routers get the metadata from a topology server. Usually there is a network load balancer in front of the routers and the application connects to a network load balancer. Then the routers the vit gates talk to every chart and when a query comes based uh shart ID it will go to uh the designated shard. So

that can be any shard but it's only one. That's important uh if we have a properly configured sharding. So in this case if it's user ID 10 and we are sharding on the user ID column when the query comes it will always go to a single shard and we don't need need to scatter the queries across all shards. what are the concepts in uh vit architecture? We

have a concept of a key space and this is uh logical database. Uh if we look at uh MySQL concept, this is uh like a a regular database. Uh each consists of multiple shards and every shard is a three at le it can be uh three node replica set. It can be um more replicas with a single primary but it usually is one primary and uh two

replicas. Each of instances is being controlled by a process called VT tablet. So VT tablet is the process that uh is controlling the MySQL D underneath. When MySQLD is starting, if there is a failure, bringing up MySQL, if the process dies and it needs to restore from backup, VT tablet is the process that's responsible to do all that. And Vitigate is what is talking to uh vitable.

Vitigate is pulling the data from a topology server that usually is um etc. uh it uh can be uh zookeeper. I think console was option uh in the past but it's deprecated. The vtctld uh command is used to update the topology server and do uh changes on uh the vest cluster. Vtor York is the process that takes care of every single SH topology. If a primary goes

down then vtor will be uh the process that takes care of promoting a replica to become a new primary and vt admin is just a web user interface so that you can uh see the entire uh v architecture in a nice u web interface and uh vit Vitigate also speaks the MySQL protocol but uh it talks gRPC so your application can also talk uh gRPC to vitigate

when vitigate gets the mysale connection that's where it's terminated and vitigate to v tablet uh speaks just uh gRPC protocol and inside the entire test cluster it's uh gpc Okay. Uh this is uh one slide showing um some of the logos that are using V test today and scaling MySQL uh with Vest. Uh I briefly touched on the Vest concept. So the case the logical database consists

of multiple charts. There is also uh Vitz schema. So this is the schema that Vitz is using to know uh what are the key spaces uh inside the VES cluster. How do we chart uh our tables? Uh the vindex is uh the index that uh is showing what is the uh key space ID that belongs to uh particular uh column that we shard on. And while Vitz

is complex on the back end and it consists of multiple charts and uh there are a lot of uh databases on the back end. It provides an illusion to the application. So the application is like connecting to a single MySQL back end. The application doesn't need to worry what's on the back end. where uh where is the replica and it does that uh by the power of

v replication. So v replication is something in vest that is used for importing data into vess. So you can copy data from uh external MySQL into V test. You can use V application to um move data inside V test. So you can uh copy for example your users uh from one key space to another case or reshart and you can uh materialize. So the example that we

were using for exercises uh we can um materialize the exercises table across all vit charts and how the v application works it just selects uh star from the table and it starts streaming the rows to uh with a vstamer and it applies the rows on the target with vcopier. It does that for a default of 1 hour and after that 1 hour it goes and scans the

binary lock on the source and for the already copied rows if there is any change then it will apply uh on the target. With this we can import data from external uh key spaces to the test. And the way how this works is we test have uh has a move tables command. It uh creates a V replication workflow. Then once we copy with V replication, we can

um run VIF. So, Vest also has this Vive operation that it check sums the source and the target and it then it uh makes sure that both source and target are the same rows and once you are comfortable that there is no data drift between the source and target. You can switch your application from the source to the target by doing first switching grid operations and then

uh switching right operations and you also can uh roll back because when the test switches the right operations it will do a reverse workflow. So eventually your uh target becomes a source and your original source becomes a target. Um I have a V replication demo but I don't think we will have time. So we are we have a boot to 15. So you can come uh check

this V replication demo in action. uh the V application also allows you to do online schema changes. What I want to mention here is that there is a online schema change that is online and even if you have hundreds of charts on the back end and your application is connecting to a single MySQL endpoint. When you do a change, the V application takes care of everything and

VES will run the change on every single chart. Now, this may look simple, but it has to coordinate every chart. It has to go and all of these charts might take different time. So it has to wait that all shards are at the exact time before it actually does the uh DDL operation. So VS has this native and it also has an option to uh revert. This

is based on the V application. You can watch the migration live. So when the migration is started, it has a record in a vitz migrations uh table and it's reversible. So once Vitz is finished with this change, you don't like it, the application is having impact. You didn't uh think that this change may cause negative impact, but at the end it did. So you can just do

revert v test migration and you will be back to where you were. So with hundreds of charts you have the ability to just say go back and vitz also supports this declarative migration. So you don't need to run alter table but you just say create table and it will figure out what is the difference between the source and target and it will apply the change. So the

schema changes are easy. Um maybe by some it's much uh easier compared to you doing this uh with some other community tools. It's easy to reverse and it's all powered by the V replication. So summary um MySQL has limits vertical scaling is definitely not an option. uh you will hit the wall after some time. Horizontal scaling is what uh Vitz uh supports and it's a sharding framework

that pretends to the application that this is a single MySQL server. It's all powered by the V application. Oil and schema changes are made easy by uh the power of V replication. It has built-in high you also don't need to worry about whether any of your shards has high and uh you don't need to do any application changes your application can talk to a vitest cluster uh

with any legacy code with any OMS or frameworks that you have with that um thank you and open for any >> So the minimal infrastructure is that it you will have at least one shard. So that's one primary and two replicas. You will need to have it CD for topology server. That's also three HCDs but that is not like a huge hardware requirement like you would do

for MySQL. Uh you will need uh vitig gates. So that's also something that can live maybe in Kubernetes you can have this in autoscaling and uh also with test has a kubernetes operator uh that's open source. mostly yes but you can also deploy it on virtual machines uh like on EC2 uh is just all of these components they will have to be highly available you will need

to have uh you can run multiple instances on the same machine but you will just need to take care that these machines need that many resources like CPU, memory, disk uh but that's like you will need vitig gates at least two for high availability you will need uh topology server uh mysql at least one chart will be a and if you are in the cloud you may

be putting that behind load balancer so these are the hardware infrastructure requirements That's all. Okay. Thank you. Oh, there's still more people. Hi Okay, how are you guys? That's great to hear. Okay, so today we're going to have Gabriel Siccilani from Rem uh talk to us about the hidden life of internal temporary templates. So, let's give a round of applause to right, cool. Much better. All right.

Hello everyone. excited about spending the next hour talking about my SQL internals. Okay. Right. Who wouldn't be? Um, so let me see some hands. Who who's running my SQL in prod here? One, two, three. Okay. Uh, any mission critical applications or just prod but not so much. All right, cool. Anyone thinks that postgris is better than my SQL? Okay, cool. Yeah, you're on the right session then.

And uh if if you do, I want I want to know you have some facts to support that. But yeah, we can talk about that later. All right, let's see what this is about. Um so I'm going to start with doing some introduction about temporary tables in general on Mariab my SQL. Um that we're going to see how those uh temporary tables works, how those u the

mechanics, right? Um and um we're going to see like what telemetry we have available to you know to see um yeah to measure that those temporary tables. Uh we're going to see why that is important. Um you know uh I'm going to show you some commands and some strategies to to deal with them. And lately you know and finally we're going to do some lessons and takeaways.

Um feel free to ask any questions you know let's let's keep it uh you know uh keep keep the questions coming. If you have any questions just interrupt me. uh go ahead and you know ask for a microphone. I'm I'm good. We don't need to wait on till the end of the session. So with that being said um uh let me a brief introduction. I started as

a as a junior Oracle DBA back in 2006 as many other DBAs. Then uh the company I was working for they found out they were spending a lot of money in Oracle and they said okay we are going to um we're going to start using my SQL and that's when I moved into that that world. uh since then around 2014 I've I did a lot of work

around my SQL as a DBE consultant um and even some uh development as well for for one of the big uh you know one of the big cloud providers um I started building tuning wizard um October 2022 that's a automatic query optimization API um that was uh then acquired by Rolim last year and since then I'm helping them um you know to take automatic server performance optimization

to the next level. And um and I'm from Osiris, Argentina, by the way. So yeah, the land of Messi, you know, tango, um inflation, right? Uh so so yeah, that's where I'm from. Uh so if you're thinking uh Argentina is the right country, you're wrong. That's actually Brazil. You know, we are down there, right? It's a lot that's a lot of confusion. There's a lot of people

thinks Argentina is part of Buen Osiris is part of Brazil. We're not we're down there. So in case in case you were wondering. All right. So kinds of temporary tables, right? Um there's a lot of you you use the term temporary tables a lot with with MySQL Mariab. Uh so we have userdefined temporary tables. The ones that you would create with a create temporary table statement. Um

not the ones we're interested in today. Um you also have temporary shadow tables when you're doing some schema change, right? And that schema change cannot happen in place. So you need to create a copy of that table. uh those are called shadow right um also temporary table not not the you know not the goal of this session so we are not going to be talking about those

we're going to be talking about that third item there internal temporary tables right um and um yeah so those are table temporary tables are created automatically by the server uh during query execution right what what are the you why are they created right usually because you need some additional So the server needs some additional space to compute that result, right? So it allocates some more space somewhere

and we're going to see where uh of course tring memory disk uh to perform that query analysis and to compute that final result, right? And you know the question is why why should we care about this? Like it's okay so cool temporary tables and stuff but why why should we care about this right? And of course these temporary tables are created during query execution and anything that's

created like during the execution of course is uh on the critical path and it will burn execution time right it will make your query to run slower. So you should be caring about that. You should be asking yourself okay what what the query is doing and if it's burning time on temporary tables then why is there anything we can do right so performance right that's the that's

usually the answer uh that's why it's interesting to to see how this works and and understand a little bit more about them um there's so if you if you check the documentation there's a lot of uh situations or scenarios where uh temporary tables are created um my SQL has a good good list Marb not so much you're going to see you you're probably familiar familiarize with MB

documentation sometimes it's not so good um but yeah I I wanted to you know focus on these four uh cases which are kind of the most common ones so um group buys and order buys around those operations there's a lot of uh good you know good chances of of having to create temporary tables um especially if you're doing like group by and then order by and those

those two clauses are different well it's probably the server is going to be generating a temporary table for that um usually group buys that are not using any indexes are going to resort to a temporary table. So um those are like again around group buy order buys there's probably a lot of uh chances you're you're getting a temporary table if uh you don't are not using an

index right so um subqueries right that's another scenario when you run a query you use subquery as a table sometimes that needs to be materialized right that's that that subquery needs to be converted into some temporary table so the query can continue executing u that's another scenario where you will get a temporary multi-table updates. You're doing updates. You know, you you're doing a join. Uh that's another

scenario where the server will build an uh internal temporary table first to then proceed with execution. Another scenario perhaps not that common when you do an in select and the same table you're reading and writing on the same table. Then also temporary tables are created. And there's a lot of other scenarios, but I think those are these are the most common ones. You're probably familiarized with with

this. Um and yeah, we're going to see later how how how we can actually spot this, right? It's not that we need to rely on some list. Uh we can actually ask the optimizer if a a table's been used, right? So um you know at a very very high level, right? Very simply, uh so the optimizer knows that it's going to need a temporary table, right? So

uh the query execution starts, that temporary table is created in memory and the query starts adding rows there, right? on that on that that portion of memory. Um if you know of course there's a threshold right because any uh you're using memory and these tables are allocated per thread. So you can just let uh threads go wild and allocate all the memory they need. So you need

to set some sort of threshold there. Um probably familiar familiar with that parameter u tmp table size that's that's going to be your your your main threshold. Um if the memory is usage is below that then uh the query is just going to create that temp table memory. It's going to use it. It's going to drop it and you're done. The problem starts when you hit that

threshold. Right? So uh no more memory can be used. The then the server spills that table in memory sorry on disk uh creates a copy of of all the rows on disk and then continues to work on the uh disk copy. So that operation of moving rows from memory to disk is usually expensive in terms of total execution time. Right? You can argue that okay how expensive

it is is I know another 10 millconds 20 millconds it could be considerable right especially if your result set has 100,000 rows or a lot of rows uh that copy operation is expensive right and that's definitely something you want to avoid uh because it's adding execution time to your to your query. So this is how it works at a high level right um so historically uh the

for the memory phase the uh memory engine was used right um my SQL mar multi-engine uh server so you had a special type of table special type of tables that are created in memory uh and developers said okay we need to put rows in memory we have already this engine already created so let's reuse that and let's use memory uh internally this memory engine internally to store

our our rows memory and then um for the disk part right when you had to move to disk my isome was used uh back then um starting with my SQL 57 inb was introduced for uh temporary tables that's because um you know my ISM has some limitations when it comes to you know uh the the additional files that are generated every time you create a my table

um you know my ISM uses this key cache right that has a mutx that is acts kind of as a bottleneck, right? When you have multiple sessions trying to use temporary tables. So that was not scaling pretty well. Of course, my isome is not crash safe either. So sometimes the clean app if the server crashes could be uh complicated, right? You you could not just you lost

the track of that that file. Um so they said okay let's use for that. let's create this temporary table space that that was also introduced in uh inb57 and let's store our temporary tables there right uh and it's cool you know we know we we know that everything's on the on that temporary table space can be wiped out uh when the server shuts down uh or can

be wiped out when the server the crash recovery happens so it's completely safe my inb is a memory first engine right so any everything will go to memory both data pages and index pages as opposed to my isome that only caches is um index pages. So it it was you know performance was better with InnoDB and with my SQL 80 that stem table uh storage engine was

introduced uh that will handle both the uh yeah for the memory part right so the memory engine was kind of deprecated or left aside and the um temp table was introduced um I'm going to show you a little bit more about this temp table engine because it's is kind of interesting and uh it works it's a little bit more complex u compared referred to the memory engine.

MB on the other side uh it's still using memory right the approach still being it's still the same um I guess the only difference is that they started using this area engine right you know this evolution of my ISM like crash safe version of my ISM uh for the on disk part but uh the memory uh the memory portion is still using the um the memory u

so as I said before tempt table has it's a little bit more complicate more complex right it has this additional overflow layer, right? Um where uh once you're done with your memory space, it will go to uh this M map uh M map files, right? So um another limit was introduced into the server, another parameter which is called temp table max, I think or max um max

RAM, sorry. Uh so when that limit is hit, that's a global limit, right? not it's not as it's not the same as TMP table size which is only for the thread. Uh so when that limit is hit uh this M map mechanism kicks in and the server will continue to write a memory thinking it's memory it's just memory but on the on you know on uh behind

the scenes the kernel is actually swapping those um those pages to disk as needed. Uh so it's kind of a it's an you know it's it's better than having to stop and create a table on disk and copy all the rows. uh but of course it's a little bit uh less performant than uh working with memory directly and of course uh you have this second threshold which

is the um tempt table max map when that one is hit then you end up on InoDB uh tables so it's interesting uh again with temp table these other two threshold were were added and those those are global right those are those uh work at the server level um so with 57 again I I I wanted to cover 57. I know there's still people using it. So,

uh you know the internal um tmp so so this internal tmp disk storage engine variable was introduced. Uh so you can switch to switch to enob. Uh you can you can go back to my ism if you're nostalgic or something or you want to you know you want to still use it but yeah inb was made the default uh works well and uh of course because we

were still using the engine uh memory engine uh you still need to deal with um tmp table size and max heap table size um you probably if again if you did some myql tuning or you read some courses some books you know that those two variables need to go together right um maxip table size is actually a parameter that's related to the memory engine, right? But because

you're using the memory engine for temporary tables, it also limits the size of your of your uh you know of your temporary tables. Um so this the code everywhere has this like min between you know choose the minimum between those two right so you usually want to move those together right when you're using memory engine just to prevent one from limiting the other one. So because the

server will use uh whichever is smaller from those two and again again historically that's because the max heap table size is it's a limit for the memory engine itself. So that's why you need to keep both uh into consideration. Um if with H0 uh again you don't need to if you're using tempt table you don't need to worry about max heat table size anymore because you're not

using memory anymore but you have these two other uh threshold that are at the server level that I mentioned before the max RAM and max mAP right um so how how does it work right when you have temp table size and max RAM and max mAP so you can think of max RAM and max MAPAP as uh a total like total memory space that you can but

all the other restrictions uh won't apply. So in case you were wondering okay uh so is it temp table table better than memory kind of it is uh again it was created to um to um yeah just to to replace it and I guess a good thing about temp table is that it's more efficient when it comes to memory uh it will not so memory is kind

of is fixed you know the the the space allocated is fixed length uh based on the data types um when it comes to temp table it will actually allocate the memory it needs. So let's say you have a table that has begin defined as a as a data type but you have all your values are just four digits then tempt table is just going to allocate that

space for those four digits right it's not going to allocate entire a bytes just in case you have large values memory will do that temp table will be more efficient and will just allocate what it needs um as I said the over overflow behavior right a memory doesn't have that uh tempt table does have these um mm mapap based and h yeah of course it supports blob

and text which memory doesn't so it's kind of better I guess it's it's a it's a better option it's more efficient supports more data types uh and it has this intermediate uh overflow layer with end mapap telemetry um so again uh if you did any any query tuning or any server tuning before you're probably familiar with those two counters The second one created TMP tables will is

a counter that will increment every time a temporary table is used. Um and created TMP disk tables is is incremented every time that tables spilled to disk. So with those two counters you can compute uh what you know you can say what percentage of my temporary tables are going to disk. Uh so if you get that that number is I know 80% 90% then of course it's

a pretty basic indicator that you are you might need some a larger TMP table size or max heap table size if you're using memory. So that was kind of that that's kind of the key uh rate ratio that you will calculate uh when you're dealing with uh temporary tables. Um then well with my SQL 9 this this other counter was introduced uh just to keep track of

every time you hit that global server level. Remember this temp table um um um max RAM parameter. So it will increment every time you hit that and of course it's telling you if that's counter is moving it was going to tell you like you need to bump that uh up. Some more telemetry my SQL uh you have some stats on the performance schema. Uh they have a

couple instruments there. Physical RAM, physical disk. Um physical disk is kind of confusing. Initially I thought it was tracking the actually the ones activity. It's just those stats are tracking the M map activity actually. So every time you spill that second overflow layer kicks in, you're going to see some stats there about the memory being allocated. So but yeah, it's for map. not giving you any details

about um like the on disk tables like the inb tables. Um yes um there's a bug was you know this bug was raised uh also it can be a little bit confusing right when you're when this m map um layer kicks in this overflow mechanism kicks in uh you're actually you're you're writing memory but that memory is being swapped to disk so there's some dis activity it's

not uh you know pure memory activity but the create created tmp this tables will not increment with that it will only increment if the tables moved into disk. Uh but yeah, someone thought that that was kind of confusing and raised a bug about like hey why do we make this counter to move um when you know it's using MAPAP but uh I think I don't I'm not

sure it's not close yet and um yeah again is debatable I guess but yeah keep keep that in mind like if the M map thing is kicking in you're not going to see that counter moving. all right. So, h how do we know if my query is using a temporary table? That's a fair question, right? Um, as I said before, the optimizer deter determines that. Uh, so

you can just run explain. Uh, I love the JSON format. I'm a JSON format fan because it's much more comprehensive and detailed. Um, and in particular, I'm a fan of the my SQL output because it's much better than my read unfortunately. uh is better much more consistent and detailed. So you can see for different scenarios uh when you run explain you're going to see like okay duplicate

removal like uh distinct or group by it's going to say using temporary table true. So very very very verbose very very clear uh when materializing a subquery it's also you also have the same key same with sorts same with grouping operations. So it's pretty easy. You just get the explain and you you will see uh if a temporary table is in the optimizer plan of not or

not. Of course that's going to tell you if a temporary table is being used. It's not going to tell you if it's going to go to disk or not, right? Uh because that depends on how big that that is and what what what your settings are. So it's just going to say okay a temporary table will be created in memory at first. uh and uh also it's

not taking any limitations into consideration like blobs or or anything. Uh this is the Mariab counterpart as you can see it's not super verbose. You have this file sort section and if a temporary table is used you're going to find this temporary table section there kind of confusing. You don't know if if you have multiple operations uh like group buys, order by distinct on the same query,

you don't know exactly which one is triggering this temporary table. So it's not not cool, but at least you know uh okay, temporary tables being used. Um um so I I I you know I was um another cool thing that you can do with my SQL and and is to actually determine if the table was moved into disk, right? Um, my SQL has this cool optimizer trace

feature that's like an explain on asteroids that will give you um it will not only show you the execution plan but it will show you all the steps that were ex that were done to process it query to optimize it to simplify it um what decisions were made by the optimizer discarded plans. So it will show you a lot of interesting stuff uh if you're like really

really deep diving into some query trying to understand what's going on and included it's going to show you hey the temporary table was moved to disk. So you would do set the optimizer to enable you set the you know maximum uh you run the query and then you um you check the information scheme optimizer trace and on the entire output you're going to see something like this.

Of course, it again is um you need to actually run the query, right? Because otherwise the temporary table will not be created. But I know it's kind of small, but it says it's going to say something like converting TMP table on disk. It's going to say the cost memory table size exceeded. It's going to show you the location, disk, enov in this case. Um, so it's again

if you're really really trying to understand what's going on or if uh with your current configuration the table is going to disk or not then uh optimizer trace is a good tool for for to confirm that only available in my SQL unfortunately. And um another question is can we so this is cool right? So of course memory is faster than disks. Can I is there a way

for me to estimate this? Right? And actually this question was the one that uh triggered uh a little bit of investigation on my side and and ended up becoming this session. Right? Like I was trying to provide uh our customers like uh some minimum TMP table size they need to set for their query to go to disk sorry to go to to to stay in memory. Um

and um again for some specific queries is good to know. Uh and and so my idea was to okay for a given query can I tell you what minimum TMP table size you need to set. Um so for memory it was straightforward right as I said fixed length storage. So you know the columns that are part of your of your projection of your query you know the

data types you roughly know the amount of uh rows. So you can get a pretty good estimation of how much memory will be used. um you can set that on tmp table size and you you your your table is going to stay in memory right so it's kind of straightforward um with temp table I had a lot of back and forth um was trying to understand why

numbers were being different um so first thing I found out is that if you go to I was I was experimenting with very low tmp table sizes right like kind of 16k I was trying to force that table to go to disk right I wanted I wanted to see how how it behaved Um and um you know I I was putting like very small values and I

was not seeing any temp table activity right I was like what's going on right um I ended up finding out finding out that there's a minimum allocation size for a temp table uh engine right and when you go below that the server says I'm going to I'm not going to allocate any temporary table on temp table engine I'm just going to use a sort buffer it's it's

like okay so it will just go with the sort buffer uh it will is something more similar to what is done for sorting and none of the none of the stats are going to apply. None of the mechanisms are going to kick in. It's just going to use the sort buffer. So that was that was confusing. Um and of course then the tempt table engine has these

three layers. So you need to keep that into consideration. What's the TMP table size but also what's the um temp table max RAM if MAP is going to kick in or not. Um so it's harder to track uh where the table actually is, right? and uh yeah and the other thing is like the actual estimation is not that easy because uh it's allocating the memory it needs

right temp table is allocating the memory it needs and not it's not using the data types as uh uh for yeah it's not it's not allocating the full space it's just it so it depends on it ends up it ultimately depends on your data size. So, but yeah. how are we doing? Okay, cool. Um, so lesson number one, right? Like if you can avoid temporary tables, of

course, we should do that. Um, again, these are adding additional computing, additional uh work that your server needs to do in order to get your query. Um, so union versus union all that's a that's an easy one. You probably heard this one a lot of times. Uh the union all is preventing that uh um dduplication phase that usually requires temporary tables. Uh so again if you don't

need that if you know your all both queries are unique they are returning unique values then you can you know get rid of that. Um of course every time an index is used for a group by order by uh it's very likely that the temporary tables will not be created. So um that's another good way to to to avoid them. uh I created this some sometimes people

they they don't know that that you know the index can be used for group buys and order by as well and and they you know and they can be used for both right like for the actual uh filtering like the actual to process the actual wear clause and the group buy or the order by so I put a few examples there right for for an index that's

abc uh if you have a where a value and group by b the index will be used you have where a equals value group by b and C the index will be used. Of course the order of that group by BC it's important in that case if you do group by CB then that that index won't be used for the group by um another another example you

know value um A equal value B equal value and group by C again the full full index will be used and uh another another example where you have a range uh condition where C uh you know greater than value group by C it would still use they fully index both for the wear clause and for the group by. So you get the idea, right? So you need

to keep the order. You need to make sure that any range condition uh is using the last column of your index and um the group by is done by that by by that range condition. But if you if you if you stick to these rules, you can avoid temporary tables for most group by and order buys. Um, of course, if uh you can avoid the the temporary

tables, let's try to keep them in memory. That's kind of obvious. Um, how we can do that? Of course, uh, TMP table size um, which we can try to estimate it. We can try to we can experiment with some values. I show you couple tools at least from the MySQL side where you can um, where you can estimate or you can actually check if a temporary table

is being spilled to disk or not. Um, keep in mind that TMP table size is a session level variable. So if you have a long like a large query that's using that you know it's going to use a lot of memory, you can bump up that level that that parameter just for that uh just for that query that that's that's um completely fine. Um and of course

uh well max heap table size if you're using the memory engine or you're using MARB um temp table max RAM of course that's keep in mind that's the overall memory limit that um uh you know all sessions that any like like the full space that uh all threads are using is going to be limited by that max RAM value. Uh well of course if you're using M

DB try to avoid blog and text right sometimes you do select star or sometimes you include some columns or you kept them and you don't really need them just get rid of them right and um same with large columns uh another good reason to be conservative uh with the data with the with the column sizes try to keep you know things below 512 uh if you know

you can um and again that will prevent and things going to disk directly. yeah, uh that's all I have for today. So it's now time if you have any questions for me uh I can I can answer. Of course, I'll happily do it. >> Does anyone have any questions? You can raise your hand and I'll we'll come to this microphone there if you want. >> Uh yeah.

Uh so it looks like you f focus mostly on uh my SQL uh tables. Uh we use Maria DB and so we have an issue here where uh you know things are being slowed down because we're creating a lot of temporary tables. Uh so I want to get so would you suggest migrating from Maria DB to my my SQL or do you think Maria DB because it

look like it looks like kind of you focus more on my SQL. So is it something where like Arya can work my Maria DB doesn't have the uh temporary table storage engine for example is it worth it for something like we have or is it more uh something more where you think we should kind of analyze the uh the queries to see what's creating the temporary tables

and adjust those uh you know the mechs and things like that. >> Yeah, it's a good question. Um definitely the the work you know my SQL put a lot more of developer work into the temporary tables management. Um MARB is still handling things as like 20 30 years ago. Um and yeah, I have to say it is more efficient, right? Um if you have a severe situation

where like okay you you're dealing with huge queries you know huge group buys or huge subqueries and you really need to keep that memory usage under control you have a lot of concurrency that might be a good might be a good you know uh a good uh strategy I know moving from one to the other is not easy they're not fully compatible anymore uh but yes temporary

table is more is is better handled uh by my SQL, right? And you have more you have more telemetry and you have more tools to troubleshoot that and to see what's going on, right? As the optimizer trace, the planes are more clear. Tempt table is more efficient. Uh you have additional layers. Yeah, it's definitely better. Management is definitely better. >> I think you had a question. sorry,

who has a question? So a lot of this talk was about performance and about um storage limits. Uh do you have any notes about retention and about let's say running multiple similar queries um slight differences between them um consecutively and uh how that might change some of the considerations between u memory mapped and disc storage. Um so the question is how would how would this like how

the mechanics would work with different type of queries. >> Yeah. Yeah. >> Kind of. >> Yeah. Um feel free to like if you want to uh uh that's what I got from the question but uh if you if it's not what you said just feel free to you know re reask um so what what you're interested in to know like uh how is how is handled how

are different query type of queries handled or >> not so much different types of queries possible disadvantages to relying more heavily on um the uh my SQL strategy versus the Maria DB strategy where there's more disk usage with Maria and I was wondering if there there's any other cons with uh the uh the strategy where we use an MMA an a memory map phase instead of going

directly to disk. >> Okay. Yeah. Again I I I I kind of uh it's kind of related to the question the previous question. Um it it is so my SQL has a better better management of that temporary space right um because the temp table is more efficient and uh even like adding that intermediate uh layer where it's using MAP that's usually up to some point is better

than going to disk directly. So it's it's it was kind of a smart move. uh they said okay if let's say you just need a couple more megs right let's just use this memory this mm mapap uh you know this um yeah memory map files right this additional memory that's actually mapped to files on disk instead of so for the server it will continue writing on memory

as if it was memory but it's actually under the hood the kernel is doing this swapping right so there's going to be more activity but it's definitely cheaper than stopping things creating a a table on is moving all the rows there and then continue working on InnoDB. So again like is as usual it depends right like uh usually it's if better if you can stick to memory

and m map uh and without going to disk of course uh but it's I guess it depends a little bit on on your workload. For me, it's like that M map MAPAP approach is like a like a like a last resort, like a like a like a last attempt to try to keep things in memory before it goes to disk. So, it's it's a good idea, but

again, if you're if your parameters are too small, then yeah, the server is just going to go over them. It's going to use the RAM. It's going to use the M map and it's going to go to disk directly. So, it ultimately depends on your configuration and the query. So it's a work you need to do with your with your workload, right? But again, you have definitely

more options uh with uh my SQL than with MARB, right? Uh MB is kind of still kind of legacy the way it's working with is dealing with with this um memory. Yeah. And temporary tables. >> Does anyone else have a question? Is there a way to optimize like PHP when you're actually writing the code to to avoid this entirely you know so you don't have an issue

with the temporary tables? So for for PHP language specifically you know last time I touched PHP was a long time ago. Uh I guess um yeah a lot of OMS like if you're using some sort of OM uh they don't build the best queries. I don't think there's anything specific to PHP. What you can try like if you know you have some hot queries that are taking

long or you know they are doing some group buys uh order buys or they using distincts or subqueries uh you could try to override what the OM is doing and try to write your own query. Um and of course uh you can try to create the index using this this uh this um approach I show you like try to use an index where you can map not

only the the the columns on the work clause but also the ones on the group by and order by again that that would apply to I guess any language is it's not as specific to PHP to be honest but yeah there's this is uh it's very common that the ORM sometimes is not giving you enough enough flexibility so it's better just to override the query the OM

is building and put your own query, right? Most frameworks allow that. >> Okay. Yeah. Um, is there any way we can get a copy of this? >> Yeah. Yeah, I think they're going to be available, I think. Uh, so I'm happy to send you a copy. Sure. >> Anyone else? >> All right. Thank you guys. >> Give him a round of applause. Thank you. Oh, yeah. Thanks.

Yeah, I think they're going to be available. So, but yeah, I can I can send you a copy. Should I get started? Yeah, we can get started. The most popular spots right after lunch. that's fine. I I see. Crowles. Yeah, sure. Okay, fine. Hello. How are you guys? So, today we have Matias Crowwell. Uh he's coming from Planet Scale to talk to us about Are You Ready

to Leave My SQL 8.0 behind? And let's give a round of applause to Matias. Thank you. The spot right after lunch, the most popular one everybody's still digesting. So this morning there was a question um is MySQL dead and that's not really what I meant with this title. Um it is a talk about upgrading uh from the current uh ADO version that is going end of life

uh in April this year uh to the next uh long-term releases that they are uh announcing. So who am I? Uh my name is Matias Kels. Uh I live in Belgium in Gent. Um and I've been a Linux user and administrator for over 30 years. uh started in my teens running Linux on on all kinds of machines. Um then I was uh professionally a PHP developer in

the typical LAMP stack that you that most people know about the early 2000s and no one in our team had any clue how the M in the LAMP stack worked. So I started learning about MySQL and I became a MySQL DBA. Uh previously I was a consultant um for a Canadian company and currently it's my fourth year at Planet Scale. Uh I'm in the enterprise uh customer

engineering team um where we support our enterprise type customers um in their uh road into planet scale uh and in their success with fest. Um this talk however is not a planet scale or a fit talk. It's a regular MySQL talk. So let's get MySQL has been the popular the most popular open source database still is. Um if you look at DB engines ranking um it's still

the the most popular open source one. Uh it's been declining however it became 30 years last year and it was the default database of choice for a long time for many developers. Um and in the last decade that has shifted to more toward more more towards Postgress. I I can't really explain why. Um I I don't have anything against Postgress. Uh but I still like MySQL a

lot. So we can see how the what the future will bring. Um and MySQL releases before they were always like every two three years there was a new major release. um going from 51 to 55, 56, 57. And then um in 2018 they uh decided to start releasing um a new version my school 8.0. It was originally planned to be 5.8 and then they decided to reumber

to 8.0. Um they didn't go to six because my school 6 was a version that they worked on for a long time when my school was part of Sun. Uh that never really was released. My school 7 is the um what's it called again? The enterprise one with the NDB cluster. That's the one my 7. So then they decided let's let's go to MySQL 8 and they

would release a new point release every quarter until the database would hit end of life. Uh so you can see there um from 55 to uh 57. Every every two three years there was uh one version going end of life. And so three years after the last end of life for um 57 um 8.0 know is going end of life this year in April. Um and typically

MySQL has always had two supported major versions. Uh and so when 57 went end of line at end of life there was only MySQL 8 left supported. So then the MySQL release team decided they needed um a new release plan and they came up with something called innovation releases and long-term support releases. And every time a new version goes end of life, they uh made a contribution

to also create a new uh LTS release. So the current LTS releases are still 80. Uh current version 8044 which goes end of life uh now in April. And then you the other uh long-term support release that's currently available is 84 um which is current version 846. And the next long-term release that they're planning is uh 9.7 in uh in April. So this is their release schedule.

So you can see that ADO has been the legacy uh version that they still uh do bug fixes on until um now. So the the last version of that is going to be released now. Um and then they created all the innovation releases 81, 82, 83. And those are meant to be like single um single releases. There's no uh no follow-up releases for any of those. And

then they make a new LTS version which then has support releases for a longer time. Um as you can see in the numbering like after 9.7 uh which is up up here there you can see they're go planning to go to version 10 uh and that's going to be a problem for some things because um there's a there's a fork of MySQL ever since uh MySQL came

to be in the uh uh possession of Oracle um there were many people that would that thought that Oracle would kill MySQL. And so the original author of MySQL created a uh a fork called Mariab and they started reumbering their versions after 55 which is the version they forked off to 10.0 and above. Uh their current LTS version is 11.8. and so a lot of the tools

out there if they do a version check and they see version uh 10 they will assume that it's going to be MARB and it will they will behave different. So that's uh one thing to very to be careful of. Um so now there will be a MySQL version 10 as well and that's not going to be MARB. Um the innovation releases they were uh they're not meant

for production workloads and they're basically the way to introduce new features into the the server. Uh for uh for a while in the ADO releases every major version uh every minor version that they released so every point release could contain backwards breaking changes which was not very very convenient. At some point they released a version of ADO that changed the red lock format and that broke everyone's

backups which was kind of dangerous. Um so that's what they wanted to fix with those innovation releases. So they are not meant for production workload but they're a good way to test new features up front and there's only going to be one release. So there's there was an 8.0 800 810 820 but there's never going to be an 811 or an 821. um they're just released every

three And then the LTS series, they are um scheduled to be approximately every 3 years uh to have a new um LTS release. So the current legacy one is the 88 80 which will have its latest release in April. Then the current version for 84 is 846 which was released in January and the expected release of 970 in in April when um uh when 80 goes end

of life. So um MySQL um removed many of the old 57 syntax that was deprecated in ADO already like they made a point to get rid of the master slave nomenclature and they went to replica and replication source uh instead. And so while uh in in many of the tools that you that you were using before you would run something like show slave status that would still

work in ADO but it's now gone in 84. So if if you haven't updated your tooling yet it's the time now really to do so uh to change that nomenclature. Um they changed the the the master designation uh to replication source. So if you want to set up a replica, you have to no longer change master to but you have to do change replication source to and

the command like show master status has been replaced by something called show binary lock status which more u covers the the load of the what the command will do. My 84 does in place upgrades. So if you start um a MySQL 84 binary on a data directory that was uh running on MySQL 800, it will automatically upgrade your data directory to uh 8 8.4. Um and there's

no roll back from that. So it it's a single way transaction. Um so if you want to try to upgrade, do not run this the first time in production. Like test it first before you you do something like that because you cannot just roll back. you would have to restore a backup which might uh let you lose some data if if you don't have regular backups. So

the less latest point in time uh might be a bit behind. Uh they also removed the MySQL native password authentication plug-in. So if you don't do anything and you just um upgrade your MySQL 80 to 84 and you're still using MySQL native password for your users and your root user uh you will not be able to login anymore like the the plugin has now been disabled. It's

not fully removed yet. So you can still enable it in A4 uh but they will fully remove it in 97. Um so it it's important that you know to first upgrade your um your passwords to the caching shot uh uh plugin which is the new way of uh encrypting passwords which is uh not reversible anymore. Um so um that's very important to know. Um also um in

a previous life I was uh running uh and building systems using puppet and the puppet MySQL labs um puppet labs myql module still doesn't have proper support for caching shot two passwords. So if you're running uh managing your MySQL servers using puppet um you will run into issues there. uh things that they all the things that they that they removed in 84 is the auto increment. Uh

it does no longer work for float and double types. Um which made sense like auto increment has always been an integer. So if you choose um a float or a double type um it's not not support anymore to to put an auto increment. You'll get a syntax error. uh they removed the expire logs days which is the uh expiration for your bin logs and they made it

bin log expire log in seconds. So now you have much more granular control of how long you want to keep your binary logs uh because before it was in just in days. So you had to specify how many days you want to keep them. Now you can specify in seconds. So if you set it to one second your binary logs will basically disappear immediately. Um so make

it the same value. Um we typically say like 3 days or seven days is a is a good value for your binary log retention. Um, so that's that's there. And also the old SSL parameters uh are removed. They're new um SSL parameters. But this is also something I ran into using Puppet. Um if you don't do anything to your puppet recipes um and uh you deploy it

with MySQL puppet lab mySQL uh module, it will add an SSL uh keyword in your um in your config file um and then your server refuses to start up uh on the new version. So there's a setting skip SSL that you need to set in the puppet which is then counterintuitive because you actually are not skipping SSL because you're using the new version then the upcoming 9.7

release uh which will be released in um April uh Oracle announced the new version um on the Belgian MySQL days earlier this year in uh in Brussels and the things they said to be in uh the new release is they're going to decouple the foreign keys from the storage engine uh which will make it uh which will make foreign keys supported throughout the server now while before

it was only supported in the specific storage engine. um they added that in the innovation release 96 which was released in January and it's going to make it it should make it into the upcoming LTS release um uh in April. They added a new native vector data type um in MySQL 9.0 earlier and they're going to also include that in the upcoming LTS release. They're adding support

for JavaScript stored procedures. Uh they're going to enable the new hyperraph optimizer by default which they use for their um um heatwave um deployments in in OCI and they said they're going to be performance improvement. So we'll see. But most uh there was a commitment from the from Oracle from the MySQL team at Oracle to work closer with the community again because there was um some dissatisfaction

from the community about the fact that Oracle is not very open in um features they accept in as contributions. Uh it's not very clear how you have to contribute. Like if you open a poll request in GitHub, you basically do not get any answer for many months or or even many years, which is not good for uh for community engagement. And so they they promised uh they're

they're making some changes inside of the Oracle uh corporation uh where my is going to be positioned in a different place and they going to have a new uh vice president uh leading that effort. and their commitment is to work much closer with the community. So we hope that that yields any improvement So how do you upgrade from uh a previous major version to a next one?

And that is typically done uh by replication. Replication supports the upgrade path for the major version. So they support um replicating an 84 instance from an 80 instance and they will support replicating a 97 from an 84 instance. Um so typically what you would do is you would restore a backup onto a new instance and upgrade the instance in place to the the new major release and

then you hook it up in replication. So what you then do is first you have to verify if your replication keeps up like if you're running your workload and u the new version doesn't keep up in replication that points to something wrong. Um so that's the first step you and then later on um if if your application is fine you can start sending a portion of your

reads to the new version to see how it behaves to your queries. Um, and then, uh, what I always recommend if there's a new major version is first make sure that your backups are working in the new version uh, before you upgrade everything to that new version. Uh, and if you verify backups, also verify restores because if you don't test the backup, I kind of assume that

you don't have a backup. And then finally, at the end, you can upgrade the primary to the new version and your entire um, system will be will be upgraded. MySQL 80 introduced group application and for group application it's a bit more difficult to upgrade because you have to keep in mind that um the version in the the group has to be um the same. So what you

can do is the first operate the first way to upgrade is an uh inroup upgrade upgrade. So basically you uh take one of the vers one of the servers out of your group. You upgrade the server uh to the new version and it can join the group again as a secondary. Um and then you keep doing that with all your secondaries until all the secondaries have been

upgraded. And then finally uh you remove the old primary of the group. Um and you upgrade that upgrades the group to a new uh to have a new primary. and then the old primary you can then also upgrade and rejoin to the group. Important to notice that um if you have a group of three uh instances in your group replication uh you will lose the ability um

to uh to send failures like if you only have three instances that's the minimum for a a group replication to be able to lose a node. um if you take a node out of that group um you will lose that ability. So the the high availability of your group is going to be compromised while you're upgrading. Um so typically what you would do uh in in a

if you want the inroup upgra upgrade process is you would have more than three instances. The ideal number there would be seven. So you can upgrade three instances completely. So you still have the um uh the quorum there. Another way uh to upgrade the group replication would be the rolling migration upgrade. So basically you remove the group members one by one and you create a new group

with the upgraded members. Um and then you set up asynchronous replication between the old group and the new. And then once you have the majority of your group update upgraded, you start pointing your uh traffic to the new group and then you can uh upgrade to the remaining members. Also, in this case, um if you only have three instances to upgrade, it's always going to be a

bit more dangerous um because there's uh there there's no more quorum and no more ability to lose anything uh out of And then um finally is the rolling duplication upgrades methods. And basically then you create a new group with the new version. uh you set up the asynchronous application between the old group and the new and um uh you start building up the new group and once

you have the new group up set up to the number of nodes that you want you um can flip it over to the new group and then you can decommission the old And that was kind of it. So questions. >> Are there any questions? >> Hey, so quick question. I saw there was some deprecation around the SSL parameters. Mhm. >> Is that just for naming reasons or

they're just changing the way you set up >> They changed the name of the parameters. >> it's just a name naming change. There's nothing really. >> It's the old is the old- SSL parameter that they moved that they removed and it's now the SSL mode which you then say um um that you want to verify the CA or that you just want to um verify the server

identity and those things. >> Okay, cool. Do you have any idea what percentage of running? >> Do they know how many or what percentage of servers are running 8 versus 84 versus 9? >> Uh, no. There's no statistics about that. So, Perona does some telemetry about that in their version. Um, so there you could maybe get some some numbers for for that, but currently there's no uh

um no no public data on how much uh people still are there. I know for a fact that there's still a bunch of people running >> One of our servers just got rid of it last fall. And is there any other questions? Any more questions? If not, let's go ahead and give Matias a round of applause. And thank you so much for joining Hello. Hello. Is it

coming through? Testing one, two, three. >> Okay, so today we have Scott Straws and he will be going over Rest assured serving up MySQL REST service with nodes. Let's give it up for Scott. >> You both of you. Thank you for being here today. I'm going to be talking about a relatively new feature that's part of MySQL and it's something that I think is actually really cool.

It's something that I have played around with a lot and I can't wait for it actually to be baked into the full product. But before we talk about anything else, this is the obligatory I love me slide. Uh I am a full stack developer. I'd been a full stack developer longer than the term full stack developers actually been in existence. Back then we used to call ourselves

developers before there was all the speciation and now everybody came back into again and now we're full stack developers. It was basically a way for people to say we know you're doing the same job but we want you to feel better about it. Uh in the entire time I've been a developer the only thing that's been a constant in my stack has been MySQL. I have been

using MySQL since my first job as a web developer. And every day since then, I have had a MySQL instance in either production or for or sorry for professional or personal use. I wouldn't call myself a power user, but I have been using it for a long time. I like to give things away. Kind of like a bribe for people to come talk. So, and the fact

that there's not many people here, you guys are in for a treat. So during the presentation, I'm sorry, at the end of my presentation, I'm going to ask a question about something you saw or something I said. The first person to get the question right by raising your hand, don't yell out the answer, um, is going to win a dolphin. This one, okay? So, you got to

pay attention. And then lastly, this cute guy here in the screen is my dog Murphy. Murphy is my best friend. He's the best office mate in the world. He spends pretty much all day every day in my office. Most of that time at my feet. And what makes him the best office mate in the world is when I'm trying to work through a problem, when I'm have

coding a problem or trying to figure out a database problem, I talk to Murphy about it and he looks at me with those eyes and I get the solution. It's almost like he's willing it to me. So he's like he knows more about databases and code than any other dog in But this is Murphy four years ago. This is Murphy as the 112 pound beast that he

is now. He is a huge dog. He's an affectionate dog and he's just awesome. So this is our safe harbor statement basically saying that anything I talk about today could possibly change because it's not a fully baked in thing So what is the MySQL REST service? The marketing speak is a next generation JSON document store solution that enables fast and secure HTTPS REST access to MySQL data.

That sounds awesome. But what does it mean? What it means is we're going to we're going to be able to and you can right now and I'll explain how you can in in a bit um be able to build restful services connected directly to your MySQL database. Okay, it's designed to have HTTP and JSON um application access. So you could theoretically have an app that only serves

HTML from the MySQL server, SQL, MySQL server, and you don't need middleware. It's possible. I don't know how practical that is, but it is possible to do that. Whoops. and it allows you to do that without SQL. And I'm not talking about NoSQL where it's kind of SQL but not really. Like you literally can just hit an API endpoint and get data back or hit an API

endpoint to insert data into a database and you never actually have to run a SQL command to make any of that happen. Uh and we can with through the um REST service we can expose tables, views, stored procedures and stored functions. MRS follows a similar design principle to Oracle REST database services or ords for the cool kids which I don't don't think anybody's cool saying ords but

um that which has been around for a while and we're kind of building on top we're kind of we're not building on top of it we're following the same type of paradigm to allow similar functionality for MySQL that is allowed through um ODS uh initially MRS which is that's what I'm going to call it from now on because it's easier to say um my my SQL mysql

rest service is MRS was originally available as middleware that was on MySQL router. So in order to use this, you had to have an instance of MySQL router running and you would make the request to MySQL router and then MySQL router would then pass that those requests along to your MySQL server and get your data. But now you can actually run MRS directly in a um MySQL

database instance without needing MySQL router. right now that's only available as a lab the um URL is labs.mmysql.com and when you go there you'll log on and you'll see you're right now it's MRS16 so that's the one that you want to grab but I do want to point out two very important things on this screen um the first one says this is not fit for production and

in case that didn't make sense to you it says please do not use these binaries in production I like how we use please there like please don't do it rather than don't do it we're like please please don't um keep in mind that this is something that's still being worked on and it's not actually ready for it's not ready for general availability yet I don't know how

long it's going to take before it's GA but it is something that um we are working towards and there have been improvements made in this over time when you want to configure MRS there's two ways you can do it you can do it through the MySQL shell extension ion for Visual Studio Code or you can do it through running SQL commands. You actually can run a SQL

command that sets up not only the REST service but creates endpoints and dictates what those endpoints can actually do and what data they're going to return. Um, for this demo, we're only going to show how to do it in shell because I'm a poor typist and there's no way I'm going to type any of these demos with people watching me. Uh I will show you a way

that you can see what the SQL looks like when you're done for one of the examples. So the first thing we need to do is we need to load the MRS component. The MRS component comes installed with the lab version, but it's not loaded. You just run this command install component file component_mysql_rest_ervice and it will load the component and be ready for you to use it. Now

again, this is going to be something you're going to be running locally, hopefully, not on a server somewhere that runs your production system. Before we can do anything in terms of setting up REST services, we need to enable it. And that's very easy to do in VSC because when you load up the uh MySQL shell extension in VSC and you start it up and you make a

connection to a database, you'll actually see an option underneath your connection. You'll see here it says MySQL RS service underneath local host. You rightclick that and then you click configure MySQL RS service. Pretty easy. And then when you click that, you're going to get a window that pops up that's going to ask you for information. So, the first thing you want to do is you want to

enable it because if you don't enable it, you can't use it and then the whole exercise becomes irrelevant. Um, it asks for the version, which I think right now 4.1.5 is the only option there. And then you can pretty much for testing purposes leave the defaults the way they are. Click okay. And then you have the rest service enabled for your local instance. So now that we

have we now that we have it enabled so we can actually start using it. The first thing we need to do is we actually need to add a service to our instance. And we do that again by right-clicking the now we rightclick the MySQL REST service. So this isn't on the local host that we did before. This is on the service that we just enabled. And you

do add rest service. And when you click that, you'll get a window that pops up that asks you for some information. The first bit of information is going to be what is the service path going to be. This is actually going to be starting at the root of your URL that you're going to use to hit the API. So after you have, you know, local host port

blah blah blah slash, this is what's going to show up next. Okay, you can actually you could actually leave that blank and just leave it slash, but for this example, I name it my service or slashmy service. Um, I give it a name. This is basically the way that it's it's um recognized throughout the system, not necessarily by the path itself. Uh, the rest service flags, you

want to enable it again because if you don't enable it, not going to work. Um, I'm setting it here as the default. So, there's going to be no other REST service here. Whenever we start dealing in this particular instance of rest service, it's going to use this by default. And then I mark it as published. If it's enabled and not published, not going to work. You're not

going to be able to access the endpoints. Oops. And then down here for the l the linked REST authentication apps, there's two ways you can do authentication right now. One of them is through NR MRS. You actually create an you actually create a user through this interface that will allow you to access the information or you can say I'm going to use a MySQL user that already

exists. For this demo I'm using the second option there. So I I actually have a user in my database that has access to this particular um database. Uh actually it has access to everything because it's basically my root user which you don't want to do. I just did it for the demo. Uh, it is important to note that the author authentication can handle OOTH. I actually haven't

played with it yet because I haven't gotten to that point to try it. I've just been playing around with some of the stuff that I'm going to show you today, which again I think is So now that we have our we have the service enabled, we added a service called my service. The next thing we need to do is we need to add a schema. So remember

in MySQL parlance, a schema is just the same thing as a database. So here what we're going to do is we are going to rightclick on any of the databases we have on our server. Here I'm using the Squila database which is a database that is publicly available that people can use for MySQL. Um it's a fake database of movies and actors and I I even think

there's something about talking about rentals. So that gives you an idea of how long Squil has been around. So it's been like you know probably like based off of like a Blockbuster type thing or whatever. Um, Blockbuster is what we had before Netflix, just for the new kids in the room. So, you rightclick on the the schema that you want to use and then you do add

schema to rest service and then you specify the service path that you want. So here we're saying we want to add the Squila schema to the my service rest service that we created. Then we give it the schema path. Now this is basically going to mean this means that we want when we want to access this schema through MRS, we're going to be using slashmy service. And

that'll be the path to let us know that this is the schema we're working For the um rest schema flags, there is enabled, disabled, and private. The only way this is actually going to work is if you do enabled. uh disabled, it's as if you didn't enable it at all. Private is it's handled internally, I believe. Um and you'll see then you get to you you can

change the schema database name, but I don't know why you would right click on the schema and then change it because that could lead to confusion. And then you click okay. So far so good. Very very easy. Now the last step we need to do is we actually need to add a table to the schema that we added to the service. And we do that by opening

up the schema that we want, right clicking on the table that we want to add and then click add database object to rest service. Again, very easy, very it's very intuitive. It's very well thought out. If you understand the steps that need to be done, it's very easy to figure out and remember what it is that you need to do. Now, here we get a little bit

more of a complex form. There's a little bit more data here, a little bit more stuff that's going on here because this is like the meat of what we're doing. And what you can do is very, very granular. So starting off we're saying we want this in the my service service path as part of the secila schema path and then we're giving it the object path of

actor. So if you think of it in the way that the service is like the top tier and a service can have multiple schemas and a schema can have multiple tables. So you can actually have a single REST service that provides information for multiple database schemas and then multiple tables underneath each schema or views or however you want. And then you need to enable it. Now, if

you hit if you check off required, that means that you're actually going to have to use the the authentication um spec that you chose initially, whether it was MRS or was the MySQL user to authenticate before you can use any of the the features. If you check if you leave that unchecked, you can list only. So if you uncheck author required and you still allow insert and

update, you can't insert an update unless you have authenticated. So that the author required is only for anything you're going to list. And then here, this to me is the fun part because what you can do now is you're dict you're dictating which data is going to come back when you call this REST service and which data can be managed. So here we're saying that we want

the actor ID, first name, last name, last update, the film actor actor ID, which is a foreign key to another table. And then in that table, we we hit the film film ID, which is the foreign key to another table that actually lists has the film data for the actor or for the actors that we're going to be dealing with. And in this particular case, you can't

see it here, but I picked title, um, length, and I think date released. So when you set up your schema here, when you set up this particular object, you can dictate which information comes back and you can keep going down the tree of foreign key relationship as much as you want and that data will be returned. Now you got to be careful because if somebody has a

thousand films that they're tied to, that's going to, you know, that's going to cause problems down the road at scale. But for this demo, I'm like, let's just do it all. See, you know, we can we can show what happens. And again, this is fairly intuitive. I actually the first time I used this, I was able to pull in I I figured out what the stuff was

without looking into it. And I was able to be like, "Oh yeah, if I click this and I can get that table and if I click this, I can get that table. Let's see what happens. Holy crap, all the data came back and I didn't have to do anything." It's actually it's pretty cool. So then you can determine what you want to do, whether you want insert,

up, if you want to add, insert, update, or delete. Everything that you do so far, it's all based off of listing. So it's based off of selecting. I actually had to go in and enable insert, update, and delete for this particular endpoint to make sure. So by default, you're not allowed to you're not allowed to to to make any changes. You're only allowed to read. You actually

had to go in and check those. And you'll see they'll be they'll be dark, like they'll be black with the white letters. um when you before you select them. And then there's icons that are next to each column. And I'm not going to go into each one because they're all kind of they're they're kind of hard to see, but the one that looks like like a document

with a down arrow is saying that you can actually sort on this column. So you can actually make a call to a rest service that includes how you want to sort the data. And if it if you have a column that isn't part of that, it's not going to do any. You won't throw an error, but it won't do anything. So if you say if you pass

in I want to sort by the film ID and there's no way to sort on the film ID, you won't get an error. It just won't be sorted that way. Okay. Um there's other information where you can actually say can't delete, can't update, stuff like that for those particular columns. And then remember that's that's based on the column, not the not the table. So up here the

insert, update, delete is for the table. Here we're talking about the specific column. So you can actually have that type of granular control where you can dictate which columns can be sorted on and which columns can't. And then there's this nifty little button called SQL preview. Anybody want to take a guess what it does? Previews the SQL. So if you click that button, you'll actually see the

SQL query that would be that would be used database object in the REST service. This isn't so if you just ran this without doing anything else, it's going to throw an error because it's not going to know about the rest service being enabled or the um excuse me or the schema being added. So this was just to add those particular items that I showed you on that

particular to dealing with that particular table. And if you look at this thing, you can actually see where the relationships are and what's allowed. So you see it has at insert at upsate at at delete. That's showing that we can actually do those particular procedures against the uh this the table that we're using. So that went a lot faster than I anticipated. It's demo time and I'm

actually going to create a REST endpoint live because I can click I can click a mouse like it's nobody's business. So you'll see here we have this now this is actually the same instance that I grabbed those those images from. Okay. So you see we have the actor the users is there because I needed that to log into the application and I'm going to show you momentarily.

But I want to add, hold on, before I do that, I want to show you here is an endpoint. You can see up top I'm looking to just list staff, right? And I'm doing this to show there's no smok and mirrors. I'm doing this live. If I click send here, says not found. That's basically saying the endpoint that you're looking for doesn't exist. So, if I go

back over to here and I find the Techquila database, which is right here, and I right click on staff because that's the one I want to add, and I do add database object to Come on. All right, hold on. Sometimes this is a little you got to do it a couple times or if the window's open. There we go. So now we have the window where we

can say, you know, we have staff, first name, address. I know for a fact I don't want picture because that puts up a lot of data that makes it hard to read. We also don't want the password to show even though I think it's hashed. It shouldn't matter. And I'm going to get rid of last update. And the other thing I'm going to do for this particular

demo is I'm going to get rid of Oth required because I didn't want to have to sit there and try and log in through Insomnia to show you this demo. So remember, when O isn't required, you can you can always list you can't put or up you can't insert or update or delete. So then I click okay. And now if we scroll back up, we'll see that

staff is now one of the um tables that's part of the schema. And you'll also see that's got a slightly different icon. There's a small yellow icon that lets you know that it's being secured. So that's basically saying that for the staff you don't need the authentication. So now if I go back over here to the same endpoint and click send, now we get data back. Okay.

So you see it has the email. The cool thing I this this to me is really cool and I thought this was a very good idea. Whenever you list something, there will always be a property that that's returned called links. And those links in this particular case is telling you what link you can enter in to get the information for just this specific user. Okay. So if

I go to the endpoint my service staff one, I'm going to see the information about Mike Hillier. Okay. And I'll show I'll show you that in a second. So you see here we have items and we have the store ID and you know here's John Stevens and it shows he's active and his staff ID and the store ID. But if I go up here and just do

backslash one and send it now you'll see we just get a single object with just Mike Hillyard's and I didn't write a line of SQL to make that happen. Um it it is pretty fast. It happens pretty quickly. Again, this it's a small data set running on my laptop and I'm just you know making one request at a time. But the fact that it's it still comes

back pretty quickly. I haven't done any testing of this on scale or any type of load testing to know how it holds up. But considering the fact that everything takes place on the back end on the MySQL side, it's going to you know be as at least as performant as what the database is itself. Okay. And if you notice, that was what, three mouse clicks, four mouse

clicks maybe, and I was able to expose that table. Now, granted, yeah, there was a little bit of setup because I created the service, added this added the the schema. I didn't want to do all of that um live during a demo, but it just shows you how quickly you can get you can get up to speed with stuff. Now, what I like about this, and let

me get back to my slides Oh, no. Andrew, you get to see the slides you missed. You getting this? See, this is when I have all those neat little animations where it comes where it becomes an issue. Okay. Add a table. So, some of the advantages of using MySQL REST service is you can actually perform CRUD operations with no SQL. You don't need to run any SQL

queries to be able to insert, update, um, read or delete data or complex RM mappings because I I was talking to somebody once and they're like, well, you could do that with OM. Well, yeah, if you have a PhD in like hieroglyphics, because that's sometimes what it takes to configure RMS. When you OMS, Here's the thing. I don't want to badmouth OMS. I like OMS. I've used

them before. They are very good for what they're designed for, which was basic CRUD. When you start getting into complex relationships where there's a table that's related to another table with four tables in between, managing that becomes very very difficult with OM. um you know especially like hibernate can can get very hibernate's great I love hibernate but when you start getting complex relationships the configuration is difficult

and the queries that hibernate generates are not as performant as if you could if as if you wrote them manually okay just something to keep in mind there it eliminates the need for middleware okay as I said you can actually I didn't show it in the demo because I didn't think I was going to have time and now I wish I had done it you can actually

consider you can actually configure the rest service to return HTML. So you can actually you can actually have an HTML page that's in the um the database or as part of the rest service and then when you hit the rest service it loads the HTML or loads the JavaScript. So you could have like just a you could basically have a static page that pulls that information down,

displays the data, and then allows people to start iterating through and doing the stuff by hitting the APIs that we just showed. I don't know if that's necessarily a good idea. I don't know if that's how I would do it because if you start getting if you start getting into a lot of different users, one of the things that I've heard is people say, well, if for

each user you should have, they should have their own MRS user, which now you're you're talking some systems that they have millions of users. Now you have millions of users in your database determining who can access what. Now there is a way with a security you can determine roles. So you can actually create a user, assign it to a role where they can only read or they

can only insert, they can only update, you know, very few people can like delete. Okay, so there is a way to do role-based security with that. But still when you start talking that type that many users at scale, it becomes a little bit more difficult. So what is more likely the case is you actually have some type of middleware set up like node like I'm talking about

um where you have node connect to the database using the singular user that you created or users if you want to have different permissions based on what's going on and then use that particular session from the database for everybody else including logging the person into the application which I'm going to show you in a second. um there's no SDK or connector needed, which is good because if

you're working with a language where you need an SDK or or you need a um connector or an SDK, they're usually third party. Okay, we do have some connectors that we offer. The node connector we offer is not necessarily the best one that we have because the node connector is specifically for using document store or the um the XDEV protocol, which we're not using in this demo

because we you wouldn't use this demo. I typically I use the MySQL 2 uh connector for node applications and that's the one where you can actually just write raw SQL. All right. And you can use in this particular case native JavaScript features to call the API. So you can now insert data into your database just by calling just by using fetch or by using the XML HTTP

request which I don't think anybody uses anymore but it is something that you can think of. So imagine that like if you have a form where you collect the data as JSON you just call an API and pass it in. Now you just either updated or inserted a row into your database again without any SQL. The advantage of that to me is the fact that if you

have somebody who knows about the database and can set up the rest services in a way that makes it intuitive for developers, your developers don't need to know a whole lot about your schema. They just need to know what data is coming back and what data they need to send. They don't need to know what tables are going to. They don't need to know they really honestly

for the most part don't need to know data types, you know. They just need to know you need first name, last name, here it is, you know, and there's a lot of developers out there who know how to make API calls who could not find their way through a database with a flashlight. Okay, there's and there's nothing wrong with that because you know what? Some people are

like, databases, ew, I'm not one of those people. I love databases and I still love this feature. I still find this because I'm a developer and I'm like, "Holy crap, this is so much easier than, you know, trying to figure out the query to use and, you know, and what tables to use." Now, granted, in this particular example, there's only like six tables, but if you had

a system where there were dozens or hundreds of tables, having these API endpoints like this can really mitigate the time it takes developers to get ramped up and working on your system when they don't have to worry about writing queries. they can just say, "Hey, I need an endpoint that does this." And then the person who's in charge goes and makes that endpoint. So, here's some code

examples to show you the difference between the two pieces of functionality. And I'm going to admit I purposely made the SQL one more lines of code because I thought it would be a cool little demonstration. But the first thing you need to do in this particular case is you need to import the third party library that you use to connect to MySQL. And here I'm saying we're

using MySQL 2. Then we need to create the connection. And when we create the connection, we pass information such as the host, the password, the p uh the user, the database. You also in this particular case can start adding information about thread pooling, which by the way is something you don't need to be concerned about as much when you're using the MRS because you don't have to

worry about the connection between the middleware and the client staying connected. You're just constantly making calls. you make constantly making an HTT HTTP request. And in this particular case, we're setting variables for the actor and for the actor's first name and last name. And then we do up we we have a um we call connection.execute and pass in the query and parameterize the values. Something else you

don't need to be concerned about when you use MRS is you don't have to worry about parameterizing the values. Why? Because when you create the JSON string that gets sent to the server, it's already parameterized. So it kind of can help mitigate SQL injection. I'm not saying it's going to get rid of it completely, but you know, if the server knows that this particular value is a

string and to always treat it as a string and not as a command, you kind of limit the risk of being exposed through SQL uh SQL injection through that. And then on the flip side um you see we just do a fetch and we pass in the URL which in this particular case it's localhost 33060 to um my server/sakquila actor. You'll see the method is put. That

means we're going to be updating or inserting this information. Actually it's going to mean we're updating it because we're passing the ID. we do have to add headers to say that it's um the content type is application JSON and the authentication the authorization. So when you call the endpoint that logs you in you will get a token. This is the token you will use for all your

other requests during that particular session. And then we do body JSON.stringify first name last name. So this is actually going to say hey this user's name is John Doe. Now, I will admit to some people the first part is probably easier, but for a lot of people, the second one is probably easier because a lot of JavaScript developers, a lot of Java developers, a lot of PHP

developers and and Python developers, they know how to execute or they know how to call APIs. All they need to know is the path and what data is expected. That's all they need to know. And if you know that, then that's a lot easier than trudging through the database to find the table to know what pl you know where you're going to be inserting data or or

how you're going to write the query to insert data or if you're pulling information back. You know what tables are related to get this information because sometimes there are foreign key relationships that are related to tables that kind of sound like what you want, but they're not. You actually needed something else. But if you have somebody who knows a database who sets up the service, then here's

the thing. As a developer, that's not on you. That's on Oh, look at that. I actually did I didn't think I did that. My bad. it's demo time again. Now, I'm going to show you the web app that I did, which here we go. And let me make this full screen. So hopefully the session would have timed out. Oh, the session didn't time out. Oh well. Oh

wait, I can log out. There we go. So I'm going to log in here. And what I'm actually doing here is I'm not logging in using the credentials from the MySQL user I specified when I created the service. This is actually going to be a user and password that's in the users table that I was showing you. And there's actually an authentication process that happens on the

node side that is like hey go run this endpoint and let's hope it works. Yes it works. So now we have logged into the application. We have a list of actors. We can create a new actor. call him uh Skip Dinglehoffer. Little thing I like to throw out to people is if you're ever in a database and you see a username Skip or Skippy Dinglehoffer, I was

probably working in that database at one point in time because that's I have been using that as a test user since day one of my first job as a uh as a developer. So now if we go over here and we and we we search for dingle or I'm just going to do ding. Oops, I hit the wrong button. And then I hit search, you'll see that

we have skip dinglehofer and we have a skippy dingle. And then if I want to edit that to say, you know what, we want to do skippy dinglehofer and click save. And then we can go back and dingle search for Dingle again. And you'll see here that Skippy, it's now Skippy Okay. So, just for those of you who were paying attention, I logged in, listed the actors,

created a new actor, searched for that actor, updated the actor, searched for the actor again without one SQL command being run. All of that, everything you just saw was done using the MRS APIs. Now, I feel kind of guilty that I put, you know, with node in this title because there's very little node code to talk about in here because all we're doing is writing APIs and

I figured that's something you could learn how to do. You know, you don't need to. So, it was kind of like a hook like, hey, if you want to know how to do this in Node, here this is how you set everything up. Oh, and by the way, just use fetch, you know. So, you don't really need to be overly concerned um about how to actually access

the API as long as you know what the endpoint is and what the data is expected. If you're doing a write operation, you it's doesn't matter whether you're using, you know, an API from somewhere else or an API from MRS. It's the same procedure. You use fetch, you use the right, you use the right headers with passing in the right authorization token, you pass in the right

data, and you will get this, you will get the results that you expect. Um, again, I think it's very very cool. I don't know how much it'll be adopted with bigger companies that you know deal with like terabytes of throughput. I don't know how that would scale for that. Um but I think for most people for most companies, most applications, this probably is going to be a

good fit. It's probably going to be a good way for people to access things easier. And the the cool thing there is you can kind of treat your front end as if you're dealing with documents like as if you were sending information to or getting information from like a document store like because as far as the front end is concerned it doesn't matter what you know because

same thing when you're using your front end is going to send information in a certain way and then Mongo's going to handle it on the back end. Very very similar concepts you're just hitting a different endpoint. So, if you have an application that uses you're going to have a get, a put, um, possibly a patch, and a delete endpoint that you can use to manage those documents.

And it's going to be the same process here with the advantage of the fact that your data is now stored in in a um transactional relational database rather than in just a a blob, you know, NoSQL database. So you I know you guys have gotten the spiel but for you because I don't remember seeing you today whenever we come and talk at conferences my team is actually

allowed to offer forget the forget training exists in that slide because it changed and I didn't realize it. Uh we actually offer free certifications for MySQL. You can get up to two oper two chances to take an exam and it won't cost you a penny. If you're interested, you can either see me after, actually, probably be easier if you came to the booth because I can scan

you and then keep your information with everybody else's. Uh, something to keep in mind that I don't know if you guys got is once you get the notification that the credit has been applied, you have three week or three months to use it, that doesn't mean you have to take the exam in three months. It means you have to apply the credit. Think of it as a

coupon code. Your coupon code might expire on Sunday, but what you buy with the coupon code will last for much longer. So you can like I don't know how far in advance you can schedule the exams but you if you have the ability to schedule it more than three months in advance then you could do that and not have to worry about it. And with that little

short on time I should have put that other stuff in there. Does anybody have any questions? >> Yes. We were talking before >> and I was thinking that this is actually a great way to gate premium features in a plugin like if you have your own server with MRS on it >> and you have specific things that you want to keep like so you have a premium

model right where you have the free plugin and then you've got the pro >> you can have this where so like it's not denied by WordPress.org or you know since you're gating something and you have the SQL on your own server somewhere. >> And you can completely have an entire uh full feature rest endpoint. >> Yep. Absolutely. >> Seriously genius. It is it is a so so

I don't know if Andrew if you heard what he said but he works on plugins for WordPress and he said this is a great way that you can actually integrate other services outside of WordPress in a in a separate database by using this because WordPress isn't going to know you're using SQL. >> Yeah. Because WordPress will deny it immediately if you have like a paid feature, you

know, that's in built into the free plugin. But if you create, you know, an exterior, something else >> that it calls, it's not they're not going to care. >> I will say I will say that you there's a chance you could be violating the terms of service there. So, be careful with >> But I do think that's that is a nifty idea. I just don't know how

>> it's and you know, it it really is to me I I thought >> I like I like our document store. For those of you who don't know, we we actually have a document store that is similar to that stores the data in um JSON documents, but it's in a relational database table. So, you get all the benefits of of like InnoDB in terms of speed and

stuff like that um with a document store. But I think I like this better. Now, I don't think this is a replacement for a document store because again, you have to have the tables mapped on the back end >> to, you know, to where this data is supposed to go. It's not like it's not going to be schemalless. It's still there still is a schema, but like

I said, it it is kind of you can kind of treat it that way because you're only dealing in JSON. You're only getting JSON back and you're only sending JSON in. So, it doesn't matter what's on the back end. You know, you as for most developers, they're not really going to >> It's perfect. >> You know, it it really is. and and the fact that we the

fact that we've been able to take it from putting it into and actually making it a part of the product now so you don't need router because in some instances router might not be an option. It really is it's a nifty feature and I'm really excited to see where our engineering team takes it. >> Another question. >> Sure. >> Authentication. Are you stuck with you know like

regular SQL authentication or can you use something like JW uh JWT? >> It does support OOTH. >> Yeah. Again, I don't know how. I haven't played with that yet, but I do know we I do know it supports OOTH >> because that'd be that's even better for a plugin, you know. Exactly. You have a license and then it JWT authentication. >> Yeah, it is. It's it's really

cool. Like I I have a database that I use. I manage a golf league. >> And if anybody's ever been to a talk of mine, I talk about the golf league all the time because I think it's a great data set to use for examples and stuff. But um I've been using the golf league or I've been managing the golf league for over 15 years and I

wrote a web application because I got sick of using Excel and then I found a WordPress plugin that kind of worked but didn't really fit my needs. So I actually wrote an entire web application that manages the golf league and I have used that in my personal examples of for doing MRS and because there's some there's some complex relationships in there because you have a league each

league has a season. Each season can have one or more divisions. Each division can have one or more weeks which is you know that that's the schedule. Each season can also have one or more teams. Obviously, you need to have more than one team or else it seems kind of stupid, you know, and then there's a schedule so the teams play each other and trying to get

some of that stuff. Some of the some of the queries are pretty complex. Um, and I've tried setting re reproducing them and it actually does a really good job of pulling the information back that you want. And you can actually pass in um I didn't show this because it was kind of I didn't think it really fit because I didn't want to go that deep into what

this was kind of like a hey let's show let's see how interested you are then you can learn this but basically when you call when you call an endpoint like if you call actors and you want to filter it by the first name you actually URL encode a JSON string that says um it's like it's you do Q equals or question mark q equals And then you

pass in the JSON string, the encoded JSON string for um order by colon first name colon descending and like as if it was a J. So you you generate the JSON for that and then you encode that as a URL encoded string and pass that to the server and then it knows to sort by the first name or the last name and you can also use that

for pageionation. So you can actually pass so if you have a list of you know you have a hundred users and you only want to show 10 at a time you can actually pass in that JSON blob or that JSON string encoded JSON string to do limit and offset to be able to pagionate pageionate the data. >> I just see so many uses for word process. >>

Oh so do I. It's when I was putting this demo together, I was like, "Oh man, there's so many things I can think I can think of that are cool." >> Just because you have so many users in different scenarios and different builds and you can set one like one SQL database that everyone, you know, everyone connects to. >> And they don't have to have it locally.

That's amazing. >> Yeah. And and the fact that it's so easy to I mean, you saw how easy it was to create that endpoint. >> Yeah. You know, again, again, you could write the SQL for that, but I guarantee you it would have taken me a lot longer to type out the characters and look up all the different options that need to be done rather than doing

V. And I don't like VSC. I'm not a fan of VSC. I shouldn't say I don't like it. It's not my favorite. Okay, I'll use it if I have to um and I can muddle through, but I prefer IDE or um IDE. I prefer Intelligj Idea. But for this it I mean it's it's so easy. And there's another option. Let me see if I can I don't

know if I can show it. Let's see. I got time. So if I right click on the service, I thought there was a way you could get like there was a way that you can actually generate. Maybe it's maybe it's on the service. That's probably what it is. copy to clipboard create rest service including docket database object. So if I click that and then open up a

new file which I forget how to do here. Oops. I forget how to create a new file but I can't paste it in here I don't think. Or can I? I can. So now here this is the this is the SQL script that creates that enables the service adds the service adds the schema and adds the database objects to the So if I was to take this

to another MySQL instance that that had the MRS component loaded and ran this SQL script all that stuff that I just showed you would be available. So that's how you can actually get it to where you know okay it works on my laptop. How would I get that to work on a remote server? when you should put it in production. Don't do it now. And that's how.

And you can actually at that point you can actually put put the script. >> When can I do that? >> I don't know. And if I did know, I couldn't tell you. I don't know what I don't I I can't even give you like a rough estimate. Um but trust me, when this becomes like GA, I'll be blogging about it. >> I'm subscribing. Give give me your

blog. >> blogs.oracle.commysql. oracle.com/mysql. Oh, wait. I forgot. I got to give something away. So, if you remember in the beginning of my talk, I said I was going to ask you a question at the end of the talk about something I saw, about something you saw, or something I said. And the first person to get it right wins the little dolphin. So, the question I have to

ask is, how much does my dog weigh? >> Oh, so close. >> Nope. >> Go ahead. >> You know what? Dad gets it. He's closest. 112. >> If you guys don't have any other questions, thank you for coming. Sorry I was a little short on time, but I hope you guys got enough out of it to be, you know, intrigued. God, now I could take this off

cuz it's annoying. There we go. Oh, let's see. A little loud. Hopefully, it has enough battery here. I'll have to be careful not to use teacher voice or I'm going to wreck somebody's eardrums. >> My family pron my family pronounces the C like an S. So, W was kind of like klelaw. The Polish is Vatavski. I go by Phil. >> I'm not Yeah, I I try not

to be e too evil to my students. I mean, little evil, but not too evil. Okay. Yeah. Okay. I think that's >> Huh. Um, it cooled off the past couple days, but yeah, we've already had some days in the 90s. >> Oh, yeah. No, the summers in Arizona are a lot like winters in Michigan. You hide inside, you run out to the car and either turn the

heat on or the AC on, and then you drive to the nearest appropriately temperature building and run in. You know, this time of year it's not too bad, but once it hits June, July, August, most of September, a lot of October, um it's just Yeah, >> I believe so. >> Hello. So, today we have Phil Bushloski. Um and we he will be going over 25 years in

education promoting Linux open source and my SQL and he is currently doing computer information systems faculty at the community college in Messa Arizona. So let's go ahead and give him a round >> Thank you. Um, so David Stokes who's actually given a few talks for my classes convinced me to do this talk and I was trying to think of specifically what because I do use MySQL heavily

and open source. So I figured just kind of doing a a summary of what I've covered over the years. Now, I doesn't look too bad on my face, but yesterday I was at my dermatologist and she gets out the nasty little freeze ray gun and has blasted three small spots thankfully on my face before she asks, "Oh, hey, do you have anything coming up? typical disclaimer, I

am technically, of course, my this is part of my faculty professional growth, but I am not officially representing Mesa Community College or the Maricopa Community College District. This is just based on my years of experience and from my perspective. So, some of the goals of this talk, um, it's been a long, strange road trying to convince colleges and even high schools sometimes to use open source. Um,

I'll talk about some of the strange things we did as a college back when, you know, security wasn't necessarily a big deal. And there were definitely some challenges promoting open source. I got a lot of interesting comments from people. Um, you've probably all heard the, well, why don't you use a real fill in the blank instead of the open- source one? and I'll show you how I

use open source or some of the tools I use in my classes explain how my SQL fits into all of this and then maybe try to discuss the future. This is the part that's really in flux for community colleges right now and high schools. Things are changing so much. you have well, we'll get to it, but just the AI um revolution. Nobody necessarily knows what to do

with it yet. Now, I always like this picture. Uh I tend to inflict puns on my students because I figure I'm actually a closet comedian who wants a captive audience. Uh difference is you guys have chosen to be here. So, couple bad puns. My original background, I was going to be an entomologist, a tropical entomologist studying insects. Now, I do that type of stuff as a hobby,

though I will admit I'm looking more at ocean invertebrates now than that. But, um, I always told my students I've just gone from hunting one type of bug to another. And this one will take you a second. The people who can't distinguish between an entomologist and an etmologist bug me beyond words. There you go. So, um, my bachelor's of science was at Michigan State University in biology

and and tomology, though I did quite a bit of work with computers. my masters of science. Um, I ended up building some of the lab equipment, programming the data loggers, and of course did a fair amount of work with things like SAS and other things like that. Depending on how you define it, I've been teaching for over 30 years. I started out as an undergrad biology lab

assistant um just helping around and trying to keep students from killing themselves or killing me. My favorite had to be you would tell them not to do something. You turn around and somebody's got this steaming boiling beaker of benzene and they're putting it under your nose and I've got contacts and I'm worried I'm about to melt them to my face. Uh, and that type of thing is

a little bit safer with computers. The worst they could do is crash their system and they get to reinstall it. I was a graduate TA for a while. Did a variety of things in that. Um, and then I became an adjunct instructor in the community college system in uh, the Phoenix Valley doing mostly biology, ecology, anatomy, and physiology. And then I became a Linux and HTML uh,

adjunct instructor and then I finally became full-time in 2001. I got my 25 years of service this past uh past December and people are already starting rumors about me retiring. You got to love it. And I've also done a few multimedia madness summer camps where just using open source software things like uh and Creda and Blender and a whole bunch of other little tools and we make

some really cool presentations. And we've got this neat little planetarium that uses projectors and a computer at MCC. So they put it up on the big 30-foot dome at the end. Kind of miss those. And I always like to do the joke with my students. I started back in the 1900s. And um so yeah, if you notice it's like if hungry eat else type of thing. So,

my early history, um, back when I was at ASU, I got interested in this weird thing called the internet. It wasn't really graphical yet. Um, but they had a VMS. I think it was a VMS. Maybe it was a vax. It's been so long. Sector not found. Um, but I chat with folks and have fun. And eventually, of course, they found out that I was no longer

a student and they cut my account and there was a company in the valley called PrimeNet, but they really pushed you to use Linux to connect to it. I never heard of that. So, I downloaded the 32 individual floppies. Yeah. The three and a half inch ones. And installed them in. When Walnut Creek came out with the CD and CDs came out, it's like, "Oh, this is

so much better." Because downloading 32 floppies and labeling them and making sure none of them gets damaged. now Primeet would get they kind of got bought out by a more commercial interest and they were upset because I was running a server on it. So, they did this little thing. Well, you know, human beings need at least 6 hours of sleep a day, and you've been on 24

hours a day. So, I just set up a little cron job that hung up at 3:00 a.m. and dialed up at 9:00 a.m. and no problem. Um, so yeah, I ran a 28.8K Ba modem and played around Oh, sorry. um played around with a lot of different things and we did a summer camp as a group where we're going to do machinima and they decided for some

reason I was a gamer which at the time I was not. They bought me a copy of WoW and said learn it because there's some really good good tools for that. They however still refused to pay my monthly fees. But I kept a copy. I figured I was okay cuz, you know, I had done it on Windows and games don't run on Linux. And the very last

Linux World Expo, which was horrible. Uh, all the good comp talks were cancelled and the other ones were here's how you convince your boss security is important. Um, so I got so bored that I played with the copy on my little copy of Ubuntu. and oh crud. World of Warcraft ran on Linux in 2008 and I'm still playing it to this day. Um early on I would

just apply patches and you know compile kernels for fun and looking back at it didn't really do me any good in most cases but it was something that I could say I did and it took like a couple hours to compile them sometimes. Um, I use it heavily in my day-to-day. All my laptops are some flavor of that. I've got couple little mini PCs. I've got my

servers, which I'm switching over to Alma Linux after Redhead, uh, CentOS did their little thing. Um, I scuba dive, so I use Subsurface, which was written by Linus Torvalds, but he's no longer in charge of it, uh, to keep track of my dive logs off my dive computer. And scale 10 was my very first scale. I used to still have the hat for it. It's I got

lost someplace. So again, right I'm going to have to be very careful. This microphone is very sensitive. Right around um 1994, I started teaching as an adjunct at the community colleges in biology, anatomy, 94 is kind of an interesting year because yeah, the worldwide web had been out, but this was right about the time where some of the first graphical browsers start becoming around and CDs were

more common. So, you were starting to see more multimedia even creep into biology and anatomy and physiology slowly but surely. Um, and of course, because I was still playing around on this Linux thing, I needed to learn more about it. The very first time I opened up Vim, I had no idea what it was, and I'm hoping nobody else did this, but I wasted like 30 or

40 minutes trying to figure out how to delete all those little blue squiggles that were on the Oh well. So I took a Unix class with a gentleman by the name of Greg Greg Swan, great guy and I chatted with him a lot and so he invited me to teach the class and um I then later helped develop some of the very first web and internet classes

for that as an adjunct and even did a couple web development classes with Pearl. It has been long enough now that I remember a few things about Pearl, but the main thing I remember is I'm glad Python exists. I may be a little biased, but oh well. Um, I then of course as I started teaching, I gave my students accounts on my server so that they could

do stuff in the class rather than having to use the school one which was not always going to be as upto-date. Plus, I could let them host web pages. Now, of course, you can all think of some potentially interesting side effects to that. My favorite had to be one morning, I'm hearing a phone call. I'm going, "Oh, it's 9:00 a.m. I'm not up for another couple hours.

This is so and so from security services on behalf of Chase Manhattan Bank. One of the users on your server is running a fishing scam." I teleported out of bed, grabbed the phone, I said, "Hello, let me go check." I logged in and one of my students decided that it was too much trouble to create links and click on them and then fill out the forms. He

just created a little interface page. The only thing that saved me is that the domain name was so far from Chase Manhattan Bank, I got to keep all my And I had to explain this to this student. And he said, "Oh, I'll just do it home." It's like you don't understand this is throwing up huge alarms at this company. It's it's you know it's cross cross-sight request

forgeries and it's like oh my he eventually figured out it was a bad idea. Um then of course I want to teach a Unix server class and I kind of helped develop that. Again pretty primitive to what we do nowadays. problem is I had to have we couldn't share a system. If you have 30 beginners administering a Linux box, it's it's dead. I mean, heck, to be

honest, considering how opinionated most admins are, you put 30 different admins with the same level of access and they're going to keep undoing each other's changes till eventually it's it's dead. So, I had to convince Eden to let me install Linux on their precious uh Microsoft Windows machines. Went around and around. I said, "Okay, as long as it runs on Windows, no problem." Freaked when I said

it was instead of Windows. Now, to be fair, we had a couple of odd things that did make this a little risky. Um, our school at the time, which we were a fairly big school, but our entire internet was one single bridge. That means that every device had access to every device. So much so that when I did set up my home server and students wanted to

print, well, the the HP printers in the classroom used Jet Direct cards. So I just configured my remote server to print to the printer in the classroom because there was no security. Again, 90s, things have changed a lot since then. Um, but I still did occasionally have students who weren't very careful and we got yelled at. They wouldn't use the proper IP range I told them to,

and they would just literally go to some poor Windows machine, smack it upside the head, grab its IP address, and the person's going, "What do you mean I'm disconnected from the internet?" Now, one of the things that I ran into, and I haven't heard this as much in the past 10, 15 years, but I heard it a lot whenever I suggested using open source, save money. You

get the deans and the administrators whose neck do I choke and it better not be mine because and you you also hear the nobody got fired for, you know, buying Microsoft. Well, of course, you could always blame Microsoft. In fact, that was one of the reasons people kind of hated Linux is a lot of the hardware vendors would go, "Oh, that's just a Microsoft problem." Well, when

you started putting Linux on the same hardware, people were able to show, "No, your hardware is messed up." I think that was one of the reasons why some of the early hardware folks didn't like Linux because they couldn't use the, "Oh, it's Windows." Um, I had one faculty member. She was a great teacher, but she was convinced that we were wasting all this time and effort on

Linux. At least once or twice a year, she'd come with some sort of news article about how Linux was going to die. And I think she did this until just about two years before she retired. every year. Um I think I finally when I pointed out that IBM had was it I think it was IBM made like a billion dollars on it that year they went oh

well maybe it's real. Um but yeah no it was it was quite interesting. So I needed a plan B and with the help of a friend I built a custom bloop floppy and used Anybody even know what these are? They're jazz cartridges. They have one gigabyte of storage. The drive is about this big and it's got some weird form of scuzzy cable connection, but they would let

me put those in there. The problem is is that the boot floppies and yes, we were booting off a three and a half inch floppy for Linux. Um, didn't have SCSI support until you booted up, but we couldn't boot these things up because we didn't have SCSI support. So, I had a friend who uh knew a little bit more about Linux at the time than I did

help me put in SCSI support into the custom boot drives. So, they worked. Um, but they were the cartridges were like a hundred bucks back in 1995. And after a couple hours of running, when you popped them out, you had to be careful. They were hot. Um, a lot of heat there. And of course later we finally got removable hard drives we could check out to the

students but then they'd have to at least you know buy the bay to use at home. I wanted students to be able to do it outside of the classroom. Um we then did VMware. We ran into some problems. I think it was VMware or Virtual Box. I think VMware never had in the early days a free version for the Mac users, you know, and with community college

students there's only so much you could get them to buy. And then finally many many years later and this basically was probably a couple about 2018 we finally got a membership with the AWS academy and we could use that that I mean I'll admit having dealt with their billing system it is the biggest voodoo ever but for just using it with the students it solves so many

problems mainly it took our district office out of the equation entirely and it worked much better. Um, and then of course as time went on, there's several times where I helped develop things and then we hired somebody full-time to take it over. Uh, some of you may or may not have uh met Dennis Kibby. He was a big one in the Phoenix Linux users group and he's

given talks here. He got hired on and focused on the Linux courses and built it up and as of January 3rd, 2017, we became a Red Hat Academy. That's a big improvement from why are you using Linux? Use a real OS. So, Python and you're going, "Wait a minute, still know my SQL." We'll get there. Um, taught a few sections of a very primitive web development course

using Python. It's long since been um they decided to get into the gaming. Gaming is a big deal. So, we're building a gaming associates. Well, we ran into a problem as we are a district of 10 colleges. Our instructional council is made up of the 10 colleges and you have to get at least seven of the 10 to vote and we want a programming prerequisite for the

intro game course. But they said you can't have a significant prerec for a 100level So I basically used Python and Pygame when I first taught it. Um because you could learn the basics in a few weeks. I mean basics you're not going to be powerful. They could after that first few weeks do some fairly fun little uh textbased games. Some students are very creative. Few of them

may need therapy, but that's a whole different issue. Um, some of them were fun fans of a game, uh, I have no mouth, but I must scream. Look it up sometime. It's a interesting game. Um, and then we'd use Pygame. Okay, so my SQL finally, I used it in nearly all of my web classes. Um, you know, because if you're going to do PHP or even JavaScript

or whatever, having a data store works. and we had access to why don't I give each student their own little database on the system and work through that. We used it with PHP JavaScript. I created a simple PHP API that they could call using Ajax. Um and of course we developed Python level one and two because it became more of a serious language. This was another one

where in the gaming class I constantly went use a real language and it's like well what do you want me to use? C. Okay. And what's the gaming environment for that? At the time, it was something called the XNA developer studio. C. You could argue that the reason why it exists is a little petty, but it's a solid good language. The XNA developer studio, I can't say

anything about it because if you can't say anything good, don't say anything at all. It was horrible. We did that for one semester. C, great language, but to learn the basics because nobody had programmed before and built a game in it that just didn't work. Went back to Python. And of course, eventually we had a full-blown MySQL class because databases were becoming more important. And they realized

that while Microsoft SQL is a solid choice, access is not. So, we need to get, you know, some other things here. It is about to get better. Um but as is typical before it gets better we ran into co uh to say that the faculty and the administrator and everybody else was not ready for this is putting it unbelievably mildly and I'm not talking about the disease.

I'm talking about no in-person classes anymore. Going to have to go to either purely online or live online. Um, now there were a few of us who realized that some of our students, because I teach evening classes because most of my students work, I'm a night owl and I've told them if you have a 9:00 a.m. meeting on Friday, let me know how it goes because I'll

be I won't be there. Um, so I had students who were working and they couldn't always get to the classroom, but we were starting to use Zoom and WebEx. And um when this happened right around spring break of 2020, we were told the pure online courses were allowed to meet, but everybody else tell your students it's three weeks and you can then do all the work you

need to convert things over and then you can start teaching. So a 3-week gap. And I'm thinking that some of those students are stuck home. They can't even work. it seems this would be a good idea to give them something to do. So, as I was already doing this, I just contacted all the students and right after spring break, we just started. The week went beautifully. Minor

hiccups. Um, I realized very quickly with Zoom, you definitely want two monitors so you can have one monitor that you don't show to folks. Um, and again, the students were doing fine, but word got out that I was This is a really odd thing for me having them tell me that I did it wrong by teaching students but it was making the other folks look bad. It's

like but okay but the pure online courses are still meeting because oh well um had to tell students that week never happened. So I ended up with a couple extra weeks of vacations like use that time to get ready. uh been there, done that. quite interesting. The other interesting part is because we lost three weeks, they said, "Okay, we'll extend the semester by two weeks for those

folks who feel they need it." And the accountants went, "Uh, everybody's saying they need it." Yeah, you just cut three weeks out of some cases an eight-week class. the good thing is we about three years finally got approved at the state level. We had to kind of fight with some of the universities. We can now in certain cases offer four-year degrees at a community college. This was

a many many many year fight and the one that we got was a uh data analytics and programming. So we started developing it around 2022 23. first classes I think were about fall 2023 and our first graduates should be this May. We're hoping about 40 people. We've had a lot of people interested in it. Um my SQL database is one of the key classes. I have at

least two sections a semester and they're usually full. And uh I also created a NoSQL class and using my SQL well it was much easier on AWS Academy. our district was sold a Palpo Alto firewall but we also have one at MCC. The people in charge of these firewalls were not the people who knew how to configure them. So, we were having problems when we were using

VMware that when we tried to do updates on Linux or updates on my SQL, it would time out. And I talked to our person and he proved to me, see, we're not blocking it. Yeah, but why are we getting zero bytes per second? It's like, oh, they must be doing it at district. It took him a week to get somebody there who knew and would admit that,

oh yeah, well bzzip files are only used by hackers, so we're just blocking And I was like, okay, can we whitelist them? It's like, yeah, give us one or two. It's like, well, no, it's it's there's a big list I need. AWS made life so much easier. Uh, I just have students install on an EC2 instance. Now, there's no Linux prerex, so I kind of have to

give them step by step for the Linux stuff. And I will go into additional information like during lab hours and the rest, but it works out pretty well. Um, I have them create a final project in my SQL and they have to do a entity relationship diagram, create procedures, functions, triggers, views, etc. And there's been some interesting ones. Um, a lot of people do inventory systems. I

had one person do dog breeding is apparently a really big thing and he was breeding aas and there's apparently breeders know each other and they keep track of the lines and of course it gets a little confusing because the whole you know not your relatives thing doesn't apply to dog breeding had a really cool system set up with it. um students have put together like Minecraft recipes

so that you can search for all the components uh discussion boards. Now they don't usually have the graphical interface but they have all the procedures and triggers to basically provide the information for that fitness tracking. Uh, one person created one to track um, you could keep track of what trails you've been on, make notes about it and look at other people's notes and go, "Oh, that trail

washed out this week." Or, "Hey, you should check. There's some big horn sheep on the Fremont Saddle Trail again. I've seen them once or twice. Recipes and meal planning for the family." And quite often they create stuff to use at work. Um again we go over data types, indexes, security issues, ethics and legal issues. I demo some web interfaces and we have separate classes of course and

I would teach some of these where okay, we're not going to teach you the database part. So here's a pre-built database with some procedures, but now we're going to connect that to PHP and actually have it do queries and the rest. Um, I really like teaching the full stack. And of course, the two words that my students started to dread the most. What is the best way

to do blank? Well, it depends. What are you doing? Uh, what resources do you have? What questions are you asking? There are some cases where we know there's some things that are just dumb and you shouldn't do it, but most of the time it really So, some of the things that I've used AWS EC2 uh is the new one. We're using MySQL 84 for the time being.

Um because we got a lot of Windows computers, we do use Putty and FileZilla. I still wish they didn't use PPK files, but whatever. Um MySQL workbench, which is starting to little long in the tooth. It's no longer being updated. So when we connect it to 84 the students get warning this is not properly supplied. You want to see a student freak out pop up a warning.

My favorite had to be in the early internet days I had a grandma taking the class. I'm so sorry I didn't mean to. Are they going to call the police? It's like what? She points to the screen. You have performed an illegal operation. And I'm going no you didn't do anything wrong. The people who write the error messages at Windows are morons though. She did odd outlook

on things. Um, I'm thinking of switching to DBver. We've got that installed. There is a process. I can't just install software. I have to fill out forms and and wait a I think they regret the form system because before that I would get bounced around all the way up to the vice chancellor of information services and then well okay to be fair he got diagnosed with stage

4 colon cancer and left but I kept getting you know ignored now that there's an official system they realized oh crud there's a paper trail I started getting stuff more often then um I also of course like portable apps stuff that I don't have to install really helps. Um, been using PyCharm for Python, but VS Code is also popular. Um, and of course, we use Notepad++. And

this semester, it's like well, I double checked. Either we didn't install the version that was vulnerable or because it wasn't after everybody, we were not considered important enough. And to be fair, that's happened to us before. We had a major hack in 2004. I do not know the exact details, so do not take any of this, again, this is just what I've heard, but effectively the FBI

called us and said, "Hey, did you know your database is for sale online?" Yeah. Of course, at the time, you had Target, Neiman Marcus, and all these other big databases. We were saved because oh community college students nobody wanted to buy It's like uh gee that makes me feel good. Um so no SQL and MySQL. Um I have them install my SQL for contrast because if you're

saying it's and I love how it's changed. It used to be no SQL. Now it's not only SQL. Yeah. Right. It's kind of like Pine stood for portable internet network emailer, not Pine is not Elm. We all remember what it used to say. Um, and of course I compare that with MongoDB, Neo4j, Cassandra, and Memcached. Um, and I realize there are a ton of other non relational

databases out there, but I picked four biggies when I developed the class, and it works well. It can be a bit overwhelming. I don't think we could cover too much more. Um, but I do use some of the JSON features to extract from a relational database to JSON and then import that into Then I'm really evil and have them try to create a database side schema. Yeah,

that's loads of fun. Um, and of course I discussed the document store feature in my SQL. In fact, Scott Stro, who I think gave a talk here, he's given a talk to my NoSQL class and demonstrated the store and it's like, wow, you can mix SQL and the JavaScript queries and a document store. And if it had the same partitioning support that did, my SQL could be

a real competitor. Um, it was kind of cool. So students, most of them are probably in their teens, but it ranges from about 15 to 70. Most of them are recently out of high school. Many are the first in their family to go to college. Every time at graduation, they have, you know, how many people here are the first in your family to graduate from college? And

it's about half of them or more. Um, they vary widely, wildly and widely in how wellprepared they are. Some of them are great. Some of them have never had to study before. Um, I always love it. Can you give me extra credit? No, I don't want to grade another assignment. And I have to give it to everybody. I can't just give it to you. Why don't you

just go and do the actual assignments? They will check YouTube. They'll check their friends. They'll look for books. But there's this weird person they're already paying who's sitting here in lab hours on Zoom just going, "Huh, well, I had two people show up. Let's see what happens for the last half hour." Um, many of them are working full-time, often with a second job. Again, community college students

have a different makeup than university students. Um, they often have a new family or kids to care for. I'm the number of times like, "Can I, you know, have a couple extra days? My wife just gave birth." It's like, "Yeah, yeah, yeah." You know, no big deal. Um, of course, they're worried about the job market in general and the AI hype. I've had people going, and this

of course happened just about the middle of our first three years of our degree. It's like, is our degree worth anything anymore? Yeah. Yeah. It's going to be a little rough at first. um they're going to be doing their best not to hire junior people for a while and just use senior people with AI to speed things up is one of my guesses. Um but that will

eventually be self-defeating because you run out of affordable or just out of entirely of the senior folks, you know, um housing costs are climbing far faster than wages are in Arizona. Um and quite often employers say, "Oh yeah, we'll support education and then finals week." If you don't show up, you're fired. Um yeah, so students prefer live online or in person. They prefer to deal with a

person, but and this is the big but their life just doesn't make it practical. So that's why they tend to go for the asynchronous online. I get a lot of compliments that you do it after 5. So none of my classes start till 5:20 or 7:10 to 9:15. By then, usually by about 3:00, the campus is already starting to close. And at 5:20, if I have a

technical support issue, um, yeah. Oh well. Um, at least they've stopped locking the bathrooms at nine o'clock. I got rather upset about that. Um, so I do flexible attendance. Now, some of my classes are live online, which means nobody in person, but others I have a classroom, but they can attend either physically or via Zoom. And I do have some students who bounce back and forth. Of

the six sections I teach a semester, I usually get about one or two of them where half the students show up in person. and I always have at least one of them where I'm talking to an empty room. Um, but it really does work well. I don't require them to show their video partly because a lot of them don't have internet that can handle that well. Um,

and they can chat their questions. They can unmute themselves and ask questions. And I always tell them if you DM me a question, I'll read it, but I won't say your name. I have noticed, you could argue whether they're more or less prepared than they used to be. I think it's probably about the same. You just tend to notice the ones who have problems more. But as

far as willingness to ask questions, I actually had a student say, "Well, you know, we shouldn't interrupt you." The class is more interesting when you ask questions. So, the DM thing seems to help. Um, we do it for time. And I will do step-by-step lab assignments where we'll work our way through class. And then I will have students add on like I'll you'll create a simple little

table here and then I want you to alter the table, add a few columns, put your name in it with some inserts and updates and other things, and then do a screenshot of that. And the fact that they have to put their information in it means it's more likely that they're going to do it and not necessarily be just a copy of somebody else's homework. Though with

the way generative AI is, they could probably fake this. We'll see. Um, I create a large number of help pages that I constantly update and tailor to the students needs. And sometimes I beg some students. I have three types of students. Basically, when it comes to lab hours, those who always show up and don't really need it, but it's still good to see them and they ask

some cool questions. Those folks who do need it and they come occasionally and they do much better. And then those who desperately need it and I never see them no matter how much I beg or plead. Um, and of course I also have speakers come in and David Stokes was one of the first ones from Oracle uh at the time who gave talks to my database classes

via Zoom and Scott Stro has done a couple and I'll probably have him come uh via Zoom this April as well. So I do help pages and I I got to admit I'm not I always told my web students everything I teach you is great. just ignore how I pick color. And no, I've been tested. I'm not color blind, so I don't have that excuse. I tend

to use too much color. I actually did use a pallet generator, but I I don't know how to be subtle. Um, but I do use a little Python script that does the color coding on the code. Um, and we get to this point. I am mostly self-taught. I just started playing around and building my own servers and and compiling kernels for no good reason. I updated the

cache on my uh little 486, which I have no idea if it did any good. um and just you know learn that way. I'm constantly trying to keep up on all the different technologies and we'll get to the whole AI issue here in a minute. I go to conferences and it's mostly to get a list of things I need to learn or a list of things I

shouldn't do anymore. I've gotten those. Uh, one of my favorites was we have a local security conference called Cactus Con and I'm walking by and somebody says, "Oh yeah, if you're using Ginga 2 templates in Flask, there's this huge injection vulnerability." It's like, "Hello, I need to talk to you right now because I use that." Um, I've been to Scale Defcon a few times. Perona Live, I

wish they would do that one again. That was a great one. Um, and that was back when David Stokes worked for Perona. And of course, PYON. This year, I can't go to PYON. It's actually closer. Instead of Pittsburgh, it's in LA, but it's the weekend of finals and it's graduation and it's our first batch of students. And they've all had me as a teacher, so I need

to show up. We've been hiring some people. I'm on the interview committee. I see these folks and I go, I'm glad my job is secure because these people are really good and I feel inadequate. And of course, we have things like AI. So, future of education, AI, hype versus reality. It is going to do everything. It is going to destroy everything. It's absolutely worthless. It's the best

thing ever. And I'm fairly certain both of those groups are entirely wrong. It's going to be muddle out in the middle. There are certain cases where it works really well. I mean, and it's getting better, but it's also quite clear what happens when people are using it in an area they're not familiar with. Um, there are some folks who've done some amazing projects with Python and other

things using Claude and other tools, but they take their time to figure out what it is that just spit it to it. Some of them are great at explaining what's going on. it's a big deal and some students are using it and oh, some of them are really bad about it. Python, my intro Python class where they've never done any programming. That's where I get most of

it. And it's usually from some library that doesn't exist import something that's not even used in the code and I try to run it and it just dies. Now, there is a new attack based on this. Has anybody heard of slop squatting? That's where some of the folks who create bad libraries notice that these AIs tend to generate the same bad name. So they create actual libraries

of that name to trick people to installing an attack library. Yeah, I usually just go I don't know if this is AI slap or not, but this is not the assignment. Please see me in lab and let's redo it. For now, you get a zero. Um, to say that schools and colleges are still trying to work out AI policies is putting it mildly. Um, I'm trying to

convince them that they need to learn the material and then use AI to speed up their productivity, but nobody wants to take those first steps. Well, there's a certain number of students who don't want to take those first steps. And of course, because of this, students are incredibly worried about the job market because the people who are trying to sell you things, Nvidia, Anthropic, and Open AI.

Oh yeah, you can get rid of all your employees. You won't need anybody. You know, you can have the janitor for half price code your whole code base. Um, it's what it sounds like. And to the students, they hear this, it's like, it's already a competitive job market and they're not going to want a beginner like me. and I give them a variety of advice. Pick a

couple projects, work on them, put them up on GitHub, and just tinker. We've got various different data sources, Kaggle. We have access to policy map. do a a data project and show that you know you're using Python and then maybe Tableau or PowerBI or Nime uh maybe some R and then presenting it with a database and just show them that you're working on things. That's going to

be useful as well, but it's no guarantee. And I'll be honest, I'm not entirely sure what to tell them. Um, and yet, you know, as I was mentioning, some of the projects they do are really, really cool. And I have honors projects in most of my classes where a couple students will come in, we'll set up a project, I give them some ideas, they go through it,

and I many cases learn more than a student. Um, one we we're doing a uh demo table at the Barrett Jackson STEM show, which is the one family day for this huge car show in the valley. I mean, some of these cars, I've only ever once seen a price on one. It was a really fancy SUV and it started at $500,000. I mean, these are really nice

custom things. So I had a student who for his honors project now he did use I think Claude for some of the JavaScript front end but for the back end for Neo4j and a few other things and he took the map of the school created a whole bunch of different little nodes and you could pick any two spots and it would give you walking directions to get

there and he demonstrated that. That was a really cool thing. So, you know, we're being pushed put AI in every class. Well, I mean, I at least discuss it and I do demonstrate um you know, building tables with uh chat GPT or others um and point out, you know, you like any tool, you have to double check it, but it is I don't know how to put

it. It's I don't know what to tell them sometimes. Hell, a lot of the faculty don't know. We do have folks who say things like, "Oh, yes, and my thought partner." Oh, well, I won't say which of our administrators said that, but um it made me cringe. And yet, I mean, you've all played with it. Some of you build it. Some of you work with it. It

can do incredible things. It can also do a big pile of male bovine excrement. um if you can give me any advice, but I'm hoping just the history of how I dealt with open source and tried to be an advocate for it was at least somewhat Any questions? I mean, I could try. Oh, hello. Um, yeah. Hi. My name's Ian. I'm a student at Calp Poly Pomona

and I'm part of my school's cyber security club. Um, I really like putting together the workshops for the club and teaching the other students, but I'm about to graduate with a bachelor's. So, do you know if there's any other type of opportunities someone in my position could continue teaching people like cyber IT stuff with after graduation without getting a master's or am I going to have to

get one maybe? So, you're looking for teaching opportunities before you have like a master's or other upper level degree. >> it really depends on the school and where you're going. Um, you can become an adjunct. Understand that adjunct means underpaid for what you do. Um, but it is a good way to get experience. Sometimes you may not be required to do that. You can also use work

experience if you have that. Um, I was occupially certified. My actual degrees are in a STEM field. They're just not in computer information. Um, but I worked at a few places and basically babysat networks at night and other things like that. Um, that's one possibility you might want to look at. There are online places that will let you teach or develop classes. Um, you could just try

to do it with some local groups. Look at some of the luggs. The Linux user groups quite often they you can do presentations and put those up on YouTube. Um it can help. You have to kind of get your name out there. The degrees are and they'll what usually get you the job, but you won't know the job exists quite often unless you do some networking. I

mean, yeah, they'll put stuff on LinkedIn, they'll put it on the other job searches, but like even for the Phoenix Linux users group, a lot of those people work at different companies throughout the valley and they will occasionally, you know, somebody's been coming to the meetings, they've been asking cool questions. They're not somebody you want to kill in two weeks. And they'll go, "Hey, you know, we've

got a potential position open. You should apply for it." And you won't normally hear that because that's one of the drawbacks of using places like LinkedIn and other tools is you may get somebody who's brilliant, but you're going to have to figure out where to hide the body in two weeks. Um because nobody can stand them. And that is definitely a skill that a lot of people

in uh the computer field need to learn. again, I wish I had better answers, but that's some possibilities. >> Thank you so much. >> Hi, I was just wondering uh what was your experience building the curriculum and the I guess the lesson plan for the MySQL class? >> Building the curriculum for the NoSQL class. my SQL the the main one you know were the experiences that kind

of shaped >> we have two levels of curriculum development there's the official competencies that are put into the course bank and then there's the actual material um the material in my SQL has evolved over time I keep adding new pages new things that we find and of course some of the changes uh you know going from 57 to 8 was actually even when 57 kicked in and

suddenly, oh, you need need a separate permission for references and little things like that. Um, it could be a pain. You have to constantly keep it up to date. But the NoSQL one, I've done both levels. So, the MySQL curriculum was developed and then I built the class materials. But NoSQL, it was me from the beginning. And this was a team uh as we're building it for

a four-year degree even though it's a 200 levelvel class. We had people from several other colleges who were giving feedback. And I put the course together because it was the one I was supposed to write. And I'm sitting here looking at this going may have been a little ambitious. So when I bring it forward to them, I say, "Okay, feel free to cut some stuff out. I

may have been a little bit overly ambitious. I'm worried that this might be a little bit too much to try to do in a single semester class. They deleted a line, changed some wording, added one other line, and said, "It's good to go." And I'm sitting there going, "Oh, no. Now I have to teach it." But, and that one took, I would say about the third time

I taught it. It was finally fleshed out. You're allowed to do a pilot. I technically did two pilots. And then I got a lot of things worked out and I'm at the point where I'm not adding too much in the way of new material. Uh the last big thing I did was adding the server side schemas in MongoDB, which you can definitely tell that that was not

the original intent behind People would normally enforce schemas on the front end using something like Mongoose.js. You could do it on the server side. It definitely slow things down. That's usually when a student first time they put in a schema in is when they realize they put in a T3 micro instead of a T2 medium because things slow way down. It's like, yeah, there's a reason why

I said not to use the default itty bitty little instance. Um, so I mean that's part of it. I go to conferences, I go to these talks, I'm already thinking about debaver and I'm looking at the AI options and it's like well you know helping them learn about that and not just do it for them that might be a useful tool. So that could be something I

could add in. Um it is we are constantly updating things partly because software changes, partly because things become out of date and occasionally it's because oh this is cool. I want to show them. I don't know if again I hope I answered your question. I'm a little tired. So >> no it was perfect. Thank you for your presentation and for doing all this work for community college

uh students. I went to a community college before I got my undergrad degree. So, it's always uh something I like to see. >> I like to think that we give a really good bang for the buck. I mean, yes, there's the occasional class that just goes kabooy and there's the occasional instructor that will eventually need to be encouraged to seek other pastures, but overall I think we

do a pretty good job. We have folks from ASU come back for us because we have programming classes where you program and build things. Um I got a compliment from one of the folks who works for the chief uh CISO, chief information security officer at ASU. My friend said, "Yeah, half the folks in our department took a class with Phil." Um and I don't know if that's

true or not, but it feels really good. No. Let's give a round of a round of applause for Phil. Thank you so much,