texts: SSRS error "something went wrong please try again later" in web portal

texts: I have a brand new installation of SQL Server Reporting Services 2019 on top of MSSQL 2019 Enterprise CU1. SSRS has been installed and configured using default settings, apart from the service account it should use. Both AD and MSSQL instance are configured in minimum permissions only (or close to this state). When I am accessing the Web Portal I receive an error saying "please try again later", which isn't much of help. I found many similar posts asking for help on the same issue, but wanted to see the answer here.


texts:
We were reviewing the restore documentation for Cassandra here, and noted it made the following distinction between restoring a single node or an entire cluster:

  If restoring a single node, you must first shutdown the node. If restoring an entire cluster, you must shut down all nodes, restore the snapshot data, and then start all nodes again.

Under what circumstances would restoring a backup of a single node be desirable compared to spinning up a clean node and letting it gather its data the normal way from the other replicas?


texts:
Having a sharded cluster in production which has been running for a good year now without issues. Everything is on 4.0, classic 3-3-3(config) + 2 mongos.

While revisiting our back-up strategy i stumped upon this step.

I tried finding that value on every node (mongos, primaries, secondaries) and none of them have a shardIdentity.

mongos> db.system.version.find()
{ "_id" : "featureCompatibilityVersion", "version" : "4.0" }
{ "_id" : "authSchema", "currentVersion" : 5 }

This thing looks important and is part of STARTUP2 phase.

For the sake of testing and documenting our back-ups, i followed all the steps from the "Restore Sharded Cluster" documentation. At the end of it:

sh.status() is looking good, i'm seeing the shards
rs.status() is also good
A standalone mongod gives me the proper stats on restored data
mongod output seems fine..

But then db.coll.stats() fails with :

{
        "ok" : 0,
        "errmsg" : "Cannot accept sharding commands if sharding state has not been initialized with a shardIdentity document",
        "code" : 203,
        "codeName" : "ShardingStateNotInitialized",
        "operationTime" : Timestamp(1548184216, 1),
        "$clusterTime" : {
                "clusterTime" : Timestamp(1548184216, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}

And i can see this error from mongod:

I CONTROL  [LogicalSessionCacheReap] Sessions collection is not set up; waiting until next sessions reap interval: sharding state is not yet initialized

So far i tried:

sh.enableSharding() returns true (1) but doesn't change anything
Been through https://github.com/mongodb/mongo/wiki/Sharding-Internals#sharding-component-initialization
Double checked on our production cluster and no shardIdentity there.

So, how does one enforce a shardIdentify or generate one? How do you find out the "shard name" because i couldn't find such a thing. And, well, why would sh.status() give the proper result but cannot associate its shards with the current data?

Thanks,


texts: Index for date column along with multiple other columns in where clause

texts:
I have a 'joinings' table which lists the joining date of all the employees along with their office location, department no and the job advertisement through which they were selected. I want to be able to query this table by their joining dates and group them using one of the other columns. I also want to be able to apply a where clause on any of the columns.

It could be like this -

SELECT month(joining_date) as month, count(*) as entries
     , branch_name
FROM joinings
WHERE joining_date >= '2017-01-01'
  AND joining_date <= '2017-05-31'
  AND department_no in (5,4,7)
  AND source_of_appliction IN ('glassdoor', 'linkedin')
GROUP BY month(joining_date), branch_name

OR could be like this -

SELECT year(joining_date) as month, count(*) as entries
     , source_of_application
FROM joinings
WHERE joining_date >= '2017-01-01'
  AND joining_date <= '2019-12-31'
  AND department_no IN (5,4,7)
  AND branch_name IN ('ZEIT','DUS')
GROUP BY year(joining_date), source_of_application

This table can contain thousands of records for several years. The range of the joining date that I would query could be between a single month, range of months or range of years.

I would like to know what indexes need to be created to get me optimized performance for my select queries. If not the exact indexes, I am at least looking out for pointers to get me started to create the right indices.

What I have-
I currently have a multi-column index created on joinings(joining_date, department_no), joinings(joining_date, branch_name), joinings(joining_date, source_of_application) and also have individual indexes for all the single columns. But my select performs a full table scan for the queries that I have listed above.


texts: Implications of using PostgreSQL COPY command to stdout and then immeadiately reading it from stdin

texts:
When transferring data between two databases, I would like to know if there is any disadvantages in the following approach:

psql -h [HOST1] -U postgres -d [DB1] -c [SQL1] | psql -h [HOST2]  -d [DB2] -U postgres -c [SQL2]

where

[SQL1]="\copy (SELECT [FIELDS_SUBSET] FROM [TABLE_NAME]) TO STDOUT"

[SQL2]="\copy [NEW_TABLE_NAME]( [FIELDS_SUBSET] ) FROM STDIN"

Basically, I extract some data from DB1 to stdout and then I immediately read it from stdin in order to import this data into DB2. Both databases are in different networks. This approach removes the need of an intermediate file.

In this context, I would like to know if this strategy has any drawbacks if compared to using intermediate files. For instance, is this approach suitable for transfering large volumes of data (GBs of data)?

Also, by using COPY with STDIN and STDOUT, do I still have to worry about the COPY command caveats? (Reference)


texts: Why does this say there is no join predicate?

texts:
My execution plan is posted on PasteThePlan.com. The second nested loop is warning that there is no join predicate. I'm still new to this so I'm confused. Is that because I am using a CTE to determine the values for two of the attributes?  This presentation table is used in more that a few reports, and I don't want to create a second one for this very unique one.

Here's the query:

WITH Accumulators AS
(
 SELECT DISTINCT AreaNum, SuperintendentNum, SuperName
   FROM FI.SLAnalysis
  WHERE SuperName LIKE '%ACCUM%'
)
SELECT
    slard.[IO]
  , slard.PhaseName             AS PhaseName
  , slard.JobNum              AS JobNum
--  , slard.JobName              AS JobName
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperintendentNum
   ELSE slard.SuperNum
     END                AS SuperNum
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperName
   ELSE slard.SuperName
     END                AS SuperName
  , slard.AreaNum              AS AreaNum
  , slard.AreaName             AS AreaName
  , fp.WeekEndDate             AS WeekEndDate

  -- WTD Metrics
  , slard.WeeklyRevenue            AS WTDRevenue
  , slard.WeeklyCost             AS WTDCost
  , slard.WeeklyRevenue - slard.WeeklyCost       AS WTDGP
  , CASE
   WHEN slard.WeeklyRevenue = .01 THEN 0
   ELSE slard.WeeklyRevenue
     END                AS WTDRatio
  , slard.Weekly_PER             AS WTDGPPer
  , slard.WeeklyStandOvrheadAdmin          AS WTDOverAdmin
  , slard.WeeklyStandOvrheadEquip          AS WTDOverEquip
  , (slard.WeeklyRevenue
   -slard.WeeklyCost
   -slard.WeeklyStandOvrheadAdmin
   -slard.WeeklyStandOvrheadEquip)         AS WTDOHGP

  --MTD Metrics
  , slard.MTDRevenue             AS MTDRevenue
  , slard.MTDCost              AS MTDCost
  , slard.MTDRevenue - MTDCost          AS MTD_GP
  , CASE
   WHEN slard.MTDRevenue <= .05 AND slard.MTDRevenue > 0 THEN 0
   ELSE slard.MTDRevenue
     END                AS MTDRatio
  , CASE
   WHEN slard.MTDRevenue <> 0
    THEN CAST(slard.MTDCost / slard.MTDRevenue AS DECIMAL(18, 2))
   ELSE CAST(0.00 AS DECIMAL(18, 2))
     END                AS MTDGPPer
  , slard.MonthlyStandOvrheadAdmin         AS MTDOverAdmin
  , slard.MonthlyStandOvrheadEquip         AS MTDOverEquip
  , (slard.MTDRevenue
   -slard.MTDCost
   -slard.MonthlyStandOvrheadAdmin
   -slard.MonthlyStandOvrheadEquip)        AS MTDOHGP
  -- YTD Metrics
  , slard.YTDRevenue             AS YTDRevenue
  , slard.YTDCost              AS YTDCost
  , slard.YTDRevenue - YTDCost          AS YTD_GP
  , CASE
   WHEN slard.YTDRevenue <= .53 AND slard.YTDRevenue > 0 THEN 0
   ELSE slard.YTDRevenue
     END                 AS YTDRatio
  , slard.YTDStandOvrheadAdmin          AS YTDOverAdmin
  , slard.YTDStandOvrheadEquip          AS YTDOverEquip
  , (slard.YTDRevenue
   -slard.YTDCost
   -slard.YTDStandOvrheadAdmin
   -slard.YTDStandOvrheadEquip)         AS YTDOHGP
  FROM [DW].[FI].[SLAnalysisReportData] slard
 INNER JOIN Accumulators ac
   ON slard.AreaNum = ac.AreaNum
 INNER JOIN CORP.FiscalPeriod fp
  ON slard.FY = fp.FY
   AND slard.[Period] = fp.[Period]
WHERE slard.FY = 2018
 AND slard.[Period]  = 3
    AND slard.AreaNum IN ('40')
   AND NOT (    WeeklyRevenue = 0
            AND WeeklyCost = 0
            AND WeeklyStandOvrheadAdmin = 0
            AND WeeklyStandOvrheadEquip = 0
            AND MTDRevenue = 0
            AND MTDCost = 0
            AND MonthlyStandOvrheadAdmin = 0
            AND MonthlyStandOvrheadEquip = 0
            AND YTDRevenue = 0
            AND YTDCost = 0
            AND YTDStandOvrheadAdmin = 0
            AND YTDStandOvrheadEquip = 0
           )


texts: Problem getting zips in radius via MySQL

texts:
I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point.
It just occurred to me that, just because a zip's center point is not within a given radius, does not mean that the zip itself is not within the radius.
I used my super advanced art skills to illustrate the point here:

The green stripy blobs represent zip codes A, B and C.

The red smudges are the geographic centers for each zip code

The fuchsia dot is the target location, and..

The lumpy blue circle is a 1 mile radius from the target location

If I run a query for all the zip codes within a 1 mile radius from the pink smudge, only zip codes B and C will be returned as the center point for zip A is not within the one mile radius, even though the pink smudge itself is clearly in zip code A.
SELECT *,
        p.distance_unit
                 * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                 * COS(RADIANS(z.y))
                 * COS(RADIANS(p.longpoint) - RADIANS(z.x))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(z.y)))) AS dist
  FROM standard_zip AS z
  JOIN (   /* these are the query parameters */
        SELECT  $lat  AS latpoint,  $lng AS longpoint,
                $miles AS radius,      69 AS distance_unit
    ) AS p ON 1=1
  WHERE z.y
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND z.x
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
  ORDER BY dist

How the heck do I write a query that will include zip A in the results?
I have access to spatial/geometry for each zip code that I can add to the table if needed, but I have no idea how I would use it for this purpose in MySQL.

Edit: I spent a day reading the Oracle and MySQL docs for spatial data and managed to successfully convert my spatial data to MySQL. How do I go about writing a similar query that uses the geometry column instead of the lat and long? I am using 2D data.. the geometry are polygons and multipolygons only..
I think I sort of figured it out..
select
  *
from
  (
    select
      MIN(st_distance(geom, POINT(-82.765136, 28.0914015))) * 69 as miles,
      zip
    from
      zip_spatial
    group by
      zip
    order by
      miles asc
  ) d
where
  d.miles < 5

I'll leave the bounty open for now in case someone has a better, more efficient solution.


texts:
We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to store the data.

Basically we have dataloggers which send data back to the server every 5 minutes. With current system we only have few sensors as we've just been starting so we only have approx 250 loggers out there and they are generating data at a pace of 2.5gb per year or about 21M rows per year. The data is mainly just numbers like light levels, temperature, humidity etc. We expect these logger numbers to grow quite fast and we should have thousands of loggers by end of the next year.

However with this next generation platform we'll have multiple sensors that can be used and multiple similar sensors can be connected to one system. System will have about 8 ports for sensor boards but each sensor board can have multiple sensors. As an rough example one system can have anywhere from 5 to 100 different kind of sensor inputs. ( 2-10 times as much as we currently have)

Data is only written once and then only read. 99% of the reads are to data which is maximum of two months old.

So far I've come up with few different options and I'd love to hear if someone has good insights which one might be the most scalable.

Have a main Ping table which contains some basic data for each report and then have separate tables for each sensor board types. If all of the data needs to be shown the select would be at most 8 table join.
Have one very wide table with all the ping data
Fully or partly use NoSQL ideas and insert most of the ping data in JSON or similar format.

Secondly it would be interesting to hear as to what database setup would be the most scalable:

Have a production server which has maximum of 2 months data so it would be easier to keep fully in memory. Offload all historical data to secondary server.
Have everything in one server.
Sharding?
Replication?


texts:
I have read that table index compression happens just in MyISAM, but I came across
an article in dev.mysql that said: InnoDB supports table and index compression!
So now what is the pros of MyISAM?
Is there any reason left to use MyISAM? (except full-text search)


texts:
I have two tables. Each table has a single column with 2 records. How would I go about merging the columns from both tables into another?

For instance, the first table has records 1 and 2, and the other 4,5. I would like the new table to look like the following.


texts: How to change default port for T-SQL Debugger

texts:
The default port for T-SQL Debugger is 135, it is possible to change this port ? In my company this port is on special list of banned ports, therefore we need to open that service on another port number.

I am following this article:

Transact-SQL Debugger for SQL Server 2008 – Part 2 by Bill Ramos


texts: Mysql Database Design - identifying required patterns

texts:
I'm trying to figure out which pattern (if any) might describe the problem I'm facing when designing the database for a club membership management system. I require help in identifying the pattern (if one exists) or some pointers of what considerations I need to make in order to make some headway.
Members are a main entity, with some information such as contact details on them.
Members can have central functions at the club, ie be the treasurer or commodore etc.
In order to model the club's top level hierarchy, I would have the ID from Members, an enumeration table "BoardMemberTypes" and a "BoardMembersType" table linking the two.
However, the Club also has sub-groups, each with their own hierarchy structure.
Every Member is part of at least one of the Subgroups, with the subgroup Role "member", and the membership type such as "active" or "passive".
Each of these structures have a few identical and a few specific roles. As an example:

Subgroup A has chair, vice chair, treasurer from the identical set, and "Subgroup A Technology Chair" from a list only applicable to Subgroup A
Subgroup B has a chair, a treasurer, and a "Specific Subgroup B 1" and "Specific Subgroup B 2" role
Subgoup C might have only a chair and a "Specific Subgroup C" role.

The Subgroups are the complicated bit:
Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)

First hurdle: When presenting the admin interface, I'd need to constrain MembershipFunction depending on the subgroup.
I guess this could be achieved through a link table:
 SubgroupMembershipFunctions (Id, SubgroupId, MembershipFunctionId)
 e.g. (Name output after applying joins obviously)

1    Subgroup A    Member
2    Subgroup A    Chairman
3    Subgroup A    Subgroup A Technology Chair
4    Subgroup B    Member
5    Subgroup B    Chairman
6    Subgroup B    Specific Subgroup B 1

In theory, I could now create a table "MembersSubgroupMembershipFunctions" with MembersID, SubgroupMembershipFunctionsId
At this point, I'm still lacking the abilty to store the MembershipType (active, passive) which only really applies to the "member" entry.
The end result I need would be something like this maybe
User          Subgroup    Type        Status
Justin Case   A           member      active
Justin Case   A           chairman    null(?)
Justin Case   B           member      passive
Justin Case   B           B 1         null(?)
Joe Bloggs    A           member      active
Jane Doe      B           member      active
Jane Doe      C           member      passive
Jane Doe      C           vicechair   null(?)

Any help, pointer or idea towards improving this design is greatly appreciated.


texts: Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

texts:
I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it manually).

Here is what I am seeing...

but on the table itself...

Could this be why it's not happening automatically?


texts: When doing a full-table scan, why does Oracle scan all the rows beneath the high-water mark? Doesn't it know that some of the rows have been deleted and that they shouldn't be scanned?


texts: Can't remote access MySQL server running on Mac OS X

texts:
I have a MySQL server running on my work computer, which runs Mac OS X Maverick. I want to access it from home computer, which also has Mac OS Maverick.

From terminal, if in 1 tab, I ssh into my work computer and just let the ssh session sit there without being idle, then on another tab, I can access MySQL server.

However, without the ssh session running, I will get following error

  ERROR 2003 (HY000): Can't connect to MySQL server on 'SERVER_IP_ADDRESS' (60)

I've tried to modify my.cnf file on the server by commenting out bind-address or assigning it to 0.0.0.0 but it doesn't work.

Why is it like that? How can I configure the server so that it allows remote access without having to run ssh on the client? Can someone please help?


texts:
I am learning PL/SQL programming. I'm trying to create a view with the following statement:

create or replace view viewName as select ID from employees;

The view creation throws the following error:

ORA-00604 error occurred at recursive level 1
ORA-08102 Index not found, obj #39 , file 1 block 61248

How can I fix it?

The problem is with all objects, procedures,functions,views and i am using the HR Database of Oracle 11G for learning purposes.


texts: Increase frequency of updates (ETL process) to Data Warehouse

texts:
Our data warehouse is updated only once every 24 hours (at night). The ETL process takes a little over an hour to complete. I am wondering how to increase the frequency of the ETL process - so running it at noon in addition to running it at midnight - without disrupting any connections to the data warehouse.

In my mind I see a way to have TWO databases with the same schema... and so whenever an update occurs, anything needing to read into the data warehouse will be pointed at the NON updating db... but as soon as the ETL finishes something essentially "switches" out the two db's so that now anything trying to read will be reading from the newly updated database.

Is this possible?

BTW -> I HAVE thought about optimizing the ETL and just running it at noon - creating a (hopefully smaller) window of time where the DB is "not available" ... but I'd rather avoid this anyway. I want no disruption at all to the availability of the data.

MS Sql Server 2016


texts: Mysql: Create a view with multiple self joins without duplicates in result

texts:
Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you.

So i have a table entities_attributes_values where an entity has a lot of attributes and the value of that attribute, basically imagine 3 fields:

entity_id
entity_attributes_id
value

Because every entities attribute and its value is on row getting more values is not so easy i was thinking of multiple self joins, and because this query will be very common i created a view, which is built with this query:

SELECT `L1`.`entity_id`,
       `L1`.`value` as 'company_id',
       `L2`.`value` as 'entity_name',
       `P`.`value` as 'person_name',
       `L4`.`value` as 'establishment_id',
       `L5`.`value` as 'department_id'
FROM `entities_attributes_values` `L1`
LEFT JOIN `entities_attributes_values` `L2` ON `L1`.`entity_id` = `L2`.`entity_id` AND `L2`.`entity_attributes_id` = 1
LEFT JOIN `entities_attributes_values` `L3` ON `L1`.`entity_id` = `L3`.`entity_id` AND `L3`.`entity_attributes_id` = 3
LEFT JOIN `persons_attributes_values` `P` ON `L3`.`value` = `P`.`core_persons_id` AND `P`.`core_persons_attributes_id` = 4
LEFT JOIN `entities_attributes_values` `L4` ON `L1`.`entity_id` = `L4`.`entity_id` AND `L4`.`entity_attributes_id` = 12
LEFT JOIN `entities_attributes_values` `L5` ON `L1`.`entity_id` = `L5`.`entity_id` AND `L5`.`entity_attributes_id` = 13
WHERE `L1`.`entity_attributes_id` = 2

So this works but i have one problem i get "duplicate" values and its not really duplicate but the point is that in my view i want every entity to be only one row with all its attributes values but instead i get this:

So as you can see the first three result are not good for me, i only need the fourth one, where i have all my data about one entity.

Thank you in advance for any help!


texts: How to use a foreach loop with project deployment model

texts: I have a SSIS project that is configured to project deployment model, within this I have two connections one for a flat file and one for a sql db. Both connections are in project mode. Now I want to use a foreach loop container to go through a folder of files to import them into the sql db. Within the for each loop container I can only create a variable on the user level for the file names. Using this variable in an expression for the file connection is not possible since the connection requires a project variable. How can the foreach loop container be used in a project deployment model ?


texts: SQL query to return result with different row

texts:
I am working with a database that has a table called 'order' that looks like the following.

id    day    item
-------------------
11     1     apple
11     3     banana
11     4     berry
22     1     coke
22     3     pepsi
33     2     chips
33     4     salsa

I want mysql query output to look like the following:

id    1       2       3       4
------------------------------------
11    apple   null    banana   berry
22    coke    null    pepsi    null
33    null    chips   null     salsa

Is it possible to get such an output using mysql?


texts: which database engine is best for full text search on a 10M rows table?

texts:
I'm creating a search engine and needs to search keywords in a text field (about 300 words in each text field, unstructured).

I'm thinking about noSQL\MySQL+Sphinx, the performance impact here is crucial.

which DB engine should I use?


texts: SQL Count function returning inaccurate results

texts:
I am trying to write a query in oracle to list the number of records contained in a single table filtered by multiple columns in the tablespace.  Here is the query syntax I have come up with:

SELECT COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL
         END) AS USERS1
   ,COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL
          END) AS USERS2
   ,COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL
          END) AS USERS3
   ,COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL
          END) AS USERS4
   ,COUNT(*) AS USERS5
FROM table_USERS
WHERE is_terminated = 'False'

Here is the resulting output:

users1=1192
users2=38
users3=25
users4=240
users5=1548

The result is not correct it is showing "users5" as the total number of users that exist in the database (1548), when it should show me only 53 users which don't match any of the user categories (users1 - users4).

What am I missing and is there a more simplistic way to write this query?


texts: How can I stop continuous failed logins on SQL Server

texts:
I have a couple SQL Server Express databases running on Windows Server 2008 R2 on a VPS.

A couple of websites access them (these are local connections, right?). Website connections use integrated security.

I also connect using Remote Desktop. SQL Server Express is accessible remotely through 1 port (same as Remote Desktop)

Problem: logs are showing failed login attempts every 5-10 seconds. They will be from 1 IP for a few minutes, then from another IP. They usually try user sa (I don't have one) or a variation. I have blocked several hundred of these using Windows Firewall, but they keep coming.

So - what else should I be doing? Is this something I should be worrying about?


texts:
I have a simple query, It might be answered before but I didn't find the precise answer for the same.

My question is:

I created a table using JPA through entity. Now I shows string fields as varchar(255). Now I'm wondering that what will happen when I'll save some data in that field of size let say 10? Will it save that data in 10 bytes or it will use 255 bytes?

Any clarification will be appreciated.


texts: Accessing Memory Optimised Tables Under Snapshot Isolation

texts:
We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains.
Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view unioning both together.
Create Table Demo_Cold
(
    [ID] INT NOT NULL PRIMARY KEY,
    [Description] VARCHAR(MAX) NOT NULL
);
GO

Create Table Demo_Hot
(
    [ID] INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
    [Description] VARCHAR(MAX) NOT NULL
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA );
GO

CREATE VIEW Demo
AS
Select *
From Demo_Hot
UNION ALL
Select *
From Demo_Cold;
GO

When I attempt to query this (either directly or view the view) everything is fine:
Select * From Demo

But when I try to set the isolation level to snapshot, I get an error instead:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Select * From Demo

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

We use snapshot isolation in this database at the moment to allow unrelated parallel commits, and fail on concurrent commits on the same data. According to the MS Documentation, Snapshot isolation is supported by IM-OLTP.
I've even tried setting the escalation setting on to allow this:
ALTER DATABASE CURRENT
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

But it doesn't change the error.
If I set the isolation level to REPEATABLE READ, I get another error:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Select * From Demo

The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

The same for accessing just the memory-optimised table:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Select * From Demo_Hot

Am I missing a key step required? Ultimately I'd like to be able to continue using Snapshot isolation from our code (to minimise any other changes).


texts: Postgres 9.5 deadlock with many updates on a table + many "select for update" on another table

texts:
I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key.
I face a deadlock problem while executing many of these requests:
//this will produce an "AccessExclusiveLock" of type "tuple" on machines
SELECT FROM * machines FOR UPDATE where id = :id;
//this will produce a "RowExclusiveLock" on magictools and a "RowShareLock" on machines
UPDATE magictools SET collections = "large-json" where id = :id

As far I understood running many of these request produces deadlocks. Maybe it's only the updates who are performing this, I don't know.
How should I avoid the deadlocks in this case?
I have many indexes on these tables, maybe I have too many indexes?
Below is the report of pg_activity when the problem happened. I do not understand the different modes and lock types, and, just, what happens here?
Is it possible that just an update without any transaction causes a deadlock?
deadlock trace link


texts: SQL/Access: Iterate through a query's results to use in another's WHERE clause

texts:
Just to preface, I'm pretty terrible with databases in general (patience appreciated).

I have a table structured as follows:

    TEAM   |PERSON |GAME |SCORE
    _______|_______|_____|_____|
    Apples |bob    |A    |30
    Apples |bob    |B    |15
    Apples |chris  |A    |13
    Apples |chris  |B    |2
    Oranges|sally  |A    |15
    Oranges|sally  |B    |39
    Oranges|tina   |A    |19
    Oranges|tina   |B    |27
    Bananas|larry  |A    |34
    Bananas|larry  |B    |30
    Bananas|harry  |A    |33
    Banasas|harry  |B    |13
    Bananas|charles|A    |32
    Bananas|charles|B    |28

I would like to generate output that shows who scored the highest in each game and what team they are from.

Example:

    GAME  |TEAM   |PERSON  |SCORE
    ______|_______|________|_____|
    A     |bananas|larry   |34
    B     |oranges|sally   |39

Here is what I have so for:

I can grab the list of games via:

SELECT DISTINCT(GAME) from table;

And I can get the highest scorer from a specific game via:

SELECT TOP 1 GAME,TEAM,PERSON,SCORE from table WHERE GAME='A' ORDER BY SCORE DESC;

The part I am having trouble with using the results of the DISTINCT() query as part of the WHERE clause in my second query.  Data is dynamic in my simple database.

Hopefully this can be solved with some SQL kung fu but:
If there is a better way to structure my database, I'm definitely open to those suggestions (right now, it's a single table database, data is ingested/overwritten daily, and dataset size is not expected to grow to problematic sizes, and I don't really know what I'm doing)

Thanks!


texts: What does 'exclude rows that do not contain values' checkbox do in SQL Profiler?

texts:
I want to filter a trace and wonder what does  'exclude rows that do not contain values' checkbox exactly do? Is it to filter all the data columns that are not selected in any event class?

Thanks.


texts: Reduce query execution time in frequently updated tables

texts:
The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets executed 7,00,000 times in a single day with updated data. A query is already optimized and indexed. Table approximately has 300,00,000 rows. Is there any way to reduce the query time?
The table works on the InnoDB engine.

I was planning for few methods please correct me if I am wrong

Memcache the rows using PHP
Use replication Master/Slave architecture in slave have MyISAM (Doubt about the engine in this architecture)
Use MySQL caching mechanism

(a)  query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
(b) buffer_pool
Can both (a) and (b) perform together? Will it improve the performance for such huge data?

Is there some other technique for improving query execution time?


texts: Migrating 300GB database to Azure SQL Server

texts:
We are having a devil of a time trying to migrate our current on-premises SQL Server to an Azure SQL Database. It is large but not that large (about 300 GB). We've tried the "Publish to Azure" option from SQL Server and get errors and exceptions (out of memory exceptions). The Data Migration Assistant seems to start working OK, but it fails after 12 hours or so having completed only about 15% of the migration. We can't be down for 12 hours, let alone the time it would seem to take in order to move the complete 300 GB database.

What are some strategies and best practices that we could try for the data migration that would minimize downtime?


texts: How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

texts:
We have a situation where Developers do not have any UPDATE permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).

Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1 (that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.

DBA can not change password for SQLLogin1 without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1 is maintained by Developer.

Question:

Is there a way to deny access to SQLLogin1 SQL login, but only if it is connecting over SSMS?

At the same time if SQLLogin1 is connecting over .Net SqlClient Data Provider (program_name in the sys.dm_exec_sessions), it must be allowed to login.

This way we want to not let Developer connect over SSMS using SQLLogin1, while the application that is using SQLLogin1, would still be able to connect.


texts: Sharding Mongodb while limiting the number of documents to be migrated

texts:
I have little experience working with databases, but at my work I was tasked to deploy a sharded Mongodb cluster and the constrain on the shard is:

database with multiple collections, 7 collections will live on primary shard, 25 will be sharded evenly and one ( that has over 2.5 billion documents, 2.3Tb) will have 2Tb on primary and 0.3Tb divided withing the other clusters.
I have been reading the doc's from Mongodb website, tutorials on the internet and posted questions on different forums with no luck so far.

I understand that if you don't enable shard on the collection, it will live on only one collection. this will take care on the collections that will stay on the primary server.

Also, adding shard on the collection, will make the collection to shard evenly (or close to it), and that takes care of the 25 collections that can live on any shard.

Now for the last one I was thinking of a compound key using the id field (which is a unique field) together with "in_Use" field which is a boolean to divide the collection as in B=true stays on the primary shard and MOST, but not all, B=false goes tho the other shards.

A second problem with this collection is that the B=true values are changed to false after 24 hours and new documents with the value true are created, so they would needed to be swapped everyday. This solution I couldn't find on any documentation or tutorial, so I am not sure if it is doable.

Thirdly, since we have a size restriction on all shards except the primary one, we will have to restrict the size of the shards, according to the documentation, this can be accomplished by adding "maxSize" option to the addShard command, but i believe that would be set to all collections for the database and it might create a problem if more space are added dynamically. The shard might not increase it's "maxSize" dynamically.

Any ideas or suggestions are welcomed.


texts: MySQL Conditional Counter Based Other Columns?

texts:
I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is returned. I need the SELECT statement to calculate a column that means "This is the customer's Nth purchase for the day." The data is sorted by customer name and date already, so when the customer name or date changes, I want the counter variable to reset to 1.

 ----------------------------------------------------------------
| Customer Name | Product | PurchaseDate | PurchaseNumberForDate |
 ----------------------------------------------------------------
| Customer A    | ...     | 2019-04-01   | 1                     |
| Customer A    | ...     | 2019-04-02   | 1                     |
| Customer A    | ...     | 2019-04-03   | 1                     |
| Customer A    | ...     | 2019-04-03   | 2                     |
| Customer A    | ...     | 2019-04-03   | 3                     |
| Customer B    | ...     | 2019-04-03   | 1                     |
| Customer B    | ...     | 2019-04-03   | 2                     |
| Customer B    | ...     | 2019-04-04   | 1                     |
 ----------------------------------------------------------------

I have tried using MySQL variables, but I cannot figure out how to reset the counter conditionally when the customer name or purchase date change. If I could get the PurchaseNumberForDate correctly calculated, I will use this as a subquery with another query  that will select WHERE PurchaseNumberForDate = 1.

I have found plenty of examples using COUNT() and @var := @var+1, but I haven't found one based on multiple conditions. Is this possible with MySQL?


texts: using view (necessary) with where conditions (dynamically)

texts:
Suppose we have two tables like A {a1, a2, a3 ...} and B{b1, b2, b3 ...}

We are using a MySQl view like

Select a.a1, b.b1 from A a Left JOIN  B b ON a.a2=b.b2

Now our requirement is that we have to put "where conditions" dynamically on some columns of A and B, so we have to again join table and putting a where condition (In current code).

Now as joining twice is increasing our query execution time and I am trying to reduce the time.

We can't remove the view as per the security requirement (SQL SECURITY).

Is there a way to handle this scenario, I thought of using functions with "SQL SECURITY" or there are any other alternatives.


texts:
I have

select * from generale where titolo  ~* 'hp-ux' order by titolo;
      autori       |                                                   titolo                                                    |        casaeditrice
-------------------+-------------------------------------------------------------------------------------------------------------+----------------------------
 Asghar Ghori      | HP Certified Systems Administrator: Hp-ux 11i V3: Exam Hp0-a01: Training Guide and Adminstrator's Reference | Endeavor Technologies Inc.
 Autori vari       | HP-UX tuning and performance                                                                                | Prentice Hall

The line title is very long for Book Entry Hp Certified...etc,etc
I want to truncate to this

 Asghar Ghori      | HP Certified Systems Administrator:....

The ... are not important i want to truncate column output to max 20 characters,
on oracle i use column titolo format a20 how to do this on postgres?
Thanks


texts:
We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was wondering, is there a way to exclude only procedure from the dump?

For tables there is an "ignore-table" option in MySQL. Is there something similar or a workaround to do this for one procedures in particular?


texts: Oracle SQL Developer 4: how to tell under which user I connected

texts:
I'm using Oracle SQL Developer 4 to connect to multiple databases at the same time, sometimes using different credentials that those saved in connection properties. So I have multiple tabs in main window labeled with DB names, but I'd like also have indication of under which account I'm connected (seems natural to have it in tab label/name).
Any option setting to achieve this? Probable I'm looking in wrong place or can do some query to check currently using credentials?


texts: How to trigger newlines in log messages?

texts:
Is it possible for log messages to span multiple lines? I am expecting:

RAISE WARNING 'Line 1\nLine 2';

to result in the following log:

Line 1
Line 2

But instead I get:

Line 1\nLine 2

Any ideas?


texts: SQL Server shrink command 'estimated_completion_time' keeps going up

texts:
Running SQL 2012 R2...

Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)  Standard Edition (64-bit)

Ran the shrink command

DBCC SHRINKDATABASE(databaseNameHere)

(Yes, I know this shouldn't be done regularly, if ever... we have a 200 GB database and cleared out many years worth of data, and should be able to reclaim about 100 GB of space)

When I checked the status of the task at 1.5 hours, it was 49.1xxx percent_complete.

It's been running for 2.5 hours... and now at 49.5xxx percent_complete.

Additionally, just in the last 20 minutes, the estimated_completion_time (found in sys.dm_exec_requests) has gone from 8,741,035 milliseconds to 9,385,086 milliseconds...

There is still a ton of space available on the drive.  It is a development/test database that nobody is using... so whats the deal? why does the estimated time keep increasing?

I've been using sp_who2 active to verify there are no blocks...


texts: Upgrading Oracle ODBC driver from 11 to 12 causing code to fail

texts:
In the process of replacing our servers which involves updating the Oracle ODBC drivers from 11 to 12. We have a process that executes on these servers that calls a stored procedure. The procedure gets passed a number of items to return one result.

For some reason, when the code that runs fine on a server with the version 11 ODBC drivers is executed on a server running version 12 it produces this error

[Oracle][ODBC]Function sequence error." error code S1010.

The configuration settings for the DSN between the two servers were copied as closely as possible (there are 3 or so new options in  version 12). The database being accessed still resided on an Oracle 11 server. Other databases residing on Oracle 12 servers require the version 12 ODBC drivers. Are there subtle differences in how a stored procedure is called or executed that my need to be addressed?

I cannot post the code details for company policy reasons.


texts: Why is MongoDB user with the only privilege as "find" able to insert and update collection?

texts:
I want my user to have only find action privilege on a specific collection. However, when I create the user with the privilege, restart the mongod server and run the mongo shell again with -u -p and --authenticationDatabase parameters and run insert command on the collection, it successfully inserts the document in collection. Similarly, update command also works seamlessly for this user and the corresponding collection.

Here's the code for creating the user-

use testauth
db.createUser({user: "user", pwd: "password", roles: [{role: "readWrite", db: "testauth", privileges: [{resource: {db: "testauth", collection: "readonlycol"}, actions: ["find"]}]}]})

Followed by this, I restart the mongod server with --auth and run the mongo shell with authentication parameters.
When I go ahead and try to insert a document in the collection,  it works -

> use testauth
> db.readonlycol.insert({"key": "value"})
WriteResult({ "nInserted" : 1 })

update also seems to work. The only command which doesn't work is remove. What am I doing wrong?


texts:
I got an Oracle 10g server with a bash script running every night which extract each datafile and create an rman script, which is launched from the bash script itself and make backups.

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/archivelogs/autobackup_control_file%F';
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
COPY LEVEL 0
  DATAFILE 1 TO '/opt/oracle/backup/datafile1.dbf'
, DATAFILE 2 TO '/opt/oracle/backup/datafile2.dbf'
...
, DATAFILE N TO '/opt/oracle/backup/datafileN.dbf'
;
SQL 'alter system archive log current';
BACKUP FORMAT '/opt/oracle/archivelogs/archive_logs_%d_%s-%t.bck' ARCHIVELOG ALL DELETE INPUT;
BACKUP CURRENT CONTROLFILE FORMAT '/opt/oracle/archivelogs/controlfile_%d_%s-%t.bck';
RELEASE CHANNEL RMAN_BACK_CH01;
}
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME='sysdate-6';

As you can see from the script the backups are made via datafile copy and archivelogs backup.
I got two questions:

on the archivelogs directory the script creates two controlfile backups (autobackup_control_filec-* and controlfile_SID_) which have quite the same size (6.1MB vs 6.2MB) but not exactly the same.
controlfile_SID_ file creation is clear from the syntax, but when the autobackup_control_filec-* is created? Perhaps during BACKUP ARCHIVELOG ALL command?
And why this different size? If they are both controlfile backup I expect they have the same size.
Datafile backup rotation seems to work fine, every day I found in /opt/oracle/backup only the backup files from the latest backup schedule, but in logs I don't find any trace of old backup deletion or messages on overwrite.
I suppose COPY LEVEL 0 command overwrite without any warning, is it right?


texts: Upgrade BI server to 2014 and stay on 2008r2 with the database engine server

texts:
We would be happy to upgrade our SQL Server environments to 2014, but we have a tight budget for next year.

Our BI server (SQL Server 2008 Standard Edition, that is used only for BI purposes) is located separately from the Production Database server (SQL Server 2008 Enterprise Edition). The BI server contains our SSIS and SSRS packages and reports.

I was wandering whether it's possible to have a configuration where the BI server will have SQL Server 2014 (Standard of Business Intelligence) but the database server itself will remain on 2008.

Thanks for your answers,

Roni.


texts: Migrating system and user databases to a new server by moving the LUN

texts:
I have to replace a server. The system and user databases all reside on the E: drive which is a SAN LUN. The method they want to employ is to remove the LUN from the current server and present it to the new server.

My concerns are that SQL Server is installed on the C: drive which is local storage and will not move with the LUN. I will need to install SQL Server on the new server, and when I do the system databases will be created on the new server.

My questions are:

I know the user databases will need to be detached before moving the LUN, but what about detaching master, model, and msdb?
Assuming the system databases including master can be detached, after the LUN is associated with the new server, how would you handle the master, model, and msdb files on E: with the LUN?

My thoughts were maybe choosing to install the system dbs on C on the new server and then after the LUN moves associate SQL Server with the location of the system databases on E and delete the ones on C.

I also thought about restoring the master db from a backup. How would you handle this?

This is SQL Server 2008 R2 SP2 10.52.4000 Enterprise non clustered.


texts: SQL Server performance is slow when migrated from SQL Server 2012 to SQL Server 2014

texts:
We are trying to migrate from SQL Server 2012 to SQL Server 2014 on Windows 2012 R2 OS.

After the upgrade, we are doing performance testing and observed huge difference in running a simple query from only one table (No joins at all).

The query is getting executed in 4 seconds in SQL Server 2012, but taking more than 1 minute in SQL Server 2014.

The SQL Servers are installed on 2 different VMs (different IP addresses).
They have only one SQL Server instance on each machine.

The processor / ram / hard drive space is same between 2 machines.

The machines have the same applications installed. We are running the same select query from the SQL Server Management Studio directly.


texts:
I am trying to get data from table based on parameters,
Declare @Loadtype varchar(10) = 'A'

Select *
from TableA (if  @Loadtype = 'A')

Select *
from TableB (if  @Loadtype = 'B')

Not sure how I can achieve this.
Many Thanks,
Uday


texts: Modelling two dependent many-to-many relationships

texts:
I have a user who may have 0 or more foo's and bar's. Here is a diagram:

Each foo has a unique integer ord.

If a user has a bar, then this implies that he has a foo with ord=1. A user may not have foo with ord=1 without a bar.

My thought how to avoid logical inconsistencies is to simple never insert any foo_user relationship with ord=1. However when I want to query the foo of a user with smallest ord I need to do this a bit complicated like this:

Query: Check if bar relationship exists. If it does, get foo with ord=1

Query: If no bar bar relationship exists, get foo with smallest ord column.

Is there maybe a more convenient database structure for this scenario?


texts: Join a table to itself, each row needs to join to the ordered last record

texts:
I have a logging table which for whatever reason logs the column and table that changed, the date it changed and also the value it was changed FROM (the old value). I want to join these records together so that I can see what the record changed TO. (The very newest value is obviously not going to appear as that is never logged). Each record has a unique identifier also.

so I have

| UID | historyFor | DateUpdated | Table      | Column    | oldValue |
| 1   | uid=123    | 01/01/2019  | Person     | firstname | Joe      |
| 2   | uid=123    | 02/01/2019  | Person     | firstname | Bill     |
| 3   | uid=123    | 03/01/2019  | Person     | firstname | Elliot   |

I want to add a column so that it would be

| UID | historyFor | DateUpdated | Table      | Column    | oldValue | nextRecord |
| 1   | uid=123    | 01/01/2019  | Person     | firstname | Joe      | Bill |
| 2   | uid=123    | 02/01/2019  | Person     | firstname | Bill     | Elliot     |
| 3   | uid=123    | 03/01/2019  | Person     | firstname | Elliot   | <blank>    |

nextValue should contain the oldValue from the next record when sorted ascending on DateUpdated. Note there are all different records in here so it has to join on historyFor and find the next most recent oldValue and put it in nextRecord. Is there a way to do this? I'm thinking a top 1 greater than DateUpdated will come into play but I'm just not sure how this is going to work.


texts:
I've got this query that builds my drop login statements.  As part two I want to execute this output.  Ideally I want to do this in a sql job with two steps (generate and execute).  Can this be done?

SELECT 'DROP LOGIN [' + name + ']'
FROM [master].[dbo].[syslogins]
WHERE isntgroup = 0
  AND isntuser = 0
  AND sysadmin = 0
  AND name != 'sa'
  AND name NOT LIKE '##%'
ORDER BY name


texts: Access ('07 -'10) form is not showing entered records

texts:
I have a form that I enter data into that is broken into a number of different tables for normalization.

However, once I reopen the form, only the first record is shown.
I have it set as "single form" and Data Entry is set to "no".
I've tried some macros to open the form to the last record but those don't seem to work.


texts: Mysql trigger not working, syntax issues

texts:
I have this trigger

CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN
    SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y;
END;

mysql 5.1.72-0ubuntu0.10.04.1 (Ubuntu)

But I am getting a syntax error, and I don't see where...

EDIT:

DELIMITER //
CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END//
DELIMITER ;

this still got a syntax error

EDIT2:

I think I need the old syntax from here:
https://dev.mysql.com/doc/refman/5.0/en/commit.html

I don't know how to complete the code though...

START TRANSACTION;
SELECT p.* FROM players p WHERE EXISTS (SELECT w.* FROM walls w WHERE w.x=p.x AND w.y=p.y);
COMMIT;
ROLLBACK;


texts: regularly calculating data and then loading to production database

texts:
I have a Postgres database running a small website.  Most of the tables in the database are calculated values derived from various input data.  This input data is updated approximately monthly.  The tables in question are not otherwise altered.

What I need to do each month is:

acquire the new input data (mostly downloading from a few sources)
use the new data to compute new calculated values for the db tables.  Only a smallish fraction (< 5%) of the records will actually change with each update
replace the existing tables in my production db with the newly-calculated tables

Although it would be convenient, I don't feel it is prudent to do step 2 on the actual production server (the calculations are a bit processor heavy, and I don't want to bog it down).  That seems to leave me with carrying out steps 1 & 2 elsewhere and then somehow transferring the new data to the production server.  Right now that "elsewhere" is simply on my local machine that has its own Postgres db, but it might end up being something like an EC2 instance.

Is there a smart way to  carry out step 3 without resorting to full data dumps?  At this point my db is <2GB and a data dump is feasible, but the data calculated may expand significantly, and transferring only the changed records will likely become more important.

I'm hoping there is a generally-accepted answer to this question, although I can see that it has the potential for subjectivity and, thus, might not be the ideal fit for this forum.  I'm sorry if that's the case - I'm hoping to learn from the folks here and am not sure where else to ask.


texts:
I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:

Table A - primary table - products:

+----+-----------+-------------+------------+--------+
| id | title     | description | createdAt  | status |
+----+-----------+-------------+------------+--------+
|  1 | Product A | Desc A      | 2019-07-19 |      1 |
|  2 | Product B | Desc B      | 2019-07-04 |      1 |
+----+-----------+-------------+------------+--------+

Table B - product_highlights -> productId and highlightId are foreign keys.

+----+-----------+-------------+
| id | productId | highlightId |
+----+-----------+-------------+
|  1 |         1 |           9 |
|  2 |         1 |          10 |
|  3 |         1 |          11 |
|  4 |         2 |           9 |
|  5 |         2 |          10 |
|  6 |         2 |          11 |
|  7 |         2 |          12 |
+----+-----------+-------------+

Table C - product_categories ->  productId and categoryId are foreign keys.

+----+-----------+------------+
| id | productId | categoryId |
+----+-----------+------------+
|  1 |         1 |          5 |
|  2 |         1 |          7 |
|  3 |         2 |          5 |
|  4 |         2 |          7 |
|  5 |         2 |          9 |
+----+-----------+------------+

I need to select categories and the number of their tours after the filtration process.
Currently, I am using a subquery for the filtration process then, selecting categories for each tour. I was wondering if any optimization can be applied to this query?

 SELECT
        pc.categoryId,
        COUNT(pc.productId)
    FROM
        product_categories pc,
        (
        SELECT
            t1.productId AS s1
        FROM
            product_categories t1
        JOIN
            product_highlights t2
        ON
            t1.productId = t2.productId
        WHERE
            t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
        GROUP BY
            t1.productId
        HAVING
            2 * 3 = COUNT(
                DISTINCT t1.categoryId,
                t2.highlightId
            )
    ) productsIds
    WHERE
        pc.productId = productsIds.s1
    GROUP BY
        pc.categoryId

The resultset should be:

+------------+---------------------+
| categoryId | COUNT(pc.productId) |
+------------+---------------------+
|          5 |                   2 |
|          7 |                   2 |
|          9 |                   1 |
+------------+---------------------+


texts: Select one row from n number table; Join / Union table with looping

texts:
I have a list table.

locations_list

id |  location | table
----------------------
1  | Location 1 | location_1
2  | Location 2 | location_2
3  | Location 3 | location_3

location_1

delivered_time       |  item_name
-----------------------------------------------
2017-06-08 16:37:57  | Bag 1
2017-06-08 19:37:57  | Bag 2

location_2

delivered_time       |  item_name
-----------------------------------------------
2017-06-08 15:37:57  | Bag 3
2017-06-08 20:37:57  | Bag 4

output expected

 id |  location | table       | delivered_time       |  item_name
 ------------------------------------------------------------------
 1  | Location 1 | location_1 | 2017-06-08 16:37:57  | Bag 2
 2  | Location 2 | location_2 | 2017-06-08 20:37:57  | Bag 4
 3  | Location 3 | location_3 |                      |

The join table names are in first table. I don't have option to put all delivery info into one table and do a where clause.

I tried UNION . But I don't know the table names. It is available in location table only.

How can I solve this?
dbfiddle link


texts: SQL Server 2008 R2 SP1 - Tempdb "Available free space" showing negative value

texts:
Please help as this issue is driving me nuts...

Tempdb has four data files and one log file. They are all located on the same dedicated drive.

When I right click on tempdb in SQL Server Management Studio (SSMS) to view Properties --> General...I'm seeing "Size" as ~65000 MB with "Space Available" as ~64000 MB. However, when I try to shrink the files via the GUI, I see that the "currently allocated space" hasn't changed and is still showing the initial size.

This is also where the "Available free space" is shown as a negative number. The only workaround I have found so far is to restart SQL Server to flush out tempdb, but I can't keep on doing this.

Has anyone ever come across this and knows the root cause/permanent solution for it please?


texts: How do I initialize transactional replication from a backup at a URL?

texts:
I created a publication on my server. I had to use this special option so that I could initialize replication from a backup:

exec sp_addpublication
    ...
    @allow_initialize_from_backup = N'true',
    ...

Then, I took a backup of my database:

BACKUP DATABASE [MyDatabase]
    TO URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
    WITH CREDENTIAL = 'mycredential', COMPRESSION, STATS = 5;
GO

And restored from it on the target server:

RESTORE DATABASE [MyDatabase]
  FROM URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
  WITH CREDENTIAL = 'mycredential'
    ,MOVE 'MyDatabase' to 'SomePath\Data\MyDatabase.mdf'
    ,MOVE 'MyDatabase_log' to 'SomePath\Log\MyDatabase.ldf'
    ,STATS = 5
GO

Now I'm ready to create the subscriber. The problem I'm having is that I don't know what options to provide to the sp_addsubscription procedure.

I can make it through the process using a regular hard drive instead of a URL. I could see data being replicated from source to target. I back up to disk, copy the backup to the target server, restore, and then initialize from backup using this command:

exec sp_addsubscription
    @publication = N'MyPub',
    @subscriber = N'MyTargetServer',
    @destination_db = N'MyDatabase',
    @subscription_type = N'Push',
    @sync_type = N'initialize with backup',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0,
    @backupdevicetype = 'disk',
    @backupdevicename = 'D:\Backups\MyDatabase.bak'

How do I tweak these options in order to initialize replication from a URL (which in my case is an Azure blob storage container that both servers have the credentials for)?


texts: innodb high i/o , some parameters make mysql crash

texts:
i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp.

the mysql version is 5.7.x

i tried some different configs but when some parameters changing mysql dont start anyway!

1)when i set innodb_dedicated_server mysql dont start

innodb_dedicated_server = true

2)when i set innodb_buffer_pool_size , innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size together mysql dont work too

innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 40
innodb_buffer_pool_chunk_size = 1G

but when i set these configures mysql start working correctly (innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size  are commented)

port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_connection=5000
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

max_allowed_packet=1048576

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

#innodb_buffer_pool_size=40G
#innodb_log_file_size=2G

#innodb_dedicated_server = true

innodb_buffer_pool_size = 20G
#innodb_buffer_pool_instances = 20
#innodb_buffer_pool_chunk_size = 1G

how can i get a better performance?!

updates:

my.cnf

SHOW GLOBAL STATUS;

SHOW GLOBAL VARIABLES;

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS;

MySQLTuner.pl


texts: Is is possible to manage SQL Server 2008 databases via R2 Management Studio?

texts: Is it possible to manage SQL Server 2008 databases using SQL Server 2008 R2 Management Studio?


texts: Encountered unexpected growth Transaction log file size

texts:
We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance Plan, Database transaction log file size increased abnormally. For suppose, if Primary Data file is 7 GB, the transaction log file grows to 55 GB.

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

The Other solution ever one recommended is Transaction log backup after Full Backup. We did same and observed no change in file sizes it shows same size as before Primary file is 7 GB and Transaction log file is 55 GB.

Please suggest me best way to reduce log file size without any effect on Performance.


texts:
environment sql server 2005

In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the original stored proc. I am doing this by using return scope_identity(); My understanding is return is only used for error messages/status codes. Is this a "good" way of doing what I need to do, or is there another "better" way?


texts:
I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:

  Does a re-index update the statistics?

We found discussions online arguing both that it does and that it doesn't.


texts: How to I interleave logs from two different Oracle tables?

texts:
I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's going on in both pieces of code. Both tables have a time-stamp column (although the columns have different names). Here's a stripped-out version of the two tables, and what I'd like as a result:

Table LOG_1:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3

Table LOG_2:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824005  Log_2 entry 3

New table LOG_COMBINED:

TIME                          ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3
11-SEP-12 12.39.16.037824005  Log_2 entry 3

(Note the [artificial] difference in the timestamps!)

I'm sure this can be done in a simple SQL statement - perhaps involving a union all?


texts:
Can someone please advise best methods to do a mysql PITR from binary logs that are saved in MIXED format.

I am finding it difficult to identify the wrong queries that executed to avoid from restore process using mysqlbinlog tool.

Following : https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/pit_recovery_ibk.html


texts: Implementing foreign keys on generalized data model

texts:
Suppose the ASCII tree below is a UML generalized concept model.  To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub classes in UML.  In other words, class [wild] inherits all attributes from [mammal], and [domestic] inherits all attributes from [mammal] as well.  Furthermore, [raccoon] and [white tail deer] inherit all attributes from [wild].

I want to convert the UML model to a relational database model.

Notice how [raccoon] extends both [wild] and [domestic].  In other words, it has two parents.  I would normally make the primary key of all descendant tables the same as their parent primary key.  In addition, I'd make the foreign key in the descendant table the same as the primary key in its parent table.  Because [raccoon] has two parents, how would I build that table?

mammal
 |
 +- wild
 |  |
 |  +- raccoon
 |  |
 |  +- white tail deer
 |
 +- domestic
    |
    +- cat
    |   |
    |   +- siberian
    |
    +- dog
    |   |
    |   +- golden retriever
    |   |
    |   +- lab
    |
    +- raccoon


texts: How can I specify which MariaDB instance to launch in a Galera Cluster

texts:
I have several MariaDB servers, each with multiple instances running. I'm trying to figure out how would I bootstrap a Galera cluster but specify which instance I want to launch i.e:

node1, 2, and 3 each has instances 1-4 running via mysqld_multi, each running on their own port.

I want to use instance 4 on each server for the cluster, so I shut each one down.

On my test cluster (which has a single instance of MariaDB running) bootstrapping is simple: start it on the first node with galera_new_cluster then bring up the other two servers.

How would I do the same thing but specify GNR "4" to bootstrap the cluster?


texts: What is the sense to use server_lifetime with PgBouncer?

texts:
I've found server_lifetime option in PgBouncer config, and I'm trying to undestand if it is really needed in our case.

https://pgbouncer.github.io/config.html

  The pooler will try to close server connections that have been
  connected longer than this. Setting it to 0 means the connection is to
  be used only once, then closed. [seconds]

  Default: 3600.0

What is the sense of closing and reopening connections?

Why does the doc say "will try"? May PgBouncer fail on this operation?


texts: Why "Data File(s) Used Size (KB)" PerfMon counter doesn't exist?

texts:
I wanted to create my own SQL Server baseline by collecting data with the Performance Monitor.

Among all counters that I have decided to monitor, I also wanted to have a historic trend about data file(s) growth for each database, but, unfortunately, there's no counter for that. I only have a "Data File(s) Size (KB)" counter, but that refers to the space that the database takes in advance, not the one that is actually using.

I know already that i can get the actual used size by doing some queries, in fact my question is not about how to retrieve that information but why can't i get it from the PerfMon.

Is there any official motivation for that? Laziness from Microsoft developers, some technical problem, or maybe something else?

Also note that I can get with no problem the used size for the log(s) file, which is kind of funny in my opinion!


texts: Is there any option to enable Window NT logs in SQL Server Logs

texts:
I'm trying to view SQL Server logs on a specific server and is missing Windows NT section in same.
Though other server are showing that section.
In the screenshot shared below, I highlighted section that I'm not getting for one of the SQL Server instances.
Question

Is there any configuration or any required steps, for enabling Windows NT section in SQL Server Logs?

Server details with the issues:
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Server details having no issue or the screen server details
Microsoft SQL Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0 (X64)   Dec 15 2019 08:03:11
Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)


texts: dropping SYNONYM is blocking in sql server?

texts:
I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2.

Now my problems is: while users querying from synonyms which points to TestDB1 is still running it won't let me drop synonyms. It is blocking the process synonyms re-point to TestDB2.

It only let me re-point after queries complete. Is there any other process to overcome this problem. I want to re-point synonyms while users querying?

Note: Both databases contains same tables but data will be change


texts: mariadb Unknown system variable 'server_uuid'

texts:
Im trying to setup replication between mariadb(10.1.31-MariaDB - MASTER) and mysql(5.7.12-log - SLAVE). After executed the start slave, I got the below error.

  Fatal error: The slave I/O thread stops because a fatal error is
  encountered when it tries to get the value of SERVER_UUID variable
  from master.

Then I tried to get the value of master_uuid.

MariaDB [(none)]> show variables like '%server_uuid%';
Empty set (0.00 sec)

Then tried to setup this in mysql prompt.

MariaDB [(none)]> set global  server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33;
ERROR 1193 (HY000): Unknown system variable 'server_uuid'

And also I tried to setup auto.cnf in data directory.

[auto]
server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33

chmod 644 auto.cnf

But still im getting the fatal error is encountered when it tries to get the value of SERVER_UUID variable from master.


texts: Assembly 'EDCLR' references assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089 error

texts:
I'm try to create clr function in c# with this code:

[SqlFunction]
        public static string Decrypt(string Input)
        {
            var test = "";
            var url = "http://www.url/feed/";
            using (XmlReader reader = XmlReader.Create(url))
            {
                SyndicationFeed feed = SyndicationFeed.Load(reader);
                //MessageBox.Show("Title:" + feed.Title.Text);
                //MessageBox.Show("Uri:" + feed.Links[0].Uri);
                foreach (SyndicationItem item in feed.Items)
                {
                    test=item.Title.Text;
                }
            }
            return test;
        }

when try add that to sql server assembly with this script:

create assembly behzad
from 'd:\EDCLR.dll'

i get this error:

  Assembly 'EDCLR' references assembly 'system.servicemodel,
  version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
  which is not present in the current database. SQL Server attempted to
  locate and automatically load the referenced assembly from the same
  location where referring assembly came from, but that operation has
  failed (reason: 2(The system cannot find the file specified.)). Please
  load the referenced assembly into the current database and retry your
  request.

how can i solve that?thanks.


texts: Table size in Azure SQL Managed Instance vs On Premise SQL Server

texts:
In doing a test, I have observed that the size of the same table with the same exact DDL (created from scratch) with the same exact data (10M rows) varies drastically in our Azure SQL Managed Instance vs a local installation of SQL Server: 336 MB in the managed instance vs 198 MB in the local.

The steps are the same in either scenario:
- Run the table creation script (exact same) without any primary key/index in both environments
- Load a file with 10M rows (3 columns) to the table with bcp in both environments

Does anyone have any insight as to why the managed instance takes significantly more space? (I am not worried about the actual MBs, but the ratio since this is a table that will contain billions of rows).


texts: How can I "shift" certain column values to different rows in an existing table?

texts:
I'm implementing a queue as a ring buffer in a table along the lines of what Thomas Kejser talks about here: Implementing Message Queues in Relational Databases If the ring buffer fills up, I have to increase the number of slots and possibly shift some of the values around. This is part of my routine to add slots to the ring buffer

I have a table that looks like this:

SlotId    SlotVal1  SlotVal2
1          3           3
2          4           4
3          1           1
4          2           2
5          NULL        NULL
6          NULL        NULL
7          NULL        NULL
8          NULL        NULL

I would like to "shift" the values from slots 3 and 4 forward by 4 slots to rows 7 and 8 so the table would look like this:

SlotId  SlotVal1 SlotVal2
1       3        3
2       4        4
3       NULL     NULL
4       NULL     NULL
5       NULL     NULL
6       NULL     NULL
7       1        1
8       2        2

I tried two different UPDATE queries to shift the rows forward and then clear the previous rows.  The first query (as reproduced below) doesn't update any rows in my table and I'm drawing a blank on what I must have left out:

        -- shift slot values forward
        DECLARE @firtRowToMove AS int = 3;  -- slot id of the first row to move
        DECLARE @rowsToShift AS int = 4; -- number of rows forward to shift
        UPDATE [SlotTable]
        SET [SlotVal1] = (SELECT [SlotVal1]
                          FROM [SlotTable] AS s2
                          WHERE s2.[SlotId] = ([SlotId] - @rowsToShift)),
            [SlotVal2] = (SELECT [SlotVal2]
                          FROM [SlotTable] AS s2
                          WHERE s2.[SlotId] = ([SlotId] - @rowsToShift))
        WHERE [SlotId] >= (@firtRowToMove + @rowsToShift);


texts: How to replay xlogs from the beginning of time? (postgresql)

texts: Let's say I'm keeping all the xlog's for a cluster instance since the time of cluster initialization (ie, initdb). Let's call that time T0. All xlog's are retained/archived through T1 and continuing through time T2, at which point a backup is made. But now, I need to recover to time T1. I know how to do PIT to a time after T2, but I'm not sure how to do it to time T1. Do I re-initialize the database and replay the logs? Will that actually work, or will I have issues with timelines?


texts: Database Design Advice for Expert Systems - Rule Based Systems

texts:
Expert Systems are a branch of Artificial Intelligence. These systems try to capture knowledge of Experts via set of rules. Most of the rules are stated in natural language. But they need to be interpreted by rule engine therefore they are statements in the form of classic if-then-else.

IF

THEN

ELSE

This specific expert system will hold substances and patients. Every rule give an advice  according to patient's attributes , which is stored in database.

For example:

 IF (PatientWeight < 50 AND PatientSex = Male AND DaysInTreatment = 0)
 THEN SUBSTANCE_X = 10
 IF (PatientWeight < 50 AND PatientSex = Male AND DaysInTreatment > 0)
 THEN SUBSTANCE_X = 20 +  DaysInTreatment *5

I have never designed such a system. Can you give me some starting pointers to read? Or Any advice?


texts: What is the point of the Script Date in a stored procedure script generated by SSMS?

texts:
When I generate a stored procedure script, SSMS additionally generates a comment containing the name of the object and a Script Date timestamp:

The timestamp is just the current time at the moment the script was generated, and I am trying to understand the point of it.

I could see SSMS including an automatic feature to capture when a stored procedure is created, or last updated, or ran, for that matter. Any of those might be of use to a programmer in their day-to-day activity. What is the point of including the time it is viewed, though?

I have tried searching for an answer across the Internet but nothing useful has come up.


texts: Backing up Time Series Database (PostgreSQL/TimescaleDB) where old data will never be updated

texts:
If a PostgreSQL (using TimescaleDB extension) database that holds time series data is continuously growing in size, what is the recommended backup strategy if we do not want to continuously create backups of old data that is over 1 year old, since this old data will not be updated.

Assume that

There are about 5000 tables in the database, each growing at different rates
Full backups are taken every 3 months, with incremental backups taken every day
The backup files will be saved to the same machine that the database is on. After the backup process is completed, these backup files will then be moved to a remote server (not S3, etc) for storage
PostgreSQL 11 is running on Ubuntu 18.04

Possible Backup Strategies

Create multiple databases, one for each year

For example: databases temperature_2019, temperatures_2020, etc..., each containing 5000 tables
Maintain one database and create a new table every year for each existing series of related tables.

For example: One database temperature, with tables newyork_2019, newyork_2020, etc...
Logical backup by writing a Python/Nodejs script that selects data within the time range that needs to be backed up and writes these rows to a file. Maintains 1 database, 5000 tables. A restore script will need to be written as well. What file format (CSV, HDF5, Parquet, etc.) & compression is recommended for this?

Method #1 appears to be the simplest to use with existing tools like barman, as we can select the databases that we want to create full & incremental backups.

What will be your recommendation? Thank you!


texts: Howto create a detailed report of all actions by publishing with sqlpackage.exe?

texts:
When publishing ssdt projects from visual studio I realized that a "deploymentreport.txt" file is created on hard disk.

sqlpackage.exe can be executed with the action "DeployReport" that creates a report of all actions that would have been done during a publishing.

Is there any way to create this report DURING the publishing? As a result / log?

I don't want to create the report first and call sqlpackage.exe with the publish action again afterwards to get a report and do the deployment. This might be much more work to do than necessary.


texts: Modeling for inclusion of two different entities

texts:
What I Have
I am currently hashing out the data model for an application that will utilize MongoDB. In my setup, users or members (as they are called in the schema) will be created and then assigned to teams (team).
Currently the set up is like so:
member:
    first_name
    last_name
    mi
team:
    member (an array of objectIDs)
    assigned (the tasks the team is assigned to)
    name (string name for the team)

The problem
In the midst of this, I realized I wanted the ability to combine multiple teams into one team. For example if I create a Team A and Team B but then realize they should work together on something, I would want to combine them as Team C and assign Team C the task.
Idea 1
My first idea was when the two teams were combined, that all the members from the combined teams would just be added together in one mass array of member ObjectIDs. I don't like that for several reasons.

It makes dropping members difficult. If I drop a member from Team A and want it to reflect into Team C, that's going to take a lot of extra effort to keep it consistent.
It also makes adding a member difficult. I add a member to Team B, thus I must add the member to Team C.
I'd imagine that would be a lot more taxing in terms of writes.

Idea 2
The best idea I can come up with at the moment is to add an included_team field to the team document. This will start to get a little verbose in terms of queries but it's the best I can think of so far.
There is a better way to do this. I know there is. Suggestions?
A little Background
Perhaps this will help some understand if the solution seems all to simple to figure out. This project is my first endeavor into MongoDB and NoSQL. I come from an all SQL background for interacting with databases.


texts:
I am using SQL Server 2017 based on Windows server 2016. I am trying to connect my named instance, but I got the error message as below screenshot:

So I tried to reinstall ODBC Driver 13. But I got the error as follow:

Even after I uninstalled ODBC Driver 17, I still got the same error message.
Any thought?

Thanks in advanced!


texts: In Postgres, set log_rotation_size but my logs are still not rotating

texts:
I’m using Postgres 9.5 on Ubuntu 14.04.  I wanted to rotate my logs whenever they reach a certain size.  So I edited  /etc/postgresql/9.5/main/postgresql.conf and set this

log_rotation_size = 50MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

Then I restarted my server

sudo /etc/init.d/postgresql restart

However after running some operations, I ran out of disk space, and discovered my log had not rotated at all …

myuser@mymachine:~$ ls -al /var/log/postgresql/postgresql-9.5-main.log
-rw-r----- 1 postgres postgres 3165773943 Dec 29 18:34 /var/log/postgresql/postgresql-9.5-main.log

What else do I need to do to get the log rotation to kick in?


texts: DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones

texts:
Kimberly Tripp's blog post here made me think that running DBCC FREESYSTEMCACHE('SQL Plans') clears just single-used ad-hoc and prepared query plans. But that seems incorrect. It clears all. Which is probaly not a good option to go if you have high average use count of prepared plans.

Is there any option to clear JUST ad-hoc plans? Not the prepared ones?


texts: ALTER TABLE SWITCH fails with indexed view

texts:
I'm having issues with ALTER TABLE SWITCH between a staging table and a target table which is schema-bound to an indexed view.

When I issue the SWITCH statement, e.g

ALTER TABLE dbo.MASTERPrices_Staging switch TO dbo.MASTERPrices;

I get the following message:-

  Msg 11402, Level 16, State 1, Line 1
  ALTER TABLE SWITCH statement failed. Target table 'MASTERPrices' is referenced by 1 indexed view(s), but source table 'MASTERPrices_Staging' is only referenced by 0 indexed view(s). Every indexed view on the target table must have at least one matching indexed view on the source table.

We use only SQL Server 2008 Standard Edition so partitioning is not a solution. I need to SWITCH about 10 million rows daily, yet not loose the indexed view solution?

If I alter the view to not schema bound, then SWITCH Works, but when I ALTER the view again and set it back to SCHEMABOUND, all the indexes (12 of them including clustered index have dissapeared)

Any ideas anybody?


texts:
I have to restore our production database to a test instance on a daily bases.  This test instance gives people access to the data that we do not want changing production data.  When I restore the database there are logins in the test instance with access to the database that do not have a login or user account in production instance.  So I have a script that goes and adds each user to the users list in the restored database on the test instance.  This list of users changes more than I would like. So I am always making changes to the script.

Does any one know a way to pull a list of instance logins with access to the restored database and add them to the user list of the restored database.  I have to add them with read, write and execute permissions

Thanks


texts:
Database version: Oracle 11gR2
We want to use DYNAMIC_REGISTRATION_LISTENER parameter with off option.
Our listener.ora file like this:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = oracle)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

DYNAMIC_REGISTRATION_LISTENER = OFF

But when we use this parameter, our web service request cannot connect to the database.
SQL> SELECT dbms_xdb.gethttpport FROM dual;

GETHTTPPORT
-----------
       3010

Without this parameter:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-FEB-2021 03:21:59
Uptime                    0 days 0 hr. 2 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=3010))(Presentation=HTTP)(Session=RAW))

With this parameter:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-FEB-2021 03:21:59
Uptime                    0 days 0 hr. 2 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

We need to use this parameter. Web services also need to be connected. How is that possible?
I could not find any clear solution.
Best regards,


texts: Why is Postgres CTE slower than subquery?

texts:
I have a somewhat involved query that splits strings and outputs each word as a record.

I did a quick test, one with a CTE and one with a subquery and was somewhat surprised to see that the CTE takes twice as long to execute.

Here is the gist of what the query does:

-- 1. translate matches characters from comment to given list (of symbols) and replaces them with commas.
-- 2. string_to_array splits string by comma and puts in an array
-- 3. unnest unpacks the array into rows

Inline subquery

SELECT
    sub_query.word,
    sub_query._created_at
FROM
(   SELECT unnest(string_to_array(translate(nps_reports.comment::text, ' ,.<>?/;:@#~[{]}=+-_)("*&^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
        nps_reports.comment,
        nps_reports._id,
        nps_reports._created_at
    FROM nps_reports
    WHERE nps_reports.comment::text <> 'undefined'::text
) sub_query
WHERE sub_query.word IS NOT NULL AND NOT (sub_query.word IN ( SELECT stop_words.stop_word FROM stop_words))
ORDER BY sub_query._created_at DESC;

CTE

WITH split AS
(
SELECT unnest(string_to_array(translate(nps_reports.comment::text, ' ,.<>?/;:@#~[{]}=+-_)("*&^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
    nps_reports.comment,
    nps_reports._id,
    nps_reports._created_at
FROM nps_reports
WHERE nps_reports.comment::text <> 'undefined'::text
)

SELECT
    split.word,
    split._created_at
FROM split
WHERE split.word IS NOT NULL AND NOT (split.word IN ( SELECT stop_words.stop_word FROM stop_words))
ORDER BY split._created_at DESC;

And here are the EXPLAINs for each:

Subquery Explain

Sort  (cost=15921589.76..16082302.91 rows=64285258 width=40) (actual time=16299.150..17697.914 rows=4394788 loops=1)
  Sort Key: sub_query._created_at DESC
  Sort Method: external merge  Disk: 116112kB
  Buffers: shared hit=22915 read=7627, temp read=34281 written=34281
  ->  Subquery Scan on sub_query  (cost=2.49..2311035.10 rows=64285258 width=40) (actual time=0.177..13274.895 rows=4394788 loops=1)
        Filter: ((sub_query.word IS NOT NULL) AND (NOT (hashed SubPlan 1)))
        Rows Removed by Filter: 3676303
        Buffers: shared hit=22915 read=7627
        ->  Seq Scan on nps_reports  (cost=0.00..695825.11 rows=129216600 width=88) (actual time=0.073..9781.244 rows=8071091 loops=1)
              Filter: ((comment)::text <> 'undefined'::text)
              Rows Removed by Filter: 844360
              Buffers: shared hit=22914 read=7627
        SubPlan 1
          ->  Seq Scan on stop_words  (cost=0.00..2.19 rows=119 width=4) (actual time=0.016..0.034 rows=119 loops=1)
                Buffers: shared hit=1
Planning time: 0.115 ms
Execution time: 18451.245 ms

CTE Explain

Sort  (cost=17213755.76..17374468.91 rows=64285258 width=40) (actual time=44008.467..45508.786 rows=4394788 loops=1)
  Sort Key: split._created_at DESC
  Sort Method: external merge  Disk: 116112kB
  Buffers: shared hit=23031 read=7531, temp read=34281 written=353942
  CTE split
    ->  Seq Scan on nps_reports  (cost=0.00..695825.11 rows=129216600 width=135) (actual time=0.057..10451.951 rows=8071091 loops=1)
          Filter: ((comment)::text <> 'undefined'::text)
          Rows Removed by Filter: 844360
          Buffers: shared hit=23027 read=7531
  ->  CTE Scan on split  (cost=2.49..2907375.99 rows=64285258 width=40) (actual time=0.162..37888.364 rows=4394788 loops=1)
        Filter: ((word IS NOT NULL) AND (NOT (hashed SubPlan 2)))
        Rows Removed by Filter: 3676303
        Buffers: shared hit=23028 read=7531, temp written=319661
        SubPlan 2
          ->  Seq Scan on stop_words  (cost=0.00..2.19 rows=119 width=4) (actual time=0.009..0.030 rows=119 loops=1)
                Buffers: shared hit=1
Planning time: 0.649 ms
Execution time: 46297.825 ms


texts: Update with id returned from insert using WITH

texts:
I need a help with this query I make an insert that returns me an id, then I need to run an UPDATE with that id returned...

WITH disca AS (
    INSERT INTO callcenter.campanha_cliente_chamada
        (   id_campanha_cliente,
                cod_contato,
                telefone,
                telefone_discar,
                id_status_chamada,
                tentativa,
                monitorando,
                acionar_proxima,
                datahora_update,
                datahora_registro
        ) VALUES (154424,1824704,'(11) 2524-9636','1125249636',1,1,0,0,CURRENT_TIMESTAMP::timestamp(0),CURRENT_TIMESTAMP::timestamp(0))
    RETURNING id_campanha_cliente_chamada
) UPDATE callcenter.campanha_cliente_chamada
  SET accountcode = ('D-'||id_campanha_cliente_chamada||'-'||tentativa)::varchar(128)
  WHERE id_campanha_cliente_chamada = disca;


texts:
Everything works as intended, but function should return 0 if value is NULL. Currently it returns nothing (see problem at the end of the post).
How could I make to return 0 if no order is found?
'orders' Table
CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    status CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Records
INSERT INTO orders
(id, code, service_id, status, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);

Testing Statement
SELECT EXTRACT(YEAR FROM creation) AS year,
                        orders.code,
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = 2019 AND orders.code = 2394
                GROUP BY year, orders.code
                ORDER BY year ASC

Returns

year | code | count
:--- | ---: | ----:
2019 | 2394 |     3

Function
CREATE OR REPLACE FUNCTION
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0)
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric),
                        CAST(orders.code as bigint),
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested'
                AND EXTRACT(YEAR FROM creation) = inyear
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;

END;
$$
LANGUAGE plpgsql;

Executing Function
select * from yearly_orders(2019, 1194);

Returns <-- PROBLEM

retyear | retcode | retcout
------: | ------: | ------:
        |         |

SHOULD RETURN

retyear | retcode | retcout
------: | ------: | ------:
 0       |     0    |    0

db<>fiddle
The db<>fiddle to try this out can be found here.
The code was made by user:15356 (Here)


texts: Performance degradation after upgrade from 3.0.11 to 3.2.12

texts:
Environment: CentoOS 6 (kernel 2.6.32), MongoDB community edition 3.0.11 and 3.2.12 , storage engine MMAP, config servers on SCCC

We are experiencing performance degradation when moving from 3.0.11 to 3.2.12. Application throughput is getting reduced by 5-10 times in 3.2.12 compared to 3.0.11.

In the past, we had attempted to upgrade from 3.0.11 to 3.2.8 but due to https://jira.mongodb.org/browse/SERVER-26159 bug, we rollback to 3.0.11. In 3.2.8 application throughput was fine but since the mongos were randomly crashing due to SERVER-26159 we rollback to 3.0.11.

Bug SERVER-26159 fixed in 3.2.10 so we attempt to upgrade but we got our performance reduced so we rollback to 3.0.11 again. We opened a JIRA https://jira.mongodb.org/browse/SERVER-26654 about this issue (and several other people report almost the same issues) and according to MongoInc the issue was solved in 3.2.12. We attempt to upgrade to 3.2.12 but we got the same performance degradation as the 3.2.10 upgrade.

The issue we are seeing in the logs after increasing the verbosity from 1 to 2 is the following:

I ASIO  [NetworkInterfaceASIO-TaskExecutorPool-2-0] Failed to connect to (node) - ExceededTimeLimit: Operation timed out
D ASIO  [NetworkInterfaceASIO-TaskExecutorPool-2-0] Failed to execute command: RemoteCommand 23628777 -- target:(node) db:admin cmd:{ isMaster: 1 } reason: ExceededTimeLimit: Operation timed out

isMaster command is timeout for different "TaskExecutorPool" all the time.

Note: I am not changing the "protocolVersion" to 1 after the 3.0.11 to 3.2.12 upgrade as makes the rollback harder.

We managed to reproduce the issue with sysbench-mongodb using 3.2.12 on a 10 nodes sharded cluster, not in the scale we getting it on our production system.

To remedy the issue in testing we changed taskExecutorPoolSize value:

Our mongos has 6 CPUs so I assume it creates 6 connection pools with defaults. Using a smaller value like "taskExecutorPoolSize"=2 reduces the timeouts so it seems the more connection pools I use the more timeouts I get during the benchmark. When I set "taskExecutorPoolSize"=1, which I believe set a single connection pool, I am not getting the above timeouts.

We also modified the ShardingTaskExecutorPoolRefreshTimeoutMS from the default 20 seconds to 60 seconds which also eliminated the timeouts.

We combined both on production but unfortunately, the timeouts didn't go away and we still noticed the same performance degradation.

setParameter:
 ShardingTaskExecutorPoolRefreshTimeoutMS: 60000
 taskExecutorPoolSize: 1

I want to believe that is not our workload that triggering the performance degradation as it operates fine on 3.0.11

The purpose of the thread is to understand what has changed between 3.2.8 and 3.2.12 that might trigger the isMaster request to fail between mongod and mongos.

Much appreciated If anyone has internals on the change or is facing the same problem and found a workaround.

Thanks in advance,