~hp-mdbug-team/mdbug/trunk

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181
3182
3183
3184
3185
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
3239
3240
3241
3242
3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
3481
3482
3483
3484
3485
3486
3487
3488
3489
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
3711
3712
3713
3714
3715
3716
3717
3718
3719
3720
3721
3722
3723
3724
3725
3726
3727
3728
3729
3730
3731
3732
3733
3734
3735
3736
3737
3738
3739
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849
3850
3851
3852
3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
3884
3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
3941
3942
3943
3944
3945
3946
3947
3948
3949
3950
3951
MDBug: A Debugger for MariaDB and MySQL Routines

This file is part of MDBug.

(c) Copyright 2012 Hewlett-Packard Development Company, L.P.

Copying and distribution of this file, with or without modification,
are permitted in any medium without royalty provided the copyright
notice and this notice are preserved. This file is offered as-is,
without any warranty.

Author: Peter Gulutzan
Last Revised: May 31 2012

MariaDB is a trademark of Monty Program Ab.
MySQL is a trademark of Oracle Corporation and/or its affiliates.
DB2 is a trademark of International Business Machines Corporation.
Other names may be trademarks of their respective owners.



Hewlett-Packard has developed a tool for debugging stored procedures
and functions written for MariaDB 5.5 or MySQL 5.5 (and later). MDBug
consists of a low-level debugger (analogous to GDB) written in SQL, and
a high-level Eclipse plugin GUI (analogous to DDD) written in Java.
This document is the original specification / design document for the
low-level debugger portion of MDBug. It is intended for developers who
are interested in the core debugger internals, perhaps with an objective
of creating their own front end for MDBug.


Contents
========

Introduction
  Introduction: The Alternative Methods: Statement-At-A-Time or Two-Connections
  Introduction: Why I gave up on statement-at-a-time
  Introduction: Surrogate Routines
  Introduction: SQL
  Introduction: Example_Procedure
  Introduction: Walk Through A Procedure
  Introduction: Terminology Notes
Install
  Install: Prerequisites
  Install: The xxxmdbug database
  Install: Privileges
  Install: Installation script
Setup
  Setup: arguments
  Setup: Privilege checks
  Setup: Create SETUP_LOG table
  Setup: Create CONNECTIONS table
  Setup: Create ROUTINES table
  Setup: Create TOKENS table
  Setup: Create STATEMENTS table
  Setup: create VARIABLES table
  Setup: create CALL_STACK table
  Setup: create PREPARED_STATEMENTS table
  Setup: create DIAGNOSTICS table
  Setup: create BREAKPOINTS table
  Setup: create STATEMENTS_EXECUTED table
  Setup: a note about concurrency
  Setup: Insert into ROUTINES table
  Setup: Insert into TOKENS table
  Setup: Insert into STATEMENTS table
  Setup: Insert into VARIABLES table
  Setup: Insert into BREAKPOINTS table
  Setup: Check Views and triggers and events
  Setup: checks and warnings
Generate
  Generate: "create [definer clause] procedure|function"
  Generate: routine name
  Generate: starter
  Generate: label
  Generate: if (statement is debuggable)
  Generate: "while (statement is being debugged)"
  Generate: "call icc_copy_variable_to_table_row"
  Generate: "call icc_core"
  Generate: "call icc_copy_table_row_to_variable"
  Generate: "if (exit) signal"
  Generate: "if (execute) iterate"
  Generate: "if (leave) leave"
  Generate: "if (skip) leave"
  Generate: "insert into statements_executed"
  Generate: handlers
  Generate: statement text
  Generate: leave
  Generate: ender
Debuggee Start
  Debuggee Start: Run the create-surrogate-routines script
  Debuggee Start: Become Debuggee Connection
  Debuggee Start: Copy user variables
  Debuggee Start: Wait for attach command
User Commands
  User Command: 'info'
  User Command: 'attach'
  User Command: 'refresh'
  User Command: 'debug'
  User Command: 'continue'
  User Command: 'break' or tbreak
  User Command: 'break' condition
  User Command: 'step'
  User Command: 'next'
  User Command: 'clear'
  User Command: 'stop'
  User Command: 'set'
  User Command: 'finish'
  User Command: 'exit'
  User Command: 'quit'
  User Command: 'execute'
  User Command: 'kill'
  User Command: 'skip'
  User Command: 'leave'
Common Routines
  Common Routines: A note about comments
  Common Routines: tokenizer
  Common Routines: determine what variables are in scope
  Common Routines: diagnostics_handler
  Common Routines: become_debugger_connection
  Common Routines: icc_get_user_command
  Common Routines: icc_process_user_command
  Common Routines: icc_process_user_command_break
  Common Routines: icc_copy_variable_to_table_row
  Common Routines: icc_copy_table_row_to_variable
  Common Routines: icc_breakpoint_check
  Common Routines: icc_send_statement_status
  Common Routines: icc_core
  Common Routines: Change statement text
  Common Routines: change statement text: special handling of SELECT
  Common Routines: Change statement text: special handling of SHOW WARNINGS
  Common Routines: Change statement text: special handling of dynamic SQL
  Common Routines: Change statement text: special handling of RETURN
  Common Routines: Change statement text: special handling of SIGNAL/RESIGNAL
  Common Routines: command
  Common Routines: send
  Common Routines: README table
ICC (Inter-Connection Communication)
  ICC: The choice of method
  ICC: A working example
  ICC: Protocol
  ICC: Notes
Appendixes
  Appendix: Executing SQL statements on Debugger Connection
  Appendix: Debugging without surrogates
  Appendix: Widgets or "Screens and Menus"
  Appendix: Feature requests for the server

Introduction
============

MDBug will enable any MariaDB or MySQL client to view and control
stored procedures and functions, by sending commands like:
* "debug", to specify a routine
* "step", that is, execute a statement at a time
* "set breakpoint", that is, say when to stop executing
  (featuring temporary breakpoints, conditional breakpoints,
  and setting breakpoints while the routine is still executing)
* "step in", that is, drop down to a routine within a routine
* "display variables" for the point where the procedure has stopped
* "set", to change variable values
* "execute", to insert dynamic SQL while at a breakpoint.

Functionally this is as good as debuggers for Microsoft SQL Server,
IBM DB2 and Oracle 11g. But it has no GUI. For Linux people I could
say "it's like GDB (the GNU Project Debugger) but not like DDD (the GUI
Data Display Debugger which is built on top of the GNU debugger)".
I touch lightly on GUI matters in an appendix.

The main sections of this document are:
"Introduction"       which you're reading now
"Install"
"Setup"
"Generate"
"Debuggee Start"
"User Commands"
"Common Routines"
"ICC (Inter-Connection Communication)"
"Appendixes"

The larger sections of this document are organized in
a way that it should be possible to see, a step at a time,
for an example debuggable routine, how the debugger sets
up tables, generates a copy of the routine, and processes
user commands.

Whenever this document uses the term "MySQL", it should be
read as "MySQL or MariaDB". References to bug numbers are
references to bugs in the MySQL bug database, http://bugs.mysql.com/

I can hope that the description will leave
readers with specific and detailed knowledge
about the requirements. I cannot hope that
the description alone is sufficient. Total
enlightenment will arrive when one uses it
along with the source code of the MDBug (Core)
debugger, a product based on this document
which I wrote for Hewlett-Packard Corporation
in the spring of 2012. At time of writing
MDBug (Core) is an alpha product and MDBug
(GUI) is in a final planning stage.


Introduction: The Alternative Methods: Statement-At-A-Time or Two-Connections
-----------------------------------------------------------------------------

The statement-at-a-time method involves taking each
statement in the stored procedure, in the order that
it would be executed, getting the server to execute
that statement alone, and stopping when it sees that
there's a breakpoint. Sometimes this method is called
"emulation", since such a debugger will act as if it's
in a stored procedure, without really being in it.

The two-connections method involves adding "pause" and
"inter-connection communication" instructions inside the
stored procedure, or inside a copy of the stored procedure.
Then it's run in a separate connection -- thus there's
Connection #1 actually running the procedure and pausing
when breakpoints exist, and there's Connection #2 getting
information from Connection #1 and telling it when it can
stop pausing.

Introduction: Why I gave up on statement-at-a-time
--------------------------------------------------

Initially I started this project by taking
a small routine and executing it, one statement
at a time, with a C client and a GTK+/Glade
interface. I got past a few hurdles, like the
difficulty of finding out which direction to
proceed after conditional statements (IF, WHILE).
But by then I realized that there would be many
more hurdles, for example
* emulating automatic locking within functions
* catching declared handlers in outer procedures
* changing privileges to match invoker privileges.

I also noticed that the in-production statement-at-a-time debuggers
(Illatis and Mydebugger) don't have multiple user testimonials
or active discussions on their sites, like dbForge
and TOAD do. It seems they had failed to impress users.

But the decisive factor is in this example procedure:
CREATE PROCEDURE p1 ()
BEGIN
  UPDATE t SET x=function1() where y=function2();
  END
With both methods it's possible to have a breakpoint
before the UPDATE statement. But only with the
two-connections method is it possible to "step in"
to function1() or recognize a breakpoint in function2().
With the statement-at-a-time method that's next to
impossible because that would require emulating the
inner workings of UPDATE.

So the debugger will use the two-connections method.

Introduction: Surrogate Routines
--------------------------------

Another decision was whether to change routines
in place, or redirect to surrogate routines.
Suppose we had
CREATE PROCEDURE test.p () DROP TABLE t;

"Changing in place" means adding the debugger calls
within test.p().

"Redirecting to surrogate routines" means making a
copy of p in the same database, say, "xxxmdbug000pp",
and adding the debugger calls in the copy. Then,
any calls to test.p() are intercepted by the debugger
and redirected to xxxmdbug000pp().

Obviously surrogate routines are harder. I believe
that changing in place is common practice, because I
read a review that says "both [JDeveloper and TOAD
for Oracle] alter the code to debug PL/SQL".
http://www.tek-tips.com/viewthread.cfm?qid=1140524

Nevertheless, I worried because with MySQL to
support "changing in place" one needs DROP and
CREATE privileges for all the items to be debugged.
I feared that administrators would be reluctant to
grant that for any user who wants to debug something.
Besides, if something goes wrong -- and in early
days something will go wrong -- the debugger could
mess up the changes and affect non-debugger users.

So the debugger will use surrogate routines.

Introduction: SQL
-----------------

Another decision was: what language to write the debugger?

I mentioned C earlier, and that's still a candidate for
some GUI application that invokes the debugger. The
current preference for MDBug is Java with Eclipse.
But the preferable language is:
* totally portable, in fact is available automatically
  to every MySQL user already
* able to handle the problem of parsing a dynamic-SQL
  statement while inside a stored procedure.

So the debugger will be written in SQL.

Introduction: Example_Procedure
-------------------------------

In the many parts of the next sections, about setting
up the debugger database (xxxmdbug) and about generating
the surrogate procedures that will stand in for the original
procedures, I will refer many times to Example_Procedure.
It looks like this. I add line numbers for later reference
and also because some User Commands refer to line numbers.
I made it in the `test` database as user Peter.

Example_Procedure ()
01 BEGIN
02   DECLARE declared_variable INT DEFAULT 5;
03   WHILE @user_variable < 5 DO
04     UPDATE t SET column1 = declared_variable;
05     END WHILE;
06   END

Introduction: Walk Through A Procedure
--------------------------------------

There are two connections.
Debugger Connection takes the user commands.
Debuggee Connection executes the stored procedures.

Debugger Connection and Debuggee Connection use a protocol for
"inter-connection communication" (ICC) which is the subject of
a later section. The effect is that Debuggee Connection will
always freeze when Debuggee Connection reaches a breakpoint,
and Debugger Connection will not tell Debuggee Connection to
continue until after Debuggee Connection reaches a breakpoint.

Debuggee Connection can access in-memory tables which have
descriptions of the current breakpoints and current variable values.
Debugger Connection can get copies of those tables.

Debuggee Connection actually runs altered copies of stored procedures,
which contain extra code to check breakpoints, update variable
values, and handle the ICC. Usually the debugger user is unaware
of the extra code, and other users are unaffected because the
altered copies of stored procedures are kept separate, and invoke
common routines in a separate 'xxxmdbug' database which is seen only
by Debugger Connection and Debuggee Connection. That is, the original
stored procedures are not changed unless the debugger user requires
extra functionality.

Suppose a procedure looks like the earlier-described Example_Procedure.
The debugger finds this text by selecting from mysql.proc.
The debugger finds the tokens and the statement
boundaries with a tokenizer() procedure written in SQL.

The debugger makes a copy of the procedure
which has extra debugger-specific statements in it:
CREATE PROCEDURE test.xxxmdbug000pExample_Procedure ()
BEGIN
  /* Statements to intercept handlers and add to call stack */
  BEGIN
    DECLARE declared_variable INT DEFAULT 5;
    WHILE @user_variable < 5 DO
      /* Statements for inter-connection communication */
      UPDATE t SET column1 = declared_variable;
      END WHILE;
  END;
  /* Statements to remove from call stack */
END

Debugger Connection sends a message commanding "debug Example_Procedure".
Debuggee Connection receives the message, sees that Example_Procedure
has a surrogate routine, and so says
"CALL test.xxxmdbug000pExample_Procedure()".
It quickly reaches the "Statements for inter-connection
communication" which precede the first debuggable statement.
And now it pauses (it hasn't been ordered to "continue"),
looping, waiting for a message to appear from Debugger Connection and
calling a Sleep() function if there are no messages.

Debugger Connection sends a message commanding "continue".
Debuggee Connection receives the message; stops the
waiting and sleeping. Now it executes the statement
"UPDATE t SET column1 = declared_variable;". Over and over.
The WHILE is an infinite loop.

Debugger Connection sends a message commanding "break on
every line". This means every line of the original routine
(user commands always refer to the original routine not
the surrogate routine). Debuggee Connection receives the
message and inserts a row in a BREAKPOINTS table.
Did you notice the asynchronicity? Debuggee Connection can
accept a "break" message even while it's still in its infinite
WHILE loop, because it checks for messages every time it's
about to execute a statement.

Debuggee Connection now goes on to the next statement -- but
the "statements for inter-connection communication" include
a search of the BREAKPOINTS table. Debuggee Connection discovers
that there's a breakpoint for line 4, so once again it
enters a wait-and-sleep loop. Debugger Connection can see that's
happening because Debuggee Connection sends a message about
its statement status, which Debugger Connection can read.

Debugger Connection now looks in the message to get the
information "what statement is being executed?", and
in a table to get the information "what is the value
of @user_variable?". There's occasional need to send a message for
these "get information" requirements, because Debuggee
Connection updates the tables constantly, and Debugger
Connection needs Debuggee Connection's help to update
its own copies of those tables. Anyway, the answers are
"the UPDATE statement on line 4 is being executed" and
"the value of @user_variable is 0". (I happened to set
@user_variable=0 earlier on the debuggee connection.)

Presumably, after the user asks for "continue" a few times
and sees that the Debuggee Connection keeps stopping at the
breakpoint for line 4 every time and the value of @user_variable
is always the same, the user will say "aha, the WHILE is
always looping because its conditional variable, @user_variable,
is never changing". The bug has been found.

Debugger Connection sends a message commanding "set @user_variable = 5".
Debuggee Connection receives the message and changes the value of
@user_variable, and also changes the USER_VARIABLES table so that
Debugger Connection can read it if desired. Now, if the next
command is "continue", the loop will end because the
condition ("WHILE @user_variable < 5") is false at last.

Debugger Connection sends a message commanding "exit".
Debuggee Connection receives the message and signals an error.
Among the statements at the start of the routine,
"/* Statements to intercept handlers and add to call stack */",
there is a DECLARE EXIT HANDLER which intercepts the error
and thus ends the execution of test.xxxmdbugpExample_Procedure.

The user, meanwhile, has seen none of this.
The user has issued commands (or clicked for GUI widgets)
which cause the original procedure to appear on the screen,
made it run, made it stop again when it obviously was looping,
seen that line 4 is highlighted on the display, brought up
a variable display, changed what was causing the loop,
and ended. The details are our problem, not the user's.

Introduction: Terminology Notes
-------------------------------

Oracle prefers the terms "debug session" and "target session"
but I think "Debugger Connection" and "Debuggee Connection"
are better because they're more specific. The word "debuggee"
exists in Wiktionary.

Oracle people seem to be talking about the two-connections method
when they use the term "server-side debugging". Perhaps that's
the better term, but it doesn't distinguish from the main
alternate method, since statement-at-a-time debugging would also
send all statements to be executed on the server side.

The term "surrogate routine" is not used by anybody else.

Oracle uses the term "inter-session communication" in discussion
of DBMS_PIPE / DBMS_ALERT. I've chosen the term "inter-connection
communication" instead, because MySQL tends to use the term
"connection" or "thread" more frequently than "session",

Install
=======

I'm expecting install to be easy. We supply an SQL
script and a manual. The administrator should check
'Prerequisites' and the required 'Privileges' which
are the subject of this section. Then the
administrator runs the script.

Install: Prerequisites
----------------------

The database server must support SIGNAL + RESIGNAL.
Therefore the MariaDB/MySQL version must be 5.5.0 or later.

Install: The xxxmdbug database
------------------------------

It's not a formal naming convention, but database names
that end with "_schema" are used for system objects.
Examples are "information_schema" and "performance_schema".
So I considered using "xxxmdbug_schema" for the database
that contains objects that are necessary for debugging.
However, "xxxmdbug" alone was deemed sufficient.

We'll declare a reserved namespace thus:
"Any name that begins with 'xxxmdbug' may be used
by the debugger. We strongly recommend that users avoid
names that begin with 'xxxmdbug' in other applications."

The xxxmdbug database contains all the fixed
routines which have to be used as common utilities.
It is created by the installation script. The installed
routines are permanent, that is, there is no need to change
them unless an upgrade occurs.
It's probably going to save space if administrators add
xxxmdbug to the list of databases which are not subject
to row-based replication, using --binlog-ignore-db
or --replicate-ignore-db.

At first the plan was to put all the surrogate routines
in the xxxmdbug database or in user-specific copies thereof.
That didn't work, because a routine's "default" database
is the database it's defined in, and the defaults must be
the same for the surrogate as for the original. Therefore
the surrogate routines must be in the same database as the
original. And it must be possible to find the surrogate
name given the original name, or vice versa.

So the format of a surrogate name is always:
CONCAT('xxxmdbug',nnn,p-or-f,original name)
where 'xxxxmdbug' is our reserved-for-debug-use prefix,
where nnn is a 3-letter value between 000 and ZZZ,
where p-or-f is either P for procedure or F for function,
where original name is the name of the original routine.
For example, the first time that one creates a surrogate
for procedure example_procedure, the name will be
xxxmdbug000PExample_Procedure.

The nnn part of a surrogate procedure is the
"setup group identifier". Each time you run the
setup() procedure which creates new surrogates,
you get a new setup group identifier. The setup()
procedure never deletes or modifies existing surrogates.

The flaws in this scheme are:
1. Maybe an original name is long, and adding a prefix
   like 'xxxmdbug000P' (11 characters) will make a name
   longer than MySQL's maximum length of 64 characters.
   I don't think long names are common. But if they are:
   Use shorter original names.
2. Maybe the setup group identifier is already at its
   maximum value which is ZZZ. I don't think that's
   likely because the setup() procedure assigns ascending
   values containing any digit or Latin letter, so the
   number of possibilities is (36*36*36), which is lots.
   But if it happens:
   Destroy all surrogates and start again.

In this document I'll say "xxxmdbug000PExample_Procedure"
to mean "whatever name the setup() procedure has assigned".

Install: Privileges
-------------------

The debugger user must have extra privileges besides
the privileges needed to execute the routines.

The administrator will have to grant at least these:

/* Privileges necessary for running the installation script. */
GRANT CREATE ON xxxmdbug.* TO install_user;
GRANT SUPER ON *.* TO install_user;
GRANT SELECT,INSERT ON mysql.proc TO install_user;

/* Privileges necessary for setup() in order to create
   surrogate routines. */
GRANT CREATE TEMPORARY TABLES ON xxxmdbug.* TO setup_user;
GRANT CREATE ROUTINE ON xxxmdbug.* TO setup_user;
GRANT EXECUTE ON xxxmdbug.setup TO setup_user;

/* Privileges necessary for debuggee, assuming the
   intent is to debug procedure test.Example_Procedure. */
GRANT SELECT ON xxxmdbug.* TO debuggee_user;
GRANT EXECUTE ON test.Example_Procedure TO debuggee_user;
GRANT EXECUTE ON test.xxxmdbug000PExample_Procedure TO debuggee_user;
GRANT EXECUTE ON xxxmdbug.become_debuggee_connection TO debuggee_user;
GRANT EXECUTE ON xxxmdbug.debuggee_wait_loop TO debuggee_user;

/* Privileges necessary for debugger, assuming the
   intent is to debug procedure test.Example_Procedure. */
GRANT SELECT ON xxxmdbug.* TO debugger_user;
GRANT EXECUTE ON test.Example_Procedure TO debugger_user;
GRANT EXECUTE ON test.xxxmdbug000PExample_Procedure TO debugger_user;
GRANT EXECUTE ON xxxmdbug.command TO debugger_user;

/* Privilege useful for looking at PROCESSLIST.
   It's 'nice to have' but not necessary. */
GRANT PROCESS ON *.* TO debugger_user;

/* Resource limit necessary so that the user can
   make more than one connection, if the same user is
   both debugger and debuggee (which is normal). This should be
   at least 2. The default, '0', means 'no limit'. */
GRANT USAGE ON *.* TO debuggee_user / debugger_user;
WITH max_user_connections 0;

Actually I expect that the typical user will simply be root,
but I showed the minimal necessary privileges so that people
won't think that being root is necessary. The privileges for
debugger and debuggee will look innocuous, so I'll have to warn:
the debuggee_wait_loop() and command() routines are executed
with definer privileges. I promise that the "INSERT ON mysql.proc"
privilege will only be used to add surrogate routines, and the
"SUPER ON *.*" privilege will only be used for ICC. However,
careful administrators should verify my promises by looking
at the source code.

Install: Installation script
----------------------------

I considered the name xxxmdbug_common_routines.sql.
The name is patterned after a name in MySQL's ~/share
directory, mysql_system_tables.sql. However, the
name install.sql was deemed sufficient.

Inside install.sql is a series of CREATE statements.
The statements put routine definitions in xxxmdbug.

So, to install the debugger, the following is sufficient:

For MySQL:
mysql> source install.sql

For MariaDB:
MariaDB [(default_schema)]> source install.sql

In all futher cases, where this document shows the MySQL prompt,
it should be read as "either the MySQL prompt or the MariaDB prompt".

And that's the end of the installation process.
The next step is to call setup().

Setup
=====

Setup: arguments
----------------

Setup() is an SQL stored procedure in xxxmdbug.
It is called by an application.
Its job is to call the create* routines.

setup (switches and list of routines)

The switches will rarely be necessary,
and omitting them will cause a reasonable default.
But I'll discuss each switch because that will
reveal some of the design problems.

[ -track_user_variables ]

The possible values are '0', '1, '2'.
It saves space to say '0'.

[ -track_variables ]

The possible values are '0', '1, '2'.
It saves space to say '0'.

[ -track_system_variables ]

The possible values are '0', '1, '2'.
It saves space to say '0'.

[-signal_number]

The possible values are integers between 5000 and 9999.

The default is 5678.

The user can use a different signal_number value
if there is a warning that the default value is
used by SIGNAL statements in the original routines.
See section "Setup: checks and warnings".

I didn't implement this, I decided it's an unlikely problem.

[ -engine]

The possible values are engines listed in information_schema.engines.

The debugger's temporary tables have to be made with a storage engine.
The default could be engine=memory because:
*  I don't want ROLLBACK to affect changes that I make to tables in
   xxxmdbug, and I can't control whether ROLLBACK will happen.
   So transactional engines are inappropriate.
*  It doesn't matter if the data disappears; one can get it back
   by calling "Setup" again.
On the other hand, one might want to say engine=myisam if:
*  Everything in BLOB or TEXT columns is important. Since the
   MEMORY engine can't have BLOB or TEXT, and its storage is fixed-length
   even for VARCHAR, it will convert to easier-to-use data types.
*  The server variable MAX_HEAP_TABLE_SIZE is small and hard to change.

I didn't implement this, I decided it should be up to the designer.

[destroy_existing_objects]

The possible values are 'True' and 'False'.

The default is 'True'.

'True' means "if there's anything in this database already,
get rid of it". There might be some times when it's not
absolutely necessary, though.

I didn't implement this.

[maximum_variable_length]

The possible values are integers between 1 and 1000.

The default is 64.

If any declared-variable or user-variable value is longer than
maximum_variable_length bytes, the procedure will run but
users won't see the complete value in the VARIABLES tables.

I didn't implement this, I decided 64 ought to be plenty.

After the switches comes the list of routines.
This has to be flexible because we want to encourage users
to only call setup() for what's necessary for immediate needs.
Frankly setup() is going to be slow initially, and maybe forever.
So the list is comma-separated and wildcards % or _ are legal.

I suppose that users will assume that the listed routines
must exist, but in fact there won't be any error messages
if they don't exist, because (as the allowance of wildcards
implies) this is really a "search list" more than a "requirement
list". If the routine's there, fine. If it's not, it won't come
out in the setup_log. So users have to look at the setup log.

I assume that users will assume that the default database is
the one that they said USE for, but in fact the default database
is 'xxxmdbug' because that's where the setup routine lies.
Nobody ever wants that, so I decided: if the user doesn't
specify a schema, the arbitrary assumption is: it's `test`.

The routines that actually come out are the 'setup group'.

The setup() procedure looks approximately like this:
CREATE PROCEDURE setup ()
BEGIN
  CALL privilege_checks();
  CALL create_setup_log_table()
  CALL create_connections_table()
  CALL create_routines_table()
  CALL create_tokens_table()
  CALL create_statements_table()
  CALL create_variables_table()

  For (each routine from the list)
    CALL insert_into_routines()
    CALL insert_into_tokens() /* "tokenizer" */
    CALL insert_into_statements()
    CALL insert_into_variables()
    CALL generate()

  CALL view_and_trigger_and_event_check()
  CALL checks_and_warnings(database_name)

After the setup() procedure finishes, there optionally is
a final Setup step which could be run from an
application program, as described in section
"Debuggee Start: Run the create-surrogate-routines script".

Setup: Privilege checks
-----------------------

privilege_checks(database_name)
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to warn, with SIGNAL statements,
about problems that are detectable at the start.

These checks don't guarantee that "Setup" will succeed,
but it's worth warning the user before we go in deeply.

* max_user_connections should not be maxed out.
  Usually we'll be creating a new connection
  ("Debuggee Connection") after Setup.

* The SUPER privilege allows creation of routines with
  the same definer as the original creator. Ordinarily
  that's necessary so that the surrogate procedures will
  run exactly as the original procedures would. Also,
  the SUPER privilege is necessary for Inter-Connection
  Communication.

* The user must have CREATE privileges for the xxxmdbug
  database and all the object types (base tables, views,
  functions, procedures) that will go into it.

* It is best if the user has some privilege, at least
  EXECUTE privilege, for every routine that could possibly
  be accessed during the debug session, directly or indirectly.
  The debuggee will have to call the original routines
  rather than the surrogate routines whenever it can't see
  the metadata, and that could mean that the debuggee is
  unaware of hidden changes to user variables.

* The system variable @@innodb_lock_wait_timeout is 50 by
  default. This might be too small since a debug session
  will often cause a pause for many seconds while waiting
  for user input.

Setup: What's left unsaid
-------------------------

As a general rule, I won't describe
"If (something goes wrong), then (do this)."
Just take it that lots of things can go
wrong, and be ready to emit an error
message and tear everything down again.

If the administrator has been careful
about GRANT settings, it's possible to
make sure that the only person who can
view the database contents is the user
who is debugging.

We are now ready to create tables and procedures.

Setup: Create SETUP_LOG table
-----------------------------

create_setup_log_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to create the SETUP_LOG table.

CREATE TABLE SETUP_LOG (
  User
  Version_number_of_debugger
  Timestamp_when_setup_procedure_was_run
  Arguments_passed_to_setup
  Is_setup_done
  Comment_based_on_readme)

The SETUP_LOG table will have a note about what the
setup() procedure did.

Setup: Create ROUTINES table
----------------------------

create_routines_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to create the ROUTINES table.

CREATE TABLE ROUTINES (
  Routine_schema_of_original
  Routine_name_of_original
  Routine_name_of_surrogate
  Last_altered
  Definition_of_original_routine
  Definition_of_surrogate_routine)

The ROUTINES table will have a row for every original
routine and its surrogate routine.
See "Insert into ROUTINES table".

Setup: Create TOKENS table
--------------------------

create_tokens_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once per routine.
Its job is to create the TOKENS table.

CREATE TEMPORARY TABLE TOKENS (
  Routine_name
  Token_number
  Character_number
  Line_number
  Value)

The TOKENS table is used when "parsing" routine
definitions to find out what tokens are in the routine.
See "Insert into TOKENS table".

Setup: Create STATEMENTS table
------------------------------

create_statements_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once per routine.
Its job is to create the STATEMENTS table.

CREATE TABLE STATEMENTS (
  Statement_number
  Routine_schema
  Routine_name
  Statement_number_within_routine
  Token_number_of_first_token
  Line_number_of_start_of_first_token
  Character_number_of_start_of_first_token
  Token_number_of_last_token
  Line_number_of_end_of_last_token
  Character_number_of_end_of_last_token
  Type)

The STATEMENTS table has a row for every statement
in every routine.
See "Insert into STATEMENTS table".

Setup: Create VARIABLES table
-----------------------------

create_variables_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once per routine.
Its job is to create the VARIABLES table.

CREATE TABLE VARIABLES (
  Routine_schema
  Routine_name
  Token_number_of_declare
  Variable_name
  Data_type
  Value
  Is_changed_by_last_statement
  Is_in_scope
  Is_settable
  Is_updated_by_set

The VARIABLES table has one row for every declared
variable or parameter in every routine, and one row
for every user variable.
See "Insert into VARIABLES table".

It became necessary to split into three different tables:
VARIABLES for declared variables only, plus USER_VARIABLES,
plus SYSTEM_VARIABLES.

Setup: a note about locking and concurrency
-------------------------------------------

Conflict should be rare during a debug session.

The Debuggee Connection never writes or reads MDBug tables.
It only "writes" by appending to a user variable, and "reads"
by copying from a user variable. Thus there can be very long
user variables which are in effect pseudo-tables.

Except for SETUP_LOG, all MDBug tables are TEMPORARY tables.
The contents of TEMPORARY tables are local to a session.

Therefore multiple instances of Debugger Connection and
Debuggee Connection can read and write MDBug tables and
pseudo-tables without interfering with each other.

The ICC mechanism depends on changes to a global variable,
@@init_connect. Global variables are protected by a mutex.

To prevent two connections from both trying to do a
"Setup" at the same time, we have a "GET_LOCK('xxxmdbug_lock',1)"
statement at the start of setup(), and we release xxxmdbug_lock
at the end of setup().

To prevent a third connection (a non-debugger connection)
from changing xxxmdbug objects, we depend on the
privilege system. So this is an administrator's problem.

If a third connection alters or drops original routines,
Debuggee Connection will discover that (see section
"Generate: starter"), and end the debugging session.

Neither Debugger Connection nor Debuggee Connection will
ever use an explicit LOCK statement or GET_LOCK function,
while in a debug session.

Setup: Insert into ROUTINES table
---------------------------------

For each row in mysql.proc:
* If the 'db' column value (that is, the schema/database name)
  is 'mysql' or 'information_schema' or 'performance_schema'
  or like 'xxxmdbug%', skip it. (We don't want to make
  surrogate routines for surrogate routines.)
* If the 'db' and 'name' column values don't match what
  was requested by setup argument 'original_routine_name',
  skip it.
* If the 'language' column value is not 'SQL', skip it.
  Right now all routines are SQL, but there's a chance
  that someday somebody (maybe Antony Curtis) will add
  a feature for supporting external-language routines.
* Figure out routine_name_of_surrogate.
  Try to make this with a concatenation of 'xxxmdbug',
  a three-character setup group name (see xxxmdbug.get_setup_group_name() to see how this formed),
  a 'P' for 'Procedure' of an 'F' for 'Function',
  and the original routine name.
  If the concatenation result is too long, that's an error.
  Names that contain information are preferable because
  they might be long-lasting (see appendix
  "Debugging without surrogates").
* INSERT INTO ROUTINES.

SIDEBAR: Why use mysql.proc rather than information_schema.routines?
Generally I prefer to use SQL-standard information_schema tables.
But try this:
CREATE PROCEDURE pd () SELECT * FROM `t``v x`;
SELECT body from mysql.proc where name='pd';
SELECT routine_definition from information_schema.routines where routine_name='pd';
The first SELECT will return:  SELECT * FROM `t``v x`
The second SELECT will return: SELECT * FROM `t`v x`.
It is impossible to tokenize the return from the second SELECT. 
This is Bug#58342 ROUTINES.ROUTINE_DEFINITION removes double quotes
http://bugs.mysql.com/bug.php?id=58342.

Based on the Example_Procedure,
ROUTINES now looks like this:
#Row 1
Routine_schema_of_original            test
Routine_name_of_original              Example_Procedure
Routine_name_of_surrogate             xxxmdbug000PExample_Procedure
Definition_of_original_routine
 BEGIN
   DECLARE declared_variable INT DEFAULT 5;
   WHILE @user_variable < 5 DO
     UPDATE t SET column1 = declared_variable;
     END WHILE;
   END

Setup: Insert into TOKENS table
-------------------------------

insert_into_tokens_table()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once for each routine.
Its job is to populate the TOKENS table.

We're dealing with stored routines that already exist, so we
know that the text can be parsed -- MySQL has already done it.
However, MySQL won't supply the parse tree.
We have to do it ourselves, and this is
an SQL routine, so it's going to be slow.
Luckily, our 'parser' won't have to understand very much.
It just needs to recognize statements that are
debuggable, and recognize references to routine names.

The main job is to break up an SQL-text string
(the routine definition) into its basic components
of names and keywords and operators -- the "tokens".

More description is in section "Common Routines: tokenizer".
It can't handle more than a few hundred tokens per second,
but that should be okay.

Source is:
* routine_definition from ROUTINES table
* plus view_definition from information_schema.views
  plus action_statement from information_schema.triggers
  plus event_definition from information_schema.events
  (We only look at views and triggers and events for the
  sake of an error check, see section
  "Setup: Check Views and triggers and events".)

Each token will be inserted into the TOKENS table.

Based on the Example_Procedure,
TOKENS now looks like this:

+-------------------+---------+------------+--------+-------------------+
| Routine_name      | Token_  | Character_ | Line_  | Value             |
|                   | number  | number     | number |                   |
+-------------------+---------+------------+--------+-------------------+
| Example_procedure |       1 |          1 |      1 | BEGIN             |
| Example_procedure |       2 |          9 |      2 | DECLARE           |
| Example_procedure |       3 |         17 |      2 | declared_variable |
| Example_procedure |       4 |         35 |      2 | INT               |
| Example_procedure |       5 |         39 |      2 | DEFAULT           |
| Example_procedure |       6 |         47 |      2 | 5                 |
| Example_procedure |       7 |         48 |      2 | ;                 |
| Example_procedure |       8 |         52 |      3 | WHILE             |
| Example_procedure |       9 |         58 |      3 | @user_variable    |
| Example_procedure |      10 |         73 |      3 | <                 |
| Example_procedure |      11 |         75 |      3 | 5                 |
| Example_procedure |      12 |         77 |      3 | DO                |
| Example_procedure |      13 |         84 |      4 | UPDATE            |
| Example_procedure |      14 |         91 |      4 | t                 |
| Example_procedure |      15 |         93 |      4 | SET               |
| Example_procedure |      16 |         97 |      4 | column1           |
| Example_procedure |      17 |        105 |      4 | =                 |
| Example_procedure |      18 |        107 |      4 | declared_variable |
| Example_procedure |      19 |        124 |      4 | ;                 |
| Example_procedure |      20 |        130 |      5 | END               |
| Example_procedure |      21 |        134 |      5 | WHILE             |
| Example_procedure |      22 |        139 |      5 | ;                 |
| Example_procedure |      23 |        143 |      6 | END               |
+-------------------+---------+------------+--------+-------------------+

Setup: Insert into STATEMENTS table
-----------------------------------

insert_into_statements()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once for each routine.
Its job is to populate the STATEMENTS table.

Once you know where the tokens are, it's easy to find
where the statements start and end.

The end of a statement is:
  end of string, or
  ;

The start of a statement is:
  start of string, or
  anything that comes after ;

There are just a few sequences of tokens which we must
treat specially, if they're at the start of a statement:
BEGIN
REPEAT
LOOP
WHILE as far as DO
IF as far as THEN
ELSE
ELSEIF as far as THEN
UNTIL as far as ;
DECLARE {CONTINUE|EXIT} HANDLER as far as last condition_value
label as far as :
/* comment */
We treat the special sequences as "statements".
Although none of them are executable, we want to
separate them from the debuggable statements.

So go through the TOKENS table, and whenever you
encounter a new statement, INSERT INTO STATEMENTS.

Finding statement start/end is also possible with a parser.
But do not try to find statement start/end by checking
whether a token is a keyword that starts a statement, such
as SELECT or INSERT. That fails because some keywords
are not reserved words (for example GET and START and BACKUP),
and some keywords may or may not be statement starters
(for example CASE and CHECK).

Each token will be inserted into the STATEMENTS table.

The possible values of the Type column are:
"Declare"           DECLARE variable|handler|condition
"Flow Control"      BEGIN + END, WHILE + END WHILE,
                    LOOP + END LOOP, REPEAT + UNTIL
"Label"             label and : following label
"Debuggable"        everything else
The generate procedures check for Type="Debuggable" to
determine whether to generate ICC routines before a statement.

The Debugger Connection could use
  Token_number_of_first_token
  Line_number_of_start_of_first_token
  Character_number_of_start_of_first_token
  Token_number_of_last_token
  Line_number_of_end_of_last_token
  Character_number_of_end_of_last_token
  Type
to determine "can I set a breakpoint on this
line?" and what statement is the cursor on?".
Our implementation doesn't bother with that, though.

Based on the Example_Procedure,
STATEMENTS now looks like this:

Row #1 /* BEGIN */
  Statement_number                           1
  Routine_schema                             test
  Routine_name                               Example_Procedure
  Statement_number_within_routine            1
  Token_number_of_first_token                1
  Line_number_of_start_of_first_token        1
  Character_number_of_start_of_first_token   1
  Token_number_of_last_token                 1
  Line_number_of_end_of_last_token           1
  Character_number_of_end_of_last_token      5
  Type                                       Flow Control
Row #2 /* DECLARE declared_variable INT DEFAULT 5; */
  ...
Row #3 /* WHILE @user_variable < 5 DO */
  ...
Row #4 /* UPDATE t SET column1 = declared_variable ; */
  ...
Row #5 /* END WHILE; */
  ...
Row #6 /* END */
  ...

Setup: Insert into VARIABLES table
----------------------------------

insert_into_variables_table()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once for each routine.
Its job is to populate the VARIABLES table.

There are separate tables for declared variables, user variables,
and parameters.

The syntax for declaring parameters is
[ IN | OUT | INOUT ] param_name type
http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html
and the copy of the parameter list is in mysql.proc.param_list.

The syntax for declaring variables inside compound statements is
DECLARE var_name [, var_name] ... type [DEFAULT value];
http://dev.mysql.com/doc/refman/5.6/en/declare-local-variable.html

Don't worry about "[DEFAULT value]". There's no need to
try to "execute" a DECLARE statement. Just look for
each var_name, then look for type, then insert into Variables:
var_name, type, token-number. Value will be NULL initially.

You need to put user-defined variables in the USER_VARIABLES table.
We'll have an is_user_variable() function, which returns TRUE
for names that begin with '@', but not with '@@'
(it's feasible to include all the server variables that begin
with '@@' but there's another table for that.)
So look in the TOKENS table for distinct token names
that start with '@' and add them to the USER_VARIABLES table.
The USER_VARIABLES table will have a row for every user variable
that is referenced in a stored procedure or function,
or is referenced in a view/trigger/event that the user
has privileges for. It is possible that the user defined
other user variables, but we won't be aware of them.
See also
Bug#20307 Request SHOW command support for User-Defined Variables
http://bugs.mysql.com/bug.php?id=20307

Token_number_of_declare is NULL for a user-defined variable,
zero for a parameter, and a token_number for a DECLARED variable.
We need to know it for scope checking (see section
"Common Routines: determine what variables are in scope").

A note about the value column in the VARIABLES table.
In the routine, different variables can have different data types.
In the VARIABLES table, the value column has only one data type: VARBINARY.
This means that only one type of comparison is possible: exact equality.
For example, 'A' is never equal to 'a', and 04.0 is never equal to 4.
That affects breakpoint conditions (see "User Command: 'break' condition")
and it affects searches of the VARIABLES table with Debugger Connection.
We could improve matters by having several different
value columns in the VARIABLES table (value_if_string,
value_if_approximate_numeric, value_if_exact_numeric, etc.).
We could improve matters by casting to the original data type
before any comparison.
But I'm currently hoping that we won't need such improvements.
Let's see whether users complain that only exact comparisons are possible.

Based on the Example_Procedure,
VARIABLES now looks like this:
 Routine_          Token_   Variable_         Data_
 name              number_  name              type
                   of_
                   declare
 --------          ------- ----------         -----
 Example_Procedure       2 declared_variable  INT

USER_VARIABLES now looks like this:
 Routine_          Token_   Variable_         Data_
 name              number_  name              type
                   of_
                   declare
 --------          ------- ----------         -----
 Example_Procedure    NULL @user_variable     LONGTEXT

Setup: Check Views and triggers and events
------------------------------------------

views_and_trigger_and_event_check()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.

Ordinarily we will not debug any routines that
are invoked via views and triggers and events. 
For example, suppose a user creates thus:
CREATE TABLE t (s1 INT);
CREATE TRIGGER t AFTER UPDATE ON t FOR EACH ROW CALL p(@x);
We don't make "surrogate triggers" that would change
the "CALL p(@x)" to "CALL surrogate-routine-for-p(@x)".
The user might choose to do so (see appendix
"Debugging without surrogates").
But the sensible assumption during setup() is that
"CALL p(@x)" calls the original routine.

For each view in information_schema.views
  call insert_into_tokens(view_definition);

For each trigger in information_schema.triggers
  call insert_into_tokens(action_statement);

For each event in information_schema.events
  call insert_into_tokens(event_definition);

For each row in TOKENS
  if check("is it a user variable") = true
  or check("is it a routine name") = true
    put a warning in the DIAGNOSTICS table.
(For explanation of how to tell if a routine is
a routine, see section "Generate: statement text".)

Update: view_and_trigger_and_event_check() was never done.
It's just a stub.

Setup: checks and warnings
--------------------------

checks_and_warnings()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to pass warnings to the caller, with SIGNAL and SELECT.

At the end of the Setup, check whether at any
point during the Setup there was a warning.
This would indicate that there's a known problem,
perhaps a minor one, with the original routines.

Possible warnings are:

SIGNAL_NUMBER. See "Common Routines: Change statement text:
special handling of SIGNAL/RESIGNAL".

UNDEBUGGED FUNCTION. See
"Check views and triggers and events"

Update: checks_and_warnings() was never done.
It's just a stub.

Setup: Insert into SETUP_LOG table
----------------------------------

insert_into_setup_log()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to fill in SETUP_LOG.

The Debuggee Start procedures check whether SETUP_LOG
is complete. If it isn't, Debuggee Start won't start.

Generate
========

In the "Generate" phase, we create the CREATE statements
for the surrogate routines.

For each routine /* Coming from ROUTINES table */
  Generate: "create [definer clause] procedure|function"
  Generate: routine name
  Generate: starter
  For each statement in the routine /* Coming from STATEMENTS table */
    Generate: label
    If (statement is debuggable)
      Generate: IF (statement is to be debugged) THEN
                  call icc_copy_variable_to_table_row
                  call icc_core
                  call icc_copy_table_row_to_variable
                  if (exit) signal
                  if (leave) leave
                  if (skip) leave
                  insert into statements_executed
                  END IF
    Generate: handlers
    Generate: statement text
  Generate: leave
  Generate: ender

At the end of the generation, each row in ROUTINES has
a CREATE PROCEDURE or CREATE FUNCTION statement in
its Definition_of_surrogate_routine column.

Generate: "create [definer clause] procedure|function"
------------------------------------------------------

We're looking at a row in xxxmdbug.ROUTINES.
We're going to generate a complete CREATE statement for
the surrogate routine. The first bit is just
"CREATE [definer clause] { PROCEDURE | FUNCTION }".

Sometimes "CREATE ... routine_name" needs a DEFINER
clause if the creator isn't the same, for example
DEFINER=`Peter`@`localhost`. For example purposes
I'll assume that it's necessary, but that changing
sql_mode is not necessary.

We can tell from the original routine definition whether
we have to say CREATE PROCEDURE or CREATE FUNCTION.

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE

Generate: routine name
----------------------

generate_routine_name().

We're still looking at a row in xxxmdbug.ROUTINES.
We know the database name, it's the same as the original routine's.
We know the surrogate routine name, it's Routine_name_of_surrogate.
Generate:
database_name . surrogate routine_name (parameters)
or
database_name .surrogate routine_name (parameters) RETURNS data_type
Where
debug_schema = database name of original routine
surrogate routine_name = value from row in ROUTINES table

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()

Generate: starter
-----------------

Our surrogate routine will always start with code for
* "Commenting"
* "Handling the signal_number"
* "Checking if the routine is still valid"
* "Adding to the call stack"

For "Commenting":
Somebody might come across this procedure and
wonder what it's for or how it came to be.
We could additionally use a COMMENT clause.
Avoid inline comments because clients can lose them.
Generate a comment that explains reasonably fully:
BEGIN
   DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT
   'Surrogate routine for [original routine name]
    Generated by [debugger name] [version]
    Generated on [date]';

For "Handling the signal_number":
The signal number is what we'll signal for errors that
the debugger can't handle, and for the "exit" user command.
The user can pass the signal_number value during "Setup",
but we'll assume throughout that it's the default, 5678.
Generate:
debug label:
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;

For "Checking if the routine is still valid":
If a routine is dropped or altered after the 'Setup' is done,
then the surrogate routines are invalid, and 'Setup' will
have to be done again. We check for this at the start of every
routine, and if it's so, we'll have to exit from the debugger.
Generate:
  IF (SELECT COUNT(*) FROM information_schema.routines
      WHERE last_altered = [last_altered time from xxxmdbug.routines])
     <> 1 THEN
       SIGNAL SQLSTATE '05678' mysql_errno=5678;

Adding to the call stack.

There is a pseudo-table CALL_STACK, which contains a number -- 1
is top of stack, 2 is something that's called from 1, and so on.
Debuggee Connection will insert a row in CALL_STACK when it
enters a routine, and will delete the row from CALL_STACK
when it leaves a routine.
Apparently some people think call stacks are good things, 
see Bug#36106 Stored Procedure call stack in INFORMATION_SCHEMA.

Generate:
  INSERT INTO CALL_STACK VALUES ([original routine name]);

The starter could also generate "Update CALL_STACK" to
make it contain the line number of the statement
that caused invocation of this routine. This can be found,
it's the last row of the STATEMENTS_EXECUTED table.
But that would only be a "nice to know", not a "need to know".

Most of the checks described in this section are in a common routine, xxxmdbug.routine_entry().

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;

Generate: label
---------------

The sections between "Generate: label" and
"Generate: leave" are to be done for each statement
in the STATEMENTS table.

If the statement is WHILE or LOOP or BEGIN or REPEAT,
and the previous "statement" was not "label:", add a
label now.

The name of the label is "xxxmdbug_label_n",
where n is a statement number, unique within the routine.
This will make it easy to
exit loops and compound statements by saying
"LEAVE xxxmdbug_label_n" later.

There will be more labels generated throughout the routine.

Generate: if (statement is debuggable)
--------------------------------------

We're looking at the token that starts a statement.
We want to determine whether we should generate
ICC statements, that is, whether the statement is
debuggable.

We check by looking whether STATEMENT.Type='Debuggable'.

For every statement which is not debuggable,
skip to section "Generate: statement text".
In the 'Example_Procedure' examples I'll assume
that's happened for the BEGIN and WHILE statements.

For the statements which are debuggable (UPDATE
in our example) all following sections as far as
"Generate: statement text" apply.

Generate: "while (statement is being debugged)"
--------------------------------------------

Ordinarily, the surrogate routines in xxxmdbug
are invoked by Debuggee Connection -- that's what we intended
during "Setup", that's what the surrogate routine is for.

Less ordinarily, the surrogate routines in xxxmdbug
are invoked by some connection that has nothing to do
with the current debug session. The usual reason is
that users have inserted calls to surrogate routines
in the original database, see appendix "Debugging without surrogates".
That's okay. We just want to make sure that, when it
happens, the interconnection communication calls are skipped.

How can we tell? That's what the 'attach' command is for.
If the Debugger Connection has sent not yet sent 'attach', then 
skipping occurs. The attach can happen before debugging (indeed
that's what always happens with debuggee_wait_loop), or it can
happen later.

Generate:
  label:
  IF (statement is being debugged) THEN

This is handled by the common routine xxxmdbug.is_debuggee_and_is_attached().

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO             
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      

Generate: "call icc_copy_variable_to_table_row"
-----------------------------------------------

See section "Common Routines: determine what variables are in scope"
to find out which variables the routine will need at the time
it reaches the statement that we're currently looking at.

Generate, for each variable that's in scope:
  CALL icc_copy_variable_to_table_row(variable_name,token_number_of_declare,variable_value)
This will update the VARIABLES table, effectively
UPDATE VARIABLES SET variable_value=passed-variable-value WHERE variable-name=passed-variable-name.
And save the previous value, so it will be clear it changed since last time.
See section "Common Routines: icc_copy_variable_to_table_row".
Regrettably, there's no way to generate an UPDATE dynamically
if there's a variable reference.

The purpose of the CALL is to get variables' values
into a pseudo-table so that Debuggee Connection can
pass them to Debugger Connection in response to a 'refresh' command.

This step could be avoided if we could be certain
that the last statement didn't change the variable.
Since very few statements change variables (perhaps
only FETCH and SELECT and SET and DECLARE?), we could
probably reduce drastically the number of calls to
icc_copy_variable_to_table_row(). But we don't.
(By the way, simply asking "what was the last
generated statement?" wouldn't be good enough
here, because the last executed statement might
be in a CONTINUE handler, or might have invoked
a function.)

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO            
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);

Generate: "call icc_core"
-------------------------

Generate:
CALL icc_core(line_number);

This routine is the "Inter-Connection Communication" Core procedure.
See section "Common Routines: icc_core".

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO             
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);

Generate: "if (exit) signal"
----------------------------

If execution reaches this point, that means the breakpoint check
has been passed (it was in icc_core), and we are continuing.
But the return from icc_core includes the value of the last
command from Debugger Connection. It's in @xxxmdbug_command.
If it's 'exit', then execution should stop.
We cause that by signaling 5678, we never reach the generated statement.

(This check could be in icc_core, but I put it here
because I think it makes it more obvious what 'exit' does.)

Generate:
IF @xxxmdbug_command = 'exit' THEN
  SIGNAL sqlstate '56780' SET mysql_errno = 5678;
END IF;

Generate: "call icc_copy_table_row_to_variable"
-----------------------------------------------

Generate, for every variable that's in scope,
(see "Common Routines: determine what variables are in scope"),
  CALL icc_copy_variable_to_table_row(variable_name,token_number_of_declare,variable_value)
This will select from the VARIABLES table, effectively
SET variable = value-in-variables-table;

The result will be that all variables get values from VARIABLES table.
See section "Common Routines: icc_copy_table_row_to_variable".

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO             
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               

Generate: "if (leave) leave"
----------------------------

If the user command was "leave", then we want to jump out of
the current (originally unlabelled) REPEAT or WHILE or BEGIN or LOOP.
So generate:
  IF @xxxmdbug_command='leave' THEN
    LEAVE debug_label_n;
    END IF;

Generate: "if (execute) iterate"
--------------------------------

Suppose the user command was to execute an SQL statement, like
'execute insert into t values (7)';
In that case, do we really want to continue right away?
I think not, I think the user would want to stay at the breakpoint.
We also have to iterate after 'set'.
So generate:
  IF @xxxmdbug_command='execute' OR @xxxmdbug_command='set' THEN
    ITERATE debug_label_of__while;
    END IF;

Generate: "if (skip) leave"
---------------------------

If the user command was "skip", then we want to get out of the
WHILE loop so that the original SQL statement won't be executed.
So generate:
  IF @xxxmdbug_command='skip' THEN
    LEAVE debug_label_of_while;
    END IF;

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               
            IF @xxxmdbug_token_value_1 = 'leave' THEN LEAVE xxxmdbug_label_3; END IF;                                                                     
            IF @xxxmdbug_breakpoint_check_result=1 AND (@xxxmdbug_token_value_1 = 'set' OR @xxxmdbug_token_value_1 = 'execute') THEN ITERATE xxxmdbug_inner_loop_label_4; END IF;                                                
            IF @xxxmdbug_token_value_1 = 'skip' THEN LEAVE xxxmdbug_inner_loop_label_4; END IF;                                                           

Generate: handlers
------------------

This is where we should generate, at least,
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND CALL diagnostics_handler();
It's a bit too general: if there's a handler for something
more specific, then this handler might be missed. I believe
the solution for that will be to insert "CALL diagnostics_handler()"
in several places.

This is a handler for the original statement,
which we will soon be generating.
If the statement ends with an error, then
diagnostics_handler() will put the error number
in a DIAGNOSTICS table.

Update: It didn't work. I even got crashes. I reported a MySQL bug (Bug#65166).

Generate: statement text
------------------------

Generate the statement itself.

The Common Routine change_statement_text()
will change the statement text, if necessary, so that

First,
CALL change_statement_text(statement text,
                           changed_statement_text_1,
                           changed_statement_text_2);
This will change routine references to surrogate-routine
references (for example "call p()" will become
"call xxxmdbug00Pp()"), and do special handling
for SELECT, SHOW, RETURN, and SIGNAL.

In our example procedure the statement is
"UPDATE t SET column1 = declared_variable;"
which has no routine references and requires no
special handling. So change_statement_text()
returns changed_statement_text_1 = the original
statement text unchanged, changed_statement_text_2 = ''.
And that's what we generate now.

Generate: "insert into statements_executed"
-------------------------------------------

If execution reaches this point, it's certain that
Debuggee Connection will actually try to do the
original statement. So put the statement number
in our "tracking" table, STATEMENTS_EXECUTED.

We won't try to do much with statistics.
Timings would be distorted anyway, because some
statements (SELECT, SHOW) get special handling.
Timings would in any case have to be done *after*
the statement, and we avoid that.

But a debugger certainly wants to know "how many times
was the DROP statement in test.procedure5 executed",
"what were the most recent executed statements", etc.

Generate:
  INSERT INTO STATEMENTS_EXECUTED VALUES (statement-number);

Generate: leave
---------------

Generate END to end the BEGIN that we generated earlier
(see section "Generate: handlers"), then generate LEAVE and END WHILE
to exit and end the WHILE that we generated earlier (see
section "Generate: "while (statement is being debugged)").

Generate:
  END
  LEAVE debug_label_of_while;
  END WHILE;

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               
            IF @xxxmdbug_token_value_1 = 'leave' THEN LEAVE xxxmdbug_label_3; END IF;                                                                     
            IF @xxxmdbug_breakpoint_check_result=1 AND (@xxxmdbug_token_value_1 = 'set' OR @xxxmdbug_token_value_1 = 'execute') THEN ITERATE xxxmdbug_inner_loop_label_4; END IF;                                                
            IF @xxxmdbug_token_value_1 = 'skip' THEN LEAVE xxxmdbug_inner_loop_label_4; END IF;
            UPDATE t SET column1 = declared_variable;
            CALL xxxmdbug.update_statements_executed(0);
            LEAVE xxxmdbug_inner_loop_label_4;          
            END LOOP;
          ELSE
            UPDATE t SET column1 = declared_variable;
            END IF;
          END WHILE;
        END;

Generate: ender
---------------

When we reach the end of a routine, we have to
* Remove the latest item from the call stack by generating:
  DELETE FROM CALL_STACK WHERE call_stack_number = (SELECT MAX(call_stack_number) FROM CALL_STACK);
  This is required for the end of any procedure, and
  for some EXIT handlers, and for function RETURN statements.
  I am only showing it for a normal stored-procedure exit.
* Generate "END;" because we added "BEGIN" during starter.

Based on the Example_Procedure,
and taking into account that it ended with 'END WHILE; END',
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               
            IF @xxxmdbug_token_value_1 = 'leave' THEN LEAVE xxxmdbug_label_3; END IF;                                                                     
            IF @xxxmdbug_breakpoint_check_result=1 AND (@xxxmdbug_token_value_1 = 'set' OR @xxxmdbug_token_value_1 = 'execute') THEN ITERATE xxxmdbug_inner_loop_label_4; END IF;                                                
            IF @xxxmdbug_token_value_1 = 'skip' THEN LEAVE xxxmdbug_inner_loop_label_4; END IF;
            UPDATE t SET column1 = declared_variable;
            CALL xxxmdbug.update_statements_executed(0);
            LEAVE xxxmdbug_inner_loop_label_4;          
            END LOOP;
          ELSE
            UPDATE t SET column1 = declared_variable;
            END IF;
          END WHILE;
        END;
      CALL xxxmdbug.routine_exit();
    END

And we're done with the generator calls.
From the 6-line original procedure, we
have generated a 36-line surrogate procedure.
But we hide this from the users as well as
we can, so the size isn't really important.
What's important is that the surrogate procedure
has all we need for keeping track of variables,
calling ICC, handling errors, handling user
commands, and tracing.

The create-surrogate-routines script
------------------------------------

The generated routines have to go somewhere.

For simplicity, the default is to INSERT their texts in mysql.proc.
I won't UPDATE or DELETE anything in mysql.proc because I have no
guarantees about the effects on the stored procedure cache, but it
seems safe to do INSERTs. I have seen an error message (on
Windows) saying that mysql.proc has to be repaired, but REPAIR
TABLE mysql.proc fixed it, there was no actual damage.

There is another way to do it. The setup() procedure leaves the
CREATE PROCEDURE|FUNCTION texts in the table xxxmdbug.ROUTINES.
So one could use "SELECT INTO OUTFILE script-file" to dump the
CREATE statements into a script file, and then use "SOURCE script-file"
to execute them. Unfortunately I couldn't devise a simple and
automatic way to do this, it can't be done within generate() because
you can't create a stored procedure from within a stored procedure. 

But I encourage anyone doing a front end:
consider using a script file. For example,
from mysql client command line:
  source install.sql;
  UPDATE xxxmdbug.README SET Is_install_script_done = 'True';
  CALL xxxmdbug.setup('test.Example_Procedure');
  SELECT definition_of_surrogate_routine
  INTO create-surrogate-routines.sql
  FROM xxxmdbug.routines;
  source create-surrogate-routines.sql;
  UPDATE xxxmdbug.Setup_log SET Is_setup_done = 'True';


Debuggee Start
==============

After "Setup" (which includes "Generate"), all the
necessary-for-debugging tables and common routines exist
in the xxxmdbug database and in the original routine's database.
So the debug session can start. There's a protocol for that.

The connection that wishes to become Debuggee Connection says:
CALL xxxmdbug.become_debuggee_connection('channel');
which will perform some error checks to make sure that there
is hope of making a debugger-debuggee session, and then
clears a bunch of user variables, whose names always
start with @xxxmdbug.

This initialization is necessary for debuggee_wait_loop().
But it's separate from debuggee_wait_loop() because the
initialization is also necessary for debugging surrogate
routines directly. The rule: if a debugger wants to send
an 'attach' command later, then become_debuggee_connection()
has to have happened at some time in the past on the debuggee.

Debuggee Start: debuggee_wait_loop
----------------------------------

debuggee_wait_loop() is an SQL stored procedure in xxxmdbug.
Its job is to wait for the Debugger Connection to
start the debug session on its side (by sending an
"attach" user command), wait some more till the Debugger
Connection sends a 'debug xxx' command, and then invoke xxx.

To get a message from Debugger Session,
CALL xxxmdbug.icc_get_user_command();
CALL icc_process_user_command();
See section "Common Routines: icc_get_user_command"
and section "Common Routines: icc_process_user_command".

To see how the Debugger Session sends the command,
see section "Common Routines: command".

To see what the Debuggee Session will do when it
receives the "attach ..." user command,
see section "Command: 'attach'".


User Commands
=============

A User Command is a text instruction which Debugger
Connection passes to a Common Routine, xxxmdbug.command(text);

Where it's possible and appropriate, the command syntax is like GDB's.
http://sourceware.org/gdb/current/onlinedocs/gdb/

For example, if a user using a GUI right-clicks
over the second line of text on an edit window,
that might mean "set a breakpoint on line 2", causing
CALL xxxmdbug.command('break routine_name:2');

Sometimes the user commands are not much more
than wrappers for SELECT statements and for
other user commands. At other times they are
quite complicated and involve sending messages
to Debuggee Connection.

In the discussion of each command, I don't bother
saying "somehow Debugger Connection parses the
text of the command". Recall that both Debugger
Connection and Debuggee Connection can
CALL xxxmdbug.tokenizer(text);
to break up any string into its tokens.
After that it should be a straightforward matter
to loop through the TOKENS table.

User Command: 'info'
--------------------

CALL xxxmdbug.command('channel','info info-variant');

The variants of 'info' will be used for
situations where the result is a result
set, and there's no need to send the
command to Debuggee Connection. Debugger
Connection can figure out the answer itself.

These are the possible values of info-variant
and the actions that they cause:

"readme":
select * from information_schema_common.README;
The Readme table exists so that people
can see (from the metadata or by selecting
the function) what the debugger's version
number is, and any comments about the most
recent release, and a link to documentation.
See "Common Routines: README table".
Update: this wasn't implemented.

"processes"
Select from information_schema.processlist.
This is handy if you're looking for a connection
that is waiting for a message from Debugger Connection,
because processlist.info will show the statements
that icc_get_user_command() executes.
This requires the PROCESS privilege.
Update: this wasn't implemented.

"information status"
Debuggee Connection sends a status message
when it starts, when it reaches a debuggable
statement, when it reaches a breakpoint.
See section "ICC: Protocol".

In our running example with Example_Procedure,
the most recent thing that happened was that
the Debuggee Connection started and debuggee_wait_loop() happened. So
CALL xxxmdbug.command('xxx','information status');
will return a row that contains

Last_command  Status
------------  ------
(null)        Waiting for 'attach'

That's another way of saying "You can attach on channel xxx,
there's a Debuggee Connection waiting".

User Command: 'attach'
----------------------

CALL xxxmdbug.command('xxx','attach');

A connection wants to become Debugger Connection.
Check to make sure that Debuggee Connection has already
been set up (see "Start Debuggee").

The command() routine will just send 'attach'.

In our running example, Debugger Connection just found out
with the 'info' command that the debuggee is ready, so it says
CALL xxxmdbug.command('xxx','attach');
Once this is done, other user commands become legal.

The 'attach' command should perhaps fail if the Debugger Connection
and the Debuggee Connection are different users. But there
is no check for that.

User Command: 'debug'
---------------------

CALL xxxmdbug.command('debug [schema_name.]routine_name ([arguments])');

Debugger Connection checks that routine_name exists,
checks that 'attach' is done,
checks that debugging is not already going on.

If the checks succeed, it sends the command to Debuggee Connection:
CALL xxxmdbug.send('debug [schema_name.]routine_name ([arguments])');

The 'debug' will be seen by Debuggee Connection, which will
actually invoke the routine, by finding the routine's
surrogate name in the ROUTINES table, and saying
CALL surrogate_routine_name();       /* if it's a procedure */
or
DO surrogate_routine_name();         /* if it's a function */

The routine will actually start, but there is an effective automatic
temporary breakpoint on what we'll call "line 0", so a pause happens
right after the routine's initial BEGIN and DECLARE statements, if any.
We'll worry about breakpoints in a slightly later section.

In our running example with Example_Procedure, the command is
CALL xxxmdbug.command('debug Example_Procedure()');

From now on, Debuggee Connection is running the procedure and
Debugger Connection is still running the client application
program, accepting user input.

User Command: 'refresh'
-----------------------

CALL xxxmdbug.command('refresh table_name');

The table_name can be the name of a table
in xxxmdbug, such as VARIABLES, USER_VARIABLES, SYSTEM_VARIABLES,
BREAKPOINTS, etc. Values for these tables are
updated by Debuggee Connection. Debugger Connection sends
'refresh' to Debuggee Connection, waits for a response, and
fills up the table.

The waiting can take a while because the debuggee may
be busy or may even have stopped. There's a timeout, but
it's deliberately very long.

For a user, 'refresh' is useful because most of the
xxxmdbug tables have information that can
grow stale. For internal purposes, 'refresh' is
useful because there are other user commands that
might need up-to-date information.

Debugger Connection says:
CALL xxxmdbug.send('xxx','refresh table_name');

The response to 'refresh' might be slow, and if so
there are various ways to speed it up. For example,
Debugger Connection could update BREAKPOINTS
itself whenever it sees a 'break' command.

User Command: 'continue'
------------------------

CALL xxxmdbug.command('channel','continue');

The 'continue' command is legal only if the Debuggee Connection
is waiting. If it's legal, Debugger Connection merely sends it:
CALL xxxmdbug.send('xxx','continue');

When Debuggee Connection receives 'continue', it
continues. That is, it goes on executing in the
surrogate routine. Then the next statement will be
executed, and the next, until the next breakpoint.

There is no problem detecting whether Debuggee Connection
has hit a breakpoint: there will be a statement status
message. But beware. It's possible for Debugger Connection
to say 'continue' and then say 'continue' again before
Debuggee Connection has received and acknowledged the
first 'continue'. To prevent that, Debugger Connection
should always check the message's Message_number, to ensure
that it isn't reading the same message twice.

In our running example with Example_Procedure, the command is
CALL xxxmdbug.command('xxx','continue');

And Debuggee Connection halts on the first statement,
because there's a breakpoint.

User Command: 'break' or 'tbreak'
---------------------------------

CALL xxxmdbug.command('break ...')
or
CALL xxxmdbug.command(channel,'tbreak ...')

The job is to set a breakpoint, by putting a new row in the
BREAKPOINTS table.

"Tbreak" has the same effect as "break", except that the breakpoint
is temporary. Temporary breakpoints are deleted as soon as any
breakpoint is reached.

First check that the syntax is
"break|tbreak [schema_name.]routine_name line_number_minimum[-line_number_maximum] [variable=value]".
For example 'break database1.p1:5-5', or 'tbreak test.f 5-7'.
The schema_name and routine_name may be wildcards,
for example 'database1.%' means all routines in database1.
The routine_name is the name of the original routine, not the surrogate routine.

Then pass the string on to Debuggee Connection without change:
CALL xxxmdbug.send('xxx','break|tbreak location');

Debuggee Connection will get the message and call
process_user_command_break(), which performs an
INSERT INTO BREAKPOINT statement (in effect). See
"Common Routines: process_user_command_break".

In our running example,
CALL xxxmdbug.command('xxx','tbreak test.Example_Procedure:2-2')
will cause
CALL xxxmdbug.send('xxx','tbreak test.Example_Procedure:2-2')
Debuggee Connection will receive and process the message
(see "Common Routines: get_user_command" and
"Common Routines: process_user_command_break").
The end result will be that there is a row in
the BREAKPOINTS table:

Breakpoint_  Routine_           Line_   Line_
identifier   name               number_ number_
                                minimum maximum
------------ --------           ------- -------
1            Example_procedure  2       2

And later, if we "continue", Debuggee Connection will stop
when it sees that it's at such a row, see section
"Common Routines: icc_breakpoint_check".

An FAQ about the "break" command:
* Q: Is it possible to break on a label?
  A: We could find the label during the generate(),
     but typically a label is in front of a flow-control
     statement, which is not debuggable. So I think a
     break on a label wouldn't have much value.
* Q: Is "break p:9" an error if p has only 8 lines?
  A: It's not an error. The breakpoint will never be
     reached. But we allow ranges of line numbers
     like 1-99999, so we should allow any number in
     the range.
* Q: Is "break p:9" an error if it's entered twice?
  A: It's not an error. There will be two rows in
     the BREAKPOINTS table, for the same place.
* Q: Is "break p:9" an error if p doesn't exist?
  A: It's not an error. We anticipate that users
     might make reusable scripts of "break" statements.
     Sometimes those scripts will be used after a
     routine is dropped, or "setup" will be done for
     a different database. It's harmless.
* Q: What if a statement straddles a line?
  A: In reality the breakpoint has to be per statement
     not per line, because the breakpoint_check() will
     happen just before each statement. So what matters
     is "where the first executable word of the statement
     is". And if there are 10 statements on line 5, then
     "break p:5" will in reality set 10 breakpoints.
* Q: Why is the line number mandatory?
  A: Admittedly it would be pleasant to say that "break p"
     means "break on the first debuggable line of p".
     But what if that line is in an IF statement, and the
     IF condition is false? Or what if the procedure
     body is just "BEGIN END", with no debuggable lines?
     It's a flaw caused by the decision that flow-control
     statements don't have breakpoints.
* Q: How will I know that a breakpoint happened?
  A: Receive a message. You'll get a statement status
     message from Debuggee Connection.
     If additionally you want to know "which breakpoint(s)",
     then refresh the BREAKPOINTS table and then say
     SELECT * FROM BREAKPOINTS_COPY WHERE Is_matching_location='True'.

User Command: 'break' condition
-------------------------------

CALL xxxmdbug.command(channel,'break ... [condition]');
or
CALL xxxmdbug.command(channel,'tbreak ... [condition]');

This is a continuation of the discussion of "break" and "tbreak",
with the additional clause for a condition, "[variable=value]".

Users can specify a condition in addition to
a location (routine name and line number). The condition has
to be true in order for the breakpoint to have effect.

Conditions must have the form
Variable-name = Literal or
Hit_count = Literal or
Variable-name <> Old_value or
Call_stack_depth <= literal.
We could have a few variations, but not many variations.
Sorry, I wish we could handle any expression at all,
but dynamic SQL is illegal in MySQL functions.
I suppose we could say "you could use any expression
at all if you could guarantee that there are no
function calls", but the "Variable-name = literal" syntax
will handle a lot of cases and is like "Set variable_name = literal".

As is visible in icc_process_user_command_breakpoint(),
the Variable-name goes into BREAKPOINTS.Condition_name,
and the Literal goes into BREAKPOINTS.Condition_value.

We're expecting breakpoint_check() to say
  UPDATE BREAKPOINTS SET Is_matching_condition = 'True'
  WHERE CASE condition_name = 'Hit_count' Hit_count
        ELSE(SELECT value FROM VARIABLES WHERE variable_name = condition_name)
        END
        =
        condition_value

Example:
To say "break after hitting a breakpoint 5 times", say
"break test.proc1:5 Hit_count = 5".

Example:
Some debuggers allow "watch expression", which in effect means
"break whenever the expression is true regardless of location".
For this we'd just say that the location is wildcards and ranges,
which will cause icc_breakpoint_check() to get a hit every time.
So to set a watch for @f = 7, the user can say
"break %.% 1-999999 @f = 7".

Example:
For a breakpoint that happens in routine r only when @f changes,
"break r 1-99999 @f <> old_value".
The meaning is "break when the value of variable changes since
the last debuggable statement", not "since the last breakpoint".
The evaluation would take place by comparing
@f with BREAKPOINTS.Old_value and then setting Old_value to @f.

User Command: 'step'
--------------------

CALL xxxmdbug.command(channel,'step [count]');

Continue to the next source line, stopping for functions.

This is easily translated into:
Set a temporary breakpoint for "every line in every routine"
and continue.

CALL xxxmdbug.send(channel,'tbreak %.% 1-99999 Hit_count=[count]');
CALL xxxmdbug.send(channel,'continue');

SIDEBAR: "step over". "Step over" would mean "continue until
the next debuggable statement that is outside the current
compound statement". DB2 supports it. But this next debuggable
statement might be outside the current routine, or in a
handler. I think "step over" is something that we can leave out.

User Command: 'next'
--------------------

CALL xxxmdbug.command(channel,'next');

Continue to the next source line, without stopping for functions.

One might think that this can be accomplished by setting a
temporary breakpoint for the next debuggable line in the
routine. But that would fail for three reasons:
* execution might continue with a handler
* the current statement might be RETURN
* execution might reach that next line via recursion.

So what we really want is a temporary breakpoint for
the next line at the same, or higher, call stack depth.

For example: suppose the CALL_STACK table contains
'ProcedureA'
'FunctionB'
'ProcedureC'
That shows that execution started with ProcedureA which
invoked FunctionB which invoked ProcedureC, and ProcedureC
is the current routine, and call_stack depth is 3.
If ProcedureC invokes a function, call_stack depth will go up.
If ProcedureC exits to FunctionB, call_stack depth will go down.

So say
CALL xxxmdbug.send(channel,'info stack');
SET @xxxmdbug.call_stack_depth=[something from 'info stack' reply];
SET @xxxmdbug_command=CONCAT('tbreak %.% 1-99999 ',
                                 'call_stack_depth <=',
                                 @xxxmdbug_call_stack_depth);
CALL xxxmdbug.send(channel,@xxxmdbug_command);
CALL xxxmdbug.send(channel,'continue');

Unfortunately, since we can only have one condition in 'tbreak',
we cannot additionally say 'hit_count=[count]'. Therefore we
can allow the 'next' command, but not the GDB-like 'next [count]'
command.

User Command: 'delete'
----------------------

CALL xxxmdbug.command(channel,'clear [break number]);

There's an auto_increment column in the BREAKPOINTS table,
Breakpoint_identifier. So "delete 5" will cause
DELETE FROM BREAKPOINTS WHERE Breakpoint_identifier = 5
and "delete" will cause
DELETE FROM BREAKPOINTS;
See "Common Routines: icc_process_user_command".

We could also support "delete [location]", where
[location] has the same syntax as in 'break [location]'.
This would mean "delete all breakpoints whose routine
name and line numbers exactly match [location]".
But I'm guessing that real people would never need that.

There is no equivalent for GDB 'clear breakpoint'.

User Command: 'stop'
--------------------

CALL xxxmdbug.command(channel,'stop');

We can do "stop" with tbreak and wildcards.
By making a temporary breakpoint for the next statement,
no matter where the next statement is, we'll
cause Debuggee Connection to halt on the very next statement.

CALL xxxmdbug.send('xxx','tbreak %.%:1-99999');

Update: this wasn't implemented.

User Command: 'set'
-------------------

CALL xxxmdbug.command(channel,'set variable_name = value');

This is only legal while Debuggee Connection is at a breakpoint.
This causes process_user_command() to update the VARIABLES table.

First perform some error checks.
* There must be a row in the up-to-date VARIABLES table with
  Variable_name = variable_name, and Is_in_scope = 'True'
  and Is_settable = 'True'.
* The length of value must not be greater than the
  length of variables as decided by "setup [maximum_variable_length]".
  In fact it might be a good idea to insert the value
  into a temporary-table copy of VARIABLES, just to
  see whether there's a warning.

If all is well, pass the string to Debuggee Connection without change:
CALL xxxmdbug.send(channel,'set variable_name = value');

User Command: 'finish'
----------------------

CALL xxxmdbug.command(channel,'finish');

Continue until the exit point of the current routine.
Break will happen for the statement after whatever called.

Compare with the "next" command. For "next", we said that 
"So what we really want is a temporary breakpoint for
the next line at the same, or higher, call stack depth."
For "finish", the requirement is similar, we'll say that
"So what we really want is a temporary breakpoint for
the next line at *higher* call stack depth."

So say
SET @xxxmdbug_call_stack_depth=(SELECT COUNT(*) FROM CALL_STACK);
SET @xxxmdbug_command=CONCAT('tbreak %.% 1-99999 ',
                                 'call_stack_depth <=',
                                 @xxxmdbug_call_stack_depth - 1);
CALL xxxmdbug.send('xxx',@xxxmdbug_command);
CALL xxxmdbug.send('xxx','continue');

Update: this wasn't implemented.

User Command: 'exit'
--------------------

CALL xxxmdbug.command(channel,'exit');

This means "stop debug, that is,
end what you started with the 'debug' command.

If you want to get out of the debugger,
thus cancelling the effect of the 'attach' command,
you want "quit".

I thought of having a LEAVE statement for every
compound statement. That wouldn't quite work, because for
"SELECT f() FROM t;" it's not sufficient to get out of f(),
we have to stop the SELECT statement too. That can only
be done by causing an error.

So what we want Debuggee Connection to do henceforward is:
For every statement, instead of doing the statement, do
signal sqlstate '05678' set mysql_errno=5678;
I'm depending on 5678 being unused (there's a check for
that), and since "DECLARE EXIT HANDLER FOR 5678 ..."
is more specific than any "DECLARE CONTINUE HANDLER
for SQLEXCEPTION ..." it ought to be caught reliably.
See section "Generate: "if (exit) signal".

CALL xxxmdbug.send(channel,'exit');
CALL xxxmdbug.send(channel,'continue');

User Command: 'quit'
--------------------

CALL xxxmdbug.command(channel,'quit');

This is not legal if a routine is still being debugged.
You should issue an 'exit' command first.

When a routine has ended, users have the option of
starting another one (see User Command: 'debug'), or
ending the debug session with the 'quit' command.

This cancels the effects of 'attach' by Debugger Connection.
It also cancels the effects of start_debuggee_connection(),
because when Debuggee Connection sees 'quit' it will exit
from the get_user_command() loop.

CALL xxxmdbug.send('quit');

Update: this wasn't implemented.

Command: 'execute'
------------------

CALL xxxmdbug.command(channel,execute SQL-statement);

This will send "SQL-statement" to Debuggee Connection.
Debuggee Connection will PREPARE and EXECUTE SQL-statement.

This will only be legal when there is a breakpoint (Debuggee Connection
has paused and is waiting for input).

First run some error checks.
* Is dynamic SQL legal? If Debuggee Connection is currently
  in a function, then dynamic SQL is not legal. So look at
  every routine in the CALL_STACK table. If any of them is
  a function, that's an error.
* Is SQL-statement preparable? To find out, change to the
  same default database that Debuggee Connection is using,
  and say PREPARE xxxmdbug_stmt FROM [SQL-statement],
  then DEALLOCATE xxxmdbug_stmt. This not only will
  confirm that the SQL syntax is okay, it will confirm that
  max_prepared_stmt_count hasn't been exceeded
  (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_prepared_stmt_count).

If it appears that the SQL-statement might work, send it on.
CALL xxxmdbug.command('xxx','execute SQL-statement');

Debuggee Connection will tokenize the statement before preparing
and executing, and replace references to original routines with
references to surrogate routines. The technique is similar to
what we generate for "special handling of dynamic SQL"

Example:
Some debuggers allow "watch expression", which (as far as I know)
means "display the results of expression". We can do that with
CALL xxxmdbug.command('xxx','execute SELECT expression');

User Command: 'kill'
--------------------

CALL xxxmdbug.command(channel,'kill');

Debugger Connection will say "kill connection-id-of-debuggee-connection";

This is drastic. The clean way to end a debug session
is 'stop' then 'exit' then 'quit'. 'Kill' is only
justifiable when a single SQL statement is executing
so slowly that 'stop' won't work.

Update: this wasn't implemented.

User Command: 'skip'
--------------------

CALL xxxmdbug.command(channel,'skip');

This is similar to 'next', except that Debuggee Connection
skips the next SQL statement instead of executing it.

CALL xxxmdbug.send(channel,'skip');

User Command: 'leave'
---------------------

CALL xxxmdbug.command(channel,'leave');

This will cause Debuggee Connection to execute a
LEAVE statement. That brings execution out of the
current unlabelled compound statement. If it was already in
the outermost compound statement, LEAVE will exit
from the routine.

This depends on the fact that we generated a
label before WHILE or REPEAT or LOOP or
BEGIN, see section "Generate: label".

CALL xxxmdbug.send('xxx','leave');

Common Routines
===============

Common Routines are created by the Install script
in xxxmdbug.
Common Routines are called from generate(), from surrogate
routines, or from Debugger Connection.

Common Routines: A note about comments
--------------------------------------

SQL routines are code, and are protectable by the same
sort of software licenses that one sees in C programs.
Every routine that we supply must start with a comment.

Do not use inline comments. MySQL clients will strip
strings inside "/* ... */" or after "#".
See Bug#14487 Comments are being ripped out of SP's during restore.
http://bugs.mysql.com/bug.php?id=14487
Actually the mysql client has an option for not stripping,
and we recommend it, but we don't assume it's happening.

So the comment has to be a string in a statement.
And the best statement for the purpose is DECLARE,
since it can be at the start. The variable name
should begin with "xxxmdbug_", our reserved namespace.

So always include, right after the BEGIN:
"
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT
  'Copyright. License. Author. Version. Date.';

Common Routines: tokenizer
--------------------------

The procedure xxxmdbug.insert_into_routines() is an SQL tokenizer written in SQL.
It handles the MySQL rules for
One-byte tokens: !%&()*+,-:/=[]^|~<>
Two-byte tokens: && := <= >= << >> <> ||
Three-byte tokens: <=>
Enclosing punctuation: '' or "" or `` or /**/ or {}
In-string special handling: quotes inside quotes
Comment starter: # (not done yet)
Escapes: \' etc. (not done yet)
Inline comments
White space: space or tab or carriage-return
Line Feed: = white space but use it to count line numbers
Period: part of token if digit precedes|follows, else one-byte token
Commercial At Sign: @ or @@ begins a new token
Other: part of a token.
(The rules for "[" and "]" might change in a future MySQL version.)

It inserts each token into a TOKENS table.

Here is a sample exercise that calls tokenizer:

delimiter //
DROP TABLE tokens//
CREATE TABLE tokens (token_number int primary key auto_increment,
                     offset int,
                     line_number int,
                     value varchar(128) CHARACTER SET utf8mb4) engine=memory//
drop procedure Example_Procedure//
create procedure Example_Procedure ()
BEGIN
  DECLARE declared_variable INT DEFAULT 5;
  WHILE @user_variable < 5 DO
    UPDATE t SET column1 = declared_variable;
    END WHILE;
  END
//
select body into @body from mysql.proc where name = 'Example_Procedure'//
delete from tokens//
call insert_into_tokens(@body)//
select * from tokens order by token_number//

Here is the result of the SELECT:
"
mysql> select * from tokens order by token_number//
+--------------+--------+-------------+-------------------+
| token_number | offset | line_number | value             |
+--------------+--------+-------------+-------------------+
|            1 |      1 |           1 | BEGIN             |
|            2 |      9 |           2 | DECLARE           |
|            3 |     17 |           2 | declared_variable |
|            4 |     35 |           2 | INT               |
|            5 |     39 |           2 | DEFAULT           |
|            6 |     47 |           2 | 5                 |
|            7 |     48 |           2 | ;                 |
|            8 |     52 |           3 | WHILE             |
|            9 |     58 |           3 | @user_variable    |
|           10 |     73 |           3 | <                 |
|           11 |     75 |           3 | 5                 |
|           12 |     77 |           3 | DO                |
|           13 |     84 |           4 | UPDATE            |
|           14 |     91 |           4 | t                 |
|           15 |     93 |           4 | SET               |
|           16 |     97 |           4 | column1           |
|           17 |    105 |           4 | =                 |
|           18 |    107 |           4 | declared_variable |
|           19 |    124 |           4 | ;                 |
|           20 |    130 |           5 | END               |
|           21 |    134 |           5 | WHILE             |
|           22 |    139 |           5 | ;                 |
|           23 |    143 |           6 | END               |
+--------------+--------+-------------+-------------------+
23 rows in set (0.00 sec)
"

This tokenizer is not merely useful for debugging.
I'm expecting there will be other applications that need it.
As with all MDBug routines, that requires following the GPL license provisions.

Common Routines: determine what variables are in scope
------------------------------------------------------

determine_what_variables_are_in_scope(statement-number)

We start with a token-number for a token at the beginning
of a statement within a routine.
We want to set Is_in_scope='True' for all VARIABLES rows
where the variable is "in scope" for the statement.

We need to know this so that we can
* Generate icc_copy_variable_to_table_row() and
  icc_copy_table_row_to_variable() statements
* Decide when "set variable = name" is legal.

For example, suppose we have
CREATE PROCEDURE p ()
BEGIN
  DECLARE a,v CHAR(5);
  BEGIN
    DECLARE v DATE;
    END;
  DECLARE a INT;
  SET @x = CONCAT(v,a);
  END;
We're currently looking at the statement "SET @x = CONCAT(v,a);".
We have to use the most-relevant, in-scope, declarations
of v and a.

We'll go backward in the TOKENS table starting with "SET".
When we pass "END" we know that everything is out of scope
until we pass a corresponding "BEGIN". When we pass "DECLARE"
and it's in scope, we mark every variable associated with
that DECLARE as "in scope" -- unless another variable with
the same name has already been marked.
(If variable 'v' is declared twice, the later declaration
supersedes the earlier one.)
So in the end the variables that get marked are:
* The 'a' in "DECLARE a INT"
* Not the 'v' in "DECLARE v DATE" because (going backward)
  we pass an "END" before we see this DECLARE
* The 'v' in "DECLARE a,v CHAR(5)"
* Not the 'a' in "DECLARE a,v CHAR(5)" because we have
  already got an 'a' from the "DECLARE a INT" statement.

In a vaguely SQL-like way, the algorithm looks like

DECLARE token_cursor FOR 
SELECT FROM TOKENS
  WHERE Routine_schema = x AND Routine_name = x AND Byte_offset < (Byte_offset of SET token)
ORDER BY id DESC;
UPDATE VARIABLES SET Is_in_scope='False';
@end_count = 0;
LOOP
  FETCH x INTO @Token_Number, @Value;
  IF (not found) break;
  If @Value='END' @end_count = @end_count + 1;
  If @Value='BEGIN' @end_count = @end_count - 1;
  IF @Value='DECLARE'
    IF @end_count=0
      /* This DECLARE is in scope.
         Mark any variables associated with this DECLARE
         as "in scope", unless a variable with the same
         name has already been marked . */
      UPDATE VARIABLES SET Is_in_scope='True'
      WHERE Token_number_of_declare=(this DECLARE)
      AND (Variable_name NOT IN (SELECT Variable_name FROM VARIABLES WHERE Is_in_scope='True');

It's just a tad more complex than that, because
BEGIN/END isn't the only scope-changer. Also watch
for WHILE/END WHILE, LOOP/END LOOP, REPEAT/END REPEAT.

Also, parameters are always in scope, and are superseded
by variables with the same name. So the parameter list
should be taken as an equivalent of a "DECLARE".

Common Routines: diagnostics_handler
------------------------------------

The original statement (that is, the statement from the
original routine) might end with an error or a warning.
We want to store that in the DIAGNOSTICS table, so that
Debugger Connection can just say 'refresh' and 'select
from get_diagnostics' to see the error or warning.
Later, when users encounter SHOW WARNINGS | ERRORS,
the "diagnostics special handling" can make a result
set out of the DIAGNOSTICS table for them.
But also, it should be possible for Debugger Connection
to read those diagnostics any time.

The first fear is that an error might be grabbed
by an error handler from the original routine,
before we could store the error.
We try to take care of that by generating our own
DECLARE ... HANDLER statement immediately before
the generated statement, so that errors will be
caught by the debugger first and processed in
diagnostics_handler().

The second fear is that the surrogate routine has
many extra SQL statements which might clear the
error message list. We try to handle that too.
Consider this script:

DELIMITER //
DROP PROCEDURE p1//
DROP PROCEDURE p2//
DROP TABLE x//
DROP TABLE variables//
CREATE TABLE diagnostics (errno INT)//
CREATE PROCEDURE diagnostics_handler ()
BEGIN
  DECLARE EXIT HANDLER FOR 1051 BEGIN INSERT INTO diagnostics VALUES (1051); RESIGNAL; END;
  RESIGNAL;
  END//
CREATE PROCEDURE p2 ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION CALL diagnostics_handler();
  DROP TABLE no_such_table;
  END//
CALL p2()//

The result is:
"
mysql> CALL p2()//
+------+
| 1051 |
+------+
| 1051 |
+------+
1 row in set (0.00 sec)

ERROR 1051 (42S02): Unknown table 'test.no_such_table'
"

That proves that it's possible to store a statement's diagnostics
in a table, without losing the diagnostics. However, the way that
we did it was to say "if error is 1051 then put in 1051". We'll
have to do the same thing for every possible error number.
(There's no GET DIAGNOSTICS support in MySQL 5.5.)
That's every error described in error.h, and
every error that has appeared in a SIGNAL or RESIGNAL
statement in any of the user routines (search the STATEMENTS
table to see the texts of all SIGNAL or RESIGNAL statements).
See also
http://rpbouman.blogspot.com/2009/06/mysql-stored-functions-impact-of.html
So picture many DECLARE EXIT HANDLER statements here.

Some things to watch out for when doing this are:
* The SHOW WARNINGS and SHOW ERRORS and (someday)
  GET DIAGNOSTICS statements, plus SIGNAL/RESIGNAL,
  might need to be handled differently.
* Sometimes it will be difficult to tell whether it's an error or a warning, so do it as
   DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN
     DECLARE CONTINUE HANDLER FOR 1234 BEGIN
       /* save 'warning', '1234' */;
       RESIGNAL;
       END;
     RESIGNAL;
     END;
* This is still not enough for "notes", since they won't be intercepted.
* You'll also have to save @@warning_count and intercept SHOW (COUNT *) WARNINGS.
* Beware of mysql_warning_count(), max_error_count, and sql_notes.

Update: this wasn't implemented.

Common Routines: icc_get_user_command
-------------------------------------

icc_get_user_command()
is what Debuggee Connection will call from
icc_core() multiple times in order to get messages from
Debugger Connection.

Its job is to get a user command. To see how that's
done, read the "ICC" chapter, particularly
"ICC: A working example".

Return with @xxxmdbug_message is NULL (no
messages pending), or @xxxmdbug_message = the first command
in the message queue.

Common Routines: icc_process_user_command
-----------------------------------------

The possible messages are
break|tbreak, clear, refresh, set, execute, continue, exit, leave, skip.
One can say 'break|tbreak' and 'clear' and 'refresh' any time.
One can say 'set' and 'execute' and 'continue' and 'exit' and
'leave' and 'skip'
only while Debuggee Connection is stopped at a breakpoint.
One can say 'debug' only when debugging isn't in progress.

First call the tokenizer.

For most commands, the real explanation is in the section
"User Commands". The following IFs are just to show that
icc_process_user_command() will have a bunch of IFs.

If "break" THEN
  see section "Common Routines: icc_process_user_command_break"
If "clear" THEN
  DELETE FROM BREAKPOINTS
  WHERE breakpoint_identifier = (id from "clear id")
If "set" THEN
  UPDATE VARIABLES
  SET VARIABLES.Value = (value from "set variable = value"),
      VARIABLES.Is_updated_by_set = 'True'
  WHERE VARIABLES.Variable_name = (variable from "set variable = value")
If "exit" THEN
  /* just return with @xxxmdbug_command = 'exit'
If "execute" THEN
  /* Debugger Connection has already checked for errors,
     but has not replaced routine references with
     surrogate-routine references.
     Call change_statement_text() first.
     Errors will end up in DIAGNOSTICS table.
     PREPARE xxxmdbug_stmt FROM execute-string.
     EXECUTE xxxmdbug_stmt.
     See comments in "Command: 'execute'" and in section
     "Common Routines: Change statement text: special handling of dynamic SQL" */
If "continue" THEN
  /* just return with @xxxmdbug_command = 'continue'
If "debug" THEN
  See discussion in section "Command: 'debug'"
If "refresh" THEN
  See discussion in section "Command: 'refresh'"
If "skip" then
  /* just return with @xxxmdbug_command = 'skip'
If "leave" then
  /* just return with @xxxmdbug_command = 'leave'

Common Routines: icc_process_user_command_break
-----------------------------------------------

The syntax is
"break|tbreak [schema_name.]routine_name line_number_minimum[-line_number_maximum] [variable=value]"

Given that we can call tokenizer(),
and knowing that default schema_name = current_database,
and knowing that default maximum_line_number = minimum_line_number,
and knowing that default condition = '',
we can "parse" to extract variable values for:
@xxxmdbug_schema_name,
@xxxmdbug_routine_name,
@xxxmdbug_line_number_minimum,
@xxxmdbug_line_number_maximum,
@xxxmdbug_condition_name,
@xxxmdbug_condition_value,
@xxxmdbug_is_temporary.

And use those variables in an INSERT statement:
INSERT INTO BREAKPOINTS
(schema_name,routine_name,line_number_minimum,
 line_number_maximum,condition_name,condition_value,xxxmdbug_is_temporary)
VALUES
(@xxxmdbug_schema_name,@xxxmdbug_routine_name,
 @xxxmdbug_line_number_minimum,@xxxmdbug_line_number_maximum,
 @xxxmdbug_condition_name,@xxxmdbug_condition_value,
 @xxxmdbug_is_temporary);

Common Routines: icc_copy_variable_to_table_row
-----------------------------------------------

icc_copy_variable_to_table_row() is called from generated routines.
It can happen at a breakpoint, so the user might change variable values
with the "set" command.

UPDATE VARIABLES SET value = variable-value WHERE variable_name = variable-name
AND statement_number_of_declare = statement-number-of-declare;
It would probably be good to add "AND Is_in_scope = 'True'".

The UPDATE might cause a warning, such as "Data truncated for column 'value'".
In that case, we know that VARIABLES does not have a perfect
copy of the variable, so set Is_settable = 'False'. When a variable
has Is_settable = 'False', the 'set' command is illegal.

Common Routines: icc_copy_table_row_to_variable
-----------------------------------------------

icc_copy_table_row_to_variable(variable, token_number_of_declare, variable_name)
is called from generated routines.
This is the reverse of icc_copy_variable_to_table_row.
It happens after a breakpoint.
(The user can only update variables when there's a breakpoint.)

We only need to copy if the user updated variables, with the "set" command.
So in icc_process_user_command(), when handling "set",
we set Is_updated_by_set = 'True'. We check it
now: "update ... where Is_updated_by_set = 'True'.
And set Is_updated_by_set back to 'False'.

SET variable = (SELECT value FROM VARIABLES WHERE variable_name = 'variable_name');

Common Routines: icc_breakpoint_check
-------------------------------------

Debuggee Connection will call icc_breakpoint_check() from
icc_core() once in order to check whether Debuggee Connection's
current location and current variable values are matches
for the location and condition columns in one or more
rows of the BREAKPOINTS table.

The BREAKPOINTS table has rows added
due to "INSERT INTO BREAKPOINTS ..." statements
earlier. The icc_core() routine will know the routine name
and line number from the original routine (that is part
of the "Generate:" work). So we can search BREAKPOINTS
for matching routine_names and line numbers. If any
rows are found, mark what they were and return a value
greater than zero. Otherwise return zero, that is,
there's no breakpoint here.

For example, suppose BREAKPOINTS contains this row:
  Breakpoint_identifier              1
  Routine_schema                     test
  Routine_name                       proc1
  Line_number_minimum                5
  Line_number_maximum                5
  Condition_name                     @f
  Condition_value                    7
  Old_value                          NULL
  Hit_count                          0
  Is_temporary                       N (not temporary)
  Is_temporary_and_to_be_cleared     N (not temporary)
  Is_matching_location               N
  Is_matching_condition              N
This means "if you're at line 5 in routine test.proc1,
and the current value of @f is equal to 7, then
increment hit_count and set Is_matching_location to 'True'
and set Is_matching_condition to 'True'.

There are actually two sets of criteria:
1. Is the location a match? The location columns are
   Routine_schema and Routine_name and Line_number_minimum
   and Line_number_maximum. They can be compared with the
   current  routine schema and routine name and line number
   (we know these due to 'Generate: call icc_core()' parameters).
   If location is a match, we increment "hit_count"
   ("hit" is a DB2 term), and we set "Is_matching_location"
   to "Y".
2. Is the condition a match? original condition expression had
   to have the form "name = literal" or "hit_count = literal"
   (see "Command: 'break' or 'tbreak'").
   We only check the condition if the
   earlier check ("is the location a match?") is true.
   If a condition is a match, we set Is_matching_condition='True'.

Those criteria, in SQL terms, look something like this:
  UPDATE BREAKPOINTS SET Is_matching_location='False',Is_matching_condition='False';
  UPDATE BREAKPOINTS SET Hit_count=Hit_count+1,Is_matching_location='True'
    WHERE Routine_schema LIKE current-routine-schema
    AND   Routine_name LIKE current-routine-name
    AND   current-line-number BETWEEN Line_number_minimum AND Line_number_maximum;
  UPDATE BREAKPOINTS SET Is_matching_condition='True'
  WHERE Is_matching_condition='True' AND [Condition is true]";
  RETURN (count of changed rows);

For the words "[Condition is true]", one must substitute the
somewhat cryptic-looking expression
  CASE condition_name = 'Hit_count' Hit_count
       condition_name = 'Call_stack_depth' /* see later paragraph */
  ELSE (SELECT value FROM VARIABLES WHERE variable_name = condition_name)
  END
  =
  condition_value
The first part of the expression works because Hit_count is a
column of the VARIABLES table. The second part of the expression
works because BREAKPOINTS.condition_name must be a variable name
(since it's not Hit_count), and VARIABLES.variable_name of course
is also a variable name. Of course, it's not quite that easy
because declared-variable names aren't unique.

If (count of changed rows > 0)
  UPDATE BREAKPOINTS SET Is_temporary_and_to_be_cleared='True' WHERE Is_temporary='True';

(NB: This is checked *before* the statement execution,
so if statement#x causes a change, the breakpoint happens
on the next statement.)

Matching the current routine with BREAKPOINTS.routine_schema
and BREAKPOINTS.routine_name should be simple. Just remember
it's a LIKE search, and beware that LIKE searches use '_' as
a wildcard by default.

Notice that you have to set Old_value at the time that
you do the INSERT INTO BREAKPOINTS:
INSERT INTO BREAKPOINTS VALUES ((SELECT Value FROM VARIABLES ...), etc.).

Finally, re the condition "call_stack_depth <= [n]":
it's of course possible to get call stack depth by
SELECT COUNT(*) FROM CALL_STACK;
but probably it would be better to have a permanent
user variable, @xxxmdbug_call_stack_depth,
incremented and decremented by starter and ender.

Common Routines: icc_send_statement_status
------------------------------------------

icc_send_statement_status(routine_schema, routine_name, line_number)
is called from icc_core, and from debuggee_wait_loop.

Create a "statement status" message.
Send it to Debugger Connection.
Put it in CONNECTIONS table.

The message contains, in addition to the usual ornaments:

'statement status': a literal
breakpoint: whether a breakpoint has been reached
location: schema_name, routine_name, line_number, statement_number
message_number: of last received message
datetime: current_timestamp

This is a no-wait message, the Debuggee Connection does
not expect a reply.

This is a temporary message, it is overwritten the next
time that icc_send_statement_status() is called.

Common Routines: icc_core
-------------------------

icc_core(routine_schema, routine_name, line_number, statement_number)
is called from generated routines, once per debuggable statement.

Its first job is to get and process any user commands that are pending.
Some user commands ('break', 'refresh', etc.) can be
sent by Debugger Connection at any time, and they go in a
queue until icc_core() handles them now.

Its second job is to check if it's at a breakpoint, for example,
if the pending statement is at line 5 of the original routine,
and there's a breakpoint for line 5 in the BREAKPOINTS table.
If that happens, then it goes into a loop, waiting for new
commands, and processing them. Some user commands ('continue',
'set', etc.) cannot be sent by Debugger Connection at any time,
they can only be sent while Debuggee Connection is in this loop,
signaling "I am at a breakpoint". The loop ends when Debuggee
Connection receives a command saying 'continue' or 'exit'.

REPEAT
  /* get and process any pending messages from Debugger Connection */
  CALL icc_get_user_command()
  CALL icc_process_user_command()
  Until there are no more messages from Debugger Connection
IF breakpoint_check() = 0 THEN
  /* We're not at a breakpoint, so continue */
  CALL icc_send_statement_status('continuing');
  RETURN
/* We're at a breakpoint */
CALL icc_send_statement_status('break');
REPEAT
  DO SLEEP(0.1 seconds)
  CALL get_user_command();
  CALL process_user_command();
  Until command = 'continue' or 'exit'
/* We're continuing from a breakpoint. */
/* Clear obsolete temporary breakpoints, and set
   breakpoints which are 'matching' to 'not matching'.
   We delayed this step till now so that Debugger
   Connection could examine what breakpoint caused
   execution to stop.*/
DELETE FROM BREAKPOINTS WHERE Is_temporary_and_to_be_cleared='True';
UPDATE BREAKPOINTS SET Is_condition_match='False',Is_location_match='False';
CALL icc_send_statement_status('continuing');

Common Routines: Change statement text
--------------------------------------

change_statement_text(input_statement,
                      output_statement_1,
                      output_statement_2)
This is a common routine.

It is called during generation (see "Generate: statement text"),
for dynamic SQL, and for the "execute" User Command.

If input_statement contains references to routines,
change them to references to surrogate routines.
The result will be one or two output statements.

Given that we have a tokenizer but not a parser,
how do we know if a token is a reference to a routine?
I think the answer is:
"If token matches a function name in the ROUTINES table,
and next token is '(', it's a function reference.
If token matches a procedure name in the ROUTINES table,
and previous token is 'CALL', it's a procedure reference."
I realize this answer will need tweaking for special
bits of MySQL syntax, especially after CREATE or JOIN.

For example, suppose input_string is
SELECT * FROM t WHERE x.y() = 5;

Scan the TOKENS table until you see '('.
Let @xxxmdbug_possible_routine_name = the previous token,
which is 'y'.
If the token before that is '.',
  Let @xxxmdbug_possible_routine_schema = the previous token,
  which is 'x'.
Otherwise @xxxmdbug_possible_routine_schema = database().
SELECT ...
FROM ROUTINES
WHERE @xxxmdbug_possible_routine_schema = Routine_schema_of_original
AND   @xxxmdbug_possible_routine_name = Routine_name_of_original
If there's a matching row
  If none of the preceding 3 tokens is CREATE or JOIN
    Let the output string = the input string,
    except that 'x.y' is replaced by xxxmdbug_name,Routine_name_of_surrogate.
Thus, the output string is
SELECT * FROM t WHERE xxxmdbug_1.x_y() = 5;

Common Routines: change statement text: special handling of SELECT
------------------------------------------------------------------

Debuggee Connection cannot handle a result set,
because it's inside a procedure -- it
cannot call mysql_result(). Therefore
Debuggee Connection must pass the result set
to Debugger Connection for handling. This is
possible by selecting into a table.
For example:
CREATE PROCEDURE p ()
BEGIN
  SELECT * FROM t;
  END
should become
CREATE PROCEDURE p ()
BEGIN
  CALL icc();
  BEGIN
    DROP TABLE IF EXISTS xxxmdbug.RESULTSET;
    CREATE TABLE xxxmdbug.RESULTSET AS SELECT * FROM t;
    END;
  END

Debugger Connection will be able to say
SELECT * FROM xxxmdbug.RESULTSET;
and use the result set from that.

The only little problems are:
* Debuggee Connection will drop RESULTSET when it encounters
  another SELECT statement. If users want to be sure that
  they can see result sets before they disappear, they will
  have to put breakpoints right after the SELECTs.
* SELECT isn't the only statement that can produce a
  result set. We have to replace every SHOW, and a few
  other minor statements, with the SELECTs that can
  produce the same result.

Update: this wasn't implemented.

Common Routines: Change statement text: special handling of SHOW WARNINGS
-------------------------------------------------------------------------

This is also known as the late diagnostics problem.

SHOW WARNINGS (and related statements like SHOW ERRORS)
will select from the xxxmdbug.DIAGNOSTICS table.
For details see the description of diagnostics_handler()
in section "Common Routines: diagnostics_handler".

Update: this wasn't implemented.

Common Routines: Change statement text: special handling of dynamic SQL
-----------------------------------------------------------------------

PREPARE and EXECUTE and DEALLOCATE require special handling.

The problem is that MySQL won't reveal what statements were prepared.
See also Bug#39791 "MySQL needs a SHOW PREPARED STATEMENTS report".
We do our best to solve that by saving the statement text
at PREPARE time, in the PREPARED_STATEMENTS table.
If (the statement is PREPARE stmt1 FROM @x)
  DELETE FROM PREPARED_STATEMENTS WHERE statement_name = 'stmt1';
  Insert into PREPARED_STATEMENTS('stmt1',@x);
If (the statement is EXECUTE stmt1)
  SELECT statement_text FROM PREPARED_STATEMENTS where statement_name = 'stmt1'
  /* If it's not found:
     This is a problem that we can't solve.
     If the user prepared stmt1 inside a debugged procedure,
     we know what it is. But otherwise, we just have to cross
     our fingers and hope the statement isn't changing variables
     or calling functions that are not being debugged. Or, we could generate an
     error. I suppose this can be the subject of a warning at
     the end of "Setup", and I suppose this can be an option
     (error-on-dynamic-sql-problem) when starting "Setup". */
  Tokenize(statement_text)
  If there are new variables, add them to VARIABLES.
  If there are function references, change to surrogate-function references.
  If this is a SELECT, don't forget "Special handling for SELECT"
  (We have a similar technique for User command "[Debuggee Connection] SQL statement".)
If (the statement is DEALLOCATE stmt1)
  Delete from PREPARED_STATEMENTS where statement_name = 'stmt1'

For example, if the statement is DEALLOCATE stmt1, we'd generate:
DELETE FROM PREPARED_STATEMENTS WHERE statement_name = 'stmt1';
DEALLOCATE stmt1;

Common Routines: Change statement text: special handling of RETURN
------------------------------------------------------------------

If the statement we're about to generate is RETURN,
we have to delete from CALL_STACK before returning.

Common Routines: Change statement text: special handling of SIGNAL/RESIGNAL
---------------------------------------------------------------------------

If the statement we're about to generate is SIGNAL or RESIGNAL,
we'll check whether the values might conflict with the
signal values that the debugger uses for its own purposes.

Recall that the user can begin with
"call setup (...signal_number...)"
and signal_number has a special meaning for the
debugger. For example, if signal_number = 5678
(the default), we will generate "SIGNAL ... mysql_errno=5678"
at the start of every surrogate routine.
It would confuse the debugger if the original
routine used the same mysql_errno value for signals.

So if the word in the STATEMENTS table is 'SIGNAL' or 'RESIGNAL',
and if any token in the TOKENS table is '5678' or '05678',
add a warning in the DIAGNOSTICS table. Warnings produced
during setup() are displayed when setup() ends.

Search the STATEMENTS table for any statement
that starts with 'SIGNAL' or 'RESIGNAL', search
the TOKENS table to see whether that statement
contains any token value equal to Signal_number,
and generate a warning if you find one.

Update: this wasn't implemented.

Common Routines: command
------------------------

command(channel,user command) is called by the user's
application on Debugger Connection.

Somehow be ready for a request to 'interrupt'
(like control-C), but I don't think command()
has any loops.

For any commands that require sending messages,
check first: is Debuggee Connection moribund?

Parse the command.

Do a SELECT, send a message, or whatever.
The basic explanations are in section "User Commands".

Common Routines: send
---------------------

send(message text)
passes a message between Debugger Connection
and Debuggee Connection. See the "ICC" sections.

Common Routines: README table
-----------------------------

The README table, a permanent table in xxxmdbug,
has data about the debugger and about the installation.
It's made thus:
CREATE TABLE README (
  Name
  Authors
  Version of debugger
  Date of Last Revision of debugger
  Copyright
  Release Notes
  Link to documentation
  Is_install_script_done)

"INSERT INTO README (fixed information)"
is done by the install script, install.sql.
The final column, Is_install_script_done, is set when
the install script is complete.

ICC (Inter-Connection Communication)
====================================

A foundation stone of the two-connections method is
that two connections must be able to communicate.
This is not what we could call "obviously possible".
MySQL has no equivalent of the DBMS_PIPE found in
Oracle 11g and EnterpriseDB. So I'd better show that
ICC (Inter-Connection Communication) is possible in
this context.

ICC: The choice of method
-------------------------

But how do Debugger Connection and Debuggee Connection
exchange messages?

* By updating tables in xxxmdbug? No.
The MySQL Reference Manual says:
"A stored function acquires table locks before executing,
to avoid inconsistency in the binary log due to mismatch
of the order in which statements execute and when they
appear in the log."
http://dev.mysql.com/doc/refman/5.5/en/stored-program-restrictions.html
... So, while Debuggee Connection is working with a function,
Debugger Connection cannot update any xxxmdbug tables
that Debuggee Connection might read.

* With GET_LOCK() and IS_USED_LOCK()? No.
TOAD found a use for GET_LOCK(), and Beat Vontobel
recommended it in his debugger suggestions.
But a connection can have a maximum of one (1) lock.
(This is Bug#1118 "Allow multiple concurrent locks with GET_LOCK()"
http://bugs.mysql.com/bug.php?id=1118.). So this
would fail if the debugged routine contained GET_LOCK,
and would convey too little information to be generally useful.

* With information_schema.processlist? No.
Debugger Connection could say "SELECT SLEEP(0.5),'message';" and
Debuggee Connection could see it by looking at
information_schema.processlist.Info. But this requires
dynamic SQL, and dynamic SQL is illegal in functions.

* With a file? No.
Debugger Connection can write to a file. But I can't see how
Debuggee Connection could read it. LOAD DATA doesn't work in
stored procedures. One could almost get something useful with
SELECT INTO OUTFILE and LOAD_FILE() but the inability to overwrite
files stopped me.

* With CREATE VIEW? No.
Debugger Connection could CREATE VIEW xxxmdbug.v
AS SELECT 'message'; and Debuggee Connection could see it by looking
at information_schema.views.view_definition. But this
requires dynamic SQL, and dynamic SQL is illegal in functions.

* With a global variable? Yes.
It's odd, but it's all we've got.
Debugger Connection could change a dynamic global variable,
and Debuggee Connection would see the change.
This requires a SUPER privilege, but that's required anyway.

The appropriate global variable is @@init_connect.
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect
It's dynamic, it's a string, and its contents can include.
/* inline comments */. 

Thus, to send messages, a connection appends them to
@@init_connect. Another connection, to receive messages,
extracts and strips them from @@init_connect with INSTR()
and SUBSTRING() functions.

ICC: A working example
----------------------

This is an example, an example that actually works,
of inter-connection communication using the method
described in the previous section. You can try it now.

You will need a default database (`test` is good enough).
You will need to start two connections, which I'll call
Connection #1 and Connection #2.

Using either connection, on the default database,
create two functions, named send() and recv():

DELIMITER //
CREATE FUNCTION send (debug_schema_n VARCHAR(64),
                      Message_number INT,
                      Message VARCHAR(64)) RETURNS INT>
BEGIN
  SET @debug_schema_n='debug_schema_1';
  SET @debug_schema_message_number = 1;
  SET @debug_schema_message = 'break Example_Procedure:5';
  SET GLOBAL init_connect = CONCAT(@@init_connect,
  '/*',@debug_schema_n,' Message=',Message_number,' ',Message,'*/');
  RETURN 0;
  END//
DELIMITER ;

DELIMITER //
USE test
DROP FUNCTION recv//
CREATE FUNCTION recv (debug_schema_n VARCHAR(64)) RETURNS VARCHAR(64)
BEGIN
  SET @debug_schema_message_start = INSTR(@@init_connect,CONCAT('/*',debug_schema_n,' Message='));
  SET @debug_schema_message_length = INSTR(SUBSTRING(@@init_connect FROM @debug_schema_message_start),'*/')+2;
  SET @debug_schema_message = SUBSTRING(@@init_connect FROM @debug_schema_message_start+2
                                      FOR @debug_schema_message_length-5);
  SET GLOBAL init_connect =
  CONCAT(
  LEFT(@@init_connect,@debug_schema_message_start+1),
  RIGHT(@@init_connect,LENGTH(@@init_connect)-(@debug_schema_message_start+@debug_schema_message_length)));
  RETURN @debug_schema_message;
  END//
DELIMITER ;

On Connection #1, which will be the 'sender',
send two messages:
'break Example_Procedure:5', and
'continue'. Say:
CALL send('debug_schema_1',1,'break Example_Procedure:5');
CALL send('debug_schema_1',2,'continue');

On Connection #2, which will be the 'receiver',
receive two messages. Say:
SELECT recv('debug_schema_1');
SELECT recv('debug_schema_1');

The result that you will see on Connection #2 is:
"
mysql> SELECT recv('debug_schema_1');
+----------------------------------------------------+
| recv('debug_schema_1')                             |
+----------------------------------------------------+
| debug_schema_1 Message=1 break Example_Procedure:5 |
+----------------------------------------------------+
1 row in set (0.00 sec)                               

mysql> SELECT recv('debug_schema_1');
+-----------------------------------+
| recv('debug_schema_1')            |
+-----------------------------------+
| debug_schema_1 Message=2 continue |
+-----------------------------------+
1 row in set (0.00 sec)
"

This proves messages can be passed and received,
from within functions, without requiring dynamic
SQL, without requiring tables.

ICC: Protocol
-------------

As we've seen, Debuggee Connection always stops
when it reaches a breakpoint and it's time to wait for Debugger Connection.
Debugger Connection doesn't have to wait for Debuggee Connection,
except that it can't say "continue" or "exit" or "set" until Debuggee Connection reaches a breakpoint.
The messages just keep being appended to @@init_connect,
so it's a message queue.

Debuggee Connection accepts messages from Debugger
Connection, processes them, and doesn't necessarily
respond.

At start:
Debuggee sends: "Debuggee Start is done".
Debugger sends: "attach.
Debuggee sends: "ok" or "not ok".

At any time, for 'break':
Debugger Connection sends. It doesn't wait for a reply.

At any time, for 'check if Debuggee is at a breakpoint':
Debugger Connection receives. If there's no message,
that means Debuggee isn't at a breakpoint. If there's
a message, it must be "Debuggee halted at a breakpoint".

If Debuggee is halted at a breakpoint:
Repeat
  Debugger Connection sends, e.g. 'set variable'.
  Debuggee Connection receives and replies: 'ack'.
  Until Debugger Connection has no more messages.
Debugger sends: 'continue'. It doesn't wait for a reply.

The only messages that Debuggee Connection sends are:
"at breakpoint":   Sent by Debuggee Connection.
                   Debugger Connection needs to
                   know this so that it can send
                   user commands which are only
                   possible during a breakpoint,
                   such as "set variable".
"debuggee start":  When Debuggee Connections finishes
                   the Debuggee Start procedures, it
                   sends a message:
                   Debug_schema name
                   User Name
                   Debuggee Connection's connection_id()
                   Start Time
"ack (ok/not ok)": response to Debugger sending.
"statement status":See Common Routines: icc_send_statement_status.

ICC: notes
----------

In reality the send() and recv() functions will be more
complex than the working example from the previous section.
These improvements will be necessary.

Allow for the chance that two debug sessions are
happening simultaneously. That's why one of the
components of a message is a channel like 'xxx', so
that messages for other debug sessions can be
detected and ignored.

Put in escapes. For example, it would be a disaster
if the message contained '/*' or '*/'. Such sequences
must be replaced with innocuous escape sequences.

While creating the surrogate routines, emit a
warning if any original routine changes @@init_connect.

Debugger Connection can send multiple messages as
separate appended comments. Debuggee Connection
will see them when it's about to handle any
debuggable statement, regardless whether it's at
a breakpoint or not.

Put in a message sequence number, but it doesn't get
much use. I expect it makes some error tests possible.
It also might prevent confusion when Debuggee responds
to a message: it indicates which message is it
responding to, if there's a queue.

Since @@init_connect has other uses, we have
to allow for mutex contention, getting junk
in the binlog, making @@init_connect too big,
cleaning up at the end of a session, and
recovering if some non-debug connection
wrecks the message-passing system by erasing
the inline comments.

Appendixes
==========

Appendix: Executing SQL statements on Debugger Connection
---------------------------------------------------------

On Debugger Connection, it's legal to execute any SQL statement,
as well as send User Commands. We will encourage this.
It's particularly useful that users can SELECT from copies
of xxxmdbug tables (BREAKPOINTS etc.) while
Debuggee Connection is running and is not at a breakpoint.
But watch out for some things.

Do not alter or drop an original routine.
This will cause the Debuggee Connection to abort
when it enters the surrogate routine.
You will have to run setup() again.

Expect concurrency issues due to locking.
If Debugger Connection says "SELECT * FROM t"
while Debuggee Connection is holding a lock on t,
Debugger Connection will wait until Debuggee
Connection releases the lock. Ordinarily that's
okay. But if Debuggee Connection now hits a
breakpoint, then it will wait for a command
from Debugger Connection -- which will never come.

Expect concurrency issues due to transactions.
SELECT statements will cause different results when
executed on Debugger Connection rather than Debuggee Connection.
For example,
SELECT * FROM t;
may differ from
CALL xxxmdbug.command("SELECT * FROM t;");
because Debuggee Connection has inserted into t but
not committed yet.

Appendix: Debugging without surrogates
--------------------------------------

Sometimes we will fail to catch an invocation of routine X and
change it to an invocation of the surrogate routine xxxmdbug000_X.
This causes us to miss breakpoints for that particular routine.
If the routine makes new user variables or prepared statements,
we'll also miss those changes.

There are four failure situations, involving privileges,
views, triggers, events, and attaching.

PRIVILEGES. We might not have the right to see the routine_definition.
This will never happen with a routine that we debug directly,
because if we don't have EXECUTE privilege on the routine then
we can't debug it anyway. But it can happen with a routine that
we debug indirectly, for example we say "debug P1()" and P1
contains a statement "CALL P2()" without definer privileges.

VIEWS. A view might contain a reference to a function, for example
"CREATE VIEW v AS SELECT * FROM t WHERE c=f()". We don't see that
when we're looking at a statement like "DELETE FROM v".
We could parse the view definitions and make "surrogate views",
but that's a harder job than making surrogate routines, because
(a) it's hard to find view references inside a DML statement
(b) view names are case sensitive
(c) we'd need more privileges.

TRIGGERS. A trigger is bound to a table. There's no way to
change that binding, except altering the table or replacing
the trigger definition. Incidentally SQL Server also had a
problem with triggers, and the proposed solution does not
look like ours. From SQL Server Limitations page:
  "Triggers must be fired to be debugged: you cannot debug
  triggers directly. Instead start debugging in a stored
  procedure that will cause the trigger to fire."
  http://msdn.microsoft.com/en-us/library/kkyhd4yb%28v=VS.80%29.aspx

EVENTS. We don't control the execution path that leads to
the activation of an event. It would actually be rather
easy to make "surrogate events" and generate code just as
we do for procedures and functions. But the Debugger
Connection couldn't "attach" to such an event because it
doesn't automatically call a "Debuggee start" process.
And as a general rule we can't say "debug x" if we can't
invoke x ourselves.

The solution is essentially similar for any of the failure situations.
An administrator -- someone who has the power to change definitions --
must change all the relevant routine code so that it invokes
surrogate routines rather than original-schema routines,
and/or so that the original-schema code calls surrogate routines.

For example, the administrator must change:

CREATE VIEW v AS SELECT schema1.function1() FROM t;
to
CREATE VIEW v AS SELECT schema1.xxxmdbug000Ffunction1() FROM t;

CREATE EVENT e AS CALL p();
to
CREATE EVENT e AS CALL xxxmdbug000Pp();

This is "intrusive", it changes the original code.
However, it's only visible to the administrator.
To the person running a debug session, it's still transparent.
And if somebody calls a surrogate routine while a debug
session isn't happening, the debug stuff is simply ignored,
the procedure runs as if the debug stuff isn't there.

We expect that some administrators will make scripts to
change original routines, or to change back to original routines.

One has to be careful about automatically dropping
surrogate routines, because there might be references to them,
if somebody has decided to do debugging without surrogates.

Appendix: Widgets or "Screens and Menus"
----------------------------------------

For two reasons this part of the description
will be much briefer than other sections:
1. Integration.
   Commonly the debugger is part of a larger package
   For example Oracle debugging can be integrated with Visual Studio.
   For example DB2 debugging can be integrated as "integrated
   stored procedure debugger in DB2® Developer Workbench Version 9.1."
   And dbForge and TOAD debuggers are part of packages.
   I expect that to happen for our debugger too.
   Therefore the elements of the GUI -- the
   screens and menus -- are already decided.
   For example, if F8 already means "exit program"
   in the larger package, then we're not going to
   be able to say that F8 means "clear breakpoint"
   in the debugger component.
2. Prior Knowledge.
   Publicly documented GUI debuggers exist.
   To find inspiration for most situations,
   we'll merely run DDD or read the SQL Server
   manual.

The program which controls the GUI is also
connected to MySQL as "Debugger Connection".

Routine definitions are in non-editable resizable
text boxes. Users can choose routines via
list boxes (listing all routines or all routines
in the call stack or all routines previously seen).
When a breakpoint is hit, the focus is the
box with the routine with the breakpoint.
... This part is simple since the texts are in
information_schema.routines.

A new event has to be programmed: "message exists".
It has to be activated when Debuggee sends a new message.

To set or clear a breakpoint, users choose a line in
the routine-definition box (by right clicking
or by hovering) and enter a function key.
... This would cause a User command: "break".

Click A to see current breakpoints.
... This would cause "SELECT ... FROM BREAKPOINTS".

Click B to see the call stack.
... This would cause "SELECT ... FROM CALL_STACK".

Click C to edit a watch list.
... This would bring up a list box that's populated
via "SELECT ... FROM VARIABLES".

Click D to stop.
... This would cause a User command "stop".

Click E to finish.
... This would cause a User command "finish".

Click F to change a variable.
... This would cause a User command "set".

Click G to change fonts, colors, languages,
widget sizes, widget positions, and widget visibility.

Click H to step.
... This would cause a User command "step".

Click I to next.
... This would cause a User command "next".

Click J to run to cursor.
... This would cause setting of a temporary
breakpoint with the User command "tbreak ...
[line number where cursor is at])", followed
by the User command "continue".

Click K to 'attach'.

Click L to animate.
... This would cause a slow-motion run ("steps" with
05-second sleeps), displaying the constantly-changing
values of statement status messages from the debuggee,
changing the text box and highlighting the current position (again this
comes from statement status messages), and updating the watchlist
(by selecting from VARIABLES). Snazzy effects would
include turning a watchlist item red when its value
changes, increasing a statement's font size when it
has been executed many times, and slowing down when
there are errors in execution (seen by selecting
from DIAGNOSTICS).

Click M to repeat a history.
... This would involve storing the last set of
User commands, and re-running them after re-starting.

Click N to get information about what's at the cursor.
... If it's over a variable name, display the value
and data type (by selecting from VARIABLES). If it's
over a statement, show how many times the statement
was hit (by selecting from STATEMENTS_EXECUTED).

Click O to bring up a dialog box to enter SQL statements.
... Debugger Connection can do whatever the user wants, as
long as it avoids anything that's locked by Debuggee Connection.

Or, just as DDD allows GDB input, go to the
bottom of the screen and enter any of the
items mentioned in the User Commands section.

Appendix: Feature requests for the server
-----------------------------------------

This document has described a debugger that would
work with MySQL 5.5 or an equivalent. It's possible
to change some of the techniques so that we depend
on features that are only available in an enhanced
variant of the server, such as MariaDB.
The enhanced variant would have any or all of:

Message passing, so that Debugger Connection could send
to Debuggee Connection without the "@@init_connect" tricks
that are currently in the proposition for the
interconnection communication. Or, don't require
that a function must lock tables when the tables
have engine=memory and they're in a database that
isn't being replicated.

Getting a token list as a result set from an SQL statement.

Allowing dynamic SQL within a function.

Allowing dynamic SQL to refer to declared variables.

Allowing a procedure to create a procedure.

Redirecting so that, for example, activations of trigger
"test.x" automatically go to trigger "xxxmdbug000Tx".

GET DIAGNOSTICS as in MySQL 5.6.

Automatically disabling replication for xxxmdbug databases.

Bug#1118 Allow multiple concurrent locks with GET_LOCK()
Bug#14487 Comments are being ripped out of SP's during restore.
Bug#17034 Error messages from stored routines should name their source
Bug#20307 Request SHOW command support for User-Defined Variables
Bug#22012 Store procedure: support LEAVE without label
Bug#39791 MySQL needs a SHOW PREPARED STATEMENTS report
Bug#48379 Cursors on SHOW are not working
Bug#58342 ROUTINES.ROUTINE_DEFINITION removes double quotes
Bug#61261 lock tables information