texts: Returning data where all categories exactly match

texts:
I have a documents table that I'm rendering out in a ui.

This ui has a set of select2 dropdowns for different category types and the dropdowns are populated data from a categories table.

Each of these dropdowns allows for multiple selections and will post a named array of values to the server.

What I'm hoping to achieve is to only display the relevant documents based on matching the posted cat_id's within a cat_type

These are example tables

Documents

+----+------------+-------------+
| id | doc_title  | doc_version |
+----+------------+-------------+
|  1 | item 1     |           4 |
|  2 | document 2 |           3 |
+----+------------+-------------+

Categories

+----+--------+----------+
| id | cat_id | cat_type |
+----+--------+----------+
|  1 |      1 |        3 |
|  1 |      7 |        2 |
|  1 |      4 |        2 |
|  2 |      7 |        2 |
+----+--------+----------+

An example might be querying documents where the cat_id = 7 and the cat_type = 2.
This should to return row 1 and 2 of the documents table.

What I'm having difficulty with is query documents for a match where multiple cat_id's are declared for a cat_type.

An example might be querying documents where the cat_id = 7 and 4  and the cat_type = 2.
This should to return row 1 of the documents table.

I was thinking I could of used WHERE cat_id IN (4,7) AND cat_type = 2, however that returns results that match either cat_id.

How should I go about querying the data in order to return document entries that exactly match cat_id's for a cat_type?

Thanks


texts: Migrate a big database from Oracle 11g to 19c

texts:
I have a big and customized db on Oracle 11g with many store procedures, jobs, triggers, materialised views and data.
We need to migrate all data, structures, etc on Oracle 19c.
Do you know if there are incompatibilities or Oracle 19c is completely compatible with 11g syntax and structures?
Thanks


texts:
I am executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions (at least 10) which are taking beyond 15 seconds to respond. The data for each request is exactly the same as any previous request (just appending a dynamic number to name...etc).

It certainly looks like a database issue, because my database is responding differently each time we execute a same query again and again. I am not able to understand why my database is behaving like this. My database is oracle 11g. I have experienced this issue with different UI flows as well previously.

Edited to add collected metrics.


texts:
What is the overhead of RMAN hot backup (full backup including archive logs) being performed during business hours?

Does it actually affect the performance of the database?

What is the case when incremental backups are taken in terms of overhead?


texts:
I have a table with several attributes like this:

id  id2  attribute
--------------------------------
 1  100  blue
 2  100  red
 3  100  green
 4  100  white
 5  102  blue
 6  102  green
 7  102  red
 8  103  red
 9  103  blue
10  103  white
11  104  red
12  104  black
13  104  green
14  104  orange
15  105  red
16  105  blue
17  105  green

I want to know:
- what are the top attributes for the entries that have 'blue'?
or
- what are the top attributes for the entries that have 'blue' and 'red'?

For the second query the results should be:

attribute   count1
--------------------
green       3
white       2

I can build the query dynamically. I have this working, using this approach:

SELECT
  mytable.attribute,
  count(mytable.id) as count1
FROM
    mytable,
    (SELECT
     id2
    FROM
     mytable
    WHERE
     attribute in ('blue', 'red')
    GROUP BY
     id2
    HAVING
     count(distinct attribute) = 2) as t
WHERE
 mytable.id2 = t.id2
and
 attribute NOT IN ('blue', 'red')
GROUP BY
 mytable.attribute
ORDER BY
 count1 desc

The problem is that if the inner query has a lot of entries the while process takes too long. Can anyone suggest a way to improve this?


texts: How to transpose rows to column in datatabse

texts:
I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below

 Col_ID   Col_Group   Col_Price
 1        AA           0
 1        BB           2000
 1        CC           1000
 2        AA           0
 2        RR           3000
 2        DD           4000
 2        EE           0

and I want to make transpose or rows to column like as below

col_ID  group1 Price1 group2 Price2 Group3  Price3  Group4 Price4
 1       AA     0       BB    2000   CC      1000
 2       AA     0       RR    3000    DD     4000      EE     0

I use PIVOT And trued below query

SELECT * FROM tbl1 PIVOT ( SUM(Col_Price) FOR Col_Group IN ('AA','BB','CC','DD','EE','RR'))

I got table But I Want as above mention. please Help me


texts: Database design for handling 1 billion rows and counting

texts:
We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes or so, a service fetches this data, and processes it into trips. Once the trips have been generated, the actual GPS data is usually not so important, only if the user wants to see the route on a map.

The problem is that it seems the database is struggling to keep up with the rate of data being inserted. Sometimes when the load increases, the insert time suddenly increases drastically (> 30 seconds), which in turn allows more data to be buffered, which in turn results in larger inserts and longer insert duration.

I hope to get some comments on the current design, and some of the ideas we have to improve performance, and answers to some of our questions - and any other tips people might have!

Current design

The data is currently separated into tables representing one week, and data older than a year is archived into a secondary database. The whole thing is joined together in an editable view, which is used for both inserts and reads.

Table design

Id (PK, uniqueidentifier)
DeviceId (FK, int)
PersonId (FK, int)
VehicleId(FK, int)
TokenId  (FK, int)
UtcTime  (PK, datetime2(3))
Latitude (float)
Longitude(float)
Speed    (smallint)
Heading (smallint)
Satellites (tinyint)
IOData (varbinary(100))
IgnitionState (tinyint)
UserInput (tinyint)
CreateTimeUtc (datetime2(3))

Indices

DeviceId_CreateTimeUtc_Desc
DeviceId_UtcTime_Desc (Clustered)
PersonId_UtcTime_Desc
TokenId_UtcTime_Desc
VehicleId_UtcTime_Desc

Every week currently takes up around 10 GB including indices, and currently there is around 300 GB data in the main database.

The data tables in the main database have their own filegroup with 1 file, but it is on the same disk as all other tables in the main database. The secondary database is on a different disk, but on the same machine.

I think we are also running an index rebuild job weekly, when a new table partition (week) is taken into use. No shrink is performed.

The machine is an 8-core HP with 12 GB memory, and the disk holding the main database is running RAID 10.

Ideas

Limit the amount of data stored in primary database to e.g. max 1 month. At the very least it would make the database more managable for backup/restoration, but could we expect to see a performance improvement by doing this?
Create 2 files in filegroup for current data, and distribute them onto 2 different physical partitions
Create master-slave databases holding current data, so inserts and reads are performed on different databases
Put files for current data on SSD disks (would mirroring make any performance difference with SSD disks?)

Please let me know if more info is needed. There are horribly many factors influencing performance, and probably equally many ways to tweak it.


texts: Practicing AlwaysOn on SQL Server 2014 - Windows 10 Home

texts:
TL;DR:
What possible solution(s) are there to practice AlwaysOn feature on SQL server 2014 installed on Windows 10 Home edition?

I am learning Database Administration on my own and have installed Trial version of SQL Server 2014 on my home laptop for 'playing around'. Going into Configuration Manager I found that I need to have Windows Server 2008 or later OS to work with AlwaysOn. Is there any way to bypass this requirement (with any patch or service pack) or what other solution can anyone kindly recommend.

Thanks in advance


texts: Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?

texts:
Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior.

-- Table: staging.mytable

-- DROP TABLE staging.mytable;

...

WITH (
  OIDS=FALSE
);

ALTER TABLE staging.mytable
OWNER TO postgres;


texts: Storing mirror database failover details to the respective server or on repository

texts:
I would need some practical suggestions on how to achieve below:

We have quite a few servers involved in database mirroring. As part of various activities and other fixes, we have database failover happening now and then.

What i am generally doing is browsing the sql error log to find the failover details for most of them

Is there a way , we can store or track the failover details for them either locally on server, say saving all that details in a text file or on repository server?

Thanks


texts: Remote Desktop SQL Server Management Studio Dialog boxes Font size too small

texts:
When I connect from my local machine to a remote server through Remote Desktop Connection.
And then try to use SQL Server Management Studio, the Dialog boxes in the application for example Connection dialog screen shown on launch, Server Properties etc are very small, so small that the text on these dialog boxes is not readable at all.
Can someone please guide how to fix this issue?
I can change Font size of different sections for example, Query Editor, Object Explorer, Query execution results but I couldn't find any option to fix font size of text in Dialog boxes.
I am connecting through a machine with 4K display (XPS 15).
Can anyone please guide how to fix this?


texts: Constraint with respect to existing data

texts:
Talking about SQL Server.

Table contains column A and B. For simplicity - no key. Both columns may contain any values. But with a limitation: if column A receives a value which was already used in the table (in column A), column B must have same value as already used (in respected row).

Examples:

Allowed:

A | B

1 | 1

2 | 2

1 | 1

Not allowed (B differs for same A):

A | B

1 | 2

1 | 1

I can use INSTEAD OF UPDATE or INSTEAD OF INSERT triggers to enforce such logic, but I wonder if it is possible to use check constraints of indexes or some other automated tool for the same goal?


texts: DBCC failure due to sparse files on Windows Cluster

texts:
We have a SQL Server 2014 SP1 cluster on Windows Server 2012 R2, and came across a problem with DBCC jobs failing and issuing the following messages:

  Msg 1823, Level 16, State 2, Line 3
  A database snapshot cannot be created because it failed to start.
  Msg 1823, Level 16, State 8, Line 3
  A database snapshot cannot be created because it failed to start.
  Msg 5119, Level 16, State 2, Line 3
  Cannot make the file “C:\ClusterStorage\Volume1\DATA\test1.mdf_MSSQL_DBCC28” a sparse file. Make sure the file system supports sparse files.
  Msg 7928, Level 16, State 1, Line 3
  The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams.

This being Windows Server 2012 R2, there should be no problem with sparse file support, and in any case this cluster had been online months with no issues.

We finally figured out what I'd call a workaround rather than a fix, in that the SQL Data volume – one of two cluster volumes – turned out to be owned by the remote cluster node, with SQL Server running on the primary node. When we changed the ownership of the cluster volume to the primary node the problem immediately went away.

So the big question is why does a cluster volume on a remote node suddenly appear as if it doesn't support sparse files? I cannot find any info on this, and it may be a spurious error as I'm sure this disk has been owned by a separate node before and not caused this error.

Would be great if anybody had input, but if not, at least anybody else having this issue will find out fix in a search through these questions.


texts:
I have a dimension hierarchy that is related to multiple fact tables with different granularities. In the case of one fact table, it relates to the middle level of a three level hierarchy.

When you drill down that dimension, I would prefer to have values at that granularity and nothing if you drill down further. The default behaviour seems to be just duplicating the parent total for each of the leaf members.

I thought there was a property to control this but for the life of me I can't find it now.


texts: LISTEN/NOTIFY pgconnection goes down java?

texts:
I am using PostgreSQL DB and applying it's LISTEN/NOTIFY functionality. So my listener is at my AS (Application Server) and I have triggers configured on my DB such that when CRUD operations are performed on a table a NOTIFY request is sent on AS.

LISTENER class in java:

    @Singleton
    @Startup
NotificationListenerInterface.class)
    public class NotificationListener extends Thread implements NotificationListenerInterface {

        @Resource(mappedName="java:/RESOURCES")
        private DataSource ds;

        @PersistenceContext(unitName = "one")
        EntityManager em;

        Logger logger = Logger.getLogger(NotificationListener.class);

        private Connection Conn;
        private PGConnection pgConnection = null;
        private NotifyRequest notifyRequest = null;

        @PostConstruct
        public void notificationListener() throws Throwable {

            System.out.println("Notification****************");
            try
            {

                Class.forName("com.impossibl.postgres.jdbc.PGDriver");
                String url = "jdbc:pgsql://192.xx.xx.126:5432/postgres";

                Conn = DriverManager.getConnection(url,"postgres","password");
                this.pgConnection = (PGConnection) Conn;

                System.out.println("PG CONNECTON: "+ pgConnection);
                Statement listenStatement = Conn.createStatement();
                listenStatement.execute("LISTEN notify_channel");
                listenStatement.close();

                pgConnection.addNotificationListener(new PGNotificationListener() {

                    @Override
                    public void notification(int processId, String channelName, String payload){

                        System.out.println("*********INSIDE NOTIFICATION*************");

                        System.out.println("Payload: " + jsonPayload);

}

So as my AS is up, I have configured that at startup the listener class is called and it's start listening on the channel.

Now this works fine if like say for testing I edit my table in DB manually, the notification is generated and the LISTENER receives it.

However, when I programmatically send a UPDATE request on the table, the UPADTE is performed successfully but LISTENER is not receiving anything.

I feel my connection of the LISTENER goes down when I send a request (it also makes a connection to edit entities), but I am not sure. I read about permanent connections and pooled connections, but not able to decide how to pursue that.

I am using pgjdbc (http://impossibl.github.io/pgjdbc-ng/) jar for async notifications as jdbc connection requires polling.


texts: MySQL - Unix timestamp from columns with hour, day, month

texts:
I have MySQL table with columns (day, month, hour). Is it possible to build unix timestamp from this within the query itself?

I am looking for something like this:

SELECT unix(2016, a.month, a.day, a.hour, 00, 00) as d
FROM a
WHERE d > 456456 AND d < 789789

Values 456456 and 789789 is provided from an application. I know, I can create unix timestamp from STR_TO_DATE, but then I have to create it for every comparison in WHERE clause as well, which I am afraid will be slow.

Create table:

CREATE TABLE `a` (
  `data_a` smallint(6) NOT NULL DEFAULT '0',
  `hour` tinyint(4) NOT NULL,
  `day` tinyint(4) NOT NULL,
  `month` tinyint(4) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

MySQL version 5.0


texts: Can database backup and restore using pgdump resolve transaction wraparound in postgresql?

texts:
I know the solution advocated is VACUUM. However, I was interested in knowing whether old versions of XIDs are marked as frozen when restoring a database dump  using pg_dump.

Also, this blogpost mentions that the transaction wraparound issue was resolved after truncating one of the large tables that had not been auto-vacuumed. How did the truncation help?


texts: Is it safe to delete the default "postgres" role?

texts:
I'm trying to understand if it's okay for me drop the "postgres" role, or at the least, remove it as the superuser.
Thanks!


texts: count distinct column without null value in MySQL 5.7

texts:
how to distinct not contain null in MySQL, if I have a dataset like this:
a b
1 null
2 1

I want to using sql like this:
select count(distinct b)
fromt table

the expect result may output 2, but I want the count distinct result not contain null. I could not using filter in the where clause because I should using other column to sum as result, if I filtered result first, the result is not correct. My real sql like this:
select

        sum(transin_num) as transinNum,
        case when count(distinct transin_user_num) -1 < 0 then 0 else count(distinct transin_user_num) -1 end as
        transinUserNum,
        sum(transin_count) as transinCount
        from (

        select
        case when consume_item in('PLATFORM_IN') then ABS(consume_num) else 0 end as transin_num,
        case when consume_item in('PLATFORM_IN') then user_id else -1 end as transin_user_num,
        case when consume_item in('PLATFORM_IN') then 1 else 0 end as transin_count

        from wallet_consume_record
        where created_time < 1595951999999
        and created_time > 1595865600000
        and tenant_id = 84
) a

I want to calculate transin_user_num without the -1 value.Is it possible to do like this in MySQL 5.7?


texts:
I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day.  These rows have built up over 3 years and they are currently growing at approx 2 million every 4 weeks.

There are 7 indexes (3 heavily used and least fragmented/ 4 mostly unused but heavily fragmented at about 75-85% and the columns are heavily updated).  There was an SP that was scheduled to archive record stubs to another table but we have had to turn this off as it was interfering with the backups and causing a failover and outages on another system.

Ideally we only want 1 years worth of data in this table (approx 25-30m rows) and i'd like to disable the 4 unused indexes.  I've read that before i disable them they should be rebuilt but i'm not sure whether to do this before or after i've archived the previous 2 years worth of data.

A redesign has been on the cards for a few years but i'm now a team of 1 with limited knowledge of indexes/SQL tuning etc and trying to do my best with very limited resources.

The table is heavily used between 7am and 1am so most work needs to be a site outage or done in the early hours of the morning to limit downtime to users.

Any suggestions would be welcome.  Thanks.


texts: SQL Server splits A  B into A  B, yielding strange results if B is non-deterministic

texts:
We have encountered an interesting issue with SQL Server. Consider the following repro example:

CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');

SELECT s_guid FROM #test
WHERE s_guid = '7E28EFF8-A80A-45E4-BFE0-C13989D69618'
  AND s_guid <> NEWID();

DROP TABLE #test;

fiddle

Please forget for a moment that the s_guid <> NEWID() condition seems entirely useless - this is just a minimal repro example. Since the probability of NEWID() matching some given constant value is extremely small, it should evaluate to TRUE every time.

But it doesn't. Running this query usually returns 1 row, but sometimes (quite frequently, more than 1 time out of 10) returns 0 rows. I have reproduced it with SQL Server 2008 on my system, and you can reproduce it on-line with the fiddle linked above (SQL Server 2014).

Looking at the execution plan reveals that the query analyzer apparently splits the condition into s_guid < NEWID() OR s_guid > NEWID():

...which completely explains why it fails sometimes (if the first generated ID is smaller and the second one larger than the given ID).

Is SQL Server allowed to evaluate A <> B as A < B OR A > B, even if one of the expressions is non-deterministic? If yes, where is it documented? Or did we find a bug?

Interestingly, AND NOT (s_guid = NEWID()) yields the same execution plan (and the same random result).

We found this issue when a developer wanted to optionally exclude a particular row and used:

s_guid <> ISNULL(@someParameter, NEWID())

as a "shortcut" for:

(@someParameter IS NULL OR s_guid <> @someParameter)

I am looking for documentation and/or confirmation of a bug. The code is not all that relevant so workarounds are not required.


texts: How to drop java source from Oracle database properly?

texts:
I'm a little stuck here with my Java Source. First I want completly drop it from the database, because I want to recompile it, but I can't drop is, because if I execute this command:

drop java source "SCHEMA.JAVASOURCENAME";

I get this error:

ORA-04043: object JAVASOURCENAME does not exist

The Javasource itself has this structure (this is the plsql/java code for it):

create or replace and compile java source named myjavasource as

import java.io.*;
import some.custom.jar.one.*;
import some.custom.jar.two.*;
import some.custom.jar.three.*;

    public class MyJavaClass
    {
      public static String myjavafunction(String connectString, String identity, String password)
      {
       ...
      }
    }

I executed this "create or replace and compile java source..." command as DBA and with my user as well, I don't know if this can be the problem.

So my question is, how can I drop this java source?

My second question is who can I handle the custom jars? I imported them with this command:

call dbms_java.loadjava('-genmissing -r -v -force -grant SCHEMA ./some.custom.jar.one.jar');

I also executed this as DBA and with my user. If is check the invalid objects:

SELECT *
    FROM user_objects
    WHERE object_type like '%JAVA%'
    AND status = 'INVALID'

as DBA I see the whole world invalid (lot of Java classes), but when I check this as user I only get the javasource and the java class name in the invalid list (I think it needs some more external jars, what I can't find right now).

So, why is the invalid java object list different as DBA and as user? Do I need to execute the Java source creation and the jar imports as DBA (and ONLY with DBA), or with the database user if I want to use it (the java funtion, what is using the external jars) with all schema users?

Thank you very much!


texts:
I've executed the following queries:

GRANT USAGE ON my_db.* TO my_user@127.0.0.1 IDENTIFIED BY 'pass';
GRANT ALL ON my_db.* TO my_user@127.0.0.1;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO my_user@127.0.0.1;
FLUSH PRIVILEGES;

But they don't show up.

SHOW GRANTS FOR my_user@127.0.0.1;

results in

GRANT USAGE ON my_db.* TO my_user@127.0.0.1 IDENTIFIED BY '*123...';
GRANT ALL PRIVILEGES ON my_db.* TO my_user@127.0.0.1;


texts: PostgreSQL B-tree, good monotonic values?


texts:
When I did my first interview, I faced this confusing question.
By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql?

Please suggest me the best answer.


texts: Synchronize Queries Retrieving Sessions And Locks From DMVs

texts:
I have a SQL script that I run when I want to see what is happening in the database.  The script has many queries that return information from DMVs, but the two I use most are "Requests"  (sys.dm_exec_requests, sys.dm_exec_sessions, etc.) and "Locks" (sys.dm_tran_locks).  The output is similar to SQL Server Activity Monitor, but it displays more information.

Sometimes, a request appears in the Requests query, but it completes before the Locks query runs.  For example, the Requests query may show SPID 51 is waiting on a lock resource, but the Locks query does not include any lock information for SPID 51.  (I know about the wait_type and wait_resource columns from sys.dm_exec_requests.)

Is there a way to ensure that these two separate queries display a coherent snapshot of database activity?

I expect commercial database monitoring applications must encounter the same problem.

I have experimented with running these queries in SERIALIZABLE concurrency and added locking hints to the DMV joins, but the queries acquire no locks.  I would not want to do this in production anyway.

The best ideas I have so far are:

Run these queries simultaneously from different sessions.
Join requests and locks together in one query.  Considering that I have seen over 100,000 locks at once, this join would return a lot of duplicate request and session data, but it might work.

I am not familiar enough with extended events to know if they would work better-- event pairing, maybe?


texts: Is it safe to run optimze on mutliple tables in parallel in mysql (percona)

texts:
I have a slave on which no query is coming. Lot of data has been archived and deleted from almost all tables. Size of tables vary from 80Gb to few Mbs. And there are around 130 such tables.

Mysql (Percona) version is "Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1"

I am running optimize on them, one at a time, but since optimization is a time consuming process, would it be Okay if I run optimize on multiple tables (say 3 to 4) at a time? I have enough disk space for 3 to 4 tables to be copied in the same partition.

I am assuming optimize on one table simple locks that table alone and has no effect at all on the other tables, hence it would be perfectly safe. But any caveats and former experience is dealing with this is welcomed.


texts: How to change Postgres primary key column values?

texts:
My postgres database has a column called "id" that runs from 40,000,000 to about 50,000,000. The "id" column is the primary key. I need to change the "id" column values such that they span different numbers in order to merge this database with another.

How can I go about generating code to change the values from 40,000,000 - 50,000,000 to, say, 0 - 10,000,000?

The table definition is

CREATE TABLE public.keyvaluehistory (
   id bigint NOT NULL
      DEFAULT nextval('keyvaluehistory_id_seq'::regclass),
   segkey text NOT NULL,
   dvalue double precision,
   bvalue bytea,
   tstamp timestamp with time zone,
   CONSTRAINT keyvaluehistory_pkey PRIMARY KEY (id)
);

There are no foreign keys on the table.

I can afford downtime on the order of minutes/hours.


texts: MariaDB Column Store Suitability for Data Warehouse

texts:
We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column Store an attractive option. However, we are all non-professionals in the field of data warehousing. That’s why I am would like to hear some opinions on the applicability and hardware required.
The data from the source systems (Oracle, IBM DB2, MariaDBs) sum up to 1 TB (including PK/FK and indexes) containing 36 months of data history. The data consist of tables with mostly integer keys, a mix of integers/doubles (65%), mostly shorter VARCAHRs (30%) and 5% of GEO-data. The data comes from 15-20 different topics with different weights. Meaning some make 50GB some 5GB in the data warehouse. Not all topics (tables) can and will be joined, because there are simply no common keys. For the reporting case, the different topics will mostly be queried isolated (max. five topics will be joined). To query all time points (months) will be used seldomly. I think it is reasonable to assume up to 17 months of data will be quered for product performance comparison. New data will be written overnight in batch jobs.
The MariaDB database server is planned to run on a 8 phy. core machine with 1,25 TB of SSD storage and 64GB of RAM. In total, we expect about 50 people querying the data, 45 of which use aggregated reporting tables and 5 are heavy analytics users – joining tables, grouping, exporting stuff to other ML tools. Complex string searches are not in our scope for the data warehouse. We do not expect the 45 people to query the data at the same time, but up to 20 concurrent users can be possible. We do not want basic reporting queries to take 10 seconds or longer. More complex queries for the 5 analytical users can take longer.
Some considerations we had and I would be happy to get your opinions on:

Do commercial vendors offer dramatically better solutions than MariaDB column store, which we should consider for our case? Or is it possible to do it with an open source RDBMS?
Does it make sense to have one stronger machine, or 2-3 weaker ones balancing the load a little better? Would they then need a full copy of all data? (so 1,25 TB SSDs x 3 Workers?).
Does it make sense to not have one big SSD, but five (256 GB each) smaller ones to increase IO? (Saving topics of similar tables on the same SSD, because they will be joined more frequently.)
Does it make sense to have 64GB or RAM, or is more needed? What are the determinants of RAM needed?

As you can see, our lack of experience causes some fundamental questions of the hardware required. Is it even possible with the information provided to draw conclusions on the possible performance? If not, what kind of information is needed?


texts: Query for get column number in PostgreSQL

texts: I want to know how can I get column number of a table in PostgreSql? For example, If we have a table with id, name, age columns, the age column number is 2. I can do this with array and loop in C++(using libpqxx). But I want to know is there a better way to get this with simple and direct query?


texts: Why MSDB transaction log file grew while MSDB was in Simple recovery mode?

texts:
I have Availability Group of 3 servers, and transaction log backups for databases in Availability Group are taken on a secondary replica, each 1 minute

It was working like this for several months

  Now MSDB on this secondary replica is 6 GB data file size (mdf) and is only 4% full and log file size is 14.2 GB (ldf) and is 7% full

I can only remember that I made MSDB 500 MB both data and log files in the beginning when server was setup...

I thought log file grew because of often transaction log backups and FULL recovery mode - but no, MSDB has SIMPLE recovery mode on that server

So it left me wondering, what caused both data and log file growth for MSDB ?


texts: Is it efficient to use plpgsql functions only to validate identifiers and values?

texts:
Will my queries be safe from SQL injections if I use plpgsql functions only to validate identifiers and values, instead of executing queries.

Here are the example functions:

CREATE OR REPLACE FUNCTION validate_identifier(identi TEXT)
  RETURNS TEXT AS
$func$
BEGIN
  RETURN quote_ident(identi);
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION validate_value(val TEXT)
  RETURNS TEXT AS
$func$
BEGIN
  RETURN quote_literal(val);
END;
$func$ LANGUAGE plpgsql;

then i.e. simply use query:

SELECT * FROM some_table WHERE validate_identifier('user_input_identifier') = validate_value('user_input_value');

Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt?


texts:
The /dev/mapper/vg_db1-lv_root partition used 100%,but the other partition is free,why mongodb store data only use this partition. This is why? How can I solve this problem?

[root@db1 shard11]# df -alh
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg_db1-lv_root   50G   48G     0 100% /
proc                           0     0     0    - /proc
sysfs                          0     0     0    - /sys
devpts                         0     0     0    - /dev/pts
tmpfs                       1.9G     0  1.9G   0% /dev/shm
/dev/sda1                   485M   40M  420M   9% /boot
/dev/mapper/vg_db1-lv_home  3.6T  197M  3.4T   1% /home
none                           0     0     0    - /proc/sys/fs/binfmt_misc

The fdisk -h result :

[root@db1 shard11]# fdisk -l

Disk /dev/sda: 1999.0 GB, 1998998994944 bytes
255 heads, 63 sectors/track, 243031 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0001c91f

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64      243032  1951634432   8e  Linux LVM

Disk /dev/sdb: 1999.0 GB, 1998998994944 bytes
255 heads, 63 sectors/track, 243031 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000ebd30

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1      243032  1952146432   8e  Linux LVM

Disk /dev/mapper/vg_db1-lv_root: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_db1-lv_swap: 4160 MB, 4160749568 bytes
255 heads, 63 sectors/track, 505 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_db1-lv_home: 3939.6 GB, 3939621666816 bytes
255 heads, 63 sectors/track, 478965 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

And Why the two results are inconsistent?


texts: AWS DMS creates tables with no auto_increment

texts:
I am migrating my databases to Amazon's RDS using their DMS tool.  Pretty much everything looks great except that columns that were marked as auto_increment on the source database did not get marked as auto_increment on the target database.  I also noticed that my indexes and foreign key constraints were not included. I am running version 5.6 on both ends.

Is there a way to get DMS to include the full create tables for my database?


texts:
I have dates in 2 tables. Table student is as below:

ID|Name|Examdate
1  John 01/10/2015
2  Fred 11/12/2014

Table retake as below:

ID|RetakeDate
1  02/10/2016
2  05/11/2015

The IDs in retake relate to the students' IDs in student, i.e. they are the same.

I am trying to create another table where only students who have taken a retake after 12 months or more exist. The difference between examdate and RetakeDate needs to be >= 12months.

How do I achieve this?

There could be multiple rows in both tables for the same student. There will be multiple rows on both tables, as this will allow me to see how many times they have taken a retake and how many times an exam. At this moment in time I don't want to see if the retake is for the latest exam. I just want to see how many retakes they have taken all together.
End result should be comparing each Exam date with Retake date. Some more explanation:

  Q: So  if a student has dates in 2012, 2013, 2014 and then 2 retakes in 2016?

  A: There should 3 rows in the result (one for each date)

  Q: What if it's three exams – in 2012, 2013, 2014 – and (a) two retakes – in 2014 and 2016? or (b) one retake – in 2014? What should be expected as the result in each of those cases?

  A: It doesn't matter. As long as the (a) retake was taken after 12 months or more, they should appear in the result.


texts: Why does a log file grow even when transaction is not commited?

texts:
Below is what I did:

Created database in simple recovery mode.
Created table.
Began tran and inserted so many rows to exceed size of the log file.
Committed the transaction.

Before step 4 I noticed that log file grew, which confused me. Why did that happen?

According to MSDN:

  For each logical write, a transaction log record is inserted in the log cache that records the modification.

And then:

  Log records are written to disk when the transactions are committed.

So how do you explain the fact that the log file is growing when the data is being inserted, even when a transaction is not committed?

As I understand, uncommitted log records should be stored in the log buffer, not on disk.


texts: How to disable Oracle OCI client transparent reconnect

texts:
I have an application I wrote that uses OCI to connect to an Oracle database.  The database has recently been upgraded to Oracle 18, and since then database searches in the application randomly stop working.

I have narrowed the problem down to this.  When the app connects to the DB it runs some ALTER SESSION commands to disable case-sensitivity in LIKE conditions (so that I can search for "smith" and still find "Smith").  After some hours, the network drops the TCP connection.  The next morning when someone tries to access the system, the Oracle client transparently reconnects and executes the query.

The problem is that this reconnection happens without my code knowing about it, apparently by the Oracle client itself, and it does not re-run the original ALTER SESSION commands after reconnecting.  Our DBAs have supplied me with an audit log that shows the session ID changing, a new LOGON attempt and a single ALTER SESSION running to set the timezone (which does not come from my code, and also appeared in the original connection).

This means that my application issues queries to the database as per normal, unaware that there has been any interruption, but suddenly they become case-sensitive and the users start complaining that the search isn't returning any results.

With the previous Oracle server version my code would receive an error if it had been disconnected, and the code would try to reconnect and as part of the reconnection, it would run the ALTER SESSION commands again so that there was no change to the behaviour of the system.

I can't find anything about this auto-reconnect feature or how to adjust it, so is there some way to either preserve the ALTER SESSION changes so they are re-applied after the reconnection, or to disable this behaviour and return an error to the code if the connection is lost as used to happen?

The client is an older 11.2 one and upgrading it isn't really practical, but since it worked before the server upgrade I would've thought that version would be ok.


texts: Exporting database Oracle 7.3 on Windows NT 3.51

texts:
How to take dump files on Oracle7.3 running on Windows NT 3.51?

Should you stand in the Orant/bin folder and run one of the programs there like>

exp73 SYSTEM/password@[dbinstance] FULL=Y LOG=EXPORT.LOG

Did not work

These services:


texts: TSQL to find if server is listening on dynamic or static port

texts:
Using TSQL, how can I find if the MS SQL server (any version) is configured to use static port or is it using dynamic port?

Script i am trying to use:

DECLARE @TcpPort VARCHAR(5)
        ,@RegKey VARCHAR(100)

IF @@SERVICENAME !='MSSQLSERVER'
    BEGIN
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    END
    ELSE
    BEGIN
        SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP\IPAll'
    END

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpPort'
    ,@value = @TcpPort OUTPUT

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpDynamicPorts'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
        ,@@SERVERNAME AS ServerName
        ,@@SERVICENAME AS ServiceName


texts: How to send Unicode symbols using SQL Server Database Mail?

texts:
How can I send Unicode symbols using SQL Server Database Mail? My goal is to send a symbol on the subject of an email. Let's say that, for example, I want to send this  " Subject with symbol" on the subject. The symbol I want to use come from Segoe UI Emoji font.

Thanks!


texts:
Can we patch Microsoft SQL server using WSUS?

Is it not recommended?


texts: How do I add a composite check constraint that references a composite key?

texts:
Given the following tables

CREATE TABLE users (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  organization_id  TEXT NOT NULL,
  UNIQUE (name, email)
);

CREATE TABLE tasks (
  id               SERIAL PRIMARY KEY,
  users_id         TEXT NOT NULL,
  organization_id  TEXT NOT NULL,
  description      TEXT NOT NULL
);

Is it possible to create constraints for 'user_id' column , that i can use values form 'users' table only with the same organization_id?


texts: psql: fatal database does not exist error

texts:
I have a linux user account, postgresql role, and associated database, all called foo.

from the linux user foo, I ran the command

dropdb foo

Now when I try to run psql from the linux user foo, I get the error...

psql: FATAL:  database "foo" does not exist

So does this mean that when running psql from a linux user account, there must be a database associated with that linux user account?


texts: last modified rows timestamp for SQL Server 2016

texts: Is there a feature/script to check when was the last time a row has been modified/changed in SQL 2016+ tables?Does SQL track this automatically or do we have a feature which gives this info? Please let me know your thoughts/


texts:
I am using this system on which everything was already configured. I want to connect to local db on SQL Plus. When I enter user-name scott and password tiger, it gives ORA-12560:TNS:protocol adapter error.

Enter user-name: scott
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

When I try with one line connection string scott/tiger@localhost:1521/orcl, it gives ORA-12541: TNS:no listener.

Enter user-name: scott/tiger@localhost:1521/orcl
ERROR:
ORA-12541: TNS:no listener

I am new to this.


texts: Which schema is better for a shopping project?

texts:
I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized properties for different products.

I'm looking for a reasonable solution for handling different types of products like cell phones and air conditioners. For example a cell phone has a property called CPU whereas an air condition doesn't have that property. Also an air condition has a property called BTU whereas a cell phone doesn't have a property like BTU.

The thing I want to do is a classic category based product system. Users will be able to create categories that contains different fields like CPU of BTU and when they want to create a product, they will be able to enter values for these fields to that product.

The SQL world, I see that many projects use a schema like this one:

products -> (id, name, brand_id, description, category_id)
categories -> (id, name)
category_fields -> (id, category_id, field_name, field_type)
product_fields -> (product_id, category_field_id, value)

However I wonder that if there is a better solution for this problem. There are two different solutions came into my mind.

products -> (id, name, brand_id, description, category_id, (json) category_values)
categories -> (id, name)
category_fields -> (id, category_id, field_name, field_type)

As you may noticed there is a column called category_values in products table. This column is JSON type (which Postgresql has) and I will index the keys that I need to search.

The other schema:

products -> (id, name, brand_id, description, category_table)
products_cellphones -> (product_id, memory, cpu, gps ...)
products_...

In this schema, when a user creates a category the system will create a new table with its fields on database in runtime. And I will create a view that generates the sql selects on the fly and executes them.

Which way should I choice, or do you have any other solutions?


texts: MySQL performance issue with ST_Contains not using spatial index

texts:
We are having what seems to be a fairly large mysql performance issue on trying to run a fairly simple update statement. We have a table(1.8mil) with houses that contains a Lat+Long geometry point column(geo), and then a table(6k) that has a list of schools with a boundary geometry polygon column(boundary). We have spatial indexes on both, we are trying to set the school's id, that contains the point, to the house table with the update. The update is taking 1 hour and 47 minutes to update 1.6mil records. In other systems I have used in my paste experience, something like that would take just a few minutes. Any recommendations?
I have posted this same question in the GIS SE site as well, as it is very much a GIS & DBA question.
CREATE TABLE houses (
  ID int PRIMARY KEY NOT NULL,
  Latitude float DEFAULT NULL,
  Longitude float DEFAULT NULL,
  geo point GENERATED ALWAYS AS (st_srid(point(ifnull(`Longitude`,0),ifnull(`Latitude`, 0)),4326)) STORED NOT NULL,
  SPATIAL INDEX spidx_houses(geo)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE schoolBound (
  ID int PRIMARY KEY NOT NULL,
  BOUNDARY GEOMETRY NOT NULL,
  reference VARCHAR(200) DEFAULT NULL,
  type bigint DEFAULT NULL,
  INDEX idx_reference(reference),
  INDEX idx_type(type),
  SPATIAL INDEX spidx_schoolBound(BOUNDARY)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

-- type 4 means it's a elementary
Update houses hs
    INNER JOIN schoolBound AS sb ON ST_Contains(sb.boundary, hs.geo) AND sb.type = 4
SET hs.elementary_nces_code = sb.reference

The explain seems to show that it is not going to use the spatial index for schoolBound.
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+
|  1 | SIMPLE      | sb    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6078 |    10.00 | Using where                                    |
|  1 | UPDATE      | hs    | NULL       | ALL  | spidx_houses  | NULL | NULL    | NULL | 1856567 |   100.00 | Range checked for each record (index map: 0x4) |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+


texts: SQL Server Always Encrypted - Create an external table containing an encrypted column

texts:
I'm trying to create an external table MyTable on server S2 that references the table MyTable from server S1.
The table MyTable contains a column encrypted using Always Encrypted. I'd like the column to be unencrypted when querying MyTable from S2.
Is it something that can be accomplished? I haven't found anything about it in the documentation (if someone knows where to find it, I'd be happy to just read the doc)... I've tried the naive approach bellow but obviously it doesn't work.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myEncryptionPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCred
WITH IDENTITY = 'myAdminAccount',
SECRET = 'mySuperStrongPassword';

CREATE EXTERNAL DATA SOURCE RemoteServerRef
WITH
(
    TYPE=RDBMS,
    LOCATION='somewhere.on.the.inter.net',
    DATABASE_NAME='myDatabase',
    CREDENTIAL= MyCred
);

CREATE EXTERNAL TABLE [dbo].[MyTable]
(
    [Id] [uniqueidentifier] NOT NULL,
    [Field2] [int] NOT NULL,
    [Field3] [datetime] NOT NULL,
    [Field4] [decimal](18, 2) NULL,
    [EncryptedField1] [nvarchar](128) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [EncryptedField2] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
)
WITH
(DATA_SOURCE = RemoteServerRef)

Does anybody know how to do that? As a bonus question, can someone explain to me what the CREATE MASTER KEY ENCRYPTION BY PASSWORD is used for (and does it have anything to do with the first problem)?

Thanks for your help!


texts:
I have detached a database and had the systems team rename the drive letter (from E: to P:). When I try to attach the database again, it's looking for the original drive.

I don't have a backup of the database. Is there any way I can change the system tables where the actual physical locations of the files (.mdf) are stored?

Are there any alternative solutions now?

Just to be clear: It's my mistake that I approved the systems team to change the drive letter.


texts: Inner join in select statement within stored procedure

texts:
I'm trying to Create a stored procedure but there is a field actually exist in another field but its id is used as foreign key in current table, SQL Query is:

Create Procedure sp_Select
As
Begin
    SELECT [ProductID]
           ,[Name]
           ,SELECT tblCategories.CategoryName
            FROM  tblCategories INNER JOIN tblProducts
            ON    tblCategories.CategoryId = tblProducts.CategoryID;
           ,[Model]
           ,Cast([RAM] As nvarchar(10)) +' '+[RAMMemUnit] As RAM
           ,Cast([SecondryMem] As nvarchar(10)) + ' ' + [SecondMemUnit] As Memory
           ,[OSVersion]
           ,[Processor]
           ,CAST([Battery] As nvarchar(10)) +' '+ [BatteryType] As Battery
           ,UnitsInStock  AS 'Quantity In Stock'
    FROM [tblProducts]
End

Error in this following portion:
SELECT tblCategories.CategoryName FROM tblCategories INNER JOIN tblProducts ON  tblCategories.CategoryId = tblProducts.CategoryID;

Someone help me please.


texts: Enforcing distinct number of trailing spaces

texts:
I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server.

Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?

The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.


texts: Azure SQL Database patch and update doubts

texts:
It is known that Microsoft automates the patch and update of Azure SQL Database. But, some times applications must be tested before being applied any type of patch or update, because there's a possibility to get a failure in the application.

It's is possible for the customer to reverse a patch, or test it and authorize it before being applied?


texts: Find the user with closest taste of mine

texts:
I trying to write in SQL the following requirement:

Each user can rate as much films as they like. (1= I don't like, 5=I love it)
And the system should provide to the current user a list of films that he doesn't rate yet and that other users like too.

To simplify, I have this table and the following data:

create table wich (
  uid int,
  film varchar(50),
  rate int
);

insert into wich values
(1, 'usual suspect', 5), (1, 'gataca', 4), (1, 'goldeneye', 2),
(2, 'usual suspect', 4), (2, 'gataca', 5), (2, 'i am a legend', 4), (2, 'the hobbit', 1),
(3, 'usual suspect', 1), (3, 'gataca', 5), (3, 'goldeneye', 5),
(4, 'usual suspect', 5), (4, 'goldeneye', 5),
(5, 'usual suspect', 5), (5, 'gataca', 4), (5, 'goldeneye', 5),
(6, 'usual suspect', 4), (6, 'gataca', 3), (6, 'goldeneye', 3), (6, 'shrek', 4);

This can be read as: User 1 like 'usual suspect' and 'gataca' but not 'goldeneye'.

So what I want know is to find the user(s) that have the closest tastes of the current user and get a list of films that the current user could probably like.

Following, what I have done so far:

Step 1: for every films rated by user 1, if user1 and user2 rate the film in the same way, compute a score (should be 0 if the difference is greater than 2)
and it's great if both like a film or both doesn't like a film.

select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
        from wich w1
        inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
        where w1.uid=1

Step 2: compute a score by user

select scores.uid, SUM(scores.score) * 100 / sum(scores.nb) as score
from (
    select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
    from wich w1
    inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
    where w1.uid=1
) scores
group by uid
ORDER BY score desc

step 3: find films that user1 has not yet rated that he could like

select w3.uid, w3.film,w3.rate, matches.score
from (
    select scores.uid, SUM(scores.score) * 100 / sum(scores.nb) as score
    from (
        select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
        from wich w1
        inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
        where w1.uid=1
    ) scores
    group by uid
    ORDER BY score desc
) as matches
inner join wich w3 on matches.uid=w3.uid and w3.rate >= 3
left join wich w4 on w4.uid=1 and w3.film=w4.film
where w4.uid is null
order by w3.rate desc, matches.score desc;

This seems to work, but I am not sure that this will still respond in a small amount of time if each user rate of lot of films and the table become become bigger.

What do you think ?

Is there a better way to accomplish such a thing ?

A working fiddle: http://sqlfiddle.com/#!2/89c57/1/0

Edit:

In my real table, the film column is an integer and I have a second table with films data (title, description, year, ...)

create table wich (
  uid int,
  film int,
  rate int
);

The example given here is just to simplify the problem with just one table.


texts: Query user session length on a timestamped activity log

texts:
I'm trying to retrofit some analytics on a customer's MySQL 5.5 table.

Given a simple but large table:  | user_id | timestamp |

A session_length is defined as the longest number of minutes without an hour of idle time.

When I run some SQL query, I get user_id, session_length for each span of time a user is using the system without an hour of idleness.

Example table:

1 | 01:10
1 | 01:11
1 | 01:12
1 | 01:13
1 | 01:14
1 | 01:15
              ...
1 | 03:59
              ...
1 | 12:10
1 | 12:20
1 | 12:30
1 | 12:40
1 | 12:50

Should result in:

user_id | session_length
1       | 5
1       | 1
1       | 40

I'm not worried about time delta granularity.

I found a hint that this is dealt with in other db's with LAG functions. No such luck in MySQL.


texts:
We are planning to upgrade from MySQL 5.6 to 8.0 and are considering disaster recovery plans. We have three separate databases within our MySQL 5.6 instance which are 200Gb, 170GB and roughly 9GB. Here are a few that come to mind. If anyone has any suggestions or comments, please let me know:

Image the VM which the MySQL instance runs on to backup the MySQL configuration files and .ibd files
Backup MySQL configuration files and .ibd/.frm files. If issues are to appear during the upgrade process we can revert the installation files via yum, install 5.6 again and copy back the configuration and .ibd/.frm files. Is this bad practice?


texts: How can I prevent the SQL Server Query Optimiser splitting a remote query?

texts:
I am using Microsoft SQL Server and I have a query which reads data from a remote server. This query reads data from 3 different tables across two different databases, all of which are on the same remote server.

When I run this query in isolation as a SELECT in SQL Server Management Studio, and inspect the execution plan - I can see that the entire query is being executed as a single remote query and the whole thing takes about 7 seconds to run.

If I attempt to INSERT the results of this SELECT into a temporary table, the execution plan completely changes. It runs 3 separate queries on the remote server, then merges the results locally - and takes nearly a minute to run.

Is there any way I can persuade the Query Optimiser to stick to the first, faster option!?

Edit:
Thanks for the requests for the execution plans - here they are:

SELECT Only: https://www.brentozar.com/pastetheplan/?id=rygMoFHM8

INSERT (using the same SELECT): https://www.brentozar.com/pastetheplan/?id=S1EZhYBGI


texts: How to do mysql 8.0.24 master-slave replication switch?

texts: I am using two machines for mysql 8.0.24 master-slave replication, if the master database hangs, what action can the slave database take to become the master?


texts: SQL Server daily backup and restore strategy

texts:
We are in an environment where we need to restore the production db the size of around 600GB from daily backup to our UAT server.

We have currently do full backup and restore and as database grows it has slowed down the overall performance. Im not a DBA and wonder whats the best strategy to optimise this process.

Occasionally we also have experience very slow restore even after I enabled the instant file initialisation. Its processing in a normal speed but all of sudden from like 50 percent it processes extremely slow. Any idea where to begin the investigation?

The backup is compressed. The database only has about 43 VLFs. We just want to get production data in UAT. I have heard from someone it's better to use differential back up for our scenario but one thing I am not still clear is why it randomly slows down in the middle of restore.


texts: How Can I Measure How long a query takes on Azure SQL Data Warehouse

texts:
In traditional SQL Server I can get CPU and Elapsed Time by setting the following

set statistics time on

When I try that on Azure SQL Data Warehouse I get the following error

Msg 103010, Level 16, State 1, Line 19
Parse error at line: 1, column: 5: Incorrect syntax near 'statistics'.

What options do I have for getting similar diagnostic information from SQL Data Warehouse


texts:
I have one database and password file
Now I'm creating a new database, do I need to create another password file or not?

I'm a beginner in Oracle, maybe a bit stupid question.
Thanks in advance


texts:
In layman terms, what does it mean to have "Active/Active"?  What are the steps to achieve this configuration?

Edit:
We currently have an "Active/Passive" setup which isn't efficiently making use of the hardware so we need a "Active/Active" configuration.


texts: How to read Mongodb entries in plaintext?

texts:
I'm testing mongodb on a local machine for the first time, and I just want to see the actual plaintext contents of a database entry.

Started the mongo console:

$ mongo

Set dbs:

> use <my dbs name>

Searching entries:

> db.<collection name>.find({"_id": "486b4ca55b144b119f4551417c5798c8"})

{ "_id" : "486b4ca55b144b119f4551417c5798c8", "date" : ISODate("2017-04-20T05:47:06.176Z"), "content" : ObjectId("58f84b5a0318762cb6e230a6"), "digest" : "d064a874c872d75f6ab67c04d8b79b26f65f9b4f", "short" : "5f9b4f", "size" : 2374694 }

This is as far as I've gotten. The entries are pastebin uploads. I'm able to locate entries with various search queries but it doesn't actually show me what was uploaded. Is this possible? Am I going about this the hard way?


texts:
My user has the two roles sysdba and sysoper. When I connect as sysdba I can sucessfully perform in sql plus:

@> connect myuser/mypassword@myservice as sysdba;
Connected.
SYS@service> CREATE OR REPLACE DIRECTORY DP_TMP_5714 AS '/path/to/dir';

Directory created.

However, when I open session as sysoper It performs as follows:

@> connect myuser/mypassword@myservice as sysoper;
Connected.
SYS@service> CREATE OR REPLACE DIRECTORY DP_TMP_5714 AS '/path/to/dir';
CREATE OR REPLACE DIRECTORY DP_TMP_5714 AS '/path/to/dir';
*
ERROR at line 1:
ORA-01031: insufficient privileges

I already run the grant command:

GRANT CREATE ANY DIRECTORY TO myuser


texts: Excessive CPU Usage caused by long running query?

texts:
We have been getting intermittent excess CPU usage at 100%. There is not a whole amount running on the system.
We also get warning of Batch Requests/sec above threshold.: 1030, which would seem to point to CPU issues.

The server has the below...

6 CPU cores
56 GB Memory

On the server this is what I see..

The queries running using sys.dm_exec_requests are below. Is this high CPU caused by that first query that is 14mins in cpu time? Why would it throttle all CPUs?


texts: How to find the nth highest salary in SQL?

texts:
I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?

EmpID         Salary
1             90000
2             80000
3             54000
4             37000
5             12000
6             69000
7             50000

SELECT * FROM Employee E1
WHERE (N-1) = (
                SELECT COUNT(DISTINCT(E2.Salary))
                FROM Employee E2
                WHERE E2.Salary > E1.Salary
              )

If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!


texts: psql "invalid client_encoding" error on OS X, PostgreSQL 9.4.5

texts:
I'm currently running a OS X Lion Server system which ships with a built-in and not-upgradable PostgreSQL version.
After years of usage I've finnaly decided to leave the built-in version and install an indipendent version. I disabled the built-in installation and downloaded the installer from EDB and followed the wizard. After many issues reguarding encoding and locales, I've finally managed how to setup a DB with no locale and UTF8 encoding. I issued the following command:

initdb -D /path/to/data --no-locale --encoding=UTF8

If I connect using pgAdminIII I get no problems. The command show client_encoding; displays UNICODE as the encoding used by pgAdminIII (the default installation gave me a SQL_ASCII encoding and that's why I run the initdb command).

The problem is that I'm not able to connect to PostgreSQL using psql. Whatever I pass to it, I get the following error:

psql: invalid connection option "client_encoding"

I've searched through the Internet but found nothing that solves my problem (for example issuing env PGCLIENTENCODING=UTF8 and adding client_encoding=UTF8 to postgresql.conf).

otool -L /Library/PostgreSQL/9.4/bin/psql returns:

/Library/PostgreSQL/9.4/bin/psql:
    @loader_path/../lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.7.0)
    @loader_path/../lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    @loader_path/../lib/libedit.0.dylib (compatibility version 1.0.0, current version 1.48.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 169.3.0)

Anyone can help me to figure it out?

Many thanks to all
 Pietro

UPDATE

I forgot to logout and then login after editing the bash profile. The suggestion made by Daniel Vérité was right. I just edited the DYLD_LIBRARY_PATH env variable in /etc/profile in order to make it visible at a global level and not only from the interactive shell.

I added the following line to /etc/profile:

export DYLD_LIBRARY_PATH='/Library/PostgreSQL/9.4/lib'

I hope this helps others.

Daniel, Thank you a lot


texts:
I have a dump file of a single table which is 7GB. I try to restore it using

mysql -u root -p DB_NAME < .SQL FILE

But I don't see any activities in task manager. CPU is 00 and RAM is 5,700 K. I don't know if it is really restoring it or and error occurred. Is there any other way to restore this? I am running it on windows 7.


texts: What could cause an auto increment primary key to skip numbers?

texts:
I have a  table created with the following T-SQL:

CREATE TABLE [dbo].[TableName](
    [id] [int] IDENTITY(1,1) NOT NULL
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I took a look at this table today and noticed the id column was skipping values:

  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13,
  14,
  15,
  16,
  17,
  18,
  19,
  20,
  21,
  22,
  23,
  24,
  25,
  26,
  27,
  1027,
  1028,
  1029,
  1030,
  1031,
  1032,
  1033,
  1034,
  1035,
  2034,
  2035,
  3034

I'm not worried about this or anything (my application doesn't care what the primary key is), I was just curious about what might cause this to occur. It does seem there's a sort of pattern, of jumping 1000 every time a "jump" happens.

My application using entity framework to insert these rows, if that makes any difference.


texts: How to catch error from sql server on powershell?

texts:
I have written powershell script that executes all sql from folder, but I want to stop executing on error? Is it possible to catch the error with powershell that was happened on SQL server? This is powershell code

function runSqlScript{
    param([string]$sqlPath)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString.connectionString
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = Get-Content $sqlPath
    $command.ExecuteNonQuery()
    $connection.Close()
}

function runScriptsFromFolder{
    param( [string]$pathToFolder)
    Get-ChildItem $pathToFolder -Filter *.sql| ForEach-Object {
        runSqlScript($_.FullName)
    }
}


texts: How to return a result set from oracle procedure ?

texts:
How can i return a result set from a stored procedure in oracle ?
suppose i want to return a table from a simple select statement such as this :

Select * from tblTest

I tried this but this seems plain wrong!

CREATE OR REPLACE
PROCEDURE ProcSelectEveryThing(cursor_ OUT TYPES.REF_CURSOR)
AS

BEGIN

OPEN cursor_ FOR
      SELECT * FROM "tblTest";
END;

what is wrong?


texts: INSERT succeeds but all inserted values become NULL

texts:
I tired to execute an insert directly from phpMyAdmin as follows:

INSERT INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                ORDER
            )
VALUES(TID = 56919,
       ID = 115948,
       STATUS = '1',
       CREATED_BY = 15217,
       CREATED_DATE = NOW(),
       UPDATED_BY = 15217,
       UPDATED_DATE = NOW(),
       ORDER = 0)

The SQL executed successfully, however I found that all the inserted values became NULL, including the system generated values like NOW().

Does anybody have an idea?


texts: Unstable backup size, where to look for causes

texts:
I've got a new SQL Server 2014 (12.0.2000) instance with about 50 databases, it was recently upgraded from SQL Server 2005.

They were using a lot of backup space so the old maintenance plan was replaced to a different approach:

Check Db Integrity -> Back up Database (Full) (with Compression) -> Rebuild Index -> Maintenance Cleanup

That happens everyday while databases aren't being used and it preserves backups for 5 days.

At the beginning it worked OK and a lot of backup drive was saved but then some of the databases started backing up with erratic file sizes.

A few images that display the file size change:

This one went down a whole lot, that was expected. Thing is, it then when down another two times (?)

This other one when down a bit and then further down in size and finally got bigger than the first time it backed up with compression.

Finally this one went down in size, further down next day and then up the that place it was the first day.

In this case:

On restoring, the file the size increases quite a bit:

So they do restore the way they're supposed to but I'm here wondering, how come they vary in size, I can totally understand the change after the compression but after (?).

Why would the backup behave in this way? Any known reasons?

Additionally: Nothing really going on in the logs.


texts: Materialized view not purging (over dblink via view)

texts:
I've created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c):
create materialized view primary_system.workorder_mv
build immediate
refresh force
on demand
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder_vw@gistomax --view in external system that is served up by dblink (gistomax)
    --the view looks like this: select * from workorder where isgis=1

create materialized view log on external_system.workorder with rowid;

The docs say materialized views are supposed to purge automatically:

Oracle automatically tracks which rows in a materialized view log have
been used during the refreshes of materialized views, and purges these
rows from the log so that the log does not grow endlessly. Because
multiple simple materialized views can use the same materialized view
log, rows already used to refresh one materialized view might still be
needed to refresh another materialized view. Oracle does not delete
rows from the log until all materialized views have used them.

I don't have any other materialized views that reference the MV logs.
Here are the MV log properties (Toad).
Here is what all_registered_mviews looks like.
The MV has been syncing successfully. If I create or update records in the WORKORDER table in the external system, the records get synced to the primary system on the 30-second schedule.

Question:
So far, the MV log data doesn't seem to be purging automatically:

Why aren't' the MV logs automatically purging once the records are synced?


texts: How to grant permission to new user created in mysql?

texts:
I want to create new user and want to give them specific permission like inset, update and delete.
But how to create new user and give them permission i don't know.

Any help please.


texts: Can Anyone please review my database after I did the Normalization ?.Any suggestion is appreciated

texts:
User:
User_ID,user_name,E-mail
Staff: User_ID,Fname,Lname,DOB
Student: User_ID fname Lname DOB
Lecture: User_ID
Admin: User_ID
Position ID : P_ID,ROLE_NAME,
Staff_position :Staff_position_ID,P_ID,user_ID
Programme: Programme_ID P_code P_name
Course: Course_ID**Course_number** C_name C_level User_ID**
Co-programme: Co-programme_id,Course_ID,Programme_ID
Attendence:Attendance_ID,date,time
Attendance_course : Attendance_course_ID,Attendance_ID,Course_ID,date,time
Resources Resource_ID Type,size,Course_ID
Week_resources: Week_resources_ID,Week_number,term,resources_ID
Weekly_upload: Weekly_upload_ID, size,date, time,week_number
Message: Message_ID,user_ID,Body,date,time


texts: How to forwarding connection between 2 Oracle Database?

texts:
I have 2 Databases on independent 2 Linux server. I'm using Oracle 10G and 11G.

I have this stage:

client -> connect to Server A

-> check Database A (check specific tablespace or database A is down)

-> if true -> connect Database A

-> if fail -> connect Server B -> Database B

Is there any methods solve above stage? Thanks for your reading.


texts: SQL Server Replication between a domain and VLAN

texts:
I have a requirement to replicate data between two sql server 2014 instances, one of the instance is in our production domain and second instance is in a separate VLAN.

Is it possible to configure replicaiton ? if so, is there any way to open connection only unidirectional , from production domain to VLAN ? does it require bidirectional network connection like subscriber (here sql instance in VLAN) should also need access to publisher (here production domain) ?

thanks in advance.


texts: What is the standard way to make a invoice system database tables?

texts: I'm confused how to create tables for invoicing system, because when one invoice have more products, I can't repeat same invoice id for the each product in the invoice table as PK cannot repeat. so what is the concept followed to create tables in such scenario?


texts: How do i get a list of records from two tables that have different values based on a matching PK?


texts: SQL admin Scripts - Enabling read only access to all databases

texts:
I would like a script that will allow our web developers read only access to all databases on our SQL server.

Currently we are having to do this manually as we have no SQL expert / DB Admin.

Would somebody be able to help me out by creating a script that will add there windows user to SQL and Grant Read only access to all databases.

Occasionally they will need read / write access on a specific database so we can change this manually but I would like a script that we can schedule overnight that will change the access back if we forget.

Any help is much appreciated.

Regards


texts: sql server partitioned view execution plan questions

texts:
I have created a partitioned view for  3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint
(respectivly for each table)
datepart(year,[Date])=(2010)
datepart(year,[Date])=(2011)
datepart(year,[Date])=(2012)

schema for table 2010 (same for all other tables)

CREATE TABLE [dbo].[tb_Sales2010](
    [Date] [smalldatetime] NOT NULL,
    [ID] [int] NOT NULL

 CONSTRAINT [PK_tb_Sales2010] PRIMARY KEY NONCLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tb_Sales2010]  WITH CHECK ADD  CONSTRAINT [CK_Date_2010] CHECK  ((datepart(year,[Date])=(2010)))
GO

My query is

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE DATEPART(YY, DATE) = 2011

My question is, the execution plan indicates that it is scanning all 3 tables. Instead of looking at the check constraint and saying table b has the information it needs. Why is that. i feel the execution plan should only show 1 table being scanned.

Statistics turned on results

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2012'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2011'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2010'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

View Definition
CREATE VIEW PartitionedTb_Sales
AS
SELECT * FROM tb_sales2010

UNION ALL

SELECT * FROM tb_sales2011

UNION ALL

SELECT * FROM tb_sales2012


texts: Why MySQL performs so poor on SHOW PROCEDURE STATUS?

texts:
I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I need to run a query like this one:
SHOW PROCEDURE STATUS WHERE db LIKE '%SomeDB%';

The above query takes more than 5 seconds to execure and tweaking MySQL configuration parameters such as myisam_sort_buffer_size or key_buffer_size (which where already large enough) made no difference. I've 256GB memory installed on my server and I don't think it is because of any kind of memory shortage, maybe some misconfiguration that I have no Idea about it. Any hint?


texts:
MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking.

And it seems to be widely understood that if the text field is long then hashes are worth the overhead they require. But how long should text be to start thinking about hash?

If I intend to use 128bit hash then how many characters threshold would be a sufficient minimum for a text column?

Edit
Details listed in my another question maybe useful https://stackoverflow.com/questions/7403167/index-on-url-or-hashing-considering-ram


texts: List of databases present in specific maintenance plan

texts:
I have two maintenance plans and also I have two user-defined databases demo1 and demo2. The first maintenance plan is used to take the backup of all the databases. the second maintenance plan is used to take the backup of demo1 and demo2.
So, I am looking for a script that will show all the databases whose backup is taken in a particular maintenance plan.
Suppose, I have used maintenance plan 2 in the script it should show name "maintenance plan 2" and DatabaseName="demo1,demo2"


texts: Payment methods conceptual and logical model

texts:
I need to create a conceptual and logical (normalized) models of parking house according to the requirements below. It looks to me as a very simple concept that doesn't need all tables to have relationships - but then they could not be modelled as entities.
I tried asking this on stackoverflow but got no feedback for couple of days now.

Three possible methods of payment:

a ticket paid on leave,
prepaid card with cash credit,
prepaid card with "time credit",

Price of ticket depends on time:

1-2hrs = $0,
3hrs = $2,
4hrs = $4,
afterwards x hrs = $(x+1), but max. $20 for 24hrs (... easiest to put these to 24 rows, right?).

A ticket (a) may be allowed a 20% discount (ie. for shopping in the mall).
Cash credit card uses same prices as tickets but with 40% discount.
Cash credit card can be reloaded.
Time card is paid once and allows parking while valid.

The problem is I don't know how to put those highlighted relations to the logical db model and whether event to put them there. Is it ok-practice to have isolated tables in the design?


texts: How to query chart data in a database agnostic way?

texts:
I have several tables with data like this:

+---------------------+---------------+
| Creation Timestamp  | Numeric Value |
+---------------------+---------------+
| 2017-02-01 09:15:00 |        100.00 |
| 2017-02-01 11:10:00 |        150.00 |
| 2017-02-03 10:25:00 |         80.00 |
+---------------------+---------------+

and I need to query data for a chart, so I came up with this:

SELECT
    TRUNC_TO_DAY([Creation Timestamp]),
    COUNT(*),
    SUM([Numeric Value])
FROM
    [MyTable]
GROUP BY
    TRUNC_TO_DAY([Creation Timestamp])

which returns this results:

+------------+---+--------+
| 2017-02-01 | 2 | 250.00 |
| 2017-02-03 | 1 |  80.00 |
+------------+---+--------+

which are correct, by I need to fill the gaps too, like this:

+------------+---+--------+
| 2017-02-01 | 2 | 250.00 |
| 2017-02-02 | 0 |   0.00 |
| 2017-02-03 | 1 |  80.00 |
+------------+---+--------+

Some restrictions:

I can't create additional (real) tables nor views;
I've already implemented truncation functions, like this:

In SQL Server, function TRUNC_TO_DAY:

DATEADD(DAY, DATEDIFF(DAY, 0, ?), 0)

the query must work with different truncation unit (year, month, day, hour, minute);
the value to fill the gap too must be configurable (i.e. 0 or NULL).

I'm looking for a SQL Server solution primarily, but then I need to port it also to MySQL so any suggestion on that too would be very appreciated.


texts:
I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple tables, dbo.CostCard where we have 43258326 rows, and dbo.CostType, where we have 150 rows for both databases.

We have been doing some application tests for the past few weeks against DB1. As a result of these test, data has changed for both tables. Currently, table dbo.CostCard increased to 43379268 (An addition of 120942 rows), and table dbo.CostType increased to 199 (Addition of 49 rows). We also have implemented a maintenance strategy that uses logic to reorganize/rebuild indexes based on fragmentation, and also update statistics if data has been changed, where we're updating with full scan.

Currently, only DB1 has this maintenance routine setup, and we've noticed that stats and indexes for both tables have been updated correctly. So far so good!

Now here comes the weird part. We have a fairly simple statement that we've noticed a pretty big performance degradation. Here's the statement:

SELECT DISTINCT TOP 100
    n1t1.Description
FROM
    CostCard AS n0t0
JOIN CostType AS n1t1
    ON ( n0t0.CostType ) = ( n1t1.Code )
WHERE
    ( ( n1t1.Description ) LIKE ( '%legal research%' ) )
    AND ( ( n1t1.Description ) IS NOT NULL )
ORDER BY
    n1t1.Description

What we noticed is that the query optimizer is creating a Serial plan (We've recompile it many times) for DB1, where we know stats and indexes are being regularly updated, and is using a Parallel plan (Better Plan, and yes, we've recompiled many times as well) for DB2 which has been sitting idle for the past few months!!!

How is this possible? I've been trying to figure this out for the past couple weeks but have ran out of ideas. Can someone shed some light here?

P.S.: I have attached a compressed file with all the info, including the query plans and statistics info.

https://dl.dropboxusercontent.com/u/72497299/Terrible%20Bad%20Query%20Plan.zip

Thanks and REALLY, REALLY appreciate any help!!!


texts: SSPI handshake failed with error code 0x80090308 connection to Sql Server 2008 R2

texts:
I have a cross domain situation where the SQL Server 2008 R2 server sits in one domain and the client in another. There is a one-way trust between the domains. The SQL Server runs on a domain account. The domain account used on the client has been granted "Allowed To Authenticate" on the SQL Server machine and has been granted "Access this machine from the network" in the local machine policy on the SQL Server machine.

SSMS on the client connects just fine. However, when trying to connect with Embarcadero Change Manager, which uses the Microsoft JBDC driver, this exception is passed back to the client from the SQL Server:

  SSPI handshake failed with error code 0x80090308, state 14 while establishing a connection with integrated security, the connection has been closed. Reason: AcceptSecurityContect failed. The Windows error code indicates the cause of the failure.

and

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

My best guess is that there is some quirk with the Micorsoft JDBC driver causing this. The network traffic is not clear text so I can't readily see what's being sent over to the SQL Server.


texts: SQL2005 Index exists in sys.indexes but not on table

texts:
I have a routine that runs through all the non-clustered indexes in a given database on SQL2005 (SP3), and reorganizes and rebuilds them (if fragmentation > 20%).  This process is erroring out on one index entry in sys.indexes.  The index does not exist on the database in question, but still exists by name in sys.indexes.
My questions:

Is there a way to find other such indexes in all databases?
Is it safe to just delete the absent index(es) from sys.indexes?  If not, how do I clean this up?

As always, thank you in advance for your assistance.


texts: Joining 2 tables- in Select statement show how many fields matched directly (A1 = B1), vs matched on isnull(A1,B1) = B1

texts:
I have 2 tables which I need to join,
but sometimes there are

1) direct joins e.g. t1.FieldA = t2.FieldA

OR

2) when t2.FieldA is null then it must join as well then t2.FieldA is null t1.FieldA = isnull(t2.FieldA,t1.fieldA)

BUT

I have to keep track of how many were direct joins, how many were isnull joins, since there are precedent levels which need to be calculated, e.g. even though the items
joined the one where there are 5 direct joins are higher than when it joined on 5 isnull values

Now it is a hell of a mission to have the following in the Select statement, since there can be more than 5 fields to compare.

e.g. I need to test

Does all 5 match?
Does A, B, C, D  match? then 4
Does A, B, D, E  match? then 4
Does A, B, C, E  match? then 4

etc.

This is way to sloppy, I am looking for help on method to count how many were directly joined please

case when t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D  and t1.E = t2.E  then  5
     when t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D                 then  4
     when t1.B = t2.B and t1.C = t2.C and t1.D = t2.D and t1.E = t2.E                 then  4
     when t1.A = t2.A and t1.E = t2.E                 then 2

Here is a SQL Fiddle


texts: Reorganize and Rebuild Indexes, Pros and Cons?

texts:
I'm studying SQL Server and now am learning how to Reorganize and Rebuild Indexes. I've read already at msdn.microsoft, but It's not clear for me yet.
Please advise what is the best way when I have to use it. And what are the pros and cons of using it?


texts: Read all Captured Extended Events for a Given Extended Event Session Name

texts:
I want to be able to read all XE files for a dynamic XE session name. My XE session stores files to disk.

I have the query below

DECLARE @XeName SYSNAME = 'MyXE'
DECLARE @Path NVARCHAR(255)
SELECT  @Path = (
                    SELECT  CONVERT(XML,target_data).value('(/EventFileTarget/File/@name)[1]', 'NVARCHAR(MAX)') AS [Path]
                    FROM    sys.dm_xe_sessions s
                            JOIN sys.dm_xe_session_targets t
                                ON s.address = t.event_session_address
                    WHERE   s.name = @XeName
                )

SELECT  event_data = CONVERT(XML, event_data)
FROM    sys.fn_xe_file_target_read_file(@Path, NULL, NULL, NULL)

However, the target_data column in sys.dm_xe_session_targets only shows the most recent file in the format C:\Trace Outputs\MyXE\MyXE_0_132109395357410000.xel

but I need it to be C:\Trace Outputs\MyXE\MyXE*.xel

I can do this using some string functions to find the first _ after the last \ and replace that and all the text after with *.xel but I wondered if there was a nicer / easier way to do this than a load of nested string manipulation functions


texts: Error while changing the authentication - mongoDB

texts:
I had database level authentication in mongoDB, I had tried to change the password for existing user. It is throwing not authorized error.

Steps i followed to change the password

use wallet
db.auth("username","password")
db.changeUserPassword("username","newpassword");

Error

Changing password failed: not authorized for update on wallet.system.users at src/mongo/shell/db.js:186

Any suggestion will be grateful.


texts:
New to this topic - in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by web applications (obviously these are only used when the data involved is not completely time-sensitive, we do nightly refreshes of the materialized views).  Note that we are doing Complete, On Demand refreshes on a specified schedule.

The issue I am running into is it seems that Oracle has used some internal process to determine the 'master' table and when DML operations occur on the master, the MV becomes invalid.  To be honest we don't really care whether the master table has any inserts, updates, deletes as we just want to completely refresh the MV every night - unfortunately when the MV goes invalid it prevents refreshes.

So my question is can I manually set the master table to something like SYS.DUAL so that the MV never goes invalid or is there another workaround?

Failing this I suppose instead of using MVs I would just schedule a nightly CTAS.

Edit:
Added the Oracle note # in comments below - synopsis is that when the MV is in the same database as the master table, DML on master table will cause the MV to go invalid ("Needs Recompile" specifically).  Certainly I can schedule a nightly recompile but this doesn't seem elegant.

To downvoter - this is my 2nd question here, could you express what makes the question so flawed so I don't keep losing points in future posts?  :)


texts: Newer Version of SQL Server Less Stable?

texts:
Our database architect told a client of ours that SQL Server 2014 is a poor choice over 2012 because it lacks the performance and stability advantages that 2012 has.  Everything I've read contradicts this.  Aaron Bertrand says the exact opposite and all the white papers I've read from MS agree too - 2014 is enhanced and resolves some of the AG issues people had with 2012.

Is there any major disadvantage to choosing 2014 over 2012 that I'm missing?


texts:
I am unable to connect to a MSSQL 2012 Server using FreeTDS on OSX in the terminal when I pass the -P .

Example:

V-MBP:etc vashavoc$ tsql -S SERVER_IP -p PORT -U USER -P PASSWORD
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18456 (severity 14, state 1) from SERVER_IP Line 1:
"Login failed for user 'USER'."
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server

If I leave out the -P and force it to prompt for input, I can type/paste it in and I can connect. (No there is no typo when I try using -P.)

V-MBP:etc vashavoc$ tsql -S SERVER_IP -p PORT -U USER
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

Seems very odd it works when I omit the password from the connect string. FreeTDS seems installed OK.

V-MBP:sh vashavoc$ tsql -C
Compile-time settings (established with the "configure" script)
                        Version: freetds v0.95.80
         freetds.conf directory: /usr/local/Cellar/freetds/0.95.80/etc
 MS db-lib source compatibility: no
    Sybase binary compatibility: no
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 7.1
                          iODBC: no
                       unixodbc: no
          SSPI "trusted" logins: no
                       Kerberos: no
                        OpenSSL: yes
                         GnuTLS: no

Tried TDSVER=X.X variants before the tsql string without any change.

tsql -LH SERVER_IP

Also tried to see if it would list back its instances using the -LH, doesn't respond and times out I presume.

Am I missing anything that could get this to work? It is a server setting that is preventing it?


texts:
PostgreSQL 9.4

I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column:

       most_common_vals
{20000,80000,40000,60000,100000}

Now, I run the following simple query EXPLAIN ANALYZE SELECT * FROM customers WHERE income=123123 to understand the rows count estimating. Output:

Seq Scan on customers  (cost=0.00..738.00 rows=1 width=268) (actual time=4.669..4.669 rows=0 loops=1)
  Filter: (income = 123123)
  Rows Removed by Filter: 20000

Since the optimizer doesn't have statistic for the income value 123123, it made a wild guess for 0.5% of the table size. So, the estimated row count should have been 500. But the optimizer returned the 1. Why? Maybe I didn't understand the estimating process of row counting of unknown values?

Couldn't you explain it a bit?


texts:
I have an asp.net application which use Mysql. I test it on testing server running MySQL. When the application is running, it consumes 40% of cpu. I run the same code in production server which also running MySQL, now it consumes 99% of cpu usage. What setting in MySQL config will affect the CPU consuming level? Because I am running the same code but the result is totally different.

Here is the config of high cpu usage mysql:

[mysqld]
interactive_timeout=28800
wait_timeout=28800

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=1
general_log_file="EC2AMAZ-SSAVRCC.log"
slow-query-log=1
slow_query_log_file="EC2AMAZ-SSAVRCC-slow.log"
long_query_time=10

log-error="EC2AMAZ-SSAVRCC.err"

server-id=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=151

query_cache_size=0

table_open_cache=2000

tmp_table_size=2G

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=4G

key_buffer_size = 10M

read_buffer_size=64K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size = 8M

innodb_buffer_pool_size = 6144M

innodb_log_file_size=48M

innodb_thread_concurrency=33

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

 join_buffer_size=256K

max_allowed_packet=10M

max_connect_errors=100

open_files_limit=4161

query_cache_type=0

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

Here is the code of less cpu usage mysql:

[mysqld]

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE

general-log=0

general_log_file="c:\MySQL_LOG\WIN-9BPM7TSIO9C.log"

slow-query-log=0

slow_query_log_file="WIN-9BPM7TSIO9C-slow.log"

long_query_time=1

log-error="WIN-9BPM7TSIO9C.err"

server-id=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=151

table_open_cache=2000

tmp_table_size=16M

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=8M

key_buffer_size=8M

read_buffer_size=0

read_rnd_buffer_size=0

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=8M

innodb_log_file_size=48M

innodb_thread_concurrency=17

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=100M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

net_read_timeout = 999999
net_write_timeout = 999999


texts: Why does the SHOW FULL COLUMNS command show the Type field in lower case?

texts: When I run SHOW FULL COLUMNS, it shows the Type column in lower case, such as text and varchar(255).  I thought these type names were supposed to be upper case (TEXT and VARCHAR), per https://dev.mysql.com/doc/refman/8.0/en/string-types.html.


texts:
If someone could guide me on helping me understand if my results are correct I would appreciate that. Let's say I have the following table:

BOOK (BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher)

AuthorAffil refers to the affiliation of author. Suppose the following functional dependencies exist:

{BookTitle} -> {Publisher, BookType}
{BookType} -> {ListPrice}
{AuthorName} -> {AuthorAffil}

I am asked if this table is in 1NF, 2NF, or 3NF.

I believe this table is in 1NF because no two rows of data contain repeating information.

If this was to be a 2NF, it would have to look like:

R1 = {BookTitle} -> {Publisher, BookType, ListPrice}
R2 = {AuthorName} -> {AuthorAffil}
R3 = {AuthorName, BookTitle} -> {extras}

I would like some help to know if I am understanding this concept or if there are any mistakes. Any type of help is welcomed.


texts: Find if any of the rows partially match a string

texts:
I want to see if a table contains any sub-string of a given string.

Let's say I have a string

somedomain.com

In database I have:

blabladomain.com
testdomain.com
domain.com

I need to make a query that will return "domain.com" as it is a substring of "somedomain.com".

I don't know if this is even possible in MySQL.

CREATE TABLE `site_core_antispam_banned_domain` (
`domain_id` int(11) NOT NULL AUTO_INCREMENT,
`domain_name` varchar(255) NOT NULL,
PRIMARY KEY (`domain_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;