I have a Windows MySQL server running (5.6), and in the my.ini file I have the following directive under [mysqld]: lc_time_names = 'fr_FR'; But it has ZERO effect. When I start up the server, all time names are still in English. If I then launch Workbench and issue the following command: SET GLOBAL lc_time_names = 'fr_FR'; it works and the times are changed. Is there something different I need to do in my.ini? UPDATE (VERY IMPORTANT): in addition to needing to put this value in the correct ini file, you also need to NOT ADD QUOTES!! I broke my server with the above, and it took me forever to figure out I needed to change the line to lc_time_names = fr_FR WITHOUT THE QUOTES, or your MySQL server will not start up! Hope this helps someone else.

Long story short. I have a MySQL database that has only one table and is around 50GB, while my drive storage is almost full and cannot get reduced. So the only way is to reduce the size of the 50GB MySQL data table (the app I use is MySQL Workbench). I tried the Delete function but it does not work becase it requires additional storage (which I don't have) to perform the Optimize function so that the underlying IBD file can be truly reduced. I tried copying the IBD file to a different drive in the same computer and reopening it there using MySQL but it does not work as it seems opening an IBD file is not straight forward and very complicated based on my online research. In addition, then I tried changing MySQL's folder to a different drive path with the IBD file saved there by editing the my.ini file but the app cannot work after the change of folder path. So, is there any workaround such as creating a new database in some format through querying partial data from the existing table that MySQL can open (showing in the schema) before deleting the existing IBD file completely? And what is the possible reason that MySQL app cannot run after I changed the folder path in my.ini file? since the current drive has a very small storage, I will mostly likely change it to a different drive which is bigger to cater for future database growth.

Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT queries that run with WHERE clause on few columns (both INT and VARCHAR columns) on a table that has ~ 8 million records (no JOINs) and they used to work very well, but strangly after the upgrade they do NOT work at all... some queries on this table are MUCH slower :-( We don't want to stay with MySQL 5.7 as it will be discontinued this year. Have you had any similar problem as I have? I tweaked my.ini the same way I did in MySQL 5.7 by increasing buffer sizes, etc. Please help. I will provide more details later if it's necessary like the table structure, example queries and my.ini . Thank you very much. **EDIT:** TABLE AND INDEXES DEFINITIONS: > CREATE TABLE IF NOT EXISTS event ( id int(11) NOT NULL > AUTO_INCREMENT, imei char(50) NOT NULL, cradle_id varchar(45) > NOT NULL, company_id int(10) unsigned NOT NULL, event_type > int(10) NOT NULL, lat double NOT NULL, lon double NOT NULL, > speed double NOT NULL, dtime datetime NOT NULL, force_level > double NOT NULL DEFAULT '0', duration double NOT NULL, > force_hist varchar(300) NOT NULL DEFAULT 'non', lr_force_hist > varchar(300) NOT NULL DEFAULT 'non', speed_hist varchar(250) NOT > NULL DEFAULT 'non', milage double NOT NULL, filter int(11) > DEFAULT '1', filter_reason int(11) DEFAULT NULL, road_type > int(11) DEFAULT NULL, calibration_variance double DEFAULT '0', > reason int(11) DEFAULT NULL, reason_other text, > distance_adjustment float DEFAULT NULL, max_speed smallint(6) > DEFAULT NULL, location_name varchar(225) DEFAULT NULL, > device_id char(17) DEFAULT NULL, utc_offset int(11) NOT NULL > DEFAULT '0', duplicated int(11) DEFAULT '0', PRIMARY KEY (id), KEY event_index (imei,dtime,event_type,filter), KEY event_index2 (device_id,dtime,event_type,filter), KEY event_index3 (filter) ) ENGINE=InnoDB AUTO_INCREMENT=40026132 DEFAULT CHARSET=utf8; SLOW QUERY LOG: Query_time: 2.986858 Lock_time: 0.000001 Rows_sent: 1 Rows_examined: 42355 SET timestamp=1680260078; select * from event where ( ( imei = "0e69eb4e-9999-494c-873d-9416d3569ab0" and dtime > "2021-12-16 13:23:50" ) or ( imei = "ae7f20e8-34d8-48e4-99a1-ff6197bc16ff" and dtime > "2021-11-17 14:44:05" and dtime "2021-01-21 08:46:21" and dtime "2018-10-07 19:45:26" and dtime "2017-07-31 11:58:57" and dtime "2016-10-11 09:02:37" and dtime 1 SIMPLE event range event_index3,event_index event_index 155 82900 50.00 Using > index condition; Using MRR; Using filesort EXPLAIN of the above query on MySQL 5.7: > 1 SIMPLE event range event_index3,event_index event_index 155 82900 50.00 Using > index condition; Using filesort The query above takes 1.167099 second on MySQL 8.0, but is super fast on MySQL 5.7, only 0.1 second on MySQL 5.7 This happens with default MySQL 8.0 settings in my.ini and also with tweaked my.ini (increased buffer sizes). Both of the databases have KEYRING plugin enabled. I tried turning off different indexes (setting them INVISIBLE) on MySQL 8.0 but it didn't speed up the query at all. The server has NVME disk that reads with speed ~ 12 GB/s. There are about 8 million records in that table. Unfortunatelly I cannot attach entire dump of data here, because it would violate privacy of clients. I can create some dummy data though if you need it to help me. Thank you.

MariaDB 10.7 service suddenly stopped in Windows 10 after a crash and the service is not starting. The pop up shows 'windows cannot start mariadb service). The windows event viewer gives the following data: - InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. - InnoDB: Page [page id: space=0, page number=366] log sequence number 424180275 is in the future! Current system log sequence number 408454168. - InnoDB: unsupported undo header type 16 - InnoDB: Plugin initialization aborted with error Data structure corruption - Plugin 'InnoDB' init function returned error. - Plugin 'InnoDB' registration as a STORAGE ENGINE failed. - Unknown/unsupported storage engine: InnoDB - InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT for tablespace 251 Cannot restart the MaraDB service.

I would like to know, if possible, how to display (using the command line) the path to my.ini used by the server. I am using MySQL 5.6 inside a windows IIS. I have the following: - Program Files\MySQL\MySQL Server 5.6\my-default.ini - C:\ProgramData\MySQL\MySQL Server 5.6\my.ini. For sure, the server is using the second one. Is the path stored in any variable inside mysql server variables? I was checking some system variables but I couldn't find it. Any ideas? ------------------------------------------------------------------------------- UPDATE After Ronaldo's post I ran a --help and this is what I found: ... Default options are read from the following files in the given order: C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.6\my.ini C:\Program Files\MySQL\MySQL Server 5.6\my.cnf The following groups are read: mysql client The following options may be given as the first argument: --print-defaults Print the program argument list and exit. ... The strange thing is that I don't have none of these files. I sit to display hided files on each folder. So, I took a look to this post and I followed the steps. Finally I arrived at the window **MySQL56 Properties(Local Computer)**. I could read: Path to executable: "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file BUT, if we click over it with the mouse's right button and choose Select All, this is what we have: "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56 So that replies my question.

I upgraded MySql workbench to version 8. I'm running MySql Server 5.7. When I open the options file in workbench it says it cannot locate a file for MySql 8 and that it will create one in the path C:\ProgramData\MySql\MySql Server 8\my.ini I do not have MySql Server 8 installed. - Why is workbench not finding the file? - How can I point it towards the correct file? - Do I need to downgrade workbench?

I have to edit the innodb_page_size to 32K but I cannot find innodb_page_size in [mysqld] section in my.ini file. I am using MySQL Server 8.0.13. Where can I find it?

I've just installed on my new server **MySQL 5.0.24 64bit** (till now i've used same version of MySQL but **32bit** on another server), on the *old server* i've changed in my.ini the parameters of key_buffer_size, innodb_buffer_pool_size and some options for the *cache*, now on the new server i've changed the same key_buffer_size and innodb_buffer_pool_size but by incrementing the assigned memory to 20G on both. The new server is like three times more powerful than the old one, but, after i've done the backup of all the databases from the old server to the new i tought it will be more faster while by doing the same select query on both servers the new one result more slower.. OLD Server: NEW Server (same db and table): So how should i configure the my.ini for get the best performance for that server? Here is how's the my.ini right now (almost all tables are in MyISAM): # MySQL Server Instance Configuration File # ---------------------------------------------------------------------- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # ---------------------------------------------------------------------- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To # make sure the server reads the config file use the startup option # "--defaults-file". # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini" # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini" # # And then execute this in a command line shell to start the server, e.g. # net start MySQLXY # # # Guildlines for editing this file # ---------------------------------------------------------------------- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the "--help" option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # ---------------------------------------------------------------------- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # ---------------------------------------------------------------------- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #skip-name-resolve #Path to installation directory. All paths are usually resolved relative to this. basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.0/" #Path to the database root datadir="C:/Program Files (x86)/MySQL/MySQL Server 5.0/Data/" # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=INNODB # Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=341 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=9M # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] table_cache=700 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=16M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) thread_cache_size=17 #*** MyISAM Specific options # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index will be created # through the key cache (which is slower). myisam_max_sort_file_size=100G # If the temporary file used for fast index creation would be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. This is mainly used to force long character keys in # large tables to use the slower key cache method to create the index. myisam_max_extra_sort_file_size=100G # If the temporary file used for fast index creation would be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. This is mainly used to force long character keys in # large tables to use the slower key cache method to create the index. myisam_sort_buffer_size=8M # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=20G # Size of the buffer used for doing full table scans of MyISAM tables. # Allocated per thread, if a full scan is needed. read_buffer_size=64K read_rnd_buffer_size=256K # This buffer is allocated when MySQL needs to rebuild the index in # REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE # into an empty table. It is allocated per thread so be careful with # large settings. sort_buffer_size=208K #*** INNODB Specific options *** # Use this option if you have a MySQL server with InnoDB support enabled # but you do not plan to use it. This will save memory and disk space # and speed up some things. #skip-innodb # Additional memory pool that is used by InnoDB to store metadata # information. If InnoDB requires more memory for this purpose it will # start to allocate it from the OS. As this is fast enough on most # recent operating systems, you normally do not need to change this # value. SHOW INNODB STATUS will display the current amount used. innodb_additional_mem_pool_size=2M # If set to 1, InnoDB will flush (fsync) the transaction logs to the # disk at each commit, which offers full ACID behavior. If you are # willing to compromise this safety, and you are running small # transactions, you may set this to 0 or 2 to reduce disk I/O to the # logs. Value 0 means that the log is only written to the log file and # the log file flushed to disk approximately once per second. Value 2 # means the log is written to the log file at each commit, but the log # file is only flushed to disk approximately once per second. innodb_flush_log_at_trx_commit=2 innodb_flush_method=normal # The size of the buffer InnoDB uses for buffering log data. As soon as # it is full, InnoDB will have to flush it to disk. As it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions). innodb_log_buffer_size=1M # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=20G # Size of each log file in a log group. You should set the combined size # of log files to about 25%-100% of your buffer pool size to avoid # unneeded buffer pool flush activity on log file overwrite. However, # note that a larger logfile size will increase the time needed for the # recovery process. innodb_log_file_size=256M # Number of threads allowed inside the InnoDB kernel. The optimal value # depends highly on the application, hardware as well as the OS # scheduler properties. A too high value may lead to thread thrashing. innodb_thread_concurrency=16 The new server has 64GB Ram, 1.5TB os SSD and 10x Intel(R) Xeon(R) CPU E5-2630 @ 2.20GHz

I'm trying to create a DataBase for a very little program that just I and 2 more friends use, so the database will be little. What I want to do is to share this database with these 2 friends, so I thought about storing the data in a OneDrive shared folder. At the moment, I'm using a txt file as a "database". Which is placed in a shared OneDrive folder, so when my friends execute the program, it can read the data from there and make it sort of real time "online". The thing is that I can't find the my.ini file in my C:\Program Files\MySQL\MySQL Server 8.0\ directory, so I can't change the data folder. Another trouble I have is that I also don't have the Data folder in that directory, instead I've found it in this one: C:\Program Files\MySQL\MySQL Workbench 8.0 CE Is it possible to do what I want to do? And how should I proceed? Do you think I should be using MySQL 5.x version? Thanks

Where can I find innodb_page_size option? Since I cannot find it in [mysqld] section in my.ini file. I want to change it to 32k or 64k since I am getting the below error: > MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. I know by default it is 16k. I am using MySQL Server 8.0.13, MySQL Workbench 8.0.13

I need a CSV with all the MySQL Server Variables and a **basic description** of what the variable means. Like you can find here: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html I know how to get the Server Variables + values, but I want an **extra column** behind it, with the **description** of what it means. The MySQL workbench program, has these values, see "*Status and System variables*" but when I copy these lines and paste them in a spreadheet, only the names and values are pasted, but not the description. I have not been able to find it on google or this forum. Strange because I would suspect others could have had this question before. Thank for your help.

I am facing a very rare problem with my DB. I have Windows Server with 64 GB RAM but I am not able to increase innodb buffer pool size in mysql. Current InnoDb buffer pool Size : 1.6G When I increase it and restart Mysql then it throws Error : 161026 0:52:30 [Note] Plugin 'FEDERATED' is disabled. 161026 0:52:31 InnoDB: Initializing buffer pool, size = 2.6G 161026 0:52:31 InnoDB: Error: cannot allocate 2816491520 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 22604620 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. InnoDB: We keep retrying the allocation for 60 seconds... 161026 0:53:31InnoDB: Fatal error: cannot allocate the memory for the buffer pool 161026 0:53:31 [ERROR] Plugin 'InnoDB' init function returned error. 161026 0:53:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 161026 0:53:31 [ERROR] Unknown/unsupported table type: INNODB 161026 0:53:31 [ERROR] Aborting I dont have 32 bit OS **but my MYSQL is 32 bit .** I have 64 bit OS on my Windows server. Memory space is limited to 2 GB or 4 GB. For 32 bit Mysql but I am not able to make it 2 GB, my Innodb is only 1.6 GB . Increasing size of Innodb is very necessary because i am getting following error in my error log : MySql.Data.MySqlClient.MySqlException: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug What I have Already Tried : 1. I confirmed that My my.ini has no issue . 2. I restrted My mysql many times but innodb size not increasing. At last I got from somewhere that Memory allocation to Mysql in my OS is very less thats why OS is not increasing its innodb size. 3. **I increased virtual memory of windows. It was 8GB before and I increased to to 16 GB. ( Did this change just now and dont know its result because it requires server restart and before 7 hours I cant restart my Server) - Do you think this can solve my problem ?** MySQL Version : 5.1 If anyone know the solution then let me know, i will be very thank ful to you.