I picked up a good tip from one of our DBA's yesterday. He uses sp_who2 to see which users have active threads/processes running against a particular database. This stored proc returns the PIDs as well (Process ID number). I had known about this previously. What was new to me was he also used:
dbcc inputbuffer(pidNumberHere)
to get the database to tell him which stored proc was being executed by that PID. That seems to me like a handy trick. It certainly solved the problem I was having.
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Friday, January 28, 2011
Wednesday, September 22, 2010
Ms Sql Server Grant Execute against sub-set of stored procs
Thanks to Karlson for this -
Here's a script that can grant execute (or whatever you wish to grant) to a set of stored procs based on a prefix in the stored proc name:
Set nocount on
Declare @proc_name as nvarchar(max)
DECLARE procs CURSOR
FOR Select [name] FROM sysobjects WHERE [name] like '[sp prefix here]%' AND xtype = 'P'
OPEN procs
FETCH NEXT FROM procs
INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
exec('Grant Execute ON ' + @proc_name + ' TO[roleName here]')
FETCH NEXT FROM procs
INTO @proc_name
END
CLOSE procs;
DEALLOCATE procs;
Set nocount off
Here's a script that can grant execute (or whatever you wish to grant) to a set of stored procs based on a prefix in the stored proc name:
Set nocount on
Declare @proc_name as nvarchar(max)
DECLARE procs CURSOR
FOR Select [name] FROM sysobjects WHERE [name] like '
OPEN procs
FETCH NEXT FROM procs
INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
exec('Grant Execute ON ' + @proc_name + ' TO
FETCH NEXT FROM procs
INTO @proc_name
END
CLOSE procs;
DEALLOCATE procs;
Set nocount off
Monday, August 23, 2010
Some good links (to blog articles) on Configuration Management
Here's some good links to blog articles on Configuration Management. The first is about the importance of a CM person in a company. I would tend to agree with one of the comments that talks about the size of the company making a difference. Obviously a smaller startup doesn't need a dedicated CM person. However, larger enterprises like the one I'm at now with numerous development environments, multiple servers, a GIS infrastructure, a document management infrastructure, and a beomoth of a mainframe legacy system needs a dedicated CM team. Here's the link:
http://evgeny-goldin.com/blog/2010/08/21/configuration-management-position/
The other link considers adding database automated deployment to you CM system. I would highly recommend when I contrast my experience over the last two years with my previous release management experience. Previously we automatically deployed database schema and data changes. In my more recent experience the DBA group has flatly declined any encouragement and offer to do this. The difference in the amount of time to do database refreshes and debug issues with them is stark - like hours vs. days. Here's the link:
http://evgeny-goldin.com/blog/2010/08/21/configuration-management-position/
The other link considers adding database automated deployment to you CM system. I would highly recommend when I contrast my experience over the last two years with my previous release management experience. Previously we automatically deployed database schema and data changes. In my more recent experience the DBA group has flatly declined any encouragement and offer to do this. The difference in the amount of time to do database refreshes and debug issues with them is stark - like hours vs. days. Here's the link:
Wednesday, July 21, 2010
MS Sql Server Stored Proc Search Query
If you work in an org where there's a lot of business logic put into (MS Sql Server) stored procedures or you have an application that is stored proc intensive, you might find this code snipped helpful. It searches all the stored procs in the database for a specific string
select distinct o.name from syscomments c
inner join sysobjects o
on c.id = o.id
where o.type = 'p'
and c.text like '%string to search for%'
order by o.name
select distinct o.name from syscomments c
inner join sysobjects o
on c.id = o.id
where o.type = 'p'
and c.text like '%string to search for%'
order by o.name
Thursday, May 13, 2010
More Software Emergency Fixing Experiences
I've recently helped two more clients with 'emergency fix' issues.
The first issue was a problem a national grocery chain had with an export from a SAS program that did a pull from multiple database tables into an excel spreadsheet, and subsequently imported this data into a MS Access database using a SSIS (Sql Server Integration Services) package. The SAS export seemed to be fine - that is, it executed without errors, but the SSIS package would fail. From my understanding, the client would run this process about every month or so, so there were a fair number of records (1000's). However, they started having this issue in Dec. and I didnt' get called about the issue until late April, so there was a backup of data.
The client didn't have the SQL BI studio installed, so we had to go through the idx file manually to see if there was anything obviously wrong. There didn't appear to be. The database and excel connections tested fine. There didn't appear to be anything riotously wrong with the data formatting or invalid characters in the excel spreadsheet. So, what do you do?
Well, for one thing, don't test with the full dataset if you think it might be a data related issue. Keep making your dataset smaller until you are sure that none of the data works. In this case, we cut the dataset in half, started from Feb to the end of Apr and the import worked! This confirmed for us that there were no permissions issues, or software upgrade issues (we were considering that software upgrades might be part of the problem as more than one piece of the software had been upgraded in that time period). The successful test confirmed for us that we were dealing with a data related issue. From there it was just a matter of determining exactly which rows of data couldn't be imported, and what it was about the data in those rows that botched things.
Less than 5 hours of work to this point on this fix.
With the second emergency, a telecom company was having issues getting an automated FTP (file transfer protocol) process to work. They had changed the paths of where the ftp files were residing and they were voip experts, not so much into the bash shell scripting thing. Their first problem was pathing in the bash file. That was an easy fix. Then there was a problem with sending a notification email that apparently they never got to work. This was a little bit more tricky. They originally had the email coded similar to this example.
However, after a bit of work I discovered that the server this script was running on was processing all the command too quickly for the smtp server to get the email off. In the end, I had to do something more like this:
Then to set the bash script to run in a windows scheduler (using cygwin) I had to do something like this:
The first issue was a problem a national grocery chain had with an export from a SAS program that did a pull from multiple database tables into an excel spreadsheet, and subsequently imported this data into a MS Access database using a SSIS (Sql Server Integration Services) package. The SAS export seemed to be fine - that is, it executed without errors, but the SSIS package would fail. From my understanding, the client would run this process about every month or so, so there were a fair number of records (1000's). However, they started having this issue in Dec. and I didnt' get called about the issue until late April, so there was a backup of data.
The client didn't have the SQL BI studio installed, so we had to go through the idx file manually to see if there was anything obviously wrong. There didn't appear to be. The database and excel connections tested fine. There didn't appear to be anything riotously wrong with the data formatting or invalid characters in the excel spreadsheet. So, what do you do?
Well, for one thing, don't test with the full dataset if you think it might be a data related issue. Keep making your dataset smaller until you are sure that none of the data works. In this case, we cut the dataset in half, started from Feb to the end of Apr and the import worked! This confirmed for us that there were no permissions issues, or software upgrade issues (we were considering that software upgrades might be part of the problem as more than one piece of the software had been upgraded in that time period). The successful test confirmed for us that we were dealing with a data related issue. From there it was just a matter of determining exactly which rows of data couldn't be imported, and what it was about the data in those rows that botched things.
Less than 5 hours of work to this point on this fix.
With the second emergency, a telecom company was having issues getting an automated FTP (file transfer protocol) process to work. They had changed the paths of where the ftp files were residing and they were voip experts, not so much into the bash shell scripting thing. Their first problem was pathing in the bash file. That was an easy fix. Then there was a problem with sending a notification email that apparently they never got to work. This was a little bit more tricky. They originally had the email coded similar to this example.
#!/bin/bash telnet smtp.example.org 25 <<_EOF HELO relay.example.orgMAIL FROM:<joe@example.org>RCPT TO:<jane@example.org>DATAFrom: Joe <joe@example.org>To: Jane <jane@example.org>Subject: HelloHello, world!.QUIT_EOF
However, after a bit of work I discovered that the server this script was running on was processing all the command too quickly for the smtp server to get the email off. In the end, I had to do something more like this:
#!/bin/sh( echo HELO ccielogs.comsleep 2echo MAIL FROM:<test@somedomain.com>sleep 2echo RCPT TO:<test@someotherdomain.com>sleep 2echo DATAsleep 2echo Subject:Test-Mail!sleep 2echo If you can read this, it Works!!!sleep 2echo .sleep 2echo QUIT) | telnet XXX.XXX.XXX.XXX 25
Then to set the bash script to run in a windows scheduler (using cygwin) I had to do something like this:
C:\cygwin\bin\bash.exe --login -c "/myfolder/myscript.sh" Monday, April 26, 2010
Param datatypes with Sql Server Stored Procs
I tried several times to save and post this blog entry earlier today and it would NOT save. I have no idea what is wrong as I tried to email it to myself so I could do it later and I couldn't email it either. I'm thinking there was a bad character in my original text, but I pasted in into (and copied it out of notepad) so... I'm not sure what is going on. Sometimes these tools in the 'cloud' seem to be in a 'fog'.
Anyway, what I wanted to say was....
I've been working with stored procs lately in Sql Server. My stored procs take search parameters that I wanted to be very flexible - strings with wild characters on either end using a LIKE in the SQL. Originally in my declaration for the parameters I used a char datatype. What I didn't realize was if the param passed in is shorter than the length you specified, a char datatype will automatically add placeholder characters to fill up the rest of the space. So, if you declared a char with 9 characters and passed the SP a 3 character string, SQL Server would automatically add 6 more characters to it. I was then trying to prefix and append the percentage sign (wild character) on either end of my param, but it was getting ignored.
In the end (with the help of my more DB inclined colleague, Karlson) I changed the datatype of my param to varchar and I could happily prefix and append wild characters to it.
Got another interesting business idea over the weekend - it's based on a few assumptions, and it's for the investment/financial industry. The assumptions are:
- history repeats itself.
- the stock market, like history, is cyclical in nature
- investment funds are just as much marketing as they are statistical science
- people will buy anything that is marketed well, whether it provides them with tangible value or not - (the value of stock is all about perception of value)
The idea is simple - create a fund that is based on the cyclical nature of the stock market. Predictable things happen when interest rates go up, or when commodities or high. Convince people that you can harness the 'value' in the history of what happens to the stock market with certain indicators are up, and you'll have a winner fund.
Anyway, what I wanted to say was....
I've been working with stored procs lately in Sql Server. My stored procs take search parameters that I wanted to be very flexible - strings with wild characters on either end using a LIKE in the SQL. Originally in my declaration for the parameters I used a char datatype. What I didn't realize was if the param passed in is shorter than the length you specified, a char datatype will automatically add placeholder characters to fill up the rest of the space. So, if you declared a char with 9 characters and passed the SP a 3 character string, SQL Server would automatically add 6 more characters to it. I was then trying to prefix and append the percentage sign (wild character) on either end of my param, but it was getting ignored.
In the end (with the help of my more DB inclined colleague, Karlson) I changed the datatype of my param to varchar and I could happily prefix and append wild characters to it.
Got another interesting business idea over the weekend - it's based on a few assumptions, and it's for the investment/financial industry. The assumptions are:
- history repeats itself.
- the stock market, like history, is cyclical in nature
- investment funds are just as much marketing as they are statistical science
- people will buy anything that is marketed well, whether it provides them with tangible value or not - (the value of stock is all about perception of value)
The idea is simple - create a fund that is based on the cyclical nature of the stock market. Predictable things happen when interest rates go up, or when commodities or high. Convince people that you can harness the 'value' in the history of what happens to the stock market with certain indicators are up, and you'll have a winner fund.
Tuesday, February 23, 2010
CPU's and Sql Server
Again in our staging environment getting ready for a release in production, we were seeing unexplained slow response times for our AgilePoint application when compared against production or development envs. After some digging we discovered that our production DB server that serviced the AgilePoint DB had twice as many CPU's as the dev DB server, and 4 times as many CPU's as the staging server.
What I didn't know is that in Sql Server you can assigned/limit how many CPU's the database can use. This setting is in Server Properties on the Advanced tab, and is called 'Max Degree of Parallelism'. Like many of the other settings with Server Properties in Sql Server, 0 equates to 'unlimited' or in this case 'all processors'.
What I didn't know is that in Sql Server you can assigned/limit how many CPU's the database can use. This setting is in Server Properties on the Advanced tab, and is called 'Max Degree of Parallelism'. Like many of the other settings with Server Properties in Sql Server, 0 equates to 'unlimited' or in this case 'all processors'.
Tuesday, November 24, 2009
Things learned from Website moves
I recently moved several client sites off of servers that they had been on for the better part of a decade to new VPS boxes. They had MySql backends and used Java/Velocity CMS system (InfoGlue) hosted on Tomcat with an AJP connector to apache web servers on the front end. One site uses htdig for it's search implementation.
Issues (with resolutions) that I ran into...
1. GZIP'ing a running Tomcat instance
Before one of the sites was moved, a client tried to make a backup of the Tomcat instance that they were running under. They executed a:
gzip -r filename.gz ./tomcat/
which ended up recursively zipping all files under the tomcat directory. This crashed the running instance of Tomcat. (Perhaps trying to tar or gzip a running tomcat instance isn't a great idea. Copy it first and then tar/gzip that) Everything was quickly unzipped and they tried to restart Tomcat but it wouldn't start. After an hour of search, I was asked to help. I poked and prodded for about 90 minutes and discovered that all the webapps were missing the
/WEB-INF/web.xml
file. I don't know how/why gzip and upzipping would make these files disappear, but they were there anymore. So I replaced them and tried restarting Tomcat and everything came up.
2. Problems with MysqlDump
I had a couple of problems backing up the MySql database. First MysqlDump would execute, but not put all of the tables into the backup file. I discovered that I had run out of disk space. Then it turned out that mysqldump doesn't care about relational integrity when it creates the backup script so when I tried to import it into the new database I ran into errors complaining about foreign keys. To resolve this, I had to add set foreign_key_checks=0; at the top of my backup script. This allowed me to import it successfully.
3. Mysql connection pool Exhausted
After getting the db imported and the web server moved over, the application seemed to be running fairly nicely on the new VM's. Then I left it alone for a couple of hours. When I came back it was throwing connection pool exhausted exceptions all over the place. I could resolve this by logging back into InfoGlue's cms instance, but that wasn't the right resolution for me. So I did some checking. Many people suggest to set the wait_timeout parameter in the my.cfg file for mysql higher or lower. I tried that and it didn't seem to work. What I ended up doing was adding a line in my database.xml file:
<param name="validation-query" value="select * from someTable" />
This helps keep the connection alive by pinging the db with the query every so often.
4. MySql Data Directory Change
While trying to resolve the previous issue I was changing/adding parameters into the mysql my.cfg file. I did a copy/paste which had a number of properties, one of which was the pointer to the data directory for mysql. This pointer was different than what I was using. As a result, when I restarted mysql, all of my databases, tables, users, and data was gone. I freaked. Then after thinking about things for a little bit and checking through files one more time I realized the mistake I had made and commented that line out. Restarting mysqld brought all my databases, users, and data back again. Whew.
5. Getting HtDig going again
The original installation of HtDig on the old server hadn't been indexed in over two years because cron had been broken. I didn't have access to the root password to fix this problem so the client was very interested in getting it running again on the new server. I had no previous experience with HtDig.
I copied over all the files I could find related to HtDig off of the old server and installed them on the new box. After a few tries, re-indexing worked, but I still had a problem with displaying the search results page in the web site. It turned out that I was missing a virtual directory configuration in my httpd.conf file for the directory where htsearch was running from (as a cgi script). The only reason I figured that out was by using lynx (linux CLI web browser). After fixing that, I got my newly indexed results displaying on the web site.
6. Using /etc/hosts helps
I've found that the /etc/hosts file is a big help in moving sites like this - whether you want to test the site while the live site is still running, or quickly configure pointers to a server dependency.
7. MySql sql queries are case sensitive
One of the website moves I did involved porting a MySql database from a Windows box to a CentOs box. The dump on the Windows box made all characters for tables lower case. I didn't pay too much attention to this at the time. They imported the same way.
When I started up my Tomcat server, it tried to start up, but threw errors related to jdbc connection pool and a ValidateObject. After a bit of googling I discovered that this is related to the validate query (the query I wrote about earlier that checks the connection every so often to make sure it hasn't gone stale). I tried running that query right on the mysql box and it would run because the table name was all lower case. So I changed all my table names to camel case and things worked.
8. Issues with unknownDefaultHost and serverNodeName in Tomcat
My inability to set the serverNodeName was resolved by adding a line in my hosts file to point the IP of my box to the Host variable found when I run the 'set' comment in CentOS. My issue with unknowDefaultHost was resolved by going into the server.xml file and editing the Engine element's defaultHost attribute - changing it from my old domain name to 'localhost'
9. MySql error 1153 - Got packet bigger than 'max_allowed_packet' bytes
Got this error a couple of times with different DB imports. To resolve I needed to go to the my.cnf file (found sometimes in /etc/) and set that property like this:
max_allowed_packet=16M
for the mysqld (mysql daemon). Then I had to restart the daemon (/etc/init.d/mysqld restart) and I could run my import (mysql -p -h localhost dbname < dbImportFile.sql)
Issues (with resolutions) that I ran into...
1. GZIP'ing a running Tomcat instance
Before one of the sites was moved, a client tried to make a backup of the Tomcat instance that they were running under. They executed a:
gzip -r filename.gz ./tomcat/
which ended up recursively zipping all files under the tomcat directory. This crashed the running instance of Tomcat. (Perhaps trying to tar or gzip a running tomcat instance isn't a great idea. Copy it first and then tar/gzip that) Everything was quickly unzipped and they tried to restart Tomcat but it wouldn't start. After an hour of search, I was asked to help. I poked and prodded for about 90 minutes and discovered that all the webapps were missing the
/WEB-INF/web.xml
file. I don't know how/why gzip and upzipping would make these files disappear, but they were there anymore. So I replaced them and tried restarting Tomcat and everything came up.
2. Problems with MysqlDump
I had a couple of problems backing up the MySql database. First MysqlDump would execute, but not put all of the tables into the backup file. I discovered that I had run out of disk space. Then it turned out that mysqldump doesn't care about relational integrity when it creates the backup script so when I tried to import it into the new database I ran into errors complaining about foreign keys. To resolve this, I had to add set foreign_key_checks=0; at the top of my backup script. This allowed me to import it successfully.
3. Mysql connection pool Exhausted
After getting the db imported and the web server moved over, the application seemed to be running fairly nicely on the new VM's. Then I left it alone for a couple of hours. When I came back it was throwing connection pool exhausted exceptions all over the place. I could resolve this by logging back into InfoGlue's cms instance, but that wasn't the right resolution for me. So I did some checking. Many people suggest to set the wait_timeout parameter in the my.cfg file for mysql higher or lower. I tried that and it didn't seem to work. What I ended up doing was adding a line in my database.xml file:
<param name="validation-query" value="select * from someTable" />
This helps keep the connection alive by pinging the db with the query every so often.
4. MySql Data Directory Change
While trying to resolve the previous issue I was changing/adding parameters into the mysql my.cfg file. I did a copy/paste which had a number of properties, one of which was the pointer to the data directory for mysql. This pointer was different than what I was using. As a result, when I restarted mysql, all of my databases, tables, users, and data was gone. I freaked. Then after thinking about things for a little bit and checking through files one more time I realized the mistake I had made and commented that line out. Restarting mysqld brought all my databases, users, and data back again. Whew.
5. Getting HtDig going again
The original installation of HtDig on the old server hadn't been indexed in over two years because cron had been broken. I didn't have access to the root password to fix this problem so the client was very interested in getting it running again on the new server. I had no previous experience with HtDig.
I copied over all the files I could find related to HtDig off of the old server and installed them on the new box. After a few tries, re-indexing worked, but I still had a problem with displaying the search results page in the web site. It turned out that I was missing a virtual directory configuration in my httpd.conf file for the directory where htsearch was running from (as a cgi script). The only reason I figured that out was by using lynx (linux CLI web browser). After fixing that, I got my newly indexed results displaying on the web site.
6. Using /etc/hosts helps
I've found that the /etc/hosts file is a big help in moving sites like this - whether you want to test the site while the live site is still running, or quickly configure pointers to a server dependency.
7. MySql sql queries are case sensitive
One of the website moves I did involved porting a MySql database from a Windows box to a CentOs box. The dump on the Windows box made all characters for tables lower case. I didn't pay too much attention to this at the time. They imported the same way.
When I started up my Tomcat server, it tried to start up, but threw errors related to jdbc connection pool and a ValidateObject. After a bit of googling I discovered that this is related to the validate query (the query I wrote about earlier that checks the connection every so often to make sure it hasn't gone stale). I tried running that query right on the mysql box and it would run because the table name was all lower case. So I changed all my table names to camel case and things worked.
8. Issues with unknownDefaultHost and serverNodeName in Tomcat
My inability to set the serverNodeName was resolved by adding a line in my hosts file to point the IP of my box to the Host variable found when I run the 'set' comment in CentOS. My issue with unknowDefaultHost was resolved by going into the server.xml file and editing the Engine element's defaultHost attribute - changing it from my old domain name to 'localhost'
9. MySql error 1153 - Got packet bigger than 'max_allowed_packet' bytes
Got this error a couple of times with different DB imports. To resolve I needed to go to the my.cnf file (found sometimes in /etc/) and set that property like this:
max_allowed_packet=16M
for the mysqld (mysql daemon). Then I had to restart the daemon (/etc/init.d/mysqld restart) and I could run my import (mysql -p -h localhost dbname < dbImportFile.sql)
Monday, October 5, 2009
MS Sql Server bug
We ran into an Sql Server bug today that was rather interesting. We were implementing synonyms across a number of views, tables, and stored procs in a couple of DB's. Everything was working fine until another team did a deploy and changed the index on a view that was referenced by one of our synonyms. It turns out that there is a documented bug which requires that any time the DDL has changed on a view that is referenced by a synonym, that synonym looses it's connection to the view. This includes just updating the index to the view.
Thursday, August 6, 2009
Error 8510 and MSDTC...
We had the whole development environment down for a day. After spending a good bit of time debugging, we discovered that we were getting a significant number of errors in our event logs saying:
Inner: The transaction has aborted.
Inner: Failure while attempting to promote transaction.
Inner: Fatal error 8510 occurred at Aug 5 2009 1:09PM. Note the error and time, and contact your system administrator.
A severe error occurred on the current command. The results, if any, should be discarded.
They were thrown against the running of several different stored procs and because of the architecture of our system, all of our COM+ components were rendered useless.
I found a post that talks about 'fatal error 8510' that was interesting:
http://blogs.msdn.com/asiatech/default.aspx - go to the bottom of the page (it's a ways down). However that turned out to NOT be the resolution to our problem
The resolution to our problem resided in the fact that SQL Server could not initiate a distributed transaction. We had thought that there was a problem with the MSDTC cluster, but that turned out to not be an issue as other sql servers in the cluster could initiate distributed transactions. Restarting services on the sql server that could not initiate distributed transactions resolved the problem
Inner: The transaction has aborted.
Inner: Failure while attempting to promote transaction.
Inner: Fatal error 8510 occurred at Aug 5 2009 1:09PM. Note the error and time, and contact your system administrator.
A severe error occurred on the current command. The results, if any, should be discarded.
They were thrown against the running of several different stored procs and because of the architecture of our system, all of our COM+ components were rendered useless.
I found a post that talks about 'fatal error 8510' that was interesting:
http://blogs.msdn.com/asiatech/default.aspx - go to the bottom of the page (it's a ways down). However that turned out to NOT be the resolution to our problem
The resolution to our problem resided in the fact that SQL Server could not initiate a distributed transaction. We had thought that there was a problem with the MSDTC cluster, but that turned out to not be an issue as other sql servers in the cluster could initiate distributed transactions. Restarting services on the sql server that could not initiate distributed transactions resolved the problem
Sunday, May 24, 2009
MySQL service restart, InfoGlue
I have MySQL running behind Infoglue for a new web app one of my clients has. We went into production on Friday with that web site. Saturday evening the hosting providing had a scheduled update and so all the boxes got rebooted. Unfortunately, the MySQL service which was configured to autorestart didn't come back up. In the application event logs (I've got it running on a Windows server) it said:
...MySQL Server 5.1\bin\mysqld: unknown option '--enable-pstack'
I searched the MySQL forums and Google for this error and nothing. I reinstalled the service a couple of times.. and nothing - same error. I was sweating by this time.
Then I started looking at the my.ini file for Mysql in the installation folder (sibling of the bin folder). I saw these lines at the very bottom:
#Print a symbolic stack trace on failure.
enable-pstack
Ah ha. So here's where that 'unknown option' was coming from. I had no idea what was making a failure happen on startup to make it print out this 'stack trace' so I just commented out that line. ...And low and behold, MySQL started up happily and things worked.
Whew.
...MySQL Server 5.1\bin\mysqld: unknown option '--enable-pstack'
I searched the MySQL forums and Google for this error and nothing. I reinstalled the service a couple of times.. and nothing - same error. I was sweating by this time.
Then I started looking at the my.ini file for Mysql in the installation folder (sibling of the bin folder). I saw these lines at the very bottom:
#Print a symbolic stack trace on failure.
enable-pstack
Ah ha. So here's where that 'unknown option' was coming from. I had no idea what was making a failure happen on startup to make it print out this 'stack trace' so I just commented out that line. ...And low and behold, MySQL started up happily and things worked.
Whew.
Wednesday, March 4, 2009
Schema Crawler
I got back to using schema crawler this week, trying to make sure we can explain the inconsistencies we see in the metadata between our different environments. I found this cool little script to do port scanning so I could discover which ports our DB's were using.
HOST=127.0.0.1;
for((port=1;port<=65535;++port)); do echo -en "$port \n"; if echo -en "open $HOST $port\nlogout\quit" | telnet 2>/dev/null | grep 'Connected to' > /dev/null;
then echo -en "\n\nport $port/tcp is open\n\n";
fi;
done
Tuesday, September 11, 2007
Oracle & perl
Continuing to work with SqlPlus and Oracle 10g. Today I resolved two issues I had with an sql query.
With the first problem, I was passing two parameters into the .sql file and when the sql was executed in the file, along with the selected information, it would output 'old ... my sql here with the params not replaced' and then 'new... my sql here with the params replaced'. Thanks for that, but I know which params I'm replace, I don't need more notification of it. It turns out getting rid of this notification is fairly easy. At the top of the script, just put 'set verify off' and no more 'old...new' junk.
In the second problem (same script) I was doing some division and there were instances where my divisor could be (and was) 0. Sql doesn't like to divide by 0 at all (like pretty much every other language I've worked with). There is a neat little fix to get around this as well. My divisor looked like 'select count(a.blah) from ....' and if you add 'select decode(count(a.blah),0,1,count(a.blah)) from ...' it will substitute a 1 if the selected count is a 0. It worked super.
Messing around with Perl some more, I discovered some good functions. @myArray = split(/~/,$var); will take make an array of strings, seperating them by every '~' found in the string. Working with arrays, push(@myArray, someValue) pushes a new value onto 'myArray'. Pretty basic stuff, I know. I like to celebrate small victories (and remember them for next time).
Can you believe it, I hit a dog riding my bike on the way home from work today. A big doberman. He ran right infront of me. I slammed on my brakes and my rear end came up. I didn't hit him hard - but enough to put my handle bars out of alignment with my front tire a bit. I fixed that at the first red light I stopped at. I also just about hit a snake warming himself on the pavement.
With the first problem, I was passing two parameters into the .sql file and when the sql was executed in the file, along with the selected information, it would output 'old ... my sql here with the params not replaced' and then 'new... my sql here with the params replaced'. Thanks for that, but I know which params I'm replace, I don't need more notification of it. It turns out getting rid of this notification is fairly easy. At the top of the script, just put 'set verify off' and no more 'old...new' junk.
In the second problem (same script) I was doing some division and there were instances where my divisor could be (and was) 0. Sql doesn't like to divide by 0 at all (like pretty much every other language I've worked with). There is a neat little fix to get around this as well. My divisor looked like 'select count(a.blah) from ....' and if you add 'select decode(count(a.blah),0,1,count(a.blah)) from ...' it will substitute a 1 if the selected count is a 0. It worked super.
Messing around with Perl some more, I discovered some good functions. @myArray = split(/~/,$var); will take make an array of strings, seperating them by every '~' found in the string. Working with arrays, push(@myArray, someValue) pushes a new value onto 'myArray'. Pretty basic stuff, I know. I like to celebrate small victories (and remember them for next time).
Can you believe it, I hit a dog riding my bike on the way home from work today. A big doberman. He ran right infront of me. I slammed on my brakes and my rear end came up. I didn't hit him hard - but enough to put my handle bars out of alignment with my front tire a bit. I fixed that at the first red light I stopped at. I also just about hit a snake warming himself on the pavement.
Friday, September 7, 2007
Random assimilations and Business ideas
Today I learned that you can pass in parameters to .sql scripts that are destined to be run with sqlplus. It looks like this: 'sqlplus -s dbuser/dbpasswd@dbname @scriptname.sql param1 param2' and then in the sql file you output the params using &1 and &2. I was finding though that for the replacement sqlplus was being verbose and telling me about every replacement it made. I didn't like that so much, but couldn't figure out how to turn it off. I've already got 'set headers off' and 'set feedback off' set at the beginning of the script. I tried 'set logging off' but it didn't like that (because logging is set to off as a default I believe). Anyhow, the replacements were working.
With Oracle SQL, if you want to select back by a time interval, do something like this: 'select count(*) from my_log_table where logdatetime > current_date - interval '15' minute;' or you can use '- interval '6' hour' ... or '- interval '2' day' etc. Single quotes around the number!
Back to cron: to set cron to run every minute go '*/1 * * * * /scriptToBeRun.sh' Every 3 minutes: '*/3 * * * * /script2BRun.sh'
One problem I've run into with FusionCharts is the limitation of the Flash plugin to handle a big data dump. Put more than a few graphs with, say, 50 datapoints each on a page, and the browser hangs and the plugin throws our of memory errors.
Interesting IT Business Ideas:
- Reusing software development tools for other industries/applications. For example: other than the obvious issue with binary file types, why don't architects and lawyers use a repository like SVN, or Monotone? ( I left out the ones you have to pay for :-) We've tossed this idea around wondering if anybody has tried to market repositories this way....
- Becoming a freeware software consultant for small businesses. You can get everything from a good OS to all the 'openOffice' applications for free, not to mention what Google and others are putting on line (word processors and spreadsheets, etc), and universally accessible too! Seems to me if you're good at training, you can really help small businesses save a buck on software by showing them how to use all the free stuff out there. It wouldn't surprise me if people are doing this one already.
- write a GOOD, scalable, secure, extensible front end framework for web applications. There's a tonne of them out there now, but it seems like every one of them (Ruby, JSF, Struts, Facelets, Tapestry, SpringMVC, etc) has a downside. I've had conversations with a number of knowledgeable architect types and they agree that there is no good front end web solution out there right now. So, one could code a good freeware framework, consult using it, and then sell it to Red Hat (like the JBoss consultants did).
With Oracle SQL, if you want to select back by a time interval, do something like this: 'select count(*) from my_log_table where logdatetime > current_date - interval '15' minute;' or you can use '- interval '6' hour' ... or '- interval '2' day' etc. Single quotes around the number!
Back to cron: to set cron to run every minute go '*/1 * * * * /scriptToBeRun.sh' Every 3 minutes: '*/3 * * * * /script2BRun.sh'
One problem I've run into with FusionCharts is the limitation of the Flash plugin to handle a big data dump. Put more than a few graphs with, say, 50 datapoints each on a page, and the browser hangs and the plugin throws our of memory errors.
Interesting IT Business Ideas:
- Reusing software development tools for other industries/applications. For example: other than the obvious issue with binary file types, why don't architects and lawyers use a repository like SVN, or Monotone? ( I left out the ones you have to pay for :-) We've tossed this idea around wondering if anybody has tried to market repositories this way....
- Becoming a freeware software consultant for small businesses. You can get everything from a good OS to all the 'openOffice' applications for free, not to mention what Google and others are putting on line (word processors and spreadsheets, etc), and universally accessible too! Seems to me if you're good at training, you can really help small businesses save a buck on software by showing them how to use all the free stuff out there. It wouldn't surprise me if people are doing this one already.
- write a GOOD, scalable, secure, extensible front end framework for web applications. There's a tonne of them out there now, but it seems like every one of them (Ruby, JSF, Struts, Facelets, Tapestry, SpringMVC, etc) has a downside. I've had conversations with a number of knowledgeable architect types and they agree that there is no good front end web solution out there right now. So, one could code a good freeware framework, consult using it, and then sell it to Red Hat (like the JBoss consultants did).
Thursday, August 30, 2007
Nagios and things part 2
So I got to work this morning (with a fresh mind) and after a bit of googling figured out my problem in 30 minutes. Basically it boiled down to adding a reference to ORACLE_HOME in the bash script itself, and the full path of SQLPlus. After that, things ran fine even with changing the permissions of the scripts and related files back to normal and running Nagios with the Nagios user.
One of the 'nice to haves' for this little monitoring things I'm working on in Nagios is graphing capability - so we can see trends of the performance data at a glance. Just before I left today, I discovered that Nagios might install with this capability commented out. I let you know for sure in my next post.
While I was working on solving the graphing problem a different way, I got focused on some graphing addins for Nagios that required perl modules I didn't have. I learned that cspan is basically like 'yum' for perl. Didn't know what it was before today. In fact, I haven't had any exposure to perl before this project. It's all good - learning new stuff every day.
I also learned a big way to correct my golf swing today. I need to grip hard with my left hand and the right hand is just for support. I was driving much better and more consistently after I made that adjustment.
One of the 'nice to haves' for this little monitoring things I'm working on in Nagios is graphing capability - so we can see trends of the performance data at a glance. Just before I left today, I discovered that Nagios might install with this capability commented out. I let you know for sure in my next post.
While I was working on solving the graphing problem a different way, I got focused on some graphing addins for Nagios that required perl modules I didn't have. I learned that cspan is basically like 'yum' for perl. Didn't know what it was before today. In fact, I haven't had any exposure to perl before this project. It's all good - learning new stuff every day.
I also learned a big way to correct my golf swing today. I need to grip hard with my left hand and the right hand is just for support. I was driving much better and more consistently after I made that adjustment.
Monday, August 27, 2007
Oracle, Tibco, and tcpdump
I've started a new project recently that uses Tibco with an Oracle backend. It still amazes me the little nuances of domain knowledge that are so key to being a productive developer on some of these 'platforms'.
My issue today was a query I was trying to run using the jdbc 'component' in Tibco. It's pretty finicky about SQL syntax. I needed aliases for column names and it turned out the only way to get those to work is to use double quotes. Single quotes work if you want to hardcode what you're returning in your select statement. Double quotes (I was told) are effectively ignored by Oracle unless you're trying to alias a column. I'm starting this blog mainly so I can keep these little nuances documented somewhere - I hope it'll help me remember in the future.
I ran into a different configuration problem with Tibco last week which I had to use tcpdump to help me solve. I used the command something like this: tcpdump -nnXi eth0 -port 8080. This helped me realized that Tibco was responding to http GET and not http POST requests.
My issue today was a query I was trying to run using the jdbc 'component' in Tibco. It's pretty finicky about SQL syntax. I needed aliases for column names and it turned out the only way to get those to work is to use double quotes. Single quotes work if you want to hardcode what you're returning in your select statement. Double quotes (I was told) are effectively ignored by Oracle unless you're trying to alias a column. I'm starting this blog mainly so I can keep these little nuances documented somewhere - I hope it'll help me remember in the future.
I ran into a different configuration problem with Tibco last week which I had to use tcpdump to help me solve. I used the command something like this: tcpdump -nnXi eth0 -port 8080. This helped me realized that Tibco was responding to http GET and not http POST requests.
Subscribe to:
Posts (Atom)