Tag Info

New answers tagged

0

Looking for the same thing, I came across the report bug about this issue; In addition to some weird tab naming that is also happening to me. http://bugs.mysql.com/bug.php?id=74966


1

If you can provide sqlfiddle with data sample that would help a lot. But so far test this query please: SELECT p . *, c.cTrained, c.aTrained FROM (`person` p) INNER JOIN (SELECT `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`, IF(sum(c.course_id = 2) > 0, 'Yes','No') as `cTrained`, IF(sum(c.course_id = 4) ...


3

First, get rid of the subquery. It is not needed and it can interfere with optimization. Second, you don't need a left join, because the having clause is requiring matches. SELECT p .*, IF(sum(c.course_id = 2) > 0, 'Yes', 'No') as cTrained, IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as aTrained FROM person p JOIN ...


1

It's already solved in MySQL Workbench 6.3, I've just tested it an it works (it keeps only the previous tabs between sessions).


0

Make sure the event scheduler is on SET GLOBAL event_scheduler = ON; and then create the event as below: CREATE EVENT hourly_temp_avg ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO `table1` (`dateavg`, `avg_temperature`) SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:00:00') , avg(`temperature`) FROM `new_table` WHERE `date` ...


0

No you can't. SSH is an operating system for remote administration, what is happening when you do ssh and directly enter to the database is because of a script or default shell changed in /etc/passwd for the specific user. But for mariadb(mysql) (including workbench) this means nothing, cause in practice is very likely to do ssh root@yourserver and then ...


1

I discussed How is INFORMATION_SCHEMA implemented in MySQL? about 4 years ago. SQL and PostgreSQL have ObjectID enumeration (oid) of object types. MySQL made the INFORMATION_SCHEMA database a more simplified representation of table, column, and index metadata. The metadata tables are temporary MEMORY tables with no indexes. Last year in Adding new tables ...


0

You may find this useful. It's TwinDB's data recovery kit. According to the site: TwinDB data recovery toolkit is a set of tools that operate with InnoDB tablespaces at low level. The toolkit allows to recover data: After table was dropped If InnoDB tablespace is corrupt so badly so MySQL doesn’t start with innodb_force_recovery If file system with InnoDB ...


1

In addition to Rolando's suggested schema and my improvement on RENAME, you may want two more things: Purge "old" data via DROP PARTITION. This is about the only real use for moving to partitioning. Add a new partition using ALTER TABLE REORGANIZE PARTITION p99999999999 INTO PARTITION p2016_01_jan VALUES LESS THAN ('2016-02-01'), PARTITION p99999999999 ...


1

Here are the Steps to Partition Your Table by Month Create a Temp Table, Partitioned and Indexed on LogDate CREATE TABLE paramlog_ems_new ( SiteIndex smallint(5) unsigned NOT NULL, RegionIndex smallint(5) unsigned NOT NULL, OrganizationIndex smallint(5) unsigned NOT NULL, DeviceSlaveId smallint(5) unsigned NOT NULL, UserIndex smallint(5) unsigned ...


2

See the mydata.csv file at bottom of the answer. I created a table xy CREATE TABLE `xy` ( `fred` int(11) DEFAULT NULL, `mary` int(11) DEFAULT NULL, `billy` varchar(50) DEFAULT NULL ); From the documentation here, I tried this LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ...



Top 50 recent answers are included