texts: Find most frequent values for a given column

texts:
I have a table that I would like as a leader-board for invitations as described below. I would like to create a query that counts the number of duplicate rows in a given month and order in a descending fashion.

Reading through some questions, this query seems to work:

SELECT COUNT(invite_code) AS counted
FROM invite_table
GROUP BY invite_code
ORDER BY counted DESC
LIMIT 10;

But it doesn't consider the month. What I am looking for is to get the most frequently appearing user_code where the month is specified. Also any criticism about the table design is welcome as I have deliberately designed it such that there are repeating rows with duplicate values. I am trying to track users whose invite code is used the most in a given month, I also have codes that indicate which channel a user comes from (maybe seeing an ad in FB for example), is this a valid table design?

Table "public.invite_table"
   Column    |         Type         | Collation | Nullable | Default
-------------+----------------------+-----------+----------+---------
 user_code   | character varying    |           | not null |
 invite_code | character varying    |           |          |
 month       | character varying(3) |           | not null |
 points      | integer              |           | not null |
Indexes:
    "invite_table_pkey" PRIMARY KEY, btree (user_code)
Foreign-key constraints:
    "invite_table_user_code_fkey" FOREIGN KEY (user_code) REFERENCES user_table(user_code)


texts: Postgres won't use btree index in left-anchored LIKE query

texts:
Using Postgres 9.5. The Postgres documentation says that the btree index can be used for left-anchored "like" queries (i.e. "%" only at end). But my left-anchored "like" queries on a 1 billion row table won't use a btree index, and my queries are taking forever. What am I missing? Here's a small example that demonstrates the problem:

-- generate a table of 100K integers with random texts
CREATE TABLE test(num integer, string text);
INSERT INTO test(num, string) (SELECT generate_series(1, 100000), md5(random()::text));

CREATE INDEX test_string ON test USING btree (string); -- b-tree index

EXPLAIN SELECT * FROM test WHERE string LIKE 'asdf%'; -- will use seqscan
EXPLAIN SELECT * FROM test WHERE string = 'asdf'; -- will use index
SET enable_seqscan=off; -- tell query planner to avoid seqscan if possible
EXPLAIN SELECT * FROM test WHERE string LIKE 'asdf%'; -- will still use seqscan


texts: In MySQL if I create a user and grant privileges to the user, will dropping the user revoke privileges?

texts:
In MySQL, I created a user and granted all privileges to the user. I even granted proxy to user.

CREATE USER IF NOT EXISTS 'testuser'@'localhost' IDENTIFIED BY 'testpass';
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'testuser'@'localhost' WITH GRANT OPTION;

Now if I drop the user, will the user's privileges and proxy be revoked? Or do I need to manually revoke the user's privileges and proxy before I drop the user?

DROP USER IF EXISTS 'testuser'@'localhost';


texts: Optimizing for data that does not fit in memory

texts:
We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from SSH, backup and similar administrative stuff). We are running postgres 9.2.

I extracted one table of 13GB and tested it on a seperate server with 4GB of memory to benchmark some queries, indexes, etc. outside of our production environment. I had some questions for this which can be seen here: https://stackoverflow.com/questions/26468982/avoiding-external-disk-sort-for-aggregate-query.

On the seperate server, the queries use index-only scans and run relatively fast (compared to pre-index creation with sequence scans). The same queries against the same table on the production machine does a sequence scan almost no matter what settings I set up (short of setting seq_page_cost lower than random_page_cost, which is a Bad Idea(tm).)

Our workload is to extract analytical data - we have around 10 columns that are raw numbers that we do a lot of SUM() aggregate queries on, usually grouped by an item ID. The data is updated constantly, mostly with UPDATE queries to existing rows to update per-day data is it comes in during the day (so a row for a specific day may be updated ~20 times in one day). The 13GB table is for one of our biggest customers and have around 6 million rows.

The customized settings from the production config looks like this:

shared_buffers = 20000MB
effective_cache_size = 56000MB
work_mem = 512MB
maintenance_work_mem = 2048MB
checkpoint_segments = 64
temp_buffers = 16MB
max_stack_depth = 4MB

wal_level = archive
archive_mode = on
archive_timeout = 6h
checkpoint_timeout = 1h
wal_writer_delay = 100
wal_keep_segments = 32

I am looking for some insights for how to create indexes (and alter queries), as obviously in the larger setup the query planner acts differently based on the amount of data.

Is it better with small indexes that only cover the small parts of the query to help with lookups or full covering indexes (for index-only scans)?
Does the above setup match my server specifications and workload?
Any good tips to speed up aggregate queries (mainly SUM) for all rows and grouped by item ID (I am aware we may need to look into column-store databases)?

Also, does any of the above stand out as being "wrong" or should I modify it? We are running it off locally attached disks, but backup is being sent off to NFS disks (SAN).


texts: Postresql dynamic index creation for many tables

texts:
How can I create an index in Postgres dynamically over many tables but always with different names.

Lets assume table x has a column y with all table names of hundreds tables which need to get an index on their z column type numeric. How can I run a loop which dynamically creates with the table names of column y of table x indexes on every table?


texts: Is my restore process being badly affected by this third-party non copy-only backup?

texts:
This question may read like a duplicate, but is situation-based, and is posted from confusion applying the knowledge from other answers.

I've read dozens of articles (among 1, 2, 3, 4), but am finding conflicting opinions (based on my understanding, which is now suffering from information overload, or perhaps not including enough information in my other questions).  I am creating this question therefore to get a definitive answer based on my situation.

Given the following backup scenario, I need to know whether the third-party backup software would prevent me from performing a full recovery to the point of the latest backup (18:00)?

Time  | Action                                       | Device
------|----------------------------------------------|----------------------------
12:00 | Full backup (non copy_only)                  | D:\MyBackupDevice
13:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
14:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
15:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
16:00 | Full backup (non copy_only) VSS snapshot     | Third-party off-site device
17:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
18:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
19:00 | Disaster strikes                             |

My restore goal is to restore to the point of the 18:00 backup (I know there is a tail-log backup that can be added in as well to get the remaining transactions up until the disaster, but let's keep things simple for now).

Based on this answer, I believe that the third-party backup causes a conflict with my own transaction log restoration (as per this answer), which prevents me from performing my restore to that point.   My understanding is that the transaction log backup contains data since the last full non copy_only backup.

Is this correct?  Does the third-party backup prevent my own restore routine from working because it is a non copy_only backup?


texts: Is it possible to select internal InnoDB columns?

texts: I have read that InnoDB uses a few fields internally for all rows, such as the hidden columns DB_TRX_ID and DB_ROW_ID.  Is there any easy way to see these values, such as within a select statement?


texts: Difference Between Implication and Conjunction in Domain Relational Calculus

texts:
I was reading my notes, and I came across this:

  Another example would be to find the names of the lecturers teaching all modules. We might reach for the following incorrect expression:

  {<N> | ∃M ∀C ∀MN ∀Cr (lecturer(M, N) ∧ module(C, MN, Cr) ∧ teach(M, C))}

Instead, the following is more accurate:

  {<N> | ∃M ∀C ∀MN ∀Cr (lecturer(M, N) ∧ (module(C, MN, Cr) → teach(M, C)))}

The first one states that as long as there exists a lecturer who has taught a module that exists in the database, the name will be returned (which is not what we want). Or at least that is how I interpreted this.

Is there any reason why the first expression is incorrect and (more importantly) the second one is correct?


texts: How to move a database from SQL Server 2012 to SQL Server 2005

texts:
What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)?

I know I cannot:

restore a backup of the database on SQL Server 2005
detach & attach

I know I can:

use import data wizard, and I tried it on one database, but it only moves data, and even that was troublesome as I needed to do a lot of work creating temp tables to maintain identity columns, recreating all FKs, indexes etc.

Is there an easier option?


texts: Can stable (or immutable) functions call volatile functions?

texts:
The PostgreSQL documentation states:

  Any function with side-effects must be labeled VOLATILE...

Consider the following function:

CREATE OR REPLACE FUNCTION count_items()
  RETURNS integer AS
$BODY$
DECLARE
  v_result INTEGER DEFAULT 0;
BEGIN
  SELECT
    count( t.id )
  INTO
    v_result
  FROM
    some_table t;

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    PERFORM error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 10;

Since error_log_insert alters the database (performs an insert upon an exception), does this mean that the count_items function has a side-effect (albeit indirect), and thus cannot be declared STABLE, but must be VOLATILE?

In other words, does the stability or volatility of a function also depend on the functions it calls within its exception block?

If that is the case, then how would you create STABLE functions in PostgreSQL that log all exceptions to a database table?


texts: I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran psql against that included the other files however I don't remember the syntax of this script.


texts: Optimising MySQL count() with INNER JOIN (550k rows)

texts:
I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the application (PHP) level, I'd still like to try and speed up this query if possible. There are approx 550k rows in the table.
SELECT
    count(*) AS aggregate
FROM
    `establishments`
    INNER JOIN `inspections` ON `inspections`.`id` = `establishments`.`latest_inspection_id`
    INNER JOIN `ratings` ON `ratings`.`id` = `inspections`.`rating_id`
WHERE
    `establishments`.`latest_inspection_id` IS NOT NULL
    AND `ratings`.`key` in('best', 'average', 'poor', 'terrible', 'exempt')
    AND `establishments`.`deleted_at` IS NULL

This is the result of EXPLAIN on the query:
| id | select_type | table          | partitions | type   | possible_keys                               | key     | key_len | ref                                               | rows   | filtered | Extra       |
|----|-------------|----------------|------------|--------|---------------------------------------------|---------|---------|---------------------------------------------------|--------|----------|-------------|
|  1 | SIMPLE      | establishments |            | ALL    | establishments_latest_inspection_id_foreign |         |         |                                                   | 526482 |     5.00 | Using where |
|  1 | SIMPLE      | inspections    |            | eq_ref | PRIMARY,inspections_rating_id_foreign       | PRIMARY | 8       | fhrs_insights.establishments.latest_inspection_id |      1 |   100.00 |             |
|  1 | SIMPLE      | ratings        |            | eq_ref | PRIMARY,ratings_key_unique                  | PRIMARY | 8       | fhrs_insights.inspections.rating_id               |      1 |    33.33 | Using where |

Any advice on how I can speed this up?


texts: loading k:v pairs from DB into JS Object

texts:
I'm creating a single player web RPG.

I'm debating the following method of storing/retrieving quest items per player id:

In userstats table, have a quest_items column of type mediumtext, where I would store the total amount of the item the player has as key, and item_name as value, like so:

If I were to do it this way, how can I load that column data JSON into a JS object, where the key : value pairs are maintained, like so loaded for player "DAN":

quest_items = { 2 : "rice", 1 : "meat" };

Or, the alternative is to have some sort of association table, where I store the user_id against the item_id, maintaining single data in each column:

The issue with this is, each time a user gained a quest item, I would have to update this table, which could be costly.

Preferably, I would like to go with option 1, and

when I need to update the quest_items in userstats, I simply pass in a js object with new data, JSON_encode it, and save it into the DB column as text.
when I need to read it back out, just JSON_decode it

Is it bad to store k:v pairs this way in a DB field?

Update: So, it appears like option 2 is the best. Would this query be right if I wanted to
- load all item data (image path, x, y location, etc) from quest_items table against a certain player ID?

$qry =
    'SELECT u.*
    FROM users u
    LEFT JOIN join_users_questItems juq ON (u.id_user = juq.user_id)
    LEFT JOIN quest_items qi ON (qi.item_id = juq.item_id)
    WHERE u.id_user = "' . $playerID . '"';


texts: How to mark loser as winner in p2p replication conflict resolution

texts:
We have sql server 2008 databases in 2 different regions that are p2p replicated.

Now we can see there are some unresolved conflicts - most of them update/delete conflicts. The way our system is designed allows us to safely assume delete commands should be 'winners' against update commands. But most of the delete commands are losers.

So I'm wondering is there any way to make losers(delete commands) as new winners.
I came across this article. There is a way to mark looser as new winner but article applies to merge replication and probably that's the reason I don't see respective buttons to mark losers as new winner for our p2p replication.

So my question is - Is there any way to mark losers as new winners in p2p replication?


texts:
I am currently running a very long script in Oracle for our production but we are having discrepancies in the data.

As I checked the views that the script is using (I am using Toad), I see an X mark beside the view name, then I saw an option to compile it.

I am wondering if what does a compile in a view means? Is the view not updated which causes discrepancy in the data?


texts: Is there a way to specify an table/index is sorted by more than one column?

texts:
Suppose I have a table:

CREATE TABLE [dbo].[TestTable]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),
    [Timestamp] DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME()),
    [Data] INT NOT NULL
);

And suppose I need to look up rows by ID (e.g. for joins) and by Timestamp (e.g. for date-range search).

The usual way to do this would be to add an index on Timestamp, but this seems like a massive waste of resources (almost a complete copy of the table) when Timestamp should be in order in the table in the first place. So two questions:

Is there a way to enforce Timestamp being strictly ascending along with Id? (i.e. to make sure pathological cases like changing the server's system clock, leap seconds, Id's Identity-ness being overruled).
Is there a way for SQL Server to make use of the above enforcement and treat the table as indexed on both Id and Timestamp?

I suspect the answer to (1) may be yes, but that (2) is probably a no, rendering (1) a bit useless, but I'm happy to be proven wrong.

Any version of SQL Server acceptable


texts: removal of GUIDS used for all PK's, FKs & Clustered indexes

texts:
Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of using NEWSEQUENTIALID() either.

Obviously changing some of these sounds like a huge piece of work, but changing the Lookups doesn't sound too painful. I've documented the fragmentation and CL width and NC indexes built from these, optimizer choosing incorrect plans, etc.. but tasked with a workaround i'm coming up a bit short.

My current plan is to investigate how much is possible to change and in the interim add an identity field to the main heavily indexed tables and make that the clustered index and keep the GUIDs as PK's. Is this the best I can hope for? or not even worth it? any other workarounds that i have omitted ?

Thanks


texts: Fact Grain & Modeling Loan Approval Proccess

texts:
I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out:

Time(day)
Account
Opportunity
Channel

And I and getting to the modeling of fact table(s) around the loan approval and decisioning process. An opportunity will get one or more "Credit Reviews" which have a series of steps they go through before arriving at a "Credit Decision". My instinct is to model the fact grain at the credit review step level or maybe the credit review level (or have one fact for each).

I'm working with some analysts who are used to using a canned report that is at the opportunity level, and finds a "best credit review", and do their reporting at this granularity. I'm trying to explain my credit approval fact and how it would work and they think it's "overly complicated" and seem to want a fact table that is one-to-one with the opportunity, which is a dimension. And contains information on only the "best" credit review. In my mind that seems odd. I haven't run across a fact table that is at the same granularity (one-to-one) with a dimension. Have you ever heard of this? It feels like they are just trying to reproduce what they are used to, a non-dimensionally modeled report at the opportunity level. I'm trying to explain the utility of having a fact table that is at a higher granularity than they are used to in order to support many questions about the data rather than one, but I seem to be running into a wall getting this concept across.

My question is this. Is it every appropriate to model a fact that has the same cardinality as a dimension? What would you recommend for this situation?


texts: DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1

texts:
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate controller. Yes, this is a virtual platform.

So let's say we have MyCoolDB and its primary MDF is at D:\Data\MyCoolDB.mdf, with logical name MyCoolDB_data. I add 3 new files: E:\Data2\MyCoolDB_2.ndf, F:\Data3\MyCoolDB_3.ndf, and G:\Data4\MyCoolDB_4.ndf. That works.

Now I want to re-distribute the data into these new files. So I use the DBCC SHRINKFILE command with the EMPTYFILE option. Namely, DBCC SHRINKFILE ('MyCoolDB_data', EMPTYFILE);.

It runs for about 10-30 seconds, then FAILS, throwing the error as follows: DBCC SHRINKFILE: System table SYSFILES1 Page 1:32074080 could not be moved to other files because it only can reside in the primary file of the database.

This operation worked fine in DEV/TEST environment, and I got no such errors. What could be going wrong / different in PROD? What are my next-steps or other options? Because now we have 3 new NDF files sitting there with hardly anything in them (while, yes, "new data" is being distributed into them, the primary file is still huge in comparison), and storage folks are going to be banging down my door about all that "wasted space".

Things I have tried:

doing a CHECKPOINT beforehand

Things I cannot try without downtime/slow-time (I think), and therefore have not yet:

Simple recovery -> do shrink -> Full recovery -> take full-backup to allow tlog-backups to continue
Creating a new FILEGROUP, and adding files to new FILEGROUP, and using index rebuilds to move data into said new FILEGROUP's files. PS: Now that the new NDF files are there and have 'something' in them, I'm betting it's gonna be nearly impossible to UNDO that action (i.e. to EMPTY them and remove them), so I'm not looking forward to that being a thing, if it is.
Creating a new DB with the desired file structure and actually migrating all the data over to it. This seems like the last-resort answer. As I said, the SHRINKFILE EMPTYFILE worked fine in another environment for the same database.

Config: SQL 2017 Standard, 16 cores (2x8), 124 GB RAM. Platform: Nutanix with VMWare.


texts: how to interact to second database through API on mongodb

texts:
I am on Windows environment. I am using Mongoose and nodejs for my project. I have MongoDB Replication i.e 27017(primary), 27018(slave)and 27020 is (Arbitar). now I have One more seperate db named as secondary which is running on port 27021.

My mongodb Version is

MongoDB shell version v3.4.9

For Interaction with backend I made the API. Now my problem is I want to fetch some information from my second server only when specific API calls from Frontend. This API is made/written on Primary server i.e Under Replication server.

I create the logic by which I get the information but just after my primary server is closed and not open, and generate an error that is

  MongoError: topology was destroyed and then-No connection available.

My approach was.

    router.post('/test, function(req, res) {
      mongoose.connection.close( function () {
        console.log('Primary Server close Properly');
      });
      const options = {
        useMongoClient: true,
        autoIndex: false, // Don't build indexes
        reconnectTries: 10000, // Never stop trying to reconnect
        reconnectInterval: 500, // Reconnect every 500ms
        poolSize: 10, // Maintain up to 10 socket connections
      // If not connected, return errors immediately rather than waiting for reconnect
        bufferMaxEntries: 0
      };
      mongoose.createConnection('mongodb://127.0.0.1:27021/secondary', options);
            Message.find({}).limit(2).exec(function(error, data) {
                if(error) {
                    console.log(error);
                } else {
                    res.send(data);
                    mongoose.connection.close( function () {
                        console.log('Secondary Server close Properly');
                    });
                    const options = {
                      useMongoClient: true,
                      autoIndex: false, // Don't build indexes
                      reconnectTries: 10000, // Never stop trying to reconnect
                      reconnectInterval: 500, // Reconnect every 500ms
                      poolSize: 10, // Maintain up to 10 socket connections
                      // If not connected, return errors immediately rather than waiting for reconnect
                      bufferMaxEntries: 0
                    };
                    mongoose.connect('mongodb://127.0.0.1:27017, 127.0.0.1:27018, 127.0.0.1:27020/sfr_new?replicaSet=sfr', options);
                    var db = mongoose.connection;
                    console.log('Original Connection Open Successfully');
                    db.on('error', console.error.bind(console, 'MongoDB connection error:'));
                        }
                    });

})

By this I am getting the response from second server, but my first server not start. Any help is really Appreciated.


texts:
I have a table table1 like:

col1  col2

A1    A2
A1    B1
A1    B2
A2    A1
A2    B1
A2    B2
B1    A1
B1    A2
B1    B2
B2    A1
B2    A2
B2    B1

I need to concat col1 and col2 such a way that it should be sorted first.

Expected result:

col1  col2  ConcatResult

A1    A2    A1-A2
A1    B1    A1-B1
A1    B2    A1-B2
A2    A1    A1-A2
A2    B1    A2-B1
A2    B2    A2-B2
B1    A1    A1-B1
B1    A2    A2-B1
B1    B2    B1-B2
B2    A1    A1-B2
B2    A2    A2-B2
B2    B1    B1-B2

One more requirement: if possible, I would like to select the distinct from ConcatResult column.


texts: How to recursively find gaps where 90 days passed, between rows

texts:
This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.

SELECT SomeId, MyDate,
    dbo.udfLastHitRecursive(param1, param2, MyDate) as 'Qualifying'
FROM T

How should it work

  I send those three params into a UDF.
  The UDF internally use params to fetch related <= 90 days older rows, from a view.
  The UDF traverse 'MyDate' and return 1 if it should be included in a total calculation.
  If it should not, then it return 0.
  Named here as "qualifying".

What the udf will do

  List the rows in date order.
  Calculate the days between rows.
  First row in resultset defaults to Hit = 1.
  If the difference is up to 90,
   - then pass to next row until the sum of gaps is 90 days (90th day must pass)
  When reached, set Hit to 1 and reset gap to 0.
  It would also work to instead omit the row from result.

                                          |(column by udf, which not work yet)
Date              Calc_date     MaxDiff   | Qualifying
2014-01-01 11:00  2014-01-01    0         | 1
2014-01-03 10:00  2014-01-01    2         | 0
2014-01-04 09:30  2014-01-03    1         | 0
2014-04-01 10:00  2014-01-04    87        | 0
2014-05-01 11:00  2014-04-01    30        | 1

In the table above, MaxDiff column is the gap from date in previous line. The problem with my attempts so far is that I can't ignore second last row in the sample above.

[EDIT]
As per comment I add a tag and also paste the udf I have compiled just now. Though, is just a placeholder and won't give useful result.

;WITH cte (someid, otherkey, mydate, cost) AS
(
    SELECT someid, otherkey, mydate, cost
    FROM dbo.vGetVisits
    WHERE someid = @someid AND VisitCode = 3 AND otherkey = @otherkey
    AND CONVERT(Date,mydate) = @VisitDate

    UNION ALL

    SELECT top 1 e.someid, e.otherkey, e.mydate, e.cost
    FROM dbo.vGetVisits AS E
    WHERE CONVERT(date, e.mydate)
        BETWEEN DateAdd(dd,-90,CONVERT(Date,@VisitDate)) AND CONVERT(Date,@VisitDate)
        AND e.someid = @someid AND e.VisitCode = 3 AND e.otherkey = @otherkey
        AND CONVERT(Date,e.mydate) = @VisitDate
        order by e.mydate
)

I have another query which I define separately which is more close to what I need, but blocked with the fact I can't calculate on windowed columns. I also tried one similiar which give more or less same output just with a LAG() over MyDate, surrounded with a datediff.

SELECT
    t.Mydate, t.VisitCode, t.Cost, t.SomeId, t.otherkey, t.MaxDiff, t.DateDiff
FROM
(
    SELECT *,
        MaxDiff = LAST_VALUE(Diff.Diff)  OVER (
            ORDER BY Diff.Mydate ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM
    (
        SELECT *,
            Diff =  ISNULL(DATEDIFF(DAY, LAST_VALUE(r.Mydate) OVER (
                        ORDER BY r.Mydate ASC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
                                r.Mydate),0),
            DateDiff =  ISNULL(LAST_VALUE(r.Mydate) OVER (
                        ORDER BY r.Mydate ASC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
                                r.Mydate)
        FROM dbo.vGetVisits AS r
        WHERE r.VisitCode = 3 AND r.SomeId = @SomeID AND r.otherkey = @otherkey
    ) AS Diff
) AS t
WHERE t.VisitCode = 3 AND t.SomeId = @SomeId AND t.otherkey = @otherkey
    AND t.Diff <= 90
ORDER BY
    t.Mydate ASC;


texts: Why does mysql binlog use different isolation level?

texts:
Default isolation level:

mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

Binlog:

mysqlbinlog mysql-bin.000078 --base64-output=DECODE-ROWS
....
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

What is the reason for using different level isolation in binlog?
How it can affect an application code written for REPEATABLE READ isolation level?


texts: Can I limit the time a table lock is being held server side?

texts:
I have a SQL Azure server with some stored procedures with TABLOCKX hints that I call from C# code on a Windows Azure web role VM.

I'm kinda paranoid about those hints - what if there's some network protocol failure and the transaction doesn't end or something like that and a lock is held forever and all invokations of that stored procedure block on the statement with the TABLOCKX hint and all my stuff comes to a halt.

Is it somehow possible to tell SQL Server that the lock should not be held for more than say one minute so that it is unconditionally released after that time?


texts: Should I drop this column or is there a query that can achieve a results table without duplicate columns?

texts:
I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information.

How can I prevent this column, named active, from showing up in the result tables from queries joining a number of tables which contain the active column?

The result table would normally be a summary based on a link table which may have its own active column as well (example: perhaps a schedule table)

This schedule table may contain the names/ids of employees/admins gathered from their own respective tables each of which house an active column.

The goal here is to get educated on queries and keep the active column.

Or just get educated and drop the column from all my tables if it happens to be a silly means of retaining information - whichever works.

Thanks to all that read and respond.


texts: How to migrate SSAS and SSRS from SQL Server 2008R2 to SQL Server 2012?

texts:
We are upgrading our SQL Server 2008R2 to 2012 and separate the SQL Server engine from SSAS and SSRS. We want put on Server 1 the engine and on Server 2 SSAS and SSRS.

I did the side-by-side migration for the database. Now the SQL Server Engine and databases are on the SQL Server 2012(server 1).  On the older server we have SSAS and SSRS we want to migrate to new SQL Server 2012 server by itself.

My Question is:

  How to move the SSAS and SSRS from SQL Server 2008R2 to the new SQL
  Server 2012(Server 2) ?


texts: Transfer an Advantage Database's Schema/Data to a PostgreSQL Platform Using C# Program

texts:
I am currently in the process of writing a .NET program with C# to transfer a database on a Sybase/Advantage 9.1 platform to a PostgreSQL 9.3.4 platform. I am doing this transfer in two stages - transfer all the tables/schema without the data and then transferring the data. I was able to transfer the schema without much of a problem; however, I am having issues with transferring the data (mainly performance issues as there are multiple tables with over 4 million entries).

My current approach for the data transfer is as follows...

Establish an ODBC connection to the Advantage database
Create an ODBC Data Adapter
Fill a Data Table with the data entries using the method DataAdapter.Fill()
Write the contents of the Data Table to a CSV file
Establish an ODBC connection to the PostgreSQL database
Use the COPY query to upload the CSV file into the database

This current method works and correctly transfers the data. However, I calculated that it would take 45 minutes to transfer a table with 5 million entries. I need this to work much faster. The longest portion of my current method is the DataAdapter.Fill().

Does anyone have any suggestions on how to do this transfer more efficiently and in less time? Either a suggestion to improve my current method or a different approach entirely. I would like to stick to using C# and the .NET platform to write a program for this transfer.

EDIT:

Just to add more details. Below is the portion of code that is currently taking the most time and seems to be the trouble area.

string query = "SELECT * FROM \"[tableNames]\"";
OdbcDataAdapter adapter = new OdbcDataAdapter(query, myConnection);
tableEntries.BeginLoadData();
adapter.ResetFillLoadOption();
adapter.Fill(tableEntries);
tableEntries.EndLoadData();


texts: Why columns in views are always nullable?

texts:
I'm using postgreSQL. Why the fields in the views always are nullable? I get that in some situation computed columns can be NULL, but in the below snippet I only "forward" those data.
I noticed that MySQL behaves like I'd have, and I don't need special feature from psql, so I think I'll switch to MySQL.
But I really wonder WHY postgreSQL doesn't behave "normally"?
It is quite annoying when you are working with system like SQLx in Rust bridging SQL and Rust types since you cannot rely on this tool when you refers to views in your Rust code.
postgres=# CREATE TABLE a( field INT NOT NULL);
CREATE TABLE
postgres=# CREATE VIEW b AS SELECT field FROM a;
CREATE VIEW

postgres=# SELECT column_name,is_nullable, data_type
FROM information_schema.columns
WHERE table_name ='a';
 column_name | is_nullable | data_type
-------------+-------------+-----------
 field       | NO          | integer
(1 row)

postgres=# SELECT column_name,is_nullable, data_type
FROM information_schema.columns
WHERE table_name ='b';
 column_name | is_nullable | data_type
-------------+-------------+-----------
 field       | YES         | integer
(1 row)


texts: Where is `pg_temp` documented in the PG manual?

texts: I have tried various ways to search for pg_temp in the manual, but could not find it. Is it documented in the manual?


texts:
When I perform below statement in Postgres:

ALTER DATABASE db1 SET search_path = schm1, schm2;

what kind of locks will be performed on DB1?

will other users accessing the tables present in sch1,sch2,sch3 have any issue?


texts: I ran into this error earlier today and requested that the extra logs be purged from the server. It was done but it still claims that there isn't any room. Is there a way to notify the server that it has more room than it thinks?


texts: SSIS OLE DB Source Editor Data Access Mode: "SQL command" vs "Table or view"

texts:
In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source.
For example, suppose I have a table called mytable with 5 columns (column1, column2, column3, column4, column5) but I'm only interested in column1. I see these 2 options:

I can either use "Table or view" and select "mytable" and then only
check "column 1" from the "Columns" pane.
Or I can use "SQL Command"
and use this query "select column1 from mytable".

When I ran a test, the second option seemed to run much faster but I'd like confirmation of what's going on behind-the-scenes when I use "Table or view" and then only select certain columns.  I'd appreciate any insight anyone can provide.

(In my particular use case, mytable is an offsite Azure Database table and column2 has large amounts of xml within it. My guess is that all of the columns were coming over the wire when I used "Table or view")


texts: Trying to join 2 tables to get a count of orders and grouped by email

texts:
I have two tables in DB Orders with columns Orders Id, Orders NO, Customer ID; Customer with columns Customer ID, Customer email.  I need a count of the orders for each customer and group by email with output something like

customeremail@email.com 23

SELECT Customer.Customer Email,
Count Orders.Order ID,
From Orders,
Left Join Orders on Customer.Customer ID
Group by Customer.Customer email, Orders.Order ID


texts: SQL server 2019 adaptive join poor performance

texts:
I upgrade SQL Server from 2016 to 2019, the query plan of my query changed and it used adaptive join, but unfortunately the duration of query increase to 1 minute from 1 second,
I changed the join order and the problem was solved
The T-SQL code:
SELECT TOP 100 * FROM dbo.APP App
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId

and it 's query plan :
https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP
After change join order :
SELECT TOP 100 * FROM dbo.APP App
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId

and it 's query plan :
https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv
Does anyone have an idea about

Why Adaptive join caused the query to slow down?
How does changing the join order change the execution plan?


texts: Can the Atlas search filter in MongoDB cloud be used to query between dates?

texts:
All the examples I read online seem to refer to using the compass or the command line, not MongoDB cloud. I try emulate the same query code in the search filter but not having any luck.
For example with the sample collections..
_id:ObjectId("5bd761dcae323e45a93ccff1")
saleDate:2014-08-18T04:37:26.849+00:00
storeLocation:"Denver"
couponUsed:false
purchaseMethod:"In store"

_id:ObjectId("5bd761dcae323e45a93ccff7")
saleDate:2017-12-08T21:40:34.527+00:00
storeLocation:"Denver"
couponUsed:false
purchaseMethod:"Phone"

_id:ObjectId("5bd761dcae323e45a93ccfef")
saleDate:2014-03-31T16:02:06.624+00:00
storeLocation:"Austin"
couponUsed:false
purchaseMethod:"Online"

How could I filter saleDate to show objects from 2014?


texts: Restoring database with less data than the backup file

texts:
I'm not really experienced with DBA and SQL, so I need some recommendations from you.

I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.

I read this: Is a partial restore of data from backup possible?

It is similar to what I want to do, but not exactly equivalent.

To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?

For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.


texts: Problems with sqlcmd -q and quotations marks

texts:
I have a SQL Server 2008 R2 Express edition. Because it is only an Express edition I don't have a SQL Server Agent for automation of tasks.

So I tried to do something with command line program sqlcmd.

I try to execute this command:

use DB; EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";

If I do this in SQL Server Management Studio everything works fine. But if I try to execute this via sqlcmd it doesn't work.

sqlcmd -S .\INSTANCE -Q "use DB; EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";" -o C:\FOLDER\LOG_DB_REINDEX.txt

I think this is because of the quotation marks. I also tried to use ` and ' but I didn't get it to work...

How can I resolve this issue?


texts: Enumerating short versus long column names from INFORMATION_SCHEMA views on DB2 for IBM i

texts: On the IBM i (as/400) you can have short and long column names on a table. The short names are listed in the SYSTEM_COLUMN_NAME field of QSYS2.SYSCOLUMNS and the ODBC/JDBC SQLCOLUMNS view. Is it possible to get these short column names from the INFORMATION_SCHEMA view or is it not possible since its not a feature supported in most RDBMSes?


texts: How to avoid redundant case/when in Postgres?

texts:
Based on this simple example table:

create table some_data (x integer, y integer);

insert into some_data (x,y) values
(1,1),
(1,2),
(1,1000),
(1,2),
(2,1),
(2,1),
(2,2);

I query it trying to sum the values based on particular conditions, like:

select
  case when x = 1 then
        sum(case when y = 1 then 1 else 0 end) as counter_x,
        sum(case when y = 2 then 2 else 0 end) as counter_y,
        sum(case when y > 2 then 7 else 0 end) as counter_z,
      end,

  sum(case when x = 1 then 1 else 0 end) as counter_a,
  sum(case when x = 2 and y = 1 then 10 else 0 end) as counter_b,
  sum(case when x = 3 and y = 30 then 400 else 0 end) as counter_c
  from some_data;

What I am expecting here is:

counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
----------+-----------+-----------+-----------+-----------+----------
1         | 4         | 7         | 4         | 20        | 0

However my query doesn't work as the wrapping case when x = 1 is not allowed.
How can I fix it?


texts: SQL Server Query Store on isolated volume

texts: Is there a way to isolate Query Store data to a separate volume? I have a thousand or so databases that we’ve gotten pretty good at predicting growth for. I’m unsure of how much storage Query Store will need, but to be valuable across various scheduled workflows, I need 30 days. I know I can specify storage limits for QS, but wondering if I can just add a large volume of cheaper storage and dump Query Store there?


texts: MySQL update one table from another joined many-to-many

texts:
I have two tables, events and plays joined many-to-many by a third table plays_in_events.

Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in that linked event.

I have tried

UPDATE `events`
  INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE
  events.Name IS NULL and plays_in_events.PlayID = 1

but get a syntax error

SQL Error: Not unique table/alias: 'events'

(I'm trying to limit any damage from accidental multiple updates by only testing it in the first instance for PlayID = 1, but have hundreds of records to update, so I don't want to do it manually).

There is only one table events in the database.

What's going wrong here please? I can't see it, unfortunately.

LATER

Maybe I've got the JOIN wrong altogether.

This SELECT query works to join the two main tables, and was produced by a visual query editor:

SELECT
  `events`.`EventID`, `events`.`Name`, `plays`.`Play`, `plays`.`PlayID`
FROM
  `plays_in_events`
  INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
WHERE
  `events`.`Name` IS NULL AND
  `plays`.`PlayID` < 10

How could I make the JOINS in the UPDATE based on this working?


texts: Unexpected behaviour of check constraint on Transactional Updatable article identity column

texts:
We have a table named [Session] which is replicated from DBS-MASTER to DBS-SUB1 and DBS-SUB2 using transactional updatable replication. The structure of the table (simplified) is as follows:

CREATE TABLE [Session]
(
    SessionId INT IDENTITY(1, 1) NOT NULL,
    UserId UNIQUEIDENTIFIER NOT NULL,
    SessionToken UNIQUEIDENTIFIER   NOT NULL
)

As this is an article in a transactional updatable subscription, SQL Server has been left to look after partitioning the identity values into ranges on each of the servers. Up until recently this has worked entirely as expected and without issue, however we've seen two issues, one of which caused us a major issue (service impacting due to session creation failure!):

The check constraint on DBS-MASTER was stale, meaning that the range had been exhausted and no new records could be inserted into the table. Running sp_adjustpublisheridentityrange (As documented in this stackoverflow question) resolved the issue once we determined what the problem was and how to solve it.
We've seen the table have no CHECK constraint on it at all, for a period of time. It looks like this was self-corrected when the highest used identity value on DBS-MASTER ticked up to hit the threshold set in MSpub_identity_range for it.

Item 2 is less of a concern than item 1, but still looks odd and is being detailed as it may help to identify what the problem is here.

For completeness:

the table consumes around 60,000-100,000 identity values a day
the table is almost exclusively inserted into on DBS-MASTER
there are other columns in the table (which I can describe if required) but the exact structure of the table doesn't feel related to the problem
there are business logic level constraints on the number of rows present per UserId, which are managed by deleting older entries before adding new ones
the table is ordinarily never more than 10-15,000 rows in size

Question(s)

What can we do to mitigate against the issue we saw with the range specified by the constraint being exhausted and the constraint not having been updated?
What could be causing constraint exhaustion and failure to be updated?


texts:
I have a SQL server with about 150 tables and the 5 tables that are used the most consist of over 10 million rows each. I'm trying to see why the performance of the server has been diminishing day by day.

This is a corporate SQL server and we have never had a DBA to help with the server and it has gotten to the point where pulling 1000 records from one table can take 20 min.

The cpu is not pegging above 50% nor is the ram going above 2 gig of usage.

The I/O reads however are at 1.9 trillion reads in the last 48hrs.

I need some direction on how to attack this issue.

I heard something about having rowguid's causing issues with tables with millions of rows is this accurate?


texts: Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server

texts:
I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below).

How it is implemented in SQL Server? Is it programmable? Or it's something that I should implement myself? If so, what's the best way of doing that?

My business rules are:

Server A and Server B are in different geographical locations.
Data is replicated between both servers constantly.
If Server A becomes unavailable, its users requests are redirected to Server B. Some data loss is acceptable in this case.
If user performed some writes on Server A - he must keep using Server A (with only one exception, when Server A becomes unavailable). The user can't perform some writes on Server A and some writes on Server B. But he may perform reads from both servers.

Both servers uses MS SQL Server 2012 Enterprise.

AlwaysOn Availability Groups and SQL Server Cluster for some reasons is not an option for me.


texts: Get the highest parent id recursively in MySQL

texts:
I want to find the uppermost parent ID for each entry. For example,
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);

I intent to get
ID     Highest ParentID
1      NULL
2      1
3      1
4      1

I planned to create a recursive query (MySQL 8 or MariaDB 10.5) by adding a condition to break the recursive when Parent ID is a specific value (e.g., NULL in the above case). I started with
WITH RECURSIVE cte (ID, ParentID) as (
  SELECT ID,ParentID FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT * FROM cte;

but it does not work as I intend, as it gets the next ParentID instead of recursively.
Sample fiddle.


texts: Cluster is causing my query to run SLOWER then before

texts:
So I have been stuck on this for 6 hours now and I have no clue what to do. I am doing university homework that requires us to create a unoptomized sql query (does not have to make sense) and then apply index's and see if it makes it faster (which it did for me, from 0.70 elapsed time to 0.66) and then we had to apply clusters.
I applied clusters and it has now almost doubled the amount taken to finish the query. From 0.70 to 1.15. Below is how I specified my cluster:
CREATE CLUSTER customer2_custid25 (custid NUMBER(8))

SIZE 270

TABLESPACE student_ts;

I tried all my previous times with INITIAL and NEXT but that seemed not to make a difference.  Below are the tables:
CREATE TABLE CUSTOMER18 (

    CustID         NUMBER(8) NOT NULL,
    FIRST_NAME     VARCHAR2(15),
    SURNAME     VARCHAR2(15),
    ADDRESS     VARCHAR2(20),
    PHONE_NUMBER NUMBER(12))

    CLUSTER customer2_custid25(CustID);

CREATE TABLE product18(

    ProdID     NUMBER(10) NOT NULL,
    PName    Varchar2(6),
    PDesc    Varchar2(15),
    Price    Number(8),
    QOH        Number(5));

CREATE TABLE sales18(

    SaleID     NUMBER(10) NOT NULL,
    SaleDate    DATE,
    Qty            Number(5),
    SellPrice    Number(10),
    CustID        NUMBER(8),
    ProdID        NUMBER(10))

    CLUSTER customer2_custid25(CustID);

CREATE INDEX customer2_custid_clusterindxqg ON CLUSTER customer2_custid25 TABLESPACE student_ts ;

Base Query:
SELECT
    AVG(s.sellprice), s.qty, c.custid
FROM
    SALES s, CUSTOMER2 c
WHERE
    c.custid = s.custid
GROUP BY
    s.qty, s.qty, c.custid
HAVING
    AVG(sellprice) > (SELECT MIN(AVG(price))
                      FROM product
                      WHERE pname in
                    (SELECT pname
                       FROM product
                       GROUP BY pname
                       HAVING pname LIKE 'FA%')
                       GROUP BY price);

I also tried taking the tablespace section in the cluster index away.
I followed this formula to help calculate cluster sizes:

"Size of a cluster is the size of a parent row + (size of Child row *
average number of children). "

This brought me to the size of 270. However, after testing sizes (going up 20) from 250 to 350 I found 320 to be the fastest at 1.15.

No matter what I try, I can not for the love of me get it lower then my base query times.
Other students have done the same and halved their query time.
All help is really appreciated.


texts:
I am having trouble optimizing a materialized subquery in MariaDb 10.0.15 and having difficultly understanding the documentation on whether I can even optimize this query any further.

This query takes a few seconds to run but it seems it should possible to run much quicker than that. I am using "id = IN(subquery)" but have also tried "EXISTS(subquery)". My understanding is that with this version of MariaDB it shouldn't make a difference, and both types of queries take 2+ seconds.

Query:

SELECT rem.id
FROM ri_email_messages rem
WHERE rem.id IN(
    SELECT remc.message_id
    FROM ri_email_messages_contacts remc
      INNER JOIN ri_email_contacts rec ON remc.contact_id = rec.id
    WHERE rec.email = 'email@address.com'
);

Explain result:

+----+--------------+-------------+-------+-------------------------------+------------+---------+-------------+--------+----------+--------------------------------------------------------------+
| id | select_type  | table       | type  | possible_keys                 | key        | key_len | ref         | rows   | filtered | Extra                                                        |
+----+--------------+-------------+-------+-------------------------------+------------+---------+-------------+--------+----------+--------------------------------------------------------------+
|  1 | PRIMARY      | <subquery2> | ALL   | distinct_key                  | NULL       | NULL    | NULL        |      2 |      100 |                                                              |
|  1 | PRIMARY      | rem         | index | PRIMARY                       | PRIMARY    | 4       | NULL        | 126156 |       75 | Using where; Using index; Using join buffer (flat, BNL join) |
|  2 | MATERIALIZED | rec         | ref   | PRIMARY,email                 | email      | 768     | const       |      1 |      100 | Using where; Using index                                     |
|  2 | MATERIALIZED | remc        | ref   | PRIMARY,contact_id,message_id | contact_id | 4       | orgs.rec.id |      2 |      100 | Using index                                                  |
+----+--------------+-------------+-------+-------------------------------+------------+---------+-------------+--------+----------+--------------------------------------------------------------+

My understanding is a materialized view creates a temporarily table with the result of the subquery, and the id values in the temp table are used to constrain the result in the top query.

If I run the subquery itself, it runs instantly.

If I replace the subquery just with the list of ids that are returned in the subquery, it runs instantly:

SELECT rem.id
FROM ri_email_messages rem
WHERE rem.id IN(105703,110676,111628,105844,105854,105877,110508);

What am I doing wrong here :)


texts: NT SERVICE\MSSQL$SQLEXPRESS user cannot be found when added to network drive

texts:
For SQL Server 2016 Express, I would like to backup/restore directly to/from network storage I have mapped to drive S:. SQL Server Express runs under the (virtual?) account NT SERVICE\MSSQL$SQLEXPRESS as it should, which of course does not have the permissions to access S:.

As explained in
Broken NTFS Permissions for MSSQL$SQLEXPRESS
I have tried to add NT SERVICE\MSSQL$SQLEXPRESS to the users of S:. This fails with a "Name Not Found" message-box:

  An object named "NT SERVICE\MSSQL$SQLEXPRESS" cannot be found.

My machine is not on a domain and has been restarted since S: and SQL Server Express were installed.

Why does it not find that user? Is there another way to make individual folders accessible to the default SQL Server user?


texts: connection issue / Oracle Express 11g in windows7 32bit

texts:
I have an issue with Oracle Express 11g in windows7 32bit.
Everytime when I try to conncet to the database I get this error message.
How can I fix it please!


texts: Is it a good idea to schedule a daily CHECKPOINT?

texts:
Checking our production logs from yesterday, we discovered a period of about 5 minutes when a whole bunch of really simple queries were timing out. Further investigation on the server logs showed a huge spike in disk activity, which led me to the conclusion that an automatic CHECKPOINT was being run on the DB at that time.

That's something I really don't want to happen during peak hours. So I was thinking of scheduling a daily CHECKPOINT every day during off-peak hours.

Is that a good idea? Bad idea? Waste of time? If not that, then what?


texts:
I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max).

PostGreSQL already allows varchar (without a length) as an alternative for text.

The question is, what does the ANSI SQL standard have to say about deprecating text, and the best alternative? Also, does MySQL/MariaDB also follow this?

Thanks


texts: Correct SQL Server linked server connection settings to MySQL that will allow inserts

texts:
I'm in linked server hell!  I've created a linked server to a remote MySQL database.  I'm able to query and do updates.  However when I attempt an insert I get an error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.0.92-log]Commands out of sync; you can't run this command now".

What are the correct Settings I should have?  I realize there are 3 components with settings (MySQL ODBC Driver, MSDASQL Provider, Linked Server Object).  Here's what I have so far:

ODBC Driver:
Connection Tab: Nothing checked.
Metadata Tab: Nothing checked.
Cursors/Results Tab:
     Don't Cache Results of forward-only cursors - checked
     Force use of forward-only cursors - checked
     Return mached rows instead of affected rows - checked
Debug Tab:  Nothing checked.
SSL Tab: Nothing checked.
Misc Tab: Nothing Checked.

MSDASQL Provider:
     The following are checked:
     Nested Queries
     Level zero only
     Allow inprocess

Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'HOMEPROJECTADMIN', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'HOMEPROJECTADMIN', @provstr=N'DSN=HOMEPROJECTADMIN;OPTION=3'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HOMEPROJECTADMIN',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


texts:
I want to transfer data from Oracle to Vertica and I want to know what is best way to do it?
I s there a tool for it?

I tried to dump the Oracle data and load it to Vertica using 'Copy Direct' command but I fount lots of problems specially in the data and timestamps columns.


texts: Why is my Mac install of Postgres not tokenising Thai words?

texts:
On my Mac install of PG:

=# select to_tsvector('english', 'abcd สวัสดี');
 to_tsvector
-------------
 'abcd':1
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | hello | {english_stem} | english_stem | {hello}
 blank     | Space symbols   |  สวัสดี | {}             |              |
(2 rows)

On my Linux install of PG:

=# select to_tsvector('english', 'abcd สวัสดี');
    to_tsvector
-------------------
 'abcd':1 'สวัสดี':2
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |    description    | token |  dictionaries  |  dictionary  | lexemes
-----------+-------------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII   | hello | {english_stem} | english_stem | {hello}
 blank     | Space symbols     |       | {}             |              |
 word      | Word, all letters | สวัสดี  | {english_stem} | english_stem | {สวัสดี}
(3 rows)

So something is clearly different about the way the tokenisation is defined in PG. My question is, how do I figure out what is different and how do I make my mac install of PG work like the Linux one?

On both installs:

# SHOW default_text_search_config;
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

# show lc_ctype;
  lc_ctype
-------------
 en_US.UTF-8
(1 row)

So somehow this mac install thinks that thai letters are spaces... how do I debug this and fix the "Space Symbol" definition here.

Interestingly this install works with Armenian, but falls over when we reach Hebrew

=# select * from ts_debug('ԵԵԵ');
 alias |    description    | token |  dictionaries  |  dictionary  | lexemes
-------+-------------------+-------+----------------+--------------+---------
 word  | Word, all letters | ԵԵԵ   | {english_stem} | english_stem | {եեե}
(1 row)

=# select * from ts_debug('אאא');
 alias |  description  | token | dictionaries | dictionary | lexemes
-------+---------------+-------+--------------+------------+---------
 blank | Space symbols | אאא   | {}           |            |
(1 row)

Only significant diff I am seeing is that one is compiled with clang and one with gcc

PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit

VS

 PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit


texts: Which databases are recommended to be used as embedded databases?

texts: Which databases are recommended to be used as embedded databases to store data within an application?  The embedded database may or may not synchronize back to a larger system database.


texts: Deploying MariaDB Columnstore Docker image in Azure

texts:
I deployed the MariaDB Columnstore Docker container in Windows with the following command:
docker container run  --name cont_mariadb  -e MYSQL_ROOT_PASSWORD=xxxxxxx
       -p 3306:3306  -v vol_mariadb:/var/lib/mysql -d mariadb/columnstore

This worked fine, including the external storage with the volume.
Next, I tried to do the same in Azure. I created a container instance with a volume in the file storage account. The creation had no issues, but when I start the container I see the following errors in the log:

201226 23:39:27 mysqld_safe Starting mariadbd daemon with databases
from /var/lib/mysql /usr/share/mysql/mysql.server: line 264: kill:
(411) - No such process . ERROR!
....
'mysqld_safe' failed to start (exit status 0) -- '/usr/bin/columnstore-restart':
/usr/share/mysql/mysql.server: line 264: kill: (665) - No such process

Following some research, I granted to the mysql user permission to the volume data directory and restarted the container:
sudo chown -R mysql:root /var/lib/mysql

Now I only have an error similar to the second error I had previously (mysqld_safe):

201226 23:49:18 mysqld_safe Starting mariadbd daemon with databases
from /var/lib/mysql . ERROR!  /usr/share/mysql/mysql.server: line 264:
kill: (402) - No such process

If I try to start an SQL command line in the container with mysql -u root I get the following error, indicating that mysql is not active:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

There's information about deploying MariaDB in Docker (not exactly the same as it's not the Columnstore version, but I thought it may help).
It talks about disabling AppArmor. But AppArmor is not in the columnstore image (it may be in the regular MariaDB image):
# yum list app*

Available Packages
appliance-tools.noarch                 011.1-1.el8                             epel
apprise.noarch                         0.8.9-1.el8                             epel
appstream-data.noarch                  8-20200724.el8                          AppStream

Any ideas on how to make this work will be greatly appreciated.


texts:
I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming.

SQL SERVER Command:

CREATE VIEW [dbo].[vw_PurchParent] as

WITH cte AS
 (
  SELECT a._Id, a._parentId, a._name, a._IsLedger
  FROM tbl_ChartOfAcc a
  WHERE _Id = 1
  UNION ALL
  SELECT a._Id, a._parentid, a._Name, a._IsLedger
  FROM tbl_ChartOfAcc a JOIN cte c ON a._parentId = c._id
  )
  SELECT *
  FROM cte where _IsLedger = 'Y'
GO

Error in MySQL:

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'cte AS(
      SELECT
          a._Id,
          a._parentId,
          a._name,    ' at line 1

Server version: 5.6.41-84.1 - Percona Server (GPL), Release 84.1, Revision b308619


texts: SQL Server evaluates where condition before or during the run?

texts:
I have a Stored Procedured that looks similar to this:

CREATE PROC Campaign.STP_CampaignTransaction_Get
(
    @id             INT
    @first_name     VARCHAR(50)
    @last_name      VARCHAR(50)
    ,@age           INT
    ,@gender        INT
)
AS
BEGIN
    SELECT
        [id]
        ,[first_name]
        ,[last_name]
        ,[age]
        ,[gender]
    FROM dbo.Persons p
    WHERE   (@id IS NULL OR t.id=@id)
        AND (@first_name IS NULL OR t.first_name= @first_name)
        AND (@last_nameIS NULL OR t.last_name = @last_name)
        AND (@age IS NULL OR t.age=@age)
        AND (@gender IS NULL OR t.gender=@gender)
END

Basically as you can see all the parameters they work as filters. Lets say that I send only the @first_name parameter the performance will be the same as checking the parameters once and then running the following query:

SELECT
            [id]
            ,[first_name]
            ,[last_name]
            ,[age]
            ,[gender]
        FROM dbo.Persons p
        WHERE   t.first_name= @first_name

Or for each row sql will evaluate if my parameters are null or not and go through all the conditions?

NOTE: The question started with a performance discussion in my company that if we should create 2 STPs or only one with filters


texts: Backup database by copying the MDF and LDF files

texts:
I want to backup a SQL Server database by copying its files manually. Is copying the database.MDF and database.LDF files enough or should I also add more files?

It might be not the proper way for backing up a database, but in certain cases it might help for testing purposes for example.

Suppose you are left with a corrupted OS with no any DB backups ,but you can still plug it as HDD and get those files containing the data available in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA and run the following commands

 Create database dbname.

 On.

 (
 Filename= 'mymdf.MDF',
 Filename ='mylog.LDF'. ).

 For attach;


texts: How are Postgres partitions counted for the table size limit?

texts: Postgres has a limit of 32T in a single table, but what if the table is partitioned - is that 32T per partition now?


texts: How to grant MySQL user privilege to "SHOW TABLES"

texts:
I have two MySQL databases hosted on my website, one for a MyBB forum and the other for data displayed on the website that comes from Raspberry Pis. The MyBB database has a user associated with it, and I've created another MySQL user to associate with the other database and granted it all privileges in cPanel.

When I connect to the database on the Raspberry Pi, I am able to use most SQL commands (INSERT, SELECT, DELETE, etc.) and they work fine. However, the command "SHOW TABLES" gives an "access denied" error for the MySQL user.

I tried deleting the database and repopulating it didn't help. Any ideas?


texts: how to select multiple items using the AND operator


texts: Is it safe to delete the innodb schema on a new Amazon RDS instance?

texts:
As per the title, I'm struggling to find out if it's safe to delete the innodb schema that's created on a fresh Amazon RDS instance.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             | -- This was automatically created when the instance was created
| mysql              |
| performance_schema |
+--------------------+

I know it's not doing any harm being sat there - there's no tables/content or anything, I'm just curious as to why it's there and I've not been able to find out why from any other sources.


texts:
there are already some questions about how to sort version numbers. However, these do not deal with the RPM version scheme but often semver (Major, Minor, Patch). The RPM versioning specification is a bit more open and complex. For example it is also possible to use a date based version. Here is an example for how RPMs can be sorted by the version: link
Now to the real question, how can I sort and filter the records, taking into account the complexity of the version and releases, that I get a list in SQLite and PostgreSQL that contains the latest N RPM packages per RPM? Here are sample questions to solve:

What is the latest RPM pro package?
What are the latest two RPMs for the my-first-rpm package?
What are the new five RPMs?

I have the following table with records (sqlite):
CREATE TABLE packages (
  name                  VARCHAR(64)      NOT NULL,
  epoch                 INTEGER          NOT NULL,
  version               VARCHAR(32)      NOT NULL,
  release               VARCHAR(64)      NOT NULL,
  architecture          VARCHAR(16)      NOT NULL,
  CONSTRAINT pk_packages PRIMARY KEY (name, epoch, version, release, architecture)
);

INSERT INTO packages VALUES
("my-first-rpm", 0, "0.1.0", "0", "x86_64"),
("my-first-rpm", 0, "0.1.7", "0", "x86_64"),
("my-first-rpm", 0, "0.1.10", "0", "x86_64"),
("my-second-rpm", 0, "0.1.0", "0", "x86_64"),
("my-second-rpm", 1, "0.1.0", "0", "x86_64"),
("my-third-rpm", 0, "0.1", "0", "x86_64"),
("my-third-rpm", 0, "0.1.1", "0", "x86_64"),
("my-date-based-rpm", 0, "2021010128001", "0", "x86_64"),
("my-date-based-rpm", 0, "2021010128002", "0", "x86_64");


texts: How to fetch records from table which has csv

texts:
Please guide me about following case. I need to fetch records from a table which has a column like shown in the picture. I need to fetch records which has 065 and AMT campaigns only.

I tried:

SELECT * FROM table WHERE Campaigns LIKE '%AMT%' OR '%065%'

Unfortunately, it is not working. Data should not be duplicated.


texts: Getting table definitions on the command line in MySQL

texts:
I am new to MySQL. I am trying to get table definitions from the database server (4.0.26).

I tried below steps to get information, running cmd as an administrator:

c:\> cd mysql\bin\
c:\mysql\bin>mysql -u username -p
password: password

mysql>show database

    -> (nothing comes up)

After closing and opening, same steps till password:

mysql>use database
Database changed

mysql>show tables

     -> (nothing comes up)

I need to check two table definitions. What am I missing?


texts: How to Import a BACPAC file to Azure SQL and Overwrite Existing Database?

texts: Say I have a local database named MyDatabase. I want to move it to Azure SQL and replace an existing database currently residing there named MyDatabase. I know how to create a BACPAC file locally. I know how to import the BACPAC to my Azure storage account. However, once the BACPAC is in Azure storage, I don't know the preferred way of overwriting the existing MyDatabase database with the copy in storage. I could import the BACPAC file and create a second database, then delete the first, and rename the database just imported. However, is the best or preferred way of doing this?


texts: Migrating SSRS from SQL Server 2012 to 2016

texts:
I have a requirement of migrating my whole SQL Server from SQL Server 2012 to 2016. I have already migrated the database engine part. Since I have not really worked on SSRS (reporting part), so need some immediate help.

Following steps I will be taking:

I have to backup and restore the ReportServer database and ReportServerTempDB
I have to backup and restore the encryption keys
I will congifure SSRS through Reporting Server Configuration Manager

What else steps should I be doing to be able to run the reports here on SQL 2016 same as it used to run on the server for SQL 2012. I have heard we have to manually copy so RDL files to new server etc.

Kindly help!


texts: Select all customers and number of orders per month as csv

texts:
This is what I have:

customer
--------
customerId INT(10)
name TINYTEXT
phone VARCHAR(10)
...

order
-----
orderId int(10)
customerId INT(10)
dateCompleted DATETIME
...

This is what I need:

custId  name  thisMo  lastMo  last3Mos  monthsThisYear              ordersPerMonth
------  ----  ------  ------  --------  --------------              --------------
1       John  5       10        28      2019-01,2019-02,2019-03,..  7,11,9..
2       Bill  9       15        33      2019-01,2019-02,2019-03,..  12,10,14..
3       Jack  3       8         19      2019-01,2019-02,2019-03,..  3,7,5..

Trying to generate a table showing customers, number of orders they've placed this month, last month, and last 3 months, along with a spark line chart showing number of orders per month for the entire past year (hence the comma separated lists of months and orders per month)

Currently, they're using a single query to get all customers, then each customer is looped through to generate the table and additional queries are made to get the numbers. The queries to get the csv are as follows:

SELECT GROUP_CONCAT(`count` SEPARATOR ',') AS `countCsv`
FROM (
    SELECT SUM(IF(`customerId` = {$row['customerId']}, 1, 0)) AS `count`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

SELECT GROUP_CONCAT(`month` SEPARATOR ',') AS `monthCsv`
FROM (
    SELECT DATE_FORMAT(`dateCompleted`, '%Y-%m') AS `month`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

...but this is resulting in 5 extra queries per row per page load and it's taking forever. I've been trying all day (8-1/2 hours now) to search and extrapolate from similar questions and I just can't get anything even close to working in a single query.

Edit - added customerId to order table to show customerId as the relationship between the two tables.


texts: Select rows from table having more than two specific values

texts:
I have a table having details such that

Userpin  product       Time1      contains
4556     23            7:30         A
4557     23            7:30         B
4558     23            7:30         C
4559     23            7:30         D
4560     23            7:30         E
4561     24            7:30         A
4562     24            7:30         B
4563     24            7:30         C
4564     24            7:30         D
4565     24            7:30         E
4566     25            7:30         A
4567     25            7:30         A
4568     23            7:30         F
4568     23            7:30         G

So with the above table, I need all products that contains (A,B,C,D,E,F) all of them.

With the given table, I am expecting 23 as the result (Although product 23 contains G as well, but that does not bother me.).


texts:
In a blog post from 2008 the famous Kalen Delaney wrote:

  What is the difference between statistics and indexes? Do auto created
  statistics indicate that you should build an index?

  Check out this the answer to this question at SQLCommunity.com

Unfortunately SQLCommunity.com is no longer. But the answer to this very question I would very like to know. Especially to the second one.

Update: An insightful article is here: http://msdn.microsoft.com/en-us/library/ms190397.aspx


texts: Unexpected growth Transaction log file 100 GB which is part of Transactional Replication

texts:
I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.

Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.

While database under testing mode...

Subscriber of replication is also primary replica of Always On Availability Group.

How to reduce the size of transaction log file?

I had taken log backup and in full recovery model.

SELECT name, log_reuse_wait_desc FROM sys.databases;

...returns LOG_BACKUP.

Replication is running successfully. Subscriber is receiving changes from publisher.


texts: how to prevent multiple pg_dump to execute at the same time

texts:
I am exposing a REST API to backup a Postgres DB. I have few questions related to that. I have seen most of these questions floating around google but could not find any suitable answer

Is there any way to know the status of the pg_dump? I can redirected the verbose log to the user but somehow I am not convinced with that.
Is there any way to know that a "pg_dump" session is in progress so that I can prevent the user from re-triggering?


texts: Create unique sequence per set of rows sharing the same foreign key value


texts: How to avoid certain characters in MySQL full text search?

texts:
I have a column named parcel_number of varchar type. It contains values like ab-cd-ef and uvwxyz.  A search for abcdef or uv-wx-yz does not return those records.

How should I write the query to meet this requirement?


texts:
Trying to hunt down an issue with an SSIS package we have and I don't know what to look for to help correct this issue.

Error:

Errors in the metadata manager. The process type specified for the Effect dimension is not valid since it is not processed.

I tried googling this and haven't seen anything that could point in a direction of what I should be looking at or checking.


texts: Why do I have to backup a transaction log twice for it to shrink?

texts:
This question was attempted twice on experts-exchange with no real answer.

I know that I should not shrink the log file - that is not the question.

If you feel like answering that one should not do it- that has been confirmed many times yes and it is not the goal of this question.

Setting to Simple backup is also not an option.

It has been bugging me for years - the fact that I do now know why it should be backed up twice.

I recently saw the first log file that was able to shrink every time on the first attempt - it was unbelievable. I asked the guy about it and all I could make out was "reorganize before releasing" - I do not know what he was referring to. I am not able to contact him again.

I know this from the existing posts on E-E:
"Shrinking removes inactive parts of the log.  In order to be inactive, the log must be truncated which occurs as part of log backup.

However, transaction log is made up of many virtual log files that are used in a round robin fashion.  Only virtual logs at the end of the file can be pruned so if SQL server is currently using the last virtual log, nothing can be removed.  The old fix in sql 7 was to run a script that ran enough dummy transactions to fill up the log and wrap the pointer around to the first virtual log (you can still use this method if you prefer).
http://support.microsoft.com/kb/256650/EN-US

DBCC now does this for you but still requires the extra step of truncating the log again so the inactive space can be deleted"

From another user:
"All I had to do was discontinue the automated optimization and integrity checks the last Admin had left on the database maintenance plans."


texts: DR/Reporting solution for a large SQL Server database

texts:
We have a large (3TB) database that is a part of AOG. We now have a need for a third copy of the data. Here are some of things we need:

The third copy needs to be in sync with the primary in real time (or near real time); worst case we can live with a 24 hour lag.
The third copy cannot be PRIMARY NODE any time. (This is just a reporting server)

What are my options?

I don’t want to configure replication on top of AOG. That seems like a complex option. I can add a third node for the AOG. This could add some dependency on the primary; I’d prefer the new architecture to minimally interfere with the other 2 nodes.


texts: Split Backup of MSSQL VS Stripe Backup of Sybase ASE

texts:
I recently came to know about concept of split backup in MSSQL Server whereas I have been working on stripe backup in Sybase ASE.

Stripe backup in Sybase ASE is faster than normal backup and if we use three stripes then Sybase will use 3 cores of CPU(Depends on number of cores) as it will be split into that many threads, are performed in parallel irrespective of storage location.

I wanted to understand if same is the case with MSSQL split backup, I read that if we use different locations for split backup then the backup will be performed in parallel and will be faster:

Split Backup

however if storage location is same( In the same drive and folder), no extra IO involved and hence not much impact on performance.


texts: Automatic migration failed on Azure Database

texts:
I am using Entity Framework 6.1.1. The database has three million records; migration has to change the precision of a column of type decimal. The migration timeout window is set to the maximum value, CommandTimeout = Int32.MaxValue;

I was testing this scenario on Azure SQL Database. After trying for around 90 minutes, it ends up with an exception, but no migration done.

Exception details:

  The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

My questions are:

Is it the expected behavior to take more time for migration of this type on a huge DB? (Because after changing the precision it has to convert the existing value and save it back, and it has to be repeated on 3 million records)
How to  resolve this? I don't think we can have migration in chunks of transactions.


texts: Is it critical for a database to have a relationship table/diagram or not?

texts:
Got a question and wanted to get the best practices or thoughts on this.

I have an application that was developed with a SQL Server back end (site is Entity framework, vb.net, asp.net). The original design does not have a database diagram/schema/relationship table in the db itself.

The site works great but have gotten some pushback from another group that says it has to have a relationship table in order to work properly.

So is this personal preference or what? I'm not a DB guru and trying to find out if it's two ways of development and someone is trying to push personal preference over standards or best practices.


texts: pg_commit_ts growing in postgres instance

texts:
I have a customer who is trying to set up bidirectional replication with logical replication publication/subcription model across two instances with the help of last_update_wins and reported that with little to no activity he is running out of space. He only have a 5 GB disk as of now with his POC out of which ~2 GB is occupied by wal files (no big fluctuation with the usage) however from the Host console I see that there pg_commit_ts folder keeps growing and is currently @1.7 GB

2.4G    pg_wal
1.7G    pg_commit_ts
96M     db/base
44M     /pg_xact

From postgres documentation I see that this is Subdirectory containing transaction commit timestamp data. I then noticed that he also have enabled "track_commit_timestamp" which collects commit time of transactions.

I'm trying to figure out 1. Is there any way to control retention on pg_commit_ts   2. Is this normal to have huge "pg_commit_ts" with the parameter enabled or does this have to do with their use case.

Please advise.


texts: What is the maximum value for the index_length of a MySQL 5.5 table?

texts:
I'm trying to find out why a series of inserts is painfully slow (1 minute 15 seconds, on average) on a dedicated MySQL 5.5.47 server, while the same inserts are normally fast (less than 5 seconds) on a Docker test server with MySQL 5.5.46. Storage engine is InnoDB on all relevant tables.

While comparing the characteristics of the tables involved, I noticed that, on the server where it's so slow, the following query returns 65536 for index_length:

select table_name, engine, index_length
from information_schema.tables
where table_schema = 'suspicious_table';

And 65536 = 2^16. Could it be that I've hit a MySQL limit? Is it possible to have indexes larger than 65536?

I'd be glad to provide a explain output if it could help, but the above mentioned "actions" involve a lot of inserts, not just one or two, and on MySQL 5.5 and earlier versions, explain only works for selects, not for inserts, updates, or deletes.


texts: How to Count Rows in a Separate Table Using a Foreign Key

texts:
I am in school to become a software engineer, and I am taking my first class on SQL. We usually use MsSQL, but the schools VMWare instance is down, so I have an older version of Oracle on my computer (Oracle 11g Express). I am trying to take my customer table, and orderInfo table to get the count of each customers # of orders. I have it working if I use the orderTable, but then it would not get customers that have 0 orders. Basically, if the customer is not in the orderInfo table, they won't have an order count. Please See the table structure below and my SQL code.

Customer Table

Order Info Table

This is my current SQL code and the output.

SELECT CUSTOMER_ID, COUNT(ORDERINFO.ORDER_ID) AS TotalOrders
FROM ORDERINFO
WHERE CUSTOMER_ID = CUSTOMER_ID
GROUP BY CUSTOMER_ID;

Output:

Thank you so much for the help in advance!


texts: Encoding issue with SQL Server VARCHAR column retrieved in Python

texts:
We recently had an issue with encoding related to a field that's being stored as a varchar(120) in SQL Server.  In SSMS, the varchar appears as:

"Who Killed JonBen‚t?"

However, when it's brought into python, it appears as:

I've researched this from the Python side, and nothing strange is going on.  My theory is that the varchar in SQL Server is accepting UTF-8 characters which are displaying differently in python than SSMS.  I'm not very familiar with encoding in SQL Server.  Can someone please let me know the following:

Is there a way in SSMS to view the encoding of the varchar?  For instance see \x82 instead of displaying the comma as it is currently from SSMS?
We're using SQL Server 2008.  Is there any way to change the encoding for any UTF-8 characters to ASCII characters without using import /export tools or dumping to a flat file?  I.e. can I make this conversion via a query?
Is there any way to programmatically identify problematic records via a query (problematic being defined as UTF-8 characters that are not supported via ASCII)?

Thank you in advance!

Using sp_help N'table_name'; I found that the Collation of this VARCHAR column is: SQL_Latin1_General_CP1_CI_AS.


texts: MongoDB thinks it's not on XFS despite being on one

texts:
From what I can understand/read it should suffice to have the filesystem as XFS and the dbpath set to a folder on that disk. What else do I need to do to fix this? I moved the whole db folder from the initialized ext4 folder it was mounted on in the template. Is there a lock file I need to clear or something so that it re-checks the filesystem rather than thinking it's the old ext4 ?

Log

Server has startup warnings:
2019-08-28T08:34:56.863+0000 I STORAGE  [initandlisten]
2019-08-28T08:34:56.863+0000 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2019-08-28T08:34:56.863+0000 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem

However....

parted -l

Model: Msft Virtual Disk (scsi)
Disk /dev/sdc: 275GB
Sector size (logical/physical): 512B/4096B
Partition Table: msdos
Disk Flags:
Number  Start   End    Size   Type     File system  Flags
 1      1049kB  275GB  275GB  primary  xfs

/etc/fstab

UUID=cdf895f5-190c-422a-b9a5-e8ba4cfad807       /        ext4   defaults,discard        0 0
UUID=567781bc-8045-4e0d-a4f9-a971b671e920       /data    xfs   defaults,nofail,noatime         1 2

mongodb.conf

# Where to store the data.
dbpath=/data/mongodb/data/db

#where to log
logpath=/data/mongodb/log/mongodb.log

logappend=true

# Enable journaling, http://www.mongodb.org/display/DOCS/Journaling
journal=true


texts: How to turn on the expanded mode in Oracle SQL*Plus?

texts:
As in the switch \x of PostgreSQL.

For more information on the PostgreSQL expanded mode, fetch https://serverfault.com/questions/618642/psql-expanded-mode-equivalency-for-mysql.

Both PostgreSQL and MySQL have mechanisms for getting an expanded mode, I guess Oracle should have it too.

I am using the instant client 11.2 by the way.


texts: MySQL InnoDB data file directly on a disk slice (fixed-size)?

texts:
It seems that ibdata1 is like a disk slice. In Solaris (UNIX) UFS filesystems (the common one before ZFS), one would separate their disk c0t0d0 into slices, say a 10GB drive would be cut into three sections, 4GB, 1GB and 5GB. These would be fixed-size filesystems. The 4GB for, say, OS. 1GB for swap, and 5GB for software and data.

swap can be stored on a file on a filesystem, but for performance it usually stored on its own section of the disk.

Can ibdata1 be linked to its own slice for performance boost? Of course one would have to think carefully before deciding what fixed size is ideal and they would also have to know how to check usage levels.

A symbolic link can be placed from .../mysql/data/ibdata1 to /dev/rdsk/c0t0d0s3. MySQL would then see it as a file, but might perform better because it does not have to go through the file-system layer, it would write directly to a designated section of the disk.

Has anyone tried this?
Does anyone think this is a bad idea? (Keep in mind, this idea is for InnoDB-only database-only servers. Not multi-use servers.)
Would it work (Does innodb need to check the file length? If not, it should work.)
Can the usage be checked?


texts: Insert file content (with white line and special characters ) in bash variable for insert in oracle table with sqlplus

texts:
I write this simpe test.ksh scipt:
#!/bin/ksh

TEST=$(cat $1 )
sqlplus -S *****/*****@*****<<- EOF

set define off;
SET SQLBLANKLINES ON;
INSERT INTO DBADOC.TEST
(
        "TEST"
)
 VALUES
(
      q'[ $TEST ]'
)
;
commit;
EOF

exit

Now if i run it with a simple file (3 lines without special char):
./test.ksh /home/oracle/scripts/test.txt
1 row created.

Commit complete.

It runs perfectly.
If I choose a complex file (with blank lines and special character ), it doesn't work:
./test.ksh /home/oracle/scripts/check_free_space_dg.ksh
SP2-0734: unknown command beginning "# ========..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) NOM..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) GRU..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) DES..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
[...]


texts: Collecting trace info of SQL Server 2005 using SQL Server 2008 R2 profiler

texts:
I have a vs2005 application that uses SQL Server 2005. I was trying to collect the SQL trace generated by the application while executing. For collecting the trace I've connected to SQL Server 2005 through SQL Server 2008 R2 Profiler. To my disappointment I found no trace data were collected by the SQL Server 2008 R2 Profiler.

I'm using Win 7 64-bit

Am I missing anything ? Can anyone advise on this ?


texts: Why does key "concatenation" help to bring an unnormalized table to 1NF?

texts:
This question is about the proposed technique to bringing an unnormalized table to first normal form (1NF) that my textbook "A Guide To MySQL" presents. It gives an example of an unnormalized table with repeating groups:

ORDERS (ORDER_NUM, ORDER_DATE, (PART_NUM, NUM_ORDERED)).

There are multiple entries in the PART_NUM column because a customer can order many parts at once (associated with a single order). The solution is to create a composite key (ORDER_NUM, PART_NUM), so now there are multiple rows in the ORDERS table with the same ORDER_NUM but different PART_NUM that represent a customer ordering multiple parts.

My question is how does that solution make repeating groups go away? Couldn't I have a composite key with ORDER_NUM and PART_NUM but with PART_NUM still having multiple entries? I'm confused. Unnormalized table:

Normalized to 1NF table:


texts: how do I check the last time dbreindex or an index defrag was executed

texts:
Is there a quick tsql I can use to check when was the last time a dbreindex, index defrag and update stats was executed on the database?

Appreciate everyone's quick response.


texts: Avoid user login Directly to AlwaysOn High Availability Primary

texts:
I have setup an AlwaysOn High Availability in Sql Server 2014.
my IP addresses are according to this :

192.168.1.189  -- Listener
192.168.1.180  -- Replica
192.168.1.181  -- Primary

Right now users can login to my Primary directly and I want to avoid this.
Is it possible ?


texts:
I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use percona tools or mysqldump itself?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN
    SET @stmt := CONCAT(
        'ALTER TABLE testPart1 DROP PARTITION '
    ,   ' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN
  SET @stmt := CONCAT(
        'ALTER TABLE testPart1 ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 2
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;


texts: ORA-00942: table or view does not exist while creating synonym

texts:
We are using oracle 11g, the db structure goes like this:

We have users CST and FAS.
CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user.
ST_ROLE is a role which has given SELECT UPDATE DELETE ALTER privileges on ST_CAT2. ST_ROLE is granted to FAS.

Now, while creating a view as FAS,

CREATE OR REPLACE VIEW "FAS"."EXTERNAL_SR" ("PROD_ID", "PRODUCT") AS
SELECT
prod_id,
    product
FROM
st_cat;

we are experiencing the error

ORA-00942: table or view does not exist

what could be the cause?


texts: SQL Server set to force encryption, SSMS says it's not

texts:
I've set my MS SQL Server instance to Force encryption:

I've saved this setting and restarted SQL Server (and thus SQL Server Agent).

However, when I connect to this server with SQL Server Management Studio, I see two things.

First, this query says my connection is encrypted (found the query on the internet):

USE master
GO
SELECT encrypt_option FROM sys.dm_exec_connections
GO

But when I look into the Properties dialog of SSMS, it says the connection is not encrypted:

So which is it? Have I correctly forced encryption? I must add that this is using a self-signed certificate (ie, just set the flag to Yes).

Update: funnily enough, I did it on another server and there the Properties panel does say it's encrypted.

Update 2:

In both cases, I'm using SSMS 2016 (13.0.16106.4) on my local machine, making a call to the other server.

The @@version for the one that says it's encrypted is Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)   Oct 20 2015 15:36:27   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor).

For the one where the panel says it's not encrypted, I get Microsoft SQL Server 2012 - 11.0.5343.0 (X64)   May  4 2015 19:11:32   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor).

I see the "working" one has a service pack applied?


texts: SQL Server 2012 import/export wizard not showing at all

texts: So I have access of two databases that are stored in Azure, and using SQL Server 2012 to access them. All I need to do is copy a table from one db and duplicate and create it in another. I keep reading everywhere that all you have to do to bring up the import and export wizard and right click the db, go to tasks and then the option for the wizard will be right there. But I cannot find it in the menu anywhere! I feel like I'm crazy or something, but I hope this is a simple solution! Here is a screenshot of what I am seeing. Any help would be greatly appreciated! Thanks!


texts: PostgreSQL on Ubuntu - Using conf files for recovery

texts:
I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where rest of conf files present and tried to start the DB using

sudo service postgresql start

expecting the service to recognize that I am doing a recovery using recovery.conf. I found this file was ignored. Then, I had to copy all *.conf file to my data directory (in my case /dbdata/data) and then, instead of using script from init.d, I had to start the database from 'postgres' user with

/usr/lib/postgresql/8.4/bin/pg_ctl -D /dbdata/proddata  -l /var/log/postgresql/postgresql-8.4-main.log start

How I can make my recovery procedure to obey /etc/init.d/postgresql start|stop|restart
or to have pg_ctl command to look for conf files /etc/postgresql/8.4/main ?

Note : I am relatively newbie to PostgreSQL and to database administration in general