Bug #88106 Raw ST_GeomFromGeoJSON
Submitted: 16 Oct 2017 8:29 Modified: 17 Oct 2017 9:02
Reporter: Deividas Petraitis Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.19 OS:Ubuntu (Ubuntu 16.04.3 LTS)
Assigned to: Norvald Ryeng CPU Architecture:Any
Tags: geo, geojson

[16 Oct 2017 8:29] Deividas Petraitis 
Description:
Inserting data to spatial column applying `ST_GeomFromGeoJSON(@json)` on string MySQL behaves diffently comparing with `GEOMFROMTEXT(ST_AsText(ST_GeomFromGeoJSON(@json)))` resulting not adding two bytes in first case.

How to repeat:
SET @json = '{"type":"MultiPolygon","coordinates":[[[[19.3423099517823,52.3091850280763],[19.3625431060792,52.2956733703613],[19.3793506622316,52.2907676696777],[19.4193992614747,52.2886428833008],[19.4476146697998,52.2861557006837],[19.4605789184572,52.2863121032716],[19.4775009155275,52.2843132019044],[19.4931697845459,52.2812118530275],[19.5102462768555,52.2761878967286],[19.5191555023194,52.2739524841309],[19.5448379516603,52.2668151855469],[19.5671825408936,52.2572975158693],[19.5935764312744,52.2457084655762],[19.6088237762452,52.2357444763185],[19.6266956329347,52.2190170288087],[19.641019821167,52.2132797241212],[19.6572952270508,52.2153015136719],[19.6701850891114,52.2197189331055],[19.6876773834229,52.2227478027344],[19.7042388916016,52.2270050048829],[19.7251358032227,52.2315521240234],[19.738151550293,52.2347564697266],[19.7517967224123,52.2370529174805],[19.7768249511719,52.2407531738282],[19.8043746948243,52.2426490783691],[19.8491363525391,52.2457008361816],[19.8635692596436,52.253131866455],[19.8750305175781,52.2656898498536],[19.8912906646728,52.2717666625977],[19.9182033538819,52.2677383422852],[19.9386138916016,52.2521018981934],[19.9436511993409,52.245273590088],[19.9497776031495,52.2389183044434],[19.9590511322023,52.230812072754],[19.9731864929199,52.2172508239747],[19.9858531951904,52.2137298583986],[20.0072441101075,52.2144699096681],[20.0181961059571,52.2130813598633],[20.0311241149903,52.2065277099609],[20.0413761138917,52.2021141052247],[20.062089920044,52.1961784362794],[20.0706863403321,52.1897201538085],[20.0736598968506,52.1819458007812],[20.0756855010987,52.1729736328125],[20.0772285461426,52.1609802246094],[20.0767555236816,52.1472244262695],[20.0753440856936,52.1367568969727],[20.0832099914552,52.1180839538575],[20.0888824462891,52.1121673583985],[20.0964202880862,52.1068077087403],[20.1039924621583,52.1012763977052],[20.1109027862551,52.0949096679689],[20.1301174163819,52.1016387939453],[20.1401977539062,52.1035003662109],[20.1486434936524,52.1003341674805],[20.1580505371095,52.0947761535646],[20.1824836730958,52.0908851623535],[20.2044410705567,52.0855751037598],[20.2533226013185,52.0605316162111],[20.2470779418946,52.0345840454102],[20.2434673309327,52.0220489501954],[20.241247177124,52.0076141357422],[20.234302520752,52.0028762817383],[20.2098541259766,51.9981575012207],[20.1990509033204,51.9952163696289],[20.2011470794677,51.9856033325196],[20.2190151214602,51.9722480773926],[20.2399883270264,51.9604263305665],[20.252510070801,51.9554023742675],[20.2588634490967,51.9427871704102],[20.2697715759279,51.929973602295],[20.2748527526858,51.9225273132325],[20.2716083526612,51.9198417663575],[20.2610015869141,51.9165115356446],[20.2588443756103,51.9093360900879],[20.2649765014649,51.9010162353516],[20.2731742858888,51.8982391357423],[20.3005428314209,51.8975715637208],[20.3118782043458,51.8948860168458],[20.3214015960694,51.889186859131],[20.3333625793458,51.8877067565918],[20.3490772247314,51.8944244384767],[20.3680591583253,51.8936309814454],[20.3854236602784,51.8940315246582],[20.3935565948486,51.894989013672],[20.4098644256592,51.8946228027345],[20.4235610961915,51.8960266113281],[20.4353504180909,51.8943176269531],[20.4489860534669,51.8925170898439],[20.4599609375003,51.8905220031739],[20.4701042175295,51.887680053711],[20.4766902923585,51.8837509155275],[20.4864883422853,51.8754310607911],[20.505506515503,51.8659248352052],[20.5169868469238,51.8594360351563],[20.5362110137939,51.8508224487305],[20.5512504577638,51.8449058532716],[20.5764141082764,51.8326072692871],[20.5863742828369,51.8233222961426],[20.5961093902588,51.8158340454103],[20.6108627319337,51.8019752502442],[20.6150436401368,51.7901802062989],[20.6176109313966,51.7828903198242],[20.6215553283692,51.7739372253419],[20.6065883636477,51.766139984131],[20.5997390747071,51.7598686218263],[20.5966358184814,51.75],[20.5936622619629,51.7426071166993],[20.5969638824464,51.7280235290528],[20.6048851013183,51.7166900634765],[20.6146049499511,51.7085838317871],[20.6273288726808,51.7019996643067],[20.6358432769776,51.6963348388672],[20.6499233245851,51.6902923583985],[20.6666374206544,51.6740455627443],[20.671533584595,51.6676712036132],[20.673692703247,51.6619377136231],[20.6734790802003,51.6528549194336],[20.6719017028811,51.6408309936523],[20.6715946197509,51.6336288452149],[20.6683597564699,51.6262893676757],[20.6641502380372,51.620330810547],[20.660551071167,51.6167831420898],[20.6557159423828,51.6175537109375],[20.639835357666,51.6194686889648],[20.618356704712,51.6193771362306],[20.6060218811035,51.6187171936036],[20.5817165374756,51.6242866516113],[20.571466445923,51.6289443969728],[20.5575218200686,51.6353149414062],[20.5384616851806,51.6398429870606],[20.5294284820557,51.6429710388183],[20.5144348144531,51.6452636718751],[20.500961303711,51.6461486816406],[20.4666614532471,51.650547027588],[20.4520320892336,51.6495056152345],[20.4355335235598,51.6385154724122],[20.4255485534669,51.6323204040527],[20.419282913208,51.622615814209],[20.415740966797,51.6113700866699],[20.4170055389405,51.5975074768067],[20.423225402832,51.5837364196778],[20.4299545288088,51.5750045776367],[20.4357376098636,51.5666999816895],[20.433874130249,51.5646858215333],[20.4399166107179,51.5555839538575],[20.4448738098145,51.5502090454102],[20.4491691589356,51.5457839965821],[20.457805633545,51.5344200134278],[20.4628257751465,51.5112648010254],[20.4645786285401,51.5001487731934],[20.464973449707,51.4934234619141],[20.4649105072023,51.4801635742189],[20.4656639099121,51.4668922424318],[20.4714851379395,51.4631996154786],[20.4959754943847,51.45613861084],[20.5208415985107,51.4479446411132],[20.5238552093507,51.4396133422852],[20.5242023468018,51.4294929504396],[20.5231475830078,51.4166221618652],[20.5219364166262,51.4056587219239],[20.516981124878,51.3976478576661],[20.5085906982422,51.388038635254],[20.5023365020753,51.3803253173829],[20.4844722747803,51.3776016235352],[20.4624233245851,51.3655891418458],[20.4474277496339,51.3530235290528],[20.4413528442383,51.3466567993165],[20.4347839355472,51.3386192321777],[20.4297904968261,51.3279991149903],[20.4271583557129,51.3143463134766],[20.4274845123291,51.305778503418],[20.4315586090088,51.2970924377442],[20.4321250915527,51.2899971008301],[20.4194412231445,51.286979675293],[20.4063396453859,51.2834587097168],[20.3881340026856,51.2548370361329],[20.3823699951173,51.243968963623],[20.3740844726563,51.2297248840333],[20.3718528747559,51.2219047546387],[20.3672885894776,51.2149963378907],[20.3557682037354,51.2112083435059],[20.3294029235843,51.2099151611328],[20.3051280975342,51.2059097290038],[20.2833843231201,51.2033462524415],[20.2685089111329,51.1971092224122],[20.2593746185303,51.188316345215],[20.2425880432129,51.1756324768068],[20.2209854125978,51.1645851135255],[20.2026576995851,51.1574821472169],[20.1884307861328,51.1503143310547],[20.1743774414063,51.1461219787598],[20.1547393798829,51.1494789123536],[20.1388893127443,51.1527824401855],[20.1208286285401,51.1518478393555],[20.1048164367676,51.155559539795],[20.091175079346,51.1626358032227],[20.0562610626222,51.1595649719239],[20.0253810882569,51.1571731567383],[20.0129661560059,51.1441192626953],[20.002462387085,51.1339721679689],[19.998664855957,51.1303291320801],[19.9929904937744,51.1210517883301],[19.9833488464358,51.1081886291504],[19.9763813018799,51.0989646911622],[19.9731426239014,51.0880966186523],[19.972537994385,51.0792655944825],[19.9778709411622,51.0602149963379],[19.9805545806884,51.0510253906249],[19.9896602630615,51.0427246093751],[20.0017852783204,51.0338897705078],[20.0321521759034,51.0250434875488],[20.0427074432373,51.0225181579589],[20.0490760803222,51.0173110961914],[20.058240890503,51.0098876953125],[20.0618934631348,50.9899749755861],[20.0622234344483,50.9753036499023],[20.0603981018066,50.9627227783203],[20.0509452819825,50.9441871643066],[20.0457916259766,50.932315826416],[20.0430278778076,50.9274635314942],[20.031475067139,50.9294776916504],[20.0083808898926,50.9341697692872],[19.9909439086917,50.9445686340332],[19.9727478027344,50.9575080871582],[19.955141067505,50.9681053161622],[19.9356842041015,50.9788055419922],[19.9195232391358,50.9866065979005],[19.9032802581787,50.9906845092774],[19.8789348602298,50.9895973205567],[19.866102218628,50.9928283691407],[19.8575649261475,50.9888534545898],[19.8554553985596,50.9724464416504],[19.8468036651612,50.9360809326172],[19.8487739562989,50.9211807250977],[19.8508071899413,50.9116249084473],[19.8541603088381,50.9022369384767],[19.8547515869141,50.8792457580568],[19.8498096466065,50.8719749450685],[19.8438377380372,50.865203857422],[19.820203781128,50.8515167236328],[19.7994899749756,50.8449745178223],[19.7820167541505,50.8371238708496],[19.7716312408448,50.8274917602538],[19.7505264282227,50.8043251037598],[19.7419261932375,50.8060607910157],[19.7303237915039,50.8084526062013],[19.7251071929934,50.8086624145508],[19.7193527221681,50.8101196289064],[19.7107276916506,50.8146438598633],[19.7016067504884,50.8185272216796],[19.6918754577637,50.8217315673829],[19.685474395752,50.8260498046876],[19.6773357391359,50.8322219848634],[19.6700992584229,50.8366088867189],[19.6458148956298,50.8394660949707],[19.6307258605958,50.8390846252441],[19.6214771270754,50.8398742675782],[19.609043121338,50.8450088500978],[19.5996265411378,50.8454132080078],[19.5881175994872,50.8411903381348],[19.5766525268555,50.8381576538087],[19.5558376312257,50.8378829956055],[19.5307216644289,50.8382415771485],[19.5225315093995,50.8375396728517],[19.5148773193361,50.8390159606935],[19.5078887939453,50.8423805236817],[19.5009098052979,50.84769821167],[19.4941596984864,50.8566093444824],[19.4799251556398,50.8734054565431],[19.4671707153321,50.8861122131349],[19.4569664001465,50.8977432250977],[19.4487247467042,50.9065093994141],[19.4347171783447,50.9197235107422],[19.4257888793946,50.9275054931641],[19.4205970764161,50.9319229125977],[19.4145774841311,50.9364318847656],[19.3938293457031,50.9497833251953],[19.3782596588135,50.9576606750488],[19.3645496368408,50.964138031006],[19.3525390625001,50.9693908691406],[19.3432693481445,50.9758796691895],[19.3334846496582,50.9773750305176],[19.3260364532471,50.9781951904297],[19.3113327026368,50.9801139831544],[19.2997379302979,50.9801483154297],[19.2869892120364,50.979290008545],[19.2664051055909,50.9629211425781],[19.2526645660402,50.9603538513184],[19.2426013946533,50.9593811035157],[19.2308826446534,50.9591484069825],[19.2257595062256,50.9594039916993],[19.2254333496094,50.9592895507812],[19.2198371887209,50.9596862792969],[19.2155723571777,50.9594917297364],[19.2056255340577,50.960418701172],[19.1845703124999,50.9615058898926],[19.1759662628174,50.9599723815918],[19.1696224212648,50.9601593017579],[19.1631336212158,50.9617347717285],[19.1575279235843,50.9627876281739],[19.1510467529297,50.963680267334],[19.1271438598634,50.9644927978516],[19.1094341278076,50.9687652587892],[19.0976791381837,50.9723663330079],[19.0867080688478,50.9749374389648],[19.0647144317627,50.9789428710938],[19.0452766418457,50.9871406555176],[19.0290985107423,50.9927139282227],[19.0170135498047,50.9980430603028],[19.0044174194337,51.0029754638672],[18.9773426055908,51.0131416320802],[18.9670886993409,51.0200881958009],[18.9531650543213,51.0305824279785],[18.94441986084,51.035499572754],[18.9330959320069,51.0393524169923],[18.9164028167725,51.0385055541992],[18.8971920013428,51.0333976745605],[18.8846721649171,51.0263900756837],[18.8694324493409,51.0147285461426],[18.7949028015137,51.0113372802736],[18.7655811309814,51.0083312988281],[18.7379932403564,51.008628845215],[18.7190513610841,51.0074768066407],[18.6897392272949,51.009220123291],[18.6757926940917,51.0086402893066],[18.6556301116943,50.9952697753907],[18.6258964538575,51.0116348266602],[18.6049404144287,51.0227088928222],[18.5720748901368,51.0372200012207],[18.5643424987794,51.0423164367677],[18.5521621704102,51.0726203918458],[18.539800643921,51.078426361084],[18.5305004119872,51.0826606750488],[18.522066116333,51.0838317871093],[18.5153312683107,51.0726318359375],[18.5118217468262,51.070915222168],[18.5023784637452,51.0599708557129],[18.4922351837159,51.0603981018067],[18.4803886413575,51.0620918273926],[18.4772701263429,51.0620193481446],[18.4767436981201,51.0607604980468],[18.4719619750978,51.0602455139161],[18.4614467620849,51.0601119995118],[18.4493389129639,51.0596580505372],[18.4373741149902,51.0594215393067],[18.4256992340087,51.0596618652344],[18.4149971008301,51.0589599609376],[18.3967685699463,51.0609016418457],[18.380132675171,51.064609527588],[18.3392257690431,51.0729331970215],[18.31662940979,51.0793075561523],[18.3019466400148,51.0838203430176],[18.2901515960694,51.0862846374512],[18.2644939422607,51.0876388549806],[18.2541122436524,51.0887680053712],[18.2408294677734,51.0914192199707],[18.2255706787109,51.0929832458497],[18.2145366668702,51.0952873229981],[18.2020111083985,51.0959548950196],[18.1808414459229,51.1014633178712],[18.1737384796142,51.1082611083984],[18.1583614349366,51.1262779235839],[18.1501979827881,51.1361808776857],[18.1498374938965,51.1391792297364],[18.1500759124756,51.1410598754882],[18.1504974365235,51.1424751281739],[18.1498336791992,51.1466445922852],[18.1475353240967,51.1593284606934],[18.1440353393555,51.1674118041992],[18.144229888916,51.1791801452637],[18.1417083740234,51.2027435302735],[18.1372756958008,51.2106323242189],[18.131690979004,51.2144050598145],[18.1257438659668,51.2168426513672],[18.1178207397461,51.2203407287598],[18.1074962615967,51.224277496338],[18.0813064575196,51.2417030334473],[18.0631904602051,51.2511367797852],[18.0583038330078,51.2652778625489],[18.055612564087,51.2841873168946],[18.0540313720704,51.2955055236816],[18.0561904907226,51.3066482543945],[18.0580558776855,51.3158569335938],[18.0621051788331,51.3234519958497],[18.0664005279541,51.3239059448243],[18.0839557647705,51.3272209167481],[18.1157588958741,51.3336410522462],[18.1441364288331,51.3348579406739],[18.1626853942872,51.3492813110353],[18.1709918975831,51.3600044250489],[18.1773395538331,51.382122039795],[18.1834297180176,51.387866973877],[18.1884765625001,51.3944702148438],[18.1965217590332,51.3998184204102],[18.2108249664307,51.4047470092774],[18.2180786132814,51.4048194885254],[18.2236347198487,51.405948638916],[18.2300910949707,51.4019660949708],[18.2571182250977,51.3888473510742],[18.2669506072998,51.3862571716309],[18.2753105163574,51.3837699890136],[18.2816486358644,51.3835372924805],[18.2903175354003,51.3837966918946],[18.3042526245118,51.3853416442871],[18.3168392181398,51.3867149353028],[18.320297241211,51.3881797790527],[18.3235130310058,51.3898963928223],[18.325632095337,51.3929405212402],[18.328187942505,51.3980369567872],[18.331600189209,51.4007987976074],[18.3488883972168,51.4209632873535],[18.3561992645264,51.435817718506],[18.3643741607667,51.4487190246583],[18.3670921325684,51.4566001892091],[18.3683242797852,51.4640769958496],[18.3673877716064,51.470329284668],[18.3657932281494,51.4777832031251],[18.362720489502,51.4940147399902],[18.3594207763673,51.5173568725587],[18.3565406799316,51.5277404785156],[18.355432510376,51.5373191833496],[18.3541831970216,51.5469245910645],[18.3542251586915,51.5523986816407],[18.3542461395265,51.5571899414064],[18.3541698455811,51.5641174316406],[18.3542442321777,51.5884475708008],[18.3548774719239,51.6003952026367],[18.3560848236085,51.6110649108888],[18.3549499511719,51.6199493408204],[18.3554306030275,51.6296501159668],[18.3586292266846,51.6391906738282],[18.362871170044,51.6478881835938],[18.3676528930663,51.6571464538574],[18.3770446777344,51.6722297668458],[18.3817138671876,51.6828994750978],[18.3880558013917,51.6929931640626],[18.3920631408692,51.7023239135743],[18.4009113311768,51.7118682861329],[18.4027729034425,51.7156639099121],[18.4055938720704,51.7201232910156],[18.4094696044922,51.7264556884766],[18.4128799438477,51.733455657959],[18.4174938201905,51.7431182861329],[18.4248523712159,51.7553443908691],[18.4345531463622,51.76171875],[18.4435329437255,51.7681541442871],[18.4532451629638,51.7751846313477],[18.4611701965333,51.7823867797852],[18.4834499359131,51.7885360717774],[18.490644454956,51.790828704834],[18.4945182800294,51.7913780212402],[18.49662399292,51.7907028198242],[18.5033435821533,51.7909317016602],[18.5122585296631,51.7928123474122],[18.5436534881591,51.7952117919922],[18.5709800720215,51.7941856384278],[18.5864143371583,51.7921180725099],[18.6004161834717,51.7901458740234],[18.617889404297,51.7891693115234],[18.6298637390137,51.7877197265626],[18.6438713073731,51.7871131896974],[18.6620769500734,51.7816581726074],[18.6706008911133,51.7934799194337],[18.6782131195069,51.8016242980958],[18.6858081817628,51.8104362487792],[18.7067871093749,51.8292274475098],[18.7131767272949,51.8395080566407],[18.7217273712158,51.8530349731446],[18.7267189025878,51.863136291504],[18.7248802185059,51.8731155395508],[18.7164039611818,51.8899307250977],[18.7073516845703,51.9030494689942],[18.701473236084,51.9138793945314],[18.6926441192628,51.9448394775391],[18.6966037750244,51.9629516601562],[18.699899673462,51.9738082885742],[18.7060127258301,51.9884986877442],[18.7135047912599,52.009750366211],[18.7489204406739,52.0089607238771],[18.7623882293702,52.007610321045],[18.7772693634033,52.0086326599121],[18.8206253051759,52.0152015686036],[18.8307418823243,52.0167846679688],[18.8395347595215,52.0170631408691],[18.8466949462891,52.0215759277344],[18.8626174926757,52.0252952575684],[18.8722591400148,52.0279273986816],[18.8807640075684,52.0334167480469],[18.8900051116944,52.0427284240724],[18.8963146209718,52.0519142150878],[18.8981475830078,52.0591621398925],[18.896110534668,52.0637702941895],[18.8977584838867,52.0776443481446],[18.9019298553467,52.0866699218751],[18.9056549072266,52.0996208190918],[18.9066810607911,52.1215515136719],[18.9097614288331,52.1403388977051],[18.9156551361084,52.1494598388673],[18.944486618042,52.1521873474122],[18.9818801879883,52.1581802368165],[18.9990425109864,52.1670799255372],[19.0106449127198,52.172477722168],[19.0413627624512,52.1850891113282],[19.057472229004,52.1919593811035],[19.0631809234619,52.1978645324707],[19.06990814209,52.2015419006348],[19.081657409668,52.2114105224611],[19.0814723968509,52.2162322998046],[19.0749702453614,52.2187728881836],[19.0717945098878,52.2208480834961],[19.0680732727054,52.2211685180665],[19.0625476837158,52.2198753356934],[19.0610885620118,52.2212181091309],[19.0558700561523,52.2252082824708],[19.0487098693848,52.2310600280762],[19.0373859405519,52.2465324401856],[19.0398464202881,52.2588081359864],[19.0402450561523,52.2784538269044],[19.0489139556885,52.2786445617677],[19.0507259368897,52.2850494384767],[19.0535278320314,52.294101715088],[19.0752716064454,52.3031768798829],[19.0927276611329,52.3034553527833],[19.1161384582522,52.3009262084961],[19.1309623718262,52.3020629882812],[19.1533641815186,52.3060417175294],[19.1731891632082,52.3042678833008],[19.2025833129883,52.303825378418],[19.2140293121338,52.3059539794922],[19.2218894958496,52.3089637756349],[19.2356910705567,52.3099632263185],[19.243637084961,52.3274002075196],[19.2573165893557,52.3325538635254],[19.295961380005,52.3342285156251],[19.3087177276612,52.3234062194825],[19.3177795410159,52.3182373046876],[19.3423099517823,52.3091850280763]]]]}';

-- Will fail
INSERT INTO `geo_regions` (`id`, `name`, `description`, `geometry`) VALUES (null, "test", "Test Data", ST_GeomFromGeoJSON(@json));

-- Will do the job
INSERT INTO `geo_regions` (`id`, `name`, `description`, `geometry`) VALUES (null, "test", "Test Data", GEOMFROMTEXT(ST_AsText(ST_GeomFromGeoJSON(@json))));
[16 Oct 2017 8:30] Deividas Petraitis 
Bin for: ST_GeomFromGeoJSON(@json)

Attachment: geo_fail.bin (application/octet-stream, text), 8.43 KiB.
[16 Oct 2017 8:30] Deividas Petraitis 
Bin for: GEOMFROMTEXT(ST_AsText(ST_GeomFromGeoJSON(@json)))

Attachment: geo_success.bin (application/octet-stream, text), 8.43 KiB.
[16 Oct 2017 8:31] Deividas Petraitis 
Bin diff

Attachment: diff.png (image/png, text), 51.74 KiB.
[16 Oct 2017 8:34] Deividas Petraitis 
Table example:

CREATE TABLE `geo_regions` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `geometry` geometry NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
[17 Oct 2017 9:02] Norvald Ryeng 
Hi,

What you're seeing is not a bug. The bytes that differ are the SRID of the geometries, and they are different because of the ways the two geometries are constructed.

If you don't explicitly specify an SRID, most geometry import functions default to SRID 0. ST_GeomFromGeoJSON is an exception since the format specification says that the data is in WGS 84 (SRID 4326). You can find this detail in the reference manual: https://dev.mysql.com/doc/refman/5.7/en/spatial-geojson-functions.html#function_st-geomfro...

The first INSERT statement uses ST_GeomFromGeoJSON(@json), which will generate a geometry in SRID 4326 (little-endian unsigned 32 bit int hex: E6100000).

The second statement will construct the same object, then export it to WKT (with ST_AsText), which is a format that can't represent SRIDs, so the SRID is lost. When that WKT string is passed to ST_GeomFromText, the default is SRID 0. So the geometry that is inserted has the same coordinates as in the first row, but the SRID is now 0.

You can make the two geometries the same by force the SRID of the second row to 4326 by using ST_GeomFromText(ST_AsText(...), 0). Or you can force the SRID of the first row to 0 by using ST_GeomFromGeoJSON(@json, 1, 0).

So, which SRID should you use? If these are WGS 84 coordinates (GPS coordinates), then 4326 is the correct SRID. However, MySQL 5.7 doesn't really understand how to compute on geographic longitude-latitude coordinates. It only does Cartesian computations, which is what you specify if you use SRID 0.

That is changed in the recent MySQL 8.0 release candidate. MySQL now does geographic computations. So (unless there are dramatic changes made to MySQL 8.0 before the final release) this means that if you use SRID 4326, the result of computations will change when upgrading to MySQL 8.0.

On the other hand, if you use SRID 0, you specify that these are Cartesian data. It's technically not true if it is longitude-latitude, but it is a safe choice if you don't want behavior changes after upgrade. The behavior in 5.7 will not be affected by changing the SRID, and SRID 0 behavior is unchanged between 5.7 and 8.0. The downside is that you will not benefit from  the new geographic computations in 8.0.

What to choose depends on your needs. I did a whole presentation on this earlier this year, and there are a few rules of thumb at the end. You can find it here: https://www.slideshare.net/NorvaldRyeng/mysql-80-gis-are-you-ready

I hope this answers your question!