texts: Postgresql: Max Connections per server / database

texts:
I'm building a new, multitenant application using microservices and Postgres RDS in AWS. Each tenant will have their own database within the RDS instance (additional RDS instances/servers will be added when needed). There will be three microservices that will need access to each tenant database. One microservice will serve UI activity and the other two will handle background transactions, not bound to the UI.

I'm currently using C3p0 for connection pooling for each microservice attached to each tenant database (using a map to manage the tenant connection pools). When a call is made, the tenant id will be passed as part of the payload, determine which connection pool to use then invoke the sql statement. For reference, each tenant will have 20-2000gb of database data.

My question is around postgress max connections. Is the max connections for each database or the database server? I know Postgres can handle thousands of databases but how do you maintain connections to all of these databases?

Suppose I have 100 tenant databases on one database server. Each microservice has a connection pool set to have 5 connections. That would be 15 connections per tenant, or 1500 connections to the database instance/server. Now, I have to have redundancy on the microservices, so I'll have at least 2 of each microservice running, which would double the total database connections to 3000. Add more microservice instances for scalability and my connection count to the database server again increases. To keep it simple for this conversation, I'd scale all the microservices together as a group when there is additional load, so 3 sets of microservices requires 4500 connections and so on.

Will this even work? Am I thinking about connection pooling and Postgres connections correctly? The plan is to use separate databases per tenant for various reasons, unless that is, there is a better way to deploy that makes backup/restores simple, provides data isolation at the SQL level, etc.

I've read through numerous articles with performance issues with one database and a schema per tenant so I think that option is ruled out. The other alternative would be one database with shared tables for all tenants and using the tenant id as part of the primary key, but given the need to keep data isolated, i'm pulling that off the table for consideration.


texts: SQL Performance post virtualization - sp_blitz and latency info attached

texts:
Full disclosure, I'm a VMware admin.   We're trying to virtualize a SQL server that handles pricing information for multiple customers.   Up until now, it has been a physical system with local SSDs.   It's now running as a VM,  Win 2016, 128GB memory, 6 vCPU (2sockets, 3 cores) against an all flash array.    I/O has confirmed that this thing screams, storage admin says we're looking at very low latency, which confirms what I/O meter is seeing.

When it was physical, it performed a nightly process where it dumped table contents to a series of txt files per customer, then zipped those files up.   Repeat again for the next customer.

On the physical system, files that are 250MB (text) would take around 6 seconds.   On the VM they're taking around 60 seconds.    I can tell the storage subsytem is in good shape.  I've increased the queue depth for the vhba, and pvscsi controller as per vmware recommendations and no change.

My DBA's are saying "it worked on the physical server just fine, so it must be your system"  If it is, there's no indication on my end that we're saturating the storage connections or running into resource contentions.

I'm attaching my sp_blitz and the subsequent results of the script that shows the wait for the various DBs... one is much higher than others.


texts: Starting an Oracle database without a spfile & pfile?

texts: How can I start an Oracle database if both the spfile and pfile are not present, and a backup is also not available?


texts: What are the .pgAdmin4.2123502176.addr and .log files?

texts:
I recently installed pgAdmin4 on Win 10 64-bit. At the top of my main User directory it makes two tiny files, .pgAdmin4.2123502176.addr and .pgAdmin4.2123502176.log. (I'm assuming the numbers change over time...)  The .addr file says:

  http://127.0.0.1:1913/?key=4f8ced45-3edf-4583-a39d-3579........

The .log file began today empty, but it does collect errors - a lot of these:

  error: [Errno 10053] An established connection was aborted by the software in your host machine

And some real errors I've seen in the Admin program console.

All I find on the web is that logs are in:

  "C:\Users**\AppData\Roaming\pgAdmin"

There is a pgadmin4.log there, which has errors going back to installation, but not the multiple [Errno 10053] from today. It does have the console errors from today.

So: Why are these files in my main User directory?

Why isn't anyone talking about them?

Can I move them somewhere else?


texts:
Suppose I have a column with colour names (colour). Naturally, it will be a character field.

I would like to sort the table by colour, but not in alphabetical order, but by rainbow or transistor colour code order.

What is the simplest way to sort a column using an arbitrary sort order? Something like this:

id  name        colour
1   this        red
4   that        red
2   stuff       orange
5   nonsense    orange
3   whatever    yellow
6   etc         yellow

where the colours in in rainbow order.

The use of colours is just an example. There are many sets of textual data where the order is not alphabetical.


texts:
I'm still learning a lot about SQL Server and pretty green, but doing the best I can as an accidental DBA.

We have recently purchased a SQL monitoring software package, and after adjusting the threshold on alerting so I'm not bombarded with false positive warnings, I noticed that the pages input per second spikes every day at exactly the same time:  6:20:30 PM.  The only variation in time over the last week was just last night, when it alerted again at 6:19:39 PM.... very very close to the same time!

I have checked the server job schedules and it appears there are no jobs scheduled around that time.  The earliest job we have kicking off is at 9pm daily, so that's not it.

Can anyone tell me what I can look at (or for) that will help me find what is causing this to happen at exactly the same time every day?

Thanks much!


texts:
How can I create a SQL Server equivalent of this query?

SELECT m.ItemId
    ,item.NAME
    ,m.Expiratory
    ,m.t AS TotalNumber
FROM item
    ,(
        SELECT ItemId
            ,Expiratory
            ,sum(TotalNumber) t
        FROM inbox
        GROUP BY Expiratory
            ,ItemId
        ) m
WHERE (
        (
            m.ItemId
            ,m.Expiratory
            ) NOT IN (
            SELECT ItemId
                ,Expiratory
            FROM outbox
            GROUP BY Expiratory
                ,ItemId
            )
        )
    AND (item.ItemID = m.ItemId)


texts:
We have a third party software that generates reports by adding a new "temp" report to the SSRS catalog every time a user prints a report so now the dbo.catalog table is almost 3 GB and has 16K+ rows worth of one time only reports.  Is there a way to safely purge these rows from this table?

To be clear, I am talking about ReportServer not ReportServerTempDB so EXEC FlushReportFromCache @Path and EXEC CleanExpiredCache are of no help.

From what I am reading online Microsoft does not want you messing with this table directly so there is little documentation.  I can't find any sort of official maintenance function that works on dbo.catalog.  I don't expect many people want to mass delete their reports so this does not surprise me.


texts:
I have a table structured as so (Simplified)

Name, EMail, LastLoggedInAt

I have a user in SQL Server (RemoteUser) that should only be able to see data (Via a select query) where the LastLoggdInAt field is not null.

It looks like I can do this? Is it possible?


texts:
One of member of my MongoDB replica set decided it would not restart, with the following error (reformatted for readability):

Starting rollback due to OplogStartMissing:
our last op time fetched: (term: 30, timestamp: Jul 28 07:45:11:6)
source's GTE:             (term: 31, timestamp: Jul 28 07:45:11:7)

Fatal assertion 18750 UnrecoverableRollbackError
                          (term: 31, timestamp: Jul 28 07:45:12:2) > our last optime:
                          (term: 30, timestamp: Jul 28 07:45:11:6)

Let's call the instance where this happens M1, and the source its trying to sync M2. M1 used to be primary, then the primary switched to M2, and M1 restarted.

The naive interpretation of these log messages is that the first operation from M2's oplog is exactly the next operation after what we have applied in M1. So, we should just happily apply operations from M2, but MongoDB tries to rollback some operations, finds an operation in future relative to both what we've applied and what's next on M2, and dies.

I have two questions: first, why is MongoDB trying rollback in the first place, and second, where is operation with timestamp of Jul 28 07:45:12:2 is coming from?


texts: Limit number of user logins in SQL Server

texts:
I have a user login named 'manager' currently logged in PC1, I want to prevent this user from login in PC2. So I created a trigger, and I got an error

  Msg 4621, Level 16, State 10, Line 2
  Permissions at the server scope can only be granted when the current database is master

It works well if I use database master, but I want to use database Employee. How can I fix it?

USE Employee;
GO

GRANT VIEW SERVER STATE TO manager;
GO

CREATE TRIGGER connection_limit_trigger2
ON ALL SERVER WITH EXECUTE AS 'manager'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'manager' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'manager') > 2
    ROLLBACK;
END;


texts: I want Powershell's invoke-sqlcmd to ignore SQL execution errors

texts:
I've written a Powershell script that scripts out the appropriate SQL scripts for all databases on a server as well as creating BCP files for the contents of each. This is to allow the restoration of a database from a higher level of SQL to a lower level where backup and restore will be impossible (but there are no features or functions used that are incompatible otherwise). This is great, to automatically run them against the destination server is problematic.

Each script created contains small problems. For example the table scripts all start with dropping the table by name if it already exists. This is fine in SSMS because you can just remove that line if the table doesn't already exist to avoid the "doesn't exist or you don't have permission" errors. There's other things you'd remove too like creating indexes that already exist from having run other scripts that make other tables from the same database.

Invoke-sqlcmd -inputfile dbo.SomeTable.sql stops and fails on these errors. How can I make either invoke-sqlcmd ignore them and just do what it can (in the knowledge that's fine), or temporarily make SQL tolerant of those and not raise stopping errors, or what else can I use to programatically apply the scripts SQL server generates to an SQL server without manual intervention?


texts: Best practice to recover an CRUD statement to a linked server if connection is lost

texts:
I am looking for the best practice for the following scenario.

We have a CRM in our company. When an employee updates the record of a company, there is trigger that fires a stored procedure which has a CRUD statement to the linked server hosting the SQL DB of our website.

Question:

What happens when the connection is lost in the middle of the CRUD and the SQL DB of the website did not get updated?
What would be the best way to have the SQL statement processed again when the connection is back?

I read about Service Broker or Transactional Replication. Is one of these more appropriate for that situation?

The configuration:

Local: SQL server 2008 R2

Website: SQL server 2008


texts: How can I automate the process of checking of column and executing SQL statement?

texts:
I am experiencing quite difficult problem and hope for your feedback. In my table I have a "Services"column in "MyData" table which contains names of my services. I have created SQL statement which should be executed via maintenance plan and get the minimum id value from one table and update "Value" column in "MyData" table:

declare @id int
select
    @id = min(id)
from
    MyTable
where
    MyTime >= dateadd(MM, -12, DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1))

update
    MyData
set
    Value = @id
where
    ColumnName= 'MyServiceName'

The statement works correctly, but I need to automate the things. Problem is, I have several Services and it will be inefficient to manually change "MyServicename" column in where clause each time it is needed to be updated. I ask for your advice, how can I automate the process of checking the "MyServiceName" in order to execute the statement for appropriate service?
Sorry if it is written in too complicated way, but I hope could express the trouble I am expressing.


texts:
I'm trying to find a way to include all months in a result set
my query looks like this:
select to_char(changedate, 'YY-Month') Mon,
  sum(downtime) dt
from (select * from assetstatus
where assetnum in ('x','y','z')
  and siteid = 'xyz'
  and changedate between to_date('10/1/2019','mm/dd/yy')
                            and to_date('9/30/2020','mm/dd/yy')
  and exists (select 1 from workorder where worktype = 'RD'
                  and workorder.wonum = assetstatus.wonum
                  and workorder.siteid = assetstatus.siteid))
group by to_char(changedate, 'YY-Month'), to_char(changedate, 'YY-Mm')
order by to_char(changedate, 'YY-Mm');

but if i'm missing a month of data say December 2019 where no downtime was reported then it will return
19-October      91.85
19-November     26.55
20-January      0
20-February     29.1999999999999987
20-March        32.133333333333333666
20-April        1664.4333333333333
20-May          246.95
20-June         12.25
20-July         2.1
20-August       0
20-September    3

So I'm looking to have it include 19-December with 0 hours if nothing is present.
The dates are chosen dynamically based on user input one being a year prior to the later date.
using 12c drivers  if anyone has any thoughts I'd greatly appreciate it!


texts: Is there a way to automate checking stored procedures, functions, etc. written for sql server 2008 will work in 2016?

texts: The shop I work in has thousands of stored procedures which support applications on MS SQL Server 2008 R2 instances. We need to upgrade these to MS SQL 2016 over the course of the next year. Is there a tool that exists which will cycle through stored procs, functions, etc. which will identify anything that may have been deprecated prior to running an actual update?


texts: How can I restore ##MS_SSISServerCleanupJobLogin##?

texts:
At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to this user being missing from the server.

I still have the appropriate User in the SSIS database, but I need to re-create the login. However, I can't find what settings (passwords etc) to use.

The only thing I could find about restoring this user was here, but it doesn't give enough detail.

  6.1. Create login [##MS_SSISServerCleanupJobLogin##] using CREATE LOGIN TSQL statement. This login is for internal use in SSISDB maintenance.

  6.2. Map SSISDB user ##MS_SSISServerCleanupJobUser## to server login ##MS_SSISServerCleanupJobLogin##

 USE SSISDB
 ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

How can I recreate this login properly?


texts:
I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that schema.

My questions are:

is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?
is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?
if both are possible than which one should be used and why ?

Thank you in advance.

update:

I created 3 schemas:

A is the one which has tables FOO

 grant select on FOO to B

B is to create the view

create or replace view BAR as
select * from A.FOO

C is suppose to be able to select * from B.BAR but if try to (as B):

grant select on BAR to C

I'm getting an error:

Error: ORA-01720: grant option does not exist for 'A.FOO'

Basicaly I wish to achieve 3 levels:

A (original table) < B (view definition) < C (select only from the view)

But it seems that it requires me to grant C direct access to read the original table which fails all the idea.


texts:
I have a dataset similar to the one below in Access:

I am wanting to create a query which generates a table summarising how many sites each company is at. I.e. it will return something like this:

Alpha - 2

Bravo - 2

Charlie - 2

Delta - 2

I have tried creating a query which simply counts the Site field but this counts the same site multiple times if it is repeated. My aim is to count each site only once.

Does anyone have a simple way to achieve this in Access' Query Design?


texts: RDS MYSQL instance rebooted and won't load INNODB table

texts:
So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error in the logs.

140324 16:22:23 [ERROR] Cannot find or open table dbname/table_name from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

The problem is that no one noticed for three days that the table was missing and therefore my automated snapshots are useless (I only retain them that long) as all the snapshots have the same problem.

During my attempts to fix things I have had indications from the db that indicate that the .frm and .ibd files are all inplace. The show tables command lists the table (so frm is there), and I get the error below if I (on a copy) drop the table and try and recreate it.

140324 16:46:05 InnoDB: Error creating file './dbname/table_name.ibd'.
140324 16:46:05 InnoDB: Operating system error number 17 in a file operation.
InnoDB: Error number 17 means 'File exists'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: The file already exists though the corresponding table did not
InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
InnoDB: .ibd files around without using the SQL commands
InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
InnoDB: mysqld crash in the middle of CREATE TABLE? You can
InnoDB: resolve the problem by removing the file './dbname/table_name.ibd'
InnoDB: under the 'datadir' of MySQL.

I suspect there was some issue with an index before the instance was restarted, or if it indeed crashed then perhaps that caused a corruption.

I managed to recreate the data (probably) from logs though it's only a guess, not a sure thing.

So I'm asking what I should do at this point. I'd love to find a solution that allows me to get that table back with the data it had, but if I have to delete it and recreate it that's also ok. Obviously I can't touch any mysql data files as it's an rds instance. Mysql version is 5.5.27.


texts:
What is the equivalent query for

SELECT *,SUM(total_request_count) AS total_calls  FROM API_REQUEST_SUMMARY

in DB2. It says * is invalid in db2 data explorer. After running this I got below error

An unexpected token "*" was found following "SELECT ". Expected tokens may include: "NULL".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

Any Idea to resolve this is much appreciated.


texts: Can I find errors without using testing data for all the branches of each function?

texts:
In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This is a big pain while debugging functions with many conditions in them. The only way I know now is to make testing data for every branch in each function and run the function over them. This should be fine, but just making the data for all the conditions in all the ordinary and trigger functions would consume hours, so I wonder if there is some better solution (free and open source, if possible).

So how to avoid making so much testing data while debugging my PL/pgSQL functions?


texts: Allocation strategy where tables regularly grow and shrink by large amounts

texts:
I have a SQL Server 2014 database with 100+ tables that have a 5% growth rate over the course of a year. Lots of reading, very little writing.

This database is going to expand by another set of 20+ tables that regularly (quarterly) grow overnight by almost as much as the entire rest of the database combined.

Those tables are completely emptied at the end of processing in a few hours.  The data in those tables is fairly expendable, but need to be in the same database as the rest for referential integrity.

For some sample numbers, let's say the first DB is 20GB and the subset of tables grow to 15GB and then are emptied.  So the actual size of the data fluctuates between 20 and 35 GB throughout the course of a few days.

What can I do in planning this system to help account for this fluctuation?
Is this where filegroups would come into play?
What advantages / problems would I get moving this set of tables to their own filegroup?


texts: Is processing required dimensions enough or to process the entire cube?

texts:
I have four large dimensions and one of the dimension is not going to be changed, also the particular dimension didn't need to be processed.

So, processing the three dimensions individually updates the cube or do I need to process the entire cube every time (which is processing all the dimensions) ?


texts: Should I deploy Mongodb sharding for 50 collections?

texts:
I have a 10 node clusters that runs about 50 concurrent jobs. Each job needs to read/write a separate collection. So I have about 50 collections roughly. Each collection has about 20 M records. Most of the time, jobs only need to do sequential read/write.

For simplicity, I could deploy a single instance of mongodb that has no replication, no sharding. And have 50 separate collections. But the single node where mogodb is running becomes a hotspot and the rest 9 nodes can't share the read/write load.

So I would like to leverage the resource and balance the load. But I guess a 20M record collection is not worth sharding? Especially I only need to do sequential read/write. I thought about merging 50 collections into one big collection. But I am stumbled on the document size limitation which is 16 MB.

Any suggestions? Thanks,


texts: Is wrapping only possible for package bodies?

texts:
I am using oracle 11gR2 database in which I was trying to wrap my scripts. I read the documents and tried to wrap a sql script which contains a create table statement. I used below commands:

wrap iname=ORDER.sql oname=ORDER_TEST.sql
wrap iname=ORDER.sql oname=ORDER_TEST.wrp
wrap iname=ORDER.sql oname=ORDER_TEST.plb

But in all 3 format the files created have same codes visible, no encryption is happening. Also, I have tried same on a package body which get encrypted properly.
Now, I need to know does only package bodies has this facility of wrapping. Can I wrap other scripts like create table, trigger, view etc..?


texts: Group by with showing full rows in Postgresql

texts:
I have a table like below with two columns A, B:

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,1),
  (1,1),
  (1,2),
  (1,2)
) AS t(a,b);

I want the result like below:

A B
1 1
1 1
sum B = 2

1 2
1 2
sum B = 4

How I can do it in PostgresSQL. Thanks everyone a lot.


texts: Upgrading to new cPanel Server with mySQL on SSD -- What should I change in my.cnf?

texts:
I'm upgrading to a faster server with whm/cpanel and the mysql databases will be on a SSD. I've read a bit about not wanting any query cache and stuff like that, but as a novice, I've learned that tinkering with my.cnf can be disastrous.

Here's the current my.cnf for my "hard drive" based webserver.

[mysqld]
query_cache_limit=32M
query_cache_size=128M
query_cache_type=1
query_cache_min_res_unit=4096
open_files_limit=16000

table_open_cache=25000
table_definition_cache=2000
key_buffer_size=128M
innodb_buffer_pool_size=2000M
innodb_buffer_pool_instances=2
join_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=4M
tmp_table_size=3048M
max_heap_table_size=3048M
thread_cache_size=256

max_connections=250
table_cache=60000

What would you change in it on if mysql is on a SSD?

Here's a bit of info from tuner.sh all:

Avg. qps = 447

WORKER THREADS
Current thread_cache_size = 256
Current threads_cached = 96
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 250
Current threads_connected = 20
Historic max_used_connections = 116
The number of used connections is 46% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 794 M
Current InnoDB data space = 1.10 G
Current InnoDB buffer pool free = 14 %
Current innodb_buffer_pool_size = 1.95 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 3.62 G
Configured Max Per-thread Buffers : 3.02 G
Configured Max Global Buffers : 2.21 G
Configured Max Memory Limit : 5.23 G
Physical Memory : 15.56 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 222 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 1115
Key buffer free ratio = 72 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 28 M
Current query_cache_limit = 32 M
Current Query cache Memory fill ratio = 22.42 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 166 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 120260 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 60000 tables
Current table_definition_cache = 2000 tables
You have a total of 733 tables
You have 851 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 2.97 G
Current tmp_table_size = 2.97 G
Of 262494 temp tables, 33% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 1005 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 101097
Your table locking seems to be fine

The OLD web server is an 8 core single Xeon @ 16GB ram.  The NEW web server is a 24 core dual Xeon @ 64GB ram.  The SSD on the NEW server will contain the OS and the Database only, and all the sites are WordPress based resulting in about 250,000 pageviews total a day.

Thanks so much for any help or suggestions!

Regards,

Ben


texts: Can I Implement a Sliding Window for a Table Spread Across Two Partition Schemes on the Same Partition Function?

texts: I have a table with a PK based on a bigint identity and will have a clustered index on a datetime2 column. I've created a partition function based on calendar quarter and was planning on two partition schemes (one each for clustered and non-clustered indexes) to able to spread out I/O. However, if I plan to implement a sliding window to age out older data, do I need to keep all indexes in the same partition scheme or it doesn't matter as long as the partition function is the same and using the same datetime2 key column?


texts: Best Practices for Using Temp Tables in Stored Procedures

texts:
I recently developed a stored procedure which essentially queries data from several different tables, manipulates it, and then inserts the result in a certain table.

It ended up being one INSERT statement with many subqueries and was around 300 lines. The code was very performant; however, it is not very readable and may have other drawbacks.

Is it within recommended usage to write a query like so:

SELECT ...
INTO #tempfoo
FROM foo;

SELECT ...
INTO #tempbar
FROM bar;

SELECT ...
FROM #tempfoo
     INNER JOIN #tempbar ON #tempfoo.id = #tempbar.id;

Or is it better to write it like this, even though it can become somewhat unreadable:

SELECT ...
FROM
(
    SELECT ...
    FROM foo
) AS foo
INNER JOIN
(
    SELECT ...
    FROM bar
) AS bar ON foo.id = bar.id;

Note: I know this is a simple example, but imagine if you have layers of nested subqueries and four or more table joins.


texts: Mongodb Standalone Performs better than Replica Set

texts: I am currently using a standalone mongodb with high read operations. Since replica sets could provide high read performance, I setup a 3 node replica set on 3 vms with read preference as "secondary preferred". However I could not see any performance improvement, in fact the replica set runs a little slower(~2 secs) than the standalone.The configuration is as follows, 4GB of RAM, 7GB of data, 50 GB of hard disk on all 3 vms and for the standalone db and We are using aggregate queries. I would like to know what could be the real reason for the replica sets to function slower compared to the standalone db.


texts: How to find the number of queries executed since server startup in PostgreSQL

texts:
Is there any way to get the number of queries executed since server startup in PostgreSQL?

In MySQL we get this information from status variable Questions, Queries.

Thanks.


texts: Postgres - detect change in JSONB column value from 'null' to some value across rows

texts:
I have to work with a table that looks like this:

The actual table is obviously much larger, but for sake of discussion, is there any way to elegantly query the difference between when { a: null } turned into { a: "something"? }

My instinct is to try something (probably pseudocode) like this:

select (select update_time as finish_time from fun_table where json_data -> 'a' != 'null' as tableA) - (select update_time as start_time from fun_table where json_data -> 'a' = 'null as tableB) from tableA, tableB

But there are a lot of different JSON objects in the actual json_data..."b", "c", etc. So again, is there a better / cleaner way to find the difference between the timestamps when "a" turned from "null" to "something"?


texts: Multi server administration: Job that runs both on master and targets servers?

texts:
I'm trying to set up multiserver administration on SQL Server 2016. I have two servers, one master and one target. I created a test job that does a simple select statement and then sends an email. I want that job to run on both master and target servers but it seems that I can't.

I can't believe that's not possible (tried to google for that but didn't find anything).


texts: Finding the same value in a column and only returning rows without the same value

texts:
I have a query that i have to find the DiscountPercent that are the same and not show the rows with the same DiscountPercent. I have tryd this and it brings back the values that are the same

SELECT
    ProductName,DiscountPercent
FROM
    Products
WHERE
    DiscountPercent in (SELECT DiscountPercent
                        FROM Products
                        GROUP BY DiscountPercent
                        HAVING COUNT(*) < 1)
ORDER BY
    ProductName;

What i want to accomplish is to bring in the products that don't have the same discount percentage. I don't think i am doing this the right way.What is the correct way of doing this?

This is some data in the ProductName and in the DiscountPercentage

ProductName        DiscountPercent
Fender Precision     30.00
Fender Stractocaster 30.00
Gibson Les Paul      30.00
Gibson SG            52.00
Hofner Icon          25.00
Ludwig 5-piece       30.00
Rodriguez Cabelle    39.00
Tama 5-piece         15.00
Washburn D10         0.00
Yamaha FG700s        38.00

The Desired result that must use a subquery

ProductName       DiscountPercent
Fender Precision    30.00
Gibson SG           52.00
Hofner Icon         25.00
Rodrgiguez Cabelle  39.00
Tama 5-piece        15.00
Washiburn D10       0.00
Yamaha FG700s       38.00


texts:
Background:

We are trying to create an AG "is primary" test harness for one of our support teams.  Not knowing which servers at any given time will be the primary they have been instructed to execute the TSQL against a registered server group.  The registered server group consists of all servers in the AG.  The goal is to only execute TSQL on the current primary server:

Current Test Harness:

IF EXISTS (SELECT *
FROM sys.dm_hadr_availability_replica_states AS HARS
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS HACS ON HACS.replica_id = HARS.replica_id
    WHERE (HARS.role_desc = 'PRIMARY') AND (HACS.replica_server_name LIKE @@SERVERNAME))
BEGIN
<<SOME CODE TO EXECUTE>>
END

Problem:

If the first server that responds back to the multi server query doesn't return any results, SSMS will assume the right result set is no result set, even if other servers later come back with a result set.  So in this scenario, no results get returned...this is not correct and is not the expected functionality.

Can anyone think of a way, with SSMS (this is the most familiar tool to the CS team), to force execution only on current primary server?


texts:
I was watching a 7 years old webinar done by Brent Ozar (https://youtu.be/U_Kle3gKaHc), and heard of several items being recommended at that time.

SQLDiag utility.
SQLNexus.
PAL tool.
Database Tuning Advisor/wizard.
BPA (Best Practices Analyzer).
SQL Server Policy Based Management.

Are all of them still to be used/considered or there is something newer that replaced them?


texts: Why Oracle 19c Database EM Express Webpage is not opening in Microsoft Edge?

texts:
I have recently installed Oracle Database 19c on my Laptop which has Windows 10 OS. Now, I want to open the localhost:5500/em page in Microsoft Edge browser, but when I try to do so, the webpage does not open and it says localhost refused to connect.
But in the SQLPLUS command prompt I am able to login successfully and execute queries.
Please help me resolving this issue.


texts:
Getting this error, when updating the read replica MYSQL version from 5.6 to 5.7.
InnoDB: page_cleaner: 1000ms intended loop took 4253ms. The settings might not be optimal. (flushed=1113 and evicted=0, during the time.)


texts: What are the user privileges required to for an ssrs report owner to 'send email'

texts:
Yes, this question is a duplicate to:
  SSRS - rsAccessDenied error but that question is 2
  years old and unanswered...so I thought I might try again.

Our DBA has recently departed leaving behind a number of SSRS report subscriptions to which he is (or was) the 'owner'. Based on the advise found at Changing the SSRS Subscription Owner I have created a new domain account, assigned that domain account full privileges on the SSRS server (at least 'for now') and yet I am still getting the error message:

  Failure sending mail: The permissions granted to user 'DOM\NewUser'
  are insufficient for performing this operation.Mail will not be
  resent.

What privileges, in SSRS, SQL Server and/or Active Directory are required to allow "NewUser" to send email.


texts: How can I get the last inserted id from an specific table from a given stored procedure?

texts: I know about the existence of LAST_INSERT_ID() function... Though I am not quite sure about its behaviour... Does it take the last inserted id from the current user into account? Or uses all the sessions as reference? If the later holds true I am afraid another query by another user modifies such value before using such data. Are there any alternative ways to do this without recurring MAX()?


texts:
What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage?

Does BTREE provide easier iteration in increasing order ?
What kind of queries would benefit from a BTREE ?
Are there any disadvantages of using BTREE index ?
Does it increase space or indexing time?


texts: Tables with hierarchy: create a constraint to prevent circularity through foreign keys

texts:
Suppose we have a table that has a foreign key constraint to itself, like such:

CREATE TABLE Foo
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

INSERT INTO Foo (FooId, ParentFooId)
VALUES (1, NULL), (2, 1), (3, 2)

UPDATE Foo SET ParentFooId = 3 WHERE FooId = 1

This table will have the following records:

FooId  ParentFooId
-----  -----------
1      3
2      1
3      2

There are cases where this kind of design could make sense (e.g. the typical "employee-and-boss-employee" relationship), and in any case: I'm in a situation where I have this in my schema.

This kind of design unfortunately allows for circularity in data records, as shown in the example above.

My question then is:

Is it possible to write a constraint that checks this? and
Is it feasible to write a constraint that checks this? (if needed only to a certain depth)

For part (2) of this question it may be relevant to mention that I expect only hundreds or perhaps in some cases thousands of records in my table, normally not nested any deeper than about 5 to 10 levels.

PS. MS SQL Server 2008

Update March 14th 2012
There were several good answers. I've now accepted the one that helped me understand mentioned possibility/feasibility. There are several other great answers though, some with implementation suggestions as well, so if you landed here with the same question have a look at all answers ;)


texts: How to increase column size in Redshift database tables?

texts:
In oracle, I can:

Alter table table_name
modify column_name datatype;

Is this possible in a redshift database?


texts: Understanding CPU time to measure workload

texts:
I want to understand the workload of my stored procedures, which run on an instance with many other databases. Among other things I would like to know if I had my own server how many CPU core would I need and how much memory will be enough.
Say I have a server with one core. If I turn on "statistics time" and get 1000 •60•60 = 3600000 ms as CPU time does that mean that the core was occupied 100% for one hour. Is this a valid way to measure e.g. how many cores I need?
How can I measure how much memory is needed for my workload?


texts: Postgres LIKE query using a GiST index is just as slow as a full scan

texts:
What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but still it takes 5 seconds to return. Expected would be a few (like 100) milliseconds.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles  WHERE  parentpath LIKE 'somevalue'

When using %% in the query, it takes the not that long, even when using sequential scan (?!)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles  WHERE  parentpath LIKE '%a%'

I also have the same setup for the name (filename) column, when executing a similar query on that one, it only takes half of the time, even when using %%:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles  WHERE  name LIKE '%a%'

What I already tried cannot be written here in short words. Whatever I do, it gets slow starting from about 1 mio rows. As there is basically never anything deleted, of course vacuuming and reindexing does not help at all.
I cannot really use any other type of search than LIKE %% and a GIN or GiST index because I need to be able to find any character in the columns of interest, not only "words for a specific human language".
Is my expectation that this should work in around 100 milliseconds even for many million more rows that wrong?
Further information
DB create script
Re-try without any text or other index at all, 1.7 mio unique entries
EXPLAIN ANALYZE select * from residentfiles where name like '%12345%'
Seq Scan on residentfiles  (cost=0.00..78162.76 rows=33225 width=232) (actual time=0.076..3195.965 rows=45301 loops=1)
  Filter: ((name)::text ~~ '%12345%'::text)
  Rows Removed by Filter: 1604780+
Planning time: 0.596 ms
Execution time: 3318.595 ms

Try with Triagram gin index:
CREATE INDEX IF NOT EXISTS  name_gin_idx ON residentfiles USING gin (name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS  parentpath_gin_idx ON residentfiles USING gin (parentpath gin_trgm_ops);
CREATE INDEX IF NOT EXISTS  ext_gin_idx ON residentfiles USING gin (extension gin_trgm_ops);
EXPLAIN ANALYZE select * from residentfiles where name like '%12345%'

Aggregate  (cost=53717.59..53717.60 rows=1 width=0) (actual time=1694.223..1694.224 rows=1 loops=1)
  ->  Bitmap Heap Scan on residentfiles  (cost=341.89..53631.82 rows=34308 width=0) (actual time=72.010..1615.007 rows=46532 loops=1)
        Recheck Cond: ((name)::text ~~ '%12345%'::text)
        Rows Removed by Index Recheck: 111
        Heap Blocks: exact=46372
        ->  Bitmap Index Scan on name_gin_idx  (cost=0.00..333.31 rows=34308 width=0) (actual time=52.287..52.287 rows=46643 loops=1)
              Index Cond: ((name)::text ~~ '%12345%'::text)
Planning time: 10.881 ms
Execution time: 1694.755 ms

Try with varchar_pattern:
CREATE INDEX idx_varchar_pattern_parentpath ON residentfiles (parentpath varchar_pattern_ops);
CREATE INDEX idx_varchar_pattern_name ON residentfiles (name varchar_pattern_ops);
CREATE INDEX idx_varchar_pattern_extension ON residentfiles (extension varchar_pattern_ops);
EXPLAIN ANALYZE select * from residentfiles where name like '%12345%'

Aggregate  (cost=89718.74..89718.75 rows=1 width=0) (actual time=1995.206..1995.207 rows=1 loops=1)
  ->  Seq Scan on residentfiles  (cost=0.00..89574.98 rows=57507 width=0) (actual time=0.060..1913.114 rows=52232 loops=1)
        Filter: ((name)::text ~~ '%12345%'::text)
        Rows Removed by Filter: 1852103
Planning time: 8.280 ms
Execution time: 1995.255 ms


texts:
I have a query

select distinct id from patient where company_id in (1) and
(name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%')

running on a table that looks like this

CREATE TABLE `patient` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `status` enum('tmp','active') DEFAULT 'tmp',
  `remote_id` varchar(64) DEFAULT NULL,
  `name_last` varchar(64) DEFAULT NULL,
  `name_first` varchar(64) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `mtime` datetime NOT NULL,
  `ctime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_ID_SCHEME_MRN` (`company_id`,`remote_id`),
  KEY `ix_ID_SCHEME_LAST` (`name_last`),
  KEY `ix_ID_SCHEME_FIRST` (`name_first`),
  KEY `ix_ID_COMPANY_ID` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I run an explain on it, I see that MySQL isn't using any of my indexes.

           id: 1
  select_type: SIMPLE
        table: patient
         type: ALL
possible_keys: ix_ID_SCHEME_MRN,ix_ID_SCHEME_LAST,ix_ID_SCHEME_FIRST,ix_ID_COMPANY_ID
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 494
        Extra: Using where

I am surprised that it isn't using ix_ID_COMPANY_ID at least. But, honestly, I would think that it would make use of most of the indexes.

Any advice?

Thanks.


texts: Error during the installation of oracle 11gxe

texts:
When I tried to install Oracle 11g XE into my machine (Windows XP media center edition, version 2002  SP3) I'm getting an error as

0x80040702 Description: Failed to load DLL:chkportDll2

I've googled, but not found any suitable answer.


texts: DBCC SHRINKFILE for Log and Data when using Availability Groups

texts:
We had an issue where one of our database tables grew by 40GB by mistake. The database is part of an Availability Group with one primary and two replicas. While trying to delete the extra records we also managed to explode our log file sizes by ~70GB (Side note: always delete in small batches!) which started causing space issues on our primary and replicas. We were able to survive that by live-expanding the drives on the VMs and waiting for replication to finish, but now that the records are removed we would like to reclaim the unused space in the data and log files so it can be used by other databases.

How do you use DBCC SHRINKFILE for databases that are part of an Availability Group?


texts: 64 Availability groups max on SQL STD : Failure when attempting to create a new NetBIOS interface while bringing a resource online

texts:
I was patching our DB Servers this morning and encountered a really strange error when failing over some Availability Groups :

In our environment, we are running a somewhat unconventional setup, but it works well.
We have 85 Databases, running SQL Server Standard, so each DB has its own Availability Group.
What we do as a default, is to actually "Load Balance" the availability groups between the 2 servers. This means by default, we only have about 42 databases on each node, until patching where I failed all the DB's over to one node, and that when I received the error on about 24 databases.

In initial investigations, I found that you can only have 64 NetBIOS names per resource. Are Availability Groups not their own resource?
In another article, they talk about ensuring you have no duplicate IPs or DNS names. I can confirm that we have no duplicates of either.

Is it a possibility, that on SQL Standard, you can only have 64 availability groups per database?

Please help with any potential resolutions.
Thank you very much for your time.


texts:
My MySQL query is taking around 20 minutes to execute which I feel is excessive.

SELECT
  hyp_id,MAX( player_count ) AS MAXPLAYERCOUNT
  , MAX( viewer_count ) AS MAXVIEWERCOUNT
  , SUM( player_count ) AS TOTPLAYER
  , SUM( viewer_count ) AS TOTVIEWER
FROM player_count_log
WHERE
  DATE(added_time) = CURDATE()
GROUP BY hyp_id
ORDER BY  hyp_id

The player_count_log table contains over 63 million records with ~18k added every hour.

Here are screenshots of db and indexes


texts: How do I cancel an app lock request in SQL Server?

texts:
The sp_getapplock stored procedure has the following return values:

  0: The lock was successfully granted synchronously.
  1: The lock was granted successfully after waiting for other incompatible locks to be released.
  -1: The lock request timed out.
  -2: The lock request was canceled.
  -3: The lock request was chosen as a deadlock victim.
  -999: Indicates a parameter validation or other call error.

I am writing a wrapper for calling sp_getapplock in our data access layer and I want to know under which circumstances -2 can be returned so that I can throw a descriptive and helpful exception. It is obvious what return values of -1 and -3 mean and I can easily create test conditions that cause those values to be returned. How would I manage to get a return value of -2?


texts: Is there a native(none-web) GUI client of pgadmin4?

texts:
I don't like web-based GUI, so I only installed the pgadmin4-desktop package for my Kubuntu 20.04, instead of pgadmin4-web and pgadmin4.
According to the offical web page of pgAdmin4,

A desktop runtime written in C++ with Qt allows it to run standalone
for individual users...

I'm expecting that a native qt GUI app can be run, and acts as a pg-client, but I can NOT find it out anywhere, neither startting-menu nor the DESTDIR of the installation, i.e /usr/pgadmin4/bin.
There is only one program file /usr/pgadmin4/bin/pgadmin4, but running it still open a browser window to serve as a pg-client, instead of any NATIVE app!
Would anyone please tell me that:
Is the one so called "A desktop runtime written in C++ with Qt" meaning a native GUI client app? or only a management server that resides on the system tray?
If a native GUI client really exists, where to find it or how to start it?
Thanks a lot!
Pls forgive my ugly English.


texts: How do I make the zero values show up in count?

texts:
I have encountered a question with the following table:

Column Name
Type

user_id
int

spend_date
date

platform
enum

amount
int

The goal is to find the total number of users and the total amount spent using mobile platform only, desktop platform only and both mobile and desktop together for each date. For example, if on 2017-07-01 user '1' used mobile and desktop to make a purchase, this purchase would be accounted for "both mobile and desktop together". If user '2' used mobile to make a purchase but never used desktop, this purchase would show up in "mobile only."
My code looks like the following. There is no purchase by anyone using both mobile and desktop on 2017-07-02, thus there should be an extra row ['2017-07-02', 0,0]. However, this row does not show up. Can anyone please let me know what's wrong with my code:
with
mobile_users as (select distinct user_id
                 from spending
                 where platform = 'mobile'),
desktop_users as (select distinct user_id
                  from spending
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id in (select user_id from
                   desktop_users)
   and user_id in (select user_id
                   from mobile_users)
 group by spend_date)
order by spend_date


texts: Writing functions in Postgres that returns strings with dots in reverse like java namespaces

texts:
Looking for a Postgres 9.6 function that reverse sorts a string the same way that java namespaced packages do.  Preferable if this is just PGSQL, I know that this is simple in the V8 engine or Python.

I.e. if I have the string foo.bar.baz.example.com, I want the string com.example.baz.bar.foo.  My very naive attempts like running a
REVERSE on the string didn't work.

I'm thinking I would need to explode on the periods, (can you explode in Postgres?), start a cursor, loop over the set, insert the last item from the set, shift that item off the set, and continue until the set is empty.

But I don't know how to do any of that.


texts: How to show multiple names for one record but not duplicate the record?

texts:
I have a database structure like so:

Car
 - id
 - carname
 - image
 - category
 - status

Parts
 - partid
 - partname

CarParts
 - carpartid
 - carid(fk)
 - partid(fk)
 - amountid(fk)

Category
 - id
 - categoryname

Amount
 - amountid
 - amountvalue

Right now this is what I am working on:

SELECT * FROM carparts
INNER JOIN car on carparts.carpartid = car.id

INNER JOIN parts on parts.partid = carparts.carpartid

INNER JOIN amount on amount.amountid = carparts.amountid where status = 1

This returns duplicate records when there is more than one carpart in the carparts table. How can I combine the carparts.id and car.id to one row but still have multiple partname for that one record?

So what has to happen is that each car could have multiple car parts but when the query runs if there is more than one car part for one record it shows duplicate records with different partnames is there a way to have to one car with many partnames for that record?

Do i have to change the database structure or the query to do this?


texts:
I need some statistics regarding the performance of Teradata, something like with a given hardware, given nodes and given AMPs - a table which has thousands of rows can be read in 't' secs.
I need this information to analyze the feasibility of teradata.


texts: What should be innodb_buffer_pool_chunk_size for pool_size=2G, pool_instances=2?

texts:
I have 4G VPS with following config

innodb_buffer_pool_size=2048M
innodb_buffer_pool_instances=2

SELECT @@innodb_buffer_pool_chunk_size/1024/1024
128.00000000

Should I change innodb_buffer_pool_chunk_size or let it be default?


texts: What is Diagnostic.data file in MongoDb data folder?

texts:
I'm trying to create a backup server for my mongodb using the strategy of copying files.

I wonder what is Diagnostic.data folder? Should I copy that too? or I can simply ignore it?

My Diagnostic.data folder size is around 300MB that is bigger than my other database files.

What about journal folder? Can I ignore it too?


texts: MySQL CSV LOAD DATA INFILE ... doesn't want to load more than 72 lines

texts:
I'm trying to load a .csv file into my database with many lines of input from a website I'm working on.  For some reason I can load files multiple times (over and over) into the database but they have to have less than 73 lines of input.  I don't see any problem with the input for the 73rd line.

I'm doing this in php and mysql

if(move_uploaded_file($path, $target_path))
{
  $sql = "LOAD DATA INFILE '" . $target_path . "' INTO TABLE `mydb`.`testtable`
  CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
  (`num`, @FieldNum, @CommonName, @LocalityRemarks ... )
  SET
  FieldNum = NULLIF(@FieldNum, ''), CommonName = NULLIF(@CommonName, ''),
  LocalityRemarks = NULLIF(@LocalityRemarks, '')  ... );";

the size limit in my php.ini is 8M

I can have past 1000 lines in the database table when testing the loading of files less than 73

So the SQL is fine.  There seems to be something going on with the php.


texts: Is it better to define foreign keys in the database or in the code part of an application?


texts: Intellisense in SSMS 2012 not working for 2008 server

texts:
I did most of the solutions provided here.

I know about

enabling IntelliSense
clearing the cache
connecting to a server >= SQL Server 2008
using the latest SQL Server Service Pack

and it works locally with the SQL Server 2012 engine.

But it still does not work when connecting to a SQL Server 2008 SP1 engine from my SSMS 2012. If I do

SELECT SERVERPROPERTY('ProductVersion')

I get 10.0.2573.0 as result. So the server is 2008 SP1.

Edit

After a few days it started working. I don't know why.


texts:
I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

It ignores the index completely. This confuses me as I have another table CustDataMaster with way more columns (55), one of which is CustNum varchar(8). I created an index on this column (IX_dbo_CustDataMaster_CustNum) in this table, and use practically the same query:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

And it uses the index I created.

Is there any specific reasoning behind this? Why would it use the index from CustDataMaster, but not the one from CustPassMaster? Is it due to the low column count?

The first query returns 66 rows. For the second, 1 row is returned.

Also, additional note: CustPassMaster has 4991 records, and CustDataMaster has 5376 records. Could this be the reasoning behind ignoring the index? CustPassMaster also has duplicate records that have the same CustNum values as well. Is this another factor?

I am basing this claim on the actual execution plan results of both queries.

Here is the DDL for CustPassMaster (the one with the unused index):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

And the DDL for CustDataMaster (I've omitted a lot of irrelevant fields):

CREATE TABLE dbo.CustDataMaster(
    [CustNum] [varchar](8) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
    [CustNum] ASC
)WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I don't have a clustered index on either of those tables, only one nonclustered index.

Ignore the fact that the datatypes don't entirely match the type of data being stored. These fields are a backup from an IBM AS/400 DB2 database, and these are the compatible datatypes for it. (I have to be able to query this backup database with the exact same queries, and get the exact same results.)

This data is only used for SELECT statements. I don't do any INSERT/UPDATE/DELETE statements on it, except for when the backup application is copying data from the AS/400.


texts: Why does a SELECT with no WHERE cause a full table scan, (or does it)?

texts:
I have the following table with ~2000000 rows

CREATE TABLE TableA (idA integer PRIMARY KEY, idB TEXT UNIQUE)

From time to time, I need to process each rows, the order does not mater.

So I process 200 rows at a time, (around a transaction)

SELECT iaA, idB FROM TableA  ORDER BY ROWID asc LIMIT 200

but when I checked the query with EXPLAIN QUERY PLAN I noticed that it was doing a table scan.

0    0    0    SCAN TABLE TableA USING INTEGER PRIMARY KEY (~1000000 rows)

I am especially curious about the ~1000000 rows

It does not matter if I select 1 row or 1000 rows, I still do a table scan with ~1 million rows.

Is this not the most efficient way of querying the table for 'any' data?

As I said, the order does not matter, the data has to be processed.

Edit: If I run the query with no ORDER BY

SELECT * FROM TableA LIMIT 200

I get fairly similar results.

SCAN TABLE TableA USING COVERING INDEX sqlite_autoindex_TableA_1 (~1000000 rows)


texts: SQL 2017 CU10 on Windows Server 2019 - Availability Group Listener fails to obtain IP from DHCP

texts:
We have a few instances of SQL 2017 CU10 Standard on several Windows Server 2016 machines, and normally we create Basic Availability Groups with Listeners obtaining IPs from our DHCP (192.168.112.0/22 network).

We have static IPs for servers and DHCP for Basic AG Listeners.

Everything worked fine, however Listeners for AGs created on new (fully updated) Windows 2019 servers fail to get an IP address from DHCP.

I've checked our DHCP on new servers and it works perfect, server itself does obtain IP from our DHCP which means that DHCP is working fine.
I've tried to create a Listener with Static IP on new Windows Server 2019 and it works good, which means that AD permissions on OU for cluster computer account are okay (double checked it too).
I've tried to create an AG with a Listener on existing Server (Windows 2016) and Listener created there successfully obtains an address from DHCP, which means that our DHCP pool is not exhausted.
Actually there is one thing different, our new servers are connected to a freshly set up VLT-stack of Dell S4148F-ON switches, however as far as it has all VLANs configured and DHCP is working normally (see #1) I don't see any reason to blame the network.
When I capture traffic on physical interface in Wireshark on 2019 machine I see a number of broadcasts from DHCP server (Dell N3000 core switch) containing what I assume is a leased client address: Your (client) IP address: 192.168.113.143.

But it looks like the server does not accept them (?), the error I'm receiving is: Cannot bring the Windows Server FailoverClustering (WSFC) resource (ID ...) online (Error code 5942). <...>

In System Event Log I see two instances (based on two attempts, according to Wireshark) of Event ID 1240: Cluster IP address resource 'db11_192.168.112.0' failed to obtain a leased IP address.

And I've found nothing of interest in Failover Cluster Event Logs, just a detailed log of resource transitioning to failed state.

Maybe I'm missing something, but I don't see any other difference between old and new servers except new ones run Windows Server 2019.

Would be happy if someone suggests any further diagnostics since I've run out of ideas, thanks in advance.


texts: What happens when we call pg_dump while users are still using the system?

texts:
We have a web application with PostgreSQL as our database and use celery background tasks for somewhat complex task. At the very beginning of this complex task we take a backup of the database using pg_dump.

I was wondering about the state of this sql dump if the users are still using the system. Could it cause any inconsistencies ?

Also, I would like to know if pg_dump is the best choice for taking backups in this case. Of course as the database grows the backup takes a lot of time which slows the overall process.

Thanks.


texts: MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved]

texts:
I'm trying to create a simple cricket application, where i will store all cricket records from a database. I collect a sample database with data from online and i successfully did some query but I'm unable to write the query for finding Partnership Scored for a match. I have no clue how can i do this.

Below is the three main Table which i will used to get the data:

  1. ball_by_ball table, this table holds full information by ball by ball:

  2. batsman_scored, this table holds batsman run scores by ball by ball:

  3. wicket_taken, this table hold wicket fallen information by ball by ball:

I tried below query but I'm unable to group those properly

select P.Player_Name, P1.Player_Name, sum(b.runs_scored) as Runs
from ball_by_ball a
       inner join batsman_scored b using (match_id, over_id, ball_id, innings_no)
       inner join Player P on a.striker = P.Player_Id
       inner join Player P1 on a.non_striker = P1.Player_Id
where a.match_id = 981018
  and a.innings_no = 1 group by P.Player_Name, P1.Player_Name;

So this my output: (this is not what i want)

  If you look above screenshot, there Row 1 and 2 will be same, AJ Finch BB McCullum and BB McCullum AJ Finch will be same row and the
  Runs column will be sum. That's what i want but i'm unable to do it.

UPDATED- This is the finally query which gives the partnership information:

select P.Player_Name, Runs + Extra as Partnership, P1.Player_Name
from (select if(striker > non_striker,
                concat(striker),
                concat(non_striker))      as Bat1,
             sum(b.runs_scored)           as Runs,
             ifnull(sum(e.extra_runs), 0) as Extra,
             if(striker > non_striker,
                concat(non_striker),
                concat(striker))          as Bat2
      from ball_by_ball a
             left join batsman_scored b using (match_id, over_id, ball_id, innings_no)
             left join extra_runs e using (match_id, over_id, ball_id, innings_no)
      where a.match_id = 981020
        and a.innings_no = 1
      group by if(striker > non_striker,
                  concat(striker, '-', non_striker),
                  concat(non_striker, '-', striker)), Bat1, Bat2) as t
       inner join Player P on t.Bat1 = P.Player_Id
       inner join Player P1 on t.Bat2 = P1.Player_Id;

  This is the final output which i wanted:


texts: Filtering data that could have more than one category

texts:
I have a table like the following:

Annotation (
    document,
    term,
    category
)

Where document and term are some ID, while category is an integer.

The couple document - term is not unique, i.e. I could have the same couple with a different category.

document_id_1, term_id_1, category_1
document_id_1, term_id_1, category_2
document_id_1, term_id_1, category_3

I would like to design a query such that it return only the couple document-term for whom there exists a only a row with category = 1.

E.g. in the previous example the couple document_id_1 - term_id_1 is not returned becouse there exist also other two rows, with different values of category.

Can you give me some hints on how to do that?


texts: SELECT Query is slow when run remotely in MySQL

texts:
When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then It's results are very fast.

    SELECT * FROM CUSTOMERS

The customer tale contains two million records.

I have also enable skip-host-cache and skip-name-resolve in MySQL configuration.

Both machine is connected using the LAN IP and customer table is InnoDB table. I am using MySQL 5.6.

Can you please suggest me on this, why SQL query are very slow when run It from remote machine.

Thank you,
Sujeet


texts: How can I enter Mongo as a superuser or reset users?

texts: I'm was playing around with permissions and locked myself out of Mongo database.  I'm pretty sure I did this by trying to explicitly add access to a database but instead I overwrote only allowing permission to the database. So I'm effectively locked out of my Mongo database and everything I read tells me how to create a super user if I have the add user privilege.  Right now I don't think I have any users that have that privilege.  Is there a way to enter the database as all access?  I own the server and have root access.


texts: Replicaset with MongoDB: Two datacenters, one primary and the other secondary

texts:
The tutorial I found on MongoDB's website suggest installing an odd number of servers. For what it's worth, we're not sharding so it should be a simple setup. For even numbers I would need an arbiter. In our environment, DC1 is always the primary with DC2 being the secondary where we would fail over services in the event DC1 fails. With that said ...

If I have six nodes (3 node in each DC), would I need an arbiter?
If I have five nodes (3 in DC1 and 2 in DC2), if DC1 fails, wouldn't I need an arbiter in DC2?

I guess my confusion is how to handle failover in the event DC1 fails.


texts: Equivalent SQL Server's Backup COPY ONLY in DB2

texts:
In, DB2 when you have a recovery strategie, you have a Full backup, then some deltas or incrementals in between. They are creating based on the previous full (first delta, or any incre.

-F---D---D---I---D---D---D

However, if you perform a backup in the middle (let's suppose for a test, and the file will be deleted), then the following deltas or incrementals will be created based of that backup that was not part of the recovery strategie.

-F-
  F--D---D---I---D---D---D

I saw that SQL Server has an option calle COPY ONLY that prevents this problem: http://technet.microsoft.com/en-us/library/ms191495.aspx

How can I do that in DB2?


texts:
I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query.

I've been looking for this for a while without any success. I looked for a property that could be set through the db2set command, but the only one referring NLJ is DB2_INLIST_TO_NLJN.

The closest I've found is through the usage of optimization profiles but those are very specific and what I need is a general setting that would apply to any statement being optimized.


texts: Postgres: error message "does not exist" when dropping existing trigger

texts:
I have a trigger associated to a table. When i drop it via

DROP TRIGGER IF EXISTS myTrigger on dummytable;

postgres tells me

NOTICE:  trigger "mytrigger" for table "dummytable" does not exist, skipping
DROP TRIGGER

When i use a dummy table for tests this works. I've tried:

changing CaSe,
making the trigger name longer (the real trigger is 17 chars long)

When testing it always drops without issue.

This is Postgres 8.2 on Linux. What does work is adding quotation marks:

DROP TRIGGER IF EXISTS "myTrigger" on dummytable;

Why this is so is beyond me, there's nothing in the docs that mentions the name must be in quotation marks.

Am i missing something obvious?


texts: Querying with multiple where conditions and multiple columns

texts:
I have a Policies table.

policyId
status ("Pending" or "Issued")
pendDate
issueDate

Now I need get all pending Policies with a pendDate in the month of October AND all issued Policies with a issueDate in the month of October.

I'm using postgreSQL 9.6. These dates are nullable so I cant use a simple AND. I need only the pending and issued in that month so ORs dont work.

I solved with two queries but now more statuses are being added and I want to consolidate somehow. Possible?


texts:
Using SQL Server 2008 R2:

I'm currently attempting to track down the cause of a deadlock issue we have.  Not sure where to turn.  Here's the setup.

TABLE Scores:
id INT IDENTITY(1,1),
first_name VARCHAR(50),
last_name VARCHAR(50),
player_id VARCHAR(50),
score INT

PRIMARY KEY/CLUSTERED INDEX:
id

NON-CLUSTERED INDEX:
first_name, last_name INCLUDES id, player_id, score

A multi-threaded application processes messages, then calls one of two stored procedures for every message.

If the application has not processed the user, it will call a stored procedure that inserts a row into the table.

If the application has processed the user, it will update any or all of first_name, last_name, scores, based on first_name and last_name  player_id does not change.

There will be only one row in the table per player_id.

The thread that the application processes messages is chosen based on the player_id, so two threads will NOT process messages for the same player at the same time.

The INSERT and UPDATE statements are using the WITH (ROWLOCK) directive.

Here's the issue:

The process is causing deadlocks with thread 1 locking the primary clustered index and thread 2 locking the non-clustered index.

I do not understand why the locks are occurring - the UPDATE and INSERT statements are using row locks, and it's been tested and proven that threads are not accessing the same rows.

What could I do to fix this issue?

Caveats:

There must exist two stored procedures, one updating and one inserting.  As much as I would like to switch to a single MERGE, that's not gonna happen.  : /


texts: Failed to create, join or add replica to availability group in SQL Server 2014

texts:
I have a 2 node FCI and a standalone SQL Server installation on a non FCI node. I have been automating the configuration/installation of FCIs, AGs, and DB Replicas which has worked fine up to now in all of my testing.

Today I am getting the error below when I execute:

USE [master]
GO
CREATE AVAILABILITY GROUP [AGName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR
REPLICA ON N'Node3\ReadOnly' WITH (ENDPOINT_URL = N'TCP://Node3-blah.blah.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'Primary/Primary' WITH (ENDPOINT_URL = N'TCP://primary.blah.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

Error:

  Msg 19405, Level 16, State 17, Line 3
  Failed to create, join or add replica to availability group 'AGName', because node 'Node3' is a possible owner for both replica 'Node3\ReadOnly' and 'Primary/Primary'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again.

Node 3 is not part of the FCI.  It has a standalone installation of SQL Server and it is not listed as a possible owner.

If I attempt to failover to Node 3 the FCI informs me that it is not a possible owner.

I'm not sure what caused this.  I did failover using the FCI between node 1 and 2 several days earlier.  I did remove the listener this time to test that, as that was the last thing I was working on.  Any thoughts?

I could just tear down the FCI and have the automation recreate it but I wanted to try to solve this without doing that just in case this happens in prod one day.  We should be able to evict the node from the WSFC and re-add it as well but I don't want to bug our ops team which has to do that.  We will be tearing down the entire cluster and letting it build from scratch before releasing it though.

Edit. This is the output from the FCI node:

select * from sys.dm_os_cluster_nodes

NodeName    status  status_description  is_current_owner
---------------------------------------------------------
SQNodeL001-LA   0   up  1
SQNodeL002-LA   0   up  0
SQLNode003-LA   0   up  0

The output from the standalone is empty.  It is part of the WSFC but not FCI or AG yet.

The powershell output shows us that all 3 can be owner nodes which is odd.

ClusterObject                                               OwnerNodes
-------------                                               ----------
SQL Server (Instance)                                           {SQNodeL001-LA, SQNodeL002-LA, SQNodeL003-LA}

From the GUI it does not have node 3 selected as a preferred owner.  Pardon for making minor changes and removing the names.  The first node name is all in lowercase.  The other 2 are in upper case.  It looks like I might need to do all this in powershell to get the accurate data, haven't gotten to automating that step yet.  That'll be next sprint.

Edit 2 - solved:

Thanks to Sean's PowerShell query I was able to see that it is still listed as a owner in PowerShell although it is not in the GUI. I removed it using get-clusterresource "sql server (instance)" | set-clusterownernode -Owners node1 node2 and it worked.  Thanks Sean!


texts: SP how can I sum the amount of all leftchild in parentid

texts:
I have this data and table structure,I am creating MySQL stored procedure to sum all the amount in the leftchild of the parentid, I don't have enough knowledge in stored procedure.and I need some help please because I'm lost.

CREATE TABLE IF NOT EXISTS `mytree` (
  `parentid` int(11) NOT NULL,
  `memberid` int(11) NOT NULL,
  `position` char(1) NOT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mytree` (`parentid`, `memberid`, `position`, `amount`) VALUES
    (8, 27, 'R', 0.00),
    (8, 28, 'L', 0.00),
    (24, 26, 'R', 0.00),
    (0, 1, '', 5500.00),
    (24, 25, 'L', 0.00),
    (21, 24, 'L', 500.00),
    (21, 23, 'R', 0.00),
    (18, 20, 'R', 1500.00),
    (18, 19, 'L', 0.00),
    (15, 18, 'R', 2000.00),
    (15, 17, 'L', 0.00),
    (13, 16, 'L', 0.00),
    (13, 15, 'R', 2500.00),
    (12, 14, 'R', 0.00),
    (12, 13, 'L', 3000.00),
    (10, 12, 'R', 3500.00),
    (10, 11, 'L', 0.00),
    (7, 10, 'R', 4000.00),
    (7, 9, 'L', 0.00),
    (5, 8, 'R', 500.00),
    (5, 7, 'L', 4500.00),
    (1, 6, 'R', 0.00),
    (1, 5, 'L', 5500.00),
    (20, 22, 'R', 0.00),
    (20, 21, 'L', 1000.00);

here is my stored proc

CREATE DEFINER=`root`@`localhost` PROCEDURE `count_left_right_amount`(IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

DECLARE row_total decimal(10,2);
DECLARE total decimal(10,2);
DECLARE m_left int;
DECLARE run_left int;
DECLARE p_id int;

set total =  0;

select memberid into p_id from mytree where position = 'L' AND parentid = p_memid;

WHILE  p_id != 0 DO

 select sum(amount) into row_total from mytree where parentid = p_id
  GROUP BY parentid;

 select memberid into m_left from mytree where parentid = p_id;
 set total = total + row_total;
 set tot_left_amount = total;
 set p_id = m_left;

END WHILE;

END

Example if the parentid is 5 ,so the leftchild is 7 then go down to his subtree sum it all,so the total amount in the left child is '22500'? please correct me if I'm wrong.

please help me.

Thank you in advance.


texts: Loading multiValued fields into Solr via flat file, and possibly value position preservation in those fields

texts:
A hobby web project of mine uses data from many sources, some flat, some highly normalized. My ETL process boils all sources down to one logical table (which is soon to include some multi-valued fields). The final table is loaded into a few separate data presentation environments (Solr or MySQL based). My Solr environment demands a denormalized model (it just loads the transformed output table directly as a flat file), and my MySQL environments can each use a highly normalized model (loading involves some normalization). Both presentation environment load processes are very simple right now.

To facilitate the use of flat files throughout my ETL process (important because I use a variety of tools to summarize and cleanse the data, and make heavy use of pipelines for performance reasons), I've been able to reduce multi-valued fields from my sources to a single field using grouping or best candidate selection.

Now I'm designing for the multi-valued fields I need to preserve in my output. The MySQL load process is easy because I've already normalized it with these fields in mind. But I'm having trouble with the Solr part:

I have zero experience with "multiValued" fields in Solr
the flat file Load process is currently very simple, I'd like to preserve as much simplicity as possible
my multiValued fields must be coordinated by position within a record, e.g.

record X, field A, position 3 is a license #
record X, field B, position 3 is the corresponding state for that license

How might I design my ETL for the multi-valued fields I plan to preserve?

Some ideas I've come up with that I'm hitting roadblocks on:

introduce an "additive" record using the same document key that occupies its own row in the flat file

no idea where to start on this one

pre-assemble values into a single field, parse using Solr, e.g.

{[[123],[456]];[[MA],[CA]]} -> "123,MA;456,CA"
not sure if Solr can unpack a string and preserve position info appropriately across fields

I've been unable to find information about coordinating multiValued field values from separate fields. Allowing for the fact that this might not be natively supported by Solr, I'm still interested in solutions to loading multiValued fields into Solr in a way that's nearly as simple as loading a flat file.


texts: Necessary tuning settings for SQLite databases

texts:
I am working on a SQLite DB containing ~30 .db files. Each of these .db files corresponds to one table, e.g., the "table1.db" file contains a "table1" table, "table2.db" contains "table2" table and so on. The size of most of our tables are small (in KB) and we only have 4 tables in MB (each of them are ~1.5 MB). There is no index on any of these tables and queries are fast. Tables don't have more than 5000 rows now and they will go maximum upto 100k rows.

Actually, we have not experienced any performance issues yet, but we would like to know whether we could improve our DB further, e.g., we are not sure whether we should keep tables without any indexes or is it better to define some indexes. (I am looking for something like Ten MySQL performance tuning settings after installation, but for SQLite DB.

I've read some tips (e.g., using transactions and prepared statements for INSERT/UPDATE, set PRAGMA synchronous = OFF, etc) in the following posts:

What are the performance characteristics of sqlite with very large database files?

Improve INSERT-per-second performance of SQLite?

How can I analyse a Sqlite query execution?

PS. I checked our log to see the queries and we don't have any JOIN and using EXPLAIN QUERY PLAN always show the full table scan.

So, my question is: Is there anything I could do to further improve the performance? What should I consider now that might be useful later when our DB get larger? (each table will not go bigger than 100k rows)


texts: Mysql Slave Claims to be Running but New Records Not Appearing and No Delay

texts:
I've created a new mysql master and a slave. My master is also a slave of another running instance. The replication between those two works fine so I don't believe my issue is there.

I created a dump from the master with master-data=1 then sent the dump to the slave and created wrote it < file.sql. If I run show slave status I get back (roughly, simplified for MVCE, if more is needed please specify):

Slave_IO_State: Waiting for master to send event
Master_Host: master_ip
Master_User: replicant_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: ip-slave_ip-relay-bin.000004
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 120
Relay_Log_Space: 512
Master_Server_Id: 2222
Master_UUID: f2c7d7a1-bb6c-11e8-a3b1-02e34d38ec08
Master_Info_File: /var/lib/mysql/master.info
Seconds_Behind_Master: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400

then If I do show master status on the master I get back:

File: mysql-bin.000003
Position: 120

which looks correct, the position and the file match. Then if I view my users:

select userid, username from users order by userid desc limit 30;

I get 28 more records on my master and the master master (master master and master counts and values match) so the slave isn't getting new records despite the Slave_SQL_Running: Yes.

How is this possible? I guess I did something wrong but what, where should I look?

I looked at the mysql-bin.000003 but is says:

  Warning: this binlog is either in use or was not closed properly.

which makes sense it is the current file. I looked at the 00001 and 00002 to see if the queries I missed were in either of those files but I couldn't find them.


texts:
I'm doing some research about the DCM (Differential Change Map) page in SQL Server 2016.
This is what I discovered so far:

page type: 16
each DCM page covers a GAM interval
each data file has a DCM page starting with page_id 6

When I analyzed a database containing filestream data I discovered something strange. I used following query to retrieve the file_id of the file containing the filestream data:

select file_id from sys.database_files where type_desc='FILESTREAM'

Next I used DBCC Page to analyze the DCM page of this file

dbcc traceon(3604,1)
dbcc page(33,65537,6,3)
dbcc traceoff(3604,1)

In the 'Allocation Status' I saw that the DIFF pointed to (1:6).

  Allocation Status

  GAM (1:2) = ALLOCATED
  SGAM (1:3) = NOT ALLOCATED
  PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
  DIFF (1:6) = CHANGED
  ML (1:7) = NOT MIN_LOGGED

Does this mean that a file with FILESTREAM data always points to a DCM page in data file containing the filestream table?
I think this is because a file with FILESTREAM data doesn't contains data pages but serves as a container for the files.
Is my conclusion correct?


texts: SQL Replication across the internet give named pipe error

texts:
We've had a db replication working between two servers across the internet.

Server A is sitting on our server network (Windows Server 2008 + SQL Server 2008 R2)
Server B is sitting on a different network (Windows Server 2008 + SQL Server 2008 R2)

Ports are still open, TCP and Named Pipes are enabled. Alias configured, TCP and ports open in the firewall, everything was smooth and working.
All SQL Services are also running.

Server A is the publisher and Server B is the subscriber.

It's been using a Push subscription so everything was working on Server A.

All data was synced and everything was fine.

After a server issue on Server A, the DB name changed, the replication and publication was recreated and Server A was upgraded to SQL Server 2008 R2 SP3.

So far that's the only noticeable difference between the two, the publisher is running SQL 10.50.6000.34 and the subscriber 10.50.2500.0.

Now the replication stopped working and I'm not able to connect the two via named pipes..

If I try to create a new subscription from either server and use the server name (for replications you need to use the server name instead of TCP) I keep getting named pipe error

I can ping each Server from the other server.
I can connect from SQL Management Studio using tcp with domain or ip and run queries on the other server.
I can add them as linked servers and see the data as well.

But the moment I go to create new subscription and add the remote subscriber I get the named pipe error.


texts: Select only last row from table for list of values

texts:
As example we have a table:

CREATE TABLE t1 (
   a BYTEA NOT NULL,
   r_pointer BIGINT,
   s_pointer BIGINT,
   data BYTEA,
   PRIMARY KEY(a, r_pointer)
) PARTITION BY HASH (a);

In case we I want get last element:

SELECT * FROM t1 WHERE a ='\xaaa' order by r_pointer desc limit 1;

Of course, I can use executemany for this statement, but this is not very good for performance.

How can I get only the first row found for each element in a given array like {\xaaa,\xbbbb}. Similar to the following, but this returns all rows:

SELECT * FROM t1
WHERE  a = ANY ('{\xaaa,\xbbbb}'::bytea[])
ORDER  BY r_pointer DESC;


texts: Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

texts:
For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes).

The data in the database is highly (and not arbitrarily) normalized. It very efficient at organizing and shuffling data to be displayed in a lot of very helpful ways for different purposes, except this one particular query is throwing a wrench into it.

I can't understand the reason for it. However, some background information that may shed some light onto otherwise arbitrarily convoluted queries.

The company has divided the world up into many many teams (macroregions). Everybody belongs to one or two teams, based on their expertise.

For example, there are many diverse teams. A few examples are Spanish, Sahara, Iberia, Portuguese, Jungle teams. Each of the teams has considerable overlap with other teams, but is in some senses independent.
The Arabic team works quite closely with the Sahara team, by virtue of the fact that the database tells them they have to work together on certain assignments, because of overlapping geographic locations. The Spanish and the Portuguese team also work closely together, they both work with the Americas and Europe teams and the Portuguese team also works with the Africa team, as does the Arabic team.
Each team has a given set of regions, which are also not unique to that particular team. For example, the Mediterranean region belongs to around 12 teams, and when an event occurs there, they all work on it together.
Each country belongs to one or more regions. Turkey belongs to Central Asia, Europe and even Mediterranean, and a few others.

Given all that, it is necessary to show each individual what other people on their teams are doing, as well as people who are not on their team, but have overlapping regions.

Query 1 accomplishes this perfectly, and very quickly less than .09 seconds.

                SELECT report_name
                FROM reports
                WHERE region IN (
                    SELECT distinct region
                    FROM macroregions
                    WHERE macroregion IN (
                        SELECT distinct macroregion
                        FROM users
                        WHERE callsign = '$thisuser'
                    )
                )

It is also important people can see every occurrence of the report_name, when it Query 1 deems that the individual logged in should know about it. Reports_names are automatically generated and reused for each geographic location

If I am on the East Asia team and someone in my area is working with the Spanish team, there would be two entries in the database:

20120210JOMX01 Japan Okinawa
20120210JOMX01 Mexico Nuevo Leon

Query 2 takes Query 1 and wraps one layer of WHEN-INs around it. It allows the East Asia team members know that someone on their team is working in Mexico. But the performance delay is unacceptable and totally unusable; it takes nearly 5 minutes to complete a single query!

        SELECT *
        FROM reports
        WHERE report_name IN (
            SELECT report_name
            FROM reports
            WHERE region IN (
                SELECT distinct region
                FROM macroregions
                WHERE macroregion IN (
                    SELECT distinct macroregion
                    FROM users
                    WHERE callsign = '$thisuser'
                )
            )
        )

Despite the fact that the query works, it takes too long to be effective. Again, it is startling how long this particular query takes. Whereas nothing else in the interface has given me any sort of performance issue (i.e. they all take less than one second).

What steps can I take to fix this?


texts:
I have 10 tables.

dbo.Table2008
dbo.Table2009
...
dbo.Table2018

Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a clustered columnstore index on it.
Each table also holds data only for the year in its name. For instance, in dbo.Table2008 you'll only see records for which the CreatedDate column is >= 20080101 and <= 20081231. But if I want to issue a query across 3 years I need to hit all three tables with a union.

My theory is if I have a single table called dbo.Table which is partitioned into ~120 partitions, one partition for every month of every year, then it'll not only reduce my table count. So I can change my query from

with cte as (
    select col1, col2, col3, col4
    from table2008
    where col4 >= 20080201 and col4 <= 20120801
    union
    select col1, col2, col3, col4
    from table2009
    where col4 >= 20080201 and col4 <= 20120801
    union
    select col1, col2, col3, col4
    from table2010
    where col4 >= 20080201 and col4 <= 20120801
    union
    select col1, col2, col3, col4
    from table2011
    where col4 >= 20080201 and col4 <= 20120801
) select ...
from cte
join LookupTable1 on ... = ...
join LookupTable2 on ... = ...

to

select col1, col2, col3, col4, lookuptablecol1, lookuptable2col2
from dbo.Table
join ALL MY LOOKUP TABLES

Is my theory correct?
Is table partitioning with parition schemes and partition functions what I'm after?
And will this theory work using clustered columnstore indexes?


texts:
Let be the following relational scheme:
F={AB → C; AC  →  B; AD  →  E; B  → D; BC → A;E  → G}
Is the following decomposition without loss of dependency?
R¹=(A,B),R²=(B,C),R³=(A,B,D,E),R⁴=(E,G)

Looking then at their closures:
[AB]⁺={A,B,C,D,E,G}
[BC]⁺={B,C,A,D,E,G}
The teacher then did something about closures I don't perfectly understand:
F¹⁺=∅
F²⁺=∅
F³⁺={AD → E, B → D, AB → D,AB → E}

I know that F⁺={X → Y\F⊨X → Y}
and that [X]⁺= {A\F⊨X → A}

Then she did:
[AB]⁺⇔
AB → C;
AB → D;
AB → E;
AB → G
[AB]⁺ ∪ F³⁺={A,B,D,E,G}, C isn't in [AB]⁺ ∪ F³⁺
Therefore it isn't a dependency preserving decomposition
Can you help me understand why?


texts:
I have an extremely large table IMO (~137 million rows) with lots of repeated data, lots of NULL columns, and such.

I am considering exploring this using a table with a COLUMNSTORE INDEX and I have an IDENTITY column in the original table, which is my only column where every row is unique.

Should I leave this column out or include it?  I have read that you want to include all rows of your table into the COLUMNSTORE INDEX but I have also read that the best candidates are columns with lots of non-unique rows.

Is this just a bad candidate for a COLUMNSTORE INDEX?

I am using SQL Server 2012, so it is a nonclustered columnstore. I am just exploring possible better ways to store this data. Updates are nonexistent, although new rows will periodically be added through an ELT process so I am assuming some work would be done there. Some folks mine this data and generate huge reports, lots of scanning of rows, brings the server to a crawl at times which has forced us to offload a copy daily to a secondary server.


texts: Can a sublass have two parents (two entity types that are connected with it?)

texts:
I'm kind of new to ERDs and stuff, just wondering if I'm correct when my (conceptual) entity relationship diagram has two entity types connected to one subclass (so the subclass inherits data from both of it).

My question here would be: For each entity type connecting to this sublass, does it have its own instance of it? So: Will my room entity type record if its a standard or executive room, and will the booking entity type be able to record if that specific booking was to have decorations or not?

Sorry, I'm struggling to word this question and I'm sure a few will be confused, but I appreciate any help on the matter.


texts: Using `transactions` without `select ... for update`?

texts:
I have a table of ranges that should never intersect:

id | from | to
--------------
 1 |  3   | 5
 2 |  12  | 14
 3 |  20  | 24
 4 |  27  | 30

I have an algorithm that does the following:

Find gaps (gaps in example would be 6-11, 15-19, 25-26,31-null)
Compute random ranges that do not intersect existig ranges from table
Insert these ranges  (for example insert into table (from,to) values (15,17) and insert into table (from,to) values (31,40))

Now if this algorithm happens at the exact same time it would be possible that the range 15-17 and 15-19 would be inserted and that the ranges of my table intersect.

I know that one usually uses transactions with select ... for update in such a situation. However, I am not really selecting any row that I want to update. Instead, I first compute gaps that do not exist in the database. The computation of the gaps is done with this SQL:

SELECT gap_starts, gap_ends  FROM
   (SELECT (t1.to + 1) as gap_starts,
    (SELECT Min(t3.from) - 1 FROM table t3 WHERE t3.from > t1.to) as gap_ends
    FROM table t1
    WHERE NOT EXISTS (SELECT t2.from FROM table t2 WHERE t2.from = t1.to +1 )
   )

How can I use transaction here to make sure that my inserted rows from step 3 are not intersecting?


texts: Show data point values in the legend section

texts:
Is it possible to show the values in the legend section in a chart component of SSRS? I'm using SQLserver 2008.

Something similar to the image below


texts: SQL Server 2012 to 2016 Performance Issue

texts:
I've recently upgraded my SQL Server 2012 to 2016 (Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3  (Build 16299: ) (Hypervisor) ).

Like many people I've experienced performance problems with queries that would  take sub-1 second pre-upgrade now taking 3-4 seconds.

I've looked around and the short term resolution seems to be to switch the DB Compatibility level back to 2012 turn ON the Legacy Cardinality Estimation - however this doesn't seem to make any difference to my queries.

Can anyone advise on what else I need to configure/change to get it back pre-upgrade performance levels whilst we modify the queries themselves to make them performant under the new CE - I've attached the DB properties/options tab below for reference

Thanks

B


texts: Query another schema on a different sid Oracle

texts:
I have 3 db's that should be in sync with each other, but there are some differences that I am trying to rectify.  We try to stay out of the main schema and do all of our work on a separate user schema, the data is then streamed around if updates are made.

I need to be able to query data on a different schema on a different sid to get the tables in sync, but I get an ORA-01017: invalid username/password; logon denied when I do so.

For example when I am logged in as schema2 on sid1:

/* works */
select * from schema1.my_table@sid1

/* throws an error */
select * from schema1.my_table@sid2

Likewise when I am logged in as schema2 on sid2

/* works */
select * from schema1.my_table@sid2

/* throws an error */
select * from schema1.my_table@sid1

Is this type of query possible?  There is already a database link created between the db's.  I use connect through when I log on so I do not know the schema2 password and I am afraid to change it.  Is it that they are different on all three nodes?

I hope this is making sense, please ask for any clarifications.