安小虎 發表於 2021-7-16 16:06:00

IDEA连接数据库出现的时区问题：Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' property manually解决办法

<h2 class="md-end-block md-heading md-focus"><span class="md-plain">1、问题描述</span></h2>
<p class="md-end-block md-p"><span class="md-plain">mysql默认时区与当前的时区冲突。</span></p>
<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded">&nbsp;<span><span class="cm-variable">Server <span class="cm-variable">returns <span class="cm-variable">invalid <span class="cm-variable">timezone. <br>&nbsp;<span><span class="cm-variable">Go <span class="cm-variable">to <span class="cm-string">'Advanced' <span class="cm-variable">tab <span class="cm-variable">and <span class="cm-variable">set <span class="cm-string">'serverTimezone' <span class="cm-variable">property <span class="cm-variable">manually.</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="https://img2018.cnblogs.com/i-beta/1157088/202001/1157088-20200126175953777-1294576602.png"><img src="https://img2018.cnblogs.com/i-beta/1157088/202001/1157088-20200126175953777-1294576602.png" alt="img" width="751" height="467" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<h2 class="md-end-block md-heading"><span class="md-plain">2、解决方案一：通过命令窗口实现（仅单次有效）</span></h2>
<h3 class="md-end-block md-heading"><span class="md-plain">2.1 检查MySQL时区</span></h3>
<p class="md-end-block md-p"><span class="md-plain">（1）进入命令行窗口（Win+R），连接数据库：mysql -hlocalhost -uroot -p，回车后输入密码，再次回车</span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="C:\Users\cjn\AppData\Roaming\Typora\typora-user-images\image-20210716152916227.png"><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716155855942-1801785835.png" alt="" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-plain">（2）继续输入：show variables like'%time_zone';查看时区，注意不要漏掉后面的分号，回车</span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="C:\Users\cjn\AppData\Roaming\Typora\typora-user-images\image-20210716152951884.png"><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716155910781-1882393558.png" alt="" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-plain">（3）查看时区后来设置新的时区：set global time_zone='+8:00'; 注意不要漏掉后面的分号，回车</span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="C:\Users\cjn\AppData\Roaming\Typora\typora-user-images\image-20210716153041937.png"><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716155928782-2141028854.png" alt="" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-plain">之后就可以重新连接数据库了，也许可也连接成功。</span></p>
<h3 class="md-end-block md-heading"><span class="md-plain">2.2 同步MySQL驱动</span></h3>
<p class="md-end-block md-p"><span class="md-pair-s"><strong>方式一</strong><span class="md-plain">：由于本机安装的mysql版本是5.7的，那么IDEA要连接mysql也应该匹配相应的驱动版本，把Driver改成MySQL for 5.1就可以了</span></span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="https://img2018.cnblogs.com/i-beta/1157088/202001/1157088-20200126180109332-308173159.png"><img src="https://img2018.cnblogs.com/i-beta/1157088/202001/1157088-20200126180109332-308173159.png" alt="img" width="745" height="381" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-plain">点击Test Connection进行测试连接</span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="https://img2018.cnblogs.com/i-beta/1157088/202001/1157088-20200126180122447-1679654019.png"><img src="https://img2018.cnblogs.com/i-beta/1157088/202001/1157088-20200126180122447-1679654019.png" alt="img" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-pair-s"><strong>方式二</strong><span class="md-plain">：如果MySQL驱动Driver选择的是MySQL，而数据库是其他版本的</span></span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="C:\Users\cjn\AppData\Roaming\Typora\typora-user-images\image-20210716154301283.png"><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716160002947-1823660754.png" alt="" width="757" height="654" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-plain">在驱动列表里面找到MySQL，在Driver files里，选择对应的MySQL版本，保存即可</span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="C:\Users\cjn\AppData\Roaming\Typora\typora-user-images\image-20210716154357717.png"><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716160028321-169524459.png" alt="" width="754" height="651" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<h2 class="md-end-block md-heading"><span class="md-plain">3、解决方案二：在Advanced（高级）中修改serverTimezone属性（暂时性）</span></h2>
<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded">&nbsp;<span>-serverTimezone &nbsp;Asia/Shanghai</span></pre>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="https://img-blog.csdnimg.cn/20200422112945432.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjQ3Mzg0,size_16,color_FFFFFF,t_70"><img src="https://img-blog.csdnimg.cn/20200422112945432.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjQ3Mzg0,size_16,color_FFFFFF,t_70" alt="img" width="762" height="643" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<h2 class="md-end-block md-heading"><span class="md-plain">4、解决方案三：编辑mysql的安装目录下的my.ini文件（永久性）</span></h2>
<p class="md-end-block md-p"><span class="md-plain">1.编辑mysql的安装目录下的<span class="md-pair-s"><strong>my.ini文件</strong><span class="md-plain">，在<span class="md-pair-s "><strong>mysqld</strong><span class="md-plain">下，加入如下代码：</span></span></span></span></span></p>
<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded">&nbsp;<span> <br>&nbsp;<span>default-time_zone='+8:00'</span></span></pre>
<p class="md-end-block md-p"><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716160401437-697068934.jpg" alt="" width="747" height="477" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></p>
<p class="md-end-block md-p"><span class="md-plain">2.编辑后，记得保存，然后以管理员的身份运行命令提示符重启mysql服务，输入以下的命令。</span></p>
<p class="md-end-block md-p"><span class="md-pair-s"><strong>关闭mysql服务：</strong></span></p>
<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded">&nbsp;<span><span class="cm-variable">net <span class="cm-variable">stop <span class="cm-variable">mysql</span></span></span></span></pre>
<p class="md-end-block md-p"><span class="md-pair-s "><strong>开启mysql服务：</strong></span></p>
<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded">&nbsp;<span><span class="cm-variable">net <span class="cm-variable">start <span class="cm-variable">mysql</span></span></span></span></pre>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="https://img-blog.csdnimg.cn/20200422114836309.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjQ3Mzg0,size_16,color_FFFFFF,t_70"><img src="https://img-blog.csdnimg.cn/20200422114836309.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjQ3Mzg0,size_16,color_FFFFFF,t_70" alt="img" width="766" height="400" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<p class="md-end-block md-p"><span class="md-plain">3.检测是否可以连接，当测试连接成功后就可进行连接</span></p>
<p class="md-end-block md-p"><span class="md-image md-img-loaded" data-src="https://img-blog.csdnimg.cn/20200422115603584.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjQ3Mzg0,size_16,color_FFFFFF,t_70"><img src="https://img-blog.csdnimg.cn/20200422115603584.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjQ3Mzg0,size_16,color_FFFFFF,t_70" alt="img" width="759" height="641" style="display: block; margin-left: auto; margin-right: auto"></span></p>
<h2 class="md-end-block md-heading"><span class="md-plain">5、解决方案四：直接在配置文件后面加上</span>&amp;serverTimezone=UTC<span class="md-plain">（永久性）</span></h2>
<p class="md-end-block md-p"><span class="md-pair-s"><strong><img src="https://img2020.cnblogs.com/blog/2095021/202107/2095021-20210716200508568-680919070.jpg" alt="" width="1032" height="115" loading="lazy" style="display: block; margin-left: auto; margin-right: auto"></strong></span></p>
<p class="md-end-block md-p"><span class="md-pair-s"><strong>参考链接：</strong></span></p>
<ul class="ul-list" data-mark="*">
<li class="md-list-item">
<p class="md-end-block md-p"><span class="md-plain">IDEA连接mysql又报错！Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' prope - King-D - 博客园 <span class="md-link md-pair-s">https://www.cnblogs.com/qingmuchuanqi48/p/12234537.html</span></span></p>
</li>
<li class="md-list-item">
<p class="md-end-block md-p"><span class="md-plain">解决IDEA连接数据库出现的时区问题（图文详细）<span class="md-pair-s "><em>翱翔于世纪的博客-CSDN博客</em><span class="md-plain">idea时区 <span class="md-link md-pair-s">https://blog.csdn.net/qq_43647384/article/details/105677483</span></span></span></span></p>
</li>
</ul>
<p class="md-list-item">&nbsp;</p><br><br>
来源：https://www.cnblogs.com/XiaoCui-blog/p/15020573.html
頁: [1]
查看完整版本: IDEA连接数据库出现的时区问题：Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' property manually解决办法