texts: Is having all entity tables inherit from one main entity table a bad idea?

texts:
My co-worker is designing a schema where most entities (like contacts, quotes, tasks) inherit from an "entity" table, which keeps track of things like which tenant owns the entity, and chagnes to the entity. This is similar to VTiger CRM's schema.

Is this a terrible idea? What should I tell him the problems are?

Is there a name for this pattern?

ENTITY
id PK
tenant FK TENANT
version
draft
deleted

ENTITYCHANGE
entityId PK FK ENTITY
version PK FK ENTITY
createdBy
createdAt
fieldName
fromValue
toValue

CONTACT
id PK FK ENTITY
name
[other contact fields]

QUOTE
id PK FK ENTITY
contactId FK CONTACT
[other quote fields]


texts:
I am using PXC 5.6 (Percona-XtraDB-Cluster-56-5.6.41-28.28.1.el7.x86_64) and my log file is flooded with this warning:

  [Warning] Client failed to provide its character set. 'latin1' will be
  used as client character set.

how can I disable this warning or at least set character by default?


texts:
I was wondering what the difference between the following two methods is:

 GETUTCDATE()-2

and

  DATEADD(d,-2,GETUTCDATE())

I guess using DATEADD is the correct way, but was wondering why?


texts: What is wrong with this syntax of a multi-table DELETE...ORDER BY?

texts:
I have the following query:

DELETE proxies, pxpl FROM proxies
JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID
WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR)
ORDER BY pxpl.ProxyListID, proxies.ID

The ordering is needed to avoid DB deadlocks.
However, it yields an error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MariaDB server version for the right syntax to use
  near 'ORDER BY pxpl.ProxyListID, proxies.ID' at line 4

proxy_xref_proxy_list is a many-to-many relation association table.


texts: Correcting data v Manipulating Query to accommodate data discrepancy?

texts:
I hope this is not the wrong place to ask, if so, please kindly advice the right place.

Say in a company you have 2 database that are different yet contains the same data. While how the data stored is generally the same, it has quite a lot small data inconsistency and discrepancy. But you are given a task to collect data from those two databases.
I wanted to know how would a DBA handle this situation, given that you cannot fiddle directly to the actual data without getting permissions from involved departments.

Will they, say :
1. Create a temporary DB to accommodate things.
2. Adjust the Query to accommodate for all kinds of problem that can be traced
or maybe there are other solutions?

Thanks before.


texts: when scripting SQL server agent jobs, is it possible to use @@servername in the @command settings line

texts:
I am trying to script an SQL server agent job so that one script can be run on multiple servers to set up the same job.  we have the same users and will use the same job name on the different servers. however there is one part that I am struggling with, and that is getting the server details for the relevant servers pulled through as part of the script.
the initial script has something like this:

@command=N'/ISSERVER "\"\SSISDB\SSP SSIS\step1.dtsx\"" /SERVER (servername) /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',

I am aware that I could replace our server name with localhost, however then you can only view the configuration settings from the server that the job will run on, which isn't ideal as not everybody has access to all server, especially the production server.

I was hoping to use "/SERVER' + @@SERVERNAME + '/Par" but in breaking the string for the @command line I receive an incorrect syntax error at the + symbol and I can't find any way of getting this to accept this command.


texts: Create Linked Server using Active Directory account

texts:
I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my personal account and the service account, from MSSQL and from .Net exe.

Now I need to configure a Linked Server on another instance to this one, using this AD service account.

For creating a Linked Server with local MSSQL account, I just open New Linked Server dialog, provide MSSQL host, set server type to MSSQL, in Security set Be made using this security context, provide username and password, and in Server Options set true to RPC and RPC Out.

But when I try to do that with an AD account, I get an error saying that login failed.

Any idea how to configure a Linked Server using AD account?


texts: How are pages reallocated in a a data file after shrinkfile sql server

texts:
I understand that shrinking the data files is a bad idea in SQL Server due to index fragmentation.
I want to ensure that I completely understand before arguing my point.
I know that when dbcc shrinkfile (unless with truncateonly) is run, the pages from the back are moved to free spaces in an earlier position.

Does this function get the very last page in the data file and place it in the first available space?

In other words, if we have this situation:

[ Page 1 ] [ Page 2 ] [ empty1 ] [ empty2 ] [ Page 3 ] [ Page 4 ] [ empty3 ] [ empty4 ] [ Page 5 ]

...after executing a dbcc shrinkfile would [ Page 5 ] be moved to [ empty1 ] because that is the first available slot? and so on...

In the end it eventually would look like this:

[ Page 1 ] [ Page 2 ] [ Page 5 ] [ Page 4 ] [ Page 3 ]

Any help would be appreciated.


texts:
I have a two tables in the database. First table has a foreign key to second table.

Can I get second table name by the foreign key from first table?


texts:
I have a large (90 GB) database. About half of the space is taken up by two tables that are no longer used. I intend to truncate these two tables and as I understand things this won't free up the space (on the disk) assigned to this database until I shrink it. I don't want to shrink it at the moment.

There is an overnight backup process. Will it be possible for me restore this database to a server that only has 50GB space free or will the restore create a 90GB database that needs a shrink?


texts: What option we can disable temporary in PostgreSQL make database update faster

texts:
I want to update 3 million records within some seconds. i have tried doing below things

ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ;
ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs DISABLE TRIGGER ALL;

once the update is done i am reverting this settings. with this change i have noticed that my update time is now 2 minutes which was eariler taking 10 min.

What other settings i can disable in PostgreSQL to make update even more faster. Data in my table is inserted and update by nightly job.

My postgresql version is 9.5

Thanks


texts:
MySQL server version:

  mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))

MariaDB mysqldump CLI version:

  mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)

I am using this mysqldump version to take dump from the mentioned mysqld version. Data is around 25 gb.

I am getting below error while importing the dump,

  ERROR 3105 (HY000) at line 82: The value specified for generated
  column 'column_name' in table 'table_name' is not allowed.

Source and destination mysql server is same version.

Command used to dump,

mysqldump -h mysql -P 3306 -uroot -px xxxxxxx --databases db_name

Total number of characters in Line 82 is around 80k, line starts with,

  INSERT INTO table_name (ID, XXXXX, XXXXX, XXXXX,
  XXXXX, XXXXX, XXXXX, XXXXX, XXXXX,
  XXXXX, xxxxx, xxxxx, XXXXX) VALUES
  (5541,1,4852,'2018-11-12 09:30:25.306','{\"id\": 1108

Output from show create table, for the column throwing error:

  column_name varchar(45) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS
  AS (json_unquote(json_extract(OPTIONS,'$.type'))) VIRTUAL,

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?


texts: Return a ranking from multiple table with mySQL


texts:
What is the best way to check if a VARCHAR field has Non-Ascii Characters?
CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255).

I tried using PATINDEX and have run into the following issue.

Checking the lower range worked correctly.

SELECT *
FROM mbrnotes
WHERE PATINDEX('%[' + CHAR(1)+ '-' +CHAR(31)+']%',LINE_TEXT) > 0

My data had three records with 0x1E and all three where returned.

But when I check just the upper range:

SELECT *
FROM mbrnotes
WHERE PATINDEX('%[' + CHAR(127)+ '-' +CHAR(255)+']%',LINE_TEXT) > 0

It returns close to all the records in the table (table count 170737 and returned count 170735) and since my data did not have any values in this range I would think it should have returned no records.


texts: How can I get top users in specific tags?

texts:
I have a function like this:

CREATE DEFINER=`root`@`localhost` FUNCTION `user_top_tags`(`user_id` INT, `tags_num` TINYINT(1)) RETURNS varchar(50) CHARSET utf8mb4
    NO SQL
BEGIN
    DECLARE  top_tags varchar(50);

    SELECT substring_index(group_concat(x.name  ORDER BY x.tag_score DESC SEPARATOR ','), ',', tags_num) INTO top_tags
    FROM (
        SELECT t.name, sum(r.score) AS tag_score
        FROM reputations r
        JOIN qanda_tags qt ON qt.qanda_id = r.question_id
        JOIN tags t ON t.id = qt.tag_id
        WHERE r.owner_id = user_id
        GROUP BY t.name
    ) x;

    RETURN top_tags;
END

And I call it like this:

SELECT u.name, user_top_tags(u.id, 3) FROM users u WHERE 1;

And it returns a list of users with their top three tags. Something like this:

+--------+-----------------+
| Jack   | php,oop,mysql   |
| Martin | css,js,html     |
| Peter  | jquery,js,react |
+--------+-----------------+

Now I want to get the users which has active in specific tags. Something like top users page in SO (which is for javascript tag, but I want to get list of users in multiple tags, like IN ('css','html')).

Now should I do a join on the query? Or should I modify the function? Does anybody have any idea how can I do that?


texts:
I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So basically everything is heaps. There are noclustered indexes on many tabels. We get data from many source systems and after data has landed we transform the data and combine it...
I just want to hear if it sounds right in some settings to not use any columnstore or clustered indexes. Does it require more CPU to use clustered indexes when tables used for analytics?


texts: Changing the owner of a read-only database which is configured with Log Shipping

texts:
Is there a way to change the owner of a read-only database which is configured with Log Shipping without actually re setting up LS?

Per this link it's not, but I'm checking if anything has changed since then.


texts: Oracle: Not able to get value from All_Synonyms in PL/SQL

texts:
Dabatase: Oracle

User: Not dba

In my oracle database, im able to do the following:

SELECT * FROM ALL_SYNONYMS

But when i do it in pl/sql, it is able to compile and execute, but the dbms.putline prints nothing:

CREATE PROCEDURE PROCEDURE_1 AS
  strName VARCHAR2(50);
  CURSOR C_NAME IS SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = 'USER1';

BEGIN

   FOR R_NAME in C_NAME
   LOOP
     strName := R_NAME.SYNONYM_NAME;
     dbms_output.put_line(strName);        <-----
   END LOOP;

END PROCEDURE_1

Do we need to grant any role to a non-dba user for All_Synonyms table?


texts:
My database is running in archive log mode, but flashback is disabled because I do not need it.

The FRA is set to /flasharch mount point.

Now, I am going to change the FRA location to /flasharch01 mount point due to space limitation.

What to do with archivelog in the old location. Do I need to move the old files to new location?

Could you guys please guide me step by step?

RMAN uses control file instead of recovery catalog.

Should I create a recovery catalog? if yes.. please how? I need steps please.

As, I can't increase the FRA location. The only way to change the location is the solution. Could you please share the steps how to move the old files and how to catalog them? RMAN using control file instead of recovery catalog.


texts: TSQL to get a list of all users and when they last logged on in crowddb

texts:
We have a crowddatabase and are trying to get a list of every user and when they last logged on. On Atlassians website there is a query but only for MySQL, the query is:

SELECT
  cwd_user.user_name,
  from_unixtime(cwd_user_attribute.attribute_value/1000)
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated'

I need to rewrite it for mssql and tried with

SELECT
  cwd_user.user_name,
  dateadd(s,12345,1/1/1970)
  select cwd_user_attribute.attribute_value
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated'

but get error The multi-part identifier "cwd_user.user_name" could not be bound.

Anyone who can help with the query?


texts:
On my project, I have to make difference between registered users, and simple visitors. Both of them can set their own properites, specified in accounts and visitors table.

I identify them by cookie, if there is no cookie, a user can log in, and create a session (and a "remember me" cookie if she wishes) and the simple one time visitor also creates a cookie and a session.

I split the current session to either accountSession (logged in person), and to visitorSession (simple visitor).

I insert the visited pages, userAgents, IPs by the accountSession OR the visitorSession.

Am I overengineering?

Here is the diagram:


texts: SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only

texts:
I'm attempting to create a stored procedure that's able to create and drop tables. A given user will have EXECUTE permissions on that stored procedure. Is there any way that the user would be able to successfully execute the stored procedure without having the database-wide permissions granted as well for creating and dropping tables? Some initial research has shown that certificates might be a way to solve this but I'm not sure if even those are suitable for creating and dropping without database-wide permissions being granted. In Oracle this is solvable through granting the owning schema rights, but I don't think this concept is mirrored in SQL Server.

Another possible solution that comes to mind would be using EXECUTE AS in the stored procedure to some user that has access to what we need it to, maybe even dbo. This would then let the user execute just that stored procedure but the procedure would have all the permissions it needs to operate. We'd ideally not use this though as this is a vendor stored procedure so we don't want to touch it if we don't have to. Any other ideas would be welcome.

So we have common language let's say I'm using abc for user and test_proc for procedure.


texts: How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

texts:
I'm querying to the system catalog in Postgresql 9.6.4

Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to find the type of index anywhere in the system catalogs.

How can I query the catalogs to get a list of indexes along with their type?


texts: SUM over distinct rows with multiple joins

texts:
Schema:

CREATE TABLE "items" (
  "id"            SERIAL                   NOT NULL PRIMARY KEY,
  "country"       VARCHAR(2)               NOT NULL,
  "created"       TIMESTAMP WITH TIME ZONE NOT NULL,
  "price"         NUMERIC(11, 2)           NOT NULL
);
CREATE TABLE "payments" (
  "id"      SERIAL                   NOT NULL PRIMARY KEY,
  "created" TIMESTAMP WITH TIME ZONE NOT NULL,
  "amount"  NUMERIC(11, 2)           NOT NULL,
  "item_id" INTEGER                  NULL
);
CREATE TABLE "extras" (
  "id"      SERIAL                   NOT NULL PRIMARY KEY,
  "created" TIMESTAMP WITH TIME ZONE NOT NULL,
  "amount"  NUMERIC(11, 2)           NOT NULL,
  "item_id" INTEGER                  NULL
);

Data:

INSERT INTO items VALUES
  (1, 'CZ', '2016-11-01', 100),
  (2, 'CZ', '2016-11-02', 100),
  (3, 'PL', '2016-11-03', 20),
  (4, 'CZ', '2016-11-04', 150)
;
INSERT INTO payments VALUES
  (1, '2016-11-01', 60, 1),
  (2, '2016-11-01', 60, 1),
  (3, '2016-11-02', 100, 2),
  (4, '2016-11-03', 25, 3),
  (5, '2016-11-04', 150, 4)
;
INSERT INTO extras VALUES
  (1, '2016-11-01', 5, 1),
  (2, '2016-11-02', 1, 2),
  (3, '2016-11-03', 2, 3),
  (4, '2016-11-03', 3, 3),
  (5, '2016-11-04', 5, 4)
;

So, we have:

3 items in CZ in 1 in PL
370 earned in CZ and 25 in PL
350 cost in CZ and 20 in PL
11 extra earned in CZ and 5 extra earned in PL

Now I want to get answers for the following questions:

How many items we had last month in every country?
What was the total earned amount (sum of payments.amounts) in every country?
What was the total cost (sum of items.price) in every country?
What was the total extra earnings (sum of extras.amount) in every country?

With the following query (SQLFiddle):

SELECT
  country                  AS "group_by",
  COUNT(DISTINCT items.id) AS "item_count",
  SUM(items.price)         AS "cost",
  SUM(payments.amount)     AS "earned",
  SUM(extras.amount)       AS "extra_earned"
FROM items
  LEFT OUTER JOIN payments ON (items.id = payments.item_id)
  LEFT OUTER JOIN extras ON (items.id = extras.item_id)
GROUP BY 1;

The results are wrong:

 group_by | item_count |  cost  | earned | extra_earned
----------+------------+--------+--------+--------------
 CZ       |          3 | 450.00 | 370.00 |        16.00
 PL       |          1 |  40.00 |  50.00 |         5.00

Cost and extra_earned for CZ are invalid - 450 instead of 350 and 16 instead of 11.
Cost and earned for PL are also invalid - they are doubled.

I understand, that in case of LEFT OUTER JOIN there will be 2 rows for item with items.id = 1 (and so on for other matches), but I don't know how to build a proper query.

Questions:

How to avoid wrong results in aggregation in queries on multiple tables?
What is the best way to calculate sum over distinct values (items.id in that case)?

PostgreSQL version: 9.6.1


texts: upgrade from 9.4 to 9.6 using pg_upgrade fails

texts:
I'm using pg_upgrade to upgrade postgresql from 9.4 to 9.6.Both versions are installed on windows server 2016.I followed the steps described in https://www.postgresql.org/docs/current/pgupgrade.html. Just as following:
RUNAS /USER:postgres "CMD.EXE"
SET PATH=%Path%;D:\Program Files\Postresql 9.6\bin;
D:\Program Files\Postresql 9.6\bin>pg_upgrade.exe --old-datadir "D:/Program Files/Postgresql/data" --new-datadir "D:/Program Files/Postresql 9.6/data" --old-bindir "D:/Program Files/Postgresql/bin" --new-bindir "D:/Program Files/Postresql 9.6/bin"

After run I got this
check for "D:/Program Files/Postgresql/data/base" failed: No such file or directory
Failure, exiting

I've checked,the directory exists and "postgres" have all previliges. I'm new to postgresql,pls tell me how could I solve this.


texts: Two-part question on how Postgres queries multiple columns and its performance

texts:
I have a Django "nominations" app that stores some personal information of nominees, e.g. name, email, shipping address. The email column has a max length of 255, where the shipping address would consist of multiple columns: the address line, city, province, and postal code.

A request has been made to be able to identify duplicates of the shipping address in the form validation stage, so that the user will be prevented from being nominated twice. (I had just been planning to identify duplicates in post-processing -- all we're doing is collecting user information within a specified time frame and sending it all off to a third party).

EDIT because I was tired yesterday and didn't word my question properly:

So my question is two-fold:

a) If I am to match the address on four columns -- namely, address, city, province, postal_code, does that mean that when there are no matches found in address, no matches will be returned on the spot? Or will it go on to query the other columns even though there is no possibility of an exact match? The former seems smarter and more intuitive to me, but it seems to me that databases don't always exemplify that kind of behaviour.

b) Will a query on four columns perform -- on the whole -- worse than a query on one column? (in this case, an email column of max length 255?)


texts: how to setup mongodb replica on cloud server?

texts:
I want to setup mongodb repicaset on my cloud server(AWS ec2) but I have only one IP address.
Currently I have make /etc/hosts two entries 127.0.0.1 dbnode1 and xx.xx.xx.xx dbnode2 but when I try to reconfig rs config its give error.

Error:- Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded: node1:27017; the following nodes did not respond affirmatively: node2:27019 failed with Error connecting to node2:27019 (127.0.0.2:27019) :: caused by :: Connection refused

Can anyone suggest how to I can setup replica or without setup replica how to use transaction query.


texts: Piecemeal restore of entire database - fastest way to roll forward and restore multiple files

texts:
I have a database which has two filegroups (both r/w), each with two files. The backup sequence was:

Full Backup
Several transaction log backups (tlog set1)
Full Backup of file1 of filegroup1
Several transaction log backups (tlog set2)
Full Backup of file2 of filegroup1
Several transaction log backups (tlog set3)
Full Backup of file1 of filegroup2
Several transaction log backups (tlog set4)
Full Backup of file2 of filegroup2
Several transaction log backups (tlog set5)
tail log backup

The guidance I found online was that to restore file1, I will need to restore file1 backup, then roll forward tlog sets 1-5 and the tail log.

However, lets say that I need to restore the entire DB in minimal time, can I use the following sequence :

Restore file1 filegroup1
restore tlog set2 --roll forward to file 2 backup
restore file2 filegroup1
restore tlog set3
restore file1 filegroup2
restore tlog set4
restore file2 filegroup2
restore tlog set5
restore tail

would I have to do

Restore file1 filegroup1
Restore tlog set2 -- roll forward to current time
Restore tlog set3
Restore tlog set4
Restore tlog set5
Restore tail

Restore file2 filegroup1
Restore tlog set2 -- roll forward to current time
Restore tlog set3
Restore tlog set4
Restore tlog set5
Restore tail

...

bringing each file online before moving on the next file (or maybe even just use the full backup then applying all the log backups)? Wasn't able to find a definitive answer.

Additionally, is it necessary for me to restore the initial full backup, since the file backups cover the entire database?

The documentation examples restore just one filegroup, which is restore the full file backup then roll forward through every log bringing the filegroup up to date. The case I'm wondering about is if you are restoring multiple file backups, do you have to roll each file forward to the present, or can you restore the first file backup, roll transaction logs to the second file backup, then roll forward to the present. I want to minimize the number of times I have to apply a log backup.


texts: Performance when using truncated VARCHAR as index in MySQL

texts:
According to the answer of choice of this question: https://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes

  CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))
  Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

I don't think so. I think when query with a string longer than 50 chars, MySQL will first use the first 50 chars and index to get a match list (which generally will be a very short list), then search in that list without index.

Is it true that MySQL won't use index at all when the query string is longer than 50 chars?


texts: What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions?

texts:
What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions with a condition that is not referring to the partition key?
Pseudo-schema:
My table contains
ID, GroupName, IndividualName, SomeComputedValue, SomeType, UpdatedAt

the partition key is GroupName.
IndividualName is a search key.
SomeComputedValue is the value that will be updated across different groups.
SomeType is an additional search parameter

I know Searching via GroupName would be faster, but what if my data end up requiring to search for IndividualName plus SomeType?
Is there anything wrong if I end up requiring to update SomeComputedValue across 10 to 100 partitions, with 1 to 10 million rows each?
Should an index be used or avoided?
My search would be something like IndividualName, SomeType in the update statement. But after that most of the time it will be a GroupName search for select and no more update.
I feel like the index would be more detrimental.


texts: Is Synonyms to tables cost effective within SQL Server 2016

texts: For one of our new projects we are thinking of using lots of Synonyms to tables which refer to another Database and we are fully aware on what it does and how we are going to use them. But what we don't know is, are there any hidden overheads on overall SQL server performance? If not at all.


texts:
I am unable to import (.bson) file on my computer to MongoDB database .I have been trying below commands :

1)mongorestore -d demo -c dcoll C:\data\dump\twitter\tweets.bson

and

2)mongorestore -d demo -c dcoll /dump/twitter/tweets.bson


texts: Could I realistically use SQL Server Express as a distributor database for transactional replication?

texts:
We have a group of users within my company that are running reports directly against our production database.  It has gotten to the point where they are hammering it enough to cause performance issues for the rest of the company.

I'd like to setup a reporting database for them within our local data center.  Our production database is located in a different data center across the country.  Due to the costs I cannot spin up another SQL Server database in the data center that hosts our production database.

In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database?  Is this remotely feasible?


texts: Transaction Log backup overwrites full backup on same media

texts:
I have a database that is currently backed up every two hours using a SQL Agent job. This is a secondary backup, done in addition to the hosting provider's backup strategy. I created a procedure (stored within master) to perform the following actions on a scheduled basis in SQL Agent:

If the backup file doesn't exist, create it and perform a full (copy only) backup
If the backup file does exist, perform a transaction log backup

The code creates a backup file with a weekly date stamp (e.g. 2017_Week37_myDbName.bak), then drops and re-creates a new file each week.

The code is below:

ALTER PROC [dbo].[e4_backupDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar(4));
    DECLARE @weeknumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar(2));
    DECLARE @backupFileName nvarchar(max) = @year + '_Week' + @weeknumber + '_' + @mydbname; -- '2013_Week37_myDbName'
    DECLARE @backupphysicalpath nvarchar(100) = N'C:\SqlScheduledBackups\';
    DECLARE @fullpath nvarchar(150) = @backupphysicalpath + @backupFileName;
    DECLARE @backupName nvarchar(200);

    IF NOT EXISTS (SELECT * FROM sys.backup_devices WHERE (name=@backupFileName))
        BEGIN

            PRINT 'Creating backup device and performing full backup...';

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, INIT, NAME=@backupname, STATS=10;

            -- delete the previous backup file
            DECLARE @previousBackupName nvarchar(200);
            SELECT
                  TOP 1 @previousBackupName = o.name
            FROM
                sys.backup_devices o
            WHERE
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

            IF @previousBackupName IS NOT NULL
                BEGIN
                    EXEC master.dbo.sp_dropdevice @logicalname = @previousBackupName, @delfile='delfile';
                END
        END
    ELSE
        BEGIN
            PRINT 'Performing transaction log backup...';
            SET @backupname = @myDbName + '-Transaction Log';
            BACKUP LOG @mydbname TO @backupFileName WITH COPY_ONLY, COMPRESSION, INIT,  NAME=@backupname, STATS=10;
        END
GO

After a few hours, if I open the backup media in SSMS and view its contents, I would expect to see:

A full database backup for @myDbName
The most recently-taken transaction log backup

Instead, it appears that the transaction log backup is overwriting the full backup.  When I view the media contents, I only see the transaction log.  I know that INIT might trigger this behaviour, but MSDN implies that if the NAME doesn't match, then it shouldn't get overwritten.

Why is the full backup getting replaced then please?  I am trying to achieve a full backup once-per-week, then a transaction log backup every 15 minutes.


texts: Definition and declaration of a PRIMARY KEY for a temporal table

texts:
My teacher gave us an example bitemporal table holding rows related to medical records to test our homework on. The table has the columns that follow:

first name
last name
parameter
value
unit
valid start time (When parameter/value started being true e.g patient diagnosed with Fever)
valid stop time (When parameter/value stopped being true e.g patient Diagnosed as cured from Fever)
transaction time
transaction stop time (When row was deleted (null if wasn't))

Said table stores, e.g., the following data:

I think that the PRIMARY KEY (PK) is (first name, last name, parameter, transaction time).

The problem here is that there are many rows (almost half a table) where these four columns are equal. For Example:

What this means is that two rows were INSERTed at the same time for patient 'Sima Nice' regarding the parameter 'Tetracyclin'. The only thing that's different are the valid time values (because it should be an UPDATE of course).

Question

I don't understand how that makes sense. I told my teacher I think it's a mistake but he insists that (a) it's ok, that (b) there are scenarios where this can happen and that (c) the PK of this table should include the valid time.

So, what do you say: Is this situation right?


texts: Periodically update a table with new records from another server

texts:
I have databases on two different servers. I need to regularly retrieve new records from a table on server A, and process them in order to use them to update a table on server B (with a different schema).

I was going to use a trigger for this, but if this fails, the inserts on server A are rolled back. The inserts on table A must not fail, so the update of server B needs to be as decoupled from this as possible.

I am now thinking of using a scheduled stored procedure on server B to retrieve the results from server A and update server B. This would need to run every 30 seconds.

Is there anything wrong with this approach, or is there a better or more 'correct' way of achieving this?


texts: Convert SQL Server .ldf + .mdf into MySQL scripts

texts: I recently took a SQL Server backup and extracted an .ldf and .mdf from it successfully. I'm curious if there is a trivial way to take both of these files/backups and convert them to a .sql that I can use to import into a MySQL database?


texts: Password of the oracle user in oracle linux?

texts: I am trying to install oracle 12c on my virtualbox oracle linux 6.5. I ran the command oracle-rdbms-server-12cR1-preinstall , which created a user oracle . But I can't seem to login to the user cause I don't know the password. What is the password ?


texts: In what scenario would I suddenly need to recompile a Stored Procedure (SQL Server 2014 ENT)

texts:
I had an issue where a remote procedure call (from .net) for a specific parameterized stored procedure suddenly would end in a time out. This was the case for only some parameters being set not all. (typically most parameters passed to the stored procedure are null, except for 1 or 2 parameters, which have string values).

This happened suddenly. The stored procedure has not changed in ages. Indexes are well maintained for all tables in the database. I resoled the issue by adding WITH RECOMPILE to the stored procedure. But I am concerned, I want to know what triggered this behavior.

My question is:

What is/are likely causes of this and/or what potential issues
should I be looking for?
Do I need to to check statistics are
healthy (I let SQL Server take care of this for me).
Is there a way
(and should I even be doing this) to pull a list of stored
procedures that are at risk of this sudden broken behavior.

Thanks!


texts: SSIS Script Component as Source AddRow Null Exception

texts:
I have a SSIS package with single Dataflow Task.  The first step in this Dataflow is a Script component setup as a source, the default 'Output 0' has been renamed to 'Out' and it has a single column 'UserID' of W_STR, length 11.  It is being routed to an OLEDB Destination table with a single column called [User ID] of type NVARCHAR(11).  The mappings are set.

When calling OutBuffer.AddRow() I get a Null Reference Exception.  I've deleted the script component and re-created it several times, removing all other logic to the point that the script component looks like this:

public override void PreExecute()
{
    base.PreExecute();
    /*
     * Add your code here
     */
}

/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
    base.PostExecute();
    /*
     * Add your code here
     */
}

public override void CreateNewOutputRows()
{
    /*
      Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
      For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
    */
    OutBuffer.AddRow();
    OutBuffer.UserID = "1234567";
}

Yet still it fails on AddRow()...  I'm using SQL Server Data Tools 2015 with my project in package deployment mode and the target SQL version set to SQL 2012.

  at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.AddRow()
   at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.AddRow()
   at OutBuffer.AddRow() in c:\Users\oka2bbf\AppData\Local\Temp\Vsta\a20c6c234a834b8e924d02b641c4b06d\BufferWrapper.cs:line 45
   at ScriptMain.CreateNewOutputRows() in c:\Users\user\AppData\Local\Temp\Vsta\a20c6c234a834b8e924d02b641c4b06d\main.cs:line 111
   at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap) in c:\Users\user\AppData\Local\Temp\Vsta\a20c6c234a834b8e924d02b641c4b06d\ComponentWrapper.cs:line 49
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

Edit: If I change the project to SQL 2014 or SQL 2016 it works.  It seems to be an issue with SQL 2012, however I'm deploying to SQL 2012, so I need to have it set for SQL 2012.  I have Development Edition of SQL 2012 installed (the only SQL instance I have installed locally) and as I'm using SQL Server Data Tools 2015 to edit the package.


texts: SQL Formatting: Add Space within query result

texts:
I need to insert a space into result set from a query, I tried using the space() function and right but I have confused myself.

The Column result from the query is:

1234567891234567891

I need this:

1234 567891234567891 (With a space after the 4th number)


texts: Oracle query to count and group specific field

texts:
I have the following example table:
FIELD1     FIELD2      FIELD3
12345      888555      009999
12345      888555      327777
12345      888555      327777
54321      999666      334444
54321      999666      324444
55501      999333      005555
55501      999333      350000
55501      999333      890000
55501      999333      320000

Now, assuming FIELD1 and FIELD2 have always the same value, e.g. if FIELD1 = 12345 then FIELD2 will be always 888555, what I have to do is make a query where I count FIELD1 and FIELD2 occurencies when the first 2 digits of FIELD3 = 32 and when the first 2 digits of FIELD3 IS NOT 32.
In above case I should have as output:
FIELD1  FIELD2    FIELD3    COUNT
12345   888555    32            2
54321   999666    NOT32         1
54321   999666    32            1
55501   999333    32            1
55501   999333    NOT32         3

This query should do what I need:
select FIELD1,
       FIELD2,
       SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 1 ELSE 0 END) as FIELD3_EQU_32,
       SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 0 ELSE 1 END) as FIELD3_NEQ_32
from TABLE where and FIELD3 IS NOT NULL
group by ORIG_ADDR, ORIG_IMSI order by ORIG_ADDR;

What I need now is output only a the fields with counter who exceeded a specifc threshold plus a part of FIELD2.
Something like:
HAVING ((FIELD3_EQU_32 > '10' and SUBSTR(FIELD2, 1,3)='888')
or (FIELD3_NEQ_32 > '5' and SUBSTR(FIELD2, 1,3)='999')
or (FIELD3_NEQ_32 > '5' and SUBSTR(FIELD2, 1,3)='777')
or (FIELD3_NEQ_32 > '15' and SUBSTR(FIELD2, 1,3)='123')
etc...etc...)

Could someone help me to achieve that?
Thank you very much
Lucas


texts: Make the SQL Database manage CRUD operations

texts:
Is it possible to have a client app send a transaction of INSERTs to a table. Then have the table triggered somehow to run through the inserts and either INSERT the row or UPDATE an existing row?

If it is possible how would this be done on Azure Sql?


texts: InnoDB create table error: "Row size too large"

texts:
We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing that?"; let's just assume that we are addressing this).

We use MySQL 5.1.48 Community RHEL5 with InnoDB plug-in 1.0.9 on Linux.

When using MyISAM we never encountered table size limits of max columns or max row length (during investigation we have hit the max columns limit at 2598 (the 2599th causes error 1117). With InnoDB we are hitting limits. These limits are manifesting when creating the table (no data insertion) as:

  ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

I am looking for answers to the following:

What is the detailed formula for determining row size particulalry when using lot's of v/v/b/t columns? I've tried a few different formuals using varchar(N) columns (where N is between 1 and 512), the UTF8 charset (*3), and as many columns as the table will take until failure. None of the combos I've tried give values that match with actual test results.
What other 'overhead' must I consider when calculating the row size?
Why does the error message change from 8126 to 65535 when I go from creating tables with varchar(109) columns to varchar(110) columns?


texts:
create table dbo.CourseMaster
(
  CourseId char(2),
  CourseName char(3)
);

create table dbo.StudentMaster
(
  ROLLNO char(5),
  NAME varchar(10),
  ADDRESS varchar(20),
  Course varchar(100)
);

insert into dbo.CourseMaster values
('01', 'ABC'),
('02', 'DEF'),
('03', 'GHI'),
('04', 'JKL'),
('05', 'MNO'),
('06', 'PQR'),
('07', 'STU');

insert into dbo.StudentMaster values
('12345', 'RAM',   'RAM ADDRESS',   '01,02,02'),
('25695', 'HARI',  'HARI ADDRESS',  '02,06'),
('89685', 'JEFF',  'JEFF ADDRESS',  '03,05,06,07'),
('47896', 'DAISY', 'DAISY ADDRESS', '03');

Query 1:

select SM.ROLLNO,
       SM.NAME,
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from dbo.CourseMaster as CM
       where ','+SM.Course+',' like '%,'+CM.CourseId+',%'
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;

Actual Ouput:

| ROLLNO |  NAME |       ADDRESS |          Course |
|--------|-------|---------------|-----------------|
|  12345 |   RAM |   RAM ADDRESS |         ABC,DEF |
|  25695 |  HARI |  HARI ADDRESS |         DEF,PQR |
|  89685 |  JEFF |  JEFF ADDRESS | GHI,MNO,PQR,STU |
|  47896 | DAISY | DAISY ADDRESS |             GHI |

When I add course like 01,02,02 in student master it give below output:
ABC,DEF and it should give: ABC,DEF,DEF


texts: How to have remote standby PostgreSQL server in different location (AWS)?

texts: We have a primary PostgreSQL 9.4 server running multiple instances in our DC. It is using Continuous Archiving via WAL-E out to AWS S3. I would like to have a hot/warm standby server in our VPC within AWS EC2 that would be ready to take over should we need to DR. I can recover the server via WAL-E but can't seem to get it to work in hot_standby mode. What could I be missing? Docs I read seem to suggest that the standby needs to talk to the primary? Is this so? Is this required when log-shipping via WAL-E?


texts: better approach to design company hierarchies in table

texts:
not sure if I'm in the correct forums, but what I wanted to ask is how would you implement a hierarchies in database terms?

for example, a person belongs to a service, and then to a team, and then to a section, and then to a channel, and then to a division.

so to summarize, a person will have these values associated to him/her:
service > team > group > domain > company.

I think the better approach is to have different tables for each category, so there will be 5 tables for the categories and 1 table for the person entity that will hold the relationship to the appropriate categories to a person. so the person table will have the columns serviceId, teamId, groupId, domainId, companyId which are all FK to the category tables.

or do you think this approach is better. have all the categories be placed in one table, and be differentiated by a TYPE field which will identify what type of category it is. also the hierarchy needs to be declared as well. so there would a level column that will identify which is the parent of which category.

which design do you think is better, in terms of optimization, operationability and complexity?


texts:
In our application we have the following tiers for data processing:

.NET orchestration application that works out which SSIS packages to fire
SSIS packages that do much of the work and call Stored Procedures
Stored procedures to perform calculations and read/write data where SSIS data flows are not appropriate

One stored procedure runs in 40 seconds if executed from SSMS, and approximately the same time if the SSIS package is called manually.

If however, the SSIS package is called by the .NET app, then the query takes more than 14 hours to run, and flat lines all processors.

Looking at the execution plans, it seems that if the procedure is run from SSMS or the SSIS package is fired manually and then calls the procedure, SQL Server uses a sensible plan based on current statistics.

However, if the SSIS package is called from the .NET app, then SQL server create a new plan, using totally the wrong statistics. The following things made no difference:

Updating stats
Clearing the cache
Restarting the instance
Setting WITH RECOMPILE on the procedure execution

We fudged resolution by using a MAXDOP hint in the query, but my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?


texts: MySQL Database replication for high availability

texts:
configuration:

1 balancer
2 Azure machines(4 gb ram, 2 cores), with MySQL master-master replication enabled.
X users connecting to the balancer to sync data.

test:

i've tested the MySQL replication with loader.io (for a connection spike), for 6000 calls for 1 min to the balancer  (100 conn per 1 minute test), and with the current setup both dbs sync in about 6-7 mins (all 6000 syncs)

scenario:

A client should sync it's data from last connection no matter what machine the balancer distributed his connection to, and that db for example might not have finished the previous sync period yet (let's say the 6 minute, as in the above example).
I guess a sticky balancer solution might not help(Nginx, HAProxy) and Azure balancer is not persistent\sticky anyway (for example user is connected to 3G, 4G and IP changes frequent)..

problem:

how do i get the current replication sync the fastest way possible between both machines?
is there a way to transform my current setup to synchronous replication?
is there any other solution (preferable windows OS) that does synchronous sync (like: semisync replication )?


texts: Get latest timestamp from column in given table dynamically

texts:
I want to get last timestmap from a table with the column updated in PostgreSQL.
And I want a function to do that. So I can pass the table name as parameter and the function returns the latest timestamp in column updated.
My table:

Query used:
select max(updated) from mytable;


texts: Create a user account login that cannot login from SQL Server Management Studio

texts:
Can we create a new user that can access the database, but cannot access it via SQL Server Management Studio (SSMS)?

I need this to create a "safe" connection to the database through VBA of Office application, but in case the username and password is leaked, users still cannot connect through the SSMS. I made the user as data reader only, but for peace of mind, I want to make sure users cannot access it via SSMS, so that unwanted viewing of data may be prevented.

I'm using SQL Server 2008 R2. Please kindly help or share if there are other ways to do this.


texts:
I'm currently attempting to define a new user using the following:

mysql> GRANT ALL PRIVILEGES ON simple_cms_development.* TO 'br'@'localhost' IDENTIFIED BY 'password';

However, it is unsuccessful and outputting:

  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 'IDENTIFIED BY 'password'' at line 1

I am running MySQL 8.0.11.


texts: How do I rename my local SQL development server?

texts:
I'm a dev who inherited a mostly functioning box doing most of what I need. Except for the machine name is still that of the old dev (we name it "{username}-dt" or "{username}-lt" for ease of id on the network) and I want to rename it from old-username to my-username.

Naturally this will affect SQL as well, so I thought I would ask for more experienced advice on what I need to do before I rename my machine. I know there are some "sp_" sprocs to be run, but when do I run them? Do I need to restart my box before or after, and do I need a certain level of privilege? Will it destroy any existing windows-based auth on the box (those accounts are all AD accounts anyways)?


texts: Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum freeze, etc.), what happens to the txid?  Does it just continue to increment? If so, how do xmin and xmax store it?


texts: Is there a way to determine if a certain query need to have an index?

texts:
I'd like to know if there is a command in mysql (phpmyadmin) to determine if a certain query needs an index.

For example I have this kind of query:

SELECT name FROM table_name WHERE lastname = 'jobs' ORDER BY date_joined DESC

Then, when I run this query, a message will pop up and say "You can add an index to column_name to increase the speed".


texts: Sql to get Status if it has more than one value

texts:
Please let me know how can I bring back only the category and product only if all the status for the CATEGORY is Inactive, So if I have below:

CATEGORY  PRODUCT  STATUS
10141     10094    Active
10141     20278    Active
10216     10163    Active
10217     10164    Inactive
00399     00308    Active
00399     01974    Inactive
00992     00822    Inactive
00992     01399    Inactive
01720     01970    Inactive

I want to bring back:

CATEGORY  PRODUCT  STATUS
10217     10164    Inactive
00992     00822    Inactive
00992     01399    Inactive
01720     01970    Inactive


texts: single node stuck in WSREP has not yet prepared node for application use

texts:
I have a four node galera mariadb cluster based on mariadb 10.3 that has been running for about a year.

For unknown reasons a particular node tends to fall out of the cluster and is unable to rejoin automatically about once every 3 months.

Most attempts at recovery fail and I have taken to using the manual method described at Manual SST of Galera Cluster Node With Mariabackup to get it back online.

That method no longer works.

After manual recovery the node gets stuck reporting 'WSREP has not yet prepared node for application use' in response to any SELECT query.

Here is a log of an example recovery after performing the manual steps and then restarting the node:

2019-11-26  3:51:44 7 [Warning] WSREP: Gap in state sequence. Need state transfer.
2019-11-26  3:51:46 7 [Note] WSREP: Prepared SST request: mysqldump|10.4.44.86:3360
2019-11-26  3:51:46 7 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2019-11-26  3:51:46 7 [Note] WSREP: REPL Protocols: 9 (4, 2)
2019-11-26  3:51:46 7 [Note] WSREP: Assign initial position for certification: 29059601, protocol version: 4
2019-11-26  3:51:46 0 [Note] WSREP: Service thread queue flushed.
2019-11-26  3:51:46 7 [Note] WSREP: IST receiver addr using tcp://172.24.0.34:4568
2019-11-26  3:51:46 7 [Note] WSREP: Prepared IST receiver, listening at: tcp://172.24.0.34:4568
2019-11-26  3:51:46 0 [Note] WSREP: Member 0.0 (galera) requested state transfer from 'galera3'. Selected 3.0 (galera3)(SYNCED) as donor.
2019-11-26  3:51:46 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 29059601)
2019-11-26  3:51:46 7 [Note] WSREP: Requesting state transfer: success, donor: 3
2019-11-26  3:51:46 7 [Note] WSREP: GCache history reset: 57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:0 -> 57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:29059601
2019-11-26  3:51:46 10 [Warning] IP address '10.4.44.82' could not be resolved: Name or service not known
2019-11-26  3:51:47 0 [Note] WSREP: (0f639e64, 'tcp://0.0.0.0:4567') turning message relay requesting off
2019-11-26  3:51:49 17 [Note] WSREP: SST received: 57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:29057993
2019-11-26  3:51:49 17 [Note] WSREP: wsrep_start_position set to '57b37aa2-d111-11e8-a015-ab6cf5f3b3ea:29057993'
2019-11-26  3:51:49 7 [Note] WSREP: Receiving IST: 1608 writesets, seqnos 29057993-29059601
2019-11-26  3:51:49 0 [Note] WSREP: 3.0 (galera3): State transfer to 0.0 (galera) complete.
2019-11-26  3:51:49 0 [Note] WSREP: Member 3.0 (galera3) synced with group.
2019-11-26  3:54:00 0 [Warning] WSREP: Protocol violation. JOIN message sender 3.0 (galera3) is not in state transfer (SYNCED). Message ignored.

SELECTs return ''WSREP has not yet prepared node for application use' and the system remains in the state permanently

If I keep an eye on the doner node it goes into the donor state for a while and then returns to the synced state.

The other 3 nodes, including the donor are all working correctly, at least as far as I can tell.  The other 3 are willing to run SELECT queries.

Any ideas on how I might resolve this issue?


texts: Can queries containing deterministic user defined functions be cached in mysql query cache?

texts:
According to the mysql 5.6 docs:

  A query also is not cached under these conditions:
  ...
  It refers to user-defined functions (UDFs) or stored functions.

However, I have seen claims that if the UDF is completely deterministic, the query can in fact be cached.  Can someone confirm or deny that?  If indeed UDFs do prevent caching, is there any smarter way to allow the queries to be cached than to simply inline the UDF into each query?  (That would work, but would obviously be cumbersome.)

Edit: Also, is the behavior different for stored functions vs user-defined functions written in C?


texts: How can I get all CREATE VIEW statements in Teradata?

texts:
How to get all CREATE VIEW statements with a single query?
This Query is used to get the definition of a single view:
show view db.tablename;


texts: Why isn't the objectname and indexname populated in deadlock graph?

texts:
I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier.

Traceflag 1222 wasn't enabled and the server owners did not want to enable it either. I used extended events and pulled out some info using the following query:

    SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )

I received a lot of info back that looked like this:

<deadlock>
  <victim-list>
    <victimProcess id="process14846ccbc8" />
  </victim-list>
  <process-list>
    <process id="process14846ccbc8" taskpriority="0" logused="0" waitresource="KEY: 25:72057594099728384 (0800dfc12261)" waittime="2842" ownerId="7035766745" transactionname="UPDATE" lasttranstarted="2012-11-20T07:50:25.023" XDES="0x14d9ade3b0" lockMode="U" schedulerid="8" kpid="17268" status="background" spid="19" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="53" stmtstart="2674" stmtend="2980" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process3d9bdc8" taskpriority="0" logused="224" waitresource="KEY: 25:72057594099793920 (2f00faaf5d8e)" waittime="2315" ownerId="7035765940" transactionname="user_transaction" lasttranstarted="2012-11-20T07:50:24.913" XDES="0xdad541970" lockMode="U" schedulerid="6" kpid="15448" status="background" spid="23" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="549" stmtstart="32736" stmtend="33274" sqlhandle="0x03001900b9667f2bc41ade003ea000000100000000000000" />
        <frame procname="" line="549" stmtstart="34038" stmtend="35374" sqlhandle="0x03001900f28a732c051bde003ea000000100000000000000" />
        <frame procname="" line="81" stmtstart="4284" stmtend="4392" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594099728384" dbid="25" objectname="" indexname="" id="lock6b6a4ba00" mode="X" associatedObjectId="72057594099728384">
      <owner-list>
        <owner id="process3d9bdc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process14846ccbc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594099793920" dbid="25" objectname="" indexname="" id="lock74edcf180" mode="U" associatedObjectId="72057594099793920">
      <owner-list>
        <owner id="process14846ccbc8" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process3d9bdc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

How can I found out what the deadlocks are if objectname="" indexname="" ?


texts: Set of values of a field of a related entity must be unique: How do I express this constraint in SQL?

texts:
Given the following three tables:

Type( id int primary key,
      key varchar )

GroupOfInstances( id int primary key,
                  name varchar )

InstanceOfType( id int primary key,
                type int foreign key references(Type.id),
                group int foreign key references(GroupOfInstances.id) )

As seen from GroupOfInstances, I want the set of key values to be unique. I can't make key unique on Type.

To put it another way, I want all keys assigned to a GroupOfInstances through InstanceOfType to be unique for each GroupOfInstances.

How do I do that? I'm using MariaDB 10.1. I can't upgrade to >=10.2 where check constraints are enforced for stupid large enterprise reasons.

Edit: To better illustrate the question I will add examples.

This should be allowed:

INSERT INTO Type (id, key) VALUES (1, "key"), (2, "key"), (3, "otherkey")
INSERT INTO GroupOfInstances (id, name) (1, "group")
INSERT INTO InstanceOfType (id, type, group) VALUES (1, 1, 1), (2, 3, 1)

Because for GroupOfInstances with id 1, they keys are unique: "key" from Type 1 and "otherkey" from Type 3.

This should not be allowed:

INSERT INTO Type (id, key) VALUES (1, "key"), (2, "key"), (3, "otherkey")
INSERT INTO GroupOfInstances (id, name) (1, "group")
INSERT INTO InstanceOfType (id, type, group) VALUES (1, 1, 1), (2, 2, 1)

Because now, for GroupOfInstances 1, the keys are not unique: "key" from Type 1, and "key" again from Type 2. This INSERT should be rejected by the database.


texts: Issues Setting Up SQL Server - Multi Server environment

texts:
I am trying to Configure the Master target server / Multi server environment in Sql Server 2012.
I changed :

MSXENCryptChannnelOptions-->Changed from 2 to 0
AllowDownloadedJobsToMatchProxyName - changed from 0 to 1 on the target

When I run the wizard I am getting below error

MSX Enlist failed for Job Server 'MasterServerName'
The enlist operation Failed(Reason:SQL Server Agent Error: Unable to connect to MSX 'MasterServerName'(Microsoft Sql Server, Error : 22026)

They both servers SQL Agents are running on the same windows service account.
Any Suggestions on how to fix this?
Adding the Log:
Enlist TSX Progress

Create MSXOperator (Success)
Checking for an existing MSXOperator.

Updating existing MSXOperator.
Successfully updated MSXOperator.

Make sure the Agent service for 'Test3' is running (Success)
The service 'SQLSERVERAGENT' is running.

Ensure the agent startup account for 'Test4' has rights to login as a target server (Success)

Checking to see if the startup account for 'Test4' already exists.
Login exists on server.
Checking to see if login has rights to msdb.
Login has rights to msdb.
Checking to see if user is a member of the TargetServersRole.
User is a member of the TargetServersRole.

Enlist 'Test4' into 'Test3' (Error)

Enlisting target server 'Test4' with master server 'Test3'.
Using new enlistment method.
Messages
MSX enlist failed for JobServer 'Test4'.  (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'TEST3'.) (Microsoft SQL Server, Error: 22026)


texts: How can I tell whether my SQL Server instances are patched? Is there native functionality that will identify whether there are patches available for my server? Heck, do I even have version data available to me?


texts:
I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours only for this table:

CREATE TABLE `ReferenceWaves` (
  `ReferenceWave` datetime NOT NULL COMMENT 'DateTime of the Reference Wave',
  `InstrumentId` int(11) NOT NULL,
  `Triggered` datetime DEFAULT NULL COMMENT 'When it was triggered'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Assures FAFWE doesn''t use this reference wave when restarted';

ALTER TABLE `ReferenceWaves`
  ADD UNIQUE KEY `ReferenceWave` (`ReferenceWave`,`InstrumentId`);

Then running this command

SELECT COUNT(*) FROM MyISAM_ElliottWavesCore.ReferenceWaves
      WHERE ReferenceWave = '2018-04-02 17:06:00' AND InstrumentId = 73

does not return from the mysql_query() C function. This occurs more or less once in 12 hours - in the meantime around 10'000 queries like this have been executed without any problem.

Running SELECT * FROM information_schema.PROCESSLIST WHERE ID=6981; - where ID is the thread id for the query - returns this:

ID      USER    HOST                                      DB    COMMAND TIME STATE  INFO
6981    FAEWE   aaeb-app206ly.aaeb-holding.local:52492  (null)  Sleep   1836        (null)

After around 5 hours this thread apparently is killed automatically by MySQL and not visible anymore in the information_schema.PROCESSLIST.

The table MyISAM_ElliottWavesCore.ReferenceWaves has ~4'800 rows and the total size - including the index - is <150KB. I have tables which have million of rows and have size > 1GB but there is never this issue - it is only in this table and very unpredictable. You can imagine how frustrating it is.

Any idea what is going wrong?

Technical data:

SLES 12.3 running on XenServer 7.4 as VM
compiled with gcc 7.3 using -std=c++17 and almost every warning option.
using glibc 2.27 which is separately linked into the project because SLES 12.3 uses 2.22
MySql Community Server 5.7.21
C API client 5.7.19


texts: Problem with mysql group_concat, complex query, not getting required result

texts:
I have 5 tables

college
trade
district
contact
intake

Their relationship looks like this

I'm having problem with the contact table. In this table, cid and location are the primary key. It can have only two location campus and office for each cid i.e. college id. Table intake connects college and trade table.
With this query below, I'm getting results twice for each address.
This is my query:

SELECT cname, ctype, tname, seats, hostel, new, dname, address, ph_no, email, website
FROM college, trade, district, intake, contact
WHERE intake.cid = college.cid and intake.tid = trade.tid and college.did = district.did and contact.cid = college.cid
ORDER BY cname ASC;

My current result:

Result that I want:

I know this can be achieved by grop_concat and concat function but I don't know how to use these functions in this situation. Somebody please help me to build the query to achieve this result.

Thank you.


texts:
I have a doubt with the use of TRUNC function in Oracle. As read in the documentation:

  The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt

I tried this code on my Oracle client:

SELECT
    TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH:MM:SS AM') "Today's Date and Time"
from DUAL;

But all what I get is this: 10/26/2017 12:10:00 AM

It isn't supposed to be 10/26/2017 00:00:00 AM?

By the way, the SYSDATE is this: 10/26/2017 10:10:05 AM

Thanks in advance


texts: How to convert sql server table data into MS Access(.mdb) file

texts: I have SQL Database table with 6664055 rows, I want to convert this SQL Server table data to MS Access format(.MDB), How Can I do this?


texts: How can I permanently remove replication by script if I only have access to the subscriber?

texts:
We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a publisher DB which replicates to a handful of subscribers, so that if the publisher goes down, we can promote a subscriber to be the new publisher. As is, this is a tedious manual process, but we want to start scripting elements of it so that the process is quicker and closer to being automated.
What we're currently wanting to do is make a script for subscribers to disable/remove the subscription to the old publisher if the old publisher goes down. This is due to the chance that DBs sometimes come back online after being offline for some time - in this scenario, we don't want it to have any further replication impact on the subscribers as this risks data inconsistency. We'd likely clean-slate the old publisher, so it's not an issue if its replication setup is throwing errors.
I've tried sp_subscription_cleanup which briefly shows the subscription as gone, but it comes back and data continues to be distributed to it from the publisher. I've found an MS doc on this, but that assumes access to the publisher.
So is there a script approach to permanently kill the subscription just from the subscriber?
Additional information
I note that you can right click on the subscription and delete it there, but I can't see from that menu the script that it's running. The subscription has also returned.


texts: Selecting row by field and exact list of join id

texts:
I have 2 tables. For simplicity, first table Parent contains only id and name. Other table Child contains id and parent_id.

There are going to be many parent rows with the same name. They will have different childrens, so I want to be able to select a parent row only if it has childs from the list I provide.

Let's say I want a Parent with name 'test' only if it has Childs with id (223, 224, 225).

So far I achieved this with following query:

SELECT p.*, GROUP_CONCAT(c.id) as childs
FROM `parent` p
    INNER JOIN `child` c ON p.id = c.parent_id
WHERE p.name = 'test'
GROUP BY p.name
HAVING childs = '223,224,225'
ORDER BY c.id

However I think it's not a good solution if it uses id concatenation


texts: Does dropping a Primary Key constraint from a Partitioned Table break the partition?

texts:
We have a table partitioned on a date column. The Primary Key for the table is a composite key of this date column and 4 other columns.

We are thinking about dropping the Primary Key constraint from this table and simply creating a new non-clustered index.

Is the partitioning affected if we drop the Primary Key constraint since the partitioning column is part of the Primary Key?


texts: Unique Index much bigger than the table itself

texts:
Pg 9.3 on debian 7.

I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small varchar, some timestamps and integers. I need to prevent a group of 5 columns to be inserted repeated, so I created an unique index on them.
The problem is that the index is 34GB now. My questions are: How can an unique index of 5 columns in a table of 9 be bigger than the full amount of data itself? Will this index ever be used, cause I guess it's cheaper for the DBMS to scan the entire table instead. And is there any way to turn this index smaller or maybe have another solution to implement an unique constraint?

id_process_publication bigint
search_token character varying(25)
publication_date date
publication_page integer
id_publication_site integer
id_publication_book integer
insert_time timestamp
integration_consume_time timestamp
elastic_consume_time timestamp

Indexes:
"process_publication_pkey" PRIMARY KEY, btree (id_process_publication)
"idx_uniq_process_publication" UNIQUE, btree (search_token, publication_date, publication_page, id_publication_site, id_publication_book)
"idx_uniq_process_publication_where_elastic_consume_time_null" UNIQUE, btree (id_process_publication) WHERE elastic_consume_time IS NULL
"idx_uniq_process_publication_where_integration_consume_time_nul" UNIQUE, btree (id_process_publication) WHERE integration_consume_time IS NULL


texts: How to trim a string by finding multiple words

texts:
I want to trim a string if it contains certain words or characters. This is just part of a big stored procedure.

I want if v_selection_name contains any of those characters .eg: ' (RES)' or ' (R)' remove these characters.

For example :

`Micheal Jackson (RES)`    => SHOULD BE =>  `Micheal Jackson`

The way I currently do it is this and it is working:

if v_sport_id IN (1, 121225) then
    -- remove word reserved from selection name to make match possible
   SELECT TRIM(TRAILING ' (NEEDS SUPPLEMENTING)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' (RES)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' (R)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' N/R' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' %' FROM v_selection_name) INTO v_selection_name;
end if;

Is there any other better way to do this?

The string will only ever contain one substring to be trimmed.

Does only one substring to be trimmed is contained in a field/variable value? Or there are values like 'something (R) (RES)' which must be trimmed twice?


texts:
I'm using the MD5 hash function of MySQL to hash some data I import into the database from a CSV file.
The issue is that some lines have an incorrect hash (but not all of them).
When I use a query:

UPDATE mytable SET email_md5 = (SELECT MD5(email))

MySQL inserts some wrong hashes in the table but when I check with using

SELECT MD5('email@email.com')

I find the correct answer.

Any idea how to check the encoding of the data I import in the database?

UPDATE #1
For this email onachba@i-pcom.com (not existing anymore)
Mysql insert this hash when I using MySQL query (the first in this post) with data in database from the CSV

  c62804a6d7122356cf5f261ebf860684

But when I insert the same email with my hands in database and replay the same query I find this hash (and is the correct hash)

  c31383bbaee83e9704fd7b0ad52581ca

UPDATE #2
We using mysql 8 and adminer
I feel it is a problem of encoding.
When I select the line for edit it from adminer and save it without any changes the browser recognize it like an email (she becomes clickable link with mailto:) + after this when I replay the query he add the good hash ! !
The row don't recognize like an email in adminer give me wrong hash
but
the lines from adminer recognize like an email have the good hash...
This is f*** creazy !

UPDATE #3
For datatype, it's varchar(200) NULL
With another email where I find a MD5 issue
From MySQL with

SELECT MD5(email) FROM mytable WHERE id = 'theID';

  f6cd93fc748b7c856a74e6037f6e14f5

SELECT HEX(email) FROM mytable WHERE id = 'theID';

  627269636540696775616E6573747564696F2E636F6D0D


texts:
I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development environments. If I have a failure and want to use the log backups to restore to a point in time I will also need the backup taken by the developer.

This wouldn't happen if the developers only used COPY ONLY backups. So my question is: Is there a way to restrict users to only be able to perform COPY ONLY backups?


texts: Replacing too many tables with too many columns?

texts:
Background

We have a small MySQL database, which logs numeric data from devices. Each device has ~100 variables.

List of devices is stored in single table. deviceId, type and version are stored here.

Data is stored in tables, which have a name in the form of data_<deviceId>_<variable>. Each of these tables stores basically 2 pieces of data: timestamp and a variable value (double type).

What above means that each time new device is added, system creates ~100 new tables automatically. There are currently several thousand MyISAM tables in the database. Needless to say, making changes to these tables is difficult.

Example

For simplicitys sake, let's say that we have 3 devices with only a few variables:

Device 1 has variables A and B. (Device type 1, version 1)
Device 2 has variables A and C. (Device type 1, version 2)
Device 3 has variables D, E and F. (Device type 2, version 1)

Given above, database has following tables:

devices, with columns (deviceId, type, version)
data_1_A, with columns (timestamp, value)
data_1_B, with columns (timestamp, value)
data_2_A, with columns (timestamp, value)
data_2_B, with columns (timestamp, value)
data_3_D, with columns (timestamp, value)
data_3_E, with columns (timestamp, value)
data_3_F, with columns (timestamp, value)

Problems

Too many tables
New tables are created on the fly

A better way

To reduce the amount of tables, consider following:

Solution #1: Device version specific tables

There would be unique table for each device type. So we'd have tables:

devices, with columns (deviceId, type, version)
data_type_1_version_1, with columns (timestamp, deviceId, valueA, valueB)
data_type_1_version_2, with columns (timestamp, deviceId, valueA, valueC)
data_type_2_version_1, with columns (timestamp, deviceId, valueD, valueE, valueF)

Problem is that we have to add new table each time new device type or version is introduced. We also don't have generic way of reading values from tables as each table has different columns. Won't work.

Solution #2: 1 Data table with generic columns

devices, with columns (deviceId, type, version, varName1, varName2, varName3, ...)
data, with columns (timestamp, deviceId, value1, value2, value3, ...<enough columns for worst case>)

You would need to read devices table to understand what each value column contains. Should work.

Solution #3: Single data column and extra table for variable info.

devices, with columns (deviceId, type, version)
variables, with columns (varId, deviceId, varName)
data, with columns (timestamp, varId, value)

To read data from DB, you need to find out varId first from the variables table, and then read the actual data from the data table. Should work.

Question

Solution 2 seems simpler than 3, as it has less tables to deal with. But using solution 2, I would have 2 tables with over 100 columns each. Is that any improvement over original problem of having too many tables?


texts: Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it

texts:
The standard format for beginning a dialog is like this:

Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE initiator_1
   TO SERVICE 'target_1'
   ON CONTRACT MyContract ;

But I would like to make a flexible generic procedure that can send a particular conversation from any of a number of initiator services (say I have 10 potential initiator services that would be using the same stored procedure).

Create Procedure SendJoinRequest (@initiatorServiceName varchar)
AS
   Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE @initiatorServiceName
   TO SERVICE 'target_1'
   ON CONTRACT MyContract ;

I guess this won't work because the identifier of the service in

FROM SERVICE @initiatorServiceName

actually cannot be a string, and must be the actual name of the service...  Is there an easy way to pass this as an argument? or do I have to nest all of this in a nasty execution of a concatenated string ?

@String = blahblah + blah blah + escapecharacters + blah
execute(@String)

Thanks for your help


texts: How to check if a postgresql backup is finished or not?

texts:
I started my backup yesterday, but now I don't know if it is finished or not.

Can you please help me?


texts: Oracle:Test connection failed because of an error in initializing provider. ORA-12170: TNS:Connect timeout occurred

texts:
We have Migrated SQL server 2008R2 to SQL server 2012 from Windows 2008R2 onto Windows 2012R2.

Everything went well , except for the Oracle database connection:

We have a SSIS package created on SQL server 2008R2(fetches the data from Oracle DB) and while testing its connection (on newly migrated server) with Oracle database (on other server) initially we got the error below, (when oracle database client was 12c on windows 64 bit platform)

  Test connection failed because of an error in initializing provider.
  Oracle client and networking components were not found. These
  components are supplied by Oracle Corporation and are part of the
  Oracle Version 7.3.3 or later client software installation.

  Provider is unable to function until these components are installed.

As per the Link here we came to know that

Because BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client.

Hence , we went with installing both 32 bit and 64 bit Oracle 12 C client :

But after installing, when we tested the connection via SSIS package, we get error saying

"
Test connection failed because of an error in initializing provider. ORA-12170: TNS:Connect timeout occurred"

Since i do not have much expertise on Oracle, need experts guidance on this to fix the issue.

Any suggestions would be very helpful.


texts: Whether or not to create separate tables for different product types?

texts:
I'm in the process of designing a database and I'm having second thoughts about my initial design decisions...

Product types are as follows... Models, parts, replacement part kits and options.

Option A (first design):
I planned on having separate tables for the above product types. I'd say about 75% of the fields would be the same in each table.

I created each product type as separate tables because of the associations I need to create between them. For instance, a Model can have many options and a option can have many models. An option can also have many parts and a part can have many options... and so on...

Option B:
Instead of having separate tables I could create a table called Product that encompasses model, part, replacement part kits and options. I could have one field called type to differentiate between model, options, etc. I suppose a down side is several fields would never be used (left null) for certain product types. I'm guessing this is where "not best practices" would come into play..

Option B would greatly reduce the complexity of the db design. I also wouldn't have to worry about referencing a bunch of tables when pulling out data for queries...


texts: How to disable the logging of parameter values in PostgreSQL

texts:
Is there a way to disable the logging of the parameter values used in a query, while still logging the actual query itself?

I'd like to set log_min_duration_statement = 0 to use with pgBadger. I'm getting two lines per query like:

LOG: Select * from table where id = $1

DETAIL: parameters: $1 = 55

I'd really like to suppress the DETAIL lines because it's bloating my logs and saving sensitive information.

Is it possible to disable this from within Postgres?


texts:
I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query

SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1

is very slow on the view (return in 30~60 seconds), but runs fast on both table.

Both table are using id as primary key. They joins together on id as well. I have the same structure in mysql, and it runs well (return in ms).

Is there anything I should set in Postgres to make this work?

Here's the table schema:

create table table_a (
id int primary key,
name varchar(40)
);

create table table_b(
id int primary key,
age_group varchar(5)
);

create view the_view as
select a.id, a.name, b.age_group
from table_a as a
left join table_b as b using(id);

Any ideas where to look into? Thank you.

Here's the explain analyze on the slow query:

psql=# explain analyze
       SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1;
  Limit  (cost=3296039.61..3296039.62 rows=1 width=4) (actual time=265398.380..265398.381 rows=1 loops=1)
   ->  Aggregate  (cost=3296039.61..3296039.62 rows=1 width=4) (actual time=265398.374..265398.374 rows=1 loops=1)
    ->  Index Only Scan using table_a_pkey on table_a a  (cost=0.57..2763865.85 rows=106434752 width=4) (actual time=0.158..133179.832 rows=106434713 loops=1)
        Heap Fetches: 0
Total runtime: 265398.506 ms


texts: Login failed for user Error: 18456 State: 10

texts:
In one of my vb.net applications I am getting the error Login failed for user 'user'.
But the user has full permission (sysadmin). Also this error happens intermittently.

Error log has the following:

  Login failed for user 'user'. Reason: Failed to open the explicitly specified database. [CLIENT:
  192.168.1.69] Error: 18456, Severity: 14, State: 38.
  Login
  failed for user 'user'. Reason: Password validation failed with an
  infrastructure error. Check for previous errors. [CLIENT:
  192.168.1.61]
  Error: 18456, Severity: 14, State: 10.
  Login
  failed for user 'user'. Reason: Password validation failed with an
  infrastructure error. Check for previous errors. [CLIENT:
  192.168.1.88]Error: 18456, Severity: 14, State: 10.

I saw  this article.

But in my case all database are set to autoclose OFF.

Also the user exist and has sysadmin permission.

Also KB #925744 is for SQL Server 2005 as mentioned in the article. But I am using SQL Server 2008 R2.

update:

I just saw this article. In that there was query to find ring buffer.In my case i got 0x139F as error code.when i run  NET HELPMSG 5023 i got following.

The group or resource is not in the correct state to perform the requested operation.

What does this mean? Whether it will be reason for the error?


texts: Track record deltas/changes on SQL Server 2008

texts:
Trying to build some sort of ETL process that copies rows from SQL Server into Elasticsearch indexing server.

My ETL process should be able:

Detect new rows inserted to the SQL Server table.
Detect updated rows in SQL Server table.

No so much interested in the ETL tools to use but rather Db patterns/script that will allow me to somehow query the SQL server to provide the above information.


texts:
I am attempting to rebuild the indexes for a single table on a SQL Server 2005 reporting database.  The indexes are heavily fragmented as this has not been done for a long time.  I am running the following query...

ALTER INDEX ALL ON Table_A REBUILD

Before building the index, the data is consuming 32 GB and the indexes 98 GB.  I am unable to finish the rebuild as I am running out of space once the rebuild consumes an additional 80 GB.

Is there any way to determine how much space I will need to rebuild the indexes on this table (and eventually all tables within my db)?


texts: MySQL ODBC + setting up a DSN on windows + MySQL Workbench

texts:
Sorry for the vague title, but here's my problem.

I connect to my database in MySQL Workbench using the following method : TCP/IP over SSH

However, when I try to set up the DSN on Windows 7 via:

Control Panel -> Administrative Tools ->Data Sources (ODBC)

It won't connect. My other databases that connect in MySQL Workbench using Standard TCP/IP are able to connect when I set up the DSN on Windows, but I think the SSH is throwing it off.

Could someone please let me know if it's possible to set up a DSN on windows using a connection that uses SSH?


texts: I can backup my SQL Server 2008 R2 but can't restore from the very same folder

texts:
I ran a backup into a folder. The Network Service has full control access to this folder. The backup process didn't work until I granted that account full control.

I try to restore that same file but I get an error

  Operating system error 5 (failed to retrieve text for this error. Reason: 15105).

If I move the file to the instance's actual folder it restores fine, so the file itself is not bad.

Is there something slightly different in the folder rights needed to run a backup versus a restore? What is that difference?  FYI, I am doing all this programmatically using a C# application. To test the above backup/restore, I've used the server management tool.

The user access was done via the C# code, though. Here is the main line:

myDirectorySecurity.AddAccessRule(new FileSystemAccessRule(sqlserviceuser,FileSystemRights.FullControl,AccessControlType.Allow));

I connect to the database using a SQL Server authenticated user, not a Windows user, so I can't add the 'logged in' user as full control to the folder.


texts: Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)

texts:
I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;

alter system set memory_max_target = 512M scope = spfile;

Which I guess it did, since I no longer can start the Oracle instance in question. This is the error I get when trying to connect as a non-DBA-user;

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

When connecting as SYSDBA I get the following;

Connected to an inactive instance.
SQL>startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>alter system set memory_max_target = 2G scope = spfile;
ORA-01034: ORACLE not available

("Connected to an inactive instance." is my translation of a localized error message, might not be exactly correct)
So, I'm in a catch 22 situation, I can't change the memory parameters without starting up, and I can't start up.

Is there a way to change these parameters offline?


texts: Is SQL_CALC_FOUND_ROWS Safe and Accurate for a Single DB User Environment?

texts:
I have a large MySQL web application that uses a single user for all of its database queries. We have multiple databases running off of a master-slave replication setup.

My question is this: We use SQL_CALC_FOUND rows to get the number of records returned from a query as if the query had been run without a LIMIT clause; we do this for pagination on our front-end. My concern is that, if two users of the web application login and run two queries simultaneously (remember that every user is using the same DB user account to run queries), when we query for the number of found rows:

SELECT FOUND() ROWS AS total

There may be a non-zero chance that the number being returned is from a different query that was run at the same time as the first query. Is this a possibility? Could the above query return an inaccurate number or is there some sort of fail-safe in place?


texts:
I am using Oracle LINUX5. I installed MySQL 5.1.73 community edition through RPM command and it was working. Now, I stop mysql by running below command:

$ mysqladmin -u root -ptmpassword shutdown

I did not get any message after executing the above command. When I run below command to start the service:

$ /etc/rc.d/init.d/mysql start

I got below message:
Starting MySQL.Manager of pid-file quit without updating fi[FAILED]

Now, I am unable to connect MySQL database.

How can I start MySQL again?
What is the reason behind not starting the service?


texts: Correct Selection of SQL server instance db owner login name

texts:
I am attempting to connect RSA Archer (5.2) to an instance of SQL server 2008 r2 on WS 2008 R2.
When Archer attempts it's initial connection I get -
The ConnectString property has not been initialized. Please ensure that all databases on the server have a dbowner.

That instance I was connecting to did indeed have a db owner and it had
a DBO role too. I suspect it is the login account that I am associating to this DB owner user ID.

Does anyone have any suggestions how I can get around this problem?

Thanks in advance.


texts:
Possible Duplicate:
  Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

To look at the background my problem; I have run a query to import data to a new table from a query result in MySQL workbench (149.000.000 row would be decrease to 490.000 by grouping). But it is stopped unexpectedly because of connection timeout after 20000 seconds. In the table, there is no data when I look.

I executed the query "truncate table Concepts" to delete data inside also for executing the insertion query again later. I worked about an hour but not finished. So, I stopped the query and by using command line, executed "drop table Concepts" query to completely remove it.

It has been working for 3 hours but not finished. Is there any way to delete the table manually from database?

(Windows 7, 4gb ram, intel core2 duo 2.27GHz processor)


texts: How does PostGIS make its data types compatable with Postgres?

texts:
How did Postgres extend to accommodate PostGIS data types?

I am trying to create a spatial system. All the code is written in C++. I do not want to create separate data type in Postgres using CREATE TYPE command.

Is there a way using bytea or any other stuff that I can directly store my instances in Postgres.

I also have some methods I want to define on the C++ data types, is there a way to achieve this without operator class and CREATE FUNCTION command?


texts: SQL2008 - Alter Table Alter Column to increase length results in data loss

texts:
My understanding was that you could alter the length of a column without losing any data as long as you modified the table via TSQL.

I extended the length of a column to 510 characters from 255, data type and everything else was left alone.

Since pushing this change to prod some users have said their data has gone missing. I'm yet to confirm whether the users are accurate in their assessment but it's a little concerning that I modified the column and they're reporting data loss.

I thought I'd gone about it in a safe and recommended way? Could I have lost data by increasing the column length via TSQL?


texts: How to avoid implicit type casts in PostgreSQL?

texts:
I just discovered that I can insert values of any type into a PostgreSQL (9.6) column of type text:

drop table if exists d cascade;
create table d ( a text );
insert into d values ( 42 );
insert into d values ( true );
select a, pg_typeof( a ) from d;

  a   | pg_typeof
------+-----------
 42   | text
 true | text
(2 rows)

Is this an intentional feature? Have I done something wrong? Is there a setting to avoid this? Doesn't this violate
the assumption that RDBMSs should be typesafe?

I know that text acts like a catch-all in PostgreSQL which is often convenient because you can then write
string representations of arbitrary types. But surely sometimes you want to make sure that only strings get
inserted into a given column, to the exclusion of implicitly cast values.

Is there anything I can do to avoid 'casual' type casts?


texts: How much RAM I should assign to SQL Server 2008 running on W2K3

texts: I'm planning an installation for SQL 2008 over Windows 2003.  The server has 16g RAM. How much RAM I should assign to SQL server?


texts: AlwaysOn availability group DR failover with Powershell

texts:
I am setting up AlwaysOn availability group for Disaster Recovery (DR) in the test environment. For AG1, node1 and node2 (synchronous commit/automatic failover) are in DC1; node 3 and node 4 (Asynchronous commit/manual failover) are in DC2 and file share witness is in DC3. When DR event occurs, 2 nodes in DC1 will be gone. Since DR nodes are set up as an Asynchronous commit, AG status is resolving. I will need to failover manually to bring AG group to a synchronized state. I want to use powershell to do failover with data loss. When I run the following powershell to failover, I am getting the error.
Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SQLDRPOCA03\DEFAULT\AvailabilityGroups\SQLDRPOCAG

Switch-SqlAvailabilityGroup : The availability replica for availability group 'SQLDRPOCAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group.
If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual
failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.

Is it possible to use powershell to do manual failover?


texts:
I am attempting to run the below update query

UPDATE x
SET totalval = COUNT(projs)
FROM Prs x
LEFT JOIN es y
ON x.CS = y.CS

However, I am getting the error:

  Msg 157, Level 15, State 1, Line 2
  An aggregate may not appear in the set list of an UPDATE statement.

How should this statement be altered in order for it to be valid update statement?


texts: Replication - Initialize with Snapshot when using filtered rows

texts:
We have a large database that is being Transactionally replicated (one way) between sites.

The Publication has a date based filter so that it doesn't replicate old data.

I need to add a new subscription to this publication, but don't have the capacity in the transaction log at the new Subscription server to Snapshot all the data and it will take so long to copy the full snapshot over the network that the Subscriber will be millions of transactions behind after initialization.

My question is: does the filter apply to the snapshot?

For instance, the database and all data is on the new subscription server (via Backup/Restore). The filter on the publication (Filter rows) is set to > 2011 on a date field. If I set snapshot going to initialize the subscription is it going to:

Create a snapshot of all data in the Publication articles that is greater than 2011

OR

Create a snapshot of all data in the Publication

Further, if 1 is true, then when it applies the snapshot at the subscriber will it remove all data already in the table (so data added via the initial Backup/Restore operation will be gone. Leaving me with ONLY data > 2011?)

If you can't tell I need all the data in there from the Backup (the pre 2011) but need the snapshot to be small enough that it can initialize the sub within a sensible timeframe (therefore hopefully only the > 2011 data in the snapshot).

Thanks for any assistance.


texts: How to set up indexes for dynamic filtering queries in Oracle

texts:
Given I have SOURCE_EVENT table in Oracle:
create table SOURCE_EVENT
(
    ID                   NUMBER(19)                             not null
        constraint PK_SOURCE_EVENT
            primary key,
    SOURCE_SYSTEM        VARCHAR2(50 char)                      not null,
    EVENT_TYPE           VARCHAR2(60 char),
    EVENT_TIMESTAMP      TIMESTAMP(6),
    CONTRACT_NUMBER      VARCHAR2(255 char),
    PERSON_CUID          NUMBER(19),
)
/

I want to effectively filter by all columns in this table and filtering should be dynamic, i.e. user can select in UI any combination of input fields.
I also use paging to display pages of 10 source events and total number of source events.
Current situation is I have 30 million rows stored in this table.
Examples of queries:
select *
from (select s.id              as id,
             s.source_system   as source_system,
             s.event_type      as event_type,
             s.event_timestamp as event_timestamp,
             s.contract_number as contract_number,
             s.person_cuid     as person_cuid
      from SOURCE_EVENT s
      where s.source_system = 'source' AND s.event_type = 'event'
      order by s.id desc)
where rownum <= 10;

select
    count(s.id) as count
from
    SOURCE_EVENT s
where
    s.event_timestamp between TO_TIMESTAMP('2020-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF9') and TO_TIMESTAMP(
            '2020-10-23 23:59:59.999999999', 'YYYY-MM-DD HH24:MI:SS.FF9')

What is recommended way how to set up indexes?
Taking into consideration all combinations of predicates can be used


texts:
I'm trying to connect to MongoDB using a MONGO_URL:. This particular database has a username and password. I've created a user to allow readWrite commands and that's the one I'm using.

If I log into the database and use show dbs this is what appears:

admin         0.000GB
myspecial_db  0.000GB
local         0.000GB

I'm trying to access the myspecial_db.

MONGO_URL: mongodb://username:password@private-network-ip:27017/myspecial_db

When I try and launch my app I get this error in terminal.

MongoError: failed to connect to server [private-network-ip:27017]

What am I missing here?