texts: SQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queries

texts:
Our application uses SQL Server 2014 and we got an issue related to the plan cache.

We have a parametrized query and its execution plan depends on parameter values. The server caches an execution plan which is not optimal in some cases and then uses it for all consequent queries.

Details:

We have a table consists of the following columns:

(
 [Revision] [bigint] IDENTITY(1,1) NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 ...A WHOLE LOT OF OTHER COLUMNS...
)

The meaning of those two columns is pretty clear, UserId is an Id of the user that the record belongs to, Revision is an auto-incrementing index of the record. Other columns are not important, but they exist and affect execution plans.

The table contains ~40.000.000 rows and ~200.000 distinct UserId values, so each user has 200 records in average. Rows are never updated, we use only INSERT and DELETE to modify data.

Our application executes the following query against this table:

SELECT * FROM SampleTable WHERE Revision > {someRevision} AND UserId = {someId}

The table has two indexes:

Clustered index: Revision asc
Non-Clustered index: UserId asc, Revision asc

When I execute this query manually, I see that the execution plan depends on the value of someRevision.

If it's relatively close to the current max value of Revision, the server uses Clustered Index Seek with Seek Predicate: Revision > someRevision
If it isn't close, the server uses Index Seek (NonClustered) + Key Lookup (Clustered) with Seek Predicate: UserId = someId AND Revision > someRevision.

Our application uses Linq-To-Sql and generates parametrized queries, they look like this:

exec sp_executesql N'SELECT * FROM [SampleTable] AS [t0]
WHERE ([t0].[Revision] > @p0) AND ([t0].[UserId] = @p1)',N'@p0 bigint,@p1
uniqueidentifier',@p0=1234,@p1='bc38dd12-238c-41a2-9dea-bb12ce105e6d'

I used dm_exec_cached_plans, dm_exec_sql_text, dm_exec_query_plan and understood that the server put a single plan for this query into the cache. So, if the query with the corresponding value of Revision came first, the plan using Clustered Index Seek would be stored in the plan cache and then would be used for all the consequent queries.

It leads to an excessive number of logical reads (x10000) and unacceptable execution time for queries which should be executed using the second plan (Index Seek (NonClustered) + Key Lookup (Clustered)).

Also I noticed that the threshold where the server switches between plans (the tipping point) depends on statistics, if it's stale, the plan can be sub-optimal even regardless the cache, because the server incorrectly estimates the number of rows with Revision greater than the given one.

In addition, we have a large set of similar tables with similar use cases and all of them have the same issue.

What can I do to solve this issue?

I could try to use OPTION (RECOMPILE), which is not easy with Linq-To-Sql, but it also doesn't look really optimal in performance terms.

Also I could use sp_create_plan_guide or hack Linq-To-Sql even more and try to WITH (INDEX(...)) clause to force using the second plan, but as I said there is a lot of tables with the same core structure, so this way looks like a lot of manual work.

Generally, my questions:

Can SQL Server understand that the plan stored in the cache is not optimal for given parameters and don't use it?

Are there some best practices of handling parametrized queries if their optimal execution plans depend on parameters?


texts: Dynamic config file generation using powershell

texts:
I am trying to build a config file for a monitoring app for all our SQL Servers. For this I need to add all instance details of a server in a config file(i.e. one file will be generated per server and not instance). So even if server has multiple instances, it will only have one config file with its details of all instances mentioned in it. For a server with only single instance it is not a problem. What I need help is on a server with multiple instances. For e.g. If server1 has two instances Instance1 and 2 then config file should look  like below and exported to a text file. Likewise for a single instance server there will be only 1 line without comma at the end and exported to a text file.
"Server=Server1\Instance1;Port=50001;User ID:sqltest;password:****",
"Server=Server1\Instance2;Port=50002;User ID:sqltest;password:****"

I am getting all server instance details from a SQL Server database stored in format ServerName, InstanceName, PortName. Could someone please guide me how do I achieve it using Powershell? Thanks!
I have already tried something like below. But it has multiple issues like not getting correct port number, adding output to newline everytime there are multiple instances, not having comma for the last instance and generating file for each server rather than a each instance.
$serverlist = Invoke-Sqlcmd -Query "SELECT servername,instancename,portname from sqlmonitor" -ServerInstance "MainInstance"$textoutput = "This is a test."
Foreach {$server in $serverlist.servername}
{
    $textoutput += "`nServer=$Server;Port=$serverlist.portname;User ID:sqltest;password:****"}
$textoutput | Out-File "C:\temp\$server_config.txt"

Sample data from sqlmonitor table:

servername,instancename,portname
    server1,inst1,50001
    server2,inst1,50001
    server2,inst2,50002

I am still at an initial PS learning phase and know my code has lot of issues. :)


texts:
In our AlwayOn environment I have two SQL servers 2014 Ent.edition (SERVER A, and SERVER B). I setup a transaction replication for [database A ], pointing to listener which shouldn't cause any issue if a failover happened. Tested, worked just fine after a failover, but when I shut off one of the servers in AOAG ,in test environment, replication stops working. There is no error on the replication monitor. For testing purpose,I have inserted few records on the database it works fine on AOAG, but on the Subscriber the data modification is not replicated until I bring up the other server. Once the server is up, then the replication start to work. I'm pretty sure, I might be missing something, this can't be this way by design.

Any Idea/recommendation ?

Here is what I have for Replication on AOAG.

Distribution Server A ( contains Distribution database ) - SQLSERVER

This on a remote server.

two subscribers - Sv1 and sv2 - SQLSERVER 2008R2 STANDARD
Pbulishers ( SERVER A and B - from AOAG ) - SQLSERVER 2014


texts: GoldenGate: How to encode Oracle RAW columns as hex strings?

texts: I am using GoldenGate to extract an Oracle table that contains RAW data.  The destination is a flat CSV file.  How can I encode the RAW columns to hex strings so that they can be written to the CSV file without disrupting the CSV format?


texts:
Need more help from you brilliant folks.

I am trying to import a CSV file to a SQL table with this code:

$SQLServer = "MYSQLINSTANCE"
$SQLDatabase = "DBATools"
$SQLString = "Server=$SQLServer;Database=$SQLDatabase;Integrated Security=True;"
$SQLTable = "dbo.MyTable"
$CSVFile = "\\remotesrvr\OutputShare\Output.csv"
$Header = 'RecordedDTM','TablespaceName','PhysicalFileName','MaxSize','UsedSize','FreeSize','PctUsed'
Import-CSV -Path $CSVFile -Header $Header | Select-Object -Skip 1 | ForEach-Object {Invoke-SqlCmd -ConnectionString $SQLString -Query "INSERT INTO $SQLTable VALUES ('SVPPDRMDB01','$($_.RecordedDTM)','$($_.TablespaceName)','$($_.PhysicalFileName)',$($_.MaxSize),$($_.UsedSize),$($_.FreeSize),$($_.PctUsed))"}

It runs successfully in a Powershell prompt, but when I create a SQL job of type Powershell, I get the following error:

Unable to start execution of step 1 (reason: line(7): Syntax error).  The step failed

Not a lot of help, huh. What am i missing?


texts:
I was wondering what to do when for example your table is reaching it's maximum index values, I was using this Query to determine the index usage per table:

SELECT
  c.TABLE_NAME,
  c.COLUMN_TYPE,
  c.MAX_VALUE,
  t.AUTO_INCREMENT,
  IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) AS "Usage (%)"
FROM
  (SELECT
     TABLE_SCHEMA,
     TABLE_NAME,
     COLUMN_TYPE,
     CASE
        WHEN COLUMN_TYPE LIKE 'tinyint(1)' THEN 127
        WHEN COLUMN_TYPE LIKE 'tinyint(1) unsigned' THEN 255
        WHEN COLUMN_TYPE LIKE 'smallint(%)' THEN 32767
        WHEN COLUMN_TYPE LIKE 'smallint(%) unsigned' THEN 65535
        WHEN COLUMN_TYPE LIKE 'mediumint(%)' THEN 8388607
        WHEN COLUMN_TYPE LIKE 'mediumint(%) unsigned' THEN 16777215
        WHEN COLUMN_TYPE LIKE 'int(%)' THEN 2147483647
        WHEN COLUMN_TYPE LIKE 'int(%) unsigned' THEN 4294967295
        WHEN COLUMN_TYPE LIKE 'bigint(%)' THEN 9223372036854775807
        WHEN COLUMN_TYPE LIKE 'bigint(%) unsigned' THEN 0
        ELSE 0
     END AS "MAX_VALUE"
   FROM
     INFORMATION_SCHEMA.COLUMNS
     WHERE EXTRA LIKE '%auto_increment%'
   ) c

   JOIN INFORMATION_SCHEMA.TABLES t ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)

WHERE
 c.TABLE_SCHEMA = 'Database_Name'
ORDER BY
 `Usage (%)` DESC;

Which would return something like this:

+------------------------+-------------+------------+----------------+-----------+
| TABLE_NAME             | COLUMN_TYPE | MAX_VALUE  | AUTO_INCREMENT | Usage (%) |
+------------------------+-------------+------------+----------------+-----------+
| app_crontasks          | int(11)     | 2147483647 |        1536304 |      0.07 |
| app_alerts             | int(11)     | 2147483647 |              1 |      0.00 |
| app_apiclients         | int(11)     | 2147483647 |              2 |      0.00 |
| app_replicates         | int(11)     | 2147483647 |              1 |      0.00 |
| ...                    | ...         | ...        | ...            | ...       |
+------------------------+-------------+------------+----------------+-----------+

In case it fills up to, for example, 75% then we would need to do a clean up of the database? How am I able to safely do that? Would it affect the foreign keys assigned?


texts: Designing a dimensional DB off a normalized source that already implements SCD's

texts:
I have built an SSIS ETL to integrate various data sources (one from MySQL, two from SQL Server) into a single SQL Server relational and normalized database, which I've called [NDS].

The SSIS ETL handles type-2 updates, and so the [NDS] generates surrogate keys and SCD tables include an [_EffectiveFrom] timestamp and a nullable [_EffectiveTo] column, and there are constraints for the natural keys and beautiful foreign keys linking all the data together.

Now, I wanted to build an SSAS dimensional database off of it, and it didn't take too long before I realized I was setting myself up for a snowflake schema:

So I'm thinking of adding a new [DDS] (relational) database, to create the actual dimension and fact tables that will feed the DSV's for the SSAS db.

This [DDS] database would be as denormalized as humanly possible, so as to "flatten" the facts and dimensions (like, [OrderHeaders]+[OrderDetails] into an [Orders] fact table, and [CustomerStores]+[Customers]+[SalesReps] into some [Customers] dimension table) - doing this should not only make it easier for me to build the dimension hierarchies in SSAS, it should also make it easier to come up with an actual star schema.

I have a few questions though:

Can I reuse a subset of my existing surrogate keys? I'm thinking to take the existing key for the most granular level and make that the dimension key. Is that a good approach, or should I just ignore the [NDS] surrogate keys and make the [DDS] (relational db) generate a new set of surrogate keys?
How to handle SCD's? For example, "Materials" and "Suppliers" will generate new records in [NDS] when some specific fields change in the source system... I think I'll have to design the SSIS ETL to only load the "last image" records into the [DDS] db, and then re-implement the type-2 updates in that process, i.e. treat the [NDS] as a "source system" that keeps history, while duplicating everything in this [DDS] database. But then, why would I need to keep history in the [NDS] and the [DDS]? Clearly something's not right.

Am I setting myself up for a Big Mess™, or I'm on the right track?


texts: MySQL Create Table with Partition Slow on server machine

texts:
Following create table statement with Partition

runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk
runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz , 615MB Ram, single disk
While takes 100 Seconds on my server machine Windows server 2008 R2 Standard, xeon 2.4 2 Processors, 32 GB Ram, ATA SCSI Mirror drives

I am not able to understand why there is so much performance difference ..

CREATE TABLE IF NOT EXISTS `TestSpeed`.`CTM` (
  `ServerID` BIGINT NOT NULL,
  `ClientID` BIGINT NOT NULL,
  `CommunityID` INT NOT NULL,
  PRIMARY KEY (`ClientID`,`CommunityID`),
  UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
  INDEX `IX_CTM_ClientID` (`ClientID` ASC))
PARTITION BY HASH (CommunityID) PARTITIONS 300;


texts:
I'm trying to import the database to another instance. So I imported the database with restore controlfile, restore database and recover database command.
When I try to start the database, it complains:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 14134
Session ID: 969 Serial number: 5700

How can I finish the recover process? I can do startup upgrade, but what next? I'm not running a upgrade job, just migrating the database to a new server.


texts: Single data type for imprecise date values, as allowed by ISO 8601

texts:
How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?

ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’, ‘1964-05-02’ are all valid representations of a value, in increasing precision. The Python ‘datetime’ types also allow values with reduced precision in this way.

PostgreSQL native time types doesn't allow reduced precision

In the native date type, every element of a date must be present or the value is rejected. Setting the elements below the desired precision level to ‘00’ also fails.

=> SELECT CAST('1964-05-02' AS DATE);
    date
------------
 1964-05-02
(1 row)

=> SELECT CAST('1964-05' AS DATE);
ERROR:  invalid input syntax for type date: "1964-05"
LINE 1: SELECT CAST('1964-05' AS DATE);
                    ^
=> SELECT CAST('1964' AS DATE);
ERROR:  invalid input syntax for type date: "1964"
LINE 1: SELECT CAST('1964' AS DATE);
                    ^
=> SELECT CAST('1964-00-00' AS DATE);
ERROR:  date/time field value out of range: "1964-00-00"
LINE 1: SELECT CAST('1964-00-00' AS DATE);
                    ^
HINT:  Perhaps you need a different "datestyle" setting.

Expected behaviour for a reduced-precision date and/or time type

Is there a simple, standard way to support entry of ISO 8601 date values with reduced precision into a PostgreSQL date and/or time type?

Creating a type for this is possible, but I don't know how. Of course, I need the values to be range checked and deal with timezones and compare sensibly with other time values all the other useful things the built-in types do.

What I expect is that, just as the value ‘1964-05-02’ refers to the entire interval between 00:00:00 on that day until 00:00:00 the next day, a reduced-precision value would simply represent a larger interval: ‘1962-05’ refers to the entire interval between 00:00:00 at the beginning of May 1962 until 00:00:00 on the first day of June of 1962.

An example of what I'd like to see:

=> SELECT CAST('1964-05-02 00:00' AS TIMESTAMP) = CAST('1964-05-02 00:00:00' AS TIMESTAMP);
 ?column?
----------
 t
(1 row)

=> SELECT CAST('1964-05' AS TIMESTAMP) = CAST('1964-05-02' AS TIMESTAMP);
 ?column?
----------
 t
(1 row)

Currently the former behaves as above; the latter complains about “invalid input syntax for type timestamp”. To my eye, they're both cases of reduced-precision values behaving sensibly when compared to finer-precision values.

The meaning of 1964-05 in ISO 8601 includes the more-precise values 1964-05-02 and 1964-05-02 18:27 and 1964-05-23. So those should all compare equal.


texts: How safe are PostgreSQL triggers against attacks?

texts: in my PostgreSQL database I setup some triggers to prevent DELETE and EDIT operations (the triggers make sure that these operations lead to nothing). However, I'm wondering how safe these triggers are? Is it possible to circumvent these triggers and delete data by trying to illegally hack the data?


texts: How to make MySQL 5.1 connection ignore kills?

texts:
According to MySQL 5.1's manual, section 13.7.6.4 KILL Syntax, when you kill a connection, a thread-specific kill flag is set for the thread. The flag will then be checked from time to time, even during some long-running queries, so that it can interrupt the query.
Is there a way to instruct a thread not to check the kill flag while inside some kind of delimited critical section?
What I'm looking for is something like:
BEGIN IGNORE_KILLS;
-- if this thread is killed here, it will simply ignore it
END IGNORE_KILLS;
-- here, the thread would be able to tell if someone tried to kill it previously

Assuming that it does not exist, or that someone could offer me a better solution to my problem, let me explain why would I want it.
I have an application that must react to lines that are inserted in a table, and this must be scalable. According to MySQL expert Baron Schwartz, one should not keep polling the database, and he proposes, as an alternative, to use SELECT SLEEP(...)/KILL.
The problem is that after the connection waiting on select sleep(...)  is notified (by being killed) that it has work to do, I'd like to guarantee that, during its work (what I called critical section above), it won't stop doing what it should do even in the case that the killer thread goes crazy and kills it again.


texts: Changing Postmaster File Name in PostgreSQL

texts:
I have two instances of PostgreSQL with master-slave replication between them and want to use GlusterFS. Everything works fine until the second instance wants to store the data in the GlusterFS. because the postmaster file has the same name in both instances they conflict with each other.

Is there a way to rename the postmaster.pid file so it won't conflict with each other?

Or is there any other way to use two PostgreSQL instances with GlusterFS?.


texts: Does PostgreSQL write first to WAL and then to data files?

texts:
This is regarding the synchronous_commit in Postgres.

The synchronous_commit documentation says:

  Specifies whether transaction commit will wait for WAL records to be
  written to disk before the command returns a "success" indication to
  the client

So my understanding is that a transaction will be written to the database first and then WAL files. synchronous_commit determines whether this WAL write is done synchronously or asynchronously.

My problem is, from following documentation:

  Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage.

I got the understanding that Postgres first writes transactions to WAL files and then to the datafile (database).

Which one is the correct? Does Postgres write first to WAL and then to data files? Can any one give more details about how synchronous commits happen and how this relates to WAL?


texts: Can I edit the batches received by SQL Server on the fly?

texts:
I am supporting a third-party application which uses a SQL Server 2016 backend. We have no source code access, and the third-party support is slow to respond. Most of the time, I can index and magic my way out of the sometimes terrible queries that it throws at the database, but there have been multiple occasions where there is quite literally nothing I can do (within constraints of time,  money, and storage).  The query is just wrong.

Is there a way to administratively substitute the queries submitted by the application for other ones?  I am imagining some sort of regex match performed against the batch if the application name matches.

Example:

-- Submitted by application
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}'

-- What I want the query to be (to avoid spurious updates to Table)
-- (Table is large, and the application is submitting this query on every page view)
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}' and IsBilled = 1

-- Proposed regex
-- Match
update Table set IsBilled = 0 where ID = '(\{[A-F0-9\-]+\})'
-- Replace
update Table set IsBilled = 0 where ID = '$1' and IsBilled = 1

Some of my conceived uses would be to avoid useless updates, others would be in rephrasing where clauses to maintain sargability.  I am sure there are other scenarios that would come to mind.


texts: Migrating 2+ GB Oracle CLOB column to SQL Server VARCHAR(MAX)

texts: I have a column on an Oracle db containing HTML data with embedded images that result in some records being over 2GB in size for that column. I have successfully migrated the rest of the database to SQL Server except for this CLOB column. With SQL Server varchar(max) unable to handle anything over 2GB in size, what are my options?


texts: AlwaysOn High Availability MDF and LDF file locations

texts:
We have 4 replicas with 03 replica in read only mode with manual failover.

We have some drive issues and we need to rearrange the MDF and LDF files.
On the primary we have 2 drives I and J. The I drive houses the MDF/ndf and LDF files. On the J drive are the tempdb files.

In all other 03 replicas we have only I drive with all MDF/ndf/ldf and Temmpdb files.

Currently the setup is working without any issues.

In our primary replica the drive space is running low with 10% left. Can we move the ldf file to the J drive which is 80% free?

Please note that in all 03 read replicas we do not have a J drive.


texts: SQL: get product stock of all product types in a single query

texts:
I want to get stock information for all the products depending upon the product type.
The tables are products, product_stocks, complex1_childs & complex2_childs.
products

| product_id | type      |
|------------|-----------|
| 100        | simple    |
| 101        | simple    |
| 103        | complex_1 |
| 104        | simple    |
| 105        | complex_2 |
| 106        | complex_1 |
| 107        | simple    |
| 108        | simple    |
| 109        | simple    |

The available product types are:

simple
complex_1
complex_2

complex_* type is a compound product which includes other products as a child.

product_stocks

| product_id | qty |
|------------|-----|
| 100        | 10  |
| 101        | 50  |
| 104        | 10  |
| 107        | 25  |
| 108        | 100 |
| 109        | 40  |

As you can see only simple product type have qty info.

complex1_childs

| parent_id | child_id |
|-----------|----------|
| 103       | 101      |
| 106       | 107      |
| 106       | 101      |

Child products are always simple type.

complex2_childs

| parent_id | child_id |
|-----------|----------|
| 105       | 107      |
| 105       | 108      |

Child products are always simple type.

Now I want to get stock information for all the products as:

| product_id | final_qty |
|------------|-----------|
| 100        | 10        |
| 101        | 50        |
| 103        | 50        |
| 104        | 10        |
| 105        | 125       |
| 106        | 75        |
| 107        | 25        |
| 108        | 100       |
| 109        | 40        |

Note that qty for complex_* product types are calculated as sum of their child products' qty.


texts: MongoDB Collection Level Permission Issues

texts:
I am trying to set up a MongoDB user that only has access to certain collections across several databases.  The issue is that these databases are created dynamically at runtime.
I decided to employ collection level access in order to do this with the following permission on the user role:
"db" : "admin",
"privileges" : [
    {
        "resource " : {
            "cluster" : true
        },
        "actions" : [
            "listDatabases"
        ]
    },
    {
        "resource" : {
            "db" : "",
            "collection" : "packages"
        },
        "actions" : [
            "find",
            "insert",
            "remove",
            "update",
            "listCollections"
        ]
    }]

What I want is for any user with this role to be able to read/write/remove in any database but only on the packages collection inside of that database, and be unable to read or write to any other collection.  When I launch mongod in --auth I do not see any databases besides admin.
I am certain this is just me being unable to figure out exactly what actions / resources / roles I am supposed to grant.


texts: Use of SET INTEGRITY for DETACH PARTITION

texts:
I have some partitioned tables with DB2 LUW 10. We have one partition for each data.I am reviewing a DB2 stored procedure that runs daily detaching old data partition and creating a new partition because some days this stored procedure takes some hours to complete but usually it only takes some seconds. We found that the delay occurs in the detach processing,

According to some logic the stored procedure calculates how many partitions are going  to be detached (only one usually). For each partition to be detach the stored procedure issues

ALTER TABLE DETACH;
COMMIT;
SET INTEGRITY FOR TABLE XXX  IMMEDIATE ALL UNCHECKED;

Please can you explain me the function of the commit and set integrity commands in this stored procedure?

Under which conditions can this stored procedure takes some hours to complete the partition detach ?

Thanks you very much for your help


texts: What is the difference between mongod in standalone vs replica set

texts:
I've been running Mongodb as a replica set on 3 servers (primary, secondary and arbiter) but because I have to downscale I'm converting the primary to a standalone server. Before this change the replica set secondary was just a "backup" in case of primary failure, so no distribution of reads.

I was expecting the CPU load on my primary server to go down or stay the same. However it went up a bit instead (before my "uptime" load rating would be around 0.01, now it's usually around 0.1- 0.2). Not a problem, but stil significant.

So what exactly is the difference in running mongod as a replica set vs a standalone server? And are there drawbacks to running mongod as a single node "fake" replica set?

I'm using the MongoRocks storage engine.


texts: MySQL import and export data without update on insert

texts: I have a table that updates a timestamp column on every insert. This timestamp is indexed uniquely with a few other columns and I am unable to import this table. I guess the mysql dump/import inserts each row and some of the timestamps overlap because they are updated to the current timestamp. Is there a way to import and ignore the on update: current_timestamp() function that I have?


texts: How to set up data entry in many-to-many database in Access 2010 with forms/subforms?

texts:
I have a simple two table database in Access 2010 tracking landowners and properties.  They are related in a many-to-many (M:M) relationship with a link table.  Forms for viewing data have been generated from two queries following tutorial here.  There are two forms with subforms:
'landowners and their associated properties'
and 'properties and associated landowners'.

I would like the option for users to either (1) add a new record to the subform (creating a relationship record in the link table and a new record in the source table for the subform)  or (2) select from a list of existing records to relate to the property / landowner.

So far, I have (1) working.  In the subform, if a new record is inserted it populates the appropriate table and creates a relationship.  If the record is deleted from the subform, the relationship is deleted from the link table but the record is NOT deleted in the subform's source table (child table) -- as desired.

I have attempted a drop-down combobox providing the option to select from existing records in the subform's source table but I get a duplicate error.  Any help?


texts: Why are the initial queries up to minute slow on a 400M record PostgreSQL table?

texts:
Consider the following query. The table consists of almost 400 million records and has appropriate indexes.
select
      r.id as id,
      r.text as body,
      r.answer as answer
from result r
where
  r.p_id = 12345 and
  r.dirty is null

p_id is the variable part of the query which selects the results of that poll. By querying a random p_id, the server takes as long as 60s to fetch results (~300K records). On consecutive calls with the same p_id, it replies almost instantly. I have validated those numbers with explain analyze, and they are both reported under Execution time (i.e. I'm not accounting for network transmission). It kind of makes sense, even though I don't know the internals of the database.
What I'm trying to improve is the initial time, but I'm not sure where to start. Can the query be further improved? Should I look into the configuration of the server? For the record, the machine seems idle with not a lot IOPS and load.


texts: XML queries with case insensitive tag- which is the best query

texts:
I had asked question about XML queries with case insensitive tag earlier and i found a solution too. But i also found some other solution.
So table was something like

DECLARE @myTable TABLE ( yourXML XML )

INSERT INTO @myTable SELECT '<z>1<c>2</c></z>'
INSERT INTO @myTable SELECT '<Z>1<c>2</c></Z>'

And all the below solutions return what i wanted(case insensitive tag)

-----Solution 1----------

SELECT * FROM @myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = "z"]/*[lower-case(local-name(.)) = "a"] where  ( ($x/*[lower-case(local-name(.)) = "b"][1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 2----------

SELECT * FROM @myTable
WHERE
(CONVERT(XML,LOWER(CONVERT(VARCHAR(MAX),[yourXML]))).exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 3----------

SELECT * FROM @myTable WHERE
([yourXML].exist('for $x in (/Z/A,/z/a) where  ( ($x/b[1],$x/B[1]) = 1 )  return $x') > 0 )
-------------------------

Now i want to know which one is better to use by considering its all X-query commands(support for value(),exist(),count(),query() etc), Performance,Efficiency etc.


texts:
The following Query Returns 3 Million records, I want to display only Unique Assets_ID, how to modify this query, so there are no duplicate records?

Select DISTINCT
(a.Assets_ID),
   a.Offices_OfficesID,
   a.AssetInformation_AssetInformationID,
   a.Serial,
   a.AddQuantity,
   a.MEI,
   a.ICCID,
   a.AssetStatus,
   b.AssetType,
   c.Category,
   d.AssetCategories_AssetCategoriesName,
   m.ManufacturerName,
   x.Vehicle_ID,
   x.Emp_Id,
   x.Phone,
   x.QuantityforIssue as qty,
   x.AssignedStatus,
   x.AssignmentDate as stat_date,
   d.AssetCategories_AssetCategoriesName as asst_name,
   u.FirstName,
   u.LastName
FROM
   Assets as a
   JOIN
      AssetInformation as b
      ON a.AssetInformation_AssetInformationID = b.AssetInformation_ID
   JOIN
      AssetCategories as c
      ON c.AssetCategories_ID = b.Category
   JOIN
      AssetStatus as d
      ON d.AssetStatus_ID = a.AssetStatus
   JOIN
      Manufacturer as m
      ON m.Manufacturer_ID = a.Manufacturer
   JOIN
      AssetAssignment as x
      ON (x.Asset_ID = a.Assets_ID
      AND a.Offices_OfficesID = x.Offices_OfficesID)
   JOIN
      Users as u
      ON (u.UserID = x.Emp_Id
      or x.Emp_Id = 0)
WHERE
   a.Offices_OfficesID IN
   (
      1
   )

Assets table

DESCRIBE Assets
Assets_ID   int(11) NO  PRI NULL    auto_increment
Offices_OfficesID   int(11) YES MUL 0
AssetInformation_AssetInformationID int(11) YES MUL 0
ToolKits_ToolKitsID int(11) YES     0
Serial  varchar(255)    YES     NULL
qued    int(11) NO      NULL
MEI varchar(255)    YES MUL NULL
ICCID   varchar(255)    YES     NULL
AddQuantity int(11) YES     1
AssetCategories_AssetCategoriesID   int(11) YES     0
User    varchar(50) YES     NULL
Manufacturer    varchar(50) YES     NULL
AssetStatus varchar(50) YES     NULL
DateAdded   datetime    YES     NULL
Comments    text    YES     NULL


texts: What might be the cause of regular periodic slow transactions on an OLTP system?

texts:
I have a two node Oracle RAC cluster with that runs an oltp application against it.  I have both a production and a duplicate test environment with the same setup. Upon running stress tests on the Test environment (about 160 tps), The average latency is about 70ms.  I noticed that when I examined the maximum transaction time within each minute, that there is a regular spike in the maximum transaction time every 5 minutes of several transactions that take about 1200-1500 ms.  I've traced the individual long transactions down to the second  and them seem to last for anywhere from 3-5 seconds.  There don't seem to be any regular running jobs that would effect the transactions.   In  environment, we see the same thing and in fact once in a while, we see bigger spikes of transactions taking 15-20 seconds (15000-20000 ms) to complete.  While our production environment is in a pilot mode right now, and only pushes about 4 tps, we do still see these spikes where maximum transaction lengths reach 1200-1500 ms, and they also seem to occur on 5 minute intervals.

I have performed ASH analytics on the small few second intervals, and it seems that there is no common wait event that occurs during these spikes.  Sometimes we see "log file sync" events, sometimes we see "gc busy" or other "gc" wait events.  It seems to vary.  I'm just trying to figure out how to diagnose the underlying cause of this periodic slowness.  Any ideas would be appreciated.


texts:
I currently have Page Faults/sec avergaing 1269 while my Page Reads/sec is averaging 0.3.

I am just trying to interpret what this means.  I assume I am getting lots of soft page faults in this case and the issue the user is experiencing with slowness is due to the application having to wait on pages to become free in memory?

In which case should I be tracking locking and blocking?


texts:
I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to

get a good read-write (50/50-balance) performance
keep a decent performance for overall conventional system usage.

My idea is to have two main partitions in an SSD drive:

[A] an ext4 for the root system ("/") with OS recommended/default settings.
[B] an ext4 for postgres database files ("/var/lib/pgsql") with a large block size (eg. 1MB) and a large readahead size (how much?) and proper flags (which?).

I have no clue whether separating postgres db files from general system is a good idea. In such a case, any piece of advice on [B] block size, readahead size, filesystem flags?

Furthermore any helpful hint?


texts: How to verify the password generated by ENCRYPT?

texts:
I use the ENCRYPT function to generate password for my email users.

SELECT ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))

This function will generate a long encrypted string which looks something like this:

$6$103b1e8ad6b6db7d$CyTHP3ooA8N8uWtKmQOZFjRkTss60o3ckb6CPYG01uJqiLjGMFU0y9jtCA13MFtJ3LiPrKv5JVcfRaYg8B7YL0

Now my question is how I can verify the password? What I want to do is I store the encrypted password in the database. And I want my user to be able to modify their password as long as they provide the correct old password. So when the users provide their old passwords, I need to verify whether the old password they provided is correct or not.


texts:
I'm trying to help a teammate walk object dependencies in SQL Server. I've had some success using things like sys.sql_expression_dependencies (and other related DMV/DMFs) but it's falling short due to a lot of weird edge cases (cross database references, inconsistent naming conventions, etc.)

I'm looking into using SqlSMO to walk object dependencies, and so far, it's working pretty well; at least for object dependencies. I give it a procedure name, and use a DependencyWalker to tell me things like views, functions and procedures called therein.

But now I need to also know which columns are referenced in the proc. So instead of just knowing my inputProc references someTable, I'd like to know that it references someTable.A and someTable.B. So far I haven't been able to find a way to do this using SMO. Anyone know if it can be done?

This is an excerpt of C# code which returns the object references; not sure if and how to modify it to include column-level dependencies as well.

public static void Walk()
{
        Server server = new Server(@"localhost\koala");
        Database db = server.Databases["test"];
        Scripter scr = new Scripter(server);
        scr.Options.WithDependencies = true;
        scr.Options.NoViewColumns = false;
        Urn[] urns = new Urn[] { db.StoredProcedures["xdb"].Urn };

        DependencyTree dt = scr.DiscoverDependencies(urns, true);
        DependencyWalker dw = new DependencyWalker(server);
        DependencyCollection dc = dw.WalkDependencies(dt);
        foreach (var item in dc)
        {
            Console.WriteLine(item.Urn.Value);
        }
}


texts: When you make a trivial change to a view, do permissions get reset?

texts:
Just wondering - I made a trivial change to a view.

No additional tables were added, although I guess the logic was changed a bit and a subquery was added --- but again, no additional base tables.

Suddenly, it seems the permissions to other users on the view have changed/ become restricted. What's going on here?


texts: How can we ensure security and integrity of data stored in MongoDB?

texts: Since MongoDB lacks support of transactions how one can ensure consistency of data stored in MongoDB database?


texts: How can I manage PL/SQL package source at the method level?

texts: In our ERP application suite, we have a very small number of PL/SQL packages that are large and monolithic in nature, containing a wide assortment of functionality. (This is because each package requires additional configuration in the ERP suite itself.) Putting the entirety of an API in source control is prohibitive because simultaneous development projects can leapfrog along the pipeline. Right now, we manually deploy to QA and PROD at the method level, modifying and adding methods to the package by copying them out of text files. (We work with them using PL/SQL Developer by Allround Automations.) This is time-consuming and error-prone, and I'd like to automate this process in a DevOps fashion. Using an existing utility, is there a way to store package methods as discrete files in source control and deploy them to their package individually?


texts:
My understanding is that, in a MS SQL Server Management Studio window, after doing a "begin transaction" and then making some changes like add data to a table , you can only query that table and those changes from the same window until you do a "commit transaction".

Is there any way to do a query from another source prior to doing the "commit transaction"?

Specific to my current goal and to add some context.  I do some SQL queries from Excel Power Query.  I'd really like to be able do do these queries prior to the "commit transaction" so that I can do some analysis and figure out if I should be doing a rollback instead of a commit.


texts:
As of this morning we have had thousands of failed login attempts to our SQL Server 2012 box.

Error :

  Login failed for user 'Domain\User'. Reason: Infrastructure error occurred.
  Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]

  Error: 18456
  Severity: 14
  State: 1

I have checked all the logs, permissions, and scoured forums for help on this issue but to no avail.

  Server Login & Database User accounts are enabled
  Server Login is mapped to the database that it has access too
  Windows & SQL Server Authentication is applied
  SA account is enabled
  No orphaned users
  etc etc...

Any ideas ?


texts: SQL SERVER ERRORLOG FILE UTILIZING THE FREE SPACE

texts:
I'm stuck up in one of the issue.
I'm Using SQL SERVER 2008 R2.
The SQL SERVER Log file is utilizing the disk space.
When ever I clear the space in the DRIVE, the SQL SERVER Error log file starts to utilize the free space.
Log file path is available in C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50. WINCC\MSSQL\Logs\ERRORLOG
Kindly, help me to resolve the issue.


texts: tuning feed item's table query with multiple condition

texts:
I have a query like this:

SELECT *
FROM `rss_item`
WHERE `feed_id` = 1 and `timestamp` < 1350698635
ORDER BY `rss_item`.`timestamp` ASC

MySQL: 5.1; table engine: innodb

Explain result:

id  |  select_type  |  table     |  type   |  possible_keys  |  key        |  key_len |  ref     |  rows    |  Extra
1   |  SIMPLE       |  rss_item  |  range  |  timestamp      |  timestamp  |  4       |  NULL    |  133534  |  Using where

Table schema:

CREATE TABLE `rss_item2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(1000) NOT NULL,
    `description` longtext NOT NULL,
    `url` varchar(200) NOT NULL,
    `url_crc` bigint(20) NOT NULL,
    `date` datetime NOT NULL,
    `timestamp` int(11) NOT NULL,
    `feed_id` int(11) NOT NULL,
    `image` varchar(250) NOT NULL,
    `goto` int(11) NOT NULL,
    `likes` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `timestamp` (`timestamp`,`feed_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2425930 DEFAULT CHARSET=utf8

This query with 400,000 rows takes 4 seconds. I added a composite index on feed_id and timestamp fields. How can I tune this query?


texts: Is there a configuration option that lets me limit the percent of a hard drive that Cassandra uses?

texts:
I'm doing some testing with a small hard disk and I'd like to basically stop Cassandra in its tracks if a write or compaction or any other service brings that usage of the disk above a certain percentage. Is there any way to do that? How could I approach this problem?

Thank you! :)


texts: SQL Agent Job step using Powershell can not find Network Share

texts:
I have a SQL Job step running powershell that cannot find a network drive in one of its steps For brevity, I am calling this server Production, the network share is D:\ on Production and the Job is running on Production. The powershell Job step with issues in running under the SQL Server Agent Service Account.

The first Job step creates an input file on a network share. The second Job step queues an email based on information in the first steps output file. This job is run on server we will call Production. The network share is based the server were the Job is run specifically what we will call here the D:\ drive. When writing the Job step if I use SELECT-STRING .. -Path "\\network share.." my job fails it cannot find the Path, but if I use SELECT-STRING .. -Path "D:.. it runs successfully!

I kept the Job tokens in the code below in case the option for creating a script comes up. I have a solution here, but there is a possiblity I will need to use this script on a different server that does not a have a D:\ and I want to use the network share name for maintainability.

First Job step creates file(run as CmdExec):

SQLCmd -S <server> -U <me> -P <Passw0rd> -d <DB> -w 1000 -m-1 -b -i "\\<network share>\Results\20120117_02 Err Message Monitor.sql" -o "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Second Job step run as Powershell(specific problem line listed below)

$sel = SELECT-STRING "Nothing to do" -path "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"
if ($sel -eq $null) {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'My@email.com',
    @Subject = '(Prod) Message Monitor',
    @AttachmentList = '\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}
else {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'Me@email.com',
    @Subject = 'Nothing to do (Prod) Message Monitor',
    @AttachmentList = '\\<network share\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}

Problem is here

$sel = SELECT-STRING "Nothing to do" -path "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

This works:

$sel = SELECT-STRING "Nothing to do" -path "D:\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"
if ($sel -eq $null) {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'My@email.com',
    @Subject = '(Prod) Message Monitor',
    @AttachmentList = '\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}
else {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'Me@email.com',
    @Subject = 'Nothing to do (Prod) Message Monitor',
    @AttachmentList = '\\<network share\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}

There are no Proxy accounts.

How can I get my -Path to access a network share in this Job?


texts: How to differentiate between the result sets retrieved for multiple OR conditions?

texts:
I've a huge dataset which I'm quering with multiple OR conditions. Though I'm getting all the results, but I want to differentiate between the results that are retrieved for a certain where condition. Is there any way to do that? Here is an example -

Select * from table where col1 like '%text1%' or col1 like '%text2%'

This query is retrieving entire matching dataset, but how can I differentiate between the results that are matching text1 or text2? Thanks in advance.


texts:
I am working on a system where sometimes request/sec increase up to 680. It's a .NET 4.0 web api. In normal situations when requests are fewer, everything is fine, but sometimes when the number goes high database connectivity issues will happen and I can see the following error in log files

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)

Here are my web.config settings

Min Pool Size=50;Max Pool Size=101;
<serviceThrottling maxConcurrentCalls="100" />

Your help will be appreciated. I am new to ask question so please ignore if I did any mistake.


texts: Pagination - Text comparison with greater than and less than with DESC

texts:
I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if you are sorting ASC or DESC. This works great for integers and dates but I am wondering how best to do it with text columns, specifically for the first page.

For example, for the first page when sorting by name it would be

SELECT *
FROM users
WHERE first_name > ''
ORDER BY first_name ASC
LIMIT 5;

Then the next page would be

SELECT *
FROM users
WHERE first_name > 'Caal'
ORDER BY first_name ASC
LIMIT 5;

This works great. I am unsure about DESC order though. This seems to work but I am unsure if it is 'correct'.

 SELECT     *
 FROM   users
 WHERE  last_name < 'ZZZ'
 ORDER BY last_name DESC
 LIMIT 5;

Second page

SELECT  *
FROM    users
WHERE   last_name < 'Smith'
ORDER BY last_name DESC
LIMIT 5;

P.S. I am using the jooq support for the seek method and prefer to not have to hack around the native support, so ideally there is a proper parameter to put in the 'ZZZ' place above. i.e. there WHERE part of the clause is mandatory.


texts: PostGIS Intersects Query takes lot of time to get the result

texts:
I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB.

I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables contain geometry(multipolygons).

-- Table: evidensapp_seniangcbr

-- DROP TABLE evidensapp_seniangcbr;

CREATE TABLE evidensapp_seniangcbr
(
  id serial NOT NULL,
  hazard character varying(16) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_seniangcbr_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE evidensapp_seniangcbr
  OWNER TO postgres;

-- Index: evidensapp_seniangcbr_geom_id

-- DROP INDEX evidensapp_seniangcbr_geom_id;

CREATE INDEX evidensapp_seniangcbr_geom_id
  ON evidensapp_seniangcbr
  USING gist
  (geom);
ALTER TABLE evidensapp_seniangcbr CLUSTER ON evidensapp_seniangcbr_geom_id;

-- Table: evidensapp_polystructures

-- DROP TABLE evidensapp_polystructures;

CREATE TABLE evidensapp_polystructures
(
  id serial NOT NULL,
  bldg_name character varying(100) NOT NULL,
  bldg_type character varying(50) NOT NULL,
  brgy_locat character varying(50) NOT NULL,
  municipali character varying(50) NOT NULL,
  province character varying(50) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE evidensapp_polystructures
  OWNER TO postgres;

-- Index: evidensapp_polystructures_geom_id

-- DROP INDEX evidensapp_polystructures_geom_id;

CREATE INDEX evidensapp_polystructures_geom_id
  ON evidensapp_polystructures
  USING gist
  (geom);
ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

With my query below it takes almost 15mins. to get the result.

-- Executing query:
SELECT  COUNT(evidensapp_polystructures.brgy_locat ) as High,evidensapp_polystructures.brgy_locat as Barangay, evidensapp_polystructures.municipali as Municipality
FROM  evidensapp_seniangcbr  INNER JOIN  evidensapp_polystructures  ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )  WHERE  evidensapp_seniangcbr.hazard  = 'Low' GROUP BY  evidensapp_polystructures.brgy_locat , evidensapp_polystructures.municipali
Total query runtime: 896688 ms.
23 rows retrieved.

It is not acceptable. So, how do I optimize it? I've done spatial indexing and clustering based on this site.


texts: Oracle Packages, Procedures and Functions Not Respecting Roles

texts:
When you create a package, stored procedure or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 objects, granted through roles, Oracle will complain that the SCHEMA2 object cannot be found. Even if you are calling a package as SCHEMA2 that is set to authid current_user, it complains. Apparently, to get around this problem, you have to explicitly grant permissions to SCHEMA1 (e.g., grant select on schema2.some_object to schema1;).

I have come against this issue several times, now: It's always something I forget about because it seems so counter-intuitive! Could anyone offer an explanation as to why roles aren't respected by PL/SQL objects?


texts: database design question - How to make the table 3NF and have no orphans

texts:
I trying to find out what is the best way to build schema for port-nic associations:

I am storing all information about ports (e.g eth0, eth1, lo0, lo2 of a Linux pc) in a table called ports and NIC information in nics (for storing slot info, etc.)

I am trying to find out if I should store a network-device port information all in one table by a column type_of_port to separate types of ports.

Using type_of_port, the advantages are that: 1. I have only one table, e.i easier queries, 2. I don't have stale orphans if a NIC is removed. Disadvantage: is that I have nic info as NULL values for loopbacks

sqlfiddle: http://sqlfiddle.com/#!9/50977/1:

CREATE TABLE nics (
    nic_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slot_no INTEGER UNSIGNED NOT NULL
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE ports (
    port_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nic_id INTEGER UNSIGNED DEFAULT NULL COMMENT 'references to NIC',
    FOREIGN KEY (nic_id) REFERENCES nics(nic_id) ON DELETE CASCADE,
    serial_no VARCHAR(100) DEFAULT NULL COMMENT 'serialNumber of NIC;',
    port_name VARCHAR(100) UNIQUE NOT NULL COMMENT 'e.g eth0',
    type_of_port VARCHAR(3) NOT NULL COMMENT 'phy, vir'
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

insert into nics(nic_id, slot_no) VALUES(1, 0);
insert into nics(nic_id, slot_no) VALUES(2, 1);
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(1, '0000', 'eth0-0', 'phy');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(2, '1111', 'eth1-0', 'phy');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(NULL, NULL, 'lo0', 'vir');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(NULL, NULL, 'lo1', 'vir');

OR

by its type e.i one table for loopbacks, another table for physical ports.
Disadvantage here is that, ports are scattered over different tables for each type of port. But the advantage is that its in 3NF.

Any suggestions?


texts: Reusing literal string in different parts of query

texts:
I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings:

with cte as ( select 'The character between the xes is evil: xx' as my_bad_string )
select substring( s.my_bad_string, n.i, 1) char_i
  , ascii(substring( s.my_bad_string, n.i, 1)) ascii_char_i
from
  cte as s
  cross join (
    select generate_series(1, length( 'The character between the xes is evil: xx' ))
  ) n(i)

It works. It helped me to find the evil character 141.

Question: what is a more elegant way to write this query so that my hard-coded string appears exactly once instead of twice?


texts: mariadb won't restart after storage space induced crash

texts:
I have a large mariadb instance running (400 gb or so).

Due to running out of storage space it crashed.

I went ahead and resized my volume (I am using AWS), and went to restart the service service mysql start

It did not however start. journalctl -xe returns the error "2017-07-25 14:19:33 140139278653632 [Note] InnoDB: Waiting for rollback of recovered transactionsto exit"

My assumption is that a transaction was executing, mariadb ran out of space and crashed. It is now trying to rollback that pending transaction and has yet to complete.

I am not particularly experienced with database administration, and I do not want to start messing around in a manner which could potentially corrupt my data.

I was wondering if anyone could share any insight on the appropriate way to discern if my assumption is correct, and how I can most appropriately resolve the situation?

Many thanks.


texts: Select record based on multi-row criteria

texts:
So here's the deal. I have a table with the following sample data.

Edit: This table has a many-to-one relationship with the "PEOPLE" table.

Table_A

ID   |CODE   |TYPE   | BAL
-----|-------|-------|------
1    |AR     |FO     |0.00
-----|-------|-------|------
1    |RT     |CM     |335.00
-----|-------|-------|------
2    |AR     |CU     |0.00
-----|-------|-------|------
3    |RT     |CM     |167.00
-----|-------|-------|------
4    |AR     |CU     |500.00
-----|-------|-------|------
4    |RT     |CM     |0.00
-----|-------|-------|------
5    |RT     |CM     |0.00
-----|-------|-------|------
6    |AR     |FO     |200.00
-----|-------|-------|------
7    |AR     |CU     |0.00
-----|-------|-------|------
7    |RT     |CM     |0.00

I need to select all records in which if they only have one row then CODE IN ('AR', 'RT') AND BAL <= 0, but if they have two rows then I only need the ones that both BAL columns are <= 0.

Example:

The query should return:

Edit: Later I will add the DISTINCT to the query, but for now I'm just curious on how to get the records.

ID   |CODE   |TYPE   | BAL
-----|-------|-------|------
2    |AR     |CU     |0.00
-----|-------|-------|------
5    |RT     |CM     |0.00
-----|-------|-------|------
7    |AR     |CU     |0.00
-----|-------|-------|------
7    |RT     |CM     |0.00

So pretty much I need to evaluate whether or not the BAL <= 0 and if they have two rows than both rows should have BAL <= 0.

I've tried different WHERE clauses with no luck. Let me know if you all need more information and I can add it here.


texts: Check constraint for Canadian postal code?

texts:
Is there a way to check if a Canadian postal code is valid? Not sure if something like this would work:

PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9'


texts: Atler Table Alter Column Set Default FALSE: Why my alter table took 10 minutes to finish?

texts:
I have run the following command and it took about 10 minutes to finish:

ALTER TABLE "bookings" ALTER COLUMN "local_direct_booking" SET DEFAULT FALSE

for a table bookings which had 111.834 rows (about 111K rows).

I don't believe this is a big table.

This is the size of the table:

lvd-am::DATABASE=> SELECT pg_size_pretty( pg_total_relation_size('bookings') );
 pg_size_pretty
----------------
 213 MB
(1 row)

Do you have any hint why this might have taken that long?


texts: Sort table by column with column ordered by earliest instance of another column

texts:
Order a table query based on a column but using another column.

I have a table equivalent to the following:

FOO  |  BAR  | TIMESTAMP
-------------------------
CAT  |  BOB  | 2018-03-01
CAT  |  ANN  | 2018-03-06
DOG  |  JON  | 2017-07-02
DOG  |  ABE  | 2012-03-02
CAT  |  HAL  | 2016-04-12
DOG  |  ANN  | 2012-02-06
BRD  |  JON  | 2015-09-12
...  |  ...  | ....-..-..

I want it to return a list ordered by FOO, but I don't want it ordered ascending or descending, but by whichever has the earliest timestamp. So if
the earliest Bird was in 2015, the earliest dog was in 2012, and the earliest cat in 2016, I'd want all the values ordered by DOG then BRD then CAT, and then afterwards sort by BAR.

However, I have no clue how to do this. I obviously don't want to order by timestamp first, or the dogs, cats, and birds will be scattered throughout, and ordering by timestamp after Foo wil leave it ordered BRD, CAT, DOG instead of DOG, BRD, CAT.

So output would need to kind of look like this:

FOO  |  BAR  | TIMESTAMP
-------------------------
DOG  |  ABE  | 2012-03-02
DOG  |  ANN  | 2012-02-06
DOG  |  JON  | 2017-07-02
BRD  |  JON  | 2015-09-12
CAT  |  ANN  | 2018-03-06
CAT  |  BOB  | 2018-03-01
CAT  |  HAL  | 2016-04-12
...  |  ...  | ....-..-..


texts: How to get the Mongo log information for particular range from Mongod.log file

texts:
I have around 50GB Mongo log file in my cluster, for support purpose i need toshare last 10 days or range(from : this date to:that date) information to support team .

Can you tell me how to get the particular log information from log file.

Thanks


texts: How to find out what operator class and access method should be used for an exclusion constraint?

texts:
Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table.

From reading the docs carefully, I could get as far as this:

create table subnets (
subnet cidr,
exclude (subnet with &&)
);

But this doesn't work.  It produces the inscrutable message:

ERROR:  operator &&(inet,inet) is not a member of operator family "network_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

Despite reading up on the doc sections about operator classes and indexes and index types, I retained the feeling that I was missing an entire explanatory section that is taken for granted.  I still don't know what IS an operator class, really, nor an operator family.

I did find posted on a mailing list a code snippet that led to the following working code:

create table subnets (
subnet cidr,
exclude using gist (subnet inet_ops with &&)
);

But I can't truly understand what the "using gist" is for, nor the "inet_ops."

I know that "using gist" relates to types of indexes.  I know an index is automatically created for a "unique" constraint, and I guess that an index may also be automatically created for an "exclusion" constraint.  The only documentation about "operater classes" all relates to indexes, not constraints.

For this or future queries where I want an exclusion constraint, how can I determine what operator class and access method should be specified for the constraint to work?

Note that even with the working code in hand I'm unable to find why it's "gist" and not something else, and why it's "inet_ops" and not "network_ops" or nothing.  \doS and the queries listed in the operator class documentation were unenlightening.

Another error I produced was also unenlightening:

vagrant=# create table subnets (
subnet cidr,
exclude using gist (subnet with &&)
);
ERROR:  data type cidr has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

(This question is based on the premise that I should not need to resort to copying and pasting incantations; a thorough review of the documentation plus carefully reading error messages should let me resolve problems.  With Postgres, this has always been true before now.)


texts:
I have a field called FREQUENCY and it has values like '10,0;30'. How do I write a regular expression to get the result like this:
FREQUENCY

10
0
30


texts: What kind of time should I be hoping to achieve inserting 300k records into a SQLite DB?

texts:
I've written an app in UWP for Windows Phone 10.

When the app starts, I need to insert 307,750 records into a table. It's currently taking approximately 120 seconds to complete - I've wrapped it in a transaction.

I've been trying to improve performance changing various things but nothing seems to get it any faster than it currently is. Some of the blogs I've read about performance suggest using transactions but I'm already doing this.

does anyone know if this insert time is about the best I'm going to get? Example of the SQL below:

BEGIN;
INSERT INTO Point (X, Y, PolygonId)
VALUES (1,1,1) ,(1,1,2), (1,1,3), ...300,000 more...
COMMIT;


texts:
I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this:

select
    stores.stor_name
    , sales.stor_id
from
    sales
join
    stores
on  sales.stor_id =stores.stor_id
group by
    sales.stor_id
having
    (count(sales.stor_id)>=5)

When answering please mention what i have done wrong


texts: Installing default MySQL Tables in Windows (mysql_install_db)

texts:
In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows.

How can the default grant tables be installed on Windows?

(No, I’m not looking for the response that Google’s results are packed full of about how they are automatically installed on Windows, because that is only the case with the installer distribution, not the ZIP package. Besides, that does not help after MySQL is installed and the data directory is damaged or is being replaced or such.)


texts: Are triggers bad for created/timestamp columns?

texts:
We are in need of some better auditing on some of our MySQL tables.  Specifically, we'd like to store when the row was inserted and when it was last updated.

Originally, I thought DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP would be perfect, but unfortunately our current MySQL is below version 5.65, so it only supports doing this on one column and thus doesn't meet our needs.

Thus, my next thought was that triggers would be a good solution.  However, when I ran this by our DBA, he said that triggers are normally a bad idea and that I should just update the values in the code every place there is an update or insert.  This works, but I don't like the fact that it leaves an extra thing for everyone who creates/updates records to worry about, and if they forget then the values will be incorrect.  Thus, to me this seems like exactly the case where triggers should be used.

Is this a bad use of a trigger and why?


texts: Is Postgres ignoring my function cost annotation?

texts:
Why does this:

create or replace function dummy() returns double precision as $$
SELECT random() $$
LANGUAGE SQL
COST 777;
explain select dummy();

return this:

Result  (cost=0.00..0.01 rows=1 width=0)

and not a cost of 777?


texts: Why have both a postmaster and postgres executable?

texts:
It's a little confusing how you have several executables to start postgresql, e.g. postmaster, postgres, pg_ctl.

postmaster -D /usr/local/pgsql/data
postgres -D /usr/local/pgsql/data
pg_ctl -D /usr/local/pgsql/data

I know that pg_ctl is designed to simplify the process of start postgres, but postmaster and postgres appear to be the same exact binary. It appears that postmaster is a symlink to postgres. What benefit comes from doing this?


texts: Optimizing query performance with aggregation tables and partitioning

texts:
Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem:

We have a large table (items) with about 500 million rows (grows with ~1.5million rows per day). Where each item has a timestamp (among other things).
We have 5 metadata tables (based on the item type) with FKs to items. These have about 100 million rows each.

When doing queries on the items table, we need to filter and sort on values in the metadata tables. This is based on filter parameters in our UI, so we might filter on things from all 5 metadata tables, or from none of them. The query will look something like this:

SELECT
    i.id
FROM items i
LEFT JOIN itemmetadata1 im1 ON im1.itemid = i.id AND i.type = 1
LEFT JOIN itemmetadata2 im1 ON im2.itemid = i.id AND i.type = 2
LEFT JOIN itemmetadata3 im1 ON im3.itemid = i.id AND i.type = 3
LEFT JOIN itemmetadata4 im1 ON im4.itemid = i.id AND i.type = 4
LEFT JOIN itemmetadata5 im1 ON im5.itemid = i.id AND i.type = 5
WHERE
    i.timestamp > '2019-02-01'
    AND i.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND im1.somevalue = TRUE
    AND im3.anothervalue > 5
    ... etc
ORDER BY
    -- This can also change dynamically
    im5.value DESC

This makes this query quite slow for some cases. For example when looking at a long period of time, with few matching rows in the metadata tables.

So I have two questions:

1. Aggregation

Would it make sense to create a new denormalized table called aggregateditems that contains all of the item and metadata columns we need to filter and sort on? That way our query would be simplified like this:

SELECT
    i.id
FROM aggregateditems ai
WHERE
    ai.timestamp > '2019-02-01'
    AND ai.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND ai.metadata1somevalue = TRUE
    AND ai.metadata3anothervalue > 5
    ... etc
ORDER BY
    -- This can also change dynamically
    ai.metadata5value DESC

I guess this would speed things up considerably with the right indexes.

So my thought is to put triggers on items and the metadata tables, that will update aggregateditems when the data changes, is that a good idea? Or might this kill performance?

2. Partitioning

Creating an aggregated table will speed up queries I think, but we'd still have very large tables, with large indexes, so I guess we'd need to do some partitioning as well.

Let's say we partition items and aggregateditems on timestamp, how do we partion the metadata tables? (They don't have any timestamps).


texts: SQL Server restore differential after restoring full backup

texts:
Here is the situation: I had a full backup, and a differential backup. I have restored the full backup into the database. But accidentally lost that backup file.

Now I would like to apply the differential backup. Normally you must restore from both the full backup, and the differential, without opening the DB for use. However, we have opened the DB, but not made any updates.

We have made a NEW full backup, which should be identical the the original full backup, except for at least the timestamp. That is, SQL Server detects that the differential is not taken from exactly that DB with that full backup.

Is there any way to trick SQL Server into allowing a differential to work with with new full backup? (I realize it is risky, but the data is important and willing to risk some errors.)

Is there any other way to get the updates out of the differential backup so they could be applied manually? Is there any "decoder" for a differential backup file? Any special recovery tools for this kind of situation?


texts:
I would like to get the most efficient query to give me all the rows that are distinct on one field (ID) but are identical on another field (validity).

Let's set a concrete example: you have a table of meds, each having an ID, some values and a validity date. Of course, in the ideal world each single ID is an individual meds; unfortunately, in reality this is not the case as you might have two variants of the same medicine, with the same ID, some different values in the middle of the table and a validity date again the same. In this real-world example, you want to retrieve all the lines in which the medicine is "still valid" (for whatever definition of still, out of the scope of my question).

Consider the following table:

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # bar   # 2018/01/03
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # cip   # 2018/01/08
# 4  # mao   # 2018/01/10

I'd like the query to return the following lines:

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # mao   # 2018/01/10

notice that (2, bar) and (4, cip) are both "outdated" by (2, flo; 2, duv; 4, cip) respectively.

You can imagine that this is a rather trivial exercise that you can solve using an INNER JOIN:

SELECT * FROM TABLE T
INNER JOIN (SELECT ID, MAX(VALIDITY) FROM T GROUP BY ID) AS TT
ON T.ID = TT.ID AND T.VALIDITY = TT.VALIDITY

However, I noticed that this query becomes quite expensive (essentially there are two full scans, plus an additional group by.
I am not sure whether there is a less expensive way to do this (I tried with DISTINCT ON but that will kill the two entries with ID = 2).
Possibly, recommend also indexes that will work on improve this use case.

Thank you so much!


texts: What is '/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization' telling me?

texts:
I am trying to use dm_os_ring_buffers in order to get an approximation of CPU usage. I've found a few of queries online which get values from /Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization but I'm not sure what this value means. When I run it against one of my servers I'm getting 1, can someone explain this value?

Here's a sample from the query I'm using for context:

WITH SQLProcessCPU
AS(
   SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
   FROM (
         SELECT
           record.value('(./Record/@id)[1]', 'int') AS record_id,
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
           [timestamp]
         FROM (
              SELECT [timestamp], CONVERT(xml, record) AS [record]
              FROM sys.dm_os_ring_buffers
              WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
              AND record LIKE '%<SystemHealth>%'
              ) AS x
        ) AS y
)

Thanks!


texts: Where to find the installer for SQL Server Native Client 11.0.5058?

texts:
I am trying to track down some behaviour issues in our software which seem to dependent on the version of SQL Server Native Client in use. I have version 2011.110.2100.60 (aka 11.00.2100) but our client has 2011.110.5058.00 (aka 11.00.5058). I've been around the house on Microsoft websites, including MSDN, but I can't find a version more recent than the one I have.

Is there a reliable repository of the different versions of the installer (sqlncli.msi) for testing purposes, or a guide to which versions are bundled with other products?


texts:
Background

I work for a decent sized company in the hardware sector.  Currently, we have a number of varying assets that need to be tracked.  Until recently, this was done by hand as was an (obviously) error-prone practice.  We are now (finally) putting this into a proper RDBMS, though the exact DBMS has not yet been selected.

Thankfully, each asset is already tracked by a unique asset tag / number.

Question

I am trying to figure out which is the best way to track check-outs and check-ins.  I have done some googling on the matter, but I cannot seem to find what I am looking for.  I simply need to track when a given asset was checked out and subsequently checked back in.  Currently, I am considering three primary options:

One table each for check-ins and check-outs.  This has the benefit of cleanly separating the two concepts and avoiding any NULL values.  However, it does make the queries for such information more complicated.
One table for both check-ins and check-outs; check-in and check-out on different rows.  This allows me to reuse the same table, but it will require a lot of self-joining to get anything.  I also don't like the reliance on some kind of constant to differentiate between 'out' and 'in' records.
One table for both check-ins and check-outs;  check-in and check-out on the SAME row.  This is my current favorite as it makes querying trivial and marries the two concepts ('out' and subsequent return) in a very understandable manner.  My only hesitation with this is that it relies on NULL to determine if an asset is still checked out or not.  It has been my experience that NULL can be a harsh mistress, especially when the design is DBMS-independent.

I am aware that a lot of this may depend on my particular requirements; however this problem seems common enough that I am hoping for some level of expected best-practice and WHY that practice was chosen.

Too long; Didn't read

Which RDBMS-agnostic design technique is best for tracking check-outs and check-ins of assets and WHY:

Two tables; One each for check-outs and check-ins
One table for both; Different rows for' out' vs. 'in'
One table for both; 'out' and 'in' on same record

Thanks in advance for any help.


texts: Tables missing from MySQL database after removing then restoring the mysql data direcory

texts:
I Carried out the following operations.

make mysqldump of testdb

# Stop service
/etc/init.d/mysql stop
# Create copy of directory
cd /var/lib
cp -R mysql mysql.backup
# Delete datafile on disk
rm -rf mysql/testdb
# Start service
/etc/init.d/mysql start

At this stage I was going to import testdb again from the mysqldump however we wanted to roll back for an unrelated reason so I restored the mysql file

/etc/init.d/mysql stop
rm -rf mysql/
cp -R mysql.backup mysql
# Restore ownership and permissions
chown -R mysql:mysql mysql
chmod -R 700 mysql

At this point testdb shows up if I run show databases; and it shows tables but doesn't appear to contain data any more.

Is this recoverable or am I wasting my time? I have the option of refreshing the db from prod


texts: is it possible to reset innodb_row_lock without restart MySQL 5.7

texts:
Now I could using this command to check the innodb_row_lock values:
show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits   4
Innodb_row_lock_time    22471927
Innodb_row_lock_time_avg    52
Innodb_row_lock_time_max    22327
Innodb_row_lock_waits   426696

is it possible to reset the data without restart database? I aready searching from internet but no one talks about this.


texts: PosgreSQL: setting high work_mem does not avoid disk merge

texts:
This is not quite my day with postgres. On my server machine with PosgreSQL 9.2.3 I have set work_mem to 4MB to avoid Sort Method: external merge  Disk: 2072kB but it did not help:

cwu=# vacuum analyze web_city;
VACUUM
cwu=# SHOW work_mem;
 work_mem
----------
 4MB
(1 row)
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=18304.35..20487.34 rows=95562 width=10) (actual time=1557.871..1809.029 rows=64459 loops=1)
   ->  Sort  (cost=18304.35..18633.84 rows=131796 width=10) (actual time=1557.856..1707.069 rows=131796 loops=1)
         Sort Key: ("left"((name)::text, 5))
         Sort Method: external merge  Disk: 2072kB
         ->  Seq Scan on web_city  (cost=0.00..4842.45 rows=131796 width=10) (actual time=1.050..174.907 rows=131796 loops=1)
 Total runtime: 1828.936 ms
(6 rows)

Setting work_mem to 8MB finally helps:

cwu=# SET work_mem = '8MB';
SET
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=5501.43..6675.72 rows=93943 width=10) (actual time=207.628..244.667 rows=64459 loops=1)
   ->  Seq Scan on web_city  (cost=0.00..4842.45 rows=131796 width=10) (actual time=0.749..102.511 rows=131796 loops=1)
 Total runtime: 263.154 ms
(3 rows)

But why 4MB is not enough? In postgres wiki, there is this note:

  if you see a line like "Sort Method: external merge Disk: 7526kB" in
  there, you'd know a work_mem of at least 8MB would really improve how
  fast that query executed, by sorting in RAM instead of swapping to
  disk.

So I assumed it will be the same in my case.

EDIT: If I do:

cwu=# create index name_left_prefix on web_city(left(name, 5));

then 4MB is finally enough. It seems that the index causes lower memory usage. If anyone would be that kind to explain all this behaviour I would be very grateful.


texts:
I am running Oracle 11g on a Linux box.  I am trying to run the following command to set my streams user grants and other authorizations:

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'nwisxfer',
    grant_privileges => false,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => '/usr/oracle');
END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-23481: unexpected name string "/usr/oracle"
ORA-06512: at "SYS.DBMS_STREAMS_AUTH", line 1621
ORA-06512: at line 2

A Google search suggested that I run these scripts:

/usr/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/catrep.sql
/usr/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/utlrpt.sql

This did not eliminate the error.

Any advice on how to proceed?


texts: Oracle: How to create secondary index based on every word in a CLOB

texts:
I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX searches on the CLOB field. The primary key is just used to prevent duplicate insertions. To speed up searching, I'd like to build a secondary index of regular words found in the CLOB field.

I was thinking that maybe another table would act as an index. The primary keys of this new table are every unique word found in the CLOB field, of every record, of the original table. The other field would be a list of pointers to the records where that word exists in the original table.

Is there a more conventional way to do this? The original table will grow to over 10 million records. How would I build a secondary index that significantly speeds up REGEX searches by first searching for any regular words found in the expression, then, after taking that smaller list of returned records, it would search using the real REGEX search?


texts: Best practices for integer key types - mixed or all the same integer type?

texts:
I am designing a postgres database for scientific data. It has about 30-40 tables, many of which are lookup tables with less than 10 records. The bulk of the data are concentrated in just a few tables with ~ 1,000,000 records that reference the lookup tables with foreign keys. There will be web user interface that accesses the data via a GraphQL API.
I always use the same INT data type for all primary and foreign key types. Is there merit in optimizing the primary keys on the lookup tables as SMALLINT to conserve space on the big tables (and perhaps a modest overall performance)? Or is it best to just keep all key columns the same data type to reduce complexity and confusion for humans and software accessing the data?
(The lookup data could change over time so I can't use ENUM.)


texts: Efficient merging (removing duplicates) of arrays

texts:
I have two tables, left2 and right2. Both tables will be large (1-10M rows).

CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);

CREATE TABLE right2( t1 INTEGER, d INTEGER, arr INTEGER[] );
ALTER TABLE right2 ADD PRIMARY KEY(t1,d);

I will perform this type of query:

SELECT l.d + r.d,
       UNIQ(SORT((array_agg_mult(r.arr)))
FROM left2 l,
     right2 r
WHERE l.t1 = r.t1
GROUP BY l.d + r.d
ORDER BY l.d + r.d;

Where for aggregation of arrays I use the function:

CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}');

After concatenating the arrays, I use the UNIQ function of the intarray module. Is there a more efficient way of doing this? Is there any index on the arr field to speed up the merging (with removing duplicates)? Can the aggregate function remove duplicates directly? Original arrays may be considered sorted (and they are unique) if that helps.

The SQL Fiddle is here:


texts:
In your opinion what is the best configuration for optimal SQL Server performance in regards to virtual guest Socket/CPU configuration? I've read numerous articles stating wide socket configuration (1 cpu per socket) provides for optimal performance. In other articles optimal performance for SQL Server is achieved with a 1 socket and 'x' number of cpus in that one socket. Matching the guest VM Socket/CPU to the physical host configuration.
SQL Server Standard is limited with 4 sockets, but what configuration have you found to perform best? We do plan to perform load testing in the near future. I look forward to your feedback. Thanks in advance!!!


texts: Remote update/restore of database through SSMS

texts:
How can I update or restore a database through SQL Server Management Studio by connecting "remotely" to it?

If I know the "IP address" of machine, what else do I need to do? If a port number is required, how can I find it?


texts: SQL Server 2008 server cannot connect to itself with alias name or "localhost"

texts:
We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit.  This is also a windows/sql clustered environment.  There is the cluster alias for the server, and then we have 2 AD DNS aliases as well for the server

Suddenly I can't connect to the sql instance in Management Studio, or with a Powershell Invoke-SQLcmd IF I am doing it from that actual server, when using one of the dns alias names, or "localhost".  If I use the Cluster alias name it works and if I use the other dns alias name it works.

Using management studio with the non-working dns alias I get "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If I use localhost, I get "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"

Shared Memory and TCPIP are enabled in SQL Server Config manager but named pipes is not.  But we've NEVER had named pipes enabled, and don't have it enabled on any of our other sql servers and they're set up the same (clustering, dns aliases...etc) and are having no issues.

I've Googled around and had a view people suggest listing what other SQL servers it can see (cmd prompt sqlcmd -L)  If it do it from a good server I see all other servers on my network including the "bad" one.  If I do that command from the "bad" server, I get no results at all.

Pretty much checked everything from this blog and it's all ok except the sqlcmd -L issue I just mentioned above.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Windows firewall is not enabled.

This was working just fine for a couple of years.  I'm not sure when this started happening, but I know that at some point, it was not happening.

And the other kicker...it's only happening locally to this server.  If I connect with management studio or run a powershell Invoke-Sqlcmd from another machine, or my local machine connecting to the "bad" server, it works fine no matter what alias I use.

I'm stumped.  I think it sounds like an issue with the AD alias (there are 2 ad aliases, 1 works locally the other doesn't)...but not being able to connect with localhost makes me question that.

any help is greatly appreciated.


texts: Incremental Database/Data Change Restore

texts:
I have vendor production backup that needs to be restored onsite daily. The most frustrating part is that their installation is on SQL 2008 but the DB mode is SQL 2000. sigh!!!

The database in our site will primarily be readonly. The vendor runs any required updates/data changes to the DB on their site during the day and will send the backups to us on a daily basis. It is a smaller bak file now but will grow over time. The goal is to restore incremental updates only.

Possible workaround that I could think of are the following:

Keep the DB in our side in read only mode (after full backup is restored) and request the vendor to give us the diff backups every day. Of course if they take a full backup , we are back to square one.
Partition the database and request the vendor to give the partition group backups. This may be difficult because not all tables may be qualified for partition.
Replication could be an option but the vendor is not ready for this.
Bulk insert for individual objects could be an option but again the vendor is not ready for it.
CDC feature could possibly help here but again not in SQL 2000.

If you can share any other ideas that you could think of, that would be very helpful. (I agree the first step should be to asking them to upgrade)

Thanks for all your help.


texts: Terribly slow Disk Read (I/O) When fixing corrupt MyISAM table (9GB)

texts:
So I've launched the myisamchk utility with repair option on all tables within the database.

Smaller tables (1GB max) got fixed in no time, virtually seconds. However, my largest table (9GB) takes forever (20mb/min) to fix. My server is down because of that.

I've checked 'iotop -ao' and this is what i got:

Whats going on here? The read of 300 KB/S is VERY HIGH in this screenshot, i was aiming at capturing the super-fast write. Sometimes the read spikes to 5-10 MB/s, but for the rest of the time its within 0-50KB/s range.

The table has only several columns, 2 indexes (1) Long, (2) Varchar(72). Mysql 5.5.56, CentOS 6. Its doing repair by sort.


texts:
I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ids. My Query is below.

Select
  backups.id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  backups.id

This outputs the following:

The latest backup for SDSBackup_OFFICE should have an ID of 118. I have read about using either subqueries or different types of joins but I wasn't able to get them to work. Any ideas?

I tried the following query as one of the answers below suggested without success. The max backup.id was indeed selected, but it was incorrectly matched up with other values in the database.

Select
  Max(backups.id) As Max_id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  Max_id

I did some research and found that the groupwise max function only applies to the given column that it is applied to. It is left to the server to determine how the other data should be assigned. I don't know if I fully understand this. I am getting my information from a question/answer on stackoverflow. https://stackoverflow.com/questions/7594465/group-wise-maximum-of-a-certain-column.

All the examples on that page are fairly simple queries, I don't know how to apply these examples to my query. Help would be much appreciated.


texts: Health checking via Database Mail in SQL Server

texts:
I have a few SQL scripts that I would like to run every day on my SQL Server(s). How can I query multiple times and have the output similar to the following:

Health check for <server>

Query Title 1:

<query results 1>

Query Title 2:

<query results 2>

I am checking things like last good back up, uptime etc so they are not related to each other. I am planning to use sp_send_db_mail inside of a SQL Server Job. Is this the best way of accomplishing this task?

Ideally, I would only like one email.

Update

After battling for ages!!! The solution I have found so far is to put it into a stored procedure and then execute the stored procedure like so:

EXEC msdb.dbo.sp_send_dbmail
        @recipients = N'<email>',
        @body = @body,
        @subject = @subjectt,
        @profile_name = @ProfileName,
        @query ='exec test..sp_checks'

Is this ok for multiple select statements?


texts: HOW to impliment SQL 2012 send_dbmail with OAuth2

texts: Situation: we currently send emails from our sql 2012/asp app using the msdb.dbo.sp_send_dbmail process and implement multiple different domain accounts. It works (worked) well for us to send authorised emails from our application on behalf of our customers. Now that OAuth2 is being properly maintained, we now need to be able to connect to email accounts that are implementing OAuth2 for Authentication (or any of the other Authentication protocols eg encrypted password). Is this possible? Is the answer a different email agent? Who as solved this and how?


texts:
I have a table called oth_mbr. I need to get 3 years of data month wise. current date minus 36 months and each month data betwwe from_dt and thru_dt needs to be fetched.
data shoulbe be in below formate
DATE , COLUMN A, COLUMN B
2017-08-01,FEP000,FPr
2017-09-01,FEP000,FPr
2017-10-01,FEP000,FPr
.
.
.
2020-08-01,FEP000,FPr

tried below query but this gives only one data
SEL distinct ADD_MONTHS(DATE - DATE MOD 100 + 1,- 36)  AS   DATE ,
COLUMN 1,COLUMN 2
OTH_MBR
WHERE
ADDD_MONTHS(DATE - DATE MOD 100 + 1,- 36)  BETWEEN FROM_DT AND THRU_DT

Result for above query is
2017-08-01,FEP000,FPr


texts:
I'm having a problem with a 2-node active-active cluster which has been failing over lately but not bringing resources back online.

We've had a Microsoft engineer look at it and, among other things, he noted that it was unusual to have a Quorum disk group explicitly created under the Services and Applications node and thought that this might cause a problem.

I'm a bit puzzled as to whether this is a factor or not. Certainly most of the documentation on quorum disk configurations don't create a specific cluster group to contain the Quorum disk - just going through the Quorum Disk Configuration Wizard seems to be all that is required.

I had always thought of a Quorum disk as being a 'fixed point' - i.e. it doesn't belong to a cluster group and doesn't fail over when a node fails over as to do so might mean the cluster no longer being in quorum (quorate?). However other documents I've read do suggest that the Quorum disk does 'belong' to a node. However if you have an active-active configuration which one gets to own it?


texts:
Suppose,I've a table called UNIVERSITY containing universities name:

universityID    universityNAME  isACTIVE
     7            GNDU             1
     6            PU               1
     5            PTU              1
     8            LPU              1

Now these universities ID's has been(obviously) used in many tables within the database(name e.g.Education),Suppose 10 tables.

Q.Now what happen if i delete one university?

A.The universityID field in other tables becomes NULL.

But I don't want these,rather when I delete 1 university from UNIVERSITY TABLE,all its occurrences with Rows in all 10 table should get deleted.

What will be the shortest and easiest MySQL Query for this operation.

NOTE:I'm using PHP language.


texts: Should i place Registered-Users Info in every table? to Restrict user access to his Own Data only

texts:
Database: Relational Database - MySQL

I am making a database with multiple tables. In order to see his data from tables, User will need to log in. I only need to display those information(through web-pages) from the Table which only belongs to him,  so he wont get to access data of other users.

So, should i place Registered-Users Information in every table?  just so when he wants to see data, i will "SELECT data from TABLE_ABC where (login_id = Id_of_current_user_log_ined )". I will put this condition in every query so he will only be able to access his/her own data and not someone else.

I made a Relational Schema but only 1 table inherits Login-Information from Login-Table while 3 of the tables don't have information on WHO does the data belong to?


texts: Remap array columns to single column with value

texts:
Here is the sample database schema.
| id |  Critical     |  High  |    Low     |
--------------------------------------------
| 1  | {apple, ball} |  {cat} | {dog, egg} |

Now I want to remap these to another table. Critical set to 1, High to 2 and Low to 3 like:
| element | priority |
----------------------
|  apple  |   1      |
|  ball   |   1      |
|  cat    |   2      |
|  dog    |   3      |
|  egg    |   3      |

1,2,3 can be changed to column name too.
I've written a query to do it for 1 column at a time:
insert into new_table (element, priority)
select unnest(critical), 1 from old_table where id=1;

But I want to do all the 3 columns in this single query.
I want it fast performing. Is UNION the most efficient way?


texts: Find records with the same relationships

texts:
Say I have Orders and Products.
For a given Order I want to find all the other orders that are made up of the exact same products.
So the schema would look something like this:

CREATE TABLE Order (
    id int
    ...
)
CREATE TABLE Product (
    id int,
    name text
)
CREATE TABLE OrderProduct (
    OrderId int,
    ProductId int
)

Consider the following data.

Order| Product
1|Burger
1|Drink
1|Fries
2|Burger
2|Drink
3|Burger
4|Burger
5|Burger
5|Drink
5|Fries

The desired query would return a list of every order that has the exact same products:

Where order ID -> Returned Result rows
1-> 1
    5
2-> 2
3-> 3
    4
4-> 3
    4
5-> 1
    5

Everything I have come up with so far requires ugly queries, multiple steps and knowing how many products will belong to an order up front.
Is there a simple way to achieve this?


texts: Find jobs that succeeded, but had steps that failed

texts:
Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows?  I'm new to this side of development and I'm not sure about all of the system tables, etc.  If it is possible, what tables would you use to construct the query?

I'm not sure where to post 'is this possible' type questions.  If this is the wrong place, just let me know and I can take it down.  Thanks for your help.

Desired Output


texts:
I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012:
SELECT Id
FROM dbo.Person
WHERE CONTAINS(Name, '"John" AND "Smith"')
ORDER BY Name

Executing this query alone gives me about 1,300 results in less than two seconds (there's a fulltext index on Name)
However, when I change the query to this:
SELECT Id
FROM dbo.Person
WHERE CONTAINS(Name, '"John" AND "Smith"')
ORDER BY Name
OFFSET 0 rows
FETCH NEXT 10 ROWS ONLY

It takes more than 20 seconds to give me 10 results.
The following query is even worse:
SELECT Id
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNum, Id
    FROM dbo.Person
    WHERE CONTAINS(Name, '"John" AND "Smith"') ) AS RowConstrainedResult
WHERE RowNum >= 0 AND RowNum < 11
ORDER BY RowNum

It takes more than 1.5 minutes to complete!
Any ideas?
Slow plan

Fast plan


texts: Add Primary Key column at the end of an Index

texts:
If I have a table with the following primary key:

PRIMARY KEY (id)

And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an index:

INDEX from_id (from, id)

Is the "id" at the end of this index redundant because it ends with the primary key?
If yes, why?