texts: Composite or Single-Field Clustering Key

texts:
My DBA and I are having a disagreement on index structure.

Consider data for a medical claim...

We have the Header table with fields like:

ClaimId (varchar(50)), PaidAmount, MemberID...

and for each header we have one or more records in Detail like:

ClaimId (varchar(50)), LineNumber (smallint), MemberId...

The efficiency of the structure or duplication of the data is outside the parameters of this question.

There are additional tables that tie back to individual Detail lines by ClaimId, LineNumber, and we frequently JOIN Detail and Header on ClaimId as well.

For the Detail table, which would be preferable for the clustered index key:

ClaimId

or

ClaimId, LineNumber

ClaimId alone is NOT unique, but the combination of ClaimId, LineNumber is unique for a Detail record.

One of us believes that ClaimId alone is a better clustered key because it is narrower, and that the lookup will be just as efficient since we need to know the ClaimId before we lookup the LineNumber.

The other believes that the combination of the two is better because it removes the need for an additional RowID, and can be used in JOINing to the support tables that need the LineNumber as a JOIN condition.


texts: How to include a datetime parameter within a stored procedure along with string query?

texts:
My stored procedure is as follows,

    -- Add the parameters for the stored procedure here
   @FromDate datetime,
   @ToDate datetime

       --Select query
      DECLARE @query nvarchar(max)

      set @query='SELECT [col1]
                  FROM [Table1]
                  WHERE ([col2] BETWEEN '''+@FromDate+''' AND'''+@ToDate+''')'

       execute sp_executesql @query

Executing this string query results in following error,

"Conversion failed when converting date and/or time from character string."

Any one please help me to sort this out...


texts: Why is LIKE producing different results than my regex?

texts:
Using Postgres 9.5.  Having some trouble using regular expressions to find matching rows.  I tried

select * FROM my_object_times t where t.name ~ '/\(.*\)\s*$/'
...
(0 rows)

which returned no rows, but this query

select * FROM my_object_times t where t.name like '%(%)';

returns tons of rows.  How am I messing up the regular expression above?


texts: How autocommit = 1 impacts statements between BEGIN/COMMIT?

texts:
Let's say I have following code:

begin;
set autocommit = 1;
update tbl1 set col1 = 1;
update tbl1 set col1 = 2;
commit;

What would be the difference if autocommit (line 2) was set to 0?


texts: Append object to document node.js/mongodb

texts:
Say I have a mongodb document I'm trying to update. The schema is level1 (object)> level2  (object) > "objects".
If I want to add another object to the end, what is the method to do that? Do I use findoneandupdate?
I'm trying to append an object to an object
As of right now, it overwrites whatever is in the object
await base.findOneAndUpdate({ baseURL: contentObject.basURL },
                    {
                        links: {
                            subPageURLs: {
                                [contentObject.subPage]: contentObject.links.subPageurls[contentObject.subPage]
                            }
                        }
                    },
                    { upsert: true }, (err, doc) => {
                        console.log(err)
                        db.close()
                    })


texts: Conversion failed error - but not converting anything and no date/time data

texts:
I am creating a table variable in T-SQL and encountering the "Conversion failed when converting date and/or time from character string", despite the fact that I'm not converting anything and the column in question contains no date data.

Code below:

Declare @table2 (variable1 bigint, variable2 varchar(255), variablen basetypen)

insert into @table2
Select Variable1, variable2
from
etc...

Everything is working, except inserting variable2.  Selecting works fine, and I've checked every single value (there only are 10) and none of them contains a date or time value, or anything which looks like one.  (Can't post text because it would be identifying/proprietary).  In fact, every one has exactly the same value.

What's going on here?  I feel as though I might be doing something silly...


texts: First time using an Index - SQLite / Android

texts:
I'm creating a SQLite Database for Android. I can do fairly simple SQL, but I haven't used  an index before. I'm working on a table with >150,000 records and I've never dealt with a database so large. I know that I need to add at least one index to make my queries faster. Here is the table:

CREATE TABLE IF NOT EXISTS T
(
    a TEXT, b TEXT, c INT, d TEXT // ... 14 more TEXT columns
)

My only queries on this table are only on a, b, and c like this:

SELECT * FROM T WHERE c = ?
SELECT * FROM T WHERE a LIKE ?% AND b LIKE ?%
SELECT * FROM T WHERE a LIKE ?%
SELECT * FROM T WHERE b LIKE ?%

This table is filled once, and no changes are made to it after that. It's acceptable to take some extra time after import to add the index.

Can some one more knowledgeable than me tell me what index or indices I should create and if I should wait until after filling the table to add them?

This is my first question on this site. I thank you for you looking at this for me!


texts: How to properly troubleshoot Merge Replication False Conflicts

texts:
We have SQL Server 2008 R2 Merge Replication setup.

Lately I started getting conflicts for some tables, and when I check the conflict viewer, I can see that the winner & the loser column values are identical!

I am sure that these data are being entered by only one subscriber.

I used sp_showrowreplicainfo to get more details about the conflicting row & I got 2 rows : one for the Publisher & one for the subscriber with both rows having the "version" as 1.

I also implemented Brandon's method: Auditing changes in Merge Replication, but it was showing only a normal insert!

Would you please tell me how to start troubleshooting this kind of conflict?


texts:
We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine.

However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF is selected in the drop down. I don't see this line added to the my.ini file when viewing with notepad.

Should I check this setting and set it to ON so we can utilize the enabled innodb settings (buffers & log file etc.) or does this not matter?

Thanks


texts:
Our Company wants to use MySQL for one software product. This product is not open-source. Is it legal to use it freely ?

If not, how about PostgreSQL ? Is this okay for our purpose ?


texts: MySQL code to show specific rows based on another...?

texts:
I'm having a hard time figuring out how to do this, and I can't explain it well.
Since I don’t know how to explain it I can’t seem to locate an answer online by simple research. So, I have to draw it out and ask for assistance.
Here are the details:

Columns for the example: My original SQL query created this table (Data specific to this problem comes from one HUGE table)

Sample code:

SELECT
inventory .01 as  `Item#`,
inventory .02 as `warehouse`,
inventory .03 as `qty`,
inventory .04 as `price`,
inventory .05 as `weight`,
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5

Returns this:

Item# | warehouse | qty | price | weight | other
1           1        1      3        4       5
1           2        6      3        4       5
1           3        3      3        4       5

I've re-written the table using group by to make the data look like this:

SELECT
inventory .01 as  `Item#`,
inventory .03 as `qty (whse1)`,
inventory .03 as `qty (whse2)`,
inventory .03 as `qty (whse3)`,
inventory .04 as `price`,
inventory .05 as `weight`,
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5
GROUP BY
inventory .01

Returns This:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            1             1           3        4       5

Here’s where I’m stuck. I don’t know how to tell write the SQL code to make the 3 warehouse fields to show like this:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            6             3           3        4       5

I’m sure this is easy to accomplish. I just can’t figure it out.

My DB contains 30K rows, and each item is included 10 times (once per warehouse.) I want to be able to see 3K rows instead, based on the item no, with the qty per warehouse. This is just a sample of the data, the real tables and fields are clearly labeled. This is just a sample of the issue I'm dealing with.

The price, weight and other are all the same for all the items. The only real difference for these parts is the qty per warehouse.

Adding to give real code example. The responses I've received so far seem to come from creating a table from scratch vs. selecting data from an existing table. I've tried implementing the example and I'm getting errors. I can attach a screenshot of some of the data to give you an example of the content included in the database.

SELECT
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
COALESCE(inventory.ONHAND,0) AS `On Hand (LA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (SF)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (HP)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (CHINA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (INDO)`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (LA)`
AND inventory.WHSE=00)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (SF)`
AND inventory.WHSE=10)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (HP)`
AND inventory.WHSE=20)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (CHINA)
` AND inventory.WHSE=50)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (INDO)
` AND inventory.WHSE=70)
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD


texts: What is limiting the batch requests per sec

texts:
We have a newly created Azure SQL Server 2014 instance. The specs for the server are as follows

Pretty bare bones, I know. Since this is just a testing server, we have opted to set up the data files on the C:\ drive itself.

We wanted to do some performance testing to see what it would be like compared to an on-premise solution. To do this, we created a database with the following table:

CREATE TABLE [dbo].[temptbl_1](
    [seq] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [field1] [varchar](50) NOT NULL,
    [field2] [int] NOT NULL,
    [field3] [datetime] NOT NULL,
 CONSTRAINT [PK_temptbl_1] PRIMARY KEY CLUSTERED
(
    [seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Then, we ran update stats on the entire db. We then ran the following insert statement to see what kind of results we would get:

insert into dbo.temptbl_1 (field1, field2, field3)
select 'here is a great big string',123456, getdate()
go 10000

The first time we ran it from my local machine's SSMS (2016 preview), and were shocked to see that it took ~9 minutes!

We then ran the query again from the server itself in SSMS, and it took ~2 minutes. This seemed much more plausible. But why is it taking so long from the client?

Watching the activity monitor, I can see while the query is running from my local machine, the batch requests per/sec never goes above 20. Doing the math you can see why this would never finish faster than 8-9 minutes. However when running the query from the server, we can see that the batch requests per/sec go all the way up to 100.

We have tried to read as much as we can, and have looked through settings, and cannot figure out why it seems to be limiting the query from my local machine, but the query from the server it doesn't seem too.

Why does the server seem to be limiting the query from my workstation, but not when run from the server itself?


texts: Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

texts:
I'm working on a question/answer project and having a hard time coming up with a suitable database structure.

Each question can have from 1 - "n" answers, and each answer is associated with one of 6 predefined categories.

For example:

Q1) Which magazine would you prefer to read?

Science (+1 for Science category)
Wired (+1 for Science, +1 for Tech)
Vogue (+1 for Fashion)
Time (+1 for Information, +1 for Social)
Fortune (+1 for Business)
People (+1 for Social)

Q2) Have you taken an art class?

Yes (+1 for Artistic)
No (+0 for Artistic)

Q3) What's the best part of a movie?

Action (+1 for Science)
Story (+1 for Social)
Music (+1 for Artistic)
Acting (+1 for Artistic)

I know I should have one table for Questions, and a separate table for Answers linked by question_ID, but I'm not sure how to deal with a variable number of answer choices that are different for each question, and their corresponding categories.


texts:
In oracle: employees table contain more than 10 million data. For update I'm using rownum for faster update.

CREATE OR REPLACE PROCEDURE emp_up AS
begin
  loop
      update employees set commission_pct=0 where commission_pct is null and rownum <=10000;
      exit when sql%notfound;
      commit;
  end loop;
end;

In postgresql: postgresql does not support rownum. Is there any way to update employees table in postgres with the similar way in oracle?


texts: I have a database that's growing larger by the week (as expected).  Key tables are becoming huge and slower to perform queries against.  Whilst the indexes are sufficient, I've read the partitioning will improve the speed.  What I was thinking on doing was creating partitions on these tables via year.  I understand how the partitions would work for previous years.  What I've not managed to find is how do you future proof these partitions?  Do you have to create partitions for years to come?  Or do you create the partition at the end of the current year?


texts:
I'm running following:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)
# uname -a
Linux X 2.6.32-504.3.3.el6.x86_64 #1 SMP Fri Dec 12 16:05:43 EST 2014 x86_64 x86_64 x86_64 GNU/Linux
# rpm -q mysql-server
mysql-server-5.1.73-3.el6_5.x86_64
#

I issued STOP SLAVE; and deleted master.info and now /var/log/mysqld-error.log is producing following:

[ERROR] Error reading master configuration
[ERROR] Failed to initialize the master info structure

What's the proper way to disable MySQL replication?


texts: ORA-01745: invalid host/bind variable name using ORACLE

texts:
With my below debugged query, I got error as

  ORA-01745: invalid host/bind variable name

Here is the query,

SELECT DISTINCT sv.mkey,
  vehicle_no,
  TO_CHAR (date_in, 'dd-MM-yyyy')
  || ' & '
  || time_in vehicleindate_time,
  TO_CHAR (date_out, 'dd-MM-yyyy')
  || ' & '
  || time_out vehicleoutdate_time,
  gate_no_in
  || ' & '
  || gate_no_out ingate_outgateno,
  gd.good_type goods_type,
  net_weight netweight,
  TO_CHAR (challan_date, 'dd-MM-yyyy')
  || ' & '
  || challan_no challandate_no,
  remark_in remarkin,
  NULL receipt_no,
  date_in
FROM xxcus.xxgid_supinv sv,
  xxcus.xx_supinv_goodtype gd,
  xxcus.xxacl_xxgid_user_mst ms
WHERE (gd.good_type_code(+)         = sv.good_type)
AND ( sv.project_id                 = DECODE(:'  1368','0', project_id, :'1368')
AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:'  ', '0', date_in, :'  ')
AND vehicle_no                      = DECODE(:'  GJ-06-0Z-9202', '0', vehicle_no, :'  GJ-06-0Z-9202') ))
AND sv.delete_flag = 'N'
ORDER BY date_in DESC, vehicle_no

I am using ORACLE. I don't know what's the error. I am not much into i. Kindly suggest.

UPDATE

my code of query

strquerry = "SELECT DISTINCT sv.mkey, vehicle_no, TO_CHAR (date_in, 'dd-MM-yyyy') || ' & ' || time_in vehicleindate_time, TO_CHAR (date_out, 'dd-MM-yyyy') || ' & ' " +
                 "|| time_out vehicleoutdate_time, gate_no_in || ' & ' || gate_no_out ingate_outgateno, gd.good_type goods_type, net_weight netweight, TO_CHAR (challan_date, 'dd-MM-yyyy') " +
                 "|| ' & ' || challan_no challandate_no,remark_in remarkin, NULL receipt_no, date_in FROM xxcus.xxgid_supinv sv, xxcus.xx_supinv_goodtype gd, " +
                 "xxcus.xxacl_xxgid_user_mst ms WHERE (gd.good_type_code(+) = sv.good_type) AND ( " +
                 "sv.project_id = DECODE(:'" + cmbProjectName.SelectedValue + "', 0, project_id, :'" + cmbProjectName.SelectedValue + "') " +
                 "AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:'" + txtdatefilter.Text + " ', '0', date_in, :'" + txtdatefilter.Text + "') " +
                 "AND vehicle_no = DECODE(:'" + ddlVehicleno.SelectedValue + "', 0, vehicle_no, :'" + ddlVehicleno.SelectedValue + "') " +
                 ")) AND sv.delete_flag = 'N' ORDER BY date_in DESC, vehicle_no";


texts: Query optimizer recommends adding index instead of using existing index

texts:
I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query.

First the table. Column names changed to protect the innocent :-)

CREATE TABLE [myTable] (
  [id] [int] IDENTITY(1,1) NOT NULL,
  [serialNumber] [varchar](12) NOT NULL,
  [sName] [varchar](64) NOT NULL,
  [meanValue] [int] NOT NULL,
  [range] [int] NOT NULL,
  [modifiedDate] [datetime] NOT NULL,
  CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED ( [id] ASC )
)

Create the index in question:

CREATE NONCLUSTERED INDEX [IDX_myIndex]
ON [myTable] ([serialNumber], [sName], [meanValue], [range])
INCLUDE ([modifiedDate])

Add data for testing using your generator of choice ;-) Run the following query (table only has a few million records)

SELECT TOP 1000
  [serialNumber],
  [sName],
  [meanValue],
  [range],
  [modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'

The query optimizer recommends using a new index where the additional where clauses are covered in the INCLUDE instead of part of the key:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[myTable] ([sName])
INCLUDE ([serialNumber],[meanValue],[range],[modifiedDate])

I was under the impression that a broader index that encompasses more columns will be used as the index as long as the order of the WHERE clauses represents the order of the columns indexed.

If I also WHERE on the modifiedData the index gets used and the query optimizer doesn't complain:

SELECT TOP 1000
  [serialNumber],
  [sName],
  [meanValue],
  [range],
  [modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'
AND ([modifiedDate] >= '2000-04-25' AND [modifiedDate] < '2019-04-30')

The DBA link
SQL Server 2008R2 - Why is my index not used suggests a closer correlation between the index key and includes with the SELECT statement helps determine index use (but in my example they are basically the same). I have lots of rows, which probably satisfies the row use probability test, and there are no NULLs - thus negating the NULL effect of the index.

I thought, maybe incorrectly, that an index A, B, C, D would cover an query where A, B, C, or A, B, or A would be run. Is this assumption wrong? I realize there could be edge conditions that throw this basic concept off-kilter, but at a fundamental level, isn't this roughly how it's supposed to work?

Thanks in advance for any help, pointing out the stupidity in my ways, recognizing I need to go (back) to DB school etc... :-)


texts:
I am trying to use a result from a select for another select:

DECLARE
_port int;
BEGIN
select alter_port into _port from database where ....;
select data inot data_list from database where PortNumber = alter_port;

The second select always fails, the first select returns the data I want.

But, when I force assign value

alter_port := 2602;
select data from database where PortNumber = alter_port;

Then I get the data I need.

Why has this failed? I know the table has the data I want.
How do I check Error code for select statement?

Below are the table and function definitions:

CREATE TABLE AltRoute (
    GUID uuid PRIMARY KEY,
    PortNumber int NULL,
    ControllerNumber int NULL ,
    AltRtePortNumber int NULL ,
    AltRtePortName varchar (256) NULL ,
    AltRtePortList varchar(300) NOT NULL DEFAULT '0',
    HighAltPort int NULL DEFAULT 0,
    MediumAltPort int NULL  DEFAULT 0,
    LowAltPort int NULL  DEFAULT 0,
    HighEnable int NULL CONSTRAINT DF_AltRoute_HighEnable DEFAULT (0),
    MediumEnable int NULL CONSTRAINT DF_AltRoute_MediumEnable DEFAULT (0),
    LowEnable int NULL CONSTRAINT DF_AltRoute_LowEnable DEFAULT (0),
    HighPortList varchar (405) NULL CONSTRAINT DF_AltRoute_HighPortList DEFAULT (0),
    MediumPortList varchar (405) NULL CONSTRAINT DF_AltRoute_MediumPortList DEFAULT (0),
    LowPortList varchar (405) NULL CONSTRAINT DF_AltRoute_LowPortList DEFAULT (0),
    DynamicQuery varchar (4000) NOT NULL DEFAULT 0
);

CREATE FUNCTION sp_ComposeHighPortList (_startPort int) RETURNS integer AS $$
DECLARE
_altRtePort INT = 0;
_altRtePortList VARCHAR (405) = '0';
_port INT = 0;
BEGIN

  _altRtePort := 0;
  _altRtePortList := '1';
  _port := 0;

  SELECT HighAltPort into _altRtePort
    FROM AltRoute
    WHERE PortNumber = _startPort and HighEnable = 1;

  IF (NOT FOUND) THEN
    RAISE  NOTICE 'sp_ComposeHighPortList - FAILED: SELECT  _altRtePort = HighAltPort FROM AltRoute WHERE PortNumber = _startPort and HighEnable = 1, _startPort=%', _startPort;
    RETURN 0;
  END IF;

  IF (_altRtePort = 0 or _altRtePort = _startPort) THEN
    _altRtePortList := '';
    _altRtePort := 0;
    _startPort := _startPort+1;
    RETURN 0;
  END IF;

  SELECT RTRIM(HighPortList) into _altRtePortList
  FROM AltRoute
  WHERE PortNumber = _altRtePort;

  IF (NOT FOUND) THEN
    RAISE NOTICE 'sp_ComposeHighPortList - FAILED: SELECT  _altRtePortList = RTRIM(HighPortList) FROM AltRoute WHERE PortNumber = _altRtePort : %', _altRtePort;
  END IF;
...
...
..
END;
$$ LANGUAGE plpgSQL;


texts: Is there a way to prevent "reset role" in PostgreSQL

texts:
Is there a way to prevent a user from running "reset role" in PostgreSQL, and thus gaining permission he may not have ?

I have an architecture with many databases on a single server. An API connects to several databases on a local PostgreSQL server. I had planned to use Pgbouncer but it sounds like I would have to map one database/user in pgbouncer to the same database/user in PostgreSQL which sounds counter productive. Instead I would like to be able to reuse connections but have different authorization for different user upon connection.


texts: Microsoft SQL Server Error: 18456(Administrator is also not able to access)

texts:
We do have 2 windows servers(xx.xx.xx.118 and xx.xx.xx.119) and also we do have 2 SQL servers installed on those windows servers(xx01 and xx02).

The customer gave us one separate username(user1) to login to windows and SQL servers but while trying to login to xx01 SQL server from 118 servers we are getting the Error:18456. We able to connect to xxo2 SQL server from 118 servers with the same user.

We are unable to connect to xx01 SQL server from 119 windows server also with the same username(user1). We are able to connect to xx02 SQL server from 119 windows server.

We also got the Administrator account details both windows servers but unfortunately, Admin is also not having access to SQL servers.

I have gone through different URLs and tried all the available options but couldn't get the access.

Tried to run as different user...to change any settings first we don't have any user able to access.

The customer has no idea regarding other users having access to SQL servers and also they are not aware of sa account details.

The windows are virtual machines and we RDP to those servers.

Can someone suggest me how to get access to those SQL servers and resolve the issue?


texts: Using pg_prewarm to load X number of latest rows into cache

texts:
We have a large query that is extra slow when customers "run it the first time, early in the morning..."

So, I found pg_prewarm that I would like to use load into PG's buffer cache
a certain amount or most recently accessed rows (inserted, updated, or deleted)
from several tables that are used in the said query.

Furthermore, I need to make sure that 'warm up' does not exceed PG's cache (shared_buffers setting I believe, or am I wrong?)
To prewarm last 1000 pages of a single table I can do:

SELECT pg_prewarm(
    'mytable',
    -- "pre warm" last 1000 pages
    first_block := (
        SELECT pg_relation_size('mytable') / current_setting('block_size')::int4 - 1000
    )
);

Question #1: Does this approach makes sense?

The trick is that pg_prewarm can only load a certain number of pages,
so I need to calculate "how many live rows are in page for certain table"

-- show some settings
SELECT current_setting('block_size')::int4 AS page_size_bytes; -- 8192
SHOW shared_buffers; -- 512 MB

-- https://www.postgresql.org/docs/current/static/pgstattuple.html
--CREATE EXTENSION pgstattuple;
-- find out live row size and live rows per page
SELECT 'mytable'AS table_name, pg_size_pretty(tuple_len / tuple_count) AS live_row_size, 8192.00 / (tuple_len / tuple_count) AS live_rows_per_page, * FROM pgstattuple('public.mytable')

--"table_name","live_row_size","live_rows_per_page","table_len","tuple_count","tuple_len","tuple_percent","dead_tuple_count","dead_tuple_len","dead_tuple_percent","free_space","free_percent"
--"studies","1286 bytes",6.3701399688958009,652697600,462123,594431269,91.07,0,0,0,52329672,8.02

Question #2: Is my query above correct? Is this the proper way to get "live rows per page"?
Question #3: the live_row_size I get from above query is not the same as the result mentioned in this answer (by Erwin). Am I doing something wrong?

Based on live_rows_per_page I can then modify pg_prewarm to load enough last XXXX pages that contain 10,000 rows (6.37 x 10,000)

SELECT pg_prewarm(
    'mytable',
    -- "pre warm" pages of the last 10,000 rows for 'mytable'
    first_block := (
        SELECT pg_relation_size('mytable') / current_setting('block_size')::int4 - 63700
    )
);

Update #1
Regarding the Question #3, I get following when I run the query for mytable...
The output of pgstatuple is is different, probably because it is not listing the same items, but I am not sure...

"what","bytes/ct","bytes_pretty","bytes_per_row"
"core_relation_size",652697600,"622 MB",1412
"visibility_map",16384,"16 kB",0
"free_space_map",180224,"176 kB",0
"table_size_incl_toast",1101955072,"1051 MB",2384
"indexes_size",508289024,"485 MB",1099
"total_size_incl_toast_and_indexes",1610244096,"1536 MB",3484
"live_rows_in_text_representation",1138946462,"1086 MB",2464
"------------------------------",<NULL>,"<NULL>",<NULL>
"row_count",462123,"<NULL>",<NULL>
"live_tuples",0,"<NULL>",<NULL>
"dead_tuples",3,"<NULL>",<NULL>


texts: Server role with permission only to create server login, not alter one

texts:
I am working with Microsoft SQL Server Management Studio and I want to create a server role that has permissions to create server login but only create, not alter!

Does anyone know what is the permissions name?


texts: Error while executing query using send attachment

texts:
when i am executing the below query, I am getting error as:-

Msg 22050, Level 16, State 1, Line 4
Failed to initialize sqlcmd library with error number -2147467259.

This is my query:-

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Alerts',
@recipients = 'v-pusah@microsoft.com',
 @subject = 'Overlapping Subscriptions',
@body = 'kaise ho',
 @query = 'select b_id from tp_sub',

@attach_query_result_as_file = 1,
@query_attachment_filename = 'result.txt',
@query_result_separator=',',@query_result_width =5000000,
@query_result_no_padding=1


texts: How to send alert email to multiple recipients whenever SQL SERVER authenticated user is login in SSMS?

texts:
I need to trace on SQL SERVER authenticated user whenever he/she is logging in to the system, automatically send alert mail like this user is login with this IP and this time.

I have just one solution for this problem but this is not so proper because i have to see that manually.

Below solution is not work for alert mail because i have to run manually.

SELECT
   DISTINCT
    @@SERVERNAME ServerName ,
    DB_NAME(database_id) DatabaseName ,
    s.login_name ,
    s.[host_name] ,
    c.client_net_address ,
    c.connect_time
FROM    sys.dm_exec_connections AS c
        INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   c.auth_scheme = 'SQL'
    AND program_name IN ( 'Microsoft SQL Server Management Studio',
                          'Microsoft SQL Server Management Studio - Query' )
    AND c.connect_time >= DATEADD(DAY, -1, GETDATE())
    AND login_name <> 'prcommon';

Thanks.


texts: SQL query tune to replace UNION - Oracle

texts:
SELECT FN_ENQDATA_STRING (col1)
FROM tab1
WHERE tab1.col2 = tab2.col2
AND tab1.col3 = ?

I also have tab2 which has same columns as tab1 and i need to call function for that too. What I can do is

( SELECT FN_ENQDATA_STRING (col1)
FROM tab1
WHERE tab1.col2 = tab2.col2
AND tab1.col3 = ?
UNION
SELECT FN_ENQDATA_STRING (col1)
FROM tab2
WHERE tab2.col2 = tab2.col2
AND tab2.col3 = ? )

Tab1 and Tab2 have same column but no same values
But I need another option because union is taking too long.


texts: Autosync master and slave when master goes offline

texts:
I have a master slave setup using MySQL 5.0.
I have very frequent writes to the master and slave is used only for backup purpose (no explicit writes).

In my current configuration, the slave database is synced with the master by MySQL automatically via VPN. But if the system (win 7) consisting of Master goes down for some reason, after reboot the synchronization doesn't continue from the last synced record. Instead I have to stop the services at both the ends, delete and recreate the config files, move the sql of the backup from master to slave and restart the sync. All this has to be done every time my system goes down for any reason.

Is it possible to avoid this so that whenever master system goes down, the syncing starts again as usual ?


texts: SQL server DAC connection to mssqlsystemresource database

texts:
I have a problem while connecting to the mssqlsystemresource database.

When I try to connect with DAC connection (without restarting SQL server with -m option) it works, but I can't use the mssqlsystemresource database, because the server is not in Single User mode. The only way to use this database is to restart the SQL server in Single user mode.

But once I restart the server with -m option I can no longer connect with DAC connection. I get this error:

  the server is on Single-user mode, only an admin can connect to the
  server at this moment, SQL error 18461

I'm using SSMS on the same server so no need to verify the firewall
I have SQL Browser service running
I'm using a member of the sysadmin group
I have no other DAC connection to this server

I don't know what to do next. Any help, please?


texts: 1000's of SQL Update Statements taking forever to complete

texts:
I have to update stock prices in Pastel Evolution and need to create the update statements in C#, then execute the in SQL Server.

Each update statement looks like this..

UPDATE t1
SET t1.fExclPrice = 521.95, t1.fInclPrice = 1
FROM dbo._evPriceListPrices AS t1
INNER JOIN dbo.StkItem AS t2 ON t1.iStockID = t2.StockLink
WHERE t2.Code = '108085R'
AND t1.iPriceListNameID = 1 AND t1.iWareHouseID = 5;

The problem I'm experiencing is that there are more than 14000 update statements to execute in the batch and it takes forever.

Is there a way I can speed this up?

Here is my Estimated Execution Plan.

I could not add my Trace result here as it's too large :(


texts: Multiple aggregates with cross (lateral) join

texts:
Following on from Combining array_agg and unnest, given this data:

key |  a | b | c
------------------
 1  |  0 | 1 | {1,2}
 1  |  1 | 2 | {3}
 1  | -1 | 3 | {2}
 1  |  2 | 4 | {}

Executing this query:

SELECT
  d.key,
  min(d.a) AS a,
  sum(d.b) AS b,
  array_agg(DISTINCT x.c) AS c
FROM data AS d
  CROSS JOIN LATERAL unnest(d.c) AS x(c)
GROUP BY d.key

Gives the unexpected result:

key |  a | b | c
------------------
 1  | -1 | 7 | {1,2,3}

What's going on here, and how to get the correct sum?

Answer performance

On my actual data (16642 rows, 1942 keys, 6 aggregates), I get these cost estimates for each suggested solution.

a_horse_with_no_name option 1: 1761.12..49370.52
a_horse_with_no_name option 2: 0.57..89214.72
Erwin Brandstetter: 1761.12..49370.61


texts: how to clean some character from row in mysql?

texts:
How to remove some part of rows from column in mysql? I have a column like this

my desire output is like this


texts: Guidance on inter-server database or data transfer and system architecture

texts: I'm a database developer (not a DBA) responsible for about 20 different non-transactional 1TB SQL Server 2008 - 2012 databases each on its own server. In order to be able to query across databases, the business would like me to co-locate these databases on one query server; however for data loading purposes (periodic large bulk loads) it is convenient to maintain the 1 database/server model. For some databases the tables are truncated and reloaded on a monthly basis; for others, new data is populated on top of existing data via daily jobs. How do you recommend that I transfer data from the source databases to the query server? I want to maintain control over when the data is transferred.  Upgrading to a newer version of SQL Server is possible.


texts: Have Postgresql accept 1 and 0 as true and false for boolean

texts:
I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to postgresql, results in complains as it doesnt know how to cast them.

Loading like this...

psql --disable-triggers -1 -f /foo/bar/dump.sql

Due to the large amount of data and the sprinkling of boolean fields in the - regexing through to replace them with true/false appropriately has proved impractical.

After much googling i havent been able to find a way to have postgresql accept that = true and 0 = false for the duration of this data load. It seems simple enough, but its apparently not!

Hopefully someone knows exactly how to nail it and can help me out!


texts: Creating csv file with data having new lines and nullable column

texts:
I am creating a CSV file using one table data as below, the table have newlines as part of data and some columns are nullable

SELECT * INTO OUTFILE 'outfile'  FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' FROM T1;

Output:

"X\
YZ","ABC",1
"abc",\N,\N

When a newline is part of data then a newline is escaping with a backslash() in CSV but I don't want backslash.
When a column is null then data coming \N in CSV file and I don't want \N.

I want Output as below:

"X
YZ","ABC",1
"abc",,

Is there any way to achieve this in MariaDB?


texts: sp_BlitzFirst - option to save more than only SQL Server PerfMon stats?

texts:
First, I can't say enough good things about the FirstResponders kit. It's my "Go To" anytime something seems funny on the SQL servers.
I'm also using sp_BlitzFirst to capture data to tables for my own reporting.
Q: For Perfmon stats from sp_BlitzFirst, is there an option or parameter to capture additional perfmon stats? I'm thinking maybe stuff like:

Processor "% Processor time", "Queue Length"
maybe some Physical Disk values
also curious about Network Interface as we have a couple large queries coming from other servers.

sp_BlitzFirst version 7.9999 (20201114), SQL Server 2016.
Much Thanks!


texts: Why do you have to execute a full backup after a new database for trans backup jobs to run

texts: Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). Wednesday morning I received SQL agent email noticed stating the backup jobs failed specifically for the new DB. After reviewing the logs I read "The Job was invoked...There is no DB to backup," before the Senior DBA told me to do a manual full backup and then it will run. Why is this?


texts: We have some blocking going on in our database and when I run sp_who2, in the BlkBy field, I see "-2" for two of the rows.  What does this mean?  Running SQL Server 2008 R2


texts: Permission denied when creating a database

texts:
Currently, my local instance looks like this

I want to restore my user database
I have tried restoring my database my rightclicking on Databases and also tried right clicking and doing it on system databases but I get this error

I also have the same permission error when trying to create a new empty database.


texts: Upgrading the mysql server from 5.1 to 5.6?

texts:
My server have CentOS6.  Currently I'm  are using the Percona 5.1.47 version.  Would like to upgrade to 5.6.

What is the best way to do, and what are the prerequisites to keep in mind before doing that.

Added this server is master-master replication topology.


texts: MySQL 5.1.67 - Replication failure Mysql, Master Log Truncated or corrupted

texts:
I have run into some issues with replication

After an in-place upgrade of mySQL from 5.0.77 -> 5.1.67 rel 14.4 Percona

replication between a master and multiple slaves is causing issues. slaves are at the same level as master.

The replication mode is mixed (SBR and RBR)

The master seems to be truncating the log incorrectly, seemingly at random, and slaves subsequently stop replicating due to corrupt log being sent through.

mysqlbinlog also segfaults when trying to read the log.

This is the error being thrown by the slaves (table names/server names obfuscated):

  Last_Error: Could not execute Update_rows event on table ozdb1.tableA; Incorrect string >value: '\x8EIQ\x13)\x00...' for column 'Description1' at row 2, Error_code: 1366; >Incorrect string value: '\x8E\x08\x0F\x0F\x0F\x0F...' for column 'Description2' at row >1, Error_code: 1366; Incorrect string value: '\xBD\x06DINI...' for column 'Description3' >at row 1, Error_code: 1366; Corrupted replication event was detected, Error_code: 1610; >handler error HA_ERR_CORRUPT_EVENT; the event's master log apdb041-bin.005603, >end_log_pos 1070537659

currently the only way to recover is via full refresh of the slaves.

Would there be an issue on the master server?
No disk / network / logging indicates a reason why the mysql master would incorrectly truncate the log.

What  could be causing the master to incorrectly truncate or not close off the log properly?

I'd be happy to hear about any extra detail which might be required to help answer this question!


texts:
What's you opinion about Update a primary Key in SQL?

I have a "link" table with 3 key from 3 other tables. Should the administration let people to edit one of the keys?

I usely said no, dont change PK. Add a new line and if you want remove the other line.

sample:

Table A : idA Int
Table B : idB Int
Table C : idC Int

Table Link : PK { idA, idB, idC }

UPDATE [Link] SET idA=@NewIdA


texts: How to update a yes/no field to yes when all related fields in another table are marked yes

texts:
I'm new to database stuff (read: database idiot) and I am using Access. I have a table (let's call it "projects") that has a list of projects and one the fields (let's call it "actions_complete") for each of the projects is basically "are all the actions related to this project complete?" There is another table (let's call it "actions") that has broken down all the actions related to each of the projects and a field (let's call it "complete") that documents whether or not that specific action is complete.

What I want is for "actions_complete" in "projects" to change to a yes value once all of the "complete" values for a corresponding project are marked as done.

I took a database class in college but I retained very little information from then because I had never worked with databases and then I went and taught English for two years (where I forgot everything IT-related about my degree) on the other side of the world and now I'm working in a place where some basic database administration skills would be very helpful. I'm super grateful to the community here at StackExchange and I'll be very happy if anyone is willing to explain to me how I can get this done or if it's even possible. I'm sure it's much easier than I'm imagining.


texts: SQL Server Replication :: Dedicated Distributor Hardware Requirements

texts:
Does anyone know any rules of thumb (or better) with regards to hardware requirements for a dedicated distributor server?  i.e. Based on estimate metrics (e.g. number of subscribers, publishers, tables, average table size, average update frequency, etc) is there some way to calculate a minimum RAM/CPU requirement for the distributor?

More Info

We'll likely be using SQL Server 2012 for the distributor, though the publishers and subscribers are currently a mix of 2008 and 2008 R2.

The distributor will be clustered to ensure high availability.  We'll go for active/active to make optimal use of resources, sending half the publishers to one and half to the other, but will ensure that each node in the cluster's spec satisfies the minimum spec so that should one node go down the solution can still cope.

Background Info

We've been using replication to allow our international BI system to pull data from our transactional systems without impacting performance, with the BI servers hosting the distributor. Going forwards we're hoping to move this distribution onto a separate server, so that we can provide replication as a service to the BI team and to other teams requiring this (i.e. local BI teams from various countries & our in house monitoring system).  We expect to see an increase in the number of tables being replicated as well as in the number of subscribers and publishers (as we'll be including our non-production systems also).

Thanks in advance.


texts:
We are working on a WiX project for deploying our large application (the server side of the application, that is.) The customer has the choice to have the application server, the SQL Server and the Reporting Server all on the same box, on 2 boxes or on 3 separate boxes (or VM's.)

Here is one of our current issues that we are working through. For the SSRS side, we have a main model that we need to deploy that is about 7mb. With the default install of SSRS, the max is 4mb. I know that we need to manually manipulate the two web.config files to add the 'maxRequestLength' string, but we don't really want to do this on their servers and that is assuming that this meets the clients security policies as well as that they aren't already setting that to some other value for another application. (We can't assume that they are using that SSRS instance only for our application.)

Our customers are local City and County governments.

Is there any way to accomplish deploying the model file manually, bypassing the max upload size limitations?

-WC


texts:
Is it possible with citus extension in PostgreSQL to create temp table that is copied to each worker node (like reference table)?

When I run SQL like this:

DROP TABLE IF EXISTS mypoint;
CREATE TEMP TABLE mypoint (mpoint geometry primary key);
SELECT create_reference_table('mypoint');

I get the error:

  ERROR:  unacceptable schema name "pg_temp_6" DETAIL:  The prefix "pg_"
  is reserved for system schemas. CONTEXT:  while executing command on
  mynode01:5432 SQL state: 42939

The reason I am asking this is because there are more SQL commands afterwards where I need to do a join between the temp table and a distributed table (which is not allowed with citus extension).


texts: How to trigger an event when row column crosses now()?

texts:
Is it somehow possible in postgres 12 to trigger an event when a table with an tsrange column crosses now(), or someone insert something that where the tsrange is prior to now()?
Triggers, as far i know, only triggers when someone insert or updates a row.
Triggering when someone insert a row which has passed now, seem to doable
With trigger that triggers on all inserts, but i am not sure how to trigger an event, when a given timestamp in that row crosses now..
And can both trigger scenarios be combined into one?


texts: Oracle procedure sometimes runs endlessy, other times finishes instantly

texts:
The procedure always compiles instantly and I'm pretty sure I don't have any infinite loops or some other bug in there. When running the procedure in SQL Developer it sometimes finishes instantly (as it should, the tables only have like 5 rows each) and other times it never stops running. Does it have to do with the DB or my code? Sorry if the code is messy, this is my first PLSQL procedure. I'm running the procedure at the Oracle DB my university provides so I don't have admin rights.
CREATE OR REPLACE PROCEDURE integrate_data IS
    rowCount NUMBER;
    currId NUMBER;
    CURSOR ws1_customer_crsr IS SELECT * FROM ws1_customer;
    CURSOR ws1_product_crsr IS SELECT * FROM ws1_product;
    CURSOR ws1_order_crsr IS SELECT * FROM ws1_order o JOIN ws1_order_position p ON o.no = p.order_;
    CURSOR ws2_customer_crsr IS SELECT * FROM ws2_user u JOIN ws2_address a ON u.id = a.user_;
    CURSOR ws2_product_crsr IS SELECT * FROM ws2_product;
    CURSOR ws2_order_crsr IS SELECT * FROM ws2_purchase p JOIN ws2_entry e ON p.code = e.purchase;
BEGIN
    SAVEPOINT beforeProcedure;
    DELETE FROM ts_transaction;
    DELETE FROM ts_customer;
    DELETE FROM ts_product;
    /* Insert customer data from ws1 */
    FOR ws1_customer_rec IN ws1_customer_crsr
    LOOP
        INSERT INTO ts_customer VALUES('ws1_' || to_char(ws1_customer_rec.id), ws1_customer_rec.email, ws1_customer_rec.address);
    END LOOP;
    /* Insert product data from ws1 */
    FOR ws1_product_rec IN ws1_product_crsr
    LOOP
        INSERT INTO ts_product VALUES('ws1_' || to_char(ws1_product_rec.id), ws1_product_rec.name,
            ws1_product_rec.description, ws1_product_rec.price);
    END LOOP;
    /* Insert order data from ws1 */
    FOR ws1_order_rec IN ws1_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws1_' || to_char(ws1_order_rec.no) || '_' || to_char(ws1_order_rec.pos_no), ws1_order_rec.date_,
            ws1_order_rec.quantity, 'ws1_' || to_char(ws1_order_rec.customer), 'ws1_' || to_char(ws1_order_rec.product));
    END LOOP;
    /* Insert customer data from ws2, if a customer has multiple addresses only the first one gets added */
    FOR ws2_user_rec IN ws2_customer_crsr
    LOOP
        currId := ws2_user_rec.user_;
        SELECT CASE
            WHEN EXISTS(SELECT * FROM ts_customer c WHERE c.id = 'ws2_' || currId) THEN 1
            ELSE 0
        END
        INTO rowCount
        FROM DUAL;
        IF rowCount = 0 THEN
            INSERT INTO ts_customer VALUES('ws2_' || to_char(ws2_user_rec.user_), ws2_user_rec.email, ws2_user_rec.street || ' ' || to_char(ws2_user_rec.street_no) ||
                ', ' || to_char(ws2_user_rec.postal_code) || ' ' || ws2_user_rec.city);
        END IF;
    END LOOP;
    /* Insert product data from ws2 */
    FOR ws2_product_rec IN ws2_product_crsr
    LOOP
        INSERT INTO ts_product VALUES('ws2_' || to_char(ws2_product_rec.ean), ws2_product_rec.title, ws2_product_rec.description, ws2_product_rec.sales_price);
    END LOOP;
    /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/


texts: psql: how to turn off context messages at raise notice

texts:
If I use raise notice, I get context messages at each execution.  Can I turn them
off and just get the NOTICE?

NOTICE:  d = [2,1] ,tn: (1,2,"{NULL,NULL}","{{-1,4},{-1,-1}}",1)
CONTEXT:  SQL statement "SELECT aPt( vl , tn_id, color )  from tnodes where tnode_id = tn_id"
PL/pgSQL function "apt" line 42 at PERFORM
SQL statement "select  aPt(vl, tn.chldrn[d[1]][d[2]], color) from tnodes where tnode_id = tn_id"
PL/pgSQL function "apt" line 46 at SQL statement
SQL statement "SELECT aPt('{0.5,1}',itVar,1)"
PL/pgSQL function "bi" line 16 at PERFORM

I'm using postgres 9.1.14,
Thanks.


texts: EAV-like table with both primary column pointing to the same foreign column

texts:
Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features and designs.

I need to create a table with the results of matches between two users, i.e.:

table_A: user_id (primary int), other stuff
table_B: user1 (primary foreign key pointing to table_A user_id), user2 (primary foreign key pointing to table_A user_id), other stuff

Can I enforce user2<user1? This would be in order to prevent rows from having user1==user2, and to prevent the creation of rows were the couple would be inverted, i.e. I need not to have both (alice,bob) and (bob,alice).

-OR- I'm doing it all wrong in the first place and there's another, completely different, approach to do that?

Obviously I can (and will) check the values before adding them, but as far as I understand, if I can add a constraint in SQL, I would better add it, just to be sure it's consistent.

Database currently is sqlite, but in case this can't be done in sqlite but can be done with other engines (especially if InnoDB), it could be a useful answer too.

PS: I'm not sure if this still counts as EAV or not, since AFAIK EAV implies two primaries from different tables, and a single extra column while instead I'll have more than one.


texts: MariaDB 5.5.47 but cannot Install TokuDB

texts:
This is on CentOS 7.1,
sudo yum install MariaDB-tokudb-engine says there's no such package and if I try to start the server with plugin-load=ha_tokudb in my.cnf I get

[ERROR] Can't open shared library '/usr/lib64/mysql/plugin/ha_tokudb.so' (errno: 0, cannot open shared object file: No such file or directory)
[ERROR] Couldn't load plugins from 'ha_tokudb.so'.

in the error log (but the server starts OK)

It wasn't me who installed this MariaDB and I'm trying to enable/install TokuDB as it's best suited for the workload imo (or at least I have to try)

How do I install/enable TokuDB on this server? How to get that yum package installed for example if that's required in my case


texts: Local SSMS connected to VPN SQL Instance - update fails when VPN disconnects

texts:
I connect to a VPN.

I open SSMS from my local computer.

I connect my local computer SSMS to the SQL instance on the VPN

I execute a long running SQL Update from my local computer, and during this a VPN error occurs.

All progress now with the SQL Update is lost.

Is there a way to use local computer to connect to remote SQL instance, and get the SQL Update to finish even though I get disconnected.


texts:
Imagine 2 tables :

CREATE TABLE a (a_id serial primary key, a_text text);
CREATE TABLE b (
    b_id serial primary key,
    b_aid int not null REFERENCES a ON DELETE CASCADE);

If I delete from b

 DELETE FROM b WHERE b_id=3

I though that the record in a was going to delete too. But in fact it's not. I though I understand this feature until now... Is there a way to achieve this without trigger ?

I didn't find anything about this elsewhere but maybe I didn't search well. So feel free to mark as duplicate if this question have already been answered.

EDIT: Some ask some more details

I have 26 tables (let's call it a,b,c,d,...,z) that reference to a table named log (this table is going to be partitioned by table name soon). ex :

CREATE TABLE a (
  a_id uuid primary key default uuid_generate_v4(),
  a_somefield text
  a_someotherfield int
  a_log uuid not null REFERENCES log (log_id) default f_create_log(a::name)
);

CREATE TABLE log (
 log_id uuid primary key default uuid_generate_v4(),
 log_creation_date timestamptz not null default now(),
 log_creation_user text not null default user::text,
 log_update_date timestamptz
 log_update_user text,
 log_delete_date timestamptz,
 log_delete_user text,
 table_name text
);

The result that I would like to have is "When I delete a record in "a" it suppress the log record too". If you are wondering why I have a log_delete_* then, this because I accessed the data mostly from view and not the actual table (so when you delete a record in the view, there is a trigger that record the user and the time you suppress the record).

I hope this is more clear by now. I now that this design is not the best one, but it is the simplest I found when I started and I needed a really simple solution.


texts: Can I consolidate multiple MYSQL databases into one database via a script?

texts:
I'm using MYSQL 5.7.19 on an ubuntu server.
Say I have 3 databases (db_1,db_2,db_3) and I create an empty database called all_db

Is there a way to copy my databases to all_db where db_1,db_2,and db_3 fall under all_db with the format of all_db.db_1_tablename1,all_db.db_2_tablename1, etc?


texts: The opposite of ordering by a column in SQL

texts:
If you have a table and you'd like to sort by a column you can use order by foo to order the results by foo.

Is it possible to do the opposite, such that rows which have the same value for foo would be as far away from one another as possible?

I'm using Postgres 9.5 and would appreciate an answer that would be compatible with Postgres.


texts: How to specify the domain of an attribute in an entity-relationship diagram (ERD)?

texts: In an ERD (as in, rectangles for entity types, diamonds for relationship types, ovals for attributes, etc.) how would one specify that an attribute is, say, a 5-letter word?


texts: Multiple JOIN of 6 tables via 5 database instances

texts:
I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the roles of a user which he has of all instances.

I already used UNION ALL, but the output was unstructured and you couldn't tell which role on which instance. So I tried the following for only 3 tables:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')
WHERE U.USERNAME = 'USER'
ORDER BY U.USERNAME ASC;

It actually worked, but the output wasn't satisfying:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1
ROLE_2
ROLE_3
ROLE_4
                            ROLE_1
                            ROLE_2
                            ROLE_4
                            ROLE_5
                            ROLE_6

Is there any way to make an output like this:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1                      ROLE_1
ROLE_2                      ROLE_2
ROLE_3
ROLE_4                      ROLE_4
                            ROLE_5
                            ROLE_6

I tried ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2') but the output was even worse.

You guys have any suggestions or helpful thoughts?


texts: How to allow to execute stored procedures but not writes?

texts:
I would like to set up a "reading" user on my SQL Server 2012 instance. I would like to allow him the right to execute any stored procedure, or function, or direct SQL statement, which retrieves data from all tables and views, but not to update or to insert (i.e. read anything and write nothing).

Can I set this up without specifically giving rights to each function or stored procedure by name, but rather grant him execution right on any function or stored procedure, just taking away the right to modify tables?

Will anything change if I run SQL Server 2008 instead?

Clarifications and additions:

If a stored procedure changes data, the user should receive an error message (either denying the modification or refusing access to the stored procedure completely).
If a potential solution involves denying permissions, can I just not grant certain permissions instead of denying?
Can a deny be applied on all tables, views etc. (existing now and in the future) in the database in one statement?


texts: Compatibility levels and reported engine versions

texts:
I'm seeking a bit of clarification around SQL Azure versions and upgrades.
Upon trying to use some specific functionality it came to my attention that the compatibility level of the database was 100 (I needed 130). Upon looking a bit further I see that the engine version is reported as 12.0.2000.8 (equivalent to SQL Server 2014), but I need to be at a minimum engine version of 13.x in order to use compatibility level 130.
What do I need to do to upgrade the engine version for this SQL Azure instance? The article Hot patching SQL Server Engine in Azure SQL Database specifically states:

Azure SQL Database is evergreen, meaning that it always has the latest version of the SQL Engine

Additionally, answers like this one imply that the engine will always be v.latest but the versions reported will be incorrect.
Does this mean that I can ignore the reported engine version and go ahead with setting the compatibility level, or are there other steps I need to take to upgrade the engine?


texts: What should I ask my DBA in order to diagnose a slow application?

texts:
I am working in the IT department of an industrial firm. The IT department is a handful of people, mainly project managers, and everything else is outsourced.

I have been managing a project, where a third party application has been bought off the shelf, and deployed by the vendor, but to be maintained on our own.

It consists of a central application server, connected to an SQL Server database, and a dozen heavy clients, all on our premises, on the same VLAN.

Most of the time, it runs fine, however, sometimes, when accessing records from the DB, the application is awfully slow.

I have been asking our DBA (outsourced) what is wrong, but have been responded by something along the lines of "Please ask me to do something with the DB, and I'll gracefully do it".

Hence the question, in a situation of sporadic sluggishness like this, which technical actions should I ask of my DBA to make progress on the case?

Please monitor the performance of the DB is apparently too vague, and they expect something along the lines of 'Enable this performance counter for that long' and so on...


texts: Updating Always On Availability Group server connected to Windows SQL Cluster

texts:
I'm working with a unique configuration.  Our organization prefers Windows Failover Clusters for our main database servers.  In this case my primary replica is a WFSC.  This is a reporting server where the data is extracted to each night.  We have a large database that we duplicate to another server we call shadow.  The shadow server supports the secondary replica.

I don't have a lot of experience adding updates/service packs to this configuration because of the business requirements to keep it running.  That said, I'm wondering if anyone has experience with updating WFSC & Always On availability groups and can give me some pointers.  I'm concerned with how the AG will react to different configurations when we roll the WFSC from one node to the other.


texts: SQL Server 2016 in-place upgrade time for large databases

texts:
Is there any statistics on how long it will take to in place upgrade SQL Server 2014 to SQL Server 2016 on a server with 1000+ databases and 10TB of storage?
How long it will take to install subsequent 2016 service packs?
Is there a way to optimize those installation times by configuration?

The goal is to minimize maintenance windows

Update: Let me clarify a question. All production updates will be tested in QA environment, the question is not about it all.
I'm asking about known to you options to optimize upgrade duration. I'll search for those options myself by measuring upgrade time in my environment, but if there is a "previous art" - please share.


texts:
I want to create a role named cp with some defined privileges, then we will create some other roles which will be granted with cp role. I know Oracle can do this job. For examle grant resources to user_name; which means grant resources role to a user. I do the follwing test in PostgreSQL, but it does not work. Any body know this?

--create role cp and grant privilege
postgres=# create role cp login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp';
CREATE ROLE

postgres=# grant connect on database skytf to cp;
GRANT

postgres=# \c skytf skytf;
You are now connected to database "skytf" as user "skytf".

skytf=> grant usage on schema skytf to cp;
GRANT

skytf=> grant  select on skytf.test_1 to cp;
GRANT

--create role cp_1, and grant cp role privilege to cp_1
skytf=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

postgres=# create role cp_1 login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp_1';
CREATE ROLE

skytf=# grant cp to cp_1;
GRANT ROLE

--test cp_1
skytf=# \c skytf cp_1;
You are now connected to database "skytf" as user "cp_1".

skytf=> select * from skytf.test_1 limit 1;
ERROR:  permission denied for schema skytf
LINE 1: select * from skytf.test_1 limit 1;


texts: How to load a .dmp into PostgreSQL using pgadminIII

texts:
I have a dump file from oracle that I wish to load into pgSQL database.
I can use psycopg2 or pgAdminIII to accomplish this. Any tutorials or references would be much appreciated as I cant seem to find anything. New to databases here.
Thanks


texts:
I have a mapping table which defines access.

   Us | Them
    ----------
    1    MA
    2    LA
    3    NA
    4    MA

Customers send in their role and we return our role which represents access inside our system.
A simple example is

MA = Master access
LA = Limited access
NA = No access
SA = Some access

We now will be receiving a list of roles from one of our customers,
this will be in the form of "MA;LA;NA" or "LA;NA" or even "LA;XX"

What I need to do is return the highest role back to them.
SO if it exists in our table a users role becomes the highest possible access.   Input is "LA;MA" I return 1.
Input is "NA;LA" I return 2.
Input is "does not exist" I return 3.

Again this is a simple example they have of 60 roles defined.
My question is what is a clean way of doing this?

I have a working solution which has a series of IF's and ELSE IF's where if the inputted string is like "MA" it represents a 1 and move on. However the code is rather verbose and pretty ugly, order matters and if the customer requests a new role say "Orange" and we insert it into the mapping table, I then would have to edit the stored procedure.

Any thoughts to point me in the right direction?


texts: MariaDB 10.2 custom install using mysql_install_db.exe

texts:
In the past I used to setup MySQL/MariaDB manually on Windows boxes but don't remember exactly how. I unzip the .msi file and put every binary file into the desired place. I write a custom my.ini file including required InnoDB settings like autoextend but also log directory. Now I want to generate the mysql defaults database. According to the documentation it's not possible to provide the mysql_install_db.exe tool with a path to the my.ini file.
All files are generated into a single folder including a my.ini file I don't need.

How do I generate the database manually into my individual folder hierarchy?


texts: How do I know what my username/password is for SQL PLUS?

texts:
I am trying to set up an Oracle SOA Suite environment with docker containers according to this website -> https://svgonugu.com/2018/03/12/docker-containers-for-oracle-soa-suite/

I got to a point where I have to connect to a database with this command: sqlplus sys/fusion@//172.17.0.1:1521/soadb as sysdba

But it gives me the following error:

ERROR:
ORA-01017: invalid username/password; logon denied

So what am I doing wrong here? I have not given a username or password during the whole process.


texts: How can I speed up an sql query that round trips between two tables

texts:
These tables are not that big, but the time taken to process this query is long.

What can I do with the following queries to speed up the process taken to return results?

$msc=microtime(true);

$chat_alg = mysqli_query($con, "
    SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY && sid != ''
    GROUP BY sid
    ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));

while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
    $chart_songs[] = $row_chat_alg['sid'];
}

$imploded_songs = implode($chart_songs, ',');

$projects = $dbh->query("
       SELECT * FROM music
       WHERE perms != 'c' && sid IN($imploded_songs)
       ORDER BY FIELD(sid,$imploded_songs)
       limit 50
");

$msc=microtime(true)-$msc;
echo ($msc*1000).' milliseconds';

...
Time taken: 9.5310001373291 milliseconds

This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.


texts: What is the correct SQL for using where ID in List and the items are with AND operator

texts:
I need a SQL Statement to use IN operator within the items with "And" connected.

SELECT * from tbl_Users
WHERE id in [1,2,3]

is equivalent to:

SELECT * from tbl_Users
WHERE (id=1) or (id = 2) or (id = 3)

What I need is to generate using IN operator (or equivalent):

SELECT * from tbl_Users
WHERE (id=1) AND (id = 2) AND (id = 3)

Because I collect all the IDs (ex: [1,2,3,4,5]) in one string and search with it in the DB.

More Explanation:

ID is always a primary key

The case is I have a table which has all countries, and each country has several electrical standards in separate table.

Example:
Germany requires always "standard1" and "standard2"
USA required always "Standard1" and "standard3"
France required always "Standard2"

If the user in C# application search for a country which has "Standard2", the system should give him Germany and France.

BUT If the user wants countries with "Standard1" AND "standard2", the system should only give him Germany.  -> because of "AND"

My Query was

Select  * from [tables and joins]
where STANDARD_id IN [1,2]

[1,2] is a concatenated list which the user already choose from a list of standards.


texts:
This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things:

select <linked server>.@@SERVERNAME;
select <linked server>.SERVERPROPERTY('ServerName');

... but no joy. Any ideas?

This is SQL 2008 R2 & 2014 (2008R2 is the linked server)

EDIT: Errors are:

  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near
  '@@SERVERNAME'.


texts: Firebird 2.5. Windows 8.1. Can't connect to any database! Why?

texts:
I'm trying to use a FireBird database engine.

OS: Windows 8.1 x64(SmartScreen enabled, UAC enabled, Custom AntiVirus installed(with firewall))

FireBird: Version 2.5, Installed as SuperServer, as application without guardian.

I'm trying to manage this engine using many applications, such as FlameRobin or FireBird Maestro.

At the first:
After installation db said, that the "firebird.msg" not found in windows directory, when I tried retrieve server version.

Ok. I'm copied it to system directory from program files.

At the second(fatal):
I cant connect to my databases!

Firebird Maestro said, when I'm trying connect to created database:

Or FlameRobin(choosed context menu "Retrieve server version" for "localhost"):

I'm nothing changed. Never installed firebird database.
Default user(SYSDBA) and password(masterkey) is used, tried in any сase sensitivity.

Today, I'm tried install FireBird on Windows Server 2003, and do some in FlameRobin, and all is works!

Whay the DB not works under Windows 8.1?


texts: Is xp_create_subdir supported on Linux SQL Server

texts:
Some system extended procedures work on 2017 RC2

e.g.

exec xp_dirtree @directory = '/var', @depth = 1

Correctly returns the directories contained in /var

However:

exec xp_create_subdir '/foo'

Returns

Msg 22048, Level 15, State 0, Line 18
Error executing extended stored procedure: Invalid Parameter

Is the procedure supported, and if so what is the syntax for the parameter(s)?

Tested running on Ubuntu, inside Docker container on MacOS.

select @@VERSION
Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64)   Jul 27 2017 08:53:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)


texts:
Sorry if this question has already been asked, but I didn't find it performing a search.

I have 2 tables (table_a and table_b), linked by a many/many relation.
The relation can be of two types, type_a and type_b.

I was wondering what was better between:

Setting a type column in the relation table and during queries filter on this field
Creating 2 relations table, one for each type and apply no filter during queries

I can see advantage in both solutions, but I can't tell which one is generally used to solve this kind of problem


texts: Dealing with single quotes in a dynamic query

texts:
I'm building a query as a string in my application layer to be executed over jdbc.

The string is constructed as follows:

"SELECT COUNT(DISTINCT ROOT_ID) as rows1 FROM DANIEL.UNIQUE_PHYSICIAN "
                + "WHERE "
                + "UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN' AND "
                + "UNIQUE_PHYSICIAN.FIRST_NAME = '"
                + firstP.getFirstName()
                + "' AND "
                + "UNIQUE_PHYSICIAN.LAST_NAME = '"
                + firstP.getLastName()
                + "' AND "
                + "UNIQUE_PHYSICIAN.POSTAL_CODE = '"
                + firstP.getPostalCode() + "'";

This works fine, until we hit a doctor in our database containing a single quote, such as "JEANNE-D'ARC" as her first name. It turns the query into the following:

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D'ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

How can we resolve this?

EDIT: as I understand it, I should be using parameters as something. This query is going into a program and we can't necessarily control what doctor's names come in, so I guess this is a SQL injection vulnerability, right?


texts: How to design a graph database in this scenario?

texts:
here is my scenario. I have a pre-defined data type structure for books. Just take it as an example for the sake of simplicity. The structure looks like the image below. It's a Labeled Property Graph and the information is self-explained. This data type structure is fixed, I cannot change it. I just use it.

When there is 1 book, let's call it Harry Potter, in the system, it might look like below:

So, the book has its own property (ID, Name,...) and also contains a field type MandatoryData. By looking at this graph, we can know all information about the book.

The problem happens when I have 2 books in the system, which looks like this:

In this case, there is another book called Graph DB, with those information as highlighted.

The problem of this design is: we don't know which information belong to which book. For example, we cannot distinguish the publishedYear anymore.

My question is: how to solve or avoid this problem? Should I create 1 MandatoryData for each book? Could you propose me any design?

I'm using Neo4j and Cypher. Thank you for your help!


texts: SQL Server 2012 Transactional Replication - twin subscriptions on same machine

texts:
Server A publishes Pub1, Server B subscribes to Pub1.

Server A and Server B are both SQL Server 2012 Standard Edition, default instances.

When I use Replication Monitor to drop-in a Tracer Token, one publisher-subscriber shows-up passing the token as expected.

When I run sp_helpsubscription 'Pub1' on Server A, one subscription shows-up as expected.

When I expand Replication->Local Subscriptions in SSMS on Server B, two identical subscriptions show-up.

What's going on?  Which subscription should I delete at Server B?


texts: For a software application, does every workstation need database drivers?

texts:
really a just a newb question. In the case of something like an enterprise system for a company, where end-users are executing continuous reads and writes to a database, it does not appear that in most cases each workstation needs the database being used's drivers. Credentials are being passed through the software, to the databases IP and port, and I am supposing that when a new user is added to the software, it is adding them to the database and their read / write privileges are assigned at that point. The user provides their login information to the software which serves as their credentials to make read / writes to the database.

However, if I want to access the database from third-party software, Excel or what not, I then do need the drivers for the database. I guess I am not sure why this is and was wondering if someone could explain it to me.

An example, a company I used to work for used IBM DB2. We had an enterprise system around it with DB2 as the database. Using the software surrounding it, I don't recall ever needing the drivers for it. When we started writing custom reports for it in Excel, then I needed the ODBC drivers as did any other workstation that needed the Excel -> DB2 connection.


texts: Delayed transactional replication setup?

texts:
I will need to setup transactional replication for a big database. I use backup/restore to initialize the database because it's too big to use snapshot over slow network. So the following steps are used

Backup the database
Copy the the backup files
Restore the database on subscriber
Setup replication

However, What if there are some data change (insert/update/delete) between after step 1 and before step 4? Will these transactions lost? Or the replication setup will fail? How to resolve the problem?


texts: Raising an error with a system message_id in SQLCLR

texts:
If call CREATE DATABASE foo twice I get the error message:

Msg 1801, Level 16, State 3, Line 1
Database 'dropme' already exists. Choose a different database name.

The error message is listed in sys.messages:

SELECT * FROM sys.messages WHERE message_id = 1801 AND language_id = 1033

I have a SQLCLR procedure that basically does a CREATE DATABASE FOR ATTACH. It knows the name of the created database at the beginning, but does a lot of work before attaching it. Therefore, I'd like to make sure the database doesn't exist before proceeding and give that error if it does.

If I try to use RAISERROR(1801, 16, 1) on that message it will not let me:

Msg 2732, Level 16, State 1, Line 1
Error number 1801 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.

Do I have any option other than raising the error and having the msg_id being 50000?


texts: Dropping indexes doesn't seem to release space to the OS. Can it?

texts:
I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g:

evlampts=# SELECT
  pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS relation_size,
  pg_size_pretty(pg_total_relation_size('mcdata_2011_07')) AS total_relation_size,
  pg_size_pretty(pg_indexes_size('mcdata_2011_07')) AS indexes_size;

 relation_size | total_relation_size | indexes_size
---------------+---------------------+--------------
 43 GB         | 100 GB              | 57 GB
(1 row)

I can then see that, while a good chunk of this is the primary key, I have 11 GB in one index and 16 in another:

evlampts=# SELECT
  pg_size_pretty(pg_relation_size('mcdata_2011_07_timestamp_idx')) AS timestamp_idx_size,
  pg_size_pretty(pg_relation_size('mcdata_2011_07_host_idx')) AS host_idx_size;

 timestamp_idx_size | host_idx_size
--------------------+---------------
 11 GB              | 16 GB
(1 row)

If I then proceed to drop these indexes, I see no dramatic improvement in disk space in the OS:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

$ psql evlampts
evlampts=# drop index mcdata_2011_07_host_idx;
DROP INDEX
evlampts=# \q

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

If the space was released to the OS, I'd expect there to be another 16 GB available. Is there a VACUUM step I'm missing? I have tuned autovacuum to run less frequently and may have gone too far, but I don't see anything in the documentation saying a VACUUM is necessary to free index space. What am I not doing I should be doing?


texts: MySQL: database performance_schema does not exist when running from RAM disk

texts:
I’m running MySQL Community 5.7.13, started manually from a ramdisk with the following command:

sudo mysqld --port=3307 --socket="/var/run/mysqld/mysqld-testing.sock" --user mysql --pid-file="/var/run/mysqld/mysqld-testing.pid" --datadir="/tmp/mysqlramdisk" --skip-grant-tables &

This worked fine when using MySQL 5.6, but for 5.7, some things seem broken. Most notably, the database performance_schema does not exist, leading to stuff like:

mysql> SHOW VARIABLES;
ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist

$ mysql_upgrade -S /var/run/mysqld/mysqld-testing.sock -u root
Error occurred: Cannot select database.

The folder /tmp/mysqlramdisk is emptied before every start, so there is no old databases (from 5.6) lying around.


texts:
I have a database with 4500GB storage on AWS RDS. My "users" tablespace has 3749700 MB allocated. Out of which 1583777.63 MB is used and 2165922.38 MB is free.

When I do:

alter tablespace users resize 3500G;

It gives me:

Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.

I have tried this - http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html#Appendix.Oracle.CommonDBATasks.CreatingTablespacesAndDatafiles

How can I fix my tablespace?


texts: UPDATE not working properly with linked server

texts:
Please see a question I asked here earlier: https://stackoverflow.com/questions/24324823/update-not-working-properly-with-linked-server

Dommer suggested using OPENQUERY and this does appear to resolve the problem. Does anyone know why OPENQUERY resolves the problem? I have read the documentation for OPENQUERY here: http://msdn.microsoft.com/en-gb/library/ms188427.aspx, but I have not found an answer to my specific question.


texts: Multiple Availability Groups on one instance

texts:
I want to add second availability groups to Always on group and I need to change the port to 5023 but it is read-only as shown below:

The first availability group has an endpoint which is listening on port 5022. how can I change the port for second and third availability group?


texts: Searching for array elements nested in MongoDB Documents

texts:
I need to store card documents in my database. Cards need to be stored with a set of integers that represent the bundle, box and pallet, or whatever other entity they belong to. The packaging structure needs to be of an arbitrary length to accommodate flexible packing structures (otherwise they would each have their own field in the document.)

I am considering a document structure like this, where the packaging numbers are kept as an array in the Packaging field.

{
"JobNumber" : "50001-01",
"CustomerId" : "joe",
"IdentifierNumber" : NumberLong(8812739),
"TimesPrinted" : 0,
"Packaging" : [
    1200,
    120,
    3
]
}

The Packaging field will be set once, then searched against many times.

I am wondering about performance because I will often need to query this collection for all documents whose nth element in the Packaging field is a given value. Is this something that MongoDB is good at? What are the indexes I should be considering?


texts: TokuDB table stats row count decreases very fast while adding/updateing data

texts:
We run an Percona server with the newest version(5.7.19-17).
After a while the cardinality of multiple tables drop to zero.
A new created index also have an cardinality of 0 if the existing are zero.
I can repair this by do

set session tokudb_analyze_mode = TOKUDB_ANALYZE_RECOUNT_ROWS;
ANALYZE table myTable;

or

ALTER TABLE `table_name` ENGINE=TokuDB;

After some tests I found the row count droping over time.
As far as I understand the, this influences also the cardinality.
The table contains around 800.000 entries and is written nearly the whole day.

CREATE TABLE `table_name` (
`val1` char(2) COLLATE utf8_bin NOT NULL,
`val2` char(15) COLLATE utf8_bin NOT NULL,
`val3` char(15) COLLATE utf8_bin NOT NULL,
`val4` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`val1`,`val2`),
KEY `reverse_key` (`val1`,`val3`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=TOKUDB_LZMA;

Mostly I write with REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE into this table and update nearly 100% of the data per day. Both insert methods are tested with the same behavior.

I start an simple script which just print the calculated row count over time

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'shema_name' AND TABLE_NAME = 'table_name';

You can see the row count is decreasing very fast.

Can anyone explain to me where this behavior comes from and I can handle it?


texts:
This is driving me crazy.

When I try to create a few tables from my Workbench model I get this error 1044.

I've been trying to find a solution but nothing works for me.

Curiously when I run SELECT USER(),CURRENT_USER(); I get two versions of the same user.  One is techboy@(an ip address) and the other is techboy@%.

Access is denied to both at one point or another.

The MySql server is a remote hosted server with the user permissions correctly set.


texts:
I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.

I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.

However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.

I tried using SET SESSION AUTHORIZATION, but the docs say

  The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.

And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.

The PostreSQL GRANT docs say

  [A role] may grant or revoke membership in itself from a database session where the session user matches the role.

I take that to suggest that I should be able to do something like

-- from a connection to the "service" role

SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role

-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges

But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.

Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?

Or do I just have to be prepared to manage a whole bunch of username + password pairs?

Or is there some other way to accomplish what I'm trying?


texts: MongoDB: All commands spit out "not authorized on admin to execute command"

texts:
Mongo version: 3.2.11

I spun up a mongoDB droplet with DigitalOcean so that I could have a database connected to my little game. However, complications after complications have arisen.
I am connecting to the droplet through PuTTY and was previously able to invoke "mongo" then type "show dbs" to see all the current databases.

I had been altering the /etc/mongod.conf file to allow remote connecting, and since then - any command that I invoke spits back an error message:

Just through a simple show dbs - I get this.

> show dbs
2016-12-28T00:12:26.655+0000 E QUERY    [thread1] Error: listDatabases failed:{
        "ok" : 0,
        "errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
        "code" : 13
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
shellHelper.show@src/mongo/shell/utils.js:761:19
shellHelper@src/mongo/shell/utils.js:651:15
@(shellhelp2):1:1

I'm so confused as to why this is and I've been scouring the internet to find anything that could possibly resolve this.

PS: I had altered the /etc/mongod.conf file by uncommenting security and adding authorization: 'enabled' to it, as well as commenting out bindIp: 127.0.0.1 under net.


texts: Do hstore and jsonb have the same plumbing internally?

texts: Is hstore internally implemented identically as jsonb, just with a constraint that the data must be flat? If not, when/why should hstore be used instead of jsonb?


texts: MS SQL server 2016 backwards compatibility

texts: Are there any reported compatibility issues between level 130 to 100?


texts: Natural join output doesn't appear in table

texts:
I used this query:

select *
into airbnb_parcels
from pcpao_res
natural join AIRBNB_PARCELS;

and got:

  Error: relation "airbnb_parcels" already exits.

In DBeaver I used the following query:

SELECT * FROM pcpao_res
NATURAL JOIN airbnb_parcels;

But the resulting data only appears in the "output" window below the query panel, not the actual table. I've saved the project, committed the query, etc. but it won't update the table.

It's a large dataset so in the screenshots I opened up a filter on the same column in both the query window and the table window to show how values are present in the former but not the latter.

What am I doing wrong?


texts: How to force users to specify a filegroup when creating tables/indexes

texts:
I will soon be migrating to SQL Server 2017 and hence am working on beautifying some neglected aspects of our databases. One aspect is filegroups: my DBA predecessor has created multiple filegroups however as no one was really told about most objects just go to the default filegroup (PRIMARY).

Now I would like to enforce that people creating tables and indexes actually name the filegroup where their objects should reside. Meaning: A CREATE TABLE or CREATE INDEX without an "ON [Filegroup]" should roll back with an error that the filegroup is missing. Is there any way to do this an would you recommend proceeding with such an approach? I have just come across that policy based management does not work in preventing objects being created on the default filegroup.

Main point of doing this is that someboy should choose the filegroup for the objects in the first place instead of creating them in the default filegroup and afterwards have to move the objects based on rants by the DBA :-).

I thought about two techniques...however can't get started with either of them because of missing knowledge:

Use database triggers to check if there is an on clause and if not roll back --> this seems to be quite fragile as it would depend on text analysis of the command text
Make the primary filegroup really small in size (100KB?) in order for system tables still being able to fit in but other tables of moderate size would result in an error because there is not enough space left --> probably a bad idea as this could have severe side-effects on database availability and is not precise enough in preventing every user defined table or index to be created within the default filegroup

Thank you very much in advance for your help

Martin


texts: Modelling users and groups - but with multiple types of groups

texts:
I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them).

However, its seems I'm dealing with different kinds of groups: some that represent an organization (e.g. a company) and some that represent a role or differing permission levels within an organization (e.g. admin). The organizations represent tenants, or customers, in the business domain.

I currently see two possibilities: (a) create a separate table for organizations, or (b) add a boolean column to the group table called "is_organization" and use it to distinguish the two types.

Is there a best practice for modelling these type of system?  Thank you.


texts: Existing data to new table, how to set up the PK & Identity

texts: I am moving an Access app to SQL Server.  I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field.  The field is currently set as IDENTITY(1,1).  I know I can set the IDENTITY_INSERT for the insert period, but do I then need to adjust the IDENTITY start point so the next insert does not start at 1?  Example, the newest row in the existing data is id 4132, so do I insert the data then alter the column to IDENTITY (4133,1) ?


texts:
I'm new to postgres , I've aws rds instance running postgresql with engine version 11.5.
All my queries are clientRead has the wait_event.  Why do I have all my queries in idle status.Does this mean they are idle in transaction?
What are the steps should I take to fix this?
If I change to idle_in_transaction_session_timeout to 10 mins for example will it fix this?
select count(*),state FROM pg_stat_activity group by 2;
 count | state
-------+--------
     5 |
     1 | active
   451 | idle

Select pid, datname, usename, wait_event_type, wait_event, backend_type FROM pg_stat_activity where state='idle';
  pid  | datname  |         usename          | wait_event_type | wait_event |  backend_type
-------+----------+--------------------------+-----------------+------------+----------------
 14797 | xxxxx    | user                     | Client          | ClientRead | client backend

SELECT current_setting('idle_in_transaction_session_timeout');
 current_setting
-----------------
 1d
(1 row)


texts: Time interval is returning all records or no records

texts:
I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial
After running the query at 18:30 UTC, my query looks as follows:
RETURN QUERY
        SELECT
            h.heartbeat_time
            ,h.message
            ,h.rec_created_by_user_id
        FROM backup.heartbeat AS h
        WHERE h.heartbeat_time  >= NOW() - INTERVAL '30 MINUTE';

and the return:
heartbeat_time     |message
-------------------|----------------------------------------------
2019-12-19 17:46:31|some random aws event
2019-12-19 17:48:17|{¶
2019-12-19 17:48:47|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:09:23|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:10:11|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:10:48|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:11:14|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",

I would expect the anything older than 18:00 UTC to now show up, but 17:46-48 still shows up.
According to the provided link and many others, this is the correct syntax. Is there something I am missing?


texts: Using secondary id as foreign key to other table

texts:
So I have a Customer table that is linked to different tables. For instance, a Configuration table is linked to Customer table.
Instead of using Customer Id as foreign key to the Configuration table, I would like to introduce a secondary id called Reference. This Reference has a value similar to GUID and would like to use this as Foreign Key to the Configuration table. The reason behind this is because I don't want to use the Id of the Customer table. We use integer as a data type for this and I don't want to expose this.
What could be the possible complications on implementing this approach?


texts:
When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB.

Are they compressed in order to reduce disk space usage?

Is that 80MB real physical-storage size?


texts: How to finds users that can access a particular table?

texts:
How do I find all the users that can access one particular table?

For example table 'Table01'

Thanks


texts:
tl;dr: I know a few SQL semantics and I know how databases work (basically) but I never created one that had a considerable size. I lack of knowledge concerning workflow, SQL-code management and very basic SQL programming practices (write by hand or use e.g. pgAdminIII). I need some "instructions" on how to manage my source code and state of my database.

Okay, this sounds like a very basic question and in fact it is a very basic question.

Five years of computer science and I never really had to develop a database by myself and now I am here and have no idea how to do that. Now, as a software developer writing Java or C or whatever the workflow is pretty clear of course. Thinking about design, creating some files, write code, use version control and commit/push. Next day repeat.

My problem is that I can't get my head around that incremental nature of creating a database. What I mean is that I don't know how my SQL script should look like if I commit it to my repository and if I use e.g. something like FlywayDB.

At the beginning I'd have something like this e.g.

DROP TABLE IF EXISTS company_employee;
CREATE TABLE company_employee (
    id BIGSERIAL PRIMARY KEY
);

and I need that DROP TABLE IF EXISTS because while I am developing I might change that table all the time and therefore drop it each time before I recreate it. But do I want to commit that script?

I mean if somebody accidentally runs it on my production server it would drop all my tables and re-create them. So one question I'm asking is e.g. how do my scripts look like that I am actually committing to my e.g. git repository?

Do I have different versions of that file? One that is e.g. a "development"-version and another is a "I am confident it doesn't break anything"-version that I commit?

Another option could be that I e.g. just create the tables in the first step:

-- V1__create-company-tables.sql

CREATE TABLE company (
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE company_employee (
    id BIGSERIAL PRIMARY KEY
);

and in a second file I add the foreign keys:

-- V2__adding-company-tables-foreign-keys.sql

ALTER TABLE company_employee

    ADD COLUMN company_id BIGSERIAL,
    ADD CONSTRAINT fk_company_employee_company FOREIGN KEY (company_id) REFERENCES company(id),

But that would mean that I'll never have all in one file - just fragments of my database in different files V1 toVn.

Another thing I could do is to just use pgAdminIII and add everything I need, then reverse engineer my database, run it with FlywayDB and commit that generated file to my database. But I don't like that idea since pgAdminIII uses deprecated stuff like WITH ( OIDS=FALSE ).

So well, I would be glad if someone could bring me on track somehow and give me a scratch of how the actual development process of a database looks like.

Should I use pgAdminIII or write my whole database by hand?

How do I version my .sql files? Where do I store them on my local machine?

pgAdminIII doesn't even seem to have something like a "Database Project" - it just offers access to modify my database but there is no source code management. I am really confused about how my setup would/should/could look like.