texts: Really slow DISTINCT ON query with multiple joins

texts:
Originally posted:
https://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres

The songs table has only about 4k rows, posts and stations have less.  Running the query without the DISTINCT ON fixes it.

Running Postgres on Mac OS X Lion.

Song Load (7358.2ms)

EXPLAIN (426.2ms)

EXPLAIN for:
SELECT  DISTINCT ON (songs.rank, songs.shared_id) songs.*,
        songs.*,
        posts.url as post_url,
        posts.excerpt as post_excerpt,
        stations.title as station_title,
        stations.slug as station_slug
FROM "songs"
    INNER JOIN "posts" ON "posts"."id" = "songs"."post_id"
    inner join stations on stations.blog_id = songs.blog_id
WHERE "songs"."processed" = 't'
  AND "songs"."working" = 't'
ORDER BY songs.rank desc
LIMIT 24 OFFSET 0

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=546147.28..546159.16 rows=24 width=2525)
   ->  Unique  (cost=546147.28..547360.75 rows=2452 width=2525)
         ->  Sort  (cost=546147.28..546551.77 rows=161796 width=2525)
               Sort Key: songs.rank, songs.shared_id
               ->  Hash Join  (cost=466.50..2906.84 rows=161796 width=2525)
                     Hash Cond: (songs.blog_id = stations.blog_id)
                     ->  Hash Join  (cost=249.41..587.52 rows=2452 width=2499)
                           Hash Cond: (songs.post_id = posts.id)
                           ->  Seq Scan on songs  (cost=0.00..304.39 rows=2452 width=2223)
                                 Filter: (processed AND working)
                           ->  Hash  (cost=230.85..230.85 rows=1485 width=280)
                                 ->  Seq Scan on posts  (cost=0.00..230.85 rows=1485 width=280)
                     ->  Hash  (cost=140.93..140.93 rows=6093 width=30)
                           ->  Seq Scan on stations  (cost=0.00..140.93 rows=6093 width=30)

I tried a few things... first index on (rank, shared_id). Then removed that and added an index on rank and shared_id separately, as well as combinations of the three... no luck.

Are the indexes not being used for some reason? Or do I need to do anything after adding an index to ensure they work?


texts: select data from table which dont have row in another table

texts:
I have 2 tables. First "product" (id). Second - "product_has_option_value" (product_id, option_value_id).

How can I get product ids which doesn't have some option_value_id (for example 10)? "product_has_option_value" has many to many relationships.

Ok. I think I found the solution:

select p.id
from product p
where p.id not in
  (select distinct(product_id)
   from product_has_option_value
   where option_value_id=543)


texts: Is there such a thing as result cache for Informix

texts:
Informix has SSC (SQL Statement Cache), but that doesn't seem to cache the result of the query.

Is there something else to activate to also cache the result ?

I am using Informix 12.1 on Redhat.


texts: Create Synonym error for a user-SQL Server

texts:
I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission.
When the user tried to create synonym as shown below.

USE [DEMODB]
GO
CREATE SYNONYM [dbo].[MYSYN] FOR [SERVER01].[DEMODB].[dbo].[CHILD]
GO
The user gets the below error message.
The specified schema name "dbo" either does not exist or you do not have permission to use it.

I checked the login/user settings.
And the login belongs to public server role and user is mapped to the database with default schema as [dbo]

And also create synonym permission is granted for that database for that user as mentioned earlier.

How can i fix this.


texts: Windows Azure Table Row Key strategy, what makes a useful RowKey?

texts:
I read somewhere that the row key should be useful - which is why auto numbers are not supported.  However, for all 3 tables I've started saving I can't think of anything better to save in the row key column besides a nice, neat auto number.

So let's discuss a specific case now.  I want to log the clients who access my service and simply track their IP address and the requested service and the date of the request.

No single piece of data is unique, and what point is there in creating a Key that blends multiple pieces of data into a single column?  I can run a string operation to extract the individual pieces when I get the data and append them when I save it.  This seems beyond dumb.  I could also create a guid for each entry, but that seems like a more difficult way to use an auto number.

What concept am I missing about saving my entities to a table that will match the RowKey requirement and be meaningful?

my entity looks like so:

public class AccessLog : TableEntity
{
    public AccessLog() { }

    public AccessLog(string partitionKey, string rowKey)
        : base(partitionKey, rowKey)
    {
        this.RowKey = rowKey;
        this.PartitionKey = partitionKey;
    }
    public string ServiceCalled { get; set; }
    public string UserAccount { get; set; }
    public string IpAddress { get; set; }
}

Edit

There is not a correct tag in the list of tags.  This is for a
Microsoft Azure -> Storage Account -> Table object.

The question is really a general question.  I have gone with using a GUID, but I don't like it.  Timestamp is an idea, but there is already a timestamp column auto generated by the Azure Table mechanics and who knows if 2 people would at any point in time over years have their request processed at the same millisecond.

At the end of the day I think the concept of a row key I found is no more than an ideal which simply doesn't exist in every practical setting nicely.


texts: After successful installation, Is it possible create a database without DBCA?

texts:
Apologies, for I am not familiar with the Oracle Database environment. Please help me to learn. :)

As the title states, can someone please provide the steps required to create a database manually through SQL, without using DBCA? Any links or posts are welcome.

Thanks.


texts: Extracting node names by looping through xml in Oracle 10G Plsql

texts:
I have an XML that looks like below,

<email_details>
        <VENDOR_ID>73</VENDOR_ID>
        <RGP_NUMBER>1234</RGP_NUMBER>
        .....
        ..... (any number of Child Elements)
    </email_details>

The '...' means that there can be other child nodes inside  in future.

I have a requirement in which I have to loop through and extract the "node names" and value from all these child nodes in Oracle 10G.

In 11G,

Select xmltype('<ROOT>2<C>3</C><D>4</D></ROOT>').extract('ROOT/A/*[2]').getrootelement() From dual;

and

SELECT *
    FROM XMLTable('ROOT/A/*'
           passing xmltype('<ROOT>2<C>3</C><D>4</D></ROOT>')
           columns node_name  varchar2(30) path 'name(.)'
                 , node_value varchar2(30) path '.'
        )
    ;

gives me the solution but it is not working in 10G. Please help.


texts: Difference between chained and migrated rows

texts: What is the difference between chained and migrated rows? Also what query should be used to display these rows? (I'm using Oracle 11gR2 database)


texts:
I have following tables:

A room I want to lease (has user.id as ForeignKey)
A users table for users who can rent
it
An availability table for time periods the room is to use with
prices for that specific period
A transaction table with check-in
and out dates (has member.id and room.id as ForeignKey
A billing table

How will I handle the billing?

Billing so far:


texts: What's the most efficient way to safely convert from Datetime2 back to Datetime

texts:
I've got an etl project where the source has 60 datetime2 columns, and the target is entirely datetime.
A few days ago, we woke up to an empty data warehouse because one user at one hospital, for one patient, entered a surgery date of '1220-01-01'.
I temporarily got around the issue by couching each of the columns in a custom function:
    ALTER function [dbo].[scrub_datetime2](@date datetime2)
    returns date
    as
    begin

    declare @return date
    set @return = case when @date <= '1800-01-01' then null else @date end
    return @return

    end

And this works ok for getting rid of the red text, but It also doubled the time the job takes to run. I'm happy to do my part, keeping the hamsters in the server room warm through christmas, but I can't really justify losing 2.5 hours of precious overnight time to this issue.
Question How would most efficiently check that a datetime2 value will fit in datetime?


texts: how to set mogodb in upstart service with authentication in mongodb way

texts:
I set username and password in MongoDB every time I have to run this command in terminal:

sudo mongod --auth --port 27017 --dbpath /var/lib/mongodb

As soon as I close terminal, MongoDB db also closes so how will I set MongoDB in startup service?


texts: Is LocalDB installed independently of SQL Server?

texts:
I am a Developer.  I thought LocalDB was installed as a component of Visual Studio, which could be used for development purposes.  However, it appears to be installed independently of Visual Studio.

Is it installed as part of SQL Server; SQL Data Tools or both?


texts: Anomalous Updates in Normalized Database


texts: How to run DB Admin Tool (GUI) in IBM DB2 Express-C?

texts:
I had installed IBM DB2 Express-C in Windows but I can't find any shortcut to launch the Administration Tools (GUI). When installing, I had selected "Administration Tool" option (I had checked twice to make sure).

This is the only shortcuts available from Start Menu:

So how to run the Administration Tools for IBM DB2 Express-C?


texts: MySQL - Delete older Records from a huge table

texts:
I have some huge tables which have around 10years data, and each table has approx 10000000+ rows.

Basically, I want to remove older than 2 years data and make a copy of deleted data in a separate table(I'll export the separate table to CSV and drop it later)

I found this great article: Archive huge record

I want the same thing, but I don't have an ID column in my tables.

So the process should like this,

Insert a single row in my separate table, then delete the row from the main table; commit.

Then, repeat the same step.

Its my main production table and its always busy, so no locks, blocks, and deadlock.

Can anyone suggest a better approach for this?

I have already asked a question about archival, but here I don't want to partition the table.


texts: CREATE DATABASE Permission denied in database 'master' error

texts:
I'm working with a tool that as one of the automated steps will attach databases from my file system to my local SQL Express 2008 instance. The tool requires a login in the sysadmin server role, for which I've created a new dummy login for testing. When I log into Management Studio with this new login and run the exact query to attach the DBs, the DBs are attached fine. However, when I run the tool (its an executable program) I am able to turn on the tool's debug panel to see the following error:

  CREATE DATABASE Permission denied in database 'master'

The T-SQL being executed via the program is:

USE [master]
GO
CREATE DATABASE [BBB_core] ON
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.core.Mdf' ),
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.core.Ldf' )
FOR ATTACH
GO

USE [master]
GO
CREATE DATABASE [BBB_master] ON
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.master.Mdf' ),
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.master.Ldf' )
FOR ATTACH
GO

USE [master]
GO
CREATE DATABASE [BBB_web] ON
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.web.Mdf' ),
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.web.Ldf' )
FOR ATTACH
GO

My thought here is that my Windows user somehow cannot execute this query on the master database. I do have my local Windows user (a domain user) in the logins on my SQL instance and its a member of sysadmins. I've also tried running the program as administrator (right-click > Run as administrator). I know the query works, it just doesn't seem to work in the context of the program. Am I missing something?

I'm using SQL Express 2008 on Windows 7 x64

Thanks in advance for any help.


texts: How to check a database for a specific page

texts:
I am working on repairing a database, I have about 80 errors that I need to fix and there seem to be three types of problems that I don't know how to fix other than doing a REPAIR_WITH_DATA_LOSS.

I do have a previous backup which does not have issues, and I have read about doing page level restores. However in my attempts to do that so far I have been unsuccessful using SSMS and TSQL. Is it possible to query the "good" database to see if it contains these pages? For example can I run something to look for page "1:10429855"?

Also is there a way to see what that same page is associated to or is on the "bad" database?

  Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID
  -4342159331760996352 (type Unknown), page (31205:-867600381). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.

  Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID
  6691734499630645248 (type Unknown), page (23723:203812287). Test
  (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -11.

  Table error: Object ID 1518653945, index ID 1, partition ID
  72057594166444032, alloc unit ID 71875645566156800 (type LOB data).
  The off-row data node at page (1:10429953), slot 0, text ID
  954357841920 is referenced by page (1:10429855), slot 4, but was not
  seen in the scan.

  Object ID 1518653945, index ID 1, partition ID 72057594166444032,
  alloc unit ID 72057594253410304 (type In-row data): Errors found in
  off-row data with ID 954358824960 owned by data record identified by
  RID = (1:10488687:10)


texts: Strip longest common suffix and concat in Postgres

texts:
Given a table t:

id | name
------------
1  | abcfug
1  | deffug
1  | hijfug
2  | etc

How can I do something like:

select string_agg(strip_lcs(name), ', ') from t where id = 1

returning:

abc, def, hij

NB I wrote an aggregate function to return lcs if that helps:

CREATE FUNCTION lcs_iterate(_state TEXT, _value TEXT)
RETURNS TEXT
AS
$$
        SELECT  RIGHT($2, s - 1)
        FROM    generate_series(1, LEAST(LENGTH($1), LENGTH($2))) s
        WHERE   RIGHT($1, s) <> RIGHT($2, s)
        UNION ALL
        SELECT  LEAST($1, $2)
        LIMIT 1;
$$
LANGUAGE 'sql';

CREATE AGGREGATE lcs(TEXT) (SFUNC = lcs_iterate, STYPE = TEXT);


texts: Reading CSV files located on LINUX server and updating the tables in SQL Server Database

texts:
I was wondering how do we ingest CSV files located on a Red Hat LINUX server into SQL Server Database Table.

I know we can write a Stored Procedure/Bulk Insert to read the files that are located on the same Windows Server as SQL Server and update the database but not sure how to do it when the files are present in LINUX.

Any help would be greatly appreciated


texts: It seems like Oracle silently restarts my procedure

texts:
I write a procedure to construct a report on OLAP Oracle server.
It works for some time.
Also after every operation I call autonomous_transaction log procedure to log my steps.
I dont use exception handling, so if something gone wrong, the procedure will failed.

But sometimes I see that in the middle of procedure it runs again! There no other processes which can run/rerun my procedure.
But other processes my drop/truncate/drop partitions.

Is it possible that Oracle restarts my procedure again absolutely SILENTLY - with no exception raising?


texts: Correct way to uninstall SQL Server 2008 ( or above )

texts:
Since University I've been facing this problem...

How do I uninstall SQL Server?

What I've done is delete everything in "control panel" related to SQL Server.

But, is there a right way to uninstall everything?


texts: mysql query performance... got to be a better way

texts:
I have a query right now that for a lack of a better word, sucks.  I'm looking for some help with respect to other ways to approach writing this query.  Here are the details.

I've got a temp table build up with the following columns:

AssignmentSubType
AssignmentValue
OwnerUsergroupID
AssociatedObjectID

The basic gist of what I want to do is to find AssociatedObjectIDs that have the same AssignmentSubType, AssignmentValue, and OwnerUsergroupID.  This is because there is a ton of SQL down the road that really doesn't need to be run if I've got "duplicates" (same AssignmentSubType, AssignmentValue, and OwnerUsergroupID).  Once I've got my list of duplicates I run the next set of SQL stuff on one of the duplicates , when I've got back info from that I join back the rest of the duplicates for the final result set.

Example table data:

AssignmentSubType | AssignmentValue | OwnerUsergroupID | AssociatedObjectID
retailer          | Dicks           | 1                | 5
retailer          | Dicks           | 1                | 7
retailer          | Dicks           | 1                | 9

In this example I just want to do the calculations on associatedobjecid = 5 since 7 and 9 will be the exact same, and then join back in 7 and 9 at the 'end'

I've got the following query that works but is very inefficient

SELECT firstObject, f2.AssociatedObjectID
            FROM (
                SELECT firstObject, dupAss AS dups
                FROM (
                    SELECT min(AssociatedObjectID) AS firstObject, group_concat(concat('-',AssociatedObjectID,'-')) AS dupAss
                    FROM (
                        SELECT * FROM tableFromAbove
                    ) innerRes GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID
                ) outR
            ) outR2
            LEFT JOIN tableFromAbove f2
                ON outR2.dups LIKE concat('%-',f2.AssociatedObjectID,'-%') ORDER BY firstObject

this query will give me back a result set like the following

firstObject | AssociatedObjectID
5           | 7
5           | 9

like I said earlier in this post... I then use this result set to join back to the results from the rest of the queries for associatedobjectid = 5.  Any thoughts on how I could restructure this so that it is more efficient?


texts:
Looking for missing indexes on my distributor databases, I surprisingly find too many.
the tables that need to be indexed are generally 3:

MSrepl_commands
MSlogreader_history
MSdistribution_history

Here is the picture of the missing indexes

Now concentrating on TABLE MSdistribution_history
It all seems to boil down to these 3 indexes:
USE distribution_BOCSS;
CREATE NONCLUSTERED INDEX
IDX_agent_id_time_xact_seqnorunstatus_INC_delivery_latency
ON [distribution_BOCSS].[dbo].[MSdistribution_history]
 ([agent_id], [time], [xact_seqno], [runstatus])
 INCLUDE (delivery_latency,start_time,timestamp) WITH (ONLINE = ON)

USE distribution_BOCSS;
CREATE NONCLUSTERED INDEX IDX_runstatus_INC_agent_id_delivery_latency
ON [distribution_BOCSS].[dbo].[MSdistribution_history]
([runstatus])
INCLUDE (agent_id,delivery_latency,time,xact_seqno,timestamp)
WITH (ONLINE = ON)

    USE distribution_BOCSS;
    CREATE NONCLUSTERED INDEX IDX_agent_idrunstatus_INC_time_xact_seqno_delivery_latency
    ON [distribution_BOCSS].[dbo].[MSdistribution_history]
([agent_id], [runstatus])
    INCLUDE (time,xact_seqno,delivery_latency,timestamp,start_time)
     WITH (ONLINE = ON)

However, as I would imagine, the amount of updates on the current existing clustered index is very high (not sure since when it has been adding up to these stats, possibly since the last reboot)
as you can see on the picture below:

The writes are too high, it is a production system, I would like to have more support regarding this. Would I really benefit by creating these indexes?
Is there any documentation that supports creating indexes on the distributor databases of transactional replication?
Why Microsoft has not supplied these databases with the proper indexes on them?


texts:
I have a column with two values in my database, I want to retrieve the two values alternately, for example I have:

name   itemcode
----------
 A       1
 B       2
 C       2
 D       2
 E       1
 F       1

I want to display them as:

name itemcode
----------
A      1
B      2
E      1
C      2
F      1
D      2


texts:
I have to update a complex query that takes JSON fields:

SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType
from
SELECT context->>'Source' as Product,
       context->>'SourceType' as Producttype ....

It uses the Product field at the bottom to search on.

But the original query uses

SELECT context::JSON, r.rboundaryid ...

and this doesn't work, giving a JSON error:

 invalid input syntax for type json

The 'context' field in the table is OK JSON as verified by Jsonlint.

I tried changing to

 SELECT to_json(context) as jcontext ...

changing the top ->> constructs to use the 'jcontext' name ... and this
eliminates the error ... but only because the result is NULL, not a JSON object ...

Any ideas on this?


texts: Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?

texts: I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ...; or DROP VIEW ...; CREATE VIEW...; in a transaction take out an ACCESS EXCLUSIVE lock similar to DDL against a table? Would all queries issued before the DDL have to complete before the DDL could execute? Would queries issued after the DDL block until the DDL is complete?


texts: Design database where each field needs to have a checkbox

texts:
I got into a problem designing a database where I have a table, where every field needs to be validated by a human.
for example:

name
number
ID card number

Each one of these fields need to be validated (kind of a checkbox).
which one is the correct approach here? have boolean fields in the same tablet or a different table with each field as boolean?


texts: Calling a .NET method in a SQL Server script

texts:
In the data migration process, I need to convert some columns values form the source system before writing to the target table.

I've been provided with a Csharp code which reads a string and do the conversions (it is a complicated string manipulations) and then returns the converted string.

What would be the best way to plug this code in the data migration SQL script? I know little about CSahrp programming, and after researching the different sites, found out this can be done by either adding a script component in a SSIS package, or wrapping the .net code in a CLR function which can be called from SQL Server.

As my script is not part of a SSIS package, my preference would be defining a CLR function, but would be ok? What would be the pros and cons of each options?

Also do you think this goal can be achieved using another technique which is easier to do and maintain?

If you know a good link which shows me the process step by step, please share it with me :)

Thank you.


texts: T-SQL Merge not updating in explicit transaction

texts:
We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the inventory.gtin table but the update statement below it works and data is updated in the import.file_data table when the stored procedure is called in a batch job. But, when the stored procedure is executed manually, the merge works fine and updates/inserts data. Is this a bug in the merge statement?

DECLARE @targetVendorCode nvarchar(20)
DECLARE gtin_cursor CURSOR FOR

        SELECT DISTINCT aVendorCode
        FROM #fileData fd

    OPEN gtin_cursor
    FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRANSACTION;

    MERGE [inventory].[gtin] AS target
    USING (SELECT * FROM #fileData WHERE ngtin IS NOT NULL AND aVendorCode = @targetVendorCode ) AS source
    ON (target.[ngtin] = source.[ngtin])
    WHEN MATCHED AND ( ( source.createDateTime > COALESCE(target.fileCreateDateTime,'04/02/1982') ) OR source.[status] = 'override' ) THEN
        UPDATE SET
        [skuId] = source.[skuId],
        [lastUpdateFileId] = source.[fileId],
        [vendorCode] = COALESCE(source.[aVendorCode],source.[vendorCode]),
        [lastUpdateDateTime] = source.[lastUpdateDateTime],
        [fileCreateDateTime]= source.createDateTime
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (
                [skuId],
                [lastUpdateFileId],
                [vendorCode],
                [lastUpdateDateTime],
                [fileCreateDateTime]
                )
        VALUES (
                source.[skuId],
                source.[fileId],
                COALESCE(source.[aVendorCode],source.[vendorCode]),
                source.[lastUpdateDateTime],
                source.createDateTime
                )
            ;
        SELECT @action = '[{"type":"distribute","actionDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","insertDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","referenceId":' + TRY_CAST(@fileId AS NVARCHAR(20)) + '}]'

        UPDATE fd SET [status] = 'processed',[modifiedDateTime] = GETUTCDATE(),[modifiedUserName] = SYSTEM_USER FROM [import].[file_data] fd WHERE fileId = @fileId

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
    END

    FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
    END

    CLOSE gtin_cursor
    DEALLOCATE gtin_cursor


texts: Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server

texts:
This is driving me bananas.
According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and in the second it takes 35 SECONDS.
Here's the details.
I created test table named TEST with only two columns - LastName and FirstName.  It's a heap with no indexes of any kind.  It has exactly 1 Million rows and no nulls.
(I run DBCC DROPCLEANBUFFERS before each query)
When I execute "SELECT FirstName FROM Test WHERE LastName = 'Smith'" the execution plan shows a Table Scan and Statistics IO shows the total logical reads as 25,923.  The query takes less than 1 second to execute.
Then, I execute "SELECT * FROM Test" and again the execution plan shows a Table Scan and Statistics IO shows logical reads of 25,923.  BUT, this query takes 35 seconds to complete.
I've tried reversing the order of the queries, restarting the whole server in between queries, etc.  But, the results are always the same.
Unless those execution plans or statistics are lying in one of the cases, it makes no sense that there should be a 35x times difference.
How can I figure out what accounts for this difference?


texts: Database Design challenge: One (Table)-to-Many (Tables) Relationship?

texts:
I am rather new to DBs, and after days of being stuck, researching and trying different solutions, I turn to this community's expertise for help.

I am trying to have one Person table in my MSSQL DB, that would contain all our contacts. Keeping all in one table facilitates a lot of things for us downstream.
Challenge is we have to split the Company table into different tables, by categories, as we need different attributes for each - e.g. Fuel Supplier, Engine Manufacturer and Maintenance Company would each have their own table, listing companies in said category, with their own specific attributes.

Therefore, I would need a "one table to many tables" relationship where the various Company tables feed into only one attribute ("Company") in the Person table. Goal being that within a Person record, one could choose the Company that person works for - only one company, from across multiple tables.

So ideally something like that:

Is there a way to achieve this or any workaround?

Thanks for your help!


texts: Why does "DB2 Recovery Expert" web console doesn't enable the Run and Next button?

texts:
Sometimes, when performing the Log Analysis and Recovery options in the Recovery Expert web console, the buttons "Next" and/or "Run" aren't enabled.
This is strange because we do provide all the optins requested to proceed with the operation. I am using DB2 R.E. 5.1, so I'm not sure about other versions have the same issue.


texts: Does restoring a database break external synonyms targeting objects in that database?

texts:
If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms?

To be specific, imagine this situation:

Database Synonym_Targ on a SQL 2008 R2 server has some database objects in it, e.g. a table called dbo.foo
Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ.
A backup of the database normally resident in Synonym_Targ is restored into it.  This contains an object dbo.foo.

Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?


texts: mysql restore specific date data from a dump file

texts:
I have mysql [version 5.6] database dump file. I want to restore only one day data from that dump file.

Anyone has any clue ?


texts: db_datawriter and db_ddladmin on a server-level?

texts:
Ok, since SQL Server 2016 we have new server-level permissions that allow to grant connect and select to/on any exising or future databases on the server instance
use master

grant connect any database to [Login]
grant select all user securables to [Login]

Tried to run below command:
grant insert all user securables to [Login]

But it throws an error
Is there any permission or role, or other method that grants permission to insert, update, delete (db_datawriter), or create objects inside user databases (db_ddladmin) on a server level - so it applies to all existing and future user databases ?
So DBA does not have to go in and update permissions whenever new database is added ?


texts: Is it possible to run an .exe file from a CLR function?

texts:
Is it possible to run an .exe from CLR function with Sybase SQL Anywhere 12?

If yes then I thought about link this function and .exe with pipes, is it a good idea or do you have better ideas? If not then thanks for your time.


texts: SSIS packages fail with Pending Execution Status

texts:
My packages have been running on our production environment for a couple of weeks until they started failing with very little information apart from packages having a pending execution status.

All packages fail regardless of the user executing them e.g. myself or the SQL Agent service account.

There is a cryptic error message in the windows log about activation permissions for the SQL Integration Services. Setting permissions for the accounts calling the package makes no difference,

I managed to get them working by repairing the SQL Server installation I couldn't see what repairs it had made.

Now following a reboot of the server all packages refuse to run. I have managed to get them to work by repairing the SQL Server installation again. This is obviously not a scalable solution.

I suspect that some group policy setting is overriding a local security setting and this is why it happens after some time or after a reboot.

Has anybody come across this issue before or have any ideas where to look.


texts:
I have never worked on SQL Server 2000 and I got a requirement to check capture the following information to one particular database.

DBName  CurrentDate SPID    BatchDuration   ApplicationName HostName    LoginName   SQLQuery

I tried the query below but it in first place did not return any information or does not meet the requirement as it does not capture the ApplicationName and SQL Text.

SELECT
    DB_NAME(dbid) as dbName
,   CURRENT_TIMESTAMP as CurrentDate
,   P.spid
,   right(convert(varchar,
        dateadd(ms, datediff(ms, P.last_batch, getdate()), '2000-01-01'),
        121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
,   P.cmd
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                ,'MIRROR HANDLER'
                ,'LAZY WRITER'
                ,'CHECKPOINT SLEEP'
                ,'RA MANAGER')
and DB_NAME(dbid) ='master'
order by batch_duration desc

Also when I try to setup this script via SQL Agent it only allows to run it every one minutes interval. Is there any other better way to run it like every 5 seconds? or maybe I should be using another method instead e.g. capturing the logins with required information via trigger instead?

Any input will be appreciated.


texts: MySQL event scheduler -- Event running at wrong time

texts:
I have created a event to run EVERY 1 DAY at 3:15 (am). I know that timezones affects events and actually I create a query to check the START time in mysql.event (GMT timezone) and information_schema.event (Local system timezone/EST for this example).

As you can see in the image below, my event is supposed to run every day at 7:15 GMT or 3:15 EST but following the two last columns %lastExecuted% you can see tha actually it ran 4 hours and 15 minutes later than expected.

This is the creation code:

CREATE DEFINER="vpladmin"@"%" EVENT "call_biz_review_lookups" ON SCHEDULE EVERY 1 DAY STARTS '2019-05-03 03:15:00' ON COMPLETION PRESERVE ENABLE DO BEGIN

INSERT INTO.....

END

sql_mode='ANSI_QUOTES,STRICT_TRANS_TABLES'


texts: CTE where to place the where clause to filter rows sooner (in postgresql)?

texts:
I have function with CTE which looks like this:

function param(p_userId, p_templateId)

    with a(select...),
         b (select..),
         c (insert..returning..),
         d (update..returning..),
         ...k(select..)
    insert ...
    select * from k
    where  userId = p_userId
    and    templateId = p_templateId

I'm able to put the where clause right at the begining in the a CTE. If I place the condition at the end like I have it now, will it filtered the rows out at the earliest or at the latest time? What is faster?

Basically what I do is, in the CTE's which appear sooner, like the a or b, (rather then k), I'm prefetching data or precalculating columns in order to use them later in the following CTEs.

Hope this question is still not too vague.


texts: mysql - Relational database design for language dictionary references (primary, secondary, tertiary) approach?

texts:
I have a project where I am making the Ancient Egyptian language dictionary. For this dictionary, besides words and translations, I want to have a reference for every entry, so the dictionary can be much reliable as possible.

For this situation, I divided references into 3 groups:

Primary reference (wall, tomb, pyramid, bowl, papyrus scroll... from which word comes directly)
Secondary reference (standard references as books, journal articles, Ph.D. thesis... which contains/mention a primary reference)
Tertiary reference (in 99% of cases an existing Ancient Egyptian language dictionary which contains/mention secondary reference, or very rarely primary reference)

So, the dictionary entry (hieroglyphic word, translation) can have one or more of those references. Also, dictionary entry can have a primary reference, secondary and tertiary, primary reference could be without citation in the secondary/tertiary reference existing into database and vice versa.

The primary reference would have only a few descriptors as:

ID
Name
Alternative name
Hieroglyphic or hieratic (boolean)

The problem is when I come to the secondary and tertiary references (technically the same thing). I am not quite sure how to store those into the database and how to connect those to primary ones, and how to connect those with words/translations/... I am thinking about using BibTex Fields and Entries to make a table which will store secondary and tertiary references, but I am don't know how to organize this to work for my needs.

Can you give me suggestion how could I approach to this problem, and what is the practice when saving references into MySQL database?


texts: How to get SSIS to stop waiting/processing at task above time limit?

texts:
So I have a couple SSIS packages that are run sequentially, scheduled through SQL Server Agent. Right now, I'm getting some errors where certain input data steps just wait indefinitely-- i.e access database locked exclusively by another user or other issues. I'm still in the process of figuring out why it will just wait at some of these steps.
However, is there any way for me to check how long I've been at a certain step, and error if it's been too long? I.e no individual step should take longer than 15 minutes as the absolute max, so I would like to have something like:
If step_processing_time > 15 minutes:
    Send error email message, fail package

Is this possible to do in SSIS? I tried googling but couldn't really find anything from what I searched.


texts: Why can't I double quote built-in type names in Postgres?

texts:
Why can't I double quote the primary built-in type names in Postgres despite being able to double quote their aliases?  For example, these work fine

SELECT CAST (1 as bool);
SELECT CAST (1 as "bool");
SELECT CAST (1 as boolean);
SELECT CAST (1 as int8);
SELECT CAST (1 as "int8");
SELECT CAST (1 as bigint);

but these fail

SELECT CAST (1 as "boolean");
SELECT CAST (1 as "bigint");

(See http://www.sqlfiddle.com/#!17/a6ad3/13)


texts:
I'm using Windows 8.1. I have successfully installed SQL Server Management Studio, but services can't be started in "Sql configuration Manager". I search on google about these errors,and try all possible solutions but I am not able to resolve my error. Here is my error when I open services.msc and try to start services then this error generate

I can't start my services my sql configuration manager.Here is error image when i start services from configuration manager

When I check parameters path then I can't find MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf in C:\program File\Microsoft SQL Server folder. Microsoft SQLfolder only contains two folders: 90 and 100.


texts: How to find n consecutive numbers in MySql

texts:
I have numbers starting from 1 to 50 and then 90 to 200 in my database and I want to fetch 100 consecutive numbers then the result should be 90 to 189.
Records in my table are more than 10 million.


texts: How to shrink the .ldf(log) file of the particular live production database?

texts:
I would like to say that as one of my production database (.ldf) log file is growing larger than
databse size.

The size of database file (.mdf) and their log file (.ldf) is as follows:-

1)database file (.mdf) size is 29 MB
2)log file (.ldf) size is 100 GB

I am working as a DBA, still i don't have any information of that database. Due to System Administrator was maintaining to that SQL Server database. Before hiring to me, no one was as a DBA here. That's why he was maintening the windows as well as MS SQL Server services.

Before two day ago he called to me and told to me this (.ldf) file is growing to day by day.
But the database (.mdf) file lesser than that.

First i have checked out the "Recovery Model" of the database. I did found out "Full Recovery Model".

First of all , I have taken full backup of Database with "full" recovery model. Still i didn't take backup of  log file (.ldf).
I have googling related to this topic and find out so many blogs like

http://www.exactsoftware.com/docs/DocView.aspx?DocumentID=%7Bb66e7524-5818-4867-8a1c-bb3e4246d305%7D,
https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx,
http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/ \,
How does shrinking a SQL Server log file affect performance?,
Does .bak file also contain backup of ldf file? ,https://msdn.microsoft.com/en-us/library/ms178052.aspx, and finall this MSDB BOL

https://msdn.microsoft.com/en-us/library/ms189493.aspx

Before shrinking the log file (.ldf) of 100 GB, i want to some expert advice . what he will advice to this.

My working Environment is :

1)Windows Server 2012 R2
2) MS SQL Server 2012
3) Recovery Model of that database is "Full Recovery Model"
4) Windows is in Domain Environment
5)VMware Virtual Platform
6) Database (.mdf) Size is 29 MB
7)Log file (.ldf) size is 100 GB
8) No Mirroring of database
9)NO Replication of databse
10) NO AlwaysOn

As per Paul Randal Blog, if i will go 6 point **"Switch to SIMPLE when it fills up, shrink the log, then switch back to FULL"** . http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/
and also as per MSDN BOL https://msdn.microsoft.com/en-us/library/ms189493.aspx

Shrinking a log file to a specified target size
USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

This is a right procedure to shrink the log file (.ldf) of particular production database.  Or , Is there any suggestion or idea to shrink (.ldf) file apart from this procedure will be appreciated.


texts: Creating oracle database physical files from migrated DB

texts:
I migrated a database from SQL Server to Oracle 11g and there doesn't seem to be any physical files created after migration.

The client requires that they receive a copy of the database so they can clone it wherever they need. How do I create the files from the existing database? I'm from SQL server so it's pretty easy to get the files with a few clicks. Are there any similar feature for oracle 11g? Any alternatives if there aren't?


texts: MS SQL - Can I find a source of prior log growth?

texts:
We have a DB in simple recovery mode (which we want). At some point in the past, the LDF grew to be 20ish GB, and I'd like to shrink that.

However, I don't want to shrink it if some process is simply going to grow the file again. Is there a query/table/object that might help return information about transactions that caused the file to grow? Or perhaps is there a table that indicates when the file grew in the past?

Thanks!


texts:
I need to set an attribute on my ZFS Storage Appliance. I have shutdown all attached databases but still I get this message:

SQL> alter diskgroup DATA_ZFS02 set attribute 'storage.type'='ZFSSA';
alter diskgroup DATA_ZFS02 set attribute 'storage.type'='ZFSSA'

ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute storage.type
ORA-15321: cannot set attribute with connected clients

SQL> col instance_name format a10
SQL> select db_name, instance_name, status from v$asm_client;

DB_NAME  INSTANCE_N STATUS
-------- ---------- ------------
+ASM     +ASM2      CONNECTED
+ASM     +ASM2      CONNECTED

Do I shutdown my ASM2 instance as well? Can I set the attribute if asm instance is down?

Thank you,

Bjarte Brandt


texts: Combine 2 SQLite result sets to calculate percentage

texts:
I have a single sqlite table that I'm trying to pull two resultsets from and then calculate the percentage.
Table columns:

email
Location
Clicked?
Test#

Test# 123 has a list of users (identified by email address) who clicked a certain link, Test# 456 includes the same people who clicked in test#123, but with only a subset of the users from Test# 123 clicking a second time. The goal is to pull the percentage of those who clicked a second time in Test#456.

Query 1: (dividend) to calculate those who clicked a second time is:

SELECT Location, count(*) as "rClicks" from table
WHERE Test# = "456" and "Clicked?" = "Yes" and email IN (
    SELECT email from table
    WHERE test# = "123" and "Clicked?" = "Yes")
GROUP BY Location

Query 2: (divisor) to calculate the total who clicked on the first test is:

SELECT Location, count(*) as "Clicks" from table
WHERE test# = "123" and "Clicked?" = "Yes"
GROUP BY Location

I've tried all sorts of combinations, the most promising being an INNER JOIN on the two result sets, but I keep getting 0.0 for the percentage.

SELECT query1.Location, query1."rClicks" / query2."Clicks" * 100.0 as "Percentage"
FROM (query 1) repeats
INNER JOIN
(query 2) previous
ON repeats.Location = previous.Location

Any suggestions for getting this right?


texts:
Has anyone ever come across a COMException while working with geography data in SQL Server?

I didn't notice the error until I ran .Reduce() on the original zip code data.  It may have happened before then had I let queries run long enough, but most queries I tried ran for a very long time (up to 5 hours, in once case) before I terminated them.

I'm not entirely sure how to debug something like this, since it's not giving me any useful error code, and I haven't been able to find anything about this sort of error in the documentation.

The error is as follows:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GeographyUnionAggregate":
System.Runtime.InteropServices.COMException: Error HRESULT E_FAIL has been returned from a call to a COM component.
System.Runtime.InteropServices.COMException:
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticCombine(CombineMode combineMode, GeoData g1, GeoData g2, Double eccentricity)
   at Microsoft.SqlServer.Types.GeographyUnionAggregate.Terminate()
.

And I'm able to produce it with the following code:

select
    *
from
    tblGeography
    cross apply
    (
        select
            geography::UnionAggregate(ZC_Geography) as NewGeom
        from
            tblGeographyDetail
            join tblZipCode on ZC_Zip_Code like GD_Zip_Code
        where
            GD_GEO_ID=GEO_ID
    ) a
where
    GEO_ID=2


texts:
I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind of contact information per row.

Here's how I'm doing it currently: http://sqlfiddle.com/#!6/c9b2e/1 (for some reason, it's not outputting anything, but the code works in SSMS).

Is there a way to do this without using two different selects? Maybe self-joining usersToImport or using case somehow.


texts: Return a percentage of the full record set

texts:
I'm pulling back a list of orders but I need to only display a percentage of the records.  for example we want the top 30 or top 50.  The percentagbe is variable so I'd like to not use TOP.  I tried the code below but I'm getting back no records.

SELECT x.SelectionCriteriaID,
       x.DeliveryCriteriaID,
       x.SurveyID,
       x.intappid,
       x.intcustomerID,
       x.intorderid,
       x.RowNumber RowNumber,
       x.cntoid
  FROM (SELECT @SelectionCriteriaID SelectionCriteriaID,
               @DeliveryCriteriaID DeliveryCriteriaID,
               @SurveyID SurveyID,
               ord.intappid,
               ord.intcustomerID,
               ord.intorderid,
               ord.cntoid,
               ROW_NUMBER() OVER (ORDER BY ord.intcustomerid) AS RowNumber
          FROM ord
         WHERE ord.cntoid >= ISNULL(@MinNumberOrders, 1)
           AND ord.cntoid < ISNULL(@MaxNumberOrders, 999999999))x
 GROUP BY x.SelectionCriteriaID, x.DeliveryCriteriaID, x.SurveyID, x.intappid, x.intcustomerID, x.intorderid, x.RowNumber, x.cntoid
 HAVING x.rownumber < MAX(x.RowNumber)/(100/@percent)


texts: How can I force the optimizer to use hash join in PostgreSQL?

texts: In SQL Server, it is select * from A inner hash join B on A.a = B.b. Does PostgreSQL have similar join hint?


texts: SQL Server replication service in Amazon RDS

texts: I have a local infrastructure with multiple SQL Servers. Some of the servers have replications services to copy some data to a master database. Is it possible to have the replication services to work on a RDS SQL Server instance? Or do I need to have a EC2 instance with SQL Server and configure the service by myself?


texts: using lag() on multiple columns (gaps and islands issue)

texts:
I have a problem I am trying to solve where I need to eliminate duplicate consecutive rows in a table using the first 7 columns as the basis to determine if it is a duplicate. The data looks like:

textColA|textColB|textColC|textColD|textColE|numCol1|numCol2|eventNum
--------+--------+--------+--------+--------+-------+-------+--------
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 1
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 2
    x   |    y   |    z   |  foo   |  bar   |  1.1  | 2     | 3
    x   |    y   |    z   |  foo   |  bar   |  1.1  | 2     | 4
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 5
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 6

With these data in the order displayed, I want to retain eventNum 1,3 and 5 - event 5 because even though it is the same as 1 & 2 but follows 4, which is not the same.

I have the code to do it using a cursor looping through each row and doing the comparisons but while this will work for a table with 1, 2 or even 10 million rows, it will likely be problematic on the 300-400m row tables I need to process.

I've looked at different solutions using the lag() function but the examples I see only deal with one column and I can't seem to the get the syntax right for using multiple columns.

Any help is appreciated.


texts: What does the vertical pipe slash operator do?

texts:
I'm wondering what pipe slash |/ does in,

SET equal_area_radius = |/area/pi();

The statement works and it definitely changes the value:

SELECT |/125.555/pi(); --  returns: 6.32181918120139
SELECT 125.555/pi();   --  returns: 39.9653977598058

What does the pipe-slash operator |/ do?


texts: Access: search for more than one parameter?

texts:
I need to perform several queries, looking for more than one parameter.

for example: in my field "lenght" I want to extract values matching both 4.6 and 4.7.
then, next time I need 5.8 and 5.9, and so on. I will be searching for more parameters.

Is it possible to create an input form where i can input something like:

4.6 AND 4.7

PS: would it be better to use a different database software?


texts: Poor SQL query optimization of Recursive CTE with iTVF, environment specific

texts:
I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected.

In our production environment, the first iteration of the CTE executes as expected, but the iTVF on the recursive part suddenly switches to an index scan (different index) that can't even complete rather than the same index seek it used in the first iteration.

I've updated the statistics in our production and dev environment for the table the iTFV uses. I've even tried adding several relevant filtered statistics, to no avail. If I replace the iTVF with its content and use the relevant index hint, it also performs as desired, however I would like to avoid using index hints so I can maintain schemabinding.

The stripped down CTE looks like this:

WITH [ItemsOnDate] AS (
    SELECT  [l].[container_num]
        ,   [items].[item_id]
        ,   [items].[item_type]
        ,   1 AS [level]
    FROM [dbo].[location] [l]
    CROSS APPLY (
        SELECT  [item].[item_id]
            ,   [item].[item_type]
        FROM [dbo].[GetItemFromLocOnDate]([l].[loc_id],@on_date) AS [item]
        WHERE [item].[item_id] IS NOT NULL
    ) AS [items]
    WHERE [l].[container_num] = @container_num
    UNION ALL
    SELECT  [l].[container_num]
        ,   [items].[item_id]
        ,   [items].[item_type]
        ,   [ItemsOnDate].[level] + 1 AS [level]
    FROM [ItemsOnDate]
    INNER JOIN [dbo].[location] [l] ON [ItemsOnDate].[item_id] = [l].[container_num]
    CROSS APPLY (
        SELECT  [item].[item_id]
            ,   [item].[item_type]
        FROM [dbo].[GetItemFromLocOnDate]([l].[loc_id],@on_date) AS [item]
        WHERE [item].[item_id] IS NOT NULL
    ) AS [items]
    WHERE   [ItemsOnDate].[item_type] = 'container'
)
SELECT  *
FROM [ItemsOnDate]

The iTVF called goes through a master audit table. The audit table is of schema change_id, change_date, user_id, table_id, primary_key, field_name, old_value, new_value

This audit table has 3 indexes, 1 is change_date, table_id, primary_key. 2 is table_id, field_name, new_value. And 3 is table_id, primary_key. The index with the most specificity in this case is 3, where table_id correlates to the location table and primary_key is the loc_id. Any key in this index would have on average 10 rows. The table is clustered on index 1 and is unique for our purposes.

This is the iTVF:

SELECT TOP 1 [t].[new_value] AS [item_id]
    ,   [t2].[new_value] AS [item_type]
FROM [dbo].[trans] [t]
INNER JOIN [dbo].[trans] [t2] ON [t].[change_date] = [t2].[change_date]
    AND [t].[user_id] = [t2].[user_id]
    AND [t].[primary_key] = [t2].[primary_key]
WHERE [t].[table_id] = 'LOC'
    AND [t].[primary_key] = CAST(@loc_id AS VARCHAR)
    AND [t2].[table_id] = 'LOC'
    AND [t].[field_name] = 'item_id'
    AND [t2].[field_name] = 'item_type'
    AND [t].[change_date] < @on_date
ORDER BY [t].[change_date] DESC

I would expect it to use index 3 on table [t], and index 1 on table [t2]. This works in dev, this works on the first iteration in prod, then decides to do a full clustered index scan on the recursion. As I said, I've updated statistics and even added statistics relevant to index 1 and 3 for when the table_id is 'LOC'. I can't figure out for the life of me this behavior.

This is what the bad plan in production looks like:

And here's the plan explorer profile for it: https://drive.google.com/file/d/1fkzPbdIjC74Itn2-uRW9Aqh3zpnuOr6D/view?usp=sharing

This is what the good plan in dev looks like:

And here's the plan explorer profile for it: https://drive.google.com/file/d/1wPvh3W_dIA3lHZOBcXjllYXWEBs-xnkb/view?usp=sharing

Thank you very much for looking at this, it helps to have a fresh set of eyes on it!


texts:
I have a query that can run on the same data set, and sometimes it fails and sometimes it succeeds
The query is generated by hive metadata service, and I can't modify it.
This is a simplified version of the query:
select
    "TBLS"."TBL_ID",
    "FILTER0"."PART_ID",
    "TBLS"."TBL_NAME",
    "FILTER0"."PART_KEY_VAL"
from
    "PARTITIONS"
inner join "TBLS" on
    "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"
    and "TBLS"."TBL_NAME" = 'test_table_int'
inner join "PARTITION_KEY_VALS" "FILTER0" on
    "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
where
    cast("FILTER0"."PART_KEY_VAL" as decimal(21, 0)) = 1

When I spin up a new database, and populate the relevant tables, this is how the whole data looks like (querying without any filters):

and running the query above will return a single row (the one with PART_KEY_VAL = 1)
the problem starts after I run some automated tests that write to those tables. I couldn't find any pattern, I just run a few complicate tests that write to those tables
Now if I populate those tables again, the data looks similar:

but running the query above will result in:

SQL Error [22P02]: ERROR: invalid input syntax for type numeric: "c"

for some reason, the value "c" is being cast to decimal and it fails, even though the same query on the same data was working earlier
what could be the reason for this behavior?

for reference, here is where the query is generated, but I simplified it a bit above: https://github.com/apache/hive/blob/rel/release-3.1.2/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L1289-L1339


texts: Big data table processing with RDBMS like MySQL?

texts:
I have two tables Customers and Orders and each has 100 million records.

Now I have to join these two tables on the column customer_id in both Customer and Order table to generate a report. customer_id is the primary key in customer table and foreign key in order table.

I have heard from senior folks that it is close to impossible to do it MySQL(or any other RDBMS) because a single system has to perform this join on a huge volume of data. Is that correct RDBMS like MySQL(or Oracle) are not good for such a large volume of data even for simple operations like join with proper indexes ?

Also I would like to know what data size per table is considered good for RDBMS table so that simple operations like join, aggregation with proper indexes works well ? I know answer may be "It depends" or vary based on number of factors. But consider best possible tuning is done, still is there any broad level standard data size limit that is considered good ?


texts:
I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs:

COPY (SELECT ... FROM ... GROUP BY zzz) TO STDOUT >file.csv

In order to have a consistency check of the file.csv, I then run a query to get the number of lines which the COPY query had:

SELECT count(0) FROM ... GROUP BY zzz >linecount

In the bash script there is a check if both are equal, if that's the case the script proceeds, if not it stops and reports an error (which happens rarely, but it does - and i haven't figured out why yet)

The problem, the query result is rather large (takes around 5-10min) and it is basically running twice (although the count(0) takes shorter, but still 3-7min)

Is there a PostgreSQL function to get the number of rows the last query had? If that would be the case, I could cut down DB load quite a load and shorten the export time.

I have looked into ROW_COUNT, but it only seems to be valid for UPDATE/INSERT and not SELECT, I'd also like to avoid using functions for simplicity reasons.

I am open to alternative suggestions, eg first query into a temp table, then export from there (maybe count(0) is faster then)?

Debian 7 Wheezy, SSD, 32GB Ram, PostgreSQL 9.1

UPDATE @Craig Ringer

I agree that the consistency check is somewhat 'flawed' by design. I noticed once that the import-check had a problem i couldnt explain, I then ran the export again, and the file had different lines than the one before:

Rowcount DB/LinecountF:532395/532014

rowcount: SELECT count(0)... as described originally
linecount  = wc -l

They are running as cronjobs and I havent figured out why those lines were missing. Running the same script second time and everything was fine. It happens very rarely, but the logfile didnt say anything (or mail result from cron).

I didnt check the exit status of the PSQL tough (as I built in my own checks). I will do that.


texts:
Timescale allows us to query for chunk sizes in with something like

SELECT * FROM chunk_relation_size('....');

I am trying to monitor the chunk sizes so that if any of them reaches a size that is dangerous to be kept in the memory, we can scale the DB as needed.

I am assuming the latest created chunk will be the most read heavy, thus most likely to be kept in memory.

However the chunk_relation_size table doesn't expose any timestamps or information on when the chunk was created.

I am wondering if the chunk_ids are assigned incrementally or are they completely random? In the former case, I can infer which chunks are latest.

Docs doesn't specify anything. I have skimmed through the source code but have been unable to find any clues.


texts: Setting up Oracle publishers in SQL Server replication

texts:
I am attempting to set up and oracle publisher on a sql server 2008R2 server, but I am getting the following error.

"Unable to run SQL*PLUS. Make certain that a current version of the Oracle client code is installed at the distributor.  For addition information, see SQL Server Error 21617 in Troubleshooting Oracle Publishers in SQL Server Books Online. (Microsoft SQL Server, Error:21617)"

The information I have found states that an oracle client must be installed and the oracle_home\bin must be in the path variable.  I have verified that it is.

So far I have taken the following steps
Installed oracle administrator client
Added TNS_ADMIN env variable
Added ORACLE_HOME env variable
connected to the remote oracle database from the distributor via sql*plus

I am hoping someone will have run into similar errors in the past


texts: Error CRS-5014 when runing RAC 11gr2 on Redhat 6

texts:
My database using RAC 11gr2 with two node on Redhat 6.

My system running fine, but after some time about 3-4 days. Process "ora.LISTENER.lsnr"  change status Online to INTERMEDIATE and then pedding, can not connect any session.

I reboot server it fine :) (restart instance or listener is not working)

This error exists only node2.

This is log of error in /u01/11.2.0/grid/log/node2/agent/crsd/oraagent_grid/oraagent_grid.log file:

[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] Agent::abort last call info:  "Agent::Agent refreshAttr"
[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] abort command: check
[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] tryActionLock {
[ora.LISTENER.lsnr][134007104]{1:32952:11577} [check] (:CLSN00009:)Utils:execCmd abort request: killing /u01/11.2.0/grid/bin/lsnrctl
[ora.LISTENER.lsnr][134007104]{1:32952:11577} [check] clsnUtils::error Exception type=2 string=
CRS-5014: Agent "/u01/11.2.0/grid/bin/oraagent.bin" timed out starting process "/u01/11.2.0/grid/bin/lsnrctl" for action "check": details at "(:CLSN00009:)" in "/u01/11.2.0/grid/log/node2/agent/crsd/oraagent_grid/oraagent_grid.log"

2017-06-17 09:23:28.859: [ora.LISTENER.lsnr][134007104]{1:32952:11577} [check] clsnUtils::error Exception type=2 string=
CRS-5017: The resource action "ora.LISTENER.lsnr check" encountered the following error:
(:CLSN00009:)Utils:execCmd aborted. For details refer to "(:CLSN00109:)" in "/u01/11.2.0/grid/log/node2/agent/crsd/oraagent_grid/oraagent_grid.log".

[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] got lock
[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] tryActionLock }
[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] abort  }
[ora.LISTENER.lsnr][239827264]{1:32952:11577} [check] (:CLSN00110:) clsn_agent::abort }
[    AGFW][239827264]{1:32952:11577} Command: check for resource: ora.LISTENER.lsnr node2 1 completed with status: TIMEDOUT
[    AGFW][209729856]{1:32952:11577} ora.LISTENER.lsnr node2 1 state changed from: ONLINE to: UNKNOWN
[    AGFW][209729856]{1:32952:11577} ora.LISTENER.lsnr node2 1 would be continued to monitored!
[    AGFW][209729856]{1:32952:11577} ora.LISTENER.lsnr node2 1 state details has changed from:  to: CHECK TIMED OUT
[    AGFW][209729856]{0:1:4} Generating new Tint for unplanned state change. Original Tint: {1:32952:11577}
[    AGFW][209729856]{0:1:4} Agent sending message to PE: RESOURCE_STATUS[Proxy] ID 20481:134789
[ora.asm][239827264]{2:50449:2} [check] CrsCmd::ClscrsCmdData::stat entity 1 statflag 33 useFilter 0

How to fix this error. Thanks.


texts: PostgreSQL trigger copy record to another table with different columns order

texts:
I have a trigger that before every update on hundreds of tables it inserts into a table with the same name and the _hv suffix a copy of the record being updated to maintain a kind of historical updates.

So when there is an update in my_example table. The table my_example_hv that has the same fields in the beginning of the table and some extra fields in the end (update timestamps, update user, etc). Using this line it works perfectly:

EXECUTE 'INSERT INTO '|| TG_RELNAME ||'_hv SELECT  ($1).*' USING NEW;

Ok the problem comes when I need to insert a new field to the main table.
I can add the new field too to the _hv table but I can't reorder the fields in my postgresql version (8.4). I know it's obsolete but I can't upgrade it right now because I have some old software that needs that version of postgresql.

So what would be the code to insert the record to th _hv table having some field now in different order but with the same column names?


texts: Execution Plan Warnings cardinality xml data type

texts:
I have the following statements:

-- 1st
DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL
)

-

-- 2nd
DECLARE @AuditParameters XML = convert(xml, (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL
))

Both statements result in the same execution plan, however, they display the warning message

  Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1004],0))
  may affect "CardinalityEstimate" in query plan choice.

You may note I have tried to convert the second statement to XML and yet I get the same warning message.

How can I get rid of this warning message on the statement above?

Thank you


texts: Is there a way to improve performance here using No Exists?

texts:
I have three tables. One table has information on users, such as their name, bio, age etc. The primary key is the Id field alone. Second table shows people users follow (user_following) and the third table is to store relations with people who follow me (user_followers).

I want to find information on those users who have been removed from both the tables.

Users can have large IDs and hence the ID column is varchar with the lenght being max 25 characters as of now.

My query is this:

select *
FROM followers_info finfo
LEFT JOIN user_followers uf
    ON uf.follower_id = finfo.follower_id
LEFT JOIN user_following fing
    ON fing.follower_id = finfo.follower_id
WHERE uf.follower_id IS NULL
  AND fing.follower_id IS NULL

Attached is the explain results of this query:

Is there a way to improve the performance here?


texts:
I'm junior DBA ,have table with 11 million values ,one of the indexes is like :

CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl]
(
    [my_id] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

For rebuild this index need only

ALTER INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl]
REBUILD;

or with big tables need to prepare something.And also one question in this table I have some columns with asc index and some with desc , can be any problems after rebuild all indexes in this table? (ex.: slow select/insert). Is good idea to rebuild this index?

SQL Server Version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64>


texts:
We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take log position from the master or from the backup server where i took the dump from.

Secondly the new server we have created is 5.6 and old server is 5.5, would there be any replication issues as well ?


texts: Need to compress a giant table of 1.5TB in sql server 2008, which is the best option Page or row compression, please let me know the steps to do it.


texts: Oracle 11gr2 instant client installation

texts:
I have an application that uses Oracle 11gr2. For the Oracle client installation, I first install InstantClient and then run the setup again, choose Custom, and choose "Oracle Database Utilities" since the InstantClient doesn't have exp/imp with it.

My question is, instead of doing 2 steps for installation, how can I just do it once? What corresponding options do I have to select in Custom mode to install InstantClient? I have attached a pic for the complete list of items available.

Thanks.


texts:
I have multiple site servers and a central server.  Each has a Widget table with identical schema.  There is currently a custom process that sends new Widget records from the site server that generated it to the central server.  If at any point a Widget needs to be sent from one site to another for further processing, there is a process that copies the Widget record from the central server to the necessary site server.

At this point Site A will have all of its own Widget records, plus x number of Widget records from Site B.  Site B will have only its own Widget records.

If I were to set up Site A and Site B to replicate the Widget table to the central server, will adding these new records to Site A (and that already exist in the central server) have any adverse effects on replication?  We are using SQL Server 2008 R2 SP2.


texts: Generic "An Error Has Occurred" When Editing SSRS Subscription - No Parameters Appearing

texts:
I'm relatively new to SSRS Reporting. I'm attempting to manage a data-driven subscription for a two-part report on a SQL Server 2016 Developer instance that is acting as a dedicated SSRS server. At one time I was able to edit/manage a data-driven subscription for one report, but this no longer appears to be working and I'm getting a generic error.

Steps to Reproduce Error:

Browse to SSRS instance at http://testReportServer/Reports/browse
Click on the Department Folder
Click on the Report Folder
The Report folder contains a report sub-part (called Eval_rpt) and the main
report (Evaluation_rpt)
I click on the ellipses for Evaluation_rpt and click Manage because that's where the subscription is set up.
I click on Subscriptions, and there is one there called Test
I click on Edit next to the Test subscription
The Edit Subscription page begins to load, then I'm presented with the generic "An error has occurred.
Something went wrong. Please try again later."
Enabled verbose logging on the SSRS instance and walked through reproducing this error several
times. There's nothing in the SSRS log output that is meaningful or
useful.
Opening up Chrome/Firefox developer console, on the Network
tab, I see an error 500 for Model.GetParameters
When I click through the error that SSRS gives me, there should be two Parameters listed
at the bottom of the Edit Subscription page, and they are not
appearing for obvious reasons.

Facts, Analysis, & Troubleshooting Steps so Far:

This ReportServer / ReportServerTempDB database used to reside on a
SQL Server 2008 instance and were migrated to this 2016 instance late
last year. This report has been working fine since that migration. It
was only recently I tried updating this subscription and ran into
this issue.
This instance uses domain service accounts to run services (SQL Server, Reporting Service.) I've reviewed permissions/roles on those
service accounts on both the SSRS server and the SQL Server hosting
the remote data source for the report and sub-part, and they appear to be permissioned correctly.
The report in question has data sources on a remote SQL Server (e.g. ProdServer.Domain.org) and the credentials configured in those data sources have rights to connect to and query the target database on ProdServer
The owner of this report is the same service account used by the Reporting Services Service.
I am attempting to edit this subscription with a Windows/AD account that has local administrative rights on the windows server, sa rights on the SQL Server Instance, and is a full administrator on the Reporting Server instance.
This doesn't appear to be a double-hop issue as I've verified there's a trust relationship between these two servers, and I can see KERBEROS connection/authentication methods when I look for them
The rsreportserver file is set up to use an authentication type of RsWindowsNegotiate
I've reviewed/checked for shared/embedded data sources in the report parts. Where necessary, I've reconfig'd these reports to not use a shared data source to pull parameters, but instead redirected parameters to use a data source manually set up on the report parts themselves (manage
the report part > data sources >  connect to a custom data source >
provide AD credentials > test connection)
I've confirmed that the service accounts in use have rights to not just the report server database but the rsExecRole and related subcomponents/stored procedures/tables within master and msdb.

Thanks in advance for any advice/suggestions you can offer.


texts: Running out of disk space when removing documents error

texts:
I'm trying to remove unused document from a production instance of MongoDB but, when I try to execute this query:

db.collection.remove( { date: {$lt: new Date((new Date())-1000*60*60*24*90)} } )

but I get this error:

WriteResult({
        "writeError" : {
                "code" : 14031,
                "errmsg" : "exception: Can't take a write lock while out of disk space"
        }
})

I have 800mb free on that partition, how can I remove documents the documents? Trying to remove a small subset of data instead of a large one will fix the issue (the size of the data that will be removed 1.28Gb)? (It's production, I can't test directly, I need to go through our host maintainer).

thx,
Andrea


texts: Make encrypted differential backup to same media

texts:
I create encrypted backup as follow:

BACKUP DATABASE XXX
TO DISK = 'C:\Backup\XXX.bak'
WITH INIT, FORMAT,
     ENCRYPTION  (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)

Then I try to add differential backup to same file:

BACKUP DATABASE XXX TO DISK = 'C:\Backup\XXX.bak'
WITH DIFFERENTIAL,
     ENCRYPTION  (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)

but get error:

  The backup cannot be performed because 'ENCRYPTION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'ENCRYPTION' or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

What should be done to store encrypted differential backups in a single file?

Upd:
I know how to get diff backup in another file. If it is not possible to get diff backup in the same file, where I can find documentation on this behaviour?


texts:
I need to allow a user only to execute a specific Agent job, rather than be able to execute the other jobs that are there . Please advise how to accomplish this.

This user will only run a copy only backup before he updates the application and I don't want to give him access to the other jobs. Ola Hallengren scripts are used for that job.

I added the user to the DB_BackupOperator role. If I simply make him the owner of that job will he be able to run the other jobs that are owned by SA?


texts: Looking for a possible multi-column index

texts:
I have a table highscore containing the columns:

game (text)
date (timestamp)
score (integer)
more irrelevant ones...

The query most often run on it is:

SELECT *
FROM highscore
WHERE game = :gamename
AND date BETWEEN :start AND :end
ORDER BY score DESC
LIMIT 10

I currently have three B-tree indexes on this table, one for each column above.

I'm thinking that I could further optimize performance by adding some sort of multi-column index, which starts with game. However, when thinking through the various options for the next column(s), I get stuck.

Can I use a multi-column index for further optimization here?

I'm using PostgreSQL 9.1


texts:
Using SQL Server 2008 R2 Enterprise Edition

Consider the following statement:

:setvar source_server_name "SERVERNAME\INSTANCENAME"

Is it possible in SQLCMD mode to get that value using TSQL

Something like: :setvar source_server_name = SELECT @@servername

Thank you

Update 7/15/2013

The two answers offered below did not quite give the desired result, so I'm adding a more relevant exampe.

:setvar source_server_name [myserver]

The variable source_server_name is set to the text string [myserver_1]

I'd like to be able to do this:

create table #tmp(
id int identity(1,1),
server sysname
)

insert into #tmp values('myserver_1'),('myserver_2');

:setvar source_server_name = SELECT server FROM #tmp WHERE id = 1

select '$(source_server_name)'

(No column name)
myserver_1

The variable source_server_name would be set to the value in server for id 1.


texts: Alternative methods for client data integration (Azure SQL Database)

texts:
I have an application that ingests data from clients on a daily/weekly basis (two different data sets, one daily and the other weekly) into a SQL Azure Database. The clients' data source depends on what software they use, so can vary from client to client.
I currently have two methods of integration, depending on the client:

Using Azure Data Factory and Self-Hosted Integration Runtime. In this method, the client is required to provide (within their network) a VM in which I set up the Integration Runtime, and a SQL Server database with just two tables where they dump the two datasets as required. In ADF, I create pipelines to pull the data directly from their SQL Server into my Azure SQL Database, then run necessary import procedures.
Using Azure Data Factory and BLOB Storage. In this method, I provide the client with a set of Powershell scripts to be run on a schedule (Windows task scheduler) that help them to copy their exported files (.CSV) to our BLOB storage. Then, the ADF pipelines copy from the BLOB storage to the Azure SQL Db, then run the necessary import procedures.

The first method is much simpler, but in terms of infrastructure at the client end, it seems like a bit of overkill to set up a mostly blank Windows VM and a database with just a couple of data dump tables. Obviously, this can be costly if the client themselves are cloud-hosted - firing up a new VM is not cheap, so could make them think twice about using our product.

The second method requires me to set up a storage container for each client, which I feel could make administration difficult as we scale up. Also, providing scripts to run with Windows Task Scheduler doesn't feel overly elegant.

Does anybody have any alternative solutions to this scenario? Or am I on the right track?

Any insights would be greatly appreciated. Thanks.


texts: Alwayson fn_hadr_backup_is_preferred_replica takes a long time to return results

texts:
Our 2 nodes full sync mode Alwayson cluster has 20 databases which allocated into 4 different AG groups (9, 6, 3, 2). We set backup preference as on 'preferred secondary'. Find out that fn_hadr_backup_is_preferred_replica function takes 10 - 14 seconds to return results for pre-check each database which causing backup to take a long time with HADR_CLUSAPI_CALL wait.  It seems no matter which one of four backup preference methods for Alwayson, you needs to relied on fn_hadr_backup_is_preferred_replica to return its backup preference. How to fix this problem since the fn_hadr_backup_is_preferred_replica is a system function which hard to modify?
we use SQL 2016 Enterprise, SP2 on latest CU. on VM


texts: Representing a Many-to-Many Relationship Between Students and Departments

texts:
Suppose I have two tables

Table A=Student

Table B=Department

Table A:

CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);

Table B

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int unique foreign key references Student(StudentId)
);

INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);

Now the question is:

Can I insert upper values(Values that i given above insert into table B) in  Table B,
I know most of you will say no.

But suppose  I have requirement to do it how it is possible  without removing unique constraint.

Note: Actually In first table I have DepartmentId=1,
I want that more than one row should insert in table b having studentid =1

(against one Departmentid [In Table A], I can add multiple StudentId [table B] in table B)

Output will be like this:

----------------------------------
ID      DeptName       studentId |
101     CSE               1      |
102     Mech              2      |
103     Civil             1      |
---------------------------------


texts: Creating a temporary copy of a table with partitions

texts:
I've got a little issue and wonder if anyone has come across it themselves or seen a solution.

In one of my reporting scripts, it creates a temporary copy of a table before inserting some rows in order to a bulk update on the source table. To create a copy I used the SQL

CREATE TEMPORARY TABLE myTable LIKE source.myTable;

Which works fine, apart from one table which is partitioned. MySQL can't create temp tables with partitions.

I can't do the alternative which would be to do...

CREATE TEMPORARY TABLE myTable SELECT * from source.myTable;

If you do that it a) only creates the column definitions to fit the data in the select (i.e. a VARCHAR column will only be defined according to the max length of the column in the select statement) and b) I don't want to copy all 4 million rows simply to truncate them in the next statement!

Is the only other option to create it manually from the information schema? That really feels like using a sledgehammer to crack a walnut, surely there must be a simpler way to do it? Not too mention a lot of work.


texts:
I have an odd situation and I hope someone can help me get out of this.

The situation:

I'm running an Azure SQL 2012 SP2 VM with Windows Server 2012. Since it's creation I promoted the server to be a domain controller and everything is fine. I've also installed an application (Dynamics CRM) which is pretty heavy on its database use (multiple databases, a few scheduled jobs).

I'm getting an error on these scheduled jobs saying that they can't validate whether computername\my.username has access.

  SQL Server Scheduled Job 'MSCRM_CONFIG.SiteWideCleanup' (0x1D1BA70291EB7E44810A6121F9045F17) - Status: Failed - Invoked on: 2015-01-14 00:30:00 - Message: The job failed.  Unable to determine if the owner (SYSTEMNAME\my.username) of job MSCRM_CONFIG.SiteWideCleanup has server access (reason: Could not obtain information about Windows NT group/user 'SYSTEMNAME\my.username', error code 0x534. [SQLSTATE 42000] (Error 15404)).

It looks like, when I promoted my server to a domain controller, my SYSTEMNAME\my.username object got changed to DOMAINNAME\my.username. I can log in to SQL with my domain username and have all the appropriate privileges (DBOwner), but when looking at the "Login" section I still see the reference to SYSTEMNAME\my.username.

I've been searching for a way to change the user in SQL around, but haven't been able to find an answer (I'm guessing this is a very odd situation that shouldn't happen when someone sets things up properly) and hope to find one here:

How can I switch the SQL Server login SYSTEMNAME\my.username over to DOMAINNAME\my.username without losing all the things that this username is attached to?

Note: this is a development server that I set up just for my own fiddling purposes, and I can do whatever I'd need to even if it's not best practice under normal circumstances.


texts: Unable to update data from federated table automatically

texts:
I am trying to transfer data from remote Server to localhost through MySQL.

In the remote server I created a database HOD and a table tab.

In the localhost I created the database "HOD" and a table "tab". I also created a stored procedure fed_procedure and federated table "fed_tab" to pull the data from remote table. The federated table "fed_tab" gets data from the remote server but the table "tab" updates only when I call the stored procedure using CALL fed_procedure(); I want the table "tab" to update automatically every 5 minutes. So I created an event schedule to update the table "tab" every 5 minutes. But when I execute it, I am getting the error :Access denied for user 'root'@'%' to database 'HOD'". How can I do this?

Below is the stored procedure I created and the event scheduler.

DELIMITER $$

DROP PROCEDURE IF EXISTS `HOD`.`fed_procedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fed_procedure`()
BEGIN

  declare tab_indx integer(11);

  set tab_indx = (SELECT tab_ndx FROM tab order by tab_ndx desc limit 1);
  insert into tab select * FROM fed_tab where tab_ndx>tab_indx;

END $$

DELIMITER ;

The event scheduler is below

CREATE EVENT fed_procedure ON SCHEDULE EVERY 5 minute
do
CALL fed_procedure()


texts: Performance of query with a range condition and order by

texts:
We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:

lexikon
-------
_id   integer  PRIMARY KEY
word  text
frequency integer
lset  integer  UNIQUE KEY
rset  integer  UNIQUE KEY

And the query:

SELECT word
FROM lexikon
WHERE lset BETWEEN @Low AND @High
ORDER BY frequency DESC
LIMIT @N

I suppose a covering index on (lset, frequency, word) would be useful but I feel it may not perform well if there are too many lset values in the (@High, @Low) range.

A simple index on (frequency DESC) may also be sufficient sometimes, when a search using that index yields early the @N rows that match the range condition.

But it seems that performance depends a lot on the parameter values.

Is there a way to make it perform fast, regardless of whether the range (@Low, @High) is wide or narrow and regardless of whether the top frequency words are luckily in the (narrow) selected range?


texts: How do I pass a parameter in a stored procedure to call a DTS package

texts:
I have created a DTS package which fetches data from a text file into a database table.

I want to pass the file name dynamically from which it fetches data.

Can I pass the filename dynamically?


texts: innodb_log_file_size not upating even after changing my.cnf

texts:
Hello team this is my first post here,

Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

I am trying to import large db  and half way I get the error to increase Size of InnoDB Redo Log Files so I do follow this steps

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop

change my.cnf to

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

# innodb
innodb_buffer_pool_size = 256M
innodb_log_file_size = 32M

and then restart mysql

mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

but still I see

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

my values are not updated ? can you please let me know is there anything I am missing ?


texts: SQL Server 2008 Agent keep stopping for unknown reason

texts:
My MSSQL 2008 service keep stopping don't know why. I took the habit to just restart the service, but it's getting really annoying.

Does anyone has a clue on why it keeps shutting down ?

Sql server 2008, logged as local.

Here are 4 SQLdump from today.

09:29:36 09/28/16

10:05:42 09/28/16

10:32:18 09/28/16

10:46:14 09/28/16

found this in my event viewer :

  SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

Error when I tried to install MSSQL 2008 SP3 (x64)

  The version of the instance of SQL Server Shared Component does not match the expected version of the update to SQL Server. The version of the SQL Server product installed is 10.50.1600.1 and the version of SQL Server expected is 10.0.1600.22.

SELECT @@VERSION res

  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
      Apr  2 2010 15:48:46
      Copyright (c) Microsoft Corporation
      Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: )


texts: Can I determine the service pack for all my SQL Servers using SCCM?

texts: I'm trying to figure out a way to find out all the service packs for my sql servers. There's about 30+ databases which we hosted & to login to each of the server and check the service pack will going to take some time. Can I use any tools e.g. sccm to crawl into all the sql servers and provide me with the sql versions & it's service packs?


texts: T-SQL Calculate average value for Duration Field

texts:
Can anyone please help me on this..

I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'.

How do I calculate the average duration?  I read some other blogs and also used cast techniques like the one below but couldn't solve my problem...

cast(cast(avg(cast(CAST(Duration as datetime) as float)) as datetime) as time) AvgTime

Please help...


texts: innodb_thread_concurrency seems evil as it enlarge table size by big factor

texts:
Environment: OS: MS Windows 2012 R2, CPU: Xeon 12 logical processors, HDD Block Size: 8 KB, DB engine (MariaDB 10.1.23 Or MariaDB 10.2.6 Or MySQL 5.7.18) with Default Settings.

Schema:

CREATE DATABASE IF NOT EXISTS `z` DEFAULT CHARACTER SET utf8;
CREATE TABLE `test` (
    `testId` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`testId`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Use below mysqlslap to make 1 Million records without concurrency (single thread, single connection) :

  mysqlslap -pPutPASS --create-schema=z --number-of-queries=1000000
  --query="insert into test values(null)"

test.ibd size: 36.0 MB (37,748,736 bytes)

Truncate/empty the table and perform concurrency insert as below:

  mysqlslap -pPutPASS --create-schema=z --concurrency=100
  --number-of-queries=1000000 --query="insert into test values(null)" --commit=10000

test.ibd size: 56.0 MB (58,720,256 bytes)

Set innodb_thread_concurrency=12 and repeat step 2:

test.ibd size: 76.0 MB (79,691,776 bytes) and sometimes 72 MB. also I tested with --concurrency=500 and table size became 88.0 MB

the actual optimized size is 28 MB by using:

  OPTIMIZE TABLE z.test;

Update 1:

Above test.ibd size results valid for MariaDB 10.1.23.

For MariaDB 10.2.6 test.ibd size:

36 MB (without concurrency)
88 MB (concurrency insert with innodb_thread_concurrency=0)
92 MB (innodb_thread_concurrency=12)
40 MB (optimized)

For MySQL 5.7.18 test.ibd size:

36 MB (without concurrency)
68 MB (concurrency insert with innodb_thread_concurrency=0)
72 MB (innodb_thread_concurrency=12)
40 MB (optimized)

The difference of 10.2.6 & 5.7.18 from 10.1.23 mostly due to barracuda "innodb_file_format" which seems worst than the old format as above tests shows!

Questions:

Why InnoDB table size increased by big factor when I use concurrent Insertion?
Why innodb_thread_concurrency causing the table size to be tripled and nothing mentioned in official docs regarding that?!
What are the config tweaks that make MariaDB/MySQL benefit from the concurrency performance boost WHILE keeping the table size smallest? I am planning to use innodb_thread_concurrency=0 as explained in Hyperthreading & MySQL InnoDB Thread Concurrency Performance but that's not enough, my project will have billions of records and keeping the table size as small as possible will be big gain for performance and saving of H/W resources.

Update 2:

Until I get an answer for the above questions, I will highlight my project requirements in order to get best configuration:

Production Environment:

OS:  MS Windows 2012 R2

SDD: HPE 1.92TB SATA 6G Mixed, Block Size 4 KB

RAM: 768 GB

CPU: 4 Xeon Processors (Sockets) E7-8890 V3 2.5 GHZ (total cores: 72, total logical processors: 144)

Data: 150 inserts per second, 24X7, from 50 to 150 connections, single-row inserted. Avalibilty of data is 1 year (mean any record older than 1 year will be deleted), no updates on the rows, just insert once, then selecting/querying through web UI), one year data estimated to be 3-4 Billion rows. DB schema consist of one big table and 10 tables holding metadata (ex: country, etc), biggest metadata table will hold maximum of 3000 rows during the whole project life cycle. so the main focus is the big table.

So far I adjusted below config params:

max_connections=501
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 256M
innodb_buffer_pool_size=512G
innodb_log_file_size=1G
max_allowed_packet = 1G
innodb_flush_method=async_unbuffered
innodb_flush_neighbors=0
innodb_io_capacity=400
innodb_io_capacity_max=2000
innodb_log_compressed_pages=off
innodb_write_io_threads = 12
innodb_doublewrite = off
innodb_read_io_threads = 12
skip_name_resolve=ON
# dump/restore buffer pool, faster buffer pool warmup
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
innodb_page_size=4k

Please Suggest (with Explanation) further config tweaks I need in order to get the best of InnoDB and MariaDB 10.2.6


texts: Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?


texts: Reseed identity column in all empty tables

texts: I need to write a query that resets the identity on all tables that have an identity column and are empty.


texts: I am a newbie to the MySQL database.I want to know different types of joins in MySQL and any link or any other kind of documentation with examples which will clear the doubts will highly appreciable.


texts: SQL Server 2008 Group by 30 minute Time Intervals

texts:
I have a SQL select statement which produces the following results, and I need to define it so that we can group the data based on 30 minute intervals of time and provide the sum of Revenue and Nbr of Trans.

Table Date/Time field -

ActionDateTime          REVENUE NBR of TRANS
2013-01-03 07:44:57.840 5.00    1
2013-01-03 07:45:10.093 5.00    1
2013-01-03 07:45:21.557 1.00    1
2013-01-03 09:07:21.253 50.00   1
2013-01-03 09:07:42.680 40.00   1
2013-01-03 09:07:49.007 104.30  1

Current Results -

DATE        TIME             REVENUE    NBR of TRANS
01/03/2013   07:44:57   5.00    1
01/03/2013   07:45:10   5.00    1
01/03/2013   07:45:21   1.00    1
01/03/2013   09:07:21   50.00   1
01/03/2013   09:07:42   40.00   1
01/03/2013   09:07:49   104.30  1

Desired Results -

DATE        TIME             REVENUE    NBR of TRANS
01/03/2013   08:00:00    11.00  3
01/03/2013   09:30:00   194.30  3

Current SQL statement -

select
  'DATE' = CONVERT(VARCHAR(12), x30.ActionDateTime, 101),
  'TIME' = CONVERT(VARCHAR(12), x30.ActionDateTime, 108),
  'REVENUE' = SUM(t01.Amount),
  'NBR of TRANS' = COUNT(t01.DocumentNumber)
from
dbo.X30_AuditInformation x30 with (nolock),
dbo.T01_TransactionMaster t01 with (nolock),
dbo.T04_GiftDetails t04 with (nolock),
dbo.B01_BatchMaster b01 with (nolock)
where x30.TableRecordId = t01.RecordId and x30.TableId = 'T01_' and x30.Action = 'INSERT'
and t01.DocumentNumber = t04.DocumentNumber
and b01.BatchNumber = t01.BatchNumber
and t01.Date between '01/01/2013' and '01/02/2013'
and b01.BatchCategory in ('IMC')
and t04.SourceCode like 'T%'
GROUP BY x30.ActionDateTime, DATEPART(MINUTE, x30.ActionDateTime)/30
ORDER BY x30.ActionDateTime

Is my GROUP BY incorrect?


texts:
We are trying to deploy a Drupal application on our production server.

We see this strange error Lost connection to MySQL server at ''Access denied root@ipaddres password(yes) after deploying.

We made all necessary corrections in settings.php and also give GRANTs to the webserver to access the database.  The webserver and the database server are physically two different machines.

We are able to connect to the database server from our web server through the command line.

There is no firewall enabled on either server.

Any update we make to the database means the site goes down and displays an ACCESS DENIED error.

Can any one guide me to resolve this issue?

Thanks