texts: "No database selected" or "Table 'DB.table' doesn't exist" failures in pt-upgrade run where queries from multiple DBs were collected with tcpdump

texts:
I collected queries on our Master using the following tcpdump command:

tcpdump -i any -s 65535 -x -n -nn -q -tttt 'port 3306' > tcpdump.tcp

The queries are executed amongst several databases.

I then ran this file through pt-query-digest using the following command:

pt-query-digest --output=slowlog --no-report --sample 100 --type tcpdump tcpdump.tcp > slow.log

Then I ran pt-upgrade against two Slaves like this:

pt-upgrade --user user --ask-pass --run-time=1h --upgrade-table percona.pt_upgrade h=10.1.1.1 h=10.1.1.2 slow.log

But I got a bunch of failures since it does not appear to specify which database the query should be executed against.

How is one supposed to use pt-upgrade when queries are collected amongst multiple DBs? AFAICT this isn't specified in the documentation anywhere.

Are you supposed to use --filter with pt-query-digest to just output queries for a particular database and then specify --database with pt-upgrade? Rinse and repeat per database.

It takes several hours to parse my gigantic tcpdump capture so any guidance here is appreciated.

Thanks!

P.S.

I used this article as a starting point but it is outdated. e.g. pt-query-digest doesn't have a --print option anymore.


texts: Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?

texts:
I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). Since it's the Django internals, the query cannot be changed.

There are 3 tables, A, B, and C. The query looks like:

SELECT *
FROM A
   INNER JOIN B ON (A.b_id = B.foo)
   INNER JOIN C ON (B.foo = C.id)
ORDER BY A.id DESC
LIMIT 100

A simple join-3-tables together.

The id fields are primary keys and have indexes. A.b_id, B.foo both have their own indexes.

However the query plan looks wrong and says it's not using any keys on B (but it is using the keys for the other joins). From reading lots of MySQL performance stuff it should in theory be using indexes, since it's various const joins that can 'fall through'. It says it has to scan all ~1,200 rows of B.

The weird thing is that I OPTIMIZEed each one on my local machine and re-ran the queries (with SQL_NO_CACHE) and it was much faster, 0.02sec vs. the original 20sec. EXPLAIN on the same query gave a different, and much more sensible result, showing that it can use an index on each one, and that it doesn't have to scan the whole lot. A co-worker ran OPTIMIZE for each one on a testing machine with approximately the same data (which was recently recreated from a loaded dump file) and it also showed a speed increase, and a sensible-explain.

So we ran that on the live system… and it didn't change anything (the speed nor explain). I recreated my MySQL database (DROPed the database and reloaded from a dump), and now the OPTIMIZE doesn't change anything (i.e. ~20sec run time, bad query plan).

Why does this happen? How can I get MySQL to use the correct indexes and get back my 0.02s query time? This blog post ( http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/ ) implies that OPTIMIZE only really optimizes the primary key (not that b_id, foo are not primary indexes). How can I "rebuild secondary index"? I tried doing ALTER TABLE A ENGINE=InnoDB (and the same for B & C) and there was no change.

Actually the more I look at this, the more it seems to be a MySQL query plan fail. It's doing the wrong query plan and not using indexes that it could use. After doing an OPTIMIZE TABLE etc., it is sometimes able to use the correct indexes. When there's a bad query plan like this, it can be sorta randomly selected, that's why (I think) different machines have different results.


texts: How to read jsonb or json in postgresql spi?

texts:
How can I read a json/jsonb field in a PostgreSQL server side function?

I am trying to write a server side function using C and SPI for PostgreSQL 11. The json field in mind is simple and contain only unnested key (text) and value (float8) pairs. I'd like to read these key value pairs into my C code and process it.

But after much searching on Google and here at DB.SE, I can't find any documentation on this. The results I got are for using json/jsonb on the client side, e.g.:

Pushing or appending to JSON array in PostgreSQL 9.4

https://stackoverflow.com/questions/32458524

I see there is PG_GETARG_JSONB, e.g. here. But it is buried beneath other things and seems to require an external library.

Any pointers are appreciated.


texts: When and how to create users in a MongoDB sharded cluster

texts:
I hope my question is clear enough.
I'm setting up a sharded cluster, 3 members, each running as a replica set. So, on each shard there are 3 mongod running as replica sets.

My question is what should be the time during the setup process to create users&roles and on what instance of mongod?
I know about the localhost exception on a mongo db deployment, but it's not clear from the documentation what would be the best way to do it in a sharded cluster.
I have the following users:

useradmin -> able to create new users only -> role userAdminAnyDatabase (to create new users)
manager -> use to manage the cluster -> role clusterAdmin
root -> root access/roles -> role root
appuser -> role readWriteAnyDatabase

So the options I see are:

On each replica set mongod, using localhost exception, before it's added to the sharded cluster, add all users from above?
At the end of setup, only once in the config replica set?
At the end, connecting to the sharded cluster?
Create initial users only with createUser role on each mongodbs and then create the others connecting at the end to the sharded cluster?

I did using 1st option, but I feel something is not right.


texts: Postgres: SELECT with ORDER BY is 4x slower when using index

texts:
I have a table with 4 columns:

id SERIAL PRIMARY KEY
dt DATE
val bigint  -- Already has a btree index
txt VARCHAR(64)

I am running a query:

EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;

This was taking ~13ms and below is the output of EXPLAIN ANALYZE

Sort  (cost=2862269.49..2912269.54 rows=20000020 width=12) (actual time=10561.801..12214.518 rows=20000020 loops=1)
  Sort Key: dt
  Sort Method: external merge  Disk: 508816kB
  ->  Seq Scan on temp_test  (cost=0.00..436917.25 rows=20000020 width=12) (actual time=0.016..2451.364 rows=20000020 loops=1)
Planning time: 0.127 ms
Execution time: 12874.978 ms

I presumed that creating an index on dt will speed things up and I did

CREATE INDEX temp_test_dt_btree ON temp_test(dt);

Once index was created, I executed the same query again:

EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;

This one took ~54s which is around 4x more than one without index with the following output for EXPLAIN ANALYSE

Index Scan using temp_test_dt_btree on temp_test  (cost=0.44..1317015.07 rows=20000020 width=12) (actual time=0.049..53027.137 rows=20000020 loops=1)
Planning time: 0.241 ms
Execution time: 53742.120 ms

I am a little confounded as I expected the index to improve the performance at best and have little (or no) impact at worst.

I am running:

PostgreSQL 10.11 (Ubuntu 10.11-1.pgdg18.04+1)


texts: How to allow connections to SQL Server with 2 differents ip

texts:
I have a small issue here: I have a SQL Server instance 2012 (let's call it SQLINSTANCE) on a cluster. I also have 2 network interfaces for this instance:

- SQLINSTANCE.my.network
- SQLINSTANCE.mgt.my.network.

My instance listens on a specific port, 1550.

If I want to connect to it here are my results:

- SQLINSTANCE.my.network\INSTANCE: working
- SQLINSTANCE.mgt.my.network\INSTANCE: NOT working.

My guess is that it involves the SQL Browser, but I can't find how to tell it that it needs to listen on both addresses.

I'm working on a Windows Server 2008r2 without firewall. Both addresses are set as resources for the SQL Server in the cluster manager.


texts: Will database replication increase the size of the replicated database?

texts: First thank you for your time. We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the replication the data file is 4 times bigger around 19 GB. Any pointers to see what could happened will be appreciated. I know sql as developer and some little management but nothing about replication. The performance of the database has been damaged very bad so I am trying to see what need to be done to improve it. I do apologize for my English as well.


texts: Reset my root password, now I can't see my main database

texts:
I was getting access denied for root@localhost. To resolve this, I stopped the mysql services, ran mysqld --install and mysqld --initialized, added skip-tables in the my.ini file and was able to log back in with the root user got the password to reset. Cleared the skipped-tables from the my.ini file. Flush the privileges, granted the root all access. Put in the command "show databases;" but it only shows these databases and its missing one.

information_schema
mysql
performance_schema
sys

I looked at the my.ini file to see where the datadir was, looked at the path and I see the folder and the ibd file where the database resides, but it still does not show in MySQL. Any help on this would be greatly appreciated.


texts: Restore differential backup error - no files are ready to rollforward

texts:
I'm trying to restore a differential backup of a test database, but I'm getting a error saying:

What I have done is created a test database, called "tdb", where I added some data in a new table, changed the recovery model of the database to SIMPLE and then created a FULL Backup.

After that, I added some more data into a new table and continued with making a differential backup.

When restoring the database I restored the full backup first, chosing the Overwrite existing database (WITH REPLACE) option from SSMS, which worked correctly.

Then when trying to do the same thing with the differential backup file, I get the above error.

I've looked at the Microsoft documentation as well and I've followed the same steps mentioned there, but I don't see what I've done wrong. Any help is appreciated.


texts: No data in Azure SQL after importing bacpac file

texts:
I've created a bacpac file of my database for transfer to Azure. The file is uploaded and imported successfully but there's no data in the imported database. What did I do wrong? There don't seem to be options about with or without data. The structure did end up correctly in the database.

I verified the bacpac by importing it locally.


texts: Which SQL Developer Version will be compatible with Oracle 9i

texts: My Company is using Oracle 9i. I want to connect to the database. I'm planning to use SQL Developer. Which version will be compatible Oracle 9i?


texts: How to use COALESCE with DISTINCT with multiple rows and without preceding comma

texts:
With reference to How to use COALESCE with multiple rows and without preceding comma? ...

I have a similar scenario but with a small difference. I would be grateful if any expert could help me with it.

Data

area | city | state
1 | a | a
2 | a | a
3 | b | a
4 | b | a
1 | a | b
2 | a | b
3 | c | b
4 | c | b

Desired Results

Now I would like to retrieve the results as:

area | city | state
1,2,3,4 | a,b | a
1,2,3,4 | a,c | b

Retrieved Results

I tried myself with the above mentioned solution, but I am receiving the following result set:

area | city | state
1,2,3,4 | a,a,b,b | a
1,2,3,4 | a,a,c,c | b

I need city to be distinct.
I hope I have explained good enough?


texts: What can be the downside of always having a single integer column as primary key?

texts:
Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM.

However, in order to have a simple design for the generic repositories, all involved tables must define an unique integer (Int32 in C#, int in SQL). Until now, this has been always the PK of the table and also the IDENTITY.

Foreign keys are heavily used and they reference these integer columns. They are required for both consistency and for generating navigational properties by the ORM.

The application layer typically does the following operations:

initial data load from table (*) - SELECT * FROM table
Update - UPDATE table SET Col1 = Val1 WHERE Id = IdVal
Delete - DELETE FROM table WHERE Id = IdVal
Insert - INSERT INTO table (cols) VALUES (...)

Less frequent operations:

Bulk insert - BULK INSERT ... into table followed (*) by all data load (to retrieve generated identifiers)
Bulk delete - this is a normal delete operation, but "bulky" from ORM's perspective: DELETE FROM table where OtherThanIdCol = SomeValue
Bulk update - this is a normal update operation, but "bulky" from ORM's perspective: UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue

*all small tables are cached at application level and almost all SELECTs will not reach database. A typical pattern is initial load and lots of INSERTs, UPDATEs and DELETEs.

Based on current application usage, there is very small chance of ever reaching 100M records in any of the tables.

Question: From a DBA's perspective, are there significant problems I can run into by having this table design limitation?

[EDIT]

After reading the answers (thanks for the great feedback) and referenced articles, I feel like I have to add more details:

Current application specifics - I did not mention about current web application, because I want to understand if the model can be reused for other applications as well. However, my particular case is an application that extracts lots of metadata from a DWH. Source data is quite messy (denormalized in a weird way, having some inconsistencies, no natural identifier in many cases etc.) and my app is generating clear separated entities. Also, many of the generated identifiers (IDENTITY) are displayed, so that the user can use them as business keys. This, besides a massive code refactoring, excludes usage of GUIDs.
"they should not be the only way to uniquely identify a row" (Aaron Bertrand♦) - that is a very good advice. All my tables also define an UNIQUE CONSTRAINT to ensure that business duplicates are not allowed.
Front-end app driven design vs. database driven design - design choice is caused by these factors

Entity Framework limitations - multiple columns PKs are allowed, but their values cannot be updated
Custom limitations - having a single integer key greatly simplifies data structures and non-SQL code. E.g.: all lists of values have an integer key and a displayed values. More important, it guarantees that any table marked for caching will be able to put into a Unique int key -> value map.

Complex select queries - this will almost never happen because all small (< 20-30K records) tables data is cached at application level. This makes life a little harder when writing application code (harder to write LINQ), but the database is hit much nicer:

List views - will generate no SELECT queries on load (everything is cached) or queries that look like this:

SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)

All other required values are fetched through cache lookups (O(1)), so no complex queries will be generated.
Edit views - will generate SELECT statements like this:

SELECT allcolumns FROM BigTable WHERE PKId = value1

(all filters and values are ints)


texts: how to insert data from one table to another

texts:
SELECT a.nid, a.stock, b.qty, sum( b.qty )
FROM uc_product_stock a
INNER JOIN uc_order_products b ON a.nid = b.nid
GROUP BY a.nid

the above query give me the following result:

nid stock   qty sum(b.qty)
10  746     1   4
11  256     4   11

now i want to insert this in my custom table. For eg, the structure of custom table is:

id | orderIn | orderOut
10  |   746   |   4
11  |   256   |   11

Iam using this query, but it gives error:

insert into custom (id, OrderIn, OrderOut)
select
a.nid,
a.stock,
b.qty,
sum(b.qty)
from
table1 a inner join table2 b on a.id=b.id
group by a.id


texts:
I've seen a few instances of entities which have a field which can be either a reference to another entity or free text.

You typically represent this in the UI by having a dropdown widget which can optionally have text instead of one of the dropdown items selected.

Say you have a song entity which was recorded by an artist- the artist can be either a reference to an artist entity or a random string.

I'd usually represent this like:

create table artists (
  artist_id      primary key,
  [...]
);

create table songs (
  [...]
  artist_id      null foreign key references artists(artist_id),
  artist_text    null text,

  -- optionally add a constraint that checks that one of these fields
  -- is null, and the other is not
);

If you are particularly averse to nulls, you might have:

create table songs (
  song_id       primary key,
  [...]
);

create table known_artist_songs (
  song_id       primary key references songs(song_id),
  artist_id     foreign key references artists(artist_id)
);

create table unknown_artist_songs (
  song_id       primary key references songs(song_id),
  artist        text
);

, but it seems a bit overkill to me. You could also force the creation of an artist row in any case, but sometimes this doesn't seem appropriate.

Are there any better approaches?

Cheers,

Álex


texts:
My company hosts a mysql database on google and we do not have complete control over the customization involved. Recently we attempted to turn performance schema on to help debug some common complaints about our software. During the restart that was required after adjusting the flag the database was continually failing to start which caused massive problems with our customers as you can imagine.

At this point it looks like performance schema and everything that falls under its umbrella is essentially out of the question. Are there any other tools or ways to analyze a mysql database if the performance schema features are inaccessible? Aside from looking at the execution plan of every query manually.


texts: Syntax to find text in string, then pull out following text until a comma is identified?

texts:
I am trying to write a query that will extract partial information out of a longer string, but the string is not always consistent so I need to identify certain parts and extract based on that.

The string is legal description info on property parcels and is structured like this: "Lot D, Plan VIP12345, Section 70, Suchandsuch District".  I am trying to get the "VIP12345" part as a separate field.

Initially I considered exporting this field as a CSV table and then separate the info into unique sections that way, but this is a dataset that gets updated regularly by replacing the entire dataset, so every time it is updated I would have to export the table, make the field a CSV table, reimport that into separate fields and then export that into something my GIS can join to.

I would rather have my GIS link to the original table with an SQL query to sort out where the Plan# is (the "VIP12345" part).  However, the Plan# does not always start with VIP, sometimes it has no letters at start, sometimes it is VIS, sometimes EPP.  The Plan# is also not always the same number of digits, sometimes it is 4 sometimes 5, sometimes 6, so i can't just count # of characters to extract from a certain point.

So I am thinking I need an SQL query that in plain language would do this: In field [FIXLGL] Look for the string 'Plan ' then extract all characters following that until you see a ','

I am having troubles figuring out what SQL acrobatics to use to accomplish this.  I can identify records that have a plan# ([FIXLGL] LIKE '%Plan %') but then how to I tell the query that the important part is what is after that?


texts: How avoid updating unnecessary columns in Update statement?

texts:
I have an Update statement which update several columns. Some of those columns are nvarchar(max) type and not always need to be updated.

I would like to avoid updating unnecessary columns, so, the simplest solution I come up is the following:

UPDATE table1 SET column1 = @col1, Column2= @col2, ...
TextColumn = CASE SomeVar WHEN 0 THEN @NewVal ELSE TextColumn
WHERE idTable = @idTable

Does any one know if SQL is smart enough to avoid updating the TextColumn if it is going to be updated with itself?

The second option is just to split the Update as follows:

    UPDATE table1 SET column1 = @col1, Column2= @col2 ...
    WHERE idTable = @idTable

   IF Condition
    UPDATE TextColumn = @TextVal WHERE idTable = @idTable

I do not know if there is a better alternative, maybe it is possible to dynamically choose the columns in the update sentence.

I will appreciate any suggestion about this, my main target is improve the performance of the Update

This question is similar to this one. However, that question does not address the posibility of spliting the Update statement in two sentences in order to avoid unnecessary updates. Moreover, in this case the column to update is nvarchar which involves a large amount of text which brings other performance considerations.


texts: Understanding of schema and tablespaces - recommendation for a later seperation

texts:
DLDR: I would  like to get

a confirmation of my understanding of tablespaces and schemas (see image)
a recommendation how to organize schemas and tablespace to make a later separation easy.

Background: In our project we have two different teams that are working on the implementation of a website. The idea is to later separate the basic-services from the website (cms / portal) so that different websites / portals can be created an all are using the same independent basic-services.

The current installation looks like this:

TABLESPACE_N  MB  AUTOEXTENSIBLE
FOOBAR_DATA    1024       YES
FOOBAR_INDEX   1024       YES
FOOBAR_LOB     1024       YES
SYSAUX         600       YES
SYSTEM         700       YES
TEMP          1024       NO
UNDOTBS1       456       YES

This question is about how to move a schema to another tablespace. Some recommendations store indexes in another filesystem are controversial / questionable. But this is not the scope of my question. I would to get a recommendation how to organize schema and tablespace to make a later separation easy.

Confirmation / Clarification: My underständing of oracle is the following:

Tablespace: logical storage unit, one or more datafiles
Datafiles: physical structure that conforms to the OS, a datafile can be associated with only one tablespace and only one database
Schema: a collection of logical structures of data (table, index), or schema objects (view,..), same name as the db-user that owns it, each user owns a single schema. [A USER may be given access to  SCHEMA OBJECTS owned by different USERS.]

In the attached image you can see two schemas:

white schema S1
and yellow schema S2

Question 1:

Is my understanding how a schema could be organized within the database correct?
So we could create two schemas (/ two users) and separate the objects for the FOOBAR-services (yellow) from the FOOBAR-website (white) in any way we want to?

Schema 1 could only use tablespace 1 with datafile 1 and 2?
Schema 2 could only use tablespace 2 with datafile 3?

Question 2:

What structure would you recommend to make a later separation of the FOOBAR-website and the FOOBAR-services very easy

different tablespaces for FOOBAR-Web and FOOBAR-Services?
each schema within its own tablespace (or n spaces)?


texts: Databases in 'Recovery Pending' and 'Backup:CommitLogArchivePoint' not committing

texts:
I've got a bit of an odd one that I'm wondering if anyone's seen before.

Last week I had a bunch of databases showing as 'Recovery Pending'.

Some digging at the time found that SQL was complaining about the trans log being full even though there was a ton of space on the disk and none of the logs were restricted in anyway.

All that the server log shows is 'The Transaction Log for the Database 'XXX' is full due to 'ACTIVE_TRANSACTION'

The AG was failed over and things are working fine but I've been doing some digging and can see what happened but don't understand why.

Basically, after a log backup takes it should update the boot page using a transaction named 'Backup:CommitLogArchivePoint'.  Looking at the databases, there is an active transaction 'Backup:CommitLogArchivePoint'.

With a spot more investigation and poking around in the transaction log I can see 'Backup:CommitLogArchivePoint' start but it never committed for the affected databases.  The boot page is updated but the transaction is left uncommitted which obviously means that the transaction log can't be truncated or (for some reason I'm guessing is because SQL thinks that something is changing the boot page) grown out.

Has anyone seen this behaviour before, it happened on a random handful of databases at various points during the day?  I've not seen any occurrences since failing over the AGs.  Any help in understanding what caused this would be great as I'm pulling my hair out here.

Thanks!


texts:
I've reached a point where mongo fails to start because there's no enough free disk space. The log suggests using the --smallfiles option to start the mongod service.

So, I have two options, either make the hard drive bigger (it's an Amazon free instance) or doing what the mongodb log suggested.

Would data get corrupted if I enable --smallfiles for an existing mongo instance? I wouldn't want to lose any data.


texts: TLS 1.2 breaks SQL Server linked servers

texts:
As part of a recent security blitz we set up TLS 1.2 on all of our servers. Last night I flipped the "Force Encryption" flag on all of our SQL Servers and since then I've had a weird issue with linked servers.

I have two servers;

Server A running SQL Server 2016 SP2 CU3
Server B running SQL Server 2012 SP4

Both have the correct encryption certificates on them, both have have the necessary registry edits to disable SSL and TLS 1.0 & 1.1, leaving just TLS 1.2 enabled. Both have the certificate set in SQL Server Config Mgr and "Force Encryption" set.

The linked server from Server A to B (2016 to 2012) works fine.
The linked server from Server B to A (2012 to 2016) shows the server and lists the databases, but any attempt to query the tables over that linked server generates the error:

  TCP Provider: The specified network name is no longer available. (Microsoft SQL Server, Error: 64)

I can however start a SQL Server Management Studio session on Server B and connect to Server A just fine. So its only the linked server that is having the problem.

I checked the extended events on Server A and I can see the trace event for the SSL handshake coming from Server B using TLS 1.2, so the connection request is clearly coming in with the correct encryption set.

Googling the error seems to indicate a name resolution issue, so I tried setting up other linked servers using both the FQDN and the IP address, but neither resolved the issue.

Any help at this point would save what remaining hair I have left.

To eliminate network issues, I went to one of my 2016 servers that has a 2012 instance on it for backward compatibility. The same issue. the 2016 instance can query a linked server to the 2012 instance. The 2012 instance cannot query a linked server to the 2016 instance. A second test between two 2016 instances had the same issue too.

The SQL Server Client Version is 11.4.7462.6.

Registry Settings:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001


texts: Is it recommended to install extensions into pg_catalog schema?

texts: Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?


texts:
I have to update the current SQL server installed in my PROD server from service pack 1 to 2. What all things i should consider before starting the upgrade and how to pre-determine if there are going to be any issues if we upgrade or what all will get impacted or where will it break or what precautions to be taken..does it need a restart ?

Need help in all various aspects - basically a step by step process would be very helpful for me as I will be doing it for first time.


texts:
On one side, I have a MySQL DB where there is table that has a timestamp field. According to MySQL doc, this MySQL TIMESTAMP is similar to DATETIME (with different bounds). As far as I know, the required form is : "Y-m-d H:i:s".

An extract from the DB :

date_heure TIMESTAMP NOT NULL

On the other side, I have a mobile app which sends data to the DB. The time is very important, so among this data (sent using JSON) there is a timestamp field. This timestamp is UNIX-like, in milliseconds.

As you guessed, my purpose is to insert the received timestamp in the database.

To do so, on server-side, I wrote the following PHP code :

$timestamp = $json_decode['t']; //get the UNIX timestamp from the JSON data
$sec_timestamp = $timestamp/1000; //in order to have seconds, not milliseconds
$mysql_timestamp = date("Y-m-d H:i:s", $sec_timestamp); //converting UNIX timestamp into MySQL timestamp.

However, in the DB, the timestamp field still has the following form, for every row : 0000-00-00 00:00:00

There is no problem on the app side, the UNIX timestamp (in milliseconds) is properly sent via JSON.

Hence, what can I do to insert this into the DB ?


texts: sql server connectivity broke down to server after its IP address has moved to a subnet. How to restore its connectivity?

texts:
I a server called Vermont on a different domain, with a trust relationship.

I can ping the server there.

But when I try to connect to it, from my SSMS I get the following error:

I used to connect to that server, and I have replication set up, and the Vermont Server is one of the publishers.

I called the people there and I have been told that they have moved the IP address of Vermont to a subnet.

Despite allowing sql server to connect through the firewall,
what else I need to put in place in order for me to connect to my local machine to the Vermont server?

I understand that the Vermont server needs to allow remote connection

I also understand that the sql protocols like tcp/ip and the ports must be open and set. 1433 for tcp/ip and let's say 1434 for named pipes.

I have domain accounts that would have permissions to connect to Vermont or any sql server server in either of our domains.

what else could be missing that I cannot connect to the Vermont server?


texts:
My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily basis. These archived rows are processed once a week for stuff like finding trends.

This archive table consequently grows at 1 million new rows every week, and querying it can get really slow. Is MySQL suited for archiving data, or is my strategy very flawed?

Please advise, thank you!


texts: Should I in general partition tables by tenant IDs in a multi-tenant system?

texts:
We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this:
create table Things(
    accountId varchar(64) not null,
    internalId varchar(64) not null,
    externalId varchar(256) as (concat(accountId, '-', internalId)) stored,
    ...
    primary key (accountId, sourcedId),
    unique (externalId),
    foreign key (accountId) references Accounts (id)
);

All queries either have an accountId in the where clause, or they use externalId. There are no cross-account queries.
We're expecting to have a total of 200 accounts. The sizes of the other tables (like Things) vary from 5 rows per account for some tables (1000 rows total) to 225K rows per account for some other tables (45M rows total). (These are the numbers we use for performance testing - they're the max numbers)
The DB size is ~150 GB. 95% of the scenarios are reads.
The RDBMS is Mysql 8.0.16 (AWS RDS).
We don't have any performance issues at the moment and we're not trying to make anything work faster. But I'm wondering if NOT partitioning the tables like Things by accountId is a "premature pessimization"?


texts: Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

texts:
I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made approximately about 15 people in different times. And it was normal practice to us to create foreign keys on the reference tables in different schemas because it is really convinient and keeps database very clean, because we don't need to keep the same refernce tables in a different schemas.

But anyway my DBA (who created new instance with DB and copy my application inside of the Solaris zone) said very harsh today, "Foreign keys on the different schemas is evil and you need to destroy it!". He didn't explain his point of view.

Is it really bad idea to do that with a big applications?


texts: How to get the oldest values over each id in a PostgreSQL table efficiently?


texts: Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED

texts:
I have a table called queue with items that need to be processed:
CREATE TABLE public.queue (
    id serial NOT NULL
        CONSTRAINT queue_pkey
            PRIMARY KEY
);

Another table process represents processed items from the queue (e.g. a report has been generated). In reality, there are more such tables (there are more processes that need to be performed on an item). There is one-to-one relation between queue and process – each item can be processed just once.
CREATE TABLE public.process (
    id            serial  NOT NULL
        CONSTRAINT process_pkey
            PRIMARY KEY,
    queue_item_id integer NOT NULL
        CONSTRAINT process_queue_item_id_key
            UNIQUE
        CONSTRAINT process_queue_item_id_8953ec7b_fk_datastore
            REFERENCES public.queue
            DEFERRABLE INITIALLY DEFERRED
);

Here are some test data:
BEGIN;
TRUNCATE TABLE queue, process
    RESTART IDENTITY CASCADE;
INSERT INTO queue
SELECT GENERATE_SERIES(1, 10000);
COMMIT;

The worker that processes the items is implemented as follows. The code is in Django (Python framework), but I am convinced that my error is not caused by Django or its ORM.
(For simplicity, there is no termination condition.)
while True:
    with transaction.atomic():
        queue_items = Queue.objects \
                          .filter(process=None) \
                          .order_by() \
                          .select_for_update(skip_locked=True, of=('self',))[:8]

        print('Generating report...')
        time.sleep(0.1)

        Process.objects.bulk_create(
            (Process(queue_item=q)
             for q in queue_items)
        )

Here is a transcript of the SQL queries that travel to the database:
-- while True:

BEGIN;

SELECT queue."id"
FROM queue
         LEFT OUTER JOIN "process"
                         ON (queue."id" = "process"."queue_item_id")
WHERE "process"."id" IS NULL
LIMIT 8 FOR UPDATE OF queue SKIP LOCKED;

-- print('Generating report...')
-- time.sleep(0.5)

INSERT INTO "process" ("queue_item_id")
VALUES (1),
       (2),
       (3),
       (4),
       (5),
       (6),
       (7),
       (8)
RETURNING "process"."id";

COMMIT;

If I start one worker, the queue is processed perfectly fine. If I run two or more workers, I start getting this error:
duplicate key value violates unique constraint "process_queue_item_id_key"
DETAIL:  Key (queue_item_id)=(**) already exists.

How another transaction could create rows in process for items in a queue when those rows are locked?
What I tried:

I tried to rewrite SELECT query with EXISTS:

SELECT "queue"."id"
  FROM "queue"
 WHERE NOT (EXISTS(SELECT U0."id", U0."queue_item_id" FROM "process" U0 WHERE U0."queue_item_id" = "queue"."id"))
 LIMIT 8
   FOR UPDATE OF "queue" SKIP LOCKED

without success, the same error occurs.

If I arrange the rows randomly, the error occurs much later (almost at the end of the queue).

SELECT "queue"."id"
  FROM "queue"
  LEFT OUTER JOIN "process"
    ON ("queue"."id" = "process"."queue_item_id")
 WHERE "process"."id" IS NULL
 ORDER BY RANDOM()
 LIMIT 8
   FOR UPDATE OF "queue" SKIP LOCKED

I put a breakpoint in the middle of the transaction and in another transaction I checked that the row locking in my opinion works correctly:

SELECT id
FROM queue
WHERE id NOT IN (
    SELECT id
    FROM queue
        FOR UPDATE SKIP LOCKED
);

My Postgres version: PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit.
Each worker has its own connection to Postgres with default isolation level (read committed).


texts: How can I determine why a concurrent Postgres index was marked as INVALID?

texts:
I'm trying to create an index concurrently.
CREATE INDEX CONCURRENTLY blocks_expr_idx6 ON blocks USING btree ('blah') WHERE "type" = 'user root';

12 hours after I ran the query, if I run \d blocks Postgres reports that the index is INVALID:
"blocks_expr_idx6" btree ('blah') WHERE "type" = 'user root' INVALID

The documentation says:

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID.
The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is to rebuild the index with REINDEX. However, since REINDEX does not support concurrent builds, this option is unlikely to seem attractive.)

How can I figure out why Postgres marked the index as INVALID?


texts: Lock All Column Changes, Except One Column On Trigger

texts:
I want to ask how to lock all updates except one column with Trigger, is it possible to achieve this?
For example my table structure is:
--------------------------
id | name | age | point
--------------------------
1  | Test | 10  | 10
2  | Test | 12  | 10

So what I want is to lock all changes update columns, except the point column.
Thank you in advance, sorry for my bad english


texts: SQL Server process suspended while executing simple SQL (one row deletes and selects)

texts:
We have a problem on MS SQL Server. Processes get suspended. Looks like it is caused by simple SQL queries like Delete from x where id = @x, or select * from table where id = @x. Several similar parallel queries are being executed like delete one row. Process stays suspended. Any suggestions how to solve this problem?

In server reports no Locks or Blocks are reported. Process is suspending itself. There are no concurrent users or processes (it looks to me)


texts: Sum of all Memory Clerks and Total Server Memory (KB)

texts:
I am trying to understand the memory management of SQL Server.
I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB.
By using the following query I found the list of all memory clerks:
SELECT  [type] AS [ClerkType], SUM(pages_kb) / 1024 AS [SizeMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC

Total sum of all memory clerks was equal to 23523 MB.
Value for MEMORYCLERK_SQLBUFFERPOOL memory clerk is 18387 MB.

I also checked some performance counters:
Total Server Memory (KB) = 32262 MB.
Database Cache Memory (KB) = 18387 MB.

Please correct me if any of the below statements are wrong,
Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.

Total Server Memory (KB) - The committed memory from the Buffer Pool.

Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size.

Above query shows all memory clerks for both Buffer Pool Memory and Non-Buffer Pool Memory.

My question related to this subject:
If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory?
EDIT: Result of select * from sys.dm_os_process_memory


texts: HQL pagination returns same result on different pages - with SQL

texts:
We have the following query in the server (using sql):

Query query = this.entityManager.createQuery(
           "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC");

The type field is not unique.

In order to implement pagination, we use the following code:

query.setFirstResult(currentPage * pageSize).setMaxResults(pageSize);

Meaning that if we want a page size of 10, and we have 15 results overall, the client sends currentPage = 0, pageSize = 10 to display the first page, and currentPage = 1, pageSize = 10 to display the second page.

It seems that in cases where the total results > page size we get the same results on different pages, and some result lines are not displayed at all (on any page).
Our guess is that this issue has to do with the fact that the sort field is not unique, because if we use the following query, it does not occur:

"SELECT type, id, date, amount FROM Transaction ORDER BY type ASC, id ASC"

Does anyone know why this happens? And how to solve it? (Other that just adding the id as a sort field as well).


texts: How to confirm that object owners can grant and revoke privileges on objects he owns?

texts:
I'm trying to bring my systems in compliance with a certain set of standards. One of them in confirming that the Oracle default behavior of allowing users to grant and revoke privileges to objects they own is still active. I have no idea how this would even be disabled in the first place, but I'm trying to figure out what evidence I can provide that it is still active other than "this is default behavior of Oracle." Is there a way to confirm this in views like dba_tab_privs or something?

Any assistance would be appreciated. Let me know if you need me to clarify.


texts: MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db

texts:
I have a standard simple master slave replication that usually runs fine.
But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table.

I have explicitly only specified one database in replicate_do_db and in binlog_do_db. So it should only log and process that database. Why am I getting errors on another table?

This is the output of master status and slave status:

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.001473
        Position: 84364707
    Binlog_Do_DB: my-database
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: my-master-host
                  Master_User: pm-rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001473
          Read_Master_Log_Pos: 82124707
               Relay_Log_File: mysqld-relay-bin.000486
                Relay_Log_Pos: 38975372
        Relay_Master_Log_File: mysql-bin.001473
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: my-database
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 38975226
              Relay_Log_Space: 82125053
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5309510
1 row in set (0.00 sec)


texts: Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL

texts:
I have a table that contains 30 000+ records named as ProductCodes that contains a column 'Value'.

I have made a query that'll return the records matching and containing a passed String using CONTAINS.

Select TOP 5 * from ProductCodes
WHERE CONTAINS(Value,'"pepper*"')

It does return the records contains 'Pepper' e.g. Pepper and Bayleaf, Peppermint, Dr. Pepper etc.
I also have an exact record that only contains the exact string, 'Pepper' which is displayed in the mid of the results. I only need to select the TOP 5 of the records and also the exacting match of passed string.

I also tried to use the CONTAINSTABLE

SELECT Value, RANK FROM
ProductCodes PC
INNER JOIN
CONTAINSTABLE(ProductCodes,Value,N'pepper') AS KEY_TBL
ON PC.Value = KEY_TBL.[KEY]
ORDER BY RANK DESC

I tried using a WHERE rank = 1000 which I read somewhere that's the rank of the closest match but as I figured out the rank of the record 'Pepper' is 128 and phrases that have more than 1 word match have a higher rank.

I am fairly new to using FREETEXT and Semantics so I am still learning on it. How can I take the exact match and 'partial' match in my TOP 5? TIA

edit:

I have added a UNION ALL query where it first selects the equal record and combining it with my CONTAINS query.

SELECT TOP 5 * FROM (
 SELECT TOP 5 * From ProductCodes
 WHERE Value = 'pepper'
 UNION ALL
 Select TOP 5 * from ProductCodes
 WHERE CONTAINS(Value,'"pepper*"')
) as Value

Now, I can get the exacting match and partial match but wonder how it'll make my query slower.


texts: I have 100+ pdf files that I would like to load into an access database. When the pdf is uploaded, I would like for the data retrieved to update in an access form. How to do that?


texts: How to Sync and Consolidate Data from Multiple MYSQL local database to MYSQL live server database in PHP?

texts:
What I want to accomplish is that the web app will still work without internet connections, which means that the branch will still operate without internet connections. If there is an internet connection the local servers will automatically upload the data to the live server.
The local server will send the data to the live server considering no data duplicates from the other branches' local server.  The job of the Live server is to consolidate all the data coming from those local servers.
Additional info:
Each office branch has its database and only the Central Database has a copy of all the branches' data. The main function of the Branch Office is to upload new data they have if the internet connection is okay.
Central Server and the Client-Server have the same database schema.  The problem is the loss of data or accuracy of data when they have the same primary key especially when the branches are all uploading data at the same time.
I am using Mysql Version 8.


texts:
I have a SQL Server 2014 instance job that runs nightly that moves backups from the local drive to a network drive. I wrote it using PowerShell.

$mypath = $('F:\Backup\CRMDB01$CRMDB012014\Prod\Full\')
$mydestination = '\\stagingshare\fromprod\'
$myfile = gci $mypath | sort LastWriteTime | select -last 1
copy-item -path ($mypath + $myfile) -destination $mydestination

The job is failing with "reason: line(1): Syntax Error". I have it set to "Run as:" proxy_1.

proxy_1 has a credentialed name next to it that has access to the share. I don't have any principals selected.

What am I missing?


texts: SSAS Calculated Member Conditional Count

texts:
I have a table representing Accidents and a column with Accident Severity ranging from 'Slight' to 'Fatal'. I want to create a measure counting Fatal Accidents depending on other columns on the Accidents table and others (Road Type, Weather Conditions etc.)

What would the calculated member for this measure be and would adding it to a Fact Table consisting of Foreign Keys from all those tables be enough to accomplish what I want?


texts:
I am trying to restore master database, when I set the server to single mode user I am unable to connect I get the error below.

  Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time.


texts: sqlsrv_connect: use Windows authentication?

texts:
I have a PHP server running under IIS on serverIIS1, and a MS SQL database on serverDB4. I created some PHP code that runs on the serverIIS1 server:

$serverName = "serverDB4\serverDB4";
$connectionInfo = array( "Database"=>"DATABASE_NAME", "UID"=>"DOMAIN.ORG\\databaseadm", "PWD"=>"passwordishere");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.
";
}else{
     echo "Connection could not be established.
";
     die( print_r( sqlsrv_errors(), true));
}

I verified all this info is correct (the login is a Windows account with proper access, so it's via Windows authentication), but I'm getting this error:

  Login failed for user 'DOMAIN.ORG\databaseadm'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN.ORG\databaseadm'. )

Why is the login failing? I know the info is right, as I can connect manually remotely via SQL Server Management Studio with those details.

Is it because I'm using Windows authentication? If that does not allow plaintext auth, how can I sqlsrv_connect with a Windows password?


texts: We are processing a partition on a cube in latest version of SQL Server 2017 (CU 13, also tried in CU 12 and CU 4) and sometimes it totally freezes. Task manager shows no movement at all - not memory, cpu, nor IO. The only way out is to kill the SSAS process; can't even stop the SSAS service; that is frozen too. We're importing data using M script. There are about 12 tables in the cube, SQL profiler shows 2 of them completed and the rest have starts but no completions. Something is hanging and can't figure out how to debug. Any suggestions?


texts: Accessing Data after importing from file

texts:
I'm going to preface this with saying I'm a complete newbie at this database stuff before the rolling of the eyes happens, so please be patient with me.

I have an sql dump that I am trying to import and access (running Linux mysql). I created the database...

mysql> CREATE DATABASE db_name

And then tried to import the sql file into the database...

mysql -uroot -p db_name < db_import.sql

At this point, I don't see any errors and everything seems fine, so I assume the import worked. However, I have no idea how to access the imported data because SELECT * FROM db_name; returns ERROR 1146 (42S02): Table 'db_name.db_name' doesn't exist. Similarly for FROM db_import;.

I understand that it's looking for a table db_name within db_name, but where would the imported data go? How can I access it? Did I do the import wrong?


texts:
i have a notifications table like this:

i want to select all user 1 rows, but take only one row from rows with same parent id, and also have the count of those rows.

so i can show to user: 'jack and 3 more users have liked your post'.

many thanks


texts: How to model Parent -> Child -> Grandchild if the Child can be removed

texts:
I'm dealing with a set of relationships where the child can be removed, but I obviously don't want to lose the connection between the grandchild and the parent. I did think about marking the child as "deceased" (to use relevant terminology for this post), but then I would be stuck with a bunch of deceased children in my DB eventually and who wants that (just for the purpose of preserving the relationship)?

If a parent is deleted, all its descendants are deleted. Furthermore, it works like a "normal" relationship, where the grandchild and child always have the same top level parent. The hierarchy is fixed in 3 levels (as shown above). Finally, the Parent, Child, and Grandchild are all of different types (e.g. we're not talking about 3 "humans", they don't have the same base).

However, it feels a bit odd to have the grandchild track the parent since that relationship can usually be derived from the parent-child relationship. Though, I cannot think of another way of doing it.

Is this model valid? Or is there a different way of doing it?


texts:
When you store a .jpg file in a varbinary(max) column and then view the data in SQL Server Management Studio, it appears like "0xFFD8FFE000..."
Is this Hex Encoded or something?  What is SQL Server doing to generate this string?  Can it be converted to a valid JPG file?


texts: Multi-Column index not used for index-only scan, but partial index is

texts:
My question is why a multi-column index is not used for a index only scan, when a partial index with equivalent information (i think) is.

The table:

CREATE TABLE test
(
  id      INT,
  descr   TEXT,
  flag    BOOLEAN
);

INSERT INTO test
SELECT GENERATE_SERIES(1,100000) AS id,
       MD5(RANDOM()::TEXT) AS descr,
       (RANDOM() < 0.1) AS flag;

SELECT *
FROM test LIMIT 10;

A content sample:

id  descr   flag
1   81978ceb5514461fbad9af1152ad78f6    true
2   cc0aee68ba3e0095cc74d53e8da55fef    false
3   689a76e5897d565638f8ddd2d2019b7a    true
4   9df03bc2969a6af88cd1d6e0423d0f4c    true
5   318983766d11f831e9f0df34606dc908    false
6   198102bb71640a16f28263b7fb56ba2e    false
7   9bef7320389db46a8ad88ffa611e81b5    false
8   c1f0d637ee0a985aa7d768a78d2d97b1    false
9   781b4064f721ae3879d95579264b0aba    false
10  c4582890bb1e9af430e0f36b50f5e88c    false

The query I need to run is:

SELECT id
FROM test
WHERE flag;

Now if I use a partial index. The query (eventually) gets executed as a index only scan:

CREATE INDEX i1
  ON test (id) WHERE flag;

QUERY PLAN
Index Only Scan using i1 on test  (cost=0.29..354.95 rows=9911 width=4) (actual time=0.120..6.268 rows=9911 loops=1)
  Heap Fetches: 9911
  Buffers: shared hit=834 read=29
Planning time: 0.806 ms
Execution time: 6.922 ms

What I do not understand is, why a multi column index of the following form is never used for a index-only scan?

CREATE INDEX i2
  ON test (flag, id);

QUERY PLAN
Bitmap Heap Scan on test  (cost=189.10..1122.21 rows=9911 width=4) (actual time=0.767..5.986 rows=9911 loops=1)
  Filter: flag
  Heap Blocks: exact=834
  Buffers: shared hit=863
  ->  Bitmap Index Scan on i2  (cost=0.00..186.62 rows=9911 width=0) (actual time=0.669..0.669 rows=9911 loops=1)
        Index Cond: (flag = true)
        Buffers: shared hit=29
Planning time: 0.090 ms
Execution time: 6.677 ms

Can't the query visit all consecutive leaves of the btree where flag is True to determine all ids?
(Note that tuple visibility probably is not the problem since the index-only scan is used with the partial index i1.)

My Postgres version is: PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit


texts: Can I downgrade SSIS packages to run on older version of SQL

texts:
We have some new SQL 2008 SSIS jobs that were recently developed, mostly to do some simple flat-file loading and targeting a set of tables on a 2008 server.

As it turns out, they may have to run on a SQL 2005 server for both the SSIS and target tables.

Is it possible to downgrade the packages somehow, instead of redeveloping them from scratch?

Obviously any 2008 specific SSIS features would disappear; but it would be nice to not start with a blank designer screen.


texts: Is it possible to shrink mdf file in replication server

texts:
Is it possible to shrink the data file of a SQL Server replication target server and continue the replication operation?
Ideally I'm trying to avoid shrinking the data file to reduce down time, then swap the roles if shrinking is possible.
There is 60% free space in the database after a bulk clean up, so shrinking is unavoidable.


texts: How do I extract row/column elements from multiple tables with same schema in single query


texts: Shared memory error while connecting to Oracle

texts:
I get this error message each time I try to connect to my Oracle XE local database under Ubuntu 15.10 :

user@user-X220:/u01/app/oracle/product/11.2.0/xe/network/admin$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 5 20:58:13 2016

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

Enter user-name: SYS
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

I have read on some forums that i have to delete my /dev/shm repository :

 sudo rm -rf /dev/shm
 sudo mkdir /dev/shm
 sudo mount -t tmpfs shmfs -o size=2048m /dev/shm

I tried actually to do that but it seems that /dev/shm is used by several processors including my firefox navigator.

Any help will more than welcome.


texts:
I am trying to write a report where I grab data showing all server collections with maintenance windows. How I want to do this is to show only those server collections where those computers are also members of my "All Server" collection as well. So as to identify them as servers. I cannot use the OS caption like '%Server%' because some of these computers are also Windows 7, but are being used as a server role.

Here is the code I came up with, but the output is blank. Any help would be very much appreiated. Thank you very much

Select Distinct
    v_Collection.CollectionID as [Collection ID],
    v_Collection.Name as [Collection Name],
    v_ServiceWindow.Description as [Maintenance Window Description],
    v_ServiceWindow.StartTime as [Maintenance Window Start Time],
    v_ServiceWindow.Duration as [Maintenance Window Duration]
From v_Collection Join v_ServiceWindow on
        v_ServiceWindow.CollectionID = v_Collection.CollectionID
Join v_FullCollectionMembership on
        v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID
Where v_FullCollectionMembership.Name In
        (
         Select Distinct v_Collection.CollectionID
         From v_Collection
         Where v_Collection.CollectionID = 'XYZ'
        )
ORDER BY [Collection ID];


texts: Sync User Passwords Across SQL Server AlwaysOn Availability Group

texts:
I'm looking for a method to sync users across an AG when they initiate a password change on their own.

Historically (before setting up the AG), they could use the command line or, for app users, they had access to a web front-end that issued sp_password on their behalf; this was elegant because it was connecting as the user and the web server side didn't need elevated permissions set up on it.  With a user's default database now in an AG, the password change succeeds on the primary but fails on the secondary because the database is inaccessible.

I've tried changing the user's default database to master temporarily (fails to add it back while in an AG), permanently (some apps expect the default to be their own database), and modifying the CGI to use master as the database when connecting (works on primary, fails on secondary).

I can find docs on synchronizing users the first time (had no trouble with this) but having a hard time finding anything on how a user might change their own password in an AG when they are only allowed to connect to the primary.

Any ideas?  I'd still be grateful if it were glaringly obvious and embarrassing.

Edit: I meant to add: I could create a user with elevated permissions (probably 'alter any user') to change the user's password or to copy the password_hash across but I've been asked to try to limit that exposure of credentials on the web server side, if possible.

Thanks.


texts:
I am trying to execute bulk insert statement using sp_executesql.
 I received syntax error:

  Msg 102, level 15, state 1Incorrect syntax near '\t'.

my code block are based on this link

Please review my code and advise what went wrong and how can I correct it.

declare @filepath nvarchar(500);
set @filepath = N'c:\bcp\textfiles\product.txt';

declare @bulkinsert nvarchar(2000);
set @bulkinsert =
N'bulk insert product from ''' +
@filepath +
N''' with (FIRSTROW = 2, FIELDTERMINATOR = "\t", rowterminator = "\n")';

exec sp_executesql @bulkinsert;

in product.txt file, the tab key is used as field delimiter
    below a sample from the text file
    Note: first row is 6 columns (productname column has no value)

  PRODUCTID PRODUCTNAME PRODUCTGROUPCODE    PRODUCTGROUPNAME    INSTOCKFLAG FULLPRICE
  10001     CA  CALENDAR    N   15


texts: Find and print the total number of menu items and the average price of the menu items for each restaurant

texts:
For this, I have the query:

SELECT r.restaurantName, COUNT(m.itemNo), AVG(m.price)
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN Dish d
ON d.dishNo = m.dishNo;

It generates this result:

+----------------+-----------------+--------------+
| restaurantName | COUNT(m.itemNo) | AVG(m.price) |
+----------------+-----------------+--------------+
| Tasty Thai     |              12 |    11.479167 |
+----------------+-----------------+--------------+

So, it is displaying the correct average, however, it has picked ALL menu items I added to the table instead of just for each individual restaurant, as well as counting all of the items. It also is only displaying one of the three restaurants I inserted.

How do I fix this?

Database code:

CREATE TABLE Restaurant(restaurantID Integer Primary Key, restaurantName Char(15) NOT NULL, type Char(15) NOT NULL, city char(15) NOT NULL);

CREATE TABLE Dish(dishNo Integer Primary Key, dishName Char(15) NOT NULL, type ENUM ('ap', 'en', 'ds'));

CREATE TABLE MenuItem(itemNo Integer Primary Key, restaurantNo int, dishNo int, price DECIMAL(4,2) CHECK(price IN(price>=5 and price<=50)), FOREIGN KEY(restaurantNo) REFERENCES Restaurant(restaurantID) ON DELETE NO ACTION, FOREIGN KEY(dishNo) REFERENCES Dish(dishNo) ON DELETE SET NULL);

CREATE TABLE FoodOrder(orderNo Integer Primary Key, itemNo int, dateof date CHECK(dateof IN(dateof>='2017-01-01')), timeof time, FOREIGN KEY(itemNo) REFERENCES MenuItem(itemNo) ON DELETE CASCADE);


texts: Installing Netezza 6.0.8 ODBC driver on Windows Server 2008

texts:
Below are four Netezza drivers that are available for Netezza 6.0.8.  I installed the second one on Windows Server 2008, and it placed it into the system32 folder (64 bit folder).  However, when I set up the System tab ODBC drivers, and used the same configuration and credentials that I use on Windows XP, the connection test failed.

Do I need to do something special on Windows Server to get the Netezza driver working?  Or perhaps I installed the incorrect driver?  Does anyone know the difference between these?


texts: Mongo Chunk 250000 document limit is creating problem in chunk migration

texts:
We can set chunk size limit for a DB. But chunk has a limit of 250000 documents per chunk. My chunk size is 250 MB which varies the number of document in each collections. So normally some of my collections crosses this limit of maximum documents. Could you please suggest how should i do the calculation in this case.

I see in my primary shard it alsways show different chunk size for me with each collection even i have set it for 250 MB some times it is shown as 125 MB and some times it is shown as 64 MB in one case it was 1 KB only and it caused a real trouble on my system.


texts: ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed

texts: We are cleaning up the last year's transactions from our primary database. We use public database link to delete the records a day by day from another database. Each days records are around 30-40k, but somedays transaction peak to 1 million records. When the delete procedure faces such days, it locks the primary database and causing lock. What would be the ideal solution for clearing the records? We also tried to use alter table truncate partition solution. However, the lock situation is worse in this one. As we clear the database, the new records are being written to the database in real-time. So I cannot restart or increase the distributed_lock_timeout parameter. Any solution is appreciated.


texts:
The best way to reindex 500GB database in SQL Server 2008

Is it by using Maintenance plan?
Would I face performance problems while reindexing?


texts:
on an Oracle database. If I make an:

select id,id_operator,denumire,cod_fiscal,loc_den,str
 from rap where is_punct_lucru=1
 and id_operator in (
    select id_operator from rap
        where is_punct_lucru=1
        group by id_operator,denumire,loc_den,str having count(*) > 1
    )
order by id_operator

I get:

ID      ID_OPERATOR     DENUMIRE            COD_FISCAL      LOC_DEN                 STR
70446   762200          SC AF SRL           xxx-453502      Alba Iulia              SOSEAUA DE CENTURA
70430   762200          SC AF SRL           xxx-453449      Alba Iulia              SOSEAUA DE CENTURA
70445   762200          SC AF SRL - OCNA    xxx-379128      Ocna                    MIHAIL EMINESCU

but if I "shoot" just the

select id_operator,count(*) from rap
        where is_punct_lucru=1
        group by id_operator,denumire,loc_den,str having count(*) > 1

it gives me just 2 rows on 762200 because of the LOC_DEN and STR.
How can I include the ID in the group by without loosing the rows...???


texts: Azure SQL - Server login vs. database login

texts: In my Azure SQL server, I see the SQL login that allows me to connect to the server. However, when I look at the Users defined for my database on that server, I don't see a user account with the same name (which I would expect). How am I able to access my database (which I am able to do), without a database user account?


texts: getting BIGINT UNSIGNED value is out of range for integer columns

texts:
Why am I getting a BIGINT out of range error when summing 2 integer values that should be within range?
SELECT
  *,
  numberSent - cd
FROM
  (SELECT
    e.id,
    e.numberSent,
    COUNT(d.id) cd
  FROM
    emails e
    JOIN deliveries d
      ON e.id = d.emailId
  WHERE e.numberSent > 0
  GROUP BY e.id) a

returns with an error
Error Code: 1690
BIGINT UNSIGNED value is out of range in '(`a`.`numberSent` - `a`.`cd`)'

I'm confused because numberSent is an unsigned integer (not a bigint) and its largest value is 613545. The largest value of the count value cd is 523082 so the sum is well in the range of an integer.
Relevant table structure:
CREATE TABLE `emails` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numberSent` INT(10) UNSIGNED DEFAULT '0',
  PRIMARY KEY (`id`),
) ENGINE=INNODB;

CREATE TABLE `deliveries` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `emailId` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `delivery_emailid_index` (`emailId`),
  KEY `delivery_created_at_index` (`created_at`),
  ) ENGINE=INNODB;


texts:
Previously, I had installed the SSMS v17.9. I uninstalled it and tried to install v18.0 and I have the error 0x80070643. I tried the following:

Installing the latest .Net Framework 4.8
Updating Windows 10 and now I have the update from May 2019 (Windows 10 v1903).
I performed a scan to see if there was a failure in any previous update. Running in cmd (as administrator): sfc /scannow

Have anyone of you had the same problem? What can I do to install SSMS v18?


texts: How can I pivot a table then join multiple tables and change values based on information between tables

texts:
The database I'm using has multiple tables storing different information types...it's very convoluted.

I'm trying to join this information into one table, with all of the details for a single ProductID on a single line. I know I need some combination of a pivot and joins, but I'm struggling with changing the values in the pivoted columns.

My Raw Tables are:

Inventory Table:

ProductID     ProductName
---------     -------------------------
S1810944      VIOLA ENDURIO PURE YELLOW
S1810949      ACHILLEA SUMMER PASTELS
S1841708      SPEARMINT

Property Table:

ID          PropertyName
----------- ------------
592         Category
595         Product Type
598         Sale
600         Group

PropertyChoice Table (each PropertyID from above has multiple choices):

ID          PropertyID  ChoiceName
----------- ----------- ------------
541         595         ORGANIC
544         595         REFINED
545         595         STANDARD
1050        598         Yes
1051        598         No
1509        592         FLOWER
1512        592         HERB
1502        600         ANNUAL
1503        600         PERENNIAL

and finally an InventoryProperty table (associating Inventory items to Property choices for a particular property):

ProductID       PropertyID  PropertyValue
-------------   ----------- -------------
S1810944        592         1509
S1810944        598         1051
S1810944        600         1502
S1810944        595         545
S1810949        592         1509
S1810949        598         1051
S1810949        600         1503
S1810949        595         544
S1841708        592         1512
S1841708        598         1050
S1841708        600         1502
S1841708        595         541

The output I'm going for should look like this:

ProductID     ProductName     Category  ProductType     Sale    Group
---------     ------------    --------- ------------    ------  -----------
S1810944      VIOLA           FLOWER    STANDARD        No      ANNUAL
S1810949      ACHILLEA        FLOWER    REFINED         No      PERENNIAL
S1841708      SPEARMINT       HERB      ORGANIC         Yes     ANNUAL

But the closest I have achieve are the Property Values listed like this:

ID          ProductName Category    ProductType Sale    Group
--------    ----------- --------    ----------- ----    -----
S1810944    VIOLA       1509        545         1051    1502
S1810949    ACHILLEA    1509        544         1051    1503
S1841708    SPEARMINT   1512        541         1050    1502

using this query:

SELECT
    I.ProductID,
    I.ProductName,
    P.[592] as Category,
    P.[595] as ProductType,
    P.[598] as Sale,
    P.[600] as [Group]
FROM InventoryProperty
PIVOT
    (
    MAX(PropertyValue)
    FOR PropertyID IN ([592],[595],[598],[600])
    ) as P
INNER JOIN Inventory as I on P.ProductID = I.ProductID

Obviously, I need to tie in the ChoiceName values on from the PropertyChoice table, but I'm not sure how to go about doing that without using CASE...I really want to avoid that because in my real tables, I have over 450 ChoiceName values. I'd also like to make this a query I can reuse even if I add in some new Choice names in the future.


texts: Return ID from partitioned table in postgres?

texts: When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm in dire need of RETURNING. Is there a way to accomplish this?


texts:
Okay, I'm sure this will turn out to be something dumb, but then I'm a dumb n00b DBA.

I've got a largish table, about 163 million rows and something like 44 GiB.  After some experiments with mysqldump for backups, I attempted to build a tab-separated output using select in batch mode.

I tried

echo 'select * from node;' | mysql -u xx -p -B database > file

and it terminates with the message "Killed".

so I tried logging into mysql in batch more, same query, same result.

I tried logging in in the normal fashion, and doing select * from node;

Still the same result: "Killed".   HOWEVER

create table temp_node (select * from node);

works fine.

So I'm clearly missing something basic, but googling hasn't helped.  Have pity on a n00b and tell me what's happening?

Please note the question isn't about running the command per se, it's that when I run the command, or execute select * from node; from the mysql> prompt, mysql terminates with the message "Killed."

Oh, and how I can export that damned table for loading later?


texts: SQL remote connection with new user fails

texts:
Besides the sa user in the SQL server, I created a new user (called test) which only has read permissions. The user was created via MSSMS, with the following properties:

Sql server authentication
Enforce password policy
Enforce password expiration
Server Roles: public
User Mapping: db_datareader and public for master and test_db databases. test_db is the database that I need to access
Status: Permission to connect to database engine -> Grant. Login -> Enabled. Sql server authentication -> Login is locked out is unchecked and greyed out

The user exists in the Logins list, and also in the syslogins table.
I made an exception for my pc ip in the server firewall.

I am able to connect with sql server using this user in MSSMS. However, if I try to connect remotely to sql server with this user (ex. from my pc, using software such as navicat), I get the following error:

  28000 - [SQL Server] Login failed for user test

Looking at the logs in the event viewer, the actual error is:

  Login failed for user 'test' reason: could not find a login matching
  the name provided.

Also, I am able to connect remotely using software such as navicat with the sa user.

I tried creating another user with the same properties and I get the same error. Also, since this is a live server, restarting it is out of question.


texts: Can the SQL Server 2012 service be running while applying CU 8 to SP 3?

texts: I want to apply CU 8 to SQL Server 2012 sp3 and was hoping to not have to ask users to logoff the system while applying since we have 24 x 7 users on this server. Then I assume if the server does require a reboot it will tell me so or is a reboot something I should do as a best practice?


texts: PostgreSQL dump distinct column without memory consumption

texts:
I'm trying to dump csv of distinct varchar(43) column to file, table has around billion rows, select distinct command either runs out of memory and command is aborted or server just closes all connections. Does PostgreSQL have built in methods for such task?

psql (10.9 (Ubuntu 10.9-0ubuntu0.18.10.1)) 16GB RAM


texts: A function to check if a column allows NULL

texts:
Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise?

I would be something like:

UPDATE mytable
SET field = IF(A_FUNCTION_TO_CHECK_IF_ALLOWS_NULL(), NULL, '');


texts:
I have an externally-produced CSV filled with geographic points and associated data, but I would like to run some functions on the columns before saving them. For example I need to digest longitude/latitude columns into points, run some numerical processing on other columns, and discarding a couple altogether.

What's the best practise here? Options I've considered:

Processing the data with a shell script before importing. Can be invoked directly from the COPY command with COPY FROM PROGRAM, but still leaves the problem of turning the lon/lat into a geographical type.
Having two tables, data and data_import, and updating data using functions on the columns of data_import.
Having a table where the imported data is as it appears in the CSV, working with it through a View that does the necessary processing.


texts: SQL query to check if secondary database is removed from availability group

texts:
I have an AG with one primary replica and one secondary replica.
I have created a script that queries transaction log backups from these database replicas and restores them to a target database.

Recently one user removed one secondary database from the AG and started using it as a standalone database as explained here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/remove-a-secondary-database-from-an-availability-group-sql-server?view=sql-server-2017. The user also started a backup chain of this database.

The script now fails to restore as the recovery fork guid has changed on the secondary database.

Is there a way to detect if a database has left the AG so that in the script while I query the backupsets, I can skip the database if it is no longer part of the AG?

Thanks!


texts:
We use Postgres for analytics (star schema).
Every few seconds we get reports on ~500 metrics types.
The simplest schema would be:

timestamp      metric_type     value
78930890       FOO              80.9
78930890       ZOO              20

Our DBA has came up with a suggestion to flatten all reports of the same 5 seconds to:

timestamp   metric1     metric2     ...  metric500
78930890    90.9        20          ...

Some developers push back on this saying this adds a huge complexity on development (batching data so it is written in one shot) and to maintainability (just looking at the table or adding fields is more complex).

Is the DBA model the standard practice in such systems or only a last resort once the original model is clearly not scalable enough?

EDIT: the end goal is to draw a line chart for the users. So queries will mostly be selecting a few metrics, folding them by hours/minutes, and selecting min/max/avg per hour (or any other time period).

EDIT: The DBA main argument is that reducing the number of rows x500 times will allow more efficient indexes and memory (the table will contain hundreds of millions of rows before this optimization). Then when selecting multiple metrics the suggested schema will allow one pass over the data instead of separate index search for each metric.

EDIT: 500 metrics is an "upper bound" but in practice most of the time only ~40 metrics are reported per 5 seconds (not the same 40 though)


texts: How to recover the suspect mode database in SQL Server 2008?

texts:
To recover the database from suspect mode executes the following query.

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Then getting

  Check terminated. A failure was detected while collecting facts.
  Possibly tempdb out of space or a system table is inconsistent. Check
  previous errors

How to resolve this?


texts:
Trying to get the max date from my table, i can't retrieve the value when the date is null.
Sample :

the following table :

+----+--------+------+------------------------+
| id | parent |    Date                       |
+----+--------+------+------------------------+
|  1 | Alex   |   2018-10-01 00:00:00.0000000 |
|  1 | TIM    |   2012-11-07 00:00:00.0000000 |
|  3 | Cath   |    NULL                       |
+----+--------+------+------------------------+

To get the max date im using the following:

select id, Parent, Date From Table T1
Where Date = (select Max(Date) from Table T2 where T1.id= T2.id )

Wich works fine for the id = 1
But for the id = 3 it's not working

The result is :

1 | Alex   |   2018-10-01 00:00:00.0000000

Any ideas Why?


texts: SSIS Package Error when Scheduled in Job

texts:
I have created a package that brings in data from another database and with the data package populates a table in SQL database. The job runs fine when it is executed. I've imported it into SQL and it still works fine. But when I try to automate it using SQL Job agent, I get the error that Requested registry access is not allowed, and in the log says:

  The application-specific permission settings do not grant Local
  Activation permission for the COM Server application with CLSID
  {485F9D22-B7A7-446B-BBCB-23706B2624E3} and APPID
  {83B33982-693D-4824-B42E-7196AE61BB05}

I'm using SQL management studio 2012.


texts: Load PostgreSQL database from corrupt drive

texts:
I suffered corruption of a hard drive but managed to recover files for a PostgreSQL server. We are running on the assumption that the files themselves are not corrupted (yet to be able to tell). My server was running on Windows 10 Pro.

Is it possible for me to load the database(s) onto another PC running the same server version? I have the entire directory recovered from "Program Files/PostgreSQL/11" onward (so I have all of "data" etc).

If so can someone guide me on how to do this, or direct me to existing guidance? I really need to recover this data and sadly with the above corruption I lost the drive containing my dump files (backups).

Can I simply copy the "data" folder into a new build, or is it a bit more complex than that?


texts:
I have a weird one here. We have set up our production MongoDB which was populated by our test one so we had something to work off of. As soon as we started the service we hit instant 100% CPU load with nothing running, so we removed the server from it's replica set and got it down to 2-3% CPU.

When we had all of our users active we saw it spike back up to 100% and we said we would leave it over night and monitor it for any signs of improvement.

The next morning we came in and the CPU was down to around 40% and it seemed very stable. Now about a week later we have come across the same issue as above except this has been happening for almost 4 days where we are maxed on CPU.

I used the db.currentOP() command and found one from "WT RecordStoreThread: local.oplog.rs" that has been running for 355254 seconds which equated to around 98 hours which matches the CPU being maxed.

The machine has 8 cores @ 2.4GHz and 16GB RAM so I wouldn't think that CPU would be this massive an issue at such an early stage.

Our config file looks like the following with the IP and Port stared out:

systemLog:
   destination: file
   path: E:\data\log\mongod.log
storage:
   dbPath: E:\data\db
net:
   port: xxxxx
   bindIp: xxx.xxx.xxx.xxx
#replication:
#   replSetName: "rs1"
security:
   authorization: enabled
#   keyFile: E:\data\mongodb.key

I have restarted the service today hoping to maybe release some resources but it instantly maxed out again.

I have looked at other issues on SO and none seem to have a real answer to resolve this problem. Thanks for taking the time to read this and if anybody has come across the same issue or might have any idea on how to solve it that would be great to get your feedback!


texts:
I know this is probably something stupid on my part, but please bear with me. I have basically 3 tables (let's simplify things):

users
user_id | account_id | phone | name | ...

accounts
account_id | name | ...

phonedetails
phone | country_code | ...

Let's pretend we have a simple query like this:

SELECT u.*, a.* FROM users u
LEFT JOIN accounts a ON a.account_id = u.account_id
ORDER BY u.user_id DESC
LIMIT 100

This correctly assesses that the query needs to use eq_ref on account_id and properly selects only 100 records and is almost instant. However, when I want to do this (this is a simplified version of what I want to use):

SELECT u.*, p.* FROM users u
LEFT JOIN phonedetails p ON p.phone = u.phone
ORDER BY u.user_id DESC
LIMIT 100

It suddenly tries to go through Full Table Scan (so it's about 300 000 records at the moment) and then does its job. Sure, it works, but it takes almost 4 seconds for whatever reason!

account_id => type int in both tables (users, accounts)
phone => varchar(24) in both tables (users, phonedetails)

It doesn't seem to matter if I have an index on that column or not.
Funny thing is that if I do something like this:

SELECT u.*, (SELECT p.country_code FROM phonedetails p WHERE p.phone = u.phone)
FROM users u
ORDER BY u.user_id DESC
LIMIT 100

It correctly joins the tables using eq_ref and it's fast.

Is there something I can do to improve on the query performance?

Edit with more info:

Interesting (at least for me), is that if I execute this query:

SELECT u.* FROM users u
LEFT JOIN phonedetails p ON p.phone = u.phone
ORDER BY u.user_id DESC
LIMIT 100

Explain shows those tables are joined correctly with eq_ref on the primary key and it's fast again. Of course, it's useless for me as I don't get the columns I need.


texts: How can I have intensive stored procedures run on a different machine to where tables are stored?

texts:
For a home air quality monitoring project I am building, I am collecting sensor readings and storing them on my Synology server, which includes a MariaDB instance.

The Synology is great for managing things like RAID arrays and automated backups, but it doen't have a particularly impressive CPU. I also use it for all sorts of other purposes, so I don't want to burden the CPU too much.

I am also building a web server (actually a Shiny Server) on an Ubuntu machine to serve a web page of some analytics on top of the data. I want part of these analytics to be calculated on demand by a stored proc. However, I want the stored proc to use the CPU resources of the Ubuntu machine. But I want the data to be managed and backup up by the Synology.

So my plan is to have a DB in the Synology for storing the data, and have another DB with federated tables in the Ubuntu. The stored proc runs on the Ubuntu. The stored proc only reads data from the Synology, transforms it, and returns it to the caller. It doesn't write anything back to the Synology.

Does this seems like a good idea? How would you do it?


texts: SQL Server Agent Job - Unable to open Step output file. The step succeeded

texts:
I have seen many posts with "Unable to open Step output file.", but can't find any references where the step succeeded. I have my output file location set to:

$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Many other jobs on the same server are using the exact same output location (verified exact character match). This job is owned and being executed as the same user as the jobs which are successfully creating output files.

The job is performing a database restore of a user database with the job config pointed at the master database. This is the only job pointing at master, but I would be surprised if that made the difference.

Any thoughts?


texts:
Here's a real-world schema:

CREATE TABLE [dbo].[Setting]
  (
     [ID]       [BIGINT] NOT NULL,
     [Module]   [NVARCHAR](400) NOT NULL,
     [Property] [NVARCHAR](400) NOT NULL,
     [Value]    [NVARCHAR](4000) NULL,
     PRIMARY KEY CLUSTERED ( [ID] ASC )
  )

And here's the INSERT statement I'm trying to execute:

INSERT INTO [Setting]
            ([ID],
             [Module],
             [Property],
             [Value])
VALUES      ((SELECT COALESCE(max(ID), 0) + 1
              FROM   [Setting]),
             N'security.catalogs.integrated.integrated',
             N'enable-integrated-authentication',
             N'true')

For the life of me I cannot see anything special here, yet on at least two occasions this has failed on SQL Server 2008 R2 Express with the following error message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

This INSERT statement operates on a completely empty table, no strange options were allegedly configured for SQL Server, no nothing.

How can this be happening? From my understanding of SQL Server, max cannot possibly return more than one row.


texts: Should mongodb be run in a service, or a worker cluster?

texts:
I'm trying to implement an architecture that's similar to the coreos's production architecture (shown below)

Should I run the database as a central service or one or more of the workers?

I figured the database needs some kind of replication, which makes me think that putting it in the worker cluster makes more sense, but I'm just not sure.


texts: Is it possible to encrypt a column and NOT have to change app code?

texts:
Is it possible to encrypt a column (with Symmetric Key and Triple DES encryption) and then set up a login for an application such that the query code in the app does not have to be changed to used the DECRYPTBYKEY function?

Right now the app is essentially using straight ANSI SQL.  Pseudo code would looks like:

String query = "select EncryptedCol from TestTable";

To my knowledge the above has to be changed to:

String query =
"OPEN SYMMETRIC KEY TestTableKey DECRYPTION " +
"BY CERTIFICATE EncryptionTestCert " +
"SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " +
"FROM TestTable ";

SQL Server version is 2012 EE SP3-CU1 and the app is written in Java using jTDS.

Hope this question makes sense and appreciate any feedback.


texts: How can I get partition elimination with a variable?

texts:
I have four identical tables on four servers that I need to query from a fifth.  The fifth server is linked to the other four and has a view created to unify them.

-- Servers 1-4
CREATE TABLE Transactions
(
    TxID int not null primary key,
    Description nvarchar(50) not null
);

-- Server 5
CREATE VIEW vwTransactions
AS
SELECT 1 as ServerID, * FROM Server1.DB.dbo.Transactions
UNION ALL
SELECT 2 as ServerID, * FROM Server2.DB.dbo.Transactions
UNION ALL
SELECT 3 as ServerID, * FROM Server3.DB.dbo.Transactions
UNION ALL
SELECT 4 as ServerID, * FROM Server4.DB.dbo.Transactions;

When I run a query with a constant where clause on the ServerID column, I get a nice happy query plan that eliminates three of the partitions.

SELECT * FROM vwTransactions WHERE ServerID = 1;

But when I use a parameter, or worse – a join, I get a scan of all four tables.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID;

I have also tried using inline table valued functions to no avail.

How do I make a view of these tables which can be queried easily?


texts: PostgreSQL: Remove password requirement for user postgres

texts:
I understand that, upon installation, PostgreSQL has no password for its db root user (postgres):

postgres=# select usename, passwd is null from pg_shadow;
 usename  | ?column?
----------+----------
 postgres | t
(1 row)

... and one is advised to set it with:

alter role postgres password '<<very-secret>>';

(and then update the pg_hba.conf file accordingly)

My question is: what is the SQL to use to revert back to the previous situation when no password was needed for user postgres.

In general, how can I remove the password requirement for any role? I am not asking how to change the password but rather how to remove the password requirement (null passwd column in table pg_shadow).


texts: autovacuum launcher process, but no autovacuum workers

texts:
Debian Squeeze (yeah, I know), postgresql 9.1. Two similar db servers, one master one shard. postgresql.conf identical between the two besides small memory footprint config differences - all vacuum/autovacuum configs identical. On both, I have autovacuum workers set to seven. On the shard, ps shows seven happy little workers. On the master, I only have the launcher process -

postgres@mia-main-primary: ~ $ ps -ecf|grep [a]utovacuum
postgres  7071  7065 TS   19 13:30 ?        00:00:02 postgres: autovacuum launcher process

I've straced the launcher process on both, and there's little difference, except that the shard spends more time in looping timeout cycles, which sort of makes sense to me since it has actual workers to poll. I've restarted postgresql, no change, nor with changing the number of workers, the aggressiveness, anything.

What's even more curious is that the statistics activity for autovacuuming does show tables being autovacuumed - at least the most active tables.

I'm kind of at a loss. Even though I have the evidence that autovacuuming is happening, I feel uneasy not seeing the actual workers there, particularly when there's no material difference between the two servers.

Thoughts?


texts: How can I turn each record on my Access table into 6 records?


texts: SQL Server Setup failure - Cached MSI files are missing

texts:
Trying to apply SQL Server 2008 SP4 to an old instance I now have to look after, but the installer is failing with
TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
The cached MSI file 'C:\Windows\Installer\5dd0fcab.msi' is missing.
Its original file is 'sql_engine_core_inst.msi' and it was installed for product 'Microsoft SQL Server 2008 Database Engine Services' from 'e:\a9aebd1f210f70e7096a\x86\setup\sql_engine_core_inst_msi', version '10.3.5500.0', language 'ENU'.
To resolve this problem, recover the missing file from the installation media and start setup again.
For more information about how to resolve this problem, see 'Steps to restore the missing Windows Installer cache files' (http://go.microsoft.com/fwlink/?LinkId=144387) in the Microsoft Knowledge Base.
Error code 0x84B20002.

BUTTONS:
OK
I have managed to find and fix a lot of missing MSP files, but cannot locate the source media for SQL Server 2008 SP3 (10.3.5500.0). Any ideas if this is available anywhere that I can download?
http://www.microsoft.com/download/en/details.aspx?id=27594#overview - no longer available
(Yes, I know I need to upgrade to a newer version - one step at a time!)


texts: 100 TeraBytes Capacity Database - Resources and Time Estimates

texts:
I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment:

Storage Capacity ~ 100TB
Tables ~ 200, sizes ranging from 1GB to 5TB. mean size could lie between 100GB-200GB
ETL - jobs may require join between tables of 10's of millions of rows, with join keys ranging from 10 bytes to 500 bytes. such joins should finish in under 2-5 minutes
Live Selects - initially, only interested in select speeds. should support 500 selects/second. Updates/second will be relatively much smaller number and can be ignored for this exercise.
need 24x7 availability. 2 independent DB servers should be available to serve select calls (with data replicated).

Questions:

At present, I am looking at Oracle. How has your experience been with other commercial (or) opensource solutions for large databases?
What hardware-OS have you seen to work best? I am planning for Linux on Dell.
Is Network storage, such as NetApp, a must? What issues do you foresee with using commercial off the shelf disks?
Once the hardware and OS are ready, how much time would you set aside to setup, configure DB, storage etc.
What team compositions worked best in the environments you have observed? I mean, the various Admins (OS Admin, Oracle DB Admin?) required to manage and operate such a setup. How many of them might be needed to achieve a 24x7 uptime.
Any approximation/range on DB Licenses, Network Storage costs.

I know I dont have all the environment details. I am not looking for exact details, an approximation is sufficient. Though some of the questions might be best answered by managers, I am interested in Admins perspective. I appreciate your input.


texts:
I have a fact table with 2 dimensions:

cohort_id
date_id

Each record contains the amount of active users from a specific cohort at a particular date - typical structure for the cohort analysis.

Currently, a cohort is defined as a group of users signed up at the same date. I would like to start additionally storing the same data, but with a bit different definition of the cohort - a group of users with the first order at the same date.

I have a conceptual dilemma: is it better to create a new fact table with the same structure or to add a new dimension (cohort_type_id) to the existing fact table?