Category: MySQL

MySQL Error, “java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp” on ColdFusion 11

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 …

Continue reading

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/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/09/mysql-zerofill-and-lpad-shape-digits-in-db/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/08/best-way-to-order-the-best-matching-records-in-mysql/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/07/how-to-use-mysql-for-case-sensitive-string-comparison/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/06/phpmyadmin-cannot-start-session-without-errors-please-check-errors/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/06/mysql-query-cache/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/11/how-to-use-serialized-object-in-active-record-using-rails/

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()-&gt;params[‘ftpSource’]; $ftpUserName = Yii::app()-&gt;params[‘ftpUserName’]; $ftpPassword = Yii::app()-&gt;params[‘ftpPassword’]; <em><strong>// set …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/10/yii-upload-the-file-to-ftp-server/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/10/how-to-retrieve-the-last-record-in-each-group-using-mysql/

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 …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/09/how-to-reset-a-magento-password-using-mysql-server/