texts: Besides attaching the debugger, is there any other differences between "(local)\Instance" and "MyLocalName\Instance"?

texts:
When using SQL Server Management Studio for SQL Server 2008 R2 connecting to a local SQL Server 2008 R2 instance (version 10.50.2500), I know that if I am using the instance name (local)\sql2008 it will fail to attach the debugger to any queries I attempt to debug with the error message

  Unable to start T-SQL Debugging. Could not connect to computer '(local)'.  The requested name is valid, but no
  data of the requested type was found.

However, If I change that connection to src\sql2008 the debugger runs happily.

Are there any other differences in having your connection use the (local) alias other than the debugger issue I pointed out above?


texts: Trigger Gives an Error on Multiple Rows SQL SERVER

texts:
I have a problem when I create a trigger which is giving me this error:

  Msg 512, Level 16, State 1
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression***

The Trigger is :

Create TRIGGER AccountChange
ON Customer
AFTER UPDATE
AS
BEGIN
    IF(UPDATE(TotalSales))
    BEGIN
        DECLARE @TotalSales MONEY,@AccountNumber NVARCHAR(20),
            @TotalSalesOld MONEY,@customText1 NVARCHAR(50),
            @AccountTypeId int

        SET @TotalSales=(SELECT TotalSales FROM INSERTED)
        SET @TotalSalesOld=(SELECT TotalSales FROM DELETED)
        SET @AccountNumber=(SELECT AccountNumber FROM INSERTED)
        SET @AccountTypeId=(SELECT AccountTypeId FROM INSERTED)

            IF(@TotalSales BETWEEN 0 AND 5000)

                    UPDATE Customer SET AccountTypeID=8
                      WHERE AccountNumber=@AccountNumber

   END
END


texts: mysql no longer defaulting to /tmp/mysql.sock

texts:
I was testing some software to see if it could connect to mysql correctly when mysql is not using /tmp/mysql.sock.

I stopped mysql, editted /etc/my.cnf to add socket=/var/lib/mysql/mysql.sock restarted and mysql was on the new socket just fine.

Fixed my software.

Now I stopped mysql, removed socket statement from /etc/my.cnf, restarted mysql and it is still using /var/lib/mysql/mysql.sock.

I have restarted and cannot get it to use /tmp/mysql.sock unless I add it to /etc/my.cnf and I do not want to do that because of testing constraints.

Why would it no longer go to /tmp/mysql.sock when there is no configure parameter and there is no commmand line parameter (that I can see from ps aux) either?

Linux CentOS 6.5 Final
mysqld  Ver 5.6.17 for Linux on i686 (MySQL Community Server (GPL))

Help me with this please.

Thanx

Log entries

140624 10:37:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-06-24 10:37:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defa
2014-06-24 10:37:37 1277 [Note] Plugin 'FEDERATED' is disabled.
2014-06-24 10:37:37 1277 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2014-06-24 10:37:37 1277 [Note] InnoDB: The InnoDB memory heap is disabled
2014-06-24 10:37:37 1277 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2014-06-24 10:37:37 1277 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-06-24 10:37:37 1277 [Note] InnoDB: Using Linux native AIO
2014-06-24 10:37:37 1277 [Note] InnoDB: Not using CPU crc32 instructions
2014-06-24 10:37:37 1277 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-06-24 10:37:37 1277 [Note] InnoDB: Completed initialization of buffer pool
2014-06-24 10:37:37 1277 [Note] InnoDB: Highest supported file format is Barracuda.
2014-06-24 10:37:38 1277 [Note] InnoDB: 128 rollback segment(s) are active.
2014-06-24 10:37:38 1277 [Note] InnoDB: Waiting for purge to start
2014-06-24 10:37:38 1277 [Note] InnoDB: 5.6.17 started; log sequence number 6066054
2014-06-24 10:37:38 1277 [Note] Server hostname (bind-address): '*'; port: 3306
2014-06-24 10:37:38 1277 [Note] IPv6 is available.
2014-06-24 10:37:38 1277 [Note]   - '::' resolves to '::';
2014-06-24 10:37:38 1277 [Note] Server socket created on IP: '::'.
2014-06-24 10:37:38 1277 [Note] Event Scheduler: Loaded 0 events
2014-06-24 10:37:38 1277 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)


texts: Move a huge databases from one SQL Server to another

texts:
I have customers databases located on my SQL Server 2008 machine. The size of these databases are around 300 GB. I would like to move them all to another SQL Server 2008.

Is there a way to shrink them before moving them? And what is the best way to move them easily?

Will I have to create same users on the new one?

Please try to provide me details as possible.


texts: Package DBMS_REDEFINITION missing from Oracle 11g?

texts:
I'm trying to partition an existing table in Oracle and the research I've done seems to say that using the DBMS_REDEFINITION package seems to be the best way to do this.

The problem is I doesn't seem like my DB has this package.  I'm using Oracle 11g, but the online docs suggest it's been supported since 10.2.

Does anyone know how I can download or otherwise gain access to this package?

Thanks!


texts: MYSQL: How to select all attributes of all duplicate entries?

texts:
I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for these entries, such as email and firstname.

I first used group by to get every user where those two values are duplicates with this query:

SELECT *
FROM jasenet
GROUP BY hetu, sukunimi
HAVING count(*) >= 2
ORDER BY sukunimi;

This returns all duplicate entries. After some research I tried to use IN operator in order to find out each individual duplicate person with this query:

SELECT *
FROM jasenet
WHERE sukunimi
IN (SELECT sukunimi
    FROM jasenet
    GROUP BY hetu, sukunimi
    HAVING count(*) >= 2
    ORDER BY sukunimi)
ORDER BY sukunimi;

This works somewhat because I get each individual with duplicate surname, but in addition to this I need to be able to have the query use the social security number as well (hetu).

I tried this query, but it didn't work:

SELECT *
FROM jasenet
WHERE sukunimi
IN (SELECT sukunimi
    FROM jasenet
    GROUP BY hetu, sukunimi
    HAVING count(*) >= 2
    ORDER BY sukunimi)
AND hetu
IN (SELECT sukunimi
    FROM jasenet
    GROUP BY hetu, sukunimi
    HAVING count(*) >= 2
    ORDER BY sukunimi)
ORDER BY sukunimi;

How can I use two columns returned from a query in another query?


texts: too many entries for one single session and serila# in V$Session_longops

texts:
I have a concern with the entries in v$session_longops.
I used a query to identify query running from past 25 minutes for sid=62 which is given below:

SELECT s.sid,
       s.serial#,
       s.machine,
       s.program,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       totalwork,
       sofar
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = 62
AND    s.serial# = sl.serial#
AND    sl.elapsed_seconds > 25*60
and sofar<>totalwork;

it returns no rows.

But when I check with v$session_longops and v$session I have many entries for the same sid.
I am using OEM 12c. Even there in sql monitoring I am able to see many queries running for more than 3 hours. I use oracle 11.2.0.3

Can anyone help me understand y is it happening? I am confused..


texts:
There are ways already to compare sp_configure values for an instance and review all non-default settings.
Assuming that the model database of an instance has been modified from its original settings, is there a way to discover all non-default configurations and properties for a user database, specifically model?
Thanks,


texts: Does using noatime on modern Linux make sense?

texts:
Starting from version 2.6.30 (which was released 5 years ago), Linux has the option relatime enabled by default. It means updates of file access time are very infrequent and prudent.

But I still see recomendations to use noatime for databases or SSD discs. Are there any reason to use it? Does it make any measurable difference compared to relatime?


texts: Scaling Azure SQL database will make my website goes down?

texts:
I have a website using Azure SQL databases, and I have some questions:

How does the scaling process work? Is there a flow, diagram that illustrates it?
Will the scaling cause any downtime in my website?
Am I able to write things to the database during the scaling process?


texts: Use NEW in FROM clause in Postgres trigger?

texts:
I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.

SELECT
unnest(something)
FROM NEW

However, this seems to lead to an error:

  relation 'new' does not exist

How can I use the NEW row inside of the trigger function, in such a way that allows me to unnest an array field for further processing?

CREATE TABLE statements

Below are examples of the table structures:

CREATE TABLE parent_table (
    id uuid NOT NULL,
    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
    CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
  id serial primary key,
  parent_table_id uuid references parent_table(id),
  subfield_a text,
  subfield_a text
);

TRIGGER function

Here is the essence of the TRIGGER function:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS
$body$
begin
    if NEW.jsonb_array_field is null then
        raise exception 'jsonb_array_field is null';
    else
        insert into
            many_to_one_table(subfield_a, subfield_b)
            select
                parent_table_id, -- this is to be the ForeignKey
                json_data->>'subfieldA' as subfield_a,
                json_data->>'subfieldB' as subfield_b
            from (
                select
                    id, -- need ID for ForeignKey relationship
                    unnest(jsonb_array_field) as json_data
                    from new
            ) as unnested_json;
    end if;
    RETURN new;
end;
$body$
LANGUAGE plpgsql;

TRIGGER statement

The trigger statement can be run either before or after INSERT and UPDATE, so long as the data are mirrored in the 'migration' table.

CREATE TRIGGER unnest_and_normalize_json_on_insert AFTER INSERT ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey clarification
We are trying to transition this part of our data model to use a ForeignKey relationship rather than JSON field. The trigger is intended as a temporary step to ensure the normalized_table has data going forward, while we backfill/migrate data from older records.


texts: Is there a parser for Postgres' auto_explain to make it easier to read and understand?

texts:
We have a number of Postgres Functions that each call a number of other Postgres Functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't dive into the calls the parent_function makes.
Thanks to answers like this (Postgres query plan of a UDF invocation written in pgpsql), we discovered auto_explain.

So, we turned this stuff on:

auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_timing = true
auto_explain.log_nested_statements = true

The good news is that it gave us what we were looking for: A way to see the timing of every nested statement called during a long-running function.
However, the output is very verbose, and it's hard to see the parent-child relationship I'm "used" to seeing in profilers:

some_big_function           1000ms
  --child_function1          800ms
    --child_child_function   600ms
  --child_function2          200ms

Is there a better way to view this log output? Ideally it would group "nested statements" with their parents, showing me, at a glance, where I should look.
We tried pgbadger, but it's not configured to parse auto_explain output, since auto_explain of this verbosity isn't usually running on production servers.

Are there other log parsers (or other techniques) to help profile functions?


texts:
I'm Trying to connect to a remote DB using Sqlplus, but  the password contains a special character (!).
The SID is working fine, but Sqlplus is complaining about it when I use the ! in the password

/usr/lib/oracle/18.3/client64/bin/sqlplus -s bloth/hods!@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=remoteServer)(Port=1521))(CONNECT_DATA=(SID=SUDFR)))

  ORA-12505: TNS:listener does not currently know of SID given in connect

on the other hand, when I remove the special character I get

  ORA-01017: invalid username/password; logon denied

any idea how to fix that?


texts: How to query results where a column with 2 classifications has same value in another column in the same table?

texts:
I have a table like this:

Column A    Column B
-------------------------
Agent A     abc@gmail.com
Agent B     abc@gmail.com
Agent A     abc@gmail.com
Agent B     def@gmail.com
Agent C     efg@gmail.com

I want to see results where the same email address exists for Agents A and B.


texts:
suddenly My MSSQL Server Management Studio throw's error

  (Exception from HRESULT: 0x800736B3)

Database successfully connecting but

  While opening New Query Window following error message coming

when i press OK then MSSQL Server Management Studio closes automatically.


texts: Postgresql slow remote execution when using alternative clients

texts:
I noticed a strange perfomance issue when running sql queries on a remote Postgresql Server.

Everything good when using psql:

Connect via ssh and run things locally

$ ssh user@example.org
user@example:~$ psql -h localhost -U database -d database
database=# \timing
Timing is on.
database=# explain analyse select * from mytable where my_field='14123';
Time: 1646.577 ms (00:01.647)
database=# select * from mytable where my_field='14123';
Time: 1642.361 ms (00:01.642)

Forward postgresql to a local port and connect locally

$ ssh -L 1111:localhost:5432 user@example.org
$ psql -h localhost -p 1111 -d database -U database
database=# explain analyse select * from mytable where my_field='14123';
Time: 1669.424 ms (00:01.669)
database=# select * from mytable where my_field='14123';
Time: 1703.183 ms (00:01.703)

However, when I use GUI clients such as DBeaver or DataGrip I get the following timings:

DBeaver

Use option "SSH tunnel" in DBeaver

explain analyse select * from mytable where my_field='14123';
-- Execution time: 1665.447 ms
select * from mytable where my_field='14123';
-- Execution time: 6475 ms

"Direct" connection with locally forwarded port

explain analyse select * from mytable where my_field='14123';
-- Execution time: 1617.330 ms
select * from mytable where my_field='14123';
-- Execution time: 6370 ms

DataGrip

Use option "SSH tunnel" in DataGrip

sql> explain analyse select * from mytable where my_field='14123'
execution: 1 s 561 ms, fetching: 10 ms
sql> select * from mytable where my_field='14123' LIMIT 35
execution: 6 s 360 ms, fetching: 60 ms

"Direct" connection with locally forwarded port

sql> explain analyse select * from mytable where my_field='14123'
execution: 1 s 900 ms, fetching: 22 ms
sql> select * from mytable where my_field='14123' LIMIT 35
execution: 6 s 565 ms, fetching: 107 ms

Why are the timings so much worse when using these GUI applications? (6s instead of 1.5s) DataGrip's output suggests that it isn't the data being transferred, and in fact the select only yields 35 rows, which are < 100kb in total. Also it's much slower in both cases, and it takes about the same time in all the unexpected cases. What can cause this? Maybe something in the JDBC driver?

I connect to the server via VPN and then use an SSH-Tunnel to access the Postgresql server. Both the VPN server and database server are under little load and are very close to me (same city).
Postgresql version is 10 and runs on Ubuntu 18.04.


texts: Constraints number is differents after migrating from postgres 9.6 to 12.5

texts:
I have 2 postgres versions : 9.6 and 12.5
I have loaded the same database in them ( the famous dvdrental database)
I was checking the differences between the 2 databases running multiple queries for (Tables, Views, Sequences,Indexes...) the queries are from this page Validating database objects after migration using AWS SCT and AWS DMS
yet I only faced issues with validating the difference between the number of constraints between the 2 databases after running this query :
SELECT
       table_name,
       object_name,
       constraint_type,
       upper(trim(replace(replace(replace(replace(check_condition, '"', ''), '(', ''), ')', ''), 'CHECK', ''))) check_condition
  FROM (
    SELECT
           upper(n.nspname)||'.'||upper(c.relname) AS table_name,
           'SYS_C0000'||attnum                     AS object_name,
           'CHECK'                                 AS constraint_type,
           a.attname||' IS NOT NULL' AS check_condition
      FROM pg_attribute a, pg_class c, pg_namespace n
     WHERE a.attrelid = c.oid and c.relnamespace = n.oid AND n.nspname = lower('your_schema')
       AND attnotnull AND attstattarget <> 0
     UNION
    SELECT
           upper(conrelid::regclass::varchar)     AS table_name,
           upper(conname::varchar)                AS object_name,
           'CHECK'                                AS constraint_type,
           pg_get_constraintdef(c.oid)            AS check_condition
      FROM pg_constraint c
           JOIN pg_namespace n ON n.oid = c.connamespace
     WHERE  contype in ('c')
       AND conrelid::regclass::varchar <> '-'
       AND n.nspname = lower('public')
) a;

after executing this query I'm getting a different amount of constraints (202 records for the 9.6 version and 111 records for the 12.5 version ) could anyone highlight why I have this difference or suggest a better way to compare the number of constraints in both databases?


texts: Does the SQL Server Backup Database Command also backup views from a Database?

texts:
I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the  BACKUP DATABASE command to create a .bak file on a different server. This other server has its own job iterating through all  of the .baks and restoring them (with the RESTORE DATABASE command).

EDIT: The server running the RESTORE job restores the .baks into its own installation of SSMS

The jobs report success in the SQL Agent job history so I'm wondering whether or not these jobs should also backup and restore the Views in the databases (as they haven't seemed to) or if this is an indication that the scripts aren't quite working?


texts: Run aspnet_regsql.exe from SQLCMD Post-Deployment Script

texts:
I am trying to create a Post-Deployment script in SSDT that will include the creation of an ASP.NET Session State database to make sure everyone has this thing in place after deployment of my database project.

The following command creates the database for me if it's not yet in place:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d ASPSTATE -E

At first I tried running in as part of a SQLCMD script in general. The following approach in SQLCMD mode:

:!!C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d myStateDb -E

Then I tried to include it in the Post Deployment script basically the same way:

:!!C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d TESTdb -E

This ends up with the Visual Studio compiler complaining about a backslash exactly in this line.

  "SQL72007: Thy syntax check failed. Syntax error close to '\' in the
  batch near: [The command above again]"

Since it's working in SSMS I do not understand why my Post-Deployment script keeps complaining?


texts: Backup Structured Data and Unstructured Data seperately in SQL Server - Is it Possible?

texts:
Can I get some inputs on below:

SQL Server Version : 2014 SP2 GDR with AOAG environment.

Is it possible to backup Structured Data & Unstructured Data (FileStream) separately in SQL Server ?

For structured data backup, I am reading about the partial backup to exclude FILESTREAM file groups.

If backup Structured Data & Unstructured Data (FileStream) separately in SQL Server is possible , can I restore Structured Data & Unstructured Data (FileStream) separately ? How to restore FileStream data alone ? or structured data alone ?


texts:
I have a various date times stored as strings in my sqlserver database.

07/May/2012:00:01:49
07/May/2012:00:02:14
07/May/2012:00:02:26

This column is called datetimesent and it is quite easy to select group by on it. For example:

SELECT DateTimeSent, COUNT(*) FROM MyTable where group by DateTimeSent

This will group be every second. What I'd like to do is group by every ten seconds, so instead of grouping by the entire string, group by all of it except last letter.  How do I do this?

Thanks.


texts: Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

texts:
Suppose I have the following view:

CREATE VIEW dummy AS
SELECT a.some_column, b.other_column
FROM a LEFT JOIN b USING (key_column);

When I do a SELECT some_column FROM dummy, is there a way to instruct the optimizer to rewrite the query as SELECT some_column FROM a and skip the join? How?


texts: What is the safest way to install the sys schema on a production server?

texts:
The reason I'm asking is because I need to install the sys schema on my database so that I can utilize performance_schema because I'm hoping that will help me figure out why mysql is crashing every 30 minutes exactly.

I am running MySQL 5.5.45 on Windows Server 2016.  I have four schemas on this server, of which the production schema is 4.1GB in size.  The other schemas are almost as big.

MySQL Workbench gives me an option to install it. Also, I understand that running mysql_upgrade will install it, which as far as I can tell simply executes mysqlcheck.

So it looks like I have three options for installing the sys schema.

Execute mysqlcheck (I think)
Execute mysql_upgrade
Click the install button on MySQL Workbench

What does MySQL Workbench do exactly?  Does it simply execute mysql_upgrade or mysqlcheck?  Or does it do the install a different way?

Will any or all of these options make my other schemas unusable while this install is executing (as in locking tables)?  What other implications are there to using any of these three options?  And most importantly, needing to install this on a production server, what is my best approach?


texts:
My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT.  I have a requirement to be able to then wait until some nodes have actually replicated my latest commit.  If not, I will report the problem upstream.  And maybe mark the transaction as dubious on the local db, which will hopefully also get replicated.

bdr.bdr_node_slots seems to tell me a pg_lsn of the latest replication of each node.  But I need to know the pg_lsn of my last transaction.  Knowing the current state of the db now that transaction committed would be close enough.

I could only find pg_current_xlog_insert_location(), but it looks different.


texts: How do I restore an Oracle file system backup onto a new developer machine?

texts:
A server (not mine) has died due motherboard temperature problems and disk controller problems. Given that the company's IT support should have known better, and so on, the company does have a file system backup of a directory structure that looks like this:

Oracle
├───adminscripts
│       (this looks like old custom scripts)
├───EM
│   └───DBNAME
│           emkey.ora
├───fast_recovery_area
│   └───DBNAME
│       └───ARCHIVELOG
│           ├───2015_06_01
│           ├───2015_06_02
│           ├───....
│           ├───2018_11_03
│           └───2018_11_04
│                   O1_MF_1_5091_FXVO24XT_.ARC (~80MB)
│                   O1_MF_1_5092_FXWPML1J_.ARC (~85MB)
│                   O1_MF_1_5093_FXXTSO2W_.ARC (~40MB)
├───oradata
│   ├───orcl
│   │       CONTROL01.CTL (~10MB)
│   │       REDO01.LOG    (~50MB)
│   │       REDO02.LOG    (~50MB)
│   │       REDO03.LOG    (~50MB)
│   │       SYSAUX01.DBF  (~520MB)
│   │       SYSTEM01.DBF  (~750MB)
│   │       TEMP01.DBF    (~20MB)
│   │       UNDOTBS01.DBF (~46MB)
│   │       USERS01.DBF   (~5MB)
│   └───DBNAME
│       ├───CONTROLFILE
│       │       O1_MF_9T04M5YO_.CTL (~10MB)
│       ├───DATAFILE
│       │       O1_MF_SVO_DATA_9T06KN5J_.DBF (~9GB)
│       │       O1_MF_SVO_DATA_9T06LGRN_.DBF (~1.5GB)
│       │       O1_MF_SVO_DATA_9T06LO63_.DBF (~30MB)
│       │       O1_MF_SYSAUX_9T04J8M6_.DBF   (~1.8GB)
│       │       O1_MF_SYSTEM_9T04J8JR_.DBF   (~1.5GB)
│       │       O1_MF_TEMP_9T04MLQV_.TMP     (~600MB)
│       │       O1_MF_TOOLS_9T06LODY_.DBF    (~100MB)
│       │       O1_MF_UNDOTBS1_9T04J8N5_.DBF (~500MB)
│       │       O1_MF_USERS_9T04J8O5_.DBF    (~5MB)
│       └───ONLINELOG
│               O1_MF_1_9T04MB6F_.LOG        (~100MB)
│               O1_MF_2_9T04MC8Y_.LOG        (~100MB)
│               O1_MF_3_9T04MDHC_.LOG        (~100MB)
└───scripts
        (this looks like old custom scripts)

I have to assume that this database backup was done safely, with the database put into backup mode or stopped before the backup was done. The backup is a disk image of a data disk, and doesn't contain other configuration information.

So I need to be able to extract the data from this. How do I do it? I assume that I have to set up an Oracle instance on my computer (64-bit Windows 10 Pro), and then somehow move this data somewhere into the installation and hook it up. I've downloaded the Oracle installer, but is there a particular kind of setup that's going to be best for this?

I know how I'd do it with MySQL, but this is new to me. I've searched for backup / restore procedures for Oracle, but they tend to involve rman.


texts: What normal forms are these two relations in?

texts:
I have a set of relations {A, X, Y, Z}:

Given that the functional dependencies:  AX→Y, AX→Z, Y→A, Z→X, I would think that this relation would be in the normal form 1NF, because there is a partial dependency for the composite key, YZ; Y determines A and Z determines X. However, I ran across this example on a university website claiming the relationship was in 3NF. Am I wrong about this relation being in 1NF?

On a similar note, given the same relation but with functional dependencies:  A→XY, Y→AZ, would this be in 3NF or BCNF, I do not see any reason why this would not be in BCNF, but I have seen a similar example where there was a further decomposition, that was also BCNF. So is the decomposition wasteful because the original relation with given dependencies was already in BCNF?


texts:
I am trying to write an SQL script to create an insert statement for each row of table TEST which has field A (int) and field B varchar(30), in my MS SQL database.

I would like to save those insert statements into a txt file to be able to apply that data back to another database.

Any idea about how to do it?


texts: Comparing CLR Functions in Visual Studio SSDT

texts:
I'm comparing the CLR functions from a pair of SSDT projects to a database. The only difference shown in the comparison is on two properties: IsDeterministic and DataAccess

The compare shows that the project has a NULL value for these properties, while the database shows the properties equal to false and 0, respectively.

The actual C# code for the functions does assign values to these properties. After updating the database and re-running the compare, the differences still show up.

How do I make the database compare exclude these differences?

or

How do I make the SSDT projects recognize the properties in the C# code when comparing?

I use this tool to generate change scripts, and it makes the process very difficult when I have to exclude these objects everytime.

My solution contains two projects- one with the C# code for the CLR assembly (with 'Generate DLL turned off), and the other with the T-SQL objects, including the DLL for the functions.


texts: conversion of high_value to date datatype

texts:
I need to comparte the high_value which is of LONG  datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old.
The code is some what like this.

declare
       dt date;
   time_to_stay number:=1; --CONFIGURE AS PER THE REQUIREMENT
    begin
    for x in (select partition_name , high_value, partition_position
          from user_tab_partitions
          where table_name = 'DEMO')
loop
   execute immediate 'select '||x.high_value||' from dual' into dt;
     if to_date(dt) < sysdate-time_to_stay AND x.partition_position <>'1'     then
execute immediate 'ALTER TABLE DEMO DROP PARTITION '|| x.partition_name|| ' UPDATE GLOBAL INDEXES';

This query executes but does not deletes the partitions. However I see that main issue is converison of   LONG   datatype of high_value to DATE  to make the comparision between high_value and sys_date.

How can I fix this?


texts: How can I update statistics adding the data of the last day only?

texts:
I have been tuning some queries that had a warning on the sort operator, which is generally an expensive operator:

Operator used tempdb to spill data during execution with spill level 2

The way to improve the performance and get rid of the warning on the Sort Operator,was to update the statistics.

UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder]  WITH FULLSCAN
--1 hour  04 min 14 sec

But as you can see it took over an hour to update the stats.

Now I have a similar query with the same problem.

when the @toDate is today, or yesterday I get the same warning:
Operator used tempdb to spill data during execution with spill level 2

The query is below,
the plan is here.,
Picture below for a quick look.

    DECLARE @FromDate DATETIME = getdate()-1
    DECLARE @ToDate DATETIME = getdate()-0

    SELECT DISTINCT
             ap.strAccountCode
            ,em.strEmail
            ,em.dtmLastUpdated
    FROM    Bocss2.dbo.tblBAccountParticipant AS ap

            LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em
                   ON ap.lngParticipantID = em.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah
                    ON ap.lngParticipantID = ah.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBOrder AS o
                    ON ap.lngParticipantID = o.lngAccountParticipantID

    WHERE   o.sdtmOrdCreated >= @FromDate
      AND o.sdtmOrdCreated < @ToDate

OPTION (RECOMPILE)

But when @toDate is 2 days ago, I don't get any warning at all.

The query is below,
the plan is here.,
Picture below for a quick look.

    DECLARE @FromDate DATETIME = getdate()-3
    DECLARE @ToDate DATETIME = getdate()-2

    SELECT DISTINCT
             ap.strAccountCode
            ,em.strEmail
            ,em.dtmLastUpdated
    FROM    Bocss2.dbo.tblBAccountParticipant AS ap

            LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em
                   ON ap.lngParticipantID = em.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah
                    ON ap.lngParticipantID = ah.lngParticipantID

            INNER JOIN Bocss2.dbo.tblBOrder AS o
                    ON ap.lngParticipantID = o.lngAccountParticipantID

    WHERE   o.sdtmOrdCreated >= @FromDate
      AND o.sdtmOrdCreated < @ToDate

OPTION (RECOMPILE)

Updating the stats of the order table solves this problem, however, it takes over an hour to run.

UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder]  WITH FULLSCAN
--1 hour  04 min 14 sec

I have good reasons to update the stats manually.

I also found this link interesting:
Why does SQL Server refuse to update these statistics with anything but fullscan?

Question:

Instead of updating the stats with full scan, is there a way I can just update the stats for the last day?

that would be something I could run on a daily basis.

this table in particular get 50k new records every day on average.


texts: Disable MySQL binary logging with log_bin variable

texts:
Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled:

log_bin = /var/log/mysql/mysql-bin.log

When I want to disable binary logging on such installation, comment out the line in my.cnf works of course, but I wonder if there is a way to disable binary logging by setting explicitely log_bin to OFF, in a debian style, I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf, so default my.cnf is not changed and can be easily updated by apt, if necessary.

I tried "log_bin = 0", "log_bin = OFF" or "log_bin =" but none works...


texts: Pros and Cons of building a relationship model where all table ids are just "_id"?

texts:
I have always had the pk of the table as this pattern: lowercase tablename + Id.
That was what I was taught in college and didn't think about it. When I was challenged on it from someone using mongo db (a non-relational db) I couldn't figure out why I have ever done it this way?
The only thing I can think about is that some ORMs have it so that the auto build pk columns that normally match this pattern.
Any insight would be greatly appreciated.


texts: Cycle time calculation in TFS with a data warehouse query

texts:
Moved from here.

This is a simple code to extract cycle time per project team from Team Foundation Server data warehouse.

Here is the relationships of tables in data warehouse:

We use Sql Server 2014 with TFS 2018.

I'm not an SQL expert. Is this a good SQL code? If not, how could I improve this query to do less work?

Declare @from date;
Declare @initial_state nvarchar(256);
Declare @final_state nvarchar(256);

Set @from = DATEADD(year,-1,GETDATE());
Set @initial_state = 'Committed';
Set @final_state = 'Done';

With wi As (
    Select TeamProjectSK, System_Id, System_State, System_ChangedDate, System_WorkItemType
      From [Tfs_Warehouse].[dbo].[DimWorkItem]
      Where
            System_WorkItemType in ('Bug', 'Product Backlog Item')
            and System_ChangedDate >= @from
            and System_State in (@initial_state, @final_state)
)
Select P.ProjectPath, C.[Cycle Time]
    From DimTeamProject P
    inner join (
        Select D.TeamProjectSK, Avg(D.Duration) [Cycle Time]
            From (
                Select w1.TeamProjectSK, w1.System_Id, DateDiff(day, w1.ChangedDate, w2.ChangedDate) as Duration
                    From (
                        Select TeamProjectSK, System_Id, Min(System_ChangedDate) ChangedDate
                            From wi
                            Group By TeamProjectSK, System_Id, System_State
                            Having System_State=@initial_state
                    ) w1
                    inner join (
                        Select TeamProjectSK, System_Id, Max(System_ChangedDate) ChangedDate
                            From wi
                            Group By TeamProjectSK, System_Id, System_State
                            Having System_State=@final_state
                    ) w2
                    On w1.System_Id=w2.System_Id
        ) D
        Group By D.TeamProjectSK
    ) C
    On C.TeamProjectSK=P.ProjectNodeSK

You could find this code here.


texts: How to find description of error in error log?

texts:
We have errors like these in our MySQL error log:
[ERROR] [MY-010211] [Server] Got error 203 when reading table 'xxx'
But I can't find anything about either MY-010211 nor error 203 in the documentation.
Where can I find more information about this error, or other errors like this in general?


texts: Is polling the only way for updating app's data from a database?

texts:
An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the database?

I work with a MS SQL Server 2008 (and .NET applications + Entity Framework), but I'd like to get knowing about other types of databases as well.


texts: Why is MySQL Workbench is reporting different number of writes per second at the same time on the same database?

texts:
I am working on a project with MySQL Workbench on a server that I remote into with Windows remote desktop. Sometimes I will also connect to the server with MySQL Workbench on my local machine.

When I go to server status, the number of InnoDb Writes per Second is different depending on whether I am using MySQL Workbench on the remote server or on my local machine.

Here is a screen shot of what it looks like, the left status is on my local machine, the right is on the remote desktop.

It would be nice to be able see accurate live numbers of writes per second.


texts: I need to see the log backup schedule for a database. Where is this information located in SQL Server Management Studio?


texts: MSSQL - Should I change min server memory to 0

texts:
I Works as a DBA and "inherited" about 50 instances (all server has about 8GB-24GB of RAM), all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to OS.

Should I let SQL server to acquire and release by itself (Min Server
Memory = 0)?
Is it possible to get CPU overload by MSSQL Dynamic Memory
Manager?


texts: sp_Blitz says "Page Verification Not Optimal" on MDS database

texts:
I've run sp_blitz (version 45) on our test database server.
It complains about

  Database [MDS] has TORN_PAGE_DETECTION for page verification.  SQL Server may have a harder time recognizing and recovering from storage corruption.  Consider using CHECKSUM instead.

But this is a database from Microsoft.
Is it wise to change the Page Verification to CheckSum?

Or should we wait for Microsoft to release a new version of MDS?


texts: Latin1_General_BIN performance impact when changing the database default collation

texts: I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the database? The database already exists with tables in it.


texts: Would like to know why this query is behaving this way?

texts:
I have an emp table, with schema and some initial values like below:

 emp_id | name | last_name | role_id
--------+------+-----------+---------
      1 | hell | bell      |       1
      2 | well | tell      |       2
(2 rows)

And another role table, which emp table has referenced on role_id.
Below is the role table:

 role_id | role_name
---------+-----------
       1 | tech
       2 | op
       3 | dev
(3 rows)

I was just playing around, and wrote the below query, which I suppose don't make much sense.

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
INNER JOIN role
    ON emp.role_id = (
        SELECT role_id from role WHERE role_name = 'tech'
    );

It worked and returned this result:

 name | role_name | emp_id
------+-----------+--------
 hell | tech      |      1
 hell | op        |      1
 hell | dev       |      1
(3 rows)

I would like to know why I'got this result and how it worked?


texts: Why create an "oracle" user for installing Oracle 10g or 11g?

texts:
What is the specific reason for creating the oracle user to install Oracle 10g or 11g, even though root exists and can be used?

Is it possible to install as root by adding root to the oinstall group?

Every suitable technical reason is welcome.


texts: How do I check if PostGIS is installed in my PostgreSQL?

texts: I'm trying to check whether or not the PostGIS extension is installed, as a PG query. I'm unable to find this info out.


texts: Fillfactor is ignored when non-clustered index added

texts:
I have a test scenario where I have added fill-factor = 90 to a clustered index over a random generated GUID primary key.

If I create the table without fill-factor = 90 then the number of page splits is initially 31 for 3974 rows and splits occur with every subsequent insert. When I include the fill-factor = 90 the number of splits on population rises to 35 but splits stop happening with every subsequent insert. All good so far!

The problem is that when I add a non-clustered index although the clustered index still indicates a fill-factor of 90% the initial insert is back to 31 splits and splits are happening again on every subsequent insert indicating that the fill-factor is being ignored.

Does anyone have an idea as to why this might be happening?

    /*
    scenarios:
    1. Create table #Test02 without fillfactor, populate and observe 37 page splits occured, insert two rows at a time and observe page splits occurring
    2. Drop and create table #Test02 with filfactor = 90, populate and observe 40 page splits occurred, insert two rows at a time and observe that page splits do not occurr
    3. Drop and create table #Test02 with filfactor = 90, insert two rows then populate, observer 38 page splits occurred, insert two rows at a time and observe page splits occurring
    4. Drop and create table #Test02 with filfactor = 90, create index IX_#Test02_ProductName, populate and observe 37 & 35 page splits occurred, insert two rows at a time and observe that page splits occur on PK
    5. Rebuild index PK_Test02, insert two rows at a time and observe that page splits do not occurr

    Conclusion: On an empty table with a PK FF = 90 SQL preserves the fill factor
    */

    --non-sequential index table
    IF OBJECT_ID('tempdb.dbo.#Test02') IS NOT NULL DROP TABLE #Test02
    --CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID))
    CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID) WITH (FILLFACTOR = 90))

    --ALTER INDEX PK_Test02 ON #Test02 REBUILD WITH (FILLFACTOR = 90);

    IF EXISTS (SELECT * FROM tempdb.sys.indexes WHERE name = N'IX_#Test02_ProductName') DROP INDEX IX_#Test02_ProductName ON #Test02
    CREATE INDEX IX_#Test02_ProductName ON #Test02(ProductName)

    --populate script
    INSERT #Test02(ProductName)
    SELECT TOP 4000 COALESCE(O1.name,O2.name)
      FROM master.sys.objects O1
CROSS JOIN master.sys.objects O2

    --two row insert
    INSERT #Test02(ProductName) VALUES(N'Straight Banana'),(N'Bent Banana')

    --observe page splits
    SELECT ios.index_id
         , o.name as object_name
         , i.name as index_name
         , ios.leaf_allocation_count as page_split_for_index
         , ios.nonleaf_allocation_count page_allocation_caused_by_pagesplit
         , ios.leaf_insert_count
         , i.fill_factor
      FROM tempdb.sys.dm_db_index_operational_stats(db_id(N'db_name'), null, null, null) ios
      JOIN tempdb.sys.indexes i on ios.index_id = i.index_id AND ios.object_id = i.object_id
      JOIN tempdb.sys.objects o on ios.object_id = o.object_id
     WHERE o.type_desc = N'user_table'
       AND o.name like N'#test02%'


texts: memory used by a query result set in Oracle 11g

texts:
There is something not very clear in my mind and that I wanted to know when performing queries that retrieve large result sets:

Is a result set entirely loaded into memory and send to the client or only a part of it is loaded, send and then the memory is reused to load the next part until all data has been processed to fulfill the query ?

I suppose that for some queries that require a sort operation or use an analytical function it has to be fully loaded but what for basic queries that would only involve one table ? Like for instance:

select * from mytable where id between 1 and 1000000;

Will the one million records be loaded entirely into the memory allocated for my query or only a part of it ?

Thanks for your help,


texts:
I want to accept all db connections from servers with domain mydomain.com so that all subdomains like tiger.mydomain.com, venus.mydomain.com will have access to the postgresql server.

I've read that you have to create a reverse dns server so that it can lookup the domain and get the appropriate ip address. but it is not clear how to do this or a postgresql specific case.


texts: How to prevent UPDATE on any record in a table, but allow INSERT and DELETE (PostgreSQL)?

texts: How do I prevent UPDATEs on any record in a give table, but allow everything else? Must I use a trigger? I am using PostgreSQL.


texts: SQL Server does not exist or access denied" after installing SSMS

texts:
I recently installed SQL Server Management Studio (which also installed SQL Server 2016 to my local machine), and now am unable to connect to SQL Server from applications on my machine. It will come up with the error message below:

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

At this point, should I just uninstall SQL Server Management Studio/SQL Server, or is there an easy way to resolve this issue without needing to do so?

Here is the code I am attempting to execute which worked before installing SQL Server and SSMS:

string connectionstring = ConfigurationManager.ConnectionStrings["B2BConnString"].ToString();

OleDbConnection conn = new OleDbConnection("Provider=SQLOLEDB;"+connectionstring);
conn.Open();

string strSQL = "select [aliasname], [apikey], [password], [sharedsecret], [url], [isTest], [lastid] from ShopifyWSSetup as s Inner Join Clients as c on s.cno = c.cno where isactive = 1 and aliasname = '"+aliasname+"';";
OleDbCommand comm = new OleDbCommand(strSQL, conn);

OleDbDataReader reader = comm.ExecuteReader();
if (reader.HasRows)
{
    while (reader.Read())
    {
        apikey = reader.GetString(1);
        password = reader.GetString(2);
        sharedsecret = reader.GetString(3);
        url = reader.GetString(4);
        isTest = reader.GetBoolean(5);
        if (!reader.IsDBNull(6))
        {
            lastid = reader.GetString(6);
        }
    }
}

conn.Close();
conn.Dispose();
reader.Close();

Here is the connection string being used:

<connectionStrings>
    <add
        name="ExampleConnString"
        connectionString="Data Source=10.0.1.111;Initial Catalog=Example;User ID=Example;Password=examplePW;Connect Timeout=30"
        providerName="System.Data.SqlClient"/>
</connectionStrings>

Update:

I uninstalled SQL Server 2016, so only 2008 is on the machine now. The protocol is set to port 1433, enabled, keep alive 30000, keep alive internal 1000. Named pipes - default pipe: sql\query, enabled. Shared memory enabled.


texts: How to only update part of a column based on conditions?

texts:
This is a continuation of Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" , however i felt the question was too different to follow up in comments

So when i run the query:

update table1
set column1 = (SELECT distinct color
                FROM table2
                Where table1.id = table2.id
                and rownum = 1
              )

Everything where the conditions above does not apply returns null, however i would like to keep the value of the fields who do not get a new color

The create table:

CREATE TABLE table1
id varchar2 (20) [NULL]
color varchar2 (20) [NULL]

CREATE TABLE table2
id varchar2 (20) [NULL]
color varchar2 (20) [NULL]

where for example table 1 has 2 rows that are:

id: 123, color: blue
id: 124, color: red

and table 2 has 2 rows that are:

id: 124, color: green
id: 125, color: brown

i would then like to update table1 with table 2 so that table one has the 2 rows:

id: 123, color: blue
id: 124, color: green


texts: why the processing is sometimes terminated , sometimes not

texts:
DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
 (BusinessEntityID
 ,PersonID
 ,ContactTypeID)
 VALUES
 (0,0,1);
SELECT @Error = @@ERROR;
PRINT ‘The Value of @Error is ‘ + CONVERT(varchar, @Error);

I got :

  Msg 547, Level 16, State 0, Line 4 The INSERT statement conflicted
  with the FOREIGN KEY constraint
  “FK_BusinessEntityContact_Person_PersonID”....The statement has been
  terminated. The Value of @Error is 547

that means the process was still going on after the error was thrown.

But if I try create a table(run the below script twice to trigger the error) as:

CREATE TABLE OurIFTest(
 Col1 int PRIMARY KEY
 );
IF @@ERROR != 0
 PRINT 'Problems!';
ELSE
 PRINT 'Everything went OK!';

then I got

  Msg 2714, Level 16, State 6, Line 2 There is already an object named
  'OurIFTest' in the database.

which means the process was terminated when the error was thrown.

So why the process terminated for 'Create' but not for 'Insert'?


texts: Management Data Warehouse SQL Server data collection reports

texts:
I configured MDW on an SQL Server instance but when I try to access the reports from the MDW DB, the reports show information until a couple of days ago and I am getting 'collection set is not running. The data displayed here is from a previous run' as per below:

Why does this happen?


texts: How to store short stories for access to individual sentences?

texts:
I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the fact that the user will access text bodies in two ways:

1) access the entire body of the story on click of the story name.

2) the user can input a word or phrase into a search bar, which will return all sentences (not the whole stories) in which the word/phrase is found (meaning that it could potentially return many sentences from many stories).

There will be a great ("infinite") number of stories, and about 40 sentences per story, although it is free text so some stories will contain a few hundred sentences.

My initial DB design was to have a Story model (I'm using Ruby on Rails) with a story_id, story_title, and author_id_fk, and then to have a Storyline model with storyline_id, storyline, and story_id_fk.

However, I'm now doubting myself and think that maybe the best way to do it is to slap the body of the story onto a 4th column in the Story model called story_text, where I will store an array of strings (aka, the original text parsed into its corresponding sentences), and then the Storyline model can either simply not exist (in which case the appropriate item from the array would be called when needed - less normal, but also perhaps more efficient..?), or to keep the Storyline model, but have it contain a reference to the appropriate storyline as opposed to the actual text itself.

Any thoughts or suggestions would be much appreciated!


texts:
We have a query like this:

SELECT
 PROJECT.PROJECTID, REQUEST.REQUESTID,
 SAMPLE.SAMPLEID,SAMPLE.OTHERSAMPLEID,
 ILLUMINASEQPROTOCOL2.SAMPLEID
FROM
 ILLUMINASEQPROTOCOL2,
 PROJECT,
 SAMPLE,
 REQUEST,
 ANCESTORS A1,
 ANCESTORS A2,
 ANCESTORS A3
WHERE (
 ILLUMINASEQPROTOCOL2.RECORDID = A1.RECORDID AND
 A1.ANCESTORID = SAMPLE.RECORDID AND
 SAMPLE.RECORDID = A2.RECORDID AND
 A2.ANCESTORID = REQUEST.RECORDID AND
 REQUEST.RECORDID = A3.RECORDID AND
 A3.ANCESTORID = PROJECT.RECORDID)
AND  (
 (((ILLUMINASEQPROTOCOL2.RecordId > 0) AND
  ((SAMPLE.EXEMPLARSAMPLETYPE LIKE 'DNA') AND
  (REQUEST.RecordId > 0)))
 AND  (PROJECT.RecordId > 0))
)
ORDER BY SAMPLE.RECORDID LIMIT 0,501;

The RecordId column on all the tables is indexed as is AncestorId.  No other columns are indexed.  Here is what makes no sense.  When i have the query as above, it takes 130 seconds to run on our machines here.  The ORDER BY is what takes all the time since if I remove it the query runs instantly.  But what is weirder is if I change to the ORDER BY to use a non-indexed column like SAMPLE.SAMPLEID, it also comes back instantly.

So when I run this wanting to ORDER BY an indexed field it is MUCH slower than ordering by a non-indexed field.  Any help greatly appreciated.


texts: CHECK constraint not implemented in MySQL, what about other DBMS?

texts:
MariaDB, which is based on MySQL, does not.
Oracle, including Oracle XE 10g and 11g do.

I'm curious as to other DBMS?


texts: MySQL with Glusterfs Storage vs MySQL replication with hostPath storage

texts:
I am trying to search right road in a project.

We have currently three nodes openshift cluster(okd 3.11) and deployed MySQL with Glusterfs storage. its working fine but look like over engineered to me.

I am thinking to replace above with MySQL Replication with hostpath storage so 1 node be master and other node will act as slave. this way no need for extra steps to deploy glusterfs storage.

We may not extend this cluster it will be three and if we may have to deploy two node OKD cluster MySql replications will be ideal choice as Glusterfs dont recommend to use less then 3 nodes.

Main goal is HA so graceful failover can be achieved. Any thoughts?


texts:
I'm trying to Install latest version of PostgreSQL and keep running into the below problem which states -

  Problem running post-install step. Installation may not be complete
  correctly Error running sc start "PostgreSQL 10 Server":Program ended
  with an error exit code

Other important information

Using windows 10
I'm not the administrator / user
Installing version 10 "PostgreSQL-10.3-1-win64-bigsql" file from PostgreSQL website
Running 64 bit code
Double clicked and ran the installer file
Symantec enterprise antivirus is installed

bituninstaller information is below:

  Windows Specific Configuration [23:04:29] Windows Actions (services)
  Starting service "PostgreSQL 10 Server" Executing sc start "PostgreSQL
  10 Server" Script exit code: 1060

Script output:

  [SC] StartService: OpenService FAILED 1060: The specified service does not exist as an installed service.

  Script stderr: Program ended with an error exit code

  Error running sc start "PostgreSQL 10 Server": Program ended with an error exit code

  Problem running post-install step. Installation may not complete correctly

  Error running sc start "PostgreSQL 10 Server": Program ended with an error exit code

  [23:16:32] Running pgAdmin post installation actions

  Installing pgAdmin3 ... Executing C:\PostgreSQL_re/pgc install pgadmin3

  Script exit code: 0


texts: Modeling a three-way association with optional relation

texts:
Business rules

I have three tables (Parties, Categories and Products) which representing the following relationships:

A product is classified by zero-one-to-many categories
A category classifies zero-one-or-many products

Then, I have the party relationships:

A product is classified by one-to-one party
A party classifies one-to-many products

In other words, a product doesn't have to be assigned a category, but they have to be assigned a party respectively.

The party_id for a Category must match the party_id for a Product in order to relate.

EDIT

Following is a correction to the business rules above, based on @damir-sudarevic's solution proposal:

Category is defined by a party.

Each category is defined by exactly one party.
Each party may define more than one category.

Product is classified by a party.

Each product is classified by exactly one party.
Each party may classify more than one product.

Product is classified in a category by a party.

Each product may be classified in more than one category.
More than one product may be classified in the same category.
A product is classified by a party in a category, then that category is defined by that party.

Design proposals

I have based my first design on the proposal found here, but it's not entirely applicable since I want to enforce party_id for both Products and Categories respectively and in the relation.

I have made a second proposal that simplifies the design somewhat, but I'm not sure how to enforce the party_id to the Product-Category relation.

SQL based on latest design

Based on the comments and solution proposals, I have added a simplified SQL to create the tables and their relations.

CREATE TABLE IF NOT EXISTS parties (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id));

CREATE TABLE IF NOT EXISTS categories (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name_key VARCHAR(255) NOT NULL,
  party_id INT(10) UNSIGNED NOT NULL,
  parent_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id, party_id),
  INDEX fk_categories_parent_category_idx (parent_id ASC),
  UNIQUE INDEX name_key_UNIQUE (name_key ASC, party_id ASC),
  INDEX fk_categories_party_idx (party_id ASC),
  CONSTRAINT fk_categories_parent_category
    FOREIGN KEY (parent_id)
    REFERENCES categories (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_categories_party
    FOREIGN KEY (party_id)
    REFERENCES parties (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS products (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  party_id INT(10) UNSIGNED NOT NULL,
  product_code VARCHAR(50) NOT NULL,
  PRIMARY KEY (id, party_id),
  UNIQUE INDEX product_code_UNIQUE (product_code ASC, party_id ASC),
  INDEX fk_products_party_idx (party_id ASC),
  CONSTRAINT fk_products_party
    FOREIGN KEY (party_id)
    REFERENCES parties (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS product_category (
  product_id INT(10) UNSIGNED NOT NULL,
  category_id INT(10) UNSIGNED NOT NULL,
  party_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, category_id),
  INDEX fk_product_category_product_idx (product_id ASC, party_id ASC),
  INDEX fk_product_category_category_idx (category_id ASC, party_id ASC),
  CONSTRAINT fk_product_category_product
    FOREIGN KEY (product_id , party_id)
    REFERENCES products (id , party_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_product_category_category
    FOREIGN KEY (category_id , party_id)
    REFERENCES categories (id , party_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

Question

How can I setup the three-way association table correctly to avoid the risk of having an application layer assigning a product to a category without enforcing the party_id?


texts: What is an accepted method of keeping old data in MySQL?

texts:
I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here.

Some background:  I am running an application whereby users can take tests using data from two tables:  questions and answers.  Each response from every test is recorded in a test_data table.  Now, over time, I will have questions that were either put in wrong, or are no longer relavant.  How would I go about keeping that data somewhere in the database so that I maintain referential integrity?

I seem to remember using an application in the past that had an "archive" table with a few columns:

id
type (describes what data is archived in this row)
data (serialized PHP array)
etc...

This could be considered a subjective question, yes.  But what I'm looking for here are some possibilities that I can use to build an archiving system with in MySQL.

Thanks in advance :)


texts: Dimensional Model for inheritance in dimensions

texts:
How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension?

For example, assume I have an Employee dimension and a Driver dimension, where the Driver has all the attributes of an Employee but also has a Truck Number attribute.  How should these dimensions be modelled?


texts: Rebuild a mysql slave adding innodb_file_per_table without locking the master

texts:
I would like to rebuild a mysql slave, adding innodb_file_per_table to the configuration in order to reduce the disk space used (idbdata1 has grown a lot). I want to do it without locking the master's tables and I sketched up a procedure that seems to work, but I would like to know if it is really accurate. I tested this on two vms.

Here are the configurations for the test:

On the master:

server-id               = 10
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test
#binlog_ignore_db       = include_database_name

On the slave:

server-id               = 20
log_bin                 = /var/log/mysql/mysql-bin.log
relay_log               = /var/log/mysql/mysql-relay-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test

Before starting the procedure:

On the slave:

Seconds_Behind_Master: 0

At this point, this is the procedure I tried to rebuild the slave with the new configuration and then let him resume the replication where it stopped

on the slave: stop slave
on the master: insert into t1 values (4,1) # I insert values in tables t1 in database test to validate that the slave is able to recover the replication data that occured during the process
on the slave: mysqldump -u root -p --opt test > test_slave.sql
on the master: insert into t1 values (5,1)
on the slave: drop database test
on the master: insert into t1 values (6,1)
on the slave:

change configuration to set innodb_file_per_table:

innodb_file_per_table
innodb_flush_method=O_DIRECT

on the master: insert into t1 values (7,1)
on the slave:

store bin log replication position from the last stop slave:

mysql -u root -p -e "show slave status \G;" > status

on the slave: service mysql restart
on the slave: stop slave
on the master: insert into t1 values (8,1)
on the slave:

create database && import dump:

mysql -u root -p -e "create database test;"
mysql -u root -p -b test < test_slave.sql

on the master: insert into t1 values (9,1)
on the slave:

reconfigure the replication to start from the last stop slave position (with the master_log_pos and master_log_file stored in the status file):

change master to MASTER_HOST='db01',MASTER_USER='myuser',MASTER_PASSWORD='mypasswd',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=477;

on the slave: start slave

After the 16th step, all inserts made on the master during the process appear in test.t1 on the slave and the replication is working.

               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 10

It appears to be successful. However, if someone sees a pitfall in this procedure (that I can't see on this limited lab environment) it would be great to share :)

Thank you,


texts: Most efficient way to identify records with any number of key columns

texts:
I work with large third party data-sets.  Long experience has taught me that it is a really good idea to give incoming lines of data a surrogate ID as soon as it enters my system so I can track it easily as it's validated, warehoused, etc.  The problem is the key values can potentially be every dimensional value, which could be 200 columns.

My general process is this:

load data to a staging table.
match the data with a second IdMatch table that holds only the key values and surrogate IDS.

IF OBJECT_ID('Staging.myTest') IS NULL
    CREATE TABLE Staging.myTest (
        [ID] INT IDENTITY(1,1) NOT NULL,
        [Hash] INT NULL,
        [Dim_1] NVARCHAR(32) NOT NULL,
        [Dim_2] NVARCHAR(32) NOT NULL,
        [Dim_3] NVARCHAR(32) NULL,
        [Met_1] INT NULL,
        [Met_2] DECIMAL(5,2) NULL
    );

IF OBJECT_ID('IdMatch.myTest') IS NULL
    CREATE TABLE IdMatch.myTest (
        [ID] INT IDENTITY(1,1) NOT NULL,
        [Hash] INT NULL,
        [Dim_1] NVARCHAR(32) NOT NULL,
        [Dim_2] NVARCHAR(32) NOT NULL,
        [Dim_3] NVARCHAR(32) NULL,
    );

TRUNCATE TABLE Staging.myTest;
TRUNCATE TABLE IdMatch.myTest;

INSERT INTO Staging.myTest
    ([Dim_1], [Dim_2], [Dim_3])
VALUES ('A', 'A', 'A'),
    ('B', 'B', 'B'),
    ('C', 'C', NULL),
    ('C', 'C', 'C'),
    ('D', 'D', 'D');

INSERT INTO IdMatch.myTest
    ([Dim_1], [Dim_2], [Dim_3])
VALUES ('A', 'A', 'A');

--My Proc (as script) for setting the index.
INSERT INTO [IdMatch].myTest
    ([Dim_1], [Dim_2], [Dim_3])
SELECT src.[Dim_1], src.[Dim_2], src.[Dim_3]
FROM Staging.myTest AS src
WHERE NOT EXISTS (
        SELECT tgt.[Dim_1], tgt.[Dim_2], tgt.[Dim_3]
        FROM [IdMatch].myTest AS tgt
        WHERE tgt.[Dim_1] = src.[Dim_1]
            AND tgt.[Dim_2] = src.[Dim_2]
            AND tgt.[Dim_3] = src.[Dim_3]
    );

SELECT * FROM IdMatch.myTest

THE PROBLEM:
Matching to get a surrogate ID this way takes a long time when the real data-set contains 200+ columns of NVARCHAR data.  Is there a better way?  I've tried pre-computing hashes, but am not sure how to handle the eventual collisions I'll produce.


texts:
I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10.
I'm using CentOS 7.X

Using pg_upgrade it should be quite straightforward, but the problem is PostGIS.
Postgres 9.3 has PostGIS 2.1 available, whereas Postgres 10 has PostGis 2.4.4.
So I compiled PostGIS 2.4 for Postgres 9.3 and installed it.
Then for each database, I've made:

ALTER EXTENSION postgis UPDATE;

In this way, I've migrated to PostGIS 2.4.

But when I run pg_upgrade, I receive this error:

  Cannot access the file «$libdir/postgis-2.1»: It doesn't exist the file or the directory.

If I do this:

cd /usr/pgsql-10/lib/
ln -s postgis-2.4.so postgis-2.1.so

It doesn't work either, because if I run pg_ugrade another time:

pg_restore: creating FUNCTION "public.geomfromewkb("bytea")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 547; 1255 90291 FUNCTION geomfromewkb("bytea") ema
pg_restore: [archiver (db)] could not execute query: ERROR:  could not find function "LWGEOMFromWKB" in file "/usr/pgsql-10/lib/postgis-2.1.so"<br>
    Command was: CREATE FUNCTION "public"."geomfromewkb"("bytea") RETURNS "public"."geometry"
     LANGUAGE "c" IMMUTABLE STRICT
     AS '$libdir/postgis-2.1', 'LWGEOMFromWKB';

Why does Postgres 9.3 reference PostGIS 2.1 after migrating to 2.4?

The other option I see is to compile PosGIS 2.1 for Postgres 10.

UPDATE 1

After reading this tip, I've done for each database I've done:

psql mydb -c 'ALTER EXTENSION postgis UPDATE;'
psql mydb -c 'ALTER EXTENSION postgis_topology UPDATE;'
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.1/uninstall_legacy.sql
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.4/legacy.sql

with identical results.

SELECT probin, COUNT(*)
FROM pg_catalog.pg_proc
WHERE probin LIKE '%/postgis%'
GROUP BY probin
ORDER BY probin;

returns:

       probin        | count
---------------------+-------
 $libdir/postgis-2.1 |   373

UPDATE 2

This guide explains the internals of PostGIS migration.

Clearly, PostGIS tells it migrates the extension, but doesn't apply the migration script to change the procedures.


texts: Insert missing records from another table and update timestamp?

texts:
I made an app for the sales team at my company, they visit the stores that our product is sold at and scan all the retail boxes that are physically there. Now I'm building the reporting side of things, and I'm a bit stuck. I'm running SQL Server 2008.

I've got a "Stores" table which lists all of the stores we sell to as well as each product that SHOULD be there, for example:

+---------+-------------+---------+
|  Store  |   Address   | Product |
+---------+-------------+---------+
| Walmart | 123 Fake St |  702640 |
| Walmart | 123 Fake St |  704651 |
| Walmart | 123 Fake St |  709999 |
| Target  | 456 Main St |  789562 |
| Target  | 456 Main St |  777898 |
| Target  | 456 Main St |  723456 |
+---------+-------------+---------+

I've also got a "Scanned" table which lists all of the products that our sales team has scanned with their phone while being at that physical location:

+-------------+---------+-------------+---------+
| Timestamp   |  Store  |   Address   | Product |
+-------------+---------+-------------+---------+
| 2016-10-06  | Walmart | 123 Fake St |  702640 |
| 2016-10-06  | Walmart | 123 Fake St |  704651 |
| 2016-10-06  | Target  | 456 Main St |  789562 |
+-------------+---------+-------------+---------+

The problem I'm running in to is showing what products were missing on any one visit, for example on 2016-10-06 at Target only 1 product was scanned, 789562, the other 2 from the "Stores" table (777898, 723456) are missing.

I've tried doing a right join:

SELECT * FROM Scanned RIGHT OUTER JOIN Stores on Store, Address, Product

The problem with this is for any missing records in the Scanned table that exist in the Stores table it's returning a NULL for the Timestamp column, so I get something like this:

+------------+---------+-------------+---------+---------+-------------+---------+
| Timestamp  |  Store  |   Address   | Product |  Store  |   Address   | Product |
+------------+---------+-------------+---------+---------+-------------+---------+
| 2016-10-06 | Walmart | 123 fake St | 702640  | Walmart | 123 Fake St |  702640 |
| 2016-10-06 | Walmart | 123 Fake St | 709999  | Walmart | 123 Fake St |  704651 |
| NULL       | NULL    | NULL        | NULL    | Walmart | 123 Fake St |  709999 |
| 2016-10-06 | Target  | 456 Main St | 789562  | Target  | 456 Main St |  789562 |
| NULL       | NULL    | NULL        | NULL    | Target  | 456 Main St |  777898 |
| NULL       | NULL    | NULL        | NULL    | Target  | 456 Main St |  723456 |
+------------+---------+-------------+---------+---------+-------------+---------+

I need the Timestamp column to be able to do reporting on this data.

I'm not sure if there is a better way to go about this, I'm open to anything, I've tried looking into updating the table so the NULL timestamp will match the timestamp of the other entries (we don't visit a store more then once a day) but I didn't get very far.


texts:
Need help designing in my database tables.

I have a set of table which are being populated on every execution, like wise this executions are user specific if user executes an execution data at that point of time data is being populated in table set.

I have 3 approaches to define data for my executions, but i am a bit confused which one to use. It would be great if someone throw some light or some better approach.

--APPROACH 1
CREATE TABLE #USERS(
    fname nvarchar(100),
    mname nvarchar(100),
    lname nvarchar(100),
    ExecutionID int
)

In approach 1 data gets repeated for each execution, with its executionID.

--APPROACH 2
CREATE TABLE #USERS_2(
    fname nvarchar(100),
    mname nvarchar(100),
    lname nvarchar(100),
    FirstExecutionID int,
    LastExecutionID int
)

In approach 2 data gets populated with first and last execution id, if same data gets repeated in consecutive execution, I won’t create new record instead update LastexecutionID to latest one. This is similar to SCD Type – 2.

--APPROACH 3

CREATE TABLE #USERS_3(
    fname nvarchar(100),
    mname nvarchar(100),
    lname nvarchar(100),
    mappingID int
)

CREATE TABLE #USERS_Mapping_3(
    ExecutionID int,
    mappingID int
)

Approach 3 is similar to first approach but I will be storing execution ID with its mapping ID in different tables.

Any other alternative for saving this sought of execution specific data?


texts:
I logged in my server via SSH and committed a "repair table xxx" query on a MyISAM table with 34 million rows and 69GB of data. Unfortunately the SSH connection was lost. When I logged back in I found the mysql -u xxx -p session was gone by 'ps a'. The disk mount is now fully occupied and is not released. So I suppose a repair again would correct all this?

I want to use cron job to run this remotely rather than from SSH which can possibly be disconnected again. After finding this page:

http://dev.mysql.com/doc/refman/5.0/en/rebuilding-tables.html

I see it's possible to run repair table via command line like this:

mysqlcheck --repair --databases db_name ...

But how can I just --repair a specific table in the database db_name? Any other tips regarding how I can correct this mess would be much appreciated!


texts:
I know that this is very dangerous:

EXEC sp_executesql 'SELECT * FROM ' + @Table

But, is this safe from SQL injection?

IF EXISTS(SELECT * FROM information_schema.tables WHERE TABLE_NAME = @Table) BEGIN
    EXEC sp_executesql 'SELECT * FROM ' + @Table
END


texts: MS-SQL: unique constraint case-insensitive

texts:
How do I add a case-insensitive constraint without altering the column collation?

I've tried

CONSTRAINT [AK_User_Username_UserType] UNIQUE (LOWER([Username]) [UserType])

and

CREATE UNIQUE INDEX [AK_User_Username_UserType] UNIQUE (LOWER([Username]) [UserType])

But it won't compile...

Do I need to add a computed column to achieve this??


texts: How to create `instead of` trigger on a table in postgres?

texts:
I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if the room number of the new event will be busy for the time of the new event. If so then I want to raise an exception else insert the new event into the table. I thought to declare the trigger as instead of trigger for insert but postgres doesn't allow instead of triggers on insert operation on tables (and I don't want to create a view just for the trigger).

How can I work around this problem? Theoretically I could create an after trigger and then check if the newly inserted data is valid and delete if it's not. But this seems like a wrong approach conceptually and maybe practically as well (I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data).

This is the table definition:

create table room_schedule(
  start_date date,
  start_time time,
  room_no int,
  event_id int,
  duration interval,
  primary key(start_date, start_time, room_no, event_id)
);

This is my trigger and function definitions:

create or replace function inserttrigfunc() returns trigger as $$
declare count int;

begin

with end_time_table(eid, stime, etime) as (
        select event_id, start_time, (start_time + duration) as etime from room_schedule
        where room_no=new.room_no
    ),
        overlapping_time_table(eid, stime, etime) as(
            select * from end_time_table
            where (stime <= new.start_time and new.start_time <= etime) or
            (stime <= (new.start_time + new.duration) and (new.start_time + new.duration) <= etime)
        )
        select count(*) from overlapping_time_table into count;

        if count > 0
        then
            begin
                raise exception 'the room is already occupied at the time';
            end;
        else
            begin
                insert into room_schedule(start_date, start_time, room_no, event_id, duration)
                    values(new.start_date, new.start_time, new.room_no, new.event_id, new.duration);
            end;
end;
$$language plpgsql;

create trigger TRIG1 instead of insert
  on room_schedule
  for each row
  execute procedure inserttrigfunc();


texts: mysql : Is it possible to ignore some tables in cold backup?

texts:
We take every day the backup of a folder from production to uat with rsync.
That folder, among others, contains the physical files of a mysql database.
Is it possible to exclude some tables during the backup process explicitly with rsync (not mysqldump)?
If yes, which table files should I exclude in the mysql directory?
Thank you!


texts: phpmyadmin: know the import progress of sql file

texts:
Im importing large size of sql file in PHPMyAdmin and it takes to long to import. is there any way to know its progress?.

like some kind of loading bar or percent of its progress?.

and i'm importing using the browser not the mysqldump command line.


texts:
During my SQL Server 2014 Enterprise installation efforts, I encountered an error near the end of the install that has prevented completion.

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.).

There is currently no cert on the server so it looks as though SQL Server has generated a self signed cert to connect with, but isn't being trusted.

Note that all this is happening before the install is complete, so configuration manager isn't available yet. My thought is to turn encryption off, but with no config manager, I am not sure where there may be a config file for the install somewhere that I can edit. Or maybe I would need to put a trusted cert on the server, but without configuration manager fully available, there isn't a way to tell SQL Server to use this the trust cert.


texts: How to configure account to be able to create/delete folders and files?

texts:
I have a newly created domain account (domainname\user1).
This will be used by SQL server agent job proxy credential to run a SSIS package that creates/deletes files and folders on the same computer as the agent job. So effectively the package will run as though it is the user domainname\user1.
What is the permission that I need to assign to domainname\user1 on the computer? For example: I don't want to assign it to the Local administrator group. Should I add it to the Users group? Or any other group?


texts:
I'm considering applying delayed durability in a system I work on and want to make sure I have the correct mental model in order to avoid data loss.  My understanding is it basically means COMMIT turns into a sort-of "Schrodinger's cat" between COMMIT and ROLLBACK, right?  In other words, some transactions that are committed might actually end up rolled back ("the commit is a lie").
Hence, we should not actually expect to see any data loss in scenarios like the following:

A service broker activation procedure that RECEIVEs messages, processes them inside a transaction, and COMMITs.  If "the commit is a lie" and the transaction doesn't actually commit, it will be a rollback instead and service broker will automatically rerun it.
A periodic ETL-type job which bulk imports files into a staging table (this part would be fully durable).  Afterwards, inside a transaction, it processes the staged data and marks the staged data as processed.  If "the commit is a lie" and the transaction doesn't actually commit, it will be a rollback instead and the staged data will not be marked as processed, so we'll do it again next time.

In these scenarios we would not expect actual data loss because the transactions get rerun automatically.  In this case it's no more risky than anything else that could interrupt a transaction (e.g. a deadlock or an unplanned shutdown that interrupts the process).
In contrast, a scenario like the following would not be a good candidate for delayed durability and would risk data loss:

An external process (e.g. SSIS), for each file in a directory, begins a transaction, bulk imports the data from the file, and commits the transaction.  Once the transaction is committed, it deletes the file.  Now, if "the commit is a lie" and the transaction doesn't actually commit, we will have lost the file data for good because we deleted the file but the import was rolled back.

So in sum, delayed durability can lose data only if some external party is relying on the COMMIT.  For example, an online checkout enters a sale and reasonably assumes that when the data has been committed, the sale is finished, etc., and informs the user as such.  But if it's some processing that can already automatically tolerate and resume from interruptions/rollbacks, there should be no increased risk.
Does that sound right?


texts:
When I import data from DB2 using db2move, the dump file comes with the data exported as "USERX.MYTABLE".

The import process seems to be fine, I see lots of:

USERX.MYTABLE n rows imported ...

But USERX is not on my database and when I try to select data using MYUSER, I always get empty results.

Also, if I run db2move and export the data from my local database, it agains export all rows for USERX.

How can I fix the access to MYUSER?


texts: Does changing "sa" password require a SQL restart (in mixed mode)?

texts:
We discovered a SQL "sa" account being used in a way it should not have been, so we are changing sa passwords on all our SQL instances.

(We have SQL 2005 through 2017 servers running in mixed authentication mode. All users and applications should be using either domain accounts or non-sa SQL accounts to connect. I have been monitoring, but have not found any other apps, users, or non-internal spids using the sa account.)

A few questions:

Q1: Does changing the sa password require a SQL restart?

I found a few references that say a SQL service restart is required after changing the sa account password:

DBA SE: Changing sa password
SQLAuthority: Change Password of SA Login Using Management Studio

Is that true? Or only if I'm changing the authentication mode? Or only if I routinely log on as sa?

This SQL Server Central thread even suggests changing it could impact existing SQL agent jobs and other stuff; is that a concern? Or only if someone has hard-coded the SA account into an SSIS package or something?

(In case it matters, we use domain accounts for the SQL service and SQL agent service, and domain proxy accounts for jobs that call SSIS packages or PowerShell scripts.)

Q2: Can I change the sa password the "normal" way?

Can I reset it like I would any other account? Using SSMS, or more likely via:

ALTER LOGIN sa WITH PASSWORD = 'newpass';

Or would I have to enter single-user mode or something that would require planned downtime? (Note that I'd be running this from a domain account, not while connected as "sa".)

Q3: Should we try to do this password rotation on a regular basis? Or only when we find an issue?

Is this a recommended "best practice"?


texts: Moving two databases from MySQL 5.1 [Prod] to MySQL 5.6 [Stage], not working [Windows]

texts:
I'm in the process of upgrading our production MySQL from 5.1 to 5.6, on a Windows Server 2008 R2 server. What I'm trying to achieve is to move 2 databases [say abc and xyz] from Prod server to a newly created MySQL 5.6 stage environment.

At first, I tried -

C:\>mysqldump -uroot -p abc > abc.sql [from the 5.1 prod server]
C:\>mysql -uroot -p abc < abc.sql [on the 5.6 stage server]

Here, I got the error -

  ERROR 1049 <42000>: Unknown database 'abc'

When I opened the abc.sql file, I didn't find the two lines -

CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'abc' /*!40100 DEFAULT CHARACTER SET utf8 */;
USE 'abc';

I thought to export the db by specifying --database option, so I tried -

C:\>mysqldump -uroot -p --database abc > abc.sql [from the 5.1 prod server]
C:\>mysql -uroot -p abc < abc.sql [on the 5.6 stage server]

Now, the CREATE DATABASE and Use <dbname> are there, but still I'm getting the error -

  ERROR 1049 <42000>: Unknown database 'abc'

Anyone has any idea what I am doing wrong? Same is the case with the database 'xyz'.


texts: What's causing my postgresql database to freeze?

texts:
This is my current setup on my home laptop:

OSX El Capitan.
Installed postgresql 9.6 via postgres.app.
Symlinked the database files to an external HD (connected via USB).
Inserting data via the psycopg2 python module in a jupyter notebook.

I have a long list of files to insert to various tables.  I've made sure to turn off logging and there are no indexes assigned to any of the tables.  There's plenty of space on the external HD.

Eventually the upload would hang at some particular file.  I would be able to select from other tables in psql except for the table that file was being uploaded to.  Examined tables pg_prepared_xacts and pg_locks and nothing's there.  Attempting to drop the corrupted table would just sit there.
 However, I wouldn't be able to do a clean shutdown of the server on postgres.app due to the error pg_ctl: server does not shut down, even though trying to access via the psql shows that the server's shut off.  I would try to restart the laptop, and it'd just sort of freeze with all the applications closed.  After doing a hard reboot, postgres.app would complain about not being able to start the server due to some fatal error.

At a complete loss as to what's going on.  Any clues?

Edit: I've tried killing the active COPY process, which reads from STDIN to no avail via:

SELECT pg_cancel_backend(__pid__);
SELECT pg_terminate_backend(__pid__);
kill __pid__

Console.app not showing anything in logs.  When I force a shutdown via postgres.app this is what shows up in the logs:

LOG:  received fast shutdown request
LOG:  aborting any active transactions
FATAL:  terminating autovacuum process due to administrator command
LOG:  autovacuum launcher shutting down
FATAL:  terminating connection due to administrator command
FATAL:  terminating autovacuum process due to administrator command
FATAL:  the database system is shutting down


texts: What are the downsides of using PARTITION for multi-tenant access?

texts:
We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores.  Let's say we have 1MB per store and 10k stores so we're talking about 10GB of data.

We're making some changes to how the system works in general and rethinking some of the pieces.  One possibility that came up is to create partitions for each store so they will be completely isolated.  It seems ideal for our use case, but we don't have experience with this many partitions so I don't know if there are gotchas.

A few notes, the queries are always done on a per-store level - for this data set we don't need querying across stores.  Most of the stores will be active most of the time - so this isn't like historical data where old partitions won't be written to.  There isn't another dimension like time that would make sense to partition on.

Is there anything we should be concern about with a partition/schema per store or any areas that might be a problem?

UPDATE:
I found some more references to this:

https://stackoverflow.com/questions/44524364/postgresqls-schemas-for-multi-tenant-applications
https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/
https://www.quora.com/What-are-the-pros-and-cons-of-using-schemas-to-support-multi-tenancy-in-PostgreSQL-How-scalable-is-it

Another (perhaps important) detail I forgot to add is that the individual terminals at each store have their own copy of the database (and sometimes multiple terminals/databases per location) so that can continue to run completely offline for extended periods of time.  It's an asynchronous master-master data-level replication - writes can happen via an app to the cloud or on the terminals and we handle the syncing and conflict resolution.

The thought to migrate to a schema per store relates more to simplicity in the replication and backup logic to treat each store as an independent entity.  It also could make it easier to scale because we can more easily move stores to different cloud databases.  It also might help us with migrations if we could make schema changes per store as the upgrade to new versions (not sure if/how this would work with PG partitions.) It's not related to security concerns.


texts: SQL Merge Replication Issue - problem where backup and maintenance job which is running till morning and most of the server jobs are stuck

texts:
We have a merge replication environment which includes
Publisher: Server1 where only one database of 125 GB in replication and also Distributor is also configured on same server hosted in a HyperV environment.
Subscribers: We have approx 280 servers with Distributor(push subscription) configuration.
NOTE: we have a environment with lots of DML changes in day so we perform many checks on a daily basis on every server through maintenance and backup jobs which includes step1 : check integrity for complete database step2: Rebuild/Reorganise Index step3: Backup/Verify step4: Old backup jobs clean up step5: Old job history clean up
Also this job completes in 4-5 hrs on a daily basis but it varies sometimes on server to server basis.
Snapshot Agent runs at daily at 00:05 AM
PROBLEM:

Since sunday night we are having this problem where backup and
maintenance job which is running till morning and most of the server
jobs are stuck on step1 dbcc checkdb ('my database') (and Clients are
also complaining that application is crashing , can't login , very
slow)

Upon checking, my backup spid is waiting on OLEDB wait type and we do not have configured linked server connections on Publisher and other subscribers except few which are our head office servers from where we do our major imports / small updates / if we need to push out any database changes.
While checking the below query i can see the percentage is moving for my dbcc spid id but doesn't really tell me why it is stuck on this step?
select session_id
, percent_complete
from sys.dm_exec_requests
where percent_complete > 0

And it doesn't really matter which time I run this job or single step i.e. dbcc checkdb, it did not complete.
Also Check with our Windows team there was no update rolled out on weekend and no changes have been made on application side.
Any suggestions of what could be the problem ?


texts: Is the following database design good in the following table for the situation explained below

texts:
Someone has created this table with the following information:

There are more entries in the database table which I have omitted for brevity purpose.

My ultimate goal is to display the following in the User Interface:

As you can see, the DESC column has same value twice for Capital,Summary etc, same value 4 times for First Reports. I am planning to generate a JSON response using the webservice which would look like the following:

{
    "webservice_status": {
        "status": "SUCCESS",
        "message": ""
    },
    "dataInfo": [{
            "desc": "Capital,Summary etc",
            "datadesc": "Counts of capital and summary ",
            "dataid": "1"

        },
        {
            "desc": "Capital,Summary etc",
            "datadesc": "Testing of capital and summary ",
            "dataid": "2"

        },
        {
            "desc": "First Reports",
            "datadesc": "Completed Successfully ",
            "dataid": "3"
        },
        {
            "desc": "First Reports",
            "datadesc": " Still Pending ",
            "dataid": "4"
        },
        {
            "desc": "First Reports",
            "datadesc": "Failed twice ",
            "dataid": "5"
        },
        {
            "desc": "First Reports",
            "datadesc": "Re attempting ... ",
            "dataid": "5"
        }
    ]
}

I am wondering if I should ask the person who created this to change the database design? As far as I know, having duplicate values in the same column is a violation of some normal form?

Also, while programming, I am not sure how would I know that I need to stop at DATAID=2 to know that my first heading will be Capital,Summary etc and then again, I am not sure how would I know to stop at 6 to know that my second heading is First Reports.


texts: SQL Methods Function to add "Gold Medal" for anniversary

texts:
I'm trying to create a method function on SQL using Oracle Developer.

The member function will give employees a gold medal after 12 years of service to the bank, a silver medal after 6 years of service and a bronze medal after 2.

The issue I am having is with the pre-defined date_type that I have in my employee_type which is as follows

CREATE type employee_Type
AS object(
    name name_type,
    supervisor REF EMPLOYEE_TYPE,
    position VARCHAR2(20),
    salary   NUMBER(5),
    ninum    VARCHAR2(8),
    joindate DATE);

And below is the Method I'm trying to implement

    ALTER type employee_type ADD member FUNCTION give_medals
  RETURN string CASCADE;
CREATE OR REPLACE TYPE BODY employee_type
AS
  MEMBER FUNCTION give_medals
  RETURN STRING
IS
BEGIN
  IF self.join_date > 12 THEN
    RETURN 'Gold Medal';
  ELSIF self.join_date = 6 THEN
    RETURN 'Silver Medal';
  ELSIF self.join_date = 2 THEN
    RETURN 'Bronze Medal!';
  END IF;
END give_medals;
END;

But again my issues is lies with the ELSIF statement, as you can see I'm returning a Gold Medal for anything over 12 - but because the date_type is like 01-JAN-98 everything in the join_date value will be given a gold medal.

So how could I change the function to give Medals for years of service instead of just anything > 12


texts: What is the rationale of dropping all indexes for an altered view in SQL Server?

texts:
This is a follow up of ALTER VIEW drops Index from View question.

The accepted response indicates that ALTER VIEW will automatically drop any defined indexes.

This is somewhat counter intuitive to how other similar operations are happening:

it is natural to expect for related things such as permissions or indexes to be dropped on object drop not on alter
altering a table does not drop any index (actually you have to drop things like indexes, constraints etc.)

I am wondering about why having this behavior by default in the first place (i.e. is there any objective aspect like performance reduction in some cases due to view alteration).

Question: What is the rationale of dropping all indexes for an altered view?


texts: Why isn't the following query using the `always` index?

texts:
This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query:

SELECT * FROM movies, showtimes
WHERE site='5' AND
    (showtimes.movie = movies.id OR always='true') AND
    ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')\
GROUP BY movies.id ORDER BY listorder

I'm not sure who wrote this. Looks like it's on a database I designed 5+ years ago, which unknown people have been tinkering with quite a bit since I last touched it. The tables in question are:

CREATE TABLE `movies` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `rating` varchar(5) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `special` varchar(255) NOT NULL default '',
  `url` varchar(64) NOT NULL default '',
  `poster` varchar(255) NOT NULL default '',
  `runtime` varchar(20) NOT NULL default '',
  `listorder` tinyint(4) NOT NULL default '0',
  `comsoon` enum('false','true') NOT NULL default 'false',
  `always` enum('false','true') NOT NULL default 'false',
  `site` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `rating` (`rating`),
  KEY `site` (`site`),
  KEY `comsoon` (`comsoon`),
  KEY `always` (`always`),
  KEY `listorder` (`listorder`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `showtimes` (
  `movie` int(11) NOT NULL default '0',
  `showdate` date NOT NULL default '0000-00-00',
  `showtime` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`movie`,`showdate`,`showtime`),
  KEY `showdate` (`showdate`),
  KEY `showtime` (`showtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When I first was alerted to this situation, movies.always did not even have an index, nor did movies.listorder. I was sure that was the problem, however, adding an index on those two columns did not change the EXPLAIN:

mysql> EXPLAIN SELECT * FROM movies, showtimes WHERE site='5' AND (showtimes.movie = movies.id OR always='true') AND ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')  GROUP BY movies.id ORDER BY listorder;
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys       | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | showtimes | index | PRIMARY,showdate    | PRIMARY | 10      | NULL | 93411 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | movies    | ALL   | PRIMARY,site,always | NULL    | NULL    | NULL |    25 | Using where                                  |
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

I tried rewriting the query as follows:

SELECT * FROM movies, showtimes
WHERE (site='5' AND always='true') OR
  (site='5' AND showtimes.movie = movies.id AND showdate>='2012-4-11' AND showdate<'2012-04-18')
GROUP BY movies.id ORDER BY listorder

However the EXPLAIN was no different. WHy is this query not using an index on the movies table? How can I explain to the developers that they should rewrite this query to make it not bring the server to it's knees?


texts: Can't deploy SSAS Tabular Model in Visual Studio

texts:
I am following the SSAS Tabular Model Tutorial by Microsoft using the Adventure Works DW. I've imported the DW in SQL Server Management Studio to my local server. I've connected to the DW from Visual Studio, everything worked fine, completed all the steps of the tutorial until deployment. Building the Model works just fine, however when I try to deploy the Model it throws me the error

  Error Cannot deploy metadata. Reason: An error occurred while connecting to the server. 0

Some other users have also reported the same issue and could solve it with reinstalling SSDT, tried that, did not work for me.

Any ideas would be very much appreciated!


texts: JOIN to read first table and get possible relationship in the second table

texts:
Consider a main table as

CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB

and a second table for relationship as

CREATE TABLE group_members
(
relation_id int(11) NOT NULL AUTO_INCREMENT,
group_id int(11),
user_id int(11),
FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
PRIMARY KEY(relationsh_id)
) ENGINE=InnoDB

How to get a full list of all groups with an additional column indicating that if a given user is member of each group or not. This means that we will have a WHERE clause indicating the corresponding user_id, and the additional column will show 0 or 1, depending that this user_id has a connection to each group or not.