· 9 years ago · Nov 26, 2016, 12:10 PM
10 13 22:46:19 delete from parser_customer_period_mo where id = 1 Error Code: 1205. Lock wait timeout exceeded; try restarting transaction 51.169 sec
2
3SELECT
4 SUBSTRING_INDEX(SUBSTRING_INDEX(m.moname,'LabelName=',-1),',',1) as 'MO Name' ,p.datetime_start,( ( zzce_SOMECOUNTER.value + zzce_SOMEOTHERCOUNTER.value - zzce_SOMECOUNTERUra.value - zzce_SOMEOTHERCOUNTERUra.value + zzce_ANOTHERCOUNTER.value ) ) as value
5 FROM
6 parser_customer_mos AS m
7 INNER JOIN
8 parser_customer_period_mo pm ON m.id = pm.moid
9 INNER JOIN
10 parser_period p ON pm.periodid = p.id
11 INNER JOIN zzce_SOMECOUNTER ON zzce_SOMECOUNTER.moperiod = pm.id INNER JOIN zzce_SOMEOTHERCOUNTER ON zzce_SOMEOTHERCOUNTER.moperiod = pm.id INNER JOIN zzce_SOMECOUNTERUra ON zzce_SOMECOUNTERUra.moperiod = pm.id INNER JOIN zzce_SOMEOTHERCOUNTERUra ON zzce_SOMEOTHERCOUNTERUra.moperiod = pm.id INNER JOIN zzce_ANOTHERCOUNTER ON zzce_ANOTHERCOUNTER.moperiod = pm.id
12 WHERE
13 m.id IN (SELECT
14 id
15 FROM
16 parser_customer_mos
17 WHERE
18 moname LIKE 'SomeFilter%' or moname LIKE 'SomeOtherFilter_%' or moname LIKE 'AnotherFilter_%')
19 AND
20 pm.periodid IN (SELECT
21 id
22 FROM
23 parser_period
24 WHERE
25 datetime_start BETWEEN '2015-12-12 00:00:00' AND '2015-12-12 17:44:28') ORDER BY datetime_start asc;
26
27CREATE TABLE IF NOT EXISTS `perf_customer_rnc`.`parser_customer_mos` (
28 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
29 `moname` VARCHAR(200) NOT NULL DEFAULT 'asd' COMMENT '',
30 `nename` VARCHAR(10) NOT NULL DEFAULT 'asd' COMMENT '',
31 PRIMARY KEY (`moname`, `nename`) COMMENT '',
32 UNIQUE INDEX `id_UNIQUE` (`id` ASC) COMMENT '')
33 ENGINE = InnoDB
34 AUTO_INCREMENT = 27358
35 DEFAULT CHARACTER SET = latin1
36
37CREATE TABLE IF NOT EXISTS `perf_customer_rnc`.`parser_period` (
38 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
39 `datetime_start` DATETIME NOT NULL DEFAULT '2001-01-01 00:01:00' COMMENT '',
40 `datetime_end` DATETIME NOT NULL DEFAULT '2001-01-01 00:01:00' COMMENT '',
41 `datetime_gmt_start_minutes` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '',
42 `datetime_gmt_end_minutes` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '',
43 `measurement_elapsed_minutes` SMALLINT(6) NULL DEFAULT NULL COMMENT '',
44 PRIMARY KEY (`datetime_start`, `datetime_end`, `datetime_gmt_start_minutes`, `datetime_gmt_end_minutes`) COMMENT '',
45 UNIQUE INDEX `id_UNIQUE` (`id` ASC) COMMENT '')
46ENGINE = InnoDB
47AUTO_INCREMENT = 9904
48DEFAULT CHARACTER SET = latin1
49
50CREATE TABLE `perf_customer_rnc`.`parser_customer_period_mo` (
51 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
52 `moid` BIGINT(20) UNSIGNED NOT NULL COMMENT '',
53 `periodid` BIGINT(20) UNSIGNED NOT NULL COMMENT '',
54 PRIMARY KEY (`moid`, `periodid`) COMMENT '',
55 UNIQUE INDEX `id_UNIQUE` (`id` ASC) COMMENT '',
56 INDEX `moid_idx` (`moid` ASC) COMMENT '',
57 INDEX `periodid_idx` (`periodid` ASC) COMMENT '',
58 CONSTRAINT `mofk`
59 FOREIGN KEY (`moid`)
60 REFERENCES `perf_customer_rnc`.`parser_customer_mos` (`id`)
61 ON DELETE CASCADE
62 ON UPDATE NO ACTION,
63 CONSTRAINT `periodfk`
64 FOREIGN KEY (`periodid`)
65 REFERENCES `perf_customer_rnc`.`parser_period` (`id`)
66 ON DELETE CASCADE
67 ON UPDATE NO ACTION)
68ENGINE = InnoDB
69AUTO_INCREMENT = 42331658
70DEFAULT CHARACTER SET = latin1
71
72CREATE TABLE IF NOT EXISTS `perf_customer_rnc`.`zzce_CounterName` (
73 `moperiod` BIGINT(20) UNSIGNED NOT NULL COMMENT '',
74 `value` INT(11) NOT NULL COMMENT '',
75 PRIMARY KEY (`moperiod`) COMMENT '',
76 CONSTRAINT `fk_CounterName`
77 FOREIGN KEY (`moperiod`)
78 REFERENCES `perf_customer_rnc`.`parser_customer_period_mo` (`id`)
79 ON DELETE CASCADE
80 ON UPDATE NO ACTION)
81ENGINE = InnoDB
82DEFAULT CHARACTER SET = latin1
83
84CREATE TABLE IF NOT EXISTS `perf_customer_rnc`.`zzce_someOtherCounterName` (
85 `moperiod` BIGINT(20) UNSIGNED NOT NULL COMMENT '',
86 `value` VARCHAR(112) NOT NULL COMMENT '',
87 PRIMARY KEY (`moperiod`) COMMENT '',
88 CONSTRAINT `fk_someOtherCountername`
89 FOREIGN KEY (`moperiod`)
90 REFERENCES `perf_customer_rnc`.`parser_customer_period_mo` (`id`)
91 ON DELETE CASCADE
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB
94DEFAULT CHARACTER SET = latin1
95
96CREATE TABLE IF NOT EXISTS `perf_customer_rnc`.`zzce_someOtherCounterName2` (
97 `moperiod` BIGINT(20) UNSIGNED NOT NULL COMMENT '',
98 `value` BIT(1) NOT NULL COMMENT '',
99 `value2` BIT(1) NOT NULL COMMENT '',
100 `value3` BIT(1) NOT NULL COMMENT '',
101 `value4` BIT(1) NOT NULL COMMENT '',
102 `value5` BIT(1) NOT NULL COMMENT '',
103 `value6` BIT(1) NOT NULL COMMENT '',
104 `value7` BIT(1) NOT NULL COMMENT '',
105 `value8` BIT(1) NOT NULL COMMENT '',
106 `value9` BIT(1) NOT NULL COMMENT '',
107 `value10` BIT(1) NOT NULL COMMENT '',
108 `value11` BIT(1) NOT NULL COMMENT '',
109 `value12` BIT(1) NOT NULL COMMENT '',
110 `value13` BIT(1) NOT NULL COMMENT '',
111 `value14` BIT(1) NOT NULL COMMENT '',
112 `value15` BIT(1) NOT NULL COMMENT '',
113 `value16` BIT(1) NOT NULL COMMENT '',
114 `value17` BIT(1) NOT NULL COMMENT '',
115 `value18` BIT(1) NOT NULL COMMENT '',
116 `value19` BIT(1) NOT NULL COMMENT '',
117 `value20` BIT(1) NOT NULL COMMENT '',
118 `value21` BIT(1) NOT NULL COMMENT '',
119 `value22` BIT(1) NOT NULL COMMENT '',
120 PRIMARY KEY (`moperiod`) COMMENT '',
121 CONSTRAINT `fk_SomeCounterName`
122 FOREIGN KEY (`moperiod`)
123 REFERENCES `perf_customer_rnc`.`parser_customer_period_mo` (`id`)
124 ON DELETE CASCADE
125 ON UPDATE NO ACTION)
126ENGINE = InnoDB
127DEFAULT CHARACTER SET = latin1
128
129#
130# The MySQL database server configuration file.
131#
132# You can copy this to one of:
133# - "/etc/mysql/my.cnf" to set global options,
134# - "~/.my.cnf" to set user-specific options.
135#
136# One can use all long options that the program supports.
137# Run program with --help to get a list of available options and with
138# --print-defaults to see which it would actually understand and use.
139#
140# For explanations see
141# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
142
143# This will be passed to all mysql clients
144# It has been reported that passwords should be enclosed with ticks/quotes
145# escpecially if they contain "#" chars...
146# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
147[client]
148port = 7979
149socket = /var/run/mysqld/mysqld.sock
150
151# Here is entries for some specific programs
152# The following values assume you have at least 32M ram
153
154# This was formally known as [safe_mysqld]. Both versions are currently parsed.
155[mysqld_safe]
156socket = /var/run/mysqld/mysqld.sock
157nice = 0
158
159[mysqld]
160#
161# * Basic Settings
162#
163user = mysql
164pid-file = /var/run/mysqld/mysqld.pid
165socket = /var/run/mysqld/mysqld.sock
166port = 7979
167basedir = /usr
168datadir = /var/lib/mysql
169tmpdir = /tmp
170lc-messages-dir = /usr/share/mysql
171skip-external-locking
172#
173# Instead of skip-networking the default is now to listen only on
174# localhost which is more compatible and is not less secure.
175#bind-address = 127.0.0.1
176#
177# * Fine Tuning
178#
179key_buffer = 16M
180max_allowed_packet = 16M
181thread_stack = 192K
182thread_cache_size = 8
183# This replaces the startup script and checks MyISAM tables if needed
184# the first time they are touched
185myisam-recover = BACKUP
186#max_connections = 100
187#table_cache = 64
188#thread_concurrency = 10
189#
190# * Query Cache Configuration
191#
192query_cache_limit = 1M
193query_cache_size = 16M
194#
195# * Logging and Replication
196#
197# Both location gets rotated by the cronjob.
198# Be aware that this log type is a performance killer.
199# As of 5.1 you can enable the log at runtime!
200#general_log_file = /var/log/mysql/mysql.log
201#general_log = 1
202#
203# Error log - should be very few entries.
204#
205log_error = /var/log/mysql/error.log
206#
207# Here you can see queries with especially long duration
208#log_slow_queries = /var/log/mysql/mysql-slow.log
209#long_query_time = 2
210#log-queries-not-using-indexes
211#
212# The following can be used as easy to replay backup logs or for replication.
213# note: if you are setting up a replication slave, see README.Debian about
214# other settings you may need to change.
215#server-id = 1
216#log_bin = /var/log/mysql/mysql-bin.log
217expire_logs_days = 10
218max_binlog_size = 100M
219#binlog_do_db = include_database_name
220#binlog_ignore_db = include_database_name
221#
222# * InnoDB
223#
224# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
225# Read the manual for more InnoDB related options. There are many!
226#
227# * Security Features
228#
229# Read the manual, too, if you want chroot!
230# chroot = /var/lib/mysql/
231#
232# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
233#
234# ssl-ca=/etc/mysql/cacert.pem
235# ssl-cert=/etc/mysql/server-cert.pem
236# ssl-key=/etc/mysql/server-key.pem
237
238
239
240[mysqldump]
241quick
242quote-names
243max_allowed_packet = 16M
244
245[mysql]
246#no-auto-rehash # faster start of mysql but no tab completition
247
248[isamchk]
249key_buffer = 16M
250
251#
252# * IMPORTANT: Additional settings that can override those from this file!
253# The files must end with '.cnf', otherwise they'll be ignored.
254#
255!includedir /etc/mysql/conf.d/