texts: what could be preventing my transaction log autogrowth to be set to UNLIMITED?

texts:
I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same.

First thing I have checked if something is holding any locks to my database:

select radhe='kill ' + cast(DTL.request_session_id as varchar(3))
      ,DTL.request_session_id
      ,DTL.resource_type
      ,DTL.request_type
      ,DTL.request_status
      ,DEST.TEXT
      , SP.spid
      , SP.blocked
      , SP.status
      , SP.loginame
from sys.dm_tran_locks DTL
INNER JOIN sys.sysprocesses SP
       ON DTL.request_session_id = SP.spid
   --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]
   CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST
where 1=1
  and resource_type = 'database'
  and resource_database_id = db_id('junofinance')
  and request_type = 'LOCK'
  and request_status = 'GRANT'

but there was nothing holding any database locks.
so I went ahead and ran the following:

use master
ALTER DATABASE [JUNOFinance]
MODIFY FILE ( NAME = N'JUNOFinance_log', MAXSIZE = UNLIMITED)
GO

when I check about the log reuse I get the following:

select log_reuse_wait_desc, * from sys.databases
where name = 'junofinance'

but that is not the problem, as after I few times trying these scripts I got:

then when I check the log maxsize I still get it limited:

exec sp_helpdb 'junofinance'

I have even tried some silly things like disabling the autogrowth and setting it to UNLIMITED

USE [master]
GO

GO
ALTER DATABASE [JUNOFinance] MODIFY FILE ( NAME = N'JUNOFinance_log', MAXSIZE = UNLIMITED, FILEGROWTH = 0)
GO

then it worked fine, go figure!

but then when I re-enabled it, it went back to the limitation:

USE [master]
GO

GO
ALTER DATABASE [JUNOFinance] MODIFY FILE ( NAME = N'JUNOFinance_log', FILEGROWTH = 10240KB )
GO

On this server in particular, I cannot upgrade it, so we are on:

  Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64)
  May 16 2019 02:24:21   Copyright (c) Microsoft Corporation  Enterprise
  Edition:  Core-based Licensing (64-bit) on Windows Server 2012 R2
  Datacenter 6.3  (Build 9600: ) (Hypervisor)


texts: SSAS Date Dimension Relationship In Data Source View

texts:
When a fact table is at a higher granularity to the date dimension (e.g. monthly) how should this be represented as a relationship in the data source view?

With a date dimension containing columns DateKey and MonthDateKey and fact table containing MonthDateKey should the relationship be:

Fact.MonthDateKey -> Date.DateKey or Fact.MonthDateKey -> Date.MonthDateKey

What are the implications if this is set incorrectly in the data source view?


texts: Use array expression from subquery in ANY() condition

texts:
I have a relatively complicated query with a subquery fetching an array like so:

...
ARRAY(SELECT category_id FROM category_schedule_con con
      WHERE s.id = con.schedule_id ORDER BY category_id) AS cats,
...

and would like to use the array 'cats' in a later WHERE condition like

...
WHERE 4 = ANY(cats)
...

But this does not work as it states that the column 'cats' doesn't exist.
c/p'ing the subquery into the ANY clause yields the expected result.


texts:
I'm using SQL Server's

sp_send_dbmail
to distribute mail. Everything is working fine; BUT I tried to test a bad email address and it always reports success; and nothing is in the failed sent items table.

Will this procedure report IF the email isn't sent? Or is success just getting the mail to the exchange server?

Thanks in advance!


texts: How to prevent insert row duplicate value in a column and equal the any value

texts:
I have table

TABLE1(
 id number (primary key),
 IDAccount number,
 status char(10))

Table can have 2 row duplicate (IDAccount, status) with status=disable as well as

id     IDAccount     status
1       100          DISABLE
2       100          DISABLE

But, mustn't have 2 row duplicate (IDAccount, status) with status=enable as well as:

id     IDAccount     status
3       200          ENABLE
4       200          ENABLE // DON'T ALLOW

How I can do it ?
Thanks.


texts: How to setup daily SQL server export to csv text file?

texts:
We are exporting 100,000+ rows from a SQL Server 2005 DB to a text file. We have a web script setup to read all rows and then write the text file and it takes over 5 minutes to complete that script.

How can we set it up to export directly from SQL server to a csv file and schedule that daily?


texts:
I found the normal ULimit setting for mongo db is as follows:

    -f (file size): unlimited
    -t (cpu time): unlimited
    -v (virtual memory): unlimited [1]
    -n (open files): 64000
    -m (memory size): unlimited [1]
    -u (processes/threads): 32000

Just wondering why the "-u", recommendation is such high..

http://docs.mongodb.org/manual/reference/ulimit/

How many process do mongo generally spawn for each CRUD operations?


texts: Header and line item data source mismatch

texts:
I'm working with a star schema for a data warehouse and I am running into a problem with header and line items from different data sources.

CREATE TABLE DataSourceAHeader
(
     OrderId INT NOT NULL
    ,TotalCost MONEY NOT NULL
    -- Date, etc...
);

CREATE TABLE DataSourceALine
(
     OrderId INT NOT NULL
    ,LineNumber INT NOT NULL
    -- Dates, etc...
);

CREATE TABLE DataSourceBLine
(
     OrderId INT NOT NULL
    ,Cost MONEY NOT NULL
    ,LineNumber INT NOT NULL
);

I have data sources A and B which represent the same data in different ways. Data source A contains headers and line items, but it only has the net outcome (Total Cost) in the header. Data source B contains only line items and each item has an outcome (Cost).

I could keep two fact tables (one for the header and one for the line items), but I have researched and it seems inadvisable. Is there a strategy to deal with this kind of mismatched format or should they be stored in separate data warehouses (one warehouse per data source)?

My current strategy:

CREATE TABLE Fact.Order
(
     Id BIGINT IDENTITY PRIMARY KEY
    ,OrderId INT NOT NULL
    ,Cost MONEY NOT NULL
    -- Date key, etc...
);

CREATE TABLE Fact.OrderLine
(
     Id BIGINT IDENTITY PRIMARY KEY
    ,OrderFactId BIGINT NOT NULL REFERENCES Fact.Order (Id)
    ,LineNumber INT NOT NULL
    -- related line stuff
);

DataSourceAHeader and DataSourceBLine are inserted into Order and OrderLine. DataSourceBLine is split one line per row.

Here is an example for a DataSourceAHeader and DataSourceALine

SELECT * FROM Fact.Order;
|------------------------------------|
|   Id   |   OrderId   |   Cost      |
|   1    |     1100    |   12000.00  |
|   2    |     1101    |   10000.00  |
|------------------------------------|

SELECT * FROM Fact.OrderLine;
|-------------------------------------------|
|   Id   |   OrderFactId   |   LineNumber   |
|   1    |        1        |       1        |
|   2    |        1        |       2        |
|   3    |        1        |       3        |
|   4    |        2        |       1        |
|   5    |        2        |       2        |
|   6    |        2        |       3        |
|-------------------------------------------|

Here is an example for a DataSourceBLine

SELECT * FROM Fact.Order;
|---------------------------------|
|   Id   |   OrderId   |   Cost   |
|   1    |     1000    |   12.00  |
|   2    |     1000    |   10.00  |
|---------------------------------|

SELECT * FROM Fact.OrderLine;
|-------------------------------------------|
|   Id   |   OrderFactId   |   LineNumber   |
|   1    |        1        |       1        |
|   2    |        2        |       2        |
|-------------------------------------------|

Edit:

the TotalCost in the header cannot be brought down to the line level. I chatted with an architect acquaintance and his advice was to implement two separate fact tables, one for header (summary) and one for the lines (detail), and just have NULL values for the missing line information for DataSourceA.

Edit2:

I'm trying to be generic with the OrderId since I have several more data sources that may contain similar OrderId schemes (collisions). I have implemented a Mapping table in order to translate the source identifiers into the warehouse.

Edit3:

With the intention that this question be helpful to more than just myself, I would like the answer to have the following details
(mostly to compile what everyone has already reasoned about):

In general what are the approaches to resolving related disjoint data sets taking the form of summary/detail (single fact table or summary/detail fact tables)?
What are the drawbacks to each approach?
What kind of structure could the fact table take to cope with missing (or irrelevant) data?
(two fact table approach) In what cases would it be prudent to roll down the summary versus rolling up the details?


texts:
I want to use pgpool to replicate one database to another.  I want to do my processing on my master database (reads/writes/inserts/deletes), replicate that database to another database as it is modified and have other reads be directed to the replicated database.

As I read the pgpool documentation it appears that reads will be distributed across all databases in the cluster. Is there a way to not do that and direct one class of reads to one database and another class of read/writes to the master?


texts: SQL Query produced by Umbraco app & NHibernate ORM - help!

texts:
https://www.brentozar.com/pastetheplan/?id=SJ1BhSun

This is a sample of a query that this CMS called "Umbraco 5" runs against its backend DB, which is currently hosted on SQL 2008R2.  This query runs upon or after login.  Yeah, don't ask me why such a ridiculously complex query is needed on/just-after login.

It's taking over 30 seconds, which apparently is the magic borderline of the app's query/command-timeout setting when it passes the query down from the data-layer.  And apparently it used to take a bit less than 30 seconds so until now, it hasn't been a "breaking issue".  But of course now that it is, I, the DBA, am being asked to help fix it, despite having almost no control over the application nor the way the query itself is written/constructed (by NHibernate).

Any suggestions for magic index tuning based on the query plan (linked above), that might help this perform better?

Things I've checked/tried:

There are existing indexes that seem to make perfect sense, based on the join-predicates an so forth.
sp_BlitzIndex offers no further suggestions for missing indexes.
I've tried rebuild/reorg indexes, & keeping stats updated to a ridiculous degree, but none of that helps.

Edit: because the query itself got cut off by the XML dump of the plan, here is the last part of it, starting just before the query cuts off in the pastetheplan link (contextual part in curly-braces, everything after that is what's missing):

        {ON this_0_.NodeVersionStatusTypeId=innertype2_}.Id
    WHERE innerversi1_.NodeId = this_.NodeId
    ORDER BY this_0_.Date DESC)
AND this_.NodeId IN ('f3dea406-879b-42fe-be79-a0320098c921','32307dbd-c2ea-41a2-8d12-a052011a012e','613c7c0b-97a5-427a-8dbf-a05400a504ce')
ORDER BY outerhisto2_.Date ASC

Remember, I didn't write this!  (Though I did try to tidy up the format just a bit for readers.)


texts: Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform a rollback ? Is a ROLLBACK possible after a COMMIT ?


texts: I'm trying to create a database for a hotel company with multiple hotels. I'm stuck on how to reserve a room?

texts:
I'm using Xampp phpmyadmin for the first time...
So I've got the basics of the database. Customers, hotels, rooms etc.
The customer can chose a check-in and check-out date.
An admin can class a date as available/not.
My question, is how would I get the database to automatically update the dates as unavailable?
When I've tried, it makes that date unavailable for every room in every hotel.
(Still a newbie at all this, so sorry if this doesn't make any sense!)
Attached a rough draught of my ERD for reference.


texts: specify delimiter in openrowset bulk insert

texts:
I'm trying to insert data from CSV files into tables using a cursor and open rowset bulk insert.
this works for me 99% of the times but if one of the fields in the csv files contains a comma( , ) the data import is ruined and all the columns get imported one column after their place.
For example it should look like this:

timestamp   username   name         IP       title
20190331    ABCD12G    david hertz  1.1.1.1   null

But if i have a comma in the name column(for example) it will look like that:

timestamp   username   name      IP      title
20190331    ABCD12G    david    hertz   1.1.1.1

the csv example:

username,name,ip,title
ABCD12G,david,hertz,1.1.1.1,''

This is the syntax i'm using:

insert into [player table]
select 20190331,* FROM  OPENROWSET(BULK  'D:\folder\2019\03-
31\Player_statistics.csv', FIRSTROW = 2,
FORMATFILE='D:\folder\test\xml\Player_statistics.xml')
 as t1

how can i change this to ignore the comma in the middle of a word?(i can't limit the CSV since its taken from another client that will not limit characters.

the xml i'm using for the import with openrowset:

when you perform regular bulk insert you have an option to specify the delimiter
fielterminator = '","'  , I could not find something like that in openrowset.

What can help fix this issue?


texts: MySQL: Indexing by column which is also a part of a compound primary keys and

texts:
I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example:

CREATE TABLE `user_visit_stats` (
  `hour` datetime NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `visits` int(10) unsigned NOT NULL,
  `avg_time` float unsigned NOT NULL
  PRIMARY KEY  (`hour`,`user_id`,`page_id`)
)

So the primary key is composed of hour, user_id and page_id. I would like to be able to efficiently query the table on different combinations of these columns - for example to get the total visits for a page in a day or the total pages visited by a user in a week.

Does it make sense to define additional indexes on hour, user_id and page_id or will these indexes be redundant?


texts: Oracle expdp command executed in a command file not honoring %U instead using _U without a sequence

texts:
I have generated a bunch of expdp commands and stored them in a .cmd file.  When I execute one of the commands by copy and paste into a command prompt, it works fine.  If I try to execute the whole .cmd file, it also works great.

I need to use PARALLEL and %U, so I have added that to the commands.  Again, I run it manually in the command prompt and it works great and honors the %U by creating multiple .dmp files with a sequence number.  But when executing the whole .cmd file with the PARALLEL and %U, it does not honor the %U and just creates one .dmp file with _U.  How can I fix this so it will create the multiple dmp files?

Here is an example of one of my commands that executes fine when copied and pasted into a command prompt but does not work right when executed as a file.

expdp TEST/PWD@ORCL schemas=TEST directory=EXPORT_DIR parallel=4 REUSE_DUMPFILES=Y dumpfile=TEST_20190906_%U.dmp logfile=TEST_20190906_expdp.log


texts:
I need to set up a MySQL database with a maximum number of tables which can be created in it.

For example, if I have my maximum at 5 tables for a single database, and someone attempts to create a 6th table, an error will be raised.

Also note that this restriction can not affect other databases. For example, database 1 can have a max of 5 tables, and database 2 can have 10 tables, and database 3 can have unlimited tables.

Any help would be greatly appreciated. Thanks!


texts:
My company purchased part of another company. Now they have a large SQL Server 20TB database that is supposed to be moved to our data center. The problem is that only part of the database is being moved because the rest was not part of the merger.

For instance, part numbers and stuff are moving but only parts that are part of the merger deal. The rest is still considered the other companies property.

Is this possible?

I am a new Jr DBA and I mostly focus on Oracle not SQL Server.


texts: Best query to pick row based on IP address and DateStamp

texts:
Supposed I have Table1 that have account_id, ip_address and created_date columns but on the other table, Table2 has only ip_address and created_date columns. Table2 doesn't have an account_id that I can use to join in table1. For some reason from the management, I still need to assume or pick accounts_ids from Table1 to fill it in Table2.

What will be the best way to pick accounts_ids from Table1?
I tried the query below but it seems that there are accounts from Table1 which is obviously doesn't belong to Table2 due to created_date comparison. I wonder if there are ways to do this such as comparing YEAR(create_date) or etc?

SELECT t2.ip_address, t2.created_date, t1.account_id
FROM Table2 t2
LEFT JOIN ( SELECT account_id, ip_address, MAX(created_date) created_date
            FROM Table1 t1
            GROUP BY account_id, ip_address
           ) t1
on t2.ip_address = t1.ip_address

EDIT: Note that I just want to pick the closest date as possible since IP address and dates are not unique on Table1.


texts: How to create a mysql stored procedure through linux terminal

texts:
In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures.
I understood the  command   delimiter for only the mysql client(client side command).

but  I am using the mysql client which does not have the command delimiter
like dbslayer, on this kind of clients how can I define the procedures.

for now consider:

create procedure test_pro()
begin
select 'hello pro';
end

I tried the following :

mysql -u root -pmypass  test < proc_file

where  proc_file contains the above procedure;

but that is giving me the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Event I tried the following

 create procedure test_pro()
    begin
    select 'hello pro';
    end;

(added the trailing semicolon)
but am getting the same error .

Same I am experiencing with the dbslayer , if I am able to define the above procedure through terminal I think I should be able to do that through the dbslayer


texts: Disable autocommit for SQL Server connection in Oracle SQL Developer

texts:
I am using Oracle's SQL Developer to execute queries against my SQL Server 2008 database as I simply can't work with SSMS as long as it doesn't support Ctrl + Return to execute statements...

Anyway, statements in SQL Server seem to be autocommit even when using Oracle SQL Developer. Is it possible to change this on a permanent basis? BEGIN TRANSACTION is not a solution for me.


texts:
I am green been software engineer, my team lead give me a task in PostgreSQL database, I need to determine the relationship of the all 405 tables, but the problem is the database doesn't have any documentation, the foreign keys and primary keys naming are so different for example.

In table a the primary key is userId and the foreign key is accountId and in other table foreign key is employeeId and supervisorId.

How to approach this kind of database without documentation? is there any API for PostgreSQL to show the table relation ships?

any tutorial link or document is acceptable,


texts: Is there a way in SQL Server to make a table only able to insert by trigger?

texts:
The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't want the data at risk of being compromised in any way. The only thing that should ever insert on the table is the trigger I created on the active table to log its changes. In the rare case we may need to manually edit the log table I will turn off (if it exists) the "insert lock"

I am using SQL Server 2012 Enterprise with SQL Management Studio


texts:
First of all let me make kind of a disclaimer here:

1) i'm a developer, not an administrator;

2) and i dont have admin rights on my database.

3) table specs :

-- oulp_path_attendance_base_t - 5.437.370 rows

-- ota_gsi_delegate_bookings_t@DISEULPROD (over dblink) - 3.135.400 rows

--inted_parted_events_t@DISEULPROD (over dblink) 683.912 rows

That being said , to the code we go!

CREATE OR REPLACE TYPE  "OULP_AVIDS" as table of varchar2(50)
/

CREATE OR REPLACE PROCEDURE "OULP_UPDATE_ATTENDACE_INCLASS"
AS
   TYPE users_completed_avids IS RECORD(
     avid oulp_path_attendance_base_t.avid%TYPE,
     close_date oulp_path_attendance_base_t.completed_date%TYPE
    );
  TYPE avid_results IS TABLE OF users_completed_avids;

  resulted_avids avid_results;

  v_users OULP_AVIDS;
  v_user_avids OULP_AVIDS;

  v_c SYS_REFCURSOR;
  v_get_avids SYS_REFCURSOR;
  v_get_results SYS_REFCURSOR;

BEGIN
 PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS START');
  OPEN v_c FOR
    SELECT distinct person_id
      FROM oulp_path_attendance_base_t
     WHERE avid is not null;

   LOOP
    FETCH v_c BULK COLLECT INTO v_users LIMIT 100;

    FOR i IN 1..v_users.COUNT LOOP

      OPEN v_get_avids FOR
      SELECT avid
        FROM oulp_path_attendance_base_t
       WHERE avid is not null
         AND person_id = v_users(i);

      FETCH v_get_avids BULK COLLECT INTO v_user_avids;
      CLOSE v_get_avids;

     OPEN v_get_results FOR
      SELECT ev.activity_version_id avid,
             ev.course_end_date close_date
        FROM ota_gsi_delegate_bookings_t@DISEULPROD gsi,
             inted_parted_events_t@DISEULPROD ev
       WHERE gsi.event_id = ev.event_id
         AND gsi.delegate_id_emp = v_users(i)
         AND ota_status IN ('ATTENDED','Attended')
         AND ev.activity_version_id IN (SELECT column_value
                                         FROM TABLE(v_user_avids));
        FETCH v_get_results BULK COLLECT INTO resulted_avids;
        CLOSE v_get_results;

        FORALL j IN 1..resulted_avids.COUNT
        UPDATE oulp_path_attendance_base_t
           SET completed_date = resulted_avids(j).close_date
         WHERE avid = resulted_avids(j).avid
           AND person_id = v_users(i);

    END LOOP;
      EXIT WHEN v_c%NOTFOUND;
   END LOOP;

  CLOSE v_c;
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS END');

  EXCEPTION
    WHEN others THEN
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS FAILED');
END;

And the comments :

-- the purpose of this procedure is to updates my local table (oulp_path_attendance_base_t) with the close_dates for some activity_ids (named avids).

-- PROCESS_ERROR() procedure is just a simple logging procedure.

-- im running this via a scheduled job with dbms_scheduler.

Is there any way i can improve the performance here?

The job runs for about 10 hours and needs to run daily.

Cheers,
Alex


texts: How to automate creating credentials in a secure way

texts:
We have created an automated process to stand up a new SQL Server using TFS and powershell.  As part of a new server build, we deploy a database and a set of jobs for dba use.  As part of the install we need to create a credential for a sql agent proxy for some of our jobs.  However, we don't want to store in passwords in code.

Is there a solution to storing passwords (or generating a password) where we aren't storing it in plain text or in line in the code?

Our servers are on-prim, not in Azure.  We have access to Keepass, but that is being deprecated for Thycotic.


texts: Determine the size of a hashed varchar column

texts:
I am using SQL Server.

I have this table:

create table MyTable (
ID int not null ,
Name varchar(500) )

and I am adding a name_hash column to store hash values for the name column:

alter myTable
    add
    name_hash AS HASHBYTES('SHA1', CONVERT(VARCHAR(90), Name))

How can I determine the resulting size of name_hash? Is it related to the size of the column that I want to apply the hashing to?


texts: How to install pgperl for postgresql 9.6 windows

texts:
Trying various versions of ActiveState and Strawberry Perl, I'm still not having any luck overcoming:

pg96/lib/postgresql/plperl.dll:  The specified module could not be found

Suggestions that may have worked for prior versions of postgres don't work for 9.6


texts: One-To-One relation is this a good practice?

texts:
I have a table "Place" (id_place, name, address,coor_x, coor_y)

I would like to add for a place their "facebook page" "twitter page" "wesbite" and telephone

My first idea was to create a table let's say social_contact that contains these fields and add an extra column in the place table (social_contact_id) which would have been a 1 to 0..1 relation (these 4 fields are optional and could be blank)

Is this good practice? Or Am I better to stuff these 4 new files directly in the place table?

thank you!


texts:
We have a huge database and many procedures loaded onto it to cater reports and some of them are called every 5 min throughout the day.

Database is performing pretty well otherwise, but if I do not restart mysql once daily, queries which used to take few milliseconds to execute takes minutes and even hours to finish.
Even mysqldump (selective dumping of data based on timestamp columns) which normally completes in 15 minutes is taking 3-4 hours to complete if mysql restart is not done on daily basis.

Can anyone please help me understand what does mysql do internally on restart.
We use lots of temporary tables in our procedures. Could these be causing contention?


texts:
How can I create a MongoDB based on MySQL schema?

This is my schema:

productCode VARCHAR(15)
productName VARCHAR(70)
productLine VARCHAR(50)
productScale VARCHAR(10)
productVendor VARCHAR(50)
productDescription TEXT
quantityInStock SMALLINT(6)
buyPrice DOUBLE
MSRP DOUBLE


texts: Can a PostgreSQL Trigger be used to ignore an insert?

texts:
I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database.

I was hoping that while testing our replacement server, that there was some easy way that I could set up a trigger on that single table to effectively ignore all of the other triggers.  (rather than haing to remove dozens of triggers and then re-enable them when we go into production).

I was hoping to be able to use an INSTEAD OF INSERT trigger set to a function that did nothing, but you can only use those on views, not tables.  I tried a BEFORE INSERT  trigger that called RAISE EXCEPTION, but that results in the whole transaction failing, so the modifications to the other tables get rejected as well.

Is there some easy way to ignore inserts to that table, or should I just do an AFTER INSERT that clears the record out afterwards?


texts:
In SQL Server 2008 R2 Express the only mirroring alternative is "Witness only", however I can't find a good explenation to what this is.

I would like to have a database on a virtual server and then have a local SQL Server that mirrors this database, in case the internet connection goes down, I want to fail-over on the local server.

Any ideas on what SQL Server editions I need to be running on both ends?


texts: Postgres `log_disconnections = on` does not log disconnects

texts:
I have surely a basic problem and am undoubtedly overlooking a simple issue..
I have configured Postgres 12.4 with log_disconnections, but the log file does not show any disconnects. (It is logging connections, as expected with log_connections = on.)
I have connected and disconnected with both psql (exiting psql with \quit) and the Ruby pg library.  In both cases, no disconnect message was logged.
During testing, I used SELECT * FROM pg_stat_activity WHERE datname = 'foo' to verify a connection was present (idle) while I was connected, and gone when I quit psql or calling pg/Ruby conn.finish.
Why isn't the server logging disconnections?
The server configuration is basic:
max_connections = 100           # (change requires restart)

shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option

max_wal_size = 1GB
min_wal_size = 80MB

log_connections = on
log_disconnections = on
log_timezone = 'America/Toronto'

datestyle = 'iso, mdy'
timezone = 'America/Toronto'

lc_messages = 'C'           # locale for system error message
lc_monetary = 'C'           # locale for monetary formatting
lc_numeric = 'C'            # locale for number formatting
lc_time = 'C'               # locale for time formatting

default_text_search_config = 'pg_catalog.english'

The server log file:
2021-01-18 09:36:18.774 EST [45512] LOG:  starting PostgreSQL 12.4 on x86_64-apple-darwin18.7.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.17), 64-bit
2021-01-18 09:36:18.777 EST [45512] LOG:  listening on IPv6 address "::1", port 5432
2021-01-18 09:36:18.777 EST [45512] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-01-18 09:36:18.778 EST [45512] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-01-18 09:36:18.825 EST [45513] LOG:  database system was shut down at 2021-01-18 09:35:38 EST
2021-01-18 09:36:18.828 EST [45512] LOG:  database system is ready to accept connections
2021-01-18 09:44:36.433 EST [45868] LOG:  connection received: host=[local]
2021-01-18 09:44:36.434 EST [45868] LOG:  connection authorized: user=testuser database=foo application_name=pry
2021-01-18 09:45:37.299 EST [45962] LOG:  connection received: host=[local]
2021-01-18 09:45:37.300 EST [45962] LOG:  connection authorized: user=testuser database=foo application_name=psql

Postgres started with pg_ctl -l path/to/log -D path/to/pgdir start.


texts: How do I count rows with two properties in one index scan in SQL?

texts:
I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.

I need to output the following: for each day I need to count separately

items that were created in that day and have zero ItemUnitsCount
items that were created in that day and have nonzero ItemUnitsCount

and output the count and the date and on any day there can be any number of item of both kinds, maybe none. Each kind of items should be reported separately, so for any day I can have no rows, one row or two rows.

So I crafted the following query:

(SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, 1 AS CountType
   FROM Items WHERE ItemUnitsCount<>0 GROUP BY ItemCreationDate )
UNION ALL
(SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, 2 AS CountType
   FROM Items WHERE ItemUnitsCount=0 GROUP BY ItemCreationDate )

and this works okay, but when I looks at the actual execution plan I see two clustered index scans one of which has predicate ItemUnitsCount=0 and another has predicate ItemUnitsCount<>0.

ItemUnitsCount can be changed several times over lifetime of the table row, so I'd rather not build an index for it.

Is there a way to build a query with the same effect that results in one index scan instead of two?


texts: Oracle vs SQL Server, 4k vs 16k block sizes

texts:
I was reading over these 3 articles:

At my last company we only used MS SQL Server and MySQL, and if a server was strictly a database server we would setup the HDD to use 16k block sizes.

http://houseofbrick.com/improve-performance-as-part-of-a-sql-server-install/

I work for a different company running Oracle, we have a small issue every once in a while where the database will lock up for about 2 to 3 seconds when a query reads about 700K rows; we know it's a coding issue where a where statement needs to be added.

This article shows some metics for running 8k to 16k on various tables of size and weight:

http://blog.dbi-services.com/do-the-block-size-matter/

Then I read something very interesting:

http://www.dba-oracle.com/s_oracle_block_size.htm

Specifically here:

Block Size Advantages

Smaller blocksize:

-  Good for small rows with lots of random access.

Larger  blocksize:

-  Permits reading several rows into the buffer cache with a single I/O (depending on row size and block size).

Block Size Disadvantages

Smaller  blocksize:

- Not recommended for large rows. There might only be a few rows stored for each block, or worse, row chaining if a single row does not fit into a block.

Larger blocksize:

- Not good for index blocks used in an OLTP environment, because they increase block contention on the index leaf blocks.

Can I split up table reads and specify block size to use?

I thought block size is consistent across the disk and set when the drive is partitioned.

We have a 3-tier SAN, the Oracle server is virtual if that makes a difference.


texts: MongoDB - handling embedded documents and relations

texts:
I am researching MongoDB for a web app that I am bulding.  Coming from a MySQL background, the concept of embedded documents is not so easy to fully understand.

Let's say I have a document called blogpost and it looks something like this:

db.posts.save({
    _id: 1
    title: "first post!",
    body: "post content",
    author: {
        _id: 1,
        name: 'John'
        email: 'jonh@doe.com'
    },
    comments: [
        {
            _id: 20,
            author: 'mary',
            content: 'This blog post is cool!'
        }
    ]
});

Each author would actually be stored in the authors collection, when I save the blogpost, I would merely copy the data from the author document and paste it so it is embedded in the blogpost document.  Is this a good way to do that?

My concern is that when John updates his e-mail address, it would only be updated in the authors collection. Some of his older blogposts would then show an outdated e-mail address for him.

Does MongoDB have a method for dealing with that issue, or would I need to do it myself in my app code?

If I do that in my app code then what is the point of embedding the author into the blogpost in the first place? I could just store the reference, author _id, and look up the author in a separate query.

On the other hand, if I need to store historical data, for example an invoice with customer information, then it would make sense to embed the customer document inside the invoice document since invoices need to show the customer data that existed when the invoice was created.

For the comments part, I have already read about Multiple Collections vs Embedded Documents and when it comes to comments, Multiple Collections seems to be the way to go. http://mongly.com/Multiple-Collections-Versus-Embedded-Documents/

So, in general - am I completely missing the point of embedded documents?


texts: Best approch to replicate data from one db to other db on same server

texts:
I am facing issue of time out and slow data response so I did search on Google and found that I could create a server which replicates main database data to secondary database and both on same server instance; main database used for real time read and write and the Report server will get data from the secondary   database so when someone runs a large report then it will not affect the main database and the application will run with out any issue.

What replication solution I have to use for situation? I am using SQL Server 2012 Standard Edition.


texts: Hoursworked/day computation via Bio Logs

texts:
This is my checkinout table:

USERID  CHECKTIME            CHECKTYPE
------  -------------------  ---------
404     2016-02-12 07:24:03  I
404     2016-02-12 17:01:34  O
404     2016-02-15 07:24:21  I
404     2016-02-15 17:00:36  I
404     2016-02-16 07:09:01  I
404     2016-02-16 17:01:33  O

I used the script below to compute hoursworked/day, but the output gives my column Hoursworked huge value:

SELECT userid,Name
    DATE(CHECKTIME) AS date,
    SUM(UNIX_TIMESTAMP(CHECKTIME)*(1-2*checktype))/3600 AS hours_worked
    FROM CHECKINOUT
    GROUP BY date(CHECKTIME), userid;

Output:

Hoursworked  Userid  Date(Checktime)
-----------  ------  ---------------
NULL         404     2016-02-12
NULL         404     2016-02-12
NULL         404     2016-02-15
NULL         404     2016-02-15
NULL         404     2016-02-16
NULL         404     2016-02-16

How to resolve this issue?

My data source is inconsistent: our employee has the habit of multiple login\logout. Can you help where to eliminate the employee with incomplete I\O and for those having multiple login\out to get the first and last login\out logs.


texts: Process attempted to unlock a resource it does not own

texts:
SQL Server 2005 SP4 32-Bit

I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean when I run it now. The database is fully accessible. Should I be concerned? I'd hate to go back to a backup at this point.

 2013-04-02 02:10:55.53 spid56      Error: 1203, Severity: 20, State: 1.
 2013-04-02 02:10:55.53 spid56      Process ID 56 attempted to unlock a resource it
                                    does not own: PAGE: 34:1:388664. Retry the
                                    transaction, because this error may be caused
                                    by a timing condition. If the problem persists,
                                    contact the database administrator.
 2013-04-02 02:10:55.58 spid56      Error: 3314, Severity: 17, State: 3.
 2013-04-02 02:10:55.58 spid56      During undoing of a logged operation in
                                    database 'MY_DATABASE_NAME', an error occurred
                                    at log record ID (1342973:12519:37). Typically,
                                    the specific failure is logged previously as
                                    an error in the Windows Event Log service.
                                    Restore the database or file from a backup,
                                    or repair the database.


texts:
I have 4 tables related like this (it's an example):

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company

Classification:
ID
Name
Code
ID_Company

Workers:
Id
Name
Code
ID_Classification
ID_Department

Suppose that I have a classification with id = 20, id_company = 1.
And a department that has id_company = 2 (that represents another company).

This will allow the creation of a worker that is from two companies because the classification and department are linked to the company separately. I don't want that to happen, so I think I have a problem with my relationships and I don't know how to solve it.


texts:
I'm trying to update the row_start column on a table with Versioning set. The row_start column is invisible.

UPDATE pgwebp.t_prov a
INNER JOIN pgwebp.t_prov b ON a.prov_id_a = b.prov_id
SET a.row_start = b.system_ts_start;

Error message:

Unknown column 'a.row_start' in 'field list'


texts: SQL Server: Effect of Setting These Index Options

texts:
A reindexing script was recently run in our dev environment that set ALLOW_ROW_LOCK = FALSE and ALLOW_PAGE_LOCKS = FALSE on all the indexes in the database.

This was done while testing new reindexing scripts--it was not done intentionally.

After this script was run, we immediately started seeing a large number of deadlocks where we normally never saw them.

Was setting the locking options on the indexes the direct cause?
Why exactly would those settings cause deadlocks?


texts:
I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables.  As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records?  As a more concrete example, is this:

Select
  Col1, Col2, Col3
From
  Table1
Where
  (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition"

Superior or Inferior to something like this:

Select
  T1.Col1, T1.Col2, T1.Col3
From
  T1
Inner Join
  T2
    On T1.FK = T2.PK
Where
  T2.RefCol = "Condition"

--  Edited --

As a corollary question:  Is it productive to create Views which contain Intermediary validations?  IE, if I repeatedly need to check if T1.Col1 = 1 and T1.Col2 = 0 is it worthwhile to create the following view:

Create View T1Validated As
Select
  Col1, Col2
From
  T1
Where
  Col1 = 1
And
  Col2 = 0

And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?


texts: How to connect multiple users/schemas in Oracle 11g?

texts:
I'm almost new user to databases, so I think I'm doing something wrong.

I have the database created.
I created 3 users, 1 it's an Admin, 2 are just users.
I created 2 roles, 1 for the Admin and 2 for the other users.

I'm learning that the schemas in Oracle are different from SQL Server. In SQL Server I created tables assigned to a schema, for example: CREATE TABLE schema1.table1 and then I was be able to see them all with users asigned to that DB; with Oracle, I think a schema is the sum of the sequence, synonyms, etc that a user has. (If not, please correct me)

I want to achieve that in Oracle, so I created the users/schemas; after that I got connected with user1 and created the tables with that user; later, I got connected with user2 and created the tables related to the user.

Now, when I'm trying to create the alter relating user1 tables and user2 tables with admin, it says I don't have enough privileges.

ALTER I'm trying to do:

ALTER TABLE user1.PhoneTable
    ADD (CONSTRAINT C_001 FOREIGN KEY (Status)
            REFERENCES user2.ClientTable (Status) ON DELETE SET NULL);

I created those users because if I wanna see all the tables related to the Phone(user1), I got connected to that user and that should be all, I just wanna have the DB with some order.

Admin privileges:

GRANT
    CREATE SESSION,
    UNLIMITED TABLESPACE,
    CREATE TABLE,
    DROP ANY TABLE,
    CREATE CLUSTER,
    CREATE SYNONYM,
    CREATE PUBLIC SYNONYM,
    CREATE VIEW,
    CREATE SEQUENCE,
    CREATE DATABASE LINK,
    CREATE PROCEDURE,
    CREATE TRIGGER,
    CREATE MATERIALIZED VIEW,
    CREATE ANY DIRECTORY,
    DROP ANY DIRECTORY,
    CREATE TYPE,
    CREATE LIBRARY,
    CREATE OPERATOR,
    CREATE INDEXTYPE,
    CREATE DIMENSION,
    CREATE ANY CONTEXT,
    SELECT ANY DICTIONARY,
    CREATE JOB,
    ALTER ANY TABLE,
TO myAdmin;

User privileges:

GRANT
    CREATE session,
    CREATE table,
    CREATE view
    CREATE procedure,
    CREATE synonym,
    ALTER ANY table,
    ALTER view,
    ALTER procedure,
    ALTER synonym,
    DROP table,
    DROP view,
    DROP procedure,
    DROP synonym
TO myUsers;


texts:
i used SQL Server, and have two joined table

Select b.team, SUM(a.total) FROM data1 A
INNER JOIN team b ON a.teamID = b.teamID
WHERE month = 1 and year = 2019
GROUP by team

second one is like this

Select b.team, SUM(c.total) FROM data2 C
INNER JOIN team b ON c.teamID = b.teamID
WHERE Date BETWEEN '2019-01-01' AND '2019-01-31'
GROUP by team

and the result will be :

Data 1                   Data 2
TEAM   | Total        TEAM   | Total
Team 1 | 5            Team 1 | 4
Team 2 | 3            Team 2 | 3
Team 3 | 8            Team 3 | 9

i wanted to create query to get result like this :

Team   | Total
Team 1 | 1
Team 3 | -1


texts: Deadlock in INSERT ... ON DUPLICATE KEY UPDATE

texts:
We have a table defined like this:

CREATE TABLE ipsum (
    id char(40) NOT NULL,
    source char(40) NOT NULL,
    ip_address varbinary(16) NOT NULL,
    port smallint(5) unsigned NOT NULL,
    percentage decimal(5,2) NOT NULL DEFAULT '0.00',
    first_date datetime NOT NULL,
    last_date datetime NOT NULL,
    p0 tinyint(1) NOT NULL DEFAULT '0',
    p1 tinyint(1) NOT NULL DEFAULT '0',
    blocked tinyint(1) NOT NULL,
    created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    country varchar(100) DEFAULT NULL,
    continent varchar(100) DEFAULT NULL,
    note varchar(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_first_seen_peer (first_date),
    KEY idx_last_seen_peer (last_date),
    KEY ipsum (source,ip_address,port)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

We are doing bulk inserts into this table in batches of up to 15,000 records at a time. Inserts are structured like this:

INSERT INTO ipsum (id, source, ip_address, port, percentage, first_date, last_date, p0, p1, country, continent, note, blocked)
VALUES {{ 15000 legitimate values here sorted alphabetically by the id field }}
ON DUPLICATE KEY UPDATE
p0 = p0 OR VALUES(p0),
p1 = p1 OR VALUES(p1),
last_date = GREATEST(last_date, VALUES(last_date)),
first_date = LEAST(first_date, VALUES(first_date)),
percentage = GREATEST(percentage, VALUES(percentage));

The records in the VALUES set have been ordered by id alphabetically.

When we reach a very high rate of input with multiple instances of the query running we start to see deadlocks:

  Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction]

No other statements are being executed against the table -- only the inserts as described above.

How can I modify the insert query to prevent the deadlocks?


texts: Dynamically update JSONB rows - POSTGRESQL

texts:
I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field.
The node we are trying to move, however, is not fixed; sometimes its index can be 1, 2, or 3.
I currently have this query:
 UPDATE table1 t1 SET jsonbfield = jsonb_set(jsonfield ,
            '{field1,0,field2,0,configuration}',
            (select jsonbfield->'field1'->0->'field2'->0->'field3'->0->'configuration'
            from table1 where
            jsonbfield->'field1'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME'
            and id=t1.id), true)
            where jsonbfield->'field'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME';

Which basically would achieve the requirement if the node we were trying to move was always at position 0, however, as mentioned, we noticed that the node we want to move up can be on another positon.
Is there a way to iterate trough each row in JSON, get the position of the node we want to move, save it into a variable and place it in the update statement?
I have tried with a do for, but it seems it doesn't interact well with select jsonb_array_elements.
Sample Data
{
   "parent":[
      {
         "steps":[
            {
               "name":"CONFIG1",
               "index":1,
               "childStep":[
                  {
                     "configuration":{

                     }
                  }
               ]
            },
            {
               "name":"CONFIG2",
               "index":2,
               "childStep":[
                  {
                     "configuration":{
                         "configInfo":{
                           "info":"someinfo"
                        }
                     }
                  }
               ]
            }
         ]
      }
   ]
}

This json represents the jsonbfield row from table1.
What we want to do is move the configuration Node CONFIG2 from childStep to Step Level, so it would look something like this after updating it:
{
   "parent":[
      {
         "steps":[
            {
               "name":"CONFIG1",
               "index":1,
               "childStep":[
                  {
                     "configuration":{

                     }
                  }
               ]
            },
            {
               "name":"CONFIG2",
               "index":2,
               "configuration":{
                         "configInfo":{
                           "info":"someinfo"
                        }
                     }
            }
         ]
      }
   ]
}

The query that i posted, already moves the field up, however, the issue resides on that we can not know if CONFIG2 will be located at index 0, 1, 2, etc.
So we are trying to figure a way to dynamically get where its located and then update it.
With the current query posted here, it assumes CONFIG2 is always located at index 1 (or 0, first position).


texts: How to reference only affected rows in AFTER UPDATE trigger

texts:
I have this table:

And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number or step_name column.

I started creating it by

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN
    UPDATE [dbo].[app_bread_crumbs]
    SET last_updated_by = SYSTEM_USER,
    last_updated_on = GETDATE()
END

But this will update all rows at once. I'm trying to figure out how to specify to only update last_updated_on and last_updated_by by the trigger for the specific row where updates are being made.

For example if they update:

UPDATE [dbo].[app_bread_crumbs]
    SET step_name = 'DAMAGE' WHERE step_number = 1

Only first row should be updated by the trigger


texts: RMAN Recovery In Oracle 11g : Backup Database To Tape

texts:
On issuing this command:

RMAN> backup device type sbt tablespace <tablespace_name>;

we are getting the following error:

ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

How to overcome this error?


texts:
I'm using SQL Server 2016

I have a basic stored procedure using execute as that I would like to run, but it comes up with this error when I try (names have been anonymised):

  Msg 15404, Level 16, State 19, Procedure usp_executeastest, Line 0
  [Batch Start Line 2]

  Could not obtain information about Windows NT group/user
  'MyCompany\A.Smith', error code 0x54b.

Here is my stored proc:

CREATE PROCEDURE [dbo].[usp_executeastest]
WITH EXECUTE AS 'dbo'
AS
BEGIN

SELECT * FROM [dbo].[TestTable1]

END

Now the database user 'dbo' is mapped to login 'MyCompany\A.Smith' so this explains why the error message mentions MyCompany\A.Smith.

I have tried running this proc by logging in as various users, including logging in as 'MyCompany\A.Smith' who is mapped to the dbo user, and the same error message comes up every time.

So why can't any user run this stored proc?


texts: How the input entered through the front-end form match with the existing data in database

texts:
Am working on an application that helps user to enter symptoms that matches those existing in the database and gets disease(diagnosis).

I have the following database table with information about symptoms, diseases, and disease-symptoms which associates the two tables, as shown below:

DISEASE_T   SYMPTOMS    disease-symptoms
==========  ==========  ========

DISEASE_ID  SYMPTOM_ID  DISEASE_ID

VALUE       VALUE       SYMPTOM_ID

I want to search user selected symptoms, which are array of symptoms id in disease-symptoms table and return top 5 matching disease IDs, based on, where most of the symptoms are matching with each disease.

For example, user select symptoms are 1,2,3 (cough,cold,headache)

DISEASE_T      SYMPTOMS_T  disease-symptoms

=========      ==========  ===============

1,malaria       1,cough          1,1

2,tuberculosis  2,cold           1,2

3,typhoid       3,headache       2,2
                                 2,3
                                 3,1
                                 3,2
                                 3,3

Help will be appreciated.


texts: How should I model a worflow of status/activities ? (rules like "if is 3 go to 4, if is 4 go to 5" etc)

texts:
I'm trying to model a workflow (status and activities) to create "rules".

Let me give you an example.

The main "object" let's say are "vehicle requirements", I will have this table:

idRequirement, idVehicle,DateOfRequirement, idStatus, LastUpdate
1                2       2012-01-02 10:20:00, 1     ,2012-01-02 10:20:00

In the example above as the register is just created, the DateOfRequirement and LastUpdate is the same, LastUpdate it will be updated after every change of status.

Status TAble

 idStatus,Status
 1         New
 2         Vehicle Assigned
 3         Closed

Activities Table

 idActivitie, Activitie
 1            Create Vehicle Requirement
 2            Assign Vehicle
 3            Close requirement.
 4            ReAssign Vehicle.
 5            Confirm

Until the tables above I have no problems, but my problem is that
for example:

I know I cannot close a requirement (update to idStatus=3 on "vehicle requirements")
if the current status is not "Vehicle Assigned" (idStatus=2).

I can do that by code (or I think so), but is there anyway to model this kind of rules to tables ? I mean a table where I retrieve the next idStatus and the activitie to perform (to save a "kardex/historical" of each requirement and it's activities), checking for example the Activitie or the current status ?

The rules also will have 3 "alerts" accordin the time and status. For example the limit time "green" to assign vehicle is 30 minutes or less the yellow "alert" it will be between 31 and 45 after the requirement has been created (status new) and the red alert it will show it after 45 of being in status "new"

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Green

After 39 minutues it will something like this:

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Yellow

And after 45 minutes:

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Red

If there's no enough clear please tell me to try to explain better.

In the meantime I just need a single "workflow" I mean, always the order it will be idStatus=1, after that idStatus=2 and aftear idStatus=3, but if you want to help me considergin cases like go to idStatus=3 after idStatus=1, I'll be really grateful.

Some idea (probably bad) I had:

Table rules:

idRule   Name      idCurrentState  idNextState  idActivitieToPerform  GreenLimit  YelloLimit
1        Assign            1             2           1                  30          45
2        CloseFromNew      1             3           3                  30          90
3        ChangeVehicle     2             2           4                  45          90
4        CloseRequirement  2             3           3                  90          120
5        ConfirmVehicle    2             2           5                  30          90

But I have lot of trouble because of the "idCurrentState" and "idNextState" refers to the same primary key on status table.
My regards and thanks for your time.

Updates:

Remove "redLimit" (as Kevin Feasel mentioned (and I figure out a couple of hours after post, it's implied))
Add some Rules to let the sample more clear:

CloseFromNew
ChangeVehicle
CloseRequirement

Add activitie "ReAssign Vehicle"

To consider:

I'm 99% sure there are only 3 "colors" (so we can't ignore relationships with Colors and any other table as status/activities).
Different activities derive on the same status (ChangeVehicle and
Confirm) (muliple activities)
Different activities in different status derive on same status (no
linear workflow)
No linear workflow

"CloseFromNew" perform "Close" activity and set  Status to "Closed" from status "New", just as "CloseRequirement" set status to "Closed" but from status "Vehicle Assigned"

Well based on the comments of Kevin Feasel, I just extend my sample/problem (after "Update" text explain the constraints I need to accomplish)

My regards again !


texts: Using AS OF TIMESTAMP or DBMS_FLASHBACK.ENABLE_AT_TIME across multiple schemas

texts:
We have a requirement to read data as of certain time (say T1) from, say, schema 1, fill some staging tables in schema 2 with that data. And then read data from some other tables of schema 1 as of T1 based on the current data in the schema 2 staging tables (i.e. schema 1 and schema 2 are joined in the subsequent queries).

Is there a way of achieving this without having to use AS OF in each of the (500+) queries in the stored procedure, wherever schema 1 tables appear? Also there are lots of commits in the stored procedure due to the large amount of being inserted into schema 2.

Business need is to have read consistency across all the queries on Schema 1 so it can be as of the start of the stored procedure execution. All the tables in the schema 1 are enabled for flashback queries. Issue we are facing is having to read the tables of schema 1 as of certain time, simultaneously with reading current data from tables in schema 2.


texts:
I've got a back up process which initially writes to a local disk, then the disk gets backed up remotely. After each back up (log, diff and full) I'd like to delete all the files on the local disk. Will SQL Server notice this?

Does MS SQL read the bak files to know what has been backed up and what hasn't for example?

I've got a database on AWS with a disk used only for backups. After every backup a snapshot is taken of the disk (via an AWS CLI script). So now I have the data on the disk and in the snapshot. The disk costs $200+ dollars a month.

I want to change our back up process to create the disk, do the back up, take the snap shot and then drop the disk.
Will SQL Server get upset if I mess with it's log files in this way?


texts:
I want to make a query a little complicated, and I'm not able to do it alone.

I have the following tables:

Table color:
id_uo  P
id_cor   PF
id_area   F
text_cor

Table area:
id_area  P
text_area

Table atv:
id_dest PF
id_cor PF
id_uo PF

Table dest:
id_dest P
type

What I intend to have as a result is a table that returns the fields as follows:

text_area | id_cor | text_cor | type

Do not know if atv through the table, I can create a query that helps me access to all parameters. Sorry but I'm newbie in the creation of more complex query. I've already tried that with Join and Union but got nothing that I would get. Can you help me?

select * from area:

id_area | text_area
--------------------
  1     |   info1
  2     |   info2

select * from color:

id_cor |  id_uo | id_area |text_cor
-----------------------------------
  1    |  1222  |    1    |  create
  2    |    1   |    2    |  teste
  2    |  1222  |    1    |  sssss

select * from dest:

id_dest | type
--------------
   1    |   E
   2    |   M

select * from atv:

id_dest | id_cor | id_uo
----------------------------
  1     |   2    |  1222
  2     |   2    |   1
  2     |   1    |  1222

What I want to achieve for each id_uo is the following result (for id_uo: 1222):

text_area | id_cor | text_cor | type
-------------------------------------
 info1    | 1      |  create  |   M
 info1    | 2      |  sssss   |   E


texts: Time out errors - MS access front end with SQL backend

texts:
First of all I don't know anything about SQL, so I apologise if I make no sense.

I have created a MS access database and our IT area migrated the tables to SQL server 2008 and linked the tables to the front end.

The database works fine, until the load increases (around 1000 users), then whenever trying to submit a record the user gets the following error:

Error 3155 ODBC--insert on a linked table 'tblMain' failed.
[Microsoft][ODBC SQL Server Driver]Query timeout expired (#0)

That error occurs for about 90% of users, basically making it unusable. At around 4pm when most user leave for the day, the database begins to work again without issue.

The IT area say everything is fine on their end, there are no limits set to users or locks, and the issue is with the front end and the code being too complex. But there is no way that's it, because the code behind the submit button is as simple as it gets.

DoCmd.GoToRecord , , acNewRec

I don't know what to do as IT are being difficult, I just need to get a better understanding so I can get them to look into it further. So any advice appreciated.

Oh, and I got sneaky access to SQL, and if I go to database properties is says number of users = 8. This doesn't change if I open the database. Does that mean it's limited to 8 people?

Many thanks,
Jeremy.


texts: Help me define the tables in this scenario

texts:
We have 2 tables setup like this:

WEB_GK_STATE
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)

WEB GK
    PRG_CODE    Varchar2(5)
    TEXT        Varchar2(4000)

Here is some sample data:

INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('PA', 'OL');
INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('RC', 'TX');
INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('PA', 'TX');

INSERT INTO WEB_GK (`PRG_CODE`, `TEXT`) VALUES ('PA', 'THIS IS THE GK FOR PA');
INSERT INTO WEB_GK (`PRG_CODE`, `TEXT`) VALUES ('RC', 'THIS IS THE GK FOR RC');

We use this Oracle function to retrieve the values:

CREATE OR REPLACE FUNCTION MAIN.F_GET_GK_TEXT (P_PRG_CODE IN MAIN.WB_GK_STATE.PRG_CODE%TYPE
                                             , P_STATE IN MAIN.WB_GK_STATE.STATE%TYPE)
    RETURN varchar2 IS V_STR varchar2(2000);
BEGIN
    for recs in (select TEXT from wb_gk_state a, WB_GK b
                 where state=p_state and a.prg_code=p_prg_code
                       and a.prg_code=b.prg_code) loop
           V_STR:= recs.text;
    end loop;
    return V_STR;
END;
/

Now, our dilemma is that we want to be able to provide a different WEB_GK TEXT for the PRG_CODE 'PA' WHERE STATE = 'TX'

If we do it the way we have now, the WEB_GK TEXT will show the same for both OL and TX because we key on PRG_CODE.

My first thought would be to have a STATE column in WEB_GK and get rid of the WEB_GK_STATE table altogether,
but that means we would have to write 50 records to support the same WEB_GK for 50 states.

How could I do this with minimal disruption of our code and modification of our tables?


texts: Get-Counter not accepting array created from SQL

texts:
When I try and generate an array from a SQL output, although it's the same data type and content, perfmon doesn't accept these values, whereas it does for a manually created a list. So the first call for Get-Counter returns an error message as seen below, where as the second call with a manually created list works correctly. How do I resolve this?

 Get-Counter : Internal performance counter API call failed. Error: c0000bc4.

At line:26 char:9

    $SQLQueryGetCounters  = "
    DECLARE @PerfmonCounterList VARCHAR(MAX)
    SELECT
        TOP 2
            PerfmonCounter
    FROM
        Perfmon.PerfmonCounterList"

    $SQLQueryGetCounters  = "
    DECLARE @PerfmonCounterList VARCHAR(MAX)
    SELECT top 2
        ' `"' +PerfmonCounter + '`"' ItemValue
    FROM
        Perfmon.PerfmonCounterList "
    $Counters =   @(Invoke-sqlcmd -ServerInstance Localhost -Database "DatabaseMonitoring" -Query $SQLQueryGetCounters )| select-object -expand ItemValue
    $List = [System.Collections.ArrayList]@() # New-Object System.Collections.Generic.List[System.Object] # [System.Collections.ArrayList]@()

    foreach ($Item in $Counters)
        {
            $List.Add($Item)
        }

    $List = $List.ToArray()

    Get-Counter -Counter $List

     $List = "\DATABASE(*)\DATABASE CACHE % HIT","\DATABASE(*)\DATABASE CACHE SIZE (MB)"

     Get-Counter -Counter $List


texts: How to retain the current value of a sequence when copying a database in SQL Server

texts:
I have a template database that contains tables with some data I'd like to have always when starting a new project. The idea is to copy the template when starting a new project. I use sequences for getting id column values (in code) for data rows in tables.

The problem is this: when I copy the database using SQL Server Management Studio's Tasks -> Copy Database, and select Use the SQL Management Object method as the transfer method, the sequence values seem to reset to source database's sequence's "reset value" instead of retaining the sequences current value.

Is there any way to retain the current value of sequence automatically when copying the database? Now when I add some data to the template database, I basically have to every time update related table's reset value, or otherwise the data and the current value of sequence will be off in the copied database.

I'm using SQL Server Management Studio 2012 and the server version is 12.0.4237 (so SQL Server 2014).


texts: 25M x 25M inner join (postgresql) performance

texts:
I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree index the ID (bigint) column which the join is being done on for both tables. Any tips? How long do you think I have to wait?

EXPLAIN SELECT
    public.products_by_location_mv.id,
    public.products_by_location_mv."data_object.unique_id",
    public.products_by_location_mv.location AS outline,
    public.products_by_location_mv.elevation_ft,
    public.products_by_location_mv."geo_product.geo_product_id" AS pid,
    public.products_by_location_mv.cntry_name,
    public.products_by_location_mv.product_name,
    public.products_by_location_mv.product_type,
    public.products_by_location_mv.product_producer,
    public.products_by_location_mv.product_size,
    public.products_by_location_mv.do_location,
    public.products_by_location_mv.product_location,
    public.obj4.uid AS oid,
    public.obj4.size_bytes,
    public.obj4.object_date,
    public.obj4.description,
    public.obj4.location AS path
INTO
    public.inventory0
FROM
    public.obj4
INNER JOIN
    public.products_by_location_mv
ON
    (
        public.obj4.id = public.products_by_location_mv.id) ;

"Hash Join  (cost=3825983.03..12908235.27 rows=24202368 width=1356)"
"  Hash Cond: (products_by_location_mv.id = obj4.id)"
"  ->  Seq Scan on products_by_location_mv  (cost=0.00..1457298.68 rows=24202368 width=721)"
"  ->  Hash  (cost=1414691.68..1414691.68 rows=25507868 width=643)"
"        ->  Seq Scan on obj4  (cost=0.00..1414691.68 rows=25507868 width=643)"


texts:
Good morning all ,
I have an alwayson instance with a multisubnet listener
I configured RegisterAllProvidersIP = 0 in order to register a single DNS entry
HostRecordTTL is configured at 1200 seconds
I would like if with this configuration I will have timoeout during the failover
if so how much time is needed for the application to connect to the lsitener
thanks


texts: Com_commit and Com_rollback count are almost same. What doest it mean?

texts:
SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed.
When I compare Com_commit to Com_rollback, both are almost equal. I do not understand how is that possible. My understanding is that you commit transactions and any rollback is done only when required. Then, why these values are so close?
We have only one database that is used for application (Jira) and it uses READ-COMMITTED Transaction Isolation. There are no other procedures in the entire mysql that does any operations.


texts: Using MySQL workbench, how to prevent new foreing key columns when modeling

texts:
I'm reverse-engineering a database and specifying relationships.  MySQL workbench wants add foreign-key columns as I do this.

While I see the benefit of this, I would like to turn off this default behavior since the columns already exist (just using a different name than what Workbench is expecting).

I'm using Mysql 5.5.49 and Workbench 6.3.6 on Ubuntu if that makes any difference.


texts: Combining Postgres jsonb query statements

texts:
I have test table with votes column and I want to upsert nested jsonb object into it. My current query:

INSERT INTO test (post_id, username, votes)
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb)
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE
  SET votes = test.votes ||
('{"commentid" : {"vote": true} }'::jsonb || test.votes->'commentid')
  WHERE NOT test.votes @> EXCLUDED.votes
  RETURNING *;

test_pkey constraint is PRIMARY KEY (post_id, username)

How can I construct this statement

('{"commentid" : {"vote": true} }'::jsonb || test.votes->'commentid')

So postgresql would read it as

'{"commentid" : {"vote": true, test.votes->'commentid'} }'::jsonb

is it possible to somehow combine it like in json?

What this is supposed to do is make "commentid" key-value pair if one doesnt exist and combine them if it does.

I have tried using different variations of jsonb_set like

jsonb_set(test.votes,'{"commentid", vote}','true', true);

or

jsonb_set(test.votes,'{"commentid"}', test.votes->'commentid' || '{"vote":true}', true)

But the first one erases the whole votes object if "commentid" doesn't exist and second one replaces the whole votes object with second part of the statement.

If it's possible to make it work this way, I'd be able to return this data straight to frontend without any modification


texts: Connect Jira to Postgresql with Pgpool-II

texts:
I gotta problem with connecting Jira 6 to Pgpool-II which is responsible for load balancing and  replication in Postgresql bases(2 instances).

Pgpool is started on port 9999, I just want to connect to it in this (Jira)installation step but Jira tolds that connection attempt failed....


texts:
I using Mysql.

Table: Content_T1

id :  int

title : varchar255

content: text

my all page content will be save in content columns.

mysql content column...  there is some <img src="http://abc.com/data/img/......">

But I had change my domain,

so I need change all <img> tag

if url =  http://www.abc.com/data/img/....

I need change to =  http://www.def.com/data/img/....

Just replace this url and path, without affecting other content.

How can I do ?


texts: postgres function definition not saving top whitespace

texts:
For reference I am running postgres 9.6 . I'd like to write functions inside of postgres where the top of the function saves.

 create or replace function
apqp.apqp_list_gentex_tool_report_getlist(_omni_search text    DEFAULT ''::text
                                        , _cust_id     integer DEFAULT 0
                                         )
 returns TABLE(reason       text
             , program     text
             , mold_number text
             , rev               text
             , stone_part_number text
             , part_description  text
             , cust_request_num  text
             , status text
             )
language sql
as $$ -- and so on

When I build this, if I relookup the function definition in datagrip the top part loses my white space I put in there and it looks like this.

create or replace function apqp.apqp_list_gentex_tool_report_getlist(_omni_search text DEFAULT ''::text, _cust_id integer DEFAULT 0) returns TABLE(reason text, program text, mold_number text, rev text, stone_part_number text, part_description text, cust_request_num text, status text, kickoff date, mold_delivered date, first_shot date, ppap_submission_date date, interim_approval_expiration_date date, tool_shop text, manager text, apqp_status text, apqp_list_id integer, comments text)
language sql
as $$

Is there a setting in postgres or someway to save to the database when I CREATE a function that it will save my white space at the top. I'd like to be able to repull the definition while maintaining my white space.


texts: Check existence with EXISTS outperform COUNT! ... Not?

texts:
I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT.

Yet in several recent scenarios I've measured a performance improvement when using count.
The pattern goes like this:

LEFT JOIN (
    SELECT
        someID
        , COUNT(*)
    FROM someTable
    GROUP BY someID
) AS Alias ON (
    Alias.someID = mainTable.ID
)

I'm not familiar with methods to tell what's happening "inside" SQL Server so I was wondering if there was a unheralded flaw with EXISTS that gave perfectly sense to the measurements I've done (could EXISTS be RBAR?!).

Do you have some explanation to that phenomena?

EDIT:

Here's a full script you can run:

SET NOCOUNT ON
SET STATISTICS IO OFF

DECLARE @tmp1 TABLE (
    ID INT UNIQUE
)

DECLARE @tmp2 TABLE (
    ID INT
    , X INT IDENTITY
    , UNIQUE (ID, X)
)

; WITH T(n) AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master.dbo.spt_values AS S
)
, tally(n) AS (
    SELECT
        T2.n * 100 + T1.n
    FROM T AS T1
    CROSS JOIN T AS T2
    WHERE T1.n <= 100
    AND T2.n <= 100
)
INSERT @tmp1
SELECT n
FROM tally AS T1
WHERE n < 10000

; WITH T(n) AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master.dbo.spt_values AS S
)
, tally(n) AS (
    SELECT
        T2.n * 100 + T1.n
    FROM T AS T1
    CROSS JOIN T AS T2
    WHERE T1.n <= 100
    AND T2.n <= 100
)
INSERT @tmp2
SELECT T1.n
FROM tally AS T1
CROSS JOIN T AS T2
WHERE T1.n < 10000
AND T1.n % 3 <> 0
AND T2.n < 1 + T1.n % 15

PRINT '
COUNT Version:
'

WAITFOR DELAY '00:00:01'

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT
    T1.ID
    , CASE WHEN n > 0 THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
LEFT JOIN (
    SELECT
        T2.ID
        , COUNT(*) AS n
    FROM @tmp2 AS T2
    GROUP BY T2.ID
) AS T2 ON (
    T2.ID = T1.ID
)
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope

SET STATISTICS TIME OFF

PRINT '

EXISTS Version:'

WAITFOR DELAY '00:00:01'

SET STATISTICS TIME ON

SELECT
    T1.ID
    , CASE WHEN EXISTS (
        SELECT 1
        FROM @tmp2 AS T2
        WHERE T2.ID = T1.ID
    ) THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope

SET STATISTICS TIME OFF

On SQL Server 2008R2 (Seven 64bits) I get this result

COUNT Version:

  Table '#455F344D'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#492FC531'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 81 ms.

EXISTS Version:

  Table '#492FC531'. Scan count 1, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#455F344D'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 76 ms.


texts: Adding id column of table to a hstore column postgres

texts:
I am trying to figure out how to store the id column of a table into a hstore column.  That way the id is stored in two places.  Going forward the table will be moved to another database and the current id will need to be preserved.

I have tried:

update new_table set hstore_col = hstore_col || hstore("id",id::text) where id = 1

This does not work  Any other suggestions adding the id column of a database table to a hstore column it has.


texts: Which Database to choose: MySQL or Oracle

texts:
I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables (file_per_table,file_format=Barracuda).

As the data can be compressed, I'm using a medium-blob column with compression enabled. In compressed form, that would generate about 1TB a year. So far, so good.

But is this the optimal selection? I want to use a free database - but of course there are alternatives like Oracle or maybe PostgreSQL.
The target machine running the DB would be Debian stable. I'm not sure either which file system would be best.

I'm open for any suggestions.


texts:
I'm designing a database for a review site.

There are products, and then for each product there can be multiple reviews.

I'm trying to add a subscription system, so that a user can subscribe to reviews and see the new ones when they login to the system.

This is an simple ER diagram with only the part of product and the review, I'm having doubts about how to do the subscription part.

How would you do it?

My thoughts are that first I have to save what the user subscribes to, something like this:

Would you do this in relation to the product or the reviewproduct? Also this is a many-to-many, so I would decompose in an associative entity.

Now my biggest doubt, I need to save the new reviews for the products that the user subscribed, how would you do this in the ER?

Thanks for the help,
Br,
Tiago


texts: Create gist index on cube array with 512 dimensions

texts:
I want to perform nearest neighbors search on db with 5000 entries of cube array with 512 dimensions.

I referred to this answer and used docker image of postgresql with cube array limit set to 2048 which can be found in the comment to the answer. But I'm getting the following error:

ERROR:  failed to add item to index page in "faq_c_idx"
SQL state: XX000

It works with dimensions=~200, but when I set higher dimensions I keep receiving the error above.

Here's the query:

CREATE TABLE mytable
AS
  SELECT i, cube(array_agg(random()::float)) AS c
  FROM generate_series(1,5000) AS i
  CROSS JOIN LATERAL generate_series(1,512)
    AS x
  GROUP BY i;

CREATE INDEX ON mytable USING gist ( c );
ANALYZE mytable;


texts: MySQL query INSERT INTO ... failed - The Table "x" is full

texts:
I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out.

Environment: Webmin/Virtualmin | MySQL version 5.1.73 on CentOS 6.5

Basically, Im getting an error that the table "Email_Messages" is full. I compare the "Email_Messages" table to other tables in the database and it has a cell count of around 232K but others have cell counts higher: one has 334K.

I still have 8GB free space on disk and googling has told me that there really isnt much of a limit on how big a table can be beyond the OS limitations. It looks like the database in question is only 203mb.

Any ideas on how I can troubleshoot this further/ increase the max table size?

Thanks so much - let me know what other information I can provide.


texts: How to write "Verify backup when finished" in T-SQL?

texts: In SQL Server one can cross an option under Reliability which says "Verify backup when finished", when one makes a backup through the management studio. How can I write it in T-SQL? (I want to write a job which do the backup so I need to put it all to code)


texts: ALTER DATABASE command to set log filegrowth and size?

texts:
Is it possible to alter the LOG file through script when creating a database?
I need to set the file size, growth etc, but NOT specify where the DB is to be created.
e.g.

CREATE DATABASE [NewDatabase]
GO
ALTER DATABASE [NewDatabase]
    MODIFY FILE
    (SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB);
GO
**ALTER DATABASE [NewDatabase]
    MODIFY LOG
    (MAXSIZE = 200MB,
    FILEGROWTH = 1MB
    );
GO**


texts: "custom archiver out of memory" error when restoring large DB using pg_restore

texts:
I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore.  The Heroku DB is using 9.1.7 and I have had the same result using 9.1.5 and 9.1.7 under OS X 10.8.

Using the command:

pg_restore --clean --no-acl --no-owner -d db_name -U username -h localhost -v db.dump

I get the following output on the command line ("cleaning" commands omitted) after 20 minutes and seeing the DB size grow to about 30 GB:

pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating TABLE video_data
pg_restore: creating SEQUENCE video_data_id_seq
pg_restore: creating SEQUENCE OWNED BY video_data_id_seq
pg_restore: executing SEQUENCE SET video_data_id_seq
pg_restore: creating DEFAULT id
pg_restore: restoring data for table "video_data"
pg_restore(11938) malloc: *** mmap(size=18446744071605936128) failed (error code=12)
*** error: can't allocate region
*** set a breakpoint in malloc_error_break to debug
pg_restore: [custom archiver] out of memory
pg_restore: *** aborted because of error

Turning up log level to DEBUG1 reveals the error:

...(many of these checkpoints hints)...
LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
ERROR:  extra data after last expected column
CONTEXT:  COPY video_data, line 463110542: "3599794500:59:01497 797942  2012    23422-08-96 2400892 08.794219   08 0617 2342118769-08 72    115 2046225 1..."
STATEMENT:  COPY video_data (id, video_id, views, favorites, comments, created_at, likes, dislikes) FROM stdin;

I have tried increasing SHMMAX and SHMALL to 2GB, checkpoint_segments to 30, as well as upgrading PG to match patchlevels.  A search reveals a small handful of people with this problem several years back, and no resolution.

Any recommendations?


texts: How to index this table (a_level, b_level, item_id)

texts:
I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only)

Lets say we have million of items all of them are INSERTed once and then only SELECTs are requested.

If i SELECT an item based on item_id, then i need to index the item_id column. This will make the MySQL to look all millions of items, which is bad, since i already have the a_level and b_level information. So i guess if i SELECT an item based on a specific level and i have an index on that column, the MySQL will not have to look all millions of items, just the items with that particular level.

If i INDEX both on a_level, b_level (and of course item_id) and SELECT WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on b_level and item_id and SELECT WHERE b_level= AND item_id= will be enough/the best solution?

So, since i have a_level and b_level (which any b_level as i said is "child" of only one a_level) what will be the most efficient SELECT and INDEXes created for picking up an item?


texts:
I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains?

For an example of what I need, consider this cursor:

DECLARE
  TEMP1 VARCHAR2(500);
  TEMP2 NUMBER;
  TEMP3 RAW(16);
  TEMP SYS_REFCURSOR;
  PROCEDURE MAKE_TEMP(TEMP_RETURN OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN TEMP_RETURN FOR SELECT DUMMY, 5, HEXTORAW('111') FROM DUAL;
  END;
BEGIN
  MAKE_TEMP(TEMP);
  LOOP
    FETCH TEMP INTO TEMP1, TEMP2, TEMP3;
    EXIT WHEN (TEMP%NOTFOUND);
    DBMS_OUTPUT.PUT_LINE('1: '||TEMP1);
    DBMS_OUTPUT.PUT_LINE('2: '||TO_CHAR(TEMP2));
    DBMS_OUTPUT.PUT_LINE('3: '||RAWTOHEX(TEMP3));
  END LOOP;
  CLOSE TEMP;
END;
/

If MAKE_TEMP were the function I'm trying to use, I would need to know that it has 3 values per row, and that the values are a VARCHAR2, a NUMBER, and a RAW in that order so I can define variables to hold the results.

I'm using Oracle 11.2.0.1.0, although I would appreciate if answers for newer versions exist as well.

Why I can't just look at the source

If you must know, the function is part or Oracle ASP.NET Membership Provider and is written by Oracle. I'm trying to debug a problem and would like to see what this function is returning. The function's source code starts like this:

create or replace
FUNCTION  "ORA_ASPNET_MEM_GETALLUSERS" wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
33

As you can see, it's (at best) going to be extremely difficult to extract any information about the cursor of that.


texts:
I've been reading on the promise of an ACID compliant database management system without sacrificing horizontal scaling capabilities.

Where is the catch? Which part of the CAP theorem did they choose to sacrifice? Or did they develop some new database theory solution to be able to cover the whole spectrum?


texts: How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

texts:
How do I use sys.dm_exec_connections in combination with SUSER_SNAME()?

I want to see the username corresponding to each record in sys.dm_exec_connections.


texts: Is it possible to backup my ubuntu postgresql database by copying the /var/lib/postgresql/9.1/main file and pasting it back in?

texts:
A developer working on my server has fiddled with some settings and knocked my postgresql database offline. I've been messing with it for an hour and am considering just reinstalling postgresql fresh because I know it will work easily then.

I know this is where I database is. /var/lib/postgresql/9.1/main. Can I copy this file, then paste it back in and expect it to work for me? Its for the same machine and everything.


texts: MongoDB service showing down but db is running

texts:
I have a strange problem.
I am using mongodb version 4.2.8 on Redhat Linux 7. Using Master Slave Replication. When I check mongodb service status (systemctl status mongod.service) it shows as failed(complete message mentioned below). But I can connect to db primary and slave and I can query , it is working. But why it shows as service as failed when I try to check using systemctl status mongod.service  command.
[mongod@bsbdigchdb01 ~]$ systemctl status mongod.service
● mongod.service - MongoDB Database Server
Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Wed 2020-06-24 03:21:19 IDT; 3 weeks 0 days ago
Docs: https://docs.mongodb.org/manual
Process: 2458 ExecStart=/usr/bin/mongod $OPTIONS (code=exited, status=2)
Process: 2454 ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb (code=exited, status=0/SUCCESS)
Process: 2449 ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb (code=exited, status=0/SUCCESS)
Process: 2447 ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb (code=exited, status=0/SUCCESS)
Main PID: 8482 (code=exited, status=0/SUCCESS)
Jun 24 03:21:19 bsbdigchdb01 systemd[1]: Starting MongoDB Database Server...
Jun 24 03:21:19 bsbdigchdb01 mongod[2458]: Error opening config file: Is a directory
Jun 24 03:21:19 bsbdigchdb01 systemd[1]: mongod.service: control process exited, code=exited status=2
Jun 24 03:21:19 bsbdigchdb01 systemd[1]: Failed to start MongoDB Database Server.
Jun 24 03:21:19 bsbdigchdb01 systemd[1]: Unit mongod.service entered failed state.
Jun 24 03:21:19 bsbdigchdb01 systemd[1]: mongod.service failed.
But when I  ps -ef |grep mongod  it displays running process.
[mongod@bsbdigchdb01 ~]$ ps -ef |grep mongod
mongod    2539     1  0 Jun24 ?        02:56:55 mongod -f /etc/mongodb1/mongod.conf
mongod    7352     1  0 Apr07 ?        11:28:57 mongod -f /etc/mongodb2/mongod.conf


texts:
I have an application that logs into SQL Server 2014 that uses a single login to access the database when a user opens up the application.  In other words I have 6 users using the same application with different logins, but the application itself is logging into SQL server with a single login.

One of my users is having issues.  When she logs into application the first time, she gets in fine, but if she tries to reopen it, it fails.  If she reboots her computer, she can log in one time again.  (Not acceptable)

The errors I'm seeing in the SQL Error logs are

  Error 18456; Severity 14; State 38
  Error 18451, Severity 14; State 1
  Error 18056; Severity: 20, State 23
  Error: 18456; Severity: 14 State: 23


texts:
I'm running the following query:

SELECT `type`, MAX(`time`) AS `last_updated` FROM `stocks` GROUP BY `type`

On the following table:

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `type` varchar(6) NOT NULL,
);

And I have the following indexes:

PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `time` (`time`)

Yet the query takes about 4 mins to run and there's only about 3.6 million rows.

Here is my explain:

+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | stocks | ALL  | NULL          | NULL | NULL    | NULL | 3684211 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+

Why aren't my indexes getting used? Should i have different indexes?


texts: use fulltext formsof inflectional to generate terms

texts:
Is there a way to use the FullText query components to generate the list of words in the FullText dictionary that are the inflectional formsof a word?

This query would give me matches based on the query.

select *
from keywords
WHERE CONTAINS(keyword, 'FORMSOF(INFLECTIONAL, migrate)')

I am interested in words that it's using as matches.
I want a list of words like

migrate
migrates
migration
migrations

Essentially the word's inflectional / conjugated forms.


texts: SQL Server 2008 query planner failing after index drop & recreate

texts:
Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now after this one the SQL Server query plan cache is behaving oddly.

Execution planner reveals that wrong indexes are being used wrongly that return millions of rows, when only a few should match. When the indexes are correct, the execution plan shows that SQL Server resolves to use index Scan as opposed to index Seek providing much less than optimal results. With tablehints like WITH(INDEX(indexname)) or WITH(FORCESEEK) in the proper places, this can be corrected. INNER LOOP JOIN also fixes some of these.

However, the problem is that even when these new, filtered indexes are dropped and recreated as they were before, the query plan remains the same. Query plan cache has been cleared, DB has been restored to a different environment, statistics have been updated and obviously the indexes have been rebuilt so they're not fragmented.

This is currently a critical issue that nobody has any idea how to fix. While we can force SQL Server to use the correct plans, it's simply not a solution to the multitude of software that would have to be updated with it, and obviously a DB where you need to manually point out how to handle queries is not an option.

So any help would be greatly appreciated.

Edit:
We managed to fix one query by dropping the indexes, recreating them again as filtered, then running an UPDATE STATISTICS tablename WITH FULLSCAN. This fixed some of the problem and two joins were working correctly. After this we had to do a separate alter to a multi-column index that was in no way involved in the original index script, to include one of the columns used in the join. These two changes together enabled the query planner to resolve to use the correct indexes with Seek rather than Scan.

The theory right now is that due to an earlier crash, the DB was apparently restored by deleting the underlying DB and then creating a new one from backup, rather than just using REPLACE as before. This would have somehow disconnected masterdb's metadata such as execution plans, all cache and whatnot from the DB resulting in a new massive database with no existing plans to handle queries. This, grouped with an apparently failed statistics update on the newly created indexes would have produced a scenario where SQL Server had no idea how to resolve the various queries it was being bombarded with.

I'm not convinced however that this will still be enough, since some of the behavior such as having to alter the multi-column index, and the fact that no statistics update was ever necessary in any of the prior testing environments in the past 2 months of testing, seems to let on that there's something else that has gone wrong.


texts: Why does a parameterised query lead to an index scan but hardcoded values leads to index seek?

texts:
I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause.

It comes down to 2 child tasks in the execution plan. The hardcoded query can perform an index seek but the parameterised query does an index scan. Since the table contains a million rows the difference is massive.

The query is pretty ugly...

DECLARE @MethodOfDelivery nvarchar(3) = 'AAA'
DECLARE @OutwardPostcode nvarchar(4) = 'BBB'
DECLARE @InwardPostcode nvarchar(3) = 'CCC'

SELECT TOP 1
    slr.*
FROM
    [dbo].[SortLevelRecord](NOLOCK) slr
    INNER JOIN dbo.RoutingDataVersion(NOLOCK) v ON v.Version = slr.Version
    INNER JOIN dbo.Routing(NOLOCK) psr ON
        (
            slr.SortLevelKey = psr.Day1SortLevelKey OR
            slr.SortLevelKey = psr.Day2SortLevelkey OR
            slr.SortLevelKey = psr.Day3SortLevelkey OR
            slr.SortLevelKey = psr.Day4SortLevelkey OR
            slr.SortLevelKey = psr.Day5SortLevelkey OR
            slr.SortLevelKey = psr.Day6SortLevelkey OR
            slr.SortLevelKey = psr.Day7SortLevelkey OR
            slr.SortLevelKey = psr.Day8SortLevelkey OR
            slr.SortLevelKey = psr.Day9SortLevelkey OR
            slr.SortLevelKey = psr.Day10SortLevelkey OR
            slr.SortLevelKey = psr.Day11SortLevelkey OR
            slr.SortLevelKey = psr.Day12SortLevelkey OR
            slr.SortLevelKey = psr.Day13SortLevelkey OR
            slr.SortLevelKey = psr.Day14SortLevelkey
        ) AND
        psr.Version = v.Version AND
        psr.MethodOfDelivery = slr.MethodOfDelivery AND
        psr.OutwardPostcode = @OutwardPostcode AND
        (
            psr.InwardPostcode = @InwardPostcode OR
            psr.InwardPostcode = SUBSTRING(@InwardPostcode, 1, 1) OR
            psr.InwardPostcode = ''
        )
WHERE
    slr.MethodOfDelivery = @MethodOfDelivery
ORDER BY
    LEN(psr.InwardPostcode) DESC

The index scan/seek is on the Routing table.

Something else that seems a bit strange is that the parameterised query uses Parallelism to gather streams but there are no parallel operations, only 1.

This is on Azure SQL server. I'm not sure what version that correlates to.


texts: Should I increase max_connections in AWS RDS t1-micro for MySQL?

texts:
I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max value by creating a DB parameter group for this Micro instance.

My confusion is

Should I increase the max connection value for micro in DB parameter group? or Should i consider upgrading to next RDS level which provides more maximum connections (125)?
Should I increase max_connections on micro RDS to 125 vs upgrading to RDS small instance?
Why and What factors should I make the decision on?

Thanks


texts:
I work in an environment where logins are shared among applications, and occasionally developers - in a situation like this, I am having trouble determining who is connected to the database, or who is running particular queries.

The most specific information I can find seems to be host_names, or an IP address.

I am wondering, if there is a way to correlate this information with a user?


texts: Wise to use trigger to update another table?

texts:
I have an Object table which is populated from an Integrated service (which I can change if needed) from another database. At certain points we need to manually add posts in another table ObjectObjectGroup (ObjectId, ObjectGroupId) which is needed if Object.ObjectType have a certain integer value. Since the integration service doesn't handle that kind of update, I'm thinking of adding a trigger to the Object table which in pseudo-code would be the following:

if Object.ObjectType = 10
    begin
        if Object.ObjectNumber like '<string pattern>'
        begin
            insert into ObjectObjectGroup values...
        end
    end

Is this setup wise, or is there a better way in terms of performance?


texts: How to increase agent history rendering timeout in SSMS?

texts:
We keep only 30 days of SQL agent job history but due to the frequent execution of jobs, SSMS will experience the following timeout error when trying to view history for a given job:
Execution Timeout Expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.
(.Net SqlClient Data Provider)

Is there a way to increase this timeout in SQL Management Studio 18.7.1?


texts:
How to get last login time of selected IDs?

Table

|  user_id  |  lastlogin
|  100      |  2016-03-05
|  110      |  2016-03-21
|  100      |  2016-03-29
|  100      |  2016-04-03
|  110      |  2016-04-02

Here is what I need:

|  user_id  |  lastlogin
|  100      |  2016-04-03
|  110      |  2016-04-02

I have tried this:

SELECT DATE_FORMAT(lastlogin, '%d-%b-%Y') AS lastlogin,user_id
FROM last_logins
WHERE user_id IN (
100,110)
GROUP BY user_id
ORDER BY lastlogin DESC

If I use GROUP BY, it shows the old login record of user_id. If I use just ORDER BY it gives perfect results, but with multiple last logins of user_id.


texts:
The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units:

SELECT
    reports.*
FROM
    reports
    INNER JOIN
        units
        ON units.id = reports.unit_id
WHERE
    reports.unit_id IN (1111, 1112, 1113)
    AND
    (
        reports.id =
        (
            SELECT reports.id
            FROM reports
            WHERE reports.unit_id = units.id
            AND
            reports.time_secs != 0
            AND
            reports.latitude != 0.0
            AND
            reports.longitude != 0.0
            ORDER BY time desc
            LIMIT 1
        )
    )

This query took several minutes to run, and I was wondering if there is an optimization I can do to it.


texts:
Sooner or later, a db developer will come across, and possibly use, a naming scheme for many objects like foreign keyes, default constraints etc.

Let's consider this example: Name all foreign keys as:
FK__[name of source table]__[name of source col]__ref__[name of master table]__[name of master column]

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?