texts: Import in postgres json data in a csv file

texts:
I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type.

One line of my csv file contains something like

1,{"a":"b"}

Suppose the table has a schema

id              | smallint          |
data            | jsonb             |

If I try just to insert the data, everything works fine

INSERT INTO table VALUES (1, '{"a":"b"}');

Trying to import directly from the file with

COPY table FROM '/path/to/file.csv' DELIMITER ',' csv;

gives me the following error:

ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY availability, line 1, column services: "{a: b}"

I tried to quote the fields with ', with ", with \" and \', but nothing works..

Which is the correct syntax do do it?


texts:
*/

SET colsep ';'
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 800
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET NUMW 20

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT '.csv' spool_extension
FROM dual;
/

SPOOL \\SERVERNAME\FOLDER\FOLDER\FOLDER...ETC\InternationalReport&&suffix
@@"C:\FOLDER\FOLDER\International_report\International";
/
Exit;

Currently, it will spool the .csv file but I need it to have an output of InternationalReportyyyymmdd.csv

Any ideas on how exactly to set this up? Thanks!


texts: PostgreSQL: Query DB for rows containing integer[] > or < than specified value

texts:
I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less than a specified value? Here's an example table:

+=========+==================+
| city    | temps            |
+=========+==================+
| Seattle | {65,72,63,56,72} |
+---------+------------------+
| Miami   | {83,75,69,72,79} |
+---------+------------------+

Let's say I want to query for cities which have experienced a temp higher than 80. In this example only Miami has experienced temps higher than 80, so only that row should be returned. I've tried a few queries with no success, looked into intarray, but that doesn't seem to solve my problem either. Thanks much!

I'm running PostgreSQL 9.1


texts: SQL Server 2014-Can we stop sql auto recompile process?

texts: Is there a way to check what is causing a stored proc to recompile (other than index rebuild, stats update and parameter sniffing-without running profiler trace or extended events) and can we stop/cancel/ Kill the auto recompile process to reduce CPU load?


texts:
I have nearly no experience in database design, so forgive me if I'm asking something super nooby and ridiculous :)

I have an item I need to create. This item will be constructed from 3-4 types of objects, with multiple object instances. Basically as per below:

Object 1: only 1 instance per item
Object 2: extends Object 1 and shares a foreign key, may have multiple instances, one instance mandatory
Object 3: extends Object 2 and shares a foreign key, may have multiple instances, one instance mandatory . There are 3 types of Object 3 which are dependent on a field from Object 2 (haven't yet figured out how these need to link without code).
Object 4:  extends Object 3 and shares a foreign key, may have multiple instances, not mandatory, so may be omitted.

Is there a way to dynamically generate Object 3 through MySQL? Like if field #3 shows Object 3.1 call that table, if it shows 3.2 call that instead. Similar to a constructor.

Also is there a way for me to map related objects? For example, since everything is dependent on Object 1, I'd like to be able to call Object 1 and have it pull all related instance of Objects 2, 3, and 4 related to that specific object. I was thinking of creating a separate table to hold primary keys of related objects, but I'm thinking there is probably a better way of doing that.

Also I've read that I shouldn't use stored procedures, and instead do everything with code. Is that correct, because I would think for my model it would be more convenient and cleaner to just call on Object one, and have MySQL do all the joins internally and return a single completed object

Thank you very much for your help.


texts: Phpmyadmin feed a timestamp column with "0000-00-00 00:00:00"?

texts:
in PhpMyAdmin (OVH Mutu), a table with column type timestamp.
Impossible to put into this colum a timestamp :
Always display datetime like 2017-01-09 23:02:01 !!!
It generate several problmes : default current_timestamp give in this table "0000-00-00 00:00:00". Calculate delays is impossible.
The only way : define a int(11) type column.
An idea ?...


texts: Personal Finance App Monthly Reports Database Design ( SQLite )

texts:
I am building a personal finance mobile app that supposed to generate monthly reports, now I have a problem, confused about how to design the database to be monthly, I need the wallet to get archived after a month from the day it's created and transfer the remains/loss to the new wallet ( next month ) if exist.

Now my idea is to generate one wallet with xx money and let the user change the start date and make the mobile app automatically generate a new wallet every month on that day with the necessary transactions like there a loss/remain from the old wallet, and archive or prevent the user to make any transaction to the old wallets.
does my logic make sense or ...? ( Note I'm still learning and this my first app using SQL/Database so please be patient with me )


texts: Move SQL Server 2008 database from machine A to B with no downtime

texts:
I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by there are new data in the db so we cant do it at some random time in the middle of the night.

Is there any way to achieve this? I am pretty much able to do whatever I want so any suggestion will be useful.


texts: How do I grant a SELECT permission to an individual user who's part of a large group that is explicitly denied?

texts:
I have a table SuperSecretData which is currently setup to deny SELECT to everyone in the company by the standard Active Directory account "Domain Users". But a couple of specific individual users do need to be able to SELECT from it.
What's the proper way to achieve this?


texts: PostgreSQL function definition "syntax error at or near +"

texts:
Suppose a simple query:

(SELECT MAX(timestamp) FROM events e WHERE e.id < some_id)
  + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id)
    - (SELECT MAX(timestamp) FROM events e WHERE e.id < some_id)) / 2

If some_id is the parameter, this finds the approximate timestamp based on some sequence of events (it takes the previous and following event and averages their timestamps).

This query works great, now I want to package it as a function:

CREATE FUNCTION id_to_timestamp(integer) RETURNS timestamp with time zone AS
$BODY$
    (SELECT MAX(timestamp) FROM events e WHERE e.id < $1)
      + ((SELECT MIN(timestamp) FROM events e WHERE e.id > $1)
        - (SELECT MAX(timestamp) FROM events e WHERE e.id < $1)) / 2
$BODY$
LANGUAGE sql;

This suddenly fails with message

ERROR:  syntax error at or near "+"
... events e WHERE e.id < $1) + ((SELECT...

What am I doing wrong? Why is the + operator problematic?

(The actual identifiers are anonymized.)


texts: Huge database logging of event type rows and ways to optimize it

texts:
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the last 1-2 months.

Each event corresponds to a resource_id (estimated 5000).

Since just keeping all these in one table gets all maintenance and selects quite slow and resource consuming I was thinking on improving it in some ways:

a) separate every resource_id to each table. 5000+ tables. Selects from multiple resource_ids (maximum 100) will need some rewrite and unions or something.
b) separate every day to a different table. unions or something would be required when selects will require data from multiple days.
c) both the above (too extreme I think)
d) partitioning in someway on timestamp index and maybe even resource_id index. Is it worth it? Or "manual" partitioning of above suggestions is better?

The database is PostgreSQL.


texts: PostgreSQL 9 'pgstats wait timeout' problem

texts:
I'm asking about this here because in the PostgreSQL mailing lists no one answered me.
In one of my servers (Windows 2008 R2 Standard/PostgreSQL 9.0.5 64 bits) the logs are being flooded with the following message:

2012-03-26 00:00:11 BRT AVISO:  pgstat wait timeout
2012-03-26 00:00:19 BRT AVISO:  pgstat wait timeout
2012-03-26 00:00:31 BRT AVISO:  pgstat wait timeout

I checked other threads in the PostgreSQL mailing lists that were answered, but there are no definitive answer. Some speculate about I/O overload, but I don't think it would cause this message to be logged almost all the time that the server is up.
Do you, that use the combination of Windows+Postgresql, have ever had this problem? If yes, were you able to fix or identify the problem?

Thanks


texts: Dramatic decrease in SQL Azure performance after scaling to new edition

texts:
Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2).

Since then, it has been behaving rather erratically.

We have a relatively simple table called dbo.Asset.  On it, there is a column called ContentSetId which is of type integer.  This column is also covered by an index:

CREATE NONCLUSTERED INDEX [IX_Asset_ContentSet] ON [dbo].[Asset]
(
    [ContentSetId] ASC
)

The table contains around 2 million records.

The below query took 36 seconds to run the first time, but 0 seconds on subsequent runs.  We are seeing similar behaviour in other queries too, whereby they can take upwards of a minute to return under 1000 rows, but subsequent runs are much, much quicker.

SELECT TOP 100 * FROM dbo.Asset WHERE ContentSetId = 3

As stated above, we have only noticed this behaviour occurring since changing from the business edition to the new standard s2 one.

Any suggestions would be very welcome!


texts: Why does BETWEEN use a btree index but "element is contained by" range operator (<@) does not?

texts:
I've got a table with a utc timestamptz which has a "btree" index on the utc column:
CREATE TABLE foo(utc timestamptz)

CREATE INDEX ix_foo_utc ON foo (utc);

This table contains about 500 million rows of data.
When I filter utc using BETWEEN, the query planner uses the index as expected:
> EXPLAIN ANALYZE
SELECT
   utc
FROM foo
WHERE
    utc BETWEEN '2020-12-01' AND '2031-02-15'
;

QUERY PLAN
Bitmap Heap Scan on foo  (cost=3048368.34..11836322.22 rows=143671392 width=8) (actual time=12447.905..165576.664 rows=150225530 loops=1)
Recheck Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Rows Removed by Index Recheck: 543231
Heap Blocks: exact=43537 lossy=1818365
->  Bitmap Index Scan on ix_foo_utc  (cost=0.00..3012450.49 rows=143671392 width=0) (actual time=12436.236..12436.236 rows=150225530 loops=1)
Index Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Planning time: 0.127 ms
Execution time: 172335.517 ms

But if I run the same query using a range operator, the index isn't used:
> EXPLAIN ANALYZE
SELECT
   utc
FROM quotation.half_hour_data
WHERE
    utc <@ tstzrange('2020-12-01', '2031-02-15')
;

QUERY PLAN
Gather  (cost=1000.00..9552135.30 rows=2556133 width=8) (actual time=0.179..145303.094 rows=150225530 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on foo  (cost=0.00..9295522.00 rows=1065055 width=8) (actual time=5.321..117837.452 rows=50075177 loops=3)
"Filter: (utc <@ '[""2020-12-01 00:00:00+00"",""2031-02-15 00:00:00+00"")'::tstzrange)
Rows Removed by Filter: 120333718
Planning time: 0.069 ms
Execution time: 153384.494 ms

I would have expected the query planner to realise that these are doing the same operation (albeit that <@ is right-hand exlusive and BETWEEN is inclusive.)
So why are these query plans so different? (Forget about asking why the sequential scan query completes more quickly??!!)

My Postgres version:
"PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit"


texts:
This is part of a query result :

I'm going to add a constraint to my query to remove the orders that don't have an invoice_type = final, so the first three rows should be removed and the last three rows should remain in the result.

How should I do that?


texts: Any way to squeeze performance from Postgres JSONB queries?

texts:
This is using RDS Postgres 9.5.2

We have a table which contains about 25 columns, and one is a jsonb col that essentially is a simple/flat json object (key/value, not complex objects).  The data in the jsonb column is a mix of types as well as number of keys and length of data.  This has made an in-place altering to col to an hstore impossible thus far due to the length of the data stored in there.

At 3.5 mil rows, querying the jsonb col is creating a performance bottleneck and I'm unsure if there's a way around it.  Our queries can use the jsonb in a select, as a filter, or even a group by.  Here is an example of it in a select (with analyze):

EXPLAIN (ANALYZE, BUFFERS)
SELECT submit_time
        , answers->'QSatR3_value' AS "answers__QSatR3_value"
        , answers->'QSatR6_value' AS "answers__QSatR6_value"
        , answers->'QSatOR7_value' AS "answers__QSatOR7_value"
        , answers->'QSatR2_value' AS "answers__QSatR2_value"
        , answers->'QSatStaffR1_value' AS "answers__QSatStaffR1_value"
        , answers->'QSatStaffR2_value' AS "answers__QSatStaffR2_value"
        , answers->'QSatStaffR3_value' AS "answers__QSatStaffR3_value"
    FROM response
     WHERE fk_id = '0d95d6bd-f437-483c-8bbc-a38f11990153'::uuid
         AND status IN ('Completed','Partial','Invited')
         AND submit_time BETWEEN '2015-01-01' AND '2015-12-31'

In this example fk_id, submit_time are both have b-tree indexing as well as a gin index on the jsonb col flatten.

EXPLAIN ANALYZE (with BUFFERS):

Bitmap Heap Scan on response  (cost=23930.35..252834.17 rows=62340 width=156) (actual time=226.943..12643.450 rows=388668 loops=1)
  Recheck Cond: ((fk_id = '0d95d6bd-f437-483c-8bbc-a38f11990153'::uuid) AND (submit_time >= '2015-01-01 00:00:00+00'::timestamp with time zone) AND (submit_time <= '2015-12-31 00:00:00+00'::timestamp with time zone))
  Filter: (status = ANY ('{Completed,Partial,Invited}'::text[]))
  Heap Blocks: exact=149116
  Buffers: shared hit=4148144
  ->  BitmapAnd  (cost=23930.35..23930.35 rows=83039 width=0) (actual time=194.092..194.092 rows=0 loops=1)
        Buffers: shared hit=3443
        ->  Bitmap Index Scan on customer_00b3bd7e  (cost=0.00..8961.86 rows=391124 width=0) (actual time=48.794..48.794 rows=389534 loops=1)
              Index Cond: (fk_id = '0d95d6bd-f437-483c-8bbc-a38f11990153'::uuid)
              Buffers: shared hit=1495
        ->  Bitmap Index Scan on response_submit_time_7262d9fd_uniq  (cost=0.00..14937.07 rows=712064 width=0) (actual time=113.067..113.067 rows=711760 loops=1)
              Index Cond: ((submit_time >= '2015-01-01 00:00:00+00'::timestamp with time zone) AND (submit_time <= '2015-12-31 00:00:00+00'::timestamp with time zone))
              Buffers: shared hit=1948
Planning time: 0.190 ms
Execution time: 12736.179 ms

At the end of the day, each time a select of a column is added it adds maybe 2.5 seconds to the overall query, so larger queries can take upwards of 60 seconds to return. Since the data in the jsonb col is variable I cannot simply add indicies for the keys, but I can add four or five of what seem to be common keys used in queries.

Is there anything else that can be done to speed this up?  I cannot create use the FK UUID field in an index as RDS does not allow extensions to be added, so I cannot try that.  Is there anything that I can within the jsonb index or even how we are fetching the data?

Update:
As to why we are returning so many rows: this data sits under our reporting platform, and the result sets are passed into python modules that create desired statistical metrics using the data.

The actual result set of this query is about 398k which is then distilled into the desired statistical metrics and cuts.  We have not had a problem with the speed until we recently loaded an additional ~1 million records.  I'm working to understand if we can push the metrics calcs to the database, but that's a much larger change across our platform.


texts:
I have a query that uses a couple joins to get a name from another table.

It works okay except that I would like to combine "Examiners Name" instead of having multiple duplicates:

ID    |  Name       | Examiners Name
1290  | Jun         | Aron
1290  | Jun         | Mark
1292  | Takahiro    | Didier
1292  | Takahiro    | Eiro
1292  | Takahiro    | Kurt

What I am looking for:

ID    |  Name       | Examiners Name
1290  | Jun         | Aron, Mark
1292  | Takahiro    | Didier, Eiro, Kurt

Here is my query:

SELECT DISTINCT
usr.CandidateID,
usr.FirstName,
Examiner.FirstName AS [Examiners Name]
FROM
dbo.tbl_User AS usr
LEFT JOIN dbo.Tbl_ExaminerCandidates AS ExamCa ON usr.UserID = ExamCa.CandidateID
LEFT JOIN dbo.tbl_User AS Examiner ON ExamCa.ExaminerID = Examiner.UserID
WHERE
usr.CandidateID IS NOT NULL AND
usr.IsActive = 1 AND
usr.UserStatus = 1

I have been looking around but I cannot find a way to do this. I am using SQL Server 2008.


texts: How to import SQL file which has no N prefix

texts:
I have a large .sql file in Persian Language. The file is exported from sqlite db browser and has no N prefix for unicode fields. How can I reuse it in Sql Server?

This is sample of insert query in original file:

insert into `words` (id,word) values ('1','2','فارسی');

But I need this

insert into `words` (id,word) values ('1','2',N'فارسی');

I can not use find/replace to replace ,' with ,N' because there are  some ,' which represent number fields and has no need to N prefix.

I think I will need some conversion between multiple software (perhaps Microsoft access?) but I have no idea where to start?


texts:
I enabled slow-query-log on my server, and set long_query_time to 10 seconds.

But what strange is, that queries that are taking in execution 0.0002 seconds are also being logged in my log file, and now my log file is about 100Gb , which means mysql is logging all queries.

I have look at the mysql-slow.log file and i haven't seen any query taking more than 1 second, note that long-query-time is 10 seconds and what is expected is to find queries that have taken 10 seconds and more in execution

An Example

# Query_time: 0.000278  Lock_time: 0.000072 Rows_sent: 28  Rows_examined: 65

Any advisors please


texts: SSMS - How to Enable Word Wrap for Messages Pane

texts: Sometimes I need to take screen shots of error messages in SQL Server. How can I turn on word wrap in the messages pane so I can capture all the text in the screen shot?


texts: Why use Failover replica on Google Cloud RDS?

texts:
we are thinking of migrating our MySQL database from our dedicated server to a Google Cloud RDS instance. However I would like to clarify the below:

1) What is the purpose of creating a failover replica (high availability) at Google Cloud? I mean in a realistic scenario, is it even possible the RDS instance to "break" for any reason?  (Having in mind that we use failover for availability).

2) Is failover replica helping in the performance of the database, or it is used just for availability purposes? For example if I choose the failover replica, will this make my app run faster?


texts: Calculating Open Days field and stopping with status change

texts:
I had a question but wasn't sure where I needed to post or view if someone has done this before. I have a dilemma on my hands.

Background Info:
I have been trying to figure out what to do since I have a form that has a field where I use DateDiff to compare the current date to a field that has a measuring date. The difference between dates calculates the days open. I used DateDiff at first but then realize that I needed a way to stop the "Day's Open" when the status changed from a working (1) to a working (0).

Currently, I have a set of statuses (that have to be 0 or 1 for metric purposes and can only have a single 1 at any time) that are either working, dead or sold. Usually when the record is open, working is 1 and the open days field continues to increment. The other two fields are 0's if working is 1. If status changes, working turns to 0 and either dead or sold turn to 1.

My question:
If that was to change (either working turns to 0 and sold or dead turns to 1), how could I get the days open to stop counting days?

I'm sort of new still to access and vb. I am the one with the most experience that was willing to take it on so that they wouldn't be using Excel anymore for the monthly metrics sheets.

Is there a way you can help? This is where I am stuck and its very specific problem that I have not been able to see in any of the forums. Your forums have been really helpful with the other parts of my access tasks! I do appreciate it being available.

Thanks,
PPK


texts: Need separate columns that calculate percentage

texts:
I have the following table:

NAME
Alex
Bob
Bob
Tim
Alex
Roger

I need to produce the following:

 Name    Count Percentage
 Alex    2     33%
 Bob     2     33%
 Roger   1     16.6%
 Tim     1     16.6%

I have no idea where to start.  I can do the initial query for the count which is simple.


texts: Is there any way to prevent the memo structure from being pruned?

texts: We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just consider every possible plan and select the best from all alternatives?


texts: pgpool replication not allowing md5 authentication

texts:
I have installed both pgpool and postgresql via apt.

In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the same host).

I am unable to figure out what I need to add to my pg_hba.conf files in order to have pgpool connect to them. I keep getting this error

createdb: could not connect to database postgres: ERROR:  MD5 authentication
is unsupported in replication, master-slave and parallel modes.
HINT:  check pg_hba.conf

both of my pg_hba.conf files look like

local   all             postgres                                peer

local   all             all                                     peer
local   all             postgres                                md5

host    all             all             127.0.0.1/32            md5

host    all             all             ::1/128                 md5

local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

and my pool_hba.conf looks like

local   all         all                               trust
local   all         postgres                          md5
local   all         all                               md5

host    all         all         127.0.0.1/32          trust

What am I missing in order to have pgpool connect to my databases?


texts: how to update new password in maintenance plan

texts: I have changed the SQL account password but my all backups have failed. How do I update the new password manager connection in the maintenance plan from the backup?


texts: max_connections set to 500, Max_used_connections never go over 151

texts:
I have this set in the mysql config file, rebooted, seems to be correct.
SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

I have a load test against my server. This never get's above 151, server just gets real slow until the testing is done.
mysql> SHOW STATUS LIKE 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 151   |
+----------------------+-------+

I have already put in every config option that supposedly will fix this. Will Max_used_connections ever go above 151?
ubuntu 16
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper
been dinking around with this all day, thanks for any advice.


texts:
I should do really complicated tables reorder.

****The main table looks like:****

id - some data
1 - ...
2 - ...
3 - ...
6 - ...
9 - ...
12 - ...

As you can see, there are "empty" IDs (no 4, 5 between 3 and 6 etc).

****Table should look like****

id - data
1 - ...
2 - ...
3 - ...
4 - ...
5 - ...
6 - ...
7 - ...
etc (without empty IDs)

Why do I want to make it: this is a server database, includes millions IDs. The server crashes when maintaining such big numbers. If I reorder IDs and there won't be any empty ones, the MAX ID in this table will be much lower and it will prevent server from crashing.

The most complicated thing - this table has foreign key references (atleast only ID references) in like 4-5 different tables.

My thought is - reorder main table by IDs without empty ones and create new temp table with (oldID, newID) structure and then replace oldID's with newID's in the other related tables.

Any ideas how the query would look like? It will probably take half an hour to execute that query but it's the least problem.


texts: SQL Temporal Tables Include Current State

texts:
We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables.

We noticed that history tables only include history. We want to create a table which has history and current state (with EndDate being Null or 12/31/9999).
Is there any way to perform this, without creating a view to UNION current and history tables?

If I have to go with a UNION view, did SQL Server optimize internals, so there would not be any performance issue, for 50 million rows? I have to give the datawarehouse to customers and executive management, there should not be any noticeable difference.

 CREATE TABLE dbo.Department
    (
        DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
        DeptName VARCHAR(50) NOT NULL,
        ManagerID INT NULL,
        ParentDeptID INT NULL,
        SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
          CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
        SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
          CONSTRAINT DF_Department_SysEndTime
        DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
        PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));


texts: SSAS Modelling - many to many with one dimension

texts:
I'm having bit of a hard time modelling an OLAP cube.

My analogy is this: I have a list of students. Each one has one Major subject of study, and zero or more minors, up to five in total.

The raw data looks like this:

Student ID    | Major   | Minor 1    | Minor 2 | Minor 3 | Minor 4 | Minor 5
-----------------------------------------------------------------------------
12345           English   French       Null      Null      Null      Null
23456           French    Engineering  Null      Null      Null      Null
34567           Math      French       Biology   Physics   Null      Null
45678           Physics   Null         Null      Null      Null      Null
56789           French    Math         Physics   Chemistry Null      Null
67890           Math      French       English   Biology   Null      Null

Now as you can see, the subjects are all common, just that for some students, some are majors, the others are minors (where they have minors). This means I'd have a dimSuject dimension.

I can map this like so:

factStudent -> dimSubject

Such that I can drop a Measure that's a count of the Students, and see a list of Majors, with the number of students in each major.

What I'd like to do, is to be able to drop a dimMinor, which will drill down the majors into the count of each minor. Preferebly dimSubject is a unique reference of all subjects being taken, regardless if it's a major or minor. I have considered an intermediate factMinor table to store a reference to the student, and a reference to a subject, but does this maintain granularity? I couldn't also figure out how to use the intermediate fact as a dimension for drilling down.


texts: Is there a psql equivalent of bash's reverse-search-history?

texts:
I am very fond of bash's reverse-search-history (C-r) (command-line feature):

  Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental search.

When I hit control-r in bash, I get the following:

(reverse-i-search)`':

And I am able to type characters of previously executed commands, honing in on what I am looking for. If I make a mistake, I can enter control-h to erase a character (equivalent of backspace).

This command-line functionality makes it extremely easy to refer to previously executed commands. I am wondering if such a capability exists for psql. I find it extremely inefficient to rely on just control-p and control-n.

For what it's worth, I am using PostgreSQL 9.3.1 on x86_64-apple-darwin13.0.0, compiled by Apple LLVM version 5.0 (clang-500.2.79) (based on LLVM 3.3svn), 64-bit - obtained via Postgres.app.


texts: How to achieve using TRIM command on SELECT * From TABLE

texts:
I am building DWH where in source data dump is made through SELECT * query into DB. I am taking dump from the source DB, to save time (else had to write 115 select stmt using TRIM where average number of filed per table is 150) I used SELECT * FROM TABLE query to get the dump post which I am executing SED command to remove spaces from the fields.

This is taking a huge time & space as first SELECT query dumps the table and sed trims the Spaces. I have written a BJ to download the files & conversion.

There is space constraint in my server and I cannot mount extra space.

I am looking forward for inputs/tools/package/procedure on how can still use SELECT * but there are no spaces in the fields and I can avoid using sed command in UNIX.


texts: PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

texts:
(Version 9.3)

Asking this after searching around without any solution:

I create a simple materialized view (in practice, I have a few of them).
I call pg_dump to backup the database.
While pg_dump is running, I try to refresh the view using REFRESH MATERIALIZED VIEW.
The REFRESH statement hangs (remains 'active') as long as pg_dump is active (this makes things even worse as while the view hangs on REFRESH, I cannot SELECT from it as well).

My question is: how can I REFRESH materialized views while performing a dump?

I don't mind excluding the data of the view from the dump if this will help, I don't need to save the data. But I also wouldn't want to complicate my dump command to an unreasonable amount just to make this work.

Thanks in advance!

Complete list of commands:

psql my_db -c "CREATE MATERIALIZED VIEW my_view AS (SELECT * FROM my_table);"
pg_dump -Fc my_db
(in a different process, while pg_dump is active:)
psql my_db -c "REFRESH MATERIALIZED VIEW my_view;"


texts: SQL SERVER 2014 Standard - High Availability Listener solution

texts:
The company has SQL Server 2014 Standard licenses and we're standing the servers up in a VMware environment.

My initial plan is to create 2 instances on each DB server and run Instance1 on SQL1 as primary, Instance2 on SQL2 as primary with failover vice versa.

Questions:

Without the listener available only through Enterprise addition, has anyone used a load balancer to repoint the DB connection when you failover for maintenance?
Is there a better method for failing over with Standard Edition?

The only other solution that comes to mind is DNS CNames/ALIAS and repointing to a different IP address, but that seems inherently flawed.


texts:
I have the following car-related tables:
CREATE TABLE IF NOT EXISTS Brand
(
    id                              SERIAL PRIMARY KEY,
    name                            text,
);

CREATE TABLE IF NOT EXISTS Modell
(
    id                              SERIAL PRIMARY KEY,
    name                            text,
    brand_id                        INT REFERENCES brand(id) ON DELETE CASCADE,
);

So in the JSON world it is just an array of brands like this:
[
  {
    "id": "1",
    "name": "Ford",
    "models": [
      {
        "id": "2",
        "name": "Mustang"
      }
    ]
  }
]

Now given an search term I want to get the following result:
If the term matches a model name I want to get the brand with the "filtered" model items. In case it doesn't match a model but a brands name the I want to get the brand with all of its models.
Any ideas how to do that query with the best performance?


texts: Converting Data Type of a full Column/Field

texts:
I have a primary key ID column I want  to convert to a string, any type of string.
Now, I know how to do this for a specific value, by using either CAST or CONVERT. But how do I go about doing it for the whole field? I guess we use some type of Transact SQL , but I would appreciate some pointers on how to do this.
Thanks.


texts: Invalid views not showing in user_dependencies after first compilation

texts:
When I create an (invalid) view that selects from another invalid view, the view does not immediately show up in user_dependencies. I am wondering if this is expected.

These are the steps to reproduce the behavior:

First, I create a table

create table tq84_t (
  col_1 number,
  col_2 number
);

Then I create a view that is dependent on the table. Note the typo col_22 instead of col_2:

create or replace force view tq84_v as select col_1, col_22 from tq84_t;

Of course, this view will be created in an invalid state:

Warning: View created with compilation errors.

I create another view which is dependent on tq84_v (which will also be in an invalid state):

create force view tq84_v2 as select * from tq84_v;

I now query user_dependencies to check whether I have objects that are dependent on tq84_v. I would have expected TQ84_V2 to be reported, but the following query returns no record:

select name from user_dependencies where referenced_name = 'TQ84_V';

I re-compile the second view:

alter view tq84_v2 compile;

and query user_dependencies again:

select name from user_dependencies where referenced_name = 'TQ84_V';

Only this time, Oracle will report TQ84_V2 as being dependent on TQ84_V.

Is this behavior expected?


texts: What are possible causes of missing .frm files in mysql?

texts:
Recently I faced General error: 1813 Tablespace for table (..) exists. and the solution was to find and remove appropriate .ibd file of trouble-causing table. I wonder why that issue even happened. I mean why there was only the .ibd file (with the size of only 64K) without corresponding .frm. My current mysql setup has file per table on and ibd autoextend by 12M without limitation. I am using mysql 5.7.19.

In addition, that table often gets dropped and recreated. Thanks for any clues and suggestion.


texts:
Does full text search and freetext indexing drastically increase the memory usage of a SQL Server database instance?

Today, on my development machine, I installed the fulltext feature on a SQL Server 2014 instance and created a catalog on one of my tables with a fulltext index. I set the index to track changes automatically. The table I used it on has about 5000 records.

Next, I started testing this feature by running some queries. The last query that I've run so far is the following:

SELECT
    'Title' AS [MatchFrom],
    [ShopItem].[Title],
    [ShopItem].[Description],
    [SubCategory].[Name] AS [SubCategory],
    [FreeTextTable].[Rank]
FROM
    FREETEXTTABLE([ShopItem], [Title], 'Paper') AS [FreeTextTable]
    INNER JOIN [ShopItem]
        ON [FreeTextTable].[Key] = [ShopItem].[Id]
    INNER JOIN [SubCategory]
        ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
UNION
SELECT
    'Description' AS [MatchFrom],
    [ShopItem].[Title],
    [ShopItem].[Description],
    [SubCategory].[Name] AS [SubCategory],
    [FreeTextTable].[Rank]
FROM
    FREETEXTTABLE([ShopItem], [Description], 'paper') AS [FreeTextTable]
    INNER JOIN [ShopItem]
        ON [FreeTextTable].[Key] = [ShopItem].[Id]
    INNER JOIN [SubCategory]
        ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
UNION
SELECT
    'Subcategory',
    [ShopItem].[Title],
    [ShopItem].[Description],
    [SubCategory].[Name],
    [FreeTextTable].[Rank]
FROM
    FREETEXTTABLE([SubCategory], [Name], 'Paper') AS [FreeTextTable]
    INNER JOIN [SubCategory]
        ON [FreeTextTable].[Key] = [SubCategory].[Id]
    INNER JOIN [ShopItem]
        ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
ORDER BY
    [FreeTextTable].[Rank] DESC

It was at this point that my computer warned me that it's low on memory:

I quickly realised that the SQL server instance was using an unusual amount of memory (it's moving between 6 gigabytes and 9 gigabytes)

Now I'm quite concerned that implementing any freetext functionality on my live system would cause a strain on my server (a cloud VM that currently has a total of 6GB RAM), but I don't know how to confirm exactly what it is that's using so much memory. I'm currently a solo developer on a new project without any database administrators.

EDIT:

Memory usage still increasing:

SQL version (this is on my development machine running Windows 10 64bit, not the server running my application)

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
      Feb 20 2014 20:04:26
      Copyright (c) Microsoft Corporation
      Developer Edition (64-bit) on Windows NT 6.3  (Build 14393: )

The memory usage next day:


texts: PostgreSQL does not use all available IOPS on a single operation

texts:
Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD.

Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table.

using (var connection = new NpgsqlConnection("User Id=postgres;Password=**;host=localhost;database=**;"))
using (var command = new NpgsqlCommand())
{
    connection.Open();
    command.Connection = connection;
    command.CommandText = "COPY \"MyTable\" FROM STDIN;";
    var copyInSerializer = new NpgsqlCopySerializer(connection);
    var copyIn = new NpgsqlCopyIn(command, connection, copyInSerializer.ToStream);
    copyIn.Start();

    for (int i = start; i < start + 1000000; i++)
    {
        copyInSerializer.AddInt32(i);
        //add 7 other int32 values
        copyInSerializer.EndRow();
    }
    copyInSerializer.Flush();
    copyIn.End();

    connection.Close();
}

On my test hardware this took ~10 seconds execution time and measured disk usage of ~20MB/s.
CPU and RAM do NOT hit limits.

The given SSD supports ~60MB/s writing random 4k blocks so I wondered why it would not execute faster. I created a second, identical table in the same database and disk and ran the above code sample in parallel (two tasks) on both tables.
Both took ~10 seconds, and Measured Disk usage was ~40MB/s.

Why does PostgreSQL not focus all available resources on the one and only operation happening?
Is there a way to force PostgreSQL to use the full available disk bandwidth?


texts: MySQL Workbench: Edit hidden relationships in EER diagram

texts:
How can I open the Edit relationship options panel of a relationship that is previously been hidden?

Workbench v6.2


texts: inserting multiple values into SQL with %

texts:
This works:

cursor = conn.execute("""Select FAMILY,F_NAME from birthday where B_MONTH = '%s' """ % (currentMonth))

I cannot seem to expand it to work using an AND clause to also include day, I get a syntax error:

cursor = conn.execute("""Select FAMILY,F_NAME from birthday where B_MONTH = '%s' AND B_DAY = '%s' """ % (currentMonth),%(currentDay))


texts: Is it advisable to asynchronously update the summary table every time a transaction is deleted?

texts:
Given three tables:

(a) Product
(b) Transaction Head
(c) Transaction Detail

We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction date.

We also have a web page where the transaction can be created, edited and deleted.

The problem lies in the query performance. The data in the transaction head and detail tables may increase dramatically fast. Hence, executing a query with a subquery which finds the most recent transaction can be heavy.

We came up with a solution to create a summary table where it may contain the following columns:
(a) branch id
(b) product id
(c) latest quantity ordered
(d) latest ordered date

which will be updated in the transaction module. However, when a transaction entry is deleted, we had to run the whole query, order by date descending, and update the summary table since the previous state is not stored.

Is it advisable to asynchronously update the summary table using the heavy query every time a transaction is deleted? Would not it be too heavy for the server? We are also considering using cron - a job scheduler to synchronously re-compute the summary table for efficiency, but we had to display the information at real-time.


texts:
I analyzed a C# program over the weekend for a customer that basically does the following:

Truncate Table
Fetch Data (source on these is either remote database or local database where job runs)
SQLBulkCopy (.NET class, into destination table of database where job runs)

For the most part (1) and (2) are done through stored procedure calls. I believe SSIS will be more efficient in handling this process. My end result is to determine if SSIS can do a better job and then the most efficient way of designing the package.

Question(s)
In cases where there are a large number of records would SSIS get better performance with the BULK INSERT task when (2) and (3) are the same database on the same instance? Or just change the procedure call in (2) to be a INSERT INTO, taking out the need for (3)?

In cases where (2) is a remote source (another SQL instance on the same network), does SSIS handle pulling data from a remote source more efficiently than doing it through the .NET SQLClient? I would not think there is a huge difference.

As well, when (2) is a remote source that is rather large amount of data (8 million +)would it be better to have a package on the remote instance export the data; then move that file to the destination server and bulk load it?


texts: How much RAM should you allocate a MySQL replication slave?

texts:
Our MySQL master is allocated 24GB of RAM (innodb_buffer_pool_size = 24GB), but was curious if there was some standard amount to allocate to a replication slave?

Right now the slave is just used to take dumps without locking tables on master, and in theory be used as an emergency master if something happens (hasn't come up in the last 18mo). If master fails I could always quickly allocate the slave 24GB, but how much does a slave really need to just sit there replicating?


texts:
We have a table that will get inserts and updates from 10 different jobs. We need to track each DML operation with its source i.e job name. We have used audit tables and triggers to get old and new table data but we are not able to track the job which is responsible for that DML operation.

is it possible to track DML operation source?


texts:
Suppose a postgresql table items like this. (keywords column is of type text[])

name   account_id      keywords
------------------------------
foo1   1               ['k1', 'k2']
foo2   1               ['k1', 'k3']
foo3   2               ['k4', 'k1']
foo4   2               ['k1', 'k6']

Each row in items is relate to a Account (the table is virtually splitted by account_id). We want make queries like: "items for account 1 with keyword k1". This query requires a composite GIN index on account_id and keywords column.

Actually, we need an inverted index, with rows like this: (the key for each row should be composite)

(account_id, keyword) --> [item1, item2, ...]

What's the right way to create this index in postgresql?


texts: Installing SQL Server 2008 R2 Express on Server 2012 - Could not load file or assembly

texts:
I'm trying to install SQL Server 2008 R2 Express Edition SP2 on Windows 2012 server.

The error is:

Any clue?


texts:
Use Case

In a ticket tracking application, there are ticket-types. A ticket type can have a (primary) slug. Sometimes, a ticket type can have old/legacy aliases for the slug.

It should be straight forward to get the matching ticket-type from a slug.

DB Model

My database model looks like this: A ticket type with N aliases for the slug.

DROP TABLE IF EXISTS ticket_type_slug_alias;
DROP TABLE IF EXISTS ticket_type;

CREATE TABLE ticket_type(
  id INTEGER PRIMARY KEY,
  slug CHARACTER VARYING(50) UNIQUE,
  verbose_name CHARACTER VARYING(64) NOT NULL
);

CREATE TABLE ticket_type_slug_alias(
  id             INTEGER PRIMARY KEY,
  ticket_type_id INTEGER,
  FOREIGN KEY(ticket_type_id) REFERENCES ticket_type(id),
  slug CHARACTER VARYING(50) UNIQUE
);

Problem 1

the slug of ticket_type and ticket_type_alias need to be unique

Problem 2

Fetching the ticket_type by slug needs to access both tables. This is not really a problem, but accessing only one table would be simpler.

Current solution

I think I am going to store the primary slug of the ticket-type in the alias table, too.

This solves problem1 and problem2. But on the other hand it is redundant.

Up to now I try to avoid redundancy.....

And I need a way to ensure that this redundancy does not get out of sync.

Better solutions?

Do you have a better solution (or comment)? Please let me know.

DB: PostgreSQL

I use PostgreSQL. But I think this should not matter for this question.


texts: I need to run VACUUM FULL with no available disk space

texts:
I have one table that is taking up close to 90% of hd space on our server.  I have decided to drop a few columns to free up space.  But I need to return the space to the OS.  The problem, though, is that I'm not sure what will happen if I run VACUUM FULL and there is not enough free space to make a copy of the table.

I understand that VACUUM FULL should not be used but I figured it was the best option in this scenario.

Any ideas would be appreciated.

I'm using PostgreSQL 9.0.6


texts: can mysql cli exit with error if no records returned?

texts:
I would like to execute a sql command in bash, and do something based on whether or not there was a result.

e.g.

$ LOOKUP=123; mysql -e \
    'SELECT id FROM table WHERE id='$LOOKUP dbname \
    && echo "Row $LOOKUP exists"
    || echo "Row $LOOKUP does not exists"

I can think of bodges (e.g. testing for ZLS) but wondered if there was a clean way?


texts: sql server active active configuration with same database names

texts:
I have a SQL Server 2008 Enterprice in a clustered environment and in active-active mode.

By active-active mode i mean that both instances are active and serving requests to their databases.

Instance 1 has databases Db_A and Db_B and instance 2 has databases Db_C and Db_D.

I would now like to create a new database called Db_E on each instance. They will have different content and structure, but will have the same name.

I tried it and it's possible but i'm not sure what will happen in the case of failover when i shut down Instance 1?

I guess it should be okay, but i'd like a confirmation.


texts: Is it possible to restore only tables/constraints from database backup?

texts:
For circumstances beyond my control (permissions/bureaucracy), I'm unable to use SSIS or Scheduler Agent to create an automated, scheduled production database backup and development database restore.  Instead, I'm attempting to use C#.NET to create a .exe file that will perform the backup/recovery.  Please correct me if I'm going about this in the wrong direction or there are better alternatives.

I haven't tested any of the following yet, so they're just concepts for now.

First, I'm backing up the production database using the following function:

private static void BackupProductionDatabase()
{
    string backupFileName = "ProdBackup-" +
                            DateTime.Now.ToString("yyyy-MM-dd") + ".bak";
    DataHandler dh = new DataHandler(AppSettings.ProdConnectionString);
    string sql = "BACKUP DATABASE [USPRD1828] TO DISK = " +
                 "'" + AppSettings.BackupDirectory + backupFileName + "'";
    dh.ExecuteNonQuery(sql);
}

Then, I have a stored procedure with a slightly modified version of this script to drop only primary/foreign key constraints and tables on the development database.  The views and stored procedures will remain intact.

Provided that I have a .bak copy of the production database from the above code snippet, is it possible to restore ONLY the primary/foreign key constraints and tables from the production backup to the development database using either (or both) TSQL/C#.NET?


texts:
I have a long (and long running, ~30min depending on load) query. It has multiple statements UNION'ed together. Most of the statements are using the GETDATE function[1].

Is the function executed once or multiple times?
If multiple times, would putting it into a WITH statement and then SELECT timestamp FROM <WITH statement> making it getting executed only once?

[1] https://docs.aws.amazon.com/redshift/latest/dg/r_GETDATE.html


texts: How to create implicit select statements

texts:
I need to create an implicit select statement for each table with all of its coluns - i.e. SELECT COL1, COL2, COL3 FROM TABLE1 instead of SELECT * FROM TABLE1.

There are too many tables to use the scripting feature in SMSS so I was hoping to use one of the system tables. I can query the table and column names from INFORMATION_SCHEMA.COLUMNS, but the I'd have to use a cursor to build the string as required.

Just hoping there's an easier way of doing it.

Thanks


texts: Delete multiple rows in one MySQL statement

texts:
I have been looking at a lot of solutions, but I simply do not get it.
What I want to do is remove 2 rows, each in a different table:

DELETE FROM adv_equip, adv_players
WHERE session = '9746qbcinll2edfbmkfn316lu0'

There is a session field in both tables.

A lot of examples include t1, t2 and whatnot, and I do not know how to use it. I tried for example:

DELETE FROM adv_equip t1, adv_players t2
USING t1, t2
WHERE t1.session = t2.session
AND t1.session = '9746qbcinll2edfbmkfn316lu0'

What should I do? It's been days and I kind find a logical easy solution anywhere.


texts: Unable to connect with root because of skip-name-resolve option

texts:
We had our MySQL server on an old server. We decided to move all of the databases to our new server.

Old MySQL Server engine 5.5.31
New MySQL Server engine 5.5.38

We created one general dump file without logs from all of the Databases in the old server and we restored the .sql file in the newly created MySQL Server on the new server.
On the new server when we put skip-name-resolve option in the my.ini  everything is fine but we can't login to MySQL server with root account.
We tested the issue on old server and we didn't have any issue with skip-name-resolve.
We removed skip-name-resolve option and we tried to login to MySQL server.
after login when we tried to list the available users. Like previous MySQL server here after restoring the dump we have two root users when we execute:
select user,host from mysql.user;

For the first one % is defined as host and for the second one LOCALHOST is defined as host.
All of the users are defined with % as their host. We want to use skip-name-resolve and on another hand resolve our issue with root account.


texts: Why is my SQL Server consuming so much memory?

texts:
I have a 64GB Windows Server 2019 Standard server in my TEST Server.
I have SQL Server 2019 CU9 Developer Edition and my the instance is consuming 21,199 MB as shown in sql_physical_memory_in_use_MB.
I have 4 user databases.  Their sizes are approximately 5GB, 2GB, 400MB, and 200MB.
My tempdb is configured to be 8GB at instance start-up.
I'm trying to understand why my instance is using 21GB of memory.  Just worried about a possible memory leak.
Can anyone calm my nerves?
SELECT
    (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
    SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
    SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
    count(*)AS TotalPageCount,
    cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc
GO

OUTPUT

Database Name
DirtyPageCount
CleanPageCount
TotalPageCount
BufferPoolMB

SFI_WMS
55
805665
805720
6294.69

tempdb
18648
210842
229490
1792.89

DBAtools
483
82035
82518
644.67

Resource Database
0
3896
3896
30.44

msdb
42
1898
1940
15.16

master
24
766
790
6.17

ReportServer
4
476
480
3.75

ReportServerTempDB
0
308
308
2.41

model
0
245
245
1.91

SELECT
    physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage,
    process_physical_memory_low AS sql_process_physical_memory_low,
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

OUTPUT

sql_physical_memory_in_use_MB
sql_large_page_allocations_MB
sql_locked_page_allocations_MB
sql_VAS_reserved_MB
sql_VAS_committed_MB
sql_VAS_available_MB
sql_page_fault_count
sql_memory_utilization_percentage
sql_process_physical_memory_low
sql_process_virtual_memory_low

21199
0
0
124464
21461
134093263
9673913
100
0
0

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)';

value
value_in_use

60288
60288


texts: MySQL query returns different result on second execution

texts:
I'm not too familiar with MySQL variables but I'm trying to avoid redundant sub selects while calculating these results. The first execution only returns 1 of the correct calculations. The second execution returns all the correct values.

SELECT
@discount_code := '*******' AS discount_code,
@sales_revenue := (
    SELECT
        CAST(SUM(total_price) AS UNSIGNED)
    FROM
        shopify_orders
    WHERE
        discount_code = @discount_code
) AS sales_revenue,
@rsp_float := (
    SELECT
        revenue_share_percentage / 100
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS rsp_float,
@marketing_spend := (
    SELECT
        fixed_media_cost + @revenue_share
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS marketing_spend,
@roi_dollars := (
    @sales_revenue - @marketing_spend
) AS roi_dollars,
@revenue_share := (@sales_revenue * @rsp_float) AS revenue_share,
@roi := CAST(
    (@roi_dollars /@marketing_spend) AS DECIMAL (5, 2)
) AS roi,
@mroi := CAST(
    (
        @sales_revenue / @marketing_spend
    ) AS DECIMAL (5, 2)
) AS mroi;

First execution:

Second execution:


texts: How to calculate the ratio insert/select against a table to manage the index in Sybase ASE?

texts:
To create/manage indexes of a table, it is important to know the ratio insert/select or modification/reporting. For instance, if there are more INSERTS than SELECT against a table, we should try to create only a few small indexes not to impact the INSERT performance.

How to calculate this ratio in Sybase ASE 15.5 and later?

Does it make sense to run the following?

select "Table"=object_name(ObjectID), IndexID, PagesRead, PagesWritten, Operations
from master..monOpenObjectActivity
where DBID=db_id("DBNAME") and ObjectID=object_id('TableNAME')
order by IndexID


texts: Add auto increment to already existing primary key column PostgreSQL

texts:
I have a database schema of the following table:
database=# \d person
              Table "public.person"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 person_id   | smallint              | not null
 fname       | character varying(20) |
 lname       | character varying(20) |
 eye_color   | color_enum            |
 birth_date  | date                  |
 street      | character varying(30) |
 city        | character varying(20) |
 state       | character varying(20) |
 country     | character varying(20) |
 postal_code | character varying(20) |

I want to add AUTO_INCREMENT in one ALTER statement the way we can do in MySQL
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

I have tried this in Postgres but I am getting this error:
ALTER TABLE person ALTER COLUMN person_id SERIAL;
ERROR:  syntax error at or near "SERIAL"

I have seen we can create a sequence in the following fashion
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

But this is too much boilerplate code. Is there a one-line statement to add AUTO_INCREMENT to an existing column in Postgres?
Postgres Version: 9.6.16
Update
After doing the boilerplate code, I am trying to INSERT using the following query:
 INSERT INTO person
 (person_id, fname, lname, eye_color, birth_date)
 VALUES (null, 'William','Turner', 'BR', '1972-05-27');

ERROR:  null value in column "person_id" violates not-null constraint
DETAIL:  Failing row contains (null, William, Turner, BR, 1972-05-27, null, null, null, null, null).

Is there a workaround by which I can pass null values to the primary key where the value of that column is from the sequence?
Answer
Was able to insert using the following error:
INSERT INTO person(person_id, fname, lname, eye_color, birth_date)
       VALUES (nextval('person_id_seq'), 'William','Turner', 'BR', '1972-05-27');


texts: User with dba privs can't login to APEX, but can from SQL*Plus

texts:
I'm using Oracle 11gR2 XE. A user with dba privileges can't log in to the APEX interface, but can from the command line with sqlplus.

I just created user:

CREATE ROLE administrators;
GRANT dba TO administrators;
CREATE USER superuser IDENTIFIED BY "root";
GRANT administrators TO superuser;

If I run cmd, type sqlplus and log in with "superuser", I get access.

But the web interface (APEX) denies access with an "invalid credentials" error message.

What do I need to do to be able to log in on the APEX interface?


texts:
Master version: mysql-server-5.5.14-1.el5.remi
Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340
Binlog format: ROW based

I'm using PRM to setup HA for MySQL.

Sometime, MySQL slave stopped with some errors:

Error executing row event: 'Table 'reportingdb.tvc_ads_tag_date' doesn't exist'

[Warning] Slave SQL: Could not execute Update_rows event on table reportingdb.7k_banner_channel_tmp; Can't find record in '7k_banner_channel_tmp', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000854, end_log_pos 859094925, Error_code: 1032

120828  0:36:13 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.7k_bookings_ver; Duplicate entry '1518' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001022, end_log_pos 509479, Error_code: 1062

Error 'Error in list of partitions to DROP' on query. Default database: 'reportingdb'. Query: 'ALTER TABLE v3_ban_date_cpm7k DROP PARTITION pcurrent_201298'

and the mysql.sock is missing but the MySQL processes still exist. Pacemaker cannot restart due to the below errors:

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

I have to login via TCP (-h 127.0.0.1) and stop MySQL first, then let corosync start MySQL again.

In summary, the question is:

How do I solve the above slave errors and prevent them from happening in the
future?
Why is the mysql.sock missing?  How do I make the Pacemaker better handle this situation?


texts: Use T-SQL to return free space on a network share

texts:
I'd like to be able to calculate the amount of free space I have for offsite backups so I can prevent any jobs from running should there not be the relevant space and also be able to send alerts when required. I'm aware of using cmd command FSUTIL (as below) for local drives however this doesn't seem to work for network drives.  Please can an alternative be suggested?
Thanks in advance.

exec xp_cmdshell 'FSUTIL VOLUME DISKFREE c:\'


texts: Do the different "types" of autovacuum have different performance characteristics?

texts:
In monitoring the autovacuum activity on my database, I've noticed that autovacuums tagged with (to prevent wraparound) seem to take longer than "regular" autovacuums.  That got me started trying to understand the differences in performance/behavior of the various "kinds" or autovacuums.

From the docs I've identified a few different reasons why autovacuum might VACUUM a table:

The table has old transactions that need to be frozen (relfrozenxid > min(autovacuum_freeze_max_age, table.autovacuum_freeze_max_age). This is the "(to prevent wraparound)" autovacuum.
The number of tuples obsoleted in the table since the last VACUUM exceeds the "vacuum threshold"

Are there any others that I'm missing?

The "aggressiveness" knobs that seem to be exposed for a manual VACUUM are FREEZE and DISABLE_PAGE_SKIPPING.  Do these different kinds of autovacuum ever use these options, and if so, when?


texts:
MySQL 5.6.35-log (community)

How can I remove a incorrectly manually injected GTID from a GTID set without doing a RESET MASTER?

STOP SLAVE;
SHOW MASTER STATUS;

278bfda1-b93d-11e4-801b-14feb5d284bc:1-129116182,
69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661,
708bb615-d393-11e3-a682-003048c3ab22:1-1009669227,
819c985c-d384-11e3-a621-00259002979a:1-234906555,
9204e764-d379-11e3-a5d9-0013726268ea:1-360176454,
c32252c2-1ce5-11e6-8f4b-c80aa91f9ec4:1

We need to remove GTID 1009231661 from this set:

69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661,

Does anyone know where/how the GTID sets are stored? I read in the 5.7 documentation that the GTIDs are stored in table. But where are they stored in 5.6? I would like to shutdown the server, edit the file, remove the bad GTID, and then restart so the server can pickup and continue.


texts: It is possible in Oracle to examine the session-level settings for sessions other than your own (subject to privs obv). Is there an equivalent method in Postgres 9 or 10?


texts: Active Directory domain being shutdown-- Do I have to update my DBO accounts?

texts:
I manage a SQL Server machine (with SQL Server 2000 and 2005 installed) that was first created on an Active Directory domain that we are transitioning away from and are about to shut down.  All of my databases have File Owner's from the old domain.

New accounts have been created that have the correct permissions to run the SQL Server services and execute all necessary SQL Server processes.  My question is, what benefit is there to updating the associated File's Owner property on each database?

Do I need to assign a new AD account to each database, or can I leave it as it is since there are already accounts setup that have full admin rights for all of my databases?


texts: Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

texts:
I know it is often asking question. But I can't find answer for my scenario with specific condition.
We use MySQL 5.5

We have always(I am sure)

innodb_file_per_table = ON

option in our mysql config. So I drop big table with confidence that file that represent this table will be deleted and disk space will be freed immediately, seems to be this file was deleted(this file not exist in DB directory in datadir, all other DB tables files exist), but in munin(disk usage) graph I don't see that disk space get free(my bad that I didn't notice disk usage before dropping table). Also ibdata1 seems to be is quite big for this situation(~ 200 GB).

There is way to get some additional info about old table (that was dropped) ?
There is some way to reduce disks usage without dumping and then restoring DB, maybe create new table with old_name(that was dropped) and try to make ALTERING ?

UPDATE:

1) ~100-110GB, or 20% of total disk space(ssd disk)

2) files_  | 124151382016

3) 10 tables, but only 1 is big table

4) Prehistory: we had big table(busy, inserts/updates/reads) that continue growing up, but some data in this table became old, and must be deleted, so we run delete queries in infinity loop( DELETE FROM files WHERE condition LIMIT 1000-100000, I don’t know how much rows were deleted with this method, but Innodb_rows_deleted was approx. ~600kk, and Innodb_rows_inserted nearly 1 billion or so) we deleting at night when load is very low — after we calculate that deleting takes too much time, so we use persona pt-archiver to move(only data that we need) from old table to new table(files_), and then DROP old table.

Yes, old one table had key constraints to separate table — “ON DELETE CASCADE ON UPDATE CASCADE”(in new table we have not constraints), also was in old table(and in current table)UNIQUE KEY on some table field.
I am also now making backup with xtrabackup of current db to check what takes much space in ibdata1 file with innodb_space app. Seems to be only 1 possible way — to make logical backup, and then import, sql file is 40GB, so it would be not fast despite that server pretty powerful(128GB ram).

Thank you!


texts: Help with Recursive CTE query with INSERT, ON CONFLICT and RETURNING

texts:
I'm trying to write a query to insert filenames to the following table table path

CREATE SEQUENCE path_id_seq;
CREATE TABLE path (
    id INTEGER PRIMARY KEY DEFAULT NEXTVAL('path_id_seq'),
    parent_id INTEGER,
    name TEXT NOT NULL);

CREATE UNIQUE INDEX path_parent_id_name_ix
    ON path (COALESCE(parent_id, 0), name);

ALTER TABLE path
    ADD CONSTRAINT ios_path_parent_id_fk
    FOREIGN KEY (parent_id) REFERENCES path (id)
    ON DELETE CASCADE
    DEFERRABLE INITIALLY DEFERRED;

For example, given filenames Assets/images/a.png and Assets/images/b.png I'd like to insert the following rows.

id | parent_id |       name
----+-----------+-------------------
  1 |         ¤ | Assets
  2 |         1 | images
  3 |         2 | a.png
  4 |         2 | b.png

Inserting the parent is easy;

INSERT INTO path (name)
VALUES ('Assets')
ON CONFLICT (COALESCE(parent_id, 0), name)
DO UPDATE SET name = EXCLUDED.name -- this strange update is so that we get rows back
RETURNING id, parent_id, name;

which yields;

 id | parent_id |  name
----+-----------+--------
  1 |         ¤ | Assets

But I can't seem to put together a recursive CTE to use the parent's id as the parent_id in the next INSERT statement.

It will probably look somethings like this invalid query below.

WITH RECURSIVE names AS (
    SELECT name, lag(name, 1) OVER () as previous_name
    FROM (SELECT unnest(string_to_array('Assets/images/232.png', '/')) as name) names),

paths(id, name) AS (
    INSERT INTO path (name)
    SELECT name
    FROM names
    WHERE previous_name IS NULL
    ON CONFLICT (COALESCE(parent_id, 0), name)
    DO UPDATE SET name = EXCLUDED.name
    RETURNING id, name
    UNION
    INSERT INTO path (parent_id, name)
    SELECT paths.id, names.name
    FROM paths
    JOIN names ON names.previous_name = paths.name
    ON CONFLICT (COALESCE(parent_id, 0), name)
    DO UPDATE SET name = EXCLUDED.name
    RETURNING id, name)

SELECT *
FROM paths;

But I just can't seem to get it to work. If anyone sees a simpler way to do this it would be much appreciated.


texts:
I am trying to implement something like a mailbox for short messages between users. I am using Postgresql 9.4.1 with SQLAlchemy (Python3).

There is a table of users:

users = Table('users',metadata,
        Column('uuid',postgresql.UUID(as_uuid=True), primary_key=True),
        ...

I am looking for a good design for the messages related tables.
Assume that a user X has sent a message M to user Y. The requirements are as follows:

X will see M inside its outgoing messages list. Y will see M inside its incoming messages list.
If X deletes the outgoing message, Y can still see it in its incoming messages list.
If Y deletes the incoming message, X can still see it in its outgoing messages list.
If the user X is removed, Y will keep seeing the incoming message M.
If the user Y is removed, X will keep seeing the outgoing message M.
If both users X and Y are removed, nothing is left from the messages between them inside the database.

The most naive (But simply working) solution I thought of so far is something like this:

# Incoming offline messages between users.
in_off_messages = Table('in_off_messages',metadata,
    Column('msg_uuid',postgresql.UUID(as_uuid=True), primary_key=True),
    Column('src_uuid',postgresql.UUID(as_uuid=True),
        sa.ForeignKey('users.uuid',onupdate='CASCADE',ondelete='SET NULL'),index=True),
    Column('dst_uuid',postgresql.UUID(as_uuid=True),
        sa.ForeignKey('users.uuid',onupdate='CASCADE',ondelete='CASCADE'),index=True),
    Column('msg_type',sa.Integer(),nullable=False),
    Column('date_sent',sa.DateTime(), nullable=False),
    Column('content',sa.String(512),nullable=False),
    )

# Outgoing offline messages between users.
out_off_messages = Table('out_off_messages',metadata,
    Column('msg_uuid',postgresql.UUID(as_uuid=True), primary_key=True),
    Column('src_uuid',postgresql.UUID(as_uuid=True),
        sa.ForeignKey('users.uuid',onupdate='CASCADE',ondelete='CASCADE'),index=True),
    Column('dst_uuid',postgresql.UUID(as_uuid=True),
        sa.ForeignKey('users.uuid',onupdate='CASCADE',ondelete='SET NULL'),index=True),
    Column('msg_type',sa.Integer(),nullable=False),
    Column('date_sent',sa.DateTime(), nullable=False),
    Column('content',sa.String(512),nullable=False),
    )

This fulfils the requirements (Think about the ondelete for a while, you will see that it works). However, I happen to store the message content twice, which is probably not cool.

I thought about having one main table for messages: off_messages, and then the two tables in_off_messages and out_off_messages will have foreign key to off_messages. However, in that case it is much harder for me to follow the requirements.

How can I make sure that messages inside off_messages will be deleted automatically when the messages from in_off_messages and out_off_messages are deleted? Also, how can I handle the case of a user being deleted in an elegant way?

I considered implementing some kind of a refcount for every row in off_messages, but it wasn't as elegant as I expected.

What would be the correct way to solve this problem?


texts: T-SQL query for date of last full backup, size and location

texts:
I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file.  My problem is that it won't return any data at all for databases that have had no backups or for which there is no backup history for.  Ideally, I'd want to modify the query so that all databases are returned, regardless of whether or not there's any backup history for them.  Can anyone advise how the below query could be modified to accommodate this?

WITH LastBackUp AS
(
SELECT  bs.database_name,
        bs.backup_size,
        bs.backup_start_date,
        bmf.physical_device_name,
        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM  msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE   bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT
        database_name AS [Database],
        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];


texts: Create an unique integer id column for an existing string column

texts:
I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this:

| ID_STRING |
| 'ABCDEFG' |
| 'HIJKLMN' |

Now I want to compute integer ids for each element and store them into an additional column. The result should look like this:

| ID_STRING | ID_INT
| 'ABCDEFG' |   1
| 'HIJKLMN' |   2
| 'ABCDEFG' |   1
| 'HIJKLMN' |   2

Is there an easy way to achieve this?


texts: Logical Replication: Schema does not exist while subscription

texts:
PostgreSQL logical replication.
I am trying to replicate the tables from server 1 to server 2.
In Server 1
create publication locale for table code.locale_cd;
CREATE PUBLICATION

In Server 2
create subscription indsolv_dev10 connection
    'host=192.168.0.26 port=5432 password=****** user=replicator
    dbname=indsolv_dev' publication locale;

Throws an error schema "code" does not exist but it's present in the database.


texts: LOAD DATA keeps CPU usage high after it finishes

texts:
I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this:

mysql.RegisterLocalFile(fileName)
_, err = dbConnection.Exec(loadDataCommand)

This works as expected after the load has finished I run some SELECT queries (I'm reusing dbConnection for those SELECT queries and all other queries) on newly loaded data and this is where the problem occurs. The above mentioned SELECT queries are unusually slow (due to high CPU usage).

While running LOAD DATA database CPU usage spikes which is expected but after the LOAD DATA finishes CPU usage should drop but it doesn't unless I close and reopen dbConnection CPU usage stays high.

This is how I'm "reseting" the database connection:

_ = dbConnection.Close()
dbConnection, err = sql.Open(databaseDriver, databaseUser+":"+databasePassword+"@tcp("+databaseAddress+":3306)/"+databaseName+"?multiStatements=true")

This is the database configuration.

Why is this happening? The above code is running on a development database so there are no other connections to it.


texts: Can SQL Server execute two BULK INSERTs in parallel?

texts:
I have two heavy queries to run, and both of them take about 1 minute to run. Both queries are using the Bulk Insert command to insert data from text files to tables in the database.

For the two tables importing data, there are no indexes/triggers/constraints, just two empty tables to load data into.

For the two text files to load, each of them has around 20M rows.

Query 1:

Bulk insert table1
FROM 'table1.txt'  WITH
        (FIELDTERMINATOR ='|',
         ROWTERMINATOR = '0x0a',
         TABLOCK)

Query 2:

Bulk insert table2
FROM 'table2.txt'  WITH
        (FIELDTERMINATOR ='|',
         ROWTERMINATOR = '0x0a',
         TABLOCK)

I want to compare the running time under different scenarios.

Scenario 1: two queries running serially in one editor:

Query 1: 1 m 18 s
Query 2: 1 m 2  s
The total running time is 2m 20s.

Scenario 2: each query in a separate editor of the same Studio instance, running concurrently:

Query 1: 2 m 36 s
Query 2: 2 m 09 d
The total running time is 2m 40s

Scenario 3: each query in a separate Studio instance, running concurrently:

Query 1: 2 m 29 s
Query 2: 2 m 19 s
The total running time is 2m 29s

While scenario 2 and 3 seem to run queries concurrently, why the running time hasn't change much? For all scenarios, the CPU usage is between 17% to 21%, the disk usage is 50MB to 70 MB per second. No significant differences between the usage of CPU and disk are observed.

I am wondering what's happening under the hood? How does SQL Server execute these queries? Why is the running time nearly the same for all three scenarios? Is there any way to speed it up?

I'm using SQL Server 2016 Developer Edition on Windows 10 64-bit. I have quad core i7 and SSD in my laptop.

After testing more scenarios with 4 queries running together, I think the possible cause of low usage of CPU and disk is the hyperthreading with my Core i7:

With two editors running two queries concurrently, the most CPU usage is 25%.
With 4 editors running 4 queries concurrently, the most CPU usage is 50%.

Are you guys aware of ways to let one query exclusively use one core? Turning off hyperthreading is not an option for me, since my ThinkPad T460p doesn't support such an option in the BIOS.


texts: Query to Find Out How Many Days Until Database Is Full

texts:
Using my central MDW database I am trying to make a query that will be able to calculate the number of days until all my databases free space will be full. Currently the query I have is overly complicated and takes long to run.

I am wondering if there is a simple solution using the following columns to calculate this. Columns I have are:

row_number
snapshot_time
instance_name
database_name
dbsize
current_usage

All tables are found in the MDW database.
Any suggestions are greatly appreciated!


texts: SQL Server DB table data size query result not increasing if data count increasing

texts:
I have a query which will show the Table Name, Number of Rows and Size of the record of a Database tables in SQL Server. when I executed the query I got a result showing

TableName            NumberOfRows   SizeinKB
TBL_PROCESS_AUDIT2   1              16

But When I inserted more record into the same table using the same insert query and executed the query, it showing more number of rows (50) but the same size (16kb).

TableName            NumberOfRows   SizeinKB
TBL_PROCESS_AUDIT2   50             16

Actually when the number of rows of a table is increasing, it's size also should increase. So for 1 record the size is 16KB so for 50 records, the size should be 800KB. When I inserted bulk record like some 2000 records, the size is varying.

What would be the logic or problem in my query? I want the actual size of the records of a DB table.

My Query:

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes


texts: Oracle 12c connection configuration errors

texts:
I've installed Oracle 12c, and I'm trying to create a connexion using SQL Developer but I've got errors.

Here is the parameters after installation and users parameters:

Like in the photo, the port is 5500 but in tnsnames.ora I have this:

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

  PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

The port in here is 1521, so I'm confused which parameters use to create a connexion. It's my first installation of oracle.

I've changed the port to 1521. Now I've got this error:

  ORA-01017:invalid username/password; logon denied

I changed username to "system". It works on SQL Developer, but on the browser I don't know how to access.


texts: Switch MySQL lag slave to new master and keep lagging (5.1)

texts:
We have the following scenario:

Set of slaves replicate from the master, separate standby master (a.k.a slave with binlogs) replicates from the same master; lag slave, powered by pt-slave-delay, replicates from the same source.

We also utilise mha4mysql to switch slaves from one master to another in case of need.

All would be dandy, if not for lag slaves.

Problem

Let's say lag slave is running 12 hours behind master.
Switch lag slave from Master to Standby Master in such a way that failover occurs immediately (i.e. CHANGE MASTER is executed on lag slave the same time as on regular slaves) and that lag slave keeps lagging 12 hours.

Issues

While I know that slave keeps track of master binlog position in its own relay log, I can calculate master position to 12 hours in the past, however, as far as I understand it Standby Master (aka slave with binlogs) has no relation between own relay logs and own binlogs.

Question

Is there a reliable way to calculate position of the Standby Master we need to switch to (discarding the rest of relay logs on lag slave, obviously) and restart replication from 12 hours behind, but on the new master?

NOTE we are running MySQL 5.1, so 5.5 (or 5.6 with GTIDs) aren't a solution for now unfortunately.

Thank you.


texts:
I am running MySQL optimisation wizard, mysqltuner, which gives the suggestion:

  User 'root@%' does not specify hostname restrictions.

How is this achieved? I can't readily find an answer.


texts:
How to select rows with duplicate values (non-distinct rows) and summarize with a count of the number of duplicate values in order of the most frequent duplicates?

Given a table of:

person_

name_
favorite_color_

…with this data:

Bob       Brown
Lucy      Lavender
Orin      Orange
Liam      Lavender
Betty     Brown
Blair     Brown

…show:

3         Brown
2         Lavender

We omit “Orange” from the list as it is distinct, with no duplicate siblings.

I am most interested in Postgres, but I expect this can be done in standard SQL.


texts: Is it possible to synchronize two ms access db?

texts:
We have a flower store where we use MS Access and a client program to work with the database. But this program can work only with one database.
We are going to open a new store, so now we have to solve the problem of syncing between the two databases. It can be synced once a day.

Is this possible with MS Access 2007 tools, or we have to buy some program?


texts: Server version error where restoring backup with SQL Server Management Studio

texts:
I have SSMS v17.9.1 freshly installed. I believe the only later versions as of this post's date and time are only preview versions.

I am attempting to restore a .bak file. I get the error that: The database was backed up on a server running version 12.00.452. That version is incompatible with this server, which is running version 11.00.2100

I have also installed the latest preview version and attempted restore with that. The error still says Im running version 11.00.2100 and is incompatible.

What is it that I need to install / upgrade to get my 'server' version to 12.00.452 (or 12 to be generic)?


texts: Can MyISAM tables affect the backup recovery of innodb tables on Amazon RDS?

texts:
I have an Amazon RDS instance containing several databases. All the tables are innnodb except from one, that is MyISAM and currently cannot be converted to innodb.

I know that the recovery of MyISAM tables are not guaranteed by Amazon, I just want to know if this MyISAM table can affect the recovery of the other (innodb) tables.


texts:
I try to reIndex() my collection but not working and It removed all indexes. I checked mongod.log and see:

2021-01-18T05:56:59.492-0600 E STORAGE  [conn1] WiredTiger error (5) [1610971019:492802][20912:0x7fb542e32700], file:collection-0--9173913559869523275.wt, WT_CURSOR.next: __posix_file_read, 449: /var/lib/mongodb/collection-0--9173913559869523275.wt: handle-read: pread: failed to read 12288 bytes at offset 52972773376: Input/output error Raw: [1610971019:492802][20912:0x7fb542e32700], file:collection-0--9173913559869523275.wt, WT_CURSOR.next: __posix_file_read, 449: /var/lib/mongodb/collection-0--9173913559869523275.wt: handle-read: pread: failed to read 12288 bytes at offset 52972773376: Input/output error
2021-01-18T05:56:59.492-0600 F -        [conn1] Invariant failure: advanceRet resulted in status UnknownError: 5: Input/output error at src/mongo/db/storage/wiredtiger/wiredtiger_record_store.cpp 1765
2021-01-18T05:56:59.492-0600 F -        [conn1] \n\n***aborting after invariant() failure\n\n
2021-01-18T05:56:59.513-0600 F -        [conn1] Got signal: 6 (Aborted).

MongoDb version 3.6.21 and i use Wiredtiger with cache_size 38G. 1 database and 1 collection with 12000000 documents and folder size 81G
My server: 64G Ram + 480SSD. Please help me fix the issues!


texts:
We have a table with few million rows on production. We ran this query and it froze our database,

USE ExampleDatabase

GO
CREATE NONCLUSTERED INDEX IX_Table1_URL
ON [dbo].[Table1] ([URL])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table1_LastModified
ON [dbo].[Table1] ([LastModified])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table2_URL
ON [dbo].[Table2] ([URL])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table2_LastModified2
ON [dbo].[Table2] ([LastModified2])
GO

I am a developer with very limited knowledge of indexes, I am not sure where to start, just to add both Table1 and Table2 have million of rows.


texts: MongoDB Upgrade to 3.0: HostAndPort Exception

texts:
I want to upgrade my small MongoDB-Cluster (1 mongos, 3 config-servers, 2 shards a 3 nodes) running on OpenSuSe 13.1 (64bit) from 2.6.7 to 3.0.5. But it fails at the second step of the upgrade guide: upgrade a mongos instance

What I'm done:

downloaded a legacy-linux-64bit-version of MongoDB 3.0.5
deactivated load balancer
tried to start mongos with

mongos --configdb data/configdb --config config/router.conf --upgrade

This is the output of mongos:

2015-08-07T15:41:43.281+0200 W SHARDING running with 1 config server should be done only for testing purposes and is not recommended for production
2015-08-07T15:41:43.290+0200 I SHARDING [mongosMain] MongoS version 3.0.5 starting: pid=22726 port=27027 64-bit host=myhostname (--help for usage)
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] db version v3.0.5
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] git version: 8bc4ae20708dbb493cb09338d9e7be6698e4a3a3
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] build info: Linux build2.ny.cbi.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] allocator: tcmalloc
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] options: { config: "conf/router.conf", net: { bindIp: "myhostname", port: 27027 }, security: { keyFile: "res/mongodb.key" }, sharding: { configDB: "data/configdb/" }, upgrade: true }
uncaught DBException in mongos main:
9 Empty host component parsing HostAndPort from ""

I'm using the same settings as the already running instance, so the network settings should be correct.

Anyone a clue what I'm doing wrong here? Why is there a warning about one config server only, while I'm using three? And what does the exception mean?


texts: SQL Server 2012 Business Intelligence licensing

texts:
I have Microsoft SQL Server 2012 Entreprise Edition license, What are the tools needed to work on SSIS/SSAS/SSRS?

Since BIDS was replaced with SSDT on the 2012 version, does it mean I will need to get a separate license and install Visual Studio then get the free SSDT add-in?

Sorry for the confusion, I am new to Microsoft BI and can't find relevant information onlines


texts: selecting all row of max value of a column

texts:
I have this simple query.

SELECT

    t3.C_COMDTY_SHORT_DESC
    ,t3.C_HS_CODE
    ,t3.I_COMDTY_ID
FROM

    ADM_COMMODITY_MASTER t3
WHERE
 t3.C_HS_CODE = '2203.00.00'

which return two rows as below.

Now i want to select the row of corresponding the column which has max I_COMDTY_ID.
so i write like this.

SELECT

    t3.C_COMDTY_SHORT_DESC
    ,t3.C_HS_CODE
    ,t3.I_COMDTY_ID,max(I_COMDTY_ID)
FROM

    ADM_COMMODITY_MASTER t3
WHERE
 t3.C_HS_CODE = '2203.00.00'  group by t3.C_HS_CODE;

This is what is got.

Why column C_COMDTY_SHORT_DESC value is BEER ? I know how to get max row and other way around. But why it is not working ?


texts: multi-tenant data related to multiple customer?

texts:
tenant type design on the database schema tables. We have about 20 customers data and single web application is serving it.

Question, how do we improve query performance or what type of database architecture would you recommend in this situation?

In ideal world it would be each customer sits on different database user in that way we have separation of data as well as performance. But if we can't then what are the options??


texts: How to find the last error codes and messages of failed statements executed by a user?

texts:
Hopefully, a program that connects to a Oracle DB does sufficient logging of errors on its own, e.g. when doing a bulk insert the error reporting of a program could produce something like this:

ORA-24381: error(s) in array DML
statement: insert into BARTABLE(...) ....
row 5: code=12096, message=ORA-12096: error in materialized view log on "FOO"."BARTABLE"
  ORA-12899: value too large for column "FOO"."MLOG$_BARTABLE"."XCOL"
    (actual: 50, maximum: 100)

But sometimes it is not possible to change all existing DB client programs.

For that usecase - where to look up the same information inside the database?

Perhaps there are some 'virtual' oracle tables/views that one can use to get the last x error messages/codes for a certain user ...

Ideally also including the corresponding SQL statement.


texts: MySQL: JOIN empty tables with a constant value column, COUNT(*) returns a 0, but adding a GROUP BY on the constant value returns nothing?

texts:
Can someone explain why, when these tables are empty, the following query returns a COUNT(*) of 0 and a MAX(Timsestamp) of null...

SELECT 'Combined', COUNT(*), MAX(Timestamp)
FROM TableA
JOIN TableB USING (Id)

...while adding a "GROUP BY" on the constant value (first column) returns no rows at all?

SELECT 'Combined', COUNT(*), MAX(Timestamp)
FROM TableA
JOIN TableB USING (Id)
GROUP BY 1

I wasn't able to find anything in the MySQL documentation explaining this behavior. (Re-posted from https://stackoverflow.com/questions/35438668/mysql-join-with-empty-tables-and-count-returns-a-row-with-0-count-but-addin)


texts: SSRS on 2008 R2 and its Database on 2016

texts:
Can we have SQL Server Reporting Service on SQL Server 2008 R2 and its Database(ReportServer and ReportServerTempDB) on SQL Server 2016?

PS: Both are enterprise edition.


texts: Does PostgreSQL handle case-insensitive database as good as others?

texts:
First things first, please forgive me if my question seems so naive (I am not a DBA).
I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of the official docs useful.
I have tried the following but there was no luck.
CREATE COLLATION ci_fa (provider = icu, locale = 'fa-IR-x-icu', deterministic = false); -- also locale = 'fa-IR' and 'fa-IR.UTF8'

I found this answer but the locale used there, is not based on what I am looking for. Also, the example in the docs reads as:
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

This works fine but I faced with a wired behavior in sorting. If I had a non-English character in my order column the result would be different. When I have a, A, b and the non-English char, the result is a, A, b. And when I have a, A, B and the non-English char, the result is A, a, B.

On the other hand, reading a lot of articles and questions made me wondered about the following questions?

Having case-insensitive data impact the performance, so should I use it or not? Considering the following facts:

99% of my data has no casing. Our language is simpler so there is no Lower/Upper case in letters :)
I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect Full-Text.
I don't like any of the suggested solutions here (How to make “case-insensitive” query).
Although I use EF Core 5, I have DB functions to handle complex queries.

I am at the early stage of my project and for the first time, I decided to migrate to PostgreSQL from SQL Server. Should I use MySQL instead?

I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).
I need Geolocation.
I need Full-Text.
And obviously, performance.


texts: Linked Server works on SSMS and isn't found on SSDT

texts:
My Microsoft SQL Server has a linked server to another SQL Server. I have a simple query that runs on mine, deleting a remote table (truncate doesn't work) and inserting data to it.

This query works fine if it's run in SSMS. But I need it to be run on a schedule, so I add it in a dtsx as an EST. The problem is that when I run this dtsx, be it manually on SSDT or automatically scheduled on SQL Server Agent, it fails with the message:

  could not find server '-----' in sys.servers`.

This is very odd. Of course the server is in sys.servers and the linked server is working. As I said, the exact same query works when run on SSMS. I can't even start to imagine what may be causing this inconsistency. Other linked servers work fine.

Can somebody guess what may be causing it and what I could look for a clue? Googling this error only points me to explaining how to config linked server, but it's already there!


texts: Can't connect to other instance of SQL Server 2012

texts:
I'm working through a Microsoft exam study book.

I have set up 2 SQL Servers (both 2012 Standard on Server 2008 R2) and a DC.

The book wants me to remotely connect SQL-A to SQL-B. Unfortunately the book doesn't say what to do if this doesn't work.

I can ping all servers from all servers.

Both SQL Severs are logged in with the same user who has admin rights but I can't connect the 2 SQL Servers.

Things I've tried from answers to similar problems:

Allow remote connections is ticked
Port 1433 is set as the TCP Port
Browser service is running
Firewall has an inbound rule set up.

I removed both SQL Servers from the domain and added them back in but that didn't change anything. I can't see what I've missed.


texts:
According to BOL for SQL Server 2008 R2 the data type decimal requires the following
storage bytes:

Precision  Storage bytes

1 - 9      =>   5

10-19      => 9

20-28      => 13

29-38      => 17

However, when I do a datalength() on a column that is formatted decimal(19,5) and
has the value of 10999.99999 I get back 5 bytes ? Which is according to my understanding
a precision of 10 and not of 9 and should result in 9 bytes.

this results in two questions:

is the size of the table with this column not depending on the column definition,
instead the true values within the column define the table size ?
why is the information in BOL not matching with what datalength() returns ?


texts: Postgres full text search on words, not lexemes

texts:
I've got a table with columns of text that I'd like to search by word, not lexeme. More to the point, that I'd like to have indexed by word rather than lexeme. We've got error dumps with a lot of code references, which don't work well with any natural language dictionary.

Is there a way in Postgres to have FTS parse by word boundaries without resolving words to lexemes? If I have to define a list of boundary characters and a catalog of skip words, that could be fine. Does this require crafting some kind of custom dictionary, or is there something already like this available?

I keep thinking I'm missing something obvious, and then not finding it.

For the minute, trigram indexes are okay, but I would really prefer what amounts to a unique keyword parser for the text.

Postgres 11.4 on RDS.


texts:
I'm working on a script I can run on new servers to do some initial configuration.  I would like to be able to skip some sections but the problem I'm running into is the "GO" that's required.  Because of the GO I can't wrap my sections of code in IF statements.  For example

:setvar EnableDBMail 0

IF ($(EnableDBMail) = 1 )
BEGIN
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

END
....etc

The problem is this won't work because you have to use GO.  Is there another way of doing this?  I've tried searching but have yet to find anything.


texts: Default Binding and Default Schema is coming blank in local db, after running SSIS package

texts:
I am trying to copy database from SQL server 2012 which is hosted on AWS, to my local SQL server 2012 Database, I had tried couple of ways to perform this task, keep on getting similar issue with all of them << Default Binding and Default Schema is coming blank>> in local database after running SSIS Package. not sure why it's not getting copied from source database

FYI...

I had tried same in Local database server, to copy one db to another, still same issue, I am very sure nothing to do with AWS

Import/Export data
Created SSIS Package using
<< Transfer Database Task>>
<>

Please suggest me how to fix this issue, is this expected behaviour in SSIS..? do we have any work around to fix this problem..?

Source DB Screen


texts: How to index an property object in MongoDB?

texts:
I've the following sample document:

{
    "_id" : ObjectId("556e177ca43da29e040041a7"),
    "user_id" : NumberLong(24),
    "request_datetime" : ISODate("2015-06-02T15:52:12.000-05:00"),
    "total_amount" : 102.62,
    "status" : NumberLong(1),
    "adfields" : {
        "27" : "413703562015060200132",
        "28" : "S003POS4",
        "29" : "HAYLEYS",
        "30" : "1"
    }
}

The problem is that i need to index the "adfields" property, but it doesn't work with a "multikey" index type, neither using "single field index".
I tried to index the adfields property using a single field index, but when i make a query for an specific value of adfields.27 it doesn't use the index.
I can't make an single field index for adfields.27 because those numbers are created dynamically.

Do you have any idea of how to do this?
Thanks a lot.