texts: MySQL: SELECT to local variable in stored procedure

texts:
I have the following mysql stored procedure:
DELIMITER //
CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16),
                                     IN cardUID VARCHAR(14))
BEGIN
    SET @projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID =
          (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName)
             ORDER BY ID DESC LIMIT 1);
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;

    SET @id = (SELECT MIN(ID) FROM WORKDAY
                   WHERE WORKERID = @workerId
                     AND PROJECTID = @projectId
                     AND STOPTIME IS NULL
                     AND STARTTIME IS NOT NULL);
    IF (@id >= 0)
        THEN
            UPDATE WORKDAY SET STOPTIME = now() WHERE ID = @id;
    ELSE
        INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME)
            VALUES (@workerId, @projectId, now());
    END IF;
END; //
DELIMITER ;

When I call the procedure once, I get the result:
mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.09 sec)

From second run, is not working anymore:
mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

ERROR 1048 (23000): Column 'WORKERID' cannot be null

I did try to isolate and run the procedure with only this two lines of code:
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;

And it behaves just like above, but if I try to run SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = "045D91B22C5E80"; alone, works without problems.
The @projectId variable behaves normal everytime I call the stored procedure.
Can someone help me to find a solution?
Thank you!
My setup:
mihai:~$ mysql -V
mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

mysql> describe WORKER;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int         | NO   | PRI | NULL    | auto_increment |
| FIRSTNAME  | varchar(15) | NO   |     | NULL    |                |
| LASTNAME   | varchar(20) | NO   |     | NULL    |                |
| CARDNUMBER | varchar(14) | NO   |     | NULL    |                |
| POSITIONID | int         | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM WORKER;
+----+-------------+-----------+----------------+------------+
| ID | FIRSTNAME   | LASTNAME  | CARDNUMBER     | POSITIONID |
+----+-------------+-----------+----------------+------------+
|  1 | Ionut Mihai | Popescu   | 045D91B22C5E80 |          1 |
|  2 | Ilie        | Zbagan    | 040D7FB22C5E81 |          2 |
|  3 | Ilie        | Ungureanu | 043FA8B22C5E80 |          1 |
|  4 | Adrian      | Tehanciuc | 04D894B22C5E80 |          2 |
|  5 | Ioan        | Bitoanca  | 0405C6B22C5E81 |          2 |
+----+-------------+-----------+----------------+------------+
5 rows in set (0.00 sec)

DROP TABLE IF EXISTS `WORKER`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `WORKER` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` varchar(15) NOT NULL,
  `LASTNAME` varchar(20) NOT NULL,
  `CARDNUMBER` varchar(14) NOT NULL,
  `POSITIONID` int NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `POSITIONID` (`POSITIONID`),
  CONSTRAINT `WORKER_ibfk_1` FOREIGN KEY (`POSITIONID`) REFERENCES `POSITION` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


texts: SSRS --> .xlsx file --> SQL ETL -- "External table not in expected format" error, but only when files have not been opened

texts:
Long story short, I've had to use a workaround to importing data directly from SharePoint due to SSIS drivers not working with it.  My current workaround is almost good enough, but it still has some issues.

I started out with a regularly running report from SSRS that dumps into Excel 2003 files.  Turns out sometimes data completely gets left off of certain columns when using the SQL Import Wizard, so I switched to .xlsx files.  Now the data, when the import actually works, shows up, but there's a unique quirk with this in the .xlsx files where the import wizard throws the "External table not in expected format error" UNLESS the .xlsx files are manually opened, then immediately saved and closed.  After that, the import works without issue.

What could be causing this particular quirk, and is there any way to systematically get around it?  If this were a one-time deal, I wouldn't be taking issue, but this data will have to be regularly refreshed.

If it helps... .

the server this is on is a 2012 R2 Datacenter
running SQL 2014
no Microsoft office programs are installed on this server, just drivers
SSRS is version 2012 (on a different server)

There are two installations for Microsoft Office Access database engine; 2007 and 2010, which I needed to install to get the Excel --> SQL push to even work in the first place.


texts: Get Primary records where N most recent Secondary records match condition

texts:
I want a list of Authors (primary records) whose 2 most recent Books (secondary records) are best sellers.

INSERT INTO Authors
    (`author_id`, `author_name`)
VALUES
    (41, 'Heida Palffrey'),
    (42, 'Michael Grist'),
     ...

INSERT INTO Books
    (`book_id`, `author_id`, `book_title`, `published_date`, `best_seller`)
VALUES
(156, 41, 'Red Heat',      '2007-01-21', 'false'),
(161, 41, 'Far From Home', '2003-12-27', 'true'),
(859, 41, 'Storm Warning', '1992-08-19', 'true'),
(914, 41, 'Dead Heat',     '1981-01-04', 'false'),

(780, 42, 'The Last',      '2017-01-31', 'true'),
(198, 42, 'Mr Ruin',       '2016-01-18', 'true'),
(166, 42, 'Mr. Troop Mom', '1982-06-27', 'false'),
(796, 42, 'Ben Bootin',    '1972-12-01', 'false'),

    ...

For the above data, I just want author "Michael Grist" whose two most recent books were best sellers.

I have been able to get authors with consecutive best sellers, but not authors whose most recent two books were best sellers.

SELECT t.author_id, a.author_name, t.consecutive, t.best_seller, COUNT(1) consecutive_count
FROM (
  SELECT b.* ,
    @r:= CASE WHEN @g = b.`best_seller` THEN @r ELSE @r + 1 END consecutive,
    @g:= b.`best_seller` g
  FROM books b
  CROSS JOIN (SELECT @g:='true', @r:=0) t1
  ORDER BY author_id, published_date
) t
JOIN `authors` `a` ON (`a`.author_id = t.author_id)
GROUP BY t.author_id,t.consecutive,t.`best_seller`
HAVING consecutive_count >= 2 AND  t.`best_seller` = 'true';

For the data in the sqlfiddle below, only Chaddie Dreakin and Michael Grist fit the requirements of two most recent best sellers.

http://sqlfiddle.com/#!9/8808649/8


texts: Redundant / partially duplicated data in a DB - how to structure?

texts:
I'm building a website for which a large amount of user generated data will be stored in a back end db and will be discoverable by all users.

By its nature, a portion of the user entries will be redundant.  For the sake of example, let's imagine this data is made of recipes.  Suppose the following entries:

Title: Lemony dressing:
- 1 tbsp lemon juice
- 2 tbsp olive oil
Just mix!

and

Title: Lemony marinade:
- 2 tbsp lemon juice
- 2 tbsp olive oil
- 1 tbsp soy sauce
Just mix the ingredients together!

Clearly, those 2 entries have some redundant information: half the title is shared, the instructions are shared partially, one ingredient is shared exactly, another ingredient is partially shared....

Furthermore, one could easily imagine a recipe made of several sub-recipes, which can also exist on their own.  So for example, you could have a marinated flank steak recipe that uses the lemony marinade as a step.  So recipes can be defined recursively.

My question is how should I structure my SQL recipe database?  My gut feeling is that it makes sense to store the entry "lemon juice" in an Ingredients table, the quantity "2 tbsp" in a Quantity one, and then use their keys in the Recipe table.

But how far should I go?  Should I split "lemon juice" into two entries, in order to prevent duplication with "orange juice" and "lemon zest"?  After all, ingredients can also be defined recursively.  So should I store "lemon", "orange", "juice", and "zest" separately and only assemble them ("lemon juice", "orange zest", etc) later?  Or should I store "orange juice" and "lemon juice" knowing that I am duplicating "juice"?  Is it a function of word size?

But then instructions on how to use the ingredients are going to have some overlap.  Does it make sense then to store each word separately?  Why not letters?

I'm not sure how to consider the trade-offs like this.  I suppose that if I minimize the redundancy of the info in the tables, there must be some cost.  Is it performance wise, or is it a cost paid at conception?

Thank you,

JDelage

PS:  This question is not meant to ask for a specific implementation of a recipe db.  It's a more general question on how to store info that is partially duplicated.


texts: Performance of count() vs count() in a window function

texts:
I'm having a hard time trying to figure the performance gap between a raw count() query vs a count() OVER() window function.

Here is the test setup. Let's create a dummy table:

CREATE TABLE main (id int);
INSERT INTO main VALUES (generate_series(1,1000000));
VACUUM ANALYZE main;

So far so good, counting the whole set takes around 60ms using a parallel sequential scan:

SELECT count(*) FROM main;

Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8) (actual time=65.260..65.260 rows=1 loops=1)
  ->  Gather  (cost=10633.33..10633.54 rows=2 width=8) (actual time=65.229..65.256 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8) (actual time=45.728..45.729 rows=1 loops=3)
              ->  Parallel Seq Scan on main  (cost=0.00..8591.67 rows=416667 width=0) (actual time=0.008..24.864 rows=333333 loops=3)
Planning time: 0.783 ms
Execution time: 67.769 ms

Now let's try to return a few rows along with the count :

explain analyze SELECT id, count(*) OVER()
FROM main
LIMIT 10;

Limit  (cost=0.00..0.27 rows=10 width=12) (actual time=1077.092..1077.100 rows=10 loops=1)
  ->  WindowAgg  (cost=0.00..26925.00 rows=1000000 width=12) (actual time=1077.091..1077.097 rows=10 loops=1)
        ->  Seq Scan on main  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.520..60.036 rows=1000000 loops=1)
Planning time: 0.671 ms
Execution time: 1094.541 ms

This query takes 1050ms to complete. What surprises me is that the planner chooses to do a sequential scan, then a window aggregation, and finally a limit over the whole set.

Creating an btree index didn't help, while raising the work_mem to 128MB did (dropped to 300ms). But I'm not a big fan of "if it too slow, order more servers". I'd rather understand what causes this behaviour and optimize the code.

I am aware that you can get estimates by querying the reltuples, but my question is more about understanding why there is such a performance difference between executing a SELECT + a count() and selecting and counting in the same query?


texts: Consequences of changing NLS_TIMESTAMP_FORMAT

texts:
I administer a 9.2.0.8 database where the NLS_TIMESTAMP_FORMAT is set to 'DD-MON-RR HH.MI.SSXFF AM';.

This leads to odd results when using the CAST TO TIMESTAMP function:

select CAST('14-SEP-2011' AS TIMESTAMP) "DATE" from dual;
----------
14-SEP-2020 11:00:00.000000 AM

If I alter the NLS_TIMESTAMP_FORMAT to 'DD-MON-YYYY HH.MI.SSXFF AM' then I am good to go.

Would changing the NLS_TIMESTAMP_FORMAT parameter on the database to YYYY have any consequences?


texts:
I got a table in PostgreSQL containing a column 'X' of type character (255). Now I would like to migrate that table so that 'X' changes to type character[] (255), but I want the columns original character (255) value to be in the migrated column as an element of the new array in 'X'.

Using sequelize, is there a way to do that through a migration script? Or is there a way to do it in JavaScript with transitions and stuff?

What would be the best (or maybe only) way to go?


texts: InnoDB: will uncommitted transaction produce redo log?

texts:
Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.

start transaction;

update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit

If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?


texts: repmgr Hot Standby Replicatiom Postgresql 12.0

texts:
I would like to ask you about how to change  Asynchronous Replication to Synchronous Replication ?
I have 1 Master and 2 Standbys. That's 2 Standby is Asynchronous , then I want to make 1 Standby as a Synchronous. I config hot standby with Repmgr and Postgresql 12.0.
So would mind tell me some clues?
Many thanks for your answers.
Phanny


texts: Performance difference between MySQL and PostgreSQL for the same schema/queries

texts:
I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.

I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests comparing with MySQL too. We observe that MySQL is twice as fast as PostgreSQL.

I did a tangible performance test:

Same columns in table with equivalent column datatypes.
Same number of rows.
Same indexes in both (primary key included).
The CPU load are idle and Postgres machine it's significantly better.
And the same query (obviously).

What am I doing wrong?

P.S: I read many "howtos" on performance tuning for database engines.
P.S(2): We're using InnoDB (one file per table) on the MySQL database.

Hi Mat!

I did the three common select (and hardest) queries.

The question about disk, certainly it's not the same; In Postgres it's a SSD (almost three time fastest).

MySQL cache data:

+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 16777216             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+

I don't know how to view this in PostgreSQL.

Thanks in advance.


texts: View with OPENQUERY won't build in SSDT project

texts:
I have an existing database that I'm trying to set up as a database project.  I'm not able to get the following statement to build:

CREATE view [dbo].[VW_SOMEVIEW] as
SELECT * FROM OPENQUERY ([SERVER2],'
SELECT   SESSION_NAME,
         SESSION_ROWS,
         ACTUAL_START
FROM  [DATABASE2].[dbo].[TABLE2] (NOLOCK)
WHERE  SUBJECT_AREA = ''XYZ''
order by ACTUAL_START '
)

I'm getting the error:

  SQL71501: View [dbo].[VW_SOMEVIEW] has an unresolved reference to object [SERVER2].

When I use the variable name for the server instead of the actual server name, I get:

  SQL46010: Incorrect syntax near $(LinkedServer).

Both the server containing the view and the server being referenced are on SQL Server 2016, and I'm using Visual Studio 2017.

I've added the .dacpac file from SERVER2 in References, and I've tried using variables for the server and database name, but nothing works. What am I missing here?


texts:
I'm on PG 9.5 and I have a table Visitors(id, data::json)

Example:

Visitor(id: 1, data: {name: 'Jack', age: 33, is_user: true })

I'd like to perform queries like

Give me all visitors named Jack and age > 25
Give me all visitors who are users, but where name is unspecified
(key not in json)

The keys inside the data column user-specified and as such are dynamic.

Which index makes the most sense in this situation?


texts: How to monitor how much data is transfered in a linked server?

texts:
I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases.
I want to know how much data is transfered when operation is doing. How can I do it?
My local database is Sql server 2008 and remote database is SQlserver2ksp3


texts: SQL Server database partitioning purge job failed with an error

texts:
We have a SQL Server Agent job that executes package to purge partition file and filegroup older than 25 month. The job was running fine however it failed this month with following error:

  The operation cannot be performed on a database with database snapshots or active DBCC replicas

I checked the SQL Server logs and there weren't any DBCC process running during time. I cannot figure out why it is throwing out database snapshot/active DBCC replicas error.

I'm using SQL Server 2012 - clustered instance.

SSIS Package steps:

Find out oldest partition to delete
Drop Archive table if exists
Get date to delete partition (Using table --- sys.partition_range_values
Where boundary_id = 1)
Create Archive tables
Switch partition 2 to Archive tables
Drop Archive tables
Merge partitions
Remove file and filegroup


texts: What happens to mysql information_schema after switching to innodb_file_per_table?

texts:
We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box.

We've backed all our dbs up and about to set the per_table_file flag drop all the dbs and re-import, but what will happen to the information_schema database in the original mysql data file, since this db wont be dropped and recreated, it wont be in the per table/file format, will we still free up space from our original huge datafile, or will this remain the same size?


texts:
I have php 7.3 on Debian 10. I'm trying to get the Apache Cassandra driver for php 7.3.

The one provided by datastax (https://github.com/datastax/php-driver) only works up to 7.1, and the pecl one (pecl install cassandra) also only work up to PHP 7.1.x. Does one for 7.3 exist? I have searched around and can't find it.


texts:
I want to know if some data has just been deleted from my database and receive an e-mail alert with the table names which have been affected by the deletion .

I have set up SQL SERVER to send e-mails, but i do not know how to get the data as explained above and the immediate sending of the e-mail as well .


texts: How to name a new table storing location at which transaction occurred

texts:
My client wants to begin collecting data about where transactions occurred.  We have a list of their events and locations where payments might be received, I'm just stumped on what to call the table in my database.
transaction context?


texts: Is it OK to restore master db backup, after changing WSFC clustered instance into non-clustered instance

texts:
I'm converting WSFC fail-over clustered SQL Server 2012 instance into non-clustered one.
My question in short: Is it OK to restore a master database backup taken from the clustered instance, into the newly installed un-clustered instance?
To achieve the conversion I've followed the instruction in this link.
Basically I've removed all the nodes from the cluster, remove the cluster, re-install standalone SQL Server, then re-mount the previously shared NTFS volume locally and attached all user databases.
Then, I've transferred logins using sp_help_revlogin, linked servers, jobs, server roles, some permissions as shown in this link.
But still some things were missing from the re-installed instance, like permissions for system procedures, and these were stored in the master db.
Is it OK to restore the entire master db from the old clustered instance, or do I need to avoid such restore when some server configuration was changed, in my case un-clustered?


texts:
I'm using a statement similar to the following to append data to a column of type mediumtext to a bunch of rows:

INSERT INTO myTable (myKey,myVal)
ON DUPLICATE KEY UPDATE myVal=CONCAT(myVal,VALUES(myVal))
VALUES (1,'foo'),(69,'bar'),(1337,'baz')

At first this is really fast. But the more data there already is the slower it gets. It seems that when appending data, the whole field is read, merged with the new bit and then inserted again.

This mysql bugreport brings up exactly this issue:
https://bugs.mysql.com/bug.php?id=47937

Is there any way of making this faster?


texts:
I am kind of curious, one of SQL 2012 enterprise edition with 128 GB of RAM size of database is 370 GB and growing, amount of memory used by locks (OBJECTSTORE_LOCK_Manager) memory clerk showing 7466016 KB. I can also confirm that by looking at perf counter select * from sys.dm_os_performance_counters where counter_name = 'Lock Memory (KB)'

However, when I run query

select count(*) from sys.dm_tran_locks

it shows only 16 locks. So what is using over 7 GB of locks. Is there a way to find out?

Does that mean if once memory for locks has been allocated SQL has not yet deallocated it? In past 1 hour I do not see lock count exceeding 500 but lock memory stays the same.

Max Server Memory is 106 GB, We do not use lock pages in memory and I do not see any memory pressure or any errors in the error log in past 12 hours. Available MBytes counter shows more than 15 GB of available memory.

Activity monitor consistently shows 0 waiting tasks so obviously no blocking.

Considering SQL server lock take about 100 bytes of memory 7 GB is lots of memory and trying to find out who is using it.

I run a server dash board report top transaction by lock count it says "currently no locking transactions are running on the  system. However, lock memory still shows as stated above. DB is most busy during overnight hours.


texts: Clarify - Need to add Multiple foreign key constraints

texts:
I am not sure if I am thinking about this process the wrong way.

Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a particular round and date.

I have a Fk from PlayersTeams - A junction table that defines which players play for what teams.

A round table Fk - defines what round the list is for

In the TeamList table I have the two aforementioned columns, plus a date (mm/dd/yyyy) column, a TeamName column, and 17 player position columns.

To ensure that each player in the team list is only from the same team do I need 17 foreign key constraints from PlayersTeams(Fk) for each position to ensure that only a player from the selected team can be selected in a given position?

have I got something minged up?

Edit as below.

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[Junct_PlayersTeams]    Script Date: 05/02/2012 14:11:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Junct_PlayersTeams](
    [TeamPlayer] [int] IDENTITY(1,1) NOT NULL,
    [fk_Players] [int] NULL,
    [fk_Teams] [int] NULL,
    [startDate] [datetime] NULL,
    [endDate] [datetime] NULL,
 CONSTRAINT [PK_Junct_PlayersTeams] PRIMARY KEY CLUSTERED
(
    [TeamPlayer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Players] FOREIGN KEY([fk_Players])
REFERENCES [dbo].[Players] ([PLayerID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Players]
GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Team] FOREIGN KEY([fk_Teams])
REFERENCES [dbo].[Team] ([TeamID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Team]
GO

Team List Table

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[TeamList]    Script Date: 05/02/2012 14:13:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TeamList](
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [Fullback] [int] NULL,
    [LeftWing] [int] NULL,
    [LeftCentre] [int] NULL,
    [RightCentre] [int] NULL,
    [RightWing] [int] NULL,
    [FiveEighth] [int] NULL,
    [HalfBack] [int] NULL,
    [Lock] [int] NULL,
    [SecondRow(L)] [int] NULL,
    [SecondRow(R)] [int] NULL,
    [Prop(L)] [int] NULL,
    [Hooker] [int] NULL,
    [Prop(R)] [int] NULL,
    [Interchange1] [int] NULL,
    [Interchange2] [int] NULL,
    [Interchange3] [int] NULL,
    [Interchange4] [int] NULL,
 CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED
(
    [TeamListID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junc_TeamCoach] FOREIGN KEY([Coach(Fk)])
REFERENCES [dbo].[Junc_TeamCoach] ([Junct_TeamCoach])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junc_TeamCoach]
GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junct_PlayersTeams] FOREIGN KEY([Team(Fk)])
REFERENCES [dbo].[Junct_PlayersTeams] ([TeamPlayer])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junct_PlayersTeams]
GO


texts: Where can I find the failover history for an Azure SQL Database with geo replication?

texts: We have an Azure SQL Database that is geo replicated to another region. I suspect that they have swapped which is primary and which is secondary and would like to find information about when that has occurred. Where would I find that history?


texts: How to create a database from an XML file's DTD

texts:
I have a fairly large and complex self-describing XML file (It's an Apple Healthkit export; 183 MB).

Are there any utilities available to create a database in MySQL or PostgreSQL  with the tables and fields described in the XML file's DTD?

(If not, I suppose PHP's SimpleXML Parser would help but would require more thought.)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE HealthData [
<!-- HealthKit Export Version: 3 -->
<!ELEMENT HealthData (ExportDate,Me,(Record|Correlation|Workout|ActivitySummary)*)>
<!ATTLIST HealthData
  locale CDATA #REQUIRED
>
<!ELEMENT ExportDate EMPTY>
<!ATTLIST ExportDate
  value CDATA #REQUIRED
>
<!ELEMENT Me EMPTY>
<!ATTLIST Me
  HKCharacteristicTypeIdentifierDateOfBirth         CDATA #REQUIRED
  HKCharacteristicTypeIdentifierBiologicalSex       CDATA #REQUIRED
  HKCharacteristicTypeIdentifierBloodType           CDATA #REQUIRED
  HKCharacteristicTypeIdentifierFitzpatrickSkinType CDATA #REQUIRED
>
<!ELEMENT Record (MetadataEntry*)>
<!ATTLIST Record
  type          CDATA #REQUIRED
  unit          CDATA #IMPLIED
  value         CDATA #IMPLIED
  sourceName    CDATA #REQUIRED
  sourceVersion CDATA #IMPLIED
  device        CDATA #IMPLIED
  creationDate  CDATA #IMPLIED
  startDate     CDATA #REQUIRED
  endDate       CDATA #REQUIRED
>
<!-- Note: Any Records that appear as children of a correlation also appear as top-level records in this document. -->
<!ELEMENT Correlation ((MetadataEntry|Record)*)>
<!ATTLIST Correlation
  type          CDATA #REQUIRED
  sourceName    CDATA #REQUIRED
  sourceVersion CDATA #IMPLIED
  device        CDATA #IMPLIED
  creationDate  CDATA #IMPLIED
  startDate     CDATA #REQUIRED
  endDate       CDATA #REQUIRED
>
<!ELEMENT Workout ((MetadataEntry|WorkoutEvent)*)>
<!ATTLIST Workout
  workoutActivityType   CDATA #REQUIRED
  duration              CDATA #IMPLIED
  durationUnit          CDATA #IMPLIED
  totalDistance         CDATA #IMPLIED
  totalDistanceUnit     CDATA #IMPLIED
  totalEnergyBurned     CDATA #IMPLIED
  totalEnergyBurnedUnit CDATA #IMPLIED
  sourceName            CDATA #REQUIRED
  sourceVersion         CDATA #IMPLIED
  device                CDATA #IMPLIED
  creationDate          CDATA #IMPLIED
  startDate             CDATA #REQUIRED
  endDate               CDATA #REQUIRED
>
<!ELEMENT WorkoutEvent EMPTY>
<!ATTLIST WorkoutEvent
  type CDATA #REQUIRED
  date CDATA #REQUIRED
>
<!ELEMENT ActivitySummary EMPTY>
<!ATTLIST ActivitySummary
  dateComponents           CDATA #IMPLIED
  activeEnergyBurned       CDATA #IMPLIED
  activeEnergyBurnedGoal   CDATA #IMPLIED
  activeEnergyBurnedUnit   CDATA #IMPLIED
  appleExerciseTime        CDATA #IMPLIED
  appleExerciseTimeGoal    CDATA #IMPLIED
  appleStandHours          CDATA #IMPLIED
  appleStandHoursGoal      CDATA #IMPLIED
>
<!ELEMENT MetadataEntry EMPTY>
<!ATTLIST MetadataEntry
  key   CDATA #REQUIRED
  value CDATA #REQUIRED
>
]>
<HealthData locale="en_GB"> ... etc. (data)


texts: Oracle 10g dump file into Oracle 11g database

texts:
I went through this link for my answer to the same question. I was not sure if i could continue in the same link, so created a new question.

Oracle 10g dump file push into Oracle 11g database

Sorry if I am wrong.

When I used imp  with the following parameters:

file=my.dmp log=my.log fromuser=myuser touser=myuser statistics=none, ignore=y, full=y

I got the following errors:

IMP-00058: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
IMP-00000: Import terminated unsuccessfully

Also this error message was there

  the procedure entry point longjmp could not be located in the dynamic link library ORAUTS.DLL

The database and devsuite home are installed on Windows in two different drives. I found some references saying I should run the datapump import commands from the 11g home, with this:

set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_1
set ORACLE_SID=ORCL11R1

I am new to this Oracle database. Should this command be run in command prompt, since the installation is done on Windows, is it "set ORACLE_HOME"?

Any suggestion on this would be welcomed.


texts: Aurora PostgreSQL CPU usage stuck at 100% for 8 hours

texts:
One of our Aurora PostgreSQL database's(48 cores) CPU usage stuck at  100% for nearly 8 hours. These situation happened 3 times in the past 4 months. We are using AWS's business support plan, in the past few month, we worked closely with the support team to diagnose the issue, but still haven't found what caused the problem. Thus I post this problem in the forum, any advice is very much welcome.

The Problems

Problem 1: As mentioned above, the most obvious appearance of these problem is database's CPU stuck at 100% for a vary long time, then suddenly back to normal without intervention, as we can see from the two screenshot below(the second image is a screenshot from the database's Performance Insight panel):

Problem 2: When this incident happens, several empty tables that server as FIFO queues in our service, takes enormous time (roughly 500 ms) to execute a simple query:{code}select * from  order by id limit 1;{code}
in normal circumstances, a query like might just take several ms. At that time, I check the auto_vacuum execution about these tables, seemed that auto_vacuum service run normally, but every time when it ran, failed to retrieve table's dead_tuple. Then I executed +vacuum + manually, still failed to retrieve dead_tuple.

What we have done

The vacuum problem

The vacuum problem is relatively easier, as Laurenz kindly pointed out, the reason why vacuum not working is there're long running queries.

The CPU problem

After the situation occurred, we check the database's log, didn't found any dead lock or slow query, also during or before that 8 hours, the traffic to our service remain as normal.

From the second image above, we can see that an +update+ SQL take a lot database's resource. AWS's support suspected that this SQL is what responsible for high CPU usage, as it might locked the table. We disagreed, for reasons that:

this SQL only locks a row
the transaction this SQL belongs to normally takes 3ms to 10 ms to execute, given no huge traffic and the databases uses 48 cores, it's hard to believe this amount of traffic can have the database stuck for such a long time

Also, CPU became busier way before the vacuum situation occurred, the images below show the relationship between max transaction ID and CPU usage(the first image used UTC time zone, the second used UTC+8, which I added according UTC time below, I'll use UTC in this question):

as it shows, the CPU usage steadily grown since 10/12 20:00(a small peak around 10/12 16:00 was we running a background job):

from 10/12 20:00 to 10/13 00:00, CPU usage grows from 40% to nearly 100%, however, during that time, auto_vacuum works fine, the max transaction ID never exceeded 200 million
after 10/13 00:00, auto_vacuum stopped working, and since then CPU stuck at 100%. But CPU usage back to normal at around 10/13 04:00 (we didn't intervene at that time)
after 10/13 4:00, max transaction ID kept growing, now we assume, as mentioned above, is because there're long running transactions.
at about 10/13 14:30, max transaction ID back to normal, after we reboot the database

What's next

After few months' effort but still didn't found the root cause, AWS's support team give us following advices:

change database's log_statement to ALL, so that when the incident happens again, can trace back what SQLs were executing
clean up long queries regularly
avoids uncommitted transactions
monitoring database's XID


texts: How to dedicate server to MySQL for max performance?

texts:
I am using MariaDB 10.5, I see this in MySQLTuner report

Dedicate this server to your database for highest performance.

How do I do this?

My server is on Centos 8.


texts: Transaction with multiple SQLCMD calls in a batch file

texts:
Is there a good way to do something like the following?

SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION"
FOR %%f in (script1 script2 script3) do (
    SET CURRENT_SCRIPT=%%f
    SQLCMD -S Localhost -E -b -V 1 -i %%f.sql -o %%f.log
    IF !ERRORLEVEL! NEQ 0 GOTO ERROR
    SET CURRENT_SCRIPT=
)
SQLCMD -S %SERVER% -E -Q "COMMIT TRANSACTION"


texts: BULK INSERT - why does FIRSTROW skip by FIELDTERMINATOR rather than ROWTERMINATOR?

texts:
In reading the answers for this question about the caution against using FIRSTROW as a means of skipping column header rows, it struck me that it seems really inefficient and non-intuitive that a command for skipping rows even parses field terminators in the skipped rows rather than just ignoring them and looking for the row terminators to count skipped rows. But I don't really see other questions reaching the same conclusion, so I must be missing something. Which brings me to the question in the subject - why did the designers of T-SQL choose to implement that feature in that way? What problem does it solve or avoid that doing it in (in my opinion) the more obvious way of counting row terminators would not?
In short, what are the advantages of counting fields rather than rows when skipping rows?


texts:
Overview

I am attempting to join 3 tables auction, bids, and users. I need several key pieces of information from each table based off the bids table. In the end I want a summary overview of the auctions. To see the latest bid for each auction and the user that placed it.

I am used to doing simple SQL queries and I am not sure what I need to do in order to accomplish this. Not looking for a solution (although I would take it with an explanation) but learning experience.

Table Structures

auctions

id, title, location

bids

id, auction_id, user_id, bid_amount

users

id, name, address

Query So Far

So far this is what I have started with, any guidance on what to do from here is greatly appreciated.

SELECT
    MAX(bids.id) AS 'Bid ID',
    bids.auction_id AS 'Auction ID',
    auctions.title AS 'Auction Title',
    MAX(bids.bid_amount) AS 'Bid Amount'
FROM
    boardman_bidding.bids
        INNER JOIN
    boardman_bidding.auctions ON bids.auction_id = auctions.id
        INNER JOIN
    boardman_bidding.users ON bids.user_id = users.id
WHERE
    bids.auction_id > 1065 AND
    auctions.city_id = '45621'
GROUP BY bids.auction_id
ORDER BY bids.auction_id DESC;

Expected Output

I am hoping for the following:

bid_id, auction.title, auction.location, users.user_id, users.name, users.location, bid_amount


texts: SQL Server OR operator causing massive loop join

texts:
The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...).

I used live execution to verify that there is a nested loop join between the tables for the OR statement and then records are joined back to the EmailTable in the execution plan.

Basically, EmailTable is being probed twice.

If I add a hint OPTION (MERGE JOIN), the query is finishing within a second.

Please show me how to re-write this query so that the optimizer chooses a better plan by default.

EmailTable and TeamMembers has clustered index on INS_ID. Statistics on the tables are updated often.

DECLARE @a INT
    ,@b BIT
    ,@c INT
    ,@d INT
    ,@e INT;

SELECT [XYZ].[CNT] AS [C]
FROM (
    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable] AS [table1]
    WHERE ([table1].[INS_ID] = @a)
        AND ([table1].[ACTIVE] = 1)
        AND ([table1].[QUEUED_TO_SEND] = @b)
        AND ([table1].[OWNER_USER_ID] <> @c)
        AND (
            ([table1].[OWNER_USER_ID] IN (- 1))
            OR (N'Allusers' = [table1].[VISIBLE_TO])
            OR ([table1].[OWNER_USER_ID] = @d)
            OR (
                EXISTS (
                    SELECT 1 AS [C]
                    FROM [dbo].[TeamMembers] AS [table2]
                    WHERE ([table1].[INS_ID] = [table2].[INS_ID])
                        AND ([table1].[VISIBLE_TEAM_ID] = [table2].[TEAM_ID])
                        AND ([table2].[INS_ID] = [table1].[INS_ID])
                        AND ([table2].[MEMBER_USER_ID] = @d)
                        AND ([table2].[TEAM_ID] = [table1].[VISIBLE_TEAM_ID])
                    )
                )
            )
    ) AS [XYZ];


texts: Easy way to change Column ID (primary key) of a table to Auto increment

texts:
I started to use SQL Server recently and I still don't know the best way to do some things.

I created all the tables with a column ID as the primary key. Now when I try to insert values I get the following error:

  "Cannot insert the value NULL into column 'id', table 'Project.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated."

What is a good, simple solution to this problem?


texts:
I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I do anything other than a straight equality check, MariaDB uses all the partitions.

This is how I have my table setup:

CREATE TABLE IF NOT EXISTS events
(
  time TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
  id BIGINT NOT NULL AUTO_INCREMENT,
  CONSTRAINT id PRIMARY KEY (id, time)
)
-- Need to take the FLOOR. See  https://mariadb.com/kb/en/mariadb/unix_timestamp/
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(time))) (
  PARTITION p20150801 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-01 00:00:00'))),
  PARTITION p20150807 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-07 00:00:00'))),
  PARTITION p20150814 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-14 00:00:00'))),
  PARTITION p20150821 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-21 00:00:00'))),
  PARTITION p20150828 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-28 00:00:00'))),
  PARTITION p20150830 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-30 00:00:00')))
);

And this is how my query is using the partions.

MariaDB [events]> explain partitions select * from events where time > TIMESTAMP("2015-08-27T01:02:03");
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | partitions                                                  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | events | p20150801,p20150807,p20150814,p20150821,p20150828,p20150830 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [events]> explain partitions select * from events where time = TIMESTAMP("2015-08-27T01:02:03");
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | events | p20150828  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Any suggestions would be greatly appreciated. This has me tearing out my hair.


texts:
In the below syntax with the When Matched is it possible to only update if the value in the update table differs from the value in the employee table?  Something similar to my below DDL - but of course this throws an error and does not work.

What should I update in my syntax to only update rows that are different?  My desired update would be to not iterate each and every row that matches, but only update emp44 & emp55 since the address is different.

The error presented is

  Msg 156, Level 15, State 1, Line 24
  Incorrect syntax near the keyword 'WHERE'.

Syntax

  DECLARE @Emp Table (empid varchar(10), empaddress varchar(100))
  Insert Into @Emp Values
  ('emp11', '111 No Blue'),
  ('emp22', '222 No Blue'),
  ('emp33', '333 No Blue'),
  ('emp44', '444 No Blue'),
  ('emp55', '555 No Blue');

Declare @EmpUpdates TABLE (empid varchar(10), empaddress varchar(100))
Insert Into @EmpUpdates Values
('emp11', '111 No Blue'),
('emp22', '222 No Blue'),
('emp33', '333 No Blue'),
('emp44', '999 No Blue'),
('emp55', '888 No Blue'),
('emp66', '4141 No Blue');

MERGE @Emp emp
Using @EmpUpdates eup
ON emp.empid = eup.empid
 WHEN MATCHED THEN
UPDATE
SET emp.empaddress = eup.empaddress
WHERE emp.empaddress <> eup.empaddress
WHEN NOT MATCHED BY TARGET THEN
INSERT (empid, empaddress)
VALUES(eup.empid, eup.empaddress)


texts: How to uninstall SQL server 2012 not showing in control panel's "Programs and features"

texts:
I am frustrated to the core. While fixing my Sql Server I've  made the situation worse.
Here is the problem: initially my master database was corrupted, so the MSSql service was not starting.

I tried to fix it following some instructions from the MSDN site, but I just could not, so I decided to uninstall Sql Server through Control Panel.
Uninstalled every bit of sql from Control Panel, but the Sql Server still exists in my system.

Then I read somewhere to delete the Sql Server registry keys, I did the same. And now I cannot uninstall or even reinstall Sql Server on my machine.

I don't want to reinstall my Windows, can anyone help in here with a sure shot method of how to uninstall Sql Server and then reinstall it. Note that I have tried using the installation media also, but just couldn't install.


texts: Possible to include the create schema command in pg_dump?

texts:
I'm using pg_dump to export only the DDL from a database. It's working fine, however the create schema command is not included in the output file.
Here is what I'm using:
pg_dump -s -c -t myschema.mytable connectionURI
Everything works as expected, but I was expecting a create schema myschema command. Is there an option I'm missing?


texts:
Some time we are providing same laptop for 4-5 employees in our office for some testing purpose (since a costly licensed tool is exist in that laptop only), also asked them to do not click the Remember password checkbox in SSMS to store the credentials.

But few of them accidentally click that checkbox, so each time we need to clear the SqlStudio.bin to solve the problem. Instead of that it is nice to disable the Remember password option in the SSMS in that particular laptop.

Is it possible to disable the remember password checkbox in SSMS?

The expecting behavior is as below:


texts: A restore database action change my datatype from datetimeoffset to datetime

texts:
This the sequence I follow to replicate the problem:
I have a tableA with some column, for example Field1 AS DATETIMEOFFSET(2)
I have a view that use that column: View_ForTableA
If at that moment I check sp_columns View_ForTableA, column Field1 is a DATETIMEOFFSET.
Now, I backup my database, then I have an somebackup.bak
I restore my database using this .bak file.
Surprising me...
If I check sp_columns ViewForTableA, column Field1 is now DATETIME. What happened with my original data type? This behavior is bad for my scenario where I using ORM for mapping my database.
What could be happening?


texts: DB2 Online Restore but Without Roll Forward?

texts:
I read a lot of documentations for db2 restore but I could not find how to perform online restore from the last database backup but without roll forwarding of logs?

I will appreciate command example.

On example my last online backup is made 1st february.
I want to do ONLINE RESTORE of that backup but without logs after 1st February (similar with offline restore option WITHOUT ROLL FORWARD).

I am using db2 9.7

Thank you in advance


texts: Design: How do you decide when items are related vs when they are attributes?

texts:
I've been trying to figure this out for a while.  Let's say you have a person, for example.  An attribute of a person is his or her social security number, right?  But a person also has a social security number.  So, in an ER diagram, you could draw a square box for person and a square box for ssid, and you could connect them by a diamond, has.  Alternatively, you could draw a circle, ssid, and you could connect it to a square person box.

This isn't just true of ssid, though.  It's possible to draw either-or with concrete things, like a car, a pet, or a phone number, or with conceptual things, like a phone number, a friendship, or a mood.  So, where do I draw the lines?  Is the object of design just to get everything down on paper and looking nice, or should I be using a guide?


texts: Optimizing multiple MySQL sub-queries and joins for multiple runs

texts:
The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster?

Setup:

Mac OS X 10.7.5
MySQL 5.5.27

Query:

select
    pn as productId,
    qty,
    invno as invoiceId,
    invdate as invoiceDate
from
    (select
        trim(pn) as pn,
            qtyord as qty,
            oeinvch.INVNO,
            oeinvch.invdate
    from
        (select
        invno, invdate
        from
            W_OEINVCH
        where
            (invdate between '2013-08-01' and '2013-11-06') and custid = '13651') as oeinvch
        join W_OEINVCD ON oeinvch.invno = W_OEINVCD.invno) as QtyOrdered

Explain Output:

  *************************** 1. row ***************************
             id: 1
    select_type: PRIMARY
          table: <derived2>
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 38
          Extra:
  *************************** 2. row ***************************
             id: 2
    select_type: DERIVED
          table: <derived3>
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 3
          Extra:
  *************************** 3. row ***************************
             id: 2
    select_type: DERIVED
          table: W_OEINVCD
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 1130159
          Extra: Using where; Using join buffer
  *************************** 4. row ***************************
             id: 3
    select_type: DERIVED
          table: W_OEINVCH
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 102437
          Extra: Using where
  4 rows in set (0.58 sec)

UPDATE FROM EDZE'S ANSWER: 06/11/13

Avoiding derived tables. Execution time is increased now to ~4.2 secs.

Query:

  SELECT
      TRIM(W_OEINVCD.pn) as productId,
      W_OEINVCD.qtyord as qty,
      W_OEINVCH.invno as invoiceId,
      W_OEINVCH.invdate as invoiceDate
  FROM
      W_OEINVCH
      JOIN W_OEINVCD
          ON W_OEINVCH.invno = W_OEINVCD.invno
  WHERE
      W_OEINVCH.invdate BETWEEN '2013-08-01' AND '2013-11-06'
      AND W_OEINVCH.custid = '13651'

Explain output:

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: W_OEINVCD
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 1130159
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: W_OEINVCH
           type: ref
  possible_keys: invno
            key: invno
        key_len: 24
            ref: func
           rows: 1
          Extra: Using where
  2 rows in set (0.00 sec)

SHOW CREATE TABLE output:

*************************** 1. row ***************************
       Table: W_OEINVCH
Create Table: CREATE TABLE `W_OEINVCH` (
  `INVNO` char(8) NOT NULL DEFAULT '',
  `INVDATE` date NOT NULL,
  `SHIPDATE` date NOT NULL,
  `OENO` char(8) NOT NULL DEFAULT '',
  `CUSTID` char(8) NOT NULL DEFAULT '',
  KEY `invno` (`INVNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: W_OEINVCD
Create Table: CREATE TABLE `W_OEINVCD` (
  `invno` varchar(45) DEFAULT NULL,
  `pn` varchar(45) DEFAULT NULL,
  `qtyord` int(11) DEFAULT NULL,
  FULLTEXT KEY `invnoIndex` (`invno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


texts: Postgres plpgsql: Is a view better than a select query in a plpgsql function

texts:
I have a recursive query to the general effect of:

WITH RECURSIVE text1 (selfid, parentid, text) AS
(
SELECT DISTINCT
  text.selfid,
  text.parentid,
  text.text
FROM
  text
WHERE
  text.selfid = entryPoint
UNION ALL
SELECT
  text.selfid,
  text.parentid,
  text.text
FROM
  text,
  text1
WHERE
  text1.selfid = text.parentid
)
SELECT
  *
FROM
  text1
ORDER BY
  text1.selfid;

I'm using this in a plpgsql function where the value entryPoint is the starting point of the recursion and is passed in.

Question:  In the plpgsql, should I:

code the SELECT directly into the function as above?

or

programatically create a VIEW in the pl/sql function, then get my result set from the newly created VIEW?

Some of the queries will get very large result sets, sometimes as many as 500K rows (up to 4GB in the total result set). I wonder if a view would have better performance than a select.


texts: Migrating MySQL server to MariaDb or Percona?

texts:
I have a MySQL application (MySQL 5.5) running on a virtual Ubuntu 16.04 (64bit) server with 2 kernels and 8 GB RAM. I consists of a few, small RW tables and appr. 30 read-only InnoDb tables with 100-150k rows in each (totaling ~ 1-2 GB data + indexes). Almost all queries gather data from several of the read-only tables (lots of different joins).

Wanting to increase overall performance, should I try to:

switch to MariaDb and Aria tables ?
switch to Percona and XtraDB ?
add more RAM (kernels) ?
or something else ?


texts:
I was trying to figure a MYSQL query in PHP Admin to only show rows without any blank spaces. I can write a query to not show blank spaces in one column but in two columns I am having and issue, and three columns as shown below.

Name          Phone                Email
Tom           800-555-1212         ttest@hotmail.com
Mike                               mtest@hotmail.com
John          800-666-1212
              800-777-1212         atest@hotmail.com
Kate          800-888-1212
Sue           800-555-2222         stest@hotmail.com
Phil                               ptest@hotmail.com

To filter out the blank spaces so it will be this way:

Name          Phone            Email
Tom          800-555-1212      ttest@hotmail.com
Sue          800-555-2222      stest@htomail.com


texts: Count duplicate occurrences in MYSQL table based on value in two columns using having and inner join.

texts:
I have a table that lists vehicles by owner. In the table I have owner_id and vehicle_type columns, I want to pull all owners that have both more than 1 car and more than 1 van. The query I put together appears to be working but it has some performance issues.

My table is hosted in a MYSQL database.

Here's what I'm currently using, any recommendations on what I can change?

select * from (
  select owner_id, vehicle_type, count(*)
  from vehicles
  where vehicle_type = 'Car'
  group by owner_id, vehicle_type
  having count(*) > 1
) t1
inner join (
  select owner_id, vehicle_type, count(*)
  from vehicles
  where vehicle_type = 'Van'
  group by owner_id, vehicle_type
  having count(*) > 1
) t2
on t1.owner_id= t2.owner_id


texts: How can I attach the SSL authenticated certificate for my SQL Server 2014 if it is not on domain controller

texts:
We have Microsoft Azure with Windows Server 2012 R2 in Cloud Computer and window Server is Standalone it is not come under domain controller. We want to add SSL authentication.

SQL Server is not showing the certificate because certificate "Full
Qualifier Name" is not containing matching name

So we put "SmartServerSystem" computer name get the CSR. During the validation we got error

Data in "Common name" field is invalid. because it not a domain
name.
We tried to change the name app.SmartServerSystem.com then
    name is not change because it is not come under the domain
    controller

My question is how can I attach the SSL authenticated certificate for my SQL Server 2014? If it is not in Domain controller.


texts: Deleting aged postgresql rows with foreignkey constraints

texts:
I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated

For instance, if I run DELETE FROM links_reply WHERE id=3;, I get a violates foreign key constraint error.

But when I run DELETE FROM links_groupseen WHERE which_reply_id=3; and then run DELETE FROM links_reply WHERE id=3;, everything's fine. Delete the child first, then the parent. Works!

Things get complicated though, when the respective tables are huge, and I'm trying to delete all rows before a certain date. E.g. I want to DELETE FROM links_reply WHERE "submitted_on" < now() - interval '30 days';. In this particular case, how do I take care of 'the child table' first? What command do I run?


texts:
This is related to my previous question drastically different runtimes in BIDS and the SSRS Web Portal.

I am struggling with refreshing some reports in BIDS because they run forever.  I have now noticed that when I refresh the report in BIDS the query will experience ASYNC_NETWORK_IO waits.  (also CXPACKET but I understand that's more an effect than a cause)

The post Need help with ASYNC_NETWORK_IO seems to indicate SQL Server is waiting on the client, which I assume in this case is BIDS on my desktop.

If it can be identified from this snippet of information, where is the problem here?  Is it my desktop and its lack of oomph?  Could it be the network?  Is my report doing too many calculations on the report-side?  Where else should I look?  How can I fix it?

I will add that oftentimes the data never full returns.  Instead I receive the following (redundant) error message:

  An error occurred during local report processing.  An error has occurred during report processing.  Exception of type 'System.OutOfMemoryException' was thrown.


texts:
I was trying to solve this HackerRank problem using MySQL and here's what I came up with:

select id, wands_property.age, min(wands.coins_needed), wands.power
from wands
join wands_property
on wands_property.code = wands.code
where wands_property.is_evil = 0
group by power, age
order by power desc, age desc;

This gives me a compilation error:

  ERROR 1055 (42000) at line 1: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_ctaqjwhj9bt.wands.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Now, I understand that there are multiple results for id - this was the point of the problem, having multiple wands with the same age and power but different prices - but since I've specified that I want the minimum price shouldn't I get only the id corresponding to the wand with the minimum price?

Edit: I solved it with this:

select w.id, wp.age, w.coins_needed, w.power
from wands as w
join wands_property as wp
on wp.code = w.code
where wp.is_evil = 0 and w.coins_needed = (
    select min(coins_needed)
    from wands
    join wands_property
    on wands.code = wands_property.code
    where wands.power = w.power and wands_property.age = wp.age
    group by power, age
)
order by power desc, age desc;

but I noticed something I don't quite understand: if I add

and wands.id = w.id

to the last where clause I'm getting duplicate (power and age) results with different prices instead of only the minimum one. Why is that?


texts: System-wide startup file for Postgres 10 on Ubuntu 18.04

texts:
When I log into postgres under my personal shell account, my .psqlrc file runs a series of meta commands to set up what is for me an optimum working environment. I'd like to set that same environment as the default when I or anyone else logs in as user postgres. I've been searching for a long time for a way to do this. The closest I have come is the following.

Find the system-wide configuration directory:

pg_config --sysconfdir

This returns /etc/postgresql-common. After cd'ing into this directory,  can find no psqlrc file. So I make one, adding a test line to turn off that annoying pager behavior:

\pset pager off

...and save. So far, so good. Permissions and ownership (root) all look good, same as the other files in this directory. Then I try logging into postgres as postgres:

sudo -u postgres psql postgres

and...nothing. No change.

Next, because we have more than one db cluster (currently running pg 10, but also have an inactive pre-upgrade pg 9.3 cluster that I'm not quite ready to get rid of) I get back into /etc/postgresql-common and rename psqlrc to psqlrc-10, following instructions here: http://manpages.ubuntu.com/manpages/bionic/man1/psql.1.html.

Finally, I log back into postgres as postgres and...no change. I've also tried logging out entirely and logging back in. Still no change.

This is the latest of many attempts. I'm sparing you the earlier versions. Is it possible that /etc/postgresql-common/psqlrc only affects interactive terminal behavior for non-postgres users? If so, how do I make changes for user postgres? If not, where on earth is that darn system-wide psqlrc file?


texts:
I did a fresh SQL Server 2014 install. Next, I wanted to attach some databases from a previous SQL Server 2012 server. I copied all mdf and ldf files to the data directory, but when I tried to attach them, I got this error message:

  CREATE FILE encountered operating system error 5 (Access is denied)
  while attempting to open or create the physical file
  'D:\SQLSERVERDATA\MSSQL12.BIDEV\MSSQL\DATA\controlling_demo.mdf'.
  (Microsoft SQL Server, Error: 5123)

When I checked the file security settings (right click on file, properties, security), it said that I don't have read permission. The account I used to copy the files is in the administrators group. I have full control over the DATA folder and yet, just the files that I copied there are restricted.

When I try to re-apply the security rights on the whole folder, it says:

  Failed to enumerate the objects in the container. Access is denied.

When I copy the files to somewhere else, I have access rights.

Question is, how can I get access rights for these files in the DATA folder?


texts: Oracle : Is my DISTINCT function working properly?

texts:
I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. But I get a doubt if DISTINCT function is working properly for 1 TB full data of alphanumeric values?

First query

SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_1;
SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_2;

Difference of count is 3910

Second query

SELECT COUNT(*) DOC_ID FROM
(SELECT DISTINCT(DOC_ID) FROM TABLE_1
MINUS
SELECT DISTINCT(DOC_ID) FROM TABLE_2);

count is 0

Does DISTINCT work properly on alphanumeric DOC_ID, especially if my data is huge?


texts:
I was given the task to set up incremental backups for MongoDB replicaset, as start point, of course, I googled it and could not find anything on MongoDB docs, I did find however this question on Stack Overflow, which encouraged to develop my own solution as didn't find Tayra very active.
I read about oplog and realized it was very easy to develop something to replay the log, but it turns out that I didn't have to as mongorestore does that for me.
Now I have a working solution with bash scripts and it was quite easy, that's the reason I am asking here if there is any flaw in my logic, or maybe something that will bite me in the future.
Below how I implemented that:
Full backup procedure

lock writes on a secondary member db.fsyncLock()

Take snapshot

Record last position from oplog
db.oplog.rs.find().sort({$natural:-1}).limit(1).next().ts

Unlock writes db.fsyncUnlock()

Incremental backup procedure

lock writes on a secondary member

Dump oplog from the recorded oplog position on full (or latest incremental ) backup:
mongodump --host <secondary> -d local -c oplog.rs -o /mnt/mongo-test_backup/1
    --query '{ "ts" : { $gt :  Timestamp(1437725201, 50) } }'

Record latest oplog position (same way as for full backups)

Unlock writes

Full backup restore procedure

stop all instances of mongod
copy snapshot to data dir of the box which will be the primary, but make sure to exclude all local* and mongod.lock  this restore technique is called reconfigure by breaking mirror
Start primary
reconfigure replicaset
start secondaries without any data, let them perform the initial sync. Or copy the data from the new primary with fresh local database

Restore incremental backup
When we created incremental backup it stored it like this:
/mnt/mongo-test_backup/1/local/oplog.rs.bson
/mnt/mongo-test_backup/1/local/oplog.rs.metadata.json

We're instered on oplog.rs.bson but we will have to rename it, so here are the steps:

change directory to the backup: cd /mnt/mongo-test_backup/1/local

delete the json file rm *.json

rename the bson file mv oplog.rs.bson oplog.bson

restore it:
 mongorestore -h <primary> --port <port> --oplogReplay /mnt/mongo-test_backup/1/local

I have it all scripted, I may commit it on GitHub later.
The question is if there is any flaw in the logic? I am a bit suspicious as the procedure is quite straight forward and still I couldn't find it documented anywhere.


texts: In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT?

texts:
I have this small, simple table:

CREATE TABLE `ads_tracker` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `type` smallint unsigned NOT NULL,
  `reference` smallint unsigned NOT NULL,
  `date` timestamp NOT NULL,
  `action` enum('view','click') NOT NULL DEFAULT 'view'
) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_unicode_ci';

It has only an index as the Primary Key, but nothing else. Will there be any noticeable difference between inserting rows now (empty table), with say 100,000 rows, and say 1,000,000 rows? I figure UPDATEs will be slower, but I'd say that an INSERT could be pretty much just as fast, as there are no indexes except for the primary key. Or am I wrong there?


texts: PostgreSQL: how can I shorten a multi-word string to few words?

texts:
I'm using PostgreSQL 9.5.6.  I have space separated multi-word strings that I need to shorten to say 3 words?

I've looked at the documentation and in order to use the substring() functions I need to know the index position of the char where I want to start extraction, but the strings I'm working with are varying length.

I've also looked at split_part() and that only returns one word.

How can I get something like:

hello everyone out there somewhere

shortened to

hello everyone out


texts: Schedule suggestion and index rebuild / database integrity progress for Ola Hallengren’s Maintenance Script

texts:
I have to configure Ola Hallengren's Maintenance Scripts for my SQL Server 2017. SQL Database size about total 400GB.Total 10 Dbs. But I have some questions.
1- I will use these schedules like below. What is the best practices for this ? or What do you recommended?
DatabaseBackup – SYSTEM_DATABASES – FULL job – Daily at 12:05AM
DatabaseIntegrityCheck – SYSTEM_DATABASES job – Daily at 12:10AM
IndexOptimize – USER_DATABASES job – Daily at 1:00AM

2-  How can I check the progress / status when index optimize  and databasSee integrity job ?
Kind Regards,


texts: SSIS - sp logs counters in debug mode but not from agent job

texts:
Within an SSIS project I have a number of usp's that I execute to store row counters, like extracted row count, inserted row count, etc., during an ETL process.

My SSIS project is setup to run via an agent job.
The package runs ok, without any errors, but logs 0 for all my row counts.
When I run the project in debug mode everything logs ok, and the counters are correct.
I therefor presumed it was a permissions issue for the agent job, but everything is being executed ok and the counter logs are being written to, they just aren't being updated.

I ran a trace (forgive me, it was quicker) against the agent job execution and can see that the sp calls are being execute with 0's being passed in. So the agent job, sp's and ssis project are doing what is being asked of them.

Two key points to make:

my row count variables bubble up to a parent package via a script task, and the parent package executes the sp's to log the row counts. But as this works in debug mode, I think this part is ok.
I have set the property ProtectionLevel to EncryptSensitiveWithPassword. This is the first time I've used this property value, and I think it may be stopping the code in the script task from running, when the project is being executed from the agent job.

Can anybody offer some advice on this? Why would the agent job execution not log the row counts, but debug mode would? If this property setting is preventing the script task from running, how do I enable this?

Thanks in advance for any help you can offer.

EDIT:

I don't know if this is of any relevance but this project was built on a dev server and deployed across to the live server, where the SSISDB is and the agent job executes.


texts: How to install SQL Server 2008 R2 Profiler

texts:
SQL Server Management Studio doesn't have it on the Tools menu in my case, as is reported in some blogs and forums. Apparently you somehow either got it or not, but how did you get it, if you did, is a luck of the draw, or something? I am a MSDN Subscriber.

Is is possible to install the Profiler using some well-defined steps? Like download X, press menu item Y and click button Z sort of prescription?

[Edit additional info]
Upgrading from SQL Server 2005 Enterprise Edition to Server 2008 R2 Enterprise Edition didn't help (previously, of 2008 R2 editions I had only Express Edition). I am trying Setup program's various options but am unable to upgrade from Basic Tools (originally installed with 2008 R2 Express Edition) to Complete Tools, which I hope would contain the Profiler. Complete Tools option can't be checked in any variation of Setup options that I am trying.


texts: efficient way to store immutable numeric data

texts:
I'm trying to put a bunch of worldwide climate data into some kind of database structure so I can quickly access a collation of all 10 datasets simultaneously with fast indexing and hopefully efficient (space-wise) storage. It is important to be able to look up specific lat-long coordinates and quickly get all data recorded there, so my index is based on the location.

With the full range of data from 1901-2014, there are 360 (latitude) * 720 (longitude) * 12 (months) * 113 (years) * 10 (datasets) == 3,514,752,000 32-bit numerical readings, which can be found compressed here:

https://crudata.uea.ac.uk/cru/data/hrg/cru_ts_3.23/cruts.1506241137.v3.23/cld/

At first I tried making a document per month per location, with each document containing all 10 of the different datatypes (351,475,200 documents). This was quite fast to insert, but needed lots of disk space (something I'm trying to avoid). I hadn't yet discovered collection compression in MongoDB 3.x when I tried out this path, so I'm not sure to what extent that would help here.

The new approach I'm trying stores one document for each lat/long coordinate (360 * 720 documents total), each document containing 10 arrays that get upserted ($pushed) into. I imagine the constant query/write is slower than a plain write, though I'm using a unique index, and the lookup time should be effectively instantaneous.

This is much more space efficient, but running it now with a large dataset, I've noticed that the first few million points get inserted/upserted a lot quicker than those that come later, presumably because the collection size is growing and causing a lot more cache invalidations over time. At about 25% of the way through parsing the tsvs to MongoDB, I'm getting a write speed of about (only) 2000 documents per second. The entire operation will take about 40 hours to complete at this rate, but I fear that it will get increasingly (even) slower proportional to the db size.

I'm doing the number-crunching and sending the documents via node.js on my standard iMac (2016) with 8GB RAM, no fancy db servers involved (unfortunately).

Can anyone tell me whether relating to whether this is likely to slow down even more over time (which would necessitate a different approach to storing/inserting the data), or whether it's worth just waiting it out (I plan to do this insert operation once a year at most). I wonder whether mongoimport would be faster, not sure it works with $push though.


texts: Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

texts:
I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL?

I understand that where platform interoperability and/or backwards compatibility may be an issue then sticking with the tried and trusted MySQL is best. But for a stand-alone DB on a stand-alone web site/application are there any benefits to be had by using Maria?

Will Maria work with common web platforms such as WordPress, Drupal, Joomla etc.?

I expect that some of this is going to come down to choice/preference of storage engines, but to be honest I still don't know half the time if/when I should use MyISAM, InnoDB or any of the others! Which is better or faster or whatever?

The only thing I get is that if I want 'true' table relationships (i.e. foreign keys etc.) I use InnoDB.

Thanks for any help or clarity people can offer me.


texts: How to group-by to get MultiPoint/GeometryCollection in MySQL?

texts:
I have a table x like this:

Columns:

SubjectID int(11)
Timestamp bigint(20)
fix_geom point

When i try to group my fix_geom column to a MultiPoint (or GeometryCollection) by SubjectID, like:

SELECT SubjectID, ST_AsText(MultiPoint(fix_geom))
FROM x
WHERE SubjectID = 100
GROUP BY SubjectID;

i get a table with a multipoint geometry, but it contains just one single Point instead of the many i'd liked to see there...:

Can anybody tell me what i'm doing wrong?

Cheers and many thanks in advance,
Olaf


texts: Fastest way to find (rare) rows with a null value among millions of rows?

texts:
If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to write these queries to evaluate the state of the database. I'm pretty new to optimizing SQL, I'm used to working with much smaller databases.

SELECT address_id FROM addresses WHERE address_id IS NULL;


texts: Cannot restore Amazon RDS SQL Server database to given point-in-time using SQL Server Management Studio

texts:
I'm trying to restore one of my Amazon DRS SQL Server databases. Using SQL Server Management Studio, I can correct to my DRS instance using the master account I created when creating the database instance. When I start restoring a given database I'm able to see all full backups and transaction logs, but it gives me this error message when trying to restore

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

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

  The EXECUTE permission was denied on the object 'xp_fileexist', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I can restore a full database instance from AWS management console, but that is not what I want. I want to be able to restore a given database to a specific point-in-time and not restoring a whole new database instance. Also looks there isn't much information on how to restore to a specific point-in-time.

Thanks.


texts: Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

texts:
I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months:

There is a column "SortID" that needs to get updated with sequential integers, starting from 1. The column may look like this (without ORDER BY):

SELECT SortID FROM Beleg

2
3
.5
4
5
9
10
10.000001
10.000002
11
13

and needs to look like this.

2
3
1
4
5
6
7
8
… and so on

I have tried everything that I can think of, but no luck.

I am using the Execute SQL function from within Filemaker and I can just use plain SQL-92, nothing fancy.

For example, the following tricks all don't work (for example: Update existing rows with sequence number…). All produce syntax errors:

ALTER TABLE MyTable ADD MyColumn int IDENTITY(1, 2) NOT NULL

and:

ALTER TABLE MyTable ADD MyColumn uniqueidentifier NOT NULL
    CONSTRAINT DF_MyTable_MyColumn
        DEFAULT (NEWSEQUENTIALID())

and (no variables allowed):

DECLARE @id INT
SET @id = 0
UPDATE X
SET @id = cn = @id + 1
GO

and (ROW_NUMBER () doesn't work):

update T
set cn = rn
from (
    select cn,
        row_number() over(order by (select 1)) as rn
    from TableX
    ) T

and (this gets close, but no):

update invoice set RecNo = (next value for seq_invoice_recno)
where invoiceid in (select top 100000 invoiceid from invoice where RecNo is null
order by invoiceId)

. . .
However, I have come a little closer: I can use a Filemaker function "RecordNumber" that returns a sequential number for each line. Using this statement, I can get the correct order and get the correct integer for that line:

SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

.5  1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10  10
10.00001    11
10.00002    12

But when I try to write the values, I get an error, as soon as include the ORDER BY statement:

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

=> ERROR

Only, when I remove the ORDER BY, the values are written — but in the wrong order!

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210

1
2
3
4
5
6
7
8
… and so on

The SortID "1" should have been written into line 3 and the "2" into the first line, the "3" into the second line, like this:

2
3
1
4
5
6
7
8
… and so on

So close, but still no luck.

Does anyone have any idea?

Thanks a lot!

Gary


texts: Select Oracle clob field from remote table over dblink

texts:
I am trying to select a clob field thru flink from a remote table.I am able to see the values in editor result but not able to select thru program or any export.

SQL Error: ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables

I am able to select local fields and use them in application but failed in these.I can view in the toad  result perfectly when running queries in editor.there also not able to export that particular field..


texts:
This seems like something trivial would be easy, but I couldn't find a good way to do it.

This is what I came up with:

select convert(numeric,
                    (select convert(varchar,datepart(yy,getdate())) +
                         right('0' + convert(varchar,datepart(mm,getdate())), 2) +
                         right('0' + convert(varchar,datepart(dd,getdate())), 2)
                    ))

EDIT: I know it doesn't need to have a subquery like that, but ignore that =p


texts: MySQL row-based-replication HA_ERR_KEY_NOT_FOUND... which record is it?

texts: I have a mysql slave (5.1) using row-based replication. The slave has stopped with the error HA_ERR_KEY_NOT_FOUND. OK fine, it's trying to update a record that doesn't exist for some reason. Using the master's binlog position, I use mysqlbinlog -vv to read the binlog... but the transaction in question updates at least 50 records! How can I tell specifically which record/PK is missing? MySQL has to know, but doesn't appear to tell me anywhere?


texts: How to modify authentication plugin in mariadb starting with 10.4.1 to get mysql access for root user without password when migrating mysql to Mariadb

texts:
At https://kofler.info/root-login-problem-mit-mariadb/ a method is described for modifying the plugin being used for authenticating a user

update mysql.user set plugin='' where user='root';

leading to

ERROR 1348 (HY000): Column 'plugin' is not updatable

https://mariadb.com/kb/en/library/mysqlglobal_priv-table/

explains that there is now a single column global_priv table instead and the original user table is now a view.

select * from mysql.global_priv where User='root';

is possible or

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv where user='root';

for a more readable version.

Unfortunately the documentation says nothing about

How to modify the column under this design decision?
The motivation for the change?

It would be great to get these two questions answered - obviously #1 being the more pressing one.


texts: SQL Server High Availability without Windows Clustering

texts:
As per my understanding and information, there isn't any SQL Server High Availability(HA) feature without Windows Server Failover Clustering mechanism (WSFC) being used. SQL Server Mirroring used to be one such option where HA was possible without WSFC but with Microsoft stating that Mirroring would be removed soon from the product, are there any HA features which do not require WSFC?

One of the updates in vNext was that Microsoft would be releasing an option for Cluster Free Availability groups. But as of now, do we have any Cluster Free HA feature within the product(SQL Server 2012, 2014, 2016)?. LogShipping is more of a DR option, so excluding LogShipping and Mirroring any other feature?


texts: How to calculate cache misses for PostgreSQL

texts:
I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS level that a lot of I/O is taking place, therefore I suspect that a lot of cache misses are taking place.

If you look for "cache miss" or "cache miss postgresql" or similar searches in the Internet, you will find a lot of references to "cache_miss statistics". But nowhere is explained how to get them! I kind of understood, that that value has to be calculated subtracting hits from fetches. But since I'm no experienced DB admin, I don't really understand with values are meant :-S

I found the PostgreSQL - Monitoring Database Activity documentation, but I'm not sure if following formula is all I need:

cache_miss = "result_of" pg_stat_get_db_blocks_fetched(oid) - "result_of" pg_stat_get_db_blocks_hit(oid)

An explanation for dummies would be highly appreciated.

Thank you in advance!


texts: Return a value only when conditions satisfy for entire column

texts:
Can anyone help me to how to achieve below result

Input data

ORDER_NUMBER    LINE_ITEM   FLAG
LP-13288    1   TRUE
LP-13288    2   FALSE
LP-13288    3   (null)
LP-13288    4   FALSE
LP-13289    1   FALSE
LP-13289    2   FALSE
LP-13290    1   TRUE
LP-13290    2   TRUE
LP-13290    3   TRUE
LP-13292    1   FALSE
LP-13293    2   TRUE

But I am expecting result like below:

ORDER_NUMBER    LINE_ITEM   FLAG    RESULT
LP-13288    1   TRUE    0
LP-13288    2   FALSE   0
LP-13288    3   (null)  0
LP-13288    4   FALSE   0
LP-13289    1   FALSE   0
LP-13289    2   FALSE   0
LP-13290    1   TRUE    1
LP-13290    2   TRUE    1
LP-13290    3   TRUE    1
LP-13292    1   FALSE   0
LP-13293    2   TRUE    1

Scenarios that need to implement

For a group of order_number, if all line items are 'true' then result field need be '1'
For a group of order_number, if any of line items contains mixed flags (true, false, null) then result field need to populate '0'

Any help would be appreciated.


texts: Specifying Oracle Service Name in UNIX Profile

texts:
Is there any way we can specify Oracle Service Name in UNIX Profile?

I know that there is an environment variable ORACLE_SID for specifying SID in the Profile,

But how about service name? If there is, then can we remove ORACLE_SID once we set the Service Name in the Profile?


texts:
I am working on a conceptual model for a database. I have the following entities:

Student (id, name, date of birth, department)

Subject (id, name)

This is a many-to-many relationship, one student may be enrolled in many courses, each course has many students. From what I understand, as I should break this many-to-many relationship by introducing a new into a entity, say 'registered' which allows a one-to-one relationship or one-to-many relationship?

Is this correct design decision?


texts: MYSQL any way to improve this query's performance?

texts:
MYSQL version 5.6.23

CENTOS 7

Hi,

I have the following query that is coming from an application that I am hoping to make more efficient  from the mysql side of things.

I have an index built on each of the columns in tables 'files', 'filesystem', and 'instance'.

SELECT t0_f.pk, t0_f.created_time, t0_f.md5_check_time, t0_f.filepath, t0_f.file_tsuid, t0_f.file_md5, t0_f.file_status, t0_f.file_size, t0_f.instance_fk, t0_f.filesystem_fk
FROM files t0_f, filesystem t1_f_fileSystem, instance t2_f_instance
WHERE (t0_f.file_status = 0
AND t0_f.file_tsuid
IN ('1.2.840.10008.1.2', '1.2.840.10008.1.2.1', '1.2.840.10008.1.2.2')
AND t1_f_fileSystem.pk = 1
AND t2_f_instance.sop_cuid = '1.2.840.10008.5.1.4.1.1.2'
AND t0_f.created_time < '2015-03-26 09:29:58'
AND t0_f.filesystem_fk=t1_f_fileSystem.pk
AND t0_f.instance_fk=t2_f_instance.pk)

Here is the explain:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1_f_fileSystem', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', '100.00', 'Using index'
'1', 'SIMPLE', 't0_f', 'ref', 'instance_fk,filesystem_fk,file_tsuid,file_created,file_status', 'filesystem_fk', '9', 'const', '74220028', '100.00', 'Using where'
'1', 'SIMPLE', 't2_f_instance', 'eq_ref', 'PRIMARY,sop_cuid', 'PRIMARY', '8', 'pacsdb.t0_f.instance_fk', '1', '100.00', 'Using where'

Would this maybe benefit from a 'combined' index or would there be any other way to improve the 'explain' short of changing the query as I have no control over that.

If there is anything else that I can post to help, please let me know!

thanks

UPDATE

I created an index 'files_compress' (the first index you suggested) and its estimate went down to 3.1M rows with a 'Index Range Scan'.  I added the 2nd one as well, but it didn't change the results.

Here is the new 'Explain'

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1_f_fileSystem', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', '100.00', 'Using index'
'1', 'SIMPLE', 't0_f', 'range', 'instance_fk,filesystem_fk,file_tsuid,file_created,file_status,files_compress,files_compress_2', 'files_compress', '772', NULL, '3192823', '100.00', 'Using index condition; Using where'
'1', 'SIMPLE', 't2_f_instance', 'eq_ref', 'PRIMARY,sop_cuid', 'PRIMARY', '8', 'pacsdb.t0_f.instance_fk', '1', '100.00', 'Using where'

Do you think there is any more room for improvement?  I know that its way better than 74M rows!

thanks again!


texts: Hits to Insert Ratio and Insert to Prune Ratio

texts:
I'm confused with Insert to Prune Ratio.

As I understood, for example Hits to Insert Ratio:

Hit:Insert     0.47:1

is very bad (more inserts, then hits, hence - server waste resources to insert in to QCache, but - it's rarely give results back to clients) - is this one correct?

But about next one - Insert to Prune Ratio... I'm really confused.
On same server I have this ratio:

Insrt:Prune    1.96:1

Inserts more then deletes from cache?.. But - is it bad? Or good? What is adequate value?

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 212     |
| Qcache_free_memory      | 460208  |
| Qcache_hits             | 1292474 |
| Qcache_inserts          | 2773956 |
| Qcache_lowmem_prunes    | 1417292 |
| Qcache_not_cached       | 9557843 |
| Qcache_queries_in_cache | 872     |
| Qcache_total_blocks     | 2251    |
+-------------------------+---------+
8 rows in set (0.09 sec)

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)


texts: Tools to visualize adhoc multi dimentional data

texts:
With SSMS I cannot run a MDX query such as

Select [Measures].[Internet Average Sales Amount] on 0,
[Product].[Category].children on 1,
Product.[Model Name].children on 2
From [Adventure Works];

since it supports only 2 axes and hence gives the error message

Results cannot be displayed for cellsets with more than two axes.

However SSAS itself supports 128 axes I believe.

What can I use to run ad hoc queries against SSAS with more than 2 axes?


texts: Optimized date compare in WHERE clause, convert or datediff=0?

texts:
In my query I am comparing two dates in the WHERE clause
once using CONVERT:
CONVERT(day,InsertedOn) = CONVERT(day,GETDATE())

and another using DATEDIFF:
DATEDIFF(day,InsertedOn,GETDATE()) = 0

Here are the execution plans
For 1st one using CONVERT

For 2nd one using DATEDIFF

The datatype of InsertedOn is datetime.
Which one is more optimized?


texts:
I am attempting to deny a certain schema from a particular User Group.  To do this I used the statement "DENY SELECT ON SCHEMA" statement and it was successful in denying access to the tables with that schema.

However, when testing with a view that connected to the denied schema the affected role was still able to pull the data we were attempting deny them.

To help explain, I ran this:

DENY SELECT ON SCHEMA::[phi] TO [Group1]

And was successful in blocking this:

select * from phi.NewTable

But this was not blocked (which ran the query immediately above this statement):

select * from dbo.newview

Is there a way to prevent a view from showing data from a schema that should be denied to someone?


texts: How to trust trust a document in access via PowerShell so I can trigger my database export marcos

texts:
Long story short I'm working on using PowerShell to automate a nightly DB conversion from access to PostgreSQL.

In it's more basic form the script does the following.

Copies the .accdb file to a local folder
Triggers a macro in the .accdb that exports the data to Postgres

The code looks something like this. File locations and names changed. Some database cleanup macros omitted since the extra steps aren't really relevant and are subject to the same problem.

$CopyLocation = "C:\Copy.accdb"
$SourceLocation = "C:\Source.accdb"
Add-Type -AssemblyName Microsoft.Office.Interop.Access
$MsAccess = New-Object -ComObject Access.Application
$MsAccess.OpenCurrentDatabase($CopyLocation)
$MsAccess.Run("ExportToPostgreSQL")
$MsAccess.Quit()

So the issue I'm having is between the copy and the macro execution. Since the document isn't "Trusted" the execution of the macro fails. If I run the script up to the copy, then manually open access and click the enable content button. I can then trigger the macro from powershell.

Is there a way I can trust the document and enable to content from within powershell?


texts: How is MySQL using an index when doing a case sensitive comparison?

texts:
I noticed that Ruby on Rails (ActiveRecord) was using the BINARY operator for a case sensitive comparison.  Yet, when I look at the EXPLAIN for this, MySQL can still use the appropriate index for a case insensitive column.  How is that possible?  Are all text columns indexed case sensitivly?

Without BINARY operator:

  select_type: SIMPLE
        table: my_table
         type: const
possible_keys: my_table_unq,my_table_idx
          key: my_table_unq
      key_len: 771
          ref: const,const
         rows: 1
        Extra: Using index

With BINARY operator:

  select_type: SIMPLE
        table: my_table
         type: range
possible_keys: my_table_unq,my_table_idx
          key: my_table_unq
      key_len: 771
          ref: NULL
         rows: 1
        Extra: Using where; Using index

Looks like the later is using a range.


texts: How to alter/migrate the schema of a table with tons of records

texts:
I have a table which has about 1 million records, and recently i'm going to ship a new feature which requires 2 new fields. But I found it takes about minutes to get it finish, and during the migration, i have to shutdown the service, as the table was locked.

Is there any ideal way to do such kind of jobs without making the online service offline?

Thanks.


texts: MS Access- Attachments to SQL Server -Images or Varchar(max)

texts:
I am trying to move a database from MS Access to SQL Server 2014 and I am using SSMA for Access. A few of the tables have attachments (pdfs, jpgs, emails, etc.).

When I move it to SQL Server, the attachments show up as File Names:

Is there anyway to do this from MS Access side or SQL Server side?

I downloaded all the files to my system and was thinking of matching the names with the files in the folder to upload them to the database through SQL, but then there are multiple files for one row in some instances, so basically I'm not sure how to go about it.


texts: Adding two more columns to existing script (backup summary)

texts:
Can anyone help me to add two more columns to the existing code? I am not a core developer. I want backup_size in GBs and name from msdb.dbo.backupset table, where msdb.dbo.backupset.name = **'XXXXXXX'**. I need this because we are using a third party tool for backups. That tool name is reflecting in msdb.dbo.backupset name column, so I want to filter by that name. Please see code below.

SELECT name,
       recovery_model_desc,
       state_desc,
       d AS 'Last Full Backup',
       i AS 'Last Differential Backup',
       l AS 'Last log Backup'
 FROM (SELECT db.name,
              db.state_desc,
              db.recovery_model_desc,
              type,
              backup_finish_date
        FROM   master.sys.databases db
         LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
       ) AS Sourcetable
 PIVOT (MAX(backup_finish_date) FOR type IN ( D, I, L )) AS MostRecentBackup


texts: What could be the possible reasons for cardinality estimates being very inaccurate in an execution plan?

texts:
I have a query that is selecting some data out of a table. The execution plan shows that an INDEX (FAST FULL SCAN) is used and the cardinality is 22.

On the running the query it gives 227,652 rows. I understand that query optimiser relies on row count estimates instead of actually counting rows.

So I run a select table_name, num_rows from dba_tables where table_name = 'reservations';

And it gives 910,087. So why is the cardinality estimate in query plan off by such a large margin?

Query as request in comments:

SELECT COUNT(*) FROM reservations WHERE length(office_number=2) and length(regexp_substr(text, '\d*')) = 3 and indicator_flag is null;

create index res_idx on reservations (office_number, text, indicator_flag);


texts: Is this table really in first normal form (1NF)?

texts:
Does this table comply with 1NF, even if CourseNo and StudentNo?

Student(StudentNo, StudentName, Major, CourseNo, CourseName,
InstructorNo, InstructorName, InstructorLocation, Grade)


texts: storing query results in a variable in mysql and using it in another query

texts:
I have a following query which runs perfectly fine:

SELECT count(*) as total,l.state as state
            FROM
                 db1.table1   cd
                 inner join db2.table2 l ON
                LEFT(cd.threestring_vch, 3) = l.NPA
            AND
                MID(cd.threestring_vch, 4,3) = l.NXX
            WHERE
               starttime_dt >= '2014-12-18 00:00:00'
            AND
               starttime_dt <= '2014-12-18 23:59:59'
            AND
                Result_int = 76
            GROUP BY
                l.state

However, when I try to do the following and run the query, I get and error:

          Error Code: 1241. Operand should contain 1 column(s)  0.016 sec

Not sure, what is wrong here as I am referring to one related post which says to do what I am doing right now.

SET @v1 := (SELECT count(*) as total,l.state as state
            FROM
                 db1.table1   cd
                 inner join db2.table2 l ON
                LEFT(cd.threestring_vch, 3) = l.NPA
            AND
                MID(cd.threestring_vch, 4,3) = l.NXX
            WHERE
               starttime_dt >= '2014-12-18 00:00:00'
            AND
               starttime_dt <= '2014-12-18 23:59:59'
            AND
                Result_int = 76
            GROUP BY
                l.state);

Here is my ultimate goal:

I want to use the value returned by above query in above variable v1 like the following:

SELECT state , SUM(total) as TOTAL

FROM  v1
GROUP BY state
ORDER BY state ASC

So basically  I will be doing UNION of above two queries.

Please let me know what I am doing wrong.

Thanks


texts: Difference between Sharding And Replication on MongoDB

texts:
I am just confused about how Sharding and Replication work.
According to the definitions I found in the documentation:

Replication: A replica set in MongoDB is a group of mongod processes that maintain the same data set.
Sharding: Sharding is a method for storing data across multiple machines.

As per my understanding if I have 75 GB of data then by using replication (3 servers), it will store 75GB data on each server means 75GB on Server-1, 75GB on server-2 and 75GB on server-3. (correct me if I am wrong).
And by using sharding, it will be stored as 25GB data on server-1, 25Gb data on server-2 and 25GB data on server-3. (Right?).
But then I encountered this line in the tutorial:

Shards store the data. To provide high availability and data
consistency, in a production sharded cluster, each shard is a replica
set

As a replica set is 75GB in size, but shard is 25GB in size, then how can they be equivalent?
This makes me quite confused. I think I am missing something obvious. Please help me with this.