How to model entities in an entity-relationship diagram about a car service scenario? I'm a beginner Database designer and wondering if anyone can help me understand this.
Let's say we want to design a system that has, among other entities, an entity called Vehicle, and another one called Service.
A service is a repair/maintenance.

How would you look at the cardinalites of that relation of:

A car can be serviced multiple times.
A service is for one car.

Should the minimum cardinality be 0 or one from the car side?

Also, a service involves parts, labor, and consumable.

How would you model this?
As a separate entity?
Or in the service entity or part of the relation (intersection entity) between car and service ?

The General Advice:

When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type.

This is a really good place to start with any logical database design.  If you spend some time up front thinking about what kind of things matter to your system, then you're going to come up with a solid foundation on which to build your system.  The things your organization cares about will change much less frequently than the business processes and rules your organization uses to deal with those things. That is why a solid data model is so important.

Another important rule of thumb is: Normalize your data model by default and only denormalize when you have a (really) good reason to.  This is especially true for a transactional system.  Reporting systems and data warehouses are a different story.

The Specific Answers:

Cardinality: If you think about it, it is easily the case that a car could have never been serviced (by your shop).  Therefore a minimum cardinality of zero is very plausible.  On the other hand, by the time the vehicle matters to your system it may well be because it has had its first service - so a minimum cardinality of one is also plausible.  You need to think about what the business rule is for your organization and model accordingly.  I would think, for example, that a car dealership would have lots of cars in its system that haven't been serviced by the dealership yet, whereas a muffler shop wouldn't care about cars it hasn't serviced.

Service Items: You asked:

  Also, a service involves parts, labor, and consumable.

  How would you model this? As a separate entity? Or in the service
  entity or part of the relation (intersection entity) between car and
  service ?

Let's consider an intersection entity between car and service...  You could potentially use such an intersection to store details about the service, like how much labour, which parts, and consumables were used.

However, using an intersection implies a many-to-many between cars and services, but you've already stated that each service is for (exactly?) one car.  Using an intersection entity to track service item details would mean your model isn't properly normalized.

Consider this model as an alternative:

In this model each service is for one vehicle, but each service can have many instances of labour, parts and consumables.  This model follows the first rule of thumb I mentioned and makes an entity type out of each tangible thing the system cares about.  This might be a good first stab at a logical model.

One of the issues with the above model is that it doesn't handle one aspect of how your system is likely to want to use the data, at least not very well.  One of the most important reasons for tracking all of this data in your system at all is so that you can print off an itemized service invoice.  That means that a service line item is itself a thing which is important to your system.  If you take that into consideration, you might end up with something more like this:

Notice in this second alternative SERVICE_LINE_ITEM is recognized as an entity type.  It is an intersection between SERVICE and the generic line item type: SKU.  A SKU is a supertype entity that could be a part, a consumable or some kind of labour.  You don't need to have a logical supertype for service line item types, but a lot of systems would be modeled this way because it makes the transactional detail much simpler.

This second model introduces abstract entities over and above the concrete entities of the first model.  Introduction of abstractions like this is one of the things that tends to happen as you move from an initial logical model, based mostly on tangible things to a physical model.

As you gain experience with data modeling, you'll get good instincts for moving past the conceptual/logical model stage directly to a well structured physical model.


Split value from a date range in to individual values per date I am modifying a SSRS sales report for my customer. I have already calculated the number of days and based on that the daily value. In the software you set the start and end date of the agreement which are visible on the report.
My customer wants to split the agreement value in to individual date values. Is there a way how I can split the date range in to individual dates? Then I would just group all the individual dates values from all agreements in to one daily total.
I am using SQL Server 2017 and Visual Studio 2013

WITH cte AS ( SELECT {columns list}, start_date as single_date
              FROM source_table
            UNION ALL
              SELECT {columns list}, DATEADD(day, 1, single_date)
              FROM cte
              WHERE single_date  < finish_date )
SELECT *
FROM cte;

Example fiddle


Indexes for SQL query with WHERE condition and GROUP BY I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow.

My query:

SELECT group_id
FROM counter
WHERE ts between timestamp '2014-03-02 00:00:00.0' and timestamp '2014-03-05 12:00:00.0'
GROUP BY group_id

The table currently has 32.000.000 rows. The execution time of the query increases a lot when I increase the time-frame.

The table in question looks like this:

CREATE TABLE counter (
    id bigserial PRIMARY KEY
  , ts timestamp NOT NULL
  , group_id bigint NOT NULL
);

I currently have the following indexes, but the performance is still slow:

CREATE INDEX ts_index
  ON counter
  USING btree
  (ts);

CREATE INDEX group_id_index
  ON counter
  USING btree
  (group_id);

CREATE INDEX comp_1_index
  ON counter
  USING btree
  (ts, group_id);

CREATE INDEX comp_2_index
  ON counter
  USING btree
  (group_id, ts);

Running EXPLAIN on the query gives the following result:

"QUERY PLAN"
"HashAggregate  (cost=467958.16..467958.17 rows=1 width=4)"
"  ->  Index Scan using ts_index on counter  (cost=0.56..467470.93 rows=194892 width=4)"
"        Index Cond: ((ts >= '2014-02-26 00:00:00'::timestamp without time zone) AND (ts <= '2014-02-27 23:59:00'::timestamp without time zone))"

SQL Fiddle with example data: http://sqlfiddle.com/#!15/7492b/1

The Question

Can the performance of this query be improved by adding better indexes, or must I increase the processing power?

Edit 1

PostgreSQL version 9.3.2 is used.

Edit 2

I tried @Erwin 's proposal with EXISTS:

SELECT group_id
FROM   groups g
WHERE  EXISTS (
   SELECT 1
   FROM   counter c
   WHERE  c.group_id = g.group_id
   AND    ts BETWEEN timestamp '2014-03-02 00:00:00'
                 AND timestamp '2014-03-05 12:00:00'
   );

But unfortunetly this didn't seem to increase the performance. The Query Plan:

"QUERY PLAN"
"Nested Loop Semi Join  (cost=1607.18..371680.60 rows=113 width=4)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Bitmap Heap Scan on counter c  (cost=1607.18..158895.53 rows=60641 width=4)"
"        Recheck Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        ->  Bitmap Index Scan on comp_2_index  (cost=0.00..1592.02 rows=60641 width=0)"
"              Index Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Edit 3

The query plan for the LATERAL query from ypercube:

"QUERY PLAN"
"Nested Loop  (cost=8.98..1200.42 rows=133 width=20)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Result  (cost=8.98..8.99 rows=1 width=0)"
"        One-Time Filter: ($1 IS NOT NULL)"
"        InitPlan 1 (returns $1)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan using comp_2_index on counter c  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        InitPlan 2 (returns $2)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan Backward using comp_2_index on counter c_1  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Another idea, that also uses the groups table and a construction called LATERAL join (for SQL-Server fans, this is almost identical to OUTER APPLY). It has the advantage that aggregates can be calculated in the subquery:

SELECT group_id, min_ts, max_ts
FROM   groups g,                    -- notice the comma here, is required
  LATERAL
       ( SELECT MIN(ts) AS min_ts,
                MAX(ts) AS max_ts
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2011-03-02 00:00:00'
                        AND timestamp '2013-03-05 12:00:00'
       ) x
WHERE min_ts IS NOT NULL ;

Test at SQL-Fiddle shows that the query does index scans on the (group_id, ts) index.

Similar plans are produced using 2 lateral joins, one for min and one for max and also with 2 inline correlated subqueries. They could also be used if you need to show the whole counter rows besides the min and max dates:

SELECT group_id,
       min_ts, min_ts_id,
       max_ts, max_ts_id
FROM   groups g
  , LATERAL
       ( SELECT ts AS min_ts, c.id AS min_ts_id
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2012-03-02 00:00:00'
                        AND timestamp '2014-03-05 12:00:00'
         ORDER BY ts ASC
         LIMIT 1
       ) xmin
  , LATERAL
       ( SELECT ts AS max_ts, c.id AS max_ts_id
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2012-03-02 00:00:00'
                        AND timestamp '2014-03-05 12:00:00'
         ORDER BY ts DESC
         LIMIT 1
       ) xmax
WHERE min_ts IS NOT NULL ;


Mysqldump with ansible for database backup I am trying to backup remote database using ansible and mysqldump. I have installed ansible in my local server.Generated the ssh keys. I also sent the public key to the remote server.
Everything is fine in ansible part but can't backup using ansible and mysqldump.

I tried this:

ansible -m shell -a 'mysqldump -h 192.168.10.155 -u root -proot database_name  > database_name.sql' remote_server

But the backup is being created in home directory of remote_server. My requirement is that I would like to take the backup of remote_server into my local_server.

edit:

Now i using playbook:

hosts: remote_server
become: yes
tasks:

name: do a mysqldump
ignore_errors: yes
shell: mysqldump -h 192.168.10.155 -u root -proot database_name > dbname.sql

hosts: remote_server
tasks:

name: Transfer file from remote to local

synchronize:

src: /home/user/test.txt
dest: /home/user/backup
delegate_to: local_server

Backup is happening but how can i get the backup in the server i am running ansible from?

I studied that this can be done using playbook as well.

How can I achieve it with playbook?

I am really sorry to post this one. AS i later realized, this was more of a ansible solutions i was looking for rather than mysqldump.

I tried many alternatives and finally this one worked for me.

What this playbook does is, it on the first part takes the database backup using mysqldump and creates the backup in remote_server.

In the later part, it copies the backup from the remote_server to local server(the one hosting the ansible) with fetch command to local directory.

Once it is in the local directory, this can be copied to which ever server we want.

Hope  this one helps to someone else as well.

Thank you everyone for the help.


Auto update "TIMESTAMP" column only when a manual phpmyadmin edit occurs? I have a column in my MySQL database that uses a "TIMESTAMP" to capture the current time.  I have an attribute set to "on update CURRENT_TIMESTAMP" so it auto updates the column whenever and update change is made.

Works great, except I have a small issue. My database also tracks views/votes for each row so basically the TIMESTAMP column is always being update because of the views/votes being updated. This isn't exactly what I want as I only want the TIMESTAMP column to update when significant information is changed that is not views/votes.

Is there a way to make phpmyadmin force this column to update when I make a manual edit on a row using the phpmyadmin edit gui? Besides having to type in the current TIMESTAMP which is just too time consuming for me.

Thanks!

The defaults for TIMESTAMP are quite limited.  Instead of depending on it, take charge.  You should supply values when you need them:

UPDATE tbl  SET
        ...,
        ts = NOW()   -- Include this when you need it; else leave it out.
    WHERE ...


Why does a many to one merge join change the sort order of the data set? I have a query that calculates row_Number(). The table has a clustered index on the same columns (and order) as the partitioning and ordering for the row number.

When using a merge join (many to one), a sort is required, even though the clustered index is in the correct order. Removing the join also removes the sort operation.

The clustered index that should power the row_number calculation:

create clustered index [ClusteredIndex_e060df3fbf464a8eb9b6ea5d46a9a5f5] on [dbo].[log1]
(
    [client] asc,
    [orderId] asc,
    [campaign] asc,
    [id] asc,
    [DateStamp] asc
)

create clustered index [ClusteredIndex_dd0ee53e050d436cba2cab7c678a39e5] on [dbo].[LiveReference]
(
    [client] asc,
    [orderId] asc,
    [campaign] asc
)

The query:

    with cr as
(
    select distinct client, orderId,campaign
    from LiveReference
)

select e.[DateStamp]
  ,e.[campaign]
  ,e.[client]
  ,e.[orderId]
  ,e.[ad]
  ,e.[id]
  ,e.[source]
,row_number() over (partition by e.[client] ,
                        e.[orderId] ,
                        e.[campaign] ,
                        e.[id]
                    order by e.[DateStamp]) as num
from [dbo].[log1] e
inner join cr on
                        e.client = cr.client
                        and e.campaign = cr.campaign
                        and e.orderId =  cr.orderId

which gives the below plan:

Removing the join also removes the sort:

select e.[DateStamp]
  ,e.[campaign]
  ,e.[client]
  ,e.[orderId]
  ,e.[ad]
  ,e.[id]
  ,e.[source]
,row_number() over (partition by e.[client] ,
                        e.[orderId] ,
                        e.[campaign] ,
                        e.[id]
                    order by e.[DateStamp]) as num
from [dbo].[log1] e

(I know this also removes the filtering performed by the join, but this doesn't explain why excluding those rows changes the order)

Why would the results of a sorted join then not be in the correct order?

Generally speaking, merge join (including merge join concatenation) only preserves the sort order of the join keys.

The merge join keys are client, campaign, orderId. The required input sort order for the window function is client, orderId, campaign, id , datestamp.

The merge join cannot therefore provide the sort order required by your window function. You could avoid the sort with a nested loops join (e.g. using a hint).

I wrote about the details in Avoiding Sorts with Merge Join Concatenation.


MongoDB - Insertion Speed Suppose you have a collection of students of the following form:

{"_id" : ObjectId("50c598f582094fb5f92efb96"),
"first_name" : "John",
"last_name" : "Doe",
"date_of_admission" : ISODate("2010-02-21T05:00:00Z"),
"residence_hall" : "Fairweather",
"has_car" : true,
"student_id" : "2348023902",
"current_classes" : [
   "His343",
   "Math234",
   "Phy123",
   "Art232"
]}

Now suppose that basic inserts into the collection, which only include the last name, first name and student_id, are too slow (we can't do enough of them per second from our program). What could potentially improve the speed of inserts. Check all that apply.

1) Add an index on last_name, first_name if one does not already exist.

2) Remove all indexes from the collection, leaving only the index on _id in place

3) Provide a hint to MongoDB that it should not use an index for the inserts

4) Set w=0, j=0 on writes

5) Build a replica set and insert data into the secondary nodes to free up the primary nodes.

I'm sure that the number 4 is correct. The numbers 1,3,5 are not correct. I'm not sure on the number 2. Is it correct?

1) Add an index will slow inserts.

2) It will speed up inserts

3) Inserts don't use indexes

4) It will speed up writes but w=0 risk consistency and j=0 risk durability

5) You can't insert data to secondaries, insert/update/delete applies only to primary

If you care for insert speed you should use hash based sharding which evenly distribute writes among shards and increase write throughput. (http://docs.mongodb.org/manual/tutorial/shard-collection-with-a-hashed-shard-key/)


Swap Usage but high Freeable memory We have an AWS RDS with a MySQL database. Buffer Pool size is only 144GB out of the total available 244GB RAM. However, even at such a low RAM config, we see Swap Usage happening. Does anyone have any idea on how I can go about figuring out what is causing the swap usage?
We have Performance Insights enabled, and so also Enhanced monitoring. The Swap Committed value did go back down, but we are essentially wasting about 100GB of RAM, as increasing Buffer Pool size any further aggressively increases Swap usage.

There isn't much you can do about this with RDS. It means the OS decided that whatever got swapped out was less valuable than more page cache. You have no control over this with huge pages or vm.swappiness controls like you would on an EC2 instance.


why when i run my sql code, the terminal stop on some wired number? I am creating a trigger

The CREATE statement is :

CREATE TRIGGER TRIGGER01
AFTER INSERT ON WAREHOUSE
REFERENCING NEW AS NEWWAREHOUSE
FOR EACH ROW
WHEN (NEWWAREHOUSE.STATE = 'MAINE')
BEGIN
INSERT INTO WAREHOUSE_MAINE VALUES(:NEWWAREHOUSE.SHIPPEREIN,:NEWWAREHOUSE.WAREHOUSENAME);
END TRIGGER01;
INSERT INTO WAREHOUSE VALUES('108-34-9999', 'MoonHouse', 'MAINE', 'kapa');
SELECT * FROM WAREHOUSE_MAINE;

But when I run this with other data on Oracle, my terminal displays:

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

 25

It should be back to style

sql>

or warning me there's something wrong with my code at least, but it doesn't. What is wrong with my statement? It seems like when running this statement, it stops on line 25?

Your code above does not create the trigger, it just puts it to the SQL buffer, because this is how SQL*Plus works. One side effect of this is regular SQL statements not executing, or not getting the prompt back. Use a slash / after the END TRIGGER01; clause:

CREATE TRIGGER TRIGGER01
AFTER INSERT ON WAREHOUSE
REFERENCING NEW AS NEWWAREHOUSE
FOR EACH ROW
WHEN (NEWWAREHOUSE.STATE = 'MAINE')
BEGIN
INSERT INTO WAREHOUSE_MAINE VALUES(:NEWWAREHOUSE.SHIPPEREIN,:NEWWAREHOUSE.WAREHOUSENAME);
END TRIGGER01;
/
INSERT INTO WAREHOUSE VALUES('108-34-9999', 'MoonHouse', 'MAINE', 'kapa');
SELECT * FROM WAREHOUSE_MAINE;


Modeling Optional Column I've got an optional column (residential) in one of my tables that is giving me trouble. If a Licensee intends to work w/ residential customers, they need to take an additional course (course_topic_code C):

CREATE TABLE Licensees (
  license_num INTEGER PRIMARY KEY,
  license_level VARCHAR(50) NOT NULL,
  residential BOOLEAN
);

INSERT INTO Licensees VALUES
  (123, 'Salesperson', NULL),
  (234, 'Salesperson', TRUE),
  (567, 'Salesperson', FALSE);

CREATE TABLE Course_Requirements (
  PRIMARY KEY (license_level, residential, course_topic_code),
  license_level VARCHAR(50) NOT NULL,
  residential BOOLEAN,
  course_topic_code CHARACTER NOT NULL,
  required_minutes INTEGER NOT NULL
);

INSERT INTO Course_Requirements VALUES
  ('Salesperson', true,  'C', 90),
  ('Salesperson', true,  'A', 180),
  ('Salesperson', false, 'A', 180),
  ('Broker',      true,  'C', 90),
  ('Broker',      true,  'A', 180),
  ('Broker',      false, 'A', 180);

The trouble is, it's valid for me to not know whether a Licensee intends to service residential customers. If the column is true they're required to take that course, but if it is false or null they're not required. This is the query I've come up with to see required courses for each Licensee:

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l on cr.license_level = l.license_level
                and cr.residential = l.residential;

As you can see, the licensee w/ NULL for residential has no required courses:

567;"A";180
234;"C";90
234;"A";180

I might be able to devise a query that will give me what I want, but I feel that I might be modeling things incorrectly. I generally try to avoid nullable fields, but in this situation it seems appropriate. Is there a better way to model this?

question is not clear to me but this might be what you are looking for

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l on cr.license_level = l.license_level
                and cr.residential = isnull(l.residential,1);


How to export image filenames from Wordpress database? I have a Wordpress install that's using about 10,000 images. I'd like to export all filenames used in Wordpress to a file, filenames.log

filenames.log would look like this:

car.jpg
boat.jpg
milf.jpg
car2.jpg
other.jpg

etc...

The reason I'm doing this is that I have orphaned images in /uploads, and I want to run a delete operation and exclude these filenames that are actually in use.

What command could I run to generate such a file?

Thanks!

Pure MySQL, be sure to correct it for your desired filepath /tmp/filenames.log: (Thanks Phil for the select query)

"SELECT meta_value AS filename
FROM wp_posts p, wp_postmeta pm
WHERE p.id = pm.post_id
AND p.post_type = 'attachment'
AND meta_key = '_wp_attached_file'
INTO OUTFILE '/tmp/filenames.log'
LINES TERMINATED BY '\n';"


Why can't I set oracle sessions value to 35? I want to simulate the error "ORA-00018.Maximum number of sessions exceed", so I want to limit the number of sessions to 35, and open serverl sqlplus to simulate this error, but sessions number didn't change

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     248
shared_server_sessions               integer

change sessions from 248 to 35

SQL> alter system set sessions=35 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

restart oracle instance

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             763363792 bytes
Database Buffers          301989888 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.

Nothing changed

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     248
shared_server_sessions               integer

Can anyone tell me why I can't change this value to 35, and how to simulate the "ORA-00018.Maximum number of sessions exceed" error?

SESSIONS

  Default value Derived: (1.5 * PROCESSES) + 22

  ...

  Oracle uses the default value of this parameter as its minimum. Values
  between 1 and the default do not trigger errors, but Oracle ignores
  them and uses the default instead.

If you want to decrease sessions, you need to decrease processes as well. Still, if you decrease processes, you will have plenty of 'free' sessions, and you will need to open several sessions until you receive the error.

Easiest way to simulate the error without messing with processes and opening several connections:

alter system set dispatchers = '(PROTOCOL=TCP)';
alter system set shared_servers = 1;
alter system set shared_server_sessions = 0;

$ sqlplus user/password@\'127.0.0.1:1521/orcl:shared\'

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 21 11:38:41 2016

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

ERROR:
ORA-00018: maximum number of sessions exceeded


finding keywords in text using full text search in PostgreSQL I'd like to determine if a text input contains any keywords from a known list, but also show which ones are found.

E.g with Keywords: ['fox', 'jump', 'dog', 'foo', 'bar']

'the quick brown foxes jumped over the lazy dogs'

yields

['fox','jump','dog']

I appreciate that I can do boolean matching, but I can't work out if I can return the matched keywords, as opposed to a highlighted input snippet.

I'd like to use FTS so i can benefit from finding some synonyms etc.

Is this possible in Postgres?

You have two components of this problem

getting your array to a ts_query,
getting the keywords highlighted
getting the keywords out of the returned string

Now first and foremost, this is FTS. All words in the search string get tokenized as a ts_query. From that point on the original search string is lost. You no longer have words you have lexemes -- tokens which have been stemmed and normalized.

Getting your array to a ts_query,

Essentially, a ts_query looks like, 'fox' & 'jump' & 'dog' & 'foo' & 'bar'
You should review plainto_tsquery which does slightly more voodoo to get text to a tsquery, and to_tsquery which assumes the string is already properly formatted.

To get the array to a ts_query, you can do something like this

SELECT to_tsquery(array_to_string(myarray, ' & '))
FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
  AS t(myarray);

Highlighting

To get highlighting working you need something like this,

SELECT ts_headline(
  'quick brown foxes jumped over the lazy dogs',
  to_tsquery(array_to_string(myarray, ' & '))
)
FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
  AS t(myarray);

That returns

                           ts_headline
------------------------------------------------------------------
 quick brown foxes jumped over the lazy dogs
(1 row)

There are quite a few options on ts_headline. All of the docs for this are found in Controlling Text Search

Getting out the words

The only other thing you can do here is to pull out the words which triggered those tokens

SELECT array_agg(rm[1]) AS WORDS
FROM (
  SELECT ts_headline(
    'quick brown foxes jumped over the lazy dogs',
    to_tsquery(array_to_string(myarray, ' & '))
  )
  FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
    AS t(myarray)
)
  AS t(s)
CROSS JOIN LATERAL regexp_matches(s,'(.*?)', 'g') AS rm(matches);

        words
---------------------
 {foxes,jumped,dogs}
(1 row)


autoincrement column issue in MYSQL We are using an autoincrement column in a temporary table, and doing multiple time inserts. The autogenerated ids are not being generated consecutive (I have attached a sample simple query: 16 is generated after 9 instead of 10).

DROP TEMPORARY TABLE IF EXISTS tt_temp;

CREATE TEMPORARY TABLE tt_temp
   (
      id BIGINT   AUTO_INCREMENT PRIMARY KEY ,
      value VARCHAR(2)
   )  AUTO_INCREMENT = 1;

   INSERT INTO tt_temp (value) select ('a')  union all select  ('b')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c');
SELECT * from tt_temp;

INSERT INTO tt_temp (value) select ('d')  union all select ('b')  union all select ('c');

SELECT * from tt_temp;

will somebody help me on this.

The actual thing is the variable "innodb_autoinc_lock_mode" which control the behavior for auto_inc lock during generation of auto generated numbers. It can be set to three values which are 0 ,1 or 2.

The catch is the behavior while setting this, if you set it to 0 which ensures that gap is not produced it held the table level lock and parallel inserts are disallowed while if you set it to 1 or 2 it ensure the lock is not held at table level and it is recommended to be set to 2 as it is fastest and allows parallel insert.

Just to add there may be gaps if there are some deleted rows and auto increment ensures the uniqueness not sequence.

Hope it helps


mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size I am following this solution here https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size to 4G and later 1G (also 1024M) in addition to the log file size, but mysql wont start with those values.
If I put it back to 512M mysql starts fine.

How can I solve this? My server is a 16GB one, and according to Webmin sysinfo:

Real memory 15.62 GB total, 3.13 GB used

Meanwhile I found the error log as well:

  120529 10:29:32 mysqld_safe mysqld from pid file
  /var/run/mysqld/mysqld.pid ended

  120529 10:29:33 mysqld_safe Starting mysqld daemon with databases from
  /var/lib/mysql

  120529 10:29:33 [Note] Plugin 'FEDERATED' is disabled.

  120529 10:29:33 InnoDB: The InnoDB memory heap is disabled

  120529 10:29:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins

  120529 10:29:33 InnoDB: Compressed tables use zlib 1.2.3

  120529 10:29:33 InnoDB: Using Linux native AIO

  120529 10:29:33 InnoDB: Initializing buffer pool, size = 1.0G

  120529 10:29:33 InnoDB: Completed initialization of buffer pool

  InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728
  bytes

  InnoDB: than specified in the .cnf file 0 268435456 bytes!

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

  InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

  InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.


Database insanely (and weirdly inconsistently) slow on 8.0 I recently tried moving a large DB to 8.0, and am seeing insanely bad speeds (100x slower in some cases). I've spent the last 2 days googling this and feel like I've tried everything and have not had any luck.
Some info: I spun up a new VM for the new instance. It has more ram, same SSD drives and should be generally faster than the old one, but I still have that running so I can compare the two.
I pulled one of the queries I found acting slow, and did some testing:
Table accounting has ~100k rows. Here's where things get a little odd. I have a production and dev database on the new server and I am getting drastically different speeds. I have copied this particular table recently, so I know the table, structure and keys are identical in all 3 places.  (Note cash and status are both ENUM)
SELECT * FROM `accounting` WHERE type='cash' AND status='pending'
Response on 5.7: (Query took 0.0427 seconds.)
Response on 8.0 dev db: (Query took 0.7911 seconds.)
Response on 8.0 prod db: (Query took 4.1671 seconds.)

The first digit on those response times are consistent every time. I've speed tested the underlying hard drive and it is testing faster than on the old server.
I've tried every config option that came up while googling and none of them help significantly.  Here is what I have.  I've tried larger and smaller versions of most of these options.  This server has nothing else happening on it, and this site isn't using it in production right now so I can experiment and restart as much as I need.
join_buffer_size=512K
sort_buffer_size = 4M
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances=5
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 16
innodb_write_io_threads = 64
innodb_log_file_size = 3G
innodb_log_buffer_size = 64M
innodb_lru_scan_depth=100
#optimizer_switch='derived_merge=off' #this significantly hurts performance
join_buffer_size=8M
tmp_table_size = 256M
max_heap_table_size=256M
table_open_cache=4000
performance_schema=OFF #roughly 25% speed increase
skip-log-bin
sync_binlog=0
innodb_flush_log_at_trx_commit=0

I'm really at a loss here. Any help would be appreciated.
Edit:  I tried adding a type+status index on prod-8.0 db, and the speeds were blazing.  When I removed the index, I am now getting speeds consistent with dev-8.0 db.  Not sure what this tells us.
Edit 2: Table I'm testing with is MyISAM.
Update (and solution):
I ended up installing mariadb, and the queries are blazingly fast.  I guess it's time to hop off the MySQL train.

Welcome to the growing query planner insanity heisenbug. It started in 5.7 and got worse in 8.0. I suspect what you will find is that EXPLAIN for that query looks strikingly different between 5.7 and 8.0.

Your options are:

1) See if it regains some sanity if you change optimizer_% variables to the same values as on 5.7

2) Run ANALYZE TABLE accounting; repeately and hope it'll change it's mind. Note that this is subject to change as statistics estimation runs periodically in the background.

3) Use index hinting (USE/FORCE INDEX(indexname)). This is stable, but you'll be whack-a-mole-ing queries throughout your code base.

4) Switch to MariaDB which doesn't seem to suffer from this kind of query plan instability.

Don't use MyISAM unless you have an overwhelmingly good reason for doing so.


How to grant select on a view ... based on IP adress? I wonder if it's possible to allow a client A to "see" some data in a view while allowing any other clients, namely B and C, to see ALL data from the same view.

The distinction between A, B and C would rely on their IP address.

You could use the session information function inet_client_addr(), with limited reach. It reports the IP the server is receiving the current connection from. This is only useful with direct connections to the database, though.

As proof of concept:

SELECT CASE WHEN inet_client_addr() << '192.168.1.32/30'::inet
         THEN 'foo'
         ELSE 'bar'
       END AS odd_column

<<.. "is contained within" operator.

As @Craig mentioned in a comment, consider the security_barrier option for a view like that:

CREATE VIEW my_odd_view WITH (security_barrier) AS ...

Details in the manual here and here.

The whole idea is rather dubious, though. The proper way would be to use distinct roles and distinct views with grants only for privileged roles.


Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures.

I have the following tables setup.

I normalized the tables to avoid using NULLs. The problem is that some of these tables depend on each other due to business processes. Some devices must be sanitized, and some are tracked in another system. All devices will eventually be disposed in the Disposal table.

The issue is that I need to perform checks, such as if the boolean field RequiresSantization is true, then the DisposalDate cannot be entered until the Sanitize fields are entered.

Also, if the boolean value IsTrackedInOther is true, then the OfficialOutOfService fields must be entered before the DisposalDate can be entered.

If I merge all of these columns into the Archive.Device table then I will have NULL fields, but I will be able to manage all of the business rules using CHECK constraints.

The alternative is to leave the tables as they are, and manage the business logic in the stored procedure by selecting from the tables to check if records exist and then throw appropriate errors.

Is this a case where NULL can be used appropriately? The boolean fields IsTrackedInOther
and RequiresSanitization basically give meaning to the NULL fields. If IsTrackedInOther is false then the device is not tracked in the other system and SectionID and SpecialDeviceCode are NULL, and I know that they should be NULL becuase it is not tracked in the other system. Likewise, OfficialOutOfServiceDate and OOSLogPath I know will be NULL aswell, and a DisposalDate can be entered at any time.

If IsTrackedInOther is true, then SectionID and SpecialDeviceCode will be required, and if OfficialOutOfServiceDate and OOSLogPath are NULL, then I know they have not been officially removed from that system yet and thus cannot have a DisposalDate until they are entered.

So it's a question between

separate tables/no NULLs/enforce rules in stored procedures

vs

combined table/NULLs/enforce rules in CHECK constraints.

I understand that querying with NULLs in the picture can be complex and have somewhat undefined behavior, so separate tables and stored procedures seem beneficial in that sense. Alternatively, being able to use CHECK constraints and have the rules built into the table seems equally beneficial.

Any thoughts? Thanks for reading. Please ask for clarification where needed.

Update Example table if they were merged and I allowed NULLs.

* = Allow NULL

+-------------------------+
|Archive.Device Table     |
+-------------------------+
|DeviceID                 |
|SerialNumber             |
|DeviceTypeID             |
|IsTrackedInOther         |
|SectionID*               |
|SpecialDeviceCode*       |
|OfficialOutOfServiceDate*|
|OOSLogPath*              |
|OOSRemarks*              |
|RequiresSanitization     |
|SanitizeMethodID*        |
|SanitizeLogPath*         |
|SanitizeDate*            |
|SanitizeRemarks*         |
|Location                 |
|OriginalInventoryDate    |
|ArchiveDate              |
|LastUpdated              |
|ReasonID                 |
|StorageLocation          |
|ArchiveRemarks           |
|CategoryCode             |
+-------------------------+

Example CHECK:

IsTrackedInOther = 0 AND SectionID IS NULL AND SpecialDeviceCode IS NULL AND
OfficialOutOfServiceDate IS NULL AND OOSLogPath IS NULL AND OOSRemarks IS NULL
OR IsTrackedInOther = 1 AND SectionID IS NOT NULL AND SpecialDeviceCode IS NOT NULL

Does this seem a natural way to handle this, or is this a design problem?

Querying a column that can contain nulls is more complex than querying a column that cannot.  So also is querying multiple tables more complex than querying one table.  I wouldn't let the avoidance of null drive the normalization.

For example, you mentioned that all devices will eventually be disposed and get a disposal date.  If there are no other columns in the Disposal table, then in my mind it makes more sense to put DisposalDate in the Device table.  Other tables like Sanitize might make more sense as separate tables because there are multiple data points that will not apply to some Devices.

Check constraints are great and should be used when possible, but there will always be times when a procedure is necessary.


mytop to monitor multiple mysql servers I have multiple MySQL servers running on CentOS. I would like to use mytop to monitor real time database query activities, but do not want to install mytop locally on each of my MySQL instances. How can I install mytop on a network server to monitor multiple MySQL servers (nodes) on the same network just by running a command like the one below for example:

mytop -h db-test-1

This should display results for database db-test-1, and for db-test-2 or 3, just basically change the db hostname appropriately.

Magicly, mytop thought of your question, a decade in advance, and had a -h flag. It also supports -u for user, -p for password, -P for port, and other options as per the mytop pod page in a similar way to mysql.

So you're able use it on a remote host like:

mytop -h db-test-1


how to check who has killed the job which was running ?if logs are not generated Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

This is error I am getting.(when the job is executed)

  [SQLSTATE HY000] (Error 0)  Cannot continue the execution because the session is in the kill state.
  [SQLSTATE HY000] (Error 596).  The step failed.

The error log should contain some information about what session killed a particular process.  Run the following statement to get the hostname and process id of the user the KILL command originated from.

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

Note that if any time has gone by since the command was run, this may return stale results as session ids are reused.  Using the Host Process ID, you can then find the user:

select login_name, session_id, host_name, host_process_id
from sys.dm_exec_sessions where host_process_id = XXXX AND session_id = YY

Here's an example:

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------------------------------------------
2017-06-20 07:17:32.210 spid64       Process ID 62 was killed by hostname JOHNLAPPY, host process ID 7968.

(1 row(s) affected)

select login_name, session_id, host_name, host_process_id from sys.dm_exec_sessions where host_process_id = 7968 AND session_id = 62

login_name              session_id host_name  host_process_id
----------------------- ---------- ---------- ---------------
CONTOSO\john.eisbrener  62         JOHNLAPPY  7968

(1 row(s) affected)


PostgreSQL partition pruning? In postgres partition is done by child tables. My problem are,

when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables.
How does the query optimizer do the partition pruning ? My concern is unlike other databases we don't give partition column in postgres partitioning process. So how does the query optimizer knows what is the partition column ?

when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables.

Yes if the query is simple enough to prove with constraint exclusion. See the manual.

  How does the query optimizer do the partition pruning

By proving that the query cannot access those partitions using CHECK constraints. See the documentation link above.

Because the constraint values must be known at planning time there are some cases where the planner cannot exclude partitions because it doesn't know the values of predicates early enough in query execution. Sometimes these queries land up needing to be split into two parts manually.

You can see which partitions are being scanned by using EXPLAIN.


Instance Recovery in SQL Server? When the instance crashes due to unexpected disaster in SQL Server, how does the SQL Server recover the databases?

SQL Server goes through which phases to recover the data and which are the steps that are performed in the instance recovery by SQL Server?

(Copied from https://stackoverflow.com/questions/2391399/three-phases-of-recovery-the-analysis-phase-the-redo-phase-and-finally-the-u)

You might find this document interesting : Simple overview of the SQL Server Recovery Process

  Phases of recovery

  the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.

  Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.

  Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.

  Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.

Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).


SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working I'm trying to perform a indentity insert inside a instead of insert trigger, but when I try to insert data into the data, it throws and error msg "Msg 544, Level 16, State 1, Line 36
Cannot insert explicit value for identity column in table 'PixTest' when IDENTITY_INSERT is set to OFF.".

I'm using SQL SERVER 2014 Developer Edition 64 Bit installed on Windows 8 64 bit.

Here is some sample code that might help:

CREATE TABLE PixTest
(
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
      TESTE VARCHAR(10)
)
GO

CREATE TRIGGER AutoIncrement_Trigger ON PixTest
instead OF INSERT AS
BEGIN

    DECLARE @ID INT;
    SELECT @ID=ID FROM inserted;

    IF COALESCE(@ID,0)<> 0
        BEGIN

            SET IDENTITY_INSERT PixTest ON
            INSERT INTO PixTest(ID, TESTE)
            SELECT ID,TESTE FROM inserted
            SET IDENTITY_INSERT PixTest off

        END

    ELSE

        INSERT INTO PixTest(Teste)
        SELECT TESTE FROM inserted;

END;
go

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')
GO

I googled about using SET IDENTITY_INSERT inside of a trigger, and couldn't find anything saying it is not possible, but the code above doesnt seem to work.

Any ideas?

The error isn't coming from the trigger.  It's coming from your insert statement.

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')

It's getting parsed and returning an error before you ever get to the trigger.  Change it to this and it works.

INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')


First query running in PL/SQL Developer results the error When I run first query in PL/SQL Developer I got the error:

Dynamic Performance Tables not accessible,

Automatic Statistics disabled for this session

You can disable statistics in the preference menu, or obtain select
privileges on the v$session, v$sesstat and v$statname tables

How can I fix the error?

It seems your question contains the answer.
By default PL/SQL Developer gathers statistical data per session for every query.
In the SQL Window and in the debugger's Test Window you can use the Statistics page to view the resources used by the last executed SQL statement or program unit. This information can be a big help when optimizing your SQL or PL/SQL code.

If the user you have logged in, dose not heve the privilege to query on v$ views (as described in the error description) you will not be able to see the statistics result in Test Window and the message will be shown.
To solve the problem you may choose any of the approaches bellow:

Grant the user the privilege to select on desired v$ views.
GRANT SELECT ON sys.V_$SESSION  TO scott;
GRANT SELECT ON sys.V_$SESSTAT  TO scott;
GRANT SELECT ON sys.V_$STATNAME TO scott;
Or disable automatic statistics in PL/SQL Developer in preferences menu.


Mysql connect without username/password I build a new website for a client (WordPress is underlying) and have access trough FTP to his webspace. He does not provide or even know more informations than this because he had taken it from an former employee.

Is it possible to get know if any MySQL database exist on localhost and connect to it without the knowledge of an username or password?

I dont think that the web space host could help me with this problem, because they switched there business orientation a while ago and don't provide any new web space at all.

I do not think it is possible to attempt to connect to a database without knowing the username, password, or whether it even exists.

If you could have someone log into MySQL (requires admin privileges without the MySQL name/password) they could run mysql> show databases; at the MySQL command line interface to get you started.


Surprising results from assigning multiple variables in FOR loop CREATE OR REPLACE FUNCTION my_function(input jsonb)
  RETURNS jsonb
  LANGUAGE plpgsql AS  -- language declaration required
$func$
DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
      SELECT jsonb_each_text($1)
    LOOP
        -- do some math operation on its corresponding value
        RAISE NOTICE '%: %', _key, _value;
    END LOOP;

    RETURN input;
END
$func$;

For the function, if I call:

my_function('{"a":1, "b":2}');

It will raise notice that looks like (a, 1), (b, 2) in the message box.

However, if I change the code:

        RAISE NOTICE '%: %', _key, _value;

into:

        RAISE NOTICE '%', _key;

The same results will pop up.

While the code:

        RAISE NOTICE '%', _value;

would pop up nothing. How did that happen?

Guess I am to blame for this confusion.
The code example is from my answer to your previous question, which had a subtle bug (now fixed).

What was going wrong?

jsonb_each_text() is a set-returning function, it returns a set of rows. When called with:

SELECT * FROM jsonb_each_text('{"a":1, "b":2}')

it returns:

 key | value
-----+-------
 a   | 1
 b   | 2

But when called like above with:

SELECT jsonb_each_text('{"a":1, "b":2}')

it returns:

 jsonb_each_text
-----------------
 (a,1)
 (b,2)

Each row is returned with a single value, a row value, the text representation of which is as displayed.

Now, the assignment in:

  FOR _key, _value IN SELECT jsonb_each_text($1) ...

assigns the first value per row to the first variable provided, which is _key in the example. The row value is cast to text in the assignment. Since there is no second value, _value is set to NULL. So, the later:

 RAISE NOTICE '%: %', _key, _value;

outputs the messages:

NOTICE:  (a,1): <NULL>
NOTICE:  (b,2): <NULL>

Which can be pretty misleading. (But note the dangling : <NULL>!)

Change to:

FOR _key, _value IN SELECT * FROM jsonb_each_text($1)

.. and the mists should clear.


MySQL Workbench shows running query but query not in process list I am having an issue where the same query sometimes completes in 600 seconds or less, but sometimes lags for multiple hours. MySQL Workbench shows the query as 'Running...', and initially I can see the query in the processlist. But after a significant amount of time after the query should have already returned results, I check the processlist and the query is no longer executing--yet Workbench still shows that it is running. What is happening to the query and why doesn't Workbench immediately give me an error code?
Sometimes if I wait a sufficiently long time to trigger a timeout I do get the 2013 error.
I am using a MacBook Pro running Big Sur connecting to a remote MySQL server through a VPN.
Could this be explained by poor network connectivity?

It may happen when a transaction is not committed or even roll-backed.
I had similar issues regarding incomplete queries in here when-a-table-is-locked-but-show-processlist-shows-nothing.
Hope that help you.


PostgreSQL: WHERE ID IN file_with_ids I have a file with ids which looks like this:

10555
16452
17005
17511
17512
17513
17515
17516
17517
17518
17520
17521
17522
17523
17524
17525

This file was created with unix tools like grep and cut.

I would like to execute a SQL statement in PostgreSQL roughly like this:

SELECT * from my_table where id in file_with_ids;

Is there a simple way to do this?

As a_horse_with_no_name's suggestion, the first step you need a CSV file that stores ids. Then, import it to database and execute your query as below

CREATE TEMP TABLE tmp_list_ids (id int);
COPY tmp_list_ids FROM 'your absolute path here' CSV;
SELECT * FROM your_table WHERE id IN (SELECT t.id FROM tmp_list_ids);

Please note that the COPY command is only allowed to superuser (bold is mine)

  Files named in a COPY command are read or written directly by the
  server, not by the client application. Therefore, they must reside on
  or be accessible to the database server machine, not the client. They
  must be accessible to and readable or writable by the PostgreSQL user
  (the user ID the server runs as), not the client ...
  COPY naming a file or command is only allowed to database superusers
  or users who are granted one of the default roles
  pg_read_server_files, pg_write_server_files, or
  pg_execute_server_program, since it allows reading or writing any file
  or running a program that the server has privileges to access.


How to take offline a database in Oracle? We have direct functionality of Take Offline to SQL

but how to do same functionality in Oracle Sql Developer

Open a DBA connection instead of a regular connection (View / DBA to display the DBA panel), where you can use the Modify State menu:


Dropping a table with a non-printable character in its name I have a legacy table with a name containing non-printable characters (CHAR(31), to be specific).

The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+_ creates the CHAR(31) character (which means "US" - Unit Separator). The previous developer could have hit this combination by mistake, and created the table with this weird character in it.

When we issue a SELECT command against the table, it returns results. But when we try to issue any DDL against it (DROP, sp_rename, etc), the character causes a problem.

Examples:

DROP TABLE Table_Name;

Raises:

  Msg 15225 - No item by the name of 'Table_Name' could be found in the current database 'MyDB', given that @itemtype was input as '(null)'.

EXEC sp_rename N'Table_Name', N'NewTableName';

Raises:

  Msg 102 - Incorrect syntax near '_Name'.

I already duplicated the table with the correct name, and have corrected it on the dependent objects. The remaining step is just dropping it from the database.

Just an insight: when we copy+paste from SQL Server to Notepad++, it shows the hidden character ("US") on the middle of the table name, beside the underscore:

Since you already know which character is hidden, you can easily construct the current name using a variable, pass that into sp_rename, and then drop the table using the newly acquired name:

DECLARE @t nvarchar(128) = N'dbo.Table' + NCHAR(31) + N'_Name';
EXEC sys.sp_rename @t, N'NewTableName', N'OBJECT';
DROP TABLE dbo.NewTableName;

(Always be careful about using the proper schema identifier.)

Even easier would be to take your string that you know has the character in it, and wrap it in square brackets. This worked for me just fine:

DROP TABLE dbo.[Table_Name];
---------------------^
-- There is an NCHAR(31) right before the underscore

If you didn't know the trouble character(s), you could pull the name from the catalog view, identifying it by surrounding characters, then loop through the characters to determine which are problematic. Here's a full repro:

USE tempdb;
GO

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'CREATE TABLE dbo.[Table' + NCHAR(31) + N'_Name](ID INT);';
EXEC sys.sp_executesql @sql;
GO

DECLARE @var NVARCHAR(128);
SELECT @var = name
  FROM sys.tables
  WHERE name LIKE N'Table%Name';

-- is it the right one?
PRINT @var;

DECLARE @i INT;
SET @i = 1;
WHILE @i <= LEN(@var)
BEGIN
  PRINT SUBSTRING(@var, @i, 1) + N' => ' + RTRIM(UNICODE(SUBSTRING(@var, @i, 1)));
  SET @i = @i + 1;
END

Results:

Table_Name
T => 84
a => 97
b => 98
l => 108
e => 101
 => 31
_ => 95
N => 78
a => 97
m => 109
e => 101

And then don't forget to chide or smack the person responsible for a table getting into the system that way in the first place, if they're still employed in the industry.


Enabling "log_queries_not_using_indexes" disables "long_query_time"? I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far less than long_query_time.

It seems that once the log_queries_not_using_indexes is enabled, Mysql will log all the queries that not using indexes no matter what the "long_query_time" is.

Is there a way to overcome this? I mean, is there a way to log all the queries which take longer time than long_query_time, regardless of whether they are using indexes?

That is expected behavior.  Refer to the docs online, but in summary:

long_query_time is the threshold for query execution time beyond which it is logged.  Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.
log_queries_not_using_indexes tells MySQL to additionally log all queries that do not use an index to limit the number of rows scanned.  Logging on this condition happens regardless of execution time.

Hope that helps explain what you are seeing.  It seems like you probably just want long_query_time and not log_queries_not_using_indexes if your goal is to only capture queries that take longer than a particular threshold.

Doc links:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time


How to get the settings associated with a stored procedure in relation to filtered indexes? I quite like using filtered indexes in the proper context.

I have added the following filtered index:

USE [COLA];
CREATE UNIQUE NONCLUSTERED INDEX F_InterviewReport
ON [dbo].[tbl_application_documents] (  [DocumentID] ASC )
include( State )
WHERE (DocumentClass = 'cadirect.onlineforms.InterviewReport')
WITH (  PAD_INDEX = OFF, FILLFACTOR = 90   , SORT_IN_TEMPDB = OFF , ONLINE = OFF,
DROP_EXISTING = ON,
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]

everything worked fine on server_staging.

but on server_production I got the following error message:

  Msg 1934, Level 16, State 1,  Procedure usp_ins_document_details, Line
  32 [Batch Start Line 2] INSERT failed because the following SET
  options have incorrect settings:  'ANSI_NULLS, QUOTED_IDENTIFIER'.

  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or filtered indexes and/or query
  notifications and/or XML data type methods and/or spatial index
  operations.

Both servers are at the moment at this Cumulative update:

  Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64)
  May 16 2019 02:24:21   Copyright (c) Microsoft Corporation  Enterprise
  Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
  Datacenter 6.3  (Build 9600: ) (Hypervisor)

the procedure in question is very trivial, it is posted here:

CREATE PROCEDURE [dbo].[usp_ins_document_details]
/*
 * author's name
 * 27/07/2004
 *
 * Amended
 * 28/10/2005
 *
 * Prevent the SP trying to insert the same record twice -- seems to
 * be happening with regards to the InterviewReport. Quicker to sort
 * this out that debug millions of lines of code.
 *
 * _sigh_ I am *such* a hack.
 *
 */
(
 @appid   numeric,
 @doc   varchar(50),
 @path   varchar(255)
)
AS
 DECLARE @already int

 SELECT  @already = COUNT(*)
 FROM  tbl_application_documents
 WHERE applicationid = @appid
 AND documentclass = @doc

 IF( @already = 0 )
  BEGIN

  INSERT INTO  tbl_application_documents
    (ApplicationID,DocumentClass,UserFilePath,DateCreated)
  VALUES  (@appid,  @doc, @path, getdate() )
  SELECT @@IDENTITY AS DocumentID

  END
 ELSE
  BEGIN

  SELECT  DocumentID
  FROM  tbl_application_documents
  WHERE applicationid = @appid
  AND documentclass = @doc

  END

questions:

How can I find out the current settings of the procedure in each of those servers?
SET options have incorrect settings:  ANSI_NULLS, QUOTED_IDENTIFIER - what are the correct settings?

I think those setting are in your SSMS session (not in the proc itself unless you add the "Set" command in there).

Check in the SSMS [tools] -> [options] then under [Query Execution] -> [ANSI].

You can compare the setting and make sure they match with the SSMS (that is probably running locally on your non-prod server)


Create/Delete file using SQL Server 2008 I have an Excel file in C:\testing.xlsx. I am exporting data from my database table to Excel using [OPENROWSET (Transact-SQL)]1 method. Now I want to

clear or delete all rows in my Excel file by executing some query in SQL Server
delete or create new Excel file in specified path (ie C:\) from SQL Server

Can I do this? If, please suggest me how to do that?

I have got one very useful link which i have not tried. But, its a great stuff by author...


How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set? I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was reduced but the server's ibdata1 file went crazy. Any ideas?

You may find this surprising, but did you know that there are several moving parts to ibdata1? Even with innodb_file_per_table enabled, here the classes of information stored in ibdata1

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

Pictorial Representation of InnoDB Architecture

What can make ibdata1 grow suddenly?
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

As long as your total InnoDB dataset is relatively small and you would like to shrink ibdata1, you can do the following:
STEP 01: Schedule downtime
STEP 02: mysqldump all databases to /root/MySQLData.sql
mysqldump -uroot -p --all-databases --routines --triggers > /root/MySQLData.sql

STEP 03: Run SET GLOBAL innodb_fast_shutdown = 0;
STEP 04: Drop all databases except the mysql and information_schema database
STEP 05: service mysql stop
STEP 06: rm -f /var/lib/mysql/ib*
STEP 07: service mysql start (recreates ibdata1, ib_logfile0, ib_logfile1)
STEP 08: Login to mysql
STEP 09: At mysql prompt, run mysql> source /root/MySQLData.sql
That's it. I have done this many, many times : Howto: Clean a mysql InnoDB storage engine?
From here, you just have to live with the weird growth due to transactions.
Give it a Try !!!


Slow MySQL sessions table I am currently trying to figure out why the site I am working on (Laravel 4.2 framework) is really slow at times, and I think it has to do with my database setup. I am not a pro at all so I would assume that where the problem is

My sessions table has roughly 2.2 million records in it, when I run show processlist all the queries that take the longest relate to that table.

Here is an example screen shot:

Table structure:

Surely I am doing something wrong or it's not indexed properly?
I'm not sure as I'm not fantastic with databases.

use explain on your query (it will provide output on your execution)
add index on your table


Difference between candidate key & primary key What is the differences between candidate key and primary key?

I think this post does a pretty good job of explaining the difference.

To summarize:

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key.


Restore database from backup file of different version / edition I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons.

Does anyone know off hand if you can restore a database from a *.bak file for different editions of SQL Server?  We are moving a very large database via FTP that will take a couple days, so we'd rather only do this once. If nobody responds by the time we transfer the database via FTP, we will obviously try this out and see if it works by testing, and answer our own question.

Below is a query to get version details of SQL Server.  The productversion is in the format {major revision}.{minor revision}.{release revision}.{build number}.  In my case, the {release revision} has a value of 5500 for the source and 5512 for the target.  So that looks okay. However, the edition is different.

Query:

SELECT
  SERVERPROPERTY('productversion'),
  SERVERPROPERTY('productlevel'),
  SERVERPROPERTY('edition')

Source database:

10.0.5500.0
SP3
Developer Edition (64-bit)

Target database:

10.0.5512.0
SP3
Enterprise Edition (64-bit)

From Developer to Enterprise will be fine, just be sure that if you are using processor licensing you have licenses on the target server to cover all of the CPUs. And it's not enough to just hide them from SQL, if they're physically connected to the machine, you're responsible for them.

Also when you go from a lower build to a higher build your database version will increase. There are a few scenarios where this can be problematic - e.g. if you are using 15,000 partition support on a specific build of 2008 it will not work when you upgrade to a specific build of 2008 R2. You may also be relying on optimizations (and have workarounds in place) that are actually bugs in an older build but are fixed in the new build, and this may lead to worse performance. It is also vital to review any trace flags in use at the source and determine if they should also be enabled at the destination. Never mind jobs, logins, etc.

Of course you cannot go backwards. I've never tried a minor downgrade like 10.0.5512 -> 10.0.5500 but it is definitely not possible to go down in service pack or version. So if you have a 2012 database on your Developer Edition instance and you want to put it on your 2008 instance in production, you'll have your work cut out for you (see here and here) - especially if you've used 2012 features.

But to cover other cases that might land people at this question (e.g. someone wants to go from Developer -> Standard or Enterprise -> Express or what have you)...

There are other edition -> edition upgrades that won't go so well, e.g. from Developer -> Express if you've used any features that aren't supported in Express (and same goes for any edition other than Enterprise really). Some examples of features you won't be able to use on down-level editions (in which case the restore will die at the point it tries to bring the database online):

Partitioning
Change Data Capture
Data Compression
Transparent Data Encryption

I don't know if there's a way to tell this directly from the .BAK file (I'm sure there's some magic that can be extracted from page headers somewhere, or if you've got a weekend to burn with a hex editor), but while the database is still intact on the source instance, you can always do the following to see if you're using any features that are available because of the SKU you're in:

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

I'm not sure if SQL Server Audit should be on that list - the edition exclusivity of that feature has changed, so it probably depends on what you're doing with it. There are other things that you might be using but won't show up in the DMV (some because they are in your code, which the DMV doesn't parse, and some because your database is relying on external things such as SQL Server Agent, Service Broker, etc.):

mirroring
certain forms of replication
log shipping
database snapshots
online indexing
updateable distributed partitioned views
backup compression
policy-based management
plan guides
database mail
maintenance plans
full-text search

There are also cases where you won't be able to go from Developer to Express because of file size limitations (Express databases are limited to 10GB in total data file size).

Of course there may be other gotchas that you won't be warned about - they won't prevent the migration, but they might lead to very different performance on the target. Examples:

Different memory / CPU limitations on the target edition (or even the underlying operating system on the target). This bit a lot of people who went from 2008 R2 Enterprise to 2012 Enterprise (CAL), where the service is artificially limited to the first 20 cores). This can lead to straightforward performance differences (not enough memory to satisfy a query, for example, or much slower parallel query performance); more subtle ones include plan choices that are made because of the different underlying hardware.
Reliance on features such as indexed view matching on the source will not be automatically respected on the target without changing source code to use NOEXPAND. And you may not even be aware that this capability is why your queries suddenly slow down.
Same goes for parallel index operations and probably a slew of other optimizations that aren't coming to mind this moment (thankfully I work almost exclusively in the Enterprise space, so I don't have to worry about the limitations of lower editions in most cases).

UPDATE based on this duplicate:

There may be cases where you try to restore a database from a certain edition to a lesser edition (even on the same version), and you get errors that are less than helpful:

  RESTORE failed for server 'server\instance'.
  RESTORE could not start database 'databasename'.

This isn't very intuitive. However if you look deeper in SQL Server's event logs, you will see more useful errors (just one example):

  Database 'databasename' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
  Database 'databasename' cannot be started in this edition of SQL Server because it contains a partition function '_dta_pf__9987'. Only Enterprise edition of SQL Server supports partition functions.

Now, that's not quite true - you can also restore to Evaluation Edition or Developer Edition, but that's beside the point. In order to restore this database, you basically have two options:

Restore to an appropriate edition of SQL Server - which will mean locating or installing a new instance.
Restore the backup on the source server as a new database with a different name, remove any and all Enterprise features, then backup the database again, and restore that on the lesser edition. (In this specific case, I left the name of the partition function in the error message, because this seems like a discardable thing anyway - it was created by the Database Engine Tuning Advisor and may have been done so by someone who didn't quite know what they were doing. This isn't always the case.)

A variation on (2) would be to just remove the partitioning and other features on the source database, and take another backup. But if it ain't broke...


MySQL store current binary log in different folder from past binary logs` Is there any way to do this? Specify a different directory for currently active binary log.

(It would nice if possible, because then we could store past binary logs in a compressed file system without compromising write performance of the current log.)

Why?  Replication was not designed with the thought of keeping binlogs for very long.  Very few systems ask to keep them longer than 2 weeks.  To use very old logs is costly due to the effort to rebuild the system and replay the logs.  It is usually better to periodically take some form of snapshot.

That being said, I will get back to your Question.  How about the following...  You write a shell script that runs daily and does:

If less than X GB of data in uncompressed binlogs, exit silently.
Copy the oldest binlog to the compressed filesystem.
Replace that binlog with a soft link to the copy.

Meanwhile, set up MySQL to never purge binlogs, and add something to your script to do the equivalent purging.  (I think there are some edge cases that will go haywire otherwise.)

Any reference from MySQL to the compressed binlogs should work fine; however there won't be any (unless a Slave is more than X GB behind).


Geography union via cursor returning null I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is STUnion which only works for two static geography records. I have upwards of 200 that I need to union and I'm loathe to hand code that kind of query.

On that note, I wrote up a one-off cursor to iteratively union each geography object from my staging table. However, when executed, the cursor returns a null value without any errors or messages.

DECLARE
    @ShapeUnion GEOGRAPHY,
    @Shape GEOGRAPHY

DECLARE curSpatial CURSOR FOR
SELECT
    geom
FROM
    dbo.Boundaries_Staging

OPEN curSpatial

FETCH NEXT FROM curSpatial INTO @Shape

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ShapeUnion = @ShapeUnion.STUnion(@Shape)

    FETCH NEXT FROM curSpatial INTO @Shape

END

CLOSE curSpatial
DEALLOCATE curSpatial

SELECT @ShapeUnion

Edit: Updated script for anyone curious about unioning multiple geography records. As stated in the comments, I initialized @ShapeUnion prior to the loop. Also, I added .STBuffer() to the field being unioned; this ensures that the miniscule spaces between the spatial data are completely filled.

DECLARE
    @ShapeUnion GEOGRAPHY,
    @Shape GEOGRAPHY

DECLARE curSpatial CURSOR FOR
SELECT
    geom
FROM
    dbo.Boundaries_Staging
WHERE
    geom IS NOT NULL

OPEN curSpatial

FETCH NEXT FROM curSpatial INTO @Shape

SET @ShapeUnion = @Shape

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ShapeUnion = @ShapeUnion.STUnion(@Shape.STBuffer(1))

    FETCH NEXT FROM curSpatial INTO @Shape

END

CLOSE curSpatial
DEALLOCATE curSpatial

SELECT @ShapeUnion

Try inside the loop:

SET @ShapeUnion = COALESCE(@ShapeUnion.STUnion(@Shape), @Shape);

(Or initializing @ShapeUnion somehow first.)

You'll also want to filter the cursor query to not include any rows where geom is NULL (if that is possible in your data model).

You can also do this without a cursor, I think:

DECLARE @ShapeUnion GEOGRAPHY;

SELECT @ShapeUnion = COALESCE(@ShapeUnion.STUnion(geom), geom)
  FROM dbo.Boundaries_Staging
  -- WHERE geom IS NOT NULL
  ;

SELECT @ShapeUnion;


SQL Server performance sudden degradation I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full table scan or index spool). Now the first and most obvious thing is, the statistics are obsolete causing the optimizer to get confused but I am convinced this is not the case - firstly because the underlying data isn't significantly changing (e.g. adding one day's data ontop of a year's data already in a table) and secondly because Auto Create Statistics and Auto Update Statistics are both true. However the optimizer is getting confused; running the SQL in the Tuning Advisor gives me lots of multi-column CREATE STATISTICS statements which do seem to fix it (until the next bit of SQL misbehaves).

Any ideas of a strategy I can use to approach root-causing this? Any why the "normal" statistics aren't sufficient?

From MSDN:

"Insert Operations Occur on Ascending or Descending Key Columns
Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs. Insert operations append new values to ascending or descending columns. The number of rows added might be too small to trigger a statistics update. If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. This can result in inaccurate cardinality estimates and slow query performance.

For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

After Maintenance Operations
Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates."

You might use "EXEC sp_updatestats" from time to time on your system (scheduled some time) or use the function STATS_DATE on all the objects and see when their statistics were actually updated last time and if there was too much time since then, use UPDATE STATISTICS for that particular object. In my experience, even with Automatic statistics enabled we're still forced to update stats from time to time, because of insert operations that didn't trigger automatic update.

To add my personal code (used in a weekly job that builds dynamic statements for stats update):

select distinct
        'update statistics [' + stats.SchemaName + '].[' + stats.TableName + ']'
            + case when stats.RowCnt > 50000 then ' with sample 30 percent;'
            else
                ';' end
        as UpdateStatement
    from (
        select
            ss.name SchemaName,
            so.name TableName,
            so.id ObjectId,
            st.name AS StatsName,
            STATS_DATE(st.object_id, st.stats_id) AS LastStatisticsUpdateDate
            , si.RowModCtr
            , (select case si2.RowCnt when 0 then 1 else si2.RowCnt end from sysindexes si2 where si2.id = si.id and si2.indid in (0,1)) RowCnt
        from sys.stats st
            join sysindexes si on st.object_id = si.id and st.stats_id = si.indid
            join sysobjects so on so.id = si.id and so.xtype = 'U' --user table
            join sys.schemas ss on ss.schema_id = so.uid
    ) stats
    where cast(stats.RowModCtr as float)/cast(stats.RowCnt as FLOAT)*100 >= 10 --more than 10% of the rows have changed
    or ( --update statistics that were not updated for more than 3 months (and rows no > 0)
        datediff(month, stats.LastStatisticsUpdateDate, getdate()) >= 3
        and stats.RowCnt > 0
    )

Here I get all objects where didn't have stats updated for more then 3 months or since the last stats update it had more than 10% of the rows changed.


Why are numbers tables "invaluable"? Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:

USE Model
GO

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM
    (
        SELECT number
        FROM master..spt_values
        WHERE
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

Per the blog post, the rationale given is

  Numbers tables are truly invaluable. I use them all of the time for string manipulation, simulating window functions, populating test tables with lots of data, eliminating cursor logic, and many other tasks that would be incredibly difficult without them.

But I don't understand what those uses are, exactly -- can you provide some compelling, specific examples of where a "numbers table" saves you a ton of work in SQL Server -- and why we should have them?

I've seen many uses when you need to project 'missing data'. Eg. you have a time series (an access log for instance) and you want to show the number of hits per day for past 30 days (think analytics dashboard). If you do a select count(...) from ... group by day you will get the count for every day, but the result will only have a row for each day you actually had at least one access. On the other hand if you first project a table of days from your numbers table (select dateadd(day, -number, today) as day from numbers) and then you left join with the counts (or outer apply, whatever you fancy) then you will get a result that has 0 for count for the days you had no access. This is just one example. Of course, one may argue that the presentation layer of your dashboard could handle the missing days and just show a 0 instead, but some tools (eg. SSRS) will simply not be able to handle this.
Other examples I've seen used similar time series tricks (date/time +/- number) to do all sort of window calculations. In general, whenever in an imperative language you would use a for loop with a well known number of iterations, the declarative and set nature of SQL can use a trick based on a numbers table.
BTW, I feel the need to call out the fact that even though using a numbers table it feels like imperative procedural execution, don't fall into the fallacy of assuming it is imperative. Let me give an example:
int x;
for (int i=0;i<1000000;++i)
  x = i;
printf("%d",x);

This program will output 999999, that is pretty much guaranteed.
Lets try the same in SQL Server, using a number table. First create a table of 1,000,000 numbers:
create table numbers (number int not null primary key);
go

declare @i int = 0
    , @j int = 0;

set nocount on;
begin transaction
while @i < 1000
begin
    set @j = 0;
    while @j < 1000
    begin
        insert into numbers (number)
            values (@j*1000+@i);
        set @j += 1;
    end
    commit;
    raiserror (N'Inserted %d*1000', 0, 0, @i)
    begin transaction;
    set @i += 1;
end
commit
go

Now lets do the 'for loop':
declare @x int;
select @x = number
from numbers with(nolock);
select @x as [@x];

The result is:
@x
-----------
88698

If you're now having a WTF moment (after all number is the clustered primary key!), the trick is called allocation order scan and I did not insert @j*1000+@i by accident... You could had also venture a guess and say the result is because parallelism and that sometimes may be the correct answer.
There are many trolls under this bridge and I mentioned some in On SQL Server boolean operator short-circuit and T-SQL functions do no imply a certain order of execution


Code review database design for storing college assessments & enrolments I'm desigining a database for a university testing tool – running tests in the cloud, very similar to a CI system.

It needs to store users, courses, enrolments, assessments, submissions & results.

It's not a system powering the entire institution, but it is important for a few different courses. I'd like the database to be well-structured, extensible, and "intuitive" for future maintainers.

Requirements:

Two distinct courses may have the same name (but be run in different years/periods)
A single course can only be run once per period (of which there are multiple per year)
Assessment names must be unique within a course, but not across other courses.
A user may be enrolled in a course as an instructor, tutor, or student, but not multiple at once. A student may become a tutor for the same course in the future.

I've included my schema below, with relevant comments where I feel they're needed.

CREATE TYPE PERIOD          AS ENUM ('summer', 'first', 'autumn', 'winter', 'second', 'spring');
CREATE TYPE ASSESSMENT_TYPE AS ENUM ('assignment', 'lab');
CREATE TYPE TEST_RESULT     AS ENUM ('warnings', 'errors', 'perfect');
CREATE TYPE ROLE            AS ENUM ('student', 'tutor', 'convenor', 'admin');

CREATE TABLE users (
    uid      VARCHAR(20)  PRIMARY KEY,
    email    VARCHAR(255) UNIQUE NOT NULL,
    password BYTEA               NOT NULL
);

CREATE TABLE courses (
    -- id is used instead of (code, period, year) to
    -- uniquely identify a course, since its easier.
    id     SERIAL       PRIMARY KEY,
    -- code is used within the university to identify a course,
    -- but not when it was run (a single course can be run
    -- multiple times, with different students/assessments/etc)
    code   VARCHAR(20)  NOT NULL,
    name   VARCHAR(255) NOT NULL,
    period PERIOD       NOT NULL,
    year   INTEGER      NOT NULL,
    -- ensure that a single course can only be run once per period.
    CONSTRAINT once_per_period UNIQUE (code, period, year)
);

CREATE TABLE assessment (
    id           SERIAL          PRIMARY KEY,
    name         VARCHAR(255)    NOT NULL,
    type         ASSESSMENT_TYPE NOT NULL,
    comments     TEXT,
    course_id    INTEGER         REFERENCES courses(id),
    -- assessment names must be unique within a course,
    -- but not across courses.
    CONSTRAINT unique_name_per_course UNIQUE (name, course_id)
);

CREATE TABLE test_results (
    id            SERIAL      PRIMARY KEY,
    result        TEST_RESULT NOT NULL,
    warnings      TEXT        NOT NULL,
    errors        TEXT        NOT NULL
);

CREATE TABLE submissions (
    id            SERIAL      PRIMARY KEY,
    title         TEXT        NOT NULL,
    description   TEXT        NOT NULL,
    feedback      TEXT        NOT NULL,
    uid           VARCHAR(20) REFERENCES users(uid),
    assessment_id INTEGER     REFERENCES assessment(id),
    result_id     INTEGER     REFERENCES test_results(id)
);

CREATE TABLE enrol (
    user_uid  VARCHAR(20) REFERENCES users(uid),
    course_id INTEGER     REFERENCES courses(id),
    role      ROLE        NOT NULL,
    -- ensure a user cannot have multiple roles for a single course
    CONSTRAINT one_role_per_course_run UNIQUE (user_uid, course_id)
);

I'd like some feedback on my design – what can I do better? Is there anything I missed? Any common/idiomatic design choices?

I was going to post in the code review stackexchange, but I saw a previous answer that mentioned this as a better location.

Cheers

Just a few thoughts.

I would not use ENUMs. I hate to say "always" (open to correction on this) but I would "always" use a FOREIGN KEY. Consider gender - 25 years ago, you could have had an ENUM ('M', 'F') - now you can have:

Unknown
Intersex
Non-binary
Decline-to-answer
Don't know
Don't care :-)

&c., &c...

With a FOREIGN KEY things are far easier to change into the future. Furthermore, you make the point that you want your database to be well-structured, extensible, and "intuitive" for future maintainers(*) - FOREIGN KEYs will do this by making your system portable to another RDBMS! After an FK, I'd go for a CHECK CONSTRAINT!

(*) upvoting for this alone! If only all system designers were so thoughtful - some of my  sleepless nights as a programmer might have been avoided! :-)

Check out this thread on SO (both question and accepted answer from the same poster). It tries to go through all options (but misses one - see below!) and the "consensus" (if such a thing can be said to exist on these sites) is, FKs, followed by CCs. Check out this - there's a reason that poster has a rep > 300,000 (not to mention a shedload here too)!

Gotchas. MySQL (amazingly!) doesn't support CKs! Plus, another factor mitigating against CCs is that, frequently, DBAs and/or sysadmins are the only operators allowed to change system tables (which is what modifying CCs involves) whereas ordinary programmers/users can change ordinary tables.

A corollary to this is that if you want tight control over disk activity, you might want the smallest possible amount of it on your system tables - every operation involving a CC involves querying them, so the overhead of querying the FK tables is not such a big deal after all.

A factor in favour of CCs over ENUMs is that you can use the LIKE and other operators on them - you can't with the latter. Finally, take a look at the horrible syntax here to compare values in ENUMs to other values (remember they're different types!). And if you're doing lookups of values for, say, dropdowns, you'd have to use something like this:

select n.nspname as enum_schema,
       t.typname as enum_name,
       e.enumlabel as enum_value
from pg_type t
   join pg_enum e on t.oid = e.enumtypid
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace

as opposed to:

select my_field from my_table;

All in all, I would strongly urge you to use FKs or CCs to enforce Declarative Referential Integrity (DRI) on your tables.

There are (mentioned only for completeness) ways of restricting values in tables - you could use DOMAINs or TRIGGERs. The former seem superior to ENUMs, whereas TRIGGERs (IMHO) should only be used for complex business logic!

Finally, to answer the question (phew...).

Your schema seems fine - it's pretty difficult to judge these things by eye. What I would do if I were you, is to start using/developing against it and see if you come up against problems. Get back to us here with specific issues if you do - your question is a bit broad! p.s. welcome to the forum!


SQL Server Replication We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table.  Replication converted that to 7.5 million individual update statements.  When my alerting notified me that our publication was behind our threshold, I started to examine the issue.

I discovered that the updated was executed and it would have taken a few days to chew through those update statements.  So I decided to see if we could skip over those records that it was trying to process.  Using the system tables and stored procedure I was able to determine the period of time that the updated was executed.  Once I found the last xact_seqno, I stopped the distribution agent, and did the update manually to the subscriber database.  I then executed sp_setsubscriptionxactseqno, to skip past all of those 7.5 million transactions.  When I started up the Distribution Agent, it seemed to have worked and was able to process the remaining transactions.

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it?  How are you always assured to get the next Xact_seqno?  Do you order the transactions by Xact_Seqno or entry_time?

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To do this you need to make sure the publication properties are set to FALSE for allow_anonymous and immediate_sync - if either of these are true then the a database snapshot will be taken when you remove and re add table rather than just the table.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication_name
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication name
@property = 'immediate_sync' ,
@value = 'false'
GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.


MongoDB Server Log to file and rsyslog I have to forward my logs to rsyslog via UDP, but due to the volatile nature of UDP and rsyslog I would like to also log to a file. However, AFAICT MongoDB only allows one target for its logs:

systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log
  #destination: syslog

I'm using the latest mongodb 4.

Unless I'm mistaken it doesn't even support remote syslog: https://docs.mongodb.com/manual/reference/configuration-options/#systemLog.destination

How can I log to something file-alike and a remote syslog using mongodb?

I found a solution where i configure rsyslog to pick up that file and forward it to my target server.

# RSyslog Configuration for MongoDB /etc/rsyslog.d/mongod.conf
$ModLoad imfile
$InputFileName /var/log/mongodb/mongod.log
$InputFileTag mongodb
$InputFileStateFile mongodb-state
$InputFileFacility local3
$InputRunFileMonitor
local3.* action(type="omfwd" target="target" port="514" protocol="udp" action.resumeRetryCount="100" queue.type="linkedList" queue.size="10000")

Based on:
https://www.rsyslog.com/sending-messages-to-a-remote-syslog-server/
and
https://serverfault.com/questions/396136/how-to-forward-specific-log-file-outside-of-var-log-with-rsyslog-to-remote-serv


What are the names of these Postgres concepts? What are the names for the concepts highlighted in the following SQL table?  If Postgres has particular names for them then I'd prefer those, otherwise the general SQL names would be helpful.

I'm especially interested in the name for 5 because I don't really have a clue.

My guesses/intuition are

Table specification
Row
Column name
Column
Not sure.  "Field"?

The background to this question is that I maintain a PostgreSQL API for Haskell and I would like to give these concepts their correct names in my API.

If you want relational theory names, then:

Heading (but nobody calls it that, usually "field names" or "attribute names" or "column names" is more typical)
Tuple
Attribute name
Attribute (but that'll confuse people)
Attribute value

... and a data type is a domain. The whole table toether is a relation.

Nobody really uses that language though. More typical would be:

Table definition (if it includes data types, constraints, etc), or column names if it doesn't.
Tuple or row
Field name or column name
Column (if it includes name) or column values (if it doesn't); but the latter is uncommon in relational DBs since you rarely refer to a column except by selecting fields out of tuples.
Field or attribute value

The whole thing together is usually called a table, but relation is common enough too.

Whatever names you choose to use, please do not assume that tuples have any natural ordering in a relation. They don't.

And don't bake column ordering into your API. Column ordering is stable within a query result, of course, but apps should be discouraged from hard coding column ordinal positions. This is OK (pseudocode):

 zip(['col1', 'col2', 'col3'], do_query('SELECT col1, col2, col3 FROM my_table WHERE ...'))

but this is a bad, bad idea:

 zip(['col1', 'col2', 'col3'], do_query('SELECT * FROM my_table WHERE ...)]

as any addition of a column will break the code. So will less obvious changes like altering the data type of a column.

Apps should be encouraged to use query interfaces that produce a field-name to value map as a result, where field names are discovered from the result set metadata.

For efficiency you still want to offer ordinal access. But field name access is usually preferable.

Python for example does this with a result object that can behave as both an array (ordinal access) and dictionary (key access).

You've omitted data typing though. Relational DBs are strongly typed.

Here's a more typical table definition:

demo-> \d address
                                Table "public.address"
   Column    |  Type   |                          Modifiers
-------------+---------+--------------------------------------------------------------
 address_id  | integer | not null default nextval('address_address_id_seq'::regclass)
 customer_id | integer | not null
 address     | text    |
Indexes:
    "address_pkey" PRIMARY KEY, btree (address_id)
Foreign-key constraints:
    "address_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)

Critically, note that every column has a name, a type, and zero or more constraints like not null or primary key. There are also various other details like foreign key constraints.


How do you read a one-to-many relationship in both directions? I'm having issues with reading this relationship.

Let's take the Professor and Class entities in this example. If I read this from Professor to Class, I would say, "A Professor teaches one to many Classes". If I go the opposite direction, from Class to Professor, I would initially read this as "A Class can be taught by one and only one Professor".

Is this the correct way to describe those relationships in both directions? Or, rather, is the direction from Class to Professor a "many to one" relationship?

I think I may be mixing up two different things (cardinality and relationships), but I'm not entirely sure.

Source

This screenshot was taken from California State University, Bakersfield's ERD PowerPoint

There are a lot of different graphical conventions for relationships in a data model.  See some examples in Wikipedia: Cardinalities.

The vastly most common way to represent cardinality in a diagram is to have the symbol beside an entity type represent the cardinality in the direction of that entity type.  In other words, the symbol meaning many is at the many end of the relationship.

Specifically in your example: Each PROFESSOR teaches one or more CLASSES and each CLASS is taught by exactly one PROFESSOR.

Very rarely I've seen people do this backwards (e.g. (min, max)-notation), but if you do that you're going to confuse most people.