[{"_1":2,"_1452":-5,"_1453":-5},"loaderData",{"_3":4,"_22":23},"root",{"_5":6},"current",{"_7":8,"_9":10,"_21":8},"development",false,"env",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20},"COMMIT_SHA","8f16fa6f88a8f4ca7ab6820174c34f0788cb0c7c","IMAGE_CDN","https://planetscale-images.imgix.net","IMAGE_CDN_ENABLED","true","INTERNAL_API","https://api.planetscale.com","SENTRY_DSN","https://bd81903b44804e22a06bdc0c1a91b303@o499952.ingest.us.sentry.io/4504531942572032","userSignedIn","routes/blog.author.$slug",{"_24":25,"_30":31,"_1442":1443,"_3":1451},"author",{"_26":27,"_28":29},"slug","fooforge","name","Mike Stojan","blogs",[32,896],{"_26":33,"_34":35,"_858":859,"_860":-7,"_861":862,"_867":877,"_878":879,"_882":883,"_885":886,"_887":888,"_889":890},"supports-notes-from-the-field","body",[36,55,75,80,85,109,128,133,144,156,161,166,178,183,195,200,205,249,260,277,289,307,312,326,331,336,352,363,381,404,409,418,428,448,466,471,479,484,489,514,530,541,553,558,564,569,574,585,590,616,651,681,686,691,696,712,727,738,743,748,753,758,763,784,789,794,799,810,838],["SingleFetchClassInstance",37],{"_38":39,"_28":40,"_41":42,"_45":46},"$$mdtype","Tag","h2","attributes",{"_43":44},"id","support-at-planetscale","children",[47],["SingleFetchClassInstance",48],{"_38":39,"_28":49,"_41":50,"_45":53},"a",{"_51":52},"href","#support-at-planetscale",[54],"Support at PlanetScale",["SingleFetchClassInstance",56],{"_38":39,"_28":57,"_41":58,"_45":59},"p",{},[60,61,67,68,74],"The Support team at PlanetScale is a multi-cultural, multi-national team distributed around the globe, enabling us to support our users in almost any time zone on this planet. Internally, we are organized into two separate groups: Our Customer Engineering team working closely with our ",["SingleFetchClassInstance",62],{"_38":39,"_28":49,"_41":63,"_45":65},{"_51":64},"/enterprise",[66],"Enterprise customers"," and our Cloud Support team supporting our ",["SingleFetchClassInstance",69],{"_38":39,"_28":49,"_41":70,"_45":72},{"_51":71},"/pricing",[73],"Base plan"," customers.",["SingleFetchClassInstance",76],{"_38":39,"_28":57,"_41":77,"_45":78},{},[79],"All of us have a technical background. Some of us develop software and contribute to FOSS software or have been building and maintaining infrastructure at scale for years. Others have worked on large support teams at companies such as GitHub, Google, and Microsoft.",["SingleFetchClassInstance",81],{"_38":39,"_28":57,"_41":82,"_45":83},{},[84],"Our combined collective experience in the industry racks up to a couple hundred years of knowledge. When you approach us with your database-centric problems, chances are we will be able to help swiftly resolve the issue or at least put you on the right track to get there yourself. And if it's something totally new, well, kudos to you! We are about to learn something new, and we'll be learning together.",["SingleFetchClassInstance",86],{"_38":39,"_28":57,"_41":87,"_45":88},{},[89,90,96,97,103,104,108],"If you want to get in touch with us, the easiest way is to ",["SingleFetchClassInstance",91],{"_38":39,"_28":49,"_41":92,"_45":94},{"_51":93},"https://support.planetscale.com/hc/en-us",[95],"open a ticket"," in our support portal or to ",["SingleFetchClassInstance",98],{"_38":39,"_28":49,"_41":99,"_45":101},{"_51":100},"https://github.com/planetscale/discussion/discussions",[102],"start a new conversation"," in our public discussions board on GitHub. For our ",["SingleFetchClassInstance",105],{"_38":39,"_28":49,"_41":106,"_45":107},{"_51":64},[66],", we also offer phone-based escalations and joint Slack channels where we can collaborate.",["SingleFetchClassInstance",110],{"_38":39,"_28":57,"_41":111,"_45":112},{},[113,114,120,121,127],"For a good overview about the different support plans, support targets, and service-level agreements (SLAs), please review our ",["SingleFetchClassInstance",115],{"_38":39,"_28":49,"_41":116,"_45":118},{"_51":117},"/docs/support",[119],"official documentation"," and our ",["SingleFetchClassInstance",122],{"_38":39,"_28":49,"_41":123,"_45":125},{"_51":124},"https://support.planetscale.com/hc/en-us/articles/8098742206999-Working-with-the-PlanetScale-Support-team",[126],"support portal",".",["SingleFetchClassInstance",129],{"_38":39,"_28":57,"_41":130,"_45":131},{},[132],"In the rest of this article, we'll take a look at the most common places we see folks hit a wall.",["SingleFetchClassInstance",134],{"_38":39,"_28":40,"_41":135,"_45":137},{"_43":136},"common-issues-users-run-into-on-planetscale",[138],["SingleFetchClassInstance",139],{"_38":39,"_28":49,"_41":140,"_45":142},{"_51":141},"#common-issues-users-run-into-on-planetscale",[143],"Common issues users run into on PlanetScale",["SingleFetchClassInstance",145],{"_38":39,"_28":146,"_41":147,"_45":149},"h3",{"_43":148},"ssltls-certificate-errors-with-planetscale",[150],["SingleFetchClassInstance",151],{"_38":39,"_28":49,"_41":152,"_45":154},{"_51":153},"#ssltls-certificate-errors-with-planetscale",[155],"SSL/TLS certificate errors with PlanetScale",["SingleFetchClassInstance",157],{"_38":39,"_28":57,"_41":158,"_45":159},{},[160],"SSL/TLS certificate verification errors come in various forms, but what they all have in common is that they prevent you from connecting to your PlanetScale database, so it is immediately noticeable and a hard blocker for some. Most of the time, however, these errors are straightforward to resolve.",["SingleFetchClassInstance",162],{"_38":39,"_28":57,"_41":163,"_45":164},{},[165],"To understand what leads to these errors, it is good to have a basic understanding of how SSL/TLS certificates work.",["SingleFetchClassInstance",167],{"_38":39,"_28":168,"_41":169,"_45":171},"h4",{"_43":170},"how-ssltls-certificates-work",[172],["SingleFetchClassInstance",173],{"_38":39,"_28":49,"_41":174,"_45":176},{"_51":175},"#how-ssltls-certificates-work",[177],"How SSL/TLS certificates work",["SingleFetchClassInstance",179],{"_38":39,"_28":57,"_41":180,"_45":181},{},[182],"It all starts with a certificate authority (CA). A CA issues, signs, and stores SSL/TLS certificates. They exist so that a client can connect to a server securely and that network traffic between the two entities gets encrypted. You can set up your own CA and issue your own certificates, but for your certificates to be automatically verified in modern browsers, companies such as ours need to use a certificate that was issued by a common third-party CA.",["SingleFetchClassInstance",184],{"_38":39,"_28":57,"_41":185,"_45":186},{},[187,188,194],"PlanetScale's SSL/TLS certificate was issued by ",["SingleFetchClassInstance",189],{"_38":39,"_28":49,"_41":190,"_45":192},{"_51":191},"https://letsencrypt.org/",[193],"Let's Encrypt",", a nonprofit CA run by the Internet Security Research Group (ISRG), which has issued certificates for more than 300 million websites to date.",["SingleFetchClassInstance",196],{"_38":39,"_28":57,"_41":197,"_45":198},{},[199],"When connecting to PlanetScale, your computer validates our SSL/TLS certificate by looking up the issuer of our certificate and comparing it against the trusted root certificate authorities store on your computer. When it finds a match, it checks if our certificate is valid and signed by the CA. If that turns out to be OK, it lets you connect securely.",["SingleFetchClassInstance",201],{"_38":39,"_28":57,"_41":202,"_45":203},{},[204],"Most modern operating systems have a root CA store, and your software usually knows where it is:",["SingleFetchClassInstance",206],{"_38":39,"_28":207,"_41":208,"_45":209},"ul",{},[210,222,238],["SingleFetchClassInstance",211],{"_38":39,"_28":212,"_41":213,"_45":214},"li",{},[215,216],"On Mac OS X, the store is in ",["SingleFetchClassInstance",217],{"_38":39,"_28":218,"_41":219,"_45":220},"code",{},[221],"/etc/ssl/cert.pem",["SingleFetchClassInstance",223],{"_38":39,"_28":212,"_41":224,"_45":225},{},[226,227,232,233],"On Linux, it depends on the distribution, but it almost always is either in ",["SingleFetchClassInstance",228],{"_38":39,"_28":218,"_41":229,"_45":230},{},[231],"/etc/ssl"," or ",["SingleFetchClassInstance",234],{"_38":39,"_28":218,"_41":235,"_45":236},{},[237],"/etc/pki",["SingleFetchClassInstance",239],{"_38":39,"_28":212,"_41":240,"_45":241},{},[242,243],"On Windows, the root CA store is an internal database available via the ",["SingleFetchClassInstance",244],{"_38":39,"_28":49,"_41":245,"_45":247},{"_51":246},"https://learn.microsoft.com/en-us/windows/win32/seccrypto/cryptoapi-system-architecture",[248],"CryptoAPI",["SingleFetchClassInstance",250],{"_38":39,"_28":168,"_41":251,"_45":253},{"_43":252},"solving-ssltls-errors",[254],["SingleFetchClassInstance",255],{"_38":39,"_28":49,"_41":256,"_45":258},{"_51":257},"#solving-ssltls-errors",[259],"Solving SSL/TLS errors",["SingleFetchClassInstance",261],{"_38":39,"_28":57,"_41":262,"_45":263},{},[264,265,270,271,127],"Most SSL/TLS errors that we see occur because of these trusted CA root certificates not being installed on the computer and, therefore, the software being unable to verify our certificate. On Linux, you can often solve this by installing the ",["SingleFetchClassInstance",266],{"_38":39,"_28":218,"_41":267,"_45":268},{},[269],"ca-certificates"," package. This package bundles the CA certificates from the ",["SingleFetchClassInstance",272],{"_38":39,"_28":49,"_41":273,"_45":275},{"_51":274},"https://wiki.mozilla.org/CA",[276],"Mozilla CA Certificate Program",["SingleFetchClassInstance",278],{"_38":39,"_28":57,"_41":279,"_45":280},{},[281,282,288],"Most database drivers know where to find these locally installed CA certificates, but sometimes you may need to point the database driver to it. We have the most common paths for the root CA store listed ",["SingleFetchClassInstance",283],{"_38":39,"_28":49,"_41":284,"_45":286},{"_51":285},"/docs/vitess/connecting/secure-connections#ca-root-configuration",[287],"in our documentation",", along with a more thorough explanation of how SSL/TLS works.",["SingleFetchClassInstance",290],{"_38":39,"_28":57,"_41":291,"_45":292},{},[293,294,299,300,306],"Other SSL/TLS-related errors can occur due to ",["SingleFetchClassInstance",295],{"_38":39,"_28":218,"_41":296,"_45":297},{},[298],"libssl"," being too old, or other libraries in use which have not been compiled with SSL support or that simply do not offer any SSL/TLS support at all. If you're interested to learn more about such a scenario, we had an interesting case with Debian Buster in our ",["SingleFetchClassInstance",301],{"_38":39,"_28":49,"_41":302,"_45":304},{"_51":303},"https://github.com/planetscale/discussion/discussions/317",[305],"public discussions board"," a few weeks ago.",["SingleFetchClassInstance",308],{"_38":39,"_28":57,"_41":309,"_45":310},{},[311],"In any case, things that you should NOT be doing:",["SingleFetchClassInstance",313],{"_38":39,"_28":207,"_41":314,"_45":315},{},[316,321],["SingleFetchClassInstance",317],{"_38":39,"_28":212,"_41":318,"_45":319},{},[320],"Disable SSL/TLS certificate verification",["SingleFetchClassInstance",322],{"_38":39,"_28":212,"_41":323,"_45":324},{},[325],"Issue and sign your own SSL certificate",["SingleFetchClassInstance",327],{"_38":39,"_28":57,"_41":328,"_45":329},{},[330],"Disabling certificate verification will make your connection susceptible to man-in-the-middle attacks. This is a cyberattack where an attacker secretly relays and possibly alters the communication between two parties. In order for your communication with our servers to be secure, you must not disable it!",["SingleFetchClassInstance",332],{"_38":39,"_28":57,"_41":333,"_45":334},{},[335],"We also sometimes see users creating their own CA and SSL certificates and then sending us their certificates as well as the matching private key. You will never ever need to do that when using PlanetScale! It's an easy trap to fall into if you don't know enough about SSL/TLS and, from experience, it can get overwhelming and confusing quickly.",["SingleFetchClassInstance",337],{"_38":39,"_28":57,"_41":338,"_45":339},{},[340,341,345,346,351],"Whatever the nature of your SSL/TLS issue is, we will be happy to help get to the bottom of it if you ",["SingleFetchClassInstance",342],{"_38":39,"_28":49,"_41":343,"_45":344},{"_51":93},[95]," with us or if you ",["SingleFetchClassInstance",347],{"_38":39,"_28":49,"_41":348,"_45":349},{"_51":100},[350],"open an issue"," in our discussions board.",["SingleFetchClassInstance",353],{"_38":39,"_28":168,"_41":354,"_45":356},{"_43":355},"integrating-third-party-platforms",[357],["SingleFetchClassInstance",358],{"_38":39,"_28":49,"_41":359,"_45":361},{"_51":360},"#integrating-third-party-platforms",[362],"Integrating third-party platforms",["SingleFetchClassInstance",364],{"_38":39,"_28":57,"_41":365,"_45":366},{},[367,368,232,374,380],"Sometimes, you need to integrate third-party platforms such as ",["SingleFetchClassInstance",369],{"_38":39,"_28":49,"_41":370,"_45":372},{"_51":371},"https://lookerstudio.google.com",[373],"Google's Data Studio",["SingleFetchClassInstance",375],{"_38":39,"_28":49,"_41":376,"_45":378},{"_51":377},"https://retool.com/",[379],"Retool",", but when your preferred platform does not support the common SSL/TLS certificate authorities' root certificates, you can run into trouble establishing a connection to your PlanetScale database.",["SingleFetchClassInstance",382],{"_38":39,"_28":57,"_41":383,"_45":384},{},[385,386,391,392,397,398,403],"When you enable SSL/TLS in your preferred tool, you sometimes stumble upon fields such as ",["SingleFetchClassInstance",387],{"_38":39,"_28":218,"_41":388,"_45":389},{},[390],"CA Cert",", ",["SingleFetchClassInstance",393],{"_38":39,"_28":218,"_41":394,"_45":395},{},[396],"Client Key",", or ",["SingleFetchClassInstance",399],{"_38":39,"_28":218,"_41":400,"_45":401},{},[402],"Client Cert",", which normally only exist for the user to add their organization's self-signed SSL/TLS certificate. Remember, that's the case where you act as your own certificate authority and issue your own SSL/TLS certificates, which is not needed in the context of PlanetScale.",["SingleFetchClassInstance",405],{"_38":39,"_28":57,"_41":406,"_45":407},{},[408],"If you leave these fields blank and hope for the best, you normally encounter an error message such as this one:",["SingleFetchClassInstance",410],{"_38":39,"_28":57,"_41":411,"_45":412},{},[413],["SingleFetchClassInstance",414],{"_38":39,"_28":218,"_41":415,"_45":416},{},[417],"Unable to get local issuer's certificate.",["SingleFetchClassInstance",419],{"_38":39,"_28":57,"_41":420,"_45":421},{},[422,423,427],"We can make use of these fields, though, to work around the issue. When there is a field where you can upload a ",["SingleFetchClassInstance",424],{"_38":39,"_28":218,"_41":425,"_45":426},{},[390],", you can upload Let's Encrypt's CA root certificate or, if it's a text field, paste it in. It is a regular text file after all.",["SingleFetchClassInstance",429],{"_38":39,"_28":57,"_41":430,"_45":431},{},[432],["SingleFetchClassInstance",433],{"_38":39,"_28":434,"_41":435,"_45":436},"picture",{},[437],["SingleFetchClassInstance",438],{"_38":39,"_28":439,"_41":440,"_45":447},"img",{"_441":442,"_443":444,"_445":446},"alt","Successful connection test in Retool","loading","lazy","src","/assets/blog/content/supports-notes-from-the-field/retool-configuration.png",[],["SingleFetchClassInstance",449],{"_38":39,"_28":57,"_41":450,"_45":451},{},[452,453,459,460,465],"The root certificates for Let's Encrypt can be downloaded from ",["SingleFetchClassInstance",454],{"_38":39,"_28":49,"_41":455,"_45":457},{"_51":456},"https://letsencrypt.org/certificates/",[458],"their website",". It is called ISRG Root X1 and you will need to download it in ",["SingleFetchClassInstance",461],{"_38":39,"_28":218,"_41":462,"_45":463},{},[464],".pem"," format.",["SingleFetchClassInstance",467],{"_38":39,"_28":57,"_41":468,"_45":469},{},[470],"For your convenience, below you will find Let's Encrypt's current root certificate:",["SingleFetchClassInstance",472],{"_38":39,"_28":473,"_41":474,"_45":478},"CodeBlock",{"_475":476,"_477":-7},"html","

","language",[],["SingleFetchClassInstance",480],{"_38":39,"_28":57,"_41":481,"_45":482},{},[483],"Please note that if we ever change our SSL provider or update aspects of our SSL/TLS certificate as part of its regular renewal, you would need to update the certificate you have uploaded as well. It's not something that happens often and we do not have plans to change it anytime soon, but if you see a third-party platform having trouble connecting after a few months, make sure to check if our SSL/TLS certificate has changed and update when necessary.",["SingleFetchClassInstance",485],{"_38":39,"_28":57,"_41":486,"_45":487},{},[488],"If you think, after having read through all that SSL/TLS-related information, there's got to be an easier way, then I have got something for you!",["SingleFetchClassInstance",490],{"_38":39,"_28":57,"_41":491,"_45":492},{},[493,494,500,501,507,508,127],"PlanetScale supports data integration engines such as ",["SingleFetchClassInstance",495],{"_38":39,"_28":49,"_41":496,"_45":498},{"_51":497},"/docs/vitess/integrations/airbyte",[499],"Airbyte"," and ",["SingleFetchClassInstance",502],{"_38":39,"_28":49,"_41":503,"_45":505},{"_51":504},"/docs/vitess/integrations/stitch",[506],"Stitch",". And for measuring usage and performance metrics, we also have a ",["SingleFetchClassInstance",509],{"_38":39,"_28":49,"_41":510,"_45":512},{"_51":511},"/docs/vitess/integrations/datadog",[513],"Datadog integration",["SingleFetchClassInstance",515],{"_38":39,"_28":57,"_41":516,"_45":517},{},[518,519,232,524,529],"If you're having trouble connecting your preferred third-party tool, let us know either by ",["SingleFetchClassInstance",520],{"_38":39,"_28":49,"_41":521,"_45":522},{"_51":93},[523],"creating a ticket",["SingleFetchClassInstance",525],{"_38":39,"_28":49,"_41":526,"_45":527},{"_51":100},[528],"opening an issue"," in our discussions board, and we'll look into it together.",["SingleFetchClassInstance",531],{"_38":39,"_28":146,"_41":532,"_45":534},{"_43":533},"data-imports",[535],["SingleFetchClassInstance",536],{"_38":39,"_28":49,"_41":537,"_45":539},{"_51":538},"#data-imports",[540],"Data imports",["SingleFetchClassInstance",542],{"_38":39,"_28":57,"_41":543,"_45":544},{},[545,546,552],"When ramping up PlanetScale, you will eventually get to the point where you want to import your data, and sometimes, this turns out to be more complex than initially thought. Some features, such as stored procedures, are not supported. You can see the complete ",["SingleFetchClassInstance",547],{"_38":39,"_28":49,"_41":548,"_45":550},{"_51":549},"/docs/vitess/troubleshooting/mysql-compatibility",[551],"MySQL/Vitess compatibility list"," to be aware of in the documentation. Still, hardware can also be a limiting factor.",["SingleFetchClassInstance",554],{"_38":39,"_28":57,"_41":555,"_45":556},{},[557],"So that there is no misunderstanding: We take great pride in our ability to scale along with your business, and we will do our absolute best so that you will never have to worry about your database again, but sometimes all it takes to take a database cluster to its limits is a simple, but effective:",["SingleFetchClassInstance",559],{"_38":39,"_28":473,"_41":560,"_45":563},{"_475":561,"_477":562},"

cat backup.sql | pscale shell\n

","bash",[],["SingleFetchClassInstance",565],{"_38":39,"_28":57,"_41":566,"_45":567},{},[568],"It is easy to quickly overwhelm your database with heavy IO operations if you're not careful. A huge influx in concurrent writes to your database will lead to a degraded service, and while our database clusters heal themselves automatically, it's obviously not a great first impression.",["SingleFetchClassInstance",570],{"_38":39,"_28":57,"_41":571,"_45":572},{},[573],"This is why we have built an importer.",["SingleFetchClassInstance",575],{"_38":39,"_28":57,"_41":576,"_45":577},{},[578,584],["SingleFetchClassInstance",579],{"_38":39,"_28":49,"_41":580,"_45":582},{"_51":581},"/docs/vitess/imports/database-imports",[583],"Our importer"," uses MySQL's proven, reliable replication system under the hood. If you go through the import process, your PlanetScale database registers as a replica, making the data copying process trivial and giving you full control over when to make the cut-over.",["SingleFetchClassInstance",586],{"_38":39,"_28":57,"_41":587,"_45":588},{},[589],"You can point your application to your PlanetScale database once it's been registered as a replica. We will route any writes back to the primary until the point where you have elevated PlanetScale to become the primary itself. This gives you the ability to test performance and make any necessary updates before you fully switch over to us, and it is often overlooked.",["SingleFetchClassInstance",591],{"_38":39,"_28":57,"_41":592,"_45":593},{},[594,595,391,600,391,605,232,610,615],"One important caveat is that schema changes do not get replicated between databases in either direction. Please make sure not to execute any DDL (Data Definition Language) operations such as ",["SingleFetchClassInstance",596],{"_38":39,"_28":218,"_41":597,"_45":598},{},[599],"CREATE",["SingleFetchClassInstance",601],{"_38":39,"_28":218,"_41":602,"_45":603},{},[604],"DROP",["SingleFetchClassInstance",606],{"_38":39,"_28":218,"_41":607,"_45":608},{},[609],"ALTER",["SingleFetchClassInstance",611],{"_38":39,"_28":218,"_41":612,"_45":613},{},[614],"TRUNCATE"," while the import is ongoing.",["SingleFetchClassInstance",617],{"_38":39,"_28":57,"_41":618,"_45":619},{},[620,621,627,628,633,634,639,640,232,645,650],"Sometimes, we also see users having issues with their ",["SingleFetchClassInstance",622],{"_38":39,"_28":49,"_41":623,"_45":625},{"_51":624},"/docs/vitess/imports/database-imports#server-configuration-issues",[626],"MySQL server's configuration",". You will need to be able to change your MySQL server's ",["SingleFetchClassInstance",629],{"_38":39,"_28":218,"_41":630,"_45":631},{},[632],"gtid_mode",", its ",["SingleFetchClassInstance",635],{"_38":39,"_28":218,"_41":636,"_45":637},{},[638],"binlog_format",", as well as its expiration times (either ",["SingleFetchClassInstance",641],{"_38":39,"_28":218,"_41":642,"_45":643},{},[644],"expire_logs_days",["SingleFetchClassInstance",646],{"_38":39,"_28":218,"_41":647,"_45":648},{},[649],"binlog_expire_logs_seconds"," or both), otherwise you will not be able to use our importer.",["SingleFetchClassInstance",652],{"_38":39,"_28":57,"_41":653,"_45":654},{},[655,656,232,661,667,668,674,675,127],"If you cannot use our Importer, there are other tools you could use, such as ",["SingleFetchClassInstance",657],{"_38":39,"_28":218,"_41":658,"_45":659},{},[660],"mysqldump",["SingleFetchClassInstance",662],{"_38":39,"_28":49,"_41":663,"_45":665},{"_51":664},"https://www.mysql.com/products/workbench/",[666],"MySQL Workbench",". Another great option is ",["SingleFetchClassInstance",669],{"_38":39,"_28":49,"_41":670,"_45":672},{"_51":671},"https://github.com/aquarapid/go-mydumper",[673],"aquarapid/go-mydumper"," which is based on the excellent gomydumper utility and which was adapted to work with Vitess by my colleague ",["SingleFetchClassInstance",676],{"_38":39,"_28":49,"_41":677,"_45":679},{"_51":678},"https://github.com/aquarapid",[680],"Jacques",["SingleFetchClassInstance",682],{"_38":39,"_28":57,"_41":683,"_45":684},{},[685],"Whatever you end up choosing, if the tool gives you control over concurrency, a good ground rule is to start with a low concurrency rate and to be conservative with increases. Otherwise, depending on the size of your backup, you may end up in the situation described earlier in this section.",["SingleFetchClassInstance",687],{"_38":39,"_28":57,"_41":688,"_45":689},{},[690],"Last but not least, we sometimes also see our users wanting to start over with the import and then running into the following error returned by our connection test:",["SingleFetchClassInstance",692],{"_38":39,"_28":473,"_41":693,"_45":695},{"_475":694,"_477":562},"

Error checking server configuration: found existing Vitess state on the external database\n

",[],["SingleFetchClassInstance",697],{"_38":39,"_28":57,"_41":698,"_45":699},{},[700,701,706,707,711],"This happens because to be able to import your database, we create a temporary database named ",["SingleFetchClassInstance",702],{"_38":39,"_28":218,"_41":703,"_45":704},{},[705],"_vt"," on your current primary. We clean it up automatically when finishing up the import, but if you decide to start over, we cannot automatically detect that. In that case, you will need to drop the ",["SingleFetchClassInstance",708],{"_38":39,"_28":218,"_41":709,"_45":710},{},[705]," database from the current primary first before you can reattempt the import.",["SingleFetchClassInstance",713],{"_38":39,"_28":57,"_41":714,"_45":715},{},[716,717,721,722,726],"Long story short, we know that importing large amounts of data can be daunting and it can be especially frustrating if there's a break somewhere in the process. If you're planning to import terabytes of data, chances are we are already working closely with you. In any other case, if you're running into trouble with your import, please ",["SingleFetchClassInstance",718],{"_38":39,"_28":49,"_41":719,"_45":720},{"_51":93},[95]," with us or ",["SingleFetchClassInstance",723],{"_38":39,"_28":49,"_41":724,"_45":725},{"_51":100},[350]," in our discussions board, and we're happy to get involved.",["SingleFetchClassInstance",728],{"_38":39,"_28":146,"_41":729,"_45":731},{"_43":730},"frequently-hit-timeouts",[732],["SingleFetchClassInstance",733],{"_38":39,"_28":49,"_41":734,"_45":736},{"_51":735},"#frequently-hit-timeouts",[737],"Frequently hit timeouts",["SingleFetchClassInstance",739],{"_38":39,"_28":57,"_41":740,"_45":741},{},[742],"One last common issue you may run into is our configured hard timeouts, mainly our 20 seconds transaction and our 900 seconds query timeout. These are deliberately set timeouts that exist for performance reasons and to encourage good application design. We are looking into ways to lift or at least extend these, but for the time being, these need to be considered hard timeouts.",["SingleFetchClassInstance",744],{"_38":39,"_28":57,"_41":745,"_45":746},{},[747],"When you hit our 20 seconds transaction timeout, you will usually see the following error message:",["SingleFetchClassInstance",749],{"_38":39,"_28":473,"_41":750,"_45":752},{"_475":751,"_477":562},"

vttablet: rpc error: code = Aborted desc = transaction <transaction>: in use: in use: for tx killer rollback (CallerID: planetscale-admin)\n

",[],["SingleFetchClassInstance",754],{"_38":39,"_28":57,"_41":755,"_45":756},{},[757],"When you hit the 900 seconds query timeout instead, you will see an error message similar to this one:",["SingleFetchClassInstance",759],{"_38":39,"_28":473,"_41":760,"_45":762},{"_475":761,"_477":-7},"

target: example-db.-.primary: vttablet: rpc error: code = Canceled desc = (errno 2013) due to context deadline exceeded, elapsed time: 15m0.002989349s, killing query ID 65535 (CallerID: <id>)\n

",[],["SingleFetchClassInstance",764],{"_38":39,"_28":57,"_41":765,"_45":766},{},[767,768,232,773,397,778,783],"These timeouts can be reached with complex transactions or queries, but most of the time, it's rather the user's application keeping the transaction open while handling other tasks such as data manipulation or sorting instead of closing the transaction first. Loops such as ",["SingleFetchClassInstance",769],{"_38":39,"_28":218,"_41":770,"_45":771},{},[772],"while ",["SingleFetchClassInstance",774],{"_38":39,"_28":218,"_41":775,"_45":776},{},[777],"until ",["SingleFetchClassInstance",779],{"_38":39,"_28":218,"_41":780,"_45":781},{},[782],"for"," loops are particularly susceptible to that.",["SingleFetchClassInstance",785],{"_38":39,"_28":57,"_41":786,"_45":787},{},[788],"There is a workaround to lift our configured timeouts and that is to change the workload mode from OLTP (Online transactional processing) to OLAP (Online analytical processing).",["SingleFetchClassInstance",790],{"_38":39,"_28":57,"_41":791,"_45":792},{},[793],"We generally recommend against using it, though, as it can cause rather drastic side effects such as a workload consuming all available resources or blocking other important, short-lived queries or transactions from completing, or overloading a database up to a point where it goes into an unrecoverable state and where manual intervention is needed. It can also block planned failovers or critical updates and will make it easier to hit other intentional limits or timeouts dictated by MySQL.",["SingleFetchClassInstance",795],{"_38":39,"_28":57,"_41":796,"_45":797},{},[798],"Again, we do not recommend changing your database's workload mode. There is almost always a better solution.",["SingleFetchClassInstance",800],{"_38":39,"_28":57,"_41":801,"_45":802},{},[803,804,809],"However, if you still want to try this out, you can switch to OLAP by issuing a ",["SingleFetchClassInstance",805],{"_38":39,"_28":218,"_41":806,"_45":807},{},[808],"set workload='olap';"," on a per-session basis, meaning you would have to directly execute it before running the affected transaction. The workload cannot be changed globally, and it will reset to OLTP after you have closed the session.",["SingleFetchClassInstance",811],{"_38":39,"_28":57,"_41":812,"_45":813},{},[814,815,821,822,828,829,500,833,837],"The best long-term solution still is to optimize your database's schema and your application's transactions and control structures to make its workloads fit into the 20 seconds time window. For simple workloads, consider using ",["SingleFetchClassInstance",816],{"_38":39,"_28":49,"_41":817,"_45":819},{"_51":818},"https://nickolasfisher.com/blog/Optimistic-Locking-in-MySQLExplain-Like-Im-Five",[820],"optimistic locking"," instead of transactions, and for more complex workloads, consider adopting ",["SingleFetchClassInstance",823],{"_38":39,"_28":49,"_41":824,"_45":826},{"_51":825},"https://livebook.manning.com/book/microservices-patterns/chapter-4/",[827],"Sagas",". For large ETL workloads, we support data integration engines such as ",["SingleFetchClassInstance",830],{"_38":39,"_28":49,"_41":831,"_45":832},{"_51":497},[499],["SingleFetchClassInstance",834],{"_38":39,"_28":49,"_41":835,"_45":836},{"_51":504},[506],", with which you can offload these processes to other platforms that are more specialized in this field.",["SingleFetchClassInstance",839],{"_38":39,"_28":57,"_41":840,"_45":841},{},[842,843,849,850,721,854,351],"To help you with optimizing your queries and transactions, PlanetScale provides you with additional tools such as ",["SingleFetchClassInstance",844],{"_38":39,"_28":49,"_41":845,"_45":847},{"_51":846},"/docs/vitess/monitoring/query-insights",[848],"Insights",". And, if you need a hand with any of this, please ",["SingleFetchClassInstance",851],{"_38":39,"_28":49,"_41":852,"_45":853},{"_51":93},[95],["SingleFetchClassInstance",855],{"_38":39,"_28":49,"_41":856,"_45":857},{"_51":100},[350],"body_text","Support at PlanetScale\nThe Support team at PlanetScale is a multi-cultural, multi-national team distributed around the globe, enabling us to support our users in almost any time zone on this planet. Internally, we are organized into two separate groups: Our Customer Engineering team working closely with our Enterprise customers and our Cloud Support team supporting our Base plan customers.\nAll of us have a technical background. Some of us develop software and contribute to FOSS software or have been building and maintaining infrastructure at scale for years. Others have worked on large support teams at companies such as GitHub, Google, and Microsoft.\nOur combined collective experience in the industry racks up to a couple hundred years of knowledge. When you approach us with your database-centric problems, chances are we will be able to help swiftly resolve the issue or at least put you on the right track to get there yourself. And if it's something totally new, well, kudos to you! We are about to learn something new, and we'll be learning together.\nIf you want to get in touch with us, the easiest way is to open a ticket in our support portal or to start a new conversation in our public discussions board on GitHub. For our Enterprise customers, we also offer phone-based escalations and joint Slack channels where we can collaborate.\nFor a good overview about the different support plans, support targets, and service-level agreements (SLAs), please review our official documentation and our support portal.\nIn the rest of this article, we'll take a look at the most common places we see folks hit a wall.\nCommon issues users run into on PlanetScale\nSSL/TLS certificate errors with PlanetScale\nSSL/TLS certificate verification errors come in various forms, but what they all have in common is that they prevent you from connecting to your PlanetScale database, so it is immediately noticeable and a hard blocker for some. Most of the time, however, these errors are straightforward to resolve.\nTo understand what leads to these errors, it is good to have a basic understanding of how SSL/TLS certificates work.\nHow SSL/TLS certificates work\nIt all starts with a certificate authority (CA). A CA issues, signs, and stores SSL/TLS certificates. They exist so that a client can connect to a server securely and that network traffic between the two entities gets encrypted. You can set up your own CA and issue your own certificates, but for your certificates to be automatically verified in modern browsers, companies such as ours need to use a certificate that was issued by a common third-party CA.\nPlanetScale's SSL/TLS certificate was issued by Let's Encrypt, a nonprofit CA run by the Internet Security Research Group (ISRG), which has issued certificates for more than 300 million websites to date.\nWhen connecting to PlanetScale, your computer validates our SSL/TLS certificate by looking up the issuer of our certificate and comparing it against the trusted root certificate authorities store on your computer. When it finds a match, it checks if our certificate is valid and signed by the CA. If that turns out to be OK, it lets you connect securely.\nMost modern operating systems have a root CA store, and your software usually knows where it is:\nOn Mac OS X, the store is in /etc/ssl/cert.pem\nOn Linux, it depends on the distribution, but it almost always is either in /etc/ssl or /etc/pki\nOn Windows, the root CA store is an internal database available via the CryptoAPI\nSolving SSL/TLS errors\nMost SSL/TLS errors that we see occur because of these trusted CA root certificates not being installed on the computer and, therefore, the software being unable to verify our certificate. On Linux, you can often solve this by installing the ca-certificates package. This package bundles the CA certificates from the Mozilla CA Certificate Program.\nMost database drivers know where to find these locally installed CA certificates, but sometimes you may need to point the database driver to it. We have the most common paths for the root CA store listed in our documentation, along with a more thorough explanation of how SSL/TLS works.\nOther SSL/TLS-related errors can occur due to libssl being too old, or other libraries in use which have not been compiled with SSL support or that simply do not offer any SSL/TLS support at all. If you're interested to learn more about such a scenario, we had an interesting case with Debian Buster in our public discussions board a few weeks ago.\nIn any case, things that you should NOT be doing:\nDisable SSL/TLS certificate verification\nIssue and sign your own SSL certificate\nDisabling certificate verification will make your connection susceptible to man-in-the-middle attacks. This is a cyberattack where an attacker secretly relays and possibly alters the communication between two parties. In order for your communication with our servers to be secure, you must not disable it!\nWe also sometimes see users creating their own CA and SSL certificates and then sending us their certificates as well as the matching private key. You will never ever need to do that when using PlanetScale! It's an easy trap to fall into if you don't know enough about SSL/TLS and, from experience, it can get overwhelming and confusing quickly.\nWhatever the nature of your SSL/TLS issue is, we will be happy to help get to the bottom of it if you open a ticket with us or if you open an issue in our discussions board.\nIntegrating third-party platforms\nSometimes, you need to integrate third-party platforms such as Google's Data Studio or Retool, but when your preferred platform does not support the common SSL/TLS certificate authorities' root certificates, you can run into trouble establishing a connection to your PlanetScale database.\nWhen you enable SSL/TLS in your preferred tool, you sometimes stumble upon fields such as CA Cert, Client Key, or Client Cert, which normally only exist for the user to add their organization's self-signed SSL/TLS certificate. Remember, that's the case where you act as your own certificate authority and issue your own SSL/TLS certificates, which is not needed in the context of PlanetScale.\nIf you leave these fields blank and hope for the best, you normally encounter an error message such as this one:\nUnable to get local issuer's certificate.\nWe can make use of these fields, though, to work around the issue. When there is a field where you can upload a CA Cert, you can upload Let's Encrypt's CA root certificate or, if it's a text field, paste it in. It is a regular text file after all.\n\nThe root certificates for Let's Encrypt can be downloaded from their website. It is called ISRG Root X1 and you will need to download it in .pem format.\nFor your convenience, below you will find Let's Encrypt's current root certificate:-----BEGIN CERTIFICATE-----\nMIIFazCCA1OgAwIBAgIRAIIQz7DSQONZRGPgu2OCiwAwDQYJKoZIhvcNAQELBQAw\nTzELMAkGA1UEBhMCVVMxKTAnBgNVBAoTIEludGVybmV0IFNlY3VyaXR5IFJlc2Vh\ncmNoIEdyb3VwMRUwEwYDVQQDEwxJU1JHIFJvb3QgWDEwHhcNMTUwNjA0MTEwNDM4\nWhcNMzUwNjA0MTEwNDM4WjBPMQswCQYDVQQGEwJVUzEpMCcGA1UEChMgSW50ZXJu\nZXQgU2VjdXJpdHkgUmVzZWFyY2ggR3JvdXAxFTATBgNVBAMTDElTUkcgUm9vdCBY\nMTCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAK3oJHP0FDfzm54rVygc\nh77ct984kIxuPOZXoHj3dcKi/vVqbvYATyjb3miGbESTtrFj/RQSa78f0uoxmyF+\n0TM8ukj13Xnfs7j/EvEhmkvBioZxaUpmZmyPfjxwv60pIgbz5MDmgK7iS4+3mX6U\nA5/TR5d8mUgjU+g4rk8Kb4Mu0UlXjIB0ttov0DiNewNwIRt18jA8+o+u3dpjq+sW\nT8KOEUt+zwvo/7V3LvSye0rgTBIlDHCNAymg4VMk7BPZ7hm/ELNKjD+Jo2FR3qyH\nB5T0Y3HsLuJvW5iB4YlcNHlsdu87kGJ55tukmi8mxdAQ4Q7e2RCOFvu396j3x+UC\nB5iPNgiV5+I3lg02dZ77DnKxHZu8A/lJBdiB3QW0KtZB6awBdpUKD9jf1b0SHzUv\nKBds0pjBqAlkd25HN7rOrFleaJ1/ctaJxQZBKT5ZPt0m9STJEadao0xAH0ahmbWn\nOlFuhjuefXKnEgV4We0+UXgVCwOPjdAvBbI+e0ocS3MFEvzG6uBQE3xDk3SzynTn\njh8BCNAw1FtxNrQHusEwMFxIt4I7mKZ9YIqioymCzLq9gwQbooMDQaHWBfEbwrbw\nqHyGO0aoSCqI3Haadr8faqU9GY/rOPNk3sgrDQoo//fb4hVC1CLQJ13hef4Y53CI\nrU7m2Ys6xt0nUW7/vGT1M0NPAgMBAAGjQjBAMA4GA1UdDwEB/wQEAwIBBjAPBgNV\nHRMBAf8EBTADAQH/MB0GA1UdDgQWBBR5tFnme7bl5AFzgAiIyBpY9umbbjANBgkq\nhkiG9w0BAQsFAAOCAgEAVR9YqbyyqFDQDLHYGmkgJykIrGF1XIpu+ILlaS/V9lZL\nubhzEFnTIZd+50xx+7LSYK05qAvqFyFWhfFQDlnrzuBZ6brJFe+GnY+EgPbk6ZGQ\n3BebYhtF8GaV0nxvwuo77x/Py9auJ/GpsMiu/X1+mvoiBOv/2X/qkSsisRcOj/KK\nNFtY2PwByVS5uCbMiogziUwthDyC3+6WVwW6LLv3xLfHTjuCvjHIInNzktHCgKQ5\nORAzI4JMPJ+GslWYHb4phowim57iaztXOoJwTdwJx4nLCgdNbOhdjsnvzqvHu7Ur\nTkXWStAmzOVyyghqpZXjFaH3pO3JLF+l+/+sKAIuvtd7u+Nxe5AW0wdeRlN8NwdC\njNPElpzVmbUq4JUagEiuTDkHzsxHpFKVK7q4+63SM1N95R1NbdWhscdCb+ZAJzVc\noyi3B43njTOQ5yOf+1CceWxG1bQVs5ZufpsMljq4Ui0/1lvh+wjChP4kqKOJ2qxq\n4RgqsahDYVvTH9w7jXbyLeiNdd8XM2w9U/t7y0Ff/9yi0GE44Za4rF2LN9d11TPA\nmRGunUHBcnWEvgJBQl9nJEiU0Zsnvgc/ubhPgXRR4Xq37Z0j4r7g1SgEEzwxA57d\nemyPxgcYxn/eR44/KJ4EBs+lVDR3veyJm+kXQ99b21/+jh5Xos1AnX5iItreGCc=\n-----END CERTIFICATE-----\n\nPlease note that if we ever change our SSL provider or update aspects of our SSL/TLS certificate as part of its regular renewal, you would need to update the certificate you have uploaded as well. It's not something that happens often and we do not have plans to change it anytime soon, but if you see a third-party platform having trouble connecting after a few months, make sure to check if our SSL/TLS certificate has changed and update when necessary.\nIf you think, after having read through all that SSL/TLS-related information, there's got to be an easier way, then I have got something for you!\nPlanetScale supports data integration engines such as Airbyte and Stitch. And for measuring usage and performance metrics, we also have a Datadog integration.\nIf you're having trouble connecting your preferred third-party tool, let us know either by creating a ticket or opening an issue in our discussions board, and we'll look into it together.\nData imports\nWhen ramping up PlanetScale, you will eventually get to the point where you want to import your data, and sometimes, this turns out to be more complex than initially thought. Some features, such as stored procedures, are not supported. You can see the complete MySQL/Vitess compatibility list to be aware of in the documentation. Still, hardware can also be a limiting factor.\nSo that there is no misunderstanding: We take great pride in our ability to scale along with your business, and we will do our absolute best so that you will never have to worry about your database again, but sometimes all it takes to take a database cluster to its limits is a simple, but effective:cat backup.sql | pscale shell\n\nIt is easy to quickly overwhelm your database with heavy IO operations if you're not careful. A huge influx in concurrent writes to your database will lead to a degraded service, and while our database clusters heal themselves automatically, it's obviously not a great first impression.\nThis is why we have built an importer.\nOur importer uses MySQL's proven, reliable replication system under the hood. If you go through the import process, your PlanetScale database registers as a replica, making the data copying process trivial and giving you full control over when to make the cut-over.\nYou can point your application to your PlanetScale database once it's been registered as a replica. We will route any writes back to the primary until the point where you have elevated PlanetScale to become the primary itself. This gives you the ability to test performance and make any necessary updates before you fully switch over to us, and it is often overlooked.\nOne important caveat is that schema changes do not get replicated between databases in either direction. Please make sure not to execute any DDL (Data Definition Language) operations such as CREATE, DROP, ALTER or TRUNCATE while the import is ongoing.\nSometimes, we also see users having issues with their MySQL server's configuration. You will need to be able to change your MySQL server's gtid_mode, its binlog_format, as well as its expiration times (either expire_logs_days or binlog_expire_logs_seconds or both), otherwise you will not be able to use our importer.\nIf you cannot use our Importer, there are other tools you could use, such as mysqldump or MySQL Workbench. Another great option is aquarapid/go-mydumper which is based on the excellent gomydumper utility and which was adapted to work with Vitess by my colleague Jacques.\nWhatever you end up choosing, if the tool gives you control over concurrency, a good ground rule is to start with a low concurrency rate and to be conservative with increases. Otherwise, depending on the size of your backup, you may end up in the situation described earlier in this section.\nLast but not least, we sometimes also see our users wanting to start over with the import and then running into the following error returned by our connection test:Error checking server configuration: found existing Vitess state on the external database\n\nThis happens because to be able to import your database, we create a temporary database named _vt on your current primary. We clean it up automatically when finishing up the import, but if you decide to start over, we cannot automatically detect that. In that case, you will need to drop the _vt database from the current primary first before you can reattempt the import.\nLong story short, we know that importing large amounts of data can be daunting and it can be especially frustrating if there's a break somewhere in the process. If you're planning to import terabytes of data, chances are we are already working closely with you. In any other case, if you're running into trouble with your import, please open a ticket with us or open an issue in our discussions board, and we're happy to get involved.\nFrequently hit timeouts\nOne last common issue you may run into is our configured hard timeouts, mainly our 20 seconds transaction and our 900 seconds query timeout. These are deliberately set timeouts that exist for performance reasons and to encourage good application design. We are looking into ways to lift or at least extend these, but for the time being, these need to be considered hard timeouts.\nWhen you hit our 20 seconds transaction timeout, you will usually see the following error message:vttablet: rpc error: code = Aborted desc = transaction : in use: in use: for tx killer rollback (CallerID: planetscale-admin)\n\nWhen you hit the 900 seconds query timeout instead, you will see an error message similar to this one:target: example-db.-.primary: vttablet: rpc error: code = Canceled desc = (errno 2013) due to context deadline exceeded, elapsed time: 15m0.002989349s, killing query ID 65535 (CallerID: )\n\nThese timeouts can be reached with complex transactions or queries, but most of the time, it's rather the user's application keeping the transaction open while handling other tasks such as data manipulation or sorting instead of closing the transaction first. Loops such as while or until , or for loops are particularly susceptible to that.\nThere is a workaround to lift our configured timeouts and that is to change the workload mode from OLTP (Online transactional processing) to OLAP (Online analytical processing).\nWe generally recommend against using it, though, as it can cause rather drastic side effects such as a workload consuming all available resources or blocking other important, short-lived queries or transactions from completing, or overloading a database up to a point where it goes into an unrecoverable state and where manual intervention is needed. It can also block planned failovers or critical updates and will make it easier to hit other intentional limits or timeouts dictated by MySQL.\nAgain, we do not recommend changing your database's workload mode. There is almost always a better solution.\nHowever, if you still want to try this out, you can switch to OLAP by issuing a set workload='olap'; on a per-session basis, meaning you would have to directly execute it before running the affected transaction. The workload cannot be changed globally, and it will reset to OLTP after you have closed the session.\nThe best long-term solution still is to optimize your database's schema and your application's transactions and control structures to make its workloads fit into the 20 seconds time window. For simple workloads, consider using optimistic locking instead of transactions, and for more complex workloads, consider adopting Sagas. For large ETL workloads, we support data integration engines such as Airbyte and Stitch, with which you can offload these processes to other platforms that are more specialized in this field.\nTo help you with optimizing your queries and transactions, PlanetScale provides you with additional tools such as Insights. And, if you need a hand with any of this, please open a ticket with us or open an issue in our discussions board.","aside","toc",[863,868],{"_45":864,"_43":44,"_865":866,"_867":54},[],"level",2,"title",{"_45":869,"_43":136,"_865":866,"_867":143},[870,873,875],{"_45":871,"_43":148,"_865":872,"_867":155},[],3,{"_45":874,"_43":533,"_865":872,"_867":540},[],{"_45":876,"_43":730,"_865":872,"_867":737},[],"Support’s notes from the field","authors",[880],{"_26":27,"_28":29,"_881":-7},"x","categories",[884],"tutorials","excerpt","A quick glimpse on Support at PlanetScale and the issues we see the most often.","createdAt","2023-01-11T14:45:00.000Z","meta",{"_891":892,"_893":886,"_894":895,"_867":877},"canonical","https://planetscale.com/blog/supports-notes-from-the-field","description","image","/assets/supports-notes-from-the-field-social-DmhXlGQn.jpg",{"_26":897,"_34":898,"_858":1417,"_860":-7,"_861":1418,"_867":1432,"_878":1433,"_882":1435,"_885":1437,"_887":1438,"_889":1439},"the-mysql-json-data-type",[899,910,915,932,938,943,959,964,969,974,998,1003,1014,1019,1044,1065,1077,1101,1106,1117,1127,1138,1143,1158,1164,1175,1180,1190,1195,1206,1211,1226,1231,1247,1252,1268,1273,1291,1302,1329,1334],["SingleFetchClassInstance",900],{"_38":39,"_28":40,"_41":901,"_45":903},{"_43":902},"overview",[904],["SingleFetchClassInstance",905],{"_38":39,"_28":49,"_41":906,"_45":908},{"_51":907},"#overview",[909],"Overview",["SingleFetchClassInstance",911],{"_38":39,"_28":57,"_41":912,"_45":913},{},[914],"JavaScript Object Notation (JSON) is a light-weight text-based file format similar to YAML or XML which simplifies data exchange. It was invented by Douglas Crockford in the early 2000s and became increasingly popular with the rise of document-based (also called NoSQL) databases.",["SingleFetchClassInstance",916],{"_38":39,"_28":57,"_41":917,"_45":918},{},[919,920,925,926,931],"JSON supports strings, numbers, booleans, objects, and arrays as well as null values. A simple JSON example containing key-value pairs, an object ",["SingleFetchClassInstance",921],{"_38":39,"_28":218,"_41":922,"_45":923},{},[924],"\"bandMembers\""," and an array ",["SingleFetchClassInstance",927],{"_38":39,"_28":218,"_41":928,"_45":929},{},[930],"\"songs\""," would look like this:",["SingleFetchClassInstance",933],{"_38":39,"_28":473,"_41":934,"_45":937},{"_475":935,"_477":936},"

{\n  \"artist\": \"Starlord Band\",\n  \"bandMembers\": {\n    \"vocals\": \"Steve Szczepkowski\",\n    \"guitar\": \"Yohann Boudreault\",\n    \"bass\": \"Yannick T.\",\n    \"drums\": \"Vince T.\"\n  },\n  \"bandMembersCount\": 4,\n  \"album\": \"Space Rider\",\n  \"releaseDate\": \"2021-10-25\",\n  \"songs\": [\n    \"Zero to Hero\",\n    \"Space Riders with No Names\",\n    \"Ghost\",\n    \"Bit of Good (Bit of Bad)\",\n    \"Watch me shine\",\n    \"We’re Here\",\n    \"The Darkness inside\",\n    \"No Guts No Glory\",\n    \"All for One\",\n    \"Solar Skies\"\n  ],\n  \"songsCount\": 10\n}\n

","json",[],["SingleFetchClassInstance",939],{"_38":39,"_28":57,"_41":940,"_45":941},{},[942],"MySQL has implemented rudimentary support for the JSON data type with version 5.7.8 in mid 2015 and has been adding improvements and new features ever since. Seven years later, MySQL now supports numerous SQL functions to work with JSON documents, it provides automatic content validation, allows for partial in-place updates, and uses a binary storage format for increased performance.",["SingleFetchClassInstance",944],{"_38":39,"_28":40,"_41":945,"_45":947},{"_43":946},"when-to-use-json",[948],["SingleFetchClassInstance",949],{"_38":39,"_28":49,"_41":950,"_45":952},{"_51":951},"#when-to-use-json",[953,954],"When to use ",["SingleFetchClassInstance",955],{"_38":39,"_28":218,"_41":956,"_45":957},{},[958],"JSON",["SingleFetchClassInstance",960],{"_38":39,"_28":57,"_41":961,"_45":962},{},[963],"Relational databases follow a predetermined structure and put emphasis on data cohesion and integrity. To achieve this, its data types and formats, as well as its data size, are all being enforced rigorously by the means of a schema.",["SingleFetchClassInstance",965],{"_38":39,"_28":57,"_41":966,"_45":967},{},[968],"The JSON data type is a bit of an anti-pattern to the rigorousness nature of such a schema. It allows you to break out of it, to gain flexibility when you need it. And it proves useful as long as you're aware of the trade-offs described in the next section.",["SingleFetchClassInstance",970],{"_38":39,"_28":57,"_41":971,"_45":972},{},[973],"Some examples of when it may be beneficial to store data as a JSON document are:",["SingleFetchClassInstance",975],{"_38":39,"_28":207,"_41":976,"_45":977},{},[978,983,988,993],["SingleFetchClassInstance",979],{"_38":39,"_28":212,"_41":980,"_45":981},{},[982],"Log output written by an application or a server",["SingleFetchClassInstance",984],{"_38":39,"_28":212,"_41":985,"_45":986},{},[987],"A Rest API response you want to store",["SingleFetchClassInstance",989],{"_38":39,"_28":212,"_41":990,"_45":991},{},[992],"Storing configuration data",["SingleFetchClassInstance",994],{"_38":39,"_28":212,"_41":995,"_45":996},{},[997],"A set of entities with variable attributes",["SingleFetchClassInstance",999],{"_38":39,"_28":57,"_41":1000,"_45":1001},{},[1002],"You can also use JSON documents in your relational database design to break up complex relations spanning across multiple tables. This process is called denormalization, which is another relational database anti-pattern. In certain cases, however, it can result in performance improvements depending on your use case and application design.",["SingleFetchClassInstance",1004],{"_38":39,"_28":40,"_41":1005,"_45":1007},{"_43":1006},"caveats",[1008],["SingleFetchClassInstance",1009],{"_38":39,"_28":49,"_41":1010,"_45":1012},{"_51":1011},"#caveats",[1013],"Caveats",["SingleFetchClassInstance",1015],{"_38":39,"_28":57,"_41":1016,"_45":1017},{},[1018],"The flexibility provided by the JSON data type comes with a few caveats you will need to be aware of.",["SingleFetchClassInstance",1020],{"_38":39,"_28":57,"_41":1021,"_45":1022},{},[1023,1024,232,1029,1034,1035,232,1039,1043],"Most notably, you will need to factor in that JSON documents often require more storage capacity. In MySQL, their storage footprint is similar to the ",["SingleFetchClassInstance",1025],{"_38":39,"_28":218,"_41":1026,"_45":1027},{},[1028],"LONGBLOB",["SingleFetchClassInstance",1030],{"_38":39,"_28":218,"_41":1031,"_45":1032},{},[1033],"LONGTEXT"," data type. There is an overhead, though, due to the binary enconding and the added metadata and dictionaries which exist to speed up database reads. A good rule of thumb is that a string stored in JSON uses roughly 4 to 10 bytes of additional storage compared to a ",["SingleFetchClassInstance",1036],{"_38":39,"_28":218,"_41":1037,"_45":1038},{},[1028],["SingleFetchClassInstance",1040],{"_38":39,"_28":218,"_41":1041,"_45":1042},{},[1033]," column.",["SingleFetchClassInstance",1045],{"_38":39,"_28":57,"_41":1046,"_45":1047},{},[1048,1049,391,1054,391,1059,1064],"If you want to optimize your database schema towards storage efficiency, it is best to go with MySQL's more traditional data types (",["SingleFetchClassInstance",1050],{"_38":39,"_28":218,"_41":1051,"_45":1052},{},[1053],"CHAR",["SingleFetchClassInstance",1055],{"_38":39,"_28":218,"_41":1056,"_45":1057},{},[1058],"VARCHAR",["SingleFetchClassInstance",1060],{"_38":39,"_28":218,"_41":1061,"_45":1062},{},[1063],"INT",", and alike), as they are all more storage-efficient than JSON can likely ever be.",["SingleFetchClassInstance",1066],{"_38":39,"_28":57,"_41":1067,"_45":1068},{},[1069,1070,1076],"Another caveat to be aware of is the performance impact. Similar to other binary formats, JSON documents cannot be indexed ",["SingleFetchClassInstance",1071],{"_38":39,"_28":1072,"_41":1073,"_45":1074},"em",{},[1075],"directly",". This, and the variable amount of data you can store in a JSON document, means that querying a JSON column often uses more buffer space and returns larger result sets, leading to more data exchange.",["SingleFetchClassInstance",1078],{"_38":39,"_28":1079,"_41":1080,"_45":1083},"Callout",{"_1081":1082},"type","note",[1084],["SingleFetchClassInstance",1085],{"_38":39,"_28":57,"_41":1086,"_45":1087},{},[1088,1089,1094,1095,127],"While JSON documents cannot be indexed directly in MySQL, they ",["SingleFetchClassInstance",1090],{"_38":39,"_28":1072,"_41":1091,"_45":1092},{},[1093],"can"," be indexed indirectly. Learn how in ",["SingleFetchClassInstance",1096],{"_38":39,"_28":49,"_41":1097,"_45":1099},{"_51":1098},"/blog/indexing-json-in-mysql",[1100],"Indexing JSON in MYSQL",["SingleFetchClassInstance",1102],{"_38":39,"_28":57,"_41":1103,"_45":1104},{},[1105],"While a JSON document stored in MySQL can be up to 1 GB, in theory, it is recommended to keep JSON documents to only a few MB in size. On PlanetScale, we support JSON documents up to 67 MB.",["SingleFetchClassInstance",1107],{"_38":39,"_28":40,"_41":1108,"_45":1110},{"_43":1109},"json-functions",[1111],["SingleFetchClassInstance",1112],{"_38":39,"_28":49,"_41":1113,"_45":1115},{"_51":1114},"#json-functions",[1116],"JSON functions",["SingleFetchClassInstance",1118],{"_38":39,"_28":57,"_41":1119,"_45":1120},{},[1121,1122,127],"MySQL comes with a robust set of JSON functions enabling you to create, update, read, or validate your JSON documents. PlanetScale supports all of the JSON functions except ",["SingleFetchClassInstance",1123],{"_38":39,"_28":218,"_41":1124,"_45":1125},{},[1126],"JSON_TABLE",["SingleFetchClassInstance",1128],{"_38":39,"_28":40,"_41":1129,"_45":1131},{"_43":1130},"common-operations",[1132],["SingleFetchClassInstance",1133],{"_38":39,"_28":49,"_41":1134,"_45":1136},{"_51":1135},"#common-operations",[1137],"Common operations",["SingleFetchClassInstance",1139],{"_38":39,"_28":57,"_41":1140,"_45":1141},{},[1142],"Let’s walk through a few examples together.",["SingleFetchClassInstance",1144],{"_38":39,"_28":57,"_41":1145,"_45":1146},{},[1147,1148,1153,1154,1043],"First, we create a table with an ",["SingleFetchClassInstance",1149],{"_38":39,"_28":218,"_41":1150,"_45":1151},{},[1152],"INTEGER"," and a ",["SingleFetchClassInstance",1155],{"_38":39,"_28":218,"_41":1156,"_45":1157},{},[958],["SingleFetchClassInstance",1159],{"_38":39,"_28":473,"_41":1160,"_45":1163},{"_475":1161,"_477":1162},"

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);\n

","sql",[],["SingleFetchClassInstance",1165],{"_38":39,"_28":57,"_41":1166,"_45":1167},{},[1168,1169,1174],"An empty table needs data, so let’s use ",["SingleFetchClassInstance",1170],{"_38":39,"_28":218,"_41":1171,"_45":1172},{},[1173],"JSON_ARRAY"," to add some.",["SingleFetchClassInstance",1176],{"_38":39,"_28":473,"_41":1177,"_45":1179},{"_475":1178,"_477":1162},"

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));\n

",[],["SingleFetchClassInstance",1181],{"_38":39,"_28":57,"_41":1182,"_45":1183},{},[1184,1185,127],"How do we know this is an array? Well, we can get its type by using ",["SingleFetchClassInstance",1186],{"_38":39,"_28":218,"_41":1187,"_45":1188},{},[1189],"JSON_TYPE",["SingleFetchClassInstance",1191],{"_38":39,"_28":473,"_41":1192,"_45":1194},{"_475":1193,"_477":1162},"

SELECT JSON_TYPE(songs) FROM songs;\n+------------------+\n| json_type(songs) |\n+------------------+\n| ARRAY            |\n+------------------+\n

",[],["SingleFetchClassInstance",1196],{"_38":39,"_28":57,"_41":1197,"_45":1198},{},[1199,1200,1205],"If we want to extract an item from the array, we can do so with ",["SingleFetchClassInstance",1201],{"_38":39,"_28":218,"_41":1202,"_45":1203},{},[1204],"JSON_EXTRACT",". In the below example, we extract the fourth element from the array.",["SingleFetchClassInstance",1207],{"_38":39,"_28":473,"_41":1208,"_45":1210},{"_475":1209,"_477":1162},"

blog-mysql-json/main> SELECT JSON_EXTRACT(songs, '$[3]') FROM songs;\n+-----------------------------+\n| json_extract(songs, '$[3]') |\n+-----------------------------+\n| \"Ghost\"                     |\n+-----------------------------+\n

",[],["SingleFetchClassInstance",1212],{"_38":39,"_28":57,"_41":1213,"_45":1214},{},[1215,1216,1221,1222,127],"We can also use ",["SingleFetchClassInstance",1217],{"_38":39,"_28":218,"_41":1218,"_45":1219},{},[1220],"->",", which is the operator equivalent for ",["SingleFetchClassInstance",1223],{"_38":39,"_28":218,"_41":1224,"_45":1225},{},[1204],["SingleFetchClassInstance",1227],{"_38":39,"_28":473,"_41":1228,"_45":1230},{"_475":1229,"_477":1162},"

blog-mysql-json/main> SELECT songs->'$[3]' FROM songs;\n+-----------------+\n| songs -> '$[3]' |\n+-----------------+\n| \"Ghost\"         |\n+-----------------+\n

",[],["SingleFetchClassInstance",1232],{"_38":39,"_28":57,"_41":1233,"_45":1234},{},[1235,1236,1241,1242,127],"If we need the unquoted result, we can use ",["SingleFetchClassInstance",1237],{"_38":39,"_28":218,"_41":1238,"_45":1239},{},[1240],"->>",", which is short for ",["SingleFetchClassInstance",1243],{"_38":39,"_28":218,"_41":1244,"_45":1245},{},[1246],"JSON_UNQUOTE(JSON_EXTRACT())",["SingleFetchClassInstance",1248],{"_38":39,"_28":473,"_41":1249,"_45":1251},{"_475":1250,"_477":1162},"

blog-mysql-json/main> SELECT songs->>'$[3]' FROM songs;\n+------------------+\n| songs ->> '$[3]' |\n+------------------+\n| Ghost            |\n+------------------+\n

",[],["SingleFetchClassInstance",1253],{"_38":39,"_28":57,"_41":1254,"_45":1255},{},[1256,1257,232,1262,1267],"If we need to add data to the JSON array, we can use ",["SingleFetchClassInstance",1258],{"_38":39,"_28":218,"_41":1259,"_45":1260},{},[1261],"JSON_ARRAY_APPEND",["SingleFetchClassInstance",1263],{"_38":39,"_28":218,"_41":1264,"_45":1265},{},[1266],"JSON_ARRAY_INSERT"," to update it.",["SingleFetchClassInstance",1269],{"_38":39,"_28":473,"_41":1270,"_45":1272},{"_475":1271,"_477":1162},"

UPDATE songs SET songs = JSON_ARRAY_APPEND(songs, '$', \"One last song\");\nUPDATE songs SET songs = JSON_ARRAY_INSERT(songs, '$[0]', \"First song\");\n

",[],["SingleFetchClassInstance",1274],{"_38":39,"_28":57,"_41":1275,"_45":1276},{},[1277,1278,1284,1285,127],"For more information on how to use all the different JSON functions, please see MySQL's documentation for the ",["SingleFetchClassInstance",1279],{"_38":39,"_28":49,"_41":1280,"_45":1282},{"_51":1281},"https://dev.mysql.com/doc/refman/8.0/en/json.html",[1283],"JSON data type"," and the ",["SingleFetchClassInstance",1286],{"_38":39,"_28":49,"_41":1287,"_45":1289},{"_51":1288},"https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html",[1290],"JSON Function reference",["SingleFetchClassInstance",1292],{"_38":39,"_28":40,"_41":1293,"_45":1295},{"_43":1294},"further-learning",[1296],["SingleFetchClassInstance",1297],{"_38":39,"_28":49,"_41":1298,"_45":1300},{"_51":1299},"#further-learning",[1301],"Further learning",["SingleFetchClassInstance",1303],{"_38":39,"_28":57,"_41":1304,"_45":1305},{},[1306,1307,1318,1319,1328],"If you'd like to learn more about data types in MySQL, we have an article on ",["SingleFetchClassInstance",1308],{"_38":39,"_28":49,"_41":1309,"_45":1311},{"_51":1310},"/blog/mysql-data-types-integers",[1312,1313,1317],"the ",["SingleFetchClassInstance",1314],{"_38":39,"_28":218,"_41":1315,"_45":1316},{},[1063]," data type"," and one on the ",["SingleFetchClassInstance",1320],{"_38":39,"_28":49,"_41":1321,"_45":1323},{"_51":1322},"/blog/mysql-data-types-varchar-and-char",[1324,1317],["SingleFetchClassInstance",1325],{"_38":39,"_28":218,"_41":1326,"_45":1327},{},[1058]," that you may find useful.",["SingleFetchClassInstance",1330],{"_38":39,"_28":57,"_41":1331,"_45":1332},{},[1333],"We also have short videos on the following data types:",["SingleFetchClassInstance",1335],{"_38":39,"_28":207,"_41":1336,"_45":1337},{},[1338,1348,1358,1368,1378,1388,1398,1408],["SingleFetchClassInstance",1339],{"_38":39,"_28":212,"_41":1340,"_45":1341},{},[1342],["SingleFetchClassInstance",1343],{"_38":39,"_28":49,"_41":1344,"_45":1346},{"_51":1345},"/learn/courses/mysql-for-developers/schema/integers",[1347],"Integers",["SingleFetchClassInstance",1349],{"_38":39,"_28":212,"_41":1350,"_45":1351},{},[1352],["SingleFetchClassInstance",1353],{"_38":39,"_28":49,"_41":1354,"_45":1356},{"_51":1355},"/learn/courses/mysql-for-developers/schema/decimals",[1357],"Decimals",["SingleFetchClassInstance",1359],{"_38":39,"_28":212,"_41":1360,"_45":1361},{},[1362],["SingleFetchClassInstance",1363],{"_38":39,"_28":49,"_41":1364,"_45":1366},{"_51":1365},"/learn/courses/mysql-for-developers/schema/strings",[1367],"Strings",["SingleFetchClassInstance",1369],{"_38":39,"_28":212,"_41":1370,"_45":1371},{},[1372],["SingleFetchClassInstance",1373],{"_38":39,"_28":49,"_41":1374,"_45":1376},{"_51":1375},"/learn/courses/mysql-for-developers/schema/binary-strings",[1377],"Binary Strings",["SingleFetchClassInstance",1379],{"_38":39,"_28":212,"_41":1380,"_45":1381},{},[1382],["SingleFetchClassInstance",1383],{"_38":39,"_28":49,"_41":1384,"_45":1386},{"_51":1385},"/learn/courses/mysql-for-developers/schema/long-strings",[1387],"Long Strings",["SingleFetchClassInstance",1389],{"_38":39,"_28":212,"_41":1390,"_45":1391},{},[1392],["SingleFetchClassInstance",1393],{"_38":39,"_28":49,"_41":1394,"_45":1396},{"_51":1395},"/learn/courses/mysql-for-developers/schema/enums",[1397],"Enums",["SingleFetchClassInstance",1399],{"_38":39,"_28":212,"_41":1400,"_45":1401},{},[1402],["SingleFetchClassInstance",1403],{"_38":39,"_28":49,"_41":1404,"_45":1406},{"_51":1405},"/learn/courses/mysql-for-developers/schema/dates",[1407],"Dates",["SingleFetchClassInstance",1409],{"_38":39,"_28":212,"_41":1410,"_45":1411},{},[1412],["SingleFetchClassInstance",1413],{"_38":39,"_28":49,"_41":1414,"_45":1416},{"_51":1415},"/learn/courses/mysql-for-developers/schema/json",[958],"Overview\nJavaScript Object Notation (JSON) is a light-weight text-based file format similar to YAML or XML which simplifies data exchange. It was invented by Douglas Crockford in the early 2000s and became increasingly popular with the rise of document-based (also called NoSQL) databases.\nJSON supports strings, numbers, booleans, objects, and arrays as well as null values. A simple JSON example containing key-value pairs, an object \"bandMembers\" and an array \"songs\" would look like this:{\n \"artist\": \"Starlord Band\",\n \"bandMembers\": {\n \"vocals\": \"Steve Szczepkowski\",\n \"guitar\": \"Yohann Boudreault\",\n \"bass\": \"Yannick T.\",\n \"drums\": \"Vince T.\"\n },\n \"bandMembersCount\": 4,\n \"album\": \"Space Rider\",\n \"releaseDate\": \"2021-10-25\",\n \"songs\": [\n \"Zero to Hero\",\n \"Space Riders with No Names\",\n \"Ghost\",\n \"Bit of Good (Bit of Bad)\",\n \"Watch me shine\",\n \"We’re Here\",\n \"The Darkness inside\",\n \"No Guts No Glory\",\n \"All for One\",\n \"Solar Skies\"\n ],\n \"songsCount\": 10\n}\n\nMySQL has implemented rudimentary support for the JSON data type with version 5.7.8 in mid 2015 and has been adding improvements and new features ever since. Seven years later, MySQL now supports numerous SQL functions to work with JSON documents, it provides automatic content validation, allows for partial in-place updates, and uses a binary storage format for increased performance.\nWhen to use JSON\nRelational databases follow a predetermined structure and put emphasis on data cohesion and integrity. To achieve this, its data types and formats, as well as its data size, are all being enforced rigorously by the means of a schema.\nThe JSON data type is a bit of an anti-pattern to the rigorousness nature of such a schema. It allows you to break out of it, to gain flexibility when you need it. And it proves useful as long as you're aware of the trade-offs described in the next section.\nSome examples of when it may be beneficial to store data as a JSON document are:\nLog output written by an application or a server\nA Rest API response you want to store\nStoring configuration data\nA set of entities with variable attributes\nYou can also use JSON documents in your relational database design to break up complex relations spanning across multiple tables. This process is called denormalization, which is another relational database anti-pattern. In certain cases, however, it can result in performance improvements depending on your use case and application design.\nCaveats\nThe flexibility provided by the JSON data type comes with a few caveats you will need to be aware of.\nMost notably, you will need to factor in that JSON documents often require more storage capacity. In MySQL, their storage footprint is similar to the LONGBLOB or LONGTEXT data type. There is an overhead, though, due to the binary enconding and the added metadata and dictionaries which exist to speed up database reads. A good rule of thumb is that a string stored in JSON uses roughly 4 to 10 bytes of additional storage compared to a LONGBLOB or LONGTEXT column.\nIf you want to optimize your database schema towards storage efficiency, it is best to go with MySQL's more traditional data types (CHAR, VARCHAR, INT, and alike), as they are all more storage-efficient than JSON can likely ever be.\nAnother caveat to be aware of is the performance impact. Similar to other binary formats, JSON documents cannot be indexed directly. This, and the variable amount of data you can store in a JSON document, means that querying a JSON column often uses more buffer space and returns larger result sets, leading to more data exchange.\nWhile JSON documents cannot be indexed directly in MySQL, they can be indexed indirectly. Learn how in Indexing JSON in MYSQL.\nWhile a JSON document stored in MySQL can be up to 1 GB, in theory, it is recommended to keep JSON documents to only a few MB in size. On PlanetScale, we support JSON documents up to 67 MB.\nJSON functions\nMySQL comes with a robust set of JSON functions enabling you to create, update, read, or validate your JSON documents. PlanetScale supports all of the JSON functions except JSON_TABLE.\nCommon operations\nLet’s walk through a few examples together.\nFirst, we create a table with an INTEGER and a JSON column.CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);\n\nAn empty table needs data, so let’s use JSON_ARRAY to add some.INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));\n\nHow do we know this is an array? Well, we can get its type by using JSON_TYPE.SELECT JSON_TYPE(songs) FROM songs;\n+------------------+\n| json_type(songs) |\n+------------------+\n| ARRAY |\n+------------------+\n\nIf we want to extract an item from the array, we can do so with JSON_EXTRACT. In the below example, we extract the fourth element from the array.blog-mysql-json/main> SELECT JSON_EXTRACT(songs, '$[3]') FROM songs;\n+-----------------------------+\n| json_extract(songs, '$[3]') |\n+-----------------------------+\n| \"Ghost\" |\n+-----------------------------+\n\nWe can also use ->, which is the operator equivalent for JSON_EXTRACT.blog-mysql-json/main> SELECT songs->'$[3]' FROM songs;\n+-----------------+\n| songs -> '$[3]' |\n+-----------------+\n| \"Ghost\" |\n+-----------------+\n\nIf we need the unquoted result, we can use ->>, which is short for JSON_UNQUOTE(JSON_EXTRACT()).blog-mysql-json/main> SELECT songs->>'$[3]' FROM songs;\n+------------------+\n| songs ->> '$[3]' |\n+------------------+\n| Ghost |\n+------------------+\n\nIf we need to add data to the JSON array, we can use JSON_ARRAY_APPEND or JSON_ARRAY_INSERT to update it.UPDATE songs SET songs = JSON_ARRAY_APPEND(songs, '$', \"One last song\");\nUPDATE songs SET songs = JSON_ARRAY_INSERT(songs, '$[0]', \"First song\");\n\nFor more information on how to use all the different JSON functions, please see MySQL's documentation for the JSON data type and the JSON Function reference.\nFurther learning\nIf you'd like to learn more about data types in MySQL, we have an article on the INT data type and one on the VARCHAR data type that you may find useful.\nWe also have short videos on the following data types:\nIntegers\nDecimals\nStrings\nBinary Strings\nLong Strings\nEnums\nDates\nJSON",[1419,1421,1424,1426,1428,1430],{"_45":1420,"_43":902,"_865":866,"_867":909},[],{"_45":1422,"_43":946,"_865":866,"_867":1423},[],"When to use JSON",{"_45":1425,"_43":1006,"_865":866,"_867":1013},[],{"_45":1427,"_43":1109,"_865":866,"_867":1116},[],{"_45":1429,"_43":1130,"_865":866,"_867":1137},[],{"_45":1431,"_43":1294,"_865":866,"_867":1301},[],"The MySQL JSON data type",[1434],{"_26":27,"_28":29,"_881":-7},[1436],"engineering","Learn what the MySQL JSON data type is when to use MySQL JSON and some caveats to using JSON documents in relational databases.","2022-09-23T15:00:00.000Z",{"_891":1440,"_893":1437,"_894":1441,"_867":1432},"https://planetscale.com/blog/the-mysql-json-data-type","/assets/the-mysql-json-data-type-social-QCBrRqjS.jpg","pagination",{"_1444":1445,"_1446":8,"_1447":8,"_1448":1445,"_1449":1450},"page",1,"hasPrev","hasNext","totalPages","url","https://planetscale.com/blog/author/fooforge","https://planetscale.com","actionData","errors"]

Posts by Mike Stojan

Support’s notes from the field

The MySQL JSON data type