 For those of you who were at the San Diego PHP meeting last Tuesday, I got to apologize. This is a repeat. What we're doing today is looking at something new that my skills introducing. Normally, I'm talking about things for DevOps or DBAs. This is a talk for programmers. First, let me appease the lawyers at corporate. I'm talking about some products that are not officially launched yet until they have a stock keeping unit and price or non-price associated with it. Please take what I say with a grain of salt. My name is Dave Stokes. Once again, I apologize for being late. I'm usually the person who's always there half hour early because I'm paranoid about missing stuff. These are my contact information. Slides will be up later today. I'm going to talk about a bunch of things today. Part of my job is to educate users of MySQL to make sure they're using things in the proper way. Like this little cartoon shows, if you're not using things in the right way, you may not be getting the results that you want. For those of you who haven't been paying attention, MySQL is now 22 years old. We've been owned by Oracle for the past eight. We have taken over by Sun Microsystems just over 10 years ago. By the way, we are hiring. If you want to work from home, talk to me later. Now, two years ago in October, or three years ago in October, we introduced MySQL 5.7, which has been our best received version of MySQL. Programmers really loved having a JSON data type, and we'll touch on that a little bit. We've really been cranking down on security. Your bosses might be talking cloud, but they're buying security. So make sure you're aware of that next time you go out to code on something. We've done stuff like password explorations at rest encryption. We've also done features. So if you are playing with Chinese, Japanese, or Korean, where you can actually parse video grams, we support the GB-18030 character set, which means if you're talking to folks in the People's Republic of China, they're going to want to hear that. We've done a whole bunch of improvements on the software, and that's pretty good. Something else we've done is we've introduced plug-ins for several features. As releases get bigger and bigger, it's kind of like trying to eat an elephant. It's just too many bites to actually get it down. So some new features like group replication and the document store are plug-ins, and I'll show you how to install those. Very, very soon, MySQL 8 is coming out. Before you ask, there was a MySQL 6, well, it currently releases 5.7. There was a MySQL 6 in the pre-Sundays, and that kind of works like PHP 6. It has some interesting features. We've back-ported into the 5 series, and the cluster product has been using the 7 series for a long time. So the next major release of MySQL will be MySQL 8, and we've jumped the numbers because we've put in a lot of really neat features. The first one that works like you is the true data dictionary. In the past, if you've gone under varlib, MySQL, whatever your database name, and you've done an LS, you've seen things like .frm files, MYIs, MYDs, all that. That's the metadata on your files. Well, now we're keeping the metadata in the database. The good news is that you'll be able to have millions of tables within a database because you're no longer tied to the i-nodes. The bad news is that you're going to be able to have millions of tables within a database. For those of you who are great here who remember being told, all you need is 640k to program in, I'm going to give you a flash forward correlation to that. Also, that gives us the ability to do atomic DDLs, so you'll be able to do alter tables. Boom. Yeah, we also picked bug number 199. Anthony over here, we'll smile at that. That's our old auto increment problem. We're enhancing JSON. We're giving you resource groups so you can dedicate certain numbers of processors in your multi-processor boxes or multi-core boxes to certain things like batches or certain users or certain projects, so you'll be able to segregate certain parts of the processing power just for those jobs. And everything's going to be UTF-8 MB4. That might be a little bit of a sticking point. We actually have a way with the MySQL shell that I'll demonstrate a little bit later on how to check what needs to be upgraded to do that. We're also having things like invisible indexes, true descending indexes, and we also have an interesting feature called CAPS. Yes, sir? It is 4-byte UTF-MB. Yes. He was asking UTF-MB4 implies that everything is going to be 4-bit, and if you're doing that for indexes, that means your indexes go to 4-bit. Yes, byte. I'm sorry. Yes. So, and of course the reason we're doing all this UTF-MB4 stuff is because everyone somehow wants to get the poop emoji into the data sets. So, what are we talking about today? MySQL without the SQL. DBAs see the world differently than the developers. I was originally a developer, then I became a DBA. Developers like to think of things in sets and with relational calculus. Developers see things as data records, usually. And the thing for developers is in the past over years, their job's gotten a lot harder. When I was originally a full-stack developer many years ago, LAMP was basically it. You had Linux, you had Apache, you had MySQL, and you had PHP. Maybe you had PHP, Python, or Perl. A few of them might have been playing with Python. And then you had BI, and that was about the entire toolkit you had. And recently I started learning Node.js. You don't really learn Node.js, you learn NPM, the package manager. The learning curve is getting stronger or steeper every year. A lot of projects now that I run into. You start coding first and then you design later. I kind of call this Ready, Fire, Aim Management. Scope Creep, I signed with someone the other day. Their project was originally supposed to work on handheld Android pads as they walked around a warehouse. Soon as they got it halfway delivered, oh, it needs to work with non-Android devices. It needs to work on laptops. It needs to work on watches. Oh, by the way, we want to do this off a web page so if someone's driving into work can actually see what's in the warehouse. Yeah. Oh, by the way, we need that Tuesday? Yeah. Another problem a lot of developers have that comes either from them or from management is, oh, there's a new technology. I saw this in computer world. We have to have this. This is the latest thing according to computer world. And also there's the interest in keeping current so your resume looks up to date. And a lot of convenience fighters are fighting. Now, DBAs and DevOps on the other hand, their main goal in life is to keep everything stable. You want everything running on a nice even keel as often as possible. And then you start throwing in things like continuous integration, new regulations and policies. How many of you are from Europe and are looking at the new GDPR? Isn't that a lot of fun? I keep reading the regulations and they keep changing. It's like musical chairs only instead of taking the chairs away they add new rules. So there's more demands on the DBAs and less money. And also they're being told, well, there's all these new technologies. Yeah, but a lot of business knowledge is relational. Now, part of this goes back to structured query language. Structured query language was really designed in the 1970s to minimize disk duplication. Because disks were very, very expensive and about the size of this thing for about 160 to 150 megabytes, not gigabytes. The other great advantage that SQL had over things like index sequential access method is you can get multiple records at one time to us. Now that seems kind of trivial, but at the time that was a major breakthrough. Big problem with my S structured query language is it's descriptive language, not an inductive language or procedural or an object oriented language. It's divided into two parts, DML and DDL. No one else does that. Python doesn't do that. Pearl doesn't do that. Even COBOL didn't do that. They had four sections, but it was one language. Also, you need to know something about sets. You also have to know something about your data. And it's hard to look at a query and tell if it's a good query. Most of you can look at a piece of code off, get repository, and tell them about 15 seconds looking at whether it's a piece of crap or not. Can't do that with a query. You can have a query in front of you that looks horrible, but some fastest thing out there on the server. So you don't really have a gut feeling of what a query does. And plus you have to learn how to run explain. There's some giggles up here, so they know what I'm talking about. If you don't understand, I'll show you in a little bit. So, past seven years, I've been going around talking to developers and asking them how much training have you received in SQL, relational calculus, relational theory, just general databases. And the number ends up being about 2%. And the other 100% wonder why their queries don't do exactly what they want. The other thing I see is a lot of developers say, well, there's this thing called object relational mappers. This will solve all my problems. It will translate everything between the database and my programming language, and life will be great. There's some very good ORMs out there, but they're an extra layer of complexity. They can be messy. They're hard to learn once again increasing the learning curve. And usually, they don't write the best code. Their SQL is kind of nasty sometimes. And usually if you could learn SQL, you could save that layer of complexity and get things better. So, what if you want to combine the needs of developers and DBAs together and get something that's the best to breed? And what if you do it on one stable platform, say like MySQL? So, we're introducing the MySQL document store. And what's this, SQL is now optional. It's built on the MySQL JSON data type. So, you basically have a JSON data blob out there storing all your information. You don't need to use SQL. It works with MySQL 7 or 5, 7 and 8. It's built on top of our new developer API called XDev. It is an interesting protocol. It has more features than just what you're going to see here. It's going to be our application interface into setting up clusters and a whole bunch of administrative chores. You can also set up asynchronous queries. So, you can send off several queries at once and have them come back at their leisure to your application. It also gives you a more modern programming style. You no longer have to put in that ugly string of SQL in your pretty code. A lot of developers tell me aesthetically they just don't like doing that. So, just take care of that. Now, right now we have connectors for C++, Java.net, Node.js, Python, JavaScript, PHP. PHP is a peckled extension. There's more on the way. We have a new shell that I'll show you in a couple of minutes that has built-in Python and JavaScript interpreters. It also has an SQL mode. It also has command completion. So, if you can't remember your SQL or your queries, it's all out there. Now, to install all this, you can download the MySQL shell and tell it to enable the X protocol. Over the old command line interface, tell it to install the plug-in. And it's installed plug-in MySQL X, shared object name, MySQL X.SO, or DLL if you're in the Windows world. And away you go. By the way, you can install this if you don't like it. And then you start the MySQL shell. The new MySQL protocol is on port 33060. And let me, rather than run through these pages, let me actually get it running. MySQL SH root at localhost, and then the schema I want to talk to. And that's not showing up, is it? Yeah, and I forget how to move to the other one on this. Does someone remember how on Ubuntu to share the displays? Mirror displays. That looks better. OK, so let me get out of this. As you can see, there's a little bit of color here. And that is horrible resolution for the back. OK, custom font. Is that a little better in the back? Of course not. Well, let me go back to my slides then. Please drop by the booth, and I'll show you all this. So you start the shell, and you connect. Or on the shell line, you type in the root at localhost, and then the schema you want to talk to. If you type slash s, you'll get the status. Now, the things I want to point out to you are that we are running with TLS. You're running on a secure connection. We're forcing that on you. We know there's a lot of you in the past who said we really want more security. We don't want you automatically trimming our 32 characters that we try to shove in a 32 character data slot, things like that. But there's a lot of need to make sure we're doing things properly. So if you don't like this, there's ways to turn off it by default. We're forcing security on you. And also, we're talking to port 33060. This is the port for the new protocol. Now, when I logged in, I did minefield shell root at localhost slash world x. That means I'm talking to the world x schema. The first lineup there shows you that there is a object called db, which points to that schema. Now, you can use the built-in Python or JavaScript interpreters to talk programmatically to the data out there. So the next command I'll type in there is db.getCollection, so it tells me there's a document collection out there called countryinfo. And from that, I can type db.countryinfo, find where the id equals quote, USA, unquote. And it goes out and returns that document for me. Now, if you're in a SQL hand, you're going to say, well, I could have just done a select star from countryinfo where underscore id equals USA and got the same information. But if you don't know SQL, this is another option for you. Now, of course, programmatically, we've added all the nice little things. So you can actually go out and say, what fields you want. I'll show you how to print those out with braces around them or not. We have limits. I hear we're limited to two records. And if you want, the entire e, b, and f are out there, so you can see how things piece together. Like for find, you can specify fields, group by, having how to sort them, limits, and offsets, and how to bind variables. So you have a variable like name and loop over that and feed in a new variable to that binding. We also have, for all the crud functions, like modify, add, and remove, they're out there. Now, here's an example in Python. Do you have any Pythonistas out here or got a couple? OK. It's pretty much like using the old database interface with the exception that you're not having to use SQL. So here we connect to the server. Once again, port 33060. We say what schema we're going to talk to, and then we pick the collection we're going to talk to in that schema. And then we do the same find, only in this case, we're binding a parameter. And we bind the parameter to name, param to USA and execute. So it's very similar to the example you saw a little ago. Node.js folks, this is the same thing in Node.js. What's interesting here is since we would like to work asynchronously with the new protocol, you can do a whole bunch of promises and get your answers back. So if you have a bunch of queries you want to make and you don't need to get them back all at the same time, set up multiple promises, and away you go. This is the same code in PHP. Once again, we pick our schema, figure out what collection we're going to talk to, dollar collection, find ID equals USA, and specify the fields we want. So a lot of you are going to ask, well, how does this work? And most of the reviews we were getting is that it works very well. Now on the other side, if you've been playing with MySQL for a while, you realize that this isn't a new server, it's the same MySQL server underneath the covers. What's happening on the back end? Well, when you create a collection, you're actually getting a two-column table. One of the columns is called doc for JSON document. As you can tell, we go to all the extremes to get the proper names for our columns. That's a horrible joke there. The second column you get is called underscore ID. Now, this is a primary key because in ODB you want the primary key. By the way, if you don't pick a primary key for in ODB, it will pick the worst one for you. So make sure you pick the one for that. Now, that column is a generated column. In five, six, or five, seven, we took some code from the community on how to do generated columns. A generated column lets you take information from other pieces in a column and do some sort of engineering on it. So like you have a sales price and you know what your tax rate is, you could have a full price where you estimate the tax. Well, in this case, we're going out to the JSON document and looking for a key value pair named ID, extracting the value from that key value pair, putting in its own column. So for the SQL side, if you want to do a fast search on just that column, you can do that. Big hint, if you have other data out there like zip codes, customer numbers, credit card numbers, whatever other information you want, you can also use that in generated columns. Now, the ID is something that you can set. If you don't want to take our numbers, you can set your own. And we have functions to tell you what the document ID is like. Use document ID or get document ID for multiples to get that. Now, here's an example using the built-in JavaScript interpreter where we load the MySQL library. We open up a session, and from the session, we go out and get a list of schemas. Doing this in SQL, and I probably should have that as an example for the next version, do the side-by-side comparison. I think this looks a lot cleaner than doing this with SQL. It also fits more of the Python mode. So what are you gonna say? Document source, great with collections, but what about my existing relational tables? Well, they're out there too. The only difference is you have to treat them a little differently. Rather than going out and getting a collection, you do a get underscore table. Collections and tables aren't exactly the same. So here's an example, we go out and get the table. We insert some values, and we go out and pull back records. No embedded SQL in there. The next question I'm gonna get is, okay, we can do collections, or we can do relational tables, but can we do both? And yes, you can do joins, by the way. That's coming. Well, we're trying to make this like the old Vegematics. Anyone under 25 won't know this, but this is something they used to sell on TV called a Vegematic. On that platform, they have these little blades that you can put in there, and they would cut, they would crinkle cut, they would ripple cut, anything you put in there, potatoes, carrots, cucumbers, fingers. If you had one of these and you loved it, you really loved it, if you didn't love it, it didn't do it, but it was one of those universal tools that if you had one and it really worked, you used it all the time. If you need to use tables and document collection at the same time, you have to cast the collection as a table. And how do you do that? Well, we have a nice little function called get collection as tables. And then you treat it using the document store techniques for addressing tables. Transactions. This is the true heart of any database. Most programmers don't use them to their full power, but you can also do transactions within the document store, all without having to do the SQL begin and commit or rollback. By the way, rollbacks in there too. So this is available right now for testing. It should be GA very soon. Currently it's a plugin for MySQL 5, 7, and 8. It gives you the best of NoSQL and SQL rules on top of improvement technology. So if you're already running MySQL and you need to look at something in the NoSQL world, please take a look at this. And plug for a book that I have coming out fairly soon. Last chapter went into final editing yesterday. Excuse me. It covers a little bit of it. As I was writing it, it was emerging and maybe addition two. One of our engineers, Dr. Charles Bell, was writing a book just on the document store. Mine's on the JSON data type and that. That's coming out soon from APRESS books. If you have any questions, we have a lovely booth with lots of swag. And we have a question over here. The slides will be up probably later today. I have a blog. And if you have any questions, please ping me. I'm traveling. It might take me a day or two to get back, but please contact me if you have any questions. And that being said, please, if you have any questions, please ask them here or in the booth because the only dumb question is the one you take home with you. And let's say help you out. By the way, everyone applauded him because he hasn't had enough applause today. What a cool name to have when you live in LA. Thank you. So when you showed the use collection as table, is there an overhead or computational penalty to doing that? No. Oh, and really it's just inbuilt into it, okay. Yeah, the optimizer is smart enough to know that it's coming in and how to transpose from the document store into straight SQL. I'm sure if there is any penalty, it's in the fractional percentage. Okay. So, yes, sir. This is more of a clarification. So we can use the SQL or we need to like query going forward only using those API. Yeah, if you can put it in as a document store then have someone else pull it out from the SQL side at the same time, no problem. So it's your choice whether you do everything with SQL, document store or mix. So we can use both. Yeah, okay. You mentioned that we can use transactions for JSON. How the locking mechanism works. At the storage engine level, which hopefully you're using NODB, which means it's the record. By the way, has anyone ever told you you look like SA? Yeah, his ears are better than mine. Yeah, it's storage engine level locking. And hopefully you're using it in my NODB. Okay. Yeah. Any availability for release date on the GA for that? Like. The official, my boss is in the room to have to be very careful. The official Oracle line is that I could say we hope to have it out within a year. Hopefully it's gonna be very, very, very, very soon. If you start reading some of our press releases there's things dropping now, like support for MySQL on the new Linux from Oracle on the atom processors. So, read the newspapers the next several weeks and you hopefully will be very satisfied. Any questions about MySQL 8 or MySQL in question or? All right, and thanks for being here. And I guess this is not gonna be a webcast, right? Looks like. Oh, there it is. Yeah, there's a camera. So all this new cool stuff will also be for sure in the community edition. Yes. And I'm not really a developer. I'm here to learn more. So I've been very involved with open source project called City CRM, the open source CRM and uses MySQL at the back end. And one of the challenges of getting more and newer folks involved is it's mostly PHP. And so I think, tell me if I'm wrong or right that with this new version for folks who are out and coming dev and wouldn't do stuff in JavaScript and not do PHP or bother that our application, it'll be easier to them to do code stuff and the rest to move City CRM forward with more JavaScript stuff with this new version. Sounds like that. And we do have a Pekla extension that works with the XDev API. Great, thank you. By the way, the big difference between the enterprise version and the commercial version of MySQL, if you print them all out on the old fan fold paper and hold them up to the light, the big change is that the copyright lines are a little bit different. And with 5.7, the only difference between the enterprise edition and the community version at the core is the version of OpenSSL you get with the enterprise version versus WolfSSL on the community edition. MySQL 8 will all be OpenSSL. The big difference between the enterprise and the community version is that you get support, you get a better monitoring tool, better backup tools, some other tools that help you out. By the way, if you want to see them, please drop by the booth. We can hook you up with a demo. And everyone, please thank SA again for his wonderful job. And if you have any other questions, please drop by the booth. We have a full staff this year and we have plenty of swag. And if you can answer, if you can answer, this is a big challenge. If you can answer some of our quiz questions, you can win a rare limited edition MySQL t-shirts. We haven't handed out t-shirts for a long time. And these are some very, very tough questions. And if you go back to my Twitter feed or the scale Twitter feed, you can actually find the questions out there so you can do some research before you head to the booth. So thank you for coming out. Once again, I apologize for being late.