Issue: Queries to MySQL may return the error, “java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp” or similar. This error appears when using the Adobe ColdFusion MySQL 5 JDBC driver. Reason: This error occurs when the MySQL Date or DateTime columns have a value of all zeros (for example, ‘0000-00-00 00:00:00’). The default …
Category: MySQL
Permanent link to this article: https://blog.openshell.in/2014/11/mysql-error-java-sql-sqlexception-value-0000-00-00-000000-can-not-be-represented-as-java-sql-timestamp-on-coldfusion-11/
Sep 10
MySQL zerofill and lpad – Shape digits in DB
I’m working with an application to have the item code. Item code column is set as an INT(5) but not all the item code values are 5 digits. So I need to round off the item code based on 5-digits. If the item code is 199, it should be display as 00199. How to make …
Permanent link to this article: https://blog.openshell.in/2014/09/mysql-zerofill-and-lpad-shape-digits-in-db/
Aug 19
Best way to order the best matching records in MySQL
If we are searching a word (string) in the mysql table, usually we will use the below method: [sql] SELECT * FROM user WHERE name LIKE ‘%searchstring%’ ORDER BY name ASC; [/sql] If you search like this, It will order the result set based on the ASC of name. It will not order the result …
Permanent link to this article: https://blog.openshell.in/2014/08/best-way-to-order-the-best-matching-records-in-mysql/
Jul 18
How to use MySQL for case sensitive string comparison
By default MYSQL character set and collation are latin1 and latin1_swedish_ci, so non-binary string comparisons are case insensitive. This means that if you search with col_name LIKE ‘a%’, you get all column values that start with A or a. To make this search as case sensitive, make sure that one of the operands has a …
Permanent link to this article: https://blog.openshell.in/2014/07/how-to-use-mysql-for-case-sensitive-string-comparison/
Jun 21
phpmyadmin – Cannot start session without errors, please check errors
Unfortunately sometimes we get the problem with phpMyAdmin, here is the error from phpMyAdmin on ubuntu machine. phpMyAdmin – Error [code] Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly. [/code] To find out where the session.save path http://wiki.phpmyadmin.net/pma/session.save_path run this script …
Permanent link to this article: https://blog.openshell.in/2014/06/phpmyadmin-cannot-start-session-without-errors-please-check-errors/
Jun 11
Mysql Query Cache
As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query. To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file …
Permanent link to this article: https://blog.openshell.in/2014/06/mysql-query-cache/
Nov 14
How to use serialized object in Active Record using Rails?
Serializing Object Will help us to store multiple column values in single Database column and even making processing using that column simple. It helps to extended the table without adding new column in table. Because we can store multiple column values in single column as Hash value. So, It reduce the difficulty of retriving and …
Permanent link to this article: https://blog.openshell.in/2013/11/how-to-use-serialized-object-in-active-record-using-rails/
Oct 21
YII Upload the file to FTP server
The below function used to upload the local file to remote directory. [php] /** * Upload the file in ftp directory * @param string $localFilePath * @param string $remoteFilePath . * @return boolean */ public function CopyFileToFtpBucketDir($localFilePath, $remoteFilePath) { <strong><em>//Credential are configured in main.php</em></strong> $ftpSource = Yii::app()->params[‘ftpSource’]; $ftpUserName = Yii::app()->params[‘ftpUserName’]; $ftpPassword = Yii::app()->params[‘ftpPassword’]; <em><strong>// set …
Permanent link to this article: https://blog.openshell.in/2013/10/yii-upload-the-file-to-ftp-server/
Oct 18
How to retrieve the last record in each group using mysql
In single SQL query itself we can retrieve the last record in each group. It will help us to perform action better, faster and simpler. In this post I will explain about you how to do it. Here is my table structure for your reference. [sql] desc post_status; +—————-+————-+——+—–+———+—————-+ | Field | Type | Null …
Permanent link to this article: https://blog.openshell.in/2013/10/how-to-retrieve-the-last-record-in-each-group-using-mysql/
Sep 06
How to reset a Magento password using Mysql Server
If you are unable to log in to your magento application? Follow the below steps to reset your magento store password from Mysql server. Step1: Select your database from the Mysqlserver. Step2: Find admin_user table from the selected database. Step3: In that table, edit the user which you want to reset their admin password. Step4: For security …
Permanent link to this article: https://blog.openshell.in/2013/09/how-to-reset-a-magento-password-using-mysql-server/