texts: How to structure a database to show data for each USA State and Canada Territory on a Per user basis?

texts:
I need to build a PHP/MySQL application.  I am a bit confused on how to do the database for a project like I am about to explain though.

The end result will be a Map of the USA and Canada.  Each state will have a popup with data for that state.  Below is the data items I need to show for each state.

Contacts (integer)
Pickups (integer)
Interviews (integer)
Guest Posts (integer)
Book Review Opp (integer)

Now to complex it a little more, each map page will show the data from above only for a particular User and then for a particular "Campaign" that the user owns.

Every user will have a map page with data only relevent to the campaign they are viewing.

I am not sure on how to structure a MySQL table for the "campaigns" and have it store data for the above items for each state or region of Canada.

Any ideas how to structure the database?

The image below is just an example, I need to be able to set these items

Contacts (integer)
Pickups (integer)
Interviews (integer)
Guest Posts (integer)
Book Review Opp (integer)

For every State, also each users campaign will mean a separate map where each of these items can be set again for each state


texts: Expensive Insert Operation pause Other Insert Operation

texts:
I am continuously inserting value into  3 table having same structure

Table1 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float,
    col7 numeric(18,6),col8 numeric(18,6),col9 float,col10 datetime,col11 int,col12 int,col13 bit,col14 varchar(20),col15 varchar(10))

Table2 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float,
col7 numeric(18,6),col8 numeric(18,6),col9 float,col10 datetime,col11 int,col12 int,col13 bit,col14 varchar(20),col15 varchar(10))

Table3 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float,
col7 numeric(18,6),col8 numeric(18,6),col9 float,col10 datetime,col11 int,col12 int,col13 bit,col14 varchar(20),col15 varchar(10))

I Store Data in Table1 for 1 day in Table2 for week and in Table3 for One Month On every day at midnight 1 am I delete yesterday data from Table1, 7 days ago data from Table2

when month change I change month table table3 (table3_jan,table3_feb etc)

I have set Non-index on all tables(Table1,Table2 etc.) (on column col2(bigint) and col4 (Datetime))

Currently I have 30 GB data in table3_sept and on daily basis it goes increases

data get inserted rapidly in above 3 table

Now when I fetch more than 7 days Data from Table3 and insert into #temp or other table all other insert operation get paused when query get execute then insert operation get resume.

select * into #temp from table3_aug where date between '1 aug 2017' and '17 aug 2017' order by id desc

or

select * into tablenew from table3_aug where date between '1 aug 2017' and '17 aug 2017' order by id desc

for fast result view I just created this structure. users only fetch one day data mostly and week day data then months data rarely. Previous I have Only one Table but for result view I just changed this

So my quetion is how to avoid that pause process Is their any wrong table structure I have created Kindly help me on high priority.Thanks


texts: Intellisense not working because of code creating columnstore index

texts:
I use SQL Server 2016 Developer Edition on Windows 10 64 bit. All the databases are local on my laptop.

I have a SQL script file, and Intellisense doesn't work on it. I have enabled Intellisense and tried various fixes suggested in this post:

IntelliSense not working but it is enabled

None of these fixes work for me; however, Intellisense works on all the other files. The only special thing of this file is its length: it has more than 600 lines of code.

It seems that the problem is caused by a command creating columnstore index. Here is a sample code:

SELECT TOP 100 *
INTO #test1
FROM tab1

CREATE CLUSTERED COLUMNSTORE INDEX idx ON #test1

SELECT TOP 100 *
INTO #test2
FROM tab1

I found that Intellisense stopped working after inserting the block of code in the middle! The outlining will also disappear after inserting the middle block of code.

Once I remove the middle block of code, the outlining and Intellisense will work again.

Can anyone help me test this sample case?

--

I tried to add semicolon an GO to separate code, but neither of them works for me. Now it seems that the only feasible solution is commenting that middle block out.


texts: Remove duplicates in SQL Server Group By

texts:
I have 2 tables, Product and Type, the tables looks something like this:

Product
-Id
-Name
-TypeId

Type
-Id
-Name

I want to be able to write a SQL Server query which outputs the products grouped by Type:

+------------+------------+
| Bikes      | Product 1  |
|            | Product 2  |
|            | Product 3  |
| Clothes    | Tshirt 1   |
|            | Trousers 2 |
|            | Hat 3      |
|            | Shoes 4    |
| Electrical | Tv         |
|            | Radio      |
|            | Laptop     |
+------------+------------+

If i write:

SELECT Type.Name,Product.Name
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
ORDER By Type.Name

I get similar results however the type value is included in every column.

+------------+------------+
| Bikes      | Product 1  |
| Bikes      | Product 2  |
| Bikes      | Product 3  |
| Clothes    | Tshirt 1   |
| Clothes    | Trousers 2 |
| Clothes    | Hat 3      |
| Clothes    | Shoes 4    |
| Electrical | Tv         |
| Electrical | Radio      |
| Electrical | Laptop     |
+------------+------------+

Using SQL Server is there any way i can only show the first type value?


texts: SqlPS and PowerShell with Import-Module SQLPS different behaviors

texts:
I'm trying to write an automated installation script in powershell for SQL Server Express 2014. I'm stuck with a stupid issue when trying to enable the TCP protocol in the SQL instance.

What I'm doing is the following:

Import-Module "sqlps" -DisableNamechecking

$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer').

# List the object properties, including the instance names.
$Wmi

# Enable the TCP protocol on the default instance.
$uri = "ManagedComputer[@Name='JOHN-PC']/ ServerInstance[@Name='DEMO']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $false
$Tcp.Alter()
$Tcp

The issue I have is when I run the script in a standard powershell (run as administrator) the list of instance of my $wmi object is empty.

If I run the same commands (except the first one to load the module) in SqlPS, it works as expected, with my instances being populated correctly.

Unfortunately, I cannot run the whole script in SqlPs, because the script itself installs SQL Server.


texts:
I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to

design a table when both data for basketball and football are stored
separate the stats for basketball and football completely
create a base table and store common information there and then split them into 2 different tables where they start to differ from each other

my main concern with option 1 is that down the road, the table might become too complex and too many optional/nullable columns

number 2 might be overkill? i like reusing things whenever i can.
number 3 - not sure how this would look like...


texts:
I'm running MongoDB 3.4.7 cluster (primary + 2 secondaries). Primary's cache usage is 95%, and I try to figure out what the cache contains.

I ran collStats for all collections (±18,000) and summarized the "bytes currently in the cache" metric. The total is ±16GB, which is only about half of the cache size (which is 30GB).

When running db.serverStatus(), there seems to be two relevant metrics:

"bytes belonging to page images in the cache" : 15933358790, // ±16GB
"bytes not belonging to page images in the cache" : 14141393512, // ±14GB

The first one might explain the 16GB summary of the collection cache, however I wonder what the second metric means - what else is MongoDB / WiredTiger store in the cache, and how can I break it down?

Any ideas?


texts:
i always had this question and i hope you can help me with this, how should i index created_at if i need to order results based on date.

Example:

COMMENTS TABLE:

ID (UUID)
ENTRY_ID (UUID)
USER_ID (UUID)
CONTENT
FEATURED
STATUS
CREATED_AT

I have this indexes:

PRIMARY: ID
INDEX: ENTRY_ID
INDEX: ENTRY_ID | CREATED_AT (to get latest entry comments)
INDEX: USER_ID
INDEX: USER_ID | CREATED_AT (to get latest user comments)
INDEX: STATUS | CREATED_AT (to get comments marked as spam ordered by older first)
INDEX: CREATED_AT (to get latest comments in entire db)

Is it right or i can skip some indexes?


texts: SQL Server 2008, switching to simple recovery mode

texts:
Currently our transaction log is 50 GB; impressive huh?

I want to switch to simple recovery mode and remove the transaction log entirely.

Do I need to stop any user activity and detach the database. Or can I simply go into management studio and change the recovery mode.  How do I then remove the file ?

Edit:

I switched the live server to simple recovery mode, and then performed a shrink file. Has worked perfectly so far. The file is now 1mb.

(I  am aware switching to simple mode will restrict recovery : I won't be able to go to s specific point in time, only to when the last back up was.)


texts: Switching values in a column with one update statement

texts:
You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one update query to resolve this.

This question was asked at a recent interview I had, and I'm going into more interviews that may have similar questions so I wanted to get an idea of how to handle this.


texts: MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?

texts:
I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and generating errors. As a result, functionalities based on these queries are breaking. Below is the example query to run in Windows and Linux:

Select A.id,A.title from tablename a;

This query will work in Windows while never work in Linux.

Is there any way through which I can avoid these case sensitivity in MySQL
queries and no need to check and fix every query ?


texts: How can I compare the server impact of transaction replication

texts:
We are developing a system that must extract data from an existing SQL Server 2008 R2 cluster.  One of our design goals is to limit the performance impact on that cluster as much as is practical.

Our system must have access to data that is < 1 minute old.  So we are considering both a straightforward SELECT from the source clusterexecuted at regular intervals (say every 20 seconds), or alternatively, establishing transaction replication from the source cluster to our own system for subsequent processing.

We have a test instance of the cluster, and I would appreciate any recommendations on how to measure impact on the source cluster for each of these functions for the purposes of making an architectural decision.

I'm familiar with the use of the profiler, and that seems like a reasonable tool for analyzing the impact of the SELECTs,  but I am running into difficulty when trying to find similar impact data for replication.


texts: SQL Query, get count() to output null instead of 0

texts:
I have the following query, which basically counts the how many orders a customer has done:
SELECT name, count(order_id)
FROM customers
LEFT JOIN  orders
ON customers.name == orders.customername
GROUP BY name;

Output is something like this:
-------------
|adam |    2|
|bob  |    0|

Here bob had no entries in the orders table. So I believe the left join creates a table where adam has his order columns filled but bob does not.
But I want 0 to instead be NULL. Like this:
-------------
|adam |    2|
|bob  | NULL|

How can I do this?


texts:
Our team have hard time. Situation:

API went down, all infrastructure (API + MongoDB) terminated.
We restore API and mongodb (data restored from backup).
After a while (let's say 1 day) we discovered we used not latest backup, but backup from month ago. We cannot just shutdown API and restore from latest backup, because users already created data for 1 day.

State: on our prod DB 1 month of production data has been lost. But in the same time we have this gap inside backup.

Question: what is best solution to merge missing data to existing prod? Better with no downtime.

P.S. Because if this we had to create some data manually, for example, recreate some users. What gonna happen when we merge these 2 DBs?


texts: PL/SQL Script Line with Only a Dot...What is the purpose/function of the dot?

texts:
I have inherited a pl/sql script from a previous dba that worked at my company. It is used to "clean up" failed distributed transactions. The script is easy enough to understand, except for one line that only contains a dot, "." I have not seen this before...what is the purpose/function of the dot line?

set define on
set scan on
set verify off
set serveroutput on

undefine IncidentNumber

spool &&IncidentNumber

connect / as sysdba
whenever sqlerror continue
set serveroutput on size 1000000
set linesize 200
DECLARE
   /* &&IncidentNumber */
   pragma autonomous_transaction;
   l_sql_statement       VARCHAR2(255);
   l_fdist_found         BOOLEAN := FALSE;
   --
   CURSOR cfdist IS
     SELECT local_tran_id tranid, upper(state) state
     FROM dba_2pc_pending;
   --
   BEGIN
      --
      FOR rfdist IN cfdist LOOP
          l_fdist_found := TRUE;
          if rfdist.state = 'PREPARED' then
              l_sql_statement := 'ROLLBACK FORCE '''||rfdist.tranid||''''||'/*&&IncidentNumber*/';
              DBMS_OUTPUT.PUT_LINE(l_sql_statement);
              EXECUTE IMMEDIATE l_sql_statement;
              COMMIT;
          end if;
          DBMS_OUTPUT.PUT_LINE('Executing DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('||rfdist.tranid||')');
          DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(rfdist.tranid)/*&&IncidentNumber*/;
          COMMIT;
          IF l_fdist_found = TRUE THEN
              DBMS_OUTPUT.PUT_LINE('Rolled back distributed transaction id '|| rfdist.tranid);
          END IF;
      END LOOP;
      --
      IF l_fdist_found = FALSE THEN
          DBMS_OUTPUT.PUT_LINE('No failed distributed transactions were found.');
      END IF;
      --
     EXCEPTION
       WHEN OTHERS THEN
           RAISE_APPLICATION_ERROR(-20001, 'Error:'||SQLCODE||':'||SQLERRM);
   END;
.
/


texts: Is it possible to restore a SQL Server Linux Backup on SQL Server Windows

texts:
I am running SQL Server in docker on a mac. I have taken a SQL Server backup from windows and restored it in the mac SQL Server docker instance. Now I want to take a backup from the SQL Server docker instance and restore it back to windows. When I attempt to restore on Windows I am getting a "System tables sysfiles1 is corrupted".
Is it possible to take a back up from SQL Server Linux and restore it on SQL Server Windows?


texts: Writing a simple SELECT Stored Procedure in Oracle PL/SQL

texts:
This is my SQL Server Stored Procedure which is below:

CREATE PROCEDURE passenger_details
AS
BEGIN
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No
END

EXECUTE passenger_details

The above stored procedure in SQL Server works successfully.

Then I tried to execute the same stored procedure in Oracle PL/SQL which is shown below:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_passenger_details FOR
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No;
END passenger_details;

The above stored procedure in Oracle PL/SQL is compiled successfully.

Then I tried to execute it which is shown below:

SET SERVEROUTPUT ON;
EXECUTE passenger_details;

While trying to execute the stored procedure, I'm getting the following error message which is shown below:

Error starting at line : 12 in command -
BEGIN passenger_details; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PASSENGER_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


texts: Index creation before or after loading data

texts:
I have a situation where I need to create a table in Postgres and add approximately 3 million rows. Currently I am adding an index to one field after all the data is loaded, but I was wondering whether adding an index before loading the data (at the time the table is created) is better, worse, or neutral.

What is considered best practice for when indexes should be added to tables in Postgres? Does Postgres pause indexing during transactions?


texts:
I've successfully added a ROW_NUMBER() function to my SELECT statement, but I get an "invalid column name" error when I try to reference the column I generated with the ROW_NUMBER() function in my WHERE clause by referencing the [RANK] alias.

Query

SELECT DISTINCT
      ROW_NUMBER () OVER (PARTITION BY T0.DocNum ORDER BY T0.DocNum) [RANK],
      T0.DocNum,
      T3.[PickIdNo],
      T0.[NumAtCard],
      T0.[U_STS_SalesProg],
      T0.[DocDate],
      T0.[DocDueDate],
      T0.[TaxDate],
      T0.[CardCode],
      T0.[CardName],
      T6.[SlpName],
      T8.[firstName][CSS],
      T4.[U_BOTYPE],
      T1.LineTotal BackOrderTotal,
      T2. OrderTotal
FROM ORDR T0
INNER JOIN
(SELECT
      T0.DocEntry
      , ROUND(SUM(T0.[OpenQty]*T0.[Price]),2) LineTotal
FROM RDR1 T0 where  LineStatus ='O'
GROUP BY T0.DocEntry) T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN
(SELECT
      T0.DocEntry
      , ROUND(SUM(T0.[Quantity]*T0.[Price]),2) OrderTotal
FROM RDR1 T0
GROUP BY T0.DocEntry) T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN RDR1 T3 ON T0.[DocEntry] = T3.[DocEntry]
INNER JOIN OCRD T4 ON T0.[CardCode] = T4.[CardCode]
INNER JOIN OTER T5 ON T4.[Territory] = T5.[territryID]
INNER JOIN OSLP T6 ON T4.[SlpCode] = T6.[SlpCode]
INNER JOIN OITM T7 ON T3.[ItemCode] = T7.[ItemCode]
INNER JOIN OHEM T8 ON T4.[DfTcnician] = T8.[empID]
INNER JOIN OITW T9 ON T7.[ItemCode] = T9.[ItemCode]

WHERE Rank = '1' -- <<== This is where it fails
ORDER BY T8.[firstName], T0.[CardName], t0.DocNum


texts: Newly created user has rights on MySQL database

texts:
I use the root to create a new user, like this:

I have no more operations on it. Then I use this user to log in:

I try to do some operations like this:

Here "test" is a database which I created before. Why can I do all the operations? I have not done any grant operation on the test user. When I come back to use the root user to log in, I also cannot revoke the privileges on test(database) for test(user), like this:

I am really confused by these things, and this is my first time to ask question on StackExchange. My English is also not very good. If you cannot understand what I am talking about just tell me, I will reply soon.


texts: Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave

texts:
Thanks in advance.

I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. Ideally, the solution needs to allow for Master to Master replication/automatic failover with each Master replicated to its own Slave. The Slave does not need to be promoted to a Master in any scenario, but it does need to be updated from its Master.

In .Net, and with SQL Server, it was always easy to set up mirroring/failover with a witness and then simply incorporating the "failover" server directly in the configuration file's connection string. MySQL seems to be much more involved.

I've read a good bit about MySQL replication and the potential pitfalls it may include and I would like to avoid those if possible. I've also started looking into MySQL clustering, but I'm not sure that is the best solution here since the servers are setup to be separate from each other in a primary/secondary set (with the Master in each being replicated).

Ultimately, it is just Primary Master <-> Secondary Master and then a Slave behind each Master. If there is a better solution such as using all four servers (even though separated by Primary/Secondary domains) with clustering etc. please reply also - just trying to determine the best setup.

I should note that this is for installation on Windows Server 2012 R2.

It is difficult to find this setup anywhere through searching!

Any help is greatly appreciated!


texts: Reset mongo cache memory for test perfomance - windows

texts: recently i'm working on mongo db and I'm trying to check the performance of mongodb with 2 million record. I need to clear cache every time I start the test. I don't want to restart the computer/server each time I want to run the test. I tried to clear collections cache but the result wasn't as I expected. Is there a way to clear cache like the way exist in linux ?


texts: Multiple Merge Replications on same SQL Server Database Conflicts

texts: I have 2 separate merge replications on the same database on a server, one named 'POC' with a single subscriber running continuously, and another named 'Clubs' with ~50 subscribers. As of recently, the Club subscriptions have been coming up with errors saying 'The subscription to publication 'POC' has expired or does not exist'. It seems to be confusing the publications, but the sysmergesubscriptions and MSmerge_agents tables still point to the correct publications. Odd one - anyone seen something like this previously?


texts:
I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C)

I have tried something like this which does work if I have only the first select statement but I need to exclude some records by using the second select statement.

update tableA set value1 = 'Y'
where tableB_ID in
(
    select ID from tableB
    where class in ('1','2')
)
and tableC_ID in
(
  select ID from tableC where name not like 'MPG%'
)


texts: Queue Implementation with Custom Consistent Absolute Ordering

texts:
I need to create a schema for a queue. Elements in this queue will be processed in an order based on a column. However, this is not always the e.g. date_added column.
Users should be able to freely re-order items in the queue, thus setting a custom position - let's call that field position. Therefore, queue items that have a position >= to the new position I set for a specific item need to be re-ordered on each update.
I really want to avoid using a server-side script to manually go through each of the now outdated elements and updating their position.
Is there a way to do this automatically, and with relatively low resource usage? I'm sure people have implemented such a queue in MySQL before, but there don't seem to be relevant answers anywhere, although the task sounds trivial. Any ideas?
I know I shouldn't rely on the order the records are stored in. But I am maybe considering using ALTER TABLE ORDER BY. How bad is this really? And how useful is it in achieving my goal?
There's some PK, likely an auto-increment, or maybe the date_added with more precision. The version is InnoDB, MySQL >= 5.6.
The queue position can be changed an unlimited number of times. It should work without failure. The UI with HTML and JS is trivial: On any change of immediate hierarchy within a container, you just read the order of elements with a specific attribute. It's also pretty common. Possibly hundreds of items in the list.
By "outdated elements" I mean those that no longer have the position field set correctly, and need to be "reindexed". Naturally, something in MySQL would deal with that.
As for finished, I need those to be retrievable too, so they stay in the table, and are marked as done with a boolean field. There is another table that takes care of more complicated status transition, as it is not a part of what makes a queue.


texts:
For every system Alert defined to send an email, we receive a 2 alerts with the following bodies. How do I stop the message with the empty (None) description?

Empty Alert

SQL Server Alert System: 'Severity 010' occurred on \my-server

  DATE/TIME:    1/30/2013 10:22:59 AM

  DESCRIPTION:  (None)

  COMMENT:  (None)

  JOB RUN:  (None)

Valid Alert

SQL Server Alert System: 'Severity 010' occurred on \my-server

  DATE/TIME:    1/30/2013 10:22:59 AM

  DESCRIPTION:  Log was backed up. Database: [mydatabase], creation date(time): 2011/10/03(17:20:11), first LSN: 859915:34267:1, last LSN: 859915:192686:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\my-backup-server.domain.local\sqldump\my-server\mydatabase\LOG\my-server_mydatabase_LOG_20130130_114000.trn'}). This is an informational message only. No user action is required.

  COMMENT:  (None)

  JOB RUN:  (None)

Here is our definition for the "Severity 10" Alert.

EXECUTE msdb..sp_add_alert @name = N'Severity 010'
  , @message_id = 0
  , @severity = 10
  , @enabled = 1
  , @delay_between_responses = 60
  , @notification_message = NULL
  , @include_event_description_in = 1
  , @database_name = N''
  , @event_description_keyword = N''
  , @job_name = NULL
  , @performance_condition = NULL
  , @wmi_namespace = N''
  , @wmi_query = N''
GO
EXECUTE msdb..sp_add_notification N'Severity 010', N'Project Support', 7
GO


texts: Get actual Job Name from Program Name column of the results while running sp_whoisactive or sp_who2

texts:
I am running sp_whoisactive on my Prod and offload the data for analysis.
When I run sp_whoisactive or sp_who2 and if there is any SQL Agent Jobs running it would always display the Program Name like SQLAgent - TSQL JobStep (Job 0x984ECB0C94C14943B787BDE548F77311 : Step 1).
I want  the job name to be displayed there instead of those numbers.
I have to  manually copy that value and run it against select * from msdb..sysjobs where job_id = 0x984ECB0C94C14943B787BDE548F77311
Can any help me with this.
PS: tried to substring only the hexadecimal and convert it to uniqueidentifier and then run it to on the sysjobs tables as a variable but didn't work.
--This is what I tried to do

declare @substring nvarchar(200)
select @substring = (SUBSTRING(@program,30,34))
select @substring

declare @jobname nvarchar(200)
select @jobname = (select name from msdb..sysjobs where job_id = convert(uniqueidentifier,@substring))

select @jobname

--Error message

Msg 8169, Level 16, State 2, Line 22
Conversion failed when converting from a character string to uniqueidentifier.


texts: Replace value based on previous row / next row depending on conditions


texts: We setup ALTER Index job with Ola's maintenance solution, the job is failing with below error, the job is failing with locking,


texts:
I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.

Can partitioning the table help reduce the completion time?

If so, can I partition an empty table by order year and then insert into the orders table from the CTE. Or will I need to create a partition on the items table since the CTE is rolled up from the items table?

Also my estimated number of rows differ significantly from the actual number of rows.


texts: Is there a way to use a SQL Server 2000 database in SSIS 2012?

texts:
I just got my first chance to install SQL Server 2012 on a test machine.  I also installed all of the management and Visual Studios components.  I created a copy of one of my SSIS 2008 packages and converted it to 2012.  The conversion worked jsut fine, but I'm now getting an error stating that connections to SQL Server 2000 and earlier versions are no longer supported.

Is there a workaround for this?  Is there another way to create a connection to a 2000 database?  We're not currently in a place to move this database off of 2000.

The connection is currently set up as an OLE DB connection for a couple of data sources.


texts: Optimze performance for LIMIT / OFFEST on collected result of multiple queries

texts:
I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly:

SELECT 1 : ~5 % rows
SELECT 2 : ~5 % rows
SELECT 3 : ~90 % rows

I can use UNION and ORDER BY and a RANK column, but it's very slow because Postgres collects rows from all subqueries and applies ORDER BY RANK on the result set.

Is there a way to have SELECT 1 executed, and only if that does not provide enough rows SELECT 2 is executed, etc.


texts: SQL Server: Cannot connect remotely after changing server name successfully

texts:
My local computer's operating system is Windows 10 Professional.

We have a remote server named XXXXAD01. Its operating system is Windows Server 2016 Standard. It is part of a Domain ISYYYY.ZZZZZ. It has 2016 SQL Server installed as MSSQLSERVER (default instance).

I renamed the remote server's SQL Server to XXXXAT01 using this procedure:

I made remote desktop connection to it.
Inside I open SQL Server Management Studio 18 and connect to the
server using Windows Authentication.
Execute sp_dropserver XXXXAD01; go command.
Execute sp_addserver XXXXAT01, local; go command.
I open SQL Server Configuration Manager and restart the SQL Server
service (MSSQLSERVER).
Confirm in SQL Server Management Studio that @@SERVERNAME is now
returning XXXXAT01.

Then the remote server itself was renamed to XXXXAT01 by the network guys. And rebooted.

Now here is the issue:

I can make remote desktop connection to XXXXAT01 and from inside I can open SQL Server Management Studio and connect to XXXXAT01 server using Windows Authentication and run my queries.

But if open my local SQL Server Management Studio from my PC, and try to connect to XXXXAT01 server using Windows Authentication, I get this error:

  Cannot connect to XXXXAT01.

  Additional information: The target principal name is incorrect. Cannot
  generate SSPI context. (Microsoft SQL Server)

What could be the reason and what is the solution?


texts: Is this a sane model for a network provisioning system?

texts:
I'm sketching out a model for a network setup, where I have the following conditions:

There are multiple nodes which contains a number of switches each.
Each switch has multiple services, which may or may not have a rate limiting.
Each service is identified by a VLAN ID (VID), which is different on each switch per node.
VID cannot be re-used for other services across switches, but it is permitted to re-use the VID for the same service on switches on a different node.
The rate limiting for each service may be overridden by the profile assigned to the CPE.
A CPE can have any number of ports, each with its own service, and possibly rate-limit. VID and default rate-limit is determined by service, as noted above.

Example on the service-switch-VID restrictions: The "Internet" service has VID 1 on switch 1, and VID 11 on switch 2 for node #1, and #2. But VID 21 and 31 for switch 1 and 2 on node #3

Please let me know if you need any additional information. Almost 3am here, so I might have forgotten something.

Update: Fixed the list, and updated the model to reflect the changes I've highlighted in my first comment. Also noticed a missing FK relation, between cpe and cpe_port`, which I've added.


texts: How subscriber table updated during snapshot replication?

texts:
I am testing a snapshot replication process (SQLServer 2008 (yes, unfortunately...)).

Everything is fine but I can't find in MS documentation how are tables updated.
I saw that the publication process drops tables, then recreates them, and then populates it with data.

OK, nice.
But is there a kind of security (locks, transactions...) that prevents a query to be made during this synchronization (in particular just between the drop table and the create)?

Let's imagine a database A (publisher) having a table T replicated on another database B (subscriber).
When the distribution agent is running and table T is updated on server B (drop table T + create table T + bcp-in data ?), during a few milliseconds the table won't be there. If an application tries to read table T, it will get an error.

When I try to launch a trace on server B when distribution agent is running, I can't see any transaction made for this step. And indeed I can make a select on a replicated table T on server B (and get an error).

Is it really how it is supposed to work ?

Are there settings somewhere to avoid such behaviour ?


texts:
Can anyone discuss how to exactly incorporate DDL Schema changes with Always On Availability Groups, step by step? We have a Primary Replica in one state, and Secondary Replica in another state location. The secondary replica will be read-only asynchronous.
If I were to make schema changes, including any wide variety...
Examples

Add/modify/delete columns on table
Add/modify/delete table foreign key constraints
Add/modify/delete clustered and non-clustered indexes
Add/modify/delete default constraints
Add/modify/delete stored procedures and functions
Add/modify/delete triggers
Add/modify/delete view

... what would I need to ensure Primary Replica DDL flows to the Secondary Replica smoothly?
My Hypothesis
Read queries on secondary replica will not affect Primary DML (data modification, inserts, updates, deletes) and will flow smoothly, since secondary replica is set to Read Snapshot Isolation.
Read queries on secondary replica will affect primary DDL (schema Changes, table structure changes), since read queries will place a schema lock.
The solution
Stop all queries on secondary replica, conduct primary replica DDL, and then DDL changes will flow over to secondary replica.

Are there any other steps needed in this process?
What else needs to carefully be done?
What is best way to close all read queries on secondary replica? Kill/Close Spid, SET SINGLE_USER WITH ROLLBACK IMMEDIATE?

This example is only for secondary read-only asynchronous. What if the secondary replica is read-only synchronous?

Other Background:
The Primary Replica is OLTP, transactions all day and night. Our company has a deployment change management time window. Say I only have 1 hour during the morning to conduct schema and DDL changes. I cannot wait for long queries to finish. In asynchronous mode, the log redo will never be current.
I remember reading the Microsoft article, Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)


texts: should parent entity always have a primary key?

texts: I am have 3 entities, Person(fname, lname) none of them is primary, then User(email, password) and Receptionist(emp_id) bold is primary key. I have extended there two entities from Person, total participation and overllapping, so person must be either user or receptionist or both at the same time. Is it okay if I do not use primary key in Person


texts: Why should an application not use the sa account

texts:
My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.

I have an absolute belief that applications should not use the sa password without a perfected, business person focused reason why. Answers to This Question include a lot of my reasoning for an IT focused discussion

I am being forced into accepting a new service management system that WILL NOT work unless it uses the sa password. I never had time to work out why when setting up an evaluation but the server team tried to install it to use a fixed role I had set up incorporating db_creater and other permissions I thought it would require. which failed. I then let the server team install with the sa account but run under an account in the dbo role for its database but that failed too. Grumpily I tried to get it to run with an account in the sysadmin role but even that failed and not with useful error messages that enabled me to work out what was going on without spending more time than I had available. It will only work with the sa account and the password stored in clear text in the config file.

When I queried this and the server team talked to the vendor they got the worrying answer of 'What's the problem with that?' and then 'well we can look at scrambling the password' scrambling ffs

I know that there are ways and means to restrict access to the file but it is just another weakness in the security in my opinion

Anyway, My question is, could someone point me at some documentation that I can use to explain to the business the reason why this is a bad thing and should be a big no no. I work in a field that means that I need to take security seriously and have been struggling to make the business understand and ultimately may be out-ranked anyway but I need to try.


texts:
Let's say I want to write a web service that stores home automation device readings such as thermostats, humidifiers, etc. The data would take the form of:

user_id | device_id | tag | value | timestamp

A user could have 5, 10, or 20 devices connected based on account type and readings could be taken at 1, 5, or 10 minute intervals based on account type. The service could record 10, 50, or 100 tags for each connected device.

This means I would theoretically need to support a batch insert of 2000 rows per user per minute.

Let's say I want to scale to 10,000 users. That means an upper limit of 20,000,000 rows inserted per user per minute. So 28 billion rows per day.

What kind of databases should I be considering to handle this kind of load and how would it scale? I'd especially be worried about storage space, I think it would need to make use of cloud storage.


texts:
We've noticed something curious in our office which no one seems to know the answer to. I'll explain:

We have some mildly complex queries, one of which executes in under 3 seconds and then has a fetch time of around 100 seconds.

If we do this:

CREATE TEMPORARY TABLE tmp SELECT ... query here ...

and then select from this temporary table, the creation of the temporary table takes 'just' 29.7 seconds, and the fetch time is around 1.3 second.

Overall this represents a drop from 103 seconds to 31 seconds - can anyone break down what might be happening here?

This is using Percona Server 5.6, if that helps.


texts:
Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS?

So for example I frequently will open up SSMS and then connect to SQL Servers NY-DB01, NY-DB02 etc... Can I have some sort of shortcut just to open all of these at once?


texts: Word file running an SQL command - How do I find the source SQL database?

texts:
A word 2013 file has a merge function that activates whenever this is open and it is "SELECT * FROM 'database'".

As the final results have errors (the final word document feeds from the wrong field names) I would like to edit the database to change a few things.

The problem is I cannot seem to locate the 'database' as it's not in the same folder as the aforementioned Word file.

We use Access 2003 and this code was created by a person that does not work with us anymore.

Is there a way to locate the source database from the Word file (Word 2013, we only use Access for Office 2003)?

Thank you.


texts: Column 'Comments.Text' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

texts:
This question: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Stack Overflow have explained the root of the problem very well and even advised to use max aggregate as a solution. But, lets take the example

group_id    item_id     comment
--------------------------------
1       abc     "blah-blah"
1       def     "tral-la-lah"
1       ghi
2       jkl
2       mno
2       pqr

I understand the solution: I must take an item to represent the group by max(item_id). But how do I take the comment of the selected item? How does it help me to take the corresponding comment?

edit

Everything is like in this question: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Stack Overflow. The only complication that I address here is the extra comment column that I want to select along with the max(item_id) group by group_id.

I am using this beast, Which tools and technologies are used to build Data Explorer? | Meta Stack Exchange


texts: How can the size of the notification queue be configured?

texts:
The documentation for NOTIFY states

The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case

Is it possible to configure the size of the queue?  I couldn't find anything about this in the documentation or in postgresql.conf.


texts:
I am learning more about SQL as I work on this Database project.  Today I encountered Transactions, and after looking at what they are and how they work, my first thought was, "Shit, I need to change my Procedures to use these."

Throughout today I have been looking on here, and Google is trying to find out when it is appropriate to use a Transaction, on how to correctly write the syntax for one.  I honestly haven't found much, so I'm essentially writing this so that I can see if I am implementing these properly, and for anyone who may be looking for something like this in the future (hello Google).

So I've mostly been throwing Transactions into any Procedure I have created which either inserts data or deletes data.  It seems to me that if I am querying data, not manipulating it, transactions aren't necessary.  If I am wrong in this thinking, please feel free to illuminate me.

So this is an example of me using a Transaction in a Procedure that is Deleting Data:

CREATE PROCEDURE DeleteOrder
    @OrderNum VARCHAR(20)
) AS
BEGIN
    DECLARE @OrderID INT

    IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum))
    BEGIN
        SELECT @OrderID = order_id
          FROM purchase_orders
         WHERE order_number = @OrderNum
    END
    ELSE
        RETURN 1

    BEGIN TRANSACTION DelOrder
        BEGIN TRY
            DELETE FROM order_parts
             WHERE order_id = @OrderID
            DELETE FROM purchase_price_logs
             WHERE order_id = @OrderID
            DELETE FROM purchase_orders
             WHERE order_id = @OrderID
            COMMIT TRANSACTION DelOrder
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION DelOrder
            RETURN 1
        END CATCH
END

The first question I have is; Is my syntax correct?  Am I implementing the Transaction appropriately?  Is there a way you would code what I am trying to do differently?  For that last question, it's more about the whole Procedure than just the Transaction segment.

Finally, I have a question I thought of while writing this post.  Is it possible to have said a 'Master Rollback' or 'Master Commit' procedure, where while you have a connection going on with the database, no transactions are committed until your 'Master Commit' Procedure is called?  Something like.

MasterCommit
    For Each Transaction
        Commit Transaction

With something similar being the same for Rolling back.  I know of the existence of SavePoints, so is this where they'd be helpful?  More so with the Rollbacks than the Commits I suppose.


texts: Is SQL Server 2008 going to be obsolete "soon"?

texts:
I was thinking about it today. We are in 2015. Close to SQL Server 2016, but almost every company I see, is still using SQL Server 2008 R2.

I'm studying SQL Server 2008, 2012 and now, 2014  (5 months school).

My main focus now is SQL Server 2008, because I'm currently working with it, but, should I be worried about it? It would be better to start to focus on SQL Server 2012+?

My company don't even think about an update. We have a lot of software here, and we have 0 problems. And I think, even with 0 problems, is there a need to update?

One good blog I read:
http://www.sqlservercentral.com/articles/Editorial/116525/


texts: SQLAnywhere: How to write result of a query into a file with non-interactive session

texts:
I'm looking for a way to write resultset of a query to a file maybe analog of queryout of MS SQL or the COPY from PostgreSQL

When using something like

select count(*) from "table" OUTPUT TO 'results.txt' FORMAT TEXT

I'm getting

  Could not execute statement. Syntax error near 'TO' on line 1
  SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1

When running it, as discribed in documentation like this:

select count(*) from "table";
OUTPUT TO 'results.txt' FORMAT TEXT

e.g. inside a procedure, I'm getting an error telling me that there is no result for being outputted.


texts:
I'm trying to understand why mysql would take 20mn to start.
I'm running 5.7.17-13-log, and I have a large ibdata1 file (20GB) even with file per table activated. Here is it's content:

# innochecksum -S /data/mysql/ibdata1
File::/data/mysql/ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
  781526        Index page
 1031230        Undo log page
   11836        Inode page
   25060        Insert buffer free list page
   22645        Freshly allocated page
     114        Insert buffer bitmap
     107        System page
       1        Transaction system page
       2        File Space Header
     116        Extent descriptor page
       3        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================

Here is what I see in the error log:

2017-06-05T19:08:31.307808Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-05T19:08:31.719378Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/xb_doublewrite, size 31457280 bytes
2017-06-05T19:29:09.747406Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-05T19:29:09.747507Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-05T19:29:09.763021Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-05T19:29:09.869445Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-05T19:29:09.869470Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-05T19:29:09.869574Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1238576ms. The settings might not be optimal. (flushed=0, during the time.)

I see a mention of the page cleaner afterward taking 20mn, on a startup it sounds weird!

Here is a sysdig output on mysqld which shows that it's reading ibdata1 all that time:

11706 13:01:38.019123953 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9304113152
11707 13:01:38.019163204 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116243 vm_size=45320516 vm_rss=14609036 vm_swap=0
11709 13:01:38.019292874 0 mysqld (94026) < pread res=16384 data=.......F...E..._...n....E..............6?a..=..h................................
11712 13:01:38.019337538 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9232695296
11713 13:01:38.019355435 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116247 vm_size=45320516 vm_rss=14609036 vm_swap=0
11715 13:01:38.019399597 0 mysqld (94026) < pread res=16384 data=.......?...9...G...n....E..............5?1.j....................................
11718 13:01:38.040605854 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9304522752
11719 13:01:38.040644306 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116438 vm_size=45320516 vm_rss=14609560 vm_swap=0
11721 13:01:38.040774631 0 mysqld (94026) < pread res=16384 data=69....._...F...n...n....E..............*?A...u. .....w..........................
11872 13:01:38.062582012 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9232826368
11873 13:01:38.062633600 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116541 vm_size=45320516 vm_rss=14610084 vm_swap=0
11875 13:01:38.062748273 0 mysqld (94026) < pread res=16384 data=.te=...G...?...O...n.(.gE..............X?8.i.R.......f.c........................

Update 1

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 32G
innodb_log_buffer_size         = 128M
innodb_buffer_pool_instances    = 8
innodb_max_dirty_pages_pct  = 5
innodb_io_capacity      = 2000
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Any ideas?

Thanks,


texts: Using psql with string variable that has two periods

texts:
psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3"

into the script

begin;

create table db_log (
    db_owner varchar(255) not null,
    db_user varchar(255) not null,
    version varchar(255) not null,
    created timestamp,
    id serial primary key
);

insert into db_log (db_owner, db_user, version, created)
    VALUES (current_user, :db_user_string, :version, current_timestamp);

commit;

gives...

psql:db_log.sql:10: ERROR:  syntax error at or near ".3"

Any ideas?

I am using 9.3.0


texts:
OS: Windows Server 2012
DB: Oracle11g-r2

When you restart or turn on the server, everything works perfectly, the memory is normal, the number of sessions, too. But after 2-3 days of continuous work, the consumed memory increases, and the number of sessions increases significantly to 450+. As far as I know, the database session should be thrown out by itself and there should be no problems with it. But what happens to memory? At the moment, it is 5.4GB passed 3 days. Tomorrow will be even more.
In the end, the database will stop accepting new traffic connections, even if everything is unloaded.
Only restart helps. Help me pls


texts: Select random 10 unique rows from MySQL database?

texts:
I have one table (questions) with 54 questions.

The students have to answer all 54 questions.

I have to show 10 questions at once, then next 10 and so on....

I have to select 10 questions randomly and next 10 random questions except the previous 10 questions that already answered, and so on....

I store all the answers in another table named user_answer.

what will be the MySQL Query?


texts:
I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would be the select to get all rows from this table, but only select 1 of the duplicates at a time? I have around 100k duplicates, and my DB guy is still on christmas vacation :|

the tables do have unique keys, but other than the keys the data is duplicated a few thousand times. I'd like to avoid deleting the data (just in case). Server is running MSSQL2012, and i'll edit this post with a table structure when I get back to my desk.


texts: Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

texts:
I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long as it works.

I'm abou to try it on an experimental db, but here's a puzzle: I have a mysqldump from the production database, which naturally wants to recreate the tables as myisam.

Several options present themselves:

edit the dump file -- which is an 8 GiB file so editing might be a tad difficult.
just load the dump and convert the engine afterward -- but sourcing in the file seems to take a long time.
Find some magic that will force the new DB engine to be innodb no matter what the dump says.

Is there another option? Which option should I choose?

I'll cheerfully admit DBA is not my primary skill.


texts: Interval values for aggregate function AVG greater than 24 hours

texts:
I have a simple query which runs the AVG aggregate function on a date time interval field generated by subtracting one datetime field from another.

The query looks like :

SELECT AVG(date_field_1 - date_field_2)
FROM table
WHERE date_field_1 > date_field_2
AND date_field_1 IS NOT NULL
AND date_field_2 IS NOT NULL
....

However the query is returning values such as 2 days 24:50:26.092199 in the default postgres interval style . The absolute value I get when I extract EPOCH and convert  to  seconds (EXTRACT(EPOCH FROM (AVG(date_field_1 - date_field_2))) is something like 262226.092199. I also get results like 26 hours not just 24 hours.

Should the output not be 3 days 00:50:26.092199? .


texts: MySQL replication - invalidating query cache entries (table)

texts:
We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of:

  invalidating query cache entries (table).

The global query_cache_type & size were set to 0.

I have checked around and I can see it's a known old bug in MySQL but could not find a workaround for this issue.

Any advice?

query_cache_type OFF in my.cnf before server restart.
query_cache_wlock_invalidate set to OFF.
using default % storage_engine as InnoDB.

We ran some more tests and we had an issue with disk IO - sar -u showed about 3-4 %iowait, disks infra changes help reduce this to about 1.5-2% but it still doesn't help the replication not lagging for 5000(!) seconds behind the master.


texts: Transactional Replication data during rebuild

texts:
To offload reporting I have transactional replication running between my prod box and my reporting server (top image #1). This works great until the snapshot has to be rebuilt. During this time users/apps/reports are reading from an incomplete dataset while the reporting db is being rebuilt.

Does anyone else experience this problem? What is the best work around?

Is #2 in the image a reasonable solution? Create a shell database on top of a data repo. The reporting DB no longer contains data but only views that replicate the structure of the tables in the data repo. During a rebuild of the "data repo" database the "reporting db" is set offline to prevent users/apps/repos from reading incomplete data?


texts: Grant access to all tables of a database

texts:
I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data.

I would like to grant rights to all tables from a given database to a specified user, but I am not sure if it is the best idea to grant him access to whole schema public as I don't know if it would allow some kind of privilege escalation. Is there any other way?


texts: DBA's - What did you do that helped you gain the most understanding in your role?

texts:
I'm hopeful this question isn't so subjective that it's closed, as I think it can be helpful for newer DBA's who want to get a better overall understanding of being a competent, knowledgable DBA who is skilled in their trade.

What did you do that helped you gain the most understanding in your role?
Was there any training, books, experiences, that best helped you gain an understanding of your role and how to perform it well?
If you could summarize your best advice for someone to get a good overall understanding of how to administer Microsoft SQL-Server, what would you say based on your specific journey?

Thank you.


texts: How to Set Recovery Mode in SQL Server Always On Availability Group without downtime?

texts:
I am running a SQL Server 2016 2-node Always On Availability Group, and I want to do some backups.  I normally run this query to do my backup:

-- To permit log backups, before the full database backup, modify the database to use the full recovery model.
USE master;
ALTER DATABASE dbname
   SET RECOVERY FULL;

BACKUP DATABASE dbname
   TO URL = 'https://bloblname.blob.core.windows.net/databasebackup/backup.bak'
   WITH CREDENTIAL = 'credentialname';

My question is, can I do this part on an Availability Group Primary Node without incurring downtime?

ALTER DATABASE dbname
   SET RECOVERY FULL;

I am worried that if I run this command on the availability group primary it is going to somehow cause synchronization to stop, or perhaps have other effects that could be detrimental.

Another related question is - do you advise running these commands on the primary or the secondary?

Any help with this would be greatly appreciated.


texts: Use timestamp(or datetime) as part of primary key (or part of clustered index)

texts:
I use following query frequently:

SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp

I would like to optimize this query, and I am thinking about put timestamp as part of primary key for clustered index, I think if timestamp is part of primary key , data inserted  in table has write to disk sequentially by timestamp field.Also I think this improve my query a lot, but am not sure if this would help.

table has 3-4 million+ rows.
timestamp field never changed.
I use mysql 5.6.11

Anothet point is : if this is improve my query , it is better to use timestamp(4 byte in mysql 5.6) or datetime(5 byte in mysql 5.6)?


texts: What techniques to use when designing one or more tables to allow keeping a pending and approved version of objects?

texts:
I have several objects in my systems that need to go through approvals. Users can create these objects, such as uploading a jpg to the server as a new Media object, and they need to be in a pending state until a Reviewer approves it. Users can also update objects, such as uploading a new jpg to the previous Media object. In this case, I need to store the approved version of this Media object alongside the pending version until the pending version gets approved (or deleted) by a Reviewer.

I don't need to store a complete history of objects, only the approved version and the most recent pending version until it gets approved or deleted.

As far as reading from this table, I need to be able to search for objects, where objects with both a pending and approved versions will only return the pending version. I also need to be able to get only the approved versions of objects.

Objects, for example Media, can be assigned as children of other objects, for example as Slots in a Playlist. I'm hoping that if you assign a Media to a Playlist, as the Media object gets updated with newly approved versions, the association of Media to Playlist doesn't need to be altered to reflect the change in object.

My previous attempt at designing this did not turn out as easy to use as I would like. I created tables for these objects that require approval by have a column for ModerationStatus which held information about whether the object was pending or approved, and used a Primary Key on ID and ModerationStatus. This way I could assign objects as a child of other objects, and keep track of the ID for the child which wouldn't change as the object was subjected to multiple updated and approvals. It turned out clunky, the Slots for Playlists needed to have Foreign Keys to both the ID and ModerationStatus of these objects while I wish I would only need an ID, searching for objects with a priority on their pending versions required using a subquery in the Where clause. However I am no expert on SQL design, so these little quirks which see I see as "clunky" may be fine or expected as from a DBA's standpoint. Regardless I felt the need to look further before committing to a design.

Are there any suggestions or well understood techniques you can recommend for designing such a schema?


texts: Delete duplicate keys with relation algebra

texts:
Hi I am new to databases and relational algebra. I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same keys but different value.

e.g. I want to keep only [1, 5] and [4, 9] but remove everything else.

Key    | Value
-------|-------
 1     | 5
 2     | 6
 2     | 7
 2     | 8
 4     | 9

Thanks.


texts: One to one mapping vs many to one mapping on data base design

texts:
I'm currently doing call detail records (CDR) data analysis as my project. On course of data-base design, I had separated the date (table contain id, day,month,year), time field (table contain id, second, minute, hour etc) from call table (table contain called number, duration, time_id, date_id etc) as millions of call transaction occurs in a day. Now I got confuse on how to map date, time to call table i.e. do one-to-one mapping or many-to-one mapping. One to one mapping can be done easily but data are redundant but I have no idea about many-to-one mapping. What I think about many to one mapping is I have first extract date and check if it exist or not. if exit then get its id otherwise insert date and get its id. (Background:- I have a cdr data with field card_no, service_key, calling_no, called_no, answer_time, clear_time, duration  on text file on tab separated format and have to make data mart as like figure below.

I am not good on telling problem so if any information required then comment below.

EDIT

On cdr table

Card_no is mobile number of the subscriber and if the subscriber dialing to the other number than if is equal to calling and if subscriber is receiving the call then card_no is equal to called. In call table its repeating.
sql_time_stamp means answer_time and as duration is already in database then why I have to store clear_time also ( if there is any reason then let me know)

On Subscriber table

Subscriber table contain dob which is static so I create another table  as this contain age (which may be changing) just to separate static part and dynamic part also gender is include to reduce multiple table joining as analysis just contain age(_group) and gender.


texts: Oracle - Can't login with "/ as sysdba", but can with "sys as sysdba"

texts:
I've been working on setting up a docker container running Oracle DB XE 18.4.0. I have the container built from Oracle's dockerfiles and it is working fine, except that I cannot login using

connect / as sysdba

in a sqlplus prompt from inside the container, but

connect sys as sysdba

does work. The error message that I receive is

ORA-01017: invalid username/password; logon denied

I checked that my $ORACLE_HOME and $ORACLE_SID environment variables were set and they look fine. I'm trying to install APEX in this container and the first error that is displayed when running the install script is the failed login error above where the script runs connect / as sysdba.

When installing Apex on the local instance of Oracle DB on my development environment, Everything works as expected.

I am working from a Windows 10 (1803) machine which is running docker 18.03.0-ce with a VirtualBox 6.0.2 setup.


texts:
I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin completion I need in this logic - perhaps a PL/SQL function.

I have a table that contains DNS data looks like below

RN TIMESTAMP  REQUEST                              TYPE  RESPONSE
1  1431606892 www.microsoft.com                    CNAME toggle.www.ms.akadns.net.
2  1431606892 www.ms.akadns.net                    CNAME www.microsoft.com-c.edgekey.net.globalredir.akadns.net.
3  1431606892 c.edgekey.net.globalredir.akadns.net A     23.6.72.154
4  1431606892 www.google.com                       A     173.194.121.48
5  1431606892 c.edgekey.net.globalredir.akadns.net A     23.6.72.194

I want to write a PL/SQL function that when a person searches for REQUEST "www.microsoft.com" it returns rows 1,2,3,5 to the user.  Basically it follows the CNAME trail (up to 255 times)

CREATE OR REPLACE PACKAGE DNSUTLS AS
TYPE col_table is table of tfinal%ROWTYPE;
FUNCTION CNAME_TRAIL(nsearch VARCHAR2) RETURN col_table pipelined;
END DNSUTLS;
/
CREATE OR REPLACE PACKAGE BODY DNSUTLS AS
DUMMY NUMBER;
FUNCTION CNAME_TRAIL(nsearch VARCHAR2) RETURN
col_table pipelined as
cursor temp_cur is
       SELECT * FROM tfinal where request=lower(search);
BEGIN
   for cur_rec in temp_cur loop
--if cur_rec.type = 'CNAME' THEN
--keep recursing to add additional rows
--endif;
      pipe row(cur_rec)
   end loop;
END;
END;

/


texts: SQL Server - How to know which tables are used and which are not?

texts:
I have a database that has 225 tables in it

What is the best way to find out / log which tables are used, and which are not ?

I would like to see something similar to below:

Table 1 - select 2222 times, insert 0 times, update 3 times, delete 0 times
Table 2 - select 1 times, insert 50 times, update 0 times, delete 5 times

Or in a below format:

Table 1 - select 2222
Table 1 - update 0
Table 2 - select 1
Table 2 - insert 50
Table 2 - delete 5


texts: ROLLBACK doesn't work after INSERT INTO newly created destination table

texts:
I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers).

Before inserting contents of CSV-file into the mysql table I am first backing up the original customers table.

I am wrapping whole import process (including backing up) in a mysql transaction (to account for cases when CSV is corrupt somewhere in the middle, and to ensure import is atomic).

The problem is that ROLLBACK doesn't seem to work when I am calling it right after INSERT INTO statement: when checking database via phpMyAdmin I can see the newly created table AND ROWS INSIDE IT still present after roollback.

Here's the log of the operations:

[2015-01-19 14:08:11] DEBUG: "START TRANSACTION" [] []
[2015-01-19 14:08:11] DEBUG: SHOW TABLES LIKE :table_name; [] []
[2015-01-19 14:08:28] DEBUG: CREATE TABLE `customers__20150119_14_08_20` LIKE `customers` [] []
[2015-01-19 14:08:37] DEBUG: INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers` [] []
[2015-01-19 14:08:50] DEBUG: "ROLLBACK" [] []

So I wonder why depsite ROLLBACK is called, the transaction is not cancelled. I do understand that CREATE TABLE is not transactional in nature and can't be rolled back. But I was assuming that INSERT INTO because it deals with inserting rows (not defining schema), WILL actually be transactional, and after ROLLBACK I will be left with empty destination table. Why is it not the case?

And here's output SHOW CREATE TABLE customers (so my table is InnoDb):

CREATE TABLE `customers` (
 `Code` varchar(32) NOT NULL,
 `Name` varchar(128) DEFAULT NULL,
 `Price` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`Code`),
 KEY `Price` (`Price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and here's output for the desination table:

CREATE TABLE `customers__20150119_14_08_20` (
 `Code` varchar(32) NOT NULL,
 `Name` varchar(128) DEFAULT NULL,
 `Price` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`Code`),
 KEY `Price` (`Price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: Software need db owner permission to master database (sql2016)

texts:
We have a manufacturer whose software requires authorization for the master database (only db owner working). Unfortunately, the manufacturer can not tell us why this access to the master database is necessary. Without this permission, the application will not start.

What are your opinions? Have you ever seen such software? We have 200 different applications but this is the first one that needs permanent access

Is there any objection to security?

As an alternative, we can install an SQL Express instance because the database is very small. If access to the master DB from your point of view is not a good idea.


texts:
I have read about Dynamic Data Masking and one question is still in my mind. I am wondering what will happen is such scenario:
1. Dynamic Data Masking is set up on table
2. ETL process is loading new rows using NOLOCK hint (read uncommited)
3. During data load, execute select statement with read uncommited isolation level to get "dirty read" rows.

Does this "dirty data" will masked or not?


texts: Suggestion for NoSQL database for global users

texts:
We are planning to build a social media application, lets say similar to Instagram but at much smaller scale, of course.
The users could be from many geographies like US, Europe, Asia who could interact on same post.
We are evaluating a NoSQL database choice for our use-case where we want:

optimal reads at the same time making writes(lets say comments/likes) not noticeably slow.
flexible schema, preferably json documents
okayish transaction support as it is needed only in few operations and hence we can take a latency hit

We evaluated MongoDB, and with replica set support we can serve reads from local geography(say NA replica for NA users etc) but since there is single master/primary, writes could be slow for some geography(e.g master is in Europe and write came from Australia).
Any help on this would be great.
Note: If you feel this question is broad/needs more info, please comment first before downvoting! I will make sure to add all details that is needed.


texts: insert trigger in sql server to linked oracle

texts:
i have created a linked server to oracle in sql server and i want to write one trigger on table in sql server to insert the data into oracle table. For example the table a is in sql server and table b is on oracle. If i insert record in table a the values needs to be copied or inserted into table b of Oracle.
both the tables have same structure
table a
id varchar2(10);
name varchar2(10);
table b
id varchar2(10);
name varchar2(10);


texts:
I've got a couple of tables to track volunteer workers at our non-profit.

One stores Volunteers and tracks information about a volunteer such as the person's name, a contact number, primary location and such.

Another table stores Tasks and tracks specific work done by the volunteers.  It references the Volunteers table by ID to allow me to, say, pull back all of the tasks performed by Pete.

Tasks have number of hours so we can track how much work was done on tasks at a certain location, how many total hours were volunteered at that location, etc.

I'd like to keep track of the cumulative amount of volunteer time for each volunteer and this where a design decision needs to be made.

I could add a column to the Volunteers table called VolunteerHours and then add triggers to the Tasks table to call a stored procedure to recompute the volunteer hours when rows are added/updated/deleted on the Tasks table.

Alternatively, I've considered creating a view that would be used to retrieve the totals on demand.

create view VolunteerSummary
as
    select v.VolunteerID, SUM(TaskVolunteerMinutes) as VolunteerTotal
    from Volunteer v
    join Tasks t on t.VolunteerID = v.VolunteerID
    group by v.VolunteerID

go

select * from VolunteerSummary

The view approach is attractive to me because I'm not storing a total column on the table, rather computing the total when I need it.

Assumptions I have made about these tables:

Fairly small (< 1000) number of volunteers
Most volunteers volunteer less than 12 times a calendar year (once a month)

Based on my assumptions and description, is there a good reason to go with the column/trigger/stored procedure route over the view?


texts:
I'm a little mind struck at the second as I am looking to automate the following sequence:

Extract Data from Microsoft Access
Save files as CSV
Connect toMySQL server
Run one SQL file that exports all the CSV's using LOAD DATA LOCAL INFILE

I've written the entire SQL file that DROPs each table then re-uploads it and this runs like a dream in MySQL WorkBench. I just don't know how to get this to do the task without me physically carrying out the task...

I am now stumped how to automate part 4... There's no visual basic style backend to MySQL WorkBench and I thought requesting SSH from my provider would fix this, turns out my provider states SSH access is for web files not databases.

Is this the case with all MySQL or do some providers allow SSH directly to the database? If not how do I go about automating this sequence?

Many thanks.


texts: Most efficient way to insert data into archiving table without influencing production?

texts:
For example lets say we have table A:

create table if not exists T(
column1 int,
column2 varchar,
column3 date
);

and archiving table TArchive:

create table if not exists TArchive(
column1 int,
column2 varchar,
column3 date
);

What would be the best approach to inserting data older than x date into TArchive without locking down table T in production? Under the assumption that table T has a big amount of rows.

I've been researching this for hours. In SQL Server you have different approaches like: https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/
But in PostgreSQL I can barely find anything.

Should you just directly take the data from the T table and import it into TArchive?

Should you import your data into a temp table first, then import that into the archive table? And if so, why would this approach be better when you're doing 2x the inserts for the same data.

How many functions should you make? One function to rule them all? Or a function for archiving and another for deleting the old data?

Are there any other approaches?


texts: How to copy value from one column and update another column

texts:
I made a mistake, I set a column to NULL for 3 Millions rows. The column name was "LargeImageURL".

How can I now take the value from "SmallImageURL" column and REMOVE ".SL75" from it and update LargeImageURL.

SmallImageURL

https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.SL75.jpg

LargeImageURL

https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.jpg

Any help please ? I know this can be done with php but can SQL do this ?

thanks


texts: Why RDS PostgreSQL `MaximumUsedTransactionIDs` not decreasing even after data base seems correctly `VACUUM FREEZE`'d?

texts:
My MaximumUsedTransactionIDs seems to keep growing indefinitely, but I have
these evidences hinting me it maybe should not be happening that way:
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.

my_main_data_base=> SELECT datname FROM pg_database;
     datname
-------------------
 template0
 rdsadmin
 template1
 postgres
 my_main_data_base
 another_data_base
(6 rows)

my_main_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC
LIMIT 5;
VACUUM
       relname        | xid_age
----------------------+---------
 pg_toast_1150794     |   63707
 a_user_table         |   63707
 another_user_table   |   63707
 pg_toast_1223948     |   63707
 pg_toast_1223998     |   63707
(5 rows)

my_main_data_base=> \q
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
another_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.

another_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC
LIMIT 5;
VACUUM
      relname       | xid_age
--------------------+---------
 pg_toast_2964      |   12350
 pg_toast_3592      |   12350
 pg_toast_2396      |   12350
 pg_db_role_setting |   12350
 pg_authid          |   12350
(5 rows)

another_data_base=> \q
$ _

$MY_USER_LAND_RDS_SUPER_USER is what AWS RDS provides me as a superuser.
It is a super user from the RDS point of view, but it is not a superuser
from the PostgreSQL point of view.

I would expect now to have MaximumUsedTransactionIDs decreased, but it does
not. It is still at about ~80.000.000.
I have planned that after it reaches 100.000.000 I'll fork a development
version of the RDS instance where I'll make it so AWS boots the autovacuum
process at it.
Questions

What is happening with MaximumUsedTransactionIDs? Why it does not shrink?

Will autovacuum take care of MaximumUsedTransactionIDs once booted?

Is autovacuum irrelevant in my scenario because I run manually unqualified
VACUUM FREEZEs from time to time on all my user land databases?

Should I just ignore MaximumUsedTransactionIDs?

Can I somehow connect to database rdsadmin to SELECT age(relfrozenxid)
there?

EDIT, fulfilling your first request, adding...
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.6.6, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.

postgresql=> SELECT datname, datfrozenxid FROM pg_database;
     datname       | datfrozenxid
-------------------+--------------
 template0         |         1822
 rdsadmin          |         1822
 template1         |         1822
 postgres          |         1822
 my_main_data_base |     76628948
 another_data_base |     76681257
(6 rows)

postgresql=> \q
$ _


texts:
For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why.
This is the table creating script:

CREATE TABLE IF NOT EXISTS `esp_game` (
  `gameID` int(10) unsigned NOT NULL,
  `gameType` tinyint(3) unsigned NOT NULL,
  `mapID` tinyint(3) unsigned NOT NULL,
  `createDate` int(11) NOT NULL,
  PRIMARY KEY (`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`gameID`,`summonerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `gameTypeMap` (
  `gameTypeID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `gameTypeName` varchar(50) NOT NULL DEFAULT '',
  `gameTypePortugueseDesc` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`gameTypeID`),
  UNIQUE KEY `gameTypeName` (`gameTypeName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

This is the query that gets stuck:

SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;

Extra info:

esp_game had about 10M entries (~1 Gig), esp_gameplayer about 100M (~7 Gigs) entries and gameTypeMap 10.

Explain before the deletion:

EXPLAIN SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows    | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
|  1 | SIMPLE      | g     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 | 7706280 | Using filesort |
|  1 | SIMPLE      | gt    | eq_ref | PRIMARY       | PRIMARY | 1       | teemo.g.gameType     |       1 |                |
|  1 | SIMPLE      | gp    | eq_ref | PRIMARY       | PRIMARY | 8       | teemo.g.gameID,const |       1 | Using index    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
3 rows in set (0.00 sec)

Server configuration:

[mysqld]
(...)
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 1536M
innodb_lock_wait_timeout = 25

The only way I've managed to make it work again is truncate everything from those tables - Thankfully I'm able to do that, but soon I won't be able to truncate everything anymore!
Any Idea how to solve this problem?


texts: What to do when the error message is longer than 4000 in T-SQL?

texts:
I have a OPENQUERY linking to an Oracle server that is failing. (I can't make any changes on the oracle server)

But when it returns the error string it is cut short because it returns the query in the error string and the query is longer than 4000 characters, and because ERROR_MESSAGE() is of type nvarchar(4000) the part of the error I need is missing.

Is there some way to get the remained of the string before it is truncated or is it saved some where else?


texts: How to wait between PostgreSQL DB restore and reindexing?

texts:
I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution:

Start the pg service
Vacuum all DBs (not sure if this is needed)
Backup with pg_dumpall
Stop the pg service
Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)
Install PostgreSQL 9.2.1
initdb
Start the server
Restore the dumped data
reindexdb all DBs
Recreate the referential_constraints view
Vacuum all DBs (AFAIK required after this upgrade)

Unfortunately reindexing fails in about one third of the executions, even when using a virtual machine which I reset between tries. The code:

PGOPTIONS='--client-min-messages=warning' psql \
    --no-psqlrc \
    --variable=ON_ERROR_STOP=1 \
    --quiet \
    --log-file="$restore_log" \
    --single-transaction \
    --username postgres \
    --file="$sql_backup"
[error checking which stops the script if the import failed]
for database in $(databases); do
    reindexdb --username postgres $database
    [error checking which stops the script if the reindex failed]
done

The restore log shows no error at the end. The last lines of the reindexdb output (anonymized to be able to publish it):

NOTICE:  table "myschema.mytable" was reindexed
reindexdb: reindexing of database "myschema" failed: ERROR:  could not open relation with OID 16738

/var/log/pg/postmasterlog contents during the upgrade:

After a VM reset I found that the OID belongs to a table which is backed up and restored properly. Could it be a problem that the reindexdb step is run immediately after psql has processed the backup file?

Edit: This is getting even worse - This time I encountered another issue I haven't seen before:

reindexdb: reindexing of database "myschema" failed: ERROR:  deadlock detected
DETAIL:  Process 3883 waits for AccessExclusiveLock on relation 17684 of database 16385; blocked by process 3980.
Process 3980 waits for RowExclusiveLock on relation 16513 of database 16385; blocked by process 3883.

In /var/log/messages:

Process 3980: UPDATE tablename SET [...]

Looks like the problem is a cron job which was trying to update the database during the reindexing.


texts:
I would like to upgrade a PostgreSQL 8.3 server to PostrgreSQL 8.4. There are many software components that interact with the server over the network, and I am concerned with compatibility issues.

Is there any interface change between the different versions of PostgreSQL, as far as an external component is concerned?


texts:
Please see image of the data and my code so far.

In this example I only want the first two rows to be retrieved. The reason for this is because where JobID is 1684 the sum of STILL_REQUIRED is > 0

Where JOBID is 1683 the sum of STILL_REQUIRED is 0, so don't show this row.

I think I need to get the sum when it is grouped by JobID, but I cant get it working. I am using Firebird.

Here is the code I have so far:

SELECT *
FROM (SELECT ID,
             JobID,
             NAME,
             ORDERNUM,
             STARTAFTER,
             FINISHBEFORE,
             START_OFFSET_MINS,
             TOTAL_MINUTES_REQ -
             (SELECT COALESCE(
                       SUM(TABLE_PROCESSTIMES.END_TIME -
                           TABLE_PROCESSTIMES.START_TIME) / 60, 0)
                FROM TABLE_PROCESSTIMES
               WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID
                  AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) AS STILL_REQUIRED
FROM TABLE_PROCESSES)
WHERE STILL_REQUIRED > 0


texts:
I have a large table with over 140 million of rows (basically from all brazilian investment funds shares organized by the enterprise official number (CNPJ) that I would like to left join with a table that has the names of the funds and their cnpj (around 26k of rows).

I have tried this:

INSERT INTO dataset_fundos
SELECT
inf_diario_fi.CNPJ_FUNDO,
cadastro_fundos.DENOM_SOCIAL,
cadastro_fundos.CLASSE,
inf_diario_fi.DT_COMPTC,
inf_diario_fi.VL_TOTAL,
inf_diario_fi.VL_QUOTA,
inf_diario_fi.VL_PATRIM_LIQ,
inf_diario_fi.CAPTC_DIA,
inf_diario_fi.RESG_DIA,
inf_diario_fi.NR_COTST
FROM inf_diario_fi
LEFT JOIN cadastro_fundos ON inf_diario_fi.CNPJ_FUNDO = cadastro_fundos.CNPJ_FUNDO;

However I have been running this join for over 3 days and it has not finished. I tried indexing the CNPJ column from both tables, but still another 3 days and it could not finish the dask (I interrupted it since it was taking so long). I read on some topics people joining 50 million of rows and complaining that it takes more than 3 mins, so I guess I am doing something wrong here. Sorry if it is a basic question, I am new to SQL and I adapted this code from other codes found here on stackexchange, so I guess it probably isn't optimal.


texts:
I have a SQL Database that is being restored every day from a Production SQL server.

The restore is being done by a third party application. Once the DB is restored I need to set some specific user permissions and also run other script and I would like to do that exactly after the restore process has been complete.

  Is there a way I can execute a script after a DB has been restored?

Thanks


texts: What do I need to develop an integration service to extract data from an Oracle DB to a SQL Server DB using Visual Studio 2015?

texts:
I have to develop an ETL to extract some data from an Oracle DB 11g to a SQL Server 2017 DB. I am pretty new at all this technology so I would like some advices. The strongest problem so far is to understand what I exactly need to perform this, as the more I read, the more confused I feel about all the tools I am supposed to install.

Here what I have so far:

The SQL Server 2017 DB is on a server. I can access the DB through Microsoft SQL Manager. Also, I have access to the server itself through remote connection but no permission to install whatever I want. However, I guess I could ask for any installation if it was mandatory.
The Oracle DB is on another server. I do not have access to the server itself. I have access to the DB through SQL Developer Tool. I access to the DB using EZ Connect, not a TNS.
On my PC, I have the following tools installed:

Visual Studio 2015
Visual Studio extension Microsoft SQL data tool, in order to develop the SSIS
Visual Studio extension Oracle Developer Tools in order to connect to the Oracle DB through Visual Studio

I can already develop a SSIS and transfer some data from my PC to the SQL server DB. However, I am not able to connect to the Oracle DB through the SSIS.
I am pretty sure there are plenty of details that I am missing so I would be grateful to whoever that would give me any advice.


texts: What MySQL storage engine and table schema should I use?

texts:
I am making a web project and I created necessary and the most important table, but I am not sure that I did it right.

In this site users will post data like text, picture, text's category, etc. I created a table like this:

CREATE TABLE `feed` (
  `f_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_id` int(10) unsigned NOT NULL,
  `f_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `f_loc` varchar(50) NOT NULL,
  `f_lang` varchar(2) NOT NULL,
  `f_cat` smallint(2) unsigned NOT NULL,
  `f_text` text NOT NULL,
  `f_img_ids` text NOT NULL,
  `f_img_flag` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `f_type` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `f_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`f_id`),
  KEY `u_id` (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Is this table schema good or does it need to be improved?

I also want to record every feed's statistics and add some columns like f_views, f_likes, f_favorites, etc. These columns will be updated frequently as users interact with the site.

What are your suggestions?


texts: Protecting Virtualized SSIS and SSRS servers

texts: I have a stand alone instance of SSIS and SSRS running on 2 seperate VM's in production. And I want to know if the Servers can be protected with some kind of Snapshot method like Veeam to restore them back in case they blow up. Please let me know if something like this is possible at all.


texts: Recover data from MySQL Incremental Backup files (.FRM and .IBD files)

texts:
I have MySQL Enterprise full and incremental backups (.FRM and .IBD files).
I have copied the full backup files to a different server, created table structures and imported the data the data using IMPORT TABLESPACE.
How do I import the data from the incremental backup files?


texts: selecting duplicate values with a condition from a mysql table


texts: In Change Data Capture (CDC), why lsn is used instead of timestamp?

texts: I am learning to use CDC in SQL server 2008, I have read that LSN is always incremented and is based upon timestamp, so why LSN is required, if it is calculated from timestamp which can be used as increamental sequence id for changes made. I am sure that there will be some other purpose as well of lsn.


texts: Why doesn't MariaDB server recognise Server Audit settings in config file?

texts:
I'm trying to implement the Server Audit plugin that is built-in with MariaDB 10.4.  I've added the settings to the my.ini config file, running on Windows 10.

[server]
...
#Audit Settings
server_audit_logging=ON
server_audit_output_type=FILE
server_audit_events=CONNECT, QUERY, TABLE
server_audit_excl_users=    #Default=blank
server_audit_file_path=D:/Databases/MariaDB/server_audit.log
server_audit_file_rotate_now=   #Default=0; choose 1 to create new log file, restart server, then reset to 0.
server_audit_file_rotate_size=  #Default=1000000
server_audit_file_rotations #Default=9 (range 0 to 999)
server_audit_incl_users=    #Default=blank
server_audit_query_log_limit=   #Default=1024
server_audit_syslog_facility=   #Default=LOG_USER; not relevant when using log to file.
server_audit_syslog_ident=  #Default=mysql-server_auditing; not relevant when using log to file.
server_audit_syslog_info=   #Default=blank; not relevant when using log to file.
server_audit_syslog_priority=   #Default=LOG_INFO; not relevant when using log to file.

But I can't start the server because of this error:

2020-04-17 19:26:20 0 [ERROR] D:\Program Files\MariaDB\MariaDB 10.4\bin\mysqld.exe: unknown variable 'server_audit_logging=ON'

I've tried putting the settings under [mariadb] and [mysqld] just in case that helped - it didn't.

What am I doing wrong here?


texts:
I just inherited an AWS RDS PostgreSQL 12.5 instance (so, no superuser access) and I'm required to do some cleanup and delete a bunch of groups/roles/users. I'm executing the following commands with user 'master' provided by AWS on the only available database (apart from postgres db)
When trying a:
DROP ROLE xxx

I get:

ERROR: role "xxx" cannot be dropped because some objects depend on it
DETAIL: N objects in database xxx

After a LOT of googling I ended up with:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON SCHEMA some_schema FROM "xxx";
REVOKE USAGE ON SCHEMA some_schema FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON SEQUENCES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON TABLES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON FUNCTIONS FROM "xxx";
REVOKE ALL PRIVILEGES ON DATABASE xxx FROM "xxx";

But still, the DROP fails:

ERROR:  role "xxx" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new types belonging to
role master in schema some_schema
SQL state: 2BP01

Tried to check dependencies using (taken from some stackoverflow question) but query comes up with nothing:
SELECT *
FROM
    --r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
    (SELECT N.NSPNAME AS SCHEMA_NAME,
            C.RELNAME AS REL_NAME,
            C.RELKIND AS REL_KIND,
            PG_GET_USERBYID(C.RELOWNER) AS OWNER_NAME
        FROM PG_CLASS C
        JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
    UNION ALL
    -- functions (or procedures)
    SELECT N.NSPNAME AS SCHEMA_NAME,
            P.PRONAME,
            'p',
            PG_GET_USERBYID(P.PROOWNER)
        FROM PG_PROC P
        JOIN PG_NAMESPACE N ON N.OID = P.PRONAMESPACE) AS FOO
WHERE OWNER_NAME = 'xxx'

Then tried to check privileges using (again, came up empty):
SELECT
    grantee AS user,
    CONCAT(table_schema, '.', table_name) AS table,
    CASE
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee
HAVING grantee = 'xxx'

This is my first time with PostgreSQL, coming from SQL Server.


texts: Selecting aggregated string from database

texts:
I have a table in my database consisting of one column which is NewLanguage(varchar). I insert new languages to the table and I want to select the rows of the table and assign them to a string variable delimited by ','. I try to select the languages as in the code:

    public string  SelectLanguagesFromDatabase()
    {
        try
        {
           NpgsqlCommand command = new NpgsqlCommand(
             "SELECT string_agg(\"NewLanguage\",',') FROM \"Languages\")",
             dbConnection);

           results = (string) command.ExecuteScalar();
           command.Prepare();
        }
         catch( Exception ex){
             MessageBox.Show(ex.ToString() );
         }
        return results;
    }

When I test it, results returns as null. I want to take string representation of the rows's values separated by ','. How can I do that? Thanks.


texts: Combining date and date/time ranges in PostgreSQL

texts:
In a PostgreSQL table, I'd like to store events which have a range telling when the event is happening. However, some events span whole days (i.e., the user does not care about time), while others must contain time as well. The question is how to store such data.

A typical query would be to list all events in a given date range, or date/time range, while the whole-day events shall be contained for any time of the given date. E.g., if listing events in range (2015-02-04 14:00:00, 2015-02-06 08:00:00), the result shall contain an event happening in range (2015-02-05 16:00:00, 2015-02-09 13:00:00) as well as an event happening in range (2015-02-03, 2015-02-04).

Three possible solutions occur to me:

Store date and time parts of event ranges separately, time being optional (i.e., for whole-day events, NULL would be stored in the time part, and treated separately when querying).
Store the range in a TSRANGE and have a @wholeday@ flag telling whether the time parts shall in fact be ignored (and treated separately when querying).
Store the range in a TSRANGE, while for whole-day events, ensure the time is always 00:00:00 and 24:00:00 for the range start and end, respectively.

I tend to use the third option, but would like to ask for experience with the given problem, as well as other possible solutions to it.


texts: How to keep records of past week visits in a web app?

texts:
I am developing a newspaper-like MVC webapp with an article content type which has a visits field. The database is MariaDB.

Each time a visitor requests an article, the visits field of the article row is incremented by 1. So I can easily query articles by their overall visits.

But what I want is to sort out articles based on number of visits which each article has got in the past week.

I thought to have a separate table like weekly-stats in which I can insert and increment article info with their recent visits upon each request, but then I need to empty the table weekly as a cron job, which is not ideal, and it leaves the stats empty after the data is being purged.

I also thought of having articles' visits saved in an expiring cache like redis and set the cached records to expire after a week, but then it brings all the complexities of inserting and querying redis hash elements.

So I thought there might be some more straightforward design strategies that I miss. How can I achieve this with the least hassle?


texts:
I have trouble while inserting the values corresponding to a column: position. I have multiple inserts like this:

INSERT INTO category_product(id_category,id_product,position) VALUES(2,1,1);
INSERT INTO category_product(id_category,id_product,position) VALUES(2,2,2);
INSERT INTO category_product(id_category,id_product,position) VALUES(2,3,3);
INSERT INTO category_product(id_category,id_product,position) VALUES(3,1,0);
INSERT INTO category_product(id_category,id_product,position) VALUES(3,3,1);

But I would like to have something like an insert within a select, I've tried:

SET @position := 0;
INSERT INTO category_product(id_category,id_product,position)
(SELECT 2,id_product,@position+1 FROM product);

SET @position := -1;
INSERT INTO category_product(id_category,id_product,position)
(SELECT 3,id_product,@position+1 FROM product WHERE [a condition to get id_product 1, 3]);

My problem: it doesn't work like I want and my main reason for this is I'm using a loop to make all the inserts (using a Cursor) and they're just killing me with the time. Any suggestion would be great.