MySQL 5.7 on W2K12 essentials – starts fine and then fails after restart

I have a W2K12 Essentials server that I’m trying to get MySQL 5.7 running on for in house development. It installs just fine – I change install and data directories to two separate directories on the D:\ drive.

Service starts as normal. I stop it, go in and change the Logon to Local System Account with Allow Service to Interact with Desktop (installer sets it to Network Service). Restart service just fine.

Open up my.cnf to change the innodb_buffer_pool_size to 1G. Save changes. Won’t start (Services hangs on Starting). Revert change back to 8M. Service still won’t start and eventually fails.

Event viewer shows “The MySQL57 service terminated unexpectedly. It has done this 2 time(s).”

Task manager shows mysqld running but using only about 1.2mb of memory.

UPDATE – just uninstalled and reinstalled again. Left the Log on as Network Service, but gave Network Service full permissions on my MySQL folder and my MySQLData folder.

Stopped and restarted numerous times.

Copy my.cnf to a safe place. Change innodb_buffer_pool_size from 8M to 48M. Won’t start (again Task Manager shows 1.3mb memory usage). Kill task. Switch it back to 8M. Still won’t start. Overwrite my.cnf with my backup copy and now it works again. I am changing nothing else in that file and using notepad.exe to edit it.

mysql – importing large Tablespace: Lost connection during query

iam trying to recover innodb table which has 1.5M rows from ibd file ( 5.5 GB )

this is the exact steps i do:

  1. getting create table query using mysqlfrm command

  2. create the table

  3. Alter Table discard tablespace

  4. moving the new tablespace to the db directory

  5. Alter Table import tablespace;

and i’m getting this error after 5 minutes :-

ERROR 2013 (HY000): Lost connection to MySQL server during query my.cnf:

[client] port=3307 [mysql] no-beep  [mysqld] max_allowed_packet=8M innodb_buffer_pool_size=511M innodb_log_file_size=500M innodb_log_buffer_size=800M net_read_timeout=600 net_write_timeout=600 open_files_limit=100000 skip-grant-tables port=3307 datadir=D:\dbrecover\_home_db_\home\db default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" log-output=FILE general-log=0 general_log_file="WIN-36LFCDISVVC.log" slow-query-log=1 slow_query_log_file="WIN-36LFCDISVVC-slow.log" long_query_time=10 log-error="WIN-36LFCDISVVC.err" relay_log="WIN-36LFCDISVVC-relay" server-id=1 report_port=3307 lower_case_table_names=2 secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" max_connections=151 table_open_cache=2000 tmp_table_size=123M thread_cache_size=10 myisam_max_sort_file_size=100G myisam_sort_buffer_size=236M key_buffer_size=8M read_buffer_size=64K read_rnd_buffer_size=256K innodb_flush_log_at_trx_commit=1 innodb_thread_concurrency=9 innodb_autoextend_increment=64 innodb_buffer_pool_instances=8 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_open_files=300 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 back_log=80 flush_time=0 join_buffer_size=256K max_connect_errors=100 sort_buffer_size=256K table_definition_cache=1400 binlog_row_event_max_size=8K sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000 

is there any way to import it ??

How to implement a unique key for a long VARCHAR column in MySQL

I need to let MySQL to check the uniqueness of a VARCHAR column which is 5000 max length and is not the primary key. I can use an index of length 500 on the column to speed up query, but how to create an unique key that works?

An unique key of length 500 fails when trying to insert two records whose that column is the same for the first 500 characters and differs on somewhere after that.

Mysql JOIN query takes too much time to get the result

I have a mysql query as follows which JOIN’s 8 tables. When i use 3 tables to get the data the result is getting within 10 seconds. But whenever i add one more table the fetching time goes upto 1 minute. And if added more it takes infinite time. Any idea to resolve this problem?

following is my query

SELECT  c.`user_name`, e.`event_name`, e.`event_code`, e.`event_id`, COUNT(distinct ep.`participant_id`) as participants,  COUNT(DISTINCT pm.`program_material_id`) as material_count, COUNT(DISTINCT ev.`event_news_id`) as news_count ,  COUNT( DISTINCT  es.`event_speaker_id`) as speaker_count,  COUNT( DISTINCT  epr.`event_program_id`) as program_count, COUNT( DISTINCT  sw.`social_id`) as socail_wall_count  FROM `event` e LEFT JOIN `event_participant` ep ON ep.`event_id` = e.`event_id` LEFT JOIN `program_material` pm ON pm.`event_id` = e. `event_id` LEFT JOIN `event_news` ev ON ev.`event_id` = e. `event_id` LEFT JOIN `socialwall` sw ON sw.`event_id` = e. `event_id`  LEFT JOIN `event_speaker` es ON es.`event_id` = e. `event_id` LEFT JOIN `event_program` epr ON epr.`event_id` = e. `event_id` LEFT JOIN `event_customer` ec ON e.`event_id` = ec.`event_id` LEFT JOIN `customer` c ON ec.`customer_id` = c.`user_id`     GROUP BY e.`event_id` ORDER BY participants DESC LIMIT 0,10 

I indexed all tables primary keys and the columns which i used to JOIN in the subsequent tables. Here event is the master table and all other tables will have event_id

How to create Polygon from Point datatype in MySQL?

I have a table like this:

CREATE TABLE `aois` (   `aois_id` int(11) NOT NULL DEFAULT '0',   `WS_A` point DEFAULT NULL,   `WS_B` point DEFAULT NULL,   `WS_C` point DEFAULT NULL,   `WS_D` point DEFAULT NULL,   `DB_A` point DEFAULT NULL,   `DB_B` point DEFAULT NULL,   `DB_C` point DEFAULT NULL,   `DB_D` point DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 

Now i want to create a polygons from the points:

SELECT aois_id, polygon((WS_A, WS_B, WS_C, WS_D, WS_A)) as geom FROM aois; 

But i always get the error message:

Operand should contain 1 column(s) 

(I’m using MySQL Server Version 8.0.16 on Windows 10, if that is of any importance.)

Can anyone point me in the right direction?

Thanks a lot!

WordPress php mysql errors – errcode: 13 permission denied

I’m developing a WordPress based website using the latest XAMPP release from Oct 9 2019 with Apache 2.4.41, MySQL/MariaDB 10.4.8 and php 7.3.10 using localhost on a Windows 10 machine.

Since updating XAMPP I have been besieged with errors similar to the following:

PHP Warning: mysqli_query(): (HY000/6): Error on delete of ‘C:\xampp\tmp#sql35b0_ee_0.MAI’ (Errcode: 13 "Permission denied") in C:\xampp\htdocs\amc\wp-includes\wp-db.php on line 2007

WordPress database error Error on delete of ‘C:\xampp\tmp#sql35b0_ee_0.MAI’ (Errcode: 13 “Permission denied”) for query SHOW FULL COLUMNS FROM wp_options made by wp_reschedule_event, wp_schedule_event, _set_cron_array, update_option

I have checked the permissions of \xampp\tmp 50 times and authenticated users, system, administrators and users all have full control. I have also deleted WordPress and the associated data base and data tables several times yet the error on delete permission errors continue.

I’m seriously at a loss so any help would be greatly appreciated.

Thanks in advance

WordPress php mysql errors – errcode: 13 permission denied

I’m developing a WordPress based website using the latest XAMPP release from Oct 9 2019 with Apache 2.4.41, MySQL/MariaDB 10.4.8 and php 7.3.10 using localhost on a Windows 10 machine.

Since updating XAMPP I have been besieged with errors similar to the following:

PHP Warning: mysqli_query(): (HY000/6): Error on delete of ‘C:\xampp\tmp#sql35b0_ee_0.MAI’ (Errcode: 13 "Permission denied") in C:\xampp\htdocs\amc\wp-includes\wp-db.php on line 2007

WordPress database error Error on delete of ‘C:\xampp\tmp#sql35b0_ee_0.MAI’ (Errcode: 13 “Permission denied”) for query SHOW FULL COLUMNS FROM wp_options made by wp_reschedule_event, wp_schedule_event, _set_cron_array, update_option

I have checked the permissions of \xampp\tmp 50 times and authenticated users, system, administrators and users all have full control. I have also deleted WordPress and the associated data base and data tables several times yet the error on delete permission errors continue.

I’m seriously at a loss so any help would be greatly appreciated.

Thanks in advance

Errores de MySql y no se como solucionarlos

estoy haciendo un trabajo práctico y me dieron un archivo .sql con una base de datos y algunos datos. La idea es ejecutar el script y permitir que todo se cargue en mi mysql pero me tira algunos errores el script. (desde donde tengo que entregar el trabajo práctico dicen que a ellos no les genera ningún problema y no me ayudan que con mas que eso, a mi me pasa en 2 pcs distintas lo mismo). Adjunto archivo sql con los datos: Enlace a archivo .sql por mega Y aquí adjunto los errores que me salen.

ERROR 1366 (HY000): Incorrect string value: ‘\xC3\xA1n Na…’ for column ‘nombre’ at row 23

ERROR 1366 (HY000): Incorrect string value: ‘\xC3\xB3n’ for column ‘nombre’ at row 1

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (competencias.pelicula, CONSTRAINT pelicula_genero FOREIGN KEY (genero_id) REFERENCES genero (id))

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (competencias.actor_pelicula, CONSTRAINT ap_actor_id FOREIGN KEY (actor_id) REFERENCES actor (id))

ERROR 1366 (HY000): Incorrect string value: ‘\xC3\xA8s Ja…’ for column ‘nombre’ at row 5

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (competencias.director_pelicula, CONSTRAINT director_id FOREIGN KEY (director_id) REFERENCES director (id))

Estuve leyendo algo en internet y dice que los errores 1366 son porque no puede cargar los bytes y debo pasar mi base de datos a utf8 especial que tiene mysql y así funcionaria… Ejecuté algunos comandos alter table alter database que modificaban la codificación pero no se soluciono.

Poseo la versión 8.0.15 MySQL Community Server. Gracias de antemano

Mostrar ultimo registro en un Group By de MYSQL

Tengo la siguiente consulta.

SELECT B.id,B.linea,B.DateTime,B.EndDate,C.id_employee,A.id_empatarollos, A.psb_hr, E.description, SUM(psb_produc) AS TotalProduction ,  SUM(psb_rate) AS TotalRateExpect, SUM(scrap) AS TotalScrap FROM psb_smt.psb_produccion A INNER JOIN psb_smt.psb_info B      ON A.id_psbinfo = B.id INNER JOIN psb_smt.psb_employees C      ON A.id_employee = C.id_employee INNER JOIN psb_smt.psb_employees D      ON A.id_empatarollos = D.id_employee INNER JOIN psb_smt.psb_hours E     ON A.psb_hr = E.id_desc WHERE A.DateTime>= '2019-10-16 06:40:00'  AND A.DateTime <= '2019-10-16 16:10:00' AND A.hour_psb = '1S_HOURS' GROUP by B.linea order by B.DateTime DESC 

Con esa consulta obtengo la siguiente informacion. introducir la descripción de la imagen aquí

Pero en el campo descripcion deberia obtener mi ultimo registro que es 02:00 – 03:00