Database

How to reset MySQL root password

Categories: Database; Tagged with: MySQL; @ May 1st, 2016 15:27

I lost root’s password, again.

stop MySQL

we need to stop mysql and start it in safe mode. I tried many ways to kill mysql, but only this works for my Ubuntu 14.04:

root@myhost:/usr/bin# initctl --system stop mysql

start MySQL in safe mode, set new password for root

sudo mysqld_safe --skip-grant-tables &
mysql -uroot
use mysql;
update user set password=PASSWORD("new_password") where User='root';
flush privileges;
quit
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

you may try to login using the new password now:

mysql -u root -p

store/update MySQL password in ~/.my.cnf

if you’re using a password file, remember to update it.

[client]
user=root 
password=new_password

useful links
http://stackoverflow.com/questions/18733944/ubuntu-cant-stop-mysqld
https://www.digitalocean.com/community/questions/setup-mysql-on-ubuntu-droplet-getting-error-error-1045-28000-access-denied-for-user-root-localhost-using-password-yes

Sybase Convert between Number and Varchar

Categories: Database December 22nd, 2014 22:37

SELECT (CONVERT(NUMERIC(20, 0), '100') +  1)
>> 101
  
  
SELECT (CONVERT(VARCHAR(3), 100) +  'ABC')
>> 100ABC

MarkLogic: select all collections & select latest record from collection

Categories: Database July 1st, 2014 22:42

List all collections:

<collections>{
for $collection in cts:collections()
return <collection> { $collection } </collection>
}</collections>

Select latest record from collection:

let $uris := cts:uris(     (),     (),     cts:properties-query(         cts:and-query((
             cts:collection-query('/collections/coll-A'),
             cts:collection-query('latest')
        ))
     )
)

return
doc($uris)

Sybase full outer join

Categories: Database; Tagged with: Sybase; @ May 15th, 2014 22:56

Sybase doesn’t have this feature, however we can use lift/right join + union to get it:

SELECT * FROM A LEFT OUTER JOIN B ON…

UNION

SELECT * FROM A RIGHT OUTER JOIN B ON…

UNIN will remove the duplicated records.

Quick Setup a Project Using JDBC & Derby DB/Java DB

Categories: Database • Java; Tagged with: Derby • Java • JDBC • Maven; @ May 8th, 2014 0:03

Set up Maven dependences

<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derbyclient</artifactId>
<version>10.10.2.0</version>
</dependency>

<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.10.2.0</version>
</dependency>

Connect to the DB using JDBC

This method will print out all Tables; // before you do anything with the Database, there should be some System tables already.

package com.liguoliang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JDBCTest {

	private static final String DB_URL = "jdbc:derby:tempDBForTest;create=true";	
	public static void main(String[] args) {
	    Connection conn = null ;
	    try {
	        Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
	        conn = DriverManager.getConnection(DB_URL);
	        
	        String sql = "SELECT TABLENAME FROM SYS.SYSTABLES";
			PreparedStatement ps = conn.prepareStatement(sql);
	        
	        ResultSet rs = ps.executeQuery();
	        while (rs.next()) {
				System.out.println("Rs: " + rs.getString(1));
			}
	    } catch (SQLException se) {
	         se.printStackTrace();
	    } catch(ClassNotFoundException e){
	        System.out.println("JDBC Driver not found in CLASSPATH") ;
	    }finally {
	        if(conn != null){
	            try{
	                conn.close() ;
	            } catch(SQLException se){
	                se.printStackTrace();
	            }
	        }
	    }
	}

}

Use Eclipse to manage your Derby

Switch to ‘Database Development’ perspective, you may create/manage Derby DB/table.

Older Posts