texts: SQL Server backup practice with limited disk space

texts:
I have a SQL Server 2012 Std Edition installed, contain 1 database in full recovery mode, so the log grown up quickly, but I just need a PIT recovery in past 7 days.

Can I schedule a full database backup every day (rolling 7 days) and truncate the t-log right after the backup job ? or what is the best way to keep the file size as small as possible ?


texts: Understanding the usage of version store in tempdb

texts:
We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4.

I know tempdb would need the space depending upon the transactions, and how queries are designed to use it.

We had an issue for disk space getting full , where we analyzed how tempdb grew that big over a week.

After a reboot, via monitoring we came across 4-5 queries, which were holding up version store for more than 20 minutes, grew tempdb same day from its initial size of 2 GB ( 4 data files) to over 100 GB within a spam of 6-8 hours.

For example

query A holds the version store for 10 minutes where current version generation rate was 23.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 17 MB and tempdb file size was 10332 MB.

Later after an hour the query B ran, the version store for 16 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 45 MB and tempdb file size was 45332MB.

Similarly a query C ran after 3-4 hours, the version store for 15 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 19 MB and tempdb file size was 85332MB.

and so on on. during our monitoring for that day.

Now same queries run next day and day after,

We see that tempdb allocations and dellocations value running to somewhat similar during the very first day, but tempdb did not grew that fast.

same happens for upcoming 7-8 days where growth is approx 8-10 GB compared to first day where it grew to 100 GB for the similar queries running each day.

Also , the current output of select  count(*) from sys.dm_tran_version_store

is 196470 and its been 2 days since a reboot.

May be i am unaware of some process here, but please help me understand what could be the reason apart from those identified queries as we are trying to improve if they are the one causing tempdb to grow that much.

Note* tempdb size is 110 GB and Free space it shows is almost 109 GB.


texts: Instead of static list, using a table select result for IN key

texts:
I use a static list for some queries WHERE clause like

WHERE STypeID IN ('12', '13', '14', '265')

This list will be changed in the future; new values will be added. When list changed, I don't want to check all queries which has this list to change, want to control this list from 1 place; so I created a table with two columns; Key and Value, inserted my static values into table.

      Key             Value

SupportedTypeID        12
SupportedTypeID        13
SupportedTypeID        14
SupportedTypeID        265

I modified my where clause like

WHERE STypeID IN (SELECT Value FROM LOVs WHERE Key LIKE 'SupportedTypeID')

but it causes performance issue; queries results come too late.


texts:
I am using three node alwayson Availability Group SQL 2012 Enterprise Edition.
2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR
I need to perform a DR switchover with all DC down And DR database up for applications.
Can this solution work or I need to use Log Shipping as third node instead of alwayson AG node.
Please help on this.
Thanks


texts:
I have a scheduled report with a query used to determine the recipients. Every time the report runs, there seems to be a few people who are not receiving the emails.
I enabled SMTP logging, and indeed the missing people are not in the logs at all.
What steps can I do to further debug this issue?


texts: Can't add field that begins with an underscore in Crystal Reports

texts:
There's a field in a FoxPro table that I'm trying to pull in a Crystal Report (v10), and it begins with an _ and it doesn't appear in Field Explorer->Fields->Database Fields-><table-name>->_fieldName

Are fields that start with _ invisible to Crystal Reports?


texts: CTE Query copy value if exists into an existing table

texts:
I got the following query:

WITH table_data AS (
             SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description,
                    UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) AS code,
                    UNNEST(array[NOW(), NOW(), NOW()]) AS updated,
                    UNNEST(array[NOW(), NOW(), NOW()]) AS created,
                    UNNEST(array[0.15, 350.0, 5.0]) AS sales_price,
                    UNNEST(array[0.51, 550.0, 10.0]) AS price,
                    UNNEST(array['Tax0', 'cdc', '54289hijlj']) AS code_p,
                    UNNEST(array['242', 'ded', 'fjDIashdukah']) AS sales_c
           ),
        parts_inserted AS (
         INSERT INTO parts (description, item_code, created, updated, sale_p,
                                    price, code_p_id, s_id_code)
         SELECT table_data.description, table_data.code, table_data.created, table_data.updated,
                table_data.sales_price, table_data.price, rates_p.id, rates_c.id
           FROM table_data
           LEFT JOIN rates AS rates_p
              ON table_data.code_p = rates_p.code
           LEFT JOIN rates AS rates_c
              ON table_data.sales_c = rates_c.code
          JOIN parts p ON p.item_code = table_data.code
          WHERE p.company_id = 65312322
         RETURNING *
       )
       SELECT * FROM parts_inserted;

The query selects the data ( WITH table_data AS... ) and inserts it into the parts table.

Table parts:

CREATE TABLE
    parts
    (
        id INTEGER DEFAULT nextval('p_id_seq'::regclass) NOT NULL,
        company_id INTEGER,
        item_code CHARACTER VARYING,
        price DOUBLE PRECISION,
        code_p_id INTEGER,
        sale_p DOUBLE PRECISION,
        s_id_code INTEGER,
        profit DOUBLE PRECISION,
        margin DOUBLE PRECISION,
        created TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        updated TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        taxable BOOLEAN DEFAULT true,
        origin CHARACTER VARYING,
        origin_id CHARACTER VARYING,
        description TEXT,
        created_by_id INTEGER,
        name CHARACTER VARYING,
        tsv TSVECTOR
    );

If code_p exists on the rates table, the query must insert the rates.id into parts.p_code_id
If sales_c exists on the rates table, the query must insert the rates.id into parts.s_id_code
If none of them exists, the p_code_id and s_id_code must be blank.

Table rates:

CREATE TABLE
    rates
    (
        id INTEGER DEFAULT nextval('r_id_seq'::regclass) NOT NULL,
        company_id INTEGER,
        name CHARACTER VARYING,
        rate DOUBLE PRECISION,
        created TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        updated TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        type CHARACTER VARYING,
        origin CHARACTER VARYING,
        origin_id CHARACTER VARYING,
        code CHARACTER VARYING,
        is_payable BOOLEAN DEFAULT false,
        description CHARACTER VARYING,
        active BOOLEAN DEFAULT true,
        has_component BOOLEAN DEFAULT false
    );

Currently only valid code_p and sales_c row gets inserted, if  code_p/sales_c exists:
(
LEFT JOIN rates AS rates_p
          ON table_data.code_p = rates_p.code
       LEFT JOIN rates AS rates_c
          ON table_data.sales_c = rates_c.code
) it is inserted into parts table.
If code_p or sales_c does not matches with the rates table, the row must be inserted with blank(NULL) code_p/sales_c... and this is not happening.

What should the query do?

if code_p or sales_c matches with the rates table:

LEFT JOIN rates AS rates_p
              ON table_data.code_p = rates_p.code
           LEFT JOIN rates AS rates_c
              ON table_data.sales_c = rates_c.code

the parts.code_p_id and parts.s_id_code columns must be updated with the rates.id value.

if code_p or sales_c doesn't match with the rates table, the parts.code_p_id and parts.s_id_code columns must be blank/null.

Question:

What am I missing?


texts:
We have a db which reports errors when trying to do a backup:

Starting to dig in, the first thing that was suggested was to run DBCC CHECKDB. I've been trying to run that all day now, and googling different things, and different options, but just cannot get it to run. Running:

DBCC CHECKDB ('XiaconBackupServer') WITH NO_INFOMSGS, ALL_ERRORMSGS

yields this output, and absolutely nothing else:

  Msg 8921, Level 16, State 1, Line 2
     Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

There are no other errors, or any other output, so check previous errors is completely unhelpful. Tried running the same command on all the other DBs on the server and they work fine, so it's definitely something isolated just to this DB.

The other thing it mentions is disk space for tempdb, and I saw someone say in a post that they needed to manually increase the tempdb log file for it to work.
Using the ESTIMATE option with CHECKDB says it needs all of about 4MB. So I've gone overboard, and manually sized my tempdb files (data and log) up to a minimum of 100MB. And they are set to autogrow on a drive which has about 300GB free, so tempdb space should not be the issue either.

Is there anything else we can check, or is this db likely hosed?

We are running SQL Server 2014 Express on Windows Server 2008 R2.
(Yes, I know they are both way old version, ... out of my control.)


texts: PostgreSQL: Query for location of global tablespace?

texts:
I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides.

As all our clients are on the same host as the database manager, it should be easy enough for user-created tablespaces; the client can look up the filesystem path for the tablespace (in spclocation), and use OS calls to check how much available space is available:

adb=> select * from pg_tablespace;

  spcname   | spcowner |    spclocation    |       spcacl
------------+----------+-------------------+---------------------
 pg_default |       10 |                   |
 pg_global  |       10 |                   |
 adb        |  2033793 | /database/adb     | {adb=C/adb}

I can't see how, from the client, to get the path to where the global tablespace is stored; an empty string is returned for it in the above query.

Unfortunately, we have many legacy systems in the field using a particular database created in the global tablespace, and it would be a monumental effort to get it moved into a user-created tablespace.

Hopefully I'm just missing something really simple.


texts: sqlplus (Oracle XE) doesn't start on CentOS 7

texts:
I followed the steps of this document: https://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm#XEINL121, but sqlplus doesn't start. Even if I used the command: "sqlplus /nolog".
The other services start: APEX, Main page on: 127.0.0.1:8080, and It can connect with SYSTEM user in the page. However, sqlplus still doesn't start.
I executed the command: /etc/init.d/oracle-xe start, and shows a message with "OK".

When I check the status of the oracle-xe service, it shows: active (exited).

What does it mean "exited"? Why not "running"?
How can I execute sqlplus?


texts: Steps to upgrade MySQL from version 5.0 to version 5.5

texts:
I have been developing under MySQL v5.0 for a while now and need to upgrade to v5.5 to be in step with the hosted production server.

After having searched for precise steps to do this migration, I am still unclear as to how to go ahead.

Can anyone provide a clear list of actions needed to upgrade to version 5.5 ?

Thank you

Thank you for these answers.
You are right, I should have given fuller info in my question.
Notably - I am under Windows XP SP3.

Yes I did search through Google beforehand, hence my statement

  "After having searched for precise steps to do this migration,
  I am still unclear as to how to go ahead."

To RolandoMySQLDBA : Thanks for the script, but I cannot see how I could adapt it.


texts: How to change the data directory on Postgresql 9.1 on Ubuntu

texts:
I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04:

I first did a:

sudo pg_dropcluster --stop 9.1 main

and then attempted to create a new one in the desired path:

 sudo pg_createcluster -d /home/fccoelho/Documentos/databases/postgresqldata 9.1 main

but i fails with a permission denied:

Creating new cluster (configuration: /etc/postgresql/9.1/main, data: /home/fccoelho/Documentos/databases/postgresqldata/)...
initdb: could not access directory "/home/fccoelho/Documentos/databases/postgresqldata": Permission denied
Error: initdb failed

How do I make this work?


texts: Where EVENTS history are logged/recorded in MySql?

texts:
I just setup some stored procedures to run in the event scheduler using CREATE EVENT.

I'm trying to find where (if somewhere) the history of it running is stored. I looked at the docs but couldn't find anything.

Is there some table or log where I can see that my scheduled events successfully ran?


texts: PostgreSQL translate special characters?

texts:
Description:

PostgreSQL 9.3

String: 'ì ằ ú ề'

Desired Result:  'i a u e'

My code:

select translate ('ì ằ ú ề', 'ìằúề', 'iaue') ; -- it works. Result: i a u e

Question:

If I use it this way, I have to define a manual translation between 'ìằúề' and 'iaue'. Is there a better solution?

Ref: PG Document


texts:
I am trying to store a specific user's description in the Postgres by doing this:

comment on role blog is 'blog user long name';

However, when I look for it in the pg_description table it is not there:

select * from pg_catalog.pg_description where description like '%blog%';

Returns no results. How can I accomplish this?


texts:
I am a newbie to database design, and I'm a bit confused about how to create a database with minimum redundancy. Suppose I have a table abc with repetition of data in it and no two rows are same. Now to reduce repetition? I follow normalisation rules and break the table abc into other tables with relations to table abc. Now is it a fair practise to add a primary key in table abc explictly which will identify each row uniquely, or we can ignore adding primary key?

If primary key is added in table abc and table abc contain a column authid (int). then it collides with the primary key value in it.

Update

To elaborate a flat db abc suppose i have a library table with columns

ISBN   Title   Auid   Auname   Auphone   Pubid  Pubname   pubphone   price
-----------------------------------------------------------------------------
11111  C++      4     Roman    2147483647  1   Big House    1234567890  20
9999   Emma     1     Austen   1111111111  1   Big House    1234567890  20
9133   Faeri    7     Spenser  2147483647  1   Big House    1234567890  15
9104   Hamlet   5  Shakespeare 2147483647  2   Alpha Press  2147483647  20
1034   Iliad    3    Homer     2147483647  1   Big House    1234567890  25
1234   Jane     1    Austen    1111111111  3   Small House  2147483647  49
9977   King     5  Shakespeare 2147483647  2   Alpha press  2147483647  49
5555   Macbeth  5  Shakespeare 2147483647  2   Alpha press  2147483647  12

here in this lib table we may have same ISBNin two rows but any two rows are unique.the repeatations of data is obvious as shown and to reduce that we can break the table and follow normaliation

Books

Title
ISBN
Price
ISBN primary key

Authors

AuName
AuPhone
AuID
Auid primary key

Publishers

PubName
PubPhone
PubID
Pubid primary

and:

book/author

ISBN
Auid

to maintain book/author relation

So can I add primary key explicitly in flat library table or how to acheive normalisation? I think I should add primary key else no relation. Is this breaking enough to achieve normalisation or any other concept or review is always welcome?
Following example from Access Database Design & Programming, 3rd Edition by Steven Roman


texts: Postgres - how to randomize two columns in a table

texts:
I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals).

I have anonymized all the names and other personalized information, and I would like to keep each lat/long pair together, however randomize them within the users, so that the locations stay accurate, but their association with a User is now random.

I assume this involves an UPDATE function, but I'm not sure how to perform it on the same table.

I am very new at postgres and would appreciate any help - thanks!


texts: Why is ON DUPLICATE KEY not working as expected?

texts:
Before I start, I'm not a web developer by trade. I'm self taught using google and StackOverflow.

So I recently moved my site from a shared hosting to a VPS.

My provider moved my site / files over to my new hosting. Both run cPanel.

I had a script which worked perfectly fine on my old hosting-

INSERT INTO `database`.`table`
    (`UUID`, `UpdateDate`, `JoinDate`)
VALUES
    ('$uuid',NOW(),NOW())
ON DUPLICATE KEY UPDATE
   `UpdateDate` = NOW()

The problem is it doesnt work any more on my new server.

The problem i think is it doesnt update or insert due to not updating all the columns in that table.

I created two test tables, Table A with the 3 columns in above UUID, UpdateDate, JoinDate
and a second
Table B with 4 columns in UUID, Username, UpdateDate, JoinDate.

UUID is both Unique and Primary in both tables.

Table A - This table works as expected.

Table B - This table doesnt insert/update due to the extra column Username.

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query? Is it a PHP settings I need to change on my hosting?

Sorry if I haven't provided all information, this is my first DBSE question.

CREATE TABLE TestUserA`
( UUID varchar(40) NOT NULL,
  UpdateDate datetime NOT NULL,
  JoinDate datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE TestUser
  ADD PRIMARY KEY (UUID),
  ADD UNIQUE KEY UUID (UUID),
  ADD KEY UUID_2 (UUID);

Server type: MariaDB Server version: 10.2.14-MariaDB - MariaDB Server
Protocol version: 10 cpsrvd 11.68.0.36
Database client version: libmysql - 5.1.73
PHP extension: mysqli
PHP version: 5.6.30


texts:
Our current system setup sees each server backed up by 3rd party software(Veeam). The backup for our Database server occurs once each night.

Alongside this, there is another 3rd party tool that performs database replication to a backup SQL Server.

As it stands, if a disaster were to occur then we automatically switch over to the replicated backup SQL Server. Then switch back once production has been fixed.

I am not sure if this is enough for DR. All our databases are currently in Simple mode. I feel that they should be in Full recovery mode, and Full/Differential/Log backups should be performed on a regular basis.

Or is the replication and Full backup enough for DR? I am basically trying to put an argument together in favour of Full/Differential/Log backups.

Any suggestions?

Thanks!


texts: Empty Table: Insert into Table WITH (TABLOCK)

texts:
We have a clustered index table which is empty. Table has around eg: 20 columns, 50 million rows, around (40GB).

Is it best practice to apply Insert With (TABLOCK) for empty tables such as this? The table is a datawarehouse table, and conducted during ETL hours. There are no concurrent users, and we are in simple recovery mode. Just curious, if so we can update our 100+ stored procedures.


texts: sysarticles system view - what does a status of 57 mean?

texts:
I'm trying to work out why one article within a publication isn't replicating. I've scripted it out and confirmed the replication procs it's supposed to call are the correct ones, and have run a trace on the destination instance looking for those procs being called - no results.

Querying the sysarticles view in the published database, I noticed the article in question has a status of 57, whereas the rest of the articles have a status of 17.

Looking at msdb, http://msdn.microsoft.com/en-us/library/ms174970(v=sql.90).aspx, there are 5 bitwise values available: 1, 8, 16, 24, 64.

So it makes sense that the articles with status 17 are made up of 1 and 16 above.

But even if I add all bitwise values above excluding 64, I can't get a status value of 57. The max is 49.

Can anyone please explain to me what a status of 57 is, and whether it could be the cause of my article not replicating.

Thankfully this isn't on production, so I'm going to try and update the status manually, and if that doesn't work, take the table out of replication and add it again as its own publication.

But it would be good to understand what a status of 57 means.


texts: Filtering a table based on multiple values of a single column

texts:
I have a list of names in a specific column of a table. Is there a way to build a query which can pull out all the attributes of this names? I am a total novice so any help is appreciated

Example: User table

table columns:

User id
attributes
user privileges
1st name
2nd name

The filter has to be applied based on a bunch of user first names.


texts: Monitor volume of data returned to the client application

texts:
I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team and rebuffed in a way and so I'd like to provide more detail to emphasise that we are sending an excess of data to the client. My analysis contained a supporting document explaining waits and also a wealth of resources including those from the actual client application side stating that high ASYNC_NETWORK_IO is related to poor code.

To follow this up, I'd like additional detail emphasising the volume of data consistently returned the client and tie these to peaks in our ASYNC_NETWORK_IO waits. Are there any DMVs or any other free sources I could use to report on data sent to the client?

I am aware of sys.dm_exec_query_stats although this only displays information for queries still in the plan cache.

Edit:
Over the past couple of weeks/last month performance has degraded significantly which coincides with a colleague leaving the company. Whether this is coincidence or not is unknown to me. The issue we have is we are unaware of what is going on at the client side. At the moment we have effectively 1 junior developer looking at it and no means to monitor. From the SQL Server side I am constantly seeing little strain and I want to rule SQL as the bottleneck. Obviously things can be done to improve the databases and I'll never dispute it but the issue at the moment is not SQL Server related and I need to be able to relay this. Factors such as the application simply not being able to respond and issues with connecting from the client to the actual application server (not application SQL Server) are for some reason being attributed to SQL Server, with our software development manager looking at Indexing of all things as a cause for the application not responding and people not being able to connect despite no request being made the the SQL Server ?!?!?

Our situation is an incredibly frustrating one in that it's heavily turning into a political battle and accusations are been thrown at us without any substance or data to back them up. In terms of understanding what happens at the client side, it's really difficult for me, like I said, we have no monitoring on the application side, but also the way in which the client submits queries makes things difficult. The request we see at the SQL Server layer is C\AL code (from the dynamics NAV application), but also they appear to have instances where the client session sumbits many batches....

I'll provide an example from last week which caused great confusion for me. The user submitted a query on the client side to return aged stock for customers. This request executed for 30 minutes however whilst monitoring from the SQL Server side I saw many individual requests from the session. So in SQL obviously we'd just join the relevant tables using good syntax and practise to return data. However on the client side, from what I could tell would iterate through all customers in the customers table, and one by one return the data back to the client whilst simulateanously looking at the other relevant tables for aged stock information by individual customer.

This was just one report and unfortunately I am getting little support from devs for troubleshooting. The whole political situation is really hurting our process with the software development manager pursuing a theory based on 'finger in the air' logic. Apologies for the long read.

Thanks,


texts: Why MySQL imports only one record for each table?

texts:
Code shows some info:

$ cat /etc/*-release | grep VERSION
6:VERSION="12.04.5 LTS, Precise Pangolin"

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2

$ mysqldump -uUSER -pPASS DBNAME > dump.sql

$ ll dump.sql
-rw-r--r--  1 root    397M Dec 29 01:13 dump.sql

$ mysql -uUSER -pPASS DBNAME < dump.sql

MySQL don't show any errors but after import i have only first rows in all tables.
I've checked with head sql syntax and it seems to be correct.

I've tried also mysqldump -uUSER -pPASS DBNAME | gzip -9 > dump.sql.gz and got same situation.

Why this is happening?
And how to fix?

Thanks.


texts: How to prevent scheduler jobs from running at instance startup

texts:
I'm currently testing Oracle 11g restore procedures from RMAN backups. I can restore instances to another host, but problem I'm having is that as soon as I open the restored instance, scheduler jobs start immediately.

I can disable scheduler by running:

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

But I can't do this until after instance has started.

Is there a way to disable jobs before starting up the instance?


texts: docker postgres 9.6 cannot append to pg_hba.conf

texts:
I have an instance of postgresql running in a docker container that I use for development, so it's regularly torn down and built up again.
End of last week, I suddenly started getting an error when trying to rebuild the container.
postgres | The files belonging to this database system will be owned by user "postgres".
postgres | This user must also own the server process.
postgres |
postgres | The database cluster will be initialized with locale "en_US.utf8".
postgres | The default database encoding has accordingly been set to "UTF8".
postgres | The default text search configuration will be set to "english".
postgres |
postgres | Data page checksums are disabled.
postgres |
postgres | initdb: directory "/var/lib/postgresql/data" exists but is not empty
postgres | If you want to create a new database system, either remove or empty
postgres | the directory "/var/lib/postgresql/data" or run initdb
postgres | with an argument other than "/var/lib/postgresql/data".

After some trial and error, I managed to narrow it down to my appending data onto the end of the pg_hba.conf file
RUN echo "Appending to 'pg_hba.conf'"
RUN cat ${SCRIPT_LOCATION}pg_hba.conf >> /var/lib/postgresql/data/pg_hba.conf

Up until the end of last week, this wasn't an issue.
One thing I did notice is that 10 days ago, the image for postgres 9.6 (actually, all of the postgres images) was updated (https://hub.docker.com/layers/postgres/library/postgres/9.6/images/sha256-bb25ba088bf6a4be81478a7051b412bb94d004249f007d1a7fee6a4fc622250a?context=explore)
Has anyone else encountered issues since the image update, and if not, is there a better way to append to the end of the config file?


texts: Do I really Need to Upgrade to SQL Server 2014 SP2 ? -

texts:
We have a Application upgrade to next version.

SQL Server 2014 is currently on SP1.

The upgrade pre-requisite is not necessarily SQL Server SP2.

But I feel and read the below link which says SQL Server 2014 has many fixes related to AOAG and some fix to SQL File stream.

https://support.microsoft.com/en-in/help/3171021/sql-server-2014-service-pack-2-release-information.

I am stuck up in below situation:

My Manager asked do we really need to upgrade to SP2 as it is not necessary for upgrade .

He is little worried as this might be involved in little risk as they are PRODUCTION servers.

But I am sure Microsoft released the SP2 for some intention as this help the SQL Server 2014 performance.

I want to convey the same and make them accept the feasibility to apply the SP2 for our PROD servers ?

So do I really need to apply SP2 and how can I make them understand it helps ?


texts: Question about set log_slow_verbosity='query_plan' in MariaDB ans MySQL

texts:
I have a question about set log_slow_verbosity='query_plan'

I set log_slow_verbosity='query_plan' on MariaDB 10.

I check the slow log and find more detailed information such as: full_scan; Full_join; tmp_table_on_disk.

When I set set log_slow_verbosity='query_plan' on MySQL 5.7:

mysql> set session log_slow_verbosity='query_plan,innodb';
ERROR 1193 (HY000): Unknown system variable 'log_slow_verbosity'

How to enable this parameter on MySQL 5.7 and get more detailed information in slow log?


texts:
Thanks to the recent weather/floods, we lost many of our servers. This included our central database server as well as the server that holds our backups. The database server was running Oracle 12c enterprise on RHEL6. We offload our backups to an offsite facility once a month. Long story short, our most usable backup is over 3 weeks old. The database server was sent to a data recovery place, who managed to get us partial files, some of which we believe contain our datafiles. But these files are incomplete and lack any / all header information. Is there any way of salvaging any data out of these files at all? Opening up the files with a hex editor displays the data we need, but we are taking about a 50 GB database and mining through the datafiles manually would take forever. We have pretty much given up recovering anything but I thought I would just ask the question here in case someone else has had a similar experience. I am aware of Oracle DUL tool (already opened an SR with Oracle in this regard and this was their advice) but the fees they charge are exorbitant. We are small company - we have already paid a tidy sum for the data recovery service. We simply cannot afford to pay what Oracle are asking for, as much as we would like to. Can anyone suggest a possible alternative please? And we are currently reviewing our data backup policies but as I mentioned - we are a small business with limited resources - and we didn't plan for natural disasters like we should have - we are going to be more careful in the future though.

UPDATE : Right turns out the hard drives themselves are fine though the LVMs are corrupt. I managed to rebuild the partitions with the help of testdisk. But I can't seem to mount the drives. I get an error "NTFS Signature Missing". Looking at dmesg it looks like mount has looked for etx3 and ext4 super blocks and didn't find them. So I am assuming the partitions are corrupt.

Thanks in advance.

Alex


texts: Prevent Reconnections During Application Cluster Restart

texts:
I have a large cluster of application processes (>5000) spread out over multiple EC2 servers. When I attempt to restart them or deploy new code (with a restart), they all attempt to reconnect to the database at the same time, causing the CPU on my RDS MySQL database to spike and the server usually locks up. My current solution is to add sleeps in-between individual instance restarts, however this makes my deploy take a very long time.

I would like to be able to restart everything as quickly as possible. One thought I had was to set up proxies local to each application server that would hold the connection open during the application restart. I haven't yet found something to use in production that is reliable and doesn't add lots of overhead, and I'm not sure if this is the right path to continue down.

Does anyone have suggestions for how to support a cluster like this?


texts: MySQL - Grant user permission to create table as long is it has a prefix in the name

texts:
I'd like to grant a user permission to create tables in a database so long as the table has a particular prefix, let's say example_.

I've tried the following:

$ mysql -u root -p
mysql> CREATE DATABASE example;
mysql> CREATE USER xyz@localhost IDENTIFIED BY 'password';
mysql> GRANT CREATE ON example.`example_*` to xyz@localhost;

However, when I log in to example as xyz I'm unable to create tables:

$ mysql -p -u xyz example
mysql> CREATE TABLE example_table (id INT);
ERROR 1142 (42000): CREATE command denied to user 'xyz'@'localhost' for table 'example_table'

Is this even possible? If so, how?


texts: Design for active/passive Master-Master mysql Setup

texts:
I have a dozen virtual machines running application + mysql instances (version 5.1 up to 5.5). Due to the bad I/O of the virtualization I want to buy a physical server for mysql and move all mysqldata to the new server. I would like to install Percona Server or Cluster (5.6).

The new hardware: Xenon E5-2620 (6 Cores), 32GB RAM, 400GB SAS serial 12Gb/s

For higher availability I am thinking about a active/passive Master-Master Setup and install mysql also in one virtual machine. A load balancer would forward all requests (read and write) to the new physical host. If the server would go down, all requests would be send to the virtual machine.

All hosts are connected to one switch so network latency should be no problem.

Use Percona Cluster

pro: working multi-master setup; synchronous replication;
con: the fast new server would be waiting for the slow (vm) node at each insert/update?
con: i need a 3rd node which would be an arbitrator on my load balancer?

Use mysql regular replication with bin_log

pro: active master has the best performance?
pro: no arbitrator needed
active/passive master-master should not be problematic for replication?
con: slave could be out of sync? would the master resync automatically?

Any other ideas I am missing? Does anyone have experiences with these setups regarding availability/stability/performance?


texts: How to speed up queries in MySQL on a table with ~50 million rows

texts:
The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each column separately. The table has ~50 Million rows and grows rapidly, the plan is to keep it under 100 million rows by archiving the oldest data. Reports based on various datetime ranges and other combinations of the PK columns are run on it frequently, but these reports have begun to take too long. Around 5 minutes to return 10 records.

Column Names

DateTimeStamp--DateTime------PK,NN

SystemNumber--SmallInt(6)------PK,NN

DataNumber------TinyInt(4)-------PK, NN

Value---------------Decimal(15, 5)

Indexes

Primary-------------------BTREE-Unique---DateTimeStamp, DataNumber, SystemNumber, value

DateTimeIDX------------BTREE-Non Unique--DateTimeStamp

DataNumber-------------BTREE-Non Unique---DataNumber

SystemNumberIDX----BTREE--Non Unique---SystemNumber

Select  DateTimeStamp, Value
    From  SystemStats
    WHERE  DataType=16
      AND  DateTimeStamp >='" & StartDate & "'
      and  DateTimeStamp <= '" & EndDate & "'
      and  SystemNumber =" & mVars.SystemNumber & "
    Order by  DateTimeStamp;

My Question
How would you change the index on the table to speed up queries?


texts: All instances upgrade from Command Prompt

texts:
I want to create a cmd script to upgrade sql servers in the organization(mostly 2014) to 2016. I've read Microsoft documentation
Install SQL Server 2016 from the Command Prompt regarding this issue.
The problem is that there is no option written there to upgrade all server instances like there is in installing updates: Installing Updates from the Command Prompt using /allinstances.
Is there any way to auto upgrade all instances on the server using one command line?


texts: Get previous and next record with two order criteria

texts:
I have a SQL Query with two ORDER BY criterias. How I can get the correct previous and next position for every record?

My Solution works as long the actual and previous or next record match the same first ORDER BY criteria.

Sample data

http://sqlfiddle.com/#!9/409b3/3

Target: How I can get for id 100026 the previous id 100018 and the next id 100003?

Schema

CREATE TABLE `profiles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `rnd_order` INT(11) NOT NULL,
  `profile_status` INT(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `rnd_order` (`rnd_order`),
  KEY `profile_status` (`profile_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `profile_photos` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `member_id` INT(11) DEFAULT NULL,
  `photo` VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo_status` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `photo_status` (`photo_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Select query

SELECT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
`profiles`
LEFT JOIN profile_photos
ON (
  profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
)
WHERE (profile_photos.photo_status = 1 OR
profile_photos.photo_status is null)
GROUP BY profiles.id
ORDER BY photo_status DESC, profiles.rnd_order DESC;

My Solution (it don't work for the case where order by criteria of previous or next record is different)

Previous

SELECT profile.id,,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
          `profiles`
          JOIN profile_photos
            ON (
              profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
            )
AND (profile_photos.photo_status = 1 AND profiles.rnd_order < {actual_profile_id})
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order ASC
LIMIT 1

Next

SELECT profile.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
          `profiles`
          JOIN profile_photos
            ON (
              profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
            )
AND ((profile_photos.photo_status = 1 AND profiles.rnd_order > {actual_profile_id}) OR (profile_photos.photo_status IS NULL))
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order DESC
LIMIT 1


texts: Run a PowerShell script on a different server - from inside a SQL Server job

texts:
I have a procedure to backup SSAS Databases.

That is working as a charm.

Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days.

to achieve this I am using the following POWERSHELL script:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
#
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------

#-- connect to the remote server -- SQLBILON1
#
ENTER-PSSESSION sqlbilon1

#-- set the Path where the backup files (.abf) are located
#
$path = 'H:\SQLBackups'

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item

The problem with this script is that I use MYSQLSERVER1 server to backup the databases that are on SQLBILON1 server. The backup files are on the folder H:\SQLBackups of SQLBILON1.

The job is failing with the following error message:

 The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 13 in a PowerShell script.
The corresponding line is 'ENTER-PSSESSION sqlbilon1'.
Correct the script and reschedule the job. The error information returned by PowerShell is:
'Connecting to remote server failed with the following error message :
Access is denied. For more information, see the about_Remote_Troubleshooting Help topic.'

Question, how can I run a PowerShell script on a remote server?
This script runs from a sql server job.

Can I create a proxy and use it to connect to the remote server?


texts: Select query slow in the big table because of expired stats in Microsoft SQL Server

texts:
I have an application to select/search records from the database (application and database in separate servers).
In the database, one table only is fast growing table (more than 2 Millions per day).
There is no update will happen in this table all are insert only.
Created index for this table and everything is working fine.
I try to install another SQL Server instance in the database server.
After that, I realized that the select is very slow in the big table.
I uninstall the another instances of SQL Server.
Still the select is very slow in the particular table.
Search in the some forum, they mentioned that need to update stats (EXEC sp_updatestats).
After executed this query EXEC sp_updatestats the select query is very fast.
But after 10 days, the same issue (slowness in select query) occurred again.
I created daily jobs to update the stats, after 10 days, the issue observed again.
After update the stats manually, it is very fast.

The whole issue observed after I installed the second instance of SQL server in the same server.
Is my observation correct?
Even I uninstall the seconds instance, the slowness issue observed frequently.
Kindly give me your valuable suggestion.  Thanks in advance.


texts: ORA-01830 Error for 11.2.0 client but not for 10.2 client

texts:
I have this piece of code:

BEGIN
   DBMS_SCHEDULER.DROP_JOB (
   job_name => 'LOANSBUILD.LOANSNEWYORKCLOSE');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  '***.LOANSNEWYORKCLOSE',
    job_type           =>  'PLSQL_BLOCK',
    job_action         =>  'begin loans_schedule_job.loans_close(TRUNC(SYSDATE), ''N''); end;',
    start_date         =>  '15-NOV-08 12.00.00.000000000 AM AMERICA/NEW_YORK',
    repeat_interval    =>    'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0',
    auto_drop          =>   FALSE,
    job_class          =>  'DEFAULT_JOB_CLASS',
    enabled             =>   TRUE,
    comments           =>  'Test.'
);
END;
/
exit;

When the above is executed from an environment which has oracle sql client 10.2 installed , this goes fine but when the same is executed fron an environment that has 11.2 client installed, this fails as below:

BEGIN
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 2

The variable nls_date_format is set to 'DD-MON-RR' in the 10.2 environment and set to 'YYYY-MM-DD HH24:MI:SS' in the 11.2 env.

As this was getting compiled for 10.2 env, I updated nls_lang_date in the 11.2 env as well to make it 'DD-MON-RR' but even after that I get the same error. Is there anything else I should be setting.

Please note that I am sysadmin and as this code is getting compiled on of the server, my job is to ensure that it does on others as well. Which also means that I do not have permissions to update code.


texts: Use multiple columns for location or supertype/subtype?

texts:
Given an Equipment entity, it has a location, generically called Whereabouts.  This could be a reference to:

a User entity (the employee who will use and be responsible for the eqipment for the next week).
a Vendor entity (if the equpment is getting serviced).
a StorageLocation entity (if the equpment is put away).

The piece of equpment can only be in one place at a time, even if that place is not a physical location.

Is it better:

to have Equipment with three columns (UserId, VendorId, StorageLocationId) such that one of these columns must be not null while the other two must be null (enforced by a constraint)?
or to have Equipment with a WhereaboutsId that references some type supertype table that then links to User, Vendor, and StorageLocation as subtype tables, even though they share no other common attributes?
or something else?


texts: How-to implement an entity with an unknown maximum number of attributes?

texts:
I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The way I do this in the actual program is to use a dynamic array that grows for each inning played.

For those unfamiliar with the game of baseball, games are usually nine innings long unless the game is tied still at the end of the 9th inning. Baseball games therefore, have an undetermined length which means I cannot design the database to only have 9 columns for the runs scored each inning (well technically 18 (9-innings * 2-teams). One idea I have had is to serialize the array and encode it as Base64 before storing it in the database. However, I do not know if this a good technique to use and I was wondering if anyone has a better idea.

In case it matters, the database I am developing around is PostgreSQL.

Any suggestions are greatly appreciated! Thanks!


texts: Why does a subquery reduce the row estimate to 1?

texts:
Consider the following contrived but simple query:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END AS ID2
FROM X_HEAP;

I would expect the final row estimate for this query to be equal to the number of rows in the X_HEAP table. Whatever I'm doing in the subquery shouldn't matter for the row estimate because it cannot filter out any rows. However, on SQL Server 2016 I see the row estimate reduced to 1 because of the subquery:

Why does this happen? What can I do about it?

It's very easy to reproduce this issue with the right syntax. Here is one set of table definitions that will do it:

CREATE TABLE dbo.X_HEAP (ID INT NOT NULL)
CREATE TABLE dbo.X_OTHER_TABLE (ID INT NOT NULL);
CREATE TABLE dbo.X_OTHER_TABLE_2 (ID INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE STATISTICS X_HEAP__ID ON X_HEAP (ID) WITH FULLSCAN;

db fiddle link.


texts: Grouping Data Using Joined Tables with Certain Distinct Values

texts:
I am struggling to get my head around how to do this so please bare with me while I explain.

We have an order system which records orders, order lines, items, users etc.
What I am interested in in this instance are the orders and order lines.

At the moment, we produce reports with sales figures, showing sales, returns etc, but it is slow and inefficient. This is because we are querying individual tables using PHP/MS SQL Server, looping through arrays and then bringing the data together.

I have been tasked to try and make this more efficient, and so I have tried to produce one query with joined, aliased, sub-query tables. These work fine and join together, but I am getting the wrong values due to the way I am grouping tables.

Note, I am unable to change any schema and I am working with an existing system.

This explanation may seem quite long-winded, and I probably will lose you with what I am trying to ask, but please stay with me.

Anyway, here is a diagram of the schema, sample data, and the result of my query at present:

SELECT
`ol`.`storeID`,
SUM(DISTINCT `o`.`grossValue`) AS 'Total',
SUM(DISTINCT `o`.`paymentValue`) AS 'paymentTotal'
FROM `orders` AS `o`
LEFT JOIN `orderLines` AS `ol` ON `o`.`orderID` = `ol`.`orderID`
WHERE (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY `ol`.`storeID`

I have the produced the above quickly in MySQL as I am at home at the moment, and I imagine the solution would be similar for SQL server, but please correct me if I'm wrong, or perhaps provide solutions for both, which would be great!

Basically, the orders have many lines to them. The orders record the total value of the lines, and how much has been paid so far.
Each order line is a reference to a stock item.
Each line records which store it is recorded to, as different items could be sourced from different stores.
We want to produce reports that show how much each store makes in turnover.

As you can see in the result of my query store 06 is showing 4.99, however this should be 9.98.

What is happening is the netValue is being made distinct. I DON'T want this.
I DON'T want duplicates of order <=> orderlines, but I DO want duplicates of the netValue as these are individual orders.

Have I confused you yet?

Is there a way to still group by the storeID and sum the netValue, by perhaps grouping by the orderID, or orderLineID?

I have tried all types of combinations, and gotten many errors which I don't remember now, but mostly about aggregate columns and not being able to group etc.

All suggestions and help welcome :)

Create table code:

CREATE TABLE `orderLines` (
  `orderLineID` int(10) NOT NULL AUTO_INCREMENT,
  `orderID` int(10) NOT NULL,
  `itemCode` varchar(30) NOT NULL,
  `netValue` decimal(10,2) NOT NULL,
  `taxValue` decimal(10,2) NOT NULL,
  `storeID` varchar(2) NOT NULL,
  PRIMARY KEY (`orderLineID`)
);

CREATE TABLE `orders` (
  `orderID` int(10) NOT NULL AUTO_INCREMENT,
  `grossValue` decimal(10,2) NOT NULL,
  `paymentValue` decimal(10,2) NOT NULL,
  `typeID` int(10) NOT NULL,
  `orderDate` datetime NOT NULL,
  PRIMARY KEY (`orderID`)
);

Expected Result:

+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 02      | 10.00 |  8.00        |
| 06      | 9.98  |  9.98        |
| 07      | 3.00  |  3.00        |
+---------+-------+--------------+


texts: Find rows containing a key in a JSONB array of records

texts:
I'm trying to query for a key present in an array of objects. This structure:
column jdata
{"name": "Somedata",
 "array": [ {"name":"bla1", "attr": "somevalue"},
            {"name":"bla2", "otherdata": "somevalue2"},
            {"name":"bla3", "otherdata": "somevalue"}
           ],
"otherstuff": "stuff"
}

Now I do btree's on jdata->'name' or (jdata->'datetime')::cast and that works great.
I also do json_path_ops where jdata->'array' @> '[{"name":"bla3"}]' works a real charm.
My problem is the attr key can be in any of the objects in the array and I care about the record if the key is present, however the value can be nearly anything. Is there a way to query for this? Is there a way it can be indexed? I want to do jdata->'array' @> '[{"attr": ?}]' Or maybe the ? 'attr' can be used inside an array somehow?
Currently I am thinking a trigger that scans for the key and then moves it to a header with a true or false or whatever, and then a normal btree will work. Is there a better way? I need to edit around 500k records at the average site to add this values.
Please point me in a direction.


texts:
I have a database with a memory optimized table and table partitions. The log file is over 300GB in one .ldf file, and another 40GB in another. I have tried shrinking the db, files, log

DBCC SHRINKFILE ('dblog' , 0)
DBCC SHRINKFILE ('dblog' , 0, TRUNCATEONLY)

Nothing seems to shrink the log and it's very large. Each day it gets bigger and bigger. Nothing seems to shrink it. Any advice?

I have all the service packs already installed.
The database is in SIMPLE recovery mode.
I use the maintenance wizard to backup differential nightly and full on Sundays.
log_reuse_wait_desc for the database is XTP_CHECKPOINT.
It is definitely using the SIMPLE recovery model
A manual CHECKPOINT did not change anything.


texts: Postgres, levenshtein distance and nested queries

texts:
I have a DB Table with 3,000,000 rows or more like that:

|ID|T4|T16|T64|TL

where ID =INTEGER (PK)
and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4)

I want to compare one row of this table with a SELF JOIN with all other rows based on the levenshtein distance (fuzzystrmatch extension) between TL of the compared rows and get the best-k results that correspond to smaller levenstein distance.

SELECT n1.*,n2.*
FROM (SELECT * FROM dbtable WHERE ID =110) n1,
dbtable n2
ORDER BY levenshtein_less_equal(n1.TL,n2.TL,LENGTH(n1.TEXT)/4) LIMIT 100;

Of course this would be extremely slow because it will have to do 3,000,000 levenshtein calculations.

In order to accelerate queries, I have created the artificial "hashes" (fields T4,T16,T64). So, I want to check initially levenshtein distance between fields T4. If this distance between T4 is within some limit then I check levenshtein distance between T16 and so-on. This way in each step i would need to calculate fewer levenshtein distances that by the naive approach.

The nested query is like that:

SELECT n5.*,levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
FROM
(SELECT n4.*
FROM
(SELECT n3.*
FROM
(SELECT
n1.id AS n1_id,
n1.t4 AS n1_t4,
n1.t16 AS n1_t16,
n1.t64 AS n1_t64,
n1.tl AS n1_tl,

n2.id AS n2_id,
n2.t4 AS n2_t4,
n2.t16 AS n2_t16,
n2.t64 AS n2_t64,
n2.tl AS n2_tl
FROM (SELECT id,t4,t16,t64,tl FROM dbTable WHERE id=110) AS n1,dbTable AS n2
WHERE
n1.id<>n2.id
AND levenshtein_less_equal(n1.t4,n2.t4,LENGTH(n1.t4)) <= LENGTH(n1.t4)/2
) n3

WHERE levenshtein_less_equal(n3.n1_t16,n3.n2_t16,LENGTH(n3.n1_t16)/2) < LENGTH(n3.n1_t16)/2
) AS n4

WHERE levenshtein_less_equal(n4.n1_t64,n4.n2_t64,LENGTH(n4.n1_t64)/2) < LENGTH(n4.n1_t64)/2)
AS n5
ORDER BY levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
LIMIT 100;

The query is much faster than the original naive since it only needs to access 112851 rows for TL but:

The EXPLAIN PLAN:

Limit  (cost=872281.90..872282.15 rows=100 width=1168)
  ->  Sort  (cost=872281.90..872564.03 rows=112851 width=1168)
        Sort Key: (levenshtein_less_equal(dbTable.tl, n2.tl, (length(dbTable.tl) / 4)))
        ->  Nested Loop  (cost=0.00..867968.81 rows=112851 width=1168)
              Join Filter: ((dbTable.id <> n2.id) AND (levenshtein_less_equal(dbTable.t4, n2.t4, length(dbTable.t4)) <= (length(dbTable.t4) / 2)) AND (levenshtein_less_equal(dbTable.t16, n2.t16, (length(dbTable.t16) / 2)) < (length(dbTable.t16) / 2)) AND (levenshtein_less_equal(dbTable.t64, n2.t64, (length(dbTable.t64) / 2)) < (length(dbTable.t64) / 2)))
              ->  Index Scan using dbTable_pkey on dbTable  (cost=0.00..8.60 rows=1 width=584)
                    Index Cond: (id = 110)
              ->  Seq Scan on dbTable n2  (cost=0.00..699529.82 rows=3046982 width=584)

As you see the problem is that the query optimizer joins / collapses the levenshtein distance calculations between T4, T16, T64, where without collapsing it would be probably faster, because fewer levenshtein distances would need to be made for T64. I used  SET from_collapse_limit=1; to avoid collapsing at the same session but nothing changed. Is there a way to enforce this hierarchical functionality in a Postgres query; Keep in mind that none of the T4...TL are indexed because I do not think there is an index to accelerate levenshtein distances. Any suggestions to further improve performance?


texts: Error "column does not exist" in a SELECT with JOIN and GROUP BY query

texts:
I'm using PostgreSQL 9.1 with a Ruby on Rails application.

I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id (proj_sous_projet_id = 2).

This makes me use the max() aggregate function and apply the result to a JOIN function on the same table as PostgreSQL does not authorize to use the columns in the SELECT clause if they do not appears in the GROUP BY clause, ALTHOUGH using a max() mean obviously I'm interested to the row containing the max values!

This is my query :

SELECT h_v_charges.*,
       max(last_v.version) as lv
FROM hist_versions_charges h_v_charges
    JOIN hist_versions_charges last_v
      ON h_v_charges.version = lv
    AND h_v_charges.proj_charge_id = last_v.proj_charge_id
GROUP BY last_v.proj_sous_projet_id,
         last_v.proj_charge_id
HAVING last_v.proj_sous_projet_id = 2
ORDER BY h_v_charges.proj_charge_id ASC;

The error message I got :

ERROR:  column "lv" does not exist
LINE 1: ..._versions_charges last_v ON h_v_charges.version = lv AND h_v...
                                                             ^
********** Error **********

ERROR: column "lv" does not exist
SQL state: 42703
Character: 147

I also tried with "last_v.lv" but the error remains the same.

If anybody got an idea about what's wrong, she is more than welcome.

=== UPDATE ===

According to * a_horse_with_no_name * and Colin 't Hart answers, I finally ended up with the following query :

SELECT *
FROM (
    SELECT *, max(version) OVER (PARTITION BY proj_charge_id) AS lv
    FROM hist_versions_charges
    WHERE proj_sous_projet_id = 2) AS hv
WHERE hv.lv = hv.version
ORDER BY hv.proj_charge_id ASC;

It is slightly quicker with a single ORDER BY.

I tried as well the query with a WITH clause. Though "nicer", it creates additional processing charge. As I know I will not re-used in the future the sub-query twice or more in the same main query, I'm fine with using a simple sub-query.

Thanks anyway to *a_horse_with_no_name* and Colin 't Hart. I learned many things !


texts:
I am relatively new to SQL, so I apologize if I ask some basic questions.

Our database is setup on Azure, we have automatic indexes turned on. The query I have takes about 6 seconds and gets 2217 rows. As far as I have been able to figure out is that it is my Full Outer Joins that are slowing down my query. I am fine if I can just get it down to 2~3 seconds.

Here is my query:

SELECT j.JobId, j.OrderId, j.OrderType, j.ShopName, j.ShipDate, j.CompletionDate, j.InstallDate, j.DayPriority,
        j.ShipDateConfirmed, j.IsAddon, j.CashOnDelivery, ds.ShopLabel, ds.DesignSetId, dsp.DesignSetProcessId, dsp.DueDate AS DesignSetProcessDueDate,
        dsp.DatetimeStarted, dsp.DatetimeComplete, dsp.BatchNumber, p.Name AS ProcessName, p.ProcessId, p.ZoneId, X.CabinetCount
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
FULL OUTER JOIN Schedule.DesignSetProcessesWithDueDates dsp ON ds.DesignSetId = dsp.DesignSetId
FULL OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount FROM Products pr WHERE ds.DesignSetId = pr.DesignSetId AND
                                        (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
WHERE j.Status >= 60 AND j.Status <= 70
ORDER BY j.CompletionDate, j.DayPriority DESC, j.ShopName, j.OrderId, ds.ShopLabel, p.SortOrder, dsp.BatchNumber

This query gets a master view of the jobs and the processes within the job. All the information that I am getting is needed and all the rows are to.

Is there anything that I can use as a substitute for the Full Outer Joins?

If I can just get one of them to go faster I think the query would work better.

If you have any suggestion to how I can speed up the rest of my query I am willing to give it a shot.

EDIT

Here is the link to paste the plan: https://www.brentozar.com/pastetheplan/?id=SytXw1dum

UPDATE

Turns out one of the columns from a view was slowing everything down, so I got rid of dsp.DueDate AS DesignSetProcessDueDate and now it runs in less than a second. I was able to find out how that view was getting the information and put it right into my query.


texts: Should I normalize my string arrays, or just keep it a simple string?

texts:
I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .

For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.

My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.

However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see EBrowns answer for a clearer description).

But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see To normalize or not).

AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?

EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.

Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.


texts:
I have done some optimizations on the below mysql stored procedure, however it still runs pretty slow:

CREATE DEFINER=`xxx`@`%` PROCEDURE `testing`(IN _MONTH INT, IN _YEAR INT, IN _PRODUCTS TEXT)
BEGIN

set @_START = date(_YEAR * 10000 + _MONTH * 100 + 1);
set @_END = date_add(date(_YEAR * 10000 + _MONTH * 100 + 1), interval 1 month);

DROP TABLE IF EXISTS ReportThreeTotal;
DROP TABLE IF EXISTS ReportThreeAbandondCalls;
DROP TABLE IF EXISTS ReportThreeSpillGain;
DROP TABLE IF EXISTS ReportThreeStamp;
DROP TABLE IF EXISTS ReportThreeResults;

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeTotal AS (
    SELECT count(*) as 'count', DAYOFWEEK(`end`) as 'DOW'
    FROM callstats.cdrdata_archive
    where end >= @_START and end < @_END
    AND calling IN (
        SELECT Number FROM callstats.products WHERE products.id IN (_PRODUCTS) )
    GROUP BY DAYOFWEEK(`end`)
);

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeAbandondCalls AS (
    SELECT count(*) as 'count', DAYOFWEEK(`end`) as 'DOW'
    FROM callstats.cdrdata_archive
    WHERE calling IN (
        SELECT Number
        FROM callstats.products
        WHERE products.id IN (_PRODUCTS) )
    AND end >= @_START and end < @_END
    AND CAST(`duration` AS UNSIGNED) < 30
    GROUP BY DAYOFWEEK(`end`)
);

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeSpillGain AS (
SELECT (case when (SUM(count) is null) then 0 else SUM(count) end) as 'SPILLGAIN', DAYOFWEEK(`end`) as 'DOW'
FROM (SELECT count(*) as 'count', called, end
    FROM (
        SELECT called, calling, count(*) as 'count', end
        FROM cdrdata_archive
        JOIN products ON Number = calling
        WHERE end >= @_START AND end < @_END
        AND products.id IN (_PRODUCTS)
        AND called != 'Anonymous'
        GROUP BY called, calling, DAYOFWEEK(`end`)
        ORDER BY `end` ASC )
    AS tbl1
     GROUP BY called having count(*) > 1)
 AS tbl2
 GROUP BY DAYOFWEEK(`end`));

CREATE TEMPORARY TABLE ReportThreeStamp(stamp text, DOW INT);
    SET @Counter = 1;
    while @Counter <= 7 do
        INSERT INTO ReportThreeStamp VALUES(TIMESTAMP_FROM_DATE(_YEAR * 10000 + _MONTH * 100 + @Counter), @Counter);
        SET @Counter=@Counter+1;
    end while;

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeResults AS (
SELECT CONCAT('[',S.`stamp`,',', (
    case when (A.`count` = 0)
         then 0
         else (A.`count` / T.`count`) * 100 end),']') AS 'JSON_PERCENT',
    CONCAT('[',S.`stamp`,',',T.`count`,']') AS 'JSON_TOTAL',
    A.`DOW` as 'DOTW',
 A.`COUNT` as 'ATOTAL', T.`COUNT` as 'TOTAL',
SG.SPILLGAIN as 'GAINPER', SG.SPILLGAIN as 'SPILLPER'
FROM ReportThreeAbandondCalls A LEFT JOIN ReportThreeTotal T ON A.`DOW` = T.`DOW` LEFT JOIN ReportThreeSpillGain SG ON SG.`DOW` = A.`DOW`
LEFT JOIN ReportThreeStamp S ON A.`DOW` = S.`DOW`);

SELECT * FROM ReportThreeResults;

DROP TABLE IF EXISTS ReportThreeTotal;
DROP TABLE IF EXISTS ReportThreeAbandondCalls;
DROP TABLE IF EXISTS ReportThreeSpillGain;
DROP TABLE IF EXISTS ReportThreeStamp;
DROP TABLE IF EXISTS ReportThreeResults;

END

There is an index set for the end field which should increase the performance.
Any ideas how to optimize this stored procedure?


texts: Problem with unmasking a database user during a sproc

texts:
I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server.
The problem that I have is that I have a system database user that handles API calls to the application that these DBs are a part of. We'll call the user "apiuser". The user needs to be unmasked in order to function.
Not every database has this user for various reasons (API is not enabled on all databases by default), so when the sproc runs, it should attempt to unmask the user only if they exist on that DB. What I came up with to do that was this:
if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;

This line works fine when run on its own as a statement. However, when run as part of a stored procedure, it does not work. There is no error, the sproc runs just fine, it just does not unmask the user. My guess is for whatever reason the sproc is not handling the if exists part right, and so skips this component.
SET NOCOUNT ON;
GRANT UNMASK TO systemuser1;
GRANT UNMASK TO systemuser2;
if exists (select name from sys.database_principals where name='ApiUser')
  EXEC('GRANT UNMASK TO apiuser');
ALTER TABLE Table1 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE Table2 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE Table2 ALTER COLUMN Field2 ADD MASKED WITH (FUNCTION = 'default()');
END

Worth noting the other two unmasks (the other system users) work just fine. It's only the one using the IF EXISTS that does not.


texts: MYSQL Datediff between two dates based on year

texts: I have two dates date column like '2017-12-29' and '2018-01-05' the datediff between these dates is 8, but i want to know the datediff which fall on 2018 which should be 5. I'm working on mysql.


texts: Does anyone know how to check from T-SQL code in a stored procedure if a SQL user have read access to a WIN folder that is mapped to the SQL server? Thx Erik


texts:
I have encountered a problem where the WHERE clause is causing a problem.  The varcharColumn is of datatype VARCHAR and contains numbers as well as text.  The query looks to be averaging this column, but it has now hit a problem when a number has a decimal, e.g. 33.33.

SELECT AVG(CONVERT(INT,[varcharColumn]))
  FROM View
 WHERE [varcharColumn] > 0
   AND [varcharColumn] <> 'NA'
   AND CreatedDate > @StartDate AND CreatedDate <@EndDate

Now, I have tried to CAST and CONVERT the varcharColumn in the WHERE clause, but this returns no results.  Any ideas?


texts: Slow update commands only on newly added data?

texts:
Data gets read in every quarter into this table. Then after it is read in, certain fields get updated through an update command. The updates run really fast on the previous quarter's data, but when it gets to the new data it takes a long time.

We solved the problem by rebuilding the primary key after data is read in, and then running the update commands. This makes it just as fast as previous quarters but the rebuild can take a long time.

When I rebuild the indexes,  we started using a fill factor but that did not help the cause.

Is there anything else I should consider?


texts: execute as login for nonexistent login successful

texts:
On Sharepoint database server, we found 18456 state 11: infrastructure error for account Domain\machineA$ every 10 minutes and nt authority\anonymous every hour.

I checked syslogins but didn't find login and xp_logininfo didn't return results.

I Ran "Execute as login=domain\machineA$" and it was successful.... That is strange

I executed sys.logins_tokens and it returned 10 groups(like 'Public', \Everyone, S-1-19-1012-33).but no one was there as login in sql server.

Please here help me in finding --> How Was i able to Ran "execute as login=domain\machineA$" successfully while login doesn't exist.


texts: Oracle query that use to run in less than a second now takes 8 minutes

texts:
We have an Oracle query in a report that use to run in less than 1 second. Now takes 8 minutes.

I've tried rebuilding all the indexes it uses. No impact.
The query has not been changed in months.
I personally tested the query on both an older clone of the production db and a new clone. It ran fast on the one and slow on the other. So it's real.
The explain plan on the query is perfect. No reason why it shouldn't be lightning fast.

I'm running out of ideas. Any suggestions?


texts: CUBE and calculating the number of dimensions

texts:
if a data presented with 4-dimension in which each dimension is dependent to hierarchical 3-level aggregate like (country, city, street), then we can summarize it into 4096 ways!

we know for cube with n dimension in which none of dimension is
hierarchical we have 2^n summarizing ways, but in this case that each
dimension has hierarchical 3-level aggregate have 4^4=256 ways.

Why does the above statement mention that there are 4096 ways?
if I couldn't state my problem very well because of a flaw in my English usage, please see:

This is my formula, however my answer is very different from 4096!


texts: How to set the sequence to the last used id for all tables in Oracle?

texts:
I'm working on an open source elearning system called Moodle which usually uses MySql or Postgresql, but can also use MSSQL and Oracle.

A client wishes to use Oracle, which I have limited experience of. I've got a local install of Oracle 11g r2.

I used a script to transfer data from MySql into Oracle, including the ID to keep the references.

But now when creating a record I get the error message : ORA-00001: unique constraint (SCHEMANAME.TABLENAME_ID_PK) violated

I'm guessing the sequences weren't updated when I inserted the ID's?

Is there an easy way to update all the table sequences to the last ID number used + 1 in each table?

Unless it's something else?

UPDATE : I can use this to get the last number in the sequence

SELECT sequence_name, last_number
FROM dba_sequences
WHERE sequence_owner = 'SCHEMA_NAME'
AND sequence_name LIKE 'V_%'

The last_number column has values but it looks like they are lower than the last id used in the table.

UPDATE 2: Found some existing code in Moodle that resets the sequence, I had wrongly assumed the naming convention for the sequences was created by Oracle but its Moodle. Here is some Moodle/PHP code that will reset all the sequences:

$todb = moodle_database::get_driver_instance($dbtype, $dblibrary);
$todb->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
$totables = $todb->get_tables(false);
$tomgr = $todb->get_manager();

foreach ($totables as $table) {
    mtrace($table);
    try {
        $tomgr->reset_sequence($table);
    } catch (moodle_exception $e) {
        // There are a couple of temporary tables without indexes.
        mtrace($e->getMessage());
        continue;
    }
}

UPDATE 3 : This is basically what the reset sequence function does

$value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
$value++;

// Calculate a sequence name from the tablename.
$seqname = $this->getSequenceFromDB($xmldb_table);
if (!$seqname) {
    // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
    $seqname = $this->getNameForObject($table, 'id', 'seq');
}

$this->mdb->execute("DROP SEQUENCE $seqname");
$this->mdb->execute("CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size");


texts: Indexing varchar column to search for first letter


texts:
I'm converting a sql server database to postgresql 9.4. One of the database process that was working fine is causing trouble in PG.  postgres.exe memory consumption keep going up each time a new plpgsql function is created if the connection is never closed (with a connection pool).

Spec/config :
Windows OS
shared_buffers : 256MB
The database server is sharing software with other application, so the memory is important.

A simple way to reproduce the behavior is :

Create a function
drop that function
..
repeat

The real process is more complicated than that, but i isolated where the memory consumption appear.

I'm guessing it's because the cached execution plan is never dropped when the drop function occur and i did not find any way to deallocate that memory.

Is there any other solution than :

dont do that !!!!
Close the connection after some time


texts: List parent then nested child items in a single table

texts:
With the following SCHEMA

CREATE TABLE Software (
    ID INT,
    name VARCHAR(150),
    ParentID INT );

INSERT INTO Software VALUES (1,'Main Product',NULL);
INSERT INTO Software VALUES (2,'Next Main Product',NULL);
INSERT INTO Software VALUES (3,'Addon Product',1);
INSERT INTO Software VALUES (4,'Addon Product',1);
INSERT INTO Software VALUES (5,'Sub-Addon Product',3);

I need to create a query that will sort the data by picking the first Main Product, then finding any addon products where the parent ID matches the ID of the parent, then seeing if that addon-product has any sub-addon products.

The Query I've tried based on one of the recommendations is

WITH x AS (
    SELECT ID
    FROM Software
    WHERE ParentID IS NULL)
SELECT
    Software.ID,
    Software.name
FROM Software
INNER JOIN x ON x.ID=Software.ParentID OR x.ID = Software.ID

The output however misses the sub-addon product shown below:

/---------------------
| ID  | Name
| 1   | Main Product
| 3   |   Addon Product
| 4   |   Addon Product
| 2   | Main Product
\----------------------

What I need is this where the sub-addon is added

/---------------------
| ID  | Name
| 1   | Main Product
| 3   |   Addon Product
| 5   |     Sub-Addon Product
| 4   |   Addon Product
| 2   | Next Main Product
\----------------------


texts: Trouble with triggers and insert .. on duplicate key update

texts:
I have two tables with identical schemas.  one is a "main" table that is pruned periodically and kept small.  The other is a "reporting" table that is an archive of everything that ever was.  For simplicity lets say the table schemas look like

create table main_table(
    pk int unsigned not null primary key,
    value int unsigned
);

create table reporting_table(
    pk int unsigned not null primary key,
    value int unsigned
);

I have two triggers, one AFTER INSERT, and one AFTER UPDATE that look like

create trigger reporting_insert after insert on main_table
    for each row
        insert into reporting_table (id,value) VALUES
        (NEW.id, NEW.value);

create trigger trigger reporting_update after update on main_table
    for each row
        update reporting_table  set
            value=NEW.value
        where
            id=NEW.id;

The main table is populated with queries like

insert into main_table values (1,10) on duplicate key update value=value+1;

My problem is the reporting table is not catching this ODKU +1 for the value (and it clearly is updating in the main table).

From the trigger docs:

  A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

From reading that I would think it would follow the latter path executed any Before/After update triggers.  Doing a straight UPDATE query on main_table does result on the changes getting propagated to the reporting table.

What am I missing?


texts: Assigning a logical name to a sql instance

texts:
How can we assign a logical name to a sql instance please? We would need to as to property identify messages coming through from the server.

Update

Thanks much for your replies. Yes it is for the instance name. Is it possible to assign one once the installation is already done? We now have two instances in this box, the other is server_name\inst2008. And it is the first instance that we want to name properly - which is now "local". I've considered Alias but I want to know another option - if there is. This instance is a SQL 2000 by the way.

Sorry just to clarify this topic...
We have a server that for some reasons doesn't have a logical name - it doesn't have an entry in the sys.servers table for the local server (server_id = 0).

With some research and testing I was able to do this using sp_dropserver and sp_addserver commands.


texts: Oracle Express edition on Ubuntu - control file missing

texts:
I have installed the Oracle Express edition on Ubuntu as mentioned here.

I am facing issues when I try to create a sample table.

Started oracle

$ sudo service oracle-xe start
Starting Oracle Database 11g Express Edition instance.

Started sqlplus

$ sqlplus / as sysdba

Executed the CREATE command

SQL> CREATE TABLE SAMPLE (ID NUMBER);
CREATE TABLE SAMPLE (ID NUMBER)
*** ERROR at line 1: ORA-01109: database not open**

After a series of research on web, I tried to shutdown and restart oracle:

Shutdown command

SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.

Started the oracle instance

SQL> startup

ORACLE instance started.
Total System Global Area  688959488 bytes Fixed Size
2229688 bytes Variable Size             411044424 bytes Database
Buffers          272629760 bytes Redo Buffers                3055616
bytes ORA-00205: error in identifying control file, check alert log
for more info

I realized that the control file is missing at /u01/app/oracle/oradata/XE. XE is the DB name.

So I tried to create the control file as follows:

SQL> CREATE CONTROlFILE SET DATABASE XE RESETLOGS;

Control file created.

Tried to create the sample table again

SQL> CREATE TABLE SAMPLE(ID NUMBER);
CREATE TABLE SAMPLE(ID NUMBER)

ERROR at line 1: ORA-01109: database not open

So I tried to issue the following command

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS

ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent**
ORA-01110: data file 1: '/u01/app/oracle/product/11.2.0/xe/dbs/dbs1XE.dbf'

What should be done next? I am clueless as I am not a database guy.

Note:

Output of

$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-JAN-2013 09:15:37

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully


texts: Memory issue in Transactional Replication

texts:
I want to replicate a data of 500 GB through transactional replication once in a day, so my query is this, will it create a new file in the snapshot folder on daily basis of 200-300 GB or will update it only.

What can I do if i don't have too much memory for this file, can we not transfer our data without generating this file.


texts: Sharing keys and certificates between SQL Server 2014 server for encrypted backup and restoration

texts:
I have six SQL Server 2014 servers.  I am not using TDE, Transparent Data Encryption, on any server.

I would like to be able to take an encrypted backup of a database on ANY server and restore that backup to ANY OTHER server.

What actions do I need to take on each server with the service master key, master database key,  and certificate? I would like to use the minimum number of keys, certificates, and backup files for these keys and certificates.

What actions do I need to take if I add an additional SQL Server 2014 to the group?

Thank you all.  Server Fault and Stack Overflow have helped me many times in the past.


texts: Powershell script hangs with psql command, continues only after hitting enter

texts:
I have a simple powershell script that is supposed to drop an existing schema and restore it from a backup, the script is basically those lines:

echo "Dropping Schema MySchema"
& $PG_SQL -d "host=$DBSERVER port=$DBSERVERPORT dbname=$DATABASE user=$DBSERVERUSER sslmode=require" -c "DROP SCHEMA MySchema CASCADE;"

echo "Restoring MySchema Schema from backup"
& $PG_RESTORE -h $DBSERVER -p $DBSERVERPORT -U $DBSERVERUSER -d $DATABASE -j 6 --no-owner --no-tablespaces --verbose $BACKUP_FILE

When the Schema is empty, it runs through the drop schema command without an issue. When the schema is not empty, it hangs forever after writing "Dropping Schema MySchema". While it hangs there, hitting Enter/Return seems to start it again and the script continues. Problem is that this script is supposed to be run by the CI so it ends up running in a 60min timeout and fails.

Any idea what could be causing such behavior or a different approach to try?


texts: PostgreSQL query suddenly very slow after new column addition

texts:
I have a query that's performing terribly after adding a new column. Here's the slow version:

SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."api_user_for" IS NULL
WHERE "activities"."department_id" = 123456789
AND "users"."api_user_for" IS NULL

The new column is the api_user_for column. It's a nullable string type. The SQL itself is generated from Ruby on Rails. We've added a "default scope", and that's why the api_user_for seems to appear twice in the query (on the join and in the where clause).

If I remove either one of those api_user_for checks, the query returns to its former speed. By including them both, however, the query moves from taking less than 100ms to taking close to 10 seconds.

I've compared the query plans using PEV, and here's the fast and slow queries compared:

The "fast query" in this case is the same query with one of the (unnecessarily duplicated) api_user_for checks removed. For example, both of these queries are fast:

SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id"
WHERE "activities"."department_id" = 123456789
AND "users"."api_user_for" IS NULL

and

SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."api_user_for" IS NULL
WHERE "activities"."department_id" = 123456789

Obviously there's a huge disparity in the number of loops performed, but why are these loops taking place? The scan seems otherwise very similar.

I would love some insight into what could be going on as this fairly innocuous query is now bringing performance to its knees!

Postgres Version: 9.6.8


texts: Execute a sql query inside IF() function

texts:
Wherever I find an example of IF () function I only see something like below.

SELECT IF(500<1000, "YES", "NO");
+---------------------------+
| IF(500<1000, "YES", "NO") |
+---------------------------+
| YES                       |
+---------------------------+

But can I do something like below i.e executing a query?

IF(500<1000, CREATE DATABASE test1; , CREATE DATABASE test2;);

One way is to using prepared statements. But I want to directly execute the query inside it.


texts: What can be the purpose of this trigger?

texts:
I stumble on this trigger when trying to analyze some DB:

Create trigger [tbl_Details_Trigger] on [tbl_Details]
    Instead of Insert
    As
      Insert into [tbl_Details]
      Select * from inserted

To me it looks like this trigger doesn't do anything special, and if it will be dropped the outcome will be the same. Am I right? Am I missing something?


texts: Using JOIN to find duplicates in another table taking too long

texts:
Have a MySQL 8 database with the following tables.
people
id | mobile | name

registrations
id | people_id | other_stuff

I am trying to find the mobile number and the number of times it's duplicated by registrations. Some records in the people table do not have foreign records in the registrations table, so need to exclude them from our calculation.
If I do the following, the query runs relatively fast (500ms), but it excludes two or more registrations that have the same people_id
select mobile, count(mobile) as count from people where exists
(select people_id from registrations where people_id = people.id) group by mobile having count > 1;

The following returns what I need, it returns the above but also takes into account registrations with the same people_id, but the problem is, this takes about 6 seconds for around ~250,000 rows in patients and registrations.
select mobile, count(mobile) as count from people inner join registrations on
registrations.people_id = people.id group by mobile having count > 1;

I have indexes on the mobile and people_id columns.
Wondering if there's a faster way that returns exactly what I need.


texts:
I'm working on a development in WordPress/WooCommerce which has a very large number of products, currently 180,000. The problem here is, of MySQL which slows down dramatically as the product number exceeds 60,000. This is mainly due to MySQL apparently using the Ubuntu filing system to store data. It would be better if a different database could be used but in the meantime I was considering setting up a RAMdisk and using that for the MySQL temp data. Now there are any number of posts on how to do it, all of which are wrong for Ubuntu 20.04. Running 'df' I can see a mount point at /dev/shm which appears to be usable and wondered if it is.  My knowledge of Linux isn't great enough to be sure. Tutorials also suggest editing /etc/rc.local which no longer exists in Ubunto 20 along with /etc/apparmor.d/local/usr.sbin.mysqld which also doesn't exist. Also /etc/mysql/my.cnf  has no reference to tmpdir.  All this would appear to indicate that what I'd like to do (other than use a grown up database like PostgreSQL or SQL-Server) can't be done or perhaps doesn't need to be done. Of course running the whole MySQL database in RAM would be fast but unfeasible, I know.
Any suggestions?


texts: SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds

texts:
Ultimately, what I am trying to do is create an SSIS package in the QA environment for another team which truncates the destination tables and inserts from the source tables.  However, on one table, ITEMMAST, I am receiving an error due to a unique constraint violation when trying to copy the data using a data flow task.

What is odd is that a SELECT...INSERT using a linked server succeeds!  However, I can't use linked servers because this other team wants to be able to specify the environment for the source so I have to parameterize the connection strings.

Here is the definition of the unique constraint:

ALTER TABLE [dbo].[ITEMMAST]
  ADD CONSTRAINT [ITESET1]
  PRIMARY KEY CLUSTERED
  (
      [ITEM_GROUP] ASC,
      [ITEM] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
  ON [PRIMARY];

I've tried dropping the primary key constraint, copying the data, and then recreating the primary key constraint but that also produces an error.

  Msg 1505, Level 16, State 1, Line 46
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ITEMMAST' and the index name 'ITESET1'. The duplicate key value is (SALO ,                                 ).
  Msg 1750, Level 16, State 0, Line 46
  Could not create constraint. See previous errors.
  The statement has been terminated.

A developer gave me the following query to run which helped me identify the culprits, one row with 32 spaces in the ITEM column and another row with the ASCII character of 0, a null character, in the ITEM column.

SELECT TOP 10
patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM) as [Position],
 substring(ITEM,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM),1) as [InvalidCharacter],
 ascii(substring(ITEM,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM),1)) as [ASCIICode]
,*
 FROM Lawson.dbo.ITEMMAST
WHERE ITEM_GROUP = 'SALO'

Here is more information about the QA environment.  These settings match our PROD environment so I'm unable to modify QA to make this work.  I've thought about altering the unique constraint definition and changing the destination database collation.

Source Database Collation: Latin1_General_BIN
Collation for the ITEM Column in the Source Table: Latin1_General_BIN

Destination Database Collation: SQL_Latin1_General_CP1_CI_AS
Collation for the ITEM Column in the Destination Table: Latin1_General_BIN

I checked that the codepage for the Latin1_General_BIN collation is 1252 which matches the codepage for the SQL_Latin1_General_CP1_CI_AS collation.

Does anyone have any suggestions for other things that I can try?  I'm new to SSIS so there might be a setting that I did not set correctly.

[UPDATE #1]

Screen shot showing the result of the following query from the source table.

SELECT TOP 2
 CONVERT(VARBINARY(500), [ITEM])
,ITEM COLLATE SQL_Latin1_General_CP1_CI_AS AS [ITEM_Collated]
,*
FROM PROD.dbo.ITEMMAST
WHERE ITEM_GROUP = 'SALO'


texts: SQL Server: What securables to enable a server role for read-write access to all DBs

texts:
In SQL Server, you can add a server role and give that role membership to other roles such as:

bulkadmin
dbcreator
diskadmin
processadmin
securityadmin
serveradmin
setupadmin
superadmin

However none of these roles seem to allow for only interacting with databases (SELECT, INSERT, UPDATE).

What securables should I enable to specifically allow for this kind of access? I would not want this server role to be able to create and drop databases.


texts: Insert mysql query results into database column with corrensponding ids

texts:
I'm trying to store some data from a temporary query into a table, but it doesn't work.
Suppose we have the following data
Table_A

Submission_id
Salesman
Customer name
Hobby
Carline
Fuel
Marketing_data

1
salesman1
customer1
hobby1
car1,car2
Fuel1
hobby1,car1,car2,Fuel1

2
salesman2
customer2
hobby1,hobby2
car1
Fuel2
hobby1,hobby2,car1,Fuel2

And the radio Button/checkbox value table:
Table_B

option_value
option_name

hobby1
Football

hobby2
Golf

car1
SUV

car2
Cabrio

Fuel1
Gas

Fuel2
Hybrid

I've written the following code trying to match the options stored in the previous table against the column "Marketing_data" to obtain a new column:
SELECT GROUP_CONCAT(DISTINCT c0.option_name)Marketing_data
FROM Table_A d
JOIN Table_B c0 ON FIND_IN_SET(c0.option_value, d.Marketing_data)
GROUP BY d.Marketing_data;

And the resulting column is composed as follow:

Marketing_data

Football, SUV, Cabrio, Gas

Football, Golf, SUV, Hybrid

Now, what I'm trying to do is to insert the last column into the first table (Table_A). I tried with INSERT INTO command and I've also tried to generate another table containing the Submission_id field, but nothing worked. Have you got any suggestions? Many Thanks to everyone who will try to help me.


texts: how to change the file directory of your log and data files and what are the effects

texts: I have inherited a SQL server 2012 instance where the file directories of the log and data files are on the same folder on the same drive(all are in the C:\ drive). My task that I will be doing is separating the data files from the OS disk. The instance already has few databases on it running.Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this.


texts:
Is there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ?

I google it for several minutes and I find nothing about it.


texts: How Can I Make My Statement Accept Multiple Results?

texts:
I get the error "Subquery returns more than 1 row" when I run the following statement.

SELECT `labels`.*,
       IF((SELECT `label_id`
           FROM   `customer_print_sessions`
           WHERE  `customer_id` = (SELECT `customer_id`
                                   FROM   `customer_accounts_logins`
                                   WHERE  `id` = '1069')
                  AND `customer_print_sessions`.`label_id` = `labels`.`id`) IS
          NOT NULL
       , 'Y', 'N') `in_queue`
FROM   `labels`
WHERE  `id` IN (SELECT `label_id`
                FROM   `customer_groups_label_relation`
                WHERE  `group_id` IN (SELECT `group_id`
                                      FROM   `customer_accounts_group_relation`
                                      WHERE  `customer_id` =
                                             (SELECT
                                             `customer_id`
                                                              FROM
                                             `customer_accounts_logins`
                                                              WHERE  `id` =
                                             '1069'
                                             )))
ORDER  BY `labels`.`plu` ASC;

I ran each SELECT statement separately to determine which one returns multiple results that this statement as a whole was not expecting. I've determined that the following part of the above statement is the part that is returning multiple results which aren't expected.

SELECT `label_id`
FROM   `customer_print_sessions`
WHERE  `customer_id` = (SELECT `customer_id`
                        FROM   `customer_accounts_logins`
                        WHERE  `id` = '1069')
       AND `customer_print_sessions`.`label_id` = `labels`.`id`

How can I write this so that the IF function and the whole statement will work with the multiple results that are being returned?

UPDATE: Since the above statement returns multiple results of the same value, is there a way I can write it to just return one?


texts:
I have the following SQL statement
select id
from table
where  (   p1_user_id='username'
        or p2_user_id='username'
        or p3_user_id='username'
        or p4_user_id='username')
  and turn=0
  and timestamp > 20200101

There are indexes on all the fields mentioned in the where clause (for reasons other than this query).
When this query runs, it doesn't use the indexes. Using explain, it shows the possible indexes, but then doesn't select one.
For testing purposes I added an additional index which is a combination of p1_user_id, p2_user_id, p3_user_id, p4_user_id.
testing this provided the same result, no index actually used.
How do I either index this in a way that will be more efficient , as reading the whole table which is what it is doing now, is obviously not efficient.
Alternatively, is there a way to rewrite the query that would be more efficient ?
edit: Would using 4 union all statements be more efficient way to do this?


texts: How to connect to an Amazon PostgreSQL database using SSL

texts:
I'm trying to log into an Amazon PostgreSQL DBS using SSL.  I have all credentials but my psql version doesn't seem to support SSL.  I've tried searching Postgres docs but all I see is how it has native support.  Here's my command and output.  I'm running the psql command on a CentOS 6.5 box.

psql --host=XXXX.rds.XXXX.com  --sslmode=require --sslrootcert=rds-ssl-ca-cert.pem \
--port=XXXX --username=XXXXX --password --dbname=XXXX

Response:

psql: unrecognized option '--sslmode=require'

So my question is, how do I install a psql client that supports SSL.  I don't need the full blown DB support, just the connector, psql.

Thanks

I should add that I'm trying to follow these instructions.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL


texts: Cannot reinstall SQL Server 2008 R2 after downgrade from Windows 10 to Windows 7

texts:
Scenario: using SQL Server 2008 R2 Express under Windows 7 without problems.

Then I upgraded to Windows 10 without problems.

Then I downgraded back to Windows 7 (due to problems with other applications). SQL Server is gone.

When I run x64_ENU + SQLEXPR_x64_ENU to reinstall I get the following error message during "Setup Support Files" (with ExecuteStandardTimingsWorkflow apparently the current task) and cannot go further:

  Microsoft SQL Server 2008 R2 Setup The following error has occurred: An
  error occurred during the installation of assembly
  'policy.8.0.Microsoft.VC80.CRT,version="8.0.50727.42",type="win32-policy",
  processorArchitecture="amd64",publicKeyToken="1fc8b3b9a1e18e3b"'.

Running SFC  /SCANNOW did not reveal any problems.

Program_Install_And_Uninstall also did not. Microsoft Visual C++ 2005 Redistributable Package (x64) was not installed, so I installed it.

Now SQL Server 2008 R2 Setup ran a lot further, but then generated a similar (but not identical) error at

  Install_SqlSupport_KatmaiRTM_Cpu64_Action: CreateShortcuts

The following components were installed:

SQL Server 2008 R2 Native Client
SQL Server 2008 R2 Policies
SQL Server 2008 R2 setup (English)
SQL Server Compact 3.5 SP2 ENU
SQL Server Compact 3.5 SP2 Query Tools ENU

Deleting all these, and also all components of Visual Studio, and trying again did not change this.


texts: Select column names whose entries are not null

texts:
I would like to have a list of those columns of a table that have at least one not-NULL data entries in them.

In other words, I would like to get the column names for which the following returns at least one entry:

SELECT DISTINCT column_name FROM table WHERE column_name IS NOT NULL

I tried the following:

SELECT column_name
FROM information_schema.columns
WHERE table_name = "table_name"
AND EXISTS (
    SELECT DISTINCT column_name FROM table_name WHERE column_name IS NOT NULL
)

But this also returns the column names where all the entries are NULL.

So how do I get only those columns with non-NULL entries?