texts: MySQL Script to automate creation of audit (history) tables and triggers?

texts:
For regulatory and fraud requirements, we need to record every change to most of the tables in the DB.  The way we did this very successfully with a previous project is to have a copy of each table, identical except that:

4 additional columns: DateChanged, UserId, Action (Create, Update, delete), and IP
The Id pk no longer is unique.
all other constraints (FK, unique indexes etc) removed.
the audit tables are in a separate audit schema

At a previous job, the Oracle DBAs wrote a script to automatically generate all this, it did the following:

Created a new schema called audit if not existing
Iterate over each table t in the normal schema:

created a new table in the audit schema with the same table name except prefixed it with a_, e.g. a_t
Added all the same columns as the original table, plus the 4 additional columns (DateChanged, UserId, IP Address and Action)
generated and added triggers to the original table (if not already there) to:
If updating, write a new row to the corresponding a_ table, with action of "Update" containing the pre-updated (old) values in all the columns (the main table will have the new values)
If deleting, Add a row which is a copy of the main tables row, but action = "Delete".
If inserting, add the row to the audit schema

NOTE:

All tables have a ID PK.
A few tables need to be excluded for performance or because not needed (e.g. the balance table which is updated by a trigger)

The beauty of this system is you can query what changed and when, or who made changes to what, and see the record before and after the change.  changes are at the DB row level, not the individual column level.

Has anyone come across something like this for MYSQL 5.6?  We don't have a DBA on our team who could write something like this from scratch, but we know enough to modify something similar.


texts: transaction across multiple databases - what is the overhead?

texts:
I run the following update, which is one single transaction across 2 databases.

Please note I have not committed the transaction.

--================================================================
--RUN THE UPDATE
--================================================================

BEGIN TRANSACTION T1_radhe

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT @@TRANCOUNT

BEGIN TRY

UPDATE DEStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM DEStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating DEStock.DBO.ItemStock '

UPDATE USStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM USStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating USStock.DBO.ItemStock '

--COMMIT TRANSACTION T1

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    WHILE @@TRANCOUNT > 0
         ROLLBACK TRANSACTION

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH

I get the following result from the update above:

  (196 row(s) affected) 196 updating DEStock.DBO.ItemStock

  (196 row(s) affected) 196 updating USStock.DBO.ItemStock

But when I look at the transaction logs of the databases in question I get the same view on their logs:

The spid in each database is the same, 6434, however the transaction_id seems to be different in each database.

How is it so?

Also, when running the following script AFTER I did the ROLLBACK on the updtes above I was still getting the same results as before the ROLLBACK.

I had to add the option recompile so that I could see the transaction was not there anymore.

use DEStock
go
select db_name(),@@trancount
go
Select SPID, [Master DBID], [Master XDESID], Operation, Context, [Transaction ID]
FROM sys.fn_dblog(NULL,NULL) where  [Transaction Name] = 'T1_radhe'
option (recompile)

What is the overhead of having transactions across multiple databases? I work with this quite often because I have many markets; each market has a set of databases.

Is there a way to make these transactions run quicker? I was thinking about DISTRIBUTED TRANSACTIONS.

The table used on the joint comes from here:
how to insert a list of varchar values, into a single column table?

This link explains about the function fn_dblog:
SQL Server fn_dblog() Function Details and Example


texts:
I found one of my queries was over written with following code:

SELECT
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
    st.text LIKE  '%StatRiskPrem_udf%'

I believe maybe one of the admins might have done this accidentally because I do not remember having written this code. As a matter of fact I don't even know what the code does but I might have copied it from somewhere and pasted in and saved by mistake myself. Before I go asking the admins if they overwrote my original query, does anyone here know what this code seems to be trying to do? If i knew what the code was trying to do then I might be able to remember if I did this myself or not.
Thanks!


texts:
I need to do database sharding(horizontal) based on MySQL. My database schema is as following:

I have tables A,B,C
A,B is Global(which need not to be sharding)
C have huge data so it need to be sharding to achieve write scalability.
I will have several sharding,each contains one copy of A,B, and a subset of C.

My questions are:

How could i keep A,B on all sharding consistent,i.e, replicate A,B on all sharding so that they should have exactly same data. Or just store the Global Tables on single DB and cache via memcached?
How to do high availability for each sharding,i.e, if one sharding  crashed due to unknown reasons how could i do fail over.Through MySQL backup or master-master AP mode replica?
How to do load balance for Global tables A,B on the multiple shardings?

Note that i will not use MySQL read replicas in this case.


texts:
I am trying to merge 2 large queries together and can't get it to work. I have an almost working query, except I can't get the server to accept the p.products_id in the nested query. Can anyone help work around this?
The strange inner select with many UNION ALL generates a list of all dates in a range. (adapted from https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range).
The number of levels of nesting seems excessive, but it's the only way I could get the query to work at all.
select p.products_id,
       ( SELECT count(*)
         from ( select ( SELECT if(v1.quantity, v1.quantity, null)
                         FROM inventory_history v1
                         where v1.products_id = p.products_id
                           AND v1.inventory_date <= t1.date
                         ORDER BY v1.inventory_date desc limit 1
                        ) as quantity
                FROM ( SELECT a.date
                       FROM ( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
                              FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
                              CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
                              CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
                             ) a
                       WHERE a.date >= curdate() - INTERVAL 400 DAY
                      ) t1
                having quantity is not null
               ) x
        ) as count
from products p
where p.master_categories_id=264;

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'where clause'

What I'm trying to do: inventory_history contains snapshots of inventory levels by date. The data is stored sparsely -- only adds a record when inventory changes. Most of the query is converting that sparse data into results for all dates in range, then I want to count the number of days where the inventory quantity is > 0.
Server version: 10.1.35-MariaDB FreeBSD Ports
Fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e199d540f40e4362e6aa1d667e17fcac


texts: Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed?

texts:
Sorry for the confusing title, I wasn't sure what to write there.

I have a table of a few hundred records. I need to assign each record of this table to a much smaller dynamic table of users, and the users should alternate as to what records they get assigned.

For example, if TableA is

Row_Number()    Id
1               1
2               2
3               3
4               4
5               5
6               6
7               7
8               8
9               9
10              10

and TableB is

Row_Number()    Id
1               1
2               2
3               3

I need an end result set that is

UserId          RecordId
1               1
2               2
3               3
1               4
2               5
3               6
1               7
2               8
3               9
1               10

I've managed to do something a bit messily using the mod operator, but I was curious if this same query could be run without the temp table and the variable.

The temp table is used because TableA is actually a User Defined Function that converts a comma-delimited string to a table, and I need the Count of the objects from the UDF.

-- Converts a comma-delimited string into a table
SELECT Num as [UserId], Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
INTO #tmpTest
FROM dbo.StringToNumSet('2,3,1', ',')

DECLARE @test int
SELECT @test = Count(*) FROM #tmpTest

SELECT *
FROM #tmpTest as T1
INNER JOIN (
    SELECT Top 10 Id, Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
    FROM TableA WITH (NOLOCK)
) as T2 ON T1.RowNo = (T2.RowNo % @test) + 1

Its important that the UserIds alternate too. I cannot assign the top 1/3 of the records to User1, second 1/3 of the records to User2, and 3rd 1/3 of the records to User3.

Also, the UserIds need to maintain the order in which they were originally entered in, which is why I have a Row_Number() OVER (ORDER BY (SELECT 1)) in the User's table

Is there a way of joining these tables in a single query so I won't need to use a temp table and variable?

I'm using SQL Server 2005


texts: Fragmentation with PXC setup using an ordered UUID as PK

texts:
I am looking to implement a table which will accessable via a Percona XtraDB cluster of 3+ nodes. I am expecting it to grow at about 1mil rows per month and the data will be kept for about 2 years (24mil rows).

The schema is reletively simple, but for brevity I will ommit the boring parts, using a PK of a binary(16) with an ordered UUID value. Details of this can be seen here (https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/).

On a single machine this would/should always result in a nice time-based insertion order (due to the ordered UUID v1) and this will keep the data in its intended order within the structure. However, if I start using multiple nodes in a PXC then the UUID being generated could be out of order time-wise on different hosts. Even keeping clock drift to an absolute minimum, with round trips across multiple datacenters, I could not be 100% sure that a timestamp would always be after the last one.

My conclusion would be to make a BIGINT field, with AUTO_INCREMENT enabled, the first part in a compound PK - but then, as far as the DB is concerned, I may as well do away with the UUID and just rely on the BIGINT.

Does anyone have any thoughts on this; am I missing something? It seems that the use of a ordered UUID v1 in a cluster can actually make things worse...


texts: Sql server 2012 grant role permission to just validate view but not select data from it

texts: Is there a permission in SQL server 2012 to just validate/ existence an object (in my case it's a view) but not a select permission? Idea is if a usergroupA try select a view it just should not fail and return no records. Thanks in advance!


texts: Backup or export Azure SQL Database with Row-Level Security

texts:
I'm having difficulties exporting a Sql Azure database using Row-Level Security.
When using the Export method the following error occurs:

Error encountered during the service operation.
Could not export schema and data from database.
One or more errors occurred.
Conversion failed when converting the nvarchar value 'D' to data type int.

Obviously the RLS causes these problems. But I don't see any other way to export the database.
I need to run a script to mutate data and making a backup beforehand would make me a lot safer.
Anyone got a working backup strategy?
update
This export button


texts:
I was given this essay type question during an interview but did not get the job. The full question was as follows:

How are deadlocks detected and reported in an RDBMS? What are the transaction owner and application developer responsible for ensuring in both detection and prevention scenarios?


texts: My SQL Replication to an online database server

texts:
I have two MySQL Databases:

On a local machine (connected to internet via a static IP)
My SQL database on my webserver (JustHost, which I can access remotely)

I want a replicate my local database to the online one. AFAIK I cannot modify the configuration files of the database. So is there any way to set up replication between these two, or is there any way to accomplish my requirement?


texts: SQL command to remove prefix from a set of tables

texts:
I am no DBA. I have a situation with a MySQL database (5.0.51a) where I want to remove a 'dr_' prefix to all tables having this prefix (about 110 tables). I could rename them one by one of course, but I was wondering whether there was an SQL command to perform this in one shoot?

To make it clear, a table called dr_hjkd would have to be renamed hjkd. A table called rfefd would keep the same name. Thanks.


texts: View permission to SQL Server logs on SSMS GUI

texts:
I would like to give a user see SQL Server logs from GUI, not through xp_readerrorlog. I was just wondering if this was possible?

Thank you.


texts: How to fix all duplicate key value violates unique constraint

texts:
I have a Postgres database that I created with a Django application. When I transferred it over, it never reindexed anything and it gives me errors trying to do this. It seems as if there are lots of tables that have this issue. Is there a way I can use this code that I found on another question and use it for all sequences and tables?

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1)


texts: If some object is being changed when  data pump is run how will that object go into datapump file?


texts:
Related to one of my previous questions: How to select a subset from a query result

I now have this Query:

CREATE TABLE #JourneyLogs
    (
        JourneyStartId INT,
        JourneyEndId INT,
        AssetId INT,
        [Event] INT,
        JourneyStart DATETIME,
        JourneyEnd DATETIME,
        GeofenceId INT,
        JourneyDistanceKM FLOAT,
        JourneyTime INT
    )

    INSERT INTO #JourneyLogs
        SELECT
              JourneyStartId
            , JourneyEndId
            , AssetId
            , [Event]
            , JourneyStart
            , JourneyEnd
            , GeofenceId
            , JourneyDistance = ROUND((SUM(DistanceCoveredK) / 1000), 3, 1)
            , JourneyTime
        FROM (
            SELECT
                 JourneyStartId = ignOn.iVehicleMonitoringId
                ,JourneyEndId = ignOff.iVehicleMonitoringId
                ,AssetId = ignOn.iAssetId
                ,JourneyStart = DATEADD(hour, @fGmtOffSet, ignOn.dtUTCDateTime)
                ,JourneyEnd = DATEADD(hour, @fGmtOffSet, ignOff.dtUTCDateTime)
                ,[Event] = ignOff.eEventCode
                ,DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
                                        LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime))
                ,GeofenceId = ignOn.iGeofenceId
                ,JourneyTime = DATEDIFF(SECOND, ignOn.dtUTCDateTime, ignOff.dtUTCDateTime)
            FROM VehicleMonitoringLog ignOn
            CROSS APPLY (
                SELECT top(1) iVehicleMonitoringId, eEventCode, dtUTCDateTime, sptGeoLocaitonPoint
                FROM VehicleMonitoringLog WHERE
                iAssetId = ignOn.iAssetId AND dtUTCDateTime > ignOn.dtUTCDateTime AND eEventCode = 2
                ORDER by dtUTCDateTime
            ) ignOff
            INNER JOIN VehicleMonitoringLog p ON p.iAssetId = ignOn.iAssetId AND p.dtUTCDateTime >= ignOn.dtUTCDateTime AND p.dtUTCDateTime <= ignOff.dtUTCDateTime
            WHERE
                ignOn.dtUTCDateTime > @utcStartDate AND ignOn.dtUTCDateTime < @utcEndDate
                AND ignOn.iAssetId in (Select iAssetId FROM #tblAssets)
                AND ignOn.eEventCode = 1
        ) g

        GROUP BY AssetId, [Event], GeofenceId, JourneyStart, JourneyEnd, JourneyTime, JourneyStartId, JourneyEndId

This takes 19 seconds and returns 4830 rows, with STATISTICS IO:

Table '#JourneyLogs________________________________________________________________________________________________________000000000148'. Scan count 0, logical reads 4864, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 146540, logical reads 857406, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VehicleMonitoringLog'. Scan count 9719, logical reads 64578, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tblAssets__________________________________________________________________________________________________________000000000147'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.

(4830 row(s) affected)

Execution Plan: Here

Is there a way to improve this query?


texts: Using WITH together with CREATE TEMPORARY TABLE AS SELECT ... in Postgres?

texts:
I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE).

e.g. something along the lines of

WITH a AS (
SELECT 1 foo, 2 bar
), b AS (
SELECT 4 bar, 5 baz
)
CREATE TEMPORARY TABLE foo AS
SELECT * from a JOIN b ON (a.bar=b.bar)

If I comment out the CREATE TEMPORARY TABLE line, this works. How to create a temporary table using the query result of the SELECT, without rewriting the temporary views into a single query?


texts: Is it possible to allocate data free for certain table?

texts:
After deleted a large dataset (~50GB) and optimized tables, some tables have data free (~50GB) and parts of tables have only data free (~KB). As my understanding, the data free are reused and not keeping the ibdata growing.

The situation is: My commonly used tables are growing really fast, but unfortunately the data free are allocated to not commonly used tables. The disk space of ibdata is keep growing but the data free is not reused.

The question is: Can the data free allocated to commonly used tables, so it can reuse the (~50GB) space, since the storage is costly on cloud?


texts: Disk size and log file growth during upgrades

texts:
I have four SQL Server databases. Per the maximum load requirements, each database file can be between 1 and 2 TB in size. The HDD is a multi-disk DAS with about 9TB of space. During a database upgrade, the transaction log file for a single database can grow up to 4TB in size. (yeah, I added a new column to a multi-billion row table.) The databases are on Simple Recovery model.

I can find no recommendation (or rule of thumb) from Microsoft about how much disk space I need for a given database size. I get that it can vary widely. If my understanding is correct, it's the largest transaction which I need to consider since space internal to the log file is re-used for different transactions. I think the log growth is set to 10%, but even if it were set to 1 or 2 Gig increments, I think I'd still be cutting it close.

Is there some strategy I need to employ to avoid such huge log files or do I simply need more disk space to avoid running out of space during an upgrade? If I'm doing something wrong, I want to learn how to do it the proper way.

I'm using SQL Server 2012.
Thanks!


texts: Oracle 11g increasing shared pool size and java pool size

texts:
i need to install/configure oracle jvm on 11g. to be able to install same i need to increase the size of shared_pool_size and java_pool_size. i have automatic memory management enabled. my sga_max_size = 512M.
Can any one advise how to change the shared/java pool.

Thanks for the help.


texts:
I am trying to create the following trigger:

CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
    FOR EACH ROW
    BEGIN
            if new.wp_metakey = first_name then insert into test_customer(first_name) values (new.wp_meta_value);
    END

I keep getting a 1064 error that says, "You have an error in your SQL syntax; check the manual that corresopons to your MySQL server version for the right syntaxt to use near " at line 5"

What am I doing wrong?


texts: Use serial ID for multiple columns and return it from INSERT

texts:
I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value.  This was done fine in MSSQL but I'm now trying to support PostgreSQL and I can't seem to get it right.

The SQL is similar to this:

insert into mytable (name, uniquecallid)
values (@name, @uniquecallid);

DO
$do$
BEGIN
IF coalesce(@UniqueCallId,'') = '' THEN
    SET @UniqueCallId = coalesce(@CallIdPad, '')
                     || cast(varchar(50), select currval('mytable_id_seq'));
    UPDATE mytable SET UniqueCallId = @UniqueCallId
    WHERE Id = select currval('mytable_id_seq');
END IF;
END
$do$

select currval('mytable_id_seq');

This errors with a "syntax error near select".

What can I do to get this to return the latest value inserted?

I believe this is a Npgsql issue as when inspecting the SQL that it executes the parameters are replaced with the values but the DO block doesn't have the values in place of parameters.


texts: Is there a way to include an assertion about the expected number of rows?

texts:
I have a function that abstracts updating event data:

async (
  connection: DatabaseConnectionType,
  id: number,
  venueId: number,
  movieId: number,
  cinemaMovieNameId: number,
  date: string,
  time: string,
  url: string | null,
  seatingIsAllocated: boolean | null,
  reservationFee: number | null,
  reservationPerTicketFee: number | null,
  guide: $PropertyType<EventResultType, 'guide'> | null
): Promise<number> => {
  const timezone = await connection.maybeOneFirst(sql`
    SELECT
      locality.timezone
    FROM venue
    INNER JOIN locality
    ON locality.id = venue.locality_id
    WHERE venue.id = ${venueId}
  `);

  if (!timezone) {
    log.error({
      venueId
    }, 'timezone not found');

    throw new Error('Timezone not found.');
  }

  // @todo Prevent unncessary updates. See updateVenue and updateTicket.
  await connection
    .query(sql`
      UPDATE
        event
      SET
        venue_id = ${venueId},
        movie_id = ${movieId},
        cinema_movie_name_id = ${cinemaMovieNameId},
        starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE (${timezone}),
        url = ${url},
        seating_is_allocated = ${seatingIsAllocated},
        reservation_fee = ${reservationFee},
        reservation_per_ticket_fee = ${reservationPerTicketFee},
        guide = ${guide ? JSON.stringify(guide) : null}
      WHERE
        id = ${id}
    `);

  return id;
};

It seems redundant separate locality.timezone lookup query from the UPDATE query. However, combining these two queries requires asserting within a query that timezone exists.

Is there a syntax to assert how many results a query returns?

Something that would allow me to rewrite the above into ~:

await connection
  .query(sql`
    WITH timezone AS (
      SELECT
        locality.timezone
      FROM venue
      INNER JOIN locality
      ON locality.id = venue.locality_id
      WHERE venue.id = ${venueId}
      -- Made up syntax
      ASSERT ROW COUNT 1
    )
    UPDATE
      event
    SET
      venue_id = ${venueId},
      movie_id = ${movieId},
      cinema_movie_name_id = ${cinemaMovieNameId},
      starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE ((SELECT timezone FROM timezone)),
      url = ${url},
      seating_is_allocated = ${seatingIsAllocated},
      reservation_fee = ${reservationFee},
      reservation_per_ticket_fee = ${reservationPerTicketFee},
      guide = ${guide ? JSON.stringify(guide) : null}
    WHERE
      id = ${id}
  `);


texts:
Aim: backup a database and restore it in database2

pg_dump

A PG Dump has been created by issuing the following command:

c:\Temp>pg_dump -U postgres postgres > backupTest2.sql

Opening backupTest2.sql shows SQL statements.

restore pg_dump

However, once the backup would like to be restored in another database nothing seems to happen (number of tables in database2 remains 0, command hangs).

c:\Temp>psql -U postgres -d utrechtTest > backupTest2.sql

^C


texts:
To what extent can the atomic storage of MongoDB leave your database unsafe after inserts or updates on my data?

In other words: What are the possibilities of my data not been (inserted or updated) on collection?

Imagine a big application running, many inserts/updates and other things on the same time and asynchronous, I want know if that are a problem? What the risks to my data?


texts: Partitions and Indexes, in which cases should be used

texts:
I know some knowledges:

Partitions used to achieve better performance (like described in this article)
And indexes used to increase performance in selection operations.

And as result my question: in which cases i should use indexes and in which cases i should use partitioned tables.

In other words what should be better in different operations(selections,deletions,updates) indexes or partitioning of table, and why.

Thanks.


texts: Unique foreign key constraint between three tables

texts:
I'd like to track appointment attendances. Visitors can have appointments, or they can just turn up. If they have an appointment, and miss it, in which case I'd like to record a reason for the missed appointment, if known.

These tables seem to cover it:

appointments: id, visitor_id, scheduled_time
attendances: id, appointment_id, arrival_time
missed_appointments: id, appointment_id, reason

How would I add a constraint such that an appointment can't be missed and attended? Is this schema just overcomplicating it?


texts: Can mariaDB contain indices only with data coming from remote tables?

texts:
I'm trying to think through a setup with a central mariaDB instance (A) accessing data on physically remote servers (B).

All queries to the database (A) can be answered from indices only. Indices are read-only that is re-indexing is triggered from the remote-server (B) at certain intervalls (once per day).

I'm far from a specialist in database things, so when searching around and finding mariadb CONNECT I was wondering:

Question:
Is it at all possible to have a mariaDB database (A) containing "indices only" and the underlying tables being kept on remote locations (B)? If it was, is it feasable?

Thanks for some ideas!


texts:
We are deploying our database model (vs db project) to several customers via a setup routine. Internally a dacpac process is responsible for the upgrade process, if an already existing installation will be upgraded.
Our customers uses SQL Server 2012 up to SQL Server 2017, but our codebase has to maintain the least common feature. eg. STRING_SPLIT isn't useable, because it was introduced in SQL Server 2016.

My idea is to have target specific T-SQL funtions which are deployed depending on their target system. If target system equals SQL Server 2016, than STRING_SPLITT, else self made solution is taken.

any ideas?


texts: How to create a table in access and automatically fills it with information from a query?

texts: Is it possible if I create a table that will lookup for a value filtered from a query (consult), in this case is duplicated "Name"s from a Employees registring table and automaticly fills up that number of registers, then the only thing that I need to change is the values of other fields I made in this new automatic-table?


texts: Identify mysqldump activity in MySQL log file?

texts:
I have a database that is being regularly backed up by mysqldump by... something. It's not a script that I wrote, not sure whose it is. I'm trying to find out more about these dumps, such as what database user they are being executed as. Is there a way to identify an execution of mysqldump in the regular mysql logs?

The database being backed up is hosted on a mysql server, version 5.5.49.

I can't provide the options used to call mysqldump because mysqldump is not called from any script of mine. I am trying to find the script (it does not appear to be in any normal cron job - I think it is someone's custom script), and I think that if I can determine what user it runs as, I can probably find the person who wrote it.


texts: Replace all Numeric Values with Single Character

texts:
I have a value in a SQL table like the below:

80400365_SwapOpenTrd_20180831.csv

I want to replace the numeric values so it is returned as this:

%_SwapOpenTrd_%.csv

I tried the below code but there are groups of % characters returned...

%%%%%%%%_SwapOpenTrd_%%%%%%%%%csv

What do I need to change here to get desired result?

http://sqlfiddle.com/#!18/9eecb/33261

DECLARE @Temp nvarchar(150) = '80400365_SwapOpenTrd_20180831.csv'

Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z_% ]%' -- keep underscore and space
While PatIndex(@KeepValues, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')

select @Temp


texts: Rollback database to old one without backup

texts:
I accidentally restored an SQL Server database from the wrong backup using the following statement:

RESTORE DATABASE  [" + conn.Database + "] FROM DISK='" + path_backup + "'"

This is the exact query I used. I'm not an expert, so I don't really know why this worked, but it actually did.

I did not use REPLACE. Is there any way that I can rollback to previous state of the database? I've read that REPLACE overwrites the database, does this mean that if I don't use it, there is a copy somewhere?


texts: DB design advice - shortcut foreignkeys vs long queries

texts:
I've designed quite a few databases but there's a problem I come across time and time again. The more information you want to pack into your database and the more fields and different properties you add to stuff, you end up with loads of tables that separate the objects that are semantically very closely related. Example:

The events have a M2M relationship to Identification, which is e.g. an email address, a phone number etc. that identifies a Person. All events happen through an Identification. Not all Identifications have an attached Person (if nothing is known about the Person, then the Person object isn't needed) and not all Persons are Agents (but some of them are).

A commonplace query on that database would be to get all Events related to Agents for example, or get all Events for one Team. With this schema however you have to perform those queries over 4-5 tables which is both cumbersome and probably quite slow once you have rows in the range of millions (you're welcome to comment on the performance as well). On one hand if I wanted to simplify things and speed queries up, I could just connect Event directly to Agent/Team/Person via another M2M table or a Postgres FK Array, but that would be duplicating the relationships.

1) Is there a better design pattern for this database?
2) What are the best practices when it comes to shortcut FK-s for tables that are related but through n+1 other tables?
3) Is this even a legitimate concern or am I optimizing this prematurely? Maybe this schema is fine and I'm worrying needlessly?

PS. This is a redesign of an existing database that I know will have millions of rows, so the "don't waste energy on optimizing stuff that may never be necessary" point is not valid here.

Thanks!


texts:
I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't found any solution for this problem.

How can I backup only the schema with a job?


texts: Updating SQL table from Excel sheet (using powershell)

texts:
I am using the below code to complete a bulk import into SQL, but I think I have run into a problem with my method.

$serverName = "pjim-app-02\tsg";
$databaseName = "User_Data" ;
$tableName = "dbo.AD$" ;
$filepath = "\\pjim.bne.catholic.edu.au\emmauscollege\media\cardexchange\carddata.xlsx";

#create object to open Excel workbook
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($filepath)
$Worksheet = $Workbook.Worksheets.Item(1)
$startRow = 2

#create System.DataTable
    $dt = new-object "System.Data.DataTable"
    [void]$dt.Columns.Add("StaffStudentID", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("SurName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("FirstName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("Year", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("HomeGroup", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("DOB", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("StaffStudent", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("email", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("ImageName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("CardKey", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("F11", [System.Type]::GetType("System.String"))
    Do {
        $ColValues1 = $Worksheet.Cells.Item($startRow, 2).Value()
        $ColValues2 = $Worksheet.Cells.Item($startRow, 3).Value()
        $ColValues3 = $Worksheet.Cells.Item($startRow, 4).Value()
        $ColValues4 = $Worksheet.Cells.Item($startRow, 5).Value()
        $ColValues5 = $Worksheet.Cells.Item($startRow, 6).Value()
        $ColValues6 = $Worksheet.Cells.Item($startRow, 7).Value()
        $ColValues7 = $Worksheet.Cells.Item($startRow, 8).Value()
        $ColValues8 = $Worksheet.Cells.Item($startRow, 9).Value()
        $ColValues9 = $Worksheet.Cells.Item($startRow, 10).Value()
        $ColValues10 = $Worksheet.Cells.Item($startRow, 11).Value()
        $ColValues11 = $Worksheet.Cells.Item($startRow, 12).Value()
        $startRow++
        $dt.Rows.Add($ColValues1,$ColValues2,$ColValues3,$ColValues4,$ColValues5,$ColValues6,$ColValues7,$ColValues8,$ColValues9,$ColValues10,$ColValues11)
        }
    While ($Worksheet.Cells.Item($startRow,2).Value() -ne $null)

$Excel.Quit()

#connect to SQL Server and import the system.data.table
$SQLServerConnection = "Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $SQLServerConnection
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.WriteToServer($dt)

This will only import everything, not just the changes.
I thought I would be able to get around this by enforcing a unique key, so that it would not import the records that already exist, but instead it just errors out during its bulkcopy stage.

Can someone please advise if there is a way using the above code that can complete this as an update?

As a work around I was going to import it into a new table, sync the new table with the existing one inside of SQL, then wipe the data from the new table ready for the next time the script runs.

This is for a rather simple recording database, and will only need to be run once a day.

Can anyone advise if I can re-use this script (as its already partly in place), or if there is a better way to achieve this (I have been unable to get the excel sheet setup as a linked table due to permissions)?

Thank you,
Wofen


texts:
I am new to EF and Database Design. I have been studying about using Repository Pattern and Unit of Work a lot, and I have seen a lot of queries made with LINQ.

But I have one question. If, in future, we change the schema of the database, the repository layer is bound to change, because the entities themselves will change. In other words, the repository layer is tightly coupled with the database structure.

But, if we encapsulate most of the possible transactions using a stored procedure in the database itself, and then import them using EF, then change in the database design will not affect the repository layer so much. In other words, the layers would be like this:

DATABASE TABLES <--->  STORED PROCEDURES <---> EF <---> REPOSITORY <---> APPLICATION LOGIC

Why don't we follow this pattern, or is there any pitfall that I am overlooking? Doesn't this loosely couple the application with the database?

I thought that using LINQ also binds the schema with the application, which is my concern. If in future, I want to further normalise my database, it would mean rewriting the whole LINQ queries. It's one more reason I prefer views instead showing raw tables. It's like using properties instead of fields in classes. It adds a layer of encapsulation.


texts: Creating a Trigger to Update Matching Records in Another Table

texts:
Morning, All -

I'm currently working on some SQL logic, and wanted to get some input on it.

What I'm trying to accomplish is the following (Psuedo-code):

When a record is inserted into Table A -
IF inserted.Field5 = 'ABC'
    Select records from table B, where -
        TableB:Field1 = inserted:Field1
        AND
        TableB:Field2 = inserted:Field2
    Set TableB.Field3 for selected record(s) to 'Hello World'

While I've worked with SQL in the past, I usually use external programs and languages to manipulate data.

Using SQL directly, it looks like the best way to do this is a trigger.

I've come up with a rough approximation (below) of what this might look like, but wanted to get some input before I went too much further with it.

CREATE TRIGGER I_Am_A_Trigger
    ON TableA
        AFTER Insert
            AS
                IF inserted.Field5 = 'ABC'
                    UPDATE [TableB]
                    SET [Field3] = 'Hello World'
                    WHERE
                        [Field1] = [inserted].[Field1]
                        AND
                        [Field2] = [inserted].[Field2]

Does this look like I'm heading down the right track?

Also - how can I best deal with the possibility of multiple records being inserted at once?


texts: Adding a new table to a database - are my dependencies off?

texts:
So I'm adding some functionality to an existing table for a system I'm working on. It has a few foreign key relationships and it follows the snowflake design, please see below. I've censored out the table columns for now and some of the table names for simplicity etc.

As you can see we have interactions which happen with a user, who is assigned to a client. So we have the interactions reference a user, which references a client. The fact table of this database is table D, which stores user responses in the interactions.
Each client has a script that they use to interact with the users. Originally it was stored in the client table, but now since we're doing multiple different interactions with users
Now we're going to have different scripts of interactions, so to track this we'll be adding a new table listing the different scripts, which will depend on the client.

My main question is whether this will be an issue in terms of the relationships between the tables, or break any design patterns. There isn't a cyclic dependency there so that shouldn't be an issue, but I realise this will probably no longer be a snowflake design, seeing as there is more than one way to get from one interaction to a client. Any ideas on whether there's a better way to do this? Alternatively, is this what I should be doing? If so/not, it'd be good to see some documentation to back this up, as I've not been able to find much online.
UPDATE
I have come up with a potential workaround, we can have this table like this, and for the purposes of the star schema/snowflake schema we can create a view, which consolidates the interactions, users and clients into one table, and then we can treat them just like any other snowflake table.

Does this seem like a reasonable idea? It'd be great to hear you folks' thoughts on whether this is necessary or not.


texts: Is there ANY risk in granting a superuser CREATE and ALTER procedure permissions in a specific schema?

texts:
I have a developer who has created a set of procedures/functions/views he uses to perform configuration analysis in a large application (provides much higher productivity than using the formal application UI).

The plan is to deploy these procedures into the production environment into an isolated schema named [CONFIG_ANALYSIS], as well as granted EXEC and SELECT permissions to the superuser within this schema, as well as db_datareader. This user will not have insert, update, delete permissions as they should not be altering data via the backend.

It would be preferable if the developer was able to modify and create new procedures within this schema:

GRANT CREATE PROCEDURE TO [UserName];
GRANT ALTER ON SCHEMA::[CONFIG_ANALYSIS] TO [UserName];

Is there any risk (even theoretical) in allowing this ability to modify and create new procedures within this isolated schema?  (Performance issues excluded, as the decision has already been made to allow adhoc queries and select procedures to be executed).


texts: Can't accept pgbouncer 6432 port on PostgreSQL server

texts:
pgbouncer server (440a:90a0:302:4525:lda0:1:2:3)

pgbouncer.ini

[databases]
app_db = host=pg_server port=5432 dbname=app_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser

Test connection:

-bash-4.2$ psql -p 6432 -h pg_server -U postgres app_db
psql: could not connect to server: Connection refused
    Is the server running on host "pg_server" (340b:c210:402:4545:3a1a:1:2:3) and accepting
    TCP/IP connections on port 6432?

Network ports:

sudo lsof -i -P -n | grep LISTEN | grep 6432
pgbouncer 160039 postgres    7u  IPv4 39990293      0t0  TCP 127.0.0.1:6432 (LISTEN)

postgresql server (340b:c210:402:4545:3a1a:1:2:3)

Telnet pgbouncer server with 6432 port:

$ telnet -6 440a:90a0:302:4525:lda0:1:2:3 6432
Trying 440a:90a0:302:4525:lda0:1:2:3...
telnet: connect to address 440a:90a0:302:4525:lda0:1:2:3: Connection refused

Try to connect to pgbouncer server 5432 port(postgresql server installed on pgbouncer server):

telnet -6 440a:90a0:302:4525:lda0:1:2:3 5432
Trying 440a:90a0:302:4525:lda0:1:2:3...
Connected to 440a:90a0:302:4525:lda0:1:2:3.
Escape character is '^]'.

Why can't accept 6432 port on postgresql server(340b:c210:402:4545:3a1a:1:2:3)?


texts: Performance differences between RID Lookup vs Key Lookup?

texts:
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID?

Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)


texts: Excluding log shipped DB from current database maintenance plan (Ola's solution)

texts:
The Senior DBA and I were discussing our most recent rollout of log shipping to 25+ servers and one of the issues we had ran into during our pilot was the log chain breaking randomly.
We narrowed down several other factors that, at one point, were causing this issue (VEEAM backups were not using "copy only" in application-aware processing). However, after resolving many of the issues, one of our colleagues mentioned that we should remove the log shipped db from any maintenance plan we had in place.
But, and I cannot remember where, both my Senior DBA and I were under the impression that we had read SOMEWHERE that log shipped dbs were ignored by default. Despite this, we removed the log backups (including full and diff just to be sure) and that resolved the issue.
My question is this: Should we exclude the backups? And then the follow up question is whether they should be excluded from Ola's and/or MSFT's plans?
Regardless, our issue is resolved - this is just more of question of checking our sanity that we did read this somewhere.


texts: Disk space efficient foreign key index for insert intensive scientific database?

texts:
I'm working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is starting to cause some problems since the table size is 235 GB with index sizes of 261 GB, and the server only has 800 GB so we would like to free up a bit of space.
Currently there is one foreign key reference (integer data) that is stored as a clustered b-true. This has been good for the summary queries, but likely isn't helping the disk space issues.
Is there a more disk efficient way of storing this foreign key index? Would it make sense to switch over to a hash index instead of the b-tree index?


texts: Error while restoring a Database from an SQL dump

texts:
I am  extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:

$ >mysql -u root -p -h localhost -D database -o < dump.sql
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'SQLite format 3'.

I tried $ > mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql but this gave me the following ERROR at line 1: Unknown command '\☻'.
It is a 500 Mb dump file, and when I view its contents using gVIM, all I can see is expressions and data which is not comprehensible. Also when I try to copy contents from the file to post here all I can copy is :SQLite format 3 This kind of seems strange.


texts: 1 hour query, is there another way to do this?

texts:
I saw a couple questions similar to this but none could answer my question, really.

I have two tables, and I am trying to find those rows where a match is found.
user has about 350,000 entries and match has about 55,000 entries. There are UNIQUE indices on both user.name as well as on match.string.

My query is:

SELECT user.name
FROM user
INNER JOIN match
on (
      (match.type = 'exact' AND user.name = match.string)
   OR (match.type = 'begin' AND user.name LIKE CONCAT(match.string, '%'))
   OR (match.type = 'end' AND user.name LIKE CONCAT('%', match.string))
)

The query is running for an hour now, and no end in sight.
Is there a faster way to do that?

I don't need this for regular production, but I think a query that runs for so long is unacceptable.


texts: Insert statement on one table blocking delete on another unrelated table on sql server

texts:
I performed a load testing on our environment,where for eg. stored procedure say "sp" was called multiple times to process on different set of ids.

During load testing this store procedure encountered multiple blocking over single record situated in table say "A"

Out of all these blocking, first blocking was occurred while the proc was deleting some records from table A in one session and blocker script was trying to insert few records in table B in another session.

But table A and B does not have any relation between them (no foreign key over each other and no triggers)
Although they have primary key which is a foreign key on some other common table. But I think that should not be the issue.

the mentioned blocking has wait type : LCK_M_U

I could not figure out the exact reason, which is causing the blocking.


texts: What is the best way of implement a Service Discovery like ETCD for use in a mongodb cluster? I wish have, 1 master for HA and two nodes, in my master i have a static ip, but nodes maybe can changes ip. When a node has been recreated, this should connect to master mongo for update the replication cluster.


texts: Mitigating MySQL replication delay in single master/multiple slave setup

texts:
For a particular web application that requires high availability I am thinking of scaling out by setting-up MySQL replication with one "write" master and multiple "read" slaves (as explained here http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-scaleout.html).

The only issue that I have is how to mitigate the replication delay between the master and the slaves? For example, let's say that the user posts an item (writing to the master), and very quickly thereafter wants to view his posted items (reading from one of the slaves). What efficient solution can I put in place to make sure that the a read operation from one of the slaves will always have all the content of any previously completed write operation to the master?


texts:
I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that user from being allowed to drop the database, but allow him to drop anything else.

My question is: is there a server level trigger in MySQL similar to MSSql, or is there another method to do this?


texts: SQL How to show information from others tables in select requet

texts:
There are my database

user
id | user_code | user_name
1  | 1x2d       | Peter

shops
id | shop_code | shops_name
1  | 1234      | shop1
2  | 3456      | shop2

articles
id | art_code | art_name
1  | 5678     | computer
2  | 0987     | display

action
id | M_art_code | M_shop_code | M_user_code
1 | 5678        | 1234        | 1x2d
2 | 0987        |             | 1x2d

I would like to SELECT all value of action where .... with all information from other table

Result
M_art_code | M_shop_code | shops.shops_name | M_user_code| user.user_name
5678       | 1234        | shop1            | 1x2d       | Peter
0987       |             |                  | 1x2d       | Peter

I try with

SELECT * FROM user U, shops S, articles A, action AC
WHERE AC.M_user_code = U.user_code
AND AC.M_shop_code = S.shops_code AND M_art_code LIKE %%

But with this SELECT I've just row ths all value not empty. I need all value.
How I can do to have I would like?

PS: sorry for my English...I'm french


texts: Will OPTIMIZE TABLE have any impact on my data?

texts:
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE? Will indexes and primary keys still remain the same? Are there areas of the database that will be slower after the optimization?


texts: How to tell how long autovacuum took to vacuum a postgres table?

texts:
The system table pg_stat_all_tables records when a table was last vacuumed by the autovacuum daemon.
I've noticed a possible correlation between some performance issues and one of the larger tables in my database being vacuumed. However, since I don't know the duration of the vacuuming process, I can't be 100% sure.
How can find I out how long an autovacuum took on any given table?


texts: How to prevent users other than DBAs from creating database links

texts:
To begin, please keep in mind that I am a very green DBA. I have done prior research and have not really found anything too promising, or maybe it just seems that way because I am still struggling in my understanding of the monster that is Oracle. So please forgive me and kindly point me in the proper direction if my question sounds arbitrary.

Scenario:

some users create database links into several heavy data warehouses.
these users neglect to close or drop DB link.
users process continues to run and bogs down the DW's performance.

Goal:

prevent these users from creating database links, BUT still allow DBAs to create DB links

How could this be done? Could the answer be within OID capabilities? It seems that there could be to me, but my understanding of Oracle does not seem sufficient enough to be able to determine for sure.

UPDATE:
After investigating into user privileges, I discovered that privileges are not the problem. A remote database can be accessed with just a select statement and without a dblink. Revoking the CREATE DATABASE LINK privilege would not stop a user from accessing database B from database A.

However, this is only true when the DB_DOMAIN = "blank" and the global name = "fooDB"

A link is required if the DB_DOMAIN = foo.com and the global name = "fooDB.foo.com"

I could not find any documentation similar to this scenario or that explains why this happens. Why do I need to create a link when the global name = "fooDB.foo.com" but not when the global name = "fooDB"?


texts:
I have the following two queries...

mysql> select id, title from events;
+----+-----------------------------------------------------------------------------------------------+
| id | title                                                                                |
+----+-----------------------------------------------------------------------------------------------+
|  2 | Second planning meeting for Musta                                           |
|  3 | Writing for Online Audiences                                                                  |
|  4 | Beginning with the Wintas Header - Aug 2010 |
|  5 | Sausage Monthly Meetup                                                              |
|  6 | Third planning meeting for Musta                                            |
+----+-----------------------------------------------------------------------------------------------+

mysql> select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 group by event_id;
+----------+---------------+
| event_id | num_bookmarks |
+----------+---------------+
|        2 |             1 |
|        3 |             2 |
|        4 |             4 |
|        5 |             8 |
|        6 |             1 |
+----------+---------------+
5 rows in set (0.01 sec)

The 2nd query shows me a total of bookmarks (connectiontype 3) for each event_id. I would like a single query that will show me an event in addition to the total number of bookmarks.

My best effort was...

mysql> select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join event_id on events.id=event_connections.event_id group by event_id' at line 1

The end result would look like be the output from the first query; select id, title from events in addition to a a num_bookmarks column that has a count of bookmarks for that event.

Thanks in advance!


texts: Separate SQL Logins for reading from and writing to table good design?

texts:
This is a question for SQL Server but could probably apply to nearly any SQL implementation.

A client will be consuming a web service API. From a security standpoint, my idea was to ETL all the pertinent data into one table and let the client pull from that table. Let's call the table FoobarTbl. I also am locking down the SQL Login to only have access to this one table and nothing else.

I took it further by creating two SQL Logins:

SQL Login which can execute SELECT to the table. (i.e. FoobarLoginRead (
SQL Login which can execute INSERT and UPDATE to the table. (i.e. FoobarLoginWrite).

Is this a good design for security or is it overkill and problematic? I observed that the UPDATE operation didn't function without adding SELECT, which kinda defeats the purpose of having separate logins for reading from and writing to the same table.

My thought process is: If somehow a nefarious user got the creds for FoobarLoginWrite, they would only have visibility to the one table but also they couldn't actually grab any data. A user nefariously updating or inserting data is an acceptable loss, since the source data is being ETL'd to this table. The bigger concern is grabbing the data from the table.

Am I overthinking this or is this a reasonable approach to locking down SQL Logins?

By "locking down SQL Logins", I mean effective permissions for what resources a SQL Login can access such as tables, views, and so on. In my case, I created two SQL logins. Both have permissions to access one table. And my idea was to give one login SELECT permissions to this one table and the other login gets INSERT and UPDATE permissions to this same table.

In short, what I'm asking is is having two separate logins for the same table, with what I outlined above, a good approach for securing SQL data or am I overdoing it and this would be considered poor design?


texts: Setting up a table with a calculated column

texts:
I'm setting up a table in sql server 2008. It going to hold three columns (busTime, trainTime, carTime). I want these columns to hold the amount of time a user spends traveling per day. I then want to set up a calculated column that will work out the total time per day.

My question is what data type should I use for the three columns and if it's a weird data type that I've not used before (like "time") what would be the calculation that I would use on my calculated column.

Cheers

James


texts: Connectivity timeout between cluster nodes

texts: I lose communication between my clusters almost 1 am to 1.30 am and it shows errors like 35206 on primary, 976 on clusters, sometimes the cluster is removed due to lost heartbeat leading to 1135 error , 1069 error. I have a strong feeling that my connection breaks everyday at that time but im clueless where to start kind of lost. any help will be greatly appreciated.


texts: SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

texts:
I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates.

We have a large table partitioned by date with a couple of billion rows. The partition date is the prior business date and so is an ascending key. We only load data into this table for the prior day.

The data load runs overnight, so on Friday 8th April we loaded data for the 7th.

After each run we update statistics, although take a sample, rather than a FULLSCAN.

Maybe I am being naïve, but I would have expected SQL Server identify the highest key and lowest key in the range to ensure it got an accurate range sample. According to this article:

  For the first bucket, the lower boundary is the smallest value of the column on which the histogram is generated.

However, it doesn't mention the last bucket/largest value.

With the sampled Statistics update on the morning of the 8th, the sample missed the highest value in the table (the 7th).

As we do a lot of querying on data from the prior day, this resulted in inaccurate cardinality estimation and a number of queries timing out.

Should SQL Server not identify the highest value for that key and use that as the maximum RANGE_HI_KEY? Or is this just one of the limits of update without using FULLSCAN?

Version SQL Server 2012 SP2-CU7. We cannot currently upgrade due to a change in OPENQUERY behaviour in SP3 that was rounding down numbers in a linked server query between SQL Server and Oracle.


texts: Why can a SQL Server table not have more than one IDENTITY column?

texts:
Why can a SQL Server table not have more than one identity column?

CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY)

  Msg 2744, Level 16, State 2, Line 5
  Multiple identity columns specified for table 't'.
  Only one identity column per table is allowed.

I understand we can resolve it using a computed column.

As per the product documentation, a table can not have more than one identity column. But, why? What is the real reason behind it?


texts: How can you re-synchronize oracle streams automatically?

texts:
I have noticed that when a process of oracle streams (e.g. capture/apply/propagate) is stopped or disabled, and at the same time, changes are made to the source and/or destination table/database/schema, the changes are not automatically re-synchronized when the streams processed are re-enabled.

You would then need to manually recreate the capture/apply/propagate processes in order for the data to be replicated again. Is there an automated way to do this without manually checking which tables are out of sync and manually recreating each process for each table? Please take note of the following conditions:

Replication is on a per table basis setup and there are alot of tables involved, but not to the extent that schema replication is recommended.
Oracle version used is 11gR2


texts: How do I identify functional dependencies and normalize a table based on the attributes?

texts:
I'm having trouble identifying the combination of attributes which make up Functional Dependencies(FDs) of a particular table. While I understand how Normalization process works when given the FDs, I'm unable to translate and identify FDs for a given table.
I've been tasked to create a database of a company which tracks all expenses of sales/packages etc. Naturally that means storing Customer information for the company to identify all consumers.
CREATE TABLE Customers (
    id        integer primary key,
    address   text not null,
    name      text not null,
    email     text unique not null,
    phone     text unique not null,
    unique(name, address)
);

I've chosen to represent the info for the customer table as such, given that each phone number and email is uniquely tied to the customer. The unique constraint allows for multiple people from the same household address to be present in the table.
I want to see if I can normalize this table further, however I am unable to identify the attributes to determine the FDs needed.


texts: My SQL syntax is error, how to solve it?

texts:
This is the SQL syntax:

WITH max_budget(value) AS (SELECTMAX(budget) FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = max_budget.value;

I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'max_budget(value) AS (SELECTMAX(budget) FROM department)SELECT budget FROM ' at line 1

How can i solve this problem?, help me guys, thanks.


texts: How to Install postgresql-client-11 on Ubuntu 18.04

texts:
I need to use the pg_basebackup/pg_dump program on an Ubuntu 18.04 system to connect to a remote PostgreSQL 11.6 server.

However, the current system only contains pg_basebackup 10.10 and the remote database is using PostgreSQL 11.6. Running pg_basebackup give the error

  pg_basebackup: incompatible server version 11.6

Tried to install the correct version of pg_basebackup using the command:

apt install postgresql-client-11

but the apt package is not found. Only postgresql-client, postgresql-client-10 and postgresql-client-common are available the system's apt repos.

Question: How can we install the correct version of pg_basebackup without having to copy it over from the remote database server, and without installing PostgreSQL 11.6 on this machine?


texts:
I have a question about using the DISTINCT keyword in SQL.
I have a table named Customers.  Each record contains
3 fields: CustID, CustName, CustCity.  5 records show
a CustCity of "Chicago".

I want the SQL results to show just one record for each unique city name.
Also, I just want to see the CustID and the CustName from each record.

I tried this but got a syntax error:

SELECT CustID, CustName DISTINCT CustCity
FROM Customers


texts: How currval of sequence being preserved during the backup and restore in postgres

texts:
If I do backup and restore a huge db (tons of tables in it), will it run without any issues rather rising exception about duplicate key constraints of sequenced column(s).

While taking pg_dump (complete schema / db), how its taking the individual value of a sequence and altering the value with latest value of a table's sequenced column when we do restore?


texts: Database Implementations of ORDER BY in a Subquery

texts:
I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g.

SELECT * FROM (
        SELECT ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl

The application has many different database drivers. I mainly use the MS SQL Server driver, and SQL Server 2008. This throws an error if an ORDER BY is found in a subquery.

From the MS Docs (although this is for SQL Server 2000 it still seems to apply):

  When you use an ORDER BY clause in a view, an inline function, a
  derived table, or a subquery, it does not guarantee ordered output.
  Instead, the ORDER BY clause is only used to guarantee that the result
  set that is generated by the Top operator has a consistent makeup. The
  ORDER BY clause only guarantees an ordered result set when it is
  specified in the outermost SELECT statement.

However the same type of query when run in Postgres (9) and Oracle return results - with the order as defined in the subquery. In Postgres the query plan shows the results are sorted and the Postgres release notes include the item which implies subquery orders are used:

  Avoid sort when subquery ORDER BY matches upper query

http://en.wikipedia.org/wiki/Order_by states:

  Although some database systems allow the specification of an ORDER BY
  clause in subselects or view definitions, the presence there has no
  effect.

However from my own checking of query plans:

SQL Server 2008 does not support ORDER BY in a subquery
Postgres 9 does support ORDER BY in a subquery
Oracle 10g supports ORDER BY in a subquery

So my question are there any links that can officially confirm or deny that Postgres and Oracle do not allow sorting in a subquery?


texts: How do we implement SQL Server IF EXISTS (SELECT) in MySQL 5.6?

texts:
We would like to convert the following SQL Server statement:

IF EXISTS(SELECT OptionValue
          FROM Options
          WHERE OptionCatagory = 'General' AND
                OptionName = 'HVRCustomPortNumber')
   UPDATE Option
   SET OptionValue = @custportnum
   WHERE OptionCatagory = 'General' AND
         OptionName ='HVRCustomPortNumber'
ELSE
   INSERT INTO Options (OptionCatagory, OptionName, OptionValue)
   Values
   ('General', 'HVRCustomPortNumber, @custportnum);

to MySQL version 5.6 so that it runs correctly using C# MySQL Connector .NET on Ubuntu Linux 15.10.

I tried googling how to do this and could not find any "hits" that passed the MySQL GUI Query Analyzer.


texts: How can I have long waits on Tracewrite, when I haven't run Profiler?

texts:
I recently setup a SQL Server which is used only ot run a sharepoint Server.
I love to query my wait stats, just to see what we are waiting on.
Most of the wait stats are fine, and I know where they come from and how to improve performance.
When I came to the office today, I saw that I have huge waits on "Tracewrite", but I haven't run any Trace or Profiling session. Is any task/app/anything in Sharepoint able to generate this tracewrite wait?
Google didn't help me much. Or is Tracewrite also generated by some other task?

I am on SQL Server 2014 / Sharepoint 2013 / Windows 2012

A check on:

SELECT * FROM sys.traces

shows that some log is generated in the following path:

\Program Files\Microsoft SQL Server\MSSQL12.SQLSP2013\MSSQL\Log

The event count of 83321 sounds a bit high to me.

This server runs just for some days.


texts: How to store data that depends on dynamic number of parameters

texts:
I can't get my head around this problem. I'm trying to find a good way to store this in a PostgreSQL database.

Each client of my company needs to visualize data that depends on a dynamic number of parameters.

Example:

clientA wants to store the prices of its products. A price is defined by product_id and store_id. I need a way to query the database, giving the product_id and store_id and get back a price.

clientB wants to store the power of its cars. A power is defined by car_id, car_variation and fuel_type. I need a way to query the database, giving the car_id, car_variation and fuel_type and get back a power.

Each client must be able to define its own "dimensions" or "parameters" that define the data they want to vizualize.

I can either :

put all the data in the same table, with A LOT of empty columns for each row ==> seems dumb
create a new table for each client, with only the right columns in each of them? I'm not used to having a table per client but maybe it's the way to go
a third way I did not think of that tackles this exact problem


texts: After "ALTER TABLE MOVE PARTITION" new partitions are still created in the original tablespace

texts:
I moved all objects from one tablespace (hereinafter A) to another (B) .. but when a new partition is created it resises in the original one ..

How can I fix it? I have no idea what to do because everything is moved already

DDL:

CREATE TABLE OWNER_PUBLIC.T_NO_CONNECT_CONTRACTS
   (
      CALL_DATE DATE,
        CONTRACT_ID VARCHAR2(10),
        FLAG_WF VARCHAR2(3 CHAR)
   )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE OWNER_PUBLIC
  PARTITION BY RANGE (CALL_DATE) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
    (PARTITION "P0"  VALUES LESS THAN (TO_DATE(' 2018-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE OWNER_PUBLIC


texts: Active Directory not working through SQL server Management studio

texts:
I have created a Group in SQL Server and add an Active Directory group to it with which contains users.

When I try to login through Windows authentication, it gives me the following error:

Login failed for user 'UserName'. (Microsoft SQL Server, Error: 18456)

But when I add just an AD user separate from the group, it has no problem logging in.

Why doesn't the login work with only the group added as a login?

I have tried changing the Active Directory settings from global to universal type but that didn't change anything

This on Windows 2016 server machine and SQL Server 2017.


texts: Passing parameters to a procedure executed by DBMS_SCHEDULER

texts:
I have a scheduler program that runs a stored procedure that requires input parameters.

BEGIN
  DBMS_SCHEDULER.DROP_PROGRAM
    (program_name          => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'STORED_PROCEDURE'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => NULL
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/

How can I pass parameters to the EXPORT_STATUS procedure?

I changed it into program that runs a PL/SQL BLOCK so far:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'PLSQL_BLOCK'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS(''STARTING''); END;'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => NULL
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/

It works as I expected. But if anyone knows how to keep it in the previous format, will be appreciated.


texts: Postgres Server staying in read only mode after a barman recover command is executed

texts:
All,

I am trying to perform a PITR restore using barman using recover command. The problem is that the server remains in the read-only state once the barman recover is done. Is there a way by which the destination server comes up as a read-write server on its own once the PITR is done? Based on barman documentation, I was hoping --target-action 'promote' might work but that didn't help.

I using barman 2.10 on postgres version 10.12.

Following is the command that I am using to perform PITR:

barman@abc-barman01:~ $ barman recover --target-time  '05/13/2020 14:00:00' --target-action 'promote'  --remote-ssh-command "ssh postgres@abc-pg03" abc-pg01  20200512T203002 /var/lib/pgsql/10/data
Starting remote restore for server abc-pg01 using backup 20200512T203002
Destination directory: /var/lib/pgsql/10/data
Remote command: ssh postgres@abc-pg03
Doing PITR. Recovery target time: '2020-05-13 14:00:00-04:00'
Using safe horizon time for smart rsync copy: 2020-04-04 09:27:29.180929-04:00
Copying the base backup.
Copying required WAL segments.
Generating recovery configuration
Identify dangerous settings in destination directory.
IMPORTANT
These settings have been modified to prevent data losses
postgresql.conf line 219: archive_command = false
WARNING
You are required to review the following options as potentially dangerous
postgresql.conf line 83: ssl_cert_file = 'server.crt'
postgresql.conf line 84: ssl_key_file = 'server.key'
Recovery completed (start time: 2020-05-19 15:38:23.157146, elapsed time: 24 minutes, 21 seconds)
Your PostgreSQL server has been successfully prepared for recovery!
You have mail in /var/spool/mail/barman

Currently, I run pg_wal_replay_resume() manually once the recovery is done to bring the server to read-write mode. I would really appreciate if someone can look into this.


texts: Does anyone have a stored procedure to truncate temporal table?

texts:
Does anyone have or know of stored procedure that will do generic temporal table manipulation so that history and main table are both truncated?

You have to alter it to set system_versioning = off, then drop the history table, then truncate your main table, then re-enable versioning on your main table. I was going to build a generic stored procedure to do this for any database/schema/table name passed to it but sure be nice if someone already had one. I never like re-inventing the wheel if I can avoid it.


texts: Postgres show count 0 for non-matching rows for 1 table

texts:
Let's say I have this schema.

create table foo_access (
    id         serial primary key,
    user_id    integer not null,
    created_at timestamptz not null
);

-- This data set uses user_id=0 to mean null. _shrug_
insert into foo_access (user_id, created_at) values (0, '2020-03-20T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-19T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-18T00:00:00Z');
insert into foo_access (user_id, created_at) values (2, '2020-03-18T00:00:00Z');

I want to see how many different users have accessed foo at least once a day, for the last 5 days.

I have a query like this.

select
  date_trunc('day', created_at) as period,
  count(distinct user_id) as n
from foo_access
where user_id > 0 and
  created_at >= now() - interval '5 day'
group by period

Which gives me a table like this.

         period         | n
------------------------+---
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(2 rows)

Almost what I want, but not quite.

Is there a way to also show the days where no one accessed foo? I'd like the table to look more like this.

         period         | n
------------------------+---
 2020-03-15T00:00:00+00 | 0
 2020-03-16T00:00:00+00 | 0
 2020-03-17T00:00:00+00 | 0
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(5 rows)

SQL Fiddle: http://sqlfiddle.com/#!17/ece05/2/0

This question is somewhat similar to How to count matching values and print 0 for non-matching value in PostgreSQL?, except I'm not joining two tables. I only have 1 table.


texts: Build a simple query and relationship table

texts:
This is my first post here. Thanks for having me!

I have two questions for a table I'm building into my mysql database.

Question 1: I have a need to input an artist name and the result/select be a distinct twitter handle. How might I build this query as efficiently as possible based on an optimal solution for Q2 (Preferably in one line).

Question 2: Based on the requirement above how should I build my table 'twitter' into the database. Below is a start, but happy to discuss best practice.

Simplified Example: Input = 'Beatle'. Desired output '@beatleMe'

table: songs (note artist is not distinct)
ID  ARTIST  SONG
1   Mike J  Beat It
2   Beatle  Yellow Sub
3   Beatle  Lucy Diamond

table: twitter (note artist is distinct)
ID  ARTIST  HANDLE
1   Mike J  @mikeJ
2   Beatle  @beatleMe

I'm from an excel background, so I think in terms of lookups. But I know I can get more out of my database with foreign keys, joins etc (if they are relevant here I'm not 100%). So am keen to learn.

The song table will be maintained most regularly, so it would be good to have the artist columns mirrored/linked is that possible?


texts: How can I start the SSRS 2016 Report Server?

texts: I restarted my SSRS 2016 server. When I navigate to the Web Portal URL I get a "Service Unavailable - HTTP Error 503. The service is unavailable." message. How do I start the SSRS 2016 Report Server?


texts: Best practices to maximize portability in SQL Server 2016

texts:
When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product.

In this scenarios I tend to use Microsoft SQL Server writing the queries as standard as possible to simplify the eventual migration to another server.

Is there a way or some known practice to enforce the use of standard SQL over T-SQL dialect directly in SQL Server or via SQL Server Management Studio (SSMS)?


texts: How can I avoid repeating the same condition in a "when matched then update" clause?

texts:
I want to merge one table into another. I need to apply conditional logic in my WHEN MATCHED clause, which would ideally be done like this:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Do insert
WHEN MATCHED AND b.NeedsAdjustment = 1 THEN
UPDATE SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
WHEN MATCHED THEN -- Default case (b.NeedsAdjustment <> 1)
UPDATE SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3

This is not valid SQL. According to the MSDN documenation:

  If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action.

This leads me to the following query:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Insert happens here
WHEN MATCHED THEN
UPDATE SET
    Col1 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col1 ELSE b.Col1 + b.Adjustment END
    ,Col2 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col2 ELSE b.Col2 + b.Adjustment END
    ,Col3 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col3 ELSE b.Col3 + b.Adjustment END

The conditional logic is moved inside of the update to get around the fact that merges can only have one WHEN MATCHED THEN UPDATE clause. Now, instead of one check per row, I have one check per row per column (and there are many more columns than the three in the example).

Can I avoid repeating this condition for every column that needs to be updated? Is there a better way to do conditional updates that perhaps don't involve merges?


texts: Postgresql - how to securely delete the storage freed when a table is dropped?

texts:
For a system with sensitive information stored in a PostgreSQL 9.5 database, in which data stored in a table that is deleted must be securely deleted (like shred does to files), and where the system is persistent even though a particular data set won't be, I'm trying to figure out my options for securely deleting the underlying data files when a table is dropped.

As background, I'm not a DBA, but I am an experienced implementor in many languages, contexts, and databases.  I've looked and haven't been able to find a way to ask PostgreSQL to do the equivalent of shredding its underlying files before releasing them to the OS when a table is DROPped.  Is there a built-in way to ask PostgreSQL to do this? (I might just not have searched for the right thing - my apologies if I missed something and I'll gladly accept abuse along with links to relevant documentation)

A partial answer we're looking at is shredding the underlying data files for a given relation and its indexes manually before dropping the tables, but this isn't so elegant, and I'm not sure it is getting all the information from the tables that we need to delete.

We also are looking at strategies for shredding free space on our data disk - either running a utility to do that, or periodically replicating the data volume, swapping in the results of the copy, then shredding the entire volume that was the source so its "free" space is securely overwritten in the process.

Are we missing something?  Are there other options we haven't found?  If we have to clean up manually, are there other places we need to go to shred data than the relation files for a given table, and all its related indexes, in the database's folder?  Any help or advice will be greatly appreciated.


texts:
I have data that I extract and from sam.gov and fpds.gov and a few other sources. and I throw the data in a warehouse. The sam data I just throw away at the end of the month and add in the updated data from sam.gov's public extracts.

I am not using a foreign key between the fpds and sam data but there is a relationship between them, and I am able to join them based on a certain key in the data. Should I create a FK relationship between the two tables or just continue without one?

There is a one-to-many relationship from sam -> fpds.


texts: SQL Server Reporting Services: How to check who accessed certain report?

texts:
I am in process of eliminating who should have access to SQL Server Reporting Services reports that I have created.  Is there a way to find out who has accessed certain reports during past month?

My goal here is to remove people who hasn't accessed the report for awhile.


texts: how to sort by timestamp field while counting duplicates

texts:
I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works but it doesn't sort the timestamp field. I've tried adding "ORDER BY time DESC" but it has no effect. How can I retrieve the latest timestamp? The statement below returns the first occurrence of the timestamp.

user|time|action
jones|1-1-2016|actionA
jones|1-2-2016|actionA
jones|2-1-2016|actionB

SELECT user, time, COUNT(*) AS total
FROM table
GROUP BY user
HAVING total > 0

Expected Results:

jones|2-1-2016|3


texts: DB2 Express C 10.5.4 using up all memory for package cache

texts:
The testing environment (Win64) uses a express c - which does pretty well. Now when testing a java batch it runs out of private memory exhausting all available memory (14.8 GB) to package cache. As a first action I put on STMM because I thought this would help. Now, because it did not work out, I disabled the STMM and still something is eating up the memory. The production environment is on DB2 10.1 WSE with 64GB available. I am afraid that one day our applications will run into the same situation on this machine.

In db2diag.log everything started with sqlcode -101 "statement too long".
The output of db2mtrk is:

Speicher wird überwacht am 2015/10/30 um 06:59:18.

Speicher für Instanz

   other       fcmbp       monh
   57,0M       832,0K      1,8M

Speicher für Datenbank: EASY

   utilh       pckcacheh   other       catcacheh   bph (5)     bph (4)
   64,0K       14,8G       192,0K      2,7M        64,9M       64,9M

   bph (3)     bph (1)     bph (S32K)  bph (S16K)  bph (S8K)   bph (S4K)
   82,1M       80,7M       1,8M        1,5M        1,4M        1,3M

   shsorth     lockh       dbh         apph (1149) apph (1148) apph (1147)
   2,3M        46,6M       107,4M      128,0K      128,0K      128,0K

   apph (1146) apph (1145) apph (1144) apph (1143) apph (1142) apph (1141)
   128,0K      128,0K      128,0K      128,0K      128,0K      128,0K

My setting from  get db cfg:

 Speicher mit automatischer Leistungsoptimierung                       (SELF_TUNING_MEM)    = OFF
 Größe des gemeinsamen Datenbankspeichers (4 KB)                       (DATABASE_MEMORY)    = AUTOMATIC(180832)
 Schwellenwert für Datenbankspeicher                                   (DB_MEM_THRESH)      = 100
 Max. Speicher für Sperrenliste (4 KB)                                 (LOCKLIST)           = AUTOMATIC(11200)
 Anzahl der Sperrenlisten pro Anwend. (in )                           (MAXLOCKS)           = AUTOMATIC(97)
 Größe des Paketcache  (4 KB)                                          (PCKCACHESZ)         = 16384
 Sortierspeicherschwelle für gemeinsame Sortierungen (4 KB)            (SHEAPTHRES_SHR)     = AUTOMATIC(5025)
 Zwischenspeicher für Sortierlisten (4 KB)                             (SORTHEAP)           = AUTOMATIC(1005)

If someone has a clue, an idea or hint for me - I would be very glad to hear from you.


texts: Merging two rows into one by creating two new columns based on criteria from same table (SQL)

texts:
the data that I have looks like this

name      year      quarter      type        score
a         2016         Q1         x           40
a         2016         Q1         y           50

I want to write a statement that will merge these two rows into one column output based on the name year and quarter regardless of type by creating two new columns, or one new column. The output that I would like is:

name      year      quarter      type        score      x_score      y_score
a         2016        Q1          x           40          40           50

I tried writing some subqueries, but could not understand how to effectively do this.

Thanks in advance


texts: Question on comparing two fields in Access

texts:
I'm not sure if the title is fitting as I am not 100% sure if what I am doing is correct but here goes.

Here is my enrolment table,

I have added a field called pass/fail as you can see which is a calculated field that holds the following expression. the field displays passed if the overall grade is over 40%.

What I wish to is in my student table shown below create the same field Pass/Fail that checks the student has passed or failed depending on all of his modules.

Let me explain further, as you can see within the enrolment table the student ID is mentioned twice for each student however the module number is different as each student has taken two modules. i want the field within the student table to display passed only if the student has passed all modules.

If anyone can give some advice on how I could possibly go about doing this I would be most grateful. Preferably I would like to do this with the use of an IFf statement however I'm open to other options.

I have included an image of my relationships if that would help.


texts: Is it possible to cascade delete a column value?

texts:
So say I delete catID from table A and table B has the same foreign key value? It's hard to explain so I'll try and show you with the two images below:
items table

Is it possible to delete the catID from the above table (items) when I delete on cascade a value from the below table (categories):
Categories  table:

So say I ran delete from categories where catID = 1 - This will delete both rows from both tables? So essentially it would delete the whole iID instead of just the catID value?
Is it possible to have it delete only the value from the catID column in the items table?
If I'm not making any sense, please let me know? As I'm finding it kind of hard to explain!!


texts: Is Network I/O included in the timings for "RPC complete" seen in SQL Profiler?

texts:
When watching results in SQL Profiler, does the RPC Complete include time to send results to the client?  Or is it just the time the server spent gathering the records?

The reason I'm asking is I'm working on a program to move a large amount of data.  The databases are cloud hosted in Azure, and the code is (currently) running locally.  I can see the RPC call times in SQL Profiler, and the query to select the rows isn't cheap (massive table filtered by an indexed column then sorted by the table's primary key for paging results), and it isn't an insignificant amount of data (100k records per page, some situations I'm moving up to 60 million records per run).

I'm curious if I should expect it to run faster once the code is running inside Azure.  If the RPC complete timing includes the time spent for the data to be sent to the client, this should decrease some (I'm hoping dramatically).  If the RPC complete timing is just the time for the server to select the data, then I won't see much of an increase when it's running inside Azure.


texts: Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

texts:
I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, date ranges, etc) that will be stored separately in a structured format for querying.

Since I don't need to query the data itself, would a NoSQL database offer any advantage over something like a Postgres JSONB field? I'm primarily thinking about read performance, database size, and cost (I realize cost will depend on a number of factors, so maybe that's not easy to anticipate at this high level).


texts: SQL Server connection with multiSubnetFailover fails, claims mirroring is enabled

texts:
I have two SQL Server instances in an availability group. They are not mirrored.

I'm trying to connect to the AG using the MS JDBC drivers (4.0) with a connection string including multiSubnetFailover=true (MSDN) and getting the following error:

  com.microsoft.sqlserver.jdbc.SQLServerException: Connecting to a mirrored SQL Server instance using the multiSubnetFailover connection property is not supported. ClientConnectionId:<hex-id-string> errorCode[0] sqlState[08S01]

It seems to think that mirroring is still enabled. Yet

if I try alter database d set partner off, for example, it complains that the database is not configured for database mirroring.
select * from sys.database_mirroring yields a row full of NULLs for every database.

Is there anywhere else that SQL Server might store some mirroring configuration, or some other way that it's getting confused?

Edit: Worth noting that without the multiSubnetFailover setting, this just works.


texts: installing SQL SERVER 2016 side-by-side SQL Server 2012

texts:
I am trying to install SQL Server 2016 on a server that already has SQL Server 2012 instances installed - all of the shared and instance features of the 2012 version have been installed also. I do not have an option of uninstalling these and I just need to install a separate, independent SQL SERVER 2016 instance.

According to MS, side-by-side is supported. But then it says: "you cannot prepare a SQL Server 2016 instance side by side with a prepared instance of SQL Server 2012"

What does this mean? So can I have them side-by-side without any issues or not?

Thank you very much


texts:
How can we pass the collation to a user-defined function?

e.g. I can use a hardcoded collation (e.g. "de-AT-x-icu") like this:

CREATE OR REPLACE FUNCTION test(agg_1 jsonb, agg_2 jsonb) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
  'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
, 'max', greatest(agg_1 ->> 'max' collate "de-AT-x-icu", agg_2 ->> 'max' collate "de-AT-x-icu")
)
$$;

and the test works:

select test(
  jsonb_build_object('count', 2 , 'max', 'H'),
  jsonb_build_object('count', 0, 'max', 'i')
);

We tried to use a text-argument,

CREATE OR REPLACE FUNCTION test2(agg_1 jsonb, agg_2 jsonb, text_collation text) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
                'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
            , 'max', greatest(agg_1 ->> 'max' collate text_collation, agg_2 ->> 'max' collate text_collation)
            )
$$;

but this fails with:

ERROR: collation "text_collation" for encoding "UTF8" does not exist

What is the best way to pass/use a collation in a UDF in such a case?


texts: Determine whether mixed mode auth is enabled without logging in?

texts: Is it possible in SQL Server to determine whether mixed mode authentication is enabled without logging on to SQL Server?


texts:
I am trying to connect to a remote Oracle 12c database from a client machine that has 11g oracle drivers for ODBC. I can reach to remote machine through ping but can not Telnet the remote machine on port 1521 neither the TNSPING succeeds. When I try test the ODBC connection using my TNS service name I get the TNS timeout error ORA-12170. I can see that the traffic is allowed in the firewall in between.While the same client machine is able to connect to another oracle database with another TNS entry. but on a another port not 1521
OS is Windows 2008 R2 and the complete message is

  ORA-12170 TNS : Time out occurred.

Please help


texts: Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field

texts:
I am fairly new to SQL so apologies if my terminology is incorrect. I am looking to summarise a number of measurable's from "Table 2" but in the example below I have only included 1 for ease. I would like to insert the results into a a new table (Table 3).

I use pgAdmin 4.

Tables and contents

Table 1

This has two useful fields. Field 1 is named "Code_1" but this is not a unique field. Field 2 is named "Code_2" and this is unique. This table basically highlights all the features in "Code_2" and shows which features they are linked to in "Code_1".

CREATE TABLE Table_1 (Code_1 int, Code_2 int)

INSERT INTO Table_1 (Code_1, Code_2) VALUES  (1,1), (1,2), (1,3), (2,4), (2,5), (2,6)

code_1 | code_2
-----: | -----:
     1 |      1
     1 |      2
     1 |      3
     2 |      4
     2 |      5
     2 |      6

Table 2

The features in "Code_2" also have a number of measurable's associated with them but as explained above there is only one in this example - "Measure_1".

CREATE TABLE Table_2 (Code_2 int, Measure_2 int)

INSERT INTO Table_2 (Code_2, Measure_2) VALUES (1,50), (2,75), (3,100), (4,25), (5,25), (6,50)

code_2 | measure_2
-----: | --------:
     1 |        50
     2 |        75
     3 |       100
     4 |        25
     5 |        25
     6 |        50

Output table

I would like to create a new table (Table 3) with "Code_1" (unique) which is a sum of all the measures ("Measure_1") associated with "Code_2" which is associated with "Code_1).

code_1 | sum_measure_1
-----: | ------------:
     1 |           225
     2 |           100


texts: Naming the parts of a supertype/subtype pattern

texts:
I am doing more extensive work with supertyping and subtyping as it solves some problems in a very useful way. In particular, I am enhancing a web application where many things need to be subject to security—pages, reports, actions, menus, and so on—and supertyping all these in a single Securable table is going to make security a breeze.

But some of these tables also have normal hierarchical parent-child relationships, and I'm having trouble with some naming aspects. I'm asking this question to invite you all to weigh in. Maybe we can come up with some terms that would be useful to the db community at large.

Here is my first stab at names for the various parts. Blanks mean I haven't thought of anything. It may not be necessary to name them all. I also may have blurred the lines between a nodal tree and a parent-child database relation, however at this point I think the concepts coincide very well. The second subtype column is just to list alternate reasonable names.

            Super/       Super/
Hierarchy   Subtyping    Subtyping(2)
----------  -----------  ------------
Tree
Ancestry    Ascendancy
Root        Base         Seed
Parent      Supertype    Source
Child       Subtype      Extension
Sibling     Cotype       Fellow
Ancestor    Superior
Descendant  Subordinate
Leaf        Ultimate
Height      Distance
Generation  Instance
Subtree

The question: what do you think are the best names for the supertype/subtype pattern to match all the given terms for the hierarchy pattern? Critique on the names I chose for hierarchy are welcome, too.