Wednesday, August 7, 2013

[how to] Choosing A PostgreSQL Authentication Method For A Large Course

[how to] Choosing A PostgreSQL Authentication Method For A Large Course

Choosing A PostgreSQL Authentication Method For A Large Course

Posted: 07 Aug 2013 08:22 PM PDT

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone.

I would like students to be able to do the following, and (of course) have their accounts reasonably secure from attack:

  • Use psql on the same machine where the database server runs.
  • Use pgAdmin on their local (probably Windows-based) machine.
  • Write JDBC applications that run from their local machine.
  • Write JDBC webapps that run through apache on the same machine where the database server runs.

There are many authentication methods available (see http://www.postgresql.org/docs/9.2/static/auth-methods.html), but none seem to meet all of my requirements well.

  • Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the peer mechanism.
  • Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
  • Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option A is what I have always used myself, and it would be my preference. But I believe that it would rule out accessing the database from any client other than psql running on the same machine.

Option B seems to be the most flexible. But it also seems terribly ugly for students to need to set and maintain passwords in two disparate systems.

Option C would only allow connecting from clients on remote machines, which is not really acceptable.

I am fairly unfamiliar with GSSAPI / Kerberos, but it does not really sound like what I want either. My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running. Is there some better option for my requirements than B above?

Conversion failed when converting date and/or time from character string

Posted: 07 Aug 2013 09:08 PM PDT

I am getting this error:

Conversion failed when converting date and/or time from character string.

DECLARE @fdate DATE         ,@tdate DATE    SET @fdate = CONVERT(DATETIME, @fromdate, 103);    SET @tdate = CONVERT(DATETIME, @todate, 103);    WITH cSequence  AS (      SELECT @fdate AS StartRange          ,@fdate AS EndRange          ,users.userid      FROM Users      -- WHERE Users.UserId = 54         UNION ALL      SELECT StartRange          ,DATEADD(dd, 1, EndRange)          ,UserId      FROM cSequence cq      WHERE EndRange < @tdate      )  SELECT DISTINCT EndRange      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN s.Description                  --when datepart(DW,c.EndRange) =  1 then 'Sunday'           ELSE 'Not Entered'          END AS DailyUpdate      ,CASE           WHEN CONVERT(DATE, s.reportdate, 103) = CONVERT(DATE, c.endrange, 103)              THEN convert(TIME, s.FromTime, 103)          ELSE convert(TIME, '00:00:00')          END AS FromTime              ,FullName      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN convert(TIME, s.ToTime, 103)          ELSE convert(TIME, '00:00:00')          END AS Totime  FROM cSequence c  LEFT JOIN UserDailyReportDtl s ON c.UserId = s.UserID  --AND CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)      LEFT JOIN users u ON s.UserID = u.UserId  WHERE NOT EXISTS (          SELECT 1          FROM UserDailyReportDtl          WHERE UserDailyReportDtl.UserId = c.UserId              AND CONVERT(DATETIME, UserDailyReportDtl.reportdate, 103) = CONVERT(DATETIME, c.EndRange, 103)          )      AND CONVERT(DATE, s.reportdate, 103) BETWEEN @fdate          AND @tdate      AND @Username = FullName  UNION ALL  SELECT DISTINCT EndRange      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN s.Description                  -- when datepart(DW,c.EndRange) = 1 then 'Sunday'           ELSE 'Not Entered'          END AS DailyUpdate      ,CASE           WHEN CONVERT(DATE, s.reportdate, 103) = CONVERT(DATE, c.endrange, 103)              THEN convert(TIME, s.FromTime, 103)          ELSE convert(TIME, '00:00:00')          END AS FromTime      ,FullName      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN convert(TIME, s.ToTime, 103)          ELSE convert(TIME, '00:00:00')          END AS Totime  FROM cSequence c  LEFT JOIN UserDailyReportDtl s ON c.UserId = s.UserID  AND CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)  LEFT JOIN users u ON s.UserID = u.UserId  WHERE CONVERT(DATE, s.reportdate, 103) BETWEEN @fdate          AND @tdate              --AND CONVERT(DATETIME, s.reportdate, 103)   is not null        AND @Username = FullName    ORDER BY c.endrange DESC

Administering PostgreSQL For Database Course [on hold]

Posted: 07 Aug 2013 06:13 PM PDT

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone. There are a few different aspects of administration that I know I need to think about (but am also interested in others I may be overlooking). Apologies if this would have been better as five distinct questions, but they all seem interrelated.

Aspect #1: Organization of databases

I can see several different ways to possibly partition our data:

  • Option A): Each student gets their own cluster to work in and creates a database per project.
  • Option B): Each student gets their own database within a shared cluster, and creates a schema per project.
  • Option C): Each students gets their own schema in a shared database.

Option A seems like it would be a management headache and require running many instances of the server, while option C would cause namespace clashes between projects. Option B has no downsides that I am aware of, so that's what I believe I should go with. Agreed?

Aspect #2: Client software

There are several ways that students could access the server:

  • Option A): SSH to the same machine where the server is running and run their own instances of psql.
  • Option B): Run pgAdmin on their local (likely Windows) machine.

Option A is the only one I have any personal experience with. Option B might be easier because I will not have to worry about teaching them to use UNIX at all. But the pgAdmin interface is enormously complex, exposing details that I would not like students to worry about before they have mastered the basics, and would allow them to things with point-and-click rather than mastering SQL. I think I would like to use option A initially and introduce option B later in the course as an alternative. Reasonable?

Aspect #3: Users, roles, & authentication

There are several ways the system could authenticate users:

  • Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the ident mechanism.
  • Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
  • Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option C does not seem like a good choice, because I would like students to be able to write database-driven webapps in ~/public_html on the same machine that runs the database server, and that would seem to require user accounts. Option B seems ugly, because students would need to set passwords in two different systems. Option A seems cleanest (and is most familiar to me), but it seems to me like it would be incompatible with Aspect #2 Option B. And I am not sure this would allow the writing of webapps either, since apache would not be running as the file's owner. So option B looks like the least bad alternative. Agreed?

Aspect #4: Querying student-owned objects

Once students have done some work, I need to be able to view it (hopefully in an automated way, since I will have 40+ students in the course). I could log in as the superuser role to do this, but being a superuser unnecessarily seems like a bad idea. I can grant myself CONNECT privileges to the students' databases after I create them (for aspect #1 option B, adjusted as necessary for other options). But once students create tables I will not have SELECT privileges unless the student specifically gives them to me. Is there some way that I can get that privilege (and only that privilege) automatically granted to my non-superuser role on all objects that will later be created? If not, are there any other, better alternatives to logging is as a superuser.

Aspect #5: Protecting my disk space

Since many students will be sharing one system with constrained resources, I need a way to protect it from malicious or accidental havoc. There does not appear to be any way to limit database size within PostgreSQL. I thought about creating a tablespace per user in their home directories (this rules out aspect #3 option C) and using the OS's disk quotas. But it looks like this would not work because the files would still be owned by postgres rather than the individual user. Is there any good solution for this?

Oracle won't start

Posted: 07 Aug 2013 03:28 PM PDT

While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error:

SQL> Connected to an idle instance.  SQL> ORA-01081: cannot start already-running ORACLE - shut it down first  SQL> Disconnected

Also when trying to start SQL Plus manually AS SYSDBA I get:

ERROR:  ORA-12162: TNS:net service name is incorrectly specified

Using SQL Plus with other users, I get:

ERROR:  ORA-12537: TNS:connection closed

Any help appreciated ...

Best practice for upgrading mysql on a master-master setup

Posted: 07 Aug 2013 02:03 PM PDT

Anyone have experience upgrading a master-master setup of Mysql 5.1 to a release version of Mysql while trying to keep at least one server online (in my case we are upgrading to MariaDB 5.5).

Mysql ON DUPLICATE KEY UPDATE does not work with specific key

Posted: 07 Aug 2013 01:09 PM PDT

While, my query works on almost all entries, it does not work with one particular key.

"INSERT INTO highscores (uuid, name, score) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE score = VALUES(score)"

If I remove the ON DUPLICATE KEY part, both keys throw error that they already exist.

"SELECT * FROM highscores"  a0c0abfe-e859-33d2-8075-a5532417a09d Tomas 408  a0c0abfe-e859-33d2-ffdf-591e2e27509d John 48

Before and after query:

Saving score: a0c0abfe-e859-33d2-8075-a5532417a09d, Tomas, 423  Saved score:  a0c0abfe-e859-33d2-8075-a5532417a09d, Tomas, 408    Saving score: a0c0abfe-e859-33d2-ffdf-591e2e27509d, John, 75  Saved score:  a0c0abfe-e859-33d2-ffdf-591e2e27509d, John, 75

Table layout:

CREATE TABLE highscores (UUID BINARY(16) NOT NULL,                           NAME VARCHAR(64),                           SCORE INT,                           PRIMARY KEY (UUID));

Do you have an idea what could cause this ?

Engine:mysql(5.6.12)

Why set up static data in views vs. using tables in mysql?

Posted: 07 Aug 2013 12:45 PM PDT

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields.

I have the following too:

LDAPIMPORTANT - view that stores all 75K but only 15 fields

LDAPSHORT - view that stores all 75k but 5 fields

LDAPAB - view that only stores 9k employees based on two groups (field lookup)

Each of these are used a lot and for different apps and also there are a lot of views written against these views. But there is no updates to them. We do not update employee data. It is just LDAPALL update once a night.

In this circumstance should I create tables from the LDAPALL table instead of views? I could set up jobs to create these tables once a night. What is best practice behind this? Speak in layman's terms because I am a PHP developer made to do all DB admin stuff.

How to delete duplicate records

Posted: 07 Aug 2013 10:58 AM PDT

I'm able to find duplicate records using the following query, however, I'm not sure how to delete duplicates records, and ignoring any records that starts with 0 in starttime field.

SELECT `StartTime`, COUNT(`CallDetailRecordID`) AS cnt FROM `CallDetailRecord`  GROUP BY `StartTime` HAVING cnt > 1

Sample output 

 StartTime     cnt    -------------  --------          0        198  1340511506737         2  1340511958364         2  1340512141687         2  1340512191631         2  1340512244925         2   1340512670902         2

How to query a database for empty tables

Posted: 07 Aug 2013 05:40 PM PDT

Due to some 'developers' we had working on our system we have had issues with empty tables. We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't.

I would like to run a query the system tables to find what user tables are empty. We are using MS SQL 2008 R2.

Thanks for the help.

How to execute this procedure in PL/SQL?

Posted: 07 Aug 2013 12:49 PM PDT

I have this table in the below format: 

Persnbr | Userfieldcd | Value  01      | Port | Funds     01      | Vip1 | Systems    02      | Port | Bank    02      | Vip1 | Authority

This is how I want it: 

Persnbr | Port  | Vip1  01      | Funds | Systems     02      | Bank  | Authority

As I dont know the all the fields in the userfieldcd column, I am trying to dynamically pivot the table. So I am using this procedure but I dont know how to call it in PL/SQL developer. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production 

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)  as  sql_query varchar2(1000) := 'select persnbr ';    begin      for x in (select distinct userfieldcd from persuserfield order by 1)      loop          sql_query := sql_query ||              ' , min(case when userfieldcd = '''||x.userfieldcd||''' then value else null end) as '||x.userfieldcd;                dbms_output.put_line(sql_query);      end loop;        sql_query := sql_query || ' from persuserfield group by persnbr order by persnbr';      dbms_output.put_line(sql_query);        open p_cursor for sql_query;  end;  /

When I call the procedure using:

VARIABLE x REFCURSOR    BEGIN           dynamic_pivot_po(:x)        END        /

it gives me

ORA-00900: Invalid SQL statement.

Execution plan vs STATISTICS IO order

Posted: 07 Aug 2013 06:11 PM PDT

SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON?

The following query:

SET STATISTICS IO ON;    SELECT  *  FROM    Sales.SalesOrderHeader AS soh          JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID          JOIN Production.Product AS p ON sod.ProductID = p.ProductID;

Generates this plan:

Graphical execution plan

And this STATISTICS IO output:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 3, read-ahead reads 1277, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 1, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, I reiterate: what gives? Is there a meaningful ordering to STATISTICS IO output or is some arbitrary order used?

Indexing for query containing xml column

Posted: 07 Aug 2013 03:21 PM PDT

I have below queries which performs little slow.So i am planning to create index.I created primay xml index and now performance improved little bit(by looking at execution plan) .Queries are shown below. 

Update account set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Balance/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Balance)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;    Update account  set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Transaction/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Transaction)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

The table has below structure

CREATE TABLE [dbo].[account ](      [ID] [int] NULL,      [Type] [char](10) NULL,      [Date] [date] NULL,      [Balance] [decimal](15, 2) NULL,      [TRansaction] [decimal](15, 2) NULL,      [mybal] [decimal](15, 2) NULL,      [dailybalance] [xml] NULL,      [AutoIndex] [int] IDENTITY(1,1) NOT NULL,   CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED   (      [AutoIndex] ASC  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am using sql server 2008 r2 express.So i think i cannot use full text search. So please advice me how the index should be created so that above query performance is improved.

ROW wise SUM VS COLUMN wise SUM in MySQL

Posted: 07 Aug 2013 09:34 PM PDT

I have a table tableA with the following structure:

enter image description here

I modified this structure into tableB to reduce number of rows and the category is fixed length:

enter image description here

Assume I have 210k rows in tableA. After the transfer to the new structure, tableB contains only 70k rows.

In some cases I want to sum all the values in the table:

SELECT SUM(val) AS total FROM tableA;               -- 1  -- or  SELECT SUM(cate1+cate2+cate3) AS total FROM tableB; -- 2

Query 1 is executing faster than query 2. tableB contains less rows than tableA. I was expecting query 2 to be faster.

Why is query 2 slower than query 1?

SSIS package blocks itself if uses TRUNCATE

Posted: 07 Aug 2013 10:09 AM PDT

There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data:

enter image description here

Here the Delete block deletes everything from six tables, and the Parse block loads six files to the six tables.

If the Delete uses delete from dbo.table, then everything works fine, but is slow.
But if Delete uses truncate table dbo.table instead, the deletion takes no time, but the package becomes blocked when it comes to the Parse task. Would just sit around doing nothing.

At this moment on the server side I can see an SSIS spid being blocked by spid -2, which is "Orphaned distributed transaction." It would appear that used to represent the truncating connection. This connection hangs around, and the package is blocked by the locks it placed.

If I switch the only used SQL Server Connection manager to RetainSameConnection = True, then the package fails as soon as it enters the Parse task. The task asks the manager for a connection, and the manager responds with

[SQL Server Destination [471]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection Manager" failed with error code 0xC001A004. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Is there a way to set up the package so that it uses truncate and does not block itself?

How to build a database that contain only the delta from yesterday

Posted: 07 Aug 2013 06:32 PM PDT

I need to know what has been changed on my database since last night. Is it possible to extract this data from the LDF file and to build a new Database that contains the delta?

For example, let say I have a table for users and now, a new user was added and one of the users update his home address. I need to be able to build a new database that users table will contain two records 1. The new user (and to add a new column to know if it's new or update field) 2. The user that update his record (it will be nice to know which record has been update)?

BTW, I have to SQL servers that I can use (2008 and 2012)

Thanks In Advance

Simple XML .query SELECT statement times out on 1 SQL SERVER instance

Posted: 07 Aug 2013 07:44 PM PDT

I dropped and recreated a stored procedure on a DB on a SQL Server 2012 instance (SERVER1/INSTANCE1), after which the sproc started hanging on a line. Using profiler I have reduced a recreateable scenario on this SQL Server instance to the following code:

DECLARE @RequestXML xml = '<Foo>      <Node1>Hello</Node1>      <Node2>World</Node2>      <Node3>Today</Node3>  </Foo>'    SELECT      @RequestXML.query('/Foo/Node1[1]')      ,@RequestXML.query('/Foo/Node2[1]')      ,@RequestXML.query('/Foo/Node3[1]') -- comment out any of these lines so only one or two items are returned and the query works fine!

This statement hangs. If you comment out one of the returned columns in the SELECT statement, then the statement runs fine. So one of two returned columns is fine, but anything over is bad. If you take out the first node selector (i.e. the [1]) it also runs fine.

Here's the rub though:

  • the statement fails on any DB in this SQL Server 2012 instance (SERVER1/INSTANCE1).
  • the statement succeeds on any other SQL Server instance I have tried, including other instances running from the same SQL Server machine as the failing instance (e.g. SERVER1/INSTANCE2, SERVER1/INSTANCE3 etc.) - all of which are 2012 instances.

It seems to me that SERVER1/INSTANCE1 has borked the way it runs this statement. I have heard that SQL Server does things with optimising execution of statements, but my knowledge stops there. I'm sure there must be some way to get it to behave again, but how?

UPDATE

The following adjustment (getting the singleton of the Xpath result-set rather than potentially multiple first elements) solves the issue above for the SERVER1/INSTANCE1 issue. I would put this as the answer, except that I don't believe this is identifying the underyling problem (please correct me if I'm wrong!). Given that our codebase deployed on other servers implements the above statement (or derivatives of it) I don't want to update our entire shredding XML approach without good reason (or at least I would have to justify it to my peers).

DECLARE @RequestXML xml = '<Foo>      <Node1>Hello</Node1>      <Node2>World</Node2>      <Node3>Today</Node3>  </Foo>'    SELECT      @RequestXML.query('(/Foo/Node1)[1]')      ,@RequestXML.query('(/Foo/Node2)[1]')      ,@RequestXML.query('(/Foo/Node3)[1]')

Any help gratefully received.

Thanks,

Ali

Totals Column - Blank if Zero or NULL

Posted: 07 Aug 2013 08:48 PM PDT

A report currently computes a "totals" column like so:

,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +             ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0                         THEN ''        ELSE CONVERT(VARCHAR(15),              ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +             ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0))         END AS 'MealsTotal'

How can I get a blank cell when a total equals zero, instead of NULL or "0", and avoid doing the computation twice?

Can I force a user to use WITH NOLOCK?

Posted: 07 Aug 2013 10:37 AM PDT

Can I force a user's queries to always run with the hint NOLOCK? e.g. they type

select * from customer

But what is executed on the server is

select * from customer with (nolock)

This question is not:
About the various pros and cons of NOLOCK, respectfully. I know what they are, this is not the place to discuss them.

How to select from a table without including repeated column values?

Posted: 07 Aug 2013 10:36 AM PDT

In a previous question How to merge data sets without including redundant rows? I asked about filtering redundant historical data during import, but @DavidSpillett correctly replied that I couldn't do what I was trying to do.

Instead of filtering the table during import, I now want to create a view on the table that returns only records where the price has changed.

Here's the original scenario rephrased to suite this question:

We have a table of historical prices for items. The table contains rows where the same price is recorded for multiple dates. I want to create a view on this data which only shows price changes over time, so if a price changes from A to B I want to see it, but if it "changes" from B to B then I don't want to see it.

Example: if the price yesterday was $1, and the price today is $1, and there were no other price changes, then the price today can be inferred from the price yesterday so I only need the record from yesterday.

Example (http://sqlfiddle.com/#!3/c95ff/1):

Table data:    Effective            Product  Kind  Price  2013-04-23T00:23:00  1234     1     1.00  2013-04-24T00:24:00  1234     1     1.00 -- redundant, implied by record 1  2013-04-25T00:25:00  1234     1     1.50  2013-04-26T00:26:00  1234     1     2.00  2013-04-27T00:27:00  1234     1     2.00 -- redundant, implied by record 4  2013-04-28T00:28:00  1234     1     1.00 -- not redundant, price changed back to 1.00    Expected view data:    Effective            Product  Kind  Price  2013-04-23T00:23:00  1234     1     1.00  2013-04-25T00:25:00  1234     1     1.50  2013-04-26T00:26:00  1234     1     2.00  2013-04-28T00:28:00  1234     1     1.00

My initial attempt used ROW_NUMBER:

SELECT      Effective,      Product,      Kind,      Price  FROM  (      SELECT          History.*,          ROW_NUMBER() OVER          (              PARTITION BY                  Product,                  Kind,                  Price              ORDER BY                  Effective ASC          ) AS RowNumber      FROM History  ) H  WHERE RowNumber = 1  ORDER BY Effective

Which returned:

Effective               Product  Kind  Price  2013-04-23T00:23:00     1234     1     1.00                                               -- not 2013-04-24, good  2013-04-25T00:25:00     1234     1     1.50  2013-04-26T00:26:00     1234     1     2.00                                               -- not 2013-04-27, good                                               -- not 2013-04-28, bad

I tried searching for a similar question/answer but it's hard to work out how to phrase the search, an example is worth a lot of words.

Any suggestions appreciated. Thanks

Why does Log Shipping .TRN file copy just stop

Posted: 07 Aug 2013 01:59 PM PDT

I apologize in advance for a long post but I have had it up to here with this error of having to delete LS configuration and starting it over for any DB thats got this error.

I have LS setup on 3 win2k8r2 servers(pri,sec,monitor) with 100 databases transactions backed up and shipped from the primary to secondary and monitored by monitor. Back ups and copies are run every 15min and then the ones older than 24hrs are deleted. Some DBs are very active and some not so much but shipped regardless for uniformity sake(basically to make secondary server identical to primary). Some DBs are for SP2010 and majority for inhouse app.

The issue is that after all LS configs are setup, all works well for about 3 to 4 days then i go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status because the time since last copy is over 45min so no restore has occured. This seems random and the only errors i see is from an SP2010 DB(WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a reports db that gets created weekly and LS cannot just figure which the last copy to backup or to restore is. I posted here regarding that and i have yet to find a permanent solution. For my main error(time since last copy) i have not seen anything that could have caused that and i dont get any messages(even though some alert statuses have been ignored for 3 days). Anyway, I would really appreciate any input on understanding whats causing this and how i could fix it. Thanks.

oracle alter table move taking long time

Posted: 07 Aug 2013 10:55 AM PDT

I'm currently trying to compress a table in Oracle with the following statements

ALTER TABLE MYTABLE COMPRESS FOR OLTP;  ALTER TABLE MOVE;

My question is: the MOVE operation is taking forever and unfortunately I forgot to state the ONLINE clause.

If I cancel it, will I lose data? Or is there any way to find out how long it will take?

How to determine Oracle LOB storage footprint?

Posted: 07 Aug 2013 10:36 AM PDT

With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.

Timing is fairly easy to profile but what's the easiest way to get a reasonably accurate measurement of how much space a specific LOB column takes up?

Oracle schema import is not importing all the tables present in the schema dump file

Posted: 07 Aug 2013 09:55 AM PDT

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported.

Here are the export and import commands i have used.

Export Command:  ---------------  exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log     Import Command:  ---------------  imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y

The Oracle we are using is 10g EE.

What could be going wrong ? Can you please suggest a solution to this issue.

SQL Server update query on linked server causing remote scan

Posted: 07 Aug 2013 04:54 PM PDT

I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.

The following queries executes in less than 1 second:

   SELECT kg.IdGarment     FROM Products p      INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID      INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID      INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID      INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment      INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID      WHERE log.ActionType = 'I'

t_ProcessIT_Garment contains 37,000 rows, the query returns two records, the IdGarment column is the Primary Key. No problem here.

However, if I run this query to do a remote update, it takes 24 seconds, and 2 rows is affected:

   UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment SET      IdGarment = IdGarment     FROM Products p      INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID      INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID      INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID      INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment      INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID      WHERE log.ActionType = 'I' ;

I tested using IdGarment = IdGarment to keep things simple. The execution plan shows it uses Remote Query for the first query, but Remote Scan for the second query, which has 100% of the cost.

The table joins are identical in both queries, why is it using Remote Scan for the second query, and how do I fix this?

Difference between idx_tup_read and idx_tup_fetch on Postgres

Posted: 07 Aug 2013 08:35 PM PDT

On Postgres 8.4 when you do:

select * from pg_stat_all_indexes where relname = 'table_name';

It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?

User login error when trying to access secured SQL Server database

Posted: 07 Aug 2013 01:55 PM PDT

We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past".

One vendor package we use uses the built-in MS SQL Server security. Each module has three groups:

  • xxView = View Only
  • xxUser = Add, Update rows (cannot delete)
  • xxAdmin = Delete rows

So we can add a person to one of these groups an they get the appropriate access. I don't have the actual error message in front of me anymore, but it said that they are not authorized to table CriticalVendorTable. It worked before the rename. The admin removed the person from each group and re-added them. Still no go. I even restarted the server and it still doesn't work. My best guess is that there is UUID (or unique id) somewhere that is causing problems.

The vendor's response is to delete the user and then re-add them. I have only had time to do some brief searching, but I found this page; AD User SID Mis-mapping. Would this be worth trying? Would it be better to just delete the user and recreate them?

hstore for versioning fields in postgresql

Posted: 07 Aug 2013 08:55 PM PDT

I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table.

In order to track the changes done on each field I am thinking on create a table defined by:

CREATE TABLE fieldhistory(tableid int, fieldid int, id bigint,value hstore);  CREATE INDEX fieldhistory_index ON fieldhistory(tableid, fieldid, id);

I would expect the table to grow and grow, and retrieve from fieldhistory data only by tableid, fieldid and id.

When the tables are modified I would add a new record with something like:

SELECT upserthistory(1,1,1,'first update','115435','3');  SELECT upserthistory(1,1,1,'second update','115435','3');

where upserthistory is defined by :

CREATE OR REPLACE FUNCTION upserthistory(key1 INT, key2 INT, key3 BIGINT, data TEXT, theuser TEXT, whend TEXT) RETURNS VOID AS  $$  BEGIN      LOOP          -- first try to update the key          UPDATE fieldhistory SET value = value || ((whend||'.'||theuser) => data) WHERE tableid = key1 AND fieldid = key2 AND id = key3;          IF found THEN              RETURN;          END IF;          -- not there, so try to insert the key          -- if someone else inserts the same key concurrently,          -- we could get a unique-key failure          BEGIN              INSERT INTO fieldhistory(tableid,fieldid,id,value) VALUES (key1, key2, key3, hstore(array[whend||'.'||theuser,data]) );              RETURN;          EXCEPTION WHEN unique_violation THEN              -- Do nothing, and loop to try the UPDATE again.          END;      END LOOP;  END;  $$  LANGUAGE plpgsql;

Are there better approaches to accomplish this? the table would grow max to 225 millions of rows, or would it be better to have 50 tables of 4.5 millions of rows at max each? noting that the actual trace of each field will go to the hstore.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog