A Pinoy DBA's Blog

Linux: Generating SSH Keys

2011-12-13T16:13:00.004-05:00

One of my colleagues asked if there's a way to log into a remote machine using SSH without a prompting for a password. A script was being written to check the load remotely. I told him to generate SSH keys for the user that will be running the command and copy that key to the other machine. For example, I have to machines and I want them to be cross checking each other (for some reason). Let's call them Server01 and Server 02.

First generate an ssh key on each machine and copy that to the other machine. I ran the following on both of them:

Linux> ssh-keygen -t rsa

Linux> ssh-copy-id -i ~/.ssh/id_rsa.pub user@server0x

I then tried to do a remote login from server01 to server02. However, it is still prompting me for the password. It seems that "password-less login" is not working. The permissions for the .ssh directory and authorized_keys file is 700 and 600, respectively. These are the correct permissions.
Further investigation revealed that the home directory of the user that I use to generate the keys should have a drwxr-xr-x permission. Thus, after changing the attributes of the directory using

Linux> chmod 755 /path/to/home/directory

I was able to log in remotely without the password being asked.

MySQL: Duplicate Entry Error on Slave

2011-12-05T10:07:00.001-05:00

While doing a normal routine check on the MySQL databases, one of the slave was reporting an error:

Duplicate entry '999' for key 'PRIMARY' on query. Default database...

To resolve this problem, we can set the parameter sql_slave_skip_counter to 1:

mysql> stop slave;

mysql> set global sql_slave_skip_counter=1;

mysql> start slave;

This will ignore the error that was encountered and just continue replicating.

MySQL: Relocating Slave Logs

2011-10-11T14:31:00.006-04:00

On a master-slave configuration, MySQL creates binary logs on the master and events recorded in these files are read by the slave and stores them in relay logs. These log files are stored in the data directory by default. However, they can be relocated to another location for optimization purposes.

We have a master-slave configuration set up on Linux and there was a need to move the log files to its own directory. With all the writing that MySQL is doing, it is best practice to move these log files out of the data directory. These are the files that I modified/copied/moved:

<host-name>-relay-bin.XXXXXX
<host-name>-relay-bin.index
relay-log.info

These files are in the data directory and we would like to move the <host-name>-relay-bin.* files to /var/log/mysql directory. These are the steps that I did:

1. Shutdown the slave. On my test bed, I ran the mysql.server command which comes with the MySQL installer.

shell> mysql.server stop

2. Add the following to your config file under the mysqld section, which is /etc/my.cnf. You might need root access to do this. My slave server name is server02.

relay-log=/var/log/mysql/server02-relay-bin

3. Move the relay files to the new location. My data directory is /var/lib/mysql.

shell> mv /var/lib/mysql/server02-relay-bin.* /var/log/mysql

4. Modify the entries in the /var/lib/mysql/relay-log.info to reflect the new directory. For example, the contents of my relay-log.info are:

./server02-relay-bin.000011

67884

server01-bin.000023

67735

9

All I did was change the first line to /var/log/mysql/server02-relay-bin.000011.

/var/log/mysql/server02-relay-bin.000011

67884

server01-bin.000023

67735

9

5. Modify the entries in the /var/log/mysql/server02-relay-bin.index (remember we've already moved these files). For example, if the contents of the server02-relay-bin.index are:

./server02-relay-bin.000010

./server02-relay-bin.000011

All I did was to change the (.) with /var/log/mysql:

/var/log/mysql/server02-relay-bin.000010

/var/log/mysql/server02-relay-bin.000011

6. Start the slave

shell> mysql.server start

At this point, the relay log files have been moved to the new location. Create a test table on the master and see if it replicates to the slave.

Oracle: ORA-24247: network access denied by access control list (ACL)

2011-08-19T14:49:00.006-04:00

I created a SOLR utility package in Oracle but was getting an HTTP request failed error during run time.

SQL> select solr_utl.get_response('SolrServer.traderonline.com','8080','CycleAds','select?indent=on&version=2.2&q=*%3A*&fq=&start=0&rows=5&fl=UNIQUE_ID&qt=&wt=json&explainOther=&hl.fl=&echoParams=none') from dual;

ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCHEMANAME.SOLR_UTL", line 25

It turned out the network packages like UTL_HTTP, UTL_SMTP, etc are more restricted in Oracle 11g. Before, you can just grant execute on this packages and you're ready to roll. However, in 11g, it's a little more granular.

In order for a database user to use the network packages, they should be given access. To accomplish this, the dbms_network_acl_admin package should be used. Run this packages as the sysdba.

First, we need to create the Access Control List (ACL). In this example, we are going to use the UTL_HTTP network package.

begin
dbms_network_acl_admin.create_acl(
    acl => 'utl_http.xml',
    description => 'enables http to be used',
    principal => 'USERNAME',
    is_grant => true,
    privilege => 'connect'
);
end;
/

Then we need to assign the ACL.

begin
dbms_network_acl_admin.assign_acl(
    acl => 'utl_http.xml',
    host => 'SolrServerHostName',
    lower_port => SolrServerPort
);
end;
/

Finally, grant the privilege.

begin
dbms_network_acl_admin.add_privilege(
    acl => 'utl_http.xml',
    principal => 'USERNAME',
    is_grant => true,
    privilege => 'connect'
);
end;
/

After all have been granted, I tried to run the select statement again and finally got the following output:

{
"responseHeader":{
    "status":0,
    "QTime":9},
"response":{"numFound":145594,"start":0,"docs":[
      {
        "UNIQUE_ID":"98855261"},
      {
        "UNIQUE_ID":"98407600"},
      {
        "UNIQUE_ID":"99051763"},
      {
        "UNIQUE_ID":"98406901"},
      {
        "UNIQUE_ID":"98413603"}]
}}

To drop the ACL, the following can be used:

begin
dbms_network_acl_admin.drop_acl(acl=>'utl_http.xml');
end;
/

Each network package should have their own Access Control List.

Oracle: Determine OS Process Id based on Oracle Process Id

2011-08-01T16:15:00.002-04:00

To get the OS process Id of an Oracle process, join the v$session and v$process views:

select vp.spid "OS Id", vs.sid "Oracle Id", vs.username, vs.osuser

from v$process vp, v$session vs

where vp.addr = vs.paddr;

SOLR: dataImportHandler and Oracle

2011-07-21T10:55:00.002-04:00

We are going to test the latest version of SOLR (3.3) and we need to populate it with data that is as close as the what we have on production. However, the script that populates the production index will not work with the new version because there are fields that were taken out due to localSOLR not being used with 3.3. Thus, a data-config.xml was created to be used by the data import handler.

Reviewing the schema.xml, I was caught by surprise with this definition:

  <field name="AD_ID" type="text" indexed="true" stored="true" required="true"/>
  <dynamicField name="PHOTO_*"  type="string"  indexed="true"  stored="true"/>

And the output is similar to this:

<str name="AD_ID">1</str>

<str name="PHOTO_1">image_1.jpg,</str>

<str name="PHOTO_2">image_2.jpg,</str>

<str name="PHOTO_3">image_3.jpg,</str>

<str name="PHOTO_4">image_4.jpg,</str>

<str name="PHOTO_5">image_5.jpg,</str>

How can we achieve this? We can create a user-defined transformer using JAVA script. Here's what my data-config.xml would look like:

<dataConfig>
<dataSource driver="oracle.jdbc.driver.OracleDriver" 
            url="jdbc:oracle:thin:@//hostname.com:port/SID" 
            user="username" password="password" />
<script>
  <![CDATA[
    function photoFunction(row) {
      var seqNum = row.get("SEQ_NUM");
      var imageName = row.get("IMAGE_NAME");
      var fieldName = "PHOTO_" + segNum;
      row.put(fieldName, imageName);
      return row;
    }
  ]]>
</script>
<document name="Doc">
  <entity name="Ad" pk="AD_ID" 
          query="select a.ad_id, a.other_columns 
                   from table_name a
                   where conditions">
    <entity name="Photo"
            query="select p.seq_num, p.image_name
                     from photo_table p
                     where p.ad_id = '${Ad.AD_ID}'
                     order by p.display_order"
            transformer="script:photoFunction" />
  </entity>
</document>
</dataConfig>

A MySQL database can also be used by changing the dataSource part.

SOLR: dataImportHandler via HTTP

2011-07-06T17:24:00.005-04:00

I was tasked with installing SOLR 3.2 on a new machine and use its geo-spatial search functionality. This cause the schema to change on the new machine. There's a script that populates our SOLR 1.4 index but it contains a lot of business logic to for me to transform it into the 3.2 schema. So, what I did was to use the dataImportHandler to move necessary data from the 1.4 index to 3.2.

When querying the SOLR index using the admin console, the generated XML will look something similar to this:

<response>
<result name="response" numFound="471728" start="0">
    <doc>
      <str name="ID">98339511</str>
      <str name="REMARKS">CUSTOM BUILT, 10,20,40 CUSTOM BUILT</str>
      <str name="NICHE">4</str>
    </doc>
    <doc>
      <str name="ID">98311340</str>
      <str name="REMARKS">1986 MERCEDES_BENZ, MERCEDES BENZ L1113 CAB</str>
      <str name="NICHE">4</str>
    </doc>
</result>
</response>

If you notice, the node is named str and the field name is actually the value of the attribute named "name". To use the value of attribute, the xpath symbol @ was used. Thus, my data-config.xml ended up with this:

<dataConfig>
<dataSource type="HttpDataSource" />
<document>
    <entity column="Doc"
            pk="ID"
            processor="XPathEntityProcessor"
            url="http://host:port/solr/coreName/select/?q=NICHE%3A4"
            onError="skip"
            forEach="/response/result/doc/">
      <field column="ID" xpath="/response/result/doc/str[@name='ID']" />
      <field column="DESCRIPTION" xpath="/response/result/doc/str[@name='REMARKS']" />
      <field column="SITE" xpath="/response/result/doc/str[@name='NICHE'] "/>
    </entity>
</document>
</dataConfig>

The onError attribute signals SOLR to skip the document if it encounters an error.

MySQL: expire_logs_days System Variable

2011-06-09T12:41:00.002-04:00

Our MySQL database crashed because the disk where the data files are hit 100% usage. Eventhough, there was a script that runs, checks, and purges the bin logs, a flaw prevented it from deleting the logs. The script was modified to correct the issue.

But there's another way and it avoids creating a script that manually deletes the bin logs. We can set a system variable called expire_logs_days. This parameter deletes log files that are greater than the value specified. It is dynamic and can be changed on-the-fly. Make sure that the value is at least the maximum number of lag days before the bin logs are applied.

To change it, log into the database as a super user (usually root) and set the global variable:

mysql> set global expire_logs_days=7;

After changing it, make sure that you modify the .ini file also to reflect the value.

MySQL: Setting-up replication

2011-05-18T11:30:00.002-04:00

I was given the task to set-up replication on our MySQL database. However, I am still a newbie with this database but I was able to complete the task. Here's what I did:

1. Modified the my.ini to include the following under the mysqld section:

[mysqld]

log-bin=mysql-bin

server-id=11

2. Created a replication user on the database. Let's call this the master.

mysql> create user 'repladm'@'%.domain.com' identified by 'replpassword';

mysql> grant replication slave on *.* to 'repladm'@'%.domain.com';

3. Obtained the replication coordinates from the master.

mysql> flush tables with read lock;

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| info-mysql.000015 |  8177182 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

Take note of the position and the file values.

4. Stopped MySQL on the master and slave.

5. Copied the data directory over to the slave machine.

6. Modified the my.ini file on the slave with the following under the mysqld section:

[mysqld] 

server-id=12

7. Started MySQL on the master.

8. Started MySQL on the slave.

9. Set the master configuration on the slave.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='masterdb.domain.com',
    -> MASTER_USER='repladm',
    -> MASTER_PASSWORD='replpassword',
    -> MASTER_LOG_FILE=' info-mysql.000015',
    -> MASTER_LOG_POS=8177182;

mysql> start slave;

10. Check the status on the master.

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| info-mysql.000015 |  8177182 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

11. Check the status on the slave.

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterdb.domain.com
                  Master_User: repladm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: info-mysql.000015
          Read_Master_Log_Pos: 8177182
               Relay_Log_File: masterdb2-relay-bin.000051
                Relay_Log_Pos: 8177329
        Relay_Master_Log_File: info-mysql.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 8177182
              Relay_Log_Space: 8177640
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 22
1 row in set (0.00 sec)

The Read_Master_Log_Pos and Master_Log_File values should be the same as the ones on the master.

SOLR: Adding a new core

2011-05-06T11:47:00.002-04:00

One of my clients' requirement is to have separate indexes for different on-line classified ad searches. The way to do is to have SOLR configured as a multi-core environment. To accomplish this, the steps that I did were the following. Schema definitions are the same in all the cores.

1. Create the appropriate directories. Let's assume that the data directory is under /solrdata.

% mkdir -p /solrdata/core2/conf /solrdata/core2/data

2. Copy the conf directory from the existing core

% cp /solrdata/core1/conf/* /solrdata/core2/conf

3. Create a solr.xml file in the SOLR_HOME directory with the following:

<solr persistent="true" sharedLib="lib">
  <cores adminPath="/admin/cores">
    <core name="core1" instanceDir="/solrdata/core1">
      <property name="dataDir" value="/solrdata/core1/data" />
    </core>
    <core name="core2" instanceDir="/solrdata/core2">
      <property name="dataDir" value="/solrdata/core2/data" />
    </core>
</solr>

4. Since the dataDir property is already defined in the solr.xml file, we need to comment out the dataDir element in <INSTANCE_DIR>/conf/solrconfig.xml. It will look something like this:

<dataDir>${solr.data.dir:./solr/core1/data}</dataDir>

5. Restart SOLR.

The new cores should be listed in http://<hostname>:<port>/solr

SOLR: New Core Not Listed In Admin Console

2011-04-28T10:12:00.002-04:00

I was tasked with moving the current SOLR index to its own core as preparation for a multi-core environment. Replication must also be in place. So, I set-up a machine as a SOLR slave. This will be cloned to 5 more machines.

I started SOLR on the cloned machines and checked replication using the admin console. However, the core isn't listed. I tried restarting SOLR but unfortunately, it didn't work.

The solution was to delete the index on the cloned machines and let SOLR start from scratch.

Oracle: Removing targets from Grid Control

2011-04-27T16:25:00.003-04:00

I was installing Oracle 11g Grid Control when I tested something and messed up the target list. An agent was listed as a target but unfortunately, I wasn't able to delete it from the console. Thus, I have manually delete it from the repository.

Before deleting it, I have to stop the management agent on the target database

% <AGENT_HOME>/bin/emctl stop agent

Then, I logged into the repository as sysdba to run the following query:

SQL> select target_name from mgmt_targets where target_type = 'oracle_emd';

This showed me all the registered agents (oracle_emd). Other target_types are hosts, oracle_database, etc. Run a distinct on target_type to see the full list.

After I've identified what agent should be deleted, I ran the following procedure:

SQL> exec mgmt_admin.cleanup_agent('dsdevdb02-tol-oracle:1830');

Went back to grid control, refreshed that targets page and it was indeed deleted.

MySQL: Out of Resources

2010-06-08T14:51:00.001-04:00

One of our sister companies called me today asking if I know anything about the error that they are encountering, which is:

ERROR 23 (HY000): Out of resources when opening file (Errcode: 24)

Looking at the error, it looks like it’s a database parameter value. Researching it, my hunch was correct. The parameter open_files_limit need to be changed to resolve the issue. So, I changed the value of the parameter in the /etc/my.cnf file and restarted the database.

Oracle: Changing a column using SQL*Loader

2009-11-08T22:24:00.002-05:00

Our project requires that data in one of our tables need to be summarized first before inserting into the new tables. Data that are loaded into the old table, summarized, then inserted into the new ones. While looking into the script that loads into the old table, we saw that it loads the data first using SQL*Loader, then updates a date column based on a character column. Let’s call them col_date and col_char, respectively.
This can be achieved using SQL*Loader and can improve the process time. All we did was to change the attribute of that column. For example, here’s what the old control file looks like:

LOAD DATA
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col_char,
col_date
)
What we did was to change the col_date field. This is what we ended with:

LOAD DATA
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col_char,
col_date "to_date(:col_char,'MMDDYY')"
)
Basically you can change any column using built-in Oracle functions. Let’s say you want to make the col_date yesterday’s date, then the control file would be:

LOAD DATA
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col_char,
col_date "trunc(sysdate – 1)"
)
Oracle’s buil- in functions can be easily searched using Google.

MySQL: Value Truncated When Inserting Into a Table

2009-10-19T22:27:00.004-04:00

Again as a newbie in MySQL, I created a procedure that would insert values into a table depending on the business requirements. However, there I noticed that if a value with length created than the table column, MySQL truncates the value to fit the column.

Here’s what I did:

mysql> create table sample (sample_col varchar(3));
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //

mysql> create procedure sample_proc (in par_var varchar(2))
-> begin
-> insert into sample values (par_var);
-> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call sample_proc ('XXX');
Query OK, 1 row affected, 1 warning (0.00 sec)

Notice that MySQL issued a warning.
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'par_var' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
Checking the table, I get

mysql> select * from sample;
+------------+
| sample_col |
+------------+
| XX |
+------------+
1 row in set (0.00 sec)
MySQL did truncate the value. I tried researching the issue but unfortunately I wasn’t able to find a document that resolves the issue (or maybe I wasn’t using the right keywords). So, being a newbie, I created a topic on the MySQL website (http://forums.mysql.com/). Peter Brawley, one of the forum members, suggested to change the sql_mode to ’strict_trans_table’:

set sql_mode='strict_trans_tables';
After which, I tried running the procedure again:

mysql> call sample_proc ('XXX');
ERROR 1406 (22001): Data too long for column 'c' at row 1
This corrected the issue that I was having. However, this need to be in effect on the entire database. Thus, I added this variable in the /etc/my.cnf file:

sql-mode = 'strict_trans_tables'
Note that the variable in the configuration file has a hyphen (-) instead of an underscore (_).

MySQL: db Express Error: Invalid Username/Password

2009-08-19T17:45:00.001-04:00

The company assigned me to be the DBA of a new project. However, it will be using MySQL. One of their requirements is to have the ERD in the database. Being a newbie with MySQL, I searched the internet for software that would create an ERD. Luckily, MySQL has DB Designer 4.

After installing it and creating an ERD, I tried to save it in the database but I got an error message:

dbexpress Error: Invalid username/password

Researching again, all the documents say it has something to do with the versions of Designer4 and the database (we were using MySQL 5.1). But they suggested to reset the password using the OLD_PASSWORD function. Thus, after issuing the command:

mysql> set password for ‘username’@'hostname’=OLD_PASSWORD(’password’);

The ERD was successfully saved in the database.