Wednesday, January 22, 2020

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled


When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

For example, the following function is not safe

CREATE FUNCTION generatenumber(fid INT(10))
RETURNS INT
BEGIN
 UPDATE xnumber
   SET count = (@cur_value := count+ 1)
   WHERE id=fid;
   return @cur_value;
 
END;

When you attempt to execute a stored function, if binlog_format=STATEMENT is set, the DETERMINISTIC keyword must be specified in the function definition. If this is not the case, an error is generated and the function does not run, unless log_bin_trust_function_creators=1 is specified to override this check

To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:

SET GLOBAL log_bin_trust_function_creators = 1;


Tuesday, January 21, 2020

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column '' at row 1


We encounter following  error when we moved our application from existing environment to new environment

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column ' at row 1

This error is due to our application NO_ZERO_IN_DATE mode and NO_ZERO_DATE is enabled

This mode affects whether the server permits dates in which the year part is nonzero, but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.) This also depends on whether strict SQL mode is enabled.
  •      If this mode is not enabled, dates with zero parts are permitted and inserts produce no warning.
  •          If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.
  •         If this mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.


This mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.
  • If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.
  • If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.
  • If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

Please refer details of this options in https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Solution
Change the sql_mode to allow zero dates, by removing NO_ZERO_DATE and NO_ZERO_IN_DATE. The change can be applied in the my.cnf file, so after a restart of MySQL Server, sql_mode variable will be initialized to the setting in my.cnf.
1.       Change Via PhpMyAdmin
Login to PhpMyAdmin and go to Variables and Sql_mode. Copy the existing value remove NO_ZERO_DATE and NO_ZERO_IN_DATE and apply

2.       Via Command

  • Get sql_mode : SHOW VARIABLES LIKE 'sql_mode';
  •  Result may show as follows
      ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

  •   Remove on the result : NO_ZERO_IN_DATE,NO_ZERO_DATE
  •  Set new configuration : SET GLOBAL sql_mode =’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ‘


Monday, January 20, 2020

Getting disk usage and folder usage Linux


Disk Usage
df (disk filesystem)  command will help us to know the disk usage in linux.  df command will display result in the following format





Filesystem           - List filesystem
1K-blocks             -  Total size of the file system
Used                      - used space
Available              - available space
Use%                     - percentage used.
Mounted on       - mounded as

df  command syntax is as follows
df  [options] [file]
The available options for df
Options
Description
-a, --all
include dummy file systems
-B, --block-size=SIZE

         
scale sizes by SIZE before printing them; e.g., '-BM' prints sizes in units of 1,048,576 bytes; see SIZE format below

--direct

show statistics for a file instead of mount point
produce a grand total
--total

-h, --human-readable
print sizes in human readable format (e.g., 1K 234M 2G)
-H, --si
likewise, but use powers of 1000 not 1024
-i, --inodes         
list inode information instead of block usage
-k                    
like --block-size=1K
-l, --local
--no-sync
      --output[=FIELD_LIST]

limit listing to local file systems
do not invoke sync before getting usage info (default)

use the output format defined by FIELD_LIST or print all fields if FIELD_LIST is omitted.

  -P, --portability
--sync
use the POSIX output format
invoke sync before getting usage info

  -t, --type=TYPE
limit listing to file systems of type TYPE
-T, --print-type
print file system type
  -x, --exclude-type=TYPE
   limit listing to file systems not of type TYPE

  --version                  
version information
--help    
      display this help and exit


Folder usage

du Command display the amount of disk used by the specific files or directory.
This command helps us to know the disk usage for just directories/All files, showing grand total.
For example following command
$ du -sh /var
This will display  as the option given -s( display only total) h(human  readable format) the result is as follows
17G     /var

Options are follows
Options
Description
-0, --null
end each output line with 0 byte rather than newline
-a, --all
--apparent-size
write counts for all files, not just directories
print apparent sizes, rather than disk usage; although the apparent size is usually smaller, it may be larger due to holes in ('sparse') files, internal fragmentation, indirect blocks, and the like

-B, --block-size=SIZE 
scale sizes by SIZE before printing them; e.g.,'-BM' prints sizes in units of 1,048,576 bytes;
SIZE is an integer and optional unit (example: 10M is 10*1024*1024).  Units are K, M, G, T, P, E, Z, Y (powers of 1024) or KB, MB, ... (powers of 1000)

  -b, --bytes
 equivalent to '--apparent-size --block-size=1'
-c, --total
  produce a grand total
-D, --dereference-args
dereference only symlinks that are listed on the command line
-d, --max-depth=N


--files0-from=F
print the total for a directory (or file, with --all)  only if it is N or fewer levels below the command line argument;  --max-depth=0 is the same as –summarize
summarize disk usage of the NUL-terminated file names specified in file F; if F is -, then read names from standard input
-H
equivalent to --dereference-args (-D)
-h, --human-readable
--inodes
print sizes in human readable format (e.g., 1K 234M 2G)

list inode usage information instead of block
-k
like --block-size=1K
-L, --dereference
dereference all symbolic links
-l, --count-links
count sizes many times if hard linked
-m
like --block-size=1M
-P, --no-dereference
don't follow any symbolic links (this is the default)
-S, --separate-dirs
--si
for directories do not include size of subdirectories
like -h, but use powers of 1000 not 1024
-s, --summarize
display only a total for each argument
-t, --threshold=SIZE

--time

--time=WORD

--time-style=STYLE

exclude entries smaller than SIZE if positive, or entries greater than SIZE if negative
show time of the last modification of any file in the directory, or any of its subdirectories
show time as WORD instead of modification time: atime, access, use, ctime or status
show times using STYLE, which can be: full-iso, long-iso, iso, or +FORMAT; FORMAT is interpreted like in 'date'
-X, --exclude-from=FILE
--exclude=PATTERN
exclude files that match any pattern in FILE

exclude files that match PATTERN
-x, --one-file-system
skip directories on different file systems
--help
display this help and exit
--version
output version information and exit