texts: MySQL 5.7 using /tmp directory, and filling it up

texts:
We have recently upgraded from MySQL 5.5 to 5.7.26.

When running the following query:

INSERT INTO table1_archive
SELECT *
FROM table1
WHERE dc between "2019-06-01 00:10:00" and "2019-06-31 23:59:59";

It is now filling up the / directory, and then failing. This was not the case in MySQL 5.5, where the same query has been running for years.

If I run explain on the query there is no mention of using temporary, so I can't see why it is suddenly using up all the / space.

Looking at the docs the only thing I can see is that

  When selecting from and inserting into the same table, MySQL creates
  an internal temporary table to hold the rows from the SELECT

but I am using two different tables, so this shouldn't be the case.

Is there something else in MySQL 5.7 causing INSERT . . SELECT. . queries to use /, and is there something I can do (other than create a huge / directory just for these occasional queries)

the tmpdir is currently set to /tmp which is 9GB (compared to the data directory at 1.5 TB, and mounted on /var/lib/mysql). And the buffer pool is 100GB.

The table is:

CREATE TABLEtable1_archive(
MIdint(11) NOT NULL AUTO_INCREMENT,
FIdint(11) DEFAULT NULL,
JIdint(11) DEFAULT NULL,
Headvarchar(80) DEFAULT NULL,
MStattinyint(3) unsigned DEFAULT NULL,
Latesttinyint(3) unsigned NOT NULL DEFAULT '0',
Deletedtinyint(2) unsigned DEFAULT NULL,
CByint(11) DEFAULT NULL,
Recint(11) unsigned DEFAULT NULL,
Rec2varchar(65) DEFAULT NULL,
dcdatetime DEFAULT NULL,
procdatetime DEFAULT NULL,
SReqdatetime DEFAULT NULL,
Deldatetime DEFAULT NULL,
RAtdatetime DEFAULT NULL,
WRAtdatetime DEFAULT NULL,
WDelAtdatetime DEFAULT NULL,
Deldatetime DEFAULT NULL,
Textblob,
Numint(10) unsigned DEFAULT NULL,
TB1longblob,
TB2longblob,
TB3longblob,
  . . .
TB128longblob,
Errorint(11) DEFAULT NULL,
FNamevarchar(32) DEFAULT NULL,
BFlongblob,
NAtttinyint(3) unsigned DEFAULT NULL,
EStattinyint(2) unsigned DEFAULT NULL,
Erronvarchar(255) DEFAULT NULL,
EErrorvarchar(255) DEFAULT NULL,
Holdtinyint(2) unsigned DEFAULT NULL,
  PRIMARY KEY (MId),
  KEYFId(FId),
  KEYMStat(MStat),
  KEYCBy(CBy),
  KEYRec(Rec),
  KEYdc(dc),
  KEYDeleted(Deleted),
  KEYError(Error),
  KEYJId(JId),
  KEYEStat(EStat),
  KEYDel(Del),
  KEYHold(Hold)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

n.b. I left out a load of TBn fields in the middle, as they are all identical.

The explain is:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "11005387.01"
    },
    "table": {
      "insert": true,
      "table_name": "table1_archive",
      "access_type": "ALL"
    },
    "insert_from": {
      "table": {
        "table_name": "table1",
        "access_type": "range",
        "possible_keys": [
          "Created"
        ],
        "key": "Created",
        "used_key_parts": [
          "Created"
        ],
        "key_length": "6",
        "rows_examined_per_scan": 7860990,
        "rows_produced_per_join": 7860990,
        "filtered": "100.00",
        "index_condition": "(db1.table1.dcbetween '2019-06-01 00:10:00' and '2019-06-31 23:59:59')",
        "cost_info": {
          "read_cost": "9433189.01",
          "eval_cost": "1572198.00",
          "prefix_cost": "11005387.01",
          "data_read_per_join": "17G"
        },
        "used_columns": [
          "MId",
          "FId",
          "JId",
          "Head",
          "MStat",
          "Latest",
          "Deleted",
          "CBy",
          "Rec",
          "Rec2",
          "dc",
          "Proc",
          "SReq",
          "Del",
          "RAt",
          "WRAt",
          "WDelAt",
          "Del",
          "Text",
          "Num",
          "TB1",
          "TB2",
          "TB3",
          . . .
          "TB128",
          "Error",
          "FName",
          "BF",
          "NAtt",
          "EStat",
          "Erron",
          "EError",
          "Hold"
        ]
      }
    }
  }
}


texts:
When I use terminal, enter my database and then quit by using \q I can not get to the following line in order for example to enter my other database (see below). The cursor is stuck without having the option to get to the next line.

postgres-#
postgres-# \q
logout
Saving session...
...copying shared history...
...saving history...truncating history files...
...completed.
[Process completed]

And then I can not go to next line. How do I overcome that problem?


texts:
I'm storing linked words in a Cassandra v2 cluster and I'm having a hard time doing efficient reads. I'm trying to fetch the top X rows with the highest counter value.

Cassandra doesn't store the rows in any ordered way, so simply adding LIMIT X to the CQL query returns random-ish rows.
The ORDER BY command in CQL only works for columns that are (part of) the key.
OrderPreservingPartitioner doesn't exist anymore in Cassandra v2.

At the moment, when doing a read, I have to fetch all rows and manually select those X rows with the highest counter value. This literally slows some queries down by 100x, because even mildly popular words often have hundreds of thousands of words linked to them, not to mention the waste of bandwidth and other resources.

Are there any clever techniques to make this more efficient?


texts: mongodb is using most of disk space for journal and not cleaning itself

texts:
I am using mongodb on production with journal enabled. I am using mongodb to store application data and also using gridfs to store images. I am using Elastic Block Storage (EBS) with 10GB for each mongodb instances I have (total 3 instances on replica).

When I checked the disk usage then I got surprised that journal folder is using almost all the space. Following is details.

bitnami@ip-172-31-25-9:~/stack/mongodb/data/db$ pwd
/home/bitnami/stack/mongodb/data/db
bitnami@ip-172-31-25-9:~/stack/mongodb/data/db$ du -h *
65M admin.0
16M admin.ns
65M bhs.0
129M    bhs.1
257M    bhs.2
513M    bhs.3
16M bhs.ns
3.1G    journal
64M local.0
1.1G    local.1
16M local.ns
4.0K    mongod.lock
4.0K    _tmp
bitnami@ip-172-31-25-9:~/stack/mongodb/data/db$ df -h journal
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      9.8G  7.0G  2.4G  76% /

However when I did mongodump on my data then I found that exported data size is only 500MB. I.e. most space is bsing used by journal. I understand journal does keep details on each write operation made which causing large file size. However I am surprised is that its not deleting old transaction.

Ideally I beleive after write transaction is flushed to disk then journal should be deleted. Is this journal folder size expected? Or I am missing some configuration? Should I think to increase the disk volume soon?

Please advice.


texts: mysqldump generates incorrect create view queries

texts:
I use mysqlpump to backup database:

mysqlpump --single-transaction --add-drop-database --skip-definer \
 --databases mydatabase --result-file /opt/myservice/backup/export.sql

My DB contains several views, and some of them are exported with totally incorrect SELECTs in the CREATE VIEW queries like this one:

CREATE VIEW `careerpulse-staging`.`user_view` AS SELECT
 1 AS `id`,
 1 AS `name`,
 1 AS `department`,
 1 AS `manager_id`
;

Why is it happening and how can I fix that?


texts: How to upgrade MYSQL from 5.6.12 to 5.6.21?

texts:
I have been a MSSQL DBA and don't have much knowledge about MYSQL.
Does MYSQL also have patches just like MSSQL?
I need to do MySQL version  upgrade from 5.6.12 to  5.6.21 but I do not have much information at hand.
A step by step guide will be the best but any information will be helpful.


texts: Restore SQL Server 2008 R2 Express from Description

texts:
When backing up SQL Server we have the opportunity to input a description of the backup.  Yet non of the Microsoft tools will show that description for restoring.  Is there a way for me to review the description of the backup to select that one?

Thanks.


texts: Compare value from xml node that contains decimal

texts:
I need to compare the values of two different XML nodes for a record stored on SQL Server 2014.

Here is the contents of my XML column that I will be using:

<Document>
   <PolicyId>0</PolicyId>
   <ChangeAmount>1.4-</ChangeAmount>
   <PolicyTerm>
       <OutstandingBalance>35.04+</OutstandingBalance>
   </PolicyTerm>
   <OriginalCollectionAmount>36.44+</OriginalCollectionAmount>
</Document>

Here is my SQL statement that is not working:

SELECT *
FROM   Table
WHERE  QueueName ='.\private$\CollectionActivityPosted'
AND    Payload.value('(/Document/TransType)[1]', 'nvarchar(20)') = 'C'
AND    (Payload.value('(/Document/OutstandingBalance)[1]', 'nvarchar(20)')
       <>
       (Payload.value('(/Document/OriginalCollectionAmount)[1]', 'nvarchar(20)')))

I am expecting my query to get a hit based on the OutstandingBalance not being equal to the OriginalCollectionAmount but it doesn't.


texts: Cluster Size Configurations for SQL Server at RAID Level

texts:
I was reading the below two articles (one of which I posted) and one recommends to use a RAID cluster stripe size of 256k while the other recommends to use 64k. There is a big difference between the two and was wondering if anyone could shed some light on this.

RAID 10 Cluster Size for SQL Dynamics AX Database

SAN block size for SQL Server on 4KB NTFS


texts: How to get info if any Monitoring tool (DB performance Analyzer) stops working?

texts: What is the Reason behind if SQL monitor for Production stopped working? what is the reason for this? I checked on Licensing, SQL server permissions, No Updates/Patching and Remote admin connections everything is alright. It might be cause of over load or OS issues effect SQL monitor? Any specific reason?


texts: MySQL Large Table Indexes - Recommended length of index

texts:
I am working with a table that has a couple hundred million rows of data. It uses varchar(32) UUID's as it's keys both primary and foreign (there are two). I was wondering with this size of table, how much of each field do I need to index to keep good performance but not index every character of those three fields.

For example if I set an index length of 24 characters, will I see any noticeable difference in performance? I was hoping there might be some kind of algorithm out there that can be used to estimate performance based on length of field and length of index.

Currently the table is ~100GB in size, and the index is taking up over half of that space. I would like to recover some of that space, and reduce the growth rate of the table.


texts: Joining Oracle tables with 2 strings to be oa a single row (in a distinct SQL filter)

texts:
I have an Oracle Database and if I fire that SQL:

SELECT distinct RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    A3T2.COD_REC, A3T2.COD_VAL
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'

it gives me:

ID_PL   AN    DENUMIRE  COD_DESEU   TIP_AMB  COD_REC   COD_VAL
353940  2013  DIA       15 01 03    LEMN     R 3
353940  2013  DIA       15 01 03    LEMN               R 1

but if I make an

SELECT distinct RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    (select coalesce(A32.COD_REC,'') || coalesce(A32.COD_VAL,'')
     FROM a3_t2_rows a32
     WHERE A32.TIP_AMB=A3T2.TIP_AMB AND
        A32.FK_ID_ANEXA = CHT.ID AND
        A32.COD_DESEU = A3T2.COD_DESEU AND
        A3T2.TIP_AMB = 'LEMN'
     ) as cod_op_lemn
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'

it gives me an

[Error] ORA-01427: single-row subquery returns more than one row

The question is - how can I join R3 from COD_REC and R1 from COD_VAL so it can be R3 R1 in both rows being a single row with distinct. Thanks!


texts: Getting the page name from a web URL in Oracle

texts:
I have a list of page URL's in my database table under the column heading URL. There are many different prefixes on each page name for example:

Test1/Admin/Page7.asp
Test1/One/Page3.asp
Test1/Another/Page2.asp
Testing1/Admin2/Page12.asp

Currently I have created some SQL to get a distinct list of the above URL's. However what i need is just the page names. I need to get rid of any text before the last "/". How would I be able to do this within Oracle?

My SQL currently looks something like this:

SELECT DISTINCT URL FROM TABLE1 WHERE URL IS NOT NULL ORDER BY TABLE1.URL


texts: Failure notification with multiple profiles

texts:
In my SQL Server Agent having several jobs which are running with different Proxies... and in Database mail having more than one profile [each profile having individual accounts]... now my question is , how can I send a mail using a specific profile from Database mail for failure notification...

Thanks
Kiran Kumar


texts: pulling multiple entries from a match of two tables

texts:
I have two tables, attributes and attributes_entries

attributes hold the title and attribute_entries hold the value of that title.

if I do:

SELECT attribute_entries.title AS ent_title,
       attributes.title AS attr_title
FROM   attribute_entries
INNER JOIN attributes
       ON attribute_entries.FK_attribute_id = attributes.id
WHERE attribute_entries.title = 'ford'
      AND attributes.title = 'manufacturer'

I can get a result of:

ent_title | attr_title
ford      | manufacturer

if I do another query of:

SELECT attribute_entries.title AS ent_title,
       attributes.title AS attr_title
FROM   attribute_entries
INNER JOIN attributes
      ON attribute_entries.FK_attribute_id = attributes.id
WHERE attribute_entries.title = 'whatev'
      AND attributes.title = 'nickname'

I get the result of:

ent_title | attr_title
whatev    | nickname
whatev    | nickname

both of these queries allign with the data I have in there.

so I figured this would work:

SELECT attribute_entries.title AS ent_title,
       attributes.title AS attr_title
FROM attribute_entries
INNER JOIN attributes
      ON attribute_entries.FK_attribute_id = attributes.id
WHERE attribute_entries.title = 'ford'
      AND attributes.title = 'manufacturer'
      AND attribute_entries.title = 'whatev'
      AND attributes.title = 'nickname'

This clearly isn't working.  What is my best approach to get the results in one statement?


texts: Problems with Listener in AlwaysOn AG in AWS

texts:
I have a 2 node AlwaysOn AG cluster with a single database being replicated between the 2 nodes. Windows Failover Clustering Services are installed on each node.

The AG is configured to allow connections for either node when it is primary but not to allow connections if the node is the secondary.

When I connect using the listener name, I randomly connect to the secondary node (which I would not expected to happen).

I am reviewing the IP addresses and subnets with my Infrastructure team to confirm that I have configured the AG correctly.

Is this a known problem with SQL Server 2016 SP2 CU6?


texts: MySQL `innodb_log_file_size`: how big is too big?

texts:
In many answers, discussions, and comments, the prevailing wisdom seems to be to "set innodb_log_file_size to be 25% of the inndb_buffer_pool_size.

What about fairly large buffer pools? For example, I have a server with 64GiB of memory and my buffer pool is currently 38GiB. 25% of that is 9.5GiB. What are the risks of using a "large" innodb_log_file_size versus leaving it "too small"?

With 64GiB available, I'm likely to increase my innodb_buffer_pool_size to something greater than 40GiB, possibly higher. Something makes me nervous about a 10GiB+ log file size. Am I needlessly worrying?


texts: Does listener gets affected when we replace secondary replica with another server in Availability Group?

texts:
I have created Multi Subnet Always On Availability Group.
Now, I need to replace secondary replica with another server in Availability Group. In this case, Do I need to re-create listener? OR The previous listener works fine?
Thank You


texts: Possible to create databases in MongoDB but not have access to databases created by others?

texts:
I need two user accounts - both able to create and administer databases - but neither account able to access or administer databases created by the other account.

Is this possible?


texts: Compatibility level for published database and transactional replication

texts:
I am running a database on compatibility level 80 because the application using it does not yet support a higher level.

It is my understanding that transactional replication on SQL Server 2005 and up has some new features like schema changes replication and improved schema locking.

Can I benefit from these new features if my database is on a lower compatibility level?

In other words, do these features depend on the compatibility level of the published database or does this rely only on the version of the server?  I know merge replication has a publication compatibility level (independent from database compatibility level) but I can't find a clear answer about transactional replication.


texts: What is the best to add unique index on nvarchar(max) field on existing table?

texts:
The situation I find myself in is that I am getting bad data into the database for some reason. I want to preserve data integrity by adding to the User table a UNIQUE INDEX on the UFID field.

Naturally I generated a script to remove any duplicate records, in a non destructive way:

update [user]
set UFID = UFID + '_dup_removal'
where ufid in (
  select ufid
  from [user]
  group by ufid
  having count(ufid) > 1
)

then I found that the field is unable to be used in a UNIQUE INDEX.

So now what is the best way to get to a result where I have the user table and data with the addition of the UNIQUE INDEX.

Note: it should be done in a way that allows easy reversal.


texts: Is WITH RECURSIVE the right tool for my task?

texts:
Table setup:

create table users(id bigserial, group_id bigint);
insert into users(group_id) values (1), (1), (2), (1), (3), (3);

Query:

    WITH RECURSIVE r AS (
      SELECT users.id, users.group_id, 0 as i
      FROM users

      UNION

      SELECT users.id, users.group_id
          , (CASE WHEN users.group_id = lag(users.group_id) over (order by users.id)
                  THEN (r.i)
                  ELSE (r.i+1) END) AS i
       FROM users
       INNER JOIN r ON users.id = r.id
       WHERE r.i < 5)
    SELECT * FROM r;

I'm trying to iterate through user, and assign i if previous group_id value is equal to current, i+1 otherwise, starting from 0.

However, this recursive query doesn't work as I expect, see: http://sqlfiddle.com/#!15/ad3b6/39


texts: Does Microsoft Security Bulletin MS11-049 affect SQL Server 2008 SP3?

texts: The security bulletin does not list SQL Server 2008 SP3 under affected software but it came out prior to that service pack. We are running SP3 but this issue is still showing up on a vulnerability scan. Does anyone know for sure if this is a false positive? I only found one forum post where someone said SP3 "should" include this security update.


texts:
I would like to disable auto compaction on my CouchDB instance - so that I can get a full history of all the database updates.

Is this possible? If so, how do I go about it? Is there a reason why I should not do this?


texts:
Environment and Server Details:

12.0.4457
Windows Server 2012 R2 standard
VMWare Virtual Machine
Dell Equal Logic SAN

SQL Error Log Message:

  The log shipping secondary database ServerName.DatabaseName has restore threshold of 45 minutes and is out of sync. No restore was performed for 34089 minutes. Restored latency is 2 minutes. Check agent log and log shipping monitor information.

I've verified that the logs are being restored on the log shipping secondary, matched up the LSNs, also, I have the Log Shipping Monitor from Red Gate that verifies my logs are up to date. Further, the aforementioned error message is for every database in the instance.

I've built a similar setup in a lab environment and I get the same error messages.

What gives, anyone have any insight?
Can I safely ignore these messages since I know the logs are being
backed up, copied, and restored?

Having 10s of thousands of error messages per week makes it pretty difficult to peruse and check other possible issues


texts:
I am implementing database(College project).So I come across innodb diagrams in github https://github.com/jeremycole/innodb_diagrams/blob/master/images/InnoDB_Structures/B%2BTree%20Record%20Delete%202.png
    I see that we maintain a delete chain why we wanna do this because if we write the whole block(because it if more efficient write whole block ) back why don't just simply delete that record .And what is the use of maintaining delete chain.As we can see they use linked list so deleting the whole record requires no extra cost. Please explain or tell me where i am wrong?


texts: Is there a document that specifies un-installation of SQL server 2016 R server

texts: Basically I want to remove all components of R server from SQL Server 2016 install?


texts:
I have recently been researching Selective Xml Indexing and have been trying to figure out how to index a .query method

If I have the below query

WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT

    ,a.value('@Number' , 'INT') As Number
    ,c.query('.') as FormattedXml

    From

    dbo.XmlTable As X

    Cross Apply x.XmlColumn.nodes('Root') aa(a)
    Cross Apply a.nodes('Block') bb(b)

I can write a selective Index as below if I am following it right:

CREATE SELECTIVE XML INDEX xsi_XmlTable
ON dbo.XmlTable( XmlColumn )
WITH XMLNAMESPACES ( Default 'http://www.w3.org/2001/XMLSchema' )
FOR
(
      root = '/Root' AS XQUERY 'node()'
    , rootNumber = '/Root/@Number' AS SQL INT SINGLETON

)
GO

Can I include the c.query in the index?  Thanks


texts: The purpose of transaction log in Restore First

texts:
Based on this article , the golden role for restore first

  After restoring full database backup, restore latest differential
  database backup and all the transaction log backup after that to get
  database to current state.

Differential backup saves all the data changes since the last full backup.

I dont understand why must we restore transaction log backup since ( full database backup + differential backup ) should restore my database to its latest state.

My apologies , I cannot think of a better question title


texts: Status of objects owned & permissions granted by a (now)-disabled Windows Account

texts:
When an employee (esp. sysadmin) leaves (ie, their AD account is disabled), what happens to objects they 'owned' (permissions they granted) in SQL Server?

In other words, for SQL Server security, do I have to change the ownership of those objects, grants?

(Currently on SQL Server 2008; anticipating upgrade to 2016 this fall.)


texts: Difference between 'GrantedMemory' and 'MaxQueryMemory' attributes in Showplan XML

texts:
Following new attribute to the Showplan XML schema SQL Server 2014 or 2016 was introduced by KB3170112.

MaxQueryMemory under MemoryGrantInfo: Maximum amount of memory
available for individual query grant in KB, if the query requires
memory to run.

Attribute GrantedMemory existed before above was announced. Now both are there in Showplan XML.
Here is an example:

From above picture if I add the value of RequestedMemory and RequiredMemory total is =62,864 KB. Where is the value 295,344 KB  for MaxQueryMemory coming from or what does that value really mean?
I also looked at 2017 Schema, which has below definition.

MaxQueryMemory: Maximum memory in KB allowed for single query.


texts: insufficient privileges while executing oracle stored procedure?

texts:
I'm getting an insufficient privileges error while executing the following oracle stored procedure. I'm using Oracle Database 10g Express Edition.

CREATE OR REPLACE
PROCEDURE sp_update_acounts(
    accounts_file_dir  IN VARCHAR2,
    accounts_file_name IN VARCHAR2)
IS
BEGIN

EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_accounts_dir AS '''||accounts_file_dir||'''';

EXECUTE IMMEDIATE 'grant read, write on directory ext_accounts_dir to myuser';

EXECUTE IMMEDIATE 'drop table crm_account_stage';

EXECUTE IMMEDIATE 'CREATE TABLE crm_account_stage (entity_account_id NUMBER(19,0), crm_id VARCHAR2(255 CHAR)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY
ext_accounts_dir ACCESS PARAMETERS (FIELDS TERMINATED BY '','' ( entity_account_id CHAR(225), crm_id CHAR(225))) LOCATION ('''||accounts_file_name||''''||') )';

MERGE INTO ua_crm_accounts acc
USING (
  SELECT entity_account_id,
         crm_id
  FROM crm_account_stage) acc_stage
ON (acc_stage.entity_account_id = acc.pkey)
WHEN MATCHED THEN
  UPDATE SET acc.crm_id = acc_stage.crm_id;

END;

I'm using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure successfully. I have all the rights for the oracle user because I'm the admin. I have given all possible rights.

But when I execute the SP I'm getting error like:

Error starting at line 13 in command:
execute sp_update_acounts('C:\Users\surenr\Desktop\UA\Intitial-Conversion','acc_data.csv')
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "myuser.SP_UPDATE_ACOUNTS", line 7
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

Update:
I'm not trying to update user or password. But the error message says as i'm trying to modify user details. When I try the same code step by step outside the stored procedure, it's executing without any problem.

What could be the reason for this? How can I resolve the issue?


texts: Why does Inner Join introduce a hidden sort?

texts:
Consider the following example:

DECLARE @suppliedNumber MONEY
SET @suppliedNumber = 245656.00

SELECT
    ah.FirstName,
    ah.LastName
FROM
    AccountHolders AS ah
JOIN
    [dbo].[Accounts] AS a
ON
    ah.Id = a.AccountHolderId
GROUP BY
    ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber

As you can see there is no ordering of any kind in the query. But looking in the execution plan I see implicit sorting:

Why is this happening? What's the technical reason behind this?

I'm using Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)   May 27 2016 15:06:08   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.3  (Build 14393: )


texts:
I am using Postgres with psycopg2 and sometimes when I run VACUUM VERBOSE table_name; it gives me a line giving the oldest xmin and sometimes it does not. Is there a way to make sure that it always provides it?

Here is the output one that where it didn't give me the value:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.02u sec elapsed 0.03 sec.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

Here is one time where it did give it to me:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

You will notice that the second time there is the following line:

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410


texts: Importing PostgreSQL backup into slightly different database

texts:
I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to convert the old schema into the newer one.

The only difference between the newer schema and the one represented in the backup is that some of the columns have been renamed, and a few of the types marked as varchar have become text instead.

I was hoping there'd be something like a "mapping file" that would instruct PostgreSQL to import the data and rename/retype the columns so as to wind up with the correct result, but I think I may have to resort to pgScript instead.

Does PostgreSQL offer a facility that would make this sort of restoration operation possible?


texts: Retrieve All MySQL/MariaDB Table Definition From .frm Files

texts:
I wonder if it is possible to retrieve the MySQL/MariaDB table definitions from the .frm files?

For example, if I wanted to steal your database and I had access to your MySQL/MariaDB files but didn't have its user account, could I copy all the .frm files from your database, paste them into my own server and then run mysqldump --no-data to retrieve the exact DDL?

Next if the answer is yes:

Does it apply to different server versions and/or OS?
What can I do to prevent or make it difficult for people to do this with my database?

Next if the answer is no:

Is there any dependency to other file? Or would I have to do something?
What exactly is the role of the .frm files?


texts:
I am using SQL Server 2008 R2. My server got accidentally shutdown. After restarting it, I am not able to access my database. I am getting the following error for a few tables:

  SQL Server detected a logical consistency-based I/O error: incorrect
  checksum (expected: 0xd1723da7; actual: 0xab6a6efb). It occurred
  during a read of page (1:69592) in database ID 16 at offset
  0x00000021fb0000 in file 'H:\BACK UP\SQL17052014\databasse.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

I tried DBCC CHECKDB. It was giving error in one page. I tried to repair that page but not able to do so.

Can any one help me solve this problem?


texts:
So I originally thought I was going to have a one to many relationship in a database with the following tables

ENTITY_JSON_MAP
ATTRIBUTE_JSON_MAP

Originally I was going to have an FK in ATTRIBUTE_JSON_MAP pointing to ENTITY_JSON_MAP.
I was going to the Attribute table with

alter table ATTRIBUTE_JSON_MAP add unique (NAME, JSON_MAP_ENUM, ENTITY_JSON_MAP_FK)

Now however I've realized that I need to make it a many to many relationship, so I've created a mapping table, but now I've lost the ability to make sure that I don't get duplicates of that combination.

I was hoping there was still a way to do this. Inserting/deleting tables doesn't need to be fast, as the table will not be inserted or deleted at run time.


texts: Table structure for keeping track of multiple similar accounts

texts:
I'm creating a database to keep track financial information for about 30 different accounts. So far this has all been handled with each account as its own Excel table with the different  years across the top, and the and ledger lines along the side. All of the tables are identical.

I want to put this into a database to make it more manageable, but I'm not sure if I have the best structure. Here are the basic table structures:

ledger_lines(ledgerCode, ledgerName)

account_X(accountCode, ledgerCode, year1, year2...)

With the bold bold as the primary key and ledgerCode referencing the ledger_lines table. Also, there would be 30 of the account_X tables eg. account_1, account_2... for all of the different accounts, all referencing the ledger_lines table.

Is this the best structure for managing this database? For example, and new field will have to be added to every table at the beginning of every year and to compare ledger lines across the accounts I will have to query all of the tables.


texts: Is there a way to add "distinct" to SSMS IntelliSense?

texts:
For the last few versions of SSMS (SQL Server Management Studio), I have noticed that IntelliSense does not contain the word distinct.  Is there a way I can add this to the IntelliSense word bank?

The only instructions for configuring IntelliSense I could find had no options for adding to the word bank.

Is there an undocumented means to do this? While not critical to my productivity, it is something I would like to correct.


texts: SQL Server table and view show different values

texts:
I have a view that selects data from a table that is showing different data.

The table is dbo.user, the view is dbo.users (don't judge, I inherited this system).  The view is simply select * from table.

If I run the following statements:

SELECT Field1, Field2 FROM dbo.[User] WITH (NOLOCK) ORDER BY Field1;

SELECT Field1, Field2 FROM dbo.Users WITH (NOLOCK) ORDER BY Field1;

I get different values for one of the fields.  See the attached pic:

Any ideas how this happens?  The table is clustered, has a pk and unique index.


texts: How can I obtain continuous results with a PostgreSQL query?

texts:
I am using PostgreSQL version 8.1.  I have a table of dates and tasks.  The tasks have start times and end times in the table.  The query below does not work as expected.

SELECT sites.abbrev,
       (SELECT count(*)
        FROM (
          SELECT DISTINCT measurements.task
          FROM measurements
            JOIN tasks ON tasks.id = measurements.task
          WHERE measurements.ztime >= '2016-10-10'
            AND measurements.ztime <= '2016-10-15'
            AND tasks.site = sites.id
        ) ALIAS
      ) AS num_tasks,
      count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   AND measurements.ztime >= '2016-10-10'
   AND measurements.ztime <= '2016-10-15')
WHERE sites.abbrev = 'AA-10'
GROUP BY sites.id,
         sites.abbrev,
         measurements.ztime::date
ORDER BY measurements.ztime::date;

The results are shown below:

 abbrev | num_tasks | total_time
--------+-----------+------------
 AA-10  |        62 |         36
 AA-10  |        62 |          5
 AA-10  |        62 |         58
 AA-10  |        62 |         28
 AA-10  |        62 |         17
(5 rows)

Each row corresponds to one 24 hour period (Ex. 2016-10-10 to 2016-10-15).  The problem is that I need num_tasks to show me the results for each 24 hour period, not the total number as shown.

PostgreSQL version 8.1 does not allow for dates to be used with generate_series and there are no windowing functions available either.

How can I modify my query so I can achieve the desired results?


texts: MySQL - Creating sort index and update query takes forever

texts:
I have the following MySQL query

UPDATE PCL AS p
JOIN HST AS h
ON h.SrNo =
   ( SELECT hi.SrNo
     FROM HST AS hi
     WHERE hi.SYM = p.INST
       AND hi.DATE >=  p.Date
       AND hi.HP >= p.T1
     ORDER BY hi.Date
     LIMIT 1
   )
SET p.T1A = h.Date
WHERE p.BS = 'B'
AND p.SrNo <18000;

However, this query takes over 3 hours to execute, SHOW FULL PROCESSLIST shows "Creating sort index" and almost 95% of the time it is creating sort index.

EXPLAIN SHOWS

Select type: DEPENDENT SUBQUERY
table: hi
type: index
possible keys: NULL
key: hst_idx_sym_date_hp
key_len: 32
ref: NULL
rows: 1002718
extra: Using where; Using index; Using filesort

I also tried the below update queries using subquery, but they are even slower

UPDATE PCL
SET `T1A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T1 order by HST.`Date` limit 1)
WHERE `BS` = 'B' AND SrNo <18000;

UPDATE PCL
SET `T1A` = (SELECT MIN(HST.`Date`) FROM `HST` WHERE HST.`Sym`=PCL.INST AND
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.`T1`)
WHERE `BS` = 'B' AND SrNo <18000;

Is there a way to optimize this query so that it executes faster?

Here is the SHOW CREATE TABLEs

CREATE TABLE `PCL` (
`SrNo` int(6) NOT NULL AUTO_INCREMENT,
`Date` date DEFAULT NULL,
`INS` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`T1` float(8,2) DEFAULT NULL,
`T1A` date DEFAULT NULL,
 ......
PRIMARY KEY (`SrNo`)
) ENGINE=InnoDB AUTO_INCREMENT=16893 DEFAULT CHARSET=latin1
and SHOW CREATE TABLE HST

CREATE TABLE `HST` (
`SrNo` int(11) NOT NULL AUTO_INCREMENT,
`SYM` varchar(20) DEFAULT NULL,
`Date` date DEFAULT NULL COMMENT 'YYYY-MM-DD',
`HP` float(8,2) DEFAULT NULL,
....
PRIMARY KEY (`SrNo`),
UNIQUE KEY `HST_idx_SYM_date_OP` (`SYM`,`Date`,`OP`),
UNIQUE KEY `HST_idx_SYM_date_CP` (`SYM`,`Date`,`CP`),
UNIQUE KEY `HST_idx_SYM_date_PC` (`SYM`,`Date`,`PC`),
UNIQUE KEY `HST_idx_SYM_date_LP` (`SYM`,`Date`,`LP`),
UNIQUE KEY `HST_idx_SYM_Date` (`SYM`,`Date`) USING BTREE,
UNIQUE KEY `HST_idx_SYM_date_HP` (`SYM`,`Date`,`HP`) USING BTREE,
KEY `HST_idx_date` (`Date`)
) ENGINE=InnoDB AUTO_INCREMENT=1103583 DEFAULT CHARSET=latin1 COMMENT='HST
Data - All'


texts: SQL 2005: logical consistency-based I/O error: incorrect checksum

texts:
I got this error this morning during the execution of a SSIS package:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x5fb84eb5; actual: 0x7fb84eb5). It occurred during a read of page (1:6434165) in database ID 8 at offset 0x00000c45aea000 in file 'D:\Data\REPORTING.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I ran DBCC CHECKDB and it found no errors:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'REPORTING'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

However, I do see an entry for that page_id in the suspect_pages table.

Do I have a problem or not?


texts: How to connect to a |Database from SQL Script, in SQL developer

texts:
Hi i am trying to connect to a DB using a connection string mentioned in a Script file.. in SQL developer.

I am using the below format

Connect USERNAME/PASSWORD@HOSTNAME:PORT/Service_name..

But its not working same pattern works in TOAD, i want to do this via a connection string from script file.


texts: Could the creation of an index break table dependencies?

texts:
I ran a query using the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats to determine what are my options for missing indexes. After analyzing the data I determined that I needed to create a non-clustered index for Table A.

Looking at Table A, I noted that there are 55 dependencies (Tables, SPs, Views) and 23 objects that are dependent on this table. My question is, if i create this non-clustered index on this table does it affect the dependencies and dependents of this table?

Moreover, if yes it affects the dependencies, how do I even attempt fix the dependencies?


texts: Efficient way to paginate in sql server with 50M plus data (sql server 17)

texts:
I was looking for efficient way of pagination in sql server 2017 so I came across following code:
select id, col_1, col_2, col_3
from table_name
where deleted_at is null
order by id
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only option(recompile)

This works fine for small offset. But, as the offset value increase. It gradually starts to take a lot of time.
So, I decided to fetch data with something like id > xx concepe. Something like
select id, col_1, col_2, col_3
from table_name
where deleted_at is null
and id > 50000000 and id <= 50000050
order by id

This code works lighting fast. However, this could not work perfectly if there are deleted data.
So, I tweak little bit and switch to Top method instead.
select Top(50) id, col_1, col_2, col_3
from table_name
where deleted_at is null
and id > 50000000
order by id

This seems quite close to get next 50 data in lighting speed. However, this too seems failed when you want to move from page 1 to page 3. You could not know how many data are deleted in between row 50 to row 100. So, this too seems failed for numeric pagination.
Now, I started to look pagination with top method and finally found:
SELECT  top(50) id, col_1, col_2, col_3
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id) AS RowNum, id, col_1, col_2, col_3
          FROM      table_name
          where deleted_at is NULL
        ) AS RowConstrainedResult
WHERE   RowNum >= 50000000
ORDER BY RowNum

But, even for this it took around 20-25 sec when the RowNum is around 50M.
How can I decrease this query time. AnyIdea?
NOTE:
I have already created an index as
create index IX_table_id_deleted_at on table_name(id asc,deleted_at) include(col_1, col_3, col_3) where deleted_at is null

Also, I have tried with NOCOUNT ON as well as WITH(NOLOCK) but still the query time is around 11 sec


texts: Finding five-minute interval prior to current time

texts:
I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as expected. I want to know if there is any better way...

mysql> select now() ;
+---------------------+
| now()               |
+---------------------+
| 2012-03-11 08:43:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT date_sub(from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300), interval 5 minute) as ten_minute_interval,  from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300) as five_minute_interval;
+---------------------+----------------------+
| ten_minute_interval | five_minute_interval |
+---------------------+----------------------+
| 2012-03-11 08:35:00 | 2012-03-11 08:40:00  |
+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT some better query...


texts:
I am about to launch something really big. I need to prepare my server and database.

I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one user trying to log in to the appropriate user table.

For instance, how would I know that user jay@mail.com is related to the user table #36?

Would it be the same to have 10 millions users in one user table or 100 of 100,000?

How does Facebook? I can't believe they would have one global user table with 950 million entries.


texts: Displaying query plans in Server Management Studio

texts:
Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset:

select top 10
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,

    qs.plan_handle, st.text
from
    sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as st
group by qs.plan_handle, st.text
order by sum(qs.total_worker_time) desc

Question 1: What exactly is the plan_handle? It doesn't appear to be a hash of the plan, like it is in Oracle. I ask because I want to be able to detect the situation in which the plan of a statement has changes.

Question 2: Once I have a plan_handle, I am interested in the actual plan. So I do, for example:

select * from sys.dm_exec_query_plan (0x060006001F176406B8413043000000000000000000000000)

In the query_plan column I get a link that when I click displays an XML document. If I save it on disk as whatever.sqlplan, I can double-click it in Windows and it displays correctly in Management Studio. Surely there must be a way to avoid this step?!

Question 3: Is there a way to convert the XML back into a textual format, like in the old days of SET SHOWPLAN_TEXT? I want to be able to view them graphically, but also automate diffing them in some meaningful way.

Thanks!


texts:
I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty.

To duplicate the issue, create a new SQLite 3 database and add a table. Add at least one field in the table and make sure its a numeric field (such as integer or float or something). Then create a new view with a SELECT statement similar to the following:

SELECT *, Sum(MyField) AS MySumField FROM MyTable

Now run sqlite3 <database_name> and type the following:

PRAGMA table_info(MyView);

You will notice there is no data type for some reason. Here is my output when I issued the command:

0|Field1|INTEGER|0||0
1|Field2|INTEGER|0||0
2|Field3|FLOAT|0||0
3|SumField2||0||0

Does anybody know if this is an actual bug and if there is a workaround for this behavior?


texts: Is the MySQL JSON data type bad for performance for data retrieval?

texts:
Let's say I have a MySQL JSON data type called custom_properties for a media table:

An example of the json data stored in the custom_properties column could be:

{
 "company_id": 1,
 "uploaded_by": "Name",
 "document_type": "Policy",
 "policy_signed_date": "04/04/2018"
}

In my PHP Laravel app I would do something like this:

$media = Media::where('custom_properties->company_id', Auth::user()->company_id)->orderBy('created_at', 'DESC')->get();

This would fetch all media items belonging to company 1.

My question is that lets say we have 1 million media records, would this be a bad way to fetch records in terms of performance? Can anyone shed some light on how MySQL indexes JSON data types?

From the MySQL official docs:

  JSON documents stored in JSON columns are converted to an internal
  format that permits quick read access to document elements. When the
  server later must read a JSON value stored in this binary format, the
  value need not be parsed from a text representation. The binary format
  is structured to enable the server to look up subobjects or nested
  values directly by key or array index without reading all values
  before or after them in the document.


texts: Why Database use Buffer pool for write operation?

texts:
In this case, first we write data in RAM,then same data write in database?

Why we need  do write operation two time?


texts:
I have a source table where one of column has data like:

Column
SD
SD
..
..
JK
Jk
..
..

I want to load this data into staging in format of:

Column
S&D
S&D
..
..
J&K
J&k
..
..

Meaning I want to insert '&' character wherever we have data like SD and JK, while loading to staging.

I need to convert it for SSIS. So I want to give here SSIS formula to achieve this.

How can we achieve this?


texts: Why can't C# SMO see extended properties on a column but Powershell SMO can?

texts:
I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.
The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:
Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?
Additional Information
In the C# code I open up a DataReader on a table using:
sourceServer.ConnectionContext.ExecuteReader(<select command>)

to retrieve the data from the table. I then go into a while loop with DataReader and inside that while loop I have:
foreach (Column col in sourceTable.Columns)
{
StringBuilder cleanData = CleanseColumn(col, dr[col.Name].ToString());
sbvalues.Append("'" + cleanData + "', ");
}

When I step through the foreach, the sourceTable variable has its extended property, but the col column variable does not.


texts: How to display a number with fixed number of digits before the decimal?

texts:
I am looking for result 0123.56 and the following query should have worked.

SQL> select to_number(123.56,'0999.99') from dual;
select to_number(123.56,'0999.99') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

SQL>

But it doesn't. What am I doing wrong here?


texts: MS SQL Server 2016 - Single Partitioned Table vs Multiply Separated Tables

texts:
We need to store a lot of logs in MS SQL Server 2016 Database for 1-1.5 year.
Income: 50 Gb of logs per month (~ 50 millions rows per month), we store all data in 6 months and shrink 50% of this data for older records.
Prerequisites:

Required different indexes for last 1st month, 2nd month and others.
Required data compression for big XML/Text data older than 2 months.
For now - Required data shrink for data older than 6 months (just remove 50-60% records with big data (XML/Text) from table), but it will be better just heavily compress rows and don't remove it.

Main Use-Case: search for 100-200 (sometime 1000) rows by some IDs or string filters in SQL IN manner (sometime LIKE).

Exist two approaches for partition:

"Old style" - Separated Tables for each period (I think without common View in our case), month as example (Log_0117, Log_0217, ...).

Pros: Fully independent management and schema (for evolution), independent indexes and query/plans caching, as expected - more performance and efficiency, simple to implement, less magic and more controlable.
Cons: Many-many tables (8 for every period), complex queries (especially for cross-period queries).

"Modern style" -
Partitioned tables and indexes.

Pros: Easy management, just one table for all periods, in theory simpler queries.
Cons (as I found by some articles): Strong requirements for partitioned indexes (by normal, all indexes must contain partition-column (therefore more memory required), such indexes require rebuild after partition switching, hard to change schema/indexes for different periods, more magic and less controlable.

My questions for community:

Is it really builtin Partitioned tables/indexes make developing more simple?
Does it have really inconvenient limitations now in MS SQL Server 2016 (as Enterprise, as or everything not so bad?

UPD1:
Columnstore Indexes - another interesting option from @DavidBrowne, but as described at MSDN:

  Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.

Unfortunately, we mostly search for 100-200 records in logs, rather than calculate complex statistics, but I save it in the mind for future, maybe will be helpfull.

UPD2: @DanGuzman, I meant Aligned indexes when said about some requirements - https://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx:

  Partitioning NonClustered Indexes

  When partitioning a unique nonclustered index,
  the index key must contain the partitioning column. When partitioning
  a nonunique, nonclustered index, SQL Server adds the partitioning
  column by default as a nonkey (included) column of the index to make
  sure the index is aligned with the base table. SQL Server does not add
  the partitioning column to the index if it is already present in the
  index.

It's also heavily recommended not to have Non-Aligned indexes 1) partitioning big tables - indexes , 2) Performance Impacts of Partitioned Aligned Indexes.

It means - if we want to make queries faster just for last month, we need to: 1) cover all table by this index (but it requires really more disk/memory) or 2) organize long maintenance window for partition switching and indexes rebuilding.


texts:
I am new to oracle, so I don't know how lobs are saved, like in postgresql all lobs are saved in a table ( pg_largeobject) and all tables with lobs column have an id for this "main table".
So does oracle save lobs in a table and all tables just have an id to this "main table" or each table has his own copy of the lob.


texts: Quorum Questions For An FCI And AlwaysOn in a WSFC

texts:
I was reading the white paper on FCI + AlwaysOn as well as several other resources on quorum voting differences in Windows Server 2008 and 2014 and had 3 questions.

1- Will giving a node in the WSFC that is a read only Replica a vote in WSFC let it vote on the FCI quorum or is the 'vote' in WSFC for something else?  That part I'm not clear on.  The host is only part of an AlwaysOn replica set and in the same WSFC as the FCI, but it is not part of the FCI.  It currently shows as having a vote in WSFC.

Will it count as a vote for any FCI's we have on that WSFC or it will be ignored?
Cluster info:

Get-ClusterNode  | fl Nodename, Nodeweight
    NodeName   : Server1
    NodeWeight : 1

    NodeName   : Server2
    NodeWeight : 1

    NodeName   : Server3
    NodeWeight : 1

Get-ClusterQuorum
Cluster              QuorumResource
-------              --------------
Cluster1      Cluster Disk 2

2- Will a long failover (let's say 30-50 seconds) cause a service shut down in 2012 due to the quorum disk NOT being online during that period if the server is turned off/disabled for some reason, leaving us with only 2 or 1 vote(s)?  In other words is WSFC intelligent enough to know that a Quorum is a shared FCI resource in which the host is currently failing over, so temporary ignore the fact that it's down.  Sometimes an FCI failover takes a long time on Windows 2008 in our environment.

3- Will any FCI have a issue if it only has 1 vote?  Let's say we have a quorum and a 2nd node and both the quorum and 2nd node are down at the moment due to failover or a disk issue.  Would that 1 vote then say it has 50% or less and turn off services?

Edit: I could also ask this differently: Is the vote a *total available number of votes in the entire WSFC or is it just currently available nodes?  I feel it's the *total number of all nodes regardless of current state hence why we need to stop votes from the DR sites often but I wanted clarification.  Thanks!

Current topology is simple:

    2 node multi tenant cluster (2 active instances on 2 nodes).
This will be updated to meet our requirements of n+1 so we will have 3 nodes.

    1 read only AlwaysOn read only replica server with no automatic failover

    1 quorum disk on the FCI as a witness


texts: How do I ensure that bulk inserts must fire my DML trigger?

texts:
I have a table using a DML trigger to ensure data integrity.  Although authors of SSIS packages, BULK INSERT queries, and bcp executions can enable DML trigger execution, which is disabled by default, I need a way to ensure data integrity regardless of whether the author remembers to enable trigger execution.  How can I:

ensure that the trigger fires regardless of what the package author does, or
ensure data integrity using another method within the database?

The table itself is along the lines of:

CREATE TABLE [dbo].[MyTable]
(
    [MyId] BIGINT IDENTITY NOT NULL PRIMARY KEY,
    [StoreCode] VARCHAR(50) NOT NULL,
    [ItemCode] VARCHAR(50) NOT NULL,
    [StartDate] SMALLDATETIME NOT NULL,
    [EndDate] SMALLDATETIME NULL,
    [IsDeleted] BIT NOT NULL DEFAULT (0)
)

and the trigger:

CREATE TRIGGER [dbo].[MyTrigger]
ON [dbo].[MyTable]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @OverlapIds TABLE
(
    [MyId] BIGINT NOT NULL PRIMARY KEY
)

INSERT @OverlapIds (MyId)
SELECT i.MyId
FROM   inserted i
WHERE  i.IsDeleted = 0
AND    EXISTS (SELECT 1 FROM [dbo].[MyTable] m
               WHERE  m.IsDeleted = 0
               AND    i.PricingId != m.PricingId
               AND    i.StoreCode = m.StoreCode
               AND    i.ItemCode = m.ItemCode
               AND    (i.StartDate BETWEEN m.StartDate AND COALESCE(m.EndDate, '2079-06-06 00:00:00') OR
                       m.StartDate BETWEEN i.StartDate AND COALESCE(i.EndDate, '2079-06-06 00:00:00')))

IF EXISTS (SELECT 1 FROM @OverlapIds)
BEGIN
    -- Do some things, then:
    RAISERROR('Rows with IsDeleted = 0 and matching store code and item code values may not have overlapping start and end dates.', 16, 1)
    ROLLBACK TRANSACTION
END

Denying privileges on the table is not a practical solution in this case unless there is a specific privilege denial that would still allow valid data to be inserted or updated.  Check constraints are unlikely to help because the business rule requires other rows to determine whether the rule has been violated.  Uniqueness constraints are unlikely to help because they would not prevent the date overlap.  Designing the table better is, unfortunately, not a practical option at this time.  Upgrading SQL Server itself is not completely off the table, though the immediate problem is in SQL Server 2005.


texts: When taking backups from databases, is it "good practice" to use logical backup device for backup location? What is the benefit of using backup devices?


texts:
its kind weird idea which I would need suggestions. A classical example:

users
-----
ID | USERNAME
1    test1
2    test2
3    test3

emails
------
USER_ID | EMAIL
1         test1@email.com
2         test2@email.com
2         test3@email.com
1         test4@email.com
3         test5@email.com
3         test6@email.com
2         test7@email.com
3         test8@email.com
1         test9@email.com

and a select:

SELECT * FROM users LEFT JOIN emails ON users.ID = emails.USER_ID

would resuls all emails of users. So far its so good, but lets say its a very big system and we are on MyISAM, so no real foreignkey connectionts. I only delete a user, so all of their emails remain. Normally we would say its bad, because lots of unconnected garbages remain. But what if we wipe out them later? We can SELECT those unnecessary records later and a cron-task would erase them. Is it bad, crazy idea?


texts: SQL ServerRecovery Model Change Tracking

texts:
Are SQL Server recovery model changes logged anywhere...WMI, error logs...etc?

For instance, if I change from FULL to SIMPLE, is this recorded anywhere?


texts: Cassandra - Query a column with collection type

texts:
I am pretty new to cassandra, so pardon me if this turns out to be a silly question.

I have a table structure as below

CREATE TABLE data_points (
  id text PRIMARY KEY,
  created_at timestamp,
  previous_event_id varchar,
  properties map<text,text>
);

I wanted to know, whether I can execute a query which gives matching records from the map type fields.

For instance, if I insert values in the table as below

INSERT INTO datapoints (id, properties) VALUES ('1', { 'fruit' : 'apple', 'band' : 'Beatles' });

Will I be able to fetch it as

SELECT * from data_points WHERE properties.band='Beatles';

Please Help.


texts: Why an object became invalid in oracle databse.

texts:
There are some invalid views and functions in my database.

I am not sure what changes caused these objects to became invalid since many people are working on the same database.

How can I see which change caused the view to become invalid?


texts: Will a mysqldump ever break my database?

texts: I tried to backup my database with mysqldump. I've done this on a live website also. Is there any chance that this command could ever break a website/database?


texts:
Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) says the following 4 patches are recommended to be applied to 11.2.0.4 database.

Note:21744343.8 Combo of 11.2.0.4.5 OJVM PSU and 11.2.0.4.8 DB PSU (Oct 2015)

Note:21555791.8 Oracle JavaVM Component 11.2.0.4.5 Database PSU (Oct 2015) (OJVM PSU)

Note:21352635.8 11.2.0.4.8 (Oct 2015) Database Patch Set Update (DB PSU)

Note:19852360.8 Oracle JavaVM Component 11.2.0.4.1 Database PSU - Generic JDBC Patch (Oct 2014)

I have a newly installed 11.2.0.4 database with absolutely no patches applied on it.

Now should I go ahead and apply all the above mentioned recommended patches on the database? or should I apply any previously released patches before applying these?

If yes, is there an order in which the above patches is to be applied?

Also, I will be applying the above patches through OEM12c, is there anything I should take care in doing the same?


texts: Why aren't unsigned integer types available in the top database platforms?

texts:
Databases are usually very customizable with varying data types and custom lengths.

It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither PostgreSQL and MS SQL Server. MySQL and Oracle seem to.

This seems like a glaring omission on their part - the next best perfomant option being a long/bigint, (8 byte integer), but could be completely unneccessary! Does anyone know why they would choose to not include native unsigned int support?


texts: Query with INNER JOIN takes long time than expected?

texts:
I have the following query, which takes longer than expected.
Anybody help me find a faster alternative?

SET SESSION MAX_JOIN_SIZE=18446744073709551615;

SELECT
    claims.claim_id,
    claims.lease_id,
    claims.accident_date,
    claims.insurer_reference,
    claims.status,
    claims.loss,
    claims.responsibility,
    claims.age,
    claims.collected_date,
    motor.make,
    motor.model,
    motor.YOM,
    estimations.workshop,
    estimations.labour_charges AS EstLabour,
    estimations.spare_parts AS EstSpares,
    estimations.lump_sum AS EstLsum,
    estimations.towing_charges AS EstTowing,
    estimations.total AS EstTotal,
    MIN(approvalspl.approval_date) AS PLAppDate,
    SUM(approvalspl.labour_charges) AS PLAppLabour,
    SUM(approvalspl.spare_parts) AS PLAppSpares,
    SUM(approvalspl.lump_sum) AS PLAppLsum,
    SUM(approvalspl.total_amount) AS PLAppTotal,
    approvalstl.approval_date AS TLAppDate,
    SUM(approvalspl.towing_charges) AS PLAppTowing,
    approvalstl.sum_insured AS sum_insured,
    approvalstl.total_amount AS TLAppTotal,
    approvalspl.estimation_id,
    SUM(approvalspl.deductible) AS PLDeductible,
    approvalstl.deductible AS TLDeductible,
    approvalstl.towing_charges AS TLAppTowing,
    claims.reason,
    motor.SI,
    policies.policy_key,
    claims.city,
    claimsnotes.notes,
    motor.chasis_number,
    claims.tp_payment,
    claims.tp_recovery
FROM claims
LEFT OUTER JOIN motor ON claims.lease_id = motor.lease_id
LEFT OUTER JOIN estimations ON claims.claim_id = estimations.claim_id
LEFT OUTER JOIN approvalspl ON estimations.estimations_id = approvalspl.estimation_id
LEFT OUTER JOIN approvalstl ON estimations.estimations_id = approvalstl.estimation_id
INNER JOIN policies ON motor.policy_id = policies.policy_id
LEFT OUTER JOIN claimsnotes ON claims.claim_id = claimsnotes.claim_id
GROUP BY claims.claim_id


texts: How can I send HTTP requests from SQL Server 2012 to a non-.Net application?

texts:
I know basic only concepts (DDL,DML queries) of the SQL Server 2012. I explain the workflows of both my application and the database below.

Workflow of the application:

I build web application in Java. Whenever it receives an HTTP request, it fetches data from the database, does some computations on the data and the computed data will be stored in another database.

Before explaining the database workflow, I will explain what a database modification is from my point of view: If any operation happens like creating new tables, data modification, data deletion in tables, etc. indirectly all these are effects on the database.

Workflow of the database:

I have SQL Server 2012. Whenever the database is modified, it will send an HTTP request to the application. The Application runs on an Apache Tomcat server. Once the application receives the HTTP request, it continues that individual workflow.

If you understand my question, we are not sending HTTP requests from a browser (i.e. end-users can't access this application), we are sending HTTP request from SQL Server.

I hope you understand what my question is.

I read some tutorials for this, but I didn't get anything.

Can anyone suggest the correct way of doing this?


texts: Postgresql - Internal Array handling - array type or pointer type

texts:
I have a question, about the internal handling of array columns in Postgres.
Does the engine handle it as a classical array with memory alloc or is it somehow a pointer list.

It would be interesting to know - I have a few questions to answer myself concerning performance... :)


texts: MySQL Event scheduler not generating CSV

texts:
The scheduler seems to be running from INFORMATION_SCHEMA.EVENTS but there is no CSV output.

The event scheduler has to automatically produce a csv to pass on the data to another system.  It's like integrating 2 systems through the csv, maybe not the optimum way.

The version of MySQL is 5.6.25.  What can be the issue?

The event is as below:

DROP EVENT x.tbl_event;
DELIMITER $$
CREATE EVENT  x.tbl_event
    ON SCHEDULE
      EVERY 30 minute
DO BEGIN
            select @sent := max(id) FROM x.TBL_COUNT;
            select @val := max(submission_id) FROM x.tbl_form;
             IF @sent < @val THEN
              SET @sql_stmt:= concat("select submission_id, application_type, applicant_name,
             applicant_nic,applicant_email,applicant_address_line1, applicant_address_line2,applicant_tel,
             applicant_mobile,association_name,association_registrationno,
             association_address_line1, association_address_line2,position_applicant,no_members, submission_date, no_of_guests
             from x.tbl_form where submission_id>@sent and application_type !=''
             INTO OUTFILE 'C:/output", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'");
             PREPARE extrct FROM @sql_stmt;
             EXECUTE extrct;
             DEALLOCATE PREPARE extrct;
             INSERT INTO TBL_COUNT(id, datecreated) VALUES(@val, NOW());
            END IF;

END $$
DELIMITER ;

The column last_executed from INFORMATION_SCHEMA.EVENTS seems to indicate that the event gets triggered (and so my code runs), and yet I get no output file.


texts: Problem with aborted PT-online-schema change command

texts:
I aborted a pt-online-schema change command to change a table definition. Now, when I run pt-online-schema change again, I get this error:

  The table <db name>.<table name> has triggers.  This tool needs to create its
  own triggers, so the table cannot already have triggers.

How do I run pt-online-schema change again?

I use MySQL.


texts: How do I count and order by unique entries?

texts:
I have this:

SELECT tblClient.[Printed Name], tblClient.[Address 1]
     , tblClient.[Address 2], tblClient.City, tblClient.State
     , tblClient.Zip, tblClient.Phone, tblClient.[Email Address]
FROM tblClient
INNER JOIN tblCurrent
    ON tblClient.[Client Code] = tblCurrent.Client
GROUP BY tblClient.[Printed Name], tblClient.[Address 1]
       , tblClient.[Address 2], tblClient.City, tblClient.State
       , tblClient.Zip, tblClient.Phone, tblClient.[Email Address]
       , tblCurrent.[Date Ordered]
HAVING (((tblCurrent.[Date Ordered])>#6/27/2013#))
ORDER BY tblClient.[Printed Name];

Now I need to get a count and sort by the number of times each unique "Printed Name" shows up.

I've looked all over but can't figure this out from other questions people have asked. I'm not usually the one at my company that has to mess with Access. It seems like this is probably a simple problem, but I'm noobier than noob.

Any help would be appreciated.


texts: SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present?

texts:
Here is an example picture of my layout.

As you can see I have my SCD types present (status/startdate/enddate/businesskey).

My key is a surrogate key that identifies each record.

My problem is that my hierarchy seems to error out and it may be due to an incorrect layout.  Here is my current key column structure.

Hierarchy

LineOfBusinessId (key column: LineOfBusinessId) (name column: LineOfBusinessName)

WorkerDivisionId (key column: LineOfBusinessId, WorkerDivisionId) (name column: WorkerDivisionName)

WorkerId (key column: LineOfBusinessId, WorkerDivisionId, WorkerId) (name column: WorkerName)

This dimension errors out because a workername changes and when the select distinct occurs during full processing it finds duplicates.  I am just wondering what the best way of setting up the key columns is for this specific situation.  Do the names need to be part of the key columns or is my hierarchy completely incorrect.  I was sure I had this set up correctly but I am obviously questioning myself now.

I have received some advice that the best thing to do in this situation is to use the surrogate key as part of my key columns, but then found out that this will cause issues as I only want one record to show up when querying my SCD.  Later, I received some more advice saying that I shouldn't use the surrogate in the id key columns but in the name key columns, specifically at the Worker level.

I am rather skiddish of updating all of my cubes with this setup without hearing from someone who has made a hierarchy of this type.  When I look at this setup, I have to think that others have made hierarchies of this type as it is rather simple.  Any advice/direction on this is greatly appreciated.

Thanks.


texts:
I have a setup of Windows Server Failover Cluster and i am using SQL Server Service with one DC, One Primary Server and One Secondary Server.

During Using SQL Server Management Studio, when i click at Show Dashboard Under "Always On High Availability", It shows that error i.e.

An error occurred on the always on dashboard

How to resolve this issue ?


texts: Set Value Based on Number of Records with Certain Values using SQL Server

texts:
I am stuck on how to return a bit value based on records with certain values.  Query needs to return TRUE or FALSE if there are three different Levels of the same Control:

Example data:

PRODUCT    CONTROL    LEVEL
test1      8          1
test1      8          2
test1      8          3
test2      8          1
test2      8          1
test2      8          3
test4      8          1
test4      8          3
test4      8          7

I need a query that would return:

PRODUCT    STATUS
test1      PASS
test2      FAIL
test4      PASS

Not sure where to begin.  Any tips are greatly appreciated.


texts: Is the chunk size a limit to the max data size that can be associated with a shard key value?

texts: I haven't read about the relation between chunk size and the data size associated to a single shard key value in a collection, so, another way to ask the question could be, can a shard key span more than one chunk?


texts: Get a truly RANDOM row from a PostgreSQL table quickly

texts:
I always used to do:

SELECT column FROM table ORDER BY random() LIMIT 1;

For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started hunting for more efficient methods. People recommended:

SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1;

While fast, it also provides worthless randomness. It appears to always pick the same damn records, so this is also worthless.

I've also tried:

SELECT column FROM table TABLESAMPLE BERNOULLI(100) LIMIT 1;

It gives even worse randomness. It picks the same few records every time. This is completely worthless. I need actual randomness.

Why is it apparently so difficult to just pick a random record? Why does it have to grab EVERY record and then sort them (in the first case)? And why do the "TABLESAMPLE" versions just grab the same stupid records all the time? Why aren't they random whatsoever? Who would ever want to use this "BERNOULLI" stuff when it just picks the same few records over and over? I can't believe I'm still, after all these years, asking about grabbing a random record... it's one of the most basic possible queries.

What is the actual command to use for grabbing a random record from a table in PG which isn't so slow that it takes several full seconds for a decent-sized table?


texts: Skip running trigger during cascade delete?

texts:
I have a legacy application where I have two tables X and Y.  Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X and Y (such that entries in Y are deleted when X is deleted).

Is there any way to have the trigger NOT run during a cascade delete?

The only way I currently know that this could be done is to create a package level variable to use as a flag that the trigger would check to determine if it should run.  I would then change the value of the flag in my stored procedure before and after running the delete on table X.

I was hoping to find something a bit cleaner where you could simply tell oracle not to execute the trigger if it is a cascade delete.


texts: Installing Agensgraph version of postgresql

texts:
I'm trying to install Agensgraph on postgresql

I have changed the configure file to use port 5435

I follow the INSTALL file instructions, at the following line I get an error

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test

createdb: could not connect to database template1: could not connect to
server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Here's the full routine

./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test

log file contents

2018-05-22 07:54:49.873 CEST [13186] LOG:  could not bind IPv6 address "::1": Address already in use
2018-05-22 07:54:49.873 CEST [13186] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2018-05-22 07:54:49.873 CEST [13186] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2018-05-22 07:54:49.873 CEST [13186] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2018-05-22 07:54:49.873 CEST [13186] FATAL:  could not create any TCP/IP sockets
2018-05-22 07:54:49.873 CEST [13186] LOG:  database system is shut down

I have another instance of postgresql running on default port 5432, so it looks like the Agens version of postgresql is trying to use the default port, even though I specified 5435 in the configure file. Any ideas?

grep -H '^port' /etc/postgresql/*/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf:port = 5432


texts: Why does VALUES provide a different DEFAULT column name than SELECT?

texts:
SQL Columns can get different default names depending on if the columns come from subqueries (where they all get ?column?)

# SELECT 1,2,3,(SELECT 1),(SELECT 2),(SELECT 3),* FROM (SELECT 1,2,3) AS t;
 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------+----------+----------+----------
        1 |        2 |        3 |        1 |        2 |        3 |        1 |        2 |        3
(1 row)

Or, from VALUES LIST (where they all get column#)

SELECT * FROM (VALUES (1,2,3)) AS f;
 column1 | column2 | column3
---------+---------+---------
       1 |       2 |       3
(1 row)

This is mentioned in the docs on VALUES LIST,

  By default, PostgreSQL assigns the names column1, column2, etc. to the columns of a VALUES table. The column names are not specified by the SQL standard and different database systems do it differently, so it's usually better to override the default names with a table alias list, like this:

And, then this is mentioned in the docs on SELECT

  In more complex cases a function or type name may be used, or the system may fall back on a generated name such as ?column?.

Is there any reason we generate different sets of names like this? Why not just have the SELECT list generate sequentially all unnamed columns in the same fashion as the VALUES list?

This shows a behavior that I think is somewhat awkward, I would expect, column1-column3 twice.

WITH t1 AS ( SELECT 1,2,3 ),
     t2 AS ( VALUES (1,2,3) )
SELECT * FROM t1, t2;

 ?column? | ?column? | ?column? | column1 | column2 | column3
----------+----------+----------+---------+---------+---------
        1 |        2 |        3 |       1 |       2 |       3
(1 row)


texts: Is there an efficient way in Postgres (with any limitation introduced by the fact its running on AWS RDS taken into account) to clone a table?


texts: How to hide metadata from Postgres users?

texts:
I need to provide views-only access to some subset of a database, for use by report-writing tools. It's easy enough to use permissions to prevent access to the data in the underlying tables, but I need to go one step further, and prevent these views-only users from examining the contents of Postgres' metadata tables, which would allow them to reverse-engineer the entire database schema. I managed to do that, too.

Here's my problem: When I connect Crystal Reports to this database, it emits a series of error messages, probably due to its inability to nose around in the database's metadata.

I'd really like to allow Crystal Reports to see only that subset of the metadata which describes the views that Crystal Reports can actually query (or at least get it, and other tools like it, to stop complaining). Is there some "right" way to do this that I'm missing?


texts: I had created an index wrongly and now I am trying to drop that index. Since the table is large, dropping the index is taking lot of time. Is there any other way to drop the index quickly?


texts: PostgreSQL: Slave doesn't replicate the Master's data

texts:
I'm using PostgreSQL 9.2.3 on both computers, both of which run Win2008R2. I've setup streaming replication by following this guide: http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/

My setups are:

Master

postgresql.conf

listen_address = slaveip, localhost #i tried with *(all) as well.
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8

pg_hba.conf

host replication      replicator      slaveip            trust

Slave

postgresql.conf

wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
hot_standby = on

pg_hba.conf

host replication      replicator      hostip            trust

I've re-worked the script from the guide to a .bat but for now i'm manually doing everything it does (using pg_basebackup etc.).

Both databases start and work without errors but the slave doesn't replicate any changes made to the master.

I don't have a trigger file yet, because i don't quite understand it. Does the trigger file just trigger the failover from Master to Slave or does it have an active role in the streaming replication process ?

EDIT @Daniel: I had forgotten to check the Slave logs and only checked the Master ones, my bad. There is only this error in the logs:

2013-04-25 05:26:46 PDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

As this is a test environment, there are no firewalls on, no TMG(praise the lord), no antivirus, no anti-anything. The 2 machines can ping each other, they can open shared folders, etc. I realise that the master is denying access (now that i checked the logs).
Could this be a problem with the user Postgres not having the right permissions ?

EDIT @Milen: Yes, I manually created one:

standby_mode = 'on'
primary_conninfo = 'host=masterip port=5432 user=replicator password=thepassword'
#trigger_file = 'postgresql.trigger' #This one is commented, because i haven't made the trigger yet.

EDIT: Hm, now i'm getting this error:

2013-04-30 07:22:03 PDT ERROR:  cannot execute LOCK TABLE during recovery
2013-04-30 07:22:03 PDT STATEMENT:  LOCK TABLE instances IN EXCLUSIVE MODE

Atleast now I know where to look.


texts:
We have some application forms in our system and want to define them as Reports using SSRS 2008 R2. So when users see the form on the screen and what to print it as a report in a print friendly format, they can.

I'm new to SSRS and wondering how it can be done. From what I can see the format of reports in SSRS is either tabular or matrix which none is what we need. If you imagine an application form (e.g.: Car registration), the data fields are distributed on the whole page not solely on a row.

I want to put the placeholders on the form as defined by the business analyst and link the columns of the Data source to these placeholders individually.

Any help is much appreciated.

Cheers,
Nazila


texts: Convert Character Encoding, and Check Encoding for Issues

texts:
I'm planning to convert a table from utf8 (_general_ci) to utf8mb4 (utf8mb4_unicode_ci). I want to make sure I don't have any malformed data after the conversion though so I was planning to duplicate the data, run a join on the two tables, and see if there are any differences.

Is this a good way to go about it?

1.

Create table Email_utf8mb4  like Email;

2.

insert into Email_utf8mb4 select * from Email;

3.

ALTER TABLE Email_utf8mb4
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

4.

select * from Email as old
join Email_utf8mb4 as new
on old.notificationid =new.notificationid
and (old.subject <> new.subject OR old.content <>
    new.content)

4.5. Assuming no rows are returned...

5.

drop Email;

6.

RENAME TABLE Email_utf8mb4 to Email;

subject and content are the only alpha columns in my table.


texts:
I have a database in microsoft access.  I want to know how to look up a singular datum from a reference table giving a  dynamic set of values.  Here is a representation of what I mean:

I have the following tables:

Points for Pushups(m):

Reps      Age 17-21,          Age 22-26,         Age 27-31

1            6                  7                    8

2            7                  9                    9

3            9                  11                  12

Fitness Tests:

Name  Reps   Test Date

Bob      2            1 jan 2009

Jill     1            5 may 2010

People:

Name         DOB

Bob      1 jan 1987

Jill     2 feb 1985

Sal      3 Mar 1991

I want the query to use People.DOB and the Test date to find the age the person was during the test.  I then want the query to use this value to determine which column to look in, and the value from reps to determine which row to look in coming back with the singular value and naming it points.

for example I want bob to show

Query:

Name      DOB            Age AtTest   Reps      Points

Bob      1 Jan 1987         22         2          9

Does anyone know how to do the dynamic reference part?

I know how to make the query and I know how to get age I just don't know how to use the values as columns in the reference table, I've seen it done, but long ago and never looked into it.


texts: Using PHP form to insert geometry from WKT and SRID into SQL Server 2017

texts:
I have a PHP 7.3 form that asks the user for a Name, a WKT and an SRID.  I would like to upload the WKT and SRID to SQL Server 2017 so that it creates a geometric object in Upload_WKT_Test using STGeomFromText:

<?php
    if ($_SERVER['REQUEST_METHOD']=="POST") {
        $wkt = $_POST['wkt'];
        $srid = $_POST['srid'];
        $name = $_POST['name'];
        try {
            $wktQuoted = $pdo->quote ($wkt);
            //$wktQuoted = "'$wkt'";
            $sql = "INSERT INTO Upload_WKT_Test (Name, GeomCol1) VALUES (:name, :wktGeom)";
            $wktGeom1 = "geometry::STGeomFromText(";
            $wktGeom = $wktGeom1."".$wktQuoted.", ".$srid.")";
            //echo $wktGeom."

".$name."

";
            $stmnt = $pdo->prepare($sql);
            $theData = [':name'=>$name, ':wktGeom'=>$wktGeom];
            $stmnt->execute($theData);
        } catch(PDOException $e) {
            echo "Error: ".$e->getMessage();
        }
    } else {
        $wkt="";
        $alignment="";
        $srid="";
    }
?>

My init.php:

<?php
    ob_start();
    session_start();
     try {
        $pdo = new PDO( "sqlsrv:Server=localhost\SQLEXPRESS;Database=devdb", "", "");
        $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $pdo->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );
        //$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );
    }

    catch( PDOException $e ) {
        //die( "Error connecting to SQL Server" );
        //die(print_r($stmnt->errorInfo(), true));
        echo "Error: ".$e->getMessage();
    }
    $root_directory = "testwkt";
    $from_email = "admin@somewhere.com";
    $reply_email = "admin@somewhere.com";
    include "php_functions.php";
?>

I understand that the preferred method to accept user input via forms is to use a parameterized query to prevent SQL injection.  I believe the source of the error message has to do with the quotes around the WKT are not making it into the INSERT INTO statement.  Is it possible to surround text values from an input form with quotes (WKT) and use it to build a geometry object?

Example WKT entered into form POINT(100 10)

Example SRID 0

Example Name Test

Geometry from WKT (quotes are required):

geometry::STPointFromText('POINT (100 10)', 0)

Resulting error message:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
A .NET Framework error occurred during execution of user-defined routine or
aggregate "geometry": System.FormatException: 24114: The label
geometry::STGeomFrom in the input well-known text (WKT) is not valid.
Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,
MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE,
CURVEPOLYGON and FULLGLOBE (geography Data Type only).
System.FormatException: at
Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input) at
Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at
Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at
Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid) at
Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s) .

Upload_WKT_Test table:

CREATE TABLE dbo.Upload_WKT_Test
    ( id int IDENTITY (1,1),
    Name varchar(50),
    GeomCol1 geometry );
GO

WKT - Well Known Text - a way to represent a geometric object (point, line, polygon for example) in text format.  More info here.

SRID - Spatial Reference System Identifier - an integer that represents a coordinate system.  More info here.

More info about working with Geometry Instances in SQL Server and Azure SQL Database can be found here.

More info about STGeomFromText


texts: Use SSIS to get measures from a tabular model

texts:
I need to get source data from an Azure Analysis Services via SSIS. While I can easily use an OLE DB source in SSIS to fetch columns from tables, I do not see measures listed.

Is there a way to get values from measures?


texts: Connect a linked server to MS Reporting Service?

texts:
I've got a MYSQL server setup as a linked server in MS SQL server 2008. Can someone tell me if it is possible to generate reports from this server?

How do I connect it to Reporting Services?


texts: Attempting to identify transitive dependency

texts:
Hello I am attempting to identify a transitive dependency in the following diagram. I think that possibly the determinate of "Subtotal" is "Quantity" and "Price Charged" but I'm not sure if "subtotal" is also dependant on the "product_code" which would make it a partial dependency right?

This is how I've worked out the 1NF.

1NF (CUTOMER_NUMBER, INVOICE_NUMBER, PRODUCT_CODE → CUSTOMER_NAME, INVOICE_DATE, LINE_NUMBER, COMMON_NAME, SCIENTIFIC_NAME, CATEGORY, QUANTITY, PRICE_CHARGED)
PARTIAL DEPENDENCIES:
CUSTOMER (CUSTOMER_NUMBER → CUSTOMER_NAME)
INVOICE (INVOICE_NUMBER → INVOICE_DATE)
PRODUCT (PRODUCT_CODE → COMMON_NAME, SCIENTIFIC_NAME, QUANTITY, PRICE_CHARGED)
TRANSITIVE DEPENDENCY
SUBTOTAL (QUANTITY, PRICE_CHARGED → SUBTOTAL)

I'm fairly confident about the other dependencies but am not sure the transitive one is correct?

edit: MODIFIED DETAIL

1NF (CUTOMER_NUMBER, INVOICE_NUMBER, PRODUCT_CODE → CUSTOMER_NAME, INVOICE_DATE, LINE_NUMBER, COMMON_NAME, SCIENTIFIC_NAME, CATEGORY, QUANTITY, PRICE_CHARGED)
PARTIAL DEPENDENCIES:
CUSTOMER (CUSTOMER_NUMBER → CUSTOMER_NAME)
PRODUCT (PRODUCT_CODE → COMMON_NAME, SCIENTIFIC_NAME)
INVOICE (INVOICE_NUMBER, LINE_NUMBER → QUANTITY, PRICE_CHARGED, INVOICE_DATE, PRODUCT_CODE)
TRANSITIVE DEPENDENCY
-   ?

Is this looking better, is there no transitive dependency?


texts: Delete Joined to Derived Table Causes Index Scan and Table Spool

texts:
Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates.

It seems that the query pattern produced by the bulk delete API method from this library is not optimized well by the SQL Server query optimizer.

I am using SQL Server 2014.  Consider the following table and index (names have been changed to protect the innocent):

CREATE TABLE [dbo].[a_staging_table]
(
    [staging_ky] [BIGINT] IDENTITY(1,1) NOT NULL,
    [col_a] [INT] NOT NULL,
    [col_b] [VARCHAR](15) NOT NULL,
    [col_c] [VARCHAR](256) NOT NULL,
    [col_d] [INT] NULL,
    [col_e] [INT] NOT NULL,
    [col_f] [VARCHAR](3) NOT NULL,
    [col_g] [VARCHAR](3) NULL,
    [col_h] [VARCHAR](20) NULL,
    [col_i] [CHAR](4) NOT NULL,
    [col_j] [INT] NOT NULL,
    [col_k] [VARCHAR](10) NULL,
    [col_l] [DATETIME] NOT NULL,
    [col_m] [VARCHAR](25) NOT NULL,
    [col_n] [DATETIME] NOT NULL,
    [col_o] [VARCHAR](25) NOT NULL,
    [col_p] [BIGINT] NOT NULL,
    [col_q] [BIT] NOT NULL,
    [col_r] [DATETIME] NULL,
    [col_s] [VARCHAR](25) NULL,
PRIMARY KEY CLUSTERED ([staging_ky] ASC)
);
GO

CREATE NONCLUSTERED INDEX [IDX_dbo_a_staging_table_col_p]
    ON [dbo].[a_staging_table]([col_p] ASC)
    WITH (FILLFACTOR = 100);
GO

The application is trying to delete all records from the table where col_p is equal to a certain value, and the generated SQL query from Entity Framework looks like this:

DELETE [dbo].[a_staging_table]
FROM [dbo].[a_staging_table] AS j0 INNER JOIN (
SELECT
    1 AS [C1],
    [Extent1].[staging_ky] AS [staging_ky]
    FROM [dbo].[a_staging_table] AS [Extent1]
    WHERE [Extent1].[col_p] = @p__linq__0
) AS j1 ON (j0.[staging_ky] = j1.[staging_ky])

Looking at the query plan, SQL Server does not optimize away the join between the table and itself, and instead, the plan does an index scan with a nested loops join and seek to the same index, followed by a table spool, prior to the delete.

A more traditional and optimal delete statement is something like this statement, which just does an index seek, prior to the clustered index delete:

DELETE [dbo].[a_staging_table]
FROM [dbo].[a_staging_table] AS j0
WHERE [j0].[col_p] = @p__linq__0

The actual query plans for both statements are posted at:https://www.brentozar.com/pastetheplan/?id=SygZoD6um

Is this query pattern generated by this library a known query anti-pattern for a delete statement in SQL Server?


texts: Optimize Table query causing Fatal error during execution with InnoDB table

texts:
I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables.
I have a database that contains several different tables and data is constantly accumulating in these tables. However I have my own automatic services that I use for deleting older rows from these tables.
After deleting old rows with my service I also use optimize table command to free up disk space. (innodb_file_per_table = 1)
Everything has been working well (I have used these services successfully with other tables (size 100-200MB) but now I have faced a problem with one larger table (size 900-1000MB).
Deleting data with that larger table is working fine, but when I try to use OPTIMIZE TABLE query I get error:

Error on mysql optimizing log_events table: Fatal error encountered during command execution.   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at CleanTablesService.Service1.siivoaTaulut(Object& yhteys) in c:\users\green automation\documents\visual studio 2015\Projects\CleanTablesService\CleanTablesService\Service1.vb:line 127

I assume that this has something to do with some buffer_size or something like that? I'm able to execute this exactly same query successfully with MySQL Workbench but when executing query on my service it throws this error.
So is there some value in my.ini file that I need to adjust so that I can make it work or is there some other method that I should use for optimizing table size  (releasing disk space after I have deleted old rows)?
EDIT:
I added some more logging to my service and I noticed that this Fatal error is raised 30s after executing OPTIMIZE TABLE. In MySQL Workbench execution of this same operation takes 67.110s so probably problem is that for some reason MySQL is killing execution in the middle of the process after 30s?
However I cannot find any parameter from my MySQL Settings file my.ini that would affect this 30s timeout.


texts: What is the optimized way to pass multiple parameter from a single table to a function?

texts:
I have a function [dbo].[FN_CalcAerialDistance] which accepts 4 parameters and returns its result.
Now that 4 parameters come from the same table, I do not want to hit Database multiple times to bring same data.
I have tried:

SELECT [dbo].[FN_CalcAerialDistance]
    (
       (select latitude from loc.locations where place_name = 'Delhi'),
       (select longitude from loc.locations where place_name = 'Delhi'),
       (select latitude from loc.locations where place_name = 'Mumbai'),
       (select longitude from loc.locations where place_name = 'Mumbai')
    )

  Any way to optimise the code? I have also tried to access table datatype like an array


texts: Error in using sp_describe_first_result_set with Restore

texts:
Is querying the backup metadata with sp_describe_first_result_set system sp supported?

For example the query:

SELECT * FROM sys.dm_exec_describe_first_result_set (N'RESTORE HEADERONLY FROM DISK =''C:\SQLBackups\SomeDBbackup.bak''',NULL,0)

returns NULL for each field.

error_message field:

  The metadata could not be determined because statement RESTORE
  HEADERONLY .... does not support metadata discovery.

error_type_desc field:

  UNSUPPORTED_STATEMENT

Is there a way to make this query work?

Thank you


texts: Switching a partition on a table with one to many relation

texts:
There is two main tables in database (SQL Server 2014)

table1:
----------------------------------------------
id         name         datetime        image

table2:
--------------------------------
id         Address      phone     person_id

As you can guess there is an "one to many" relation between two tables, and the foreign key of [table2] is [person_id].

I'm trying to use partition table and sliding window features on table1. Everything perfectly done (I tested sliding window and partitioning on single table successfully) except one thing.

For sliding window , the oldest partition will be switch to an Auxiliary table (with same structure of table1) and the newest partition will split for new datas.

Partition function and scheme is created on datetime column.

As Microsoft mentioned,:

  For switching task, the all indexes must be aligned.

In our situation the table1 has a primary key that is unique non-clustered index too (the database made it automatically) and the foreign key of table2 connected to that.

When stored procedure tried to switching out the oldest partition, it rises an error:

  the table1 is partitioned but the [primary key index] column is not partitioned

So now the all indexes is not aligned because of this primary key !

I tried to include datetime to primary key, but database created an index for new primary key automatically on filegroup instead of partition scheme. Also tried other situations but no difference.

My final purpose is to switch last partition to auxiliary table and truncate that(about one billion records per partition). So the related records in table2 must delete too.

I'm looking for a solution to solve this situation and handle partitioning, switching and truncating on related tables with related columns (like this one).

Please don't attend so much to the structure or image storing stuffs! , I just tried to propose a concept from my circumstance.