GreatSQL 8.0.32-25 文档手册捉虫来了！！

GreatSQL社区

搜索

[已解决] 关于GSQL / MySQL死锁日志中的SQL长度问题

354 4 2024-1-12 11:01
本帖最后由 深圳老赵 于 2024-1-12 11:01 编辑

【问题描述】：无论在 gsql 还是mysql 数据库中，若发生死锁，且事务涉及的SQL比较长，那么错误日志中的SQL貌似都没有办法完整展示出来（通常不会超过1000个字符左右），不便于后续的排查工作。请问有什么办法可以解决吗？

【复现】：
数据库版本：gsql （8.0.32-25）、mysql ( 8.0.30 )
步骤：
1、配置参数，开启死锁打印（innodb-print-all-deadlocks = 1），错误日志等级调高（log-error-verbosity = 3）
2、创建测试表，插入数据
3、使用交叉事务模拟死锁产生：


事务1：


begin;


update sbtest1 set k = 0 where id = 1 and 2 and 3 and 4 and 5 and 6 and 7 and 8 and 9 and 10 and 11 and 12 and 13 and 14 and 15 and 16 and 17 and 18 and 19 and 20 and 21 and 22 and 23 and 24 and 25 and 26 and 27 and 28 and 29 and 30 and 31 and 32 and 33 and 34 and 35 and 36 and 37 and 38 and 39 and 40 and 41 and 42 and 43 and 44 and 45 and 46 and 47 and 48 and 49 and 50 and 51 and 52 and 53 and 54 and 55 and 56 and 57 and 58 and 59 and 60 and 61 and 62 and 63 and 64 and 65 and 66 and 67 and 68 and 69 and 70 and 71 and 72 and 73 and 74 and 75 and 76 and 77 and 78 and 79 and 80 and 81 and 82 and 83 and 84 and 85 and 86 and 87 and 88 and 89 and 90 and 91 and 92 and 93 and 94 and 95 and 96 and 97 and 98 and 99 and 100 and 101 and 102 and 103 and 104 and 105 and 106 and 107 and 108 and 109 and 110 and 111 and 112 and 113 and 114 and 115 and 116 and 117 and 118 and 119 and 120 and 121 and 122 and 123 and 124 and 125 and 126 and 127 and 128 and 129 and 130 and 131 and 132 and 133 and 134 and 135 and 136 and 137 and 138 and 139 and 140 and 141 and 142 and 143 and 144 and 145 and 146 and 147 and 148 and 149 and 150 and 151 and 152 and 153 and 154 and 155 and 156 and 157 and 158 and 159 and 160 and 161 and 162 and 163 and 164 and 165 and 166 and 167 and 168 and 169 and 170 and 171 and 172 and 173 and 174 and 175 and 176 and 177 and 178 and 179 and 180 and 181 and 182 and 183 and 184 and 185 and 186 and 187 and 188 and 189 and 190 and 191 and 192 and 193 and 194 and 195 and 196 and 197 and 198 and 199 and 200 and 201 and 202 and 203 and 204 and 205 and 206 and 207 and 208 and 209 and 210 and 211 and 212 and 213 and 214 and 215 and 216 and 217 and 218 and 219 and 220 and 221 and 222 and 223 and 224 and 225 and 226 and 227 and 228 and 229 and 230 and 231 and 232 and 233 and 234 and 235 and 236 and 237 and 238 and 239 and 240 and 241 and 242 and 243 and 244 and 245 and 246 and 247 and 248 and 249 and 250 and 251 and 252 and 253 and 254 and 255 and 256 and 257 and 258 and 259 and 260 and 261 and 262 and 263 and 264 and 265 and 266 and 267 and 268 and 269 and 270 and 271 and 272 and 273 and 274 and 275 and 276 and 277 and 278 and 279 and 280 and 281 and 282 and 283 and 284 and 285 and 286 and 287 and 288 and 289 and 290 and 291 and 292 and 293 and 294 and 295 and 296 and 297 and 298 and 299 and 300;

update sbtest1 set k = 0 where id = 2 and 2 and 3 and 4 and 5 and 6 and 7 and 8 and 9 and 10 and 11 and 12 and 13 and 14 and 15 and 16 and 17 and 18 and 19 and 20 and 21 and 22 and 23 and 24 and 25 and 26 and 27 and 28 and 29 and 30 and 31 and 32 and 33 and 34 and 35 and 36 and 37 and 38 and 39 and 40 and 41 and 42 and 43 and 44 and 45 and 46 and 47 and 48 and 49 and 50 and 51 and 52 and 53 and 54 and 55 and 56 and 57 and 58 and 59 and 60 and 61 and 62 and 63 and 64 and 65 and 66 and 67 and 68 and 69 and 70 and 71 and 72 and 73 and 74 and 75 and 76 and 77 and 78 and 79 and 80 and 81 and 82 and 83 and 84 and 85 and 86 and 87 and 88 and 89 and 90 and 91 and 92 and 93 and 94 and 95 and 96 and 97 and 98 and 99 and 100 and 101 and 102 and 103 and 104 and 105 and 106 and 107 and 108 and 109 and 110 and 111 and 112 and 113 and 114 and 115 and 116 and 117 and 118 and 119 and 120 and 121 and 122 and 123 and 124 and 125 and 126 and 127 and 128 and 129 and 130 and 131 and 132 and 133 and 134 and 135 and 136 and 137 and 138 and 139 and 140 and 141 and 142 and 143 and 144 and 145 and 146 and 147 and 148 and 149 and 150 and 151 and 152 and 153 and 154 and 155 and 156 and 157 and 158 and 159 and 160 and 161 and 162 and 163 and 164 and 165 and 166 and 167 and 168 and 169 and 170 and 171 and 172 and 173 and 174 and 175 and 176 and 177 and 178 and 179 and 180 and 181 and 182 and 183 and 184 and 185 and 186 and 187 and 188 and 189 and 190 and 191 and 192 and 193 and 194 and 195 and 196 and 197 and 198 and 199 and 200 and 201 and 202 and 203 and 204 and 205 and 206 and 207 and 208 and 209 and 210 and 211 and 212 and 213 and 214 and 215 and 216 and 217 and 218 and 219 and 220 and 221 and 222 and 223 and 224 and 225 and 226 and 227 and 228 and 229 and 230 and 231 and 232 and 233 and 234 and 235 and 236 and 237 and 238 and 239 and 240 and 241 and 242 and 243 and 244 and 245 and 246 and 247 and 248 and 249 and 250 and 251 and 252 and 253 and 254 and 255 and 256 and 257 and 258 and 259 and 260 and 261 and 262 and 263 and 264 and 265 and 266 and 267 and 268 and 269 and 270 and 271 and 272 and 273 and 274 and 275 and 276 and 277 and 278 and 279 and 280 and 281 and 282 and 283 and 284 and 285 and 286 and 287 and 288 and 289 and 290 and 291 and 292 and 293 and 294 and 295 and 296 and 297 and 298 and 299 and 300;


事务2：

begin;
update sbtest1 set k = 0 where id = 2 and 2 and 3 and 4 and 5 and 6 and 7 and 8 and 9 and 10 and 11 and 12 and 13 and 14 and 15 and 16 and 17 and 18 and 19 and 20 and 21 and 22 and 23 and 24 and 25 and 26 and 27 and 28 and 29 and 30 and 31 and 32 and 33 and 34 and 35 and 36 and 37 and 38 and 39 and 40 and 41 and 42 and 43 and 44 and 45 and 46 and 47 and 48 and 49 and 50 and 51 and 52 and 53 and 54 and 55 and 56 and 57 and 58 and 59 and 60 and 61 and 62 and 63 and 64 and 65 and 66 and 67 and 68 and 69 and 70 and 71 and 72 and 73 and 74 and 75 and 76 and 77 and 78 and 79 and 80 and 81 and 82 and 83 and 84 and 85 and 86 and 87 and 88 and 89 and 90 and 91 and 92 and 93 and 94 and 95 and 96 and 97 and 98 and 99 and 100 and 101 and 102 and 103 and 104 and 105 and 106 and 107 and 108 and 109 and 110 and 111 and 112 and 113 and 114 and 115 and 116 and 117 and 118 and 119 and 120 and 121 and 122 and 123 and 124 and 125 and 126 and 127 and 128 and 129 and 130 and 131 and 132 and 133 and 134 and 135 and 136 and 137 and 138 and 139 and 140 and 141 and 142 and 143 and 144 and 145 and 146 and 147 and 148 and 149 and 150 and 151 and 152 and 153 and 154 and 155 and 156 and 157 and 158 and 159 and 160 and 161 and 162 and 163 and 164 and 165 and 166 and 167 and 168 and 169 and 170 and 171 and 172 and 173 and 174 and 175 and 176 and 177 and 178 and 179 and 180 and 181 and 182 and 183 and 184 and 185 and 186 and 187 and 188 and 189 and 190 and 191 and 192 and 193 and 194 and 195 and 196 and 197 and 198 and 199 and 200 and 201 and 202 and 203 and 204 and 205 and 206 and 207 and 208 and 209 and 210 and 211 and 212 and 213 and 214 and 215 and 216 and 217 and 218 and 219 and 220 and 221 and 222 and 223 and 224 and 225 and 226 and 227 and 228 and 229 and 230 and 231 and 232 and 233 and 234 and 235 and 236 and 237 and 238 and 239 and 240 and 241 and 242 and 243 and 244 and 245 and 246 and 247 and 248 and 249 and 250 and 251 and 252 and 253 and 254 and 255 and 256 and 257 and 258 and 259 and 260 and 261 and 262 and 263 and 264 and 265 and 266 and 267 and 268 and 269 and 270 and 271 and 272 and 273 and 274 and 275 and 276 and 277 and 278 and 279 and 280 and 281 and 282 and 283 and 284 and 285 and 286 and 287 and 288 and 289 and 290 and 291 and 292 and 293 and 294 and 295 and 296 and 297 and 298 and 299 and 300;

update sbtest1 set k = 0 where id = 1 and 2 and 3 and 4 and 5 and 6 and 7 and 8 and 9 and 10 and 11 and 12 and 13 and 14 and 15 and 16 and 17 and 18 and 19 and 20 and 21 and 22 and 23 and 24 and 25 and 26 and 27 and 28 and 29 and 30 and 31 and 32 and 33 and 34 and 35 and 36 and 37 and 38 and 39 and 40 and 41 and 42 and 43 and 44 and 45 and 46 and 47 and 48 and 49 and 50 and 51 and 52 and 53 and 54 and 55 and 56 and 57 and 58 and 59 and 60 and 61 and 62 and 63 and 64 and 65 and 66 and 67 and 68 and 69 and 70 and 71 and 72 and 73 and 74 and 75 and 76 and 77 and 78 and 79 and 80 and 81 and 82 and 83 and 84 and 85 and 86 and 87 and 88 and 89 and 90 and 91 and 92 and 93 and 94 and 95 and 96 and 97 and 98 and 99 and 100 and 101 and 102 and 103 and 104 and 105 and 106 and 107 and 108 and 109 and 110 and 111 and 112 and 113 and 114 and 115 and 116 and 117 and 118 and 119 and 120 and 121 and 122 and 123 and 124 and 125 and 126 and 127 and 128 and 129 and 130 and 131 and 132 and 133 and 134 and 135 and 136 and 137 and 138 and 139 and 140 and 141 and 142 and 143 and 144 and 145 and 146 and 147 and 148 and 149 and 150 and 151 and 152 and 153 and 154 and 155 and 156 and 157 and 158 and 159 and 160 and 161 and 162 and 163 and 164 and 165 and 166 and 167 and 168 and 169 and 170 and 171 and 172 and 173 and 174 and 175 and 176 and 177 and 178 and 179 and 180 and 181 and 182 and 183 and 184 and 185 and 186 and 187 and 188 and 189 and 190 and 191 and 192 and 193 and 194 and 195 and 196 and 197 and 198 and 199 and 200 and 201 and 202 and 203 and 204 and 205 and 206 and 207 and 208 and 209 and 210 and 211 and 212 and 213 and 214 and 215 and 216 and 217 and 218 and 219 and 220 and 221 and 222 and 223 and 224 and 225 and 226 and 227 and 228 and 229 and 230 and 231 and 232 and 233 and 234 and 235 and 236 and 237 and 238 and 239 and 240 and 241 and 242 and 243 and 244 and 245 and 246 and 247 and 248 and 249 and 250 and 251 and 252 and 253 and 254 and 255 and 256 and 257 and 258 and 259 and 260 and 261 and 262 and 263 and 264 and 265 and 266 and 267 and 268 and 269 and 270 and 271 and 272 and 273 and 274 and 275 and 276 and 277 and 278 and 279 and 280 and 281 and 282 and 283 and 284 and 285 and 286 and 287 and 288 and 289 and 290 and 291 and 292 and 293 and 294 and 295 and 296 and 297 and 298 and 299 and 300;



4、查看日志：


可以看到SQL显示不全。


数据库, GreatSQL, MySQL
全部回复(4)
只看楼主
yejr 2024-1-12 15:20:58
这个是在代码层限制了，可以看下手册中的说明
  1. Standard Monitor output is limited to 1MB when produced using the SHOW ENGINE INNODB STATUS statement.
复制代码
回复 采纳

举报

深圳老赵 2024-1-12 16:42:50
yejr 发表于 2024-1-12 15:20
这个是在代码层限制了，可以看下手册中的说明

如果想修改源代码的限制值，这种做法可行吗？适当调大是否会对mysql本身产生一些影响？GreatSQL有计划把这块变成 由配置参数控制长度吗？
回复 采纳

举报

yejr 2024-1-12 16:53:46
深圳老赵 发表于 2024-1-12 16:42
如果想修改源代码的限制值，这种做法可行吗？适当调大是否会对mysql本身产生一些影响？GreatSQL有计划把 ...

输出太多会影响内存buffer消耗，一般也不建议调大。
如果有需要就阶段性开启general log之类的方法变通下。
回复 已采纳

举报

深圳老赵 2024-1-12 17:03:06
yejr 发表于 2024-1-12 16:53
输出太多会影响内存buffer消耗，一般也不建议调大。
如果有需要就阶段性开启general log之类的方法变通下 ...

收到，感谢解答！
回复 采纳

举报

深圳老赵

1

主题

0

博客

5

贡献

新手上路

Rank: 1

积分
8
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-5-21 15:03 , Processed in 0.040128 second(s), 16 queries , Redis On.
快速回复 返回顶部 返回列表