texts: Join against two tables to get single combined result set

texts:
tl;dr I made a SQLFiddle here http://sqlfiddle.com/#!9/031e71/1 with a minimal, complete, reproducible test case (though very specific to the problem domain of orders and products).

I have a Zen Cart ecommerce system on MariaDB and have a problem with the way order information is extracted into downstream order management systems.  I have also looked into scripting in the order management system to modify the data, but it seems their API doesn't allow modification of orders at that stage (argh!) so I'm trying it via the SQL used as the orders are read from MariaDB into the order management system.

The scenario is where a coupon has been applied to an order, resulting in a discount, and I want that coupon's existence to appear in a resultset alongside the products in the order.  So if you purchase Nuts for $100 and apply a $10 discount coupon, I want two rows in the resultset, one for Nuts at $80 and one for the coupon at $10, so the total equals $90 (the $100 cost less the $10 discount).  The maths isn't the problem, the problem is that the existence of the products, and the existence of the coupon, are in two very different tables.

Zen Cart has three main tables:

orders - one record per Order
orders_totals - 4 or 5 records per Order, with things like Total, Sub-Total, VAT Content, Discount Coupon (this is the crux of my problem)
orders_products - one record per product in each order.

I want to join orders against both orders_totals and orders_products to get one row per product, and an extra row if a row exists in orders_totals for that order that is a coupon.

Is there an elegant way to solve this?  I'm not sure if a cunning JOIN is enough, or if a View or PARTITION may be required.

** Edit ** I made a new fiddle which takes the coupon amount, divides it by the number of products, and subtracts that portion from each product amount, combined with the UNION suggestion.  See http://sqlfiddle.com/#!9/27ad50/3 .. but this seems quite ugly with all the subselects going on in the joins in the first half of the UNION, I hesitate to say this is a good solution!


texts:
I'd like to find all rows if is shows even once a particular value  with postgresql

For example :

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
  3  M.        BOUVIER   Alain       V
  4  M.        DUBOIS    Paul        D
  5  M.        DREYFUS   Jean        C
  6  M.        FAURE     Alain       C
 11  M.        DUPONT    Alain       B
 12  M.        DUVAL     Arsène      B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

And I want to have all rows where CLI_ENSEIGNE = 'A' but not only I want also the rows where CLI_NOM had a CLI_ENSEIGNE = 'A' even only one time:

We see here the Dupond had an A the a B

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
 11  M.        DUPONT    Alain       B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

I tried

SELECT
  *
FROM
  table
WHERE
CLI_ENSEIGNE = 'A'
IN (CLI_NOM)

But it's saying that CLI_NOM doesn't exists


texts: What are recommended (working) permissions for a user to view all SQL Agent jobs

texts:
A new user (associated with an existing login) is created in msdb and assigned membership:

use [msdb];
create user [TheUser] for login [AnExistingLogin];
alter role [SQLAgentUserRole] add member [TheUser];
alter role [SQLAgentReaderRole] add member [TheUser];

-- Impersonate the user via an SP in another DB with execute as...
select top 10 * from msdb.dbo.sysjobs; -- fail

The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

Try the obvious:

grant select on msdb.dbo.sysjobs to [TheUser]

select top 10 * from msdb.dbo.sysjobs; -- success

Isn't the  proper way to work within the SQLAgent* roles?

SO question with competing answers

Blog on SQLAgentReaderRole


texts: How to get N set of data base on one column

texts:
example, my data

XXX yyy  order_time
x1  y1  2019/11/11 00:11:22
x2  y2  2019/11/11_00:11:22
x3  y3  2019/11/11_00:22:33
x4  y4  2019/11/11_00:33:44
x5  y5  2019/11/11_00:33:44
x6  y6  2019/11/11_00:44:55

To get all data, I can use:

select xxx,yyy,order_time from my_table where condition=bala;

To get 3 set of the order_time

select distinct(order_time) from my_table order by order_time limit 3;

But how to combine them together?
I want to get all data that the order time within the 3 set of the order_time.

expect would be:

XXX yyy  order_time
x1  y1  2019/11/11 00:11:22
x2  y2  2019/11/11_00:11:22
x3  y3  2019/11/11_00:22:33
x4  y4  2019/11/11_00:33:44
x5  y5  2019/11/11_00:33:44

Thanks!


texts: How to create a TSVECTOR column in a materialized view?

texts:
I am trying to implement full text search in a materialized view (MV for brevity), and I read that it's not possible to alter the MV itself, which rules out these solutions:

Adding a new column with a specific data type after the MV has been created.
Creating the MV with an empty column and change the column data type.

I tried both and well yes, as expected it doesn't work.

So my idea was to add a cast on the empty column when creating the MV:

CREATE MATERIALIZED VIEW mv AS
  SELECT id, '' AS textsearchable_index_col::tsvector
  FROM tbl
  ...
WITH NO DATA

But it returns the following error:

  ERROR:  syntax error at or near "::"

How can I achieve this?

ps: I am willing to have a column instead of an index because I'd like to use weighing.

Additional information

I am able to have the column created like so:

 CREATE MATERIALIZED VIEW mv AS
   SELECT id, CAST ('' AS tsvector) AS textsearchable_index_col
   FROM tbl
   ...
 WITH NO DATA

However I can't update the column using:

UPDATE mv SET textsearchable_index_col = to_tsvector('english', coalesce(title,''))

I get  an error:

  ERROR:  cannot change materialized view "mv"


texts:
While i restore my database i got the below error. But i have more space in my drive.

modify file encountered error 112 (Ther is not enough space on the disk)while attempting to expand the physical file C:\xys.mdf


texts: Powershell script executing within SQL Server Agent

texts:
I am trying to use Powershell within a SQL Server Agent job to download a zip file. The script uses PuTTY (PSCP.exe) to download a zip file from a SFTP site.

The issue I am having is that when the job runs it connects to the SFTP site and PuTTY sends a prompt back about storing the server's host key in the registry. I don't want to do this so I am trying to pipe the echo n command to PuTTY. This doesn't seem to be working though.

$SrcPath = "/somedirectory/somewhere/files/"
$DstPath = "D:\Download\"
$currentDate = (Get-Date).ToString('yyyyMMdd')
$FileName = "$currentDate.zip"
$ArchivePath = "D:\Archive\"

$File = "$SrcPath$FileName"

Set-Location $DstPath

echo n | C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:$File $DstPath
# Check the file is there
If (Test-Path "$DstPath$FileName")
{
  # Unzip the contents
  C:\"Program Files"\7-Zip\7z.exe e "$DstPath$FileName"
  #Move the zip file to the archive directory
  Move-Item $DstPath$FileName $ArchivePath -force
}

If I open up a command window and do this manually it works fine.

e.g. Typing this in the command window

C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:/somedirectory/somewhere/files/20121025.zip D:\Download\

and then pressing n when the prompt appears, downloads the files correctly.

When the job runs via SQL Agent, it errors initially complaining about the host key and then states that the file does not exist.

Can anyone point me in the right direction?


texts: Select SUM from another Select SUM result in mySQL

texts:
I have one Select SUM command that works:

# SIM Orders Pending by Customers for 3101708 IMSI SIMs with 5YY MSISDNs
SELECT accountname AS 'Account Name',
       IF ((SELECT * FROM 5YYAccounts WHERE 5YYAccounts.accountid = T3101708.accountid) IS NULL , 'N','Y') AS '5YY MSISDN',
       LPAD(CONCAT(FORMAT(SUM(quantity), 0)),15,' ') AS 'Quantity'
FROM  T3101708
WHERE outputfilereceived IS NULL AND (SELECT * FROM 5YYAccounts WHERE 5YYAccounts.accountid = T3101708.accountid) IS NOT NULL
GROUP BY accountname, accountid
ORDER BY SUM(quantity) DESC;

After running this, I get following result:

+---------------------------------+------------+-----------------+
| Account Name                    | 5YY MSISDN | Quantity        |
+---------------------------------+------------+-----------------+
| FCA - SXM - AT&T                | Y          |          48,000 |
| Numerex - AT&T                  | Y          |          34,000 |
| Mytrex Inc. - AT&T              | Y          |          24,000 |
| Honda US - AT&T                 | Y          |          18,000 |
+---------------------------------+------------+-----------------+
3 rows in set (0.03 sec)

Based on the result above, I want to SUM the last column by having following Select SUM command:

SELECT SUM(
    SELECT SUM(quantity)
    FROM  T3101708
    WHERE outputfilereceived IS NULL AND (SELECT * FROM 5YYAccounts WHERE 5YYAccounts.accountid = T3101708.accountid) IS NOT NULL
    GROUP BY accountname, accountid
);

For this time, I am getting an error in mySQL:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near 'SELECT SUM(quantity)
      FROM  T3101708
      WHERE outputfilereceived IS NULL AND' at line 2

What did I code wrong here?

Thank you.
Bobby


texts: Merge two rows from a table in a single row

texts:
I think it's pretty simple but I can't get around this one…
I have a table contactsFields like this :

| id | contactId | fieldType | order | value |

I want to get the email with order 0 and company with order 0 as well.

Pretty straightforward to get either one :

SELECT "contactId", "value"
FROM "contactFields"
WHERE "order" = 0 AND "fieldType" = '<email OR company>';

This query can return no row, or a single one because we enforce that there are never 2 fields with same fieldType, contactId and order

But I would like to get both of them at the same time (i.e. merge 2 results on a single row), like this:

| contactId | emailWithOrder0 | companyWithOrder0 |

Can I do this with a join?


texts: How can I force index on view with UNION ALL?

texts:
I use Oracle 11.2.0.4.

I have a view like this:

CREATE or REPLACE FORCE VIEW PHE.VIEW
(
   col1,
   col2,
   col3,
   col4
)
AS
SELECT
   'TABLE1' AS TABLE,
   col1,
   col2,
   col3,
   col4
FROM PHE.TABLE1
UNION ALL
SELECT
   'TABLE2' AS TABLE,
   col1,
   col2,
   col3,
   col4
FROM PHE.TABLE2
UNION ALL
SELECT
   'TABLE3' AS TABLE,
   col1,
   col2,
   col3,
   col4
FROM PHE.TABLE3;

TABLE1, TABLE2 and TABLE3 have same columns, different data. I have index on col1 and col2. col4 is of type date and I have hourly partition as col4 on all tables.

I tried SQL query like this:

select A.*,B.*
FROM PHE.VIEW A, PHE.VIEW B,
WHERE A.col1='XXX'
  AND A.col4 > SYSDATE-10
  AND A.col3 = B.col2
  AND A.col2 = B.col3
  AND B.col4 BETWEEN A.col4 - 5 / 1440
                 AND A.col4 + 5 / 1440;

First, A going to view with index. But I think that the JOIN uses a TABLE ACCESS FULL on all tables.

How can I optimize that?


texts: View SQL Server Agent jobs -> MSDB roles

texts:
When attempting to view the jobs' properties in SSMS, the users would get the error execute was denied on the object sp_help_targetserver. This is a sys SP in MSDB.

The users have

SQLAgentReaderRole
SQLAgentUserRole

which should be sufficient to view the jobs themselves. These roles are assigned to their AD group. All the users are Win domain users that belong to the AD groups that have those permissions.

Does anyone know why management studio tries to execute sp_help_targetserver just to view jobs? Executing sp_help_job works.


texts:
First to mention that I came to the need of asking help after furious research I reattempts for about 3 weeks.  I checked previous threads in Oracle community but nothing works for me. I am trying to restore Oracle 11.1.0.7. multi HDD cluster production on single hdd locally on amazon ec2.

Restore is done from full back-up copy I downloaded from ftp location (I have no access to production,export and etc.) just have access to the files, i.e. I  am not able to change anyhow export procedure.

So here are my steps in brief (I guess that problem might be in some of them):

rman target /
set DBID ...
startup nomount
RESTORE CONTROLFILE FROM 'file destination'
shutdow;startup mount;
Catalog the backup files to rman: catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';for all AL
and BK files
sqlplus / as sysdba
Rename all redo files to match my local directory structure
rman:crosscheck backup;
    Run{
Rename all data files to match my local directory structure

SET UNTIL SEQUENCE 50511;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

sqlplus sys/sys as sysdba
shutdown immediate;  startup mount;
alter database noarchivelog;
Drop the no longer used log files:-drop these in state UNUSED left Active and Inactive
Drop all temp files(they point to other drive letter;
ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace
  ADD TEMPFILE 'c:\oracle............dbf'
  SIZE 1G REUSE
  AUTOEXTEND ON
  MAXSIZE UNLIMITED;

My problem is that on my second query (get) to db through my app I receive:

ORA-01578: ORACLE data block corrupted (file # 64, block # 2559928)
ORA-01110: data file 64: 'C:\ORACLE\ORADATA\mydb\mydbLOBDATA2_1.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "mydbUSER.POBJECTSTATE", line 78
ORA-06512: at line 1

What I was able to find with validation and it is LOBSEGMENT -   Lob_ObjectState and it logically corrupted.

I try DBMS_REPAIR and with rman advise failure (which suggest to restore my copy from back-up- nothing useful).

Since back-up are refreshed every week I try with new ones from the next week, again the same problem.

Is there a possibility that the problem starts from export (how can I check  this-I do not have access but i may ask db admin to check anything)?

Any other ideas?


texts:
We currently have SQL Server 2000 in use. One is LIVE and one is BACKUP. We have an audit database which grows massively. What would be ideal is that at the end of each month, the data gets copied from LIVE to BACKUP and the LIVE data to be flushed (hope this makes sense so far)

I was told you can do this in a number ways but one way I found interesting was to split each month into a different data file and then at the end of the month to move the data file over and create a new one for the next month - any advice on this, or any other solution would be appreciated

Thanks,

JJ


texts: Is not in (a,b,c) faster than where != a, b, c

texts:
When writing a query which is faster?

SELECT * FROM table where column not in (a,b,c);

or:

SELECT * FROM table where column != a AND column != b AND column != c ;


texts: Login cannot view definition even when granted the permission

texts:
I have an AD login which is in the below AD groups.

DOMAIN\GlobalGroup1
DOMAIN\GlobalGroup2
DOMAIN\GlobalGroup3
DOMAIN\UniversalGroup1

DOMAIN\UniversalGroup1 has been granted VIEW ANY DEFINITION on this SQL Server instance (10.50.6000.34). The user is not able to view any of the databases' definitions.

Other members of the DOMAIN\UniversalGroup1 appear to be able to view definitions.

EXEC xp_logininfo 'DOMAIN\user','all'

Only displays the 3 GlobalGroups. This is a documented limitation of the xp_logininfo procedure.

EXEC xp_logininfo 'DOMAIN\UniversalGroup1','members'

Confirms the user's membership to the group.

How can I troubleshoot this problem? Is the issue in any way related to the difference between the global and universal group?


texts:
Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to?

The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am I missing something or is that how it actually works?

Is there some alternative command that enables that?


texts: How to debug a growing Redo Queue Size in the Availability Group Dashboard?

texts:
The Redo Queue Size has been growing pretty big over the last couple of hours on my secondary replica's server (SQL16). There's nothing long running or heavy running that I've found over the last hour or so, so I'm not sure where this is coming from.

My index maintenance jobs ran last night which are pretty beefy and normally cause this "lag" in sync between the replicas but that usually clears up by the morning.

I log the Redo Queue Size and can see where it spiked through the night and then started coming down as per usual. The jobs finished hours ago, and the Redo Queue Size spiked to around 10 GB during the job and after the job finished came back down to around 1.5 GB, but now it's growing again even though nothing heavy has been running since.

Note: My secondary replica is setup as a synchronous replica.


texts: Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008?

texts: Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008 ????


texts:
What is the best design for a table, a Type field that is of int or char(1)?  In other words, given this schema:

create table Car
(
    Name varchar(100) not null,
    Description varchar(100) not null,
    VehType .... not null
)

Is it more efficient (performance wise) for VehType to be an int or a char(1)?  Say you have five types of cars, should you use the incrementing values 0 -> 4, or characters for the types (say; 'v', 's', 'c', 't', 'm')?

If it is any more than that, I'd use a separate Type table and have a foreign key relationship, but I don't see the need for that.

I notice that the sys.objects catalog view uses a character for the type field.  Is there a reason for that?  Am I just grabbing at thin air here, and is it whatever I'm more comfortable with?


texts:
When updating in SQL Sever, it is my understanding that the entire row is deleted and then recreated (which is why you get a new timestamp).  So then, if you update 1 field or 500 fields, does the performance of the update change?

I suspect that if there are indexed columns, as it has to update the index, there would be more "work" for the server to perform.  When answering, assume that there are no triggers.


texts: How to visualize joins between inner queries to their respective outer queries?

texts:
I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.

Here's an example:

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT TOP 1
    FROM Ninjas AS N
    INNER JOIN NinjaWeapons AS NW
        ON N.WeaponId = NW.WeaponId
    WHERE N.NinjaId = NM.NinjaId
)

Is there a way to represent this (or a similar kind of inner query joined to outer query) in a table or diagram of sorts?


texts: SQL Server Backup failing for just 1 DB ("The system cannot find the path specified.")

texts:
For the last few mornings, I have had a backup of our ReportServerTempDB fail. We are not using SSRS on this server yet, but will in the next month, so the DB is the default 8mb .mdf and 8mb .ldf (i.e. tiny).

We are using Ola Hallengren's scripts to run backups to a network location, and all the backups run perfectly until they get to the ReportServerTempDB. We mount the network location at runtime, as it is not on our domain and requires different credentials to be accessed. This is not ideal and we are looking into changing it, but for now, I need to figure this out please.

My script is as follows:

EXEC xp_cmdshell 'net use Y: \\10.1.1.200\data\SQLSANBackups /user:BackupUser Password'
GO

EXECUTE dbo.DatabaseBackup
@Databases = 'ALL_DATABASES',
@Directory = 'Y:\\',
@BackupType = 'FULL',
@ChangeBackupType = 'N',
@CleanupTime = 168,
@FileExtensionFull = 'bak',
@Compress = 'Y',
@CleanupMode = 'AFTER_BACKUP'
GO

EXEC xp_cmdshell 'net use Y: /delete'
GO

It fails with the following errors:

  BackupDiskFile::CreateMedia: Backup device 'Y:\\Servername \ReportServerTempDB\FULL\ReportServerTempDB_FULL_20190118_033124.bak' failed to create. Operating system error 3 (The system cannot find the path specified.).

  Error: 18204, Severity: 16, State: 1.

As mentioned, all DBs before the ReportServerTempDB complete successfully.
If I manually run the exact script, just specifying the ReportServerTempDB, it completes perfectly. Things were working fine earlier in the week, and there have been no network or hardware changes since.

There are 3 production databases after the backup of ReportServerTempDB. They are not getting backed up because of the failure. I don't think adding a delay will help here but will try regardless.

I am aware that ReportServerTempDB would be easy to recreate, and I will remove it if i have to, but for a tiny database, and to keep things consistent, it would be easier if I don't have to.


texts: Selection of entries that have only two words with SQL wildcards

texts:
I have a table place that has a column called title. I want to select only those data which have only two words.
For example, if the table consists of a title named "Buckingham Palace", I would have wanted it in my resultant table rather than having titles such as "Tower Of London" or "Berlin". How can this be done in MySQL?
Note: Any title that has only one space in between will be considered. For example, "St. Marc" should also be selected.
I have tried
select title from place where title not like '% % %'

I still get results in the table like St. Bishop's Palace (the entries which usually have words like St. or Mt.).
When I try that query with just a space extra added, I get unnecessary entries like Tower of God and Palace of Parliament.
select title from place where title not like ' % % % '.


texts: Is it practical to use entity-attribute-value (EAV) model to support custom user fields and avoid bloating of tables?

texts:
I am currently working on a medium-sized web app project that is already in production using Laravel framework and MySQL 5.6.40. My clients have been asking for the ability to add additional attributes to the orders table to keep track of some additional dates.

At first to avoid bloating up the orders table, i was planning to create a new table called order_custom_fields that will link to the orders table and have a column for each of the custom field. But this will make it not conventional if more custom fields need to be added in the future for different clients or if the custom fields will be attached to different tables.

I have since stumbled upon the EAV model and thought that it seems like it would solve my problem if not for most sources saying that it should be avoided mainly for performance reason and possible difficulty when querying.

Since mysql 5.6 still does not support json, is the EAV model likely my most practical choice as to avoid bloating up the orders table with custom user field?

Does anyone have a similar case and can provide some insight?

More information:

The orders table currently already has 9 foreign key, 20 attribute fields, programatically linked to many other tables, and already has close to 50,000 records.
The Laravel framework handles all my database queries, and can programatically create polymorphic relation between database tables so only 1 EAV table will be required in my case. Also, the data in the EAV table can simply be a varchar/text and be casted as required in the program.

In laravel style, the table will be something like:

+-------------+-----------+-------------+------------+
| entity_type | entity_id |  attribute  |   value    |
+-------------+-----------+-------------+------------+
| Order       |         1 | custom_date | 2020-02-02 |
| Order       |         2 | custom_date | 1991-01-01 |
| Product     |         1 | custom_name | phone1     |
+-------------+-----------+-------------+------------+


texts: More than one MATCH/AGAINST clause becomes too slow in MySQL

texts:
I have the following query:

SELECT SQL_CALC_FOUND_ROWS
    gtd_items.*
FROM
    gtd_items
LEFT JOIN gtd ON gtd_items.gtd = gtd.id
     JOIN names importers ON gtd.importer = importers.id
     JOIN names exporters ON gtd.exporter = exporters.id
WHERE
    MATCH (description) AGAINST ('motor' IN NATURAL LANGUAGE MODE) AND
    MATCH (exporters.name) AGAINST ('khs' IN NATURAL LANGUAGE MODE) AND
    type = 'ИМ'
ORDER BY gtd.date DESC
LIMIT 0 , 25;

If I remove one of the MATCH/AGAINST clauses (any one), the query is very fast. When I look at the stats I noticed that it only examines ~6,000 rows.

However, when I add the second MATCH/AGAINST clause, somehow it examines 700,000 rows, and query becomes very slow. To me it's counter-intuitive - if one MATCH/AGAINST clause examines 6000 rows, due to the second AND clause number of rows examined should've decreased further!!!

What's happening here? Following is result of EXPLAIN, though to me it explains nothing:

    *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: exporters
   partitions: NULL
         type: fulltext
possible_keys: PRIMARY,fulltext_index
          key: fulltext_index
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: no_ranking; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: gtd_items
   partitions: NULL
         type: fulltext
possible_keys: fk_data_logs_gtd_idx,description
          key: description
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: no_ranking
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: gtd
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,fk_gtd_names1_idx,fk_gtd_names2_idx,type_idx
          key: PRIMARY
      key_len: 4
          ref: xray.gtd_items.gtd
         rows: 1
     filtered: 5.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: importers
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: xray.gtd.importer
         rows: 1
     filtered: 100.00
        Extra: Using index

Thank you!


texts: Is MySQL Database Replication Single Path or Multi-Path?

texts: I read here that NIC Bonding is only useful when the IO is multi-path.  Is MySQL Database Replication single or multi-path?


texts:
https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already

I would like to check if something exists before inserting it, what should I rely on?

UNIQUE constraint or INSERT .. INTO .. WHERE NOT EXISTS .. or maybe both? Is there any other solution?

Is it possible to check if something exists in a separate query and inserting in a separate query in the way that it's atomic? Putting it in a transaction scope maybe?


texts: Add signature to stored procedure using PowerShell SMO

texts:
I want to add a signature to a stored procedure using PowerShell SMO. The equivalent command in TSQL is:

ADD SIGNATURE TO [myprocedure] BY CERTIFICATE [mycertificate] with password = 'CertificatePW'

How do I do this using PowerShell SMO without having to resort to using any form of TSQL?


texts: All data got deleted or truncated- What happened?

texts:
What are some of the ways of knowing how the 100 M rows of record table got wiped out. Database is in Full recovery mode. I am not sure how doing daily backups are happening but before the truncate or delete happened, it was 50 GB and now down to 4 GB.

Guess my question is.

How to effectively and immediately find out what query caused it?
How to avoid it in the future? Will having SQL audit run on the machine help?

Thanks


texts: How to define custom STATIC Global Variables/Constants in MySQL

texts:
As a C# developer who did database work more than 7 years ago (with MSSQL) I am now developing a MySQL Database solution where Inline SQL is prohibited in code and the developers must go through Stored Procedures (Routines).

The core challenge is defining CONST/STATIC values on the database side in one place that all of my stored procedures can access. For example:

# == RECORD STATE
SET GLOBAL RECORD_STATE_NORMAL = 0;
SET GLOBAL RECORD_STATE_ARCHIVED = 1;
SET GLOBAL RECORD_STATE_DELETED = 2;

These values will never change and the database stored procedures need to use them when writing certain record details. I also need to share these values with the developers as they may need to pass core static defined values to some of their stored procedure calls.

The core problem is MySQL apparently refuses to let me create and set my own global variables outside of the configuration file (which I don't want to do since we're deployed on AWS RDS). Any time I try to set a global variable I get an error 1193 "unknown system variable".

I need a way to define a long list of Global static values on the database side (simple numbers) that all of my stored procedures can reference.

How can I do this?


texts: Postresql replication pg_basebackup no pg_hbaconf entry for replication connection

texts:
I have updated my pg_hba.conf on the master to this.

host    loko     replicator      10.0.2.15/32               trust

and I did reload the configuration.  Then I created a user with this command

 CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'repl'

loko which is the database on my master and replicator as the user.

and running this command on the slave

pg_basebackup -h x.x.x.x -D "C:\Program Files\PostgreSQL\9.4\data" -U
replicator -v -P --write-recovery-conf -X stream

results in

no pg_hba.conf entry for replication connection from host x.x.x.x. user replicator, SSL off

Thanks a lot for any help. Im new to this so I don't have any idea whats wrong with this. I just followed a tutorial.


texts: Is it secure to set default value for password field?

texts:
I'm looking at an application where admins can "invite" users by email and invited users. Invited users then are sent a unique URL and are inserted into a MySQL database with status pending and an activation token.

However, the application has a bug currently, where the DB was set up with the password field NOT NULL, and without default value, which makes INSERTs fail.

If I modify my table structure and set a default value for the password (using some arbitrary encrypted value), admins can add new users and these users are prompted to set a password prior to first login.

My question is - is there a security risk here that I should be aware of?


texts: Query the Differential Changed Maps in SQL Server

texts:
Differential backups use the Differential Changed Map pages to determine what pages to backup. I'd like to investigate a backup strategy involving differential backups. For that purpose I'd like to automatically track the amount of changed data over time for multiple databases.

I'd like to programmatically query the Differential Changed Map to determine how many pages are changed.

This seems to be possible with DBCC PAGE according to http://www.practicalsqldba.com/2013/07/sql-server-understanding-differential.html. Is there a cleaner or "more documented" way than this? It seems like this will be awkward and error-prone to implement.


texts: Why does it take so long to view logs on a server?

texts:
I am just trying to look up more verbose information for a maintenance plan task that failed. I open up the log file viewer and check the box to view the logs for my maintenance plan. There are no active filters besides that in this example. Then I play the waiting game.....

I had it take almost 20 minutes to get the logs to show. So I thought that it might just taking the time to load all logs from the beginning of this servers time. I reduced the filter to about 3 days since that is all I needed anyway. That came up faster but still took 4 minutes to show. Note this is the first time on my computer that I have been trying to view these logs so that might also play a factor. I also tried to look at the logs directly on the server but was getting similar time results.

Is this par for the course? Should I expect viewing the logs to be an experience like this? Is there something that I should be doing or checking? I do plan to check the log age and see if it can be purged but would that still affect looking at logs for only a short X Day period?


texts:
I stumble upon strange behavior with innoDB constraint, and cannot find cause of it.
I have tables with data.
Below listed their structures:

CREATE TABLE `contents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `type` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nameUNIQUE` (`name`),
  KEY `name` (`name`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dataTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `value_UNIQUE` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `content_data` (
  `content_id` int(10) unsigned NOT NULL,
  `field_id` int(10) unsigned NOT NULL,
  `data_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`content_id`,`field_id`,`data_id`),
  KEY `fk_content_data_2_idx` (`field_id`),
  KEY `fk_content_data_3_idx` (`data_id`),
  CONSTRAINT `fk_content_data_1` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_content_data_2` FOREIGN KEY (`field_id`) REFERENCES `fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_content_data_3` FOREIGN KEY (`data_id`) REFERENCES `dataTable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now, let's execute two queries:
First query:

mysql> SELECT * FROM `dataTable` where id=21318;
Empty set (0.00 sec)

We got empty set and that is ok since dataTable in fact has no any row with id=21318
Second query:

mysql> SELECT * FROM `content_data` where data_id=21318;
+------------+----------+---------+
| content_id | field_id | data_id |
+------------+----------+---------+
|        552 |       35 |   21318 |
+------------+----------+---------+
1 row in set (0.00 sec)

Here, last query give us result with data_id=21318. WTF!

How to explain this situation?
Why constraint does not work?
Any idea, thanks.


texts:
I have a problem with log shipping in SQL Server 2012.

I have tried to configure log shipping between server A and server B. The log destination is on my PC (shared folder). This scenario is working successfully.

Now the problem is when I try to make the logshipping between two servers.  I make a shared folder from SYNOLOGY and INFORTREND. Even when I have changed the permissions, it fails to be executed. The log history of the failure shows:

  The last step to run was step 1 (Log shipping backup log job step).
  Executed as user: NT Service\SQLSERVERAGENT.
  The step failed.
  Cannot open backup device '\...\TESTLAGI_20160601082323.trn'.
  Operating system error 86 (The specified network password is not correct.)
  BACKUP LOG is terminating abnormally.

I don't know what's wrong with it. Is it because the SQL Server log shipping doesn't support a third place to put logs for log shipping?

I have added permission for EVERYONE, and given it full control. It should be working, because when I use a shared folder on my PC, it works great, but when I use storage machine, it fails.


texts: PostgreSQL: Trigger for updating view doesn't work

texts:
I'm trying to make the trigger for updating a view work.
If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run.

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'hi';
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON specific_view
        FOR EACH ROW
            EXECUTE PROCEDURE test_trigger_function();

Then in another place I execute it:

RAISE NOTICE 'before';
UPDATE specific_view SET gender='randomstuff';
RAISE NOTICE 'after';

The result:

psql:test_function.pgsql:134: NOTICE:  before
psql:test_function.pgsql:134: NOTICE:  after

On INSERT:

RAISE NOTICE 'before';
INSERT INTO specific_view (gender)
    VALUES ('randomstuff');
RAISE NOTICE 'after';

Returns:

psql:test_function.pgsql:134: NOTICE:  before
psql:test_function.pgsql:134: NOTICE:  hi
CONTEXT:  SQL statement "INSERT INTO specific_view (gender)
                VALUES ('randomstuff')"
PL/pgSQL function ... blahblahblah ... line 261 at SQL statement
psql:test_function.pgsql:134: NOTICE:  after


texts: Mysql LOAD DATA INFILE 100,000 csv records perfect BUT 300,000 records - 0 rows inserted

texts:
The following query imports 100,000 perfectly, but larger files nothing happens.  No errors, no rows loaded.

Any clues you can offer are appreciated.  I spent hours looking into max file and memory limits and need help.  Thanks!

LOAD DATA LOCAL INFILE 'C:\\temp\\masterplay\\bigone.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(`MARK`,`SERIAL NUMBER`, ...thirty some columns...)

0 rows inserted. (Query took 2.2385 sec)

The above performs perfectly and FAST on 100,000 records, does nothing on 350,000 records.  When the CSV is broken down to 100,000+ records, the import works.

Example CSV file size is 314,946 records. Average 551 characters per row. Max row length is 575. Versions: MYSQL 5.6.17, PHP 5.5.12 WAMPSERVER 2.5, Windows 10 Pro 16 GB RAM. Unable to determine failure mode, so no reference to cutoff on file.

Per suggestions below, tried same SQL on other versions and Linux, same problem.

UPDATE:

Ideas below focused me on the source CSV files.  Thus far, I discovered resaving the CSV (even though it checks good using CSVed application and appears identical in name and content) somehow fixes the file so that it will import.  I have hundreds of large files to process and yet do not understand what the difference is.

The files are comma separated with no text delimiters.  After I re-save the file, it still appears to be identical in content but roughly 300K larger file size.  Does a CSV file type have a header or meta data that makes a difference??


texts:
I'm trying to use ODP.NET in PowerShell to access an Oracle DB. I want to use my existing Oracle Wallet for the connection credentials but I find no hints about how to specify the connect string with a Wallet. I've tried a few variations but I'm just not getting it correctly specified.
SQLPlus would use /@
What is it for ODP.NET?
Searching for the solution, I found that ODP needs to be configured to find the wallet. I specified the following as app.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.manageddataaccess.client>
    <version number="4.121.2.0">
      <settings>
        <setting name="WALLET_LOCATION" value="(SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = c:\Users\xeaa2ll\Oracle\Wallet\)))"/>
        <setting name="SQLNET.WALLET_OVERRIDE" value="true"/>
        <setting name="TNS_ADMIN" value="c:\Users\xeaa2ll\ora\product\11.2.0\client_1\network\admin\"/>
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

The code I'm testing is
$configPath = "app.config"
[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $configPath)

Add-Type -Path "c:\Users\xeaa2ll\ora\product\12.1.0\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$tns = 'OODBAP01'
$uid = 'xeaa2ll'
$pw  = 'xx'
$query = "select 1 from dual"
$connectionString = 'Data Source=/@oodbap01.bnymellon.net:1524:' + $tns  # fails
$connectionString = 'Data Source=/@' + $tns                              # fails
$connectionString = 'Data Source=' + $tns                                # fails
$connectionString = 'User Id = ' + $uid + '; Password = ' + $pw + '; Data Source=' + $tns
try {
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.open()
}
catch
{
    Write-Error ("Can't open connection: {0}`n{1}" -f `
    $connection.ConnectionString, $_.Exception.ToString())
}
finally {
    $command=$connection.CreateCommand()
    $command.CommandText=$query
    $reader=$command.ExecuteReader()
    while ($reader.Read()) {
        $val = $reader.GetValue(0)
        write-host $val
    }
}
if ($connection.State -eq 'Open') { $connection.close() }


texts:
I see one mysqld process in the output of ps (or top),
mysql     77858  9.9 30.3 13750312 10089460 ?   Sl   Jul09 443:27 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

but at the same time, I see multiple instances in the output of iotop command.
 77883 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  5.27 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
  8537 be/4 mysql       0.00 B/s    3.44 K/s  0.00 %  4.50 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77887 be/4 mysql       0.00 B/s  433.41 K/s  0.00 %  1.79 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77884 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  1.59 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77889 be/4 mysql       0.00 B/s  433.41 K/s  0.00 %  0.93 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77886 be/4 mysql       0.00 B/s  901.22 K/s  0.00 %  0.29 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77888 be/4 mysql       0.00 B/s  220.15 K/s  0.00 %  0.13 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
126013 be/4 mysql       0.00 B/s    6.88 K/s  0.00 %  0.07 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 48458 be/4 mysql       0.00 B/s   10.32 K/s  0.00 %  0.05 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 78017 be/4 mysql       0.00 B/s    3.44 K/s  0.00 %  0.04 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 31528 be/4 mysql       0.00 B/s    3.44 K/s  0.00 %  0.04 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

I want to know how much that is normal? Which mysql parameter controls that? Should I increase more or decrease? I guess that is related to the connections made to the db.
The system is ubuntu 18.04 and has 32 GB of memory and the pool size is 8 GB.


texts: Altering table on SQL Server 2012 AON High Availability Databases

texts:
In a production site, the current configuration I am using is three SQL Server 2012 servers, with some databases configured with AON High Availability. I understand that between the AON High Availability databases, data would be replicated, but I was wondering if this remains true for the table definition. By my understanding, I believe that table structures not to be handled manually. CMIIW.

Assuming that is correct, what is the best way for me to deploy changes in table structure with minimal downtime?

Update:

It seems I missed out a similar question but with different wording, but nevertheless it answers my question: Do schema changes "break" SQL Server 2012 AlwaysOn or are they handled transparently?

Even though it's a duplicate question, but I think I'll keep this just for those who is searching it with a different wordings.


texts: Oracle - Using index on unique constraint, what the deal?

texts:
Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on.

SQL generated by Druid 3.10 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

SQL generated by Druid 3.13 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE),
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

As you can see, the only difference is that Druid don't generate anymore a USING INDEX TABLESPACE &&TSINDEX after the UNIQUE constraint.

I suspect that there is a reason for why Druid don't generate anymore this USING INDEX thing on the UNIQUE constraint (could have something to do with the fact that there already a USING INDEX on the PRIMARY constraint), but I would like to understand why.

I have two questions then :

Can this cause some error on Oracle ?
Do this change something for the index used by the unique constraint ? Not in the right table space ?


texts: How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)


texts: Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

texts:
I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:

922475bb-ad93-43ee-9487-d2671b886479

There is no possible hope of changing this approach, however performance with index access is poor. Setting aside the myriad of reasons this might be, there is one thing I noticed that seemed less than optimal - despite all id values in all many tables being exactly 36 characters in length, the column type is varchar(36), not char(36).

Would altering the column types to fixed length char(36) offer any significant index performance benefits, beyond the very small increase in the number of entries per index page etc?

Ie does postgres perform much faster when dealing with fixed-length types than with variable length types?

Please don't mention the minuscule storage saving - that's not going be matter compared with the surgery required to make the change to the columns.


texts: Display child records in single column by comma separted with parent records

texts:
Have parent & child table like below for Oracle

T_OPT_PARENT

OPT | OPT_DESC| STATUS
XY  | XY_DESC | A
YZ  | YZ_DESC | A
ZZ  | ZZ_DESC | A

T_OPT_CHILD

OPT | CODE
XY  | ONE_CODE
XY  | TWO_CODE
XY  | THREE_CODE
ZZ  | ONE_CODE
ZZ  | TWO_CODE

Expected OutPut

OPT | OPT_DESC| CODE                 | STATUS
XY  | XY_DESC | ONE_CODE,TWO_CODE,THREE_CODE | A
YZ  | YZ_DESC |                              | A
ZZ  | ZZ_DESC | ONE_CODE, TWO_CODE           | A

Could some one help me to construct SQL to obtain the expected result.


texts: SQL SERVER: How to get the stored procedure text EXCLUDING comments?

texts:
I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures.  However, --I didn't do this; it's inherited-- many of  the stored procedures contain testing procedures with in comment blocks and most of the tests contain transaction blocks. I am only interested in changing stored procs that actually use transactions. AND I want to be able to monitor when stored procs are updated so that I can make sure that this flag is set.

SET XACT_ABORT ON;

Addendum: based on comments, here're some examples from my system.

/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/

/*
Use case: 147
BEGIN TRANSACTION
....
*/


texts: ETL: extraction strategy for 200 source databases

texts:
What is the best extraction strategy for approximately 200 SQL Server 2005 source databases (same schema) for a daily load into a staging area in preparation for data warehouse cleansing, de-duplication, and transformations?

So far I have envisioned the following possibilities:

Transactional replication: create 200 SQL Server 2008 R2 subscribers pulling data from their respective publishers in 2005. Enable Change Data Capture on the required tables in the subscriber and the shadow tables in order to perform incremental loads into our staging database.
Rowversion: add a rowversion column on each required source table and use that in conjunction with an SSIS process to extract the change data to the staging database directly.
BCP files: create an automated task to do a nightly dump of BCP files from all source tables. Use SSIS to load these tables into the staging database as part of a full load (as opposed to incremental).

Additional thoughts:

I am nervous about the administrative and hardware overhead required to support an entirely new transactional replication topology on 200 databases.
The total combined size of the databases is about 100GB. But a majority of that is part of the transaction logs and other tables which won't be used in any fact or dimension. In other words, the BCP files won't be enormous which is why I'm considering a full extraction strategy even though everything I've read recommends against it.
I'm open to suggestions, documents, etc.


texts:
I've been considering this for quite a long time now.

The basic question is: how to unit test stored procedures?

I see that I can set up unit tests relatively easily for functions in the classic sense (I mean they get zero or more arguments and return a value).  But if I consider a real-life example of a seemingly simple procedure inserting a row somewhere, with a few triggers doing this and that before or after the insert, even defining the boundaries of a 'unit' is quite difficult.  Should I test only the INSERT itself?  That's fairly straightforward, I think–with relatively low value.  Should I test the result of the whole chain of events?  Apart from the question whether this is a unit test or not, designing a suitable test can be quite a strenuous job with lots of additional question marks arising on the way.

And then comes the problem of constantly changing data.  In the case of an UPDATE affecting more than just a few rows, every potentially affected row must be included somehow in the test cases.  Further difficulties with DELETEs and so on and so on.

So how do you unit test your stored procedures?  Is there a treshold in complexity where it gets completely hopeless?   What resources are needed for maintenance?

EDIT One more small question, based on AlexKuznetsov's answer: Or is there a treshold under which it is completely useless?


texts:
I have been tasked with creating a document for the testing of our DR/BC Plan for our SQL Servers.  We are using DB Mirroring (tier-1) and transaction log shipping (tier-2) and for tier-3 databases we will rely on nightly backups to restore too (non-critical DB's with little to none data changes).

What I am interested in is getting ideas on what should be in the document for testing the plan, I am wanting an accurate an detailed document that will test as many feasable scenarios as possible.

What I need to cover is the scenarios to test the three tiers (with more focus being on the top two tiers), which has very detailed step by step instructions on how to complete the scenario.

I have never done a document like this and would be interested in other peoples thoughts and experience.

Is there examples for this type of document that I can look at?
Has anyone had any experience in creating a document like this that would be happy to share thoughts and touch on what they included in there plan?

I have done application test cases before, do I aim for a document that is similar to a test case whereby an explanation of what you want to achieve and the steps that you do including specifying what gets typed, or should it be a more open document allowing the user to push the boundaries a little?

AS this is my first attempt and my searches on the internet hasnt returned much that is of any assistance, my appologies if I have upset anyone with silly questions or incorrect use of terminology, your guidance and corrections is appreciated.

Update: Basically, I am seeking any ideas or input into the best way to structure a SQL Server DR/BC Test failover plan to enable myself or others in the organisation to test our plans to ensure they work as intended.  If anyone knows of any good resources they can direct me to it would be greatly appreciated.


texts: Whether Cold backup method can be used to “migrate oracle DB”?

texts:
I have to migrate one database.

I have exact server replica. (Same directory structure on target as well as source database)

Previously I have performed DB migration using export/import datapump (As we don't have access to any tools).
I want to ask : Whether I can create replica using Cold backup method ?

DB Details :
Oracle 10.2.0.3 (On Both Source & target)

DB is currently running in no archive mode.

Steps I am going to follow :

create pfile from spfile
shutdown normal on source db
Copy pfile to target server $ORACLE_HOME/dbs
Copy all the dbf , ctl , rdo files to target server (Using binary mode FTP) (select name from v$datafile; select member from v$logfile; select name from v$controlfile)
startup pfile='<path>' on target server

Doubts :

Can cold backup can be used to create replica/ DB migration ?

If I tried above process , shall I am able to start the db on target server without any errors ?

As I don't have any archive logs , will that create any problem here ?

EDIT

This has been done perfectly fine with no oracle errors.

Few points :

Even you can change the directory of the dbf file while using this method. All u need to do is :

startup mount pfile='pfilepath'

alter database rename file '/flowsystem/oracle/SYSTEM01.DBF' to '/flowsystem/oracle/oradata/SYSTEM01.DBF';

shutdown

startup

If you have done changes in directory structure of controlfile , log file then edit the pfile before startup & then startup using pfile (Not Tried)

Information Source : http://www.adp-gmbh.ch/ora/admin/backup_recovery/cold_backup.html


texts: Can SQL Server OpenQuery/OpenRowSet to an LDAP Linked Server be used for Authentication?

texts:
I can fetch data back from LDAPS (port 636/3269) by first creating a Linked Server using sp_addlinkedserver and sp_addlinkedsrvlogin.
However, what I really need to do is to authenticate a Username/Password pair against LDAPS, to determine if the credentials are Valid.
Can SQL Server be used in any way to authenticate Username/Password credentials against AD/LDAP?  Is it possible to use an Extended Stored Procedure or the CLR to do this?  I need to abstract this away from a .DLL because the same authentication needs to be used in a legacy application and on a website.


texts: What happens redis uses all allocated memory?

texts:
I am quite new to redis and try to learn and explore the structure and architecture of redis.

Before version 2.4, there was Virtual Memory option available in redis by which  one could swap the lesser used values to the disk, and from the disk whenever needed. Only hotspot data(frequently used) was kept in main memory.

But now as there is no option like Virtual Memory, how does redis deals with the scenario when the maximum space allocated to it gets filled up fully ?

How it removes data from the main memory, using which algorithm/process ?
Can we write the data which is rarely used to disk without using Virtual Memory ?


texts: Add Secondary to Replica Set in MongoDB Windows

texts:
I am trying to add the second replica set to primary.

Primary = 192.168.56.52:27017
Secondary = 192.168.56.136:27017

    # mongod.conf

    # for documentation of all options, see:
    #   http://docs.mongodb.org/manual/reference/configuration-options/

    # Where and how to store data.
    storage:
      dbPath: **mypath**\DBs
      journal:
        enabled: true
    #  engine:
    #  mmapv1:
    #  wiredTiger:

    # where to write logging data.
    systemLog:
      destination: file
      logAppend: true
      path:  **mypath**\mongod.log

    # network interfaces
    net:
      port: 27017
      bindIp: 0.0.0.0

    #processManagement:

    #security:
    #operationProfiling:

    replication:
      replSetName: configServers

    #sharding:

    ## Enterprise-Only Options:

    #auditLog:

    #snmp:
    #mp:

But when I try I add rs.add("192.168.56.136:27017")
It shows as picture .

Please help to get run replicaset. Thanks.
I run on windows and start replicaset with service .


texts: Database Corruption - object in sys.parameters does not have a matching row in sys.objects

texts:
I'm using SQL Server 2008 R2 and I have a strange db corruption.
I ran dbcc check db and the output is:

  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=1) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=2) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=3) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=4) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=5) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=6) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=7) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=8) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2
  Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=9) in sys.parameters does not have a matching row (object_id=1096186) in sys.objects.
  Msg 3853, Level 16, State 1, Line 2

Now that's just a sample.
I've tried to run:

DBCC CHECKDB ('xxx', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

with no help, I tried to determine which object is that and drop and recreate the object by using select object_name (1096186) and it returns 'null'.

I've tried to do way back and still no valid backup all backups have the same corruption.
What should I do ?


texts: Manually trigger "Write current results to disk" during query

texts:
Using PostgreSQL 12, I have the requirement that some data needs to be "refreshed" each night. That means: The database has to do some calculations based on the current date and write the result to the table.
That works in general. However, the way it works is not perfect, it seems.

Basically, there is a PLV8 query like this:

DO $$

var myData = plv8.execute('SELECT id FROM my_table');
for(let i = 0; < myData.length; i++) {
    plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);
}
$$ LANGUAGE plv8;

my_function_which_refreshes_lots_of_data takes the id of one item and does all the work. It has to be done this way, because triggering of refreshing also needs to be possible for single rows sometimes.

The problem is that somehow this got out of control. Currently, the query runs for more than an hour, has taken 99% of the memory, fills up the swap space and is most of the time in status "D" - "disk sleep (uninterruptible)".
The duration of the query is not a problem, since it usually runs over night, triggered by a cronjob.
However, the memory consumption and the process status (basically somehow like a zombie) is or might be a problem.

So, my idea was:
As far as I know, PostgreSQL keeps all the changes to tables in memory while the query runs and writes them only at the end.
Would it be possible to trigger manually "write to disk what you've got so far and free the memory"?

I would do that in my code after plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);.
Of course, if the process crashes or whatever, that would result in an inconsistent state: Part of the items would be processed by my_function_which_refreshes_lots_of_data, while the rest would remain as before. However, that's better than no item being written at all. And definitely better than a query running for so long and consuming all the memory.

I know I could somehow try to split the query at client side. However, that would be less convenient for me for some reasons.


texts: Postgresql split date range by business days then aggregate by month

texts:
I have the following rows in a table (dd-mm-yyyy).

Start      | End        | Value
---------------------------------
01-01-2019 | 31-03-2019 | 64
01-02-2019 | 30-04-2019 | 126
01-03-2019 | 31-05-2019 | 66

I would like to divide the values by number of working days (just mon-fri, not holidays) between the start and end dates, then aggregate the values that occur in each month in postgres.

Based on this year's working days per month being:

Month  | Working Days
--------------------
Jan 19 | 23
Feb 19 | 20
Mar 19 | 21
Apr 19 | 22
May 19 | 23

The 1st row has 64 working days in Jan, Feb, Mar - so it's value per working day is 1. And has an aggregate value of 23 in Jan, 20 in Feb, 21 in Mar.
The 2nd row has 126 working days in Feb, Mar, Apr - so it's value per working day is 2. And has an aggregate value of 40 in Feb, 42 in Mar, 44 in Apr.
The 3rd row has 66 working days in Mar, Apr, May - so it's value per working day is 1. And has an aggregate value of 21 in Mar, 22 in Apr, 23 in May.
If we sum all the aggregate values for each month for each row we should get the result below.

Month   | Value
----------------
01-2019 | 23
02-2019 | 60
03-2019 | 84
04-2019 | 66
05-2019 | 23

So it's like grouping by month but the values are weighted by the number of working days per month.

is it possible to do this in postgres?


texts: Some basic questions about SQL and TLS 1.0 and 1.2

texts:
(I don't believe this is a duplicate of any other TLS-related question, like the authoritative question about SQL Compatibility with TLS or its many duplicates, or questions about specific TLS-related issues you might encounter with SQL CLRs or Classic ASP or SSRS or whatever. This question is a bit more broad.)

All our SQL servers (SQL 2012-2017) are on recent patch levels that support TLS 1.2 (see first link above), and yet when we disabled TLS 1.0 on our SQL servers, nearly all our applications broke (SharePoint farms, web apps, dot.net apps, PowerShell scripts, many vendor apps, heck, even some SQL jobs running on the same box failed).

I thought that clients already tried to negotiate the most secure encryption protocol that both the app and SQL support (TLS 1.2), but that's clearly not the case here.

So... what am I missing? Is there a master switch at the Windows OS level on our application servers that was never flipped to tell applications they should start using TLS 1.2? (We run Windows 2012 R2 and 2016.) Are there different registry entries for enabling "client" TLS 1.2 vs enabling "server" TLS 1.2?

I believe MS components like .Net are up to date as well. I know we will eventually need to look at individual applications, but I can't help but think there is something more obvious going on here.

Also, is there a SQL query that can tell me what connection method was attempted by each application (none vs TLS 1.0 vs TLS 1.2)? This page suggests encrypt_option from sys.dm_exec_connections, but that appears to be a boolean, not one that tells me the type (plus all my rows show FALSE, so I don't think that's right).

Update: Added bounty for more attention. I have determined that at least a couple of our older SQL servers are Windows 2012 (not R2), which (I believe) does not have TLS 1.2 enabled by default, where it should be for Windows 2012 R2 and later?? Is that right?

Also still interested if anyone has a query and/or other method to determine what protocols different clients are using when they attempt their first connection to SQL; or would that be a network-layer tool?


texts: How can one reliably update a LOB column with data from another LOB column in Oracle?

texts:
I have tried:
    UPDATE "SCHEMA".TABLE SET LOB_COLUMN = (SELECT LOB_COLUMN FROM "SCHEMA".TABLE)

I have tried:

CREATE OR REPLACE PROCEDURE COPY_LOB_DATA IS
    OLD_BLOB BLOB;
    NEW_BLOB BLOB;
    BLOB_LENGTH NUMBER;
BEGIN
    SELECT LOB_COLUMN INTO OLD_BLOB FROM SCHEMA.TABLE WHERE OLD;
    SELECT LOB_COLUMN INTO NEW_BLOB FROM SCHEMA.TABLE WHERE NEW;
    SELECT dbms_lob.length(NEW_BLOB) INTO BLOB_LENGTH FROM DUAL;
    dbms_lob.TRIM(OLD_BLOB,1);
    dbms_lob.copy(OLD_BLOB,NEW_BLOB,BLOB_LENGTH);
    if (dbms_lob.compare(OLD_BLOB,NEW_BLOB) = 0) THEN
        commit;
    ELSE
        rollback;
    END IF;
END;

and I've tried using cursors. Each method seems to work four times out of five, but every so often we'll encounter a field that just won't budge. the largest of these would be around fifty megs, but usually it's one of the smaller ones.


texts: simple SELECT EXISTS takes about two hours to execute

texts:
I have mysql server hosted. Now, If I issue simple SQL statement
SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12')
it takes about 2 hours to execute. What could be wrong? The ORDERS table is tiny, it has about 10-50 records average per day.
The structure of table ORDERS is:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 30, 2015 at 01:34 PM
-- Server version: 5.5.40-cll-lve
-- PHP Version: 5.5.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `ORDERS`
--

CREATE TABLE IF NOT EXISTS `ORDERS` (
  `USER_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PLACE_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PRODUCT_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PRODUCT_QUANTITY` bigint(3) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ORDERS`
--

INSERT INTO `ORDERS` (`USER_ID`, `PLACE_ID`, `PRODUCT_ID`, `PRODUCT_QUANTITY`) VALUES
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', '2bc7277f-c7c3-43bf-9598-9466c96a8f12', 001),
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', '4782e465-fb0a-46a0-9323-aeb3b05bb10e', 001),
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', 'b2bd1c75-6b01-4105-a626-438e365c95f5', 001);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ORDERS`
--
ALTER TABLE `ORDERS`
 ADD PRIMARY KEY (`USER_ID`,`PLACE_ID`,`PRODUCT_ID`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here is screenshot of situation:


texts: How to use array variable in query in PostgreSQL

texts:
Create table t1 ( xcheck varchar[], name text );

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS record AS
  DECLARE xrc as record;

  execute 'select name from t1 where xcheck @> ''' || xch :: varchar[] || ''';' into xrc;
return xrc;
END;

In table t1 having array value and text. I am calling this(fn_acgroup) function with array as its parameter value. In that function, I am checking the array value with passed array if it matches then I will return the name. In that case I get error as operator does not exist: text || character varying[]. Am I doing anything wrong? How can I solve this problem?


texts:
I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query.

create table t2
select field_1,
       sum(ifnull(field_2,0)) as field_2,
       sum(ifnull(field_3,0)) as field_3,
       sum(ifnull(field_4,0)) as field_4
from t1
group by field1;

The data type of all the columns is varchar(200).

This query is not able to produce the result even after running for more than 1 day. Any suggestions on getting the results quickly will be helpful.


texts: 32-bit Enterprise SQL 2005 on 64 bit Windows 2008 Server Standard. Memory configuration

texts:
Working on a project we need to first stabilize and then upgrade.

We have a 32bit Enterprise edition SQL Server 2005 installed on a 64 bit Windows 2008 standard Server.

The server has 64GB RAM installed, however in taskmgr there are only 32GB available. I believe this is a Standard Windows Server limitation, just want to confirm.

On other side, we have checked the SQL Server memory counters on perfmon, and the value we get for target and total memory is about 3.500.000, so I guess SQL server is using about 3,5GB??

I want to ask about the configurations we need to make in order to make SQL Server use as much memory as possible, hopefully 28 GB, leaving 4GB for OS, as we have no other services in this machine.

This is what I'm planning so far, but as it's a production environment, I would ideally try to nail it on one attempt, coz don't want to stop the services many times:

The service account is Local System, but I cant see the "Using locked pages for buffer pool" in the SQL Server logs. Do I need to swtich to another user account to take advantage of the "lock pages in memory" policy? What I can see in the SQL Server log is this: "Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required."
The Max server memory property is set to 10240; I think after enabled lock pages in memory, I will need to increse this to 28000
As this is 64 bit OS, no need to enable AWE. However, in the SQL server log, I can see this message: "Set AWE Enabled to 1 in the configuration parameters to allow use of more memory."

Anything else I need to take into consideration in order to enable more memory for SQL Server??


texts: show table name + number of records for each table in a mysql innodb database

texts:
How to list all tables in the current database, together the number of rows of the table.

In other words, can you think of a query to come up with something like this in mysql?

+------------------------++------------------------+
| Tables_in_database     |  Number of rows         |
+------------------------++------------------------+
| database 1             |   1000                  |
| database 2             |   1500                  |
+------------------------++------------------------+

Different approaches are welcome.


texts: Syncing a SQL Server table with a CSV file

texts:
I have recently started a project with sensors that add data to a single CSV file as data the data. I would like to have this data stored in a SQL Server database in a relatively real-time manner. I know that CSV files can be imported but I am looking for a method that will sync data between the file and the database such that data automatically gets imported to the database as it is added to the CSV file.

I have looked around and found this post which suggests using SSIS to accomplish this. Having never used SSIS, I am not sure if it is the best method or if it even meets my requirements. Does someone have a good suggestion for how I can go about doing this and/or good resources for how to do this basic task in SSIS.

If it is relevant, I am using Microsoft SQL Server 2014 Standard edition on a Windows 7 desktop.


texts: Power BI Capacity/Tenant/Gateway - where does the data sit and flow?

texts:
I have a scenario with Power Bi Premium where it has been very difficult to get a straight answer or find information that answers the question.
Our Power BI tenant is located in Australia, our Power BI Premium capacity is located in Switzerland, our on premise data is located in UK.
When configuring the on-premise data gateway, you are forced to create the connection in the default tenant region, this being Australia.
If you are a Swiss users with a workspace configured to use the Swiss hosted capacity is all data now flowing from UK > Australia > Switzerland Capacity?
Are reports rendering from a swiss user perspective;

Via the PBI service in Aus using the swiss capacity directly?
in the same datacentre location as the capacity?

I'm not sure how else to describe it and hope it makes sense. This just seems like a non enterprise ready setup perfornace wise and hope I've miss interpreted what I've been reading.
Thanks


texts: Why did my database size almost double over night?

texts:
Actually it almost doubled in a few hours.

I am hosting relatively small database in sql server 2008. By hosting I mean I have an account at hosting provider.
Last time I checked (call it hour zero) my DB was about 90MB big according to hosting data about disk space used. At the same time I did a backup and got backup file 93MB big.

Today I did some work with DB. Added few hundred record in a couple of tables. Nothing big, no blob fields nothing like that. Al this data shoud amount for less tha 0.1% of DB size.
Right now, 7 hours after hour zero I did another backup and I noticed that now my DB occupies 160MB. Meaning it almost doubled in size. But the new backup file is actually smaller than the one at hour zero. It is 81MB.

I assume that SQL Server reserves space in increments and it can reserve empty space for later use. But to double the size of DB in a few hours is a bit to much. In the past it has grown incrementaly pretty much correlated with the amount of actual data stored in DB.

I did run some transaction on DB, but all executed OK so I think none of them was left hanging.

I also regularly run elaborate query which tells me how many rows, hom much used space and how much total space every table needs. Nothing unusual there.
Another guess is that transaction log, which I admit do not know much about also takes disk space quota and I suspect this has grown out of proportions.
Just to chek I have restored the 81MB backup file, which holds the 160Mb big database. I did shrink database on that restored database and it more than halved to 77MB.
There are people who strongly advise against shrinking database, so I do not do that on production DB.

Can someone shed some light on what is going on or what should I keep my eye on?

EDIT:
Just checked.

Database files size: 131648 KB
LOG size:32448 KB

So, I guess my file size has grown. But why?

Did:

SET STATISTICS TIME ON
exec sp_MSforeachdb 'use [?]; EXEC sp_helpfile'

Got:

Growth for DB: 1024KB
Growth for log: 10%

BradC suggested I run a query, so I did and got:

db_name  type  logical_name  TotalMB  UsedMB  FreeMB  MaxSizeMB  GrowthRate
MyDb     ROWS  MyDB            128.6    73.4    55.2     1000.0     1MB
MyDB     LOG   MyDB_log         31.7     3.2    28.5       NULL     10%


texts: possible to specify field enclosure in psql command line?

texts:
In psql if I have a field separator of ",", but there are commas in some fields, is it possible to specify a field enclosure character? I have looked through the documentation but I can't find it.

example:

psql -h my_server -U my_user -d my_db -f sql_in/my_sql.sql -o sql_out/my_sql_out.csv -A -F','

-A will turn off the table formatting, -F specifies the field separator

If I have a row that looks like

id  types
1   zebra, lion, cat
2   horse

I want it to output to csv like:

1,"zebra, lion, cat"
2,"horse"


texts: Information schema not showing reliable information?

texts:
I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information and the source information.

However, I did a select count(*) from table; gave me the correct number of rows

Why is there a difference between the information_Schema.tables.table_rows and the select count(*) from table?


texts: how can I call nzsql or nzload from script without disclosing sensitive password?

texts:
I don't see this in the Netezza documentation.

We have lots of Linux scripts where we do this:

NZ_PASSWD_ENCRYPTED=29TY20T98=
NZ_PASSWD_CLEAR=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER}  -pw $NZ_PASSWD_CLEAR -h $NZ_HOST \
              -qc "select 'hi there folks;'")

I don't like leaving the password hanging there in the command environment where it can be hacked.

Is there any kind of function to handle the password differently in the nzsql script?  Did I miss something in the documentation?  Or any tricks in Linux scripting I could use?

Thanks in advance....


texts:
I would like to get your valuable suggestions for the below problem.

My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data for a while and after sometime I start seeing the below Error 1.

Error 1:

[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003

Error 2:

[IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003

Error 1 occurs only once, after that Error 2 occurs repeatedly till I restart the application and establish new connection to the database. It's been confirmed that the database is running healthy and able to accept connections and even the network connections are healthy.

Background on Error 1:

Error "CLI0108E" Suggests that "The connection between the driver and the data source failed during execution of this function."

And "SQLSTATE=40003" suggests that "The statement completion is unknown."

Is it possible that something going wrong in an insert statement actually closes the connection? Or leaves the connection stateless?

This actually breaks my Perl application. What are the best options to handle this situation?


texts: Where can I find good literature for beginners?

texts: I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. I searched through the questions on the site but didn't find any. Thankfull for all your input.


texts:
I was trying ti figure out how to calculate the right gcache for my server and came across the following post discussing just how to do that:

https://www.percona.com/blog/2014/09/08/calculate-correct-size-percona-xtradb-clusters-gcache/

In the post, the talk about doing the following to get the values needed for the calculation:

show global status like 'wsrep_received_bytes';
show global status like 'wsrep_replicated_bytes';
select sleep(60);
show global status like 'wsrep_received_bytes';
show global status like 'wsrep_replicated_bytes';

My question is, will running the select sleep(60) in production block all reads on MySQL (by keeping the mysqld thread busy)? How can i calculate the best gcache value in production?


texts: Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables

texts:
I've been doing some reading on the improvements that Trace Flag 2453 can give on the performance of Table Variables by maintaining statistics and was wondering what would be the reasons you would advise to not turn this on globally.

I understand there will be some overhead of recompiles if the number of rows being used substantially differs - but to me, this would appear similar to any other query, and be preferred behaviour if it is caching better query plans.

Is there other reasons why this would not be preferred behaviour to generate statistics on table variables, similar to temp tables?

Primarily I'm thinking about replacing a few places where we use XML to bring in data sets and use Table-Valued-Parameters instead. We do use temp tables for the most part.


texts: How to create login and logout trigger for client audit?

texts:
I need a table to put login and logout information for client not sysadmin,then i can use date to statistic user login/off ration...

Can anyone offer me a simple method to create login and logout trigger for audit specific client not the sysadmin,

Thanks in advance.


texts: Data Sharing between Client and Server Two Way

texts:
Please solve my query

One of my Silverlight application is running.
This Application is using one central database (sql server 2008) and three other client databases (sql server 2008) at other server locations.
Our requirement is with minor changing or without changing in Silverlight application
When we make change in central database (insert update and delete in multiple tables) these change
Automatically occurs in all client database.
and when any change made in any client database all client as well as central database should be updated automatically.

For this I read some about Sync Framework and SQL Server 2008 Change Tracking.
But i have no idea about how will I do this.
Please help me. How can we achieve this.


texts: When trying to configure data guard broker I'm getting Error: ORA-16606: unable to find property ""

texts:
I'm trying to setup data guard broker in my production instance. I have duplicated the primary database to standby database successfully. I've added primary database to my configuration and when I'm trying to add standby database following error is getting

DGMGRL> add database 'orcl_standby' as connect identifier is orcl_standby
maintained as physical;
Error: ORA-16606: unable to find property ""
Error: ORA-16554: translation not valid

Failed.

I think it is the issue with the connect identifier I have given. But I'm not able to troubleshoot that. Below is my tnsnames.ora of standby database.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.19)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
      (UR = A)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL_STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl_standby)
      (SERVICE_NAME = orcl_standby)
      (UR = A)
    )
  )

Please help to troubleshoot this issue.


texts: psql \watch implementation for MariaDB? Repeatedly execute the current query

texts:
Quote from current Postgres manual

  \watch [ seconds ]
  Repeatedly execute the current query buffer (as \g
  does) until interrupted or the query fails. Wait the specified number
  of seconds (default 2) between executions. Each query result is
  displayed with a header that includes the \pset title string (if any),
  the time as of query start, and the delay interval.

Is there some similar functionality for MariaDB or MySQL?

For example this statement I wish to repeat every 2 seconds. In Linux it's the watch command

NAME
       watch - execute a program periodically, showing output fullscreen

run statement till state changes.

SELECT * FROM TABLE WHERE state = retry


texts: Can we use subquery in GRANT statement in postgres

texts:
I want to grant select privilege on few selected tables.

Can I use sub-query in GRANT statement????

I have tried the following query:

GRANT SELECT ON TABLE
(SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' ) as  flttab
FROM information_schema.tables
where table_schema = 'try3' AND table_name like 'ph1_part_03%') a TO xyz;

But I have got the below error:

  ERROR:  syntax error at or near "("
  LINE 5: GRANT SELECT ON TABLE  (SELECT array_to_string( array_agg(ta...
                                  ^

  ********** Error **********

  ERROR: syntax error at or near "("
  SQL state: 42601
  Character: 282


texts: Postgres revoke database access from user

texts:
When I tried

REVOKE ALL PRIVILEGES ON DATABASE postgres from admin;

but the user admin is still able to connect to postgres remotely via pgadmin after that.

How do I completely revoke an user's access to a database?

Log: https://pastebin.com/raw/Q2mfkf3P


texts: Is compression used in RDS SQLServer database

texts:
I have few Clarification regarding AWS RDS SQL Server.

Do we have any compression technique to compress the data loaded into RDS SQL server?
Is there any option or parameter to compress the data and load into RDS SQL server?


texts: How to update a field in sharded environment in mongodb using C# driver

texts:
I have successfully done loading in sharded environment. It is working fine. But when I do update in sharded environment, I am getting the error as :

WriteConcern detected an error
 'update { q: { IsTagged: "0" }, u: { $set: { IsTagged: "1" } }, multi: false, upsert: false } does not contain _id or shard key for pattern { JobId: "hashed" }'.
 (Response was { "ok" : 1, "code" : 61, "err" : "update { q: { IsTagged: \"0\" }, u: { $set: { IsTagged: \"1\" } }, multi: false, upsert: false } does not contain _id or shard key for pattern { JobId: \"hashed\" }", "n" : NumberLong(o), "updatedExisting" : false }).

I need to update the value of Istagged from 0 to 1 . But it is showing as read concern error.I can get the point that the error is due to not giving the shard key. The Code which I am using is :

var query = Query.EQ("IsTagged", "0");
var update = Update.Set("IsTagged","1");
collection.Update(query, update);

I am using C# driver to communicate with mongodb. I dont know how to mention shard key in this coding.


texts: query to get list of tables that has same columns from information_schema.columns?

texts:
idea is to list all the tables that has redundant columns across database using information_schema.columns!
let's say

table1 = dbo.ABC
columns = id, name, department, designation

table2= dbo.DEF
columns= department, permissions, cube

table3= dbo.GHI
columns= street, zip, city, state

the query should list the tables 'dbo.ABC'  and 'dbo.DEF'


texts:
I'm trying to save a database diagram but I'm getting an error. How can I fix this?

  Cannot insert the value NULL into column 'diagram_id', table
  'SOFT.dbo.sysdiagrams'; column does not allow nulls. INSERT fails.
  The statement has been terminated.
  The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed.
  A status of 0 will be returned instead. (.Net SqlClient Data Provider)


texts: Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store?

texts:
Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object".
The goal is to make it simpler to associate statements in the Query Store:

select object_name(q.object_id) as [Statement Context]
from sys.query_store_query q
where 1=1
    and object_name(q.object_id) like 'This will be the procedure name for ''normal'' statements'

While it's possible to embed comments in the statements that will show up in the query_sql_text, this feels a bit extra-hackish.
Also, it seems that sp_executesql would need some form of context binding as the dynamic SQL can access non-global temp tables in the surrounding scope: without a binding, how can SQL Server ensure the validity & stability of the temp table schema in the created plans?


texts: SP or query to dump "password policy" checkbox for all logins

texts: sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for screenshots of every login. I'd prefer to make this simpler on me (to generate) and simpler on them to audit.


texts: My hard drive running out of space using temp tables in MySQL SSMS, what can I do to continue with my work?

texts:
My computer only has 5.5 GB of available disk space.
I am running heavy queries that are computing billions of records with many temporary tables to get the final output. What's clogging my hard drive is not the final output but the temporary tables generated during the query.
I know the following can help to workaround the issue:

Shrink database
Improve query efficiency (which I had with many indexes, etc...)

While the above list helps, I am going to need something more effective than this as I plan to have much more vast computation that will require space from the hard drive that realistically my computer won't be able to accommodate the space for it no matter if I shrink or how much I improve the query efficiency.
So the question is, what can I do to accrue for this extra space, would an external hard drive work? Could I use a cloud computer, or do I need to buy a whole new computer with more space in the hard drive to get this to work?


texts: Performance issues with partial join query

texts:
I have two tables:

dbo.allowedURLs (a unique list of all permitted domains)

*** DomainName ***
google.com
facebook.com
etc...

dbo.loggedURLs (a list of URLs processed by a proxy server)

*** Username ***      *** URL ****
fred.smith            www.google.com/something-else-here
joe.black             www.othersite.net/search/query

I need to be able to return a list of all the domain names in the 'allowedURLs' table and a count of how many different users accessed the service. The 'url' field in the 'loggedURLs' table contains the domain name within the full URL string.

Output

*** DomainName ***      *** Count of Username ****
google.com              1

While the query below gives me the results I'm after, I've been having significant performance issues running this and I was just wondering if anyone has advice on a better way to achieve this same outcome?

SELECT
dbo.allowedURLs.DomainName,
COUNT(DISTINCT dbo.loggedURLs.username)

FROM dbo.loggedURLs

INNER JOIN dbo.allowedURLs ON
dbo.loggedURLs.URL LIKE '%' + dbo.allowedURLs.DomainName + '%'

GROUP BY dbo.allowedURLs.DomainName

Thanks for any advice!


texts: Information on a Database that was restored with NO RECOVERY

texts:
I'm working with SQL Server 2008Sp3.

I have a database that was restored from a full backup WITH NO RECOVERY and it then was restored with Transaction log backup files - also WITH NO RECOVERY.

At the moment the database is in Restoring status.

Let's say that couple of hours after the last transaction log restoration I want to continue restore the new 2 hourly transaction logs that were created, and then restore it WITH RECOVERY.

How can I query the status of the database, so I will know what was the last Transaction log backup that was restored on that database?

The reason I'm asking that is because I would like to automate this in a script -

I want to create a daily full restoration of a database on a separate server with all the available Transaction log backups WITH NO RECOVERY, and then upon request (when it's necessary), I'll be able to execute another script that will restore the new Transaction log backups that were created since the end of the daily script + restore it WITH RECOVERY.

My question is regarding the second separate script - finding the rest of the needed Transaction log backups.

Thanks in advance,

Roni.


texts: ora-49204: recursive dde invocation at phase i - after switchover to standby

texts:
I am currently testing a data guard environment. After a switchover, which is performed successfully, I can see the following error in the alert.log of the new stand:

ora-49204: recursive dde invocation at phase i
This error is followed by ora-01110.
This happens for all data files.

The standby is running in open mode read only.
I checked the database for logical and physical block corruption via rman.
Everything looks good.

How do I have to interpret this error?
I am using the base version of Oracle 12cR2.

I would appreciate, if anyone could give me some hint.

Best regards

IronMike


texts: Export from Oracle 10g and import to Oracle 12c unable to create users

texts:
I have and old install of Oracle 10g with a lot of users and tablespaces in it, for development purposes. Now, I need to migrate these users, tablespaces and everything else, to an Oracle 12c installation.

Following some posts online, I did this command to export, everything I hope, from the Oracle 10g.

CREATE DIRECTORY datapump AS '/oracle/datapump'
expdp system/password@osub full=Y directory=datapump dumpfile=DB10G.dmp logfile=expdpDB10G.log

It executed with no errors. To import on the oracle 12c instance I did this:

CREATE DIRECTORY datapump AS '/oracle/datapump'
impdp system/password@osub full=Y dumpfile=all.dmp logfile=impdpDB10G.log directory=datapump

But on the log file I get error like this:

CREATE USER "BI" IDENTIFIED BY VALUES 'FA1D2B85B70213F3' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
ORA-39083: Object type USER:"JBOSSDB" failed to create with error:
ORA-65096: invalid common user or role name
Failing sql is:
 CREATE USER "JBOSSDB" IDENTIFIED BY VALUES '00C6DD010C22FF42' DEFAULT TABLESPACE "JBOSSDB" TEMPORARY TABLESPACE "TEMP"

I read online about Oracle pluggable databases. I don't care about any of that.
I just wanted to import all the users, tablespaces etc, that I had on Oracle 10g, without having to do it manually one by one.

Is it possible?


texts: SQL Server 2008 commit overhead warnings

texts:
I have four mirrored user DB's on a pair of Server 2008 R2 machines. My Db engine is SQL Server 2008. Of the four Db's one is warning me of overhead commit latencies ( > 20ms). From some research on the web I see many references of a "redo queue".

I was hoping some one could explain to me what is a redo queue and how can I monitor its behavior.


texts: Are database backups specific to the OS on which they are created?

texts:
I am trying to get a better understanding of the behaviors of database backups for Sybase (15.X) and Oracle (11.x) as it relates to the underlying OS. Is the backup file OS specific or can it be backed up on one OS and restored on a separate OS without issue?

If we wanted to support a customer on Oracle (Linux) and wanted to be able to restore their database for support purposes but only had Oracle on AIX is this possible?

If it is feasible does it depend on the settings used to generate the backup?

I would like to know for both Sybase and Oracle versions mentioned above.

Thank you for your help.


texts: SQL Server 2005 onwards- Capturing Baseline

texts:
I want to design a process by which I can capture the baseline data of multiple SQL Server instances (2005 onwards) in a single database from where I can get all the info and alerts.

As of now I want to capture info about the below counters:

Free space available on disks
Database File and Log space available
Wait statistics
CPU and memory counters

Is there any free tool that I can refer to and modify if needed or any useful article that can help me to set this up?

As of now I am referring to the article by Erin Stellato in SQLServerCentral.com.

Any help would be greatly appreciated.


texts: Difference between SQL node and Data Node

texts:
In a MySQL cluster, there are several different types of nodes:

Management Node,
SQL Node,
Data Node,
API Node,

What are the differences between the latter three types?


texts: understanding index statistics in pgadmin

texts: I am trying to get an understanding of indexes. Below are some index statistics for one of my indexes in pgadmin. I am attempting to understand the difference between a scan,tuple read,tuple fetched,block read, and block hit so I can determine the validity of the index's existance.


texts: Is it possible to duplicate a standby database on the fly?

texts:
Can I duplicate a standby database on the fly?
Currently, I'm getting this error:

RMAN-03002: failure of Duplicate Db command at 01/08/2021 01:55:58
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 3
was not restored from a sufficiently old backup

When I run:
DUPLICATE TARGET DATABASE TO clone_db FROM ACTIVE DATABASE;

Any idea?


texts: MySQL: Writing log entries from stored procedure

texts:
Is there a standard way to write log messages in MySQL stored procedures?

I can write log messages to a designated log table, but I prefer the standard log mechanism where each message is coupled with severity and sent to an external file.