When trying to import large SQL dump, you often get the above message. Remedy for this issue is to increase the packet size of MySQL’s daemon by setting a packet size of max_allowed_packet. Sudo to your account and set the following
root $ mysql -u admin -p
mysql> set global net_buffer_length=1000000;
mysql> set global max_allowed_packet=1000000000;
In the command line, issue the command
$ mysql –max_allowed_packet=128M -uUSER -pPASSWORD database < database.sql.dump
After VPS reboot, you may encounter this issue and the fix is simple. Follow these stops to prevent this error.
$ service mysqld stop
$ mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak
$ service mysqld start
To find the collation of tables within MySQL database, you can use one of the commands listed below
mysql> show table status from exampledb;
mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=’exampledb’;
To find the character set used in a MySQL table
mysql> SELECT tbl.table_name, CCSA.character_set_name FROM information_schema.`TABLES` tbl, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = tbl.table_collation AND tbl.table_schema = “schemaname”;
To find the collation of a database
mysql> SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ‘exampledb’ LIMIT 1;
| DEFAULT_COLLATION_NAME |
| utf8_general_ci |
To find the characterset of a MySQL database
mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = “exampledb”;
Apache server is installed by default on Mac. Access the configuration settings for Apache by going to
$ cd /etc/apache2/
$ vi httpd.conf
To enable php, edit httpd.conf and uncomment the line
#LoadModule php5_module libexec/apache2/libphp5.so
Uncomment other lines that you may want to load in your system. As you are enabling PHP in your system, you may want to change the DirectoryIndex to
DirectoryIndex index.php index.html
Default group/user settings inside Apache on Mac is _www:_www
The reason why this message is shown is because the user for the view does not exist in the database.
There are few ways by which we can remove this error
i) Alter statement
ii) Create the specified user in the database
iii) Modify the .frm files related to the view and set the definer
We will show the Alter statement method to achieve the desired results
# To fix it:
mysql> ALTER DEFINER = ‘root’@’localhost’ VIEW `mytableview` AS select * from cities;
mysql> SELECT * FROM mytableview;
Sometimes it so happens that the view statement will be a big selection of fields with joins in it. Export the table first to get the required SELECT statement for the view and then change the DEFINER
When we began creating an application using Struts2 framework with Hibernate and MySQL, we ran into a problem which was related to MySQL timing out its connection after a span of 8 hours when left unused. It conceived a lot of time at our end but we were able to nail down the issue at the end.
Steps that we had taken to correct the issue related to Hibernate MySql connection timeout problem are listed below
– Download Hibernate C3P0 and copy .jar files
– Set c3p0.properties
– Make changes to hibernate.cfg.xml
– Test MySQL connection timeout
– Hibernate, MySQL connection timeout related error messages
List the listening ports and check if mysql is listed on it.
$ netstat -tln | grep mysql
If you cannot find the port number for mysql view my.cnf file to see if you can find the port number. If you see a line “skip-networking” try commenting out the line and then restart mysql daemon.
$ vi /etc/my.cnf[mysqld] set-variable=local-infile=0
$ service mysqld restart
Whitelist validation refers to data validation techniques such as checking the data type, data length, input range, nature of data by its format (for e.g. phone number will be ten digits separated by hyphens satisfying the format nnn-nnn-nnnn). Regular expressions may be used for format validation in inputs.
Blacklist validation refers to rejecting data based on a know bad list filter. This is not a powerful solution as the list of possible exclusions will be many and it is difficult to implement all possible scenarios. Blacklist validation should be used in conjunction with whitelist validation but in cases where whitelist validation cannot be applied at least blacklist validation should be implemented.
Dynamic SQL (concatenated sql string) is a coding practice where by the queries are built in the program and sent to SQL Server for execution. This can allow the code to be injected into the dynamic queries causing a damage to the database.
A good alternative is to use parameterised queries where placeholders are set for the variables. The possibility of queries getting infected with injected code is completely removed with parameterized queries.
In addition to parameterized queries, it is alway a best practice to sanitize the input parameters before using them in queries.
Also the data input should be encoded appropriately especially in case of dynamic sql usage and to apply appropriate encoding when extracted from the database to avoid cross-site script execution.