i can UPDATE on mysql database but not INSERT
-
robinsouthchina
- Posts: 11
- Joined: Wed Apr 12, 2017 5:02 am
i can UPDATE on mysql database but not INSERT
Hi,
The control panel is installed and working just fine for creating domains and with the SSL certificates as well. I have been using vesta for about 1.5 years with very little problem.
However, I just installed a new instance of vesta yesterday and now I can not insert new records into my mysql database for a website I have. I can update records like name, age, height etc etc .. but i can't create new users for this app because the mysql INSERT command does not seem to work in my PHP script. I logged into mysql as "admin_default" and did this:
*************************************
mysql> SHOW GRANTS;
+--------------------------------------------------------------------------+
| Grants for admin_default@localhost |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin_default'@'localhost' |
| GRANT ALL PRIVILEGES ON `admin_default`.* TO 'admin_default'@'localhost' |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
**************************
and so it looks like that database user (the one in my mysqli object) has enough permissions. Could someone please help?
The control panel is installed and working just fine for creating domains and with the SSL certificates as well. I have been using vesta for about 1.5 years with very little problem.
However, I just installed a new instance of vesta yesterday and now I can not insert new records into my mysql database for a website I have. I can update records like name, age, height etc etc .. but i can't create new users for this app because the mysql INSERT command does not seem to work in my PHP script. I logged into mysql as "admin_default" and did this:
*************************************
mysql> SHOW GRANTS;
+--------------------------------------------------------------------------+
| Grants for admin_default@localhost |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin_default'@'localhost' |
| GRANT ALL PRIVILEGES ON `admin_default`.* TO 'admin_default'@'localhost' |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
**************************
and so it looks like that database user (the one in my mysqli object) has enough permissions. Could someone please help?
-
robinsouthchina
- Posts: 11
- Joined: Wed Apr 12, 2017 5:02 am
Re: i can UPDATE on mysql database but not INSERT
FIXED MY OWN PROBLEM:
This is caused by the STRICT_TRANS_TABLES SQL mode.
Open phpmyadmin and goto More Tab and select Variables submenu. Scroll down to find sql mode. Edit sql mode and remove STRICT_TRANS_TABLES Save it.
My local phpmyadmin settings were different than the settings for phpmyadmin up at my new server. ... Note that to made the configuration changes above I needed to login as root at phpmyadmin ...
It seems that now this STRICT_TRANS_TABLES setting exists by default and you have to remove it. The mysql/phpmyadmin that previously was installed by default with vesta was different. So when doing INSERT statements you would have to be a bit more careful. I don't want it so i got rid of it.
This is caused by the STRICT_TRANS_TABLES SQL mode.
Open phpmyadmin and goto More Tab and select Variables submenu. Scroll down to find sql mode. Edit sql mode and remove STRICT_TRANS_TABLES Save it.
My local phpmyadmin settings were different than the settings for phpmyadmin up at my new server. ... Note that to made the configuration changes above I needed to login as root at phpmyadmin ...
It seems that now this STRICT_TRANS_TABLES setting exists by default and you have to remove it. The mysql/phpmyadmin that previously was installed by default with vesta was different. So when doing INSERT statements you would have to be a bit more careful. I don't want it so i got rid of it.