texts:
I am building a database to feed into a PowerBI dashboard.
The data is extracted as CSV (SurveyResults) from Survey Monkey which will be loading into the MSSQL database.
The outcome i am after is the ability to provide an Average for each KRA.
The response table has a unique row for each survey response, with each question as a separate column, with a score for a value.
Each Question is assigned a KRA, so i have created the lookup table (Question_KRA).
can someone point me in the right direction in how i can structure the data in order to have a summary table which describes the KRA and the average?
I would like to avoid hard coding in a view, as i would like the ability to add in new questions, with relative ease.


texts: Is there any particular reason LIMIT 0 is allowed?

texts:
I've just tried running SELECT * FROM tbl LIMIT 0, which unsurprisingly, returns 0 results.

I could see an argument for making it be an error, because it will never return any result, but then again it isn't invalid like LIMIT 'HelloWorld'.

Are there any actual real-world use cases for LIMIT 0, or is it just allowed because there's no real reason to not to allow it?


texts: I need to recover the jobs from a MSDB file. I had to fully reinstall a new SQL Server and Windows Server, but apparently I forgot to ever do a MSDB backup. Yup, big blunder. Now I need to restore it or at least get the jobs out of it, which are essential to day-to-day operations. Our tape backups that have the backup I need will arrive here by Saturday but I don't want to risk another day without those jobs running. I tried to the sp_dbattach procedure so I could get the scripts out but apparently the log file is broken because it had some transactions going on when the other server went down. Do I have any option?


texts: Archive Logs not applied on Primary Database

texts:
I have a oracle database and some archive logs in the primary database have not been applied. When I run SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG order by SEQUENCE# I get the following results.

How will be able to apply these logs?


texts: Conversion failed when converting date or time from character string error?

texts:
i have a problem with this error i don't know why but i try many thing and search about it bout i didn't find any thing to solve this problem ( i want to make a query to calculate the age for the client auto in a column i have the Civil number for him like (286063001795) and i have some wrong civil No in my data ) the query is working good with little of data when i use it with my table gave me that error

UPDATE [dbo].[tabl]
set dob = CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))

select *, datediff(Month, dob, getdate())/12 as age, (CIVILIDD / 100000),17000000 + (CIVILIDD / 100000)
from [tabl]

  Name_Arabic   nvarchar(255)   Checked
  CIVILIDD    bigint  Checked
  gender  nvarchar(255)   Checked
  NATIONALITY nvarchar(255)   Checked
  dob date    Checked


texts: My SSMS is bringing wrong object in "Script Stored Procedure as" > "CREATE To" > "New Query Editor Window"

texts:
When I go to Object Explorer and expand down to Stored Procedures (or Tables), right-click on one of them and choose "Script Stored Procedure / Table as" > "CREATE To" > "New Query Editor Window", it takes a while (becomes unresponsive) and then brings another object (function / table).

If I do the same but choose other action (for instance, "ALTER" for Procedures or "DROP And CREATE") instead of CREATE, it brings the correct object.

It started to behave that way without any clear reason, except for, perhaps, the last SSMS update. I thought I had successfully used CREATE just a few minutes before noticing that error, but later I realized I've used ALTER and not CREATE.

I've tried different databases and even different servers, with the same results. I've already restarted the machine but it didn't change anything. I've also asked a coworker to do the same steps and he didn't get any errors in his machine.

Obs.: It has nothing to do with permissions; it is also happening with my local SQL Express installation (where I have full access).

Is there any template for those script generation? If yes, where are they stored? If not, how can I possibly fix that?

Version information:

Microsoft SQL Server Management Studio = 13.0.16100.1;
Microsoft Analysis Services Client Tools = 13.0.1700.441;
Microsoft Data Access Components (MDAC) = 10.0.14393.0;
Microsoft MSXML = 3.0 6.0;
Microsoft Internet Explorer = 9.11.14393.0;
Microsoft .NET Framework = 4.0.30319.42000;
Operating System = 6.3.14393;


texts:
Hey trying to create a simple database table for a small beer review project, it been awhile since i have created db's so could anyone just tell me if i am totally wrong here?

Especially my use of unique identifiers, as i see it they would always be unique?

Project description: A simple asp.net site containing beer data, and information on specific key elements like brewery etc. people should be able to search by name and read about the beer.


texts: how to track progress of a large postrgresql dump

texts: Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.


texts: MongoDB: co-locate the mongos process on application servers

texts:
I would like to ask a question about a best practice described in this document:

http://info.mongodb.com/rs/mongodb/images/MongoDB-Performance-Best-Practices.pdf

  Use multiple query routers. Use multiple mongos
  processes spread across multiple servers. A common
  deployment is to co-locate the mongos process on
  application servers, which allows for local communication
  between the application and the mongos process.The
  appropriate number of mongos processes will depend on
  the nature of the application and deployment.

Just a little bit of background about our deployment. We have a lot of application server nodes. Each of them runs one JVM-based process with stateless RESTful WS. As this best practice suggests, every single application server node runs its own mongos process, which means that the number of JVM processes always equals the number of mongos processes.

All mongos processes connect to 3 config servers and several mongo shards (with replica sets within each shard). Even though we are using a sharded deployment, we are not really sharding our collections. In fact we have a large number of databases which are spread across all of the shards during their creation time (and this is our main use case for sharding at the moment).

Since best practice also suggest that "The appropriate number of mongos processes will depend on the nature of the application and deployment" I started to wonder whether our usage of mongos is actually appropriate or if it would be better for us to have several dedicated mongos nodes and let our app servers connect to them without having mongos running locally.

What is your opinion on the best approach to decide how many mongos instances are appropriate in relation to the application server instance count or the size of the MongoDB cluster?

Recently we started to look into cluster management for our stateless web services, by which I mean tools like Docker, Apache Mesos, and Kubernetes. If we are using Docker, then it is generally discouraged practice to run more than one process within container. Considering this fact it becomes really hard to make sure that application server container and mongos container are always co-located on the same physical node and have equal amount of processes. This makes me wonder whether this best practice still applies for the cluster architecture I just described. If not, can you please suggest what would be the better way to locate and deploy mongos processes in this architecture?


texts: Remove punctuation marks, special chars and space and compare cols

texts:
I have a table with new and old long description columns and I want to compare them by removing space, punctuation marks, and special characters for differences.

Create table DESCR_COMP
(
HCPCS Char(5) not null,
SEQNUM Char(4) not null,
NEW_LONG_DESC char(1000) Not NUll,
OLD_LONG_DESC char(1000) Not NUll,
SHORT_DESC char(250) Not nUll,
ADD_DT DATE,
ACT_EFF_DT DATE,
TERM_DT DATE,
CONSTRAINT PK_HCPCS primary key clustered
(
HCPCS ASC
)
)

Here are the values. total number of rows is 644 but below is the first few records

insert into Z64338_DESCR_COMP select 'A0120', '0010', 'Non-emergency transportation: mini-bus, mountain area transports, or other transportation systems', 'Non-emergency transportation: mini-bus, mountain area transports, or other  transportation systems', 'Noner transport mini-bus', '19820101', '20030101', ''
insert into Z64338_DESCR_COMP select 'A0130', '0010', 'Non-emergency transportation: wheelchair van', 'Non-emergency transportation: wheel-chair van', 'Noner transport wheelch van', '19820101', '', ''
insert into Z64338_DESCR_COMP select 'A0170', '0010', 'Transportation ancillary: parking fees, tolls, other', 'Transportation ancillary:  parking fees, tolls, other', 'Transport parking fees/tolls', '19820101', '20030101', ''
insert into Z64338_DESCR_COMP select 'A0180', '0010', 'Non-emergency transportation: ancillary: lodging-recipient', 'Non-emergency transportation: ancillary:  lodging-recipient', 'Noner transport lodgng recip', '19820101', '', ''
insert into Z64338_DESCR_COMP select 'A0190', '0010', 'Non-emergency transportation: ancillary: meals-recipient', 'Non-emergency transportation: ancillary:  meals-recipient', 'Noner transport meals recip', '19840101', '', ''
insert into Z64338_DESCR_COMP select 'A0200', '0010', 'Non-emergency transportation: ancillary: lodging escort', 'Non-emergency transportation: ancillary:  lodging escort', 'Noner transport lodgng escrt', '19820101', '', ''

I am first trying to see if I can query the columns with the values replaced and then use it in an update statement and have the column value stored in those columns and then compare. I start with the 'NEW_' column but the query is running for 20+ mins before I had to cancel it.

When I use Maxrecursion 1000, I get ",......has been exhausted before statement completion" so using infinite recursion and I don't know if that is the issue.

I dont know to use RegEX with SQLCLR so i havent gone that route. Are there more efficient ways doing it? Any way to fix my code?

DECLARE @Pattern varchar(50) = '%[^A-Za-z0-9]%';

WITH FixBadChars AS (SELECT NEW_LONG_DESC, cast(NEW_LONG_DESC as Varchar(max)) as FixedString, 1 AS MyCounter, HCPCS
                FROM [Z64338_DESCR_COMP]
                UNION ALL
                SELECT NEW_LONG_DESC, Replace(FixedString, PatIndex(@Pattern, FixedString COLLATE Latin1_General_BIN2), '') AS FixedString,
                    MyCounter + 1, HCPCS
                FROM FixBadChars
                WHERE FixedString COLLATE Latin1_General_BIN2 LIKE @Pattern)
SELECT NEW_LONG_DESC, FixedString, MyCounter, HCPCS
FROM FixBadChars
WHERE MyCounter =
        (SELECT MAX(MyCounter)
        FROM FixBadChars Fixed
        WHERE Fixed.HCPCS = FixBadChars.HCPCS)
OPTION (MAXRECURSION 0);

UPDATE FixBadChars
SET NEW_LONG_DESC = FixedString
WHERE MyCounter =
        (SELECT MAX(MyCounter)
        FROM FixBadChars Fixed
        WHERE Fixed.Id = FixBadChars.Id)
OPTION (MAXRECURSION 0);


texts: Table with Clustered Columnstore Index bigger than a Clustered Rowstore Index

texts: We have a query that is doing an index scan on a 36 million row table that has a Clustered Columnstore Index. All the cost of the plan is pretty much on the index scan of this Clustered Columnstore index. I dropped the Clustered columnstore index and replaced it with a Clustered rowstore index, and created a non clustered covering index that covers the query in question. Performance of the query increased from 40 minutes down to a minute. Checking the Properties of the table after adding a Clustered Rowstore index, i noticed that the Data space of the table was greatly reduced. When it still  had a columnstore index, Data space was at 34,153MB. After changing it to rowstore index, its now only at 4,163MB. My question is, how is it that the size of this table is bigger when it had a clustered columnstore index when columnstore indexes supposedly compresses the data in a columnstore format?  I appreciate anyone that can provide a good link to read on for a similar issue.


texts: How to enforce SQL Server policies in order to avoid users enabling bad practices

texts:
I have some rogue Windows Administrators who give themselves SA and then grant end-users SA.
These users then end up enabling auto-shrink, auto-growth percentage and all kinds of other poor practices - no matter how many times I tell them not to do it, they are still doing it.
Is there a way for me to use SQL Policies to forcefully stop these settings from being enabled? Currently I can only evaluate them and get a notification when the policies are failing.


texts: What is the meaning of filtered in MySQL explain?

texts:
As described here in the MySQL docs:

  The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. Before MySQL 5.7.3, this column is displayed if you use EXPLAIN EXTENDED. As of MySQL 5.7.3, extended output is enabled by default and the EXTENDED keyword is unnecessary.

I still don't get it. What is the meaning of "filtered" here? What information we can get from this column?

For example, when I start querying, some query will show 100, and some others show 18 or any lower than 100.

+-------------+-------+--------+---------+---------+------+----------+
| select_type | table | type   | key     | key_len | rows | filtered |
+-------------+-------+--------+---------+---------+------+----------+
| PRIMARY     | a     | range  | search  | 4       |  174 |   18.00  | <--
| PRIMARY     | b     | eq_ref | PRIMARY | 4       |    1 |   100.00 |
| PRIMARY     | c     | ALL    | PRIMARY | 4       |    1 |   100.00 |

What the main point we can conclude from this value?

Is it says that, the column filtered only 18%? Or if the  more lower the score, the more good the index/query ?

I'm using MySQL 5.7


texts: why i am getting The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

texts:
When I am only trying to create a table my "DBA" gave reader, writer, "ddladmin" to the database and I am getting this error when trying to create a basic table:

  The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

Can somebody please let me know the answer.


texts: SQL Server instance running out of worker threads

texts:
I have a SQL Server 2008 R2 SP3 Standard Edition 64-bit instance on 8 cores (576 max worker threads) with 32 GB RAM (MaxMem = 28000). It is the data store for a SharePoint installation, with 218 databases.

It was getting dozens of "SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection." errors per day, but no other errors. I found that MAXDOP = 0, which is bad for SharePoint. I gradually (over weeks) brought MAXDOP down to 1. As I did so, the frequency of those errors went down to zero on most days. But I still see them once in a while.

sys.dm_os_wait_stats has this to say about THREADPOOL waits:

waiting_tasks_count wait_time_ms    max_wait_time_ms    signal_wait_time_ms
26149               474516          4428                9

The server was last restarted at Mar 25 2018 5:55PM and current server time is Apr 20 2018 10:07PM. sp_Blitz finds nothing interesting other than use of join and order hints and slow storage writes on the drives holding tempdb files.

This is on a VM in a private cloud. Increasing the number of CPUs will be very expensive, and while it is heavily used, CPU usage doesn't seem to be a problem. In this case, would increasing the max worker threads be a reasonable thing to try, should I just leave it alone and live with the occasional 17189 error, or is there another option?


texts: SQL Server - Is there a limit to the number of Distributed Availability Groups that a primary replica can participate in?

texts:
The documentation has diagrams with availability groups taking part in multiple Distributed Availability Groups (AG 1, AG 2 and AG 3) take part in Distibuted AG1 ([AG 1, AG 2]) and Distributed AG2 ([AG 2, AG 3]).

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-2017

Is there a limit to this?

The documentation says "As long as two availability groups can communicate, you can configure a distributed availability group with them".

The documentation also says "In other words, a primary replica can participate in two different distributed availability groups"

Is 'two' the max? Or just used for the purpose of illustrating that there can be more than one. Can a primary replica participate in three different DAGs?

The book "Pro SQL Server Always On Availability Groups" says that an availability group can be a member of more than one distributed availability group, it does not explicitly say two. https://link.springer.com/book/10.1007%2F978-1-4842-2071-9


texts: Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages?

texts:
I am building an Azure SQL DB database and to do this I will have to regularly (weekly-monthly) pull data from various other servers. I am designing SSIS packages to do this and whilst most of the data import tasks run within an hour, a few take much longer (it's been running all day and hasn't finished yet) due to the much larger number of rows in these tables. Optimally, the transfers would run as quickly as possible to minimise the costs of running our Azure SSIS IR.

I have so far been transferring the data by truncating each destination table (to prevent duplication) and then copying over the entire contents of the source table (as I just want to replicate it).

Would it (in general, assuming the majority of rows in each table are not new this week/month) be faster to transfer data in this way or would it be preferable to use a lookup task and copy only non-matching (i.e. new) data from the source database to the destination, rather than truncating the table and refilling it completely each time? (Please correct me if there's a more appropriate way of doing this or I've misunderstood how lookup works!)

I can't currently test which is faster as the initial transfer is still going, so the tables aren't fully populated yet, and also I'm new to SSIS so would really appreciate some advice from people more familiar with it. For context, I only have read-only access to the source servers and not all source tables have unique row IDs, so matching on row ID alone or changing the source tables are not strategies I can use.


texts: Oracle database 19C moving back and forth with time without resetlogs

texts:
my client have a oracle database 19C on windows server 2016
for a business reason he need to be able to move back in time to specific date/time and modify some data and then move forth to the current date with the same database using archive logs and without opening database with resetlogs
i did check flashback and point in time recovery and it always ends up opening the database with resetlogs
is it doable ? if there is some other way please share


texts:
I'm having trouble getting Postgres 9.4 working on a clean installation of CentOS 7 (fully patched) when using a non-default data directory.

It's taken me a while to get here using various articles but now I'm stumped.  The directory I want the database in is /opt/pgsql/data/  Here's what I've done:

disable SELinux and reboot

yum install postgresql94-server postgresql94-contrib pgadmin3_94
sudo mkdir -p /opt/pgsql/data
sudo chown -R postgres:postgres /opt/pgsql/
sudo chmod -R 700 /opt/pgsql/
sudo cp /usr/lib/systemd/system/postgresql-9.4.service /etc/systemd/system/postgresql-9.4.service
sudo vi /etc/systemd/system/postgresql-9.4.service

change Environment=PGDATA=/var/lib/pgsql/9.4/data/ to Environment=PGDATA=/opt/pgsql/data

sudo systemctl daemon-reload
sudo vi /var/lib/pgsql/.pgsql_profile

add: PGDATA=/opt/pgsql/data

The next command is the one that fails:

sudo su - postgres -c '/usr/pgsql-9.4/bin/postgresql94-setup initdb'

It throws this error: Initializing database ... failed, see /var/lib/pgsql/9.4/initdb.log

Looking in the log file I see this: runuser: may not be used by non-root users

I've tried su-ing to postgres with the same result (e.g. sudo su - postgres -c '/usr/pgsql-9.4/bin/postgresql94-setup initdb')

I've also checked the $PGDATA variable is correctly set when becoming the 'postgres' user.

Any pointers would be very welcome.


texts: Creating a view with a Left Outer Join and Where Condition

texts:
Let's say I have a User Table and a User Record table which lists Data Sources associated with that User.

I want to create a (Sql Server) View that lists all the contents of a User, and an optional column which lists a specific row from a User Record table only if a particular condition is met.

This is what I have so far:

create view
select user.*,user_record.external_identifier
from [User] user
left outer join [User Record] user_record on user_record.USER_ID = user.ID

Adding a "where user_record.source = 'Peoplesoft' will filter out the User rows which do not have an associated Peoplesoft User record.

If I have 100 users in the User table and 95 of those users have Peoplesoft User Records, I want 100 records to get returned with a "null" in the last column for the five without an association.

I know this is relatively simple, but I'm struggling. Thanks in advance.


texts: Does RAM taken up Shared Memory connection counts against Max Server Memory?

texts:
I have an app that's local to the SQL Server and thus has a Shared Memory connection to it.  I was wondering whether the RAM taken by the connection (including data transfers) counts against the max memory limit set for the SQL Server.

The reason I am asking is that the SQL Server is maxed out on memory (e.g. Target Server Memory = Total Server Memory and other metrics).  If the RAM taken up by Shared Memory connection counts against it, wouldn't I be better off using TCP connection to the SQL Server?


texts: Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

texts:
I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)

The following query works perfectly

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = ENTRY_CODE
  where Client_recd = 'A'
  --and client.[FileRecordID] = 10607

I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has  client.[FileRecordID] = 10607 it reports to me

  Msg 245, Level 16, State 1, Line 1
  Conversion failed when converting the varchar value 'BK/TN' to data type int.

The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.

What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.

I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.

How do I fix this?
Can anyone explain what is happening so I can understand how to avoid this in the future?

Non filtered query planed execution

Filtered (non working) query planned execution

Notice the reversed order of the Compute Scalar and the Filter

Other things I have tried that do not work:

Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join.
Added OPTION (FORCE ORDER) query hint to the query.

Update:
I found a solution but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')
  where Client_recd = 'A'
  and client.[FileRecordID] = 10607


texts: Add array table constraints, empty or null if other column is false

texts:
I'm adding constraint on an existing table jobs:

DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
CREATE TABLE jobs(
status job_state NOT NULL DEFAULT 'UNSTARTED',
error_message text[]
);

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false
   CHECK ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED') AND ( error_message IS NULL OR error_message = '{}' ));

What I want is

INSERT INTO jobs VALUES ('UNSTARTED', null);
INSERT INTO jobs VALUES ('STARTED', null);
INSERT INTO jobs VALUES ('FAILED', null); -- I would like to be able to do this

I know this error_message != '{}' OR error_message IS NOT NULL is not written properly. But I'm stuck finding a way to write it.


texts:
I am trying to attach a database just from the MDF file, cause the server crashed and could never find the LDF. I am on a 2019 MS SQL Server. I am following the instructions from this page. I have created the database, took it offline and swapped my MDF and deleted the created LDF. Then put back online. When I run the following command I get the following:

UPDATE sysdatabases
SET status = 32768
WHERE name = 'MyDB'

Ad hoc updates to system catalogs are not allowed.

I ran the previous command without problems

USE MASTER
GO
sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.

Furthermore, if I run the next command, I get the following:

DBCC TRACEON (3604)
DBCC REBUILD_LOG(Database_Name, 'g:\Program Files\Microsoft SQL Server\MSSQL15.AUS\MSSQL\Data\MyDB.ldf')
GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2526, Level 16, State 3, Line 2
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.


texts: Writing CSV directly to a BYTEA column from PostgreSQL tables

texts:
Assuming these two tables:

CREATE TABLE data (day DATE, kpi INTEGER);
CREATE TABLE reports (csv BYTEA);

Is it possible to (pseudocode):

INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM data));

with the result being a CSV stored as BYTEA in table reports, column csv?

I specifically do not want to go through the filesystem (COPY, etc.).


texts: How do I grant the privilege of connecting to SSIS?

texts:
Attempting to run SSIS packages using dtexec.exe, I got this error message:

Could not load package "D:\Filename.dtsx" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "" failed
with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service.  On
Windows Vista and later, the process must be running with administrative privileges in
order to connect to the Integration Services service. See the help topic for information
on how to configure access to the service.

Typically for Microsoft, there's no clarification about which help topic has this information.  Certainly the dtexec /? help does not.

Running dtexec from a command prompt launched with admin privileges, or from an application with admin privileges, did not resolve the problem.


texts: RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

texts:
Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

The last two weeks one of our applications has had downtime due to RESOURCE_SEMAPHORE_QUERY_COMPILER waits.  We've "fixed" them by rebuilding indexes; it was just a shot in the dark.

The application vendor has "dug in" and "found the root cause" which they claim is index fragmentation and their recommendation was to implement the workaround we'd already put in place: rebuild indexes weekly (was in place at time of issue) and reorganize nightly (new).

My concern is the application vendor just took our work and claimed it as their own.  When we originally stumbled across the fix,  we considered it a temporary workaround unlikely to cause issues (its hard to object to rebuilding/reorganizing indexes). This issue started just after our largest user's busy time, so usage and index fragmentation was down. We had just purged a significant amount of data, so percentage fragmentation would get higher easier (it peaked about 22% when we were having the semaphore issue) but the percentages were still significantly down.

My concern is just we're putting a band-aide on the situation and that its just a matter of time before we have issues again.  If the query was taking a long time, I could understand, but it seems odd to me that we'd be having memory issues while compiling the query.

Random bits of information:

Two application servers with about 130 concurrent users
Dedicated database server

Microsoft SQL Server 2008 Enterprise
2 x Xeon E5-2650 @ 2.3 GHz
64 GB of ram (don't know details)
Gigabit Ethernet x 3

The datafile for the database is 199GB
According to sp_spaceused

database_size is 37831736 KB
index_size is 54547920 KB

The log and datafile are one dedicated drives; both drives are on iSCSI SAN and are RAID10
There is no partitioning in the database

I apologize if I left off something important; let me know and I'll try to add it.

Edit:

When we reorganize daily, we are recompiling statistics and obviously when we rebuilt indexes that would be done, so I'll spend some time looking into our query plans.  I really appreciate the guidance.


texts:
I have a table setup with the following columns, and it contains about 100K listing of data.

Column: address   |  Type: Text
Column: geo_point | Type: geometry(Point,4326)

My question is for each listing, how do I write the radius search query to find all the listing within 5 miles?


texts: Finding stored procedures with XACT ABORT ON OR OFF

texts:
I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog post online--

Is there a way to find what stored procs are missing those
statements for a database?
As SQL by default has this off, is there a harm enabling at SQL
    instance to ON or is it recommended to have only some SP to turn
    this on as. Why i am asking because as mentioned here it seems
    this should be added to all SP as best practice, is that true? if
    yes why not enable this at SQL instance level?


texts: Use Dynamic SQL To Create View From Cursor

texts:
I want to iterate the table names that are listed in a table and create one view that has all of the info listed.  I have the cursor set, I have the base of the view set, but how would I modify the syntax to add a UNION ALL statement instead of repeating the Create View statement?

This is the syntax I have:

Create Table dbo.tabletoprocess
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,DatabaseName varchar(100) NOT NULL
    ,TablePrefix varchar(25) NOT NULL
    ,ActiveCustomer varchar(50) NOT NULL
)

Insert Into dbo.tabletoprocess (DatabaseName, TablePrefix, ActiveCustomer) VALUES
('arc', 'night91_', 'yes')
,('bell', 'abc123_', 'yes')
,('fed', 'fred13_', 'yes')
,('rea', 'bad12_', 'yes')
,('bva', 'red121_', 'yes')
,('sez', 'bc12_', 'yes')

Declare @dbname varchar(100), @tableprefix varchar(100), @sql nvarchar(max), @fullpre varchar(500)

Declare DB_CURSOR CURSOR FOR

    Select databasename, tableprefix
    FROM tabletoprocess
    WHERE ActiveCustomer = 'yes'

    OPEN DB_CURSOR
    FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

    Set @fullpre = @dbname + '.' + @tableprefix

    WHILE @@FETCH_STATUS = 0
    BEGIN

        Set @sql = 'Create View dbo.Test As
                    Select *
                        FROM OPENQUERY(linkedserver, ''Select op.field1, op.field2, o.field3, a.field4,
                                                a.field5, a.field6, a.field7, a.field8, a.field9,
                                                o.field10, o.field11
                                                FROM '+@fullpre+'orderinfo as op
                                                LEFT JOIN '+@fullpre+'order as o ON op.oi=o.oi
                                                LEFT JOIN '+@fullpre+'address as a
                                                ON o.osa_id=a.ai'')';
        Print @sql

         FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

    END

CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR


texts: Oracle Enterprise Manager 13c - query used in metric

texts:
I got task - "send me the query used in this metric?"
It is related to Job with status "FAILED".
I find this failed Job but can't find with which query this metric detect this error.


texts: Multiple parents and multiple children in product categories

texts:
I am making a ecommerce site. In this site I want to categorise the items into three different layers

primary category             sub category           sub category

>>electronic             >>smart phone          samsung
  cameras                       tablets              nokia
                                laptop               apple
                                headphone

In the above table, I want to display the sub category after the customer selects the primary one. At the same time the 'samsung' also comes under 'camera'. Like this a parent can have any number of children and one child can have many parents. In the future we may change the primary and secondary.

What is the best solution for this?
Which model will adopt our category: tree or nested?


texts:
I have a players table

id  name
--  ----
1   jack
2   john
3   mike
4   pikachu

And I have a matches table where these players can be matched together.

id pair_id player_id
-- ------- ---------
 1       1         1
 2       1         2
 3       2         3
 4       2         4

So I tried something like select group_concat(name) group by pair_id
The end results is something like:

names
---------
Jack,John

But what I like to is to get players in their own columns like:

p1   p2
---- ----
Jack John

How can I do that?


texts: db2 removal of Generate always Expression on Timestamp data type

texts:
I have a column UPDATE_TIME with an expression

 TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

How to remove the Generated always for timestamps?

I tried

  db2 "alter table xxxx alter column UPDATE_TIME drop expression"


texts: Mysql master-master replication auto flush old logs

texts:
I have mysql-5.1.41 master-master setup running on ubuntu machines and below is the configuration of a master:

skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
max_connect_errors = 1000

server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.1.106
master-user = repli
master-password = secret
master-connect-retry = 60

binlog-format = MIXED
binlog-ignore-db = information_schema
binlog-ignore-db = lb1

log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index

log-slave-updates
report-host = 192.168.1.105
replicate-ignore-db = information_schema
replicate-ignore-db = lb2

relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

The log files seems to be growing everyday. Does anybody know what parameter I should include in above configuration to automatically remove/flush old unnecessasry logs with a decent value causing no damage to sync?.


texts:
I use SQL Developer version 17.3.1.279.  I try to save a file with File > Save.

When the 'save' window opens, it is unresponsive, meaning that:

the 'location' combo is not clickable
icons such as Desktop or Documents are not clickable
'File Name' text area is not clickable
'File Type' combo is not clickable
the close button is not clickable

I  can only move and resize the window. Resizing leads to this:

After a while, let's say 30 seconds,  all the above fields eventually become clickable , but it is annoying to wait so much. Any ideas about what might be causing this problem?

I must mention that this does not seem to be a RAM problem, since other processes like running  a query work really fine, and task manager shows that I still have some extra 2GB of RAM


texts: Grant READ ONLY permissions to all tables in all databases

texts:
I created new role named "support" in my PostgreSQL. Now I need grant "READ ONLY" permissions for this role an ALL exists databases/tables.
Also I need automatically granted same permissions on each DB that will created in future.

I unsuccessfully tried next queries for grant permissions in new databases (Can not select from new database tables ):

ALTER DEFAULT PRIVILEGES FOR ROLE support GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to support;

I looked at this post, but it about all tables within single database. It not enough for me.


texts: Percona XtraDB Cluster (5.6.28-76.1-56-log) - random OOM events

texts:
We have a multi-node (each of 64G RAM) cluster. The main (the one we use for mutating data) node randomly just OOMs, this happens both in peak times and in extremely quiet times.

What we've noticed just before the OOM event is usually a bunch of transactions waiting for locks on the main node. MySQL tuner suggests that the max possible usage of mysql is 59G, but when this starts to buildup it exhausts ALL the resources on the box and OOM event happens. There is nothing else running on that box.

The last occurrence of the event was on a quiet period, where there were very small amount of connections (less than 150), with even less actively doing anything.

Can I get any pointers on what may be causing MySQL to go over what is supposed be its maximum in terms of memory?

our my.cnf (sanitised a bit)

[mysqld]
datadir                         = /var/lib/mysql
long_query_time                 = 5
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow_query.log
log_bin                         = mysql-bin
log_error                       = /var/log/mysql/error.log
tmpdir                          = /var/lib/mysql/.tmpdir
binlog_format                   = ROW
default_storage_engine          = InnoDB

expire_logs_days                = 28
ft_min_word_len                 = 1
ft_stopword_file                = ''
join_buffer_size                = 16M
key_buffer_size                 = 24M
lc-messages-dir                 = /usr/share/mysql
lower_case_table_names          = 0
max_allowed_packet              = 512M
max_connections                 = 400
max_heap_table_size             = 6G
memlock                         = 0
myisam-recover                  = BACKUP
query_cache_limit               = 4M
query_cache_size                = 64M
query_cache_type                = 1
read_buffer_size                = 2M
read_rnd_buffer_size            = 16M
skip-external-locking
skip_name_resolve
sort_buffer_size                = 10M
sysdate_is_now                  = 1
table_open_cache                = 1024
thread_cache_size               = 512
thread_stack                    = 192K
tmp_table_size                  = 6G

explicit_defaults_for_timestamp = 1

innodb_autoinc_lock_mode        = 2
innodb_buffer_pool_instances    = 8
innodb_buffer_pool_size         = 32G
innodb_doublewrite              = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 200
innodb_log_buffer_size          = 96M
innodb_log_files_in_group       = 2
innodb_log_file_size            = 1024M
innodb_read_io_threads          = 4
innodb_thread_concurrency       = 0
innodb_locks_unsafe_for_binlog  = 1
innodb_write_io_threads         = 4

innodb_ft_min_token_size        = 0
innodb_stats_on_metadata        = 0

Additional information as requested:

number of threads = 16 per node

uses provisioned 1024 iops (aws) with 1024G storage per node

show global status: https://pastebin.com/kpiWrERR

show global variables: https://pastebin.com/x3uXrvJs

mysqltuner output: https://pastebin.com/7uGHQsGy (we've already been through a few iterations of it)

there are various other system processes the run on that box, but none of them are using more than normal and their memory usage is accounted for, im afraid thats all i can share on that.


texts: Creating a SQL Login and User on an AlwaysOn Replica

texts:
I have a database (DB1) which is synced to (DB2) on another server (via AlwaysOn - SQL Server 2014).

I want to give a user read access to DB2. I create a SQL login and database user in DB1. The new database user is migrated to DB2, but the SQL login is not (which makes sense).

The problem is that when I attempt to create a SQL login for the user on DB2 and give the user read rights to DB2, tied to the user account that came from DB1, I get an error saying DB2 is read only and cannot be modified.

So, how can I accomplish this?


texts: Guidelines for full-text index maintenance

texts:
What guidelines should be considered for maintaining full-text indexes?

Should I REBUILD or REORGANIZE the full-text catalog (see BOL)?  What is a reasonable maintenance cadence?  What heuristics (similar to the 10% and 30% fragmentation thresholds) could be used to determine when maintenance is needed?

(Everything below is simply extra information elaborating on the question and showing what I've thought about so far.)

Extra info: my initial research

There are a lot of resources on b-tree index maintenance (e.g., this question, Ola Hallengren's scripts, and numerous blog posts on the subject from other sites).  However, I have found that none of these resources provide recommendations or scripts for maintaining fulltext indexes.

There is Microsoft documentation that mentions that defragmenting the b-tree index of the base table and then performing a REORGANIZE on the full-text catalog may improve performance, but it doesn't touch on any more specific recommendations.

I also found this question, but it is primarily focused on change-tracking (how data updates to the underlying table are propagated into the fulltext index) and not the type of regularly scheduled maintenance that could maximize the efficiency of the index.

Extra info: basic performance testing

This SQL Fiddle contains code that can be used to create a full-text index with AUTO change tracking and examine both the size and query performance of the index as data in the table is modified.  When I run the script's logic on a copy of my production data (as opposed to the artificially manufactured data in the fiddle), here is a summary of the results I am seeing after each data modification step:

Even though the update statements in this script were fairly contrived, this data seems to show that there is a lot to be gained by regular maintenance.

Extra info: Initial ideas

I'm thinking about creating a nightly or weekly task.  It seems that this task could perform either a REBUILD or REORGANIZE.

Because the full-text indexes may be quite large (tens or hundreds of millions of rows), I'd like to be able to detect when indexes within the catalog are sufficiently fragmented that a REBUILD/REORGANIZE is warranted.  I'm a little bit unclear on what heuristics might make sense for that.


texts: How to determine column character set used on creation?

texts:
I have this SQL creation statement:
CREATE TABLE `testdb`.`new_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text_default` VARCHAR(45) NULL,
  `text_latin` VARCHAR(45) CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci' NULL,
  PRIMARY KEY (`id`));

where testdb has the default charset latin1.
Later, without knowing the create statement, I want to figure out if a column was defined to have latin1 as character set or it just inherited it from the database.
What I have tried
Export from the database using MySQLWorkbench
The CHARACTER SET instruction is not there.
Change the default character set of the table
ALTER TABLE `testdb`.`new_table` CHARACTER SET utf8;

Both columns text_default and text_latin preserve the latin1 as charset.
Convert the whole table to another charset
ALTER TABLE testdb.new_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
All columns are now utf8.
So basically I found no solution to track back to creation if the column was specifically as latin1 created. Any ideas?


texts: How to use SQL Web Service Endpoints for TSQL

texts:
I'm really having a hard time finding information on how to use SQL Server Web Service (Endpoint for TSQL).  I found some articles on how to create endpoints which seems to be simple enough.  From my understanding, an SQL web Service listens on a specific port for TSQL (TSQL is the one that I want to try).  I haven't been able to find how to call this endpoint with a query or a stored procedure...

Is there something I'm missing? Any article or path I can take to understand how this works would be greatly appreciated.

Any help is greatly appreciated!
Thank you


texts: Import a Oracle DMP file into a Fresh install of oracle

texts:
A client sent us an Oracle database we need to test against. We don't use Oracle or have any in-house Oracle expertise.

We need to setup the database so we can connect to it and debug a problem.

I did a fresh install of Oracle 9 (the version the client is running) and the management tools.

For the life of me, I cannot get it to import the data. It should not be this complicated. I must be getting something wrong.

I've tried:

imp 'sys/password AS SYSDBA' file=c:\database.dmp full=yes log=implog.txt

and got:

Connected to: Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYS
. importing ABCDE's objects into ABCDE
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.

But nothing shows up in the manager as far as tables in any schema and I'm at my wits end.


texts: Using Mixed mode authentication to authenticate Active Directory users to an application

texts:
I have a vendor supplied application that I have very limited access to change.  The application controls a machine, and cannot easily be logged off between users (expect up to 5 user changes per hour).  I also have to deal with Part 11, and Corporate rules.

The plan is to have a shared login to the PC (AD user), then the application which has to connect to a MS SQL database anyways, will prompt for the user name and password, which it will use for the database connection.  (previous user's DB connection will be closed between users).  If the user/password does not authenticate, they will not be able to use the application.

I have AD authentication working, and I can connect with Server Management Studio using either a SQL user, or an AD user.  The application can also connect using a SQL user.  But the application always fails to connect when I attempt to use an AD user (I am only substituting the user/password, I have also tried to prepend the "Domain\" to the user).  The application is using the default MS SQL drivers that are loaded when Server Management Studio is installed.

Is this even workable?


texts:
Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take 250ms, while some the queries for users who don't have that many events take 30 seconds.

CREATE TABLE `events` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `enactor_id` int(11) unsigned NOT NULL,
  `enactor_type` varchar(20) NOT NULL DEFAULT '',
  `subject_id` int(11) unsigned NOT NULL,
  `subject_type` varchar(20) NOT NULL DEFAULT '',
  `result_id` int(11) unsigned NOT NULL,
  `result_type` varchar(20) NOT NULL DEFAULT '',
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `enactor_id` (`enactor_id`,`enactor_type`),
  KEY `subject_id` (`subject_id`,`subject_type`),
  KEY `result_id` (`result_id`,`result_type`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here is a query that showed up in our slow logs (it ran for 35 seconds):

SELECT `events`.`id`
FROM (`events`)
WHERE (
(
    `subject_id` in (235799, 23987, 294828746, 234897224, 23429847) AND
    `enactor_type` in ('User') AND
    `subject_type` in ('Product') AND
    `result_type` in ('Comment')
) OR (
    `result_id` in (1503) AND
    `enactor_type` in ('User') AND
    `result_type` in ('Search')
) OR (
    `subject_id` in (3523) AND
    `subject_type` in ('User')
))
AND `events`.`date_created` <=  '2012-12-13 19:44:48'
ORDER BY `events`.`id` desc
LIMIT 100;

Explain shows:

id  select_type table   type     possible_keys                      key      key_len  ref   rows  Extra
1   SIMPLE      events  index    subject_id,result_id,date_created  PRIMARY  4        NULL  200   Using where

As a reference, this query completes in 70 ms:

SELECT `events`.`id`
FROM (`events`)
WHERE (
(
    `subject_id` in (1234, 23876234, 234234, 232, 2342, 23424234, 2, 456456, 567, 56756756, 567567) AND
    `enactor_type` in ('User') AND
    `subject_type` in ('Product') AND
    `result_type` in ('Comment')
) OR (
    `enactor_id` in (879,486,11154) AND
    `result_type` != "Search" AND
    `result_type` != "Recommendation" AND
    !((`result_type` = "Product" AND `subject_type` = "Store")) AND
    `enactor_type` in ('User')
) OR (
    `subject_id` in (29) AND
    `enactor_type` in ('User') AND
    `subject_type` in ('Store') AND
    `result_type` in ('Product')
) OR (
    `subject_id` in (285) AND
    `subject_type` in ('User')
))
AND `events`.`date_created` <=  '2012-12-13 22:12:47'
ORDER BY `events`.`id` desc
LIMIT 100;

The events table has ~30 million rows. The database server has 15GB of memory, and the events table is ~1GB. I have to imagine that some sort of indexing will cure this, but I have been playing around with different index choices to no avail.

Edited #1:

@Erwin Smout - You bring up a good point. For users without very many matching rows, the DB will need to go through all 30 million. That would explain the reason that heavy users have faster queries than new users or non-heavy users. The question, then, is how to speed up the worst case scenario, right?

@Pieter B, the indexes are listed in the create table definition above.

@ssmusoke, our site has a following model like many "social" sites. The events table is basically a chronological table of actions that have been taken on the site, and that query pulls events of different types that are relevant to the user based on who that user follows. I will look into changing the *_type's to tinyints to see what kind of performance gains that garners.


texts: oracle: No more data to read from socket

texts:
We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and we have an error on a query that gives us the error

Error: No more data to read from socket
SQLState:  null
ErrorCode: 17410

The query is similar to the following

select * from (
    select ... <many, MANY fields>
    from table
    inner join
    left outer join
    left outer join
    inner join
    where string1='value1' and string2='value2'

) where rownum > 500 and rownum < 510

If I try to remove the final where statement the query runs fine.
Also if I try to "reduce" the rownum filter to a lower level of records the query runs fine:

where rownum<10

Searching on internet I have found that it could be caused by the parameter "cursor_sharing" to "similar" that seems buggy on some version of oracle. I tried to set it to "exact" but unfortunately I have the same error.

I was wondering if the retrieved data of a select statement may have a limit in bytes in oracle10g but I haven't found anything. I have found a limit on oracle8i but nothing to oracle10g.

We have the same problem executing the query on different client (dbvisualizer, squirrel) and on oracle console.


texts: Performance between Delete In and For Loop

texts:
I'm trying to find out what is the most efficient way for a procedure to delete hundreds million of records

    For B in (select id from invoice where data < trunc(sysdate)) LOOP
      delete invoice_details where details_id = b.id
    END LOOP;

vs

    delete invoice_details where details_id in (select id from invoice where data < trunc(sysdate))

Is there any performances issue? which one is a better way to delete?


texts: Retrieving specific results from a query

texts:
I have the following database table

Firstname  Varchar(40)
Surname    Varchar(40)
Department Binary
JobCode    Char(1)

Normally, pulling the records of a particular department is no problem.  The department field is a 7-part field, with each bit representing a specific department, allowing the system to easily show where people work over one or more departments. Lets say that John Smith has a job code of K and Freda Williams has a Job Code of M.

Again, returning the records for a specific department is fine and easy.  However, if John Smith has a temporary promotion to the same department, rather than change the record, an additional record is added with a Job Code of F.   In essence the lower the job code letter the higher the grade.

So, if we want to retrieve John Smith's current details, it's simple, select the record where the name is John Smith, ordered by Job Code Asc, Limit (1).

But what is the SQL statement to pull all records for a department, but making sure only one record appears for John Smith and it is the 'F' record?  Can anyone help? I had thought that a sub-query might sort it, but couldn't work out how.


texts: Tools and best practice for reporting/presentation database

texts:
Currently my employer has a team that creates tableau dashboards and another very large team that creates ad hoc and regular reporting for external clients.
Both of these teams pull data from many databases and other sources.  For each report they either create a script that lives on their machine, a stored proc that lives in a source database, sometimes a scheduled job on a jenkins instance, or manually copy and paste the results of a query.  It's a badly documented mess.

My solution, that will have the least impact on how they work, and cost to implement, is to create a database on which all of the extracted data will live, so the reports and dashboards only access this single source.  A task scheduler to run regular extraction jobs, and a fairly simple custom software application that tracks changes to the source databases and alerts the project owners (of the jobs) but can also be used by the database owners to determine which downstream jobs will be impacted by changes to their databases.

I was wondering if what I have described already exists as a concept (is a presentation database a thing?) or if there exists software that could do the bulk of the heavy lifting for me? I after any tools that I might look into as alternatives, or concepts that might help my better design this solution.

So data needs to be extracted mainly from mssql, jira, jenkins and few custom sources.  I'd be storing the extracts on RDS and running the jobs once migrated on Jenkins.  I'd be building a custom web based UI for some very specific interactions, but monitoring would all be tableau dashboards.

The RDS instance wouldn't be a true data warehouse or vault, more akin to a lake I guess, as the data stored there would be aggregated from the disparate systems for a very specific purpose per table and would not be linked to each other in anyway.  This is basically replacing a huge number of regular extracts living all over the various systems and disks and instead storing them in a single database.

So I'm wondering in particular if this setup is common? and if there are any tools I should be looking at that might do some of this already.


texts: What are sensible/rational character limits in a database e.g. for a persons name

texts:
This is a general question about database admin.

Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names, phone number etc where you know the sort of data being entered and you want the data entered to be rational so you restrict the character limit. But how do you know what a sensible limit is in this crazy world there could a lot of exceptions.

In my case it is a system for a local company here in the UK with only 10-15 employees. So I could go back and change it if needed but I'd rather enter a rational limit to start with.

Sorry if this question has already been asked. If it has I couldn't find it through Google or on similar Questions.


texts: SQL Server Threads and Degree Of Parallelism

texts:
My understanding about SQL Server threads is that SQL Server breaks down an execution request within a session into several tasks and associates each task with a worker thread. So SQL Server always tries to use multiple threads (if available and needed ) to run a query, regardless of whether it is a parallel or nonparallel query.

But the following statement (From book Microsoft SQL Server 2012 Internals (MS Press)) seems to state something different:

A parallel query execution plan can use more than one thread; a serial execution plan, used by a nonparallel query, uses only a single thread.

Edit: My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.

For a parallel execution, multiple schedulers/processors would process the same execution request (which is broken down to several tasks) at the same time, and each of the scheduler will use (again, when there is a need) multiple workers/threads from their respective NUMA node.

In other words, parallel or not, there is a chance multiple threads are used.

Obviously there must be a flaw in my original understanding, but I don't know where the misconception is. Does my original understanding only apply when it is a PARALLEL query being executed?

Thanks


texts: What is the difference between Interbase and Firebird?

texts: I'm interested in a broad overview of how they are diverging and why.


texts: pass selected variable value to inside select query MySQL

texts:
i have two tables connected with one to many relationship. i have to sort parent table data according to child table data.
tables :
product
---------------------------
|id | name | default_price |
---------------------------

variant
-----------
|id | name|
-----------

product_stock
------------------------------------------------
|id | product_id | variant_id | price |available
------------------------------------------------

one product can have many product_stock rows.
I just need to sort product by product_stock minimum price
here what i did:
SELECT id,(SELECT price FROM `product_stock`
             WHERE product_id = 1
             ORDER By price LIMIT 1) as price
    from product ORDER BY price

this is working for only product id 1 . i need to pass dynamically selected product id  to it . is this possible? thank you


texts: Is my entity-relationship diagram about customers and accounts correct?

texts:
Business domain description

Each customer has a name, a permanent address, and a social security number.
Each customer can have multiple phone numbers, and the same phone number may be shared by multiple customers.
A customer can own multiple accounts, but each account is owned by a single customer.
Each account has an account number, a type (such as saving, checking, etc.), and a balance
The bank issues an account statement for each account and mails it to its account owner every month. As time goes on, there will be multiple statements of the same account.
Each statement has an issued date and a statement ID.  All the
statements of the same account have different statement IDs, but two
different accounts could have statements with the same statement ID.
For example, it is possible that account A has a statement with ID
‘123', while account B has another statement with the same ID '123'.

My entity-relationship representation

I have drawn the following entity-relationship diagram (ERD) to represent that scenario:

Questions

Can Min-Max notation be used in case of any relationships, or, just when there is an indication for that in the description?
Are my many-to-many relationships portrayed correctly here?
Could I properly portray the relationships among Account vs Account Statement vs StatementID?
As per my assumption, Is Account Statement really a weak entity and is Has really a weak relation that is dependent on Statement ID? Is issue-date a weak key?


texts: SQL Server Replication w/o network connection

texts:
Is there a way to use SQL Server Replication without a network connection?

My would-be publisher is an SQL Server 2005. It is on the "outside" as it is connected to my company's primary LAN, which is connected through a firewall to the Internet. My would-be subscriber, however, is an SQL Server 2012 that is on the "inside" - due to security reasons, it must be connected to a network that is completely (i.e. physically) isolated from our primary LAN and the Internet. I need a way to replicate outside server changes on the inside on a daily basis.

Is there a way to use Replication without a network connection between publisher and subscriber (e.g. via an optical-disc-based file transfer)? Our security protocol allows for media to be brought inside from the outside, but any kind of communication from inside to outside is forbidden. Ideally, I would run a script on the outside server, burn the resulting output to a disc, and then import the changes (be them inserts, updates, or deletes) to the inside server.

It seems Merge Replication is close to providing this functionality, as it is intended to support subscribers with sporadic network connectivity, but it doesn't seem to support scenarios in which there is no network connectivity.

Further confounding the situation is the fact that due to the size of the database involved (~50 GB), I need a way to only transfer the differential - I would only like to have to burn the daily changes to disc, not the entire DB (thus eliminating Snapshot Replication as an option).

For now, I have both servers on the outside, so I can use network-based Replication for the initial transfer, but once I deploy the subscriber to the inside, this will no longer be a possibility.

I am also open to other means of solving this problem. I have looked into backup/restore solutions, but have run into trouble as I only need to perform this operation on a subset of the data in the DB, and backing up/restoring only a subset of a DB does not seem to be a trivial exercise.


texts: Troubleshooting vanishing fulltext index in SQL Server 2012

texts:
I am trying to get to the bottom of some weirdness. Yesterday I enabled a fulltext index on a table in SQL Server 2012 running on one of our LAN servers. I used the Wizard and set the index to update automatically. I had put some test data into the table and ran some CONTAINS queries and they worked fine.

Today I added some more data to the table and ran some CONTAINS() queries, and they failed with this error message:

  Cannot use a CONTAINS or FREETEXT predicate on table or indexed view
  'foo' because it is not full-text indexed.

What could make the fulltext index ephemeral? How can I get the status of fulltext indexing in this database, including which tables have been indexed?

When I go back in to the Fulltext Wizard to recreate the indexes, the catalog that I created is still present.

EDIT:  I did confirm that there were data in the table. Test queries were working. Nothing was deleted, but there was indeed some kind of anomaly overnight that caused the server to shut down.  Is the definition of the fulltext index stored in the Master database?


texts:
I have a sharded cluster (mongo 3.2). I'm trying to set a Maximum Storage Size for a Given Shard, but it fails.

mongos> show dbs
DB_files  820.835GB
config      0.012GB
mongos> config = db.getSiblingDB("config")
config
mongos> config.shards.updateOne({ "_id": "shard0000"},{$set : { "maxSize": 4194304 }})
2016-12-21T15:47:00.467-0300 TypeError: Property 'updateOne' of object config.shards is not a function

So I tried something else:

mongos> use config
switched to db config
mongos> show collections
shards
actionlog
chunks
mongos
collections
lockpings
settings
version
locks
databases
tags
changelog
mongos> db.shards.updateOne({ "_id": "shard0000"},{$set : { "maxSize": 4194304 }})
2016-12-21T15:50:05.842-0300 TypeError: Property 'updateOne' of object config.shards is not a function

The shading is working, balancing, etc.

mongos> db.shards.find({ "_id": "shard0000"})
{ "_id" : "shard0000", "host" : "mongosh1:27017" }
mongos> sh.status()
--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("57cf4769b7dc19454fe95604")
}
  shards:
        {  "_id" : "shard0000",  "host" : "mongosh1:27017" }
        {  "_id" : "shard0001",  "host" : "mongosh2:27017" }
  databases:
        {  "_id" : "DB_files",  "primary" : "shard0000",  "partitioned" : true }
                DB_files.fs.chunks
                        shard key: { "files_id" : 1, "n" : 1 }
                        chunks:
                                shard0001       9136
                                shard0000       9136
                        too many chunks to print, use verbose if you want to force print

Any ideas?


texts: OPENJSON running once per row of an INNER JOIN unnecessarily?

texts:
I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a mobile number to match to user(s).  These get converted to one row each and matches with the users able through a JOIN operation.  User results need to be returned along with the unique ID.  Although the following query does work for this:
WITH QryCte AS (
    SELECT * FROM OPENJSON('[{"UniqueId":"0xy-5244249247924974924C","MobileNo":null,"Email":"joe.bloggs@domain.co.uk"},{...},...]') WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255))
)
SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN QryCte qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])

... it seems to run very inefficiently on our Azure SQL database, taking 5-10 seconds to run (it's much quicker on my local SQL Server DB, but that's probably because of increased resources as the execution plan is the same).  The execution plan reveals:

... that the OPENJSON has been run 147 times, once for each row of the MP_User table, presumably because of the INNER JOIN.  However, I don't understand why SQL Server does this.  The result of OPENJSON isn't going to change and you'd think that making it the table in a CTE would give SQL Server the hint that it just needs to execute it once and cache the result table that can then be joined to in the CTE's SELECT statement.
How can I make SQL Server just run the OPENJSON once, put the result into a memory table, and join to that?  Is it possible to do in a CTE or do I need to use procedural code where I explicitly define the table variable and assign it?  I'd rather avoid that if possible as the CTE form looks more elegant I think.


texts: Database Restore info not logged to msdb..restorehistory

texts:
I have 4 databases on a secondary server two of which are using Sql Server log shipping and two of which are using a "manual log shipping" config (there are just 2 primary databases).

Both implementations on the secondary server were initialized using Backups from the two primary databases.

The issue I currently have is only one of the databases which is being replicated on the secondary server is writing information to msdb..restorehistory.

To be clear it is working for both implementations of one database but neither implementation of the other.

I can't see a setting somewhere that would mean this would not happen and it's definitely not getting cleared as I'm checking instantly after a restore operation.

Anybody any thoughts on why a restore operation wouldn't be getting written to msdb..restorehistory?


texts: Whats the term in Oracle, when I'm speaking about SCHEMAS on SQL Server

texts:
Here, we have our applications developed both for oracle and SQL Server.

Devs are trying to fix their own way to be organized, using VW on views, PR on procedures and etc.

they told me that they're going to use something like DBO.REPORT_XXX to name tables, and I said "why don't you guys use Schemas"?

but, can I achieve something like sql server schemas on oracle?

I'm new with oracle and everything is different (like schema on oracle is the name of the database, that's a user)...


texts: Unable to connect oracle 12 database remotely while local connection works

texts:
I have two oracle test nodes. On node 2 as root I am able to connect without credential with the command,

su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"

Output:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 15 12:18:20 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

I'm trying to connect from node 1. Passwordless login is already established using keys between node 1 and 2.

Command:

ssh root@node2 su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"

But it prompts for credentials,

Output:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 15 12:23:07 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name:

Why does it prompt for credentials if oraabc is already a part of the dba group? Is there a configuration missing in order enable remote connection as oracle user without credentials?


texts: How do I automatically set meta commands when starting psql?

texts:
When I start a psql session, I would like certain meta commands to already be set.

For instance:

\x auto
\set ON_ERROR_STOP on

I was hoping to find an rc or config file but couldn't. The command line options don't seem comprehensive either.


texts: Unable to uninstall Sql Server 2012 Express (With SP1)

texts:
My Stack:
. Windows 10 Enterprise
. SQL Server 2012 Express (with SP1 bundled).

ISSUE:
. Installed SQL to test for backward compatibility.
. When I tried to Un-Install the following prompt is thrown.

Attempted Resolution Step:
. uninstalling individual components.
. re-installing SP1 for that edition.

None of these steps have worked. One last resort was to delete registry entries and then reattempt to delete.

Any ideas?


texts:
Story:

I tried to restore my backup database using a different name (The backup database name is "db1" and db1 is still existing in sql server. In restore, I renamed the "To Database" field into "db2"). Then after restoring, it gave an error saying "db1.log cannot be overwritten because it is still in use (or something like that)" error. Then I realized, I forgot to rename the physical file in "File" page and that caused my problem... Now db1 has a status of "Restoring..." and I don't know what to do anymore...

Is it possible to restore db1?

PS: db1.bak is outdated.

Thanks in advance!


texts: How can I reset the counters from pg_stat_database view?

texts:
I know I can reset some counters by running:

select pg_stat_reset()

but some counters like xact_commit aren't reset.

How can I do it?

I've tried also:

pg_stat_reset_shared('database')

but doesn't work.   :-(

Any ideas, please?

I could store the last counters and substract for the next time.


texts: How to specify SQL startup parameters in quiet installation via command line

texts:
I want to fix the identity gap bug in SQL Server 2012 via the -T272 startup parameter.

How can I automate this after installation? I found no install parameter for startup parameters.


texts: Should I use Postgres' JSON columns for additional related data of different types and not yet known?

texts:
I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys.
The tbl_main is the most frequently written to table, as in it receives new rows (not so much updating existing rows).
The application should be able to support various additional data related to this table that adds valuable information to the entries in tbl_main.
Yet it is not known ahead of time what kind of information this will be.
E.g. it can simply be a value, an array or an object/document or a combination of those types.
(When) Should I

create several new JSON columns for each new related kind of data or
create one JSON column for every kind of related data or
create a new table referencing the main table via foreign key (possibly via another junction table) for every new related data or
create one separate meta table tbl_main_meta that has a foreign key relationship and one binary column for the additional data?

I would like to keep the database architecture and the application architecture extensible for future data that is related to this tbl_main, yet it should be easy/efficient in the application to query for this additional data and to process it for multiple queried rows of tbl_main.
Example:

additional data is only one integer value (may be null) and should be summed up.
additional data is a list of integer values with different units, each unit's values should be summed up for all queried rows of tbl_main
additional data are multiple documents that should be both summed up for unit's values but also returned if there are string entries

The answer could also be that I must evaluate this for every new related data that I want to support, but then how should a general design look like, because I assume the meta_table approach is bad practice/inefficient?


texts: How to stop OSX MySQL filling up error logs

texts:
I've noticed about every second, a new line the mysqld error log noting a login failure. The user isn't always the same, but it consistently happens every second when mysqld is running. I'd like to turn this behavior off or prevent it from logging. I suspect this has something to do with launchd / launchctl.

Suggestions??

e.g.

2016-03-12T16:50:28.126292Z 1567 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:30.298595Z 1568 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:32.130295Z 1569 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:34.131190Z 1570 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:36.131302Z 1571 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:39.211193Z 1572 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:40.322560Z 1573 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:42.130756Z 1574 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:44.131086Z 1575 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:46.130904Z 1576 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:49.064900Z 1577 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:52.814885Z 1578 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:54.130283Z 1579 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:56.132243Z 1580 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)

My install info:

mysql> SHOW VARIABLES LIKE '%version%';;
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.9                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.9-log                    |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | osx10.9                      |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)


texts: How to change entire SQL server location from C:\ program file to another drive?

texts:
scenario:

one of my client was asked me to move entire program files folder from C:\ drive E:\ drive and change the all databases location to E:\ drive. They have copied entire program file folder through third party tool and pasted it on E:\ drive and they asked me to change path of databases and SQL installation to E:\ drive. Now how to change all databases paths and how to do this kind of activity? i know how to move system and user databases manually from one drive to another but i haven't face this king of scenario. Please suggest me how to do this task without any errors.


texts: Pass .csv file as parameter file for expdp Oracle 11.2.0.4.0

texts:
Can we use .csv file as parameter file for expdp ?

Ex: expdp scott/xxx@dev parameter=table_full.csv

Thanks


texts: Out of Memory Exception When Creating BACPAC File

texts:
We're having a frustrating time migrating our on-premises SQL application to Azure. The database is large but not crazy, about 265 GB. SQL Server Management Studio > "Deploy Database to Windows Azure SQL Database" will run for 15 - 20 hours, and then fail with a Out of Memory exception when exporting one of our larger tables.

We have BAK files of our database and data. Is there a way to import this into Azure SQL Database?


texts: How to give a table a "primary selection" to a table it is many-to-many with?

texts:
Description of Issue / Question

I want to be able to specify a default or primary relationship of a table to a row in the database that the first table already has a many-to-many relationship with.

Example / Usecase:

A user can be a member of multiple organizations. We do this by creating a many-to-many relationship via an association table between "user" and "organization". I want the user to be able to specify a "primary" organization

What I've tried

Schema:

organization:
  - id (integer, pkey)
  - name (varchar)

user
  - id (integer, pkey)
  - email (varchar)
  - primary_org_id (fkey to organization table) <-- "Primary Organization"

user_organization_assoc:
  - organization_id (integer, fkey to organization table)
  - user_id (integer, fkey to user table)
    * This also has a unique constraint on (organization_id, user_id)

My initial issue was DROP dependency issues - this was fixed by properly naming the foreign key constraints. It now "works", but several other theoretical issues arise with this solution.

The most obvious issue is the lack of integrity between the user_organization_assoc table and user.primary_org_id. When a user is removed from an organization, we have to remember to manually update the primary_org_id value of the user table.

Questions

How would you do this?
Am I going about this the wrong way?
This is a concept that is seen a lot in SaaS and other applications, are there any resources on this?

Disclosure

This development work was initially being done in python + sqlalchemy, but now it is mainly a question of principles / theory.

Thanks!


texts: Returning rows where all non-NULL values are equal

texts:
Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:

WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL

this would be the equivalent of the COALESCEs of all the permutations to be equal - but there is no non-aggregating MIN/MAX function

An alternative for 3 columns is:

WHERE (
COALESCE(a, b, c) = COALESCE(b, c, a)
AND COALESCE(a, b, c) = COALESCE(c, a, b)
AND COALESCE(a, b, c) = COALESCE(b, a, c)
AND COALESCE(a, b, c) = COALESCE(a, c, b)
AND COALESCE(a, b, c) = COALESCE(c, b, a)
)
OR COALESCE(a, b, c) IS NULL

i.e. "All non-NULL columns a, b, c must be equal or all columns may be null"

Obviously a PIVOT/UNPIVOT implementation could be done or a complex CASE statement, but I'm looking for something relatively simple.

In this case, the columns are all integers, so I guess there is probably a math trick I could figure out.


texts:
I upgraded Postgres from 9.3 to 9.4. But when I login to server then it is showing 9.3.

see the screenshot details below:

$ psql --version
psql (PostgreSQL) 9.4.10
$ pg_dump --version
pg_dump (PostgreSQL) 9.4.10

$psql
psql (9.4.10, server 9.3.13)
Type "help" for help.

postgres=# select version();
                                                           version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
        (1 row)

How can I upgrade it? What is the issue?

Updated:
After running pg_lsclusters, it is showing:

pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log


texts:
Is there other way to install ORACLE instead of GUI? Such as Terminal..
If I can't use GUI interface, then how can I install ORACLE?

Thank you.


texts: Postgres: Given some parent id's how can I SELECT X child records for each?

texts:
In Postgres, if I have :

Parent Table
id, name
1, Parent1
2, Parent2
.....
5, Parent5

Child Table
id, parent_id, name
1, 1, Child1
2, 1, Child2
.....
6, 2, Child6
.....
25, 5, Child25

so 5 children for each parent, how do I get, say, the first 3 children for parent id 1,3,4 in one query?


texts: Slow query log is not storing slow queries

texts:
Using 5.6.35 MySQL Community Server (GPL). I have setup the local MySQL instance as follows

show variables like '%slow%query%log%'

slow_query_log  ON
slow_query_log_file /usr/local/mysql/data/MacBook-Pro-slow.log

general_log ON

but I don't see any queries being logged in

SELECT * FROM mysql.slow_log;

SELECT * FROM mysql.general_log

Both the tables are empty. I am running queries that are running for more than 20 seconds, and my threshold is set fine. Any advice?


texts: what is MDL? (e.g. 'MDL semantics' or 'MDL locks') Probably Galera related

texts:
I found a guy talking about MDL semantics in a Galera/XtraDB bug report.

What is MDL? The term MDL also in Galera logs MDL conflict. Please explain in detail.

2018-06-14 17:07:10 140112699321088 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:07:10 140112699321088 [Note] WSREP: Victim thread:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: Winning thread:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: Victim thread:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=4 solved by abort
2018-06-14 17:07:10 140112695683840 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=8 solved by abort
2018-06-14 17:07:10 140112695683840 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:07:10 140112695683840 [Note] WSREP: Victim thread:
2018-06-14 17:26:47 140112698108672 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:26:47 140112698108672 [Note] WSREP: Victim thread:

2018-06-14 17:34:48 140087340817152 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:34:48 140087340817152 [Note] WSREP: Victim thread:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: Winning thread:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: Victim thread:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=4 solved by abort
2018-06-14 17:36:48 140087340817152 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=8 solved by abort
2018-06-14 17:36:48 140087340817152 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:36:48 140087340817152 [Note] WSREP: Victim thread:

2018-06-14 17:37:50 139917573339904 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:37:50 139917573339904 [Note] WSREP: Victim thread:

2018-06-14 17:05:12 139927950939904 [Warning] WSREP: Failed to report last committed 1941199495, -4 (Interrupted system call)
2018-06-14 17:09:41 139900840323840 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-06-14 17:09:41 139900840323840 [Note] WSREP: Winning thread:
2018-06-14 17:09:41 139900840323840 [Note] WSREP: Victim thread:


texts: Oracle PL/SQL does not work -- bind variable not allowed

texts:
Oracle 12.2 PL/SQL
When running:
declare
    s_sql varchar2(200);
begin
    s_sql := 'dbms_hs_passthrough.execute_immediate@mylink' || '(''exec sp_get_names'')';
    execute immediate 'begin :x; end;' using in s_sql;
end;
/

Fails with:
PLS-00110: bind variable 'X' not allowed in this context

The procedure runs fine as
begin
     dbms_hs_passthrough.execute_immediate@mylink('exec sp_get_names');
end;
/

But I want to keep it dynamic.  So how (if at all) can the procedure dbms_hs_passthrough.execute_immediate@mylink() be executed in a dynamice fashion as in the first example?
NOTE:  It is not of any significance to understand what dbms_hs_passthrough.execute_immediate@mylink() does, just how to make it (or any other procedure that is called) dynamic.
Any suggestions?


texts: Upgrading Operating System only. OS 2016 - 2019

texts:
Currently we have a number of Servers that are using Windows 2016 with SQL Server 2017. The support team want to do an in-place upgrade of the OS to 2019 due to 2016 Mainstream support ending in Jan 2022. I don't want to upgrade the SQL Server versions, so I am wondering if it is Ok to just upgrade the OS? I will obviously be taking backups and the like but just wanted to know if there were any gotchas I should be aware of before agreeing with the OS upgrade?
Thanks


texts: Proper way to fully backup and restore a database including indexes in a low-resource shared web host with ssh access?

texts:
I'm rather new to database management and I would like some advice on better practices by using the following experience as an example.

Every time I update php software I do a quick export from phpMyAdmin (ended up with a ~250mb gzipped single sql file this time), then I store it somewhere and hope I don't ever need it.

But one day I needed to restore the backup, and this is my sad story of issues...

phpMyAdmin has the import size capped as 50mb so I had to use mysql from ssh
MySQL server timed out at ~2minutes so I had to split the sql file
Indexes were not exported so the CMS started issuing CREATE INDEX on big tables, causing timeouts in the sql server
I executed the queries to make the index manually but it was timing out anyways so I had to drop the data, execute the CREATE INDEXquery and re-import that table (in chunks, because of the timeouts)
The CMS finally loaded fine, rebuilding other smaller indexes

  Now I naively thought I was done, but I checked some other pages...

    (╯°□°）╯︵ ┻━┻ ®ƒÂ¼

All the UTF8 was interpreted as extended ascii
So I dropped the database and had to set client encoding to utf8 but only in SESSION. The server timed out and mysql reconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again.
I edited the sql files to set the encoding every time a new source command was used and repeated the split import.

Now the question: How could I do this correctly?

I hope I am not off-topic...


texts:
I can run a delete statement on my server, but anytime I add a where clause to my delete statement it throws an error of:

  Msg 10054, Level 20, State 0, Line 0
  A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This syntax throws the error ->

DELETE
FROM sys.views
WHERE sys.views.name LIKE '%DoNotNeed_%'

What am I setting up incorrectly in order to delete all views that are prefaced with Do Not Need?


texts: Database Migration - Can it be this easy?

texts:
We have an old SQL Server 2008 server that needs to be replaced, and as the sole developer (and by extension DBA), I have been asked to figure out the best way to move the 5 databases to a new server.  As someone more familiar with T-SQL than SSIS, I'm not sure the way to go, but have come up with a plan that seems to allow quick and mostly painless (for the users at least) migration to the new server.  My high level plan so far is:

Setup the new server (applications, patches, etc).  We may stay with 2008, but I'm going to recommend 2012 or 2014 to bring us up to date.
Enable database mirroring or replication from the old server to the new one.  Wait for the databases to copy over.
With all users logged out, shut down old server and rename new server to old server's name.
Shut down replication, go live.

Yes, I realize there are a lot of steps in here, and plenty of caveats (users, security roles, etc), but if I'm thinking about this right, then most of this will be taken care of automatically.  My question is this (two part): Is this a valid plan to get everything moved over?  If not, what would be better?  I'm trying to minimize downtime for the users and overtime for us.


texts: SQL Server 2008 T-SQL select hanging, but not dead locked

texts:
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally it appears to get stuck.

I have looked at the results of sp_who2 and what I see is that the process is not blocked, it's still listed as 'RUNNABLE', but the value in disk IO stays the same. Once the process gets in this state it never finishes. If I kill the process and re-execute the stored proc, I usually get the same result.

Sometimes re-executing the stored proc works, but increasingly I end up having to restart SQL Server. Not ideal... After restart the stored proc executes as expected. Has anyone even hit a problem like this? Any advice would be greatly appreciated.

Some more context. The stored proc I am executing looks something like this:

delete
from table1

insert into table1
select columns
from
(
    select columns
    from giant_table_1 WITH (NOLOCK)
    where condition
    group by columns

    UNION

    select columns
    from giant_table_2 WITH (NOLOCK)
    where condition
    group by columns
)
where condition

more crud on table1
.
.
.

If I insert some logging statements into the stored proc I can see that when it hangs it hangs on the insert/select. Wondering if this could be a weird consequence of the NOLOCK statement. We (and by we I mean my boss :)) added the nolock because giant_table_1 and giant_table_2 are constantly being changed. Without the nolock our sproc was getting blocked a lot by other long running processes and vice versa.

Thanks in advance!


texts: Is it bad practice to use a data-warehouse for providing reorganized non-BI data?

texts:
The Primary goal is the case-optimized data representation. The idea is to gather data from several databases, save the data in a data-warehouse and restructure/connect the data in data marts(similar to a view across multiple databases). Basically the intention is to make it easier to access the data that is spread across databases.

As you can see in the picture the plan is to have dependent data marts(shown in the upper half of the picture) to avoid having multiple Clean and Load processes like independet data marts have (shown in the bottom of the picture). The BI aspect of the data-warehouse will also be used but is not relevant for my question. Only the data in the DB will be changed. The data represented in the DW is just for reading purposes. So the basic concept is not hurt. But I still feel like the DW is misused here.

To anwser my question please include following points:

Is the DW misused?
Are there any DW's with dataconnectors that fit (near) real-time
capabilities?
What alternatives fit these requirements to connect    data from
multiple DB's and represent them in a clean and structured    way in
real-time without having tons of Clean and Load processes?


texts: Can I time out my scripted "FLUSH TABLES WITH READ LOCK;"?

texts:
I have a single Enterprise Linux 6 system running MySQL 5.5. I am attempting to use LVM to capture a weekly filesystem snapshot.

However, as many of us have experienced, "FLUSH TABLES WITH READ LOCK;" can lead to a deadlock situation, create a backlog of queries, fill up the connection pool and effectively knock the system offline. In fact last night my "FLUSH TABLES WITH READ LOCK;" didn't complete until 8 hours after execution due to a long running query.

If I perform the MySQL snapshot using a script like this, can I somehow time out the "FLUSH TABLES WITH READ LOCK;". If it doesn't execute after a period of time I would like to kill it and avoid the inevitable deadlock.

mysql -u root <<-MYSQL_INPUT
FLUSH TABLES WITH READ LOCK;
SYSTEM /usr/local/bin/mysql-create-lvm-snapshot;
UNLOCK TABLES;
\q
MYSQL_INPUT


texts:
How do you diagnose why an instance of psql is hanging mid-way through executing a SQL script?

I have a sanitation script with a bunch of UPDATE statements like:

UPDATE table1 SET column1 = CONCAT('stuff', id);
UPDATE table2 SET column2 = CONCAT('stuff', id);
...

The script is part of an automated routine to prepare a development database. However, this is blocking me because the script hangs indefinitely.

There's about a hundred statements like this in the file, yet, about half way through, the script hangs. When I re-run the process from a parent bash script, it hangs at a different point, so it's not a specific statement that's causing the issue. Moreover, when I run the psql command manually, it runs perfectly without hanging.

The call I'm running is:

psql --user=myuser --no-password --host=localhost -d mydatabase -a -f sanitize.sql

Since it non-deterministically fails when run from inside Bash, but succeeds when I run it directly in a shell, that's likely a part of the problem, but I'm not sure what to investigate, since no error's being thrown. Where do I begin investigating this?


texts: SSIS Job "Argument "ORARA********.xxxxxxx.COM" for option "connection" is not valid. The command line parameters are invalid. The step failed. " error

texts:
My SSIS package is running fine in BIDS but when I tried to schedule the job it's showing:

  Argument "ORARACOCCUAT1.xxxxxxx.COM" for option "connection" is not valid.
  The command line parameters are invalid.
  The step failed.

I'm trying to move data from SQL Server to Oracle. I'm executing the package as well as scheduling the job in same server.

My command line is:

/FILE "C:\Users\..(snip)..\VERIFIERTERMCODE.dtsx"
/CONNECTION "10.226.211.222.KK.*";"Data Source=10.226.211.222;
User ID=*;Initial Catalog=KK;Provider=SQLNCLI10.1;
Application Name=SSIS-VERIFIERTERMCODE-{6B1F6F56-1097-4F40-B681-217B3EB954C1}
10.226.211.222.KK.*;Auto Translate=False;\""
/CONNECTION "ORARACOCCUAT1.xxxxxxx.COM/LSSUATDB.*****";
"\"Data Source=ORARACOCCUAT1.xxxxxxx.COM/LSSUATDB;
User ID=*****;Password=****;Provider=OraOLEDB.Oracle.1;\"" /X86
/CHECKPOINTING OFF /REPORTING E

Steps I tried to fix this issue:

As stated on this site:

  If your SQL Server is 64 bit
  In SQL Server JOB-->Properties-->Steps--->
  In the Execution Option Tab -- Check "Use 32 bit runtime"
  In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if checked.
  Schedule the Job and run it.

...but that is not working for me and showing the same error.

As mentioned on this site, removed hyphen and double quotes before Data source and after Provider but getting same error.

Can anyone help me to fix this?