texts: SQL Server 2012 error when exporting to Excel

texts:
Let me start off by saying I know little and less about SQL. I received a .bak file from a client earlier today and they want me to move it to a new database (not SQL). I've created a SQL Server 2012 instance and restored the .bak file to a database and now it is .mdf file.

The last step of this process is getting it into either Excel or a .csv file (the inputs accepted by the new database system). I tried following the basic "right click and then select export and a destination" instructions, but I keep getting an error kicked back to me. I've copy/pasted the report below:

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Copying Rows (Error)
Messages
Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE `aspnet_Profile` (
`UserId` GUID,
`..." failed with the following error: "The Microsoft Jet database engine could not find the object 'aspnet_Profile'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 (SQL Server Import and Export Wizard)

- Post-execute (Stopped)


texts:
I have the following code that is generated by an unsupported app

SELECT ID, TYPE, ID FROM my_table t1 ORDER BY TYPE, ID

I can't change this, now it used to work fine on SQL2008R2 or below on DB's in compatability mode 80 but due to infrastructure/licensing changes we require to move to a more modern version of SQL which no longer allows for mode 80 and now generates this error:

  Msg 209, Level 16, State 1, Line 1
  Ambiguous column name 'ID'.

I'm aware it's because of the duplicated reference to the ID column but I can't alter what the app generates as TSQL, is there anything I can do at the DB\server level to have this execute without error?

PS: Yes we are working to replace this app


texts: insert is not working as expected while doing partitioning

texts:
Created a table with one column and sample values

create table t1 ();
alter table t1 add column id_ numeric;
insert into t1 values (generate_series(1,10));

And, done partitioning odd numbers in one table and even numbers in one table.

create table t1_odd (check (id_%2!=0)) inherits (t1);
create table t1_even (check (id_%2=0)) inherits (t1);

create table t1_r15 (check (id_<6)) inherits (t1);
create table t1_r510 (check (id_>4)) inherits (t1);

Call this function while insert or update anything in table t1

create or replace function t1_func() returns trigger as $$
begin
    if NEW.id_ % 2 != 0 then
       insert into t1_odd values (NEW.*);
    else
       insert into t1_even values (NEW.*);
    end if;
    return null;
end;
$$
language plpgsql;

create trigger t1_trig before insert or update on t1 for each row execute procedure t1_func();

Call this function while delete anything from table t1

create or replace function t1_rfunc() returns trigger as $$
begin
    if NEW.id_ < 5 then
        insert into t1_r15 values (NEW.*);
    else
        insert into t1_r510 values (NEW.*);
    end if;
    return null;
end;
$$
language plpgsql;

create trigger t1_rtrig before delete on t1 for each row execute procedure t1_rfunc();

It works fine for insert, and If i tried to update, getting some error

update t1 set id_ = id_ + 1;

gives

ERROR: new row for relation "t1_odd" violates check constraint "t1_odd_id__check"
SQL state: 23514
Detail: Failing row contains (2).

and while doing delete nothing gets inserted into t1_r15 or t1_r510 (data got removed from t1).

Am I missing anything?

(Using Postgresql 9.5)


texts: SQL Server losts and shows error 26 after random time

texts:
I have a Windows Server 2012 as server, which have SQL Server 2008 R2 where our database is stored.

We have another 5 PC's installed with Windows 7 64-bit, which are run under domain of above server, they are also installed SQL Server 2008 R2.

We successfully connected SQL Server. But during after some work they loss connectivity from server and show SQL network related instance error 26 on random time and random client. LAN works perfectly. So what is the issue?


texts: How to delete old entries based on MAX(id) after an INSERT query?

texts:
How can I chain multiple sql queries and reuse a query result as a variable?

Specific: I want to store MAX(id), persist new data, and delete the old one based on the maxid fetched before.

SELECT MAX(id) as maxid FROM table; //TODO how to save this?
INSERT INTO table SELECT * from temp_table...
DELELTE FROM table where id < maxid; //TODO how to reuse?


texts:
Is there an option for load balancing with mongodb replica sets?
I am not asking for a HAProxy or similar, but for a built-in feature from mongodb.
Could not find tips for this in the docs.


texts:
For some irrelevant reasons it's hard for me to get LEFT OUTER JOINS working with multiple ON clauses. Since simply moving these clauses to the WHERE statement results in different result sets, I add an additional OR IS NULL check.

LEFT OUTER JOIN ON query

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id and t1.ShardKey = t2.ShardKey
WHERE t1.id = @id and t1.ShardKey = @shardkey

LEFT OUTER JOIN WHERE OR IS NULL query

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id
WHERE t1.id = @id and t1.ShardKey = @shardkey
AND (t1.ShardKey = t2.ShardKey OR t2.ShardKey IS NULL)

Simplified definition for both tables:

CREATE TABLE [dbo].[Table1](
    [Id] [int] NOT NULL,
    [ShardKey] [int] NOT NULL,
    [Table2] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [ShardKey] ASC
))

CREATE TABLE [dbo].[Table2](
    [Id] [int] NOT NULL,
    [ShardKey] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [ShardKey] ASC
))

These queries seem to give exactly the same query plan in SQL Server Management Studio, but I wonder if there're situations where the one outperforms the other.


texts: how to load a file into a mysql table for a particular column?

texts:
I need to load a file into mysql table for a particular column I have a table cc with 2 columns name and points, I have to update only the points column, I have a txt called point.txt which has only the points,now I need to load the point.txt into the table cc for the particular column(pionts) this command is for a whole table.

load data local infile '/path/point.txt' into table tablename;`

I need to load the data for a particulate column


texts: How do I install PostgreSQL in Debian/Ubuntu?

texts:
I'm following these instructions, however I can only get to step 17.2.

Despite installing postgresql successfully via the

sudo apt-get install postgresql

command, upon running

initdb -D /usr/local/pgsql/data

Ubuntu tells me that it 'initdb' isn't installed. The instructions tell me this command is installed by

sudo apt-get install postgresql

so what's going on?


texts: Set based query to group data ordered data

texts:
I have a table of user action data and I would like to try and group it into 'sessions', so I could figure out how many times a user has logged on, how long they stay online, and how many actions they do during the time...

Full sql fiddle here: http://sqlfiddle.com/#!6/e24a50/4

Basically I have a table defined below, and I would like to group the actions when there is less than 20minutes of activity between two requests

CREATE TABLE WebsiteUsage (
      Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      UserId INT NOT NULL,
      ApplicationId VARCHAR(MAX) NOT NULL,
      TimeStamp DATETIME NOT NULL
    )

Ideally, I would get a resultset that resembled this:

UserId      Session     StartTime               EndTime                 ActionCount
----------- ----------- ----------------------- ----------------------- -----------
1           1           2015-04-15T10:28:03.887 2015-04-15T10:38:18.037 58
1           2           2015-04-15T14:40:26.277 2015-04-15T14:56:02.857 13

I've got the time between requests using the following query, but I cannot figure out how to group them based on when MinutesSinceLastRequest < 20

WITH cte AS (
SELECT *,
    PreviousTimestamp = LAG([Timestamp], 1, NULL) OVER (PARTITION BY UserId, ApplicationId ORDER BY [Timestamp])
FROM dbo.WebsiteUsage
)
SELECT
    *,
    MinutesSinceLastRequest = DATEDIFF(MINUTE, PreviousTimestamp, [Timestamp])
FROM cte

Is there a way to accomplish this in a set based operation or do I have to fall back to the old cursor?


texts: Why is a Star Schema more normalized than a 3NF Schema?

texts:
According to Oracle's documentation, third normal form schemas "may require less data-transformation than more normalized schemas such as star schemas".

I'm confused, I thought 3NF is the most normalized among common schema models, then goes snowflake schema and at last star schema.


texts: How can we manage cross-database dependencies across environments?

texts:
I've put off asking this question for some time since it's tricky to
encapsulate our situation and challenges without a wall of text, but the
situation is getting worse so I'll do my best. I'm looking for some assistance in improving the way we develop and manage our application database and developer environments, particularly in regards to database dependencies across environments.

About us

We're a medium-sized company with a fair amount of legacy code. To give an sense of what our current application database looks like, here are some ballpark numbers: 50gb, 450 tables, 200 views and 400 stored procedures. In addition, our production server runs approximately 15 databases, most of which needs or is needed by our application database.

To clarify: When I say "need", I'm referring to database objects that will not
compile/will compile but not run without the dependency. Examples of these
objects are server objects like Linked Servers and Replication Subscriptions, or
database objects like Stored Procedures and Views.

Over the past year, we've made significant improvements to the way we develop
and deploy our database. Improvements so far include the introduction of
dedicated developer environments, version control of (nearly) all database code,
automated deployment from Git (trigger-based) and the transition to a SQL Server
cluster.

The issue

What we're struggling with, and which I cannot seem to find suitable resources for, is how to deal with dependencies from our application database to other databases. These dependencies fall into two separate challenges:

1. Databases on the same server

At the moment of speaking, our application database depends upon 5 databases on the same server. These are databases with separate repositories, deployment pipelines, libraries and web projects. While bootstrapping developer environments, we must take care to create these in a particular order in order to successfully apply DDL and DML-scripts, lest we face dependency errors. This process alone is causing a lot of headache. In fact, it is causing so much headache that some of our developers have simply given up on local developer environments and do all of their development in a shared database.

2. Databases on a remote server only accessible to production

In our production environment, we import data from a handful of remote SQL Server instances. Some of this data is imported using stored procedures that reference remote servers using Linked Server objects. In order for the stored procedure to run, the Linked Server object needs to exist. In order for the Linked Server object to "successfully" exist, the remote server it references must be reachable. The remote servers are only accessible from our production server (and rightly so), but this causes our stored procedures not compile properly during deployment.

In the book "Continuous Delivery", author Dave Farley emphasizes that in true Continuous Integration, every single resource required to assemble and run a project should reside in its repository. Additionally, he specifies that every environment should be identical (with the exception of configurations such as credentials and connection strings). Our application does not satisfy these principles, and I'm not even sure if it's feasible to do so.

Our tools

Database server: Microsoft SQL Server 2017
Build server: Visual Team Services
Build tools: Redgate DLM Automation Suite

It feels like I'm missing out on some core architectural principles here. What can we do to alleviate these issues? Suggestions to relevant literature are also welcome.


texts:
I have a problem, because I have been using this query without problem... until now:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.Date)
FROM FactTransactions T1
INNER JOIN DimStore T2 ON T1.cod_store = T2.cod_storeKey

But now It gave me an error:

  Conversion failed when converting date and/or time from character string

I have no idea what's going on. Here are the columns:

Ordinal(numeric,null)
Opening_date(varchar, not null)
Date(varchar, not null)
cod_store(int,not null)
cod_storekey(PK,int, not null)


texts: Conditional window function with a twist -- include at most X preceding rows but exclude some

texts:
Asking for a friend who uses SQL Server Parallel Warehouse.

They™ has a table of weekly sales amounts, like so (please forgive improper column justification):

+-------+------------+
| week  |  amount    |
+-------+------------+
|    1  | 100.00     |
|    2  | 100.00     |
|    3  | 100.00     |
|    4  | 100.00     |
|    5  | 100000.00  |
|    6  | 100.00     |
|    7  | 50000.00   |
|    8  | 50000.00   |
|    9  | 50000.00   |
|   10  | 100.00     |
+-------+------------+

And also a list of "bad" weeks, e.g.

+------+
| week |
+------+
|    5 |
|    7 |
|    8 |
|    9 |
+------+

And They™ needs to select for each week, including "bad" weeks, the sum of sales for the four preceding not "bad" weeks, i.e. going as far back as possible, skipping "bad" week records, to add up at most four sales amounts. So the expected result would be:

+-------+------------+
| week  | sum_not_bad|
+-------+------------+
|    1  | null       |
|    2  | 100.00     |
|    3  | 200.00     |
|    4  | 300.00     |
|    5  | 400.00     |
|    6  | 400.00     |
|    7  | 400.00     |
|    8  | 400.00     |
|    9  | 400.00     |
|   10  | 400.00     |
+-------+------------+

I have a fiddle that has I think one step in the right direction but I can't figure out the next step(s).

Does anyone have insights?


texts:
According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved word. The old PostgreSQL docs do say "the presence of a key word does not indicate the existence of a feature." When aliasing a table or column I've seen AS, but never ALIAS.

Where is (or was) the SQL keyword ALIAS used? Was it ever in-use or only ever reserved for future-use?


texts: Update Table 1 taking value from table 2

texts:
I want to update Table 1 by taking the value from Table 2. Both the tables have Emp No which is a key. I want to update the Salary column in Table 1 with the value that is present in Table 2

UPDATE TEST.tbl1  --< first the table that will be updated
JOIN   TEST.tbl2
ON     tbl1.IDNo = tbl2.IDNo
SET    Salary = Salary (From Second Table)

When i tried this iam getting an Error.. It says Error at or before the TEST..

Please anyone help me


texts: Splitting a large table to improve performance

texts:
This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table.

The table is 100+ million rows (35 columns) and growing at around 250,000 rows per day. We need all the data to be "online", and most of the columns need to be searchable in some fashion. The vast majority of activity on the table is reading; apart from the new data being INSERTed during the day, there's no need to change anything.

Users perform a range of queries on the table, ranging from simple look-up-a-record requests to pulling tens of thousands of rows based on a range of criteria. We only have limited control on the queries that are run, and performance is starting to suffer, even with indexing.

A big part of the problem is disk I/O, which we're addressing by retrofitting a SSD-based array. As all database files will be on this new array, the consensus is that having multiple database files won't make any difference, but that splitting the table up into separate tables might be the way to go.

I'm now puzzling over what would be the best approach to this. Two ideas that I'm debating with myself:

Split the table into "tiers"

A table containing the last week's data, which is the one being
INSERTed into each day
Next table containing from last week
back to 3 months previous
Next table containing from 3 months to 6 months
Next table containing anything older than 6 months

I'd then "shuffle" the data down the tiers overnight (the database
is only accessed 8am-10pm, so I have a window overnight to process
data).
Create tables for data ranges

Create a table for a data range - say per quarter. I'd then have
the data INSERTing into the table 2Q2013, and then trip over to
3Q2013, 4Q2014 etc ...

I could use filegroups to make older tables "read only" if this
would improve performance.

Option 1 is the easiest for me to implement, but I'm not sure if this is a completely mad idea. Option 2 is a more work to implement and maintain, but if it's "best practice" for this kind of problem that it's the way I'll go.

Any and all advice or alternative ideas would be gratefully received - I'm away that these kinds of problems are best solved at design-time.


texts: Using of delete obsolete in Oracle 11gR2 RMAN on Linux

texts:
I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 backup. Let's say:

Day 1: 8 AM

Day 1: 23 PM

Day 2: 8 AM (When my script run here and complete backup operation, after that my delete command should delete just Day 1: 8 AM backup)

How can I do it with delete obsolete command?

It's my script:

#!/bin/bash
. /home/oracle/.bash_profile
RMANBACKUP_MOUNTPOINT1=/u01/backup/rman/
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"
rman target /  LOG '/oracle/backups/log/rman.log' << EOF
run{
allocate channel ch1 device type disk MAXPIECESIZE 200M;
allocate channel ch2 device type disk MAXPIECESIZE 200M;
backup spfile;
backup current controlfile;
backup database format '%d_%T_db' plus archivelog format '%d_%T_arch';
delete obsolete redundancy 2;
}
exit;
EOF

Thank you.


texts: pg_restore warning column X has type unknown

texts:
I'm doing a dump from PostgreSQL 9.1 and restoring on PostgreSQL 9.4 and getting this warning

pg_restore: WARNING:  column "foobar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

But after the restore I can verify the column "foobar" type is defined and correct.

What could be causing this warning?


texts: SQL Server 2012 duplicate key error with Spatial index that shouldn't be unique

texts:
We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed

( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ CONVERT(VARCHAR(50),@xval) + ' ' + CONVERT(VARCHAR(50),@yval) +')',27700) )

The table has a spatial index (SPIX_BLPUS) on it on the SW_GEOMETRY column.

For some specific value changes of, for example, a y value, we get the error:

  Cannot insert duplicate key row in object
  'sys.extended_index_462728801_384000' with unique index 'SPIX_BLPUS'.
  The duplicate key value is (0x0119251c04, 10033223837).

I don't understand why this is happening, as the index shouldn't care about uniqueness? It doesn't happen for all value changes, just specific ones that I can only assume already exist in the index - but duplicates should be allowed.


texts:
I have a query:

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id in (
    select distinct product_tmpl_id
    from product_template
    where type='import');

And it takes 7 hours to complete. However, when I execute the subquery:

select distinct product_tmpl_id
from product_template
where type='import';

I get the error:

  column "product_tmpl_id" does not exist

There isn't a column product_tmpl_id in the table product_template.

It was my mistake. The first query should have been:

update product_product set (write_date,default_code) = (LOCALTIMESTAMP,'update')
where product_tmpl_id in
 (select distinct id from product_template where type='import');

And it onle takes several seconds to run.

So my questions are the following:

why didn`t the first query fail?
why took it so long to run?
what exactly did it do?

The result of select version(); is

PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu,
compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit


texts: How to get status of "opatch prereq CheckApplicable" via script

texts:
I want to check if a patch is Applicable via

opatch prereq CheckApplicable -oh <oracle home> -ph <patch home>

but the returncode is always 0! It doesn't matter if the text output says

"... Prereq "checkApplicable" for patch 19121548 failed ..."

or if it succeeded.

I would prefer not to parse the output of opatch prereq. Only if I have no other chance.


texts:
I have a set of data which basically looks like the following:
+---------+--------+
| EventId | Field  |
+---------+--------+
|       1 | apple  |
|       1 | paper  |
|       1 | boy    |
|       2 | banana |
|       2 | cat    |
|       3 | girl   |
+---------+--------+

I would like to pivot this to look like this:
+-------+--------+------+
|   1   |   2    |  3   |
+-------+--------+------+
| apple | banana | girl |
| paper | cat    |      |
| boy   |        |      |
+-------+--------+------+

I am trying to fiddle around PIVOT, but I can't get the result I want.
Am I looking at or using the wrong command? Is PIVOT the correct relational operator for this?
This is the code I have been working on so far:
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME(EventID)
  FROM (
            select distinct
                EventId
            from Events
        ) AS x;

SET @sql =
N'select  ' +
STUFF(@columns, 1, 2, '') +  '

into ##tempMap from
(
    select
        ev.EventID,ev.FieldName
    from Events ev
) as a
pivot
(
      max(FieldName) for EventId in ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) as pivottable;';

EXEC sp_executesql @sql

select * from ##tempMap
drop table ##tempMap

The code above returns the following results because of the MAX aggregate function.
+-------+-----+------+
|   1   |  2  |  3   |
+-------+-----+------+
| paper | cat | girl |
+-------+-----+------+


texts: show list of master tables in postgresql

texts:
I need to the SQL command to list all the master tables in the Postgresql database.

I have logged into a database called OpenBravo and when I run the command

\dt

It shows all the tables created in that database. I want to see the list of tables prefixed with pg_ which are master tables carrying the schematic details I believe


texts: postgres: WAL files not being deleted during PITR

texts:
I have got an issue with setting up PITR on my postgres server:
WAL archiving and restoring works quite well, as well as restoring to a specific point in time. The issue however is, that if i do a long replay all the WAL files are extracted from the archive and start piling up in the pg_xlog directory without ever being deleted, this causes the playback to stop with an error once the disk is full. If i then delete all the logs and restart the server the replay continues until the disk is full again. Repeat this procedure a few times and the recovery is completed sucessfully.
(the actual working time of the server is just a few minutes)

Since this is already a pain after only a few weeks of history i cannot take a system like this online as the recovery process would take forever...

I'd appreciate any help you can provide.

Ps.: wal_level is set to minimal and archiving is off during recovery


texts: How do I define the frequency of my mongo backup

texts:
I want to know if there is a way to define in Mongo the frequency of backups have to be executed. There is any config file for that? An example will be excellent!!

UPDATE

I want to create a automated mongo configuration. With this configuration I want to define the process of backup. I want the backups process be executed based on my time frequency, for example every two minutes.

So I don't know if there is a possible configuration for that. I prefer a configuration file because I want to create an automated provisioning and with commands it becomes more hard to automate.


texts:
(This is somewhat a follow up on my last question)

I am using MySQL 5.5 with innodb.

My Log table consists of two columns. One for user number, and the other for a string consisting of IP, request method, request body, and so on. The two columns use no index.

Usually the content of the logs are below 200 letters. However, there are special cases that the content go up to more than 5000 words.

I've thought of three ways to log long contents. First is to split the content into 200 words and put it in several rows. Second is to set the data type to TEXT or VARCHAR(10000). Third is to make a new table only for storing long contents.

What is the normal approach to storing logs?


texts: How to design sql database with a subset of tables for each row in a main table?

texts:
Consider a restaurant database where you have a table of restaurants (with some additional information) and for each restaurant (each row) in this table, you have a set of tables containing information (lets say menu etc..). Is it OK to have so many tables (with thousands of restaurants), or is there a better way to design such relationship?

for better imagination:


texts: How can I log which queries are in a distributed transaction on an MS SQL server?

texts:
I am looking at migrating a database from a self-hosted cluster to Microsoft Azure SQL. I am aware that there are a few distributed transactions involved, which isn't supported on Azure SQL.

Is there a way that I can log all distributed transactions and their queries, so that I can inspect the client application and remove the requirement for distributed transactions?


texts: Concurrent connections to the same table

texts:
We are a small firm, with about 10 people using a database server.

I have this application that connects to a database and fills about 40 rows of data in an table. Then calls a stored procedure to process the data before inserting the data back into another table and then truncates the table.

Is it possible if two people run the application at the same time, the data can be nonsensical. If so, how can I prevent this?


texts: PostgreSQL : Encrypting the data directory on PostgreSQL-9.4

texts:
I am working on a Spring-MVC project in which we are using PostgreSQL-9.4 running on Debian Linux as our database. I was able to find library Jasypt which can be used for encrypting and decrypting specific columns. However, is it possible to encrypt the data directory which is decrypted in runtime. I stumbled upon EcryptFS, but as far as I know it doesn't work with PostgreSQL, any alternative options? Thank you.

How much performance impact are we looking at?

We are open to upgrading PostgreSQL version if necessary.

Update

I also came upon this link, although it doesn't provide how to tell the PostgreSQL service script to connect to Postgresql. Thank you.


texts:
I have big CSV files and placed it on Linux (Amazon AMI). I have installed instant client on Amazon Linux AMI and I can connect to my Oracle database using SQL Plus. How can I import all the CSVs to Oracle from Linux terminal?

My Oracle database is located on Amazon RDS.


texts:
I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key.

The engine of choice here is InnoDB.

There are about 2 million entries in Table A with my unique-key 256 bit key.

Table B, with 7 million records, is related to table A with the 256 bit foreign key.

My question is, instead of 64 char. length keys, should I opt for 160
bit (40 digit) keys?

Will I see a large performance boost? Or the difference would not be
too large?

My machine has limited computation power and although scripting and programming is quite easy, it would take me hours to compute and re-calculate for 160 bit from 256, which is the reason for my question.


texts:
I'm using Ubuntu 16.04 and MongoDB 4.0.1. When I use mongo shell I got this :

I STORAGE  [initandlisten]
I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
I CONTROL  [initandlisten]
I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
I CONTROL  [initandlisten]

So I read lot of threads about this problem but nothing works... I created my users as it said here and login with my created users but I got again the same warnings.

I don't have created the data/db folder. I don't know if it's necessary in 4.0.1 version. It's confused for me. Inside my mongod.conf I got this :

storage:
  dbPath: /var/lib/mongodb

Should I let this config or should I rename to :

storage:
  dbPath: /data/db

I don't know what to do. Can you help me please ?


texts: Configuring SQL Replication subscriber to a publisher in a different DNS zone

texts:
I have a server that I wish to set up a subscriber on, however the publisher lives on a different domain controller and is not in the primary DNS.

To access the server, a FQDN needs to be provided.
so publisherServer becomes publisherServer.something.local

when trying to add a subscription, I get the following error:

  SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name

How can I set up a subscription, when the server name publisherServer does not resolve from the server I wish to subscribe from.


texts: SQL Server 2008 - performing nightly full backups & transaction log shipping, but log file keeps growing

texts:
Rather perplexed with my SQL server Log file. With identical primary & secondary servers, the production server on live sends the log files to the secondary every 15 minutes.
Also, every evening, a full backup (using the maintenance plan wizard) performs a full backup to a file location on the secondary server.

However, the log file seems to continually steadily grow. The database is 43Gb in size, and the log file has steadily increased to nearly 81Gb now. There are only about 80 users using the database.

On top of the full backup, I added a transaction log backup just to see if this made a difference, but still the log file is expanding. There's a weekly re-indexing job that runs on a Saturday night.

Database is on SQL Server 2008R2 (was migrated from a 2005 server a few months ago). It's in 2008 compatibility mode now, Full recovery model.

Any help greatly appreciated!


texts: Copy DB from SQL Server 2008 to SQL Server 2012

texts:
I have several databases on SQL Server 2008 that I wish to transfer to SQL Server 2012.

The two servers are on the same network but separate domains.

I am attempting a DB Copy from the one server to the new one and it connects successfully
I just am not allowed to select any of the DB instances to copy to the new server.

I have been attempting the connection from the 2012 end. I have DB sysadmin IDs at both
ends.

Any advice is appreciated.

Lou


texts: Joining two subqueries with union all and join leads to suboptimal execution plan

texts:
I've faced the similar problem on my production environment, but I managed to reproduce this behavior on Northwind DB.

Consider the following query:

USE NORTHWND;
DECLARE @ids TABLE ( Id NCHAR(50) );
INSERT  INTO @ids
VALUES  ( N'AROUT' ),
        ( N'ALFKI' );

SELECT  *
FROM    ( SELECT    c.CustomerID
          FROM      dbo.Customers c
          WHERE     c.CustomerID IN ( SELECT    Id
                                      FROM      @ids )
          UNION ALL
          SELECT    '0'
        ) t1
JOIN    ( SELECT    o.CustomerID
          FROM      dbo.Orders o
          --LEFT JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
          UNION ALL
          SELECT    '0'
        ) t2 ON t2.CustomerID = t1.CustomerID
OPTION  ( RECOMPILE );

It has pretty good query plan:

Now when I uncomment line with LEFT JOIN, the plan becomes not as good (seems like query processor can't push the CustomerID filter to the data access operators):

When I add FORCESEEK hint on dbo.Orders, query processor can't produce a query plan.
On the other hand, when I remove one of the UNION ALL, the query plan becomes as good as the first one.

Is that expected behavior? Why does the query processor not put filter by CustomerID before the join operator?


texts: Error when restoring up a database backup: Cannot find file ID 1 on device

texts:
Below is the query that I am using to restore my database.

However, whenever I run it, i always get this error message:

  Msg 4038, Level 16, State 1, Line 1
  Cannot find file ID 1 on device 'D:\DB_Backup\MMS.BAK'.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

This is my query:

RESTORE DATABASE METERIAL_MANAGEMENT_2
FROM DISK = 'D:\DB_Backup\MMS.BAK'
WITH
    MOVE 'METERIAL_MANAGEMENT' TO 'D:\DATA\METERIAL_MANAGEMENT_2.mdf',
    MOVE 'METERIAL_MANAGEMENT_log' TO 'D:\DATA\METERIAL_MANAGEMENT_2_Log.ldf'


texts: Matching columns within the same table in SQLite

texts:
I have a table of Employees which contain, among other things, the employeeId, the lastName and the ReportsTo column, that refers to the manager ID to which the employee has to report. That manager is just another employee in the same table, so there is only one employee whose value in this column is zero, which means he/she doesn't have to report to anybody.

I need to retrieve a list with the managers last name, and the last name of the employees who report to him or her.

I suspect I have to use a Self Join but I'm not sure how to write it, so any help or hint would be appreciated.


texts:
I have an Enterprise Edition of SQL 2016 SP1, that has a database with Query Store active.

I want to search for a specific word or command in the saved queries. I have googled around and not seen anything. In this case I want to find 'SHRINK', but the same approach should be usable for any text in stored query.

Anticipating the "why?" comment. I have a vendor app that does a bunch of work and then shrinks the database and log files. I am trying to find the query that does it.

How to search Query Store for a specific word or command in a query?


texts:
Is it possible to find the size of data that will be returned by a particular query?

For example, I can use the following to find the number of rows per date:

select mydatecolumn, count(*)
from MyTable
where mydatecolumn < '01-JAN-2014'
group by mydatecolumn

But is there a way to find out the size of the data that is contained in those rows?

e.g.

select mydatecolumn, "sizeofactualdata",
from mytable
where mydatecolumn < '01-JAN-2014'
group by mydatecolumn

Even if it's one query per date that's not a problem.


texts: How to remove orphaned Log Reader Agents from Replication Monitor?

texts:
How do I remove the two Log Reader Agents with errors from the Replication Monitor?

As you can see, the job does not exist (Error message produced by right click > Properties on errored Log Agent Reader) - it has previously been deleted, but these two Log Reader Agents refuse to go!


texts: Attempting to Pivot a column of grades and coming up with NULL values

texts:
I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything.

Here's the source data:

    Catalog Class Nbr   ID  Status  Grade
    281 82142   5000293644  Enrolled    1
    295 12128   1012642448  Enrolled    1
    296 12124   1012642448  Enrolled    4
    280 82141   5000289133  Enrolled    4
    295 10528   1012923963  Enrolled    1

And the Pivot Query:

      SELECT *
      FROM (
      SELECT [ID], [Catalog], [Grade]
      From CSNCombined.dbo.csnName
      ) as c
      PIVOT
      (
        SUM([Grade])
        FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
        )
        AS PVT

The query executes fine, but all values are NULL.


texts: How to make this query, to INNER JOIN with the schema?

texts:
I'm in a process to change database collations. I could create ( and copy ) scripts to create/drop FKS, PKS, but i'm having problems with unique keys and check keys.

I got this query:

 SELECT

     'ALTER TABLE  ' +
     QuoteName(OBJECT_NAME(so.parent_obj)) +
     CHAR(10) +
     ' DROP CONSTRAINT ' +
     QuoteName(CONSTRAINT_NAME)
 FROM
     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
     INNER JOIN sys.sysobjects so
     ON cc.CONSTRAINT_NAME = so.[name]

But, I can't figure it out how to implement SCHEMA on it.

I'm trying something like thi ( this is one of dozens i've tried ):

SELECT
      'ALTER TABLE  ' + quotename(sh.name)+ QuoteName(OBJECT_NAME(so.parent_obj)) +
      CHAR(10) +
      ' DROP CONSTRAINT ' +  QuoteName(CONSTRAINT_NAME)
  FROM
      INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
      INNER JOIN sys.sysobjects so
        ON cc.CONSTRAINT_NAME = so.[name]
    inner join sys.schemas sh
        on sh.schema_id=so.schema_id

but obviously, it has columns name error.

EDIT1:

I think what I did here is right:

select
    'alter table '+SCHEMA_NAME(schema_id)+'.'+OBJECT_NAME(parent_object_id) +
    char(10)+
    ' drop constraint '+OBJECT_NAME(OBJECT_ID)
    from sys.objects where type_desc='CHECK_CONSTRAINT'


texts: Methods of Denying login by specific SQL Authenticated user using Management Studio

texts:
I am trying to create a way of restricting/complicating access, for a particular user, to login to SQL Server, using Management Studio with SQL Server Authentication.
I would like a way that also gives the user a custom error whenever he tries to connect.
One way I know of is to use Logon triggers. Ex:

CREATE TRIGGER ManagementStudio_Connection_Limit_Trigger
ON ALL SERVER WITH EXECUTE AS 'TestUser'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN()= 'TestUser' AND EXISTS (SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID AND program_name LIKE 'Microsoft SQL Server Management Studio%')
        ROLLBACK;
END;

(I know that the Application Name can be spoofed but if they really want to connect, that is OK)

Are there any other ideas?

Edit: The fully story is that the SQL Authenticated user in question, is a user used primarily from some Java applications. I cannot change the permissions without a full test of all applications. Also, unfortunately the Java developers knows these passwords and uses this user willy-nilly. So, if we change the password, the developers need to know. Windows Authentication from Java is a can of worms I'd prefer not to go into right now. And again, if they manage to find a way to connect anyway, it's not the end of the world. I just want to make it complicated enough for them to reconsider. (They have personal Windows Authenticated users which they can use and sometimes do).


texts: Will I need more system resources to run DB2 instead of MySQL?

texts:
I need to convert my web application from MySQL to run on DB2. I need to know in advance whether I will need a high spec server to ensure the web application performs at the same speed as it does now on MySQL.

The application is a very database intensive data analysis application with a browser interface. It is only using about 20% of the available CPU power, and 30% of the memory on the server now. Would it take more CPU or memory if converted to DB2? Would I need a high spec server?

I am just looking for any general feel of whether a server upgrade would be required for a MySQL to DB2 conversion if everything else remains equal (database size, traffic, etc).


texts: What ports does SQL Server need to communicate with a web server?

texts:
If I have a web server trying to communicate to a SQL Server through a firewall, and the firewall is completely closed both ways (inbound AND outbound), I know I have to open port 1433 from the web server to the SQL Server in that direction.

However, do I need to open any ports from the SQL Server to the web server for the SQL activities to work? Does SQL Server respond back with any other ports?


texts: I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

texts:
My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that.
This procedure will be called every two minutes or so, and as more products are sold the results table will get bigger.

Would it be more efficient to implement a view with all the details I require and just run a SELECT statement based on that, or is it better to keep it as a stored procedure?

The stored procedure currently checks other views, but they are not tailored to my specific needs and so contain a lot of other data I don't need.


texts: MS Access 2007: Combining data from tables caused combinations of data


texts:
I have been looking at the following post and using the query to look at my top wait stats:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

My top wait stat is CXPACKET and according to the total wait time I have a number which converts to about 700 days.  I know that the server has only been up 58 days so how can this be accurate?

Does CXPACKET total for each thread?  In which case how do I know if this is a problem?


texts: Database design: how to handle the "archive" problem?

texts:
I'm pretty sure a lot of applications, critical applications, banks and so on do this on a daily basis.

The idea behind all that is:

all the rows must have a history
all links must stay coherent
it should be easy to make requests to get "current" columns
clients who have bought obsolete things should still see what they've bought even though this product is not part of the catalogue anymore

and so on.

Here's what I want to do, and I'll explain the problems I'm facing.

All my tables will have those columns:

id
id_origin
date of creation
start date of validity
start end of validity

And here are the ideas for CRUD operations:

create = insert new row with id_origin = id, date of creation=now, start date of validity=now, end date of validity=null (= means it's the current active record)
update =

read = read all the records with end date of validity==null
update the "current" record end date of validity=null with end date of validity=now
create a new one with the new values, and end date of validity=null (= means it's the current active record)

delete = update the "current" record end date of validity=null with end date of validity=now

So here's my problem: with many-to-many associations. Let's take an example with values:

Table A (id = 1, id_origin = 1, start=now, end=null)
Table A_B (start=now, end=null, id_A = 1, id_B = 48)
Table B (id = 48, id_origin = 48, start=now, end=null)

Now I want to update table A, record id=1

I mark record id=1 with end=now
I insert a new value into table A and... damn I've lost my relation A_B unless I duplicate the relation, too... this would end to a table:
Table A (id = 1, id_origin = 1, start=now, end=now+8mn)
Table A (id = 2, id_origin = 1, start=now+8mn, end=null)
Table A_B (start=now, end=null, id_A = 1, id_B = 48)
Table A_B (start=now, end=null, id_A = 2, id_B = 48)
Table B (id = 48, id_origin = 48, start=now, end=null)

And... well I have another problem: the relation A_B: shall I mark (id_A = 1, id_B = 48) as obsolete or not (A - id=1 is obsolete, but not B - 48)?

How to deal with this?

I have to design this on a big scale: products, partners, and so on.

What is your experience on this? How would you do (how have you done)?

--
Edit

I've found this very interesting article, but it doesn't deal properly with "cascasding obsolescence" (= what I'm asking actually)


texts: Varchar2 datatypes are longer than expected

texts:
When I run the following script :

create table tbl1 (
col1 varchar2(30),
col2 varchar2(50),
col3 number(4,2)
);
desc tbl1;
alter table tbl1 modify (col1 varchar2(35));
desc tbl1;
drop table tbl1;

Here is the output I get :

Table created.
TABLE tbl1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(120)
 COL2                                               VARCHAR2(200)
 COL3                                               NUMBER(4,2)
Table altered.
TABLE tbl1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(140)
 COL2                                               VARCHAR2(200)
 COL3                                               NUMBER(4,2)
Table dropped.

Can somebody explain me why the column lengths for varchar2 would be 4 times larger than they should ?


texts: DBCA silent database creation rolls back prompt when asked for sys password

texts:
I'm experiencing a strange behavior when trying to perform a silent database creation with 12.1 DBCA.

My command:

dbca -silent -createDatabase -templateName pbw4.dbt -responseFile NO_VALUE -gdbname pbwdup -sid pbwdup -emConfiguration NONE -totalMemory 2048

After running this command, I get a prompt asking me for the SYS password, as expected. However, I'm unable to actually enter in the password because my command prompt continuously rolls back, it looks like it is just automatically deleting every text on my screen. I have to ctl+c out of it to regain control.

Below is an attempt to illustrate the behavior:

Does anyone have any idea why this is happening?


texts: Why does Postgresql Data directory revert to default after updates/reboots?

texts:
I have something that is pretty simple to explain. We have certain server builds as it relates to directories in our CentOS boxes at my company and so to accommodate those, I have a script that changes the PostgreSQL data directory. However, there seem to be some occasions where it reverts back to the default value. I know one such instance was after our IT department did some upgrades and then rebooted. I can't say for sure what exactly does this, but let me show you the code that I use to change the data directory and maybe someone can figure out what I am doing wrong.
sed -i "s/Environment=PGDATA=\/var\/lib\/pgsql\/11\/data/Environment=PGDATA=\/usr\/local\/pgsql\/data/" /usr/lib/systemd/system/postgresql-11.service

systemctl daemon-reload

systemctl restart postgresql-11

There is more, but that is what changes the data directory. Any ideas why this randomly will switch back to /var/lib/pgsql/11/data and how I can prevent this from changing back? My guess is that I need to change it somewhere other than just the service file, but I have yet to find out where else I need to change it.
I guess it could help if I provided some information. This is on a CentOS 7 box, with Postgresql 11.


texts: Difficulty understanding Postgres synchronous_commit = off and data loss

texts:
I am confused what "data loss" means the context of synchronous_commit = off.

It sounds like synchronous_commit = off is pretty safe to increase write perf, and may result in "data loss", but not "data corruption" which sounds a lot worse.

So as I understand, if things crash, I might have lost some data a second before the crash. But the database is still consistent and not "corrupted", so I can simply re-run what didn't go through once it's back up, correct? My database does not end up in a state where it "kinda happened" and all the data is messed up?

I think it may also help me understand to give me examples where synchronous_commit = off is NOT ok to use.

In the documentation (https://www.postgresql.org/docs/11/wal-async-commit.html) it says:

  Thus asynchronous commit should not be used if the client will take external actions relying on the assumption that the transaction will be remembered. As an example, a bank would certainly not use asynchronous commit for a transaction recording an ATM's dispensing of cash. But in many scenarios, such as event logging, there is no need for a strong guarantee of this kind.

I am confused because the ATM dispensing cash is too obvious of an example, and the event logging example does not go into enough depth for me to understand the difference.

Say we wanted to sequentially save 3 items in our database table called numbers: [1, 2, 3]
And we have a client that queries the table numbers for what was last saved, and will resume from where it left off.

numbers
---
id
---
1

So if we ran our client, it will find 1 and start at 2. Now if we write 2, with asynchronous commit we get a premature success signal back, but then our database suddenly crashes before it can actually persist. Now if we were to restart our client, would it resume from 2 again (correct) or skip it and start with 3 (incorrect)?


texts: how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

texts:
The query I am using to retrieve data is as

"select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb,"
+ " sum(totalqt) as tqdb,"
+ " sum(payed) as tpdb,"
+ " sum(balance) as tbaldb,"
+ " `customer`"
+ " from `orderacc` join `orders` using (`orderaccid`)"
+ " group by `customer` order by max(`date`) DESC";

I am getting error that orderaccid is not a column in clause.


texts: Select all foreign key relationship chains from any set of potentially gapped table names

texts:
I am using this query to find all foreign key relationships:

 SELECT con.relname AS child_table,
    att2.attname AS child_column,
    cl.relname AS parent_table,
    att.attname AS parent_column
   FROM (
       SELECT
         unnest(con1.conkey) AS parent,
         unnest(con1.confkey) AS child,
         con1.confrelid,
         con1.conrelid,
         cl_1.relname
       FROM
         pg_class cl_1,
         pg_namespace ns,
         pg_constraint con1
       WHERE
         con1.contype = 'f'::"char"
         AND cl_1.relnamespace = ns.oid
         AND con1.conrelid = cl_1.oid
  ) con,
    pg_attribute att,
    pg_class cl,
    pg_attribute att2
  WHERE
    att.attrelid = con.confrelid
    AND att.attnum = con.child
    AND cl.oid = con.confrelid
    AND att2.attrelid = con.conrelid
    AND att2.attnum = con.parent

For any number of tables connected by foreign keys, how can only all of the relevant relationships be selected?

Take for example this relationship tree:

progenitor
    grandparent_a
        parent_a_1
        parent_a_2
    grandparent_b
        parent_b_1
        parent_b_2

That is a simple example, and my trees have many many layers, but I'd like, from an arbitrary amount of tables, be able to get all of the linking foreign key relationships.

If I wanted to select data from both parent_a_1 and parent_a_2, the two relationships to grandparent_a would be selected.

If I wanted to select data from both parent_b_1 and parent_a_1, all relationships from both to progenitor would be selected.

I can do this when all tables are immediately connected, but I can't conceptualize how to connect the gaps.


texts: Strange Sybase / SQL Server linked server issue

texts:
I have a sybase table which has a column note varchar(1000) null.

The following T-SQL will get the following error if there is a not-null value in note. (sybase is the linked server to the Sybase database)

select * from (sybase, 'select note from table')
select * from (sybase, 'select left(note, 257) note from table')
select * from (sybase, 'select left(note, 1000) note from table')

  OLE DB provider 'ASEOLEDB' for linked server 'sybase' returned data
  that does not match expected data length for column '[ASEOLEDB].note'.
  The (maximum) expected data length is 257, while the returned data
  length is 258.

However, it works if the length parameter is less and equal to 256.

select * from (sybase, 'select left(note, 256) note from table')

Also the following SQL works.

exec ('select note from table') at [sybase]

Is it a bug of the Sybase driver?


texts:
The following command:

DELETE FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60)

seems to hang indefinitely. However, the corresponding SELECT * command completes in a fraction of a second, see 'explain' command below.

Deleting rows individually is fast too.

Since the field 'id' is the primary key, I don't see any reason for the DELETE command to take any significant amount of time.

Is there an explanation and/or a work-around?

The table 'hours' contains approx 1 million rows, and 161 rows are to be deleted using the above command.

I'm using MySQL version 5.5 with the InnoDB engine. The server is completely idle.

Removing the foreign key from the 'hours' table and then changing the table engine to MyISAM makes no difference.

The table hours looks like this:

MariaDB [MSG_PLAN]> show create table hours \G;
*************************** 1. row ***************************
       Table: hours
Create Table: CREATE TABLE `hours` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `persons_id` int(6) NOT NULL,
  `year_nr` int(6) NOT NULL,
  `week_nr` int(6) NOT NULL,
  `week_day` int(6) NOT NULL,
  `activity_id` int(6) NOT NULL,
  `hours` decimal(3,1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `persons_id` (`persons_id`),
  CONSTRAINT `hours_ibfk_1` FOREIGN KEY (`persons_id`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1057141 DEFAULT CHARSET=latin1

MariaDB [MSG_PLAN]> EXPLAIN SELECT *  FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60);
+------+--------------+-------------+--------+---------------+---------+---------+---------------------+------+-------------+
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref                 | rows | Extra       |
+------+--------------+-------------+--------+---------------+---------+---------+---------------------+------+-------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL                |  851 |             |
|    1 | PRIMARY      | hours       | eq_ref | PRIMARY,id    | PRIMARY | 4       | MSG_PLAN.holdkey.id |    1 |             |
|    2 | MATERIALIZED | holdkey     | ALL    | NULL          | NULL    | NULL    | NULL                |  851 | Using where |
+------+--------------+-------------+--------+---------------+---------+---------+---------------------+------+-------------+

MariaDB [MSG_PLAN]> show create table holdkey \G;
*************************** 1. row ***************************
       Table: holdkey
Create Table: CREATE TABLE `holdkey` (
  `id` int(6) NOT NULL DEFAULT '0',
  `persons_id` int(6) NOT NULL,
  `year_nr` int(6) NOT NULL,
  `week_nr` int(6) NOT NULL,
  `week_day` int(6) NOT NULL,
  `activity_id` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: How to perform AVG, arithmetic operations in MySQL query

texts:
I am trying to perform some arithmetic operations and avg in single query, e.g I had two column data in a table which looks like this

Temp            value         value2

1401631200      -85.5582      6.87686
1401631201      -85.266       6.54354
1401631202      -83.266       6.9174344
1401631203      -84.1243      6.900333
.
.
.
.
.
1401632400      -83.43532     6.3335622

So I perform AVG by using this query statement and it works perfectly fine and produces the average value. and SQL statement is

SELECT avg(value) as value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND
usec <= 1401632400

and for average of value2 I can use this statement

SELECT avg(value2) as value_avg2 FROM kami_all_dat WHERE usec >= 1401631200 AND
usec <= 1401632400

I am trying to perform some arithmetic operations after getting this average value on the fly in query. my formula looks like:

final_val = value_avg1 + (5.67 * 10^(-8) * (value2_avg + 273.14)^4)

I am trying to achieve like this, for example

SELECT (avg(value) as Average FROM kami_all_dat WHERE usec >= 1401631200
AND usec <= 1401632400) + {arithmetic operation} * SELECT (avg(value2) as
value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND usec <= 1401632400) +
{arithmetic operations}

I am trying to achieve the single value as final_val and I am trying to perform all these operation in a single query.
But I am facing difficulty to do these arithmetic operations as mention in final_val in a single query as well as doing this average values calculation in single query.

I am beginner to database technology. Any help or guidance will be greatly appreciated. Thanks


texts: Connection Failure for postgres at Talend Open Studio

texts: I have tried to connect my PostgreSQL database in Talend Open Studio. After giving a login name, password, port and server, I tested the connection. It's saying "Connection Failure. You must change the DB Setting". When I try the same credentials with MySQL, it's connecting properly.  Please tell me the solution.


texts:
I have been told that we can greatly reduce our processing times by rebuilding our 2000 cubes in 2008.  I was told the difference is substantial (2hrs down to 20mins).

Is this true?  If so, why?


texts: Return only records with a date after the most recent September 1st

texts:
I have a table with data with a date column called "dtAssessmentDate" in a table called "tblAssessmentRecords". What I want to achieve is for a select query to return only the records from that table where the date value is after the last September 1st.
For example, if the current date is #27/02/2021# then the query would return the records with any date after #01/09/2020# as that was the most recent September 1st in the past.
I can do this for a record after a fixed date with:
[dtAssessmentDate] > #01/09/2020#
but I need the fixed date to roll over as the years go on, so if the current date was 27/02/2022, the query would return records after #01/09/2021#


texts: Cannot dump database in Oracle on Docker

texts:
Oracle is working fine on Docker, I can connect from sqlDeveloper with no problem, but I cannot dump the database logging into Docker in the bash command line.

Runnung:

expdp CONFIG0001-TEST/xxxxxxx DIRECTORY=backups DUMPFILE=dumpfile.dmp
      LOGFILE=config_export.log SCHEMAS=CONFIG0001-TEST

I get:

UDE-12547: operation generated ORACLE error 12547
ORA-12547: TNS:lost contact

Troubleshooting the ORA-12547 error on this page I see that the most likely option is that Oracle doesn't have permission:

  Possible solutions to to the ORA-12547 error include:

  Bad kernel parameters settings (e.g. "ulimit" set too small)
  Incorrect permissions on the ORACLE executable ($ORACLE_HOME/bin/oracle.exe)
  $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
  Oracle binaries have not been linked correctly

What user needs to have access to $ORACLE_HOME/bin/oracle.exe ?


texts: How to determine which one is my default instance in MS SQL Server?

texts:
I have two instances in my server and I want to know which one of them is the default. Can you, please, point out ways I can recognize it.
By saying a default instance I mean the one that listens for 1433.

Thanks for your support.


texts:
I can't find exact answer on my question. When i right click on any database in SSMS, i see "Space available parameter". Is this free space in mdf file only or it include transaction log file too (free space in mdf + ldf files together)? Thanks

I asked because ... I have a table (size about 450GB with 5 millions rows) with LOB data.
Application support delivered this script to delete some rows (should delete about 300K rows).

declare @cnt int;
declare @cnt_total int = 0;
set nocount off
while 1 = 1
begin
    delete top(5000)    -- PARAM BATCH_SIZE
    from fw.fw_o_clob
    where DT_TEC_CREATED < '2019-01-01T00:00:00.0000000'
        AND ID_CLOB <> -1
    ;
    set @cnt = @@ROWCOUNT;
    set @cnt_total += @cnt;
    if @cnt = 0
        break;
end;
print concat('Total ', @cnt_total, ' rows deleted.');
go

But when we run this, "Size DB" started growing (info from properties) - this is OK, because transaction log started growing but what is interesting "Space available" was getting smaller and smaller so we canceled it. Can someone explain me why? Thanks


texts: MYSQL - How I can filter only Debtor and only Creditors using action_type column?

texts:
I have created the balance sheet using PHP MYSQL, for admin. Now I want to filter only Debtors and only Creditors from the table.
Here are Debtors and Creditors means:

Debtors: A debtor is a person or enterprise that owes money to another party
Creditors: A creditor is a person, bank, or another enterprise that has lent money or extended credit to another party

Below is my table structure:

I am using below queries to get both different:
To get Debtors only:
SELECT *,
           Sum(Coalesce(CASE
                      WHEN action_type = 'dr' THEN amount
                    end, 0)) AS total_debits,
       Sum(Coalesce(CASE
                      WHEN action_type = 'cr' THEN amount
                    end, 0)) AS total_credits,
       Sum(Coalesce(CASE
                      WHEN action_type = 'cr' THEN amount
                    end, 0)) - Sum(Coalesce(CASE
                                              WHEN action_type = 'dr' THEN
                                              amount
                                            end, 0)) AS total_debtors,
       IF(Sum(Coalesce(CASE
                         WHEN action_type = 'cr' THEN amount
                       end, 0)) - Sum(Coalesce(CASE
                                                 WHEN action_type = 'dr' THEN
                                                 amount
                                               end, 0)) > 0, 'dr', 'cr') AS balance
FROM tbl_balancesheet
GROUP BY area_id
HAVING balance = 'cr' AND total_debtors <> 0 ORDER BY total_debtors DESC;

To get Creditors only:
SELECT *,
   Sum(Coalesce(CASE
                  WHEN action_type = 'dr' THEN amount
                end, 0)) AS total_debits,
   Sum(Coalesce(CASE
                  WHEN action_type = 'cr' THEN amount
                end, 0)) AS total_credits,
   Sum(Coalesce(CASE
                  WHEN action_type = 'cr' THEN amount
                end, 0)) - Sum(Coalesce(CASE
                                          WHEN action_type = 'dr' THEN
                                          amount
                                        end, 0)) AS total_debtors,
   IF(Sum(Coalesce(CASE
                     WHEN action_type = 'cr' THEN amount
                   end, 0)) - Sum(Coalesce(CASE
                                             WHEN action_type = 'dr' THEN
                                             amount
                                           end, 0)) > 0, 'dr', 'cr') AS balance
    FROM tbl_balancesheet
    GROUP BY area_id
HAVING balance = 'dr' AND total_debtors <> 0 ORDER BY total_debtors DESC;

Now, I have issue with the calculation and not getting proper Debtors and Creditors
Also, I have uploaded the dump on SQL Fiddle. Below is the URL:
URL: http://www.sqlfiddle.com/#!9/badc3f/5
Plese help me!!!
Thanks


texts:
I have been searching all day to find a better answer to my question but those I found was useful
but do not critically analyse my question.

I am building a chat room using php, mysql, jquery and ajax.
The target group members are 3000 people who will frequently
chat every day, so I am expecting like one million messages a day.
The chat room is open to all the members, that means
every member has the same privilege to send and view
sent messages.

In this case, every member has the permission to delete messages whether sent by him or different members,
however deleted messages should not affect other users. I wouldn't also keep messages for more than two days,
meaning every sent message should be deleted after two days.

Below are the sample tables that represents the logics above

users table

|  u_id |
|  name |

messages table

| msgID            |
| msgBODY          |
| msgTime          |
| senderID--FK     |
| deleted_by_sender|

recipient table

| recID               |
| msgID--FK           |
| recipientID ---FK   |
| deleted_by_recipient|

Now, if I implement the schema above, it means that every single sent message has to do 3000 inserts into the
recipient table and one insert into the messages table. This also means that if there are 50 sent messages within 1 minute there would be more
inserts within the one minute. At the same time 3000 people are viewing the messages. Awwsh! more work load on the database within that minute.
hmm!

Is there a way to handle this, scalability and performance wise?


texts:
A customer can make a reservation for an accommodation, the accommodation can be in a resort or elsewhere.

So i would say that a resort can have different types of accommodations, ie. Chalets, apartments bungalows, caravans, lodges, hotels, etc...

and if a customer wants to make a reservation for these types of accommodation but not in a resort.

How would i model it?

ps. lets assume that elsewhere* means a hotel outside the resort

ERD Diagram-

http://i.stack.imgur.com/y9W6d.jpg


texts: How to find column with all same repeating character

texts:
I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a  way using LIKE so its not hard coded to 15? I don't see why the [^0] below doesn't match all zeros.

CASE WHEN LTRIM(RTRIM(IL.ItemNumber)) = '' THEN 1 ELSE 0 END IsEmpty
CASE WHEN LTRIM(RTRIM(IL.ItemNumber)) <> '' AND LTRIM(RTRIM(IL.ItemNumber)) NOT LIKE '[^0]' THEN 1 ELSE 0 END IsZeros

I'm basically trying to validate the column. Trimming works for empty, but maybe there is a much cleaner/flexible way to accomplish both of these validations? Thanks!


texts: mysql if else with a statement as its condition?

texts:
I am trying to calculate something from the DB level and would like to know if its possible to an if/else or ifnull or other to evaluate this?  IF not what is a good approach?
Is it possible to do something this way?
I've tried several ways, but here is my latest example.
IF (SELECT markups.markup FROM markups LIMIT 1)
THEN
    SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1
ELSE
    SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"


texts:
I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only user and also a read write user on sql server. So I created one user USER_R and another USER_RW, that match with the domain users, on my USER_R, I want to assign db_datareader and db_denydatawriter and then select all my dbs on the User Mapping section and click OK, this will create USER_R in all my dbs.

Question 1, how to automatically manage this in case I restore a new db on that server and I want to assign the user USER_R?

Question 2, now that I have the user USER_R, a user A want to connect to management studio, he doesn't see any table or stored procedure, what's missing to let them see all objects and besides be read only?


texts: Zoned and hashed sharding of existing, non-empty collection

texts:
I am trying to shard a existing collection. Collection is pretty big (about 100gb). Sharding should be done by 2 fields. First one is zone ['Europe', 'America'] and second one is _id.
So my sharding index looks like this {"zone": 1, "_id": "hashed"}.
I have followed these steps https://docs.mongodb.com/manual/reference/method/sh.updateZoneKeyRange/#compound-hashed-shard-key-with-non-prefix-hashed-field. This works only on empty or non existing collection. My question is is this type of sharding possible on collection that is not empty.
I am using mongo version 4.4


texts:
I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?

I know that timing can be turned on with \timing at the psql prompt, but ideally I'd like to have a script that takes care of everything automatically: running the query a few times, clearing PostgreSQL cache after each run (probably by restarting the PostgreSQL's service), and outputting the average running time (and memory used is a plus).


texts: Unable to re-install an instance using the original instance name

texts:
We are trying to uninstall and re-install a 2008R2 instance.  We have uninstalled the database engine but when we tried to re-install it we got the error "Original instance id\name in use".

The instance is on a VM machine (no cluster).  Unfortunately there is another (production) instance on the same machine.

The steps taken were as follows:

Back up the databases
Uninstall the instance
Reboot
Attempt re-install failed
Review registry for any possible references to the database engine and that instance name and remove them.
Attempt re-install - same error.

We did discover that there is a reporting server instance with the old instance name already installed but I wouldn't have thought that the instance name on a reporting server instance would affect the install of a database engine instance.

Any suggestions on what we are missing?


texts:
I have heard that Hadoop has better performance than MySQL. Until now, I have used relational databases so this is really new technology for me. I have a 6 core PC. Suppose I have a table with 20 columns and 5million rows. Does Hadoop give better performance for operations like Select, Insert, and Update?

What is the equivalent command of Create Table, Select, Update, Insert, etc. in Hadoop 1.1?


texts: How can I get status messages during a large re-indexing operation with MySQL 5.5?

texts:
I get large data transfers periodically that I used to completely replace the contents of a MySQL table.  Each time I do this, I truncate the table, disable keys, add several million rows of data, and then enable keys.  As expected, once I enable keys, there is a lengthy delay while the table is being reindexed. The table has several active indices and it takes about 30 minutes to complete.

Unfortunately, I can't figure out how to get status messages that would give me progress updates on how the operation is proceeding. I found an old thread from 2001 on the MySQL forum that basically says you can't get progress updates.

I was hoping that was no longer the case. I am running MySQL Server 5.5 and MySQL Workbench 5.2, both community editions.  Perhaps there's something I can do with workbench?

Side question:  Currently, I do my updates from a Windows program that sequentially parses the transfer data file into SQL INSERT statements that I immediately dispatch to the MySQL client over a socket. Would it be faster to generate all the INSERT statements to a file and then do a bulk import by executing the SQL script from Workbench instead?

-- roschler


texts: How to select two fields from a table using join with another table

texts:
I have a books database.

CREATE TABLE `books` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `book_subject` int(11) DEFAULT NULL COMMENT 'What genre the book is to be picked up from subject table',
  `book_subject2` int(11) DEFAULT NULL COMMENT 'What genre the book is to be picked up from subject table',
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subject` (
  `subject_id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Let's say a book xyz falls under two subjects 'Mystery' and 'Thriller' with id values 1,2 in 'subject' table.

How do I write a select query that fetches the 'subject_name' value for both 'book_subject' and 'book_subject2'


texts: Looking for tree organisation for database, based on one to many relationship both ways

texts:
All the tree organisations for databases I've found documentation, papers or blogs about were built on a one parent node can have many children, but a child node can have only one parent, with a single original node - basis.

I am looking for a way to organise a database efficiently on a basis that a parent node can have many children, a child node can have many parents, and there are multiple original nodes.

The data is not going to change or move around much, but there will be insertions in the middle of the tree on a regular basis.

Any ideas or links to papers appreciated.


texts:
I'm a bit new to the database design. Thus this question to people who may have more experience.

I need to design a database that needs to store statistical data for many systems. The data is collected every day. There may be a couple hundred statistical counters. The number of systems can also grow.

Which database design is more efficient? From long term maintenance standpoint, from performance standpoint, etc.

Design 1: one giant table with columns for counters. Then each system on each date will add its number of entries.

Design 2: every system gets its own table dynamically created. It will contain a line for every day of statistics collection. There will also be one more table of tables that will contain the list of all system tables.


texts: Oracle 11g - drop maxvalue partition, keep data

texts:
I have some tables with data like this:

 CREATE TABLE "PARTTEST4"
   ("year" number(4,0) NOT NULL ENABLE)
  PARTITION BY RANGE ("year")
 (PARTITION "P_OLD" VALUES LESS THAN (2010),
  PARTITION "P_2020"   VALUES LESS THAN (2020),
  PARTITION "P_MAX"    VALUES LESS THAN (MAXVALUE));

insert into parttest4 values (2005);
insert into parttest4 values (2015);
insert into parttest4 values (2025);

...in very simplified terms. Some of the tables also have some partitioned global/local indexes. I wish to drop the maxvalue partition (to use SET INTERVAL instead) but:

DROP PARTITION --causes data loss. Unacceptable
MERGE PARTITION --violates "VALUES LESS THAN (2020)" if I try to merge the P_2020 + P_MAX

What should I do? Split the P_MAX? What precautions should I take?

Thanks


texts: Can multi indexing speed up "AND" of comparisons?

texts:
I am using PostgreSQL and have query of type:

SELECT * from t
WHERE a > 30 AND b < 20

where a and b are columns of table t. I have indexed columns a and b. Would that speed up this query or do I need to add a multi index (a, b)?

This post mentions that multi index speeds up query of type a = 3 and b = 4 and a = 3 but I am not sure if same applies for comparisons.

Also official PostgreSQL docs says that it can speed up queries of type SELECT name FROM test2 WHERE major = constant AND minor = constant, but in the later text it says many things which I don't really understand and hence asking this question here.


texts: Do I need to Vacuum DB after massive insert?

texts:
I use Postgres 12.7
I did massive insert using CopyManager.

Input: 320GB of tab separated data

Sample row is:
2021-03-16T07:14:41.121000  588467  1900000 0   0   0   0   S   0   LITTLE_CHAR

-- around 60 bytes
Table schema is:
create table my_table
(
    time         TIMESTAMPTZ NOT NULL, -- 8 bytes
    price        integer     NOT NULL, -- 4 bytes
    size         bigint      NOT NULL, -- 8 bytes
    bid          integer     not NULL, -- 4 bytes
    ask          integer     not NULL, -- 4 bytes
    openInterest integer     not NULL, -- 4 bytes
    marketCenter integer     not NULL, -- 4 bytes
    conditions   varchar(8)  not NULL, -- 8 bytes
    side         smallint    not NULL, -- 2 bytes
    symbol       varchar(16) not NULL  -- 16 bytes
                                       -- 8*2 + 4*5 + 8 + 2 + 16 =
                                       -- 16 +  20 +     10 + 16 = 62 bytes per row
);

It took 1TB on Postgres side. Three times more, what went wrong?
i was advised to run VACCUM. Why would I if there were only inserts? 24 hours of inserts.


texts: Shrinking data file to save disk space before migration

texts:
I know this is most discussed answered on various forums: I completely understand answer is no in most of the time or cases.
However want to know if it can be good approach under any scenario:
Lets say for current prod DB which in our case is over 30 TB and have retention of data for 1 year. Tables are mostly compressed and partitioned.
Development team came up with logic where they dont need data to stay upto year and want to change retention to 6 months. So it is expected data would be reduced to half after purge and just having 6 months data going forward.
This is where we have been requested to work on shrinking data files so that those LUNS with TB of space could be returned or if we migrate to new sql version we would be requesting lets say few (approx10) TB's less which do save some money, assuming we keep 5Tb extra for growth in database in future.
In this case would shrink help if we go with shrinking in small chunks assuming in low peak time blocking may be accepted or if shrink is run for longer duration can be canceled?
How much worth is the above shrink data file activity or is there a better approach?
Edit --> We are migrating to latest version possibly SQL 2017or SQL2K19. The tables are not partitioned based on filegroups, as for this databases all the 30+ files are on primary. I know, its just a vendor DB design and dont have much in my hand.


texts:
Should I add INDEX to my VARCHAR column? I need to select rows with a string, not with ID, will it speed my queries?

Example:

SELECT * FROM `cities` WHERE `slug` = 'new-york';


texts: What's a good (free) client for MySQL on Windows?

texts:
I am almost repeating a question done here by another user, but related to MySQL. What do you think is the best free (if any) MySQL client software?

Thank you!


texts: Creating a summary table for an averaging query

texts:
In relation to another post of mine regarding slow performance for an averaging query on a table with ~30 million rows, I tried creating a summary table to store the averages.

Firstly, I have a table of activity_scores and members. Below is an example of the activity_scores table:

 id | member_id | score |         created_at         |         updated_at
----+-----------+-------+----------------------------+----------------------------
  1 |         8 |    73 | 2016-11-04 05:32:55.564235 | 2016-11-04 05:32:55.564235
  2 |        10 |    20 | 2016-11-04 05:22:55.564235 | 2016-11-04 05:22:55.564235

The scores are inserted every 10 minutes over time. In response to ypercube, I created a summary table with the constraint unique(member_id, date) for quicker access during a graphing process.

Then, I wrote the following query to store the average of scores for 'yesterday' per member into a summary table.

INSERT INTO activity_score_daily_averages (member_id, date, average_score)
  SELECT
     activity_scores.member_id, TO_CHAR((activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')), 'YYYY/MM/DD') AS date,
     AVG(activity_scores.score) AS average_score FROM activity_scores INNER JOIN members ON members.id = activity_scores.member_id
     WHERE
       NOT EXISTS(
         SELECT 1 FROM activity_score_daily_averages
         WHERE activity_score_daily_averages.member_id = activity_scores.member_id
         AND activity_score_daily_averages.date = date
       )
       AND activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC') > (current_date-1) AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')
       AND activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC') < (current_date) AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')
     GROUP BY member_id, date

Yet, running the above query took ~5 minutes. I feel as though there is a lot of room for improvement considering that the table I am working with isn't even considered that large for some of the DBMS masters out there...

Some things I was considering for optimizing this query were:

Somehow using a CTE or variable(?) for COALESCE(members.zone, 'Etc/UTC')
Find a different way to handle DUPLICATE KEY exceptions for entries that have the same member_id and date (i.e., replace WHERE NOT EXISTS(...) with something else)

I would appreciate any constructive criticisms and/or pointers.


texts: Why does TempDB spill happen even though statistics are correct?

texts:
I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get any help from here.

Question: How much data is spilled into disk? 400 MB or 60 MB(7643KB*8)?

in the article he states:

And no matter how many times I update statistics, I’ll still get a
~400MB spill to disk.

I am kinda confused here(

Question: If everything is okay with estimates, stats are up to date, box has sufficient memory, and no queries were running at that time, then why does spill to disk happen?

look at the estimated number of rows versus the actual number of rows.
They’re identical. The stats are fine.
I’m not using a small server, either: my virtual machine has 32GB RAM, and I’ve allocated 28GB of that to SQL Server. There are no other queries running at the same time – it’s just one lonely query, spilling to disk...


texts: Anything obvious in optimization missed in my left join query?

texts:
I've really simple join statement, which take a lot time to complete. I would like to know if I'm missing any obvious optimization steps i could take.

So I've got two tables, with the following create statements:

CREATE TABLE `objects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `positive` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  `feature_id` int(11) DEFAULT NULL,
  `cell_size` int(11) DEFAULT NULL,
  `edge_size` int(11) DEFAULT NULL,
  `file_name` char(255) DEFAULT NULL,
  `frame_number` int(11) DEFAULT NULL,
  `left_coord` tinyblob,
  `right_coord` tinyblob,
  `image_info_id` int(11) DEFAULT NULL,
  `image_data_id` int(11) DEFAULT NULL,
  `image_data_size` tinyblob,
  `opcode` char(50) DEFAULT NULL,
  `object_name` char(50) DEFAULT NULL,
  `pose` char(50) DEFAULT NULL,
  `tilt_angle` float DEFAULT '0',
  `weight_id` float NOT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `video_data_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `image_info_id` (`image_info_id`),
  KEY `feature_id` (`feature_id`),
  KEY `on_ind` (`object_name`),
  CONSTRAINT `feature_link` FOREIGN KEY (`feature_id`) REFERENCES `features` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `image_info_link` FOREIGN KEY (`image_info_id`) REFERENCES `image_info` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1283145 DEFAULT CHARSET=latin1;

And this one:

CREATE TABLE `uchog7x7` (
  `id` int(11) NOT NULL,
  `uchog7x7` blob,
  `iuchog7x7` blob,
  `needs_update` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `needs_update` (`needs_update`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I'm performing the following query:

SELECT
    objects.id
FROM
    objects
        LEFT JOIN
    uchog7x7 ON objects.id = uchog7x7.id
WHERE
    uchog7x7.needs_update = 1
        AND uchog7x7.id IS NOT NULL
        AND object_name = 'face'

This query takes a lot of time to complete. Table named objects has 1,035,526 records, table uchog7x7 contains 639,322 objects.

When I do explain I get the following:

I'm not using file-per-table.

Am I missing something obvious explaining why does it take a lot of time to complete? My guess is that somehow non-desirable full table scans happen, but I do not know for sure.

Thank you.


texts: Details about existing roles and users in azure sql database

texts:
I would like to get details of all users existing in a sql server DB and the respective roles they are part of and the permissions granted/denied for the a role/user.

what is the best way to get this details?


texts: what happens to WAL files when standby server is in recovery

texts:
in the case where we have SERVER1 (prod db) and SERVER2 (hot standby), what happens to the WAL files from SERVER1 when SERVER2 goes into recovery mode and is opened?

On SERVER1, the ARCHIVE_COMMAND looks like this

test ! -f SERVER2:$PGDATA/pg_xlogArchive/%f && scp %p SERVER2:$PGDATA/pg_xlogArchive/%f

on SERVER2, the RESTORE_COMMAND looks like this

cp -f /vol2/postgres92/pgsql/data/pg_xlogArchive/%f /vol2/postgres92/pgsql/data/pg_xlog

all works ok, and when the trigger file is created both SERVER1 and SERVER2 are available for writes. But as the ARCHIVE_COMMAND on SERVER1 remains in place I was expecting the WAL files to continue to be shipped to SERVER2 into the SERVER2:$PGDATA/pg_xlogArchive/%f location, but they aren't. So how does SERVER1 know to stop shipping the logs?

thanks
Dave


texts: SQL Server - Files timestamps unchanged so what is it that's being backed up?

texts:
Please see the below snapshot, showing the timestamps of all the data files as well as transaction log files for one of the SQL Server instances under my management. This is a production instance.

Today is Dec 27th, 2019. However, as you can see, all the timestamps are showing much older times.
The truth is, I took this snapshot a few days ago on 24th Dec. However, as I checked today, the situation is still unchanged.
The snapshot above was taken from the user databases. I checked the system databases as well, which are residing on a separate drive, and took the snapshot of the files' timestamps, which were as shown below:

These databases are certainly not super active. But, I am certain that there are transactions running all the time. Additionally, I looked at the "Schema Changes History" report for a few of them, and could confirm that our maintenance jobs have been working and rebuilding indexes in as recently as yesterday 26th Dec.
The databases are all in FULL recovery model (although that may not be relevant - just a fact to mention).  The OS is Windows NT 6.0 (= Windows Server 2007) and SQL Server version is 2008 SP3. Both the OS and SQL Serve are due for upgrade next year Q1. Also as is evident from the above snaps, with data files and log files both residing in the same disk drive and folder, the configuration of this server which I have "inherited" is certainly not the best. So, hopefully all will be fixed during the planned upgrade/migration activity which is going to happen in a few months.
Now the question for me is why the timestamps of these files are not getting updated, despite the databases being active anyway? (even manually running of Checkpoint didn't change anything). and then, if the transaction log files are not getting updated, will the backups be good and reliable? Do they really contain the records that they are supposed to?


texts: Select arbitrary amount of rows from child table and join them into parent table as columns

texts:
Good evening StackExchange DBA's. I've been asked to pull pull back every piece of information from on of our machines and export it. The problem in is, all of the values in question are spread between a few tables that have a column structure like (parent_id BIGINT, attribute VARCHAR(40), val INT).

Here's a simplified idea of what our database structure looks like.

create table parent (id BIGINT, name VARCHAR(45));
create table child (parent_id BIGINT, attribute VARCHAR(10), val INT);

INSERT INTO parent (id, name) VALUES (1, 'Adam');
INSERT INTO parent (id, name) VALUES (2, 'Brent');
INSERT INTO parent (id, name) VALUES (3, 'Cathy');

INSERT INTO child (parent_id, attribute, val) VALUES (1, 'height', 67);
INSERT INTO child (parent_id, attribute, val) VALUES (1, 'weight', 185);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'height', 63);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'weight', 162);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'arms', 2);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'height', 71);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'weight', 179);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'arms', 2);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'legs', 2);

Ideally, I'd like to return all the values and attributes of a parent in the same row but each parent will have vastly different attributes as it's children.

This is the closest I've come to what I'd like to see. Is there any improvement to be had in this query? Does anybody know what these types of queries are called so I know what to google?

select a.id, a.name, b.attr, b.val
from parent a INNER JOIN
  (select distinct parent_id,
   GROUP_CONCAT(attribute) as attr,
   GROUP_CONCAT(val) as val
   from child group by parent_id) b
ON a.id = b.parent_id;

Thanks


texts: How can I force a minimum isolation level in a procedure?

texts:
How can I force a minimum isolation level in a procedure in mssql?

I can use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" inside my procedure to set the transaction-level. But this command will downgrade the isolation level if the caller/executor uses e.g. "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;" How can I force a minimum isolation level without downgrading a higher isolation level?

What happens if I commit the "nested" transaction in my procdure? Will the locks acquired by the nested transaction remain until the outer transaction (of the caller) is committed?


texts: ODBC Driver Manager Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303) - Windows Server 2012 R2

texts:
I have a problem with adding MySQL server via linked server to my SQL Server Database

Following this instruction unfortunately just does not work out for me.

I added four data sources, 2 using 64-bit mySQL ODBC drivers and 2 using 32-bit drivers:

Test connection brings me success

Linked server configuration:

But I am still getting

Software used:

Windows Server 2012 R2 64bit
MySql Connector/ODBC 8.0 32bit/64bit
Microsoft ODBC Driver 17 for SQL Server
Microsoft SQL Server Managment Studio 2018

What am I doing wrong?


texts: mongodb: Can I create an unshard Replica set for a sharded cluster?

texts:
I have a 3 node shard cluster mongoDB with replica set, and want to add (single node replica set) a un-sharded Replica set to collect all data in one node.

Can I create an unshard Replica set for a sharded cluster?


texts:
I often need to select a number of rows from each group in a result set.

For example, I might want to list the 'n' highest or lowest recent order values per customer.

In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). This part is definitely optional/for extra credit and not intended to dissuade people from answering.

What are the main options for solving these types of problems in SQL Server 2005 and later? What are the main advantages and disadvantages of each method?

AdventureWorks examples (for clarity, optional)

List the five most recent recent transaction dates and IDs from the TransactionHistory table, for each product that starts with a letter from M to R inclusive.
Same again, but with n history lines per product, where n is five times the DaysToManufacture Product attribute.
Same, for the special case where exactly one history line per product is required (the single most recent entry by TransactionDate, tie-break on TransactionID.


texts: What is the most effective way to manage a community table with one customizable flag?

texts:
Fair warning
I do not have dba training or anything of the like but I am managing a rather large web app database. I know the basics but I'm not sure how to handle this latest scenario.
Setup
I have about 300 different databases. 1 common database that all clients share for pulling static info and the remaining are 1 for each individual client.
Scenario
I have a table of about 130k items that never change. Not the largest table in the system but still a fair size. I have about 300 copies of this table currently. One for each client. They are all identical except for 1 simple enable/disable flag. I also have a single database where I store common information for all clients such as templates and fixed db lists that do not contain the enable/disable ability.
Question
Is their a way to store this mostly common db table in my "community" database and still allow individual "client" databases to have the enable disable flag?
My Thoughts
I know I could just do a simple [id,enable] table in the client db but that would add a join to the system. Then the question is: does the effect of the join on 130K entries worth the saved space? I just feel like I'm missing something obvious with this whole scenario.


texts: Optimize query that only returns when the number of rows to be returned is less than an amount

texts:
I have a query that first counts the number of rows to be returned and, if it's below a limit, it returns those rows, else it returns nothing.

For context: I have a map that displays some points only after a certain zoom level. However, my users now want that the map displays the points as long as there is space in the screen, instead of having a predefined minimum zoom level. This means that now whenever they move across the map, I'll have two queries, instead of one: one to know how many items there are, and another one to actually return them.   The load will potentially double. I thought about doing it this way:

SELECT p.ACOLUMN, p.ANOTHERCOLUMN, p.LATITUDE, p.LONGITUDE
FROM table p
where p.LATITUDE < @latMax)
and p.LATITUDE > @latMin)
and p.LONGITUDE < @lngMax)
and p.LONGITUDE > @lngMin)
and (SELECT count(*)
FROM table
where LATITUDE < @latMax
and LATITUDE > @latMin
and LONGITUDE < @lngMax
and LONGITUDE > @lngMin)) < 200 -- example limit value

Looking at the actual execution plan, I see that SQL Server 2017 is seeking on the same index twice:

It's also of course reading the same amount of rows in both seeks. Is there a way to remove those duplicate seeks? Or is there maybe another, more optimal way to do what I'm trying?


texts: pg_upgrade from Postgres 9.5.5 to Postgres 12.3 errors out

texts:
I am trying to upgrade the database cluster from 9.5.5 to 12.3 using pg_upgrade.
I ran it with the --check option, and the result came back "clusters compatible", but when I run it with the --clone option, I get the error below. The log does not tell me where it’s failing.
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
executing: SELECT pg_catalog.set_config(‘search_path’, ‘’, false);
Checking for presence of required libraries         ok
could not clone file between old and new data directories: Operation not supported


texts: Mysql update table using ubuntu terminal

texts:
I have a CSV file that has 2 columns

Name
Email

I have a table in my database that has the same columns + some extra columns.

Now I would like to update my db table using that csv file. I mean like this

UPDATE `mytable` SET `email` = 'csvemail' WHERE `name` = 'csvname'

Can someone help me to do that from my ubuntu terminal?


texts: SQL server 2012 inner join estimated number of rows issue

texts:
I have a simple join like the one below:

select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK

The fact table contains a little over 500 million records (with NC columnstore), the query will return all of them, however, the estimated number of rows after the join is only 300 million. Up till the hash join, the estimated number of rows are correct, only after the join it drops to 300 million. Here's the estimated plan for the query:

I've updated the stats on the SK column used in the join in both the fact table (with fullscan) and the dimension table, here's the histogram for each:

This issue only seems to occur for a couple dimension tables in the database, joining the other dimension tables doesn't produce the same type of cardinality estimate issue - any suggestions on how to possibly fix this or further investigate?

If I add a where clause to the query, it correctly estimates the number of rows before/after the join, e.g.

select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK where company_SK = 1

will estimate 467,583,000 rows coming out of the join, which matches what's in the histogram.

The issue seems to only occur when I don't have any filter in the query. It's causing a problem in a bigger query (sort spill). I've narrowed it down to this particular join.

I do have an FK constraint, but they have been turned off (WITH NOCHECK) on the fact table (we were told to turn them off so ETL can go faster). Unfortunately, turning the FK back on is not an option :(

Update: enabling trace flag 2301 solved the issue :p


texts:
I have a MongoDB with compound index by 4 fields and I want to reorder physical data distribution at HDD by this index. I am looking for something like creating chunk in NOT sharded environment.
Is any way to do this?

UPDATE:
As workaround solution I can enumerate my collection by index and write it to another collection in this order. I didn't test it, but it should make sence.


texts: Is password complexity policy linked to Active Directory in SQL Server

texts:
We have a change in policy in the organisation and a password complexity policy will be applied to active directory. I know when Enforce password complexity is checked on a SQL Server login it gets it rules for this via windows.

However I am unsure if it is applied via AD, will this apply to SQL Server via Windows or have no effect?