texts:
I'm using MariaDB 10.3.22 on debian buster; I've set up a query to cleanup some expired user session from a table; this query should run every 15 minutes; the query is correct (tested and working on another identical DB but based on MySQL) but the start event is not triggered.

What I checked so far:

Event scheduler is ON

MariaDB [(none)]> show processlist;
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
| Id  | User            | Host      | db   | Command | Time  | State                       | Info             | Progress |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
|   1 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge coordinator    | NULL             |    0.000 |
|   2 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   3 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   4 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   5 | system user     |           | NULL | Daemon  |  NULL | InnoDB shutdown handler     | NULL             |    0.000 |
|  37 | event_scheduler | localhost | NULL | Daemon  | 13949 | Waiting for next activation | NULL             |    0.000 |
| 411 | root            | localhost | NULL | Query   |     0 | Init                        | show processlist |    0.000 |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
7 rows in set (0.000 sec)

Event inserted and enabled

MariaDB [integrity]> show events\G;
*************************** 1. row ***************************
                  Db: integrity
                Name: DELETE_EXPIRED_SESSIONS
             Definer: phpmyadmin@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 15
      Interval field: MINUTE
              Starts: 2019-04-10 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.001 sec)

Event query seems to be ok

MariaDB [integrity]> show create event delete_expired_sessions\G;
*************************** 1. row ***************************
               Event: DELETE_EXPIRED_SESSIONS
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`phpmyadmin`@`localhost` EVENT `DELETE_EXPIRED_SESSIONS` ON SCHEDULE EVERY 15 MINUTE STARTS '2019-04-10 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM `core_sessions` WHERE `core_sessions`.`expiry` <= NOW()
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci

Other things to check?

Thank you for any hint...


texts:
I have a secondary database on a standby and read only mode.
The question is .. Can I replicate this database ?


texts: Is it possible to check all constraints in a MySQL database?

texts:
MySQL offers the ability to temporarily disable the foreign key checks, thus allowing inconsistent data to enter the database.

Is it possible to check an entire MySQL database, table by table, row by row, to ensure that all constraints are satisfied?


texts: LoadBalancing JDBC DataSource Connect without URL

texts:
When using JDBC I like the new DataSource-way to establish the connection. Further I like the isolated set-Methods for the properties like setUser, setPassword, setDriverType, setServerName, setNetworkProtocol, setDatabaseName, setPortNumber and so on.
The question is (esp. for the use with Oracle), if there is a way to set up LoadBalancing with two or more servers without specifying the URL directly with

setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dbpool)))");

for example.


texts: Will "bridge" storage engines work with MariaDB Galera Cluster?

texts:
Galera states that it only supports InnoDB. The reason is quite clear, and it is related with the way synchronous replication is implemented in Galera.

However, some storage engines are just "bridges", or they do (almost) nothing. Can anyone tell me which of these storage engines can realiably be used in Galera?

OQGRAPH (referencing an InnoDB table)
Mroonga (referencing an InnoDB table)
CONNECT (TABLE_TYPE = PROXY, referencing InnoDB table)
CassandraSE
SphinxSE
BLACKHOLE
SEQUENCE


texts: Script to Restore/Create SQL Server 2000 Database from Another Database's Backup

texts:
I'm trying to create a database, NewClientDB, by restoring another database's, OldClientDB, backup, d:...oldclientdb.bak.  I've always done this via SQL 2000 Enterprise Manager, but the GUI keeps hanging.  I've already created the empty NewClientDB.

Anyone know how to restore the OldClientDB's backup to NewClientDB via script without affecting OldClientDB?


texts: Designing a bank database schema with multiple balances per account

texts:
I'm trying to build a schema to keep a record of the transactions made on some accounts, but those accounts are not "user accounts", instead there are orders that are being paid, and money is being transfered by the buyer to a middle man, and there to the seller, so there are two distinct balances to track.

The problem I have is that I have different kind of transactions to track, as the price of the product can change (affecting the balance for both parties), the buyer can pay part of the product (affecting just the first balance), or the buyer can pay directly to the seller (affecting both balances). There are more events to track (like canceling the account, changing the price of the product to 0 or near 0, etc).

I've been reading Writing a simple bank schema: How should I keep my balances in sync with their transaction history? but I'm not sure how does it could apply in this context.

I would like to have a design that provides a transaction history easy to read while being flexible to keep track of different kind of transactions.

Update

I will try to provide more data about the entity types:

The main object is a Reservation, that have one buyer and one seller and a price (the buyer have to pay that price, the seller should be payed). It have two different balances, one for each.

There are different type of Transactions that affect the balances in different ways:

a Buyer can deposit money to pay part of the reservation,
money can be sent to the Seller,
the Buyer can send money directly to the Seller, and
money can be transfered from one Reservation to other Reservation.

What I'm not sure about, is what is a better approach:

Store a complex object on the database, that explicit, the kind of transaction, how much money was added/subtracted from which Reservation, if affect buyer and seller, or which one, etc.
Store multiple simple transactions that just add/subtract from each account. For example, if the buyer send money directly to the seller I would add two transactions, one for each.


texts: MySQL HAVING Causes Extra Increment of Session Variable

texts:
I'm writing a select statement to get the top 3 most recent items for a list of users. An example can be seen in this fiddle: http://sqlfiddle.com/#!9/cb25a4/12

The rows are getting ordered correctly using sessions variable which are set to 0 in a dummy FROM statement so as to not mess up future session requests. However, when I want to limit rows in the outer SELECT by row_num < 4, the increments I have in the SELECT clause are hit twice... or something.

Is the HAVING clause causing these variables to increments twice then? That's what it looks like. If so, so there any way I can get around this? Thanks.


texts:
My database size was 1TB and its full export size is 300GB.

After imported db to new environment, db size reduced to 700GB.  What could be reason for this ?


texts:
I have a person table. I also have a car table and books table.  The car and book tables both have a foreign key back to person.

I am trying to count the number of books and cars each person has. I tried:

SELECT
    Person.Name,
    COUNT(BooK.bookid) books,
    COUNT(Car.CarId) cars
FROM Person, Book, Car
WHERE Person.ID = Book.PersonID = Car.PersonID

but I get:

Incorrect syntax near '='


texts: Creating a table with CHECK constraints in MySql workbench

texts:
I created a table ORDER using Mysql Workbench as follows:
CREATE TABLE IF NOT EXISTS `rr`.`ORDER` (
  `Order No.` INT UNSIGNED NOT NULL COMMENT 'The order of the inspected equipment',
  `Order_Type` CHAR(8) NOT NULL COMMENT 'External or Internal work',
  `Engine_Description` VARCHAR(45) NOT NULL COMMENT 'Description of the Engine',
  `Equipment_Quantity` INT UNSIGNED NOT NULL COMMENT ' Number of the inspected equipment',
  `Required_Task` VARCHAR(8) NOT NULL COMMENT 'Whether Repair or Overhaul',
  PRIMARY KEY (`Order No.`))
ENGINE = InnoDB
COMMENT = '\n';

So I need to know how to make a constraint where if the value of the Order_Type field is 'External' then the value of  automatically the value of Engine_Description  will be NULL or else a value will be added?
Also, if I want to do the same for two different entities?


texts: Finding "islands" of holiday periods for employees

texts:
I'm looking for some tips to find a nice solution for my problem:

Let's say I have a table, where days of vacation of employees are stored. e.g.

CREATE TABLE test_dates (
  id             NUMBER        NOT NULL,
  holidaydate    DATE          NOT NULL,
  holidaycomment VARCHAR2(255) NULL
);

INSERT INTO test_dates VALUES (1, To_Date('17.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (2, To_Date('18.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (3, To_Date('19.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (4, To_Date('20.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (5, To_Date('21.08.2015','dd.mm.rrrr'), 'Stay at home');
INSERT INTO test_dates VALUES (6, To_Date('03.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (7, To_Date('04.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (8, To_Date('07.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (9, To_Date('08.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (10, To_Date('17.09.2015','dd.mm.rrrr'), NULL);

SELECT * FROM test_dates ORDER BY holidaydate;

Fine. Now I want to query this table to get the whole period of their holidays.

SELECT
  *
FROM (
  SELECT
    connect_by_root(id) AS id,
    connect_by_root(holidaycomment) AS holidaycomment,
    connect_by_root(holidaydate) AS date_from,
    Decode(connect_by_root(holidaydate), holidaydate, NULL, holidaydate) AS date_to,
    CONNECT_BY_ISLEAF AS is_leaf,
    LEVEL AS days
  FROM test_dates
  CONNECT BY PRIOR holidaydate = holidaydate - 1 AND (PRIOR holidaycomment = holidaycomment OR (PRIOR holidaycomment IS NULL AND holidaycomment IS NULL))
)
WHERE is_leaf = 1
ORDER BY date_from;

That query gave me 10 rows, a way too much (August 17th to 20th, August 18th to 20th and so on). So I'm trying to select the starting dates:

SELECT
  id, holidaycomment, date_from, date_to, days
FROM (
  SELECT
    connect_by_root(id) AS id,
    connect_by_root(holidaycomment) AS holidaycomment,
    connect_by_root(holidaydate) AS date_from,
    Decode(connect_by_root(holidaydate), holidaydate, NULL, holidaydate) AS date_to,
    CONNECT_BY_ISLEAF AS is_leaf,
    LEVEL AS days
  FROM test_dates
  -- NEW Code --
  START WITH id IN (
    SELECT t1.id
    FROM test_dates t1
    LEFT JOIN test_dates t2 ON t1.holidaydate - 1 = t2.holidaydate AND (t1.holidaycomment = t2.holidaycomment OR (t1.holidaycomment IS NULL AND t2.holidaycomment IS NULL))
    WHERE t2.id IS NULL
  )
  -- NEW Code --
  CONNECT BY PRIOR holidaydate = holidaydate - 1 AND (PRIOR holidaycomment = holidaycomment OR (PRIOR holidaycomment IS NULL AND holidaycomment IS NULL))
)
WHERE is_leaf = 1
ORDER BY date_from;

ID  HOLIDAYCOMMENT     DATE_FROM     DATE_TO      DAYS
------------------------------------------------------
1   Trip to Berlin     17.08.2015    20.08.2015   4
5   Stay at home       21.08.2015    NULL         1
6   Trip to London     03.09.2015    04.09.2015   2
8   Trip to London     07.09.2015    08.09.2015   2
10  NULL               17.09.2015    NULL         1

It works, but it seems to be too complicated for me. Is there a better way for select the date_from?

And my last problem: My/The Application stores no information on weekend (See Ids 6 and 8). So I get 2 rows for one holiday.

Anyone an idea to join these rows with a query and no stored function? I'm looking for this result:

ID  HOLIDAYCOMMENT     DATE_FROM     DATE_TO      DAYS
------------------------------------------------------
1   Trip to Berlin     17.08.2015    20.08.2015   4
5   Stay at home       21.08.2015    NULL         1
6   Trip to London     03.09.2015    08.09.2015   6
10  NULL               17.09.2015    NULL         1


texts: How to sync mysql remote database daily ?

texts:
I have a database that contains orders , products ..etc. I have another system that generates reports based on this database. Due to heavy load i want to separate those systems Where i have the master database for products .. and a slave database for the reporting system.

Whats the best approach to build this given that :

I dont care about real time data . Syncing will be done once a day.
I might have a different schema for the slave database.


texts:
Hello all I have a function:

ALTER FUNCTION [dbo].[getContentURL]
(
    @VID    int,
    @accountID int
)
RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
    DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  ''
DECLARE @RETURN VARCHAR(500)
DECLARE @ParmDefinition VARCHAR(500)
 SET @sql = '
    DECLARE @CompanyID int
    DECLARE @RETURN VARCHAR(500)
    DECLARE @ProjectNumber int
    DECLARE @ContentID int
    DECLARE @VersionNumber int
    DECLARE @FileName varchar(400)
  select @VersionNumber= V.Number,@ContentID= V.ContentID,@ProjectNumber=P.Number,@FileName=C.Name,@CompanyID=P.CompanyID
    from ' + @vTable + ' V
    inner join' + @cTable + ' C on V.ContentID=C.ID
    inner join Project P on C.ProjectID=P.ID where V.VID='+convert(varchar,@VID)+'

 select ''/''+Convert(varchar,@CompanyID)+''/''+Convert(varchar,@ProjectNumber)+''/''+Convert(varchar,@ContentID)+''/''+Convert(varchar,@VersionNumber)+''/''+@FileName'

 SET @ParmDefinition = N'@RETURN VARCHAR(500) OUTPUT'
    exec sp_executesql @sql, @ParmDefinition,  @RETURN OUTPUT
  set @RETURN=(select @RETURN)
 RETURN @RETURN
END

but when I execute this function

select  dbo.getContentURL(177,1)

this gives error:

The name 'select --------------------' is not a valid identifier.

how to return this executed value? can any one  help me please?


texts: Unique Non Clustered Column in Partitioned table

texts:
We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a non-aligned index. There is one more column xyz which is UniqueIdentifier datatype and xyz column also has Unique Non-clustered index.

The problem is most of our queries use the XYZ column to retrieve data as XYZ is guid and unique.

At this point we are not sure what will be the impact of setting XYZ column (uniqueIdentifier)  with an Aligned Index strategy vs Non-aligned Index strategy.


texts: Combining a query for value and an insert MySQL

texts:
I have 2 tables, ranks and accounts. When inserting into accounts I fetch the ID of the rank first, storing it in a variable on the script running the queries, then insert the new account into the table with the rank ID. How could I combine this into a single MySQL statement?

UPDATE:

// This is pseudo code.
var acct = GetAcct()
var a = MySQL.query("SELECT `id` FROM `ranks`
           WHERE `name`=" + acct.rankName).firstResult()
var b = MySQL.query("INSERT INTO `accounts` (`sid`, `did`,`rank`)
           VALUES (" + acct.sid + "," + acct.did + "," + a + ")
           ON DUPLICATE KEY UPDATE
               `did`=" + acct.did + ",`sid`=" + acct.sid + ",
               `rank`=" + a)


texts:
Recently I built an app that gets that from SVN repositories to run a few metrics.

What I do is basically:

1) Read data from an existing SVN repository and save this data to a database (with only two tables Commit and Modification - which includes the list of files that were changed in an specific commit)

2) After, I go through all the data on the database, looking for information like average time between commits of a particular developer, how many files each developer changes on average, etc.

3) Then, I store those information on spreadsheets. For example: in the case of time between commits, I create an CSV file for each developer, and each line of this file represents a commit, storing the data of the commit and how many days since the last commit:

Having this info, I calculated the average by adding the numbers on column B and dividing the sum by the number of rows. So, I have another CSV file that sums up the average number of days that each developer on the team takes to make a new commit.

I follow this approach for many other information, like how many commits on average each developer makes by month, etc.

The main issue here is that whenever a new commit is made, it is not only stored in the database, but the CSV file for the developer that made the commit needs to be updated with the new information (and so does the file that sums up the averages of the team). I've been thinking that having spreadsheets might not be the best approach, since it's not very easy to update them. I'd considered discarding the spreadsheets and expanding the MySQL database that I'm using for commit information, but it didn't seem the best option.

My question is: what would be the best approach to use in this case? Relational DB? Document, Key-Value, Column Family? Keep the spreadsheets but in a different way (store less information, maybe)? I do a lot of updates and this is probably the main problem with spreadsheets. What would you suggest?

Sorry for the very long post and thank you in advance :)


texts: How to use variables in a mongodump query

texts:
I want to backup data of last day (Current Day-1) every day using cron tasks.
In the mongo shell, I can query the data using variables as below:

var start = new Date();
start.setHours(0,0,0,0);
start.setHours(start.getHours() - 24);

var end = new Date();
end.setHours(23,59,59,999);
end.setHours(end.getHours() - 24);

db.collection.find({created_at: {$gte: start, $lte: end}});

How to use the above process as a single query to run with mongodump from outside of the shell. What will be the query in the command below?

mongodump --db dbname --collection cname --query '{}' --out collection_01NOV2019


texts:
I am designing an ERP like app for a local martial arts studio. Currently, the main functionality is to keep attendance records to training sessions. To do so, I have designed the following data model:

Some relations are:

A martial art practitioner can practice more than one martial art. But they must practice at least one martial art (I couldn't express the latter in my diagram).
An instructor for a given martial art must be chosen among the practitioners of that martial art.
In order to be counted as present or absent in a training session, a student should have had registered for that training session slot beforehand.

This is my first data model, so I am guessing that I have at least a couple of mistakes here. Could you please point out my mistakes here, if any?


texts: Migrating SQL Server 2012 Web Edition to Azure

texts:
I am trying to migrate to Azure I have gone through this article Migrate SQL Server to a single database or pooled database in Azure SQL Database online using DMS, However since I am having SQL Server Web Edition I wasn't able to configure distribution on premises server I am getting this error related to MSSQL 'web edition'

  In attempting to create the publication, I get This edition of SQL
  Server cannot act as a Publisher or Distributor for replication.,
  which is expected as my server is Web edition.

Noting that the article doesn't say the web edition is not supported. So I assume either the article is wrong or there is something else I am missing. I would appreciate if someone has a solution for this issue (on web edition) online migration


texts: Cloning the database to another server with raw data files

texts:
I'm trying to clone the database of server 1 to server 2 with raw data files. I'm using Linux 7 and Oracle 11g R2. I have the instance running with same SID in Server 2 but file structure is different. The database is about 85 GB.
I have got the data files, control file, redolog files, spfile and init.ora.  Can someone explain me the way to clone in  this way ?


texts: mysql statement based replication - unsafe statements

texts:
I have recently set up statement based replication and my error logs are filling up with this error

  121231 21:10:55 [Warning] Unsafe statement written to the binary log
  using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON
  DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is
  unsafe Statement: INSERT INTO bunching (route_number, vehicle_name,
  time, status, direction, eta) VALUES (11, '223', -1.0, 1, -1, 99999.0)
  ON DUPLICATE KEY UPDATE route_number = 11, time = -1.0, status =1,
  direction = -1, eta = 99999.0

and

  121231 21:10:55 [Warning] Unsafe statement written to the binary log
  using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON
  DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is
  unsafe Statement: INSERT INTO vehicleETA (routes_id, vehicleNum,
  relEta, relStopID, lat, lng, bearing, online, msg, lastReport,
  direction, inETA) VALUES (18, 2012, 39.70092857098426, 7, 29.970915,
  -90.09537, 2.2320573776159844, 1, '', '2012-12-31 21:10:55',1, 1 ) ON DUPLICATE KEY UPDATE routes_id = 18, relEta = 39.70092857098426,
  relStopID = 7, lat = 29.970915, lng = -90.09537, bearing =
  2.2320573776159844, online = 1, msg = '', lastReport = '2012-12-31 21:10:55', direction =1, inETA = 1, GMT = '031052', lastPass = 0

Mysql Docs state that this is unsafe because:

  INSERT ... ON DUPLICATE KEY UPDATE statements on tables with multiple
  primary or unique keys. When executed against a table that contains
  more than one primary or unique key, this statement is considered
  unsafe, being sensitive to the order in which the storage engine
  checks the keys, which is not deterministic, and on which the choice
  of rows updated by the MySQL Server depends.

I simply don't understand what this means. Can someone explain this to me? I'm wondering if this is a huge problem that will cause data differences on the slave or a warning I can simply ignore. Unfortunately these errors are filling the logs quite quickly. This data is relatively ephemeral and im

I have the option of

changing the logging mode to "mixed"
turning off warnings (ignoring these warnings)

Or we can alter the queries. Any advice here?


texts: SSIS Script Task doesn't appear to run when scheduled

texts:
I have a script task inside SSIS which does not appear to run when the job is triggered from SQL Server Agent.  I say does not appear to run because I don't know if it's the SQL Script task or some task preceding it.

The basic goal of this part of the ETL process is to group all files downloaded and process them one set at a time (5 files per set).

The GetFullSourcePath SQL Task outputs an Object data type (Return Type = full result set).  Inside the Copy Source Files ForEach Loop Container, I convert that to a string using the SQL Script task and then use a File System task to move the file from Inbound to ToDo for processing.

The SQL Script is a very simple C# operation converting an Object data type to a string.  It has 1 input and 1 output variable. However, the output value is never populated which means the next step of the ETL job cannot locate the source file.

    public void Main()
    {
        //Dts.Variables["User::SourceFile"].Value = Dts.Variables["User::FullSourcePath"].ToString();

        object myObjectVar = Dts.Variables["FullSourcePath"].Value;
        string myString = (string)myObjectVar;
        Dts.Variables["SourceFile"].Value = myString;

        Dts.TaskResult = (int)ScriptResults.Success;
    }

When I view the execution report, the script has no errors.
I'm using Visual Studio 2015 Enterprise, SSDT BI Preview (December 2015 release), and SQL Server 2014.  It works fine if I run the package from VS.

The error I get is can't find "somefile.txt". The read/write variable which is also the output variable of the script task is defaulted to "somefile.txt" in the package variables. This made me assume the script task was not working but equally it could be something else. I'll dig a little deeper in the execution logs to be sure.


texts: How to mysqldump to a subset of rows in specific table during a complete backup

texts:
I have a database (myDB) that holds some tables. During my backup process I have one of those tables that should be filtered. So I want to backup only a subset of rows inside table1, when I backup the entire database.

I am using mysqldump like this:

mysqldump -uUSER -pPASSWORD --routines --add-drop-database   --databases myDB --tables table1 -w "id<3"> myDB_BK.sql

The subset rows is correct for table1 but I dont have the entire database. Only the specified table1.

I've tried all combination with no success.

Any Idea in how to accomplish this task?


texts: ON DELETE CASCADE doesn't work even storage table is InnoDb

texts:
I tear my hair with the ON DELETE CASCADE Clause. In fact I have two tables
I'm on linux and I have installed lamp with phpmyadmin.

That's the tables :

CREATE TABLE a(
    Idatable bigint(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    Nameatable varchar(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE anObject(
    IdAnObject bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nameobject varchar(20) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE linkTable(
    Idatable bigint(20) NOT NULL REFERENCES a (Idatable),
    IdAnObject bigint(20) NOT NULL REFERENCES anObject (IdAnObject) ON DELETE CASCADE,
    PRIMARY KEY (Idatable, IdAnObject),
    CONSTRAINT linkTable_fk1
        FOREIGN KEY (Idatable)
        REFERENCES a (Idatable),
    CONSTRAINT linkTable_fk2
        FOREIGN KEY (IdAnObject)
        REFERENCES anObject (IdAnObject) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Unfortunately, it doesn't work because when I delete a row in the table 'linktable' the associated row in anObject isn't delete

Thank you


texts: Need help in converting this cursor to a set based operation

texts:
I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 has 8 million rows. Can you suggest how can I convert this cursor to a set based approach for better performance? Can a CTE be used?

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP] AS
SET NOCOUNT ON
DECLARE @SEQ SMALLINT,
@CASEID DECIMAL,
@ASSIGNED  NCHAR(30),
@AUDITSTAMP DATETIME,
@AUDITOPRID NCHAR(60),
@STATUS NCHAR(10),
@LASTCASEID DECIMAL,
@PROVGRPID NCHAR(20)
SET @SEQ = 1
SET @LASTCASEID = @CASEID

DROP TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP

CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP (
  SEQ_NUM SMALLINT,
  CASE_ID DECIMAL,
  ASSIGNED_TO NCHAR(30),
  AUDIT_STAMP DATETIME,
  AUDIT_OPRID NCHAR(60),
  RC_STATUS NCHAR(10),
  PROVIDER_GRP_ID NCHAR(20) )
DECLARE AUDIT CURSOR FOR
   SELECT CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID
   FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2

-- Open the cursor
OPEN AUDIT
FETCH NEXT
   FROM AUDIT
    INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID
WHILE @@FETCH_STATUS = 0

begin
 INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP
 SELECT @SEQ, @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID

SET @LASTCASEID = @CASEID
FETCH NEXT
   FROM AUDIT
    INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID

SET @SEQ = CASE WHEN @CASEID <> @LASTCASEID THEN 1 WHEN @CASEID = @LASTCASEID THEN @SEQ + 1 END
end
-- Close and deallocate the cursor
CLOSE AUDIT
DEALLOCATE AUDIT


texts: Mongodb C-driver install fail on windows

texts:
when build c-driver libbson, I have a CMake error

C:\mongo-c-driver-1.15.0\src\libbson>cmake -DCMAKE_INSTALL_PREFIX=C:\mongo-c-driver -G "Visual Studio 14 2015 Win64"

CMake Warning:
  No source or binary directory provided.  Both will be assumed to be the
  same as the current working directory, but note that this warning will
  become a fatal error in future CMake releases.

-- Selecting Windows SDK version 10.0.14393.0 to target Windows 10.0.17134.
libbson version (from VERSION_CURRENT file): 1.15.0
--     struct timespec found

CMake Error at C:/Program Files/CMake/share/cmake-3.15/Modules/CMakePackageConfigHelpers.cmake:265 (message):
  Variable LIBRARY_INSTALL_DIRS does not exist

Call Stack (most recent call first):
  build/cmake/BSONPackage.cmake:10 (configure_package_config_file)
  build/cmake/BSONPackage.cmake:26 (install_package_config_file)
  CMakeLists.txt:378 (include)

and LIBRARY_INSTALL_DIRS is in BSONPackage.cmake

set (INCLUDE_INSTALL_DIRS ${BSON_HEADER_INSTALL_DIR})
set (LIBRARY_INSTALL_DIRS ${CMAKE_INSTALL_LIBDIR})
set (PACKAGE_LIBRARIES bson-1.0)

How can i build it?


texts: Why would a long running query remain sleeping and with null wait_info

texts:
The following query was run on SQL Server 2008 R2 and took over 1 hour to run:

select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id

It produced 0 rows returned. I know this because I restored the database to the point in time that the query started running and re-ran the query. When I did this, the query ran very fast (less than 1 second).

While this query was running for over an hour, sp_WhoIsActive was kicked off every 5 minutes via a SQL Agent job and logged the details to a table. The below image, which you should able to download and view zoomed in, shows what was running during that time.

The long running query is highlighted in green. The wait_info column is null and the sleep_status is always sleeping.

Any idea why this query took so long to run and/or any thoughts on what I can do to troubleshoot it further?


texts: How to reinstall SQL Server Integration Services I've deinstalled from a Production server by mistake

texts: In deinstalling a named instance of SQL Server 2012 from a Production server, inadvertantly, I selected SSIS as a product to deinstall.  Now, I need to reinstall it with the least disruption possible.


texts: Unable to change character_set_server on MySQL

texts:
I have converted my database to UTF8 but the character_set_server is still Latin1.

Is it compulsory to change it to UTF8 or can I just leave it like that?

This is a production server, so it's not possible to reboot the MySQL database.


texts: mysqlbinlog (mysql5.5) output errors out (char length)

texts: wondering why an invalid insert (exceeds char length varchar(100)) breaks when piping mysqlbinlog output to RDS-- when pasted into a client and run separately-- it inserts, truncates, and warns instead.


texts:
I have a "comments" table that models a conversation on a topic, like this:

id serial
topic_id integer
parent_comment_id integer
body text

So, every comment has a reference to its topic AND eventually its parent comment (if it's not the first comment on the topic).
I'd like to add a constraint that would prevent adding rows having mismatched topic/parent (for example by referencing a topic that doesn't have the required comment, or conversely a comment that references the wrong topic).
Is this possible? Is a trigger required?

(For the record, I tried

ALTER TABLE comments ADD FOREIGN KEY (parent_comment_id, topic_id)
                 REFERENCES comments (id, topic_id)

but it complains that there is no unique constraint matching given keys for referenced table "comments")


texts: DDL Trigger not working as expected SQL Server 2012

texts:
I have a DDL Trigger in SQL Server 2012 as:

CREATE TRIGGER [AuditProcChanges]
ON DATABASE FOR
CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
DECLARE @message_body XML
SET @message_body = EVENTDATA()

    BEGIN
       BEGIN TRANSACTION
       INSERT  INTO MyChangeLog(vcEventType, dtEventTime, vcServerName, vcLoginName
                                            , vcUserName, vcObjectName, vcObjectType, vcSQL)
            SELECT  @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                        'varchar(128)') AS EventType,
                    GETUTCDATE() AS PostTime,
                    @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                        'varchar(128)') AS ServerName,
                    @message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
                                        'varchar(128)') AS LoginName,
                    @message_body.value('(/EVENT_INSTANCE/UserName)[1]',
                                        'varchar(128)') AS UserName,
                    @message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
                                        'varchar(128)') AS ObjectName,
                    @message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
                                        'varchar(128)') AS ObjectType,
                    @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                        'nvarchar(max)') AS TSQLCommand

        COMMIT TRANSACTION
    END

But when I'm altering the table from design view (right click and Design), the events that are getting logged are only of creation of temp table and dropping of main table. Create_Table event is not getting captured.

Can't we capture the log events when we alter a table from design view. Please help.


texts: Strategies to archive high frequent data in SQL Server

texts:
I have a database that stores high frequent data, the data has a complex data model. Now I want to offload/archive this data in 15 minute intervals to another database server so user can do reporting on this data with a minimal delay. To improve performance I don't want to keep more than 2 days of data on the primary system.
In a previous version of this system the data model was very simple so I used SSIS and transfered data from two tables based on a time stamp, however now with the complex data model I have some doubts that this approach will be efficient.

What strategies would be best for such an environment ? HADR would be nice, however with the deletion of the date on the primary it won't work since I want to keep all data on the reporting instance.


texts: 8Kb row size limits ignored - but only sometimes!

texts:
As a result of poorly tested code (not mine!) I've ended up with a table containing 319 VARBINARY(MAX) fields, plus two DATETIME fields and two UNIQUEIDENTIFIER fields. Obviously this is not ideal, but it should still be within the limits of what SQL Server can handle in terms of row size.

As I understand, VARBINARY(MAX) is stored on-table as a 24-byte pointer to off-row storage. 24*319 = 7656 bytes, plus the other four fields = 7704 bytes, so I'm just in the limit of what I can put into one row.

This all behaved fine until the code started inserting data into this table. It got 6 rows in before crashing with the message:

  Cannot create a row of size 8345 which is greater than the allowable maximum row size of 8060. The statement has been terminated.

This is very puzzling as none of the rows should be any larger than the others (on-row at least). I've scripted the table off and it looks fine. I did notice that it was created with ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] although I'm not sure what effect that would have.

Does anyone know what might be happening here, and more importantly how do I get around it?

Here's the code used to create the database, and the insert command

CREATE DATABASE MyBigDB;
GO
USE [MyBigDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Autogenerated table with really big data](
    [Primary_key] [uniqueidentifier] NULL,
    [A Foreign key] [uniqueidentifier] NULL,
    [Created] [datetime] NULL,
    [Updated] [datetime] NULL,
    [c1] [varbinary](max) NULL,
    [c2] [varbinary](max) NULL,
    [c3] [varbinary](max) NULL,
    [c4] [varbinary](max) NULL,
    ...
    ...
    etc
    ...
    ...
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [Autogenerated table with really big data] ([Primary_key], [A Foreign key], [Created], [Updated], [c1], [c2], [c3], [c4],............)
VALUES (<a UID>, <a UID>, <a DATETIME>, <a DATETIME>, <binary blob>, <binary blob>,............)


texts: Choosing database type and schema for rental availability

texts:
I'm looking at what type of database and schema is best to use for product rental availability. The requirements are simply that it needs to cater for high writes and low reads. So I've been pushed towards the idea of Cassandra or rather Keyspaces on AWS. Although this is such an early stage, I'd prefer to stay with AWS as I'm making great use of their free tier as this is a learning project for me.

The data will be read from an API every hour. For my purposes, I'll be limiting the forward availability to 1 year. Each product has an availability for each day. So let's say on day 1, a product tennis ball has 10 available and on day 2 it has 8 available and days 3 and 4 it has 10.

The data I need to know is:

Between a certain set of dates, what is the minimum number available

So my initial thoughts were as follows:

Partition Key: Product ID
Clustering Column: Timestamp
Availability: Number (int)

For the tennis ball it would look like:

+-------------+----------------------------+--------------+
| product     | date                       | availability |
+-------------+----------------------------+--------------+
| tennis ball | 2020-06-01 00:00:00.0+0000 | 10           |
+-------------+----------------------------+--------------+
| tennis ball | 2020-06-02 00:00:00.0+0000 | 8            |
+-------------+----------------------------+--------------+
| tennis ball | 2020-06-03 00:00:00.0+0000 | 10           |
+-------------+----------------------------+--------------+
| tennis ball | 2020-06-04 00:00:00.0+0000 | 10           |
+-------------+----------------------------+--------------+

The total row count would be number of products * 365. (Assuming past dates are removed daily because they are of no use to us).

The query to know availability between day 1 and day 2 would just be as follows:

SELECT * FROM table WHERE product = "tennis ball" AND date >= '2020-06-01 00:00:00.0+0000' AND date <= '2020-06-02 00:00:00.0+0000'

My questions:

Is this a good use of Cassandra/Keyspaces?
I notice that on Keyspaces you can't run a "MAX" or "MIN" condition, which would obviously be very useful. In terms of how that actually works on Cassandra, would it have to read all rows between the dates and then filter?
Is updating the database with the following update query a good idea? In terms of workload on the database is it better to read and then write if necessary, or just write no matter what?

UPDATE table SET availability = x WHERE product = "tennis ball| AND "date" = '2020-06-01 00:00:00.0+0000' IF availability != x


texts: What are the performance implications of creating an index in MySQL?

texts:
The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against which is building the index.

If anyone could give me an idea as to what the implications are, it would be appreciated; what would be appreciated even more is a link to some documentation where I can answer this question for myself!

Regards,

Chris :D


texts:
I took my SQL Server Database OFFLINE. Is it still occupying the same space as when it was ONLINE?

Should I keep it OFFLINE or is it better to BACKUP the database and DROP it?


texts:
I am a SQL Server DBA with minimal Informix 11 experience.

I'm tasked with extracting an entire Informix database (circa 8GB according to systables in Informix) into a SQL Server database. I plan to do this using linked server use OPENQUERY.

However I am worried about the impact this may have. It's not a large database at all and I know Informix does row level locking (was worried it may do full table locking) and I'm going to run it when the application is not busy.

Does anyone have any advice regarding this?


texts: multi-column index has 1 unique column vs single column

texts:
let assume that I have 2 columns

comment_id | unique
shop_name  | duplicated

which case is batter ?

1- making multi-column index using these 2 columns

2- making single column index using comment_id

which one is batter based on your experience?


texts: How to monitor backup jobs on Oracle 11g?

texts:
Background:

I'm trying to monitor that backups run as expected on several databases. Zenoss is used to monitor most other stuff.

A couple of KPIs are already monitored using Zenoss in combination with script that runs sqlplus on database host.

What I've found so far:

Views in database:

V$RMAN_STATUS
V$RMAN_BACKUP_JOB_DETAILS;
V$RMAN_BACKUP_SUBJOB_DETAILS;

Stop gap solution:

Sending alerts from Enterprise Manager:

Setup > Monitoring Templates > Create > Target : some Database > Metric Threshold > Failed Job Count

or

Preferences > Rules > Create > Tab "General" - Target Type: Database Instance, Tab "Jobs" - Specific Jobs > Add Specific Jobs: Select the job and Job Status: "Problem"

What I'd like to know:

Does this seem reasonable or is there another preferred way to integrate monitoring of Oracle into a third party nms? (Polling is preferred to traps in the current setting. )
Pitfalls to be aware of.
There seems to be significant differences between the database views I mentioned and what I see in EM backup history. Where should I be looking for the status of past backups?


texts: Understanding the impact/risk of turning off "verify backup integrity" on SQL backup

texts:
Currently we use standard maintenance plans for backups on the SQL Server 2005/2008/2008R2/2012 servers in our environment, and the "Verify backup integrity" box has always been checked.

Some of the backups run very long, so I've recommended turning off that option, but management needs me to document the impact and risks of this change.

I understand the use and history of this option, it just seems unnecessary to me to double the time for the backup job when (in my opinion), any error that is likely to occur would occur during the backup step, not during the verify.

Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something? (We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)

Is there any official MS recommendations for when it is safe to turn this off?

Do you run "verify" on every backup in your environment? Do you spot-check them?

EDIT: To clarify, when you check "verify backup integrity" in the maintenance plan, SQL will do a full RESTORE VERIFYONLY on each database immediately after each backup. This is just as data/IO intensive as the original backup, and (basically) doubles the overall time of the backup job. This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).


texts: Separate or Combine 2 Tables with Similar Attributes?

texts:
I have two kind of users in my business system: Customer and Employee.
Both user have Username, Password, Fullname, Phone Number, Email, and other similar attributes.

  I have a difficulty to determine which is  better to merge
  Customer and Employee on one table (for example I store in User table) or separate each entities on different table?

On my case, the Customer has additional attributes that the Employee doesn't had (for example: NewsUpdateSubscription). And also for Employee, it has additional attributes that Customer doesn't had (for example: Salary). What is the best practice for this case? Thanks in advance.


texts: Log Shipping Fails with CONTINUE_AFTER_ERROR

texts:
DR plan for primary database in a private hosted cloud environment. The primary is a hosted physical server. The secondary is on another network, same domain. Version is SQL Server 2008R2 (10.50.2550.0) Enterprise. Compatibility=2008.

Log Shipping runs fine for a few restores, then I come back the next day, and I see "A previous RESTORE WITH CONTINUE_AFTER_ERROR operation left the database in a potentially damaged state." And the restore job continues to fail. No further restores go through.

I checked the primary db with DBCC CHECKDB and there are no errors reported. I reconfigured LS twice, and the last time, added a 5-minute delay for the restore job. Same thing happens every time. It runs fine for a few restores, but then fails.

I could really use some help knowing where to look for the issue.

Graeme
PS: This question was previously posted on SQL Server Central, with no responses. At the request of an expert, I am moving it here. Thanks again.


texts:
I'm trying to design a products relations table structure. There is an existing product table that has the following fields:

A product could be related to other product but also to a category or a subcategory. Or even, have the three type or just two type of relations at same time.

At the same time, a product subcategory could have a relation to other subcategory or category also to a product.

The main goal for what this is for is when a product is selected by the user, he can get suggestions to other product related to the one selected.

So whats the best table structure you can suggest to store those relations?

I was thinkin some like this:

But I dont know if thats reliable way.

Thank you in advance!

EDIT:

Maybe I did not explain well. Lets say a product x is related to products in Category Y and/or also related to products in Subcategory Z. But could also be related directly to a single product. There are chances to a product have the three types of relations or only two or one of them.

And the Subcategory field of product could be have the three types of relations too.. Or none of them..


texts: How to select distinct TOP but group by column

texts:
I have a result set of 500 rows, but this are for 100 invoice.
I need only the top 10 of the last invoices.

I found something like similar: How to select top 10 records from each category

Sample data:

InvNr | DetailLine
111   |    1
111   |    2
112   |    1
112   |    2
112   |    3
113   |    1
113   |    2
114   |    1
115   |    1
...   |    ...

What I hope to get is for example:

SELECT DISTINCT TOP 2 InvNr, DetailLine FROM tbl_Invoice

With this result:

InvNr | DetailLine
111   |    1
111   |    2
112   |    1
112   |    2
112   |    3

Update:
So I need the last 10 (or first 2 in the example above) invoices they have created, but each invoice can have "x" amount of detail lines and I want all their detail lines (of these last 10) in the results.


texts: High ASYNC_NETWORK_IO when user locks computer with Excel open

texts:
I have a user who gets in at 8AM and leaves at 6PM every day, and every day I see the same strange pattern in SolarWinds DPA.

It seems that Excel is refreshing the Query Table when the user locks their screen.  My questions are:

Is there any way to stop Excel from doing that?  I can't be the only DBA who has fought such problems.
Is there any way I can reproduce the problem on my machine?
Help finding Google keywords to search for a known issue... I've tried unsuccessfully

UPDATE

It occurred to me there could be other factors at play here, so here goes:

The file is on a Network-Attached Storage Drive shared among a work group.
I looked into various Network Interface Card powersaving feature issues that could be causing Excel to think the file handle is lost (???)
"Refresh data when opening the file" is checked under Connection Properties
This may be occurring on a Virtual Machine.

I have found one other user with a similar problem: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bcc35121-1575-4a7f-b82f-1c20f6fed58d/process-blocked-by-asyncnetworkio-status-blocks-other-queries?forum=sqldatabaseengine


texts:
Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:

The first time, make a full backup.
The second time, only the changes since the last backup, and so on.

Only with some software or script, not PHP.


texts: Oracle start database with incorrect password

texts:
I have a question regarding starting oracle database with in correct sys password.

I have provided incorrect password for sys user and I am still able to start database and execute statements. So wondering whether I have missed something while installation or this is how it is?

Any insight into this is highly appreciable, Below is the result of start database.

sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 15 16:50:39 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys/d as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             285215104 bytes
Database Buffers          130023424 bytes
Redo Buffers                6094848 bytes
Database mounted.


texts: Modelling products along with their attributes and discounts for an e-Commerce database

texts:
Scenario
My application will have products belonging to 1 company. Each of those products will:

Have at least 1 quantity
Have 0 or more discounts
Have 1 or more attributes
Belong to 1 category, which can have parent a parent category

A quantity can be 1 unit, 1 box, 1 pallet... Each quantity will thus have it's own price and that price can change over time. So I've added a fromDate attribute to know which price was active at which time. Is necessary when a product gets bought so I know the price of the quantity at the time of purchase.
A discount can be something like 50 euro, 5%, 2 for 1... Also having a fromDate so I know which discount is applicable.
Attributes will always belong to 1 product. And these will contain a name of the attribute and the value of the attribute. Their goal is to give the company the possibility to add more detailed information next to the existing required information.
Examples of attributes:

Key Feature 1 => Key Value 1
Name => Something
Color => Black
Size => 10 cm (h) x 5 cm (w)
Weight => 450 gr

Diagram
The following diagram represents my complete database schema as I have it now (only the section regarding products is relevant here):

Questions

How do I create my schema so that my requirement for discounts is met? How do you handle the ability to have different types of discounts? And how do you best keep track when there is an active discount for the product and where there is none?
Does it make sense to simply store attributes as name and value? It's possible that the same attribute name will be used for multiple products. Do I keep a list of all possible attribute names? What is the most dynamic way of tackling this issue?


texts:
I have a table as detailed below. I am trying to build a report on our internal portal (using PHP).  I need to be able to choose a date and it then gives information on how many active subscribers were live at the start of the period.

The user will select a month (always the first of the month). My issue seems to be it will include people who have cancelled back in July. How do i make it so that once that person has cancelled they don't appear in the output?  I'm hoping it can be done in SQL vs having to make something in PHP.

select * FROM group_assigned_users
        where gau_regdate < '2016/10/01'  and (gau_cancelleddate <  '2016/10/01' or gau_cancelleddate = '0000-00-00')
        and  gau_group_id = 26

CREATE TABLE IF NOT EXISTS `group_assigned_users` (
  `gau_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
   `gau_status` enum('Pending','Active','Suspended','Terminated','Cancelled','Froze') NOT NULL DEFAULT 'Pending',
  `gau_regdate` date NOT NULL,
  `gau_expirydate` date NOT NULL,
  `gau_nextdue` date NOT NULL,
  `gau_cancelleddate` date NOT NULL,
  PRIMARY KEY (`gau_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7746 ;

INSERT INTO `group_assigned_users` (`gau_id`, `gau_status`, `gau_regdate`, `gau_expirydate`, `gau_nextdue`, `gau_cancelleddate`) VALUES
(2007, 'Active', '2007-01-10', '2017-06-26', '2017-06-26', '0000-00-00'),
(2020, 'Cancelled', '2007-12-19', '2015-12-19', '2016-12-19', '2015-12-17'),
(2044, 'Cancelled', '2007-09-26', '2014-05-27', '2014-05-27', '2014-05-27'),
(4824, 'Cancelled', '2010-08-26', '2011-08-26', '2011-08-26', '2011-08-26'),
(6520, 'Cancelled', '2013-06-19', '2015-07-16', '2015-07-16', '2015-07-16'),
(6680, 'Cancelled', '2013-09-26', '2016-06-26', '2016-06-26', '2016-01-19'),
(7058, 'Cancelled', '2014-11-07', '2015-12-10', '2015-12-10', '2015-09-25'),
(7066, 'Cancelled', '2014-11-14', '2015-11-20', '2016-11-20', '2015-11-19'),
(7174, 'Cancelled', '2015-04-22', '2016-05-20', '2016-05-20', '2016-02-16'),
(7191, 'Cancelled', '2015-05-17', '2015-12-04', '2015-12-04', '2015-10-07'),
(7372, 'Cancelled', '2015-10-11', '2016-10-11', '2016-10-11', '2016-02-19'),
(7389, 'Active', '2015-11-01', '2016-11-01', '2016-10-18', '0000-00-00'),
(7404, 'Cancelled', '2015-03-06', '2016-03-06', '2017-03-06', '2016-03-09'),
(7419, 'Active', '2015-11-04', '2016-11-04', '2016-11-04', '0000-00-00'),
(7422, 'Active', '2019-11-30', '2020-11-30', '2020-11-03', '0000-00-00'),
(7431, 'Cancelled', '2015-12-03', '2016-12-03', '2016-11-23', '2016-07-07'),
(7465, 'Cancelled', '2016-01-03', '2017-01-03', '2017-01-03', '2016-02-22'),
(7491, 'Cancelled', '2016-01-29', '2017-01-29', '2017-01-29', '2016-06-13'),
(7549, 'Active', '2016-03-09', '2017-03-09', '2017-03-01', '0000-00-00'),
(7595, 'Active', '2016-04-05', '2017-04-05', '2017-04-05', '0000-00-00'),
(7616, 'Active', '2016-05-04', '2017-05-04', '2017-04-27', '0000-00-00'),
(7627, 'Active', '2016-05-15', '2017-05-15', '2017-05-15', '0000-00-00'),
(7650, 'Active', '2016-06-01', '2017-06-01', '2017-06-08', '0000-00-00'),
(7684, 'Active', '2016-07-25', '2017-07-24', '2017-07-24', '0000-00-00'),
(7689, 'Active', '2016-08-02', '2017-08-02', '2016-11-01', '0000-00-00'),
(7691, 'Suspended', '2016-08-03', '2017-08-08', '2016-11-08', '0000-00-00'),
(7698, 'Active', '2016-08-10', '2017-02-11', '2017-02-11', '0000-00-00'),
(7715, 'Active', '2016-08-25', '2017-09-08', '2017-09-08', '0000-00-00'),
(7716, 'Active', '2016-08-25', '2017-02-08', '2017-02-08', '0000-00-00');


texts: How can I write a query avoiding intermediate views/steps from different tables in PostgreSQL?

texts:
I am working in a large query  I would like to eliminate intermediate steps, so I am trying to write the two queries below in just one.
The first query (QUERY 1) select the grid id from a table called tiles, from here i obtained an UUID who correspond to a value in a second table so to obtain the real value of this grid id I have to query a second query (QUERY 2). I have try to cast everything as I did in the third query for other values, but this approach doesn't work. Has someone an idea how can I manage to do this query (query 1 and 2) just in one:
QUERY 1:
SELECT
jsonb_array_elements(grid_id_tile.tiledata -> '34cfea5d-c2c0-11ea-9026-02e7594ce0a0'::text) ->> 'resourceId'::text AS grid_id
FROM mv_geojson_geoms mv
LEFT JOIN tiles grid_id_tile ON tv1.resourceinstanceid = grid_id_tile.resourceinstanceid
WHERE (( SELECT resource_instances.graphid
           FROM resource_instances
          WHERE mv.resourceinstanceid = resource_instances.resourceinstanceid)) = '34cfe98e-c2c0-11ea-9026-02e7594ce0a0'::uuid;

QUERY 2:
SELECT grid_id.legacyid AS grid_id,
FROM table 1 (Where I have obtained the grid id)
LEFT JOIN resource_instances grid_id ON hb1.grid_id = grid_id.resourceinstanceid::text

QUERY 3:
( SELECT "values".value
           FROM "values"
          WHERE ((name_ft_tile.tiledata ->> '34cfea97-c2c0-11ea-9026-02e7594ce0a0'::text)::uuid) = "values".valueid) AS nametype,

FROM mv_geojson_geoms mv

LEFT JOIN tiles name_ft_tile ON mv.resourceinstanceid = name_ft_tile.resourceinstanceid AND (name_ft_tile.tiledata ->> '34cfea97-c2c0-11ea-9026-02e7594ce0a0'::text) <> ''::text

WHERE (( SELECT resource_instances.graphid
           FROM resource_instances
          WHERE mv.resourceinstanceid = resource_instances.resourceinstanceid)) = '34cfe98e-c2c0-11ea-9026-02e7594ce0a0'::uuid


texts: SQL Server Replication issue with INSTEAD OF UPDATE Trigger and Column-level tracking

texts:
I’m facing an issue with SQL Server Replication and INSTEAD OF UPDATE trigger.

I have a table and a trigger that looks like this :

CREATE TABLE [dbo].[TableA]
(
       [Identifier]  BIGINT         IDENTITY(1,1) NOT NULL,
       [Name]        NVARCHAR(50)   NOT NULL,
       [Description] NVARCHAR(4000) NULL,
       CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ([Identifier] ASC)
)

GO
CREATE TRIGGER [dbo].[TableAInsteadOfUpdate] ON [dbo].[TableA] INSTEAD OF UPDATE AS
BEGIN

       --Obviously some code here

       UPDATE
             dbo.TableA
       SET
             Name = t0.Name
             ,Description = t0.Description
       FROM
             inserted AS t0
       WHERE
             dbo.TableA.Identifier = t0.Identifier

       --And here to...
END

The UPDATE inside the trigger update all columns, disregarding of the real updated columns in the original UPDATE query :

--This update will lead to a final UPDATE instruction on all columns
UPDATE TableA SET Name = 'test' WHERE Identifier = 2

The column-tracking option of replication article is not working because SQL Server replication internaly use COLUMNS_UPDATED() to flag updated columns in a trigger.

One solution would be to execute individual UPDATE for each column separatly like that :

CREATE TRIGGER [dbo].[TableAInsteadOfUpdate] ON [dbo].[TableA] INSTEAD OF UPDATE AS
BEGIN

       --Obviously some code here

       IF UPDATE (Name)           BEGIN UPDATE TableA SET Name = t0.Name                FROM inserted AS t0 WHERE TableA.Identifier = t0.Identifier END
       IF UPDATE (Description)    BEGIN UPDATE TableA SET Description = t0.Description  FROM inserted AS t0 WHERE TableA.Identifier = t0.Identifier END

       --And here to...
END

This is obviously not a good things for performance but at least this should work. Another way would be to modify replication trigger to implement a real column data comparision logic but i’m not sure that’s a good idea…
Does someone have a better solution ?

By advance thank you for your answer and have a nice day!


texts: Oracle : How to grant to a ROLE the ability to affect a ROLE to a USER?

texts:
I have a role : ADMIN_ROLE

I'm connected as ADMIN_ROLE and created a TEST_USER and TEST_ROLE

I would like to be able to affect TEST_ROLE to TEST_USER using ADMIN_ROLE

I did (using system user) :

Grant create role to ADMIN_ROLE with admin option;
Grant create user to ADMIN_ROLE with admin option;

but when executing from ADMIN_ROLE :

Grant TEST_ROLE to TEST_USER

I'm receiving error: ORA 1932 - ADMIN option not granted for role string.

Any ideas please ?


texts:
I have several small questions and I wrote some simple examples to clarify them:

Will a nested aggregate take one value from from all groups? Like the minimal maximum of all groups? or the minimum count?

Select x, min(max(z))
From y
Group by x

On the same note, is there any merit in doing the following to get the minimal count from all groups, or the second line is unnecessary?

select x, min(count(*))
select x, count(*)
From y
Group by x

If both are invalid, how would you do a query on all groups like taking the minimum of all maximums in each group?
Can you do a query inside a "from"?

Select x
From y natural inner join (select z AS y
                           from foo)

Is it allowed to do from * after a group by?

Select x
From y
Group by x
Having avg(x) > (select *
                 from *
                 where x > 1)

and if not, how would you do a query on each group after the group by?

Note: this isn't some live version of SQL server, just old theoretical SQL.


texts:
Can someone tell me what the cause of this alert is that's captured by our SQL Audit trace we have setup using the built-in auditing functionality? This alert is generated from time to time in the audit log and the audit log has not been disabled or the server has not restarted.

<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data">
  <session>
    <![CDATA[EDW Audit$A]]>
  </session>
  <action>event enabled</action>
  <startup_type>automatic</startup_type>
  <object><![CDATA[audit_event]]></object>
</action_info>


texts:
I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of columns as the original, simply with the column names changed to better reflect the data coming in.

My problem is, the data is over 50 million rows, so I'm unable to simply alter the columns in place, so I've made a new table that I wish to use moving forward anyway. I've made the necessary column changes, but now when I try to load the data in, it looks like the process is simply stick.

+-----+------+-----------+------+---------+------+-------+----------------------------------

--------------------------------------------------------------------+
| Id  | User | Host      | db       | Command | Time | State | Info                                                                                |
+-----+------+-----------+------+---------+------+-------+------------------------------------------------------------------------------------------------------+
| 481 | root | localhost | database | Query   | 8617 | NULL  | LOAD DATA  LOCAL INFILE '/home/SQLBackUps/file.csv' INTO TABLE `tablename` FIELDS   |
| 483 | root | localhost | database | Sleep   | 7174 |       | NULL                                                                                |
| 485 | root | localhost | database | Query   |    0 | NULL  | show processlist                                                                                |
+-----+------+-----------+------+---------+------+-------+------------------------------------------------------------------------------------------------------+

I ran the talk using mysqlimport with the necessary flags, so it should in theory at least be running, but as the state says null, I'm not sure what that really means as NULL, according to the documentation, only appears for show processlist. The file's permissions are as follows:

-rw-rw-rw-  1 mysql mysql 14141022004 May 15 16:42 file.csv

The file was created using SELECT INTO OUTFILE ...

Is there anything I can do to speed up this process? Right now it's on a very limited Dev machine with 2GB of RAM and an okay processor, so I understand it will take a bit of time, but given it's been over 2 hours and it's still in a null state, I'm stuck wondering if something is wrong, or if there is anything I can do to speed this process up.

Are there other ways to import the data quickly? Are there any flags I can use to speed this process up? Any, and all help is appreciated.

Update:

I started the process again in the very early morning, and have let it run for about 7 and a half hours now, and the process list looks as follows:

mysql> show processlist;
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time  | State | Info                                                                                |
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+
|  9 | root | localhost | db   | Query   | 22978 | NULL  | LOAD DATA INFILE '/home/SQLBackUps/file.csv' INTO TABLE table FIELDS TERMINATED |
| 15 | root | localhost | db   | Query   |     0 | NULL  | show processlist                                                                                |
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+


texts: How do I "link" two or more rows of the same table with a unique "link" id?

texts:
I am looking for a better way to create a link between two or more rows of the same table, but the link itself must have a unique ID. What I am currently doing seems rather convoluted. I have a positions table that I am "linking" via the table linked_positions, but using yet a tertiary link via the linked_positions_1nf table. Perhaps it will be easier to see the structures with some sample data...

mysql> SELECT id FROM positions WHERE id IN (104, 105);
+-----+
| id  |
+-----+
| 104 |
| 105 |
+-----+

mysql> SELECT id, linked_ids position_ids FROM linked_positions LIMIT 1;
+----+--------------+
| id | position_ids |
+----+--------------+
|  1 | 104, 105     |
+----+--------------+

mysql> SELECT * FROM linked_positions_1nf WHERE link_id = 1;
+-----+---------+-------------+
| id  | link_id | position_id |
+-----+---------+-------------+
| 123 |       1 |         104 |
| 124 |       1 |         105 |
+-----+---------+-------------+

It seems like it would be more ideal to only have one linking table to achieve the above, but I haven't been able to find a more refined solution. Thanks in advance for any guidance.


texts: Compare the same column on different dates

texts:
I have two tables

project_key: project_id, key_id
project_report: project_id, key_id, level, created_at

I would like to compare the column level when created_at is now() and created_at is now()::date - 1.

Sample data:

project_id: 1, keyword_id: 1, level: 2, created_at: today
project_id: 1, keyword_id: 2, level: 3, created_at: today
project_id: 1, keyword_id: 3, level: 5, created_at: today
project_id: 1, keyword_id: 4, level: 6, created_at: today

project_id: 1, keyword_id: 1, level: 1, created_at: yesterday
project_id: 1, keyword_id: 2, level: 6, created_at: yesterday
project_id: 1, keyword_id: 3, level: 2, created_at: yesterday
project_id: 1, keyword_id: 4, level: 6, created_at: yesterday

In the end a report should be produced, the expected output:

Legend:

increased: today < yesterday
decreased: today > yesterday
not_changed: today =  yesterday

Output:

project_id: 1, increased: 1, decreased: 2, not_changed: 1

Also when one of the level(today or yesterday) is 0 the increased and decreased will change:

increased: today > yesterday
decreased: today < yesterday


texts: SQL Server 2008 user and group permissions behavior weirdness

texts:
I have a strange situation:

An AD user account has a login to a SQL Server 2008 database without any roles assigned to that user.

That same database has a login for an AD group with datareader/datawrites roles.

The AD user is a member of the AD group.

It looks like if ONLY the user-permissions are used. He is not able to do anything with the database.

This doesn't make sense to me at all.

I would presume that the sum of all roles (from user and group) would be in effect.

As I'm not really a SQL Server guy I am at a loss to explain this. Can someone clarify how user and group derived permissions interact ?

P.S. The user has been a member of that AD group for years. This is not a case of a recent addition to an AD-group not being synchronized in AD.


texts: Measure compilation time of stored procedure

texts:
I need to measure how much time it takes for the MSSQL-2012 to compile a stored procedure.

Is it possible?


texts: How to prevent redundant relationships in data?

texts:
If I want to link users with other users. Would this be a good way to do it:

I'm asking because I probably get double data like this if i don't do specific checks:

+------+-------------+
|userId|relatedUserId|
+------+-------------+
|   1  |      2      |
|   1  |      3      |
|   2  |      1      |
|   2  |      3      |
|   3  |      1      |
|   3  |      2      |
+------+-------------+


texts: PostgreSQL streaming replication with slots works for one slave, but not for another slave

texts:
Master (10.0.0.1):

Edit postgresql.conf

listen_addresses = '*'
wal_level = logical
max_wal_senders = 3
max_replication_slots = 3
hot_standby = on

Edit pg_hba.conf

host    replication     postgres    0.0.0.0/0   trust

Create a replication slot for the slave

select * from pg_create_physical_replication_slot('master');

Slave #1 (10.0.0.2):

Remove 9.4/main directory

rm -rf /var/lib/postgresql/9.4/main

Do a base backup of the master

pg_basebackup -P -R -X stream -c fast -h 10.0.0.1 -U postgres -D /var/lib/postgresql/9.4/main

Edit recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres host=10.0.0.1 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'master'

Copy conf files from master

rsync -av user@10.0.0.1:/etc/postgresql/ /etc/postgresql/

Restart cluster

pg_ctlcluster 9.4 main restart

Slave #2 (10.0.0.3):

Same steps as for slave #1 except the ip of the slave is different.

The problem: A change on the master propagates the change onto slave #1 but not slave #2. Tried rebooting slave #2 and setting up again from a fresh install but still doesn't work. Any ideas?

Thanks!


texts:
There's a SQL Server 2012 machine which I have login and password. It's a SQL Server login, not an AD one.

I'm able to login using SSMS. But when I configure a linked server in my SQL Server I get error message saying this login doesn't have access:

  Cannot process the object ""DB"."SCHEMA"."TABLE"". The OLE DB provider "SQLNCLI11" for linked server "SERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

Any idea why this is happening?

Update: This is the scripted creation of the linked server:

USE [master]
GO

/****** Object:  LinkedServer [REMOTE_SERVER_HOST]    Script Date: 16/03/2016 13:56:56 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'REMOTE_SERVER_HOST', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REMOTE_SERVER_HOST',@useself=N'False',@locallogin=N'LOCAL_LOGIN',@rmtuser=N'REMOTE_LOGIN',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


texts: Sync vs Async Replication for Aurora MySQL

texts:
In what way (synchronous or asynchronous) Amazon Aurora RDS for MySQL implements the transfer of data from master to replica, and can this method be changed using settings?
For reference in the Amazon documentation, I found references to both synchronous and asynchronous replication of the master to the slaves:

When data is written to the primary DB instance, Aurora synchronously replicates the data across Availability Zones to six storage nodes associated with your cluster volume.

High availability for Amazon Aurora

For the MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server database engines, Amazon RDS creates a second DB instance using a snapshot of the source DB instance. It then uses the engines' native asynchronous replication to update the read replica whenever there is a change to the source DB instance.

Amazon RDS Read Replicas


texts:
How to configure the property of DBPROP_COMMANDTIMEOUT ?

Can this property be configured in Database ?

Thanks.


texts: How to update two records with two different values with two 'where' conditions at once?

texts:
In T-SQL, how to have this:

DECLARE @amount decimal(18,2) = 1000
UPDATE account
    SET balance = balance +  @amount WHERE accountID= 1;
UPDATE account
    SET balance = balance -  @amount WHERE accountID= 2;

in one update query to make sure eg. no one is changing account currency between two updates? When a row with ID = 1 is locked I want the row with ID = 2 also to be locked. Isolation level is read commited. Something like:

   DECLARE @amount decimal(18,2) = 1000
   UPDATE account
        SET balance = balance +  @amount WHERE accountID= 1;
    AND
        SET balance = balance -  @amount WHERE accountID= 2;


texts: How to script server level objects in sql server?

texts:
I regularly do sql server installations (mainly sql server 2016), in virtual machines or otherwise, and I have a bunch of things like permissions, and linked servers and endpoints that I have mostly all scripted so that in the new installs I can create all these objects.

On a brand new install that I am working on however,
it was supposed to be an exact copy of a specific server let's call it serverX.
serverX had a couple of endpoints not specified on my creation of sql server server level objects script
therefore it caused me troble - port 4022 was not listed, and not listening and the powershell connectivity test was not working
 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection my_sql_server -port 4022'
 EXEC xp_cmdshell @sql

although all the firewall was already set up.
to avoid this in the future, is there a way to script all the endpoints?
or better would be script all server level objects as per the picture above?
obviously to script this specific endpoint, you just need to right-click and script it:
USE [master]
GO

/****** Object:  Endpoint [ServiceBrokerEndpoint]
   Script Date: 20/03/2021 16:04:05
 ******/

CREATE ENDPOINT [ServiceBrokerEndpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)
GO

and after I did that the netstat command started to list port 4022 - the service broker default.
but just to automate future installs, is there a way to via code or powershell, script all or some server level objects?


texts:
During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new tables that I will then execute on the production server before I publish the new version of the application.

So I'm wondering is there a way to put the whole generate script file in the transaction and rollback changes if there's an error returning everything to the previous state, no new tables added, data inserted etc.

If there is a reason that this isn't a recommended approach please explain.


texts:
While trying to optimize a few queries to not do with PHP something I could bring ready-to-use from database, I tried to convert two routines originally created in PHP to encode an integer in its AlphaID representation and then back, from string to integer.

Let's take a deep breath before this can be considered as a Stack Overflow question, I asked before posting

I tried to port them by myself but there were a few problems:

The original routine, in PHP, was able to encode correctly up to the maximum possible value fo a 32bits integer (2147483647), but for some reason my port stops earlier. I wasn't capable to debug further but in some moment after 2000000000 doesn't encode anymore.
Up to the point in which the function works, the encoding is always "one ahead', for example, encoding the integer 2, instead of returning the expected baaac is resulting in baaad, the equivalent of the integer 3 when encoded

I've got suggestions to use AES_ENCRYPT() and its counterpart to decrypt, but that's not the purpose because what I really need, as a requirement of the project, is to display something better characterized as a PRIMARY KEY in a textual representation of letters and number (only!)

I also had suggestions to use hashing functions and I tested MD5(), SHA1() and all combinations of SHA2() but all of them result in very long strings which would be difficult to display on web environment.

And, of course, being able to do such conversion straight in the database would allow me to change INT for BIGINT and have up to 9,223,372,036,854,775,807 records.

So, that said, is there a way to represent integer as AlphaID with MSQL? Or something similar then, I'm open to suggestions.

If not something built-in, and maybe this could be considered as off-topic, how could I fix the implementations I made?


texts: How to return JSON objects in tabular format using T-SQL?

texts:
I am storing student data by group (or class) and week in the following format:

where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can change from week to week, so I am trying to come up with a way to display the results by group as follows:

where I can see the grades by week side by side.

The solution I have come up so far is not very elegant:

DECLARE @json VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 1)
drop table #week1
drop table #week2
SELECT *
into #Week1
FROM    OPENJSON(@json)
WITH (
        StudentId INT '$.PersonId',
        StudentName VARCHAR(100) '$.StudentName',
        Week1 FLOAT '$.GradePoints'
)

DECLARE @json2 VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 2)
SELECT *
into #Week2
FROM    OPENJSON(@json2)
WITH (
        StudentId INT '$.PersonId',
        StudentName VARCHAR(100) '$.StudentName',
        Week2 FLOAT '$.GradePoints'
)

select w1.studentid, w1.studentname, w1.week1, w2.week2
from #week1 w1
FULL join #week2 w2
on w1.studentid = w2.studentid

Besides not being elegant, this approach is not scalable for a 14-week course.

If anyone can think of a better solution (even if it requires a change in how the data is stored), I am open to suggestions.

Thanks in advance.


texts: Looking for ways to optimize sql query with inner join

texts:
My join query seems to be using the right index but is still slow. I am not sure where the issue lies and would appreciate any tips. The query and its explain is as follows:

SELECT
  events.*,
  event_subscriptions.read_at AS read_at,
  event_subscriptions.created_at
FROM events
INNER JOIN event_subscriptions
  ON event_subscriptions.event_id = events.id
WHERE event_subscriptions.user_id = 456
  AND event_subscriptions.group_id IN (123, 321, 222, 333)
  AND event_subscriptions.valid IS true
  AND events.type != 1
ORDER BY date_trunc('second', event_subscriptions.created_at) DESC
LIMIT 50;

explain:

Limit  (cost=24.84..24.84 rows=4 width=215) (actual time=13409.821..13409.842 rows=50 loops=1)    ->  Sort  (cost=24.84..24.84 rows=4 width=215) (actual time=13409.819..13409.828 rows=50 loops=1)
         Sort Key: (date_trunc('second'::text, event_subscriptions.created_at)) DESC
         Sort Method: top-N heapsort  Memory: 76kB
         ->  Nested Loop  (cost=0.23..24.83 rows=4 width=215) (actual time=0.105..13399.879 rows=7259 loops=1)
               ->  Index Scan using index_event_subscriptions_on_user_id_group_id on event_subscriptions  (cost=0.11..8.35 rows=4 width=20) (actual time=0.066..4728.728 rows=7295 loops=1)
                     Index Cond: ((user_id = 263010) AND (valid = true) AND (group_id = ANY ('{95513,220377,215898}'::integer[])))
               ->  Index Scan using events_pkey on events  (cost=0.11..4.12 rows=1 width=199) (actual time=1.183..1.185 rows=1 loops=7295)
                     Index Cond: (id = event_subscriptions.event_id)
                     Filter: (type <> 1)
                     Rows Removed by Filter: 0  Planning time: 23.064 ms  Execution time: 13409.973 ms

events is the table with actual data and event_subscriptions is a denormalized version which contains event_id along with some user info. Events have close to ~60M rows with event_subscriptions having 220M rows.

indexes:

index_event_subscriptions_on_user_id_group_id btree (user_id, valid, group_id, read_at DESC) WHERE valid IS TRUE


texts:
Oracle SQL geometry allows me to find the nearest neighbor using

SDO_NN(neighbors.GEOLOC,p.GEOLOC,'SDO_NUM_RES=1 UNIT = MILE',1) = 'TRUE')

How can I get the select the second nearest point?


texts:
I've model,

class ModelA(models.Model):
    device_id = models.IntegerField()
    deal_sent = ArrayField(models.IntegerField)

For given device_id, we can have a list of repeating deal_sent. For eg:- Consider the following table

device_id         deals_sent
    1       [4,5,1]
    2       [1,2,3,4,2,3,4,3,4,5,1]
    3       [1,4,2,3,4,3,4,5,1]
    4       [1,4,3,4,5,1]
    5       [4,5]
    6       [1,3,4,2,3,4,3,4,5,1]

I want to get top N device_id, who have minimum count of 1 in deal_sent ArrayField. For eg:- If I want top 2 device_id who have minimum count of 1 in deals_sent, then device_id 1 and 5 will be returned since the count of 1 in deals_sent was least in device_id 1 and 5.

I've followed this question which seems to be a close solution what I'M expecting.

with elements (element) as (
   select unnest(ARRAY['a','b','c','a','a'])
)
select count(*)
from elements
where element = 'a';

How can I convert the above query to DjangoORM query? OR What can the alternate perfect query at the DB level itself without using a for-loop at the application level?


texts: Find changed columns for near-identical rows across tables

texts:
I run PostgreSQL 9.3.5 on Windows 7, 64-bit.
My data arrives quarterly, in multiple tables (table1, ..., tableN) that are linked, intra-period, by cross-table constraints based on key identifiers. Among other columns, each table has identifiers that persist over time: pfi - persistent feature identifier and ufi - universal feature identifier.
pfi is unique per table (it's exceedingly rare that table1.pfi = table2.pfi.
ufi is unique across all tables and across all time. It's not a hash of the row data, but you could think of it as such.
Each period, in each table, some new pfiare brought into being and some old pfi are retired. Some pfi change attributes. ufi tracks any change to any attribute for a given pfi(row), so to fetch changed (and new) rows for table1 it's simply a matter of:
-- 1st query
select a.*
into vm201512.property_d
from vm201512.property a
where not exists (select 1 from vm201412.property where ufi = a.ufi);

This selects all rows which are either new (new pfi) or changed in at least one column.
About 96% of each table remains unchanged in every respect. Accordingly, in analysing the cross-period changes I build a table that only includes changed and new data. This reduces the table size from ~3.5m rows to ~225k rows: that's a BIG reduction if you subsequently do spatial comparisons with relatively-complex polygons and multiple (spatial and non-spatial) JOINs.
The property table has relatively few columns, so I can identify which elements of the data have changes as follows:
-- 2nd query
create table vm201512.property_d_changes as
select pfi,
   case when a.view_pfi=b.view_pfi then 0::int else 1::INT end as view_pfi,
   case when a.status=b.status then 0::int else 1::INT end as status,
   case when a.property_type=b.property_type then 0::int else 1::INT end as property_type,
   -- ... more columns
from vm201512.property_d a -- table created with first query
join vm201412.property b using (pfi);

This gives me a nice table where I can determine precisely what changes happened to a changed (not new) row. I can figure out that pfi 123456 had changes to its propnum and its status; I can figure out how many pfi had changes to their view_pfi - that sort of thing.
Several of the other tables have >50 columns, which makes the case statement unwieldy (I realise it only has to be coded once, but what if the data structure changes?)
Question
With two rows in 2 different tables new.table1, old.table1 where new.table1.pfi = old.table1.pfi and one or more columns different, is there a parsimonious, elegant PostgreSQL statement to figure out the changed columns? Or am I stuck with CASE?
I realise I could write a dynamic function to loop through all columns for a given table, and build the query with CASE statements.


texts: reduce network traffic for MySQL queries

texts:
As part of a hobby-project I have a number of servers on my network that measure various parameters (both system statistics and domotica) and store the results (mostly as 5-minute averages) in various tables in a MySQL database running on one of the servers.

Using bash each server periodically queries the MySQL server to get a subset of the stored data in a local file. Then, I use python and gnuplot to turn the raw data in some nice trend graphs.

I currently make hourly, daily and weekly graphs. I already have a lot of data and I would like to make a couple of yearly graphs, however, I'm seeing that queries for a week's worth of data already take some time (query + network + local diskwrites) so I'm hesitant to proceed.

I was wondering if you guys have experience with this type of problem and know of / have experience with standard solutions that I have not found.

Are there magic queries, compression techniques or local storage solutions that I might want to investigate? The goal being to reduce the network load and reduce the MySQL server workload by preventing it to have to server the same data over and over again.


texts: How do I know an index is used for any queries?

texts:
I've found a likely abandoned index in the production database. It's not included into .sqlproj from which this database it usually deployed. Most likely it was created for some experiments and then left there but I'm not sure. It's not mentioned in any WITH INDEX hints. It's not used for a PK or unique constraint.

I want to make sure that deleting this index will not cause performance degradation. How can I do that?


texts: Remove index fillfactor for Azure migration

texts:
I'm deploying a SQL Server database to Azure. I've tried to export to a .bacpac, but receive an error:

  fill factor set and is not supported when as part of a data package

Using the script in this link I can locate where the fill factors indexes are.  I face 2 issues:

The index property page will not let me set the fill factor to 0. I have read that 100 is the same as 0. This does work.
I have too many indexes to do manually. Is there a script I can use that will reset this value to 0?


texts:
I'm trying to generate physical database from my logical model using ERWIN 7.

All is well in the script that is generated automatically, there is only the problem that the model is created in the database "master" which is the default SQL Server, rather than being created in the database that I inserted along with the model.

I cant find a way to associate my MODEL with the database I'm creating along with the model.

Here is the script that ERWIN auto-generates:

CREATE DATABASE Movies go
ALTER DATABASE Movies SET  RECOVERY FULL go
CREATE SCHEMA fbd AUTHORIZATION dbo go
CREATE DEFAULT Default_Value_movies_gen   AS 1 go
CREATE DEFAULT Default_Value_titulo_movies    AS 'No title' go

But it should generate automatically:

CREATE DATABASE Movies go
--Here should be "USE Movies",to use database that ERWIN have just created
ALTER DATABASE Movies SET RECOVERY FULL go
CREATE SCHEMA fbd AUTHORIZATION dbo go
CREATE DEFAULT Default_Value_movies_gen AS 1 go
CREATE DEFAULT Default_Value_titulo_movies AS 'No title' go

Any ideas?


texts:
SQL>
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00204: error in reading (block 3, # blocks 8) of control file
ORA-00202: control file:
'E:\APP\SHAVULHAMEED\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O
device error.


texts: Restoring a backup gives "Failed exit code 1"

texts:
I'm migrating from Heroku's Postgres to AWS RDS. I've generated a pg_backup.dump file from Heroku, installed Postgres on my local machine, launched PGAdmin, configure remote AWS server, and used the Tools > Restore feature to restore my database.
The process completed with failed (exit code: 1)
When I look throuh the progress window I see lots of errors like these:
pg_restore: creating SEQUENCE "public.auth_group_id_seq"
pg_restore: from TOC entry 203; 1259 24981038 SEQUENCE auth_group_id_seq uohhysqaagrjqj
pg_restore: error: could not execute query: ERROR:  role "xxxxx" does not exist
Command was: ALTER TABLE public.auth_group_id_seq OWNER TO xxxxx;

Browsing through my database I can see that everything has restored. Anything to worry about? Looks like the role name is different in the RDS database and that's all?


texts:
I have an inline TVF that take much longer to run than should be necessary. The issue looks to be the Linked Server reference.

The Linked Server is SQL Server 2012. The server with the TVF is SQL 2008R2.

The Remote Query Cost is 715%! How can I improve the performance on this?

ALTER FUNCTION [Dashboard.Trades.Facade].[TESTClientData]
(
    @FundID         nvarchar(4),
    @StartDate      datetime,
    @EndDate        datetime
)
RETURNS TABLE
AS
RETURN
    SELECT
        DISTINCT
            TradeID,
            TradeAction,
            CASE
                WHEN TradeAction = 'Delete' THEN 'OK'
                WHEN TradeAction = 'Cancel' THEN 'OK'
                ELSE 'Not OK'
            END AS 'Status'
    FROM dbo.TradeData
    WHERE (Fund = @FundID)
    OR (Fund COLLATE DATABASE_DEFAULT IN
                        (SELECT DISTINCT me.Value1 COLLATE DATABASE_DEFAULT AS Fund
                        FROM
                            [MyLinkedServer].[DB].[dbo].[MappingElement] AS me
                        INNER JOIN
                            [MyLinkedServer].[DB].[dbo].[SourceMapping] AS sm
                            ON me.MappingID = sm.MappingID
                        WHERE     (me.ValidValue = @FundID)
                        AND (sm.Name = 'FundTranslations')))
    AND (TradeFileDate >= @StartDate)
    AND (TradeFileDate <= @EndDate)
    AND (TradeAction != 'Delete' AND TradeAction != 'Cancel')
    AND (TradeID NOT LIKE '%Reset%')

Query plan


texts:
We are planning to backup database using RMAN, but since I'm a newbie in DB administration, I don't have much knowledge in this.

I've a few queries though.
Currently we are using Oracle 11g database in noarchivelog mode. As far as I know, running a database in archivelog mode, backup is taken at short intervals, which I don't prefer. I need the backup to be taken only once in a day, that too without shutting down my database.
Is that possible?

Which is better option, database to be run in archive mode or noarchive mode?
Also, if I'm going for noarchive mode, do I need to delete the backups manually or,
does it happen automatically?


texts: Tablesize makes no sense between enviroments

texts:
We are currently building a GatewayAPi which hold some data required to intergrate with legacy grids.
This means these tables hold some bulk data until the legacy components are removed.
Our database for this is set in Azure, 1 in an Dev/Test subscription, 1 in an normal one.
Both running an elastic pool in which this database resides.
Currently our Acceptance gateway database hold 2 big tables, 1 (A) with 9.566.312 rows, totaling about 8888 MB and 1 (B) with 47.731.430 rows totaling about 20160 MB.
Now here is where it gets weird.
We moved this to the production enviroment and synced the data as well, now off course production holds more records, however the 2 tables grew exponentially.
When looking at the production database we have the following statistics.
Table A holds 19.151.064 and now has a total space of 32720 MB, 3.5 times more while the data only roughly doubles. Table B holds 59.908.850 rows and has a total space of 46615 MB 2.3 times more while only having 12 million more records.
Both acceptance and production have the same indexes added and seem to follow this logic as well. The NonClustered index on Table B also is 3.4 times bigger.
Am i missing something?
The schema of Table B (focussing on this one as this is the biggest)
CREATE TABLE [PriceHistory](
    [ProductId] [int] NOT NULL,
    [ProductName] [nvarchar](1024) NOT NULL,
    [SearchValue] [nvarchar](30) NOT NULL,
    [Subclass] [nvarchar](3) NOT NULL,
    [Brand] [nvarchar](50) NOT NULL,
    [BasePrice] [decimal](18, 4) NOT NULL,
    [SalesLevel] [nvarchar](5) NOT NULL,
    [ItemPrice] [decimal](18, 4) NOT NULL,
    [Factor] [decimal](18, 4) NOT NULL,
    [Rule] [nvarchar](10) NOT NULL,
    [EffectiveUntil] [datetime] NULL,
    [CreatedBy] [nchar](10) NOT NULL,
    [CreateDateUTC] [datetime] NOT NULL,
 CONSTRAINT [PK_PriceHistory] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC,
    [SalesLevel] ASC,
    [CreateDateUTC] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Index
CREATE NONCLUSTERED INDEX [NCX_PriceHistory_CreateDateUTC] ON [PriceHistory]
(
    [CreateDateUTC] DESC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]


texts: How can I tell if a SQL Server backup is compressed?

texts:
We have recently upgraded from SQL Server 2005 to SQL Server 2012.  Under SQL Server 2005 there is no option to create compressed backups as there is in 2012.

If you attempt BACKUP DATABASE ... WITH (COMPRESSION); to a file that has already been initialized without compression, the BACKUP DATABASE command will fail with the following error message:

ERROR MESSAGE : BACKUP DATABASE is terminating abnormally.
ERROR CODE : 3013

How can I tell if an existing backup file is initialized for compressed backups?


texts: Elastic: Are Analyzers only for `text` type fields?

texts:
Most of the documentation about Elastic Analyzers assumes that you NEED an Analyzer and so the documentation talks mostly about how Analyzers work and the differences between the different Analyzers.

But I had trouble finding authoritative documentation answering the question: are Elastic Analyzers only for text type fields?


texts: PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type

texts:
I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions:

 // Defining struct for linked list
 typedef struct intSet *link;

typedef struct intList {
    int num;
    link next;
} intList;

 //  Create a new node
 link newNode(int item) {
     link n = (link) palloc(sizeof(*n));
     n->num = item;
     n->next = NULL;
     return n;
 }

 link insertEnd(link list, link n){
    link curr;
    // Empty list
    if(list == NULL){
        list = n;
        n->next = NULL;
    // If list not empty, iterate to end of list, then append
    } else {
        for(curr = list; curr->next != NULL; curr = curr->next) {
        }
        curr->next = n;
        n->next = NULL;
     }
     return list;
 }

 PG_FUNCTION_INFO_V1(intList_in);

 Datum
 intList_in(PG_FUNCTION_ARGS)
 {
     char       *str = PG_GETARG_CSTRING(0);
     char       *token;

 // Create an empty linked list
 link newList;
 newList = NULL;

 // Get individual ints from a set e.g. {1, 2, 3, 4}
 token = strtok(str, ",{} ");

 // For each int, create a new node then
 // append to list
   while (token != NULL) {
       link a = NULL;
       a = newNode(atoi(token));
       newList = insertEnd(newList, a);
       token = strtok(NULL, ",{} ");
   }

   PG_RETURN_POINTER(newList);
}

One thing I'm struggling with is that in the set.source file that I'm to include with this set.c file, in the creation of the type:

CREATE TYPE set {
     internallength = ?,
     input = .., output = .., alignment = ..
}

I'm not sure what to put for the internal length. Upon checking the sizes, I found that the struct had a size of 8 bytes, the num member 4 bytes, and the pointer next was 8 bytes. Does this mean that I have to put 'variable' in the internallength field? If so what would the values of the (int4 field in the struct for var length) and alignment be in this case?

Any pointers/tips would be greatly appreciated.


texts: What does a TRUNCATE TABLE write to the Log file in SQL Server?

texts: I know it does not log everything like a DELETE would but I am not sure what it would write to the log file.


texts: Assign Failover Cluster and High Availability Always On from Old to New Domain

texts:
What are best practices steps when the Windows Server 2016 machine hosting SQL Server and failover cluster (2 node and shared folder witness) is joined to another domain?
What steps definitely/might need to be done for SQL Server and failover cluster before and after the new domain is joined?