[Á°][¼¡][ÈÖ¹æ½ç°ìÍ÷][¥¹¥ì¥Ã¥É°ìÍ÷]

mysql:16343

From: "yoku ts." <"yoku ts." <yoku0825@xxxxxxxxxx>>
Date: Wed, 25 May 2016 14:15:01 +0900
Subject: [mysql 16343] Re: [mysql 16342] RE: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë

¤³¤ó¤Ë¤Á¤Ï¡¢yoku0825¤Ç¤¹¡£


5.7.11¤Î¥Á¥§¥ó¥¸¥í¥°¤Ë¤³¤ó¤Ê¤â¤Î¤ò¸«¤Ä¤±¤Þ¤·¤¿¡£

> Data corruption or a server exit could occur if a stored procedure had a variable declared as TEXT or BLOB and data was copied to that variable using SELECT ... INTO syntax from a TEXT or BLOB column. (Bug #22203532, Bug #22232332, Bug #21941152)


http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-11.html


var_text¤ÏTEXT·¿¤Ê¤Î¤Ç¡¢¤³¤ì¤Ë¥Þ¥Ã¥Á¤·¤Æ¤¤¤ë¤è¤¦¤Êµ¤¤¬¤·¤Þ¤¹¡£
¡Ê8·å¥Ð¥°ÈÖ¹æ¤ÏOracle¼Ò´ÉÍý¤Î¥Ð¥°ÈÖ¹æ¤Ê¤Î¤Ç¡¢¾ÜºÙ¤Ï³°Éô¤«¤é¸«¤é¤ì¤Ê¤¤¤Î¤Ç¤¹¤¬¡Ë

¤·¤«¤· "or a server exit"¤Ã¤Æ¤Ê¤«¤Ê¤«¥´¥Ä¤¤¤Ç¤¹¤Í¡£¡£


yoku0825,

2016ǯ5·î24Æü 12:43 ¤¿¤¯¤ä <yata.s15ste@xxxxxxxxxx>:
> yoku¤µ¤Þ

>

> ÊÖ¿®¤¢¤ê¤¬¤È¤¦¤´¤¶¤¤¤Þ¤¹¡£¤¿¤¯¤ä¤Ç¤¹¡£

>

> ¤Þ¤º¡¢¸åȾ¤Î

> SET SESSION optimizer_switch= 'mrr=off';

> ¤Ë¤Ä¤¤¤Æ»î¤·¤Æ¤ß¤Þ¤·¤¿¤¬¡¢var_text ->[eeee]¤Ç½ÐÎϤ·¤Þ¤·¤¿¡Ê²þÁ±¤»¤º¡Ë

>

>>¸µ¤Î¥Ç¡¼¥¿¤Ç¡¢°ÅÌۤη¿ÊÑ´¹¤Ê¤É¤Çvarchar¤¬DOUBLE¤Ë¥­¥ã¥¹¥È¤µ¤ì¤Æ

>>¤¬2¹ÔÊÖ¤·¤Á¤ã¤Ã¤Æ¤ë¤È¤«¤½¤ó¤Ê¤³¤È¤Ï¤Ê¤¤¤Ç¤·¤ç¤¦¤«¡Ä¡£

> ³Î¤«¤ËµóÆ°¤òȯ¸«¤·¤¿ºÝ¤ÎSELECT INTOʸ¡Ê͹ÊØÈÖ¹æTBL»þ¡Ë¤Ç¤Ï

> 2¹ÔÊÖ¤·¤½¤¦¤Ê¤ó¤Ç¤¹¤±¤É¡¢¥µ¥ó¥×¥ëÄ󼨤·¤¿¾õÂÖ¤À¤È

> cd¼«ÂΤ¬°ì°Õ¤Ë¤Ê¤Ã¤Æ¤Þ¤¹¤«¤é2¹Ô¤ÏÊÖ¤é¤Ê¤¤¤È»×¤¤¤Þ¤¹¡£

>

>

> »ä¤âÊ̴Ķ­RHEL6·Ï+MySQL5.7.12-EnterpriseÈǤÇ

> »î¤·¤Æ¤ß¤¿¤ó¤Ç¤¹¤¬¡¢¤³¤Î¾ò·ï¤Ç¤ÏȯÀ¸¤·¤Þ¤»¤ó¤Ç¤·¤¿¡£

>

> º£¤Î¤È¤³¤í¡¢yoku¤µ¤Þ¤Î3üËö¤Ë²Ã¤¨¤Æ¡¢

> »ä¤¬³Îǧ¤Ç¤­¤ë2üËö¤Ç¤âȯÀ¸¤»¤º¤Ç¤·¤¿¤Î¤Ç¡¢

> ȯÀ¸¤·¤¿´Ä¶­¡Ê¼«µ¡MySQL 5.7.10¡Ë¤Î¤ß¤Ç³Îǧ¤Ç¤­¤ëÆâÍƤȤʤê¤Þ¤·¤¿¡£

>

> ¼«µ¡MySQL 5.7.10¤Ë¤Ä¤¤¤Æ¤Ï

> ¾¤Î´Ä¶­¤¬¿·µ¬¤Ç¥¤¥ó¥¹¥È¡¼¥ë¤·¤¿¤â¤Î¤ËÂФ·¤Æ¡¢

> MySQL 5.6·Ï¤«¤éyum + mysql_upgrade¤·¤¿¤È¤¤¤¦°ã¤¤¤¬¤¢¤ë¤°¤é¤¤¤Ç¤¹¡£

>

>

> µ­ºÜ¤¤¤¿¤À¤¤¤¿URLÆâ¤Î°ÅÌۤη¿ÊÑ´¹¤¬

> °ìÈÖ²ÄǽÀ­¤¬¹â¤½¤¦¤À¤Ê¤Ã¤Æ»×¤Ã¤Æ¤ß¤Æ¤Þ¤·¤¿¤¬

> ¼«Ê¬¤¬ÊÝÍ­¤¹¤ë1´Ä¶­¤Î¤ß¤ÇȯÀ¸¤·¤Æ¤¤¤ë¤È¤Ê¤ë¤È

> ´Ä¶­°Í¸¤«¥Ñ¥é¥á¡¼¥¿¤Î²ÄǽÀ­¤¬Ç»¸ü¤Ç¤¹¤Í¡£

>

>

> ¤Á¤Ê¤ß¤Ë¿½¤·¾å¤²¤ë¤È¡¢

>  |---------------------------------------------------------|

>  |zipcode| state |              city|                 town |

>  |---------------------------------------------------------|

>  |0010000| Ë̳¤Æ»|        »¥ËÚ»ÔË̶è|  °Ê²¼¤Ë·ÇºÜ¤¬¤Ê¤¤¾ì¹ç|

>  |    ¡¦¡¦¡¦|    ¡¦¡¦¡¦|               ¡¦¡¦¡¦|                   ¡¦¡¦¡¦|

>  |9071800| ²­Æ츩|  Ȭ½Å»³·´Í¿Æá¹ñÄ®|  °Ê²¼¤Ë·ÇºÜ¤¬¤Ê¤¤¾ì¹ç|

>  |9071801| ²­Æ츩|  Ȭ½Å»³·´Í¿Æá¹ñÄ®|                Í¿Æá¹ñ|

>  ----------------------------------------------------------

> µóưȯ¸«»þ»þ¤Ï¤³¤ó¤Ê´¶¤¸¤Î͹ÊØÈÖ¹æ¥Æ¡¼¥Ö¥ë¡Êcity¤Ètown¤ò¼èÆÀ¡Ë¤Ç¤·¤Æ¡¢

>

> ¾ò·ï¤ÎZipcode¤Ë²¿¤òÀßÄꤷ¤Æ¤â

> city¤Ï[Ȭ½Å»³·´Í¿Æá¹ñÄ®]

> town¤Ë»ê¤Ã¤Æ¤Ï¡ÎÍ¿Æá¹ñ·ÇºÜ¡Ï¡Ê°ìÉô·ë¹ç¡©¡©¡©¡Ë

> ¤È¤¤¤¦·ë²Ì¤¬Ê֤äƤ­¤Æ¤Þ¤·¤¿¡£

>

> Ȭ½Å»³·´Í¿Æá¹ñÄ®¤Î¥Ç¡¼¥¿¤Ï¥Æ¡¼¥Ö¥ë¾åºÇ¸å¤Î¹Ô¤ÈºÇ¸å¤«¤é2ÈÖÌܹԤǤ¹¤Í¡£

>

>

> ¤Ê¤«¤Ê¤«¹á¤Ð¤·¤¤µóÆ°¤Ç¤¹¡£

>

> -----Original Message-----

> From: yoku ts. [mailto:yoku0825@xxxxxxxxxx]

> Sent: Tuesday, May 24, 2016 11:00 AM

> To: ml@xxxxxxxxxx

> Subject: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë

>

> ¤³¤ó¤Ë¤Á¤Ï¡¢yoku0825¤È¤¤¤¤¤Þ¤¹¡£

>

> ¼è¤êµÞ¤®¡¢CentOS 6.6¤ÎMySQL 5.7.10¤ÈMySQL 5.7.12¡¢

> Windows¤Î5.7.10¡Êx64¡Ë¤Ç¤â¤¤¤¿¤À¤¤¤¿¥Æ¥¹¥È¥±¡¼¥¹¤À¤ÈºÆ¸½¤·¤Þ¤»¤ó¤Ç¤·¤¿¡£

>

> ¸µ¤Î¥Ç¡¼¥¿¤Ç¡¢°ÅÌۤη¿ÊÑ´¹¤Ê¤É¤Çvarchar¤¬DOUBLE¤Ë¥­¥ã¥¹¥È¤µ¤ì¤Æ

>> select word,word

>> into var_text,var_text2

>> from dtb_test

>> where cd = '1111';

>

> ¤¬2¹ÔÊÖ¤·¤Á¤ã¤Ã¤Æ¤ë¤È¤«¤½¤ó¤Ê¤³¤È¤Ï¤Ê¤¤¤Ç¤·¤ç¤¦¤«¡Ä¡£

>

> http://soudai1025.blogspot.jp/2015/12/mysql.html

>

>

> ¤½¤ì¤«¡¢5.6¤Î½é´ü¤Ç¤ÏMRR¤¬Æ±¤¸¹Ô¤ò2²óÊÖ¤·¤Á¤ã¤¦¤è¤¦¤Ê¥Ð¥°¤¬¤¢¤Ã¤¿¤Î¤Ç¡¢

> ¤½¤ì¤òµ¿¤¦¤Ê¤é

>

> mysql> SET SESSION optimizer_switch= 'mrr=off';

> ¤È¤·¤Æ¤«¤é¡¢CALL test_proc(); ¤·¤Æ¤ß¤Æ¤â¤é¤¨¤Þ¤»¤ó¤«¡©

>

>

> yoku0825,

>

>

> 2016ǯ5·î23Æü 16:28  <yata.s15ste@xxxxxxxxxx>:

>> ½é¤á¤ÆÅê¹Æ¤µ¤»¤Æ¤¤¤¿¤À¤­¤Þ¤¹¡£

>> ³«È¯¼Ô¤Î¤¿¤¯¤ä¤È¿½¤·¤Þ¤¹¡£

>>

>>

>> ÅöÊý¤¬ÍøÍѤ·¤Æ¤¤¤ë´Ä¶­¤Ë¤ª¤¤¤Æ¡¢

>> ÉÔ¶ñ¹ç¤«»ÅÍÍÄ̤꤫¤É¤¦¤«È½ÃǤ·¤«¤Í¤ë»ö¾Ý¤¬È¯À¸¤·¤¿¤Î¤Ç¡¢

>> ³§ÍͤÎÃηäò¤ª¼Ú¤ê¤·¤¿¤¤¤È»×¤¤Åê¹Æ¤µ¤»¤Æ¤¤¤¿¤À¤­¤Þ¤·¤¿¡£

>>

>>

>> [³µÍ×]

>>¡¡ MySQL5.7·Ï´Ä¶­¤Ë¤ª¤¤¤Æ¡¢

>>¡¡ ¥¹¥È¥¢¥É¥×¥í¥·¡¼¥¸¥ã¡Ê¤â¤·¤¯¤Ï¥Õ¥¡¥ó¥¯¥·¥ç¥ó¡ËÆâ¤Ç

>>¡¡ Èó¥­¡¼¹àÌܤˤè¤ëWhere¾ò·ï¤òÀßÄꤷ¡¢

>>¡¡ Select Into ¶ç¤òÍøÍѤ·¤¿¤È¤­¤ÎµóÆ°¤¬»×ÏǤȰۤʤ롣

>>

>> [¼Â¹Ô´Ä¶­]

>>   ¡¦Linux RHEL6·Ï

>>   ¡¦MySQL 5.7.10-log Community Server

>>¡¡ ¡¦¥¹¥­¡¼¥Þ¤Î¥Ç¥Õ¥©¥ë¥Èʸ»ú¥³¡¼¥É¤ÏUTF-8

>>

>>

>> [ºÆ¸½¼ê½ç]

>>¡¡ £±¡ËÁ°Äó¤È¤·¤Æ¡¢°Ê²¼¥Æ¡¼¥Ö¥ë¤òºî¤Ã¤Æ¤ª¤¯¡£

>>¡¡   CREATE TABLE `dtb_test` (

>>¡¡     `iddtb_test` int(11) NOT NULL AUTO_INCREMENT,

>>¡¡     `cd` varchar(12) DEFAULT '',

>>¡¡     `word` varchar(45) DEFAULT NULL,

>>¡¡     PRIMARY KEY (`iddtb_test`)

>>¡¡   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

>>

>>¡¡ £²¡Ë°Ê²¼¥Ç¡¼¥¿¤òÀßÄꤹ¤ë¡£

>>         ­¡iddtb_test ­¢cd ­£word

>>         |--------------|

>>         |­¡|  ­¢|    ­£|

>>         |--------------|

>>         |1| 1111|  aaaa|

>>         |2| 2222|  bbbb|

>>         |3| 3333|  cccc|

>>         |4| 4444|  dddd|

>>         |5| 5555|  eeee|

>>         ----------------

>>

>>¡¡ £³¡Ë°Ê²¼¥¹¥È¥¢¥É¤ò¥Ç¥â¤È¤·¤ÆºîÀ®¤¹¤ë¡£

>>         DELIMITER $$

>>         CREATE PROCEDURE `test_proc`()

>>         BEGIN

>>

>>         declare var_text text default '';

>>         declare var_text2 varchar(45) default '';

>>

>>         select word,word

>>         into var_text,var_text2

>>         from dtb_test

>>         where cd = '1111';

>>

>>         select var_text,var_text2;

>>

>>         END$$

>>         DELIMITER ;

>>

>>¡¡ £´¡ËºîÀ®¤·¤¿¥¹¥È¥¢¥É¤ò¼Â¹Ô¤¹¤ë¡£

>>

>> [µá¤á¤ë·ë²Ì¤È¤·¤Æ]

>>¡¡¡¡ ºÇ¸å¤ÎSelectʸ¤Ç½ÐÎϤµ¤ì¤ë·ë²Ì¤¬¤¤¤º¤ì¤â[aaaa]¤È¤Ê¤ë¤³¤È¡£

>>

>> [·ë²Ì]

>>¡¡¡¡ var_text ->[eeee]¡Ê¤³¤Á¤é¤¬»×ÏǤȰۤʤë¡Ë

>>¡¡¡¡ var_text2->[aaaa]

>>

>>

>> [Êä­»ö¹à]

>>¡¡ ¡¦MySQL 5.6.26 Community Server (Windows7)¤Ç¤Ïµá¤á¤ë·ë²Ìvar_text ->[aaaa]

>> ¤¬½ÐÎϤµ¤ì¤Þ¤·¤¿¡£

>>¡¡ ¡¦³ºÅö¤Î¾ò·ï¶ç¤Ç¤¢¤ëcd¤òInt·¿¤ËÊѹ¹¤·¤Æ¤âvar_text ->[eeee]¤Ç½ÐÎϤµ¤ì¤Þ¤·

>> ¤¿¡£

>>¡¡ ¡¦¾ò·ï¶ç¤Îcd¤ËÂФ·¤ÆUnique Index¤òÉÕÍ¿¤¹¤ë¤È¡¢var_text ->[aaaa]¤Ç½ÐÎϤµ¤ì

>> ¤Þ¤·¤¿¡£

>>¡¡ ¡¦¸µ¡¹¤Ï͹ÊØÈÖ¹æTBL¤Îzipcode¤Ç¸¡º÷¤ò¤«¤±¤ë¤è¤¦¤Ê½èÍý¤Î¤È¤­¤ËȯÀ¸¤·¤¿»ö¾Ý¤Î

>> °Ù¡¢

>>¡¡¡¡ Unique Index¤ò»ÅÍÍŪ¤ËÉÕÍ¿¤Ç¤­¤«¤Í¤Þ¤¹¡£

>>¡¡ ¡¦Select Into»þ¤Ë Convert(word,char)¤ò¤¹¤ë¤Èvar_text ->[aaaa]¤Ç½ÐÎϤµ¤ì¤Þ

>> ¤·¤¿¡£

>>

>>

>> ¸½¾Ý¤¬È¯À¸¤·¤Æ¤«¤é¡¢Êä­»ö¹à¤ËÄ󼨤·¤¿¥Ñ¥¿¡¼¥ó¤Ê¤É¤ò¸¡¾Ú¤·¤Þ¤·¤¿¡£

>>

>> ¸¡¾Ú¤Î·ë²Ì¡¢varchar·¿¤Îʸ»úÎó¤òTEXT·¿ÊÑ¿ô¤ËÆþ¤ì¤ëºÝ¤Ë

>> ¾ò·ï¶ç¤ò¥¹¥ë¡¼¤·¤Æ¤·¤Þ¤¦¤è¤¦¤ÊÉÔ¶ñ¹ç¤¬¤¢¤ë¤Î¤Î¤«¤Ê¤È¤â»×¤Ã¤Æ¤¤¤Þ¤¹¤¬¡¢

>> ¤â¤·¤«¤·¤¿¤éTEXT·¿¤Èvarchar·¿¤¬Èó¸ß´¹¤È¤¤¤¦²ÄǽÀ­¤â¹Í¤¨¤Æ¡¢

>> ¼ÁÌ䤵¤»¤Æ¤¤¤¿¤À¤­¤Þ¤·¤¿¡£

>>

>> ¤É¤Ê¤¿¤«¤´¶µ¼¨¤¤¤¿¤À¤±¤ì¤Ð¹¬¤¤¤Ç¤¹¡£

>>

>> °Ê¾å¡¢¤è¤í¤·¤¯¤ª´ê¤¤¤¤¤¿¤·¤Þ¤¹¡£

>>

>>


[Á°][¼¡][ÈÖ¹æ½ç°ìÍ÷][¥¹¥ì¥Ã¥É°ìÍ÷]

     16340 2016-05-23 16:28 [<yata.s15ste@xxxxxxx] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë
     16341 2016-05-24 11:00 ¨±["yoku ts." <yoku0825] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë
     16342 2016-05-24 12:43 ¡¡¨±[¤¿¤¯¤ä <yata.s15ste@] RE: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë
->   16343 2016-05-25 14:15 ¡¡¡¡¨±["yoku ts." <yoku0825] Re: [mysql 16342] RE: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë
     16344 2016-05-25 15:42 ¡¡¡¡¡¡¨±[¤¿¤¯¤ä <yata.s15ste@] RE: [mysql 16343] Re: [mysql 16342] RE: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë
     16345 2016-05-25 15:46 ¡¡¡¡¡¡¡¡¨±["yoku ts." <yoku0825] Re: [mysql 16344] RE: [mysql 16343] Re: [mysql 16342] RE: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë¤Ä¤¤¤Æ¡ÊMySQL5.7¡Ë
     16346 2016-05-25 16:01 ¡¡¡¡¡¡¡¡¡¡¨±[¤¿¤¯¤ä <yata.s15ste@] RE: [mysql 16345] Re: [mysql 16344] RE: [mysql 16343] Re: [mysql 16342] RE: [mysql 16341] Re: [mysql 16340] ¥¹¥È¥¢¥ÉÆâSelectInto¶ç¤ÎµóÆ°¤Ë