texts:
This is less a question than just a "beware." Thankfully MariaDB 10.2 is an old version, but it does some funky things when joining to nested derived tables. I updated my database from MariaDB 10.0.35 to 10.2.12 and this bug popped up. I tested the same setup and query on 10.0.35, 10.2.12, and 10.4.17 and got different results.
Here's the setup:
CREATE TABLE `users`
(
    `id`       int AUTO_INCREMENT PRIMARY KEY,
    `username` varchar(128) NULL
) CHARSET = `utf8`;

INSERT INTO `users` (`id`, `username`)
VALUES (6171, 'user01'),
       (6172, 'user02'),
       (6880, 'user03'),
       (7184, 'user04'),
       (7185, 'user05'),
       (7186, 'user06'),
       (7187, 'user07'),
       (7188, 'user08'),
       (7190, 'user09'),
       (7874, 'user10'),
       (9307, 'user11'),
       (9585, 'user12'),
       (12918, 'user13');

CREATE TABLE `addons_users`
(
    `id`         int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`    int        NULL,
    `addon_id`   int        NULL,
    `active`     tinyint(1) NULL,
    `start_date` datetime   NULL,
    `end_date`   datetime   NULL
) CHARSET = `utf8`;

INSERT INTO `addons_users` (`id`,
                            `user_id`,
                            `addon_id`,
                            `active`,
                            `start_date`,
                            `end_date`)
VALUES (623, 7185, 1, 0, '2018-10-19 22:56:01', NULL),
       (624, 7185, 1, 0, '2019-10-19 22:56:01', NULL),
       (625, 7185, 1, 1, '2020-10-19 22:56:01', NULL),
       (626, 7186, 1, 0, '2020-04-19 22:56:01', NULL);

Here's the query:
SELECT `users`.`id`, `users`.`username`, `addons_users`.`active`
FROM `users`
         LEFT JOIN (SELECT *
                    FROM `addons_users`
                    WHERE `id` IN (SELECT MAX(`id`) AS `id`
                                   FROM `addons_users`
                                   GROUP BY `user_id`)) AS `addons_users_unique_current`
                   ON `addons_users_unique_current`.`user_id` = `users`.`id`;

And here are the results:
MariaDB 10.0.35                 MariaDB 10.2.12                MariaDB 10.4.17
+-------+----------+--------+   +------+----------+--------+   +-------+----------+--------+
| id    | username | active |   | id   | username | active |   | id    | username | active |
+-------+----------+--------+   +------+----------+--------+   +-------+----------+--------+
|  7185 | user05   |      1 |   | 7185 | user05   |      1 |   |  7185 | user05   |      1 |
|  7186 | user06   |      0 |   | 7186 | user06   |      0 |   |  7186 | user06   |      0 |
|  6171 | user01   |   NULL |   +------+----------+--------+   |  6171 | user01   |   NULL |
|  6172 | user02   |   NULL |                                  |  6172 | user02   |   NULL |
|  6880 | user03   |   NULL |                                  |  6880 | user03   |   NULL |
|  7184 | user04   |   NULL |                                  |  7184 | user04   |   NULL |
|  7187 | user07   |   NULL |                                  |  7187 | user07   |   NULL |
|  7188 | user08   |   NULL |                                  |  7188 | user08   |   NULL |
|  7190 | user09   |   NULL |                                  |  7190 | user09   |   NULL |
|  7874 | user10   |   NULL |                                  |  7874 | user10   |   NULL |
|  9307 | user11   |   NULL |                                  |  9307 | user11   |   NULL |
|  9585 | user12   |   NULL |                                  |  9585 | user12   |   NULL |
| 12918 | user13   |   NULL |                                  | 12918 | user13   |   NULL |
+-------+----------+--------+                                  +-------+----------+--------+

As you can see, 10.2 treats the LEFT JOIN like an INNER JOIN.


texts: Logical reads amount difference with almost identical indexes

texts:
I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server.
The mentioned query is quite simple, and has the following shape>

set statistics io on
set statistics time on

SELECT t.field3
FROM Table t --WITH(INDEX(Index2))
WHERE t.field1 = 6 AND t.field2 = 'B'
AND t.field3 >= CAST ('01-07-2012' As DateTime) AND t.field3 < CAST ('01-10-2012' As DateTime)
ORDER BY t.field3

If I run this query on Table t (which contains 52M rows) the statistics retrieved are:

Logical reads: 95
CPU time: 15 ms
Elapsed time: 6 ms

If I check the query's real execution plan it shows a 100% of an Index Seek on Index1, which is non clustered and contains fields field1, field2, field3 in this order.
However, if I uncomment the line to force using Index2 instead, which contains the same fields in same order, but it is clustered (primary key), the statistics are:

Logical reads: 1456
CPU time: 15 ms
Elapsed time: 12 ms

Although total time spent is not incredibly worse, I would like to understand what is happening here. Can anyone give me an explanation? Is there a performance difference apart from little time difference?

Thank you very much !!


texts:
I'm looking into adaptive compression. I can't seem to find any information on how HADR is affected when a table is compressed and then reorg:ed. Example

t=mytable
db2 "alter table $t compress yes adaptive";
for i in $(db2 -x "select rtrim(indschema) || '.' || rtrim(indname) from syscat.indexes where rtrim(tabschema) || '.' || rtrim(tabname) = '${t}' order by 1"); do
    db2 "alter index $i compress yes"
done

db2 "reorg table $t resetdictionary";
db2 "reorg indexes all for table $t"
db2 "runstats on table $t with distribution and sampled detailed indexes all";

Question is whether I need to bring down HADR during this process and restart it once finished, or is everything captured in the log, so that HADR can continue?


texts: determine how long the "write lock" is going to last

texts:
In a MariaDB Galera cluster composed of 3 nodes when running schema changes, for example, an ALTER and being in TOI(Total Order Isolation) mode it will write lock the full cluster until the schema upgrade finishes.

One way to prevent the global write lock is to run the upgrade in each node by doing:

SET wsrep_OSU_method='RSU';
# run the alter
SET wsrep_OSU_method='TOI';

To find how long it has been running the query I use this:

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' AND info LIKE 'alter%' AND time >= 2 ORDER BY time DESC, id LIMIT 1;

By using the file system and checking the size of the databases I can roughly get an idea in size but not time.

But in any case, how to get the progress,  estimate or calculate the remaining time that the lock will last?


texts: What does "GIN doesn't support full index scans" mean?

texts:
I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be able to retrieve the texts containing a word or two belonging to a pre-set, almost 'constant', list of words.

I believe that the best to do so is to use full text search, based on my own dictionary and configuration, with a GIN or GiST index. That was for some context.

The pgsql doc states:

  GIN doesn't support full index scans.

I don't understand what that means.
Does it mean that if I query the word 'hello' it is possible that a line which text contains the word 'hello' could be not returned?

That would not be great, I'd rather be positive that I get exactly and fully what I want.

Thanks,

Arthur


texts: Handle multiple simultaneous requests Mysql

texts:
sorry for this noob question.
I do have an application which expects about 5000 users accessing it simultaneously, my current database is running on RDS and for each request, a query is called, it takes about 30 milliseconds to be executed.
The main caveat is when we open multiple connections the CPU database spikes to 100% and the app starts to getting timeout error.
What solution would be possible to handle so many requests?
Architecture:

3 EC2 (db.t3.large) running behind a load balancer
1 RDS (db.r5.16xlarge) MySql 5.7
1 Index Created for the AAA table, this table has about 17K rows, after index created the number of rows read was reduced to 6754 rows.
When the connections go to spike it achieves up to 2K connections
The Max connections for this instance type is 10K
The time registered on profiling to execute is 0.03705 seconds (before the index it was 0.7 seconds)

SELECT Count(*)
FROM   (SELECT `AAA`.id,
               Degrees(Acos(-0.55754389032929 * Sin(Radians(ZZZ.latitude)) +
                                    0.83014746301876 * Cos(
                                    Radians(ZZZ.latitude)) *
                                    Cos(Radians(151.211111 -
                            ZZZ.longitude)))) * 60 * 1.1515 * 1.609344 AS
               distance
        FROM   `AAA`
               LEFT JOIN 'ZZZ'
                      ON `AAA`.`userid` = 'ZZZ'.`id`
               LEFT JOIN 'BBB'
                      ON 'ZZZ'.`id` = 'BBB'.`userid`
               LEFT JOIN 'CCC'
                      ON 'ZZZ'.`id` = 'CCC'.`userid`
               LEFT JOIN 'EEE'
                      ON 'CCC'.`badgeid` = 'EEE'.`id`
               LEFT JOIN 'FFF'
                      ON 'ZZZ'.`id` = 'FFF'.`userid`
        WHERE           ( ( `AAA`.`active` = 1 )
                 AND ( `AAA`.`approved` = 1 )
                 AND ( 'ZZZ'.`active` = 1 )
                 AND ( 'ZZZ'.`verified` = 1 ) )
               AND ( St_contains(St_geomfromtext( 'POLYGON((151.30120109009 -33.97620109009, 151.30120109009 -33.79602090991, 151.12102090991 -33.79602090991, 151.12102090991 -33.97620109009,151.30120109009 -33.97620109009 ))'), AAA.`position`) )
 GROUP  BY `AAA`.`id`
 HAVING distance < 10) `c`;


texts:
In a Parent and Child relationship, I'm trying to return Parents that only have one Child and a where clause applied to the only child firstname like 'tom'

t-sql pseudo: parents with only one child and that child's name is tom

select p.*

from parents p
  inner join children c on c.parentId = p.id

where count(c.id) = 1 -- ??? with only 1 child ???
  and c.firstname like 'tom'

Any ideas?


texts:
I am trying to write a process to run several prewritten Queries in a row without any human interaction. I tried to write a macro, but the after saving it it still prompted for the date range in the query. The people who came before me,  created a switchboard form with the correct date ranges on it. Is there a way to pull the dates with VBA and run the query without any user interaction. I am new to VBA. and have tried everything i know and have found in the on. I asked the question on stack exchange and they had some sql i did not understand and did not work. I keeping error messages when i run the code from below. Any help would be great i am at a standstill. I am using access 2010 Thanks for your time.

Sub ModQueryDefs_OpenPramaeters()

Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim rst As DAO.Recordset
Set db = CurrentDb

DoCmd.OpenForm "frmSwitchboard"
[Forms!] [frmSwitchboard]![txtDateStart] = 11 / 5 / 2012
[Forms!] [frmSwitchboard]![txtDateEnd] = 11 / 11 / 2012

Set qdef = db.QueryDefs("query")
Dim param As DAO.Parameter
For Each param In qdef.Parameters
Debug.Print param.Name; param.Value
param.Value = Eval(param.Name)

Next
Set rst = qdef.Openrecordset()
Do While Not rst.EOF
Debug.Print rst!Company
rst.MoveNext
End Sub


texts: Converting Heap to MyISAM issue on temp table defined as innodb

texts:
I was having an issue on the aws aurora brand of mysql 5.6.  I was creating temp tables with the memory engine from some selects ... ie

create temporary table mytemp table engine memory as select * from table

Some of my queries end up creating a temptable with a few hundred thousand rows (three int columns) and in the process enter a state of "converting heap to MyISAM" which I've read is horrible for performance especially when I am using the temp tables as part of a bigger join against innodb tables later in the mysql session.  (convert to myisam, then convert back to innodb to join etc)

So, after some research I changed all my temp table engines to use innodb thinking this would resolve the problem...

create temporary table mytemp table engine innodb as select * from table

When I watch the queries go by after the change to innodb I see that some are still entering a state of "converting heap to MyISAM" .  I was under the understanding that if I define the temp table as innodb that this would not happen.

Is there something I'm doing wrong here?  IF this is a performance problem, what is the best way to deal with what I'm seeing?


texts: How to exceute view without DBLink in oracle server

texts: I have 2 views, example V1 from Database  DB1 , V2 from database DB2, these 2 database server are remotely configured, I can able to execute these views separately without database link in the respective database server. Now i want to join these views and want to execute it without database link. How do I achieve it?


texts: Resources for understanding SQL Server locking and concurrency?

texts:
As demonstrated by a recent question of mine locking and concurrency are HARD.

Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that would, properly learned, enable better navigation of all the inherent pitfalls in this area?

I'm thinking of all kinds of resources--tutorials, blogs, manual pages, PASS sessions, or anything.


texts: Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

texts:
I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in execution time, it just suddenly spikes.

The only way to fix it is to update the statistics; I usually delete the query plans at the same time. It then goes back to normal.

Some of the indexes were wrongly created with STATISTICS_NORECOMPUTE=ON at the beginning. Since changing them to OFF, the issue happens less frequently. However, when I look at the indexes and table-creation SQL, nothing is unusual and nothing is unusual about the system. There's new rows being added daily but nothing unusually large or small. I read that statistics only update the 20%+500 of the table has been changed, but I have other similar databases running without issue.

So, if I scheduled the update of statistics every few days at midnight, am I treating the symptom whilst ignoring the elusive cause? Is it bad practice or is this standard database maintenance?


texts: Query runs slowly when a non-indexed column is added to the WHERE clause

texts:
My SQL Server houses a logs table that has 4 columns:

RowID - GUID PRIMARY KEY
OperationID - GUID (NONUNIQUE INDEXED)
Line - INT
Message VARCHAR(250)

Some table facts:

An operation may have anywhere between 1 and 380,000 messages
operationID is the same for all log lines that pertain to a particular operation
Line increments from 1 for each operationID (hence the combination of operationID and Line is unique- I don't know why the original table designer added a GUID PK)
The message varies considerably
A nonclustered nonunique index is present on OperationID. It INCLUDEs no other columns
The table has about 13.5 million rows

I find the following query reasonably performant, it returns 7000 rows in 5 seconds or less. The query plan shows index seek for all rows matching the operationID, key lookup on the primary key and then nested loops to produce the resultset:

SELECT *
FROM logs
WHERE operationid = <some guid>

Side info: SQL Server Management Studio complains that the query would be better served if an index were created on OperationID that INCLUDEd line and message.

If I add something that I consider trivial:

SELECT *
FROM logs
WHERE operationid = <some guid> AND Line > 2000

Suddenly the query takes well over 3 minutes to return a result. My immediate thought was to look at the execution plan differences. The only significant difference I find is that in addition to the "seek predicate" that is associating the table rows with index rows, there is a "predicate" section in the tooltip of the table for the nonperformant query. The "predicate" is listed as "line > 2000".

Side info: SQL Server has switched to complaining that the query would be better served by indexing OperationID and Line and INCLUDEing Message.

Is SQL Server doing things in the wrong order? i.e. is it looking through 13.5 million rows for all those whose Line > 2000, and then filtering and joining to the index?

As a human I can see this query should be sub-5-second, because filtering all the rows based on the index first (13.5M -> 7K) and then filtering 7K rows for the matching Line would be a better strategy, than finding potentially millions of rows with line > 2000. I was expecting that filtering any large table on an indexed column is something SQL Server would prefer over starting with non indexed columns in a where clause?

I don't really need an index on Line, as we don't select entries based on line ranges in common use - this is just for code development, I know for this particular operation I'm analysing now that all line entries before 2000 are uninteresting. I'm more interested as to why a query WHERE indexedcol = value is super fast but WHERE indexedcol = value and unindexedcol = othervalue runs like a dog. And by that I don't mean a greyhound.


texts:
I am just check if my table has Cluster Index or not by running this query

Table Design

CREATE TABLE [dbo].[postTB](
[postID] [bigint] IDENTITY(1,1) NOT NULL,
[slug] [nvarchar](300) NULL,
[title] [varchar](150) NOT NULL,
[postContent] [nvarchar](max) NULL,
[createDate] [datetime] NULL,
[publishDate] [datetime] NULL,
[userID] [bigint] NOT NULL,
[categoryID] [bigint] NOT NULL,
[isShow] [bit] NOT NULL,
[isApproved] [bit] NULL,
[websiteID] [int] NULL,
[miniPostContent] [nvarchar](300) NULL,
[modifedDate] [datetime] NULL,
[pageTitle] [nvarchar](300) NULL,
[pageKeyword] [nvarchar](300) NULL,
[pageDescribtion] [nvarchar](300) NULL,
[CombinedTags] [nvarchar](300) NULL,
[blogImage] [nvarchar](50) NULL,
[postBlogType] [nvarchar](50) NULL,
[postTag] [nvarchar](100) NULL,
[firstname] [nvarchar](50) NULL,
[lastname] [nvarchar](50) NULL,
[contactemail] [nvarchar](50) NULL,
[viewcount] [bigint] NULL CONSTRAINT [DF_postTB_viewcount_1]  DEFAULT ((1)),
[blogCategoryTypeID] [int] NULL CONSTRAINT [DF_postTB_blogCategoryTypeID]  DEFAULT ((0)),
CONSTRAINT [PK_postTB] PRIMARY KEY CLUSTERED
(
[postID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am executing this

  Execute sp_helpindex [postTB]

But the result is

  The object 'postTB' does not exist in database 'cwpros' or is invalid for this operation.

Please help how to properly create cluster if not exist then check using above command?


texts: How to install SQL Server Express 2005 with Full text search

texts:
I am in need to Install SQL server 2005 Express edition with Full Text Search capability. I don't know what to download and the order of installing the Database management system. Could someone provide me link to download the version with Full Text Search [as default 38Mb setup file does not have this feature].
Also step by step instruction to enable Full Text Search during the installation.


texts: pg_stat_statements empty with administrator user and unusable for other users

texts:
I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed.

portaldb=> SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.4             | 1.4               | track execution statistics of all SQL statements executed
(1 row)

portaldb=# SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';
        name        | version | installed | superuser | relocatable | schema | requires |                          comment
--------------------+---------+-----------+-----------+-------------+--------+----------+-----------------------------------------------------------
 pg_stat_statements | 1.4     | t         | t         | t           |        |          | track execution statistics of all SQL statements executed
(1 row)

The extension is enabled on the portaldb database

portaldb=> create extension pg_stat_statements;
ERROR:  extension "pg_stat_statements" already exists

I was considering removing it and then recreating it but I feel a bit scared to apply a cascade drop, because this is a big e-commerce portal and we don't want lose data or cause any outage.

portaldb=# DROP EXTENSION pg_stat_statements;
ERROR:  cannot drop extension pg_stat_statements because other objects depend on it
DETAIL:  function get_pg_stats() depends on type pg_stat_statements
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

If I try with the user that manages the database we receive the following error:

portaldb=> SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: SELECT * FROM pg_stat_statements ORDER BY total_time DESC;

However if we execute the same query under root (postgres user) we receive a blank output.

portaldb=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
 userid | dbid  |  queryid   |

portaldb=#

root@Serv01:~# cat /app/postgres/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements'
root@Serv01:~# cat /app/postgres/postgresql.conf | grep pg_stat_statements.
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on

Has anyone else come across this situation? I Googled but I didn't find any related case.


texts: AWS DMS Task getting failed after completion with error as AlwaysOn BACKUP-ed data is not available

texts:
I have created DMS task for migrating data from one RDS MS SQL Server instance to another RDS MS SQL Server instance with "Full load + ongoing replication", and data copied to target instance but status showing as failed with below error -

Last Error AlwaysOn BACKUP-ed data is not available Task error
notification received from subtask 0, thread 0
[reptask/replicationtask.c:2822] [1020465] Error executing source
loop; Stream component failed at subtask 0, component
st_0_NJZW5VSVPASASA4E4N2SJGTVEZ3UIDLJHX3NDY; Stream component
'st_0_NJZW5VSVASASAZ4E4N2SJGTVEZ3UIDLJHX3NDY' terminated
[reptask/replicationtask.c:2829] [1020465] Stop Reason FATAL_ERROR
Error Level FATAL

Cloud watch logs:

2021-01-04T15:24:02 [SOURCE_CAPTURE  ]E:  Failed to access LSN '0000009a:00000087:0010' in the backup log sets since BACKUP/LOG-s are not available. [1020465]  (sqlserver_endpoint_capture.c:717)
2021-01-04T15:24:02 [TASK_MANAGER    ]I:  Task - PHTLF2WXLFVWESX4GLZ2CKAW3MY76HLRXHIYYYY is in ERROR state, updating starting status to AR_NOT_APPLICABLE  (repository.c:5103)
2021-01-04T15:24:02 [SOURCE_CAPTURE  ]E:  Error executing source loop [1020465]  (streamcomponent.c:1867)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Stream component failed at subtask 0, component st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY [1020465]  (subtask.c:1409)
2021-01-04T15:24:02 [SOURCE_CAPTURE  ]E:  Stream component 'st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY' terminated [1020465]  (subtask.c:1578)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Task error notification received from subtask 0, thread 0 [1020465]  (replicationtask.c:2822)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Error executing source loop; Stream component failed at subtask 0, component st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY; Stream component 'st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY' terminated [1020465]  (replicationtask.c:2829)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Task 'PHTLF2WXLFVWESX4GLZ2CKAW3MY76HLRXHIYYYY' encountered a fatal error  (repository.c:5196)

What is the issue? any suggestions!


texts: Error during pg_restore: text search dictionary "unaccent" does not exist

texts:
I'm trying to move data between servers. I recreated the whole database structure, checked that same users exist and the unaccent extension that we use is enabled for all schemas of the target database.

When I try to run:

pg_restore -h %server_host% -a -c --disable-triggers -d %db_name% -U %user_name% 2014-12-09.custom

I get the following error:

pg_restore: [archiver (db)] COPY failed for table "contracts":
ERROR:  text search dictionary "unaccent" does not exist

What am I missing here?


texts: Why the MySQL query for where clause is very slow?

texts:
I was running a query, the rows of the table are about 10 millions:

select * from zhihu.zhihu_answer_meta  where voteup_count>100000 limit 5;

However, it took more than 20 mins to finish.

I also tried other condition and found that it ran fast (in a second) with small number in where clause:

select * from zhihu.zhihu_answer_meta  where voteup_count>10 limit 5;
select * from zhihu.zhihu_answer_meta  where voteup_count>100 limit 5;

the DDL is as below:

CREATE TABLE `zhihu_answer_meta` (
  `answer_id` bigint(20) NOT NULL,
  `author_name` varchar(500) DEFAULT NULL,
  `reward_member_count` int(11) DEFAULT NULL,
  `answer_created` datetime DEFAULT NULL,
  `voteup_count` int(11) DEFAULT NULL,
  `is_advertiser` tinyint(1) DEFAULT NULL,
  `author_url_token` varchar(500) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `is_org` tinyint(1) DEFAULT NULL,
  `question_type` varchar(100) DEFAULT NULL,
  `question_created` datetime DEFAULT NULL,
  `author_type` varchar(100) DEFAULT NULL,
  `insert_time` datetime DEFAULT NULL,
  `badge_num` int(11) DEFAULT NULL,
  `answer_updated` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `reward_total_money` int(11) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL,
  `can_comment` tinyint(1) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  UNIQUE KEY `answer_index` (`answer_id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So why is it slow and is there any way to optimize?

Thanks for any advice.


texts: What are these SFP files that SQL Server distributes?

texts:
What are the SFP files that come with SQL Server 2017?

/opt/mssql/lib/sqlservr.sfp
/opt/mssql/lib/sqldkxplat.sfp
/opt/mssql/lib/secforwarderxplat.sfp
/opt/mssql/lib/system.common.sfp
/opt/mssql/lib/system.certificates.sfp
/opt/mssql/lib/system.netfx.sfp
/opt/mssql/lib/system.sfp


texts: How to create a non-sa SQL Server login / access without control server permission

texts:
I have SQL Server, and I need to give access to other users to run basic queries (just select on 1 database).

I have created a login for a user, and have amended their permissions, but once I deny access to "Control Server" that login can no longer connect.

I am totally stuck as have no experience here, but must complete this task. Please can someone help with what the next step is?

p.s. it can't be hire someone that knows what they are doing :-)

Cheers


texts: How to synchronize a Windows database for Linux?

texts:
I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application.

Is it possible to synchronize?


texts:
I am new to MySQL, but I have to solve this problem quickly.

I installed FileRun (which is a file share service) on two nodes, those two nodes have MySQL Cluster backend.

In Filerun GUI, I created a new user on the primary node, but the secondary one couldn't get the data. However, if I manually create a table and insert a data in the filerun database, the synchronise can proceed.

Here is what I did:

On both nodeA and nodeB:

mysql> use filerun;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
+----+-----------+
1 row in set (0.00 sec)

On nodeA, I created a new user via Filerun GUI, I query again and can see the new user has been added:

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
|  9 | test      |
+----+-----------+
2 rows in set (0.00 sec)

However, on nodeB, the table was still not updated.

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
+----+-----------+
1 row in set (0.00 sec)

For the debug purpose, I created a new table on nodeA in the same database:

mysql> create table students (name VARCHAR(10));
Query OK, 0 rows affected (2 min 0.82 sec)

mysql> insert students set name="john";
Query OK, 1 row affected (0.08 sec)

mysql> select * from students;
+------+
| name |
+------+
| john |
+------+
1 row in set (0.01 sec)

Back to nodeB, I can see the data has been transferred:

mysql> show tables;
+-----------------------+
| Tables_in_filerun     |
+-----------------------+
….omitted…..
| students              |
+-----------------------+
1 row in set (0.51 sec)

mysql> select * from students;
+------+
| name |
+------+
| john |
+------+
1 row in set (0.01 sec)

At the same time on nodeB, this event was popped up by MySQL log(only shows the table replication, I don't know why):

2017-09-09T00:40:58.202248Z 1 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$filerun/students
2017-09-09T00:40:58.211230Z 1 [Note] NDB Binlog: logging ./filerun/students (UPDATED,USE_WRITE)

Again, I tried to manually insert a data into the "df_users: table on nodeA:

mysql> insert into df_users (id,name) values("2","Jack");
Query OK, 1 row affected (0.01 sec)

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
|  2 | Jack      |
|  9 | test      |
+----+-----------+
3 rows in set (0.01 sec)

Unfortunately, the nodeB still receiving nothing!

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
+----+-----------+
1 row in set (0.00 sec)

Any idea about this? How to check the transaction between two nodes?

Other information:
Both two nodes has Ubuntu 14.04 installed.
MySQL is installed locally,version: 5.7.18-ndb-7.6.3-log

root@ndb-mgmd:~# ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2    @192.168.0.20  (mysql-5.7.18 ndb-7.6.3, Nodegroup: 0, *)
id=3    @192.168.0.21  (mysql-5.7.18 ndb-7.6.3, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.30  (mysql-5.7.18 ndb-7.6.3)

[mysqld(API)]   2 node(s)
id=4    @192.168.0.20  (mysql-5.7.18 ndb-7.6.3)
id=5    @192.168.0.21  (mysql-5.7.18 ndb-7.6.3)


texts: Can I restore an SQL 2016 database mdf at an SQL 2014 Sp1 instance?

texts: I have a database file .mdf created in SQL server 2016. I am planning to move at a new web hosting provider who supports 2014sp1 version. Should I proceed or this is out of specs? Can I restore an 2016 version at a 2014 one?


texts: mysql.proc keeps crashing, cannot do a mysqldump?

texts:
Due to some problems with InnoDB, I'm going to dump all databases to a new server:

mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server

The dump process stopped with error:

59.9kB assword: 59.9kB
ERROR 145 (HY000) at line 2970: Table './mysql/proc' is marked as crashed and should
    be repaired
 228MB
mysqldump: Got errno 32 on write

I've ran the following command to repair all tables in all databases:

mysqlcheck --auto-repair --all-databases

When I examine the mysql.proc status, I get:

mysql> check table mysql.proc;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.proc | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.02 sec)

However, I still get the same error when re-running the mysqldump command.

I have approximately 2000 store procedures. Can that be the reason for the error?


texts:
I am trying to create a ssh tunnel to my production database /usr/sbin/mysqld  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)).
When using my ssh-tunnel I get in my mysql error log file this single line:
2021-03-02T22:34:33.874444Z 24 [Note] [MY-010914] [Server] Got timeout reading communication packets

My query log shows:
2021-03-02T22:52:59.338491Z    36 Connect   root@localhost on test_db using TCP/IP

On my local machine I get MySQL server has gone away.
I have the following configurations for time related values:
binlog_max_flush_queue_time 0
connect_timeout 90
default_password_lifetime   0
delayed_insert_timeout  300
explicit_defaults_for_timestamp ON
flush_time  0
have_statement_timeout  YES
innodb_flush_log_at_timeout 1
innodb_lock_wait_timeout    50
innodb_old_blocks_time  1000
innodb_rollback_on_timeout  OFF
interactive_timeout 28800
lc_time_names   en_US
lock_wait_timeout   31536000
log_timestamps  UTC
long_query_time 10.000000
max_execution_time  0
mysqlx_connect_timeout  30
mysqlx_idle_worker_thread_timeout   60
mysqlx_interactive_timeout  28800
mysqlx_port_open_timeout    0
mysqlx_read_timeout 30
mysqlx_wait_timeout 28800
mysqlx_write_timeout    60
net_read_timeout    90
net_write_timeout   90
original_commit_timestamp   36028797018963968
regexp_time_limit   32
rpl_stop_slave_timeout  31536000
slave_net_timeout   60
slow_launch_time    2
system_time_zone    CET
time_zone   SYSTEM
timestamp   1614724734.545546
wait_timeout    28800

For package related values I got the following:
max_allowed_packet  1073741824
mysqlx_max_allowed_packet   67108864
slave_max_allowed_packet    1073741824

For thread I have:
create_admin_listener_thread    OFF
innodb_log_writer_threads   ON
innodb_parallel_read_threads    4
innodb_purge_threads    4
innodb_read_io_threads  4
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_write_io_threads 4
max_delayed_threads 20
max_insert_delayed_threads  20
myisam_repair_threads   1
mysqlx_idle_worker_thread_timeout   60
mysqlx_min_worker_threads   2
performance_schema_max_thread_classes   100
performance_schema_max_thread_instances -1
pseudo_thread_id    32
thread_cache_size   100
thread_handling one-thread-per-connection
thread_stack    286720

My verbose log level is:
log_error_verbosity 3
show_create_table_verbosity OFF

To be honest I tried a lot in changing my values specifically, max_allowed_packages, wait_timeout and connect_timeout.
Any suggestions how to find out which value is the problem?
I appreciate your replies!


texts: Script that alters all stored procedure on the database

texts:
I have a database with thousands of stored procedures and I need to alter hundreds of them.

In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds some code at the beginning (if needed).

For example, to write a script that adds SET NOCOUNT ON if not exists. So that it will alter all the stored procedures that does not have it yet.

Does it possible? If yes, can you give me some tips, guides?

I haven't tried nothing yet because I don't have any direction for doing it.

Thanks.

Solved

Thanks to tpet suggestion I solved it. Maybe not the best way, and I should add some more code so it will be more safe.

DECLARE @HelpText TABLE
(
    Val NVARCHAR(MAX)
);

DECLARE @sp_names TABLE
(
    ID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(128)
);

DECLARE @sp_count INT,
        @count INT = 0,
        @sp_name NVARCHAR(128),
        @text NVARCHAR(MAX);

INSERT  @sp_names
SELECT  name
FROM    sys.Procedures;

SET @sp_count = (SELECT COUNT(1) FROM sys.Procedures);

WHILE (@sp_count > @count)
BEGIN
    SET @count = @count + 1;
    SET @text = N'';

    SET @sp_name = (SELECT  name
                    FROM    @sp_names
                    WHERE   ID = @count);

    INSERT INTO @HelpText
    EXEC sp_HelpText @sp_name;

    SELECT  @text = COALESCE(@text + ' ' + Val, Val)
    FROM    @HelpText;

    DELETE FROM @HelpText;

    IF @text LIKE '%SET NOCOUNT ON%'
    BEGIN
        SELECT @text;
    END
    ELSE --Not found, should be added.
    BEGIN
        SET @text = REPLACE(@text, 'CREATE PROCEDURE', 'ALTER PROCEDURE');

        DECLARE @Find NVARCHAR(255);
        SET @Find = 'BEGIN';

        SET @text = STUFF(@text, CHARINDEX(@Find, @text), LEN(@Find), @Find + CHAR(13) + CHAR(10) + SPACE(4) + 'SET NOCOUNT ON;');

        EXECUTE sp_executesql @text;
    END
END


texts: Can sqlite dump database schema in a fixed order?

texts:
I am using SQLiteStudio 3.0.7 to dump the database schema regularly for versioning purposes and it seems to change the order of the table definitions at random. This wreaks havoc on the diffs as even a small change like adding one or two additional columns seems to result in wild diffs all over the place. A side by side comparision can result in a whole table being list out separately when it is just the line difference that should show.

Can SQLiteStudio be configured to do that correctly., or can sqlite itself do the same schema export in a fixed formatted manner?


texts: Does SHOWPLAN permission toggle exist in SSMS

texts:
I'm using SQL Server 2008 R2.

Is there an option in SSMS 2016 to toggle SHOWPLAN permissions for logins (I know this is issued at the database level)?

Is there a query to see if a user, other than myself, has SHOWPLAN permissions?

I know of fn_my_permissions, but am looking for something similar that can be ran against another user account. For security reasons I am unable to use EXECUTE AS


texts: Including count of a result in the main query?

texts:
Currently we execute two queries to get the count and result with a pagination filter. While we can easily combine both of these into a single network call (using sql seprators), is there a way to do this in a single query following the DRY principle?

-- count
SELECT COUNT(*) FROM table;

-- result with pagination
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
WHERE row >= 1 AND row <= 10;

It is really challenging to maintain both queries - single query reduces the maintenance overhead.


texts:
I want to get a random row from my table by id.

My table:

ID|Word     |Dificult|Category_id|
1 |'Dumb'   |'Easy'  | 3         |
2 |'Leopard'|'Medium'| 6         |

If my user selects a category and a difficult, I have pick a random word with user parameters, like:

idRaffle := raffle.id;
   -- raffle.id = An id that postgres will bring me by some raffle function.
d := 'Easy';
c := 3;
select * from words where id=idRaffle and Dificult=d and Category_id=c

raffle.id

I don't know how to get that random row id (raffle.id).

Note that it must follow the user's selection conditions.


texts: Conditional Connection in SSIS based on Availability Group status

texts:
I'm working on an SSIS package that will refresh dev databases from production backups. The package has been in place for years, but we're deploying Availability Groups on the dev instance, so the tasks have to be changed to work with both the primary and secondary replicas.
Background: All instances are SQL Server 2014 SP2. One production instance (PD1), two dev instances (DV3 & DV7) and one utility instance (DV1), where the SSIS package is deployed to SSISDB and where the SQL Agent job runs.
In the SSIS package, after the full backups are obtained and put on a network share from production, the next set of tasks (in a separate SSIS package) needs to figure out which node hosts the primary replica. I'm following the steps listed here to go about this work, and everything works great if it's hard-coded (which, of course won't work if/when a failover occurs).
The only way I've been able to think about approaching this is, at runtime, evaluate which instance is primary and then proceed with the tasks (which, by the way are all "Execute SQL Task" objects--requiring their own connection definition). I've seen a few examples of how to use expressions in ConnectionStrings and variables, but can't figure out how to set the value returned from the below query in SQL Server to return the correct instance to set the connection string in the package.

    select cs.replica_server_name
from sys.dm_hadr_availability_replica_states rs
join sys.dm_hadr_availability_replica_cluster_states cs
    on rs.replica_id = cs.replica_id
join sys.dm_hadr_name_id_map n
    on rs.group_id = n.ag_id
where rs.role = 2
and n.ag_name = 'DVAG001'

Ideally, there would be two of these connection managers, one for the primary and one for the secondary, which I could then use in the "Execute SQL Task" objects. Then everything would work perfectly.
There very well may be other ways to do this, but I'm not very experienced in SSIS or development outside of TSQL.

UPDATE: The above process is done every day...which is why I'm using an SSIS package and SQL Agent. So, the whole process has to be automated and have all the values necessary--especially the determination of the replica states (primary and secondary)--inside the package so that no manual intervention is required.


texts:
EDIT:

I know this question is closed, but I hope it will help someone.

The problem is on our disks ( cluster ).

Using PerfMon, I could create some counters ( disk reads and writes ) and the reads counter is fixed on 100%.

I am seeing the following wait in sp_WhoIsActive:

PAGEIOLATCH_SH:Database_Name:1(*)

I see some posts here, saying this warning is because of high I/O. SQL Server Books Online defines the SQL wait type PAGEIOLATCH_SH as:

  Occurs when a task is waiting on a latch for a buffer that is in an
  I/O request. The latch request is in Shared mode.

And this:

  A user process will request some data which is not currently in the
  buffer cache. At that  point – SQL Server will attempt to allocate a
  buffer page – and while the data is moved from disk to buffer cache an
  exclusive PAGEIOLATCH_EX is created on the buffer. At the same time a
  PAGEIOLATCH_SH is created on the buffer from the user process perspective.

I've created index and stats, and also used the SQL Server Profiler to help me.

Is there any way to improve the query?  How can I improve the handling of lots of ANDs?

We have been having this problem for about a week, and I can't figure out what to do.

SELECT TOP 30 codCliente
FROM (
    SELECT t1.CodCliente
        , codcampo
        , valor
        , t1.chavealeat
    FROM tblCliente AS t1 WITH(NOLOCK)
        INNER JOIN tblClienteDetalhe AS t2 WITH (NOLOCK)
            ON t1.codcliente = t2.codcliente
            AND CodCampo IN (-1, 4)
    WHERE codStatus IN(0)
        AND t1.ChavePeriodo < getdate()
        AND t1.CodStatusLigacao = 0
        AND EXISTS (
            SELECT codcliente
            FROM tblclientedetalhe WITH (NOLOCK)
            WHERE codcampo = 3
                AND valor = '2'
                AND codcliente = t1.codcliente
            )
        AND EXISTS (
            SELECT codcliente
            FROM tblclientedetalhe WITH (NOLOCK)
            WHERE codcampo = 6
                AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31'
                AND DateDiff(day,Valor,GetDate()) > 15
                AND codcliente = t1.codcliente
            )
        AND NOT EXISTS (
            SELECT 0
            FROM tblPesquisa WITH (NOLOCK)
            WHERE tblPesquisa.CodCliente = t1.CodCliente
            )
        AND EXISTS (
            SELECT codcliente
            FROM tblclientedetalhe WITH (NOLOCK)
            WHERE codcampo = 4
                AND valor = '161'
                AND codcliente = t1.codcliente
            )
    ) AS Cliente
PIVOT (
    MAX(Valor)
    FOR codCampo in ([4])
    ) AS PivotTable
WHERE ([4] = '161')
ORDER BY chavealeat;

EDIT1:

As i said bellow in comments, there are other queries with this Wait Status.
I'm waiting for a job to finish to be sure it's not the problem.


texts:
I have a PostgreSQL table with id and clusters like this:

CREATE TABLE w (id bigint, clst int);
INSERT INTO w (id,clst)
VALUES
  (1,0),
  (1,4),
  (2,1),
  (2,2),
  (2,3),
  (3,2),
  (4,2),
  (5,4),
  (6,5);

If you aggregate clusters grouped by id, you can see that there are overlapping values in the cluster arrays:

select id, array_agg(clst) clst from w group by id order by id;
 id |  clst
----+---------
  1 | {0,4}
  2 | {1,2,3}
  3 | {2}
  4 | {2}
  5 | {4}
  6 | {5}

i.e. cluster 4 covers id 1 and 5, cluster 2 covers id 2, 3 and 4, whereas cluster 5 corresponds only to one id.

How can I now aggregate ids grouped by cluster arrays overlapping?
i.e. the expected result is:

 id      | clst
---------+-------
 {1,5}   | {0,4,4}
 {2,3,4} | {1,2,3,2,2}
 {6}     | {5}

I don't care much about the cluster column just need ids properly aggregated.

There is no restriction on number of possible overlappings. Number of clusters per id is not restricted either (it can be hundreds or even more). Clusters are assined to ids not sequenctially.

There are millions of rows in the table!!!

Using PostgreSQL 11.


texts: Postgres - returning custom values ie. "true" rather than "t"

texts:
I am wondering if there is a way to show custom "values" in the output of a query purely for readability, similar to the AS statement for renaming column labels.

Seems unlikely but would be useful for cutting down a number of custom post-query formatting steps.

ie.

| type (varchar) | status (boolean) |
| A              | t                |
| B              | f                |

as

| type  | status |
| A     | true   |
| B     | false  |

or

| type  | status     |
| A     | collecting |
| B     | disabled   |

I am aware I could change the database to contain these values, but this is not possible in this case.

Thanks


texts: CPU utilization dangerously high due to COUNT queries

texts:
Our app has recently been receiving traffic up to 10-30 requests per sec. The life cycle of every request is pretty complex. MySQL's load average ranges from 5 (when the requests are 10 per sec) and goes to 20 (30 requests, peak traffic, lasts for a few minutes).

The load average is abysmally high due to extremely high CPU utilization of 200-350%. Now, for every request that we receive, we fire off 3 COUNT queries on a single table. They look pretty similar.Just to show you how one looks like:

SELECT COUNT(DISTINCT COL1,COL2)
FROM conversion_Details c
WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')
AND (COL4 = 0 OR COL4 = 17)
AND COL5=5
AND COL2 = 'SOMETHING'

As you can see, for 1 count query, we have 1 table and 5 columns involved.
In total,we have 3 varchar columns, 2 int columns, and 1 timestamp column.
I would like to add that all of these columns are already indexed, and the table contains about 1 million entries.

Now imagine 2 more queries like this one. These 3 queries are fired in parallel. If I comment these queries out,the load average comes down to less than 1.

Is there anything I can do to optimize my database further? MySQL is currently running on 8GB ram, 4 core, CentOS.


texts: SQL Server + Reporting Services + SSIS + SSAS Licensing and Infrastructure

texts:
Actually I am running 3 environments (Dev / UAT / PROD ) with SQL Server installations on them.

Development server has also Integration Services Service running on it an we are starting to actively using it.

We are thinking also on installing Reporting Service to start using the whole suite provided by Microsoft. And here comes my question.

I'm not really sure if having everything on same server with huge workloads and usage is a good idea and maybe isolating each component on its own server would be the best option. However, how is licensing applied in this case?

Is it possible to have:

1 MSSQL Server with Data Engine and Databases on it.
1 Server with Integration Services on it (Does it need Database or something?)
1 Server with Reporting Services on it (Does it need Database or something?)

You have to license all 3 servers?


texts: Why can't we access SQL Server when a long query is running in Management Studio?

texts:
We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load.

This work fine normally, however when we run a long query from Management Studio, the web page fails to load. It seems database does not respond when the long query is running in Management Studio. Is this a normal behavior?

Web page gives the following error -

Timeout expired.  The timeout period elapsed prior to completion of the operation or
the server is not responding.
Description: An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and where
it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout
period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request.
Information regarding the origin and location of the exception can be identified using
the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to
completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
    breakConnection) +404
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412


texts:
SCCM Current Branch 1706

SQL Server 2014 SP2, AlwaysOn Availability Group enabled

What are the permissions required on the SQL server hosting site database?

Site server account.
SCCM install account.

Based on this link, the document is a bit older.
http://technet.microsoft.com/en-us/library/gg712320.aspx

  Sysadmin rights on the instance of SQL Server that hosts the site
  database. After setup completes, both the user account that runs setup
  and the site server computer account must retain sysadmin rights to
  SQL Server. It is not supported to remove the sysadmin rights from
  these accounts.

Do you know any latest documents about permission?

Any chance these 2 accounts permission can be reduced for operation (after installation done)?


texts: Generate a table that shows a monthly series and the number of tasks done per month

texts:
I'm trying to generate a table that will show the number of tasks that were completed in each month.
The table has the following columns:

id
job_name
job_status
date_completed

My goal is to query the number of completed jobs per month between two values (e.g. 2021-02-01 [Feb 2021] to 2022-02-28 [Feb. 2021]), with the condition that it will only count those with status value '1' (1 = complete, 0 = ongoing) with the query producing this table:
Months   | Jobs Completed
---------+-----------------------
Feb 2020 | 0
Mar 2020 | 2
Apr 2020 | 0
...
Jan 2022 | 1
---------+------------------------

I managed to generate the series and have values to it, but the said table has the following problems:

The order of the months are not arranged in ascending order (From Feb 2021 - Jan 2022, the generated series was random)
The value of the months (the "jobs_completed") copies the highest value, rather than show their own. (e.g. if there are 3 jobs done in March 2021, and 0 on other months, all of the months will register their "jobs_completed" value as 3)

I have made a db-fiddle to show my problem: https://www.db-fiddle.com/f/gH7C7R6udMucjSATpw8Erh/0
SELECT to_char(generate_series('2021-02-01'::DATE, '2022-01-31'::DATE, '1 month'), 'Mon YYYY') AS month,
            COUNT(tbl.job_status) FILTER(WHERE job_status = 1) as jobs_completed
FROM monthly_tasks tbl
WHERE tbl.date_completed BETWEEN '2021-01-01' AND '2021-12-01'
GROUP BY month;

Thank you in advance!


texts: Opponent Winrate Over another opponent sql

texts:
I am trying to find the most efficient way to build a counter warehouse.

shortened the tables just to needed info for question

Match Table

MatchId, version, type

MatchParticipants

matchId, playerid, characterid, teamid (only team1 or team2), winner (1 or 0)

10 of these rows per match

Characters

characterid, name

I considered doing a cross join of characters on characters to give me all the possible of opponents for each character. Then I would have do a massive sub-query to look into the matches table where the id's were on opposite teams.

Any ideas essentially what I see the warehouse table looking like.

character1.characterid, character1.name,
character2.characterid, character2.name,
winrate of character1 over character2


texts: Disabling sql services while performing maintenance on SQL Server 2008 R2 cluster

texts: Our storage team is performing updates on an MPIO driver for our SQL Server 2008 R2 cluster.  They suggested disabling the sql server service on the node they are doing the update on - will disabling the sql server service interfere with the configuration of the cluster or have no impact?


texts: Join 3 tables with where and sorting clauses

texts:
I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too.

Each camera has a reference of vendor_model as model_id.

10 cameras can have one same vendor_model.

I am doing this query

SELECT v0."id", v0."vendor_id", v0."exid", v0."name", v0."username",
   v0."password", v0."jpg_url", v0."h264_url",  v0."mjpg_url", v0."mpeg4_url",
   v0."mobile_url", v0."lowres_url", v0."shape", v0."resolution",
   v0."official_url", v0."more_info", v0."audio_url", v0."poe",
   v0."wifi", v0."upnp", v0."ptz", v0."infrared", v0."varifocal",
   v0."sd_card", v0."audio_io", v0."discontinued", v0."onvif",
   v0."psia", v0."channel", v0."created_at", v0."updated_at"
FROM "vendor_models" AS v0
INNER JOIN "vendors" AS v1 ON v0."vendor_id" = v1."id"
WHERE ((lower(v0."name") LIKE $1))
  OR (lower(v1."name") LIKE $2)
ORDER BY v1."name" ["%%", "%%"]

after getting all those values. I am doing another query. such as

SELECT c0."id", c0."owner_id", c0."model_id", c0."exid",
   c0."name", c0."timezone", c0."thumbnail_url",
   c0."is_online", c0."offline_reason", c0."is_public",
   c0."is_online_email_owner_notification", c0."alert_emails",
   c0."discoverable", c0."config", c0."mac_address",
   c0."location", c0."last_polled_at", c0."last_online_at",
   c0."created_at", c0."updated_at"
FROM "cameras" AS c0
WHERE (c0."model_id" = $1) [4]

which then give me the total number of cameras which are using this model.

my problem is I want to merge this query to in single one.

where I can get all the values of VendorModel as well as vendor. and also the count of cameras which are using each vendor model. which I am unable to do right now any help would be appreciable.

Update:

I have got here

SELECT vendor_models.id, vendor_models.name, count(cameras.id) as count
  FROM "vendor_models"
  INNER JOIN "vendors" ON "vendor_models"."vendor_id" = "vendors"."id"
  INNER JOIN "cameras" ON "vendor_models"."id" = "cameras"."model_id"
  GROUP BY vendor_models.id, vendor_models.name
  ORDER BY count desc

But I cannot get vendor.name.. can you help?

Update:

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count
  FROM vendor_models
  INNER JOIN vendors as v ON vendor_models.vendor_id = v.id
  INNER JOIN cameras ON vendor_models.id = cameras.model_id
  GROUP BY vendor_models.id, vendor_models.name, v.name
  ORDER BY v.name asc

I got the final one. can you help to make it better?


texts: DB2 LOAD data into table - Interpreting the error messages

texts:
I encountered some constraint issues when loading data from file into table

**
SQL3109N  The utility is beginning to load data from file
"xag".
SQL3500W  The utility is beginning the "LOAD" phase at time "07/30/2012
23:33:53.199615".
SQL3519W  Begin Load Consistency Point. Input record count = "0".
SQL3520W  Load Consistency Point was successful.
SQL3116W  The field value in row "F12-33856" and column "9" is missing, but
the target column is not nullable.
SQL3185W  The previous error occurred while processing data from row
"F12-33856" of the input file.
SQL3227W  Record token "F12-33856" refers to user record number "440439".**

db2 LOAD client from data  of del MESSAGES msg replace INTO TABLE_L ;

I thought this message is telling me that we have an constraint issue on row 440439, but seems it is not the case. anyone know how to read the messages ?


texts: SQL Server 2016 Security - Restrict users from viewing a single database

texts:
Our client requirement, is to restrict other users from viewing/accessing the database if they are not assigned to work on it.

Some background on how the databases are setup:

For each client, a new database is created to host their data. Also there are
  other databases which are used by the front end application.

There are three types of users:

Data manager - modify tables according to data, load data, execute SP
Application user - used by front end web application
SME - They are data experts who work on the DB in need

All of the above mentioned users are configured as SysAdmin role. We
  understand this is not a good practice and efforts are in place to assign
  appropriate role to each db user

Consider this scenario:

There are

5 databases
3 data managers and 3 SME's and 1 application user.

1 data manager and 1 SME are assigned to work on the client data.

Also the application user is required to access by default.

The expectation is:

2 data managers will have access to 4 database (excluding this client)
2 SME's will have access to 4 database (excluding this client)
1 application user will have access to all databases
1 assigned data managers will have access to all databases
1 assigned SME will have access to all databases

My knowledge on SQL Server 2016 security and roles are limited. Please suggest an approach for this requirement.


texts: How to configure DBeaver to save files without a BOM for UTF files?

texts:
I am looking for the equivalent setting as the PGADMIN setting Write BOM for UTF files which will cause saved files to be saved with/without the BOM.

I've found settings in DBeaver when exporting data, but nothing that would configure new (or existing) files to be saved without the BOM.

How do I configure DBeaver to save files without a BOM for UTF files/


texts: Scripting execute permissions on stored procedure

texts:
My goal is to write a script which:

Creates a stored procedure
Grants execution permissions to a particular user on that new stored procedure

On this SO question the recommendation is to add GRANT EXECUTE ON at the end of a script that creates a stored procedure.

However, a permissions issue not letting my own non-admin account to grant permissions on stored procedures trying to execute the stored procedure lead me to believe that that GRANT EXECUTE ON statement is executed each time that the stored procedure above it is executed. Am I incorrect?

Edit with clarification:
In the script involved, the stored procedure statements included BEGIN...END, and was followed by GO, before the GRANT EXECUTE statement.


texts:
Do SQL Server or MYSQL contain JSON supporting functions? If so, then please tell me the name of the functions or where else I can find it?

I am new in this stuff and I want to implement JSON with the databases mentioned above.


texts:
I want to create a table which has a column named "direction".

This column is either "in" or "out". It is not nullable and it can only contain these two values. Never other values.

Which data type is most feasible?

I use in PostgreSQL. It is 100% ok if the data type is PG specific (only known to this database implementation).


texts: Is using association tables for One-to-Many relationships common practice?

texts:
I'm currently running into a conceptual disagreement with some database architects at work.  My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find supporting evidence online for their side.

The part of the database that we're discussing has to do with pieces of manufacturing equipment within different areas.
There is a table containing all unique AREA_CD's.  Each area can contain multiple pieces of equipment (EQUIP_CD), but a single piece of equipment cannot be in multiple areas.  I.e. this is a One(AREA_CD) to many(EQUIP_CD) relationship.

My understanding of db design is that we should have a table for the equipment directly related to the area table where:
AREA_CD= PK & FK to area table
EQUIP_CD= PK
(The combined PK is because there are some pieces of equipment that share the same code name, but are not the same. This is an unfortunate reality from the legacy system we are trying to upgrade).

The architects are saying that it is best practice to have the area table and equipment table joined via an association table, which I thought was specifically for many-to-many relationships.

This would make sense to me if an EQUIP_CD referred to a specific type of equipment (with attributes we're interested in storing) of which multiple could exist in different areas.  However that would be a many-to-many relationship which isn't the case here.  We have some shared code names that refer to different types of equipment, and the attributes depend on which area they are in.

Their justifications for the use of an association table are the following:

Data protection.  The association table is protected by the code tables.. but I don't think this adds any value in this case.  There are other tables which are connected to the EQUIP_CD which will protect it.
Future flexibility.  This is understandable to me.
Best practice. Is it though?

So my question.. Is the use of an association table for a one-to-many relationship best practice?  Are there any other justifications for this design beyond the three above?

I'm not looking to challenge my colleagues, just further my learning.  Thank you in advance!

Update:  The data model is being fixed, we're getting rid of those association tables.


texts: set date value based on other row values

texts:
Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem:

I've got a table with the following structure and some example data:

CREATE TABLE [dbo].[set_dates](
[Split3_ID] [int] IDENTITY(1,1) NOT NULL,
[CU_ID] [int] NULL,
[order_id] [int] NULL,
[st_date] [datetime] NULL,
[sku] [int] NULL,
[Priority] [int] NULL,
[Delay] [int] NULL,
[CourseDate] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[set_dates]
       ([CU_ID]
       ,[order_id]
       ,[st_date]
       ,[sku]
       ,[Priority]
       ,[Delay])
 VALUES
       (25721,7907,GETDATE(),63,4,4),
       (25718,7910,GETDATE(),63,4,4),
       (25718,7910,GETDATE(),6,5,2),
       (25719,7908,GETDATE(),6,5,2),
       (25719,7908,GETDATE(),57,4,4),
       (25719,7908,GETDATE(),52,8,2)
  GO

So my Table should look like this:

Split3_ID CU_ID order_id st_date                    sku Priority Delay CourseDate

1         25721 7907     2014-07-08 18:04:30.973    63  4        4     NULL
2         25718 7910     2014-07-08 18:04:30.973    63  4        4     NULL
3         25718 7910     2014-07-08 18:04:30.973    6   5        2     NULL
4         25719 7908     2014-07-08 18:04:30.973    6   5        2     NULL
5         25719 7908     2014-07-08 18:04:30.973    57  4        4     NULL
6         25719 7908     2014-07-08 18:04:30.973    52  8        2     NULL

What I would like to accomplish now is to set the CourseDate depending on three fields, order_id, Priority and Delay ... more exact:

If there is only one row with the same order_id, then CourseDate = st_date, if there is multiple rows with the same order_id, then the Dates should be entered depending on the Priority. Lowest Priority would be the first CourseDate = st_date, next bigger Priority would be st_date + Delay from previous entry ... and so on ...

In the above example, the outcome should look like this:

Split3_ID CU_ID order_id st_date                    sku Priority Delay CourseDate

1         25721 7907     2014-07-08 18:04:30.973    63  4        4     2014-07-08

2         25718 7910     2014-07-08 18:04:30.973    63  4        4     2014-07-08
3         25718 7910     2014-07-08 18:04:30.973    6   5        2     2014-07-12

4         25719 7908     2014-07-08 18:04:30.973    6   5        2     2014-07-12
5         25719 7908     2014-07-08 18:04:30.973    57  4        4     2014-07-08
6         25719 7908     2014-07-08 18:04:30.973    52  8        2     2014-07-14

Is there a way to accomplish this? I've got up to ten orders with the same order_id with no presorting, so my first attempts with CASE Statements ended up in a lot of writing.

The second, but not so important problem is, that there may be orders with the same priority from time to time under the same order_id. In this case I would like those to be treated just like CourseDate = st_date

Any help is highly appreciated, as I got a major brainfart at the moment ... ;)


texts: Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

texts:
While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement.
For that we are trying to perform some testing in our local set up,where we have placed some partitions of a table(Actually partitions of the innodb tables files, the .ibd ones) outside of the data directory by means of alter table statements with data directory clause.
But the problem is that whenever we alter the table after partitioning ,the partitioned .ibd files placed outside of datadir comes back to the datadir which is really unexpected.
So can anyone please help in this context to clarify whether this is a issue with mariadb or  with the concept itself described as above.


texts:
I am trying to create some tokudb tables to experiment with the different row format options to compare the compression available.

https://www.percona.com/doc/percona-server/5.7/tokudb/using_tokudb.html

I have tried all the following
TOKUDB_SNAPPY
TOKUDB_ZLIB
TOKUDB_DEFAULT

With no effect.

If I just ignore it the tables are created with row_fromat = fixed.

MariaDB [eventlog]> show VARIABLES like "%row_format%";
+--------------------------------+-------------+
| Variable_name                  | Value       |
+--------------------------------+-------------+
| tokudb_hide_default_row_format | ON          |
| tokudb_row_format              | tokudb_zlib |
+--------------------------------+-------------+

MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_DEFAULT' at line 1
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ROW_FORMAT=tokudb_default;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tokudb_default' at line 1

MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;
Query OK, 0 rows affected (0.09 sec)

MariaDB [eventlog]> show table status from eventlog\G;
*************************** 1. row ***************************
           Name: stable1
         Engine: TokuDB
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 18446744073709551615
 Auto_increment: NULL
    Create_time: 2017-02-20 12:26:18
    Update_time: 2017-02-20 12:26:18
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

 MariaDB [eventlog]> ALTER TABLE stable1 ROW_FORMAT=TOKUDB_SNAPPY;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_SNAPPY' at line 1

 version | 10.1.21-MariaDB
 tokudb_version | 5.6.34-79.1


texts: How do I organize tables for simple lottery game? mysql

texts:
I am building a simple lottery web game that will let users pick a number that is in between that weeks given range. A number will be randomly select to determine the winner. I have a table for users (id, username, password, pin, funds) but as I go to setup the games table I get kinda stumped. How do I keep track of what numbers have been picked so far and who they belong to? game (id, date, winner...)? How do I handle the fact that the number range can change from week to week?

users
id
username
password
pin
funds

games
id
date
winner

number_range
date
range

selected_numbers
date
user_id
number

Is this heading in the right direction?


texts: How to append value of column with double quotes (add quotes around string)

texts:
I have a table with a column that is TEXT type. In the column are numeric characters. What I'm trying to achieve is to wrap those characters in double quotes.

EXAMPLE:

NAME     ID     QTY
Apples   A1     1
Oranges  O1     1
Foo      F1     0

IDEAL OUTPUT:

NAME     ID     QTY
Apples   A1     "1"
Oranges  O1     "1"
Foo      F1     "0"

I attempted to run the following SELECT statement but it didn't give me the result I was expecting. Perhaps you can guide me in the right direction?

SELECT `qty`, CHAR('"'|| qty ||'"')
FROM `myTable`;

Thank You


texts:
I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). It's impossible that two events overlap for the same factory.
My idea was to introduce an exclusion constraint based on a GIST index:
CREATE TABLE event
(
   identifier SERIAL NOT NULL PRIMARY KEY
   factory_identifier INTEGER NOT NULL REFERENCES factory,
   period TSTZRANGE NOT NULL,

   EXCLUDE USING gist
   (
      factory_identifier WITH =,
      period             WITH &&
   )
);

In the documentation I read:

A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns.

I am not sure how to interpret this. Given that there are only a small amount of factories but a large number of events, should I define the exclusions constraint as following instead?
EXCLUDE USING gist
(
   period             WITH &&
   factory_identifier WITH =,
)

Now, the period is the first column, for which there are a lot of distinct values (almost all values are unique, actually), in contrast to factory_identifier, for which there only a few distinct values.
Is this better now? In short, I have difficulties to grasp the quoted documentation above.

additional
In the slides on a presentation of exclusion constraints on page 16 and a blog post, both coming from the original author of exclusion constraints, I found the following example:
EXCLUDE USING GIST(room WITH =, during WITH &&)

Given the (reasonable) assumption that there much more distinct reservation periods than distinct rooms, I wonder if this should have been (during WITH &&, room WITH =) instead, given the quote regarding GIST column ordering above.
This makes me believe that I am not really understanding the quoted documented above.


texts: Estimated vs. Actual Row counts in a trivial case

texts:
I'm working on optimizing some queries in a database I've inherited.  I'm not permitted to disclose the query, but I can present an anonymized version of a query plan that is showing some very strange behavior (to me):

My question is about the 'Clustered Index Scan' denoted (1) in the images.  As I understand it, the 'Clustered Index Scan' highlighted in yellow doesn't have any dependencies.  The operation doesn't have any predicates (although I don't show it here) and it's sibling in the tree (2) is executed the same number of times as the number of rows (1) returns -- I take this to mean that (1) is executed first, and (2) is executed row by row for the join (which makes sense, since the join is a 'Nested Loop').

Here's the issue:  The 'Estimated Rows' for (1) is 46.  The 'Actual Rows' is 283972, which is the number of rows in that table exactly...which makes sense since there's nothing to trim it down. I know the query is very complex (4 levels of nested views), but how in the world does SQL Server get this wrong?  Why isn't the estimate for the number of rows that come back in an Index Scan with no predicate equal to the number of rows in the table?

I've updated all of the relevant table statistics with no success.  I've also noticed that reducing the nesting level of the queries has the effect of the problem occurring less often, though the results are 'unstable'.  There are a number of queries that reference this view, and if I partially reduce the nesting of this view, a few queries may be fixed, but it may break a few more.


texts: Where to look for clues as to why SQL Server become unresponsive/crashed?

texts:
Recently an SQLExpress instance suddenly become unresponsive/crashed whilst I was mid-dialog (SSMS transaction log backup), which froze with an hourglass and the dialog was unusable. No other applications or users on the network were able to access instance. I tried to restart using Services, but that didn't respond. Eventually I rebooted the server entirely and it came back up. All the databases in use at the time appear to still be working post-reboot and not corrupt.

Where are the best places to look to find out the source of this crash?

I looked at Event Viewer > Applications, but the only messages around the time of the crash are (in order):

The last dialog box I received before the crash
(approx 1 min pause)
Windows Error Reporting and Application Hang for SSMS (I was using locally on the server)
Subsequent warnings from ASP.NET saying it couldn't connect
Several "Access to server validation failed while revalidating the login on the connection." errors
Several "The client was unable to reuse a session with SPID 68, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message."
Login failed for user 'xxxxxxxx'. Only administrators may connect at this time. [CLIENT: 127.0.0.1]

Messages 5/6/7 appear to be resulting from the server becoming unresponsive, not the cause.

I have looked at the SQL Error Logs using SSMS > Management > SQL Server Logs but that just jumps from 1 to 5 above.

Any other things I can analyse?


texts: MySQL BEFORE INSERT trigger with IF conditions

texts:
In my BEFORE INSERT trigger, I select data, then I check the value if it's null then I do insert data into table A then else into table B. I select data from a table in another database called ebsms.inbox then insert to dbsmsra_pit2.smsra_sms and dbsmsra_pit2.smsra_trash

Here's my complete code :

DROP TRIGGER IF EXIST dbsmsra_pit2.smsra_sms.filterNoHP;
DELIMITER $$
CREATE TRIGGER `filterNoHP`
BEFORE INSERT ON smsra_sms FOR EACH ROW

BEGIN

DECLARE IDx int(11);
DECLARE NumberHP varchar(20);
DECLARE WaktuKirim timestamp;
DECLARE IsiSMS text;

SELECT ID, SenderNumber, ReceivingDateTime,
TextDecoded into IDx, NumberHP, WaktuKirim, IsiSMS FROM ebsms.inbox
WHERE NumberHP in (SELECT NoHP FROM smsra_blokir_number)
and DATE(ReceivingDateTime) = curdate();

IF NumberHP = null then
insert into smsra_sms
set idsms = IDx, SendingDateTime = WaktuKirim,
isi = IsiSMS, pengirim = NumberHP
ON duplicate key update idsms = idsms + IDx;

ELSE
insert into smsra_trash
set Nohp = NumberHP
ON duplicate key update Nohp = Nohp + NumberHP;

END IF;

END$$

DELIMITER ;

here's the error message :

Error Code: 1235. This version of MySQL doesn't yet support
'multiple triggers with the same action time and event for one table'

Please I need help.


texts: Right way to delete the archive - Oracle

texts:
So I have an Oracle database (standalone) running in RHEL and I have scheduled RMAN for backups on daily and weekly basis. Sometimes my file system (archive mount-point) becomes full because of high archive generation. Now what is the right way to delete the archives from the file system?

My RMAN script has this

delete archivelog all completed before 'SYSDATE-5'

but the above command isnt deleteing the archives properly as I see, some of the very old archives are still there..

How to delete them properly?

Should I run crosscheck archivelog all? and then run the above command once again? What is the right way?

Thanks!


texts: table design to track significant events associated with an item

texts:
An Orders table I'm dealing with at present in design phase has about 10 or more columns.

Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders could go into the millions. At the moment, I'm aware of about 20 important events to track for each Order, but that number may increase or decrease.

Of these 20 or so events, only a handful will always take place, while the rest are just potential events. Also, many - but not all - of these events have at least two points which need storage:

the Date the event is supposed to happen (if it is no longer just a "potential" event)
the Date the event did happen

The events data will be updated in the database from external sources in the form of .xls or .csv files. For all events, it is only possible for the given event to happen once.

What would be the best design for storing this milestone event data?
Would you try to use a somewhat normalized design, where there is an Events table designed similar to this...

CREATE TABLE dbo.Events (
    EventID             INT
    , OrderID           INT
    , EventExpectedDate DATEIME2(7)
    , EventActualDate   DATETIME2(7)
    , EventTypeID       INT
    , EventSkip         BIT
    ...
    )

Or would you try a less normalized approach and flatten all the possible events out into one  very wide events table that mimics what the .xls/.csv update files will look like?

CREATE TABLE dbo.Events (
    EventID             INT
    , OrderID           INT
    , Event_1_ExpDate   DATEIME2(7)  /* No, the actual names would not include 1, 2, 3 */
    , Event_1_ActDate   DATETIME2(7) /* The actual names would name type of Event */
    , Event_2_ExpDate   DATETIME2(7)
    , Event_2_ActDate   DATETIME2(7)
    ...
    , Event_20_ExpDate   DATETIME2(7)
    , Event_20_ActDate   DATETIME2(7)
    )

Or is there another pattern you would use?


texts: Does one of the normal forms require separation of repeating clusters of values into another table?

texts:
Should the repeating values in the "Sales" table below be separated out into a second table as part of the process of database normalization? Note that none of the values are functionally dependent on each other; those values that are functionally dependent are already normalized out to other tables.  I'm just not sure if there's any benefit to separating the "clusters" of values in different domains out into a separate table since the table contains tens of millions of rows, but only a few thousand distinct combinations of these values:

LOCATION     PRODUCT         RECIPIENT     DATE       AMOUNT
New York     Cheesecake      Bill          1/1/2000   $25.00
New York     Cheesecake      Bill          2/1/2000   $42.00
New York     Cheesecake      Bill          3/1/2000   $17.00
Dallas       Cheesecake      Bill          4/1/2000   $15.00
Dallas       Cheesecake      Bill          5/1/2000   $17.00
Dallas       Cherry Pie      Bill          6/1/2000   $14.00
Dallas       Cherry Pie      Bill          7/1/2000   $13.00
Dallas       Cherry Pie      Sam           8/1/2000   $16.00

The table would be separated into a "Sale Types" table:

SALETYPE     LOCATION    PRODUCT       RECIPIENT
1            New York    Cheesecake    Bill
2            Dallas      Cheesecake    Bill
3            Dallas      Cherry Pie    Bill
4            Dallas      Cherry Pie    Sam

So that the original "Sales" table would look like this:

SALETYPE     DATE        AMOUNT
1            1/1/2000    $25.00
1            2/1/2000    $42.00
1            3/1/2000    $17.00
2            4/1/2000    $15.00
2            5/1/2000    $17.00
3            6/1/2000    $14.00
3            7/1/2000    $13.00
4            8/1/2000    $16.00

Since each unique combination of values appears approximately 1,000 times each on average, I believe that would save storage and potentially memory pressure, but my understanding of the underlying RDBMS memory optimizations is admittedly pretty limited.  Would that be considered database normalization though?


texts:
Given that there are 15 entities in E1, 20 entities in E2 and 25 entities in E3, what's the maximum and the minimum number of entities in E0? It's also known that the ISA hierarchy follows the covering constraint but not the overlapping constraint.

I get that the maximum number would be 60, but I'm confused when it comes to the minimum value.

What I'm confused about is if E0 can have 0 entities (or fewer than 15+20+25=60 entities). I understand that unioning all entities in E1, E2, and E3 will give us all entities in E0, but can it be empty too?

Thanks.


texts:
How does '+' operator behave in following statement?

select + 'taco';  --Result is 'taco'

Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?


texts:
I'm trying to set up mechanism that will automatically create database backup.
The idea is to create a script that will be executed by crontab. Seems easy but I'm struggling with the following problems:

I cannot use msqldump, because it needs password input from the user;
I've tried physical copy, but after removing database or dbfarm and restoring them, monetdb and monetdbd couldn't start "restored" database and dbfarm;

Can I bypass above problems and/or are there other solutions?


texts: Want to output post title with meta-tags at same time on MySQL of WordPress

texts:
I want to edit meta-tags of a blog page of my WordPress blog using SQL editting tool such as MySQL workbench.

I want SQL output such as below. Can not?

# post_title, metatag
'metadev', 'hoge'
'metadev', 'fuga'

SELECT * FROM yuis_yuisorgblog.wp_posts;

Output of above SQL is below.

# ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count
'164', '1', '2018-08-20 15:49:53', '2018-08-20 06:49:53', '
metatagdev
\n', 'metatagdev', '', 'publish', 'closed', 'open', '', 'metatagdev', '', '', '2018-08-20 15:49:53', '2018-08-20 06:49:53', 'metatagdev', '0', 'http://yuis.org/blog/?p=164', '0', 'post', '', '0'

SELECT * FROM yuis_yuisorgblog.wp_term_relationships;

Output of above SQL is below.

# object_id, term_taxonomy_id, term_order
'164', '5', '0'
'164', '6', '0'

SELECT * FROM yuis_yuisorgblog.wp_term_taxonomy;

Output of above SQL is below.

# term_taxonomy_id, term_id, taxonomy, description, parent, count
'5', '5', 'post_tag', '', '0', '1'
'6', '6', 'post_tag', '', '0', '1'

SELECT * FROM yuis_yuisorgblog.wp_terms;

Output of above SQL is below.

# term_id, name, slug, term_group
'5', 'hoge', 'hoge', '0'
'6', 'fuga', 'fuga', '0'


texts: Tables names in a supertype/subtype schema

texts:
I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited information about each user. I then have 3 tables for each user type: admin, reader, approver.

Now, the design I have gone with thus far has been something like this:

General Tables (don't pertain to users)
e.g.,
country
city

Tables That Pertain to all Users
e.g.,
person_contact
person_contact_category

Tables That Pertain to User Type
e.g.,
approver_approve_for
reader_can_view

Now, based on this information, I am wondering a few things:

Global tables do not get a prefix. Is it considered bad practice to prefix the tables that pertain to a subset with the subset name (e.g., admin_)? I figured it makes it easy to look at the DB design and see exactly what subtype each table pertains to, but the next question is what made me ask this question at all.
The table person_contacts_categories_map is a junction table between the many categories a person can have, and the many contacts in those categories. OK, typically I name my junction tables something like contact_category. Unfortunately, there are more things that get categories in this app than just contacts, thus I am saying this table relates to all persons (e.g., person_) and relates to contacts (contact_). I was thinking then that the junction table should be named person_contact_person_contact_category, but that is just horrible.

So, given what I've laid out, does anyone know a better approach to this problem, or is what I am doing fine?


texts:
We have MariaDB server with single table with image url information. The table is about 400GB on disk and contains probably 400M rows.

Table is partitioned in 1024 partitions.

All queries are similar to this one:

select * from container where id in (1234, 1235 ... );

The sql usually took 2 sec to be executed.

Each row contains a single image url, a title and keywords.

Keybufer is set to 8GB.

This set up works well, until we begin to insert aditioan rows. We tried normal inserts, also low_priority inserts. It is slow in both cases.

I wonder what else we can tweak in order to speed up the selects.

Update:
the table has no indexes, except the primary key on a bigint field e.g. primary key(id)

Update 2:
Here is some more information:

Create Table

CREATE TABLE `container` (
  `id` bigint(20) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=binary
/*!50100 PARTITION BY KEY (id)
PARTITIONS 1024 */

data field stores standard JSON. It is UTF8 text, but because of wrong encoding of the input data, we were forced to store it in blob + binary encoding.

Size on disk

# du -h /usr/local/mysql/var/mydb/
371G    /usr/local/mysql/var/mydb/

Count(*)

> select count(*) from container;
+-----------+
| count(*)  |
+-----------+
| 409036295 |
+-----------+
1 row in set (0.04 sec)

my.ini

[mysqld]
server-id = 1

port=3306
socket=/tmp/mysql.sock
skip_name_resolve

open-files-limit=64000

#Flush every 5 min (300 sec)
set-variable = flush_time=900

#Max Clients
set-variable = max_connections=5050
set-variable = max_user_connections=5000
set-variable = back_log=50

set-variable = table_open_cache=1024
set-variable = table_definition_cache=1024

#INSERT While SELECT-ing. Default is 1 (1 = On if have no hole, 2 = On if have hole)
set-variable = concurrent_insert=2

#Interactive timeout 60 min (from console)
set-variable = interactive_timeout=3600

#non-interactive timeout 3 hours
set-variable = wait_timeout=10800

set-variable = key_buffer_size=8192M
set-variable = max_allowed_packet=5M
set-variable = sort_buffer_size=256M

set-variable = tmp_table_size=512M
set-variable = max_heap_table_size=64M

#all updates will wait for selects
set-variable = low_priority_updates=1

#preforking
set-variable = thread_cache_size=64

#----- SLOW QUERIES -----

set-variable = long_query_time=2
set-variable = log_slow_queries=mysql-slow.log

#----- CASHE -----

# SELECT SQL_CACHE * from x
set-variable = query_cache_type=0
set-variable = query_cache_limit=1M
set-variable = query_cache_size=128M


texts: Long running statement not monitored by Oracle

texts:
We are trying to find out why a (pretty complicated) statement has an extremely high variance in runtime. Sometimes it just takes "minutes" (around 40-50) and sometimes it does not finish even after 12 hours.
My usual approach to problems like that is to check if the statement is monitored and if it is, get the "real-time" execution of it using dbms_sqltune.report_sql_monitor() so I can pin-point the bottleneck in the execution.
However that specific statement is never monitored by Oracle. According to the Oracle manual a statement is monitored, if:

By default, SQL monitoring is automatically started when a SQL statement either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution

As this statement runs for substantially longer then 5 seconds and uses parallel execution it should be monitored.
We even added the hints gather_plan_statistics and monitor to the SQL to force monitoring of the statement, but still it's not monitored.
According to "My Oracle Support" (DocID: 1613163.1) monitoring is limited to statements with a plan with less then 300 lines. When I generate the plan, it has 282 lines (=highest line ID in the plan), so that should not prevent monitoring.
There is a hidden parameter ("_sqlmon_max_planlines") that controls this, but this is a hosted database server and changing something like that is quite complicated.
So my question is:
Are there any other (maybe undocumented) restrictions on why Oracle would not monitor a statement?

This is an Oracle 11.2.0.3 Enterprise Edition (upgrade to 11.2.0.4 is scheduled, but that might take some time).


texts:
We experienced extreme slowness of login on 6 Mar in 1 of our Oracle servers (Solaris 10) running Oracle DB. During that period of time, our firewall had detected and dropped many TCP connections (almost every min) that used port 48464, 64245, 64667 and 64996 to connect to another Oracle server (Solaris 10) that also running Oracle DB. No one knew what had happened.

We would like to find out the root cause of the problem if it happens again. Is there a way to trace these abnormal connections? Better still if there is any tools that can be activated once the problem surface since the incident can happen when the administrator is not around.

Thanks in advance.


texts:
what is the difference between these two ,second is arithmetic expression assigne to sal , first one is ?  how they differ exactly. if salary has intial value of 10 both result same answer. when to use them in oracle based on what  ?
if this is not a good question please disregard .

sal :=sal+300

sal=sal+300;


texts: Requests to local CouchDB have 40ms latency

texts: Requests to a CouchDB instance on the same machine have about 40ms latency when HTTP keepalive is used by the client, about 0-1ms latency when the client does not use keepalive. What causes this latency?


texts: Is there any performance advantage to marking a SQL function as LEAKPROOF?

texts:
PostgreSQL has an attribute LEAKRPOOF which you can use to declare a function,

  LEAKPROOF indicates that the function has no side effects. It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof. This affects how the system executes queries against views created with the security_barrier option or tables with row level security enabled. The system will enforce conditions from security policies and security barrier views before any user-supplied conditions from the query itself that contain non-leakproof functions, in order to prevent the inadvertent exposure of data. Functions and operators marked as leakproof are assumed to be trustworthy, and may be executed before conditions from security policies and security barrier views.

The docs go on to say,

  The query planner has more flexibility when dealing with functions that have no side effects. Such functions are referred to as LEAKPROOF, and include many simple, commonly used operators, such as many equality operators. The query planner can safely allow such functions to be evaluated at any point in the query execution process, since invoking them on rows invisible to the user will not leak any information about the unseen rows. Further, functions which do not take arguments or which are not passed any arguments from the security barrier view do not have to be marked as LEAKPROOF to be pushed down, as they never receive data from the view. In contrast, a function that might throw an error depending on the values received as arguments (such as one that throws an error in the event of overflow or division by zero) is not leak-proof, and could provide significant information about the unseen rows if applied before the security view's row filters.

Is the following LEAKRPOOF function

CREATE FUNCTION foo_leakproof(bar int)
RETURNS int AS $$
  SELECT bar;
$$
IMMUTABLE LEAKPROOF
LANGUAGE sql;

Ever planned differently or does it run with a different runtime profile than the LEAKPROOF lacking alternative,

CREATE FUNCTION foo_leaky(bar int)
RETURNS int AS $$
  SELECT bar;
$$
IMMUTABLE
LANGUAGE sql;


texts: SSRS 2008 report fast in test, slow in production: parameter sniffing problem?

texts:
We recently had an issue with reports running in an integrated SharePoint environment where the report runs fine in development, test and staging, but very slow (times out) in production.  Running sp_recompile didn't help.  It runs fast in SSMS.  So, thinking it could be a parameter sniffing problem, we fixed production by adding local variables to the stored procedure and setting them to the input parameters.

The question I have is: why did we have to do this in just one environment?  I'd like to understand what could cause an SSRS report to run so poorly on one server when the exact same report works fine and fast on another.

I have checked configurations on the servers and databases (test is a restore from prod) and they appear to be the same.  It's frustrating to migrate from one environment to another without issue just to have it fail when it goes live.  Any pointers?


texts: GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?

texts:
TL;DR - I would like examples of where using a GROUP BY (example shown below) using columns not in the SELECT list can be used to resolve SQL challenges in a [practical | elegant | powerful] way. I mean in a general way - the example demonstrated below is interesting because it demonstrates the principle (but it doesn't work!). I want working examples where use of this technique can be used to achieve something "significant".

Following up to this question, which required the counting of routes from point_x to point_y and vice-versa, taking routes between the same points to be equivalent - i.e. A -> B is the same for the count purposes as B -> A. A working general solution to that question is given there.

However, one poster (SQLRaptor) showed a solution which solved the problem for the (presumably) subset of data shown in the question, but this poster also said that their solution wasn't general and asked the OP could they see why? SQLRaptor also said this was an example of a GROUP BY using columns not in the SELECT list and that this could be an elegant (powerful) solution to some SQL challenges!

SQLRaptor's solution (using my own notation from answer) was:

SELECT  MIN(origin) AS point_1,
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM route
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY point_1, point_2

I thought the non-generality might be something to do with the multiplication (duplicates), but had no concrete proof. This transpired to be the case - see my proof here and here.

Unfortunately, this example of using a GROUP BY using columns not in the SELECT list doesn't work very well - what I would like are examples of where this technique can work well?


texts: To implement a facade DB table or not? [pics]

texts: Does it make sense to normalize a schema with tables that only serve the purpose of logically separating parts of a system? Below are 2 example schema with what I am talking about. (sorry about the titles being hard to read) One with the "superfluous" selection criteria table and the other without it. What makes more sense? why? Thanks!


texts: SQL SELECT query takes too long after updating the version

texts:
To begin with, here is my query:

SELECT
  pd.meterclass, pd.meterscore, pd.rt_url, pd.imdbId, pd.title, pd.adult,
  pd.backdrop,pd.budget, pd.homepage, pd.plot, pd.poster, pd.release_date,
  pd.revenue, pd.runtime, pd.language, pd.tagline, pd.status,
  pd.imdbRatings, pd.imdbVotes, mg.genre_id, cst.cast_id,
  cst.character_name, cst.name,
  cst.profile_path, crw.job, crw.name as crew_name,
  crw`enter code here`.profile_path as crew_profile_path, crw.id as crew_id,
  crw.credit_id as crew_credit_id
FROM (
       SELECT *
       from movie_data_primary
       WHERE cinestopId="8QRJTD"
     ) pd
INNER JOIN movie_genre mg ON pd.cinestopId = mg.cinestopId
INNER JOIN movie_cast cst ON cst.cinestopId  = pd.cinestopId
INNER JOIN movie_crew crw ON crw.cinestopId = cst.cinestopId;

Before I upgrade my MySQL version to 5.7, this query used to take around 3 seconds (not the most optimal time, but tolerable anyway). After the upgrade, it takes more than half an hour. Here's the profile:

+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| starting             |   0.000221 |
| checking permissions |   0.000013 |
| checking permissions |   0.000006 |
| checking permissions |   0.000004 |
| checking permissions |   0.000008 |
| Opening tables       |   0.000039 |
| init                 |   0.000158 |
| System lock          |   0.000018 |
| optimizing           |   0.000025 |
| statistics           |   0.002507 |
| preparing            |   0.000063 |
| executing            |   0.000007 |
| Sending data         | 999.999999 |
| end                  |   0.000013 |
| query end            |   0.000012 |
| closing tables       |   0.000012 |
| freeing items        |   0.002281 |
| cleaning up          |   0.000023 |
+----------------------+------------+

The field 'cinestopId' is indexed. Can someone tell me what's going berserk?

Thanks!