windows 7 32bits pt-br

Hi Roberto,

Please try to do the same with other tables in information schema – it's about the first problem you reported.

Please do not report several unrelated problems in one bug report, only one (at best) will be addressed.

Please do not raise JIRA issues for questions that are neither bugs or tasks. There are a mailing list and IRC for that.

Thanks.

same problem (/* Erro SQL (6): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_6_1a40.MAD' (Errcode: 13) */) with:
QUERY_CACHE_INFORMATION
STATISTICS
EVENTS
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
REFERENTIAL_CONSTRAINTS
ROUTINES
TABLES
TABLE_CONSTRAINTS
TRIGGERS

i'm running the heidisql in the same machine of windows mariadb 5.5.31, ~10-20 seconds of refresh per table

i tested all tables of information_schema:
CHARACTER_SETS
CLIENT_STATISTICS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
INDEX_STATISTICS
KEY_CACHES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
USER_PRIVILEGES
USER_STATISTICS
VIEWS
INNODB_CMPMEM_RESET
INNODB_RSEG
INNODB_UNDO_LOGS
INNODB_CMPMEM
INNODB_SYS_TABLESTATS
INNODB_LOCK_WAITS
INNODB_INDEX_STATS
INNODB_CMP
INNODB_CMP_RESET
INNODB_CHANGED_PAGES
INNODB_BUFFER_POOL_PAGES
INNODB_TRX
INNODB_BUFFER_POOL_PAGES_INDEX
INNODB_LOCKS
INNODB_BUFFER_POOL_PAGES_BLOB
INNODB_SYS_TABLES
INNODB_SYS_FIELDS
INNODB_SYS_COLUMNS
QUERY_CACHE_INFO
INNODB_SYS_STATS
INNODB_SYS_INDEXES
XTRADB_ADMIN_COMMAND
INNODB_TABLE_STATS
INNODB_SYS_FOREIGN_COLS
INNODB_BUFFER_PAGE_LRU
INNODB_BUFFER_POOL_STATS
INNODB_BUFFER_PAGE
INNODB_SYS_FOREIGN

maybe it happen with remote host too , i'm testing in "10.0.0-MariaDB-log" (linux) over tcp/ip internet

select * from EVENTS ;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,062 sec. */
select * from EVENTS ;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,062 sec. */
select * from EVENTS ;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,062 sec. */
select * from EVENTS ;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,062 sec. */
select * from EVENTS ;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,062 sec. */
select * from EVENTS ;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,062 sec. */
select * from EVENTS ;
/* Conexão com <host> fechada em 2013-05-24 17:40:12 */
/* Conectando em <host> via MariaDB (TCP/IP), usuário <user>, usando a senha: Yes... */
/* Erro SQL (2006): MySQL server has gone away */
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 0 of 1 query: 0,000 sec. */

at database error log have no information about this problem (not changed), and there's no temporary file in tempdir

----------
running in command line of server (linux) i got no connection lost, and no error, maybe it's a problem of windows temporary file ?!

-------
at windows with mysql command line tool, i have the problem really fast:
use information_schema;

MariaDB [information_Schema]> select * from TABLE_CONSTRAINTS limit 1;
-------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
MariaDB [information_Schema]> select * from TABLE_CONSTRAINTS limit 1;
ERROR 6 (HY000): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_8_1d0.MAD' (Errcode: 13)
MariaDB [information_Schema]> select * from TABLE_CONSTRAINTS limit 1;
ERROR 6 (HY000): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_8_20f.MAD' (Errcode: 13)
MariaDB [information_Schema]> select * from TABLE_CONSTRAINTS limit 1;

tested via internet tcp/ip to linux server running mariadb 10.0.0 and command line tool, and i have no errors (connect drop), maybe a bug in heidisql (very frequent)

well i think it's a specific windows temporary file problem, if this help
i tested via command line with a long 250 commands like :
select * from TABLE_CONSTRAINTS limit 1;select * from TABLE_CONSTRAINTS limit 1;select * from TABLE_CONSTRAINTS limit 1; ...... select * from TABLE_CONSTRAINTS limit 1;
in only one line

results: linux = no problem with temporary file deletion, windows = problem with temporary files

Hi Roberto,

I suppose you could easily exclude HeidiSQL from the scenario by replacing it by a script or a tiny program which sends the same queries to the server directly.

Meanwhile, let me try to summarize your findings and see if I understand them correctly.

• you perform a GUI-based stress test (press and hold a button which causes a query to be executed over and over again) with a scenario that's inapplicable to real life (I don't see how pressing and holding F5 button makes any sense on a production system);
• doing so, you apparently hit some OS limitation;
• the limitation appears in the form of error messages, no data corruption or server/client crash occurs.

Is this all correct? If so, could you please explain what kind of outcome you would expect instead, given the circumstances?

From my point of view, tests like that are totally valid, but they are normally performed to reveal vulnerabilities and points of critical failures which a user can exploit, either intentionally or by mistake. Those are security problems, data corruption, server DoS and, to some extent, client DoS, although the latter is arguable. Getting an error message upon such a scenario seems to be a reasonable result. Do you disagree?

Thanks.

hi elena, yes, the stress test only occur with windows 7 32-bit, mariadb-5.5.31 msi binary (in only tested this version), on linux it runs normally
yes i agree with you
but could a "broken" temporary file function implementation have implications with others queries/tables outside of information_schema?
in linux it's not a problem... just on windows

on php with windows some comments at unlink function show that it happen on windows when delete it without fclose file before unlink, on linux there's no problem

don't work:
f=fopen(tempfile)
unlink(tempfile);
fclose(f);

works:
instead of
f=fopen(tempfile)
unlink(tempfile);
fclose(f);

since it works some times, and sometimes don't, maybe we could retry the unlink on windows if we got a permission error and file still in filesystem? it's a problem since we "lost" connection with database and must retry, ok application can deal with it, but, could we workaround it and make mariadb runs on windows like it runs on linux?

Hi Roberto,

Your "don't work" and "works" fragments look identical to me. Could you please clarify?

>> ok application can deal with it, but, could we workaround it and make mariadb runs on windows like it runs on linux?

Possibly, but is it worth the trouble? What is the actual use case that such application tries to implement by bombing the server with I_S queries many times per second, that would be so important that it needs a workaround once it (presumingly) hits an OS-specific limitation and receives an error message?

Are you willing to provide a patch for such a workaround?

>> but could a "broken" temporary file function implementation have implications with others queries/tables outside of information_schema?

Possibly. Did you encounter any such problems outside of information_schema?

the work / don't work is an example in php, fclose before unlink "remove" the permission problem of windows system using temporary files, fclose after unlink (delete) may cause erros like this one in I_S

i didn't found a problem outside I_S yet, where i can found from what file and line (or function) the error was generated? there's many deletes in source and i don't know where to find

hi, all tests pointed that only i_s have this problem and only windows version
the main problem is temporary files still forever in filesystem
i can onlye remove the files from filesystem outside of database (in windows), should be nice retry while file isn't removed in mysql server to don't have problem with temporary files

Could you please follow the instructions outlined here https://kb.askmonty.org/en/how-to-use-procmon-to-trace-mysqldexe-filesystem-access/ to trace mysqld.exe file system activity, and send the resulting log ?

Roberto ,can you please provide logs, as discussed above?

ok, i will update to mariadb 10.0.3 with debug
set the debug_dbug to output trace and every thing else, and send all logs
ok?

I'm not sure it will deliver good info . Personally, I would actually prefer the above mentioned https://kb.askmonty.org/en/how-to-use-procmon-to-trace-mysqldexe-filesystem-access/ , however if you think DBUG is is easier for you, we can try that too.

tell me what you need and i try to do my best =]
i will read this link and maybe report both here, just some minutes it's 13:00 here in brazil i will eat something

It would be great, If you could follow the section "(Advanced) Seeing stack traces corresponding to events", https://kb.askmonty.org/en/how-to-use-procmon-to-trace-mysqldexe-filesystem-access/ , (add also add directory where mysqld.exe is located to the symbols PATH)

After you can reproduce the error with procmon on, save procmon output using "save the file as XML". compress, attach to the report.

Thank you!

ok =) with time i will read and execute that, i'm without time now, maybe at weekend i do it, ok?

yes, sure, take your time.

xml file have 125MB uncompressed! i will attach it

xml file of procmon when delete couldn't be done in temporary file

i didn't read the mariadb code, but i think that this site is interesting:
http://stackoverflow.com/questions/14230886/using-temporary-files-safely

and this too
http://stackoverflow.com/questions/3764072/c-win32-how-to-wait-for-a-pending-delete-to-complete

reading the procmon when file is created:
Desired Access: Generic Read/Write
Disposition: OverwriteIf
Options: Synchronous IO Non-Alert, Non-Directory File
Attributes: N
ShareMode: Read, Write, Delete
AllocationSize: 0
OpenResult: Created

i don't see the temporary file flag, maybe it's the wrong part?

reading this:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/04/21/unexplained-temp-table-errors.aspx

i was thinking that kaspersky antivirus was blocking mariadb, but...
i retry the test with antivirus disabled, and i got the same problem, but with less frequency (maybe the antivirus was part of the problem?)

i'm uninstalling kaspersky now to redo the test, just some moment to really know if antivirus is or isn't the main problem

well antivirus fully removed and after 100000 select * from query_cache_info i got no erros
summary is the same of http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/04/21/unexplained-temp-table-errors.aspx

the antivirus was blocking the delete temporary file of mariadb

maybe we should add some more comment in error? for example, when running windows instead of:
"Error" "6" "Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_7_1a6.MAD' (Errcode: 13)"
output
"Error" "6" "Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_7_1a6.MAD' (Errcode: 13), Windows users consider disable antivirus"

or maybe found a nice solution to run delete with antivirus

or maybe in the last file open we put a attribute to delete on close ? i think it's the best solution
since the main problem isn't the permission denied, but the temporary file still in my temporary directory and maybe, be used twice (could this occur?), or make a disk full problem in my temporary patch

now reading http://msdn.microsoft.com/en-us/library/aa363915%28v=VS.85%29.aspx
Remarks

If an application attempts to delete a file that does not exist, the DeleteFile function fails with ERROR_FILE_NOT_FOUND. If the file is a read-only file, the function fails with ERROR_ACCESS_DENIED.

i think that on close antivirus change file to read_only, and don't allow a delete, just a sugestion... maybe a FILE_FLAG_DELETE_ON_CLOSE on last 'fopen' is the best solution, or maybe after a access_denied we reopen the file with FILE_FLAG_DELETE_ON_CLOSE and close to 'force' a close/delete

well for me this MDEV-4576 could be closed but a remark in documentation could be very helpfull
WINDOWS USERS: DISABLE ANTIVIRUS FOR MYSQLD PROCESS
with very big font, and maybe include this information in DOWNLOAD page too, since many users don't read documentation...

attached procmon with antivirus process being included
8MB of XML file

check that in the last file close, the antivirus changed file to read_only and after that mysqld try to delete it
i think it's something related to delete file XXX, instead of fopen(file xxxx), mark file to be deleted on close, fclose, maybe i'm wrong and maybe the best solution is before last close mark file to be delete, and don't execute the delete file xxx

again just an idea... i don't know how internally it's done, if table cache can open and close the file and do something that i couldn't explain

another information... i was running with skip_external_locking = ON

i tried again with skip_external_locking= OFF, and same error (antivirus still blocking delete)

antivirus = on
skip_external_locking = off

again can't delete
i'm sure that antivirus is the main problem

sorry, i'm an user that didn't read the mysql docs
http://dev.mysql.com/doc/refman/5.1/en/windows-installation.html
but... could we allow or change the delete? it's a problem for me with many temporary tables in temporary directory consuming filesystem space.
ok removing antivirus solve the problem, but could we put this solution without considering remove the antivirus check? maybe a retry for 1 second, with sleep times of 10ms (100 tries to delete temporary file)

MySQL and Virus Checking Software

Using virus scanning software such as Norton/Symantec Anti-Virus on directories containing MySQL data and temporary tables can cause issues, both in terms of the performance of MySQL and the virus-scanning software mis-identifying the contents of the files as containing spam. This is because of the fingerprinting mechanism used by the virus scanning software, and the way in which MySQL rapidly updates different files, which may be identified as a potential security risk.

After installing MySQL Server, it is recommended that you disable virus scanning on the main directory (datadir) being used to store your MySQL table data. There is usually a system built into the virus scanning software to permit certain directories to be specifically ignored during virus scanning.

In addition, by default, MySQL creates temporary files in the standard Windows temporary directory. To prevent the temporary files also being scanned, you should configure a separate temporary directory for MySQL temporary files and add this to the virus scanning exclusion list. To do this, add a configuration option for the tmpdir parameter to your my.ini configuration file. For more information, see Section 2.3.6.2, “Creating an Option File”.

Would you mind checking a mysqld.zip attached? It corresponds to the http://lists.askmonty.org/pipermail/commits/2013-June/004816.html patch . You'd need to unpack mysqld.exe and replace the one that came with installation with the one from zip (the same with mysqld.pdb). What MySQL docs say is fine, but I think we can at least try to improve what we can improve

sure, some minutes and i get it

well, i don't have visual studio here, do you have the executable file?

sorry founded in mdev

hummm i got a error about share folder
C:\Program Files\MariaDB 10.0\bin>mysqld --port=3306 --data_dir=..\data1\ --log_path=..\log\
130615 13:25:50 [ERROR] Incompatible header in messagefile 'C:\Program Files\MariaDB 10.0\share\english\errmsg.sys'. Probably from another version of MariaDB
130615 13:25:50 [ERROR] Aborting

130615 13:25:50 [Note]

could you add the errmsg.sys of your machine?

i'm with mariadb 10.0.3 here, maybe the patch is for 5.5.???

i'm with i5 and windows 7 32bits here
your executable is amd64
C:\Program Files\MariaDB 5.5\bin>mysqld --version
mysqld Ver 5.5.31-MariaDB for Win32 on AMD64 (Source distribution)

C:\Program Files\MariaDB 5.5\bin>mysqld-old --version
mysqld-old Ver 5.5.31-MariaDB for Win32 on x86 (mariadb.org binary distribution)

sorry my error again data_dir instead of datadir

i will do tests now

Same problem using your mysqld.exe file

select * from information_schema.QUERY_CACHE_INFO;
/* Erro SQL (6): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sql1f10_1_2ba.MAI' (Errcode: 5) */
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 0 of 1.980 queries: 0,000 sec. */

proc mon file attached

i opened a kaspersky issue to help too
i don't know how it works but in windows i think that if you have a file opened (var=fopen(some file)) you can use the var to set a flag to delete on close, instead of close and after a delete
i don't know if it's a very big workaround or not but i seens to be something interesting, since we don't close the file, just mark it to be deleted. i don't know if a read_only flag don't allow to add flag delete on close.. but it's could be tested...
the other workaround that i see is a
i=0;
do

while(i<100)

or.. instead of a total of 1 second, get a total of (time to write temporary file * 2)

or (bigger workaround, but nice solution)
create a warning (show warnings) ("can't delete temporary file xxxx, file will be deleted by delete thread") or something like it
create a background thread and send temporary files that we can't delete to that thread, and it will delete when possible, if the 'delete temporary' list is very big send this to a log file and clean the list

Hi,
I attached mysqld-5.5.zip made from new patch. This should be reasonably safe, clean and simple (no background threads, heh) solution for the problem in hand. I would appreciate if you could find some time to test it. Thank you!

The corresponding patch is http://lists.askmonty.org/pipermail/commits/2013-June/004817.html

sure

downloaded, i will start tests

2640 queries and no error....
i will test more times and send a procmon to ensure that mdev was solved =]

procmon xml file, used 7z compression with ultra, since zip was 50MB and couldn't be uploaded (max of 10MB in jira)
the xml have 850MB
the patch worked and no error was sent to mysql client, temporary files are deleted from temporary path
now everything is ok =) very nice job vladislav! thanks!

check that 10.0.3 have the same problem

fixed in 5.5 (all fixes are merged up to higher version, so it will be in 10.0 too)