texts:
I've got newly installed Win 2008 R2 machines, in a workgroup, in the same subnet, and of course SQL Server 2012.

I allowed login with SQL Server authentication, thinking it may help with each workstation having a different user account database. I created identical logins for each of three machines. Two have the full SQL Server software and one has SQL Server Express. Then I tried to mirror two different databases, producing the same result.

The first, I just right clicked on database in the SQL Server Management Studio, made a new database, then confirmed the recovery model was set to full. Backed it up and the transaction log. Restored the two items to my other server. (To start I'm not using the Witness yet in hopes to keep it as simple as possible) When restoring the data and log, for both I am restoring it without recovery. Then when I try to mirror, I get one warning about Fully qualified domain names, click through it because my servers are not in a domain, and then when it tries to mirror and comes back with:

  An error occurred while starting mirroring.

  Additional Information:
  Alter failed for database 'test'. (Microsoft.SqlServer.Smo)
  An exception occured while executing a Transact-SQL statement or batch.
  (Microsoft.SqlServer.ConnectionInfo)
  The server network address "TCP://(SERVERNAME):5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, ErrorL 1418)

I have tried it with the AdventureWorks database too with the same result.

In Event Viewer I am seeing "Database Mirroring connection error 4. 'An error occurred while receiving data.' '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.

In the SQL Server log I am seeing

  Error 1443. Severity 16. State 2
  Database mirroring has been terminated for database 'test'. This is informational only.  No user action is required.

and

  Error 1474. Severity 16.  State 1.
  Database Mirroring connection error 4. 'An error occurred while receiving data.'  '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.

I have been doing searches on google trying to get my head around this, but I think because I am new to all this, I am having a hard time understanding where to go from here.


texts:
I create a database in the terminal linux ubuntu,gave it all privileges,created the table,inserted the rows.When I open phpmyadmin the database isn't showing how do I connect them?
I did use mysql -u root -p to open mysql.


texts:
I have a setup of two databases, master - slave.
Slave was stooped for about two weeks and old binlogs binlogs purged from master but copied on another fs before purging.

Slave can't catch up the master because index don't contain anymore the names of the purged binlogs.

I've already copied old binlogs to the correct place but now I need to edit the .index file on master.

Is there a tool that can be used to tell mysql too add binary logs back to index files (somethinf like unpurge :) or add binary log) or can index file be edited without shutting down mysql ?

Thanks a lot.


texts: Postgres full text search with unaccent and inflection (conjugation, etc.)

texts:
I want to be able to search unaccented phrases in an inflected (Polish) language in Postgres.

Say, if a document contains robiłem, the lexeme should be robić (the infinivite). Its forms are robię, robił, robiła and so on. I want to be able to find it, for example, with a phrase robie which is unaccented robię.

What I did is I started out with a perfectly well working polish text search config

CREATE TEXT SEARCH DICTIONARY polish_ispell (
TEMPLATE = pg_catalog.ispell,
dictfile = 'polish', afffile = 'polish', stopwords = 'polish' );

Then I tried to extend it to include the unaccent.

create extension unaccent;
create text search configuration polish_unaccented (copy = polish);
ALTER TEXT SEARCH CONFIGURATION polish_unaccented   ALTER MAPPING FOR hword,
hword_part, word WITH unaccen, polish_ispell, simple, ;

Sadly, lexems are not created correctly with this config:

select to_tsvector('polish_unaccented' ,'robił');

'robil':1

The lexem should be of course:

'robić':1

So the below cant return true (and that's what I need I think):

select to_tsvector('polish_unaccented','robić') @@ to_tsquery('polish_unaccented','robie');

I've googled but did not find any documents showing how to really configure Postgres for my case. The docs only show the lame 'Hôtels' example, which is not a 'lexemed' word.

Cheers


texts: Oracle - Tracking events that lead to a deadlock

texts:
Executing the below query from http://www.orafaq.com/node/854

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;

I found that "SID=52 is blocking SID=730" and "SID=730 is blocking SID=52", a clear case of a deadlock. However all I could find from the v$session, v$sqlarea views is the SQL that is currently being blocked by both sessions. How do I see which SQL executed in both sessions that caused the locks.

We are using Oracle 11g (11.2.0.4) and recently noticing deadlock issues everywhere.


texts:
I've been reading up on application roles in Microsoft SQL server. It seems an application role is like any other role in that permissions on securables can be granted to an application role, however, the difference being that it has to be "called" by an application using the stored procedure sp_setapprole.

The only reason I can see for using this is for a DBA to allow an application (and its developers) to connect to the database and not having to supply a server login or a database user and therefore the developers cannot connect directly to SQL Server (via SSMS for example) even then, I can't really see a benefit to this as surely the account provided to the application / developers would be granted the minimum required permissions anyway?

Am I missing something here?


texts: I need to find the "rank" in a sub-set of entries

texts:
I am currently working on some sort of a highscore list. But values displayed are individual for each user, ie. limited by constraints.

One constraint that I am currently working on is "being part of a list".

I have to tables in my database:

Connections

| Origin | Destination |
234        333
234        453

Values:

| ID | Users.ID | Points
1      1          30
2      2          40
3      1          60
4      4          100

Users:

|ID | Name    | InternalID |
1     damian    234
2     damian2   333
3     damian3   453
4     damian4   555

My goal is the following:

I need to find my rank, but only within the set of connected "values".

I am damian, my ID is 1 and my internal ID is 234. My "connected users" have internal IDs: 333, 453 which map to User IDs 2, 3.

This means, the list of users to be monitored is 1,2,3 but NOT 4 (since not connected to my internal ID).

With this list (1,2,3) I will now check the values, ordered by Points DESC, and find my position in the subset.

My current approach is:

Get the list of Users.ID:

Query 1:

    SELECT DISTINCT ID
    FROM users
    WHERE InternalID IN (
                    SELECT Destination
                    FROM connections, users
                    WHERE Origin = users.InternalID AND users.ID = 1)

This list, I will now map to the values:

Query 2:

 SELECT Points
 FROM values
 WHERE UsersID IN (Query 1)
 ORDER BY Points DESC

This should result in a sorted list of all values (Points), only of connected users.

But now I need to find the highest / first position in this set, of my own "Points" value, and I don't know how to do this efficiently. I have tried different approaches with FIND_IN_SET like FIND_IN_SET(Points, GROUP_CONCAT(Points)) but I can't seem to get them to work, also this   would result in 4-5 nested queries.


texts:
Can some one help me how to find out Original report of a Linked report in SSRS.

We tried by editing the permissions on SSRS console and report permissions but no luck.

Thanks,
Venkat Kokulla


texts: Unable to use cyrillic characters in mysql docker console

texts:
I'm running a MySQL docker container (latest - 8.0) with the custom configuration file containing:

======================================
[client]

default-character-set=utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character_set_server = utf8mb4

[mysql]
default-character-set=utf8mb4
======================================

Executing inside MySQL console:

show variables like "%coll%";

produces this:

+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_unicode_ci |
| collation_server              | utf8mb4_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

Executing inside MySQL console:

show variables like "%char%";

produces this:

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

BUT when I go the MySQL console with:

docker exec -it mysql1 mysql -uroot -p*********

I can't use cyrillic (russian) characters. When I switch to Russian locale and try to type anything, the console just ignores keypresses, nothing is displayed on the screen. But when I switch to English locale, everything starts working again.

What can I do to solve this problem?

PS: I AM able to interact with the database via DataGrip and enter Russian characters for table names, fields and records. I'm just completely unable to use Russian characters when I'm inside the MySQL console inside the docker container.

PPS: MySQL is running from an official Docker image on MacOS Mojave and I'm using iTerm 3.2.6. Character encoding in iTerm is set to "Unicode (UTF-8)"


texts:
I have this query that does not return any results:

SELECT
    MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha') AS Score
    , e.*
FROM
    employees AS e
WHERE
    MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha' IN BOOLEAN MODE)
ORDER BY `Score` DESC;

firstname, lastname and tc_identity are of type varchar.

I have data in firstname like 'Bahadir', 'Bahadır', etc...

When I try to match against 'Baha', I get no results.

When I try to match against 'Bahadir' or 'Bahadır', I get no results.

What's the problem?


texts:
Currently i'm using Oracle 10g XE database and want to upgrade to Oracle 11g XE. When  i try to run the installer, it fails because of running instance. I searched the internet and got to know i have to uninstall 10g XE before installing 11g. The problem is i want to retrieve my schemas in 11g. So as stated here, i tried,

exp system/<system_password>@<database> file=<user>.dmp owner=<user>

but i got the following error

SP2-0734: unknown command beginning "exp system..." - rest of line ignored.

i read on the internet i tried using

SQL> expdp system/password full=Y directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

but got

SP2-0734: unknown command beginning "expdp syst..." - rest of line ignored.

is there a way (preferably GUI) to just back up my data before uninstall so that it can be recovered after upgrade? I know complete databses can be put into an sql script for mysql using phpmyadmin. Does anything similar exist for oracle as well??

Is recreating a table using netbeans a good solution?


texts: MS SQL Express - Find when a Query was run

texts:
I have an ecommerce website in .NET and using MS SQL Server Express, Version info:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)   Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation  Express Edition (64-bit) on
Windows NT 6.3  (Build 9600: ) (Hypervisor)

There is a DELETE query running from my code and I can not figure out what is triggering it. (My entire system shows one place that has that Delete code, but that can not be the problem.)
I need to find out from the database when the Delete is running so I can try to use that Data to help me solve my problem.
Even if I can't find going backwards, I would like to install/configure something to help me find it in the future, as it is a recurring problem


texts: Restoring the database dump of a newer version to an older version of mongodb

texts:
I find myself having to move a database between hosts but at the moment don't have the option of upgrading or downgrading any of the hosts.

I'm trying to find out if it is safe to do the following:

I have a running mongodb 3.0.15 instance which I've taken a dump from.
I've restored that to another mongo instance running version 3.4.4 without any issues.

Can I now take a dump of the data in 3.4.4 and restore it on 3.0.15? There has been no material change to the db besides the usual create/update & deletion of records while running on the surrogate host.


texts: SQL Cannot patch the latest CU4 for SQL 2014 SP3

texts:
I cannot patch the latest CU4 for SQL 2014 SP3. Checked "view installed program", 4500181 is already installed.

but when I use "Select @@version" against my SQL instance, it still shows "12.0.6259.0" which is SP3-CU3. Has anyone encountered this before and how to fix it?

Thanks,


texts:
If I right click on the properties of a replication article, I can get the transaction retention information.  This is useful ONLY if I have very few replication articles.  What table/view allows me to access this information, so that I can quickly do this without the GUI?

Thanks!

It's distribution.dbo.MSpublications.


texts: How can I find databases with a large number of virtual log files?

texts:
I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis.

Almost all these databases are mirrored using SQL Server Mirroring.

Even though we have carefully planned the physical log file sizes to match expected log file activity; occasionally something goes wrong and logs need to grow beyond our predicted maximum.  We have setup all log files to grow by 8192MB, however when the database is under pressure to grow the log file it sometimes will only grow the log in very small chunks, thereby creating in some cases hundreds of thousands of virtual log files (VLFs).

I came to understand the importance of keeping the number of VLFs low when one of our production databases unexpectedly went into recovery with over 200,000 VLFs.  Recovery took 20+ hours; during which time a portion of our business could not operate.

I need a solution that can monitor the number of virtual log files for all databases present on a server, sending an alert email if any particular log file has more than a given number of VLFs.

I know DBCC LOGINFO; returns the list of VLFs, however, I don't want to manually run this.

I created the following SQL statement that creates a nice table listing the databases, along with the number of VLFs however, I do not know how I can put this into a SQL Agent job to email our team whenever any database has over "x" number of VLFs.

DECLARE @cmd_per_database_prefix nvarchar(max);
DECLARE @cmd_per_database nvarchar(max);
DECLARE @database_name nvarchar(255);
SET @cmd_per_database = '';
SET @cmd_per_database_prefix =
'
    SET NOCOUNT ON;
    DECLARE @vlf_count_table TABLE (database_name nvarchar(255), vlf_count int);
    DECLARE @params nvarchar(max);
    DECLARE @db_name nvarchar(255);
    DECLARE @vlf_count int;
    SET @params = ''@db_name nvarchar(255) OUTPUT, @vlf_count int OUTPUT'';
    DECLARE @cmdGetVLFCount nvarchar(max);
    SET @cmdGetVLFCount =
    ''
        DECLARE @tab TABLE
        (
            FileId int
            , FileSize nvarchar(255)
            , StartOffset nvarchar(255)
            , FSeqNo nvarchar(255)
            , Status int
            , Parity int
            , CreateLSN nvarchar(255)
        );
        DECLARE @cmd nvarchar(max);
        SET @cmd = ''''DBCC LOGINFO;'''';
        INSERT INTO @tab
        EXEC sp_executesql @cmd;
        SET @db_name = db_name();
        SET @vlf_count = (select count(*) FROM @tab t);
    '';
';
DECLARE cur CURSOR FOR
SELECT NAME
FROM sys.databases
WHERE database_id > 4 and state=0;
OPEN cur;
FETCH NEXT FROM cur INTO @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd_per_database = @cmd_per_database +
    '
        EXEC ' + @database_name + '.sys.sp_executesql @cmdGetVLFCount, @params, @db_name OUTPUT, @vlf_count OUTPUT;
        INSERT INTO @vlf_count_table (database_name, vlf_count) VALUES (@db_name, @vlf_count);
    ';
    FETCH NEXT FROM cur INTO @database_name;
END
SET @cmd_per_database = @cmd_per_database_prefix + @cmd_per_database + char(13) + char(10) + 'select * from @vlf_count_table t;';
EXEC sp_executesql @cmd_per_database;
CLOSE cur;
DEALLOCATE cur;

Attempting to output this using INSERT...EXEC into a table for attaching to an email using sp_send_dbmail is proving futile.

SQL Server chokes with:

An INSERT EXEC statement cannot be nested.
Msg 8164, Level 16, State 1, Line 5


texts: Red Gate SQL Prompt in Visual Studio not working?

texts:
I have tried out SSDT Database project in Visual Studio, and with the SQL Prompt solution from Red Gate. It seems that the intellisense only works for SQL keywords like SELECT, WHERE etc, it does not provide intellisense for table names, column names etc.

Is there a trick to get this working? (Tried in both VS2015 and VS2017).


texts: Mongoose is not showing the ISODate in the result

texts:
I am using mongodb version 4.2.2.
I have following data:
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa9"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 885
},

/* 2 createdAt:2020-07-13T12:48:08.000Z*/
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa8"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 870
},

/* 3 createdAt:2020-07-13T12:48:08.000Z*/
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa7"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 855
},

/* 4 createdAt:2020-07-13T12:48:08.000Z*/
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa6"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 840
}

I am using mongoose in my project. When I run this query in node js:
const { memberId, date } = req.params;
let isoDate = date.toISOString();
let timeSlots = await TimeSlots.find(
{
  staffMember: memberId,
  date: isoDate
});

The result printed on the console:
db.spaTimeSlots.find({"staffMember" : ObjectId("5f0c58076b559419d62efe47"), date: new Date("Wed, 11 Jul 2020 00:00:00 GMT")})
   .projection({})
   .sort({_id:-1})
   .limit(100)

However, I want this result:
db.spaTimeSlots.find({"staffMember" : ObjectId("5f0c58076b559419d62efe47"), date: ISODate("2021-07-11T00:00:00.000Z")})
   .projection({})
   .sort({_id:-1})
   .limit(100)

I want the result returned with the date in ISODate format, not as text (i.e. not Wed, 11 Jul).


texts: Looks like a postgres backup/dump structure (A sort of a forensic/data archeology question)

texts:
I was given a compressed folder that contains number of subdirectories, and, among others, directories named .pgdir with number of numerically sequenced files in a <9999>.dat.gz format and one in a toc.dat format.

I cannot post the content of the files, but when I uncompress the <9999>.dat.gz, they each contain pretty structured, tab-separated tabular data with \N [sic] at the end line.
toc.dat has a mix of binary and strings, starting with PGDMP where I can recognize some postgres DDL statements, schema creation. etc.

To me, this looks like a postgres backup, but I am not familiar with this layout and how to restore it.

Is anyone familiar with these structures, and how could they be used to restore the database?


texts: Postgresql role_table_grants grantee differences between users

texts:
I have two users [UserA, UserB] -- UserA is an existing user. I just created UserB and gave it GRANT ALL PRIVILEGES.

But when I try to see privileges by doing

select * from information_schema.role_table_grants

UserA sees a lot more rows than UserB in the public schema.

For example

UserA can see all the rows with grantee in "admin_group", but UserB can't.
UserA and UserB can see all the rows with grantee in "user_group".

This is confusing because I did not do anything special to "user_group" (that I know of). What additional permission do I have to give to UserB so it can see all rows in information_schema.role_table_grants.


texts:
In general, I understand that normalization is usually beneficial even with the join costs. However, I came up with an interesting dilemma recently.

What if the data is duplicated but unlikely to ever change. It's possible, but I would not anticipate it.

I have a nutrients table with a unit column and the units would be g, kg, ug, etc.

I can't see these values every changing.

I'm tempted to just put them as a column in the table rather than normalizing and having a units table and using a foreign key and having to join whenever fetching a row from the nutrients table. At the same time, I know in general, even with the join coins, we should normalize.

What should I do (and why)?


texts: create a visible uniquifier column for a nonunique clustered index

texts:
I have a table of events, clustered index is the creation date/time.

Normally there should never be two events happening in the same millisecond, but freak occurences happen, so the column is not neccessarily unique.

I want to add an uniquifier column, that is 0 for the first inserted row with a given time, 1 for the second, and so on:

TIME                    UNIQUIFIER,     TEXT
2018-01-01 01:23:45.678,   0,           "aaa"
2018-01-01 02:00:00.000,   0,           "bbb"
2018-01-01 02:00:00.000,   1,           "ccc"
2018-01-01 02:00:00.000,   2,           "ddd"
2018-01-01 03:45:67.890,   0,           "eee"

I know MS Sql Server creates such a column automatically, when I create the clustered index. However the column is hidden.

I would like a visible uniquifier, that behaves like this hidden one, so I can use it in foreign key relationships.

I.e. so that when ...

another table needs to reference an existing event,
and I only know the time when that event happened,
and I only care about the first event that happened at the same time,

... I can use Time:2018-01-01 02:00:00 / Uniquifier:0 as the foreign key, knowing this row exists, without me looking up the event in the first table.

I could use an autoincremented identity column instead to make the key unique, but this means I cannot reference an event without knowing its identity column.

For example if I want to create the event AND a bunch of additional event info in a separate 1-n table, I would have to first create the event, then look up its identity column, and only then create the related information, using that identity column. I am trying to avoid that roundtrip.

Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?

Or do I have to manually calculate such a column when I insert rows?


texts: archive command repeatly failing for one particular file

texts:
I am seeing many pg_log files full of following errors

2019-07-08 00:04:06 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST LOG:  archive command failed with exit code 1
2019-07-08 00:04:07 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST WARNING:  transaction log file "000000010000002100000025" could not be archived: too many failures
2019-07-08 00:05:07 CEST LOG:  archive command failed with exit code 1**

postgres.conf having settings

checkpoint_segments = 256
wal_buffers = 256MB
wal_level = archive
archive_mode = on
port = 7112
work_mem = 128MB
shared_buffers = 512MB
effective_cache_size = 32719MB
listen_addresses = '*'
logging_collector = on
log_line_prefix = '%t '
archive_command = 'copy "%p" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/%f"'**

I have checked pg_xlog folder for this file. This file is not present in pg_xlog but in pg_xlog\archive_status with following permissions

pg/pg_xlog\archive_status\000000010000002100000025.ready

KDLF\adminkdlf:(I)(F)
VORDEFINIERT\Administratoren:(I)(F)
NT-AUTORITŽT\SYSTEM:(I)(F)
VORDEFINIERT\Benutzer:(I)(RX)

My disk is getting full. Please tell why this issue is coming because of this others logs are still not archived. I am not sure if I am missing something here.


texts: Trying to do a fact and dim table in Sql

texts:
so far this is what i have code wise, but im getting an error 1239 . Is it not referencing something correctly .

CREATE TABLE CustomerDim
(
   C_CustomerID_Cu                INT(10) NOT NULL,
   C_FacilityReservationNumber_Cu INT(10) NOT NULL,
   I_EventCode_Cu                 INT(10) NOT NULL,

   CONSTRAINT C_CustomerID_Cu FOREIGN KEY (C_CustomerID_Cu)
   REFERENCES CustomerDIM
);

CREATE TABLE RoomDim
(
   C_RoomNumber_Ro  INT(10) NOT NULL,
   C_RoomType_Ro    INT(5)  NOT NULL,
   C_RegionID_Re    INT(4)  NOT NULL,
   C__ResortCode_Fa INT(10) NOT NULL,

   CONSTRAINTC_RoomNumber_Ro FOREIGN KEY(C_RoomType_Ro)
   refereREFERENCES nces RoomDIM
);

CREATE TABLE ReservationFact
(
   C_RoomReservationNumber_Rr     INT (10) NOT NULL,
   C_CustomerID_Cu                INT (10) NOT NULL,
   C__RoomType_Ro                 INT (10) NOT NULL,
   C_D_ArrivalDate_Rr             DATE,
   C_EmployeeNumber_Em            INT (5) NOT NULL
   C_ResortCode_Rs                INT (10),

   CONSTRAINT C_RoomReservationNumber_Rr_pk PRIMARY KEY
   (C_RoomReservationNumber_Rr)
);


texts: Why is PostgreSQL blocking creation of a new table on update on foreign table?

texts:
I understand why an UPDATE query on tableA can block an ALTER TABLE DDL statement on the same tableA.
ex:
UPDATE tableA SET column1 = 'test' WHERE id = 1;`

But I am trying to create a new table, say tableB, that only references the id of tableA with ON DELETE CASCADE.
CREATE TABLE tableB id REFERENCES tableA(id) ON DELETE CASCADE NOT NULL`;

This query waits until the update is complete.
I was able to figure that out by getting the blocking pids through this query
SELECT pg_blocking_pids(<pid of the create table query>);

I got the pid of the update query as the blocking pid.
Why is creating a new table blocked by the UPDATE query of the referenced table?


texts: postgresql: How to disable all indexes of a table

texts:
There are more than 100 indexes on my table. i want to disable all the indexes temporary and want to re-enabled them once the indexing is done.

It is not feasible to go and type every index name to disable it. is there any PostgreSQL statement which can disable all the indexes temporary and then re-enabled them again.

Regards,


texts:
I'm new to sql language and I'm learning about subqueries..
I have one question is there other way how to write that query?

SELECT Employees.FirstName,
       Employees.LastName
FROM   Employees
WHERE  Employees.HireDate < (SELECT MIN(Employees.HireDate)
                             FROM   Employees
                             WHERE  Employees.City = 'London');

on northwind database schema

Thank you,
I really appreaciate your answer


texts: How to call the unix Sendmail from oracle

texts:
I know that is possible to send mails using the oracle-db by UTL_SMTP, and there are several examples of such usage everywhere.

My question is: Can I call the GNU/Unix/Linux Sendmail to perform a similar action?


texts: How do I find a long running query with process ID, process name , login time, user , start time and duration?

texts:
Can anyone help me to find a below given details for long running query.
ProcessID, process name, database, host ,user ,process login time, query start time and query duration.

I am looking for a query or an SP which gives me this data.


texts: How do I set up active-passive database clustering between data centers?

texts:
I am looking to setup an active/passive database cluster between data centers using mirroring and/or log-shipping across these two nodes. My challenge is understanding High Availability (HA) and getting the HA configuration correct.

Scenarios:

Hardware Failure.  I have no issues with this and understand that all traffic will be diverted to another data center or another node with in the same data center.
Data Center 1 Failure. Again, I have no issues here. All the traffic will be diverted to another data center.
Database Updates. I am facing an issue here.

During a patch upgrade on the Data Center 1 database, I want to divert all traffic to the Data Center 2 database.  After patching is complete, I want to revert it back to Data Center 1.  This may require breaking the mirroring/log-shipping configuration.  How would I then copy all the changes made on the Data Center 2 database back to Data Center 1?  Is there any feature I can use to get this incremental data while minimizing downtime?


texts: Does Azure SQL DW support partitioned views?

texts:
I've got a really expensive join in data warehouse that isn't distribution aligned. Unfortunately the join key is nullable, and only half the data has a value, meaning that it wouldn't be a good candidate for the distribution key.

Would be be possible to created a partitioned view where the null half of the table is round robin distributed and the other half is distributed on the available key values?


texts:
Is it possible to mimic Oracle SET AUTORECOVERY ON in SQL Server 2008 for applying transaction logs?

RESTORE LOG [DB_NAME] FROM  DISK requires backup name;  in case I have couple hundreds logs I must ensure logs are restored in proper order . Surely, I can sort files by name, but I wonder maybe there is out of the box solution.

Thank you.


texts: Replication slows down on massive deletes

texts:
First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue.

We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so server A is the source of data to servers B, C and D. There is one-way peer-to-peer replication between servers. Network is 1Gbps.

  __ A __
 /   |   \
 B   C    D

We have some denormalized table in our database with about ~700k rows. Table is about of 1Gb in size (and 5.5Gb of indexes) This table constantly updates - so all content in this table is renewed in ~2 hours (i.e. old rows are deleted and new are inserted). This is done in transactions of 5k records.
All works fine on server A, but replication of data from A to B, C and D slows down and delay could be about one hour between these servers.

Our DBAs say that it happens because of big delete transactions. They say that SQL Server executes delete operations on subscriber sequentially on each row, one by one. Is there a way to change this behavior if it does it so?

Actually, I'm not a big fan of replication, I would prefer to use some kind of messaging and publish-subscribe pattern to synchronize data between servers, but replication is the main way to transfer data in a company that I work for now. So it will be kinda hard to change these things.

Any thoughts how to improve performance of replication?


texts: What is the meaning of DOP in the context of SQL Server?

texts: What is the meaning of DOP in the context of sql server?


texts: Accidentally removed privileges from root user and can't access database

texts: New to databases and accidentally removed privileges from root user; only privileges I currently have are USAGE and PROXY. This is a MySQL database that I access through HeidiSQL. Currently cannot access my data tables; is there a way to get these privileges restored? Did not create any other user accounts.


texts: Oracle limit the number of files of audit

texts: I would like to know how to limit the number of files of audit generated in the audit directory, I have an Oracle 12c database. The AUDIT_TRAIL parameter is equal to NONE, however it continues to generate .aud files, I would like to know how to limit the number of files or if I can prevent them from being generated completely. Thank you


texts: Autogrow. Primary vs additional data files

texts:
My databases all use autogrow, which grows the primary MDF file on a percentage.  But one of the databases, from a third party application grows by adding additional .NDF files.

Where is this option set?  When I look at autogrow settings, there is the option to grow or not, by percentage or by xMB, and an option for limited or unlimit growth.  But I see nothing that tells it whether to grow the primary MDF, or grow by adding additional NDFs.

And, is there a way to combine these NDF files back into the primary MDF?

Thanks!

RZ


texts: What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database?

texts:
I'm trying to design a star-schema fact table along with some dimension tables that will surround it. If I reuse the natural key called customer_key in both the fact_table and dim_customer then I don't see a difference between calling something dim and a lookup table. Additionally if I every need to update the customer_name I will loose historical data that represented this fact at the time of the recording. What am I missing when trying to model the dim and fact tables?

I would like to understand difference between relational "lookup" table technique and data warehouse "dimension" table?

fact_table               dim_customer          dim_product
-------------            ------------          -----------
customer_key             customer_key          product_key
product_key              name                  name
units_sold               email                 description
unit_price

Please forgive any ignorance I might show in this question. I am a data warehouse newbie.


texts: MySQL NDB Installer won't start (webserver already running)

texts:
I have encountered an odd error where I accidentally closed the shell that was running the python script that launched the installer for NDB cluster.  The site stopped working, however, when I try to relaunch it, I'm told that the webserver is already running, but I can not connect to the installer.

How can I get the NDB installer functioning again?  I am running a set of Linux lightsail instances and the most recent GA release of community NDB server.


texts:
I have to create a single report in MSSSRS to view calculated columns (Salary, Total Compensation, Performance Bonus, 401 (k)) by Employee, Department, Service Line, Geography, Quarter, etc.

My modeler is going to give me  one and only one single flat TABLE (No FACTS and Dimensions) that he has developed in MS Access and now he is going to load into MS SQL SERVER 2008 R2.

Therefore, the question is how do I develop this report without having Dimensional Structure in place? Do I create a Multi-dimensional Views on a given single table or no matter what but he should create Star-Schema model?
What if he doesn't create a Star schema structure then what are my options to create a report that requires viewing calculated columns by Employee, by Department, by Service Line, by Geography, by Quarter?

Also, should I write multiple Stored Procedures or perform simple calculations in MSSSRS?


texts:
I am trying to import this SQL file in phpmyadmin and got this message

#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'
 Fatal error: Allowed memory size of 134217728 bytes exhausted (tr' at line 1

What do I do to do this import?


texts: How to constrain many-to-many to mark a single record as a "default"?

texts:
Suppose I have the following entities:

customer(id, name)

template(id, name)

With a junction table:

customer_template(id, customerId, templateId, is_default)

A customer may have any number of templates but only one is allowed to be the default.

How do I constrain the junction table so that there is one and only one default template assigned?


texts:
I have have been assigned to resolve Performance issue of a Stored Procedure.

Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3
     --- Section 1
    from TableOne
    inner join TableTwo on TableOne.Column4 = TableTwo.Column4
    inner join DatabaseTwo..TableThree on TableTwo.Column1 = DatabaseTwo..TableThree.Column5
    inner join TableFour on TableOne.Column6 = TableFour.Column6
    inner join TableFive on TableFour.Column7 = TableFive.Column7
    inner join TableSix on TableFour.Column8 = TableSix.Column8
    inner join TableSeven on TableSeven.Column7 = TableFive.Column7
    inner join TableEight on TableOne.Column9 = TableEight.Column9
    inner join TableNine on TableFour.Column10 = TableNine.Column10
    inner join TableTen on TableFive.Column11 = TableTen.Column11
    inner join TableEleven on TableFive.Column12 = TableEleven.Column12
    inner join TableTwelve on TableTwelve.Column7 = TableFive.Column7 and TableTwelve.Column9 = TableOne.Column9
    inner join TableThirteen on TableThirteen.Column13 = TableFour.Column13
    inner join TableSeventeen on TableSeventeen.Column14 = TableOne.Column14
    where TableFive.Column15 = 3

    --- Section 2
    and TableOne.Column6 in  (Select TableOne.Column6
                                from TableOne, TableTwo, TableFour
                                Where TableOne.Column4 = TableTwo.Column4
                                and TableOne.Column6 = TableFour.Column6
                                and TableFour.Column16 >= @StartDate
                                and TableFour.Column16< @EndDate )

    --- Section 3
    and TableFour.Column6 In (Select TableFour.Column6
                                from TableFour,TableEightteen,TableNineTeen
                                Where TableFour.Column7 = TableNineTeen.Column7
                                and TableEightteen.Column17 = TableNineTeen.Column17
                                and TableFour.Column10 = TableNineTeen.Column10
                                and TableEightteen.Column18 > TableFour.Column16
                                )

    --- Section 4
    and TableFour.Column7 Not In (SELECT Column7
                                FROM TableFour
                                GROUP BY Column7, Column10
                                HAVING ( COUNT(Column7) > 1 )
                                and Column7 = TableFour.Column7
                                and Column10 = TableFour.Column10
                                )

    --- Section 5
    and TableOne.Column6 in (Select TableOne.Column6
                                 from TableOne, TableTwo, TableFour , TableFive
                                 Where TableOne.Column4 = TableTwo.Column4
                                 and TableOne.Column6 = TableFour.Column6
                                 and TableFive.Column7 = TableFour.Column7
                                 and TableFive.Column12=@Column12
                                 )

    Group By TableTwo.Column1, DatabaseTwo..TableThree.[Column2]
    Order By  TableTwo.Column1, DatabaseTwo..TableThree.[Column2]

My Question is as per my understanding

Since Section2 has all tables that Section1 has with same join conditions therefore filtering condition and TableFour.Column16 >= @StartDate and TableFour.Column16< @EndDate of section2 should be transferred to Section1 and this will result in same query. AM I right?
Same scenario is with section6 which has all tables that Section1 but with one extra filter statement and TableFive.Column12=@Column12

and by removing section2 and section6 we can avoid extra conditions


texts: Why does my query return inconsistent amount of rows?

texts:
I have what seems like a basic query:

select
item
,prevailingmarketcost as cost
,SuggestedRetail as price
,SuggestedRetail-prevailingmarketcost as profit
from sprucedotnet.dbo.inventorystore
where primaryvendorcode = 'MILWAU184'

It consistently returns 484 rows. However adding a calculated column to the query causes me to get a varying number of runs with each query. How can this be?

select
item
,prevailingmarketcost as cost
,SuggestedRetail as price
,SuggestedRetail-prevailingmarketcost as profit
,(SuggestedRetail-prevailingmarketcost)/SuggestedRetail as margin
from sprucedotnet.dbo.inventorystore
where primaryvendorcode = 'MILWAU184'

The table has ~85180 records and 151 fields, So I won't add the entire table schema unless it is requested. The fields being calculated are DECIMAL(18,4).


texts: Update two tables, each on separate MySQL servers, on two different machines in one query?

texts:
Have two MySQL servers running on two different machines in a local network. Using PHP and accessing via IP. No problems there. The databases have identical schemas. One was an import from the other machine.

Want to run ONE query that updates the same table on each server simultaneously. Already have existing queries and do not want to update them extensively. Is there a simple way to do this?

I'm looking to keep the databases in sync when an update occurs.

Also open to any suggestions regarding another way to keep the databases in sync.


texts:
We are working on setting up AlwaysOn Availability Group for SQL Server 2014.

There will be 2 nodes: Primary Replica and Secondary Replica.

The question is, do we need a separate server for Quorum?

All the Windows Servers are in Azure.
As of now nothing is in place.
The vendor stated that they will add a server for maintaining quorums.
All servers for the complete system will be in same domain.


texts:
Is it possible to create an index against a subset of rows fulfilling a certain condition?

If it is possible how does one ensure the index is used?


texts: SQL - Date Range Broken Down by Individual Month and Year while maintaining the ID


texts:
I have one machine we've been fighting to do an in place upgrade of SQL Server from 2016 SP2 CU3 to 2017. It keeps failing, but that's another topic. My question is, is there a way I can run the upgrade, but force it to omit upgrading user databases to 2017?

If I can get the system databases upgraded and the server running I'll manually upgrade user databases at another time.


texts: Why is InnoDB auto_increment column skipping over large blocks of unused values?

texts:
We have an active and important InnoDB table with about 8M rows.  We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time.

I turned on query logging while watching for a jump and found no deletions from the table during the time.  (We don't often delete historical data, anyway.)

MySQL version:  mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (i386) using readline 5.1

OS: Centos 6.3

Any ideas how or why this could happen?
Any other information you might need to assess the situation?

Thanks!


texts:
I've an update statement which uses 2-3 tables. It runs in less than a second in QA but it takes forever in Prod. The number of records are exactly same and all the indexes are same too. In fact Prod server is an stand alone box while QA is a VM and have Dev,QA and Stage SQL Server instances on one server. So in that way Prod is better equipped than QA or Stage as it has more memory and more h/w.

And it's not just one update statement which has this issue. There are some other selects too which runs in lower environments but they don't move at all in Prod. I tried these codes to run when no one is on Prod.

My dba restored the prod copy to another instance. where no user has any access. And then he tried to run the same query. Again, it runs in QA but not in prod and not in the new instance. What could be the possible things which We should look for?

The update I'm running is -

UPDATE d Set Status = 'Active'
FROM DimesionTable D
INNER JOIN StageDB.dbo.StageTable a on d.PublicID = a.PublicID
WHERE EXISTS(SELECT 1 FROM StageDB.dbo.Table1 p
     INNER JOIN Table2 d ON p.PublicID = d.PublicID
     WHERE p.RetroFlag = ''
     AND p.AccountID = a.RowNumber
     AND d.IsActive = 1
     AND (p.CancellationDate is NULL
     OR p.CancellationDate > GETDATE()
     OR p.CancelFlag = 'Cancelled')
AND GETDATE() BETWEEN p.StartDate AND p.EndDate) AND d.AccountID > 0

I'm ruling out blocking as I tried in off hours in Prod as well as in the new environments where no one has any access.  I looked in sys.configurations, and the only differences I found are:

max degree of parallelism - Prod - 8      | QA - 0
max server memory (MB)    - Prod - 472064 | QA - 256000

Both servers have 8 CPUs. I double checked all the involved tables and all have the same indexes. Still the query plans are different. And the suggested index by SQL Server is different too. My understanding is - if the tables has exact same structure, then query plan and suggested indexes should be same.

Indexes and statistics are getting updated nightly. And data distribution is also exactly same. No difference at all. We restored the prod database on a separate instance and there too we have the same issue. In fact, it's not just this query, there are other queries too which is having the same problem. I'm more concerned about something wrong with the server rather than the query. All these queries run in just few secs or a min or two in QA but they won't finish at all in prod or the new test environment restored from Prod.

Tempdb configuration:

QA   - Size - 122880 MB, Available - 97277 MB
Prod - Size - 174080 MB, Available - 148476 MB

Total 8 files in each server.

QA - 1st one's initial size is 25600 and rest 10240, auto growth - 5120 MB unlimited
Prod - 1st one's initial size is 76800 and rest 10240, auto growth - 15% unlimited

Initially STG was created from QA and it used to run on STG. But when we created STG from PROD, it's not running there either. The compatibility is set to 120 on both the instances.


texts: Remote mysql connection fails with accessed denied exactly every other attempt

texts:
On my development machine remotely connecting to Mysql i will get an access denied error every other attempt. What's odd is when it does fail like this it doesn't write to the mysql error log on the server like it does when there is a "real" access denied.

I came across this when writing a python program at first i thought it was the mysql python connector, so i switched to mysqldb same problem, and then i just tried straight from the CLI and the same issue. It's exactly every other attempt.

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
ERROR 1045 (28000): Access denied for user 'user'@'ip' (using password: YES)

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.19                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.19-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
ERROR 1045 (28000): Access denied for user 'user'@'ip' (using password: YES)

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.19                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.19-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

It's to the point that i had to hack my program to just always try twice and only raise an exception if there were two back to back failures. That's hacky and bad. I have to be messing something obvious its too reproducible.


texts:
I'm a student doing a project which uses JDBC and MySQL (on Windows).
I have an application with connects to a MySQL server on a remote host.

To have a secure connection, I found this : http://www.razorsql.com/articles/mysql_ssl_jdbc.html

but SSL is disabled in my MySQL server.

I already tried enabling it using this:
http://mysqlopt.blogspot.in/2010/05/using-ssl-connections-with-mysql.html

but it's not working.

I just want to make this connection to be a little more secure in a simple way, any ideas?


texts: In MySQL 8, can we make group replication db as readonly?

texts:
I have configured group_replication on 4 db. Out of which I am trying to make 2 db as slave(only read). Even though I make read_only, user can write data in it.
Is possible to make 2 as master and 2 as slave?


texts:
I have a table with start date and end date. For example,
PlaceID | StartDate  | EndDate
1       | 2020-01-01 | 2020-01-04
1       | 2020-01-10 | 2020-01-15
2       | 2020-01-03 | 2020-01-06
2       | 2020-01-20 | 2020-01-23

I need to write a script to get the date ranges that is not in the table for each PlaceID. (For now considering the month of January.)
For PlaceID 1 - I need the output date ranges as
2020-01-05 to 2020-01-09
2020-01-16 to 2020-01-31

For PlaceID 2 - I need the output date ranges as
2020-01-01 to 2020-01-02
2020-01-07 to 2020-01-19
2020-01-24 to 2020-01-31

What is the SQL logic behind this that I can use?


texts: Could running ANALYZE on all databases on large InnoDB tables show some performance increase?

texts:
So we have an old database that was fragmented until we ran OPTIMIZE on all tables.

The database/tables are about 100GB in size.  Tables are InnoDB with heavy read/write activity.

There are so many different queries we decided to disable query_cache due to over 1 million+ queries being pruned daily, even with 512MB cache size (with 4mb min).

Would ANALYZE actually improve performance?


texts: What does Oracle SQLCODE ORA-00942 signify?

texts: I am very sorry if I am asking a very basic question, but I Googled for this and found no useful info. Could you please explain what the Oracle SQLCODE ORA-00942 stands for?


texts: What's a good indexing scheme for a type-2 SCD table with a very broad natural key?

texts:
I have a table which links several fairly long text fields (~100 chars) to a single text field: { A, B, C, D } => E.  The dependent value can change, so I query data daily and record A, B, C, D, EffectiveDate, E.  My design right now has a clustered PK on { A, B, C, D, EffectiveDate }, in that order.  I had thought that I could then query the most recent value easily:

SELECT
    X.A, X.B, X.C, X.D, X.E
FROM
    Tbl AS X
WHERE
    NOT EXISTS (SELECT * FROM Tbl AS X2 WHERE X.A = X2.A AND X.B = X2.B AND X.C = X2.C AND X.D = X2.D AND X2.EffectiveDate > X.EffectiveDate)

Since the self-join is on the first fields in the clustered key, I expected good performance, with a merge join.  However, it's taking about two minutes with less than a million rows in the table.  This is part of a near-real-time update, so I really need it to take 30 seconds, tops.

Is there a better indexing strategy for this scenario?  I have a couple ideas which I have not tried, because of some combination of (A) I'm lazy and (B) I'd rather get the right answer from the wise people on Stack Exchange, so I'll know better in the future.  I realize that the "best" solution will depend on exactly the data I have, but I suspect that there's a good general solution I'm missing, I just need to adjust my clustering somehow.

I could replace the wide PK with a synthetic integer key but keep the wide key for clustering, but my understanding is that this would only reduce the weight of any additional indices, of which this table has none.
I could should stop recording data every day, and instead only record changes.  Values for E change every week or two on average, so I'm definitely bloating the data around tenfold.
I could create a synthetic key to collapse these text values to integers, or maybe use a hash to join first and then resolve collisions with the exact values.
I could shift to a type 4 SCD and do all the heavy lifting during overnight ETL.


texts:
Just trying to gather some knowledge on this topic as I have seen numerous answers and they are just confusing. I am using SQL Server 2012/2014 EE.

Is it a good idea to shrink transaction log files? If yes, then I see a lot of people say that back up your transaction log and then shrink. We have a backup tool that does a full backup @ 9:00 PM and does transaction log backup every 3 hours, if I have to shrink a log file, then I will make a manual backup and shrink the file? Correct?

I believe this is going to break up the log backup chain of our back up tool. How should this be handled?

If the answer is No to shrinking the log file, then what is the solution to transaction log drive filling up?

Also, do I have to take any special consideration when shrinking transaction logs of the databases which are in mirroring or Always On?

I am confused about the tlog backups as well, what do they do? What I mean is that I have a database whose log file space (after taking a log backup) is 20386.00 MB and the available free space is 20256.09 MB (99%). This makes it look that the entire TLog drive is full, however when I shrink this file then the 20256.09 MB is released to OS.

The "Corporate" policy does not allow me to change the timing of log backups. I have 20 gigs on the server for transaction logs files. There were some batch processes running which caused the transaction logs to grow to full 20 GB and since there is no more space left the database went down. This is why I need to shrink the files and release the space to OS so that database can come up.


texts: postgresql statement taking ages to cancel in pgAdmin III

texts:
I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for slightly over an hour before it reported as cancelled. For all that time the CPU was around 98-99%.

The statement that was running was a call to a function. It was only part way through the function when it got stuck and all the time was taken on the following statement:

SELECT ST_UNION(shape)
FROM descendant_areas
INTO best_fit_areas;

where descendant_areas is a temporary table with no indexes containing nine records. Shape is of type geography.

Versions:

PostgreSQL=PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

PostGIS=POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.0.2, released 2016/01/26" LIBXML="2.9.1" LIBJSON="0.12" TOPOLOGY RASTER

My question is:

Is it normal for cancelling a query to take that long or is it indicative that there are config settings set up badly?


texts: How much Tablespace we need to create for Oracle schema

texts:
How is the optimum number of tablespace we need to create in an Oracle schema ?
One for data and one for index ?
Thanks


texts: Third Normal Form (3NF) Decomposition Scenario

texts:
I am trying to understand what are the steps to check if a decomposition of table is in 3NF.

There is a specific exercise I'm trying to solve with no success.

Given that R={A1,A2,A3,A4}
and F={A1→A2A3A4,A2→A3,A3→A4,A4→A2}

The decomposition is:

R1={A1,A4}
R2={A4,A2}, A3={A2,A3}

(super keys are shown in bold)

According to the answers this decomposition is not in 3NF.

Unfortunately, I cant understand why. My main difficulty is the transitivity check, and a couple of questions have arisen:

Should I check transitivity per table?
While doing that should I ignore every dependency in a→b in F if a or b does not exist in the table?


texts: MongoDB 3.6 - Journal files are not getting cleaned up and they keep building everyday and use up a lot of disk space

texts:
We have recently upgraded to MongoDB 3.6 and since then we have noticed that the journal files keep building up and are not getting cleaned. Due to this, the disk usage keeps growing until there is no space left on the disk.

This issue does not occur with MongoDB 3.4. Is there anyway to enforce cleanup of journal files in MongoDB 3.6?

Update on data volumes:
We process around 148 GB of data every day. The disk space that the MongoDB data files use is around 60 GB. However, the journal files use up additional 70 GB of space everyday. Since the journal files are not getting cleaned up, the total disk space used increases by 70 GB everyday.


texts:
In our Vb.net application(userd within a local network connection inside a office), which is used from long time now some of the users get the errors only sometimes a day

  A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.

I have already checked following

All databases are set to autoclose OFF
Didn't find much network problems and network speed is good.
When I checked in SSMS the under server properties remote query time-out set to 600 seconds
I checked sql server log and no errors found relating to it or same error message

So what may be the reason for this error? And how to troubleshoot it?


texts:
I am trying to use this programme that goes by the name xSQL Profiler but the problem is that it keeps giving me a error where it says:

Trace mietrak did not start. Please check if the Trace File Path You specified for each Sql Server exists. Also check if you can connect to the Sql Server and have ALTER TRACE permissions.

This error pops up when i try to play my trace.

So what i did is the following:

I checked if the data path was correct and if it existed and it did
I logged in as a sa on the SQL management studio and clicked on the security tab then the log in tab then the login for the machine i was using witch was production\production-server and under securable i granted it access to Alter trace
after my second step failed i decided to let the program log in as a sa by choosing the SQL server authenticated mode and it still gave me the same error
I reinstalled the program twice and followed all steps precise to no victory
I then tried another profiler and it worked perfectly just to test if there is maybe some kind of SQL related issue

So now you are thinking why am i not just using the Profiler that works and that is because it does not offer all of the Features that xSQL does.

So please if any one has a solution or knows of a free profiler that is as good and offers the same features as xSQL please help.


texts: Why does this query work with a CTE, but not with a derived table?

texts:
I was looking to identify the 'Email' that was duplicated.

The two simplest ways I thought of are shown below, however for some reason the query using the derived table produces the error: Incorrect syntax near ')'.

CREATE TABLE #TEMP (
    ID INT PRIMARY KEY,
    Email VARCHAR(255)
    )

INSERT INTO #TEMP VALUES
    (1, 'a@b.com'),
    (2, 'c@d.com'),
    (3, 'a@b.com')

-- Fails to run
SELECT Email
FROM (
    SELECT Email, COUNT(*) AS Count_
    FROM #TEMP
    GROUP BY Email
    HAVING COUNT(*) > 1)

-- Runs successfully
;WITH cte AS (
    SELECT Email, COUNT(*) Count_
    FROM #TEMP
    GROUP BY Email
    HAVING COUNT(*) > 1)
SELECT Email FROM cte

I am wanting to understand why this may be. What is the issue with the query using the derived table?


texts: Is automatic disabling of DML triggers possible during SSDT publish?

texts:
During ssdt deployment I am concerned about having DML triggers in place on tables that may slow down performance of table recreation a lot.

We are facing the issue that the column order in the table scripts is different than in the target servers. Because Microsoft does not provide an option to ignore this, the tables are recreated during publishing to ensure the same column order.

This process is very very slow. And since some tables do have DML triggers enabled, I am concerned this makes it even more slow than required. I looked for options to disable / ignore those triggers during deployment but could not find any. How are we expected to realize that?


texts: Slow query that times-out my application, any advice?

texts:
I have a query that it seems taking about 50 seconds to execute from SQL Management Studio, but when a user tries to run it as a report from a front end application (that was built in VB), the application seems to through an error of "timing-out expired (after 20 seconds),The timeout period elapsed prior to completion of the operation or the server is not responding"
So it's not waiting for SQL to finish executing the stored procedure.

It was running fine up until today, where one of the developers had to update (several 100's) rows for a column in one of the joined tables for that stored procedure, could that affected indexes, statistics..? I'm not sure where to start checking, please advice.

This report only acting up for one division (Central America), so when I log in as a West division user or any other division, it executes within 2 seconds for the same amount of rows (about 200 rows)...but I remember that it ran successfully couple of times this morning when I log back in from Central America. I checked it back again after peak hours, errors still the same!

I'm using SQL Server 2008 r2, Standard edition, not using any tools unfortunately.


texts: SQL Server - Error 8623 Severity 16 State 1

texts:
Fist of all, let me clarify that I'm brand new to this whole DBA world so I apologize in advance.

One of the databases I'm (since a couple of days ago) in charge of is throwing an Error 8623: "The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."

This particular instance is running SQL Server 2014 SP2.

How do I find which query is causing this? I've been having a hard time finding documentation on this.

Thanks!


texts: VBA Maximize Access Application Main Window

texts:
I need my access application window to open up in full screen. I have tried using:

DoCmd.RunCommand acCmdAppMaximize

and

DoCmd.Maximize (which I think only maximizes a Form).

Neither is working for me? I put the VBA code in the OnLoad event and I also tried putting it in the Click Event of a command button.

I think i need to set focus to the actual Access Application (main window) first and then run the DoCmd.RunCommand acCmdAppMaximize command but am not sure how to achieve this.

I noticed that if I click the command button with DoCmd.RunCommand acCmdAppMaximize nothing happens but If first I move the window around (by dragging it's title bar) and then I click the button it will actually Maximize the Window.

Anyone had any luck with this before?

Update:

Not sure why but the only way i can get the

DoCmd.RunCommand acCmdAppMaximize

to work, is by first calling the

DoCmd.RunCommand acCmdAppMinimize (minimize window first)

and then calling the DoCmd.RunCommand acCmdAppMaximize (and then maximize)


texts: Best practices, MySQL table with 150+ columns, many null

texts:
I have a table storing property data for thousands of properties, all coming from a feed. (updating at least once a day).

I'm certainly not an expert with databases and would appreciate some guidance on the best way to approach building the property table.  The challenge is that each property has a LOT of attributes which might or might not be included.  Each attribute value is potentially unique, so a relational table doesn't seem like it would provide any benefit.

Currently, my plan for the table is simple, creating a wide table with a lot of potentially NULL columns.  eg:

id - int(not null)
date - datetime(not null)
attribute1 - varchar(null)
attribute2 - varchar(null)
attribute3 - int(null)
attribute4 - bool(null)
ect..

Is there a better way to set this up?  Each attribute is uniquely associated with the property, so keeping it all in a single table make sense to me.

Will having that many columns, even if most are NULL, cause me issues when thousands of records exist in that table?  Each day, I need to do select several select queries on this table, returning hundreds to thousands of records each time.

Any advice or direction on what to research is greatly appreciated!


texts: How to write a trigger to add some information before update?

texts:
I want this trigger to insert some information a table named into orders_log (which has six columns orderNumber, orderdate, customerNumber, status  (before update), status (after update), action = ‘update’) every time, after an UPDATE happens into a table called orders.

I have attempted the trigger below, please let me know if this correct:

DELIMITER $$
CREATE trigger after_orders_table_update
after update
on orders
FOR EACH ROW
 BEGIN
INSERT INTO orders_log table
SET
orderNumber = old.orderNumber,
orderdate = old.orderdate,
customerNumber = new.customerNumber,
status(before update)=old.status,
status(after update)=new.status,
action = 'update';
END$$


texts: What is SQL Server Parallel Data Warehouse?

texts: What is SQL Server Parallel Data Warehouse solution? What is the benefit of PDW compared to normal DW architecture based on SQL Server and SSIS, SSAS and SSRS?


texts: Is it possible to create an alias/shortcut/makro/function for a collection of keywords?

texts:
In Oracle 12c, it's possible to use AUTO_INCREMENT functionality by using the keywords/phrase GENERATED ALWAYS AS IDENTITY. This seems cumbersome/clunky to me, given that in other dialects of SQL that I'm used to (SQLite, MySQL), the same is achieved with the keyword AUTO_INCREMENT or AUTOINCREMENT.

Is it possible to create an alias/shortcut/makro/function to get Oracle to treat AUTO_INCREMENT as meaning the same as GENERATED ALWAYS AS IDENTITY? (I'm not proposing editing the source code as a practical solution. I'd rather submit a feature request.)


texts:
I am trying to connect to my Oracle Database 11g from another computer connected in the same network.

In the connection data I put the address of the server machine, but it does not allow me the connection. From the server machine if it allows me to connect, but not from another computer:

Error de E/S. The network adapter could not establish the connection

  Machine 1 - Oracle Server: Local access connection: Yes

  Machine 2 - Client Remote connection: Failed

Both machines are in the same network segment:
192.168.x.machine1
192.168.x.machine2

Listerner
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP-BPS592UE)(PORT = 1521)(QUEUESIZE = 32))
    )
  )

Status LISTENER:

C:\..\..\11.2.0\server\network\ADMIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 22-JUN-2018 11:30:17

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                22-JUN-2018 11:04:36
Uptime                    0 days 0 hr. 25 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\..\..\..\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\..\..\..\diag\tnslsnr\LAPTOP-BPS592UE\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LAPTOP-BPS592UE)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LAPTOP-BPS592UE)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully


texts: Using OLAP-specs to compute selective running totals

texts:
Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that gives columns id, sum(value) over all entries with this id, and sum(value) over all such rows having state <> 0.

Of course, I know how to build this using subselects to compute the sums. However, I wonder how to do this elegantly using some OLAP-specification. Since I am not yet used to OLAP-specifications, I am not very certain how to do this.

Surely, something like

select * from (
  select
    id,
    state,
    sum(value) over (partition by decode(state, 0, 0, 1),
    sum(value) over ()
  from table
)
where state=0

would do the job. My questions:

Is there a better way to fetch the desired result?
Does this query perform better than the classical subselect-solution at all?

(I am using DB2 z/OS. Unfortunately, I do not have permissions to use explain on this installation)

Edit: Sample data and expected results:
 Consider the table

id      subid       state       value
-------------------------------------
 1          1           0           1
 1          1           0           2
 1          2           1           4
 2          1           0           8

The expected result would be a table which, for each ID, sums the values of all entries and the values of all entries where state=0.

 id     sum w/ state=0      total sum
 ------------------------------------
 1                   3              7
 2                   8              8


texts:
I would like to know (better ideas/best practices...) what can I use to encrypt my data.
I've used TDE in the past and I really don't like it. because if something happens with the certificate, and the servers, I will not be able to restore the backups.
I'm doing tests after tests and I just don't fell safe about it.
What are some other options to secure my database/data/backup files?
I tried masked data too, but 1) its for sql server 2016+ (and for now we are using 2012).. and 2) it just creates another column with the data masked, with the original still there.
What is a good option for this task?
i want to protect my backups so nobody can "steal and restore" it in another server, and I would like to prevent data steal.
I'm already using a good secutity measure with logins and ETC. only me have the sa pass, I created a function where it changes the pass everymonth, etc etc.


texts:
All, in SELECT Data Set Using Stored Procedure with BCP Error @Remus Rusanu provided a great solution to the problem. However, this evolved in to another problem: this BCP query now runs

DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'bcp "EXEC [2BSHAEOS]..ispsSelectEmptyAsNull ''B1A'';" ' +
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_BCPTesting2.txt" ' +
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + '';
EXEC master..xp_cmdshell @SQL;
GO

This was then failing due to the ordering of the fields in the .fmt file conflicting with those in the results from my SP. Now, following a rewrite of my SP to get the order of the fields in the ORDINAL_POSITION equal to the .fmt file, the above BCP export query gives me

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#TmpTableColumnNames'.
NULL

Edit: Changed SP Where the SP is

IF EXISTS (SELECT name
           FROM sys.procedures
           WHERE name = N'ispsSelectEmptyAsNull')
DROP PROCEDURE ispsSelectEmptyAsNull;
GO
CREATE PROCEDURE ispsSelectEmptyAsNull @TableName NVARCHAR(256)
AS
DECLARE @Columns VARCHAR(MAX);
SELECT @Columns =
    COALESCE(@Columns + N',
    NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '',
  N'NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
    SELECT ' + @Columns + N'
    FROM ' + @TableName + N';';
EXEC (@SQL);
GO

To say this stuff is making me want to poke myself in the eye with a long sharp stick is an understatment. Any help would be appreciated. Clearly from this MSDN link this error is an invalid column number. But the ORDINAL_POSITIONs of both the .fmt and the results obtained from the SP are the same!


texts: Issue with DECLARE @CutOffDate DATE = DATEADD(YEAR), @NumberOfYears, @StartDate);

texts:
When executing the steps in this query. I am running into an issue with the

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

I get a message of Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable “”CutOffDate”.

Should I be adding something in this syntax? Meaning “year” should be defined? or something along those lines?


texts:
I have an old MS Access 2000 application which connects to a SQL Server 2000 database.  I need to migrate this database and application to a new server with SQL Server 2012.  The Access application is running locally on my user's workstation.

Should I upgrade the Access database to a new version then create a new connection to the 2012 SQL Server (new adp file)?  The application is unsupported and we need to move the database to a new server.


texts: How to add Subpartition to all partitions

texts:
I believe adding sub-partition in Oracle 11g is easy

ALTER TABLE PART_TEST
modify partition OCT19
add subpartition OCT19AXCS
values ('AXCS');

I have close to 250 partitions..namely

jan07
feb07
...
up till
...
dec26

Does it mean I need to include that many alter table statements to alter all my partitions and add sub-partitions ?


texts:
See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html:
I copy it and paste on MySQL command line but it stop at every semicolon.

I wonder how to make these sentences work? I really don't know why.

mysql> CREATE PROCEDURE doiterate(p1 INT)
-> BEGIN
->   label1: LOOP
->     SET p1 = p1 + 1;

ERROR 1046 (3D000): No database selected
mysql>     IF p1 < 10 THEN
    ->       ITERATE label1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the
                    manual that corresponds to your MySQL server
                    version for the right syntax to use near
   'IF p1 < 10 THEN
      ITERATE label1' at line 1
mysql>     END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
                    manual that corresponds to your MySQL server
                    version for the right syntax to use near
   'END IF' at line 1
.......


texts:
I have a 1.2 million record in a database table( it use index column also)

I want to delete old 1 million record. I try to do it using PhpMyadmin but it take more time.

DELETE FROM `table` WHERE `id` < 1000000

Is there any way to do it fast?

I have another question: if i did this using SSH client in command line, will it be fast?


texts: What is the purpose of keeping filegroup or filestream backup in MSSQL?

texts:
I'm a new DBA for SQL SERVER. Currently I'm learning about filestream on SQL server. So I tried to restore only filegroup of filestream to other database, but I get an error message:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  File 'testdb1' was not backed up in file 1 on device 'C:\data\New
  folder\FSbackup.bak'. The file cannot be restored from this backup
  set. RESTORE DATABASE is terminating abnormally. (Microsoft SQL
  Server, Error: 3144)

Is it possible to restore only filestream to other database or restore only an old version of filestream to the current database of FILESTREAM?


texts: What is faster - insert into new table, or delete from existing table?

texts:
I have a huge DB that I want to scale down, to only look at my area of interest. Initially I wanted to write the records I'm interested in onto a new database and table. However, this is taking for ever, so I'm wondering if it might be faster to delete the records I do not want. I already got a copy of the database, so there would be no problem to delete.

Insert statement:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7)
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7
FROM OldTable1
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5
WHERE OldTable2.C8 = 70

If I would delete I would delete all data from OldTable1 that did not match the inner join and where statement.


texts:
I really like using mongodb to store my data and recently I tried out GridFS and it really fits my use case.

My problem with it is the space requirement, which seems quite odd. I have ~107GB of images in Amazon S3, which is around 1 million files (all images, mostly small ones). I made a simple Java project to download the images from S3 and insert them into two separate MongoDB GridFS collections (single server, 3.6.5, 64 bit, Windows Server 2016). The problem is, when the upload/download completes, the GridFS collections take more than 300GB storage on the server. Is this acceptable for this kind of collection or should I worry about the tripled size?

Note: I simply inserted the images using the Java Mongo Driver (Spring Boot) without any significant change, the problem is with the image chunks. I do not delete or update any images (I defined a unique index for the MD5 field though, to ignore image duplication), thus compact and repair does not change the collection sizes. As much as I can see, the collections are not overly preallocated (I don't think my problem is similar to this: Huge size on mongodb's gridfs. Should I compact? )

Also, currently it is a single mongodb server, without a replica set.

Thank you very much for your help!


texts: How to update mysql 5.7 to 8.0 Amazon linux 1, ec2

texts:
I'm using MySQL 8.0.14 for my project and I want to use it for my AWS ec2 server.
I updated from 5.5 to 5.7 by following this link: Upgrade MySQL to 5.6 on ec2
But don't have any information to update to MySQL 8.0


texts: Second Normal Form (2NF) Normalisation Exercise

texts: I'm using a past exam paper for practice, can someone help me normalise this database to 2NF? I have uploaded a picture of the original below, and also my attempt at normalising it myself. I am certain I have gone wrong with the YEAR column, but can't be sure.


texts: Attributes in relation after cross product

texts:
I have a question regarding the resulting attributes in the cross product of two relations in relational algebra...

Generally, the cross product will result in a relation whose attributes is the sum of all the attributes, So number of attributes in R1 x R2 will be attributes in R1 + R2, so R1(a, b, c) x R2(d, e, f) → R(a, b, c, d ,e ,f)...

But, what happens if R1 and R2 have common attributes (attributes of the same name)? since a relation can not have duplicate attributes... so what would happen in the case R1(a, b, c) X R2(a, e, f)? thanks...


texts: Moving the binary location of MySQL for replication

texts:
Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump > for export and mysql < for insert on the slave? I want this because in our current setup it takes ages.

We have a mixture of MyISAM and InnoDB.


texts: What is the difference between Used and Allocated space?

texts: I am learning Database operations and I have found a couple of resources online that have given me a loose understanding, but I still couldn't explain the difference myself. Explain like I am five please. I want to make sure that I have a solid foundation to expand my knowledge upon.


texts: Optimising InnoDB configuration in MySQL80 Server

texts:
I have a WAMP production environment, currently running Apache24, MySQL57, PHP73 on two Windows Server 2016 servers - an application server and a dedicated database server - and I'm toying with the idea to upgrade to MySQL80.

I created a carbon copy of the virtual machines hosting the prod. env. to test out the new version, but I sadly noted, the app running v8.0 is much slower in some cases than the v5.7 install, and I'm wondering, if it's an InnoDB configuration issue.

The 'test' I'm running to check the performance, is a particular single form load in the application, performing 56 SQL SELECT queries in a rapid succession. The v5.7 environment loads the form in ~400 ms (average of 10 requests), while the v.8.0 loads it in ~3500 ms (also average of 10).

The v5.7 configured following this fine post: How large should be mysql innodb_buffer_pool_size?
And the v8.0 is using the same configuration, with some minor tweaks, due to compatibility issues. If needed, I can provide the config files of both system.

I tried to use the new built in innodb_dedicated_server config option, but not much changed in the performance.

Is this kind of performance loss is expected from the new MySQL 8.0 - I really hope not - or I just looked over some important steppes in the configuration?

Any help will be appreciated.


texts:
I have two tables spider.expedient and spider 367.expedient, I want to copy the data from the spider.expedient table to the spider_367.expedient table daily, respecting the data from the previous day and ignoring possible duplicates.

I have run this query but it gives me error by duplicates. Is the correct way to achieve this?

INSERT INTO spider_367.expedient SELECT * FROM spider.expedient


texts: MySQL and MariaDB both hanging up with any driver when connecting from a remote client to my VPS

texts:
What I did:
1- Changed the bind-address to 0.0.0.0. It didn't work. Then I commented it out. It didn't work.
2- Enabled and disabled skip-networking to no avail.
3- My users are using native password. Hosts are all %.
4- I restart using systemctl every time I make a change to my.cnf.
My VPS runs Ubuntu. I install MySQL using PPA.
I tried using mysql-client on my server and use -h my.server.ip and it worked.
What actually happens on my PC is that, using DBeaver's JDBC, it hangs up on testing. Using mysql-client, it hangs up on authenticating.
I here's a tcpdump of 3306 port whilst my client connects using mysql -h host -u user -p:
12:57:11.242560 IP static.124.162.63.178.clients.your-server.de.mysql > 107-175-213-107-host.colocrossing.com.45414: Flags [S.], seq 563569178, ack 1663255156, win 65160, options [mss 1460,sackOK,TS val 3078215323 ecr 3502272836,nop,wscale 7], length 0
12:57:12.272743 IP static.124.162.63.178.clients.your-server.de.mysql > 107-175-213-107-host.colocrossing.com.45414: Flags [S.], seq 563569178, ack 1663255156, win 65160, options [mss 1460,sackOK,TS val 3078216354 ecr 3502272836,nop,wscale 7], length 0
12:57:14.288784 IP static.124.162.63.178.clients.your-server.de.mysql > 107-175-213-107-host.colocrossing.com.45414: Flags [S.], seq 563569178, ack 1663255156, win 65160, options [mss 1460,sackOK,TS val 3078218370 ecr 3502272836,nop,wscale 7], length 0
12:57:18.352735 IP static.124.162.63.178.clients.your-server.de.mysql > 107-175-213-107-host.colocrossing.com.45414: Flags [S.], seq 563569178, ack 1663255156, win 65160, options [mss 1460,sackOK,TS val 3078222434 ecr 3502272836,nop,wscale 7], length 0
12:57:26.544777 IP static.124.162.63.178.clients.your-server.de.mysql > 107-175-213-107-host.colocrossing.com.45414: Flags [S.], seq 563569178, ack 1663255156, win 65160, options [mss 1460,sackOK,TS val 3078230626 ecr 3502272836,nop,wscale 7], length 0
12:57:42.672785 IP static.124.162.63.178.clients.your-server.de.mysql > 107-175-213-107-host.colocrossing.com.45414: Flags [S.], seq 563569178, ack 1663255156, win 65160, options [mss 1460,sackOK,TS val 3078246754 ecr 3502272836,nop,wscale 7], length 0


texts:
I have a TSQL query that uses PIVOT to create columns called Analyst and Supervisor from the ApprovalLevelName column.

This is the current output:

In my query, I have ApprovedTime column, and Status column (commented out in the query below).

I want to also PIVOT these so that they both appear for analyst and supervisor.
i.e. AnalystStatus, SupervisorStatus, AnalystApprovedTime, and SupervisorApprovedTime.

Below is how I want it to look...

How can I do this? Or is it possible in SQL? I tried using APPLY and another PIVOT but no success.

Query:

SELECT PivotTbl.*
FROM
(
    SELECT
        Code
        ,Period
        ,Task
        ,ApprovalLevelName
        ,ApprovedBy
        ,CommentText
        ,CommentPostedBy
        --,ApprovedTime
        --,Status
    FROM [Tasks].[TaskRecordDetail] tr
) AS SourceTbl
PIVOT
(
    MAX(ApprovedBy)
    FOR ApprovalLevelName IN ([Analyst],[Supervisor])
) AS PivotTbl

Here is how the data looks without a PIVOT:


texts: Installing Oracle 11g on Windows SBS 2011 64 bit

texts:
In the Oracle web site, I found the following requirements:

http://docs.oracle.com/cd/B28359_01/install.111/b32006/reqs.htm#CHDHGGFE

Oracle Database for x64 Windows is supported on the following operating systems:
    Windows Server 2003 - all x64 editions
    Windows Server 2003 R2 - all x64 editions
    Windows XP Professional x64 Edition
    Windows Vista x64 - Business, Enterprise, and Ultimate editions

Is it possible to install an Oracle 11g on windows SBS2011 64 bit?
Thanks
Alberto


texts: MS SQL Server 2012: once a day full replication (automatic backup/restore) for reporting

texts:
Our company is w/o a DBA, and it has fallen on me (a complete DB noob) to handle some critical database tasks.

I'm looking for the best solution for us to be able to "copy" a full database to another server, once per day.  We want to handle schema changes, and data can be stale up to 24 hours.

Our current DB size is around 16 GBs, and we don't expect it to grow very fast.

What would be best?

transactional replication?  (if so, how do we schedule this to only update once per day?)
backup and restore? (what is the best way to automate this?  I would assume a differential backup / restore would be best for speed / bandwidth?)
any other method that would better fit our needs?

Thanks for your response!.


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

texts:
I have a role : ADMIN_ROLE

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

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

I did (using system user) :

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

but when executing from ADMIN_ROLE :

Grant TEST_ROLE to TEST_USER

I'm receiving error:

  ORA 1932 - ADMIN option not granted for role string.

Any ideas please ?


texts: How to avoid/prevent Aborted Connections in mysql

texts:
We are using MySQL Version 5.1.73. Database is getting huge number of connections around 600 at one time.

I observed Aborted Connections are getting increased from Global Status variables. So I enabled log_warnings=2 and when I checked MySQL log file, I  observed huge number of below warnings:

  [Warning] Aborted connection 21352666 to db: 'DBName' user: 'UserName'
  host: 'xx.xx.xx.xx' (Got an error reading communication packets)

I searched in Google and increased max_allowed_packet to 250 MB.

DB Size is 700 MB.
Innodb_log_file_size is 50 MB.

We also observed error like 'lost connection to MYSQL server at 'reading authorization packet' system error 0' in application log files.

Why are we getting these warnings and errors and what to do to avoid them?


texts:
I really don't know nothing about SQL.. I know how to insert and select data from  database using PHP but besides that, nothing.

I found this view And I am sure that 5 minutes for you to answer this question will give me a lot more knowledge.

I understand the fact the the view is created and given the name film_list and the AS defines what this view is going to retrieve.

Then we select from the Schema or Database film the field film_id and define it AS FID.

Q1. Why do we do that? What is the main purpose of this definition?

... The statement continues until GROUP_CONCAT.

Q2. What is the difference between CONCAT, that already combines the two fields to one element, and GROUP_CONCAT ?

.. Then the FROM syntax. Now we choose the schemas and tables to select from.
After reading on w3schools, I understand the INNER JOIN, FULL JOIN, LEFT JOIN and RIGHT JOIN statements, but not the JOINonly statement.

Q3. Which requirements does the JOIN statement have?

.. Okay now.. This one I haven't been able to find anything about.

Q4. What is the ON statement, and what is it good for?

The GROUP BY statement I understand.. The SQL simply groups everything together related to the film_id

CREATE VIEW film_list
AS
SELECT film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id

Q5. What would the correct way to display returned data be?

I assume that the view would create some kind of table during the SELECT statement. Usually I would be able to simply echo a row in PHP. Is that  still possible, and if so - how would I have to do? Because usually I echo like this echo $row['field_name'] but how would I know which field to call? Is that why we defined the elements with FID, title, desciption etc.?

In my MySQL database I have 14 different tables each one containing a little bit information about a cooking recipe.

Q6. Would a view be ideal for me to use when having that amount of tables to search in?

I Really hope that some of you please would take your time to answer these questions. It would help me a lot. And then a final Question:

Q7. Which websites or books are the best to teach me how to work with SQL?


texts: Updating a row through Linked Server to MySQL DB

texts:
I'm trying to update a single row using linked server from MS SQL 2012 to MySQL DB.

I run this query:

UPDATE b2b...product set [index]=`test_value` where id=12345;

Error Message:

  The OLE DB provider "MSDASQL" for linked server "b2b" could not UPDATE table "[b2b]...[product]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

I have googled this error and everyone recommends to check option "Return     matched rows instead of affected rows" in MySQL ODBC Data Source Configuration, so I tried to do that, but it was already checked. So I tried to uncheck this, it didn't help, so I re-checked this setting.

If checking this option doesn't help, what else could I do?

By the way I tried the same update using MySQL WorkBench and it works fine.

Edit: this is very weird.
When I update a particular row using MySQL WorkBench then I'm able to update it using SQL Server Management Studio. I tried it on several rows.
BTW, I had to set safe mode in MySQL WorkBench in order to be able to update rows.


texts: RMAN Duplicate database errors with password file location could not be found

texts:
Running RMAN to duplicate two Oracle 12.1.0 databases, using the below, I'm faced with an error I can't seem to find an answer too.

c:\rman TARGET sys/password@TEST AUXILIARY sys/password@TEST_SDBY
connected to target database: TEST (DBID=226670971)
connected to auxiliary database: TEST (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> DORECOVER
5> NOFILENAMECHECK;

Which gives the following errors

rman-03002: failure of duplicate Db command
rman-05501: aborting duplication of target database
rman--5622: password file location could not be found for the auxiliary database

Following documents online on how to create the password file, this is what I did:

cd %ORACLE_HOME%\database
ORAPWD FILE=PWDTEST.ora PASSWORD=password FORCE=y

I copied it from the Primary to the Secondary and put it in the same directory on the Secondary server.

I can't seem to figure what I've done incorrectly as I've followed multiple guides on how to set up a primary / secondary server for a dataguard environment. Nothing I've seen on searches seem to point to anything other than make sure your password file is correct.


texts: Sybase IQ cannot select by a calculated date

texts:
Question:

I have a query to select rows dated "last Friday from a given date", using the dateadd/datepart/modulo 7 trick. It works fine in Sybase ASE, but returns 0 rows in Sybase IQ. If I hard code the date into the query, it works in IQ as well, but I need the query to be dynamic.

Sample data:

create table #tmp1 (test_date date)
insert into #tmp1 values ('2017-09-08') -- Sep 08 is a Friday

Expanded query that works in ASE but not in IQ
Note the date provided is Tue Sep 12, and it finds "last Friday" from that date, which should be Sep 08.

select * from #tmp1
where test_date =
    convert(
        date,
        dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7),
        '2017-09-12')
    )

The gist of the query is that it will take the given date (Tue Sep 12) and find out how many days it has been since Friday. It then subtracts that many days from the given date, and uses that date in the where clause.

I expect the query to return the row in the temp table, and it works in ASE, but returns 0 rows in IQ. Can anybody explain why? If I hard code the Friday date, it finds the row just fine.

Edit: I've found that assigning the "last Friday" date to a variable, and then querying with that variable does work. Details below. However, using a variable isn't an option in this case, and I'm still very confused about why "placing the calculated date into a variable, then using the variable" works, but "calculating the date directly in the where clause" does not.

Some debugging samples when querying #tmp1 in IQ:

Actual query that fails in IQ (it returns the row correctly in ASE):

select * from #tmp1 where test_date = convert(date, dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12'))

  0 rows

Hardcoded date:

select * from #tmp1 where test_date='2017-09-08'

  9/8/2017

Deconstructing the query, to make sure the values are correct:

How many days are subtracted via the dateadd:

select ((datepart(cdw, '2017-09-12') + 2) % 7)

  4

What date is calculated by the dateadd:

select dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12')

  9/8/2017 12:00:00 AM

Nothing odd happens when it gets converted to date:

select convert(date, dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12'))

  9/8/2017

Using a variable

I've found that assigning the calculated date to a variable first does allow the query to work. However, variables aren't an option in my case (and really, this just raises more questions for me - why does it work when the value is stored in a variable, but not when place directly in the query):

Select via @val

declare @val date
select @val = convert(date, dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12'))
select * from #tmp1 where test_date = @val

  9/8/2017

Value of @val:

select @val

  9/8/2017


texts: Combine multiple alerts in to single alert

texts: I'm trying to find a way to generate a single alert by combining multiple alerts for AlwaysOn setup (for example error codes- 1480 , 19406) which gets logged in SQLERRORLOG file during planned or unplanned failover. Majority of the times, we are seeing false alerts getting raised for each of these error numbers in a SQL Server 2016 AlwaysOn setup and we would like to reduce the alerts by combining them to a single group. Could you please let me know if we there is a way to do this through T-SQL or Powershell or GUI?


texts:
I'm trying to get a better understanding of how serious this wait type is. I'm using sp_WhoIsActive stored procedure. Our DBAs don't seem to know much about this wait type. But I often see it when I run the stored procedure. Most of them are PAGEIOLATCH_SH but some are PAGEIOLATCH_EX. When they occur, the average wait time seems to be in the 20-25 millisecond range.

Questions:

1) Is this more of an indication of a problem with I/O as opposed to memory?

2) Is there a guideline to how often someone could expect to see this before it's considered a real problem?