Premium Crystal™ 25 Posted May 8, 2020 Premium Share Posted May 8, 2020 (edited) ERROR 1067 (42000) at line 243: Invalid default value for 'time' I get this error when i am trying to run a sql file. This is the line when it causes me the problem : `time` datetime Spoiler NOT NULL DEFAULT CURRENT_TIMESTAMP, When i delete the DEFAULT CURRENT_TIMESTAMP runs successfully. Does anyone knows how to fix it? Edited May 8, 2020 by Crystal™ Link to comment Share on other sites More sharing options...
Honorable Member Owsap 8175 Posted May 8, 2020 Honorable Member Share Posted May 8, 2020 (edited) If you're setting your default value as '0000-00-00 00:00:00' you might want to disable STRICT_TRANS_TABLES @ /usr/local/my.cnf Edited May 8, 2020 by Owsap 1 https://owsap.dev/ / https://osf.owsap.dev/ Link to comment Share on other sites More sharing options...
Premium Crystal™ 25 Posted May 8, 2020 Author Premium Share Posted May 8, 2020 13 minutes ago, Owsap said: If you're setting your default value as '0000-00-00 00:00:00' you might want to disable STRICT_TRANS_TABLES @ /usr/local/my.cnf This file does not exist /usr/local/my.cnf Link to comment Share on other sites More sharing options...
Honorable Member Owsap 8175 Posted May 8, 2020 Honorable Member Share Posted May 8, 2020 (edited) You can find the file location using ps -aux|grep mysql In my case it's located @ /usr/local/my.cnf Edited August 25, 2022 by Metin2 Dev Core X - External 2 Internal https://owsap.dev/ / https://osf.owsap.dev/ Link to comment Share on other sites More sharing options...
ridetpro 116 Posted May 8, 2020 Share Posted May 8, 2020 (edited) For mysql 5.7 + usr/local/etc/mysql/my.cnf Add: [mysqld] sql-mode = For mysql 5.6 - usr/local/my.cnf Change existing sql-mode to: [mysqld] sql-mode= In both cases leave sql mode empty, just with = Restart mysql server Then run mysql_upgrade Edited May 8, 2020 by ridetpro 1 Link to comment Share on other sites More sharing options...
Premium Crystal™ 25 Posted May 8, 2020 Author Premium Share Posted May 8, 2020 (edited) Still can't find my.cnf 1 hour ago, ridetpro said: For mysql 5.7 + usr/local/etc/mysql/my.cnf Add: [mysqld] sql-mode = For mysql 5.6 - usr/local/my.cnf Change existing sql-mode to: [mysqld] sql-mode= In both cases leave sql mode empty, just with = Restart mysql server Then run mysql_upgrade 1 hour ago, Owsap said: You can find the file location using ps -aux|grep mysql In my case it's located @ /usr/local/my.cnf ps -aux|grep mysql Spoiler mysql 676 0.0 0.1 5092 2212 - Is 17:38 0:00.00 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --d mysql 770 0.0 4.9 257880 100768 - I 17:38 0:13.29 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --da root 3374 0.0 0.1 4632 2128 0 I+ 12:23 0:00.00 grep mysql root 3562 0.0 0.1 4632 2124 1 S+ 13:46 0:00.00 grep mysql Edited August 25, 2022 by Metin2 Dev Core X - External 2 Internal Link to comment Share on other sites More sharing options...
Honorable Member Owsap 8175 Posted May 8, 2020 Honorable Member Share Posted May 8, 2020 (edited) Create a new file my.cnf in your /usr/locale # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION Edited May 8, 2020 by Owsap 1 1 https://owsap.dev/ / https://osf.owsap.dev/ Link to comment Share on other sites More sharing options...
Premium Crystal™ 25 Posted May 8, 2020 Author Premium Share Posted May 8, 2020 17 minutes ago, Owsap said: Create a new file my.cnf in your /usr/locale # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION same error Link to comment Share on other sites More sharing options...
Management AZICKO 7347 Posted May 8, 2020 Management Share Posted May 8, 2020 You can replace all 0000-00-00 00:00:00 with 2001-01-01 00:00:00 in your sql file Scamming ? Reselling ? metin2.download | metin2.dev | fr.metin2.dev | metin2dev.org | metin2.top | top-metin2.org Link to comment Share on other sites More sharing options...
Honorable Member Owsap 8175 Posted May 8, 2020 Honorable Member Share Posted May 8, 2020 (edited) Try to add ALLOW_INVALID_DATES in the my.cnf Like so, # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES Then restart your MySQL service mysql-server restart Once you haved restarted your MySQL run the query and confirm the variable is set. SHOW VARIABLES LIKE 'sql_mode' ; Now I think you can add '0000-00-00 00:00:00' to dates without any problem. Edited May 8, 2020 by Owsap 1 https://owsap.dev/ / https://osf.owsap.dev/ Link to comment Share on other sites More sharing options...
Premium Crystal™ 25 Posted May 8, 2020 Author Premium Share Posted May 8, 2020 2 minutes ago, Owsap said: Try to add ALLOW_INVALID_DATES in the my.cnf Like so, # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES Then restart your MySQL service mysql-server restart service mysql-server restart Once you haved restarted your MySQL run the query and confirm the variable is set. SHOW VARIABLES LIKE 'sql_mode' ; SHOW VARIABLES LIKE 'sql_mode' ; Now I think you can add '0000-00-00 00:00:00' to dates without any problem. I restarted mysql before,shouldi add this in my.cnf -> SHOW VARIABLES LIKE 'sql_mode' ; ? Link to comment Share on other sites More sharing options...
Honorable Member Owsap 8175 Posted May 8, 2020 Honorable Member Share Posted May 8, 2020 1 minute ago, Crystal™ said: I restarted mysql before,shouldi add this in my.cnf -> SHOW VARIABLES LIKE 'sql_mode' ; ? No, that's a query you can run in Navicat, for example which you can see the variables of the sql_mode, it's just so you can confirm they were set. https://owsap.dev/ / https://osf.owsap.dev/ Link to comment Share on other sites More sharing options...
ridetpro 116 Posted May 8, 2020 Share Posted May 8, 2020 (edited) I told you what to do, if you are not able to do it just ask your host company to do it. Just read over and over my message... If you have 5.6 and there's no my.cnf then creare a black my.cnf in usr/locale/ Mysql 5.6 /usr/local/ [mysqld] sql_mode= mysql 5.7, edit the existing on usr/local/etc/mysql/ [mysqld] sql-mode= Be careful at underscores AND RESET MYSQL SERVER Edited May 8, 2020 by ridetpro Link to comment Share on other sites More sharing options...
Honorable Member martysama0134 7178 Posted May 8, 2020 Honorable Member Share Posted May 8, 2020 (edited) 3 hours ago, Crystal™ said: DEFAULT CURRENT_TIMESTAMP I added this in those field because the old "0000/00/00 00:00" date could not be accepted in new mysql versions (no valid date). DEFAULT CURRENT_TIMESTAMP is not supported by mysql 5.5, but since 5.6. It returns NOW(). Edited May 8, 2020 by martysama0134 1 Check out my GitHub Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now