Get stopwords of a InnoDB fulltext index

The easiest way is to get it from the MySQL Docs.
When you go to https://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html you will see the 543 words that are the default stopwords for MyISAM fulltext indexes. There is 'Copy to Clipboard' icon in the upper right corner of that list. Click on that.
You could then construct an SQL file to build that list.
EXAMPLE
I copied that to local text file in Ubuntu
The file looks like this:
root@LAPTOP-U6EJ8KII:~/DBA# cat stuff.txt
a's           able          about         above         according
accordingly   across        actually      after         afterwards
again         against       ain't         all           allow
allows        almost        alone         along         already
also          although      always        am            among
amongst       an            and           another       any
anybody       anyhow        anyone        anything      anyway
anyways       anywhere      apart         appear        appreciate
appropriate   are           aren't        around        as
aside         ask           asking        associated    at
available     away          awfully       be            became
because       become        becomes       becoming      been
before        beforehand    behind        being         believe
below         beside        besides       best          better
between       beyond        both          brief         but
by            c'mon         c's           came          can
can't         cannot        cant          cause         causes
certain       certainly     changes       clearly       co
com           come          comes         concerning    consequently
consider      considering   contain       containing    contains
corresponding could         couldn't      course        currently
definitely    described     despite       did           didn't
different     do            does          doesn't       doing
don't         done          down          downwards     during
each          edu           eg            eight         either
else          elsewhere     enough        entirely      especially
et            etc           even          ever          every
everybody     everyone      everything    everywhere    ex
exactly       example       except        far           few
fifth         first         five          followed      following
follows       for           former        formerly      forth
four          from          further       furthermore   get
gets          getting       given         gives         go
goes          going         gone          got           gotten
greetings     had           hadn't        happens       hardly
has           hasn't        have          haven't       having
he            he's          hello         help          hence
her           here          here's        hereafter     hereby
herein        hereupon      hers          herself       hi
him           himself       his           hither        hopefully
how           howbeit       however       i'd           i'll
i'm           i've          ie            if            ignored
immediate     in            inasmuch      inc           indeed
indicate      indicated     indicates     inner         insofar
instead       into          inward        is            isn't
it            it'd          it'll         it's          its
itself        just          keep          keeps         kept
know          known         knows         last          lately
later         latter        latterly      least         less
lest          let           let's         like          liked
likely        little        look          looking       looks
ltd           mainly        many          may           maybe
me            mean          meanwhile     merely        might
more          moreover      most          mostly        much
must          my            myself        name          namely
nd            near          nearly        necessary     need
needs         neither       never         nevertheless  new
next          nine          no            nobody        non
none          noone         nor           normally      not
nothing       novel         now           nowhere       obviously
of            off           often         oh            ok
okay          old           on            once          one
ones          only          onto          or            other
others        otherwise     ought         our           ours
ourselves     out           outside       over          overall
own           particular    particularly  per           perhaps
placed        please        plus          possible      presumably
probably      provides      que           quite         qv
rather        rd            re            really        reasonably
regarding     regardless    regards       relatively    respectively
right         said          same          saw           say
saying        says          second        secondly      see
seeing        seem          seemed        seeming       seems
seen          self          selves        sensible      sent
serious       seriously     seven         several       shall
she           should        shouldn't     since         six
so            some          somebody      somehow       someone
something     sometime      sometimes     somewhat      somewhere
soon          sorry         specified     specify       specifying
still         sub           such          sup           sure
t's           take          taken         tell          tends
th            than          thank         thanks        thanx
that          that's        thats         the           their
theirs        them          themselves    then          thence
there         there's       thereafter    thereby       therefore
therein       theres        thereupon     these         they
they'd        they'll       they're       they've       think
third         this          thorough      thoroughly    those
though        three         through       throughout    thru
thus          to            together      too           took
toward        towards       tried         tries         truly
try           trying        twice         two           un
under         unfortunately unless        unlikely      until
unto          up            upon          us            use
used          useful        uses          using         usually
value         various       very          via           viz
vs            want          wants         was           wasn't
way           we            we'd          we'll         we're
we've         welcome       well          went          were
weren't       what          what's        whatever      when
whence        whenever      where         where's       whereafter
whereas       whereby       wherein       whereupon     wherever
whether       which         while         whither       who
who's         whoever       whole         whom          whose
why           will          willing       wish          with
within        without       won't         wonder        would
wouldn't      yes           yet           you           you'd
you'll        you're        you've        your          yours
yourself      yourselves    zero
root@LAPTOP-U6EJ8KII:~/DBA#

I ran a loop across the file to tokenize it into another file.
root@LAPTOP-U6EJ8KII:~/DBA# for TAG in `cat stuff.txt` ; do echo ${TAG}; done > stopwords_table.sql

Then, I jumped into vi and added SQL around the words and created this:
root@LAPTOP-U6EJ8KII:~/DBA# cat stopwords_table.sql
DROP DATABASE IF EXISTS cust_stop;
CREATE DATABASE cust_stop;
USE cust_stop
CREATE TABLE stopwords (VALUE VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO stopwords (VALUE) VALUES
('a''s'),
('able'),
('about'),
('above'),
('according'),
('accordingly'),
('across'),
('actually'),
('after'),
('afterwards'),
('again'),
('against'),
('ain''t'),
('all'),
('allow'),
('allows'),
('almost'),
('alone'),
('along'),
('already'),
('also'),
('although'),
('always'),
('am'),
('among'),
('amongst'),
('an'),
('and'),
('another'),
('any'),
('anybody'),
('anyhow'),
('anyone'),
('anything'),
('anyway'),
('anyways'),
('anywhere'),
('apart'),
('appear'),
('appreciate'),
('appropriate'),
('are'),
('aren''t'),
('around'),
('as'),
('aside'),
('ask'),
('asking'),
('associated'),
('at'),
('available'),
('away'),
('awfully'),
('be'),
('became'),
('because'),
('become'),
('becomes'),
('becoming'),
('been'),
('before'),
('beforehand'),
('behind'),
('being'),
('believe'),
('below'),
('beside'),
('besides'),
('best'),
('better'),
('between'),
('beyond'),
('both'),
('brief'),
('but'),
('by'),
('c''mon'),
('c''s'),
('came'),
('can'),
('can''t'),
('cannot'),
('cant'),
('cause'),
('causes'),
('certain'),
('certainly'),
('changes'),
('clearly'),
('co'),
('com'),
('come'),
('comes'),
('concerning'),
('consequently'),
('consider'),
('considering'),
('contain'),
('containing'),
('contains'),
('corresponding'),
('could'),
('couldn''t'),
('course'),
('currently'),
('definitely'),
('described'),
('despite'),
('did'),
('didn''t'),
('different'),
('do'),
('does'),
('doesn''t'),
('doing'),
('don''t'),
('done'),
('down'),
('downwards'),
('during'),
('each'),
('edu'),
('eg'),
('eight'),
('either'),
('else'),
('elsewhere'),
('enough'),
('entirely'),
('especially'),
('et'),
('etc'),
('even'),
('ever'),
('every'),
('everybody'),
('everyone'),
('everything'),
('everywhere'),
('ex'),
('exactly'),
('example'),
('except'),
('far'),
('few'),
('fifth'),
('first'),
('five'),
('followed'),
('following'),
('follows'),
('for'),
('former'),
('formerly'),
('forth'),
('four'),
('from'),
('further'),
('furthermore'),
('get'),
('gets'),
('getting'),
('given'),
('gives'),
('go'),
('goes'),
('going'),
('gone'),
('got'),
('gotten'),
('greetings'),
('had'),
('hadn''t'),
('happens'),
('hardly'),
('has'),
('hasn''t'),
('have'),
('haven''t'),
('having'),
('he'),
('he''s'),
('hello'),
('help'),
('hence'),
('her'),
('here'),
('here''s'),
('hereafter'),
('hereby'),
('herein'),
('hereupon'),
('hers'),
('herself'),
('hi'),
('him'),
('himself'),
('his'),
('hither'),
('hopefully'),
('how'),
('howbeit'),
('however'),
('i''d'),
('i''ll'),
('i''m'),
('i''ve'),
('ie'),
('if'),
('ignored'),
('immediate'),
('in'),
('inasmuch'),
('inc'),
('indeed'),
('indicate'),
('indicated'),
('indicates'),
('inner'),
('insofar'),
('instead'),
('into'),
('inward'),
('is'),
('isn''t'),
('it'),
('it''d'),
('it''ll'),
('it''s'),
('its'),
('itself'),
('just'),
('keep'),
('keeps'),
('kept'),
('know'),
('known'),
('knows'),
('last'),
('lately'),
('later'),
('latter'),
('latterly'),
('least'),
('less'),
('lest'),
('let'),
('let''s'),
('like'),
('liked'),
('likely'),
('little'),
('look'),
('looking'),
('looks'),
('ltd'),
('mainly'),
('many'),
('may'),
('maybe'),
('me'),
('mean'),
('meanwhile'),
('merely'),
('might'),
('more'),
('moreover'),
('most'),
('mostly'),
('much'),
('must'),
('my'),
('myself'),
('name'),
('namely'),
('nd'),
('near'),
('nearly'),
('necessary'),
('need'),
('needs'),
('neither'),
('never'),
('nevertheless'),
('new'),
('next'),
('nine'),
('no'),
('nobody'),
('non'),
('none'),
('noone'),
('nor'),
('normally'),
('not'),
('nothing'),
('novel'),
('now'),
('nowhere'),
('obviously'),
('of'),
('off'),
('often'),
('oh'),
('ok'),
('okay'),
('old'),
('on'),
('once'),
('one'),
('ones'),
('only'),
('onto'),
('or'),
('other'),
('others'),
('otherwise'),
('ought'),
('our'),
('ours'),
('ourselves'),
('out'),
('outside'),
('over'),
('overall'),
('own'),
('particular'),
('particularly'),
('per'),
('perhaps'),
('placed'),
('please'),
('plus'),
('possible'),
('presumably'),
('probably'),
('provides'),
('que'),
('quite'),
('qv'),
('rather'),
('rd'),
('re'),
('really'),
('reasonably'),
('regarding'),
('regardless'),
('regards'),
('relatively'),
('respectively'),
('right'),
('said'),
('same'),
('saw'),
('say'),
('saying'),
('says'),
('second'),
('secondly'),
('see'),
('seeing'),
('seem'),
('seemed'),
('seeming'),
('seems'),
('seen'),
('self'),
('selves'),
('sensible'),
('sent'),
('serious'),
('seriously'),
('seven'),
('several'),
('shall'),
('she'),
('should'),
('shouldn''t'),
('since'),
('six'),
('so'),
('some'),
('somebody'),
('somehow'),
('someone'),
('something'),
('sometime'),
('sometimes'),
('somewhat'),
('somewhere'),
('soon'),
('sorry'),
('specified'),
('specify'),
('specifying'),
('still'),
('sub'),
('such'),
('sup'),
('sure'),
('t''s'),
('take'),
('taken'),
('tell'),
('tends'),
('th'),
('than'),
('thank'),
('thanks'),
('thanx'),
('that'),
('that''s'),
('thats'),
('the'),
('their'),
('theirs'),
('them'),
('themselves'),
('then'),
('thence'),
('there'),
('there''s'),
('thereafter'),
('thereby'),
('therefore'),
('therein'),
('theres'),
('thereupon'),
('these'),
('they'),
('they''d'),
('they''ll'),
('they''re'),
('they''ve'),
('think'),
('third'),
('this'),
('thorough'),
('thoroughly'),
('those'),
('though'),
('three'),
('through'),
('throughout'),
('thru'),
('thus'),
('to'),
('together'),
('too'),
('took'),
('toward'),
('towards'),
('tried'),
('tries'),
('truly'),
('try'),
('trying'),
('twice'),
('two'),
('un'),
('under'),
('unfortunately'),
('unless'),
('unlikely'),
('until'),
('unto'),
('up'),
('upon'),
('us'),
('use'),
('used'),
('useful'),
('uses'),
('using'),
('usually'),
('value'),
('various'),
('very'),
('via'),
('viz'),
('vs'),
('want'),
('wants'),
('was'),
('wasn''t'),
('way'),
('we'),
('we''d'),
('we''ll'),
('we''re'),
('we''ve'),
('welcome'),
('well'),
('went'),
('were'),
('weren''t'),
('what'),
('what''s'),
('whatever'),
('when'),
('whence'),
('whenever'),
('where'),
('where''s'),
('whereafter'),
('whereas'),
('whereby'),
('wherein'),
('whereupon'),
('wherever'),
('whether'),
('which'),
('while'),
('whither'),
('who'),
('who''s'),
('whoever'),
('whole'),
('whom'),
('whose'),
('why'),
('will'),
('willing'),
('wish'),
('with'),
('within'),
('without'),
('won''t'),
('wonder'),
('would'),
('wouldn''t'),
('yes'),
('yet'),
('you'),
('you''d'),
('you''ll'),
('you''re'),
('you''ve'),
('your'),
('yours'),
('yourself'),
('yourselves'),
('zero');

root@LAPTOP-U6EJ8KII:~/DBA#

NOTE: There are 47 words with an apostrophe in it (such as ain't and can't). So, I had to change them to double single quotes in the SQL file above.
All you need to do is execute the aforementioned SQL. This would create the database cust_stop. with the table stopwords. The table has a single column called VALUE (as specified in the MariaDB documentation).
After you created the stopword table, now configure MariaDB.conf with innodb_ft_server_stopword_table:
innodb_ft_server_stopword_table=cust_stop/stopwords

and restart MariaDB.
GIVE IT A TRY !!!
Note: If you are doing this in Galera, MariaDB will have a little fit because every table must have a primary key. You could either configure Galera not to be strict (be permissive) or just use the stopword file option instead.
Please see my old posts on making a text file stopword list.
UPDATE 2019-08-06 10:08 EDT
The link you gave to the MariaDB Documentation gives you the answer.
Just run
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

Unfortunately, MyISAM stopwords are not exposed to the information_schema like InnoDB stopwords are exposed. My answer simply provides a way to populate the INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD table if you wanted these stopwords exposed to InnoDB.
Please keep in mind that for InnoDB, 36 words is the "normal" table and is exposed. For MyISAM, the 543 word "normal" table is not exposed.
UPDATE 2019-08-06 12:43 EDT
If you are interested in knowing what stopwords are used in a MyISAM table with a fulltext index, then you will have to use myisam_ftdump. I have only referred to it once in this forum (mysql MATCH relavancy score).
I have it installed on my laptop for MySQL 5.5
C:\MySQL55\bin>myisam_ftdump
Use: myisam_ftdump <table_name> <index_num>
  -h, --help          Display help and exit.
  -?, --help          Synonym for -h.
  -c, --count         Calculate per-word stats (counts and global weights).
  -d, --dump          Dump index (incl. data offsets and word weights).
  -l, --length        Report length distribution.
  -s, --stats         Report global stats.
  -v, --verbose       Be verbose.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE

C:\MySQL55\bin>

When you run myisam_ftdump -c against a MyISAM table, you will get each word and its count. You will have to parse the words out of that output and compare each word to a text file that contains the 543 MyISAM stopwords. Any word in the Fulltext Dump that are in that list is using the stopword. I will leave that as an exercise for you.
Please keep in mind that myisam_ftdump is a utility program, not a client program. What's the difference ??? A client program (such as mysql, mysqldump, mysqladmin) requires you to supply a username and password to access the table in mysqld. A utility program (such as mysaimpack, myisamchk) can be used against a table but no process should be accessing the MyISAM table (no open file handles, no running SQL). This means that you will have to copy the MyISAM table to another folder outside of datadir to use the myisam_ftdump against that MyISAM in the outside folder or you risk corrupting the table.
For the difference between client and utility programs, click the following:

Utility Programs
Client Programs

UPDATE 2019-08-07 10:24 EDT
Here is a desperate query
SELECT DISTINCT A.value FROM
INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD A,mytable B
WHERE
    B.mycolumn LIKE CONCAT(A.value,' %') OR
    B.mycolumn LIKE CONCAT('% ',A.value) OR
    B.mycolumn LIKE CONCAT('% ',A.value, '%')
;

This is an ugly Cartesian Join, the worst of its kind. It should locate a stopword at the beginning, end or middle of a text field.


shared_buffers are statically taken at startup and are never resized.
effective_cache_size is just a hint to the optimizer. it is never allocated. it merely gives a hint of what is going on. so shared_buffers is what you see as taken.


How to log value that cause unique key violation in SQL Server

is there a reason this logic won't work for you?

--Find all duplicates and save to temp table
select * into #Duplicates from
(
    SELECT DISTINCT PropertyID, PERSONID, FULLNAME FROM #SalesContacts
    INTERSECT
    SELECT  PropertyID, PERSONID, FULLNAME FROM TBL_PERSON
) a

--Log the duplicates
insert into ErrorLoggingTable(PropertyID, PERSONID, FULLNAME, ErrorMessage)
select PropertyID, PERSONID, FULLNAME, 'Duplicate Found' from #Duplicates

--Remove the duplicates from the #SalesContacts table
delete a from #SalesContacts a join
#Duplicates b on b.PropertyID and b.PersonID and b.FullName

--Insert the rest
insert into TBL_PERSON (PropertyID, PERSONID, FULLNAME)
    select PropertyID, PERSONID, FULLNAME


Putting aside the syntax was not correct (before the edit).

NO it does not get put in memory.

A proper example is a join. It will get call multiple times in a loop join.  On an expensive CTE called multiple times then materialize to #temp is the way to go.


How can I enforce ensure uniqueness of an association with a time dimension?

I was thinking because this was a calculation it'd have to be a trigger but in doing some googling there are better options.

Overlapping intervals look like they can be blocked by a particular pattern of constraints (http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx)

This slideshare walks through 'Exclusion' constraints which are part of PostgreSQL 9.0 and above and also touches on the pitfalls of approaches like triggers (http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints)

This post titled 'Non-Overlapping Dates Constraint' covers several approaches for Oracle (http://jeffkemponoracle.com/2012/08/30/non-overlapping-dates-constraint/)

My Google search query was 'unique time interval constraint database'


Is tsvector the right choice for searching exact match of two words with variations like plural and singular?

Short answer; yes, a tsvector column used as an index will allow you to return matching documents with stemming and pluralization handled by the default text search configuration.

The documentation is very complete and easy to read and will give you a solid grounding in how full-text search works and a bit in managing updates to the corpus and likely issue.

There are some subtleties you need to look at, but the defaults are pretty good for most languages. Things that might give issues are mostly to do with your documents structure and quirks that might need to be addressed with tweaks to the dictionaries or stop words list ( stop words are ignored for search purposes ).


DB2 error - SQL Statement too long or complex

This should probably be a comment rather than an answer since I'm not sure if my reasoning is correct, but then I wouldn't be able to have any formatting.

Could you create a temporary table and JOIN instead?

...<sql statements and joins>
JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.b

Edit: if the values you're checking against don't change much, it might even make sense to persist them in a table to increase performance and facilitate querying.


How to pass an array to a plpgsql function with VARIADIC parameter

To pass an actual array for a VARIADIC parameter, you must use the keyword VARIADIC in the call:

SELECT mix_table_fields('art'::VARCHAR
                      , 'out'::VARCHAR
                      , VARIADIC array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]);

Even works without explicit type casts in your case. Function type resolution can find the best candidate for untyped string literals without ambiguity - as long as the function is not overloaded:

SELECT mix_table_fields('art', 'out', VARIADIC array['type','colour','size','price']);

Or with an array literal:

SELECT mix_table_fields('art', 'out', VARIADIC '{type,colour,size,price}');

Without the keyword VARIADIC, Postgres expects a list of element values like:

SELECT mix_table_fields('art', 'out', 'type', 'colour', 'size','price');

... which is the whole point of a VARIADIC function parameter: to be able to call it with a variable number of parameters of the element type, so you don't need the array wrapper. (Also the reason why a VARIADIC parameter must come last in the parameter list: else the call could be ambiguous.) If you don't need this feature, use a plain varchar[] parameter without VARIADIC in the function definition to begin with.

Related:

How to use an array as argument to a VARIADIC function in PostgreSQL?
Make query depend on input in PostgreSQL function


mysql update sed: -e expression #1, char 17: unknown option to `s'

Oracle confirmed it as a bug and it will be fixed in the next relase.


The query is pretty simple and ought to run fast enough.

My guess is that you have a long running transaction performing DDL in one of the databases and your query is getting blocked waiting on a lock.

Unfortunately the metadata functions such as OBJECT_ID don't take account of the transaction isolation level of the outer transaction so even setting the isolation level to allow dirty reads wouldn't help.

Can you try this alternative method of doing the same operation and report back? (If still slow try adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to the beginning)

DECLARE @Databases TABLE(name SYSNAME PRIMARY KEY);

DECLARE @dbname    SYSNAME,
        @tablename SYSNAME = '_LOOK_UP_TABLE'

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT name
    FROM   sys.databases
    WHERE  state_desc = 'ONLINE'
           AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                             'ReportServer', 'ReportServerTempDB', '1303VISBOC' )

OPEN @C1;

FETCH NEXT FROM @C1 INTO @dbname;

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @nsql NVARCHAR(max) = 'USE ' + QUOTENAME(@dbname) + ';
                                     SELECT @dbname
                                     FROM sys.tables WITH (READPAST)
                                     WHERE schema_id=1
                                           AND Name = @tablename';

      INSERT INTO @Databases
      EXEC sp_executesql
        @nsql,
        N'@dbname SYSNAME, @tablename SYSNAME',
        @dbname,
        @tablename

      FETCH NEXT FROM @C1 INTO @dbname;
  END

SELECT name
FROM @Databases


mysql stored procedure create date plus one day but time set to 00:00:00


mysqld keeps the error log file open. If you delete the file the file handler is still valid and the file is available for the process that opened it (=mysqld).

# lsof  | grep mysqld
...
mysqld    20163 20192           mysql    2w      REG              253,2       4434         15 /var/log/mysql/error.log

# rm /var/log/mysql/error.log
# lsof  | grep mysqld | grep error
...
mysqld    20163 20310           mysql    2w      REG              253,2       4549         15 /var/log/mysql/error.log (deleted)

You can get the content of the error log from the /proc filesystem.

# ls -la /proc/20163/fd/ | grep error
l-wx------ 1 mysql mysql 64 May  2 15:49 1 -> /var/log/mysql/error.log (deleted)
l-wx------ 1 mysql mysql 64 May  2 15:49 2 -> /var/log/mysql/error.log (deleted)

# cat /proc/20163/fd/1
2020-05-02T15:49:46.163721Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 65535)
2020-05-02T15:49:46.355461Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1-log) starting as process 20163 ...
2020-05-02T15:49:46.371978Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-02T15:49:46.372006Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-02T15:49:46.372010Z 0 [Note] InnoDB: Uses event mutexes
2020-05-02T15:49:46.372013Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-02T15:49:46.372016Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-02T15:49:46.372023Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-02T15:49:46.372272Z 0 [Note] InnoDB: Number of pools: 1
2020-05-02T15:49:46.372380Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-02T15:49:46.376119Z 0 [Note] InnoDB: Initializing buffer pool, total size = 384M, instances = 1, chunk size = 128M
2020-05-02T15:49:46.398759Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-02T15:49:46.404255Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-05-02T15:49:46.421829Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-05-02T15:49:48.448175Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-05-02T15:49:48.448255Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-05-02T15:49:48.938748Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-05-02T15:49:48.939384Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-05-02T15:49:48.939393Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-05-02T15:49:48.939823Z 0 [Note] InnoDB: Waiting for purge to start
2020-05-02T15:49:48.990402Z 0 [Note] InnoDB: 5.7.29 started; log sequence number 3292611
2020-05-02T15:49:48.990563Z 0 [Note] InnoDB: Loading buffer pool(s) from /database/mysql/ib_buffer_pool
2020-05-02T15:49:48.990773Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-05-02T15:49:49.476557Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200502 15:49:49
2020-05-02T15:49:49.569129Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-05-02T15:49:49.569147Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-05-02T15:49:49.596450Z 0 [Warning] CA certificate ca.pem is self signed.
2020-05-02T15:49:49.596491Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-05-02T15:49:49.616398Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2020-05-02T15:49:49.616425Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2020-05-02T15:49:49.616456Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2020-05-02T15:49:49.856411Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=produ-data-db-0000-05f01-use2-relay-bin' to avoid this problem.
2020-05-02T15:49:49.946618Z 1 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2020-05-02T15:49:49.966480Z 2 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2020-05-02T15:49:49.966566Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000116' at position 2493, relay log './produ-data-db-0000-05f01-use2-relay-bin.000002' position: 1811
2020-05-02T15:49:49.971887Z 1 [Note] Slave I/O thread for channel '': connected to master 'replication@produ-data-db-0000-0c5f4-use2:3306',replication started in log 'mysql-bin.000116' at position 2493
2020-05-02T15:49:51.066602Z 0 [Note] Event Scheduler: Loaded 0 events
2020-05-02T15:49:51.066831Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.29-0ubuntu0.18.04.1-log'  socket: '/var/lib/mysql-files/mysql.sock'  port: 3306  (Ubuntu)
2020-05-02T15:50:05.681566Z 5 [Warning] IP address '10.0.182.254' could not be resolved: Name or service not known

Same way you can get other files (slow log, general log). However if the files were closed and were deleted - you're pretty much out of luck (not saying it's impossible to recover, but hard).


Using wm_concat to concatenate rows, but in an order determined by another column

So the query (which would work if under 4000 bytes) is

select p_id, listagg(description, '\n') WITHIN GROUP (ORDER BY order_by)
from my_table
where p_id = 12345
group by p_id


mysqlimport locks all tables system wide

If you start mysqlimport with --lock-tables=0 then there will be no locks.

mysqlimport uses LOAD DATA rather than INSERT/UPDATE.


I don't have access to SQL Server so I googled the syntax for generated columns, but there's probably syntax errors in here anyhow. The idea is to use a generated column that has a unique value (BatchNumber) whenever BatchComplete is not null, and a non-unique number (BatchId) otherwise:

CREATE TABLE BatchTable
( BatchNumber INT IDENTITY(1,1) PRIMARY KEY
, BatchID INT NOT NULL
, BatchComplete DATETIME2(2) NULL
, Active AS (CASE WHEN BatchComplete IS NULL
                  THEN BatchID
                  ELSE BatchNumber
             END) PERSISTED
, CONSTRAINT ONLY_ONE_ACTIVE_AT_ATIME UNIQUE ( BatchID, Active )
);

If BatchComplete IS NULL then UNIQUE (BatchId, BatchId) must hold, i.e only one Active batch at a time.

If BatchComplete IS NOT NULL then UNIQUE (BatchId, BatchNumber) must hold, but BatchNumber itself is unique, so this will always be true.

As mentioned I can't test this, but you might be able to use the idea one way or another.


Check out the Docs on Hyperscale:

  Hyperscale only supports non persistent In-Memory objects (table
  types, native SPs and functions). Persistent In-Memory tables and
  other objects must be dropped and recreated as non-In-Memory objects
  before migrating a database to the Hyperscale service tier.

As for rollback, it depends what you mean. Can it rollback an uncommitted transaction? Yes, it is a SQL Server after all. Can it restore databases to a point-in-time? Yes, see here and here. For anything else, you'd have to clarify what you mean by rollback.


Availability Groups and Transactional Replication

Distribution database should not reside on the servers that are part of AlwaysON availability group that the publishing database is (or will become) a member of.

  Replication configuration is coupled to the SQL Server instance where the Distributor is configured; therefore the distribution database cannot be mirrored or replicated.

If you want to provide HA for distribution database, then you have to go for SQL Server Failover cluster. Thats the only option.

Your scenario is as below :

So if you loose server C, the only option to get distribution running on server D is to do a RESTORE.. with KEEP_REPLICATION from a good backup. You can use this script to restore your distribution database (with some changes as per your environment). I would go for a clean install of replication !

Make sure you script out your replication topology whenever your do any changes. You should have handy scripts of both drop and create, so in a disaster situation, you have scripts that will help easily create replication.

Also, since you are using always-ON with T-Rep, I would suggest you to enable TF 1448.

  Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled,, the log reader always waits for the synchronous secondary replicas. The log reader will not go beyond the min ack of the synchronous secondary replicas. This trace flag applies to the instance of SQL Server, not just to an availability group, an availability database, or a log reader instance. This trace flag takes effect immediately without a restart. It can be activated ahead of time or when an asynchronous secondary replica fails.

Reference : Configure Replication for AlwaysOn Availability Groups


How open oracle DB Linux (After installation)

First edit your /etc/oratab changing N to Y like that:

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

Must be change to Y:

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

Then to start you must use the dbstart script like that:

export ORACLE_HOME=youroraclehome
su oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"

Where "oracle" in the "su" command is the name of the user you used to install oracle.

This will up all oracle essential services including the https port 1158 (and you can put that in an auto-start script)

And to stop it:

export ORACLE_HOME=youroraclehome
su oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"

And to backup it (dump) ;-)

export ORACLE_HOME=youroraclehome
$ORACLE_HOME/bin/exp file=mybackup.dmp full=y

Remember to test your backup before thinking its working.


getting error installing oracle 11gR2: cannot access node

It looks like the hardening was a little to hard. Oracle does need some access to the server. You can revert back the chmods for ping, Oracle rdbms does not need it. /tmp/ is not required, a usable location for tmp is, mostly for scripting. The rdbms has it's own tricks and storage for sorts.

Never install Oracle as root; use the root.sh at the end of the installation to do the system tweeks required. On some platforms there is a root pre script that should run before installation. The Oracle installation scripts refuse to run using the root account and can be run using any other account.

The access to some server config files has been made a little too tight for the install user to check the prerequisites. If you know they are ok, it's ok. Proving they are ok is a little easier when support issues arise. Check the group ownership of the critical config files and add the Oracle installation user to that group to give it read access.


IN Clause - performance difference between listing values and query

You are using a linked server to access Table1 and Table2.

The first query is sent as is to the other server and executed there returning only the rows you want.

The second query is doing a join between a local table TB_BRANCH  and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the join operations locally.

The drop in performance is there because it takes time to move the entire contents of Table1 and Table2 from the remote server to the local server.

  How can I avoid performance drop while using a sub-query in IN clause?

Move the content of TB_BRANCH (25 rows) to the remote server to avoid doing a join between tables on different servers.

One way of moving the rows from TB_BRANCH to the remote server is to execute the query on the remote server using sp_executesql with the values as a parameter in a XML structure. Unpack the XML to a table variable and use the table variable in your main query.

declare @X varchar(max);
set @X = (
         select b.BRANCH_ID
         from dbo.TB_BRANCH as b
         where b.START_DT = '99999999'
         for xml path('')
         );

-- Create a SQL Statement
declare @SQL nvarchar(max);
set @SQL = '
declare @B table(BRANCH_ID varchar(10) primary key);

insert into @B(BRANCH_ID)
select B.X.value(''.'', ''varchar(10)'')
from (select cast(@X as xml)) as T(X)
  cross apply T.X.nodes(''/BRANCH_ID/text()'') as B(X);

select *
from Schema.Table1 as t1
  left outer join Schema.Table2 as t2
    on t1.ORDER_ID = T2.ORDER_ID
where t1.BRANCH_ID not in (
                          select B.BRANCH_ID
                          from @B as B
                          );';

exec linkedServer.DB.Schema.sp_executesql @SQL, N'@X varchar(max)', @X

I have guessed the data type of BRANCH_ID to be varchar(10) you need to modify that in two places in the script if it is something else.

The XML parameter has to be a varchar(max) instead of as XML since the XML datatype is not supported with remote queries. Table Valued Parameters is not supported either so that is not an option here.

The query plan of this query is not very interesting. It will just be a Remote Scan. If you want to know what the plan looks like on the remote side you have to capture the plan there.


SQL Managed Instance - Cross Subscription Restores/Migration

copy only backups are currently the only solution. Cross subscription pitr and geo restore don't work because currently we cannot verify that initiator has all required permissions both on source and target subscriptions.


how to sum times in EXPLAIN for Postgres parallel query

Since parallel_leader_participation is at its default value off, the leader participates in the sequential scan. The I/O times of the worker processes are listed individually, but the I/O time of the leader can only be found by subtracting the workers' times from the total time.
The parallel sequential scan took 65026.980 milliseconds, almost all of the time. The fist number (31764.552) is the startup time, that is the time it took until the first result row was returned. If anything surprises me here, it is that high startup time.


Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?

Basically seems to be picking the MAX id from a partition, not max across all partitions

Writing TOP (1) without an ORDER BY clause to define which row is 'top' means the query processor is logically free to return any row from the set. The query plan selected by the optimizer happens to return a particular row (highest id from the first partition) but you cannot rely on this, even if it were a useful result.

Whenever you use TOP you should always specify an ORDER BY at the same scope to produce deterministic behaviour - unless you really do not care which row(s) come back.

  Given the table size SELECT MAX(id) FROM tableA will not perform well enough

The optimizer is lacking some logic to transform a scalar MAX or MIN aggregate over a partitioned index to a global aggregate over per-partition aggregates. Itzik Ben-Gan explains the limitation and provides a general workaround in this article.

If the highest partition number is known and guaranteed not to change, the workaround to specify a literal partition using the $partition function will work, though it may fail in a non-obvious way if the partitioning strategy changes in future.

This 'solution' works by eliminating all but one partition, resulting in a simple seek on one partition of the index.

  Adding an order by id does not improve performance for some reason

The same optimizer limitation broadly applies to TOP (1) ... ORDER BY. The ORDER BY makes the result deterministic, but does not help produce a more efficient plan in this particular case (but see below).

Implied Index Keys

Your index is on id DESC, timeSampled DESC. In SQL Server 2008 and later, partitioning introduces an extra implied leading key on $partition ASC (it is always ascending, it is not configurable) making the full index key $partition ASC, id DESC, timeSampled DESC.

Since id and timeSampled increase together (though there is nothing in the schema to guarantee this) you could rewrite the query as TOP (1) ... ORDER BY $partition DESC, id DESC. Unfortunately, the DESC keys on your index and ASC implied leading key $partition means the index could not be used to scan just one row from the index in order.

If your index keys were instead id ASC, timeSampled ASC the whole index key would be $partition ASC, id ASC, timeSampled ASC. This all-ASC index could be scanned backward, returning just the first row in key order. This row would be guaranteed to have the highest id value in the highest-numbered partition. Given the (unenforced) relationship between id and partition id, this would produce the correct result with an optimal execution plan that reads just a single row.

This 'solution' lacks integrity because the id-timeSampled relationship is not enforced, and you probably do not want to rebuild the nonclustered primary key anyway. Nevertheless, I mention it because it may enhance your understanding of how partitioning interacts with indexes.


,CASE WHEN i.DocValue ='F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
  ELSE 0
END as Value

There are two types of CASE statement, SIMPLE and SEARCHED.

You cannot evaluate multiple expressions in a Simple case expression, which is what you were attempting to do.

-- Simple CASE expression:
CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
-- Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Example of Simple CASE:

CASE x
  WHEN 'a' THEN 'b'
  WHEN 'c' THEN 'd'
  ELSE 'z'
END

Example of a Searched CASE:

CASE
  WHEN x = 1 AND y = 2 THEN 'a'
  WHEN x = 2 AND y = 1 THEN 'b'
  ELSE 'z'
END

Further Reading: http://msdn.microsoft.com/en-us/library/ms181765.aspx


Is there any way to identify which SP return data

This works locally for me to identify result returning procs using sp_describe_first_result_set, which is only available on SQL Server 2012+. It's on the quick/dirty side of things, but hopefully it'll get you started,

Use [Yourmom]

CREATE TABLE #commands
     (
         ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
         CurrentObject sysname,
         Command NVARCHAR(2000)
     );

CREATE TABLE #success
     (
         object_name sysname NULL,
         is_hidden BIT NULL,
         column_ordinal INT NULL,
         name sysname NULL,
         is_nullable BIT NULL,
         system_type_id INT NULL,
         system_type_name sysname NULL,
         max_length SMALLINT NULL,
         precision TINYINT NULL,
         scale TINYINT NULL,
         collation_name sysname NULL,
         user_type_id INT NULL,
         user_type_database sysname NULL,
         user_type_schema sysname NULL,
         user_type_name sysname NULL,
         assembly_qualified_type_name NVARCHAR(4000) NULL,
         xml_collection_id INT NULL,
         xml_collection_database sysname NULL,
         xml_collection_schema sysname NULL,
         xml_collection_name sysname NULL,
         is_xml_document BIT NULL,
         is_case_sensitive BIT NULL,
         is_fixed_length_clr_type BIT NULL,
         source_server sysname NULL,
         source_database sysname NULL,
         source_schema sysname NULL,
         source_table sysname NULL,
         source_column sysname NULL,
         is_identity_column BIT NULL,
         is_part_of_unique_key BIT NULL,
         is_updateable BIT NULL,
         is_computed_column BIT NULL,
         is_sparse_column_set BIT NULL,
         ordinal_in_order_by_list SMALLINT NULL,
         order_by_is_descending SMALLINT NULL,
         order_by_list_length SMALLINT NULL,
         tds_type_id INT NULL,
         tds_length INT NULL,
         tds_collation_id INT NULL,
         tds_collation_sort_id INT NULL
     );

CREATE TABLE #fail (
                    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    CurrentObject sysname,
                    Command NVARCHAR(2000),
                    Error_Line INT,
                    Error_Message NVARCHAR(4000),
                    Error_Number INT,
                    Error_Procedure sysname,
                    Error_Severity INT,
                    Error_State INT
                    );

DECLARE @CurrentObject sysname;
DECLARE @CurrentCommand NVARCHAR(2000);

INSERT INTO #commands ( CurrentObject, Command )
SELECT OBJECT_NAME(sm.object_id),
       N'EXEC sys.sp_describe_first_result_set @tsql = N''EXEC ' + OBJECT_NAME(sm.object_id) + N''';'
FROM   sys.sql_modules AS sm
WHERE  OBJECT_NAME(sm.object_id) IS NOT NULL
AND    sm.definition LIKE '%CREATE%PROC%';

DECLARE result_cursor CURSOR FOR
SELECT CurrentObject, Command FROM #commands

OPEN result_cursor
FETCH NEXT FROM result_cursor into @CurrentObject, @CurrentCommand
WHILE @@FETCH_STATUS = 0
BEGIN

 BEGIN TRY

    PRINT @CurrentCommand;
    INSERT #success (
                             is_hidden,
                             column_ordinal,
                             name,
                             is_nullable,
                             system_type_id,
                             system_type_name,
                             max_length,
                             precision,
                             scale,
                             collation_name,
                             user_type_id,
                             user_type_database,
                             user_type_schema,
                             user_type_name,
                             assembly_qualified_type_name,
                             xml_collection_id,
                             xml_collection_database,
                             xml_collection_schema,
                             xml_collection_name,
                             is_xml_document,
                             is_case_sensitive,
                             is_fixed_length_clr_type,
                             source_server,
                             source_database,
                             source_schema,
                             source_table,
                             source_column,
                             is_identity_column,
                             is_part_of_unique_key,
                             is_updateable,
                             is_computed_column,
                             is_sparse_column_set,
                             ordinal_in_order_by_list,
                             order_by_is_descending,
                             order_by_list_length,
                             tds_type_id,
                             tds_length,
                             tds_collation_id,
                             tds_collation_sort_id
                         )
    EXEC(@CurrentCommand);

END TRY

BEGIN CATCH

        INSERT #fail (
                      CurrentObject,
                      Command,
                      Error_Line,
                      Error_Message,
                      Error_Number,
                      Error_Procedure,
                      Error_Severity,
                      Error_State
                     )

        SELECT @CurrentObject,
               @CurrentCommand,
               ERROR_LINE(),
               ERROR_MESSAGE(),
               ERROR_NUMBER(),
               ERROR_PROCEDURE(),
               ERROR_SEVERITY(),
               ERROR_STATE();

END CATCH;

    UPDATE #success
    SET object_name = @CurrentObject
    WHERE object_name IS NULL

FETCH NEXT FROM result_cursor into @CurrentObject, @CurrentCommand
END

CLOSE result_cursor
DEALLOCATE result_cursor

SELECT *
FROM #commands AS c

SELECT *
FROM #success AS s

SELECT *
FROM #fail AS f

DROP TABLE #commands, #fail, #success


Query showing a number instead of the column value

A lookup table with a single value (StateAbbr) should contain two fields:  StateId (Primary Key) as an autonumber type, and StateAbbr as a text type.  Your Persons table should contain a number type field that will store the value held by the StateId field in the StateAbbr table.  So,

The query used would be something like:

SELECT Persons.StateAbbr, States.StateAbbr FROM Persons, States WHERE Persons.StateAbbr = StatesAbbr.StateId;

Paste this statement into the SQL view of your query builder, then switch back to the design view to see the graphical representation.

This will return the actual state abbreviation code that you have assigned in the StateAbbr table.

Edit: I have included a screenshot of a quick sample I threw together based on what you asked, showing the two tables (design view), the relationship between them, and the SQL query that delivers the results I believe you are seeking.  Modify these as you need to in order to make it work for you. This screenshot changes the query I provided above to better meet naming conventions for tables and queries.

NOTE:  In the Persons table, you will create the State field using the Lookup wizard.  Point it to the States table as its source, and only include the StateAbbr field.  The PK in the States table will not be shown at all.  When filling the data into the Persons table, you will notice that it is a drop-down box where you simply select the state you want.

Please let me know if you need me to clarify anything else.


How to add another table to publisher and subscriber without reinitializing the subscriber?

There is always 70% of answer in every question, I did research Sql Server Books Online and found the solution:

After a publication is created, it is possible to add and drop
articles. Articles can be added at any time, but the actions required
for dropping articles depend on the type of replication and when the
article is dropped.
Adding Articles   Adding an article involves:
adding the article to the publication; creating a new snapshot for the
publication; synchronizing the subscription to apply the schema and
data for the new article


Oracle: Does enabling a PK rebuild its index?

It's easily tested.

There are two possible scenarios.

Scenario 1:

Scenario 1 is creation of a PK using an existing index:

SQL> create table MY_TABLE
  2  (
  3  pk number
  4  );

Table created.

SQL>

Add the index to it:

SQL> create unique index IDX_PK_MY_TABLE on MY_TABLE (pk);

Index created.

SQL>

Add the constraint:

SQL> alter table MY_TABLE add constraint PK_MY_TABLE primary key (pk) USING INDE
X  IDX_PK_MY_TABLE;

Table altered.

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
PK_MY_TABLE

SQL>
SQL>
SQL> select index_name
  2  from user_indexes;

INDEX_NAME
------------------------------
IDX_PK_MY_TABLE

Insert some data:

SQL> insert into MY_TABLE values(1);

1 row created.

SQL> insert into MY_TABLE values(2);

1 row created.

SQL>

Test the constraint:

SQL> insert into MY_TABLE values(2);
insert into MY_TABLE values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (PHIL.PK_MY_TABLE) violated

SQL>

Disable the constraint:

SQL>
SQL> alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL>

Try the INSERT again:

SQL> insert into MY_TABLE values(2);
insert into MY_TABLE values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (PHIL.IDX_PK_MY_TABLE) violated

It succeeds because the unique index hasn't been disabled/dropped when the constraint was disabled. Note that the original error was     ORA-00001: unique constraint (PHIL.PK_MY_TABLE) violated, whereas the index constraint PHIL.IDX_PK_MY_TABLE is violated once the PK constraint is disabled.

Scenario 2:

Scenario 2 is creation of the PK upon table creation:

SQL> create table MY_TABLE
  2  (
  3  pk number,
  4  constraint PK_MY_TABLE PRIMARY KEY (pk)
  5  );

Table created.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
PK_MY_TABLE

SQL>

Disable the constraint:

SQL> alter table MY_TABLE disable constraint PK_MY_TABLE;

Table altered.

SQL> select constraint_name, status from user_constraints;

CONSTRAINT_NAME                STATUS
------------------------------ --------
PK_MY_TABLE                    DISABLED

SQL>

The index has gone!:

SQL>  select index_name from user_indexes;

no rows selected

SQL>

Re-enabling it recreates the index:

SQL> alter table MY_TABLE enable constraint PK_MY_TABLE;

Table altered.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL>

If you want to keep the index:

SQL> alter table MY_TABLE disable constraint PK_MY_TABLE  keep index;

Table altered.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_MY_TABLE

SQL>


Triggers that inserts values to a table from another table

It appears you have ommited the BEGIN and END tags in your TRIGGER declaration.

COALESCE(), SUM(), and AVG() calls need a table reference, looks like your passing a presumed column reference, also FROM keyword is missing after these calls.

Its difficult to follow your database structure from this example.  Tables 'report' and 'inventories' are clear enough, but 'i', 'p', 's', 't' are not.  The column names are descriptive, but its not always clear what table or tables they reside in.

The one thing that sticks out most to me is two table names supplied in the FROM clause on line 13, and the extra table supplied after all three closing parentheses in your LEFT JOIN statements.

Are you wanting this report to be run AFTER every time an UPDATE query is performed on the 'inventories' table?  You may be better served by building a INSERT query that builds this report on demand, see if it produces what you want, and then threading it into a TRIGGER if you want it automated.

For example, something like this, presuming it's supplied a specific drug_id to run with.  Though, with a separate database call and a while loop, you could easily run this on every drug_id.

INSERT INTO report ( drug_id, total_sales, average_sales, amount_remaining)
   VALUES (
( SELECT drug_id FROM i WHERE drug_id = $report_focus),
( SELECT COALESCE(total_sales, 0) FROM p WHERE MONTH(complete_sold) = 01 ),
( SELECT COALESCE(average_sales, 0) FROM s WHERE complete_sold BETWEEN '2016-11-01' AND '2016-12-31'),
( SELECT COALESCE(amount_remaining, 0) FROM t)
   );

I find it helpful to use ALL CAPS on MySQL SQL commands and functions for easier seperation between keywords and content.

Helpful links:
MySQL Trigger Syntax
Hive SQL query checker


This is a sad story.

When the SQL standard committee defined the NULL-ness of composite values, they must have taken the wrong drugs.

Anyway, they ruled that a composite IS NULL if all elements are NULL, and that a composite IS NOT NULL if all elements are NOT NULL.

In other words, there are composites that are neither NULL nor NOT NULL.

PostgreSQL implements that, but treats composites as NOT NULL in most other respects. There have been discussions about this, but in the face of the standard's definitions, nobody could come up with a consistent solution.

This is a mess, and you are well advised not to think too hard about this if you want to keep your peace of mind.


TLS1.0 disabled and 3rd party OLE DB use

See Does Microsoft OLE DB Provider for SQL Server support TLS 1.2 and the authoritative answer from Dan Guzman.  He references a newer version of OLEDB that supports TLS 1.2, which can be downloaded at Microsoft® OLE DB Driver 18 for SQL Server®

And to specifically answer your questions, there is no way for us to know if the installer can be forced to use a different provider.  It's certainly possible if it uses a connection string template that can be modified, but based on my experience I would say it's highly unlikely you'll be able to modify the installer to use a different provider.

As for logging what type of connection--see if the SQL Server error log contains any information when the failure occurs.  You can also set up an extended event in SQL Server to capture information about logins (there is "Connection Tracking" template).  The client_app_name field collected by "Connection Tracking" template will include the information that the client passes about the provider.  This can sometimes be blank, though, as it depends on the client to set that information.  Typically a provider will have that information included automatically unless the developer does something to override it.


If you want it for a lab environment you can get a preconfigured VHD with an evaluation edition of 2008 R2 installed. It is valid for 180 days:

SQL Server 2008 R2 Standard on Windows Server 2008 R2 SP1 Standard (x64)

Also, you can get it if you or one of your colleagues have a MSDN subscription. If the subscription is not yours you can download it from there and use it for the 180 day evaluation period.


Community wiki answer:

I think naming the column as fieldtype_fielddescription, i.e. user_id_additional_info, is more safe.

For example, not user_subscribed_to_id = the user ID that was subscribed to, but user_id_subscribed_to.

It allows, for example, to select the fields packet from information_schema if needed, using a simple LIKE 'user_id_*' pattern.


How to check if databases are consistent in pgsql replication?

You could try pg_comparator.
Also older version of pt-table-checksum (or mk-table-checksum) could compare pg data - but I wouldn't recommend these old versions.


LEFT JOIN Producing Inflated COUNT() numbers

If you run the query without aggregates, you'll see what is happening:

Select
    *
FROM @AllVendor av
    LEFT JOIN @BBC bbc
        ON av.vendor = bbc.vendor
    LEFT JOIN @TotalSalesAmt tsa
        ON tsa.vendor = av.vendor
where av.vendor = 'vendor 1'

+----------+----------+---------------+---------------------+----------+---------+
| vendor   |  vendor  | vendorcasenum |     casenumdate     |   vendor | saleamt |
+----------+----------+---------------+---------------------+----------+---------+
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 10,20   |
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 0,40    |
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 20,10   |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 10,20   |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 0,40    |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 20,10   |
+----------+----------+---------------+---------------------+----------+---------+

Due there are 2 rows in @BBC table of Vendor 1:

('Vendor 1',   'E12301',    '2013-11-06')
('Vendor 1',   'E12221',    '2013-11-06')

The aggregated SUM(saleamt) = 61.40

You can use one subquery to calculate SUM(saleamt) and another to calculate COUNT(vendorcasenum):

Select
    vendor,
    (select ISNULL(SUM(tsa.saleamt),0)
     from @TotalSalesAmt tsa
     where tsa.vendor = av.vendor) TotalSales,
    (select COUNT(bbc.vendorcasenum)
     from @BBC bbc
     where av.vendor = bbc.vendor) TotalCases
FROM @AllVendor av
ORDER BY av.vendor ASC;

This is the final result:

+-----------+------------+------------+
|   vendor  | TotalSales | TotalCases |
+-----------+------------+------------+
| Vendor 1  | 30,70      | 2          |
| Vendor 10 | 0,00       | 1          |
| Vendor 11 | 0,00       | 1          |
| Vendor 12 | 0,00       | 0          |
| Vendor 13 | 0,00       | 0          |
| Vendor 14 | 0,00       | 0          |
| Vendor 15 | 0,00       | 0          |
| Vendor 16 | 0,00       | 0          |
| Vendor 17 | 0,00       | 0          |
| Vendor 18 | 0,00       | 0          |
| Vendor 19 | 0,00       | 0          |
| Vendor 2  | 30,20      | 1          |
| Vendor 20 | 0,00       | 0          |
| Vendor 3  | 20,00      | 1          |
| Vendor 4  | 0,00       | 1          |
| Vendor 5  | 0,00       | 1          |
| Vendor 6  | 0,00       | 1          |
| Vendor 7  | 0,00       | 1          |
| Vendor 8  | 0,00       | 1          |
| Vendor 9  | 0,00       | 1          |
+-----------+------------+------------+


Database Security - Prevent column writing?

I am concerned if someone gets in my DB

Well, don't let them.

  Am I able to prevent writing / editing of that column except by a specific user?

If your tables aren't owned by the user the application runs as, then yes, you can use column-level GRANTs to give partial access to tables.

So you'd GRANT everyone the right to use the other columns, and only grant one particular database user the right to edit that particular column.

However:

This only works with database users created with CREATE USER or CREATE ROLE. If your application does its own user management with ordinary database tables, PostgreSQL's permissions cannot be used.
Rails likes to have the application user own the tables so it can do migrations etc. You cannot restrict the rights of the owner.

... so in practice, I suspect that the answer will be "no, not with Rails, do it at the application level".

There's not really enough detail here to say more.


Restoring a database, changing recovery model

I read that the change in recovery model only has effect after a backup is taken. However, if after the performing the above I do

This is mainly for when you change from simple recovery to full. After changing from simple to full you need to take full backup to take database out of Pseudo simple recovery. Pseudo simple recovery means even if database is in full recovery model it will behave like simple and automatic checkpoint would happen and you will not get point in time recovery. The moment you take full backup you tell SQL database engine that Now I would manage log truncations by taking transaction log backup.

  Do I need to take a backup to actually change the recovery model of my test database to simple?

For your case since you are changed recovery model to simple so you DONT need to take any backup to actually make it behave in simple recovery. I am not talking about normal backup you take for your dev databases. You should continue taking normal full backup of your dev databases.


MariaDB Update Column Values Based on Another Column's Value

You can use left() to get the first n characters of a string.

UPDATE elbat
       SET basin = 'NCP'
       WHERE left(stormid, 2) = 'CP';


SUM() ignores GROUP BY and sums up 4 rows instead of 2

I believe you have a misunderstanding of what GROUP BY does. Not surprising, I had issues myself when first learning, in large part because the MySQL manual doesn't actually explicitly say what GROUP BY does, at least not that I can find (and I searched a lot, just now; plenty of caveats and special behavior, not so much an actual definition).

My (on the fly) Definition:

  GROUP BY condenses your SELECT results so that only 1 row is returned
  for each distinct combination of values for the columns specified in
  the GROUP BY clause. In that sense, it is similar to DISTINCT, but
  works on the columns in the GROUP BY instead of the SELECT statement.

In non-MySQL land, you can only SELECT columns you specify in your GROUP BY clause, PLUS any aggregate functions you want. Those aggregate functions, including SUM, operate on a per row basis, reporting a result ONLY for the now "hidden" extra rows.

As you can see, that is what your query is actually doing (or would be, but I think you gave an inaccurate example, as ypercube points out in the comments). It is summing up all the now-hidden extra rows, and reporting their total, for the given sch.id.

If you want the total of only distinct values of each sch.id, you'll have to do things differently to get the information you want.

One reason that it is not simple, is MySQL has no idea WHICH row you want to include in the sum. They may be all the same in your example (8100), but there's no guarantee of that.

Since MySQL allows you to select columns that are neither specified in the GROUP BY clause nor are aggregate functions, it essentially chooses one at "random" and displays it to you. While not actually random, it is non-deterministic, and can change at any time for the same query and data, even if it appears to you to always give the same result.

So, before you can proceed, you need to decide how you want to determine which row for each sch.id contains the value you want to sum.

If you know the values to always be the same, then one simple (although not necessarily optimized) solution is to wrap your original GROUP BY query in another query (making the original a subquery) and then use the SUM function in the outer query, without a GROUP BY clause. The subquery will remove your duplicates, and the outer query will sum up the total of the de-duplicated rows.


Creating a Min date with Session Date also included in the table

When you post questions like this, it's really helpful if you post your sample data as inserts, so anyone can hop in and immediately query it.

CREATE TABLE #whatever
(
    id INT,
    employeeid INT,
    sessionloginlocal DATETIME
);

INSERT #whatever ( id, employeeid, sessionloginlocal )
SELECT *
FROM (
VALUES
(1,   354, '2017-12-12 07:29:36'),
(2,   354, '2017-12-11 16:58:21'),
(3,   354, '2017-12-12 10:59:34'),
(4,   354, '2017-12-11 18:32:59'),
(5,   354, '2017-12-11 18:33:34'),
(6,   354, '2017-12-11 17:16:58'),
(7,   354, '2017-12-12 10:56:29'),
(8,   354, '2017-12-11 19:37:54'),
(9,   354, '2017-12-12 10:57:52')
) AS x (id, employeeid, sessionloginlocal)

This code uses a case expression and MIN as a windowing function to find the min login time per user. When they match, we display it as a date. When they don't we display NULL.

SELECT *,
       CASE WHEN w.sessionloginlocal =
               MIN(w.sessionloginlocal) OVER ( PARTITION BY w.employeeid )
               THEN CONVERT(DATE, w.sessionloginlocal)
               ELSE NULL
       END AS min_sessionlocal
FROM   #whatever AS w;


What does 'fully determines' mean in the context of postgres functional dependency statistics?

It means that if col1 is identical for two rows, then col2 will also be identical.

In other words, if the functional dependency were perfect, you could remove col2 from the table and use a lookup table that maps col1 to col2. But don't forget that these are only statistical data from a sample of the rows, so even if you see a dependency of 1 in the extended statistics, that is no proof that it is really always so (only very often).


Multiple versions of SQL Server on the same development machine

No, shouldn't be any issues; I have a VM right now with 2000, 2005, 2008, 2008 R2, 2012 and 2014 and it works just fine.

I do suggest to observe the following:

install the oldest version first, and get it completely patched before moving on to the next.
use a very consistent and intuitive naming scheme for each instance (e.g. .\SQL2000, .\SQL2005, etc). Also don't install a default instance (because it can't self-document what version it is, its purpose, etc).
don't install multiple versions of the client tools, except the case where you need to manage SSIS packages in multiple versions. For just about everything else, you can manage all down-level versions with the 2012 client tools. If you plan to use Visual Studio (any version), don't install it in the VM, use it in the host. Different versions of Visual Studio don't always play well with different versions of SQL Server (particularly client tools).
make sure to not allow the default startup behavior, where all instances will start automatically (you probably only want a specific version to be running and using resources at any given time).
use the same sa password, SQL Server service account, SQL Agent account etc. on all instances, and make sure you add your own account as an admin on all instances. The last thing you want is to get locked out of one instance because it has a different password or to suffer some issue that you can't tell is account-related or version-related.
don't create databases on version x and expect to backup/restore them on x-n. Backups are not backward compatible (regardless of compatibility level), only forward.
be mindful of recovery models, log auto-growth settings, and large databases. For example you don't want to install the AdventureWorks DW database on every single instance - this takes up valuable space and you will soon consume your entire drive. Keep those databases handy (like on an external drive) but only attach/restore them when you need them on a specific version.


MySQL - ERROR 1045 (28000): Access denied for user : permission issue

The problem is simple: On the second server, the table mysql.user is from a previous version of MySQL. How do I know this? I dealt with that error message back on April 12, 2012 : Cannot GRANT privileges as root

The problem stems from the number of columns in mysql.user. When you run

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You get these numbers from each MySQL version

43 in MySQL 5.6
42 in MySQL 5.5
39 in MySQL 5.1
37 in MySQL 5.0

You cannot run that query on MySQL4.x since the information_schema database does not exists prior to MySQL 5.x. You would have to run DESC mysql.user to see it has 31 columns for MySQL 4.x.

I addressed this back on May 01, 2013 : Can I find out what version of MySQL from the data files?

While my posts suggested radical methods for correcting this, the recommended way it to run the utility mysql_upgrade on the second server as follows:

# mysql_upgrade --upgrade-system-tables

This should add the missing grant columns to mysql.user.

Give it a Try !!!


SQL Server Management Studio - Actual Execution Plan - Actual Row Size and Actual Data Size

I can see the Estimate Row and Data Size, but where is their actual
  size? Is there a way to see this information?

As commented by @Randi Vertongen I use SQL Sentry Plan explorer to see actual data size if I need. This is a free tool you can download (without even giving out your email address).

Plan Explorer Installation & Overview

Here is an example of that data you are looking for in plan explorer.

As far as actual number of rows and number of rows read is available in SSMS tool tip or properties. Here is an example.

  Same question for actual CPU, I/O, number of executions and so on.

For CPU and I/O you can right click individual operator and see it in properties or look at xml.  If you right click the leftmost node and see it's properties you will total use of total cpu and  memory grant details. Number of execution is also available in SSMS. See picture above.

Following is an example of overall cpu, time and memory grant information:

Following is an example of looking at details per operator. This will vary depending on the type of operator. This output is from a sort operator.

Here is a partial list of when some of these features were added and back ported to older versions.

Overall plan explorer is a far better tool to look at execution plan and many data professionals use it everyday.


This is likely to be a problem with daylight saving time: In the US (and possibly other places) the hour between 02:00am and 03:00am on 8th March 2020 does not exist because the clocks are moved forward one hour at 02:00am to 03:00am.


Does a full database backup include FileTable files?

Yes it does include the filetable data. From BOL :

  When you use SQL Server to back up a FileTable, the FILESTREAM data is backed up with the structured data in the database. If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.

-

  How would I perform the restore of a single file, for example?

You can use the same restore concept and do a point-in-time restore with STOPAT parameter.

For detailed example refer to (Page 319): Backup and Restore for FILESTREAM Databases.


MySQL insists on trying to use InnoDB even when told not to, please help

To disable InnoDB, add this to /etc/my.cnf on the MySQL 5.5. slave:

[mysqld]
skip-innodb

and run this on the Slave:

service mysql restart

According to MySQL Documentation on CREATE TABLE :

  You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

  CREATE TABLE new_tbl SELECT * FROM orig_tbl;
  For more information, see Section 13.1.17.1, “CREATE TABLE ... SELECT Syntax”.

  Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

  CREATE TABLE new_tbl LIKE orig_tbl;

  The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

You must specify the ENGINE upon creation

CREATE TEMPORARY TABLE ... ENGINE=MyISAM SELECT ...

to get around this problem


Indexed view not used in actual execution plan?

You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) in order for your query to use the Indexed View (instead of expanding it to the underlying table). This will solve your first issue.
This is how you can use it in your query:
SELECT TOP (100) *
FROM Losses_CombinedPortfolio WITH (NOEXPAND)
ORDER BY Loss DESC

You can see more information in the Query Hints docs by Microsoft. But this is the relevant section to what you're currently experiencing:

The indexed view is not expanded only if the view is directly
referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH
(NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.

This is one of the few query hints you can utilize without worrying about it being bad practice, and actually it's recommended to use for certain reasons as well.
As far as your issue with the "imprecise" error, this is as you guessed it, due to the Loss column being of type FLOAT (which effectively is nondeterministic across different CPU architectures). So even casting it won't help you, you'd have to change the underlying type to something that is precise so that value is materialized in the table before you can index it in the view. (E.g. if you can change the data type to DECIMAL, then you'd be able to add that column to your index.)


Uninstalling 2 instances on 1 node of a 2 node SQL Server 2012 failover cluster instance

When there is no instance on Node 2, does it become an Active Passive cluster?

Let me start by saying there is nothing like Active-Active cluster a more correct term which should be used is multi-instance cluster. When you have single instance of cluster it would simply be called as single instance failover cluster or just failover cluster instance (FCI), people do call it active passive but that is not correct. An active-passive cluster can have 2 instance on one node and other node having no instance which makes it look like active passive but actually it is multi instance failover cluster.

  Do I have to take the node offline before the uninstall?

No, you do not have to take node offline.

  Can I get any links to uninstalling an instance?

It is in the same MSSQLTIPS link you have posted. And also note when you uninstall an instance you have to uninstall it from ALL nodes of the cluster. That is what the link says in line step 9. Please dont confuse by removing node and removing node from WSFC, its removing node from failover cluster instance(FCI).

  Am I right in understanding that you need the installation media to uninstall the instance? And that I cannot use uninstall programme method for this?

When you are removing FCI from Windows server cluster you have to run the setup file and select remove node from failover cluster like shown in the links you are already referring. If you do not have FCI but standalone instance of SQL Server you can go to add remove program and select uninstall, this will uninstall the SQl Server.

  So does it mean if I use the remove node option , it will remove only the instance BUT the node will still be there ?

Yep correct.


Postgres slave max_connections = master's max_connections?

The documentation has something to say about it [emphasis mine - ed.]:

  When running a standby server, you must set this parameter to the same
  or higher value than on the master server. Otherwise, queries will not
  be allowed in the standby server.

There are a number of reasons to try to keep your hardware and configuration as close to identical as possible.  So I think a strict reading of your question is no.  But there are good reasons to keep it the same.

The big reasons have to do with two things: base backups and fail over.  If you are taking base backups from the slave then the base backup also includes the configuration.  If you later restore it on the master, then you have configuration changes taking place solely because you restored a backup.  In a disaster recovery situation, the last thing you want is to be trying to track down "what changed when we restored the backup?!"

The second thing is that this applies in more or less the same way when failing over.  Because failing back is still a bit of a pain, you need to be assuming your slave can handle the master's load for a bit of time while you set things up.  Keeping the configuration the same is a good way to avoid unpleasant surprises there.


postgres auto_explain : setting log_min_duration doesn't start

I put also this setting and it solved :

  custom_variable_classes = 'auto_explain'


innodb_buffer_pool_size=27G is too close to the limit especially with a large amount of (max-)connections.
Lower this down and examine utilization. show global status like 'innodb_buffer_pool_pages% should be full after a reasonable amount of warmup (24hrs+)
show global status like 'innodb_buffer_pool_read%. read_requests vs reads should be quite large. (reads% < 1%).
Once you have this balance hopefully there is sufficient ram available to avoid OOM, but still has sufficient performance.


MongoDB: replication headroom gone below 1 days (avg/sec)

"Replication headroom" means "how long time of changes your opLog can store". To get larger "replication headroom" you need to expand your opLog size at least one node, of course it is preferred to have same size opLog at every node.

Reason why this headroom has gone under one day, is that because you have more changes (opLog documents) nowadays, than before.


ORA-01506: missing or illegal database name

$ oerr ora 1506
01506, 00000, "missing or illegal database name"
// *Cause:  No db_name INIT.ORA aprameter was specified.
// *Action:  The database name must be given in the db_name
//          INIT.ORA parameter.

Your ORACLE_SID is XE.
When you issue startup, your process looks for a file %ORACLE_HOME%\database\spfileXE.ora, or if it does not exist, then %ORACLE_HOME%\database\initXE.ora.

You get the above error when the db_name parameter is missing from them.

You can edit initXE.ora with any text editor and add the missing parameter: *.db_name='XE'.
spfile should not be edited directly, first create pfile from it:

create pfile='C:\temp\initXE.ora' from spfile;

Add the missing parameter to `C:\temp\initXE.ora', then create the spfile from it:

create spfile from pfile='C:\temp\initXE.ora';

After that, try startup again.

  ORA-00205: error in identifying control file, check alert log for more info

That is a completely different error, meaning the files defined in the parameter control_files are inaccessible.


Postgresql installation failed if we provide non-english password(Chinese or French)

I'm not a Postgresql guy, but from this blog by Jasper Peilee:

  If you need Asian-language support, don’t leave(sp) the locale as “default”. Simply choose any other locales, e.g. “C” in the combo list.

  After the installation, supposing you need simplified Chinese support, change the configuration using command:

  Windows: initdb -D data –locale=chinese-simplified_china.936 -E UTF-8
  Unix/Linux: –locale=zh_CN.UTF-8

Hopefully that points you in the proper direction.  If not, please update your question with some more information such as the error you receive, the installer you're using, the installer options, etc.


Use domain username on SQL server reporting services installed in a server which is part of a Workgroup

Well according to MSDN here:

  Group or User
  Type the name of a group or user account in your
  domain. If the report server is running under a local account, you
  must specify local groups or users. If the report server is running
  under a domain account, you must specify domain groups or users. Enter
  the account in this format: <domain>\<account>.

I would take that to mean you will not be able to do what you are wanting. A work around I would try is to create a local group, adding the domain group/user to it, and try to add that local group to the new system role. It has been a while since I have supported a mixed environment so I am not sure if you can add domain accounts to a workgroup computer. You might have to check with your AD admin for assistance on that.


Yes, it is possible. Use MySQL's EVENT mechanism. As the documentation says, it's the MySQL equivalent of cron or the Windows Task Scheduler. An overview is available from here and an example here. They can also be seen as TRIGGERs fired by a time rather than by SQL.

Be sure to enable events via the global system variable event_scheduler as explained here.

You can set up an event to run at 00:01 and store the result in a table and then your app can refer to that table throughout the day, safe in the knowledge that the value won't change until 1 minute past midnight the following day (or whenever suits you).


SQLLite query datatime divided into week of the year, and month of the year

I made it

SELECT
  strftime('%Y-%m',star_time) AS date
  , Sum(distance)
FROM activity
WHERE
Group By
  strftime('%Y-%m',start_time)


mySQL Workbench new Connection : There is no disk in drive. Please insert disk in \Device\Harddisk2\DR2

Did you install that on network share? Please  try reinstall only workbench using this package https://dev.mysql.com/downloads/workbench/ and use default location c:\program files ....
Maybe you didn't install MySQL Workbench Windows Prerequisites. You can find links on the same download page.

Regards,
Marcin


SELECT
   Name,
   SUM(DC.data) AS Data,
   MAX(DC.TS) AS TS
FROM II
INNER JOIN DC ON DC.id = II.id
GROUP BY II.Name
ORDER BY 3 DESC

In stead of ORDER BY 3 DESC, you could also write ORDER BY MAX(DC.TS) DESC.


Selecting from an excel spreadsheet into SQL Server table

Excel is 32-bit, if you are using 64-bit version of Windows it could be causing this problem. I have 32 bit drivers installed and get the same error as you. In order to install the office 64 bit drivers would have to uninstall the 32 bit drivers first.


Trigger creation for Sybase SQLAnywhere 16

I think the problem you are running into is caused by getting a mixture of T-SQL (ASE) syntax and SQLA (Watcom) syntax. You can use T-SQL triggers in SQLA - but with quite a lot of limitations. It better to do a re-write if you can.
This complies, whether it works, I have no idea! (The changes are all in the DECLARE statements. One per variable, double quotes removed from the timestamp datatype (as per markp-fuso's comment). If you really need local variables further down the code, you have to declare them at the beginning of a new BEGIN / END block)
create trigger DBA.WKM_autoFillCL143 after insert order 2 on DBA.Case_Checklist
  when ((select top 1 inserted.code from inserted) in( '143' ) )
  begin
    declare @parentRef integer;
    declare @desc varchar(255);
    declare @desc1 varchar(255);
    declare @checkID integer;
    declare @tabid integer;
    declare @recProvider varchar(255);
    declare @recsRequested varchar(255);
    declare @dateFrom datetime;
    declare @dateTo datetime;
    set @parentRef = (select top 1 parent_ref from inserted);
    if (@parentRef <> '0')
      then
        set @desc = (select description from case_checklist where checklist_id = @parentRef);
        if (@desc is not null)
          then
            set @checkID = (select top 1 checklist_id from inserted);
            update WKM_RecordChecklistMapping set c143 = @checkID where c142 = @parentRef;
            set @tabid = (select top 1 tab_id from WKM_recordChecklistMapping where c142 = @parentRef);
            set @tabid = (select top 1 tab_id from user_tab2_data where tab_id = @tabid);
            if (@tabid is not null)
              then
                set @recProvider = (select top 1 Provider_Name from user_tab2_data where tab_id = @tabid);
                set @recsRequested = (select top 1 Records_Requested from user_tab2_data where tab_id = @tabid);
                set @dateFrom = (select top 1 For_Dates_From from user_tab2_data where tab_id = @tabid);
                set @dateTo = (select top 1 Through from user_tab2_data where tab_id = @tabid);
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+coalesce(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+coalesce(convert(varchar(255),@dateTo,1),'00/00/00');
                set @checkID = (select top 1 checklist_id from inserted);
                update case_checklist set description = @desc1,staff_assigned = 'ZKS',due_date = (today()+7) where checklist_id = @checkID
              end if;
          end if;
      end if;
  end;


The counter Memory Manager: Free Memory (KB) tells you below. Read the BOL document

  Free Memory (KB): Specifies the amount of committed memory currently not used by the server.

So what this means is of all committed memory SQL Server is not using 40 MB of the memory. This is not telling you free memory on the SQL Server system. A committed memory is one that is backed by physical RAM. When a process starts it can address any physical memory address in its VAS but memory will only be committed if that VAS region is backed by physical memory.


XACT_STATE() is not something that SqlClient has any special insight on, much like transaction level. So, just query it with SqlCommand.

// If you already have an open connection...
public int GetXactState (SqlConnection connection)
{
    using (SqlCommand command = new SqlCommand("Select XACT_STATE();", connection)) {
        return (int)command.ExecuteScalar();
    }
}


Data Domains use their own deduplication and compression features, which don't always play well with SQL Server's own compression. That's the main point.

Whether this is problematic or not depends on many factors. If you're not compressing your backups with SQL Server, the Data Domain will be able to deduplicate the data more efficiently, but more data will have to travel through the network. If you're compressing your backups, the Data Domain will deduplicate less efficiently, so your sysadmin will likely push you in the uncompressed route.

Another possible issue is an higher amount of time for restores, since the DD will have to apply its magic to the data before making it available to you.

In the end, there is only one way to know for sure: test it. Take backups with and without DD for a significant amount of time (take at least the number of backups you will normally keep online - say 1 month), with and without compression. Restore those backups to a test server regularly, compare the backup/restore times with and without DD and draw your conclusions.

We tested the impact of DD on our backups and it turned out to have an acceptable impact on performance in our case, especially taking into account the advantages offered by DD. YMMV: your RTO may or may not not allow such impact.


Why changing the database owner doesn't change the owner in PostgreSQL dump?

The way you are invoking pg_dump (without -C), the database metadata is not dumped at all.

Use this:

pg_dump -h localhost -U postgres database_name -C -f mydb.dump

This will add CREATE DATABASE and ALTER DATABASE ... OWNER TO to your dump.


Disable/enable PostgreSQL trigger inside function for the same table

One option would be to:

Jack up the security on the table so normal users can't directly access it at all, and remove the protecting trigger.
Create an updatable view on the table which is accessible by normal users, and add an INSTEAD OF trigger to protect the column from updates (assuming you want it still visible; if not, simply omit it completely from the view definition.)
Create a function with definer's rights which bypasses the view and directly updates the column in the base table.


Creating unique constraint to be validated from input

This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this:

create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm'));

However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is fine). The workaround for this is to create a wrapper function that is marked as immutable:

create function my_month(some_date date)
  returns varchar
  immutable
  language sql
as
$$
  select to_char(some_date, 'yyyymm')
$$
;

Then you can create an index using that function:

create unique index idx_tbl on tbl (counter, my_month(date_booked));

If you now insert the fourth row, you get the error:

duplicate key value violates unique constraint "idx_tbl"

If the table is big, you might want to use a number instead of a varchar in order to reduce the size of the index as well e.g. by using to_number(to_char(some_date, 'yyyymm'))


Unique constraint on multiple nullable columns Sql Server

There's nothing special that would prevent you from using a standard unique constraint here, unless there's something you aren't telling us.

When a unique constraint is created on a set of nullable columns, NULL is treated (in terms of uniqueness) like any other of the distinct values in the set. (I believe this behaviour varies across RDBMSes.)

So I think the only case you might have to protect against here is the where all three columns contain NULL at the same time. If this is invalid for your business logic, also create a CHECK constraint to disallow this combination.


Creating same unique constraint with different names

It can be result very confusing mixing names constraints and slow down your queries because they have the same name

maybe this results helpful

  CREATE ONLY ONE CONSTRAINT INDEX AND INCLUDE THOSE NECESSARY COLUMNS

Example

ALTER TABLE users ADD CONSTRAINT findUser UNIQUE (nameUser,emailUser);

No double constraint and no warning message and you keep the SQL rules alive

  Remember SQL is an standard but not implemented at the same time for
  everyone with the same rules

As you can see at the following link, in fact these scenario is a bug

https://bugs.mysql.com/bug.php?id=37520

and finally recognized in: https://bugs.mysql.com/bug.php?id=8565

and finally fixed in MySQL Server 5.6.7


How to set AUTHORIZATION on CREATE SCHEMA

You do not need to create a new user to be owner of the schema.

The default dbo owner is fine, especially if your adding the schema for organization reasons rather then security.

You do not need to create new users to access the new schema.

The only thing I would add is to make sure if you do change the owner from dbo, you change it to a domain service account or other permanent sql account. If the user gets deleted it can cause issues accessing the schema.


Postgres automatic insert time stamp with time zone (india)

Actually, I want to give a comment about your question. I can't to do that because I have low reputation here. So, I decided to answer whether it fits with your question or not.

Let's I start. Would you like determine "query is inserted" first?

I have an assumption about your "query is inserted". "Query is inserted" is every query is inserted or affected on database (it could be insert, update, delete, etc.) that will be noted in log. Log here means log table where the trigger handles every query. OK. I give you clear explanation by an example below.

First, make a definition of log table like this,

CREATE TABLE query_log (
    log_id SERIAL PRIMARY KEY,
    log_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    action VARCHAR(20) NOT NULL
);

Action column above determines whether query performed by someone (for example when someone do insert, so the action column will be "insert" and so on). Then, we have to make a trigger to handle what we write on log table. It depends on table what affected by query. For example, we do query on table query. So,

CREATE TRIGGER add_insert_query_log
AFTER INSERT ON name_of_table
FOR EACH ROW
EXECUTE PROCEDURE insert_query_log();

and

CREATE OR REPLACE FUNCTION insert_query_log() RETURNS TRIGGER AS
$$
BEGIN
   INSERT INTO query_log VALUES (currval(log_id)), DEFAULT, 'insert');
   RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

You can continue with another function for update, delete, and etc or you can combine all of them in one function.


Order By First Condtition and If multiple matches, order by second condition

Your query is wrong.

case when substring(Name from -1 for 3)
     then 1
     else 0 end

The expression substring(Name from -1 for 3) returns one last symbol from Name (if exists). Not 3 last as you want...

So the whole expression returns 0, when Name is NULL, empty string, or when the last symbol in Name is not a digit rather than 0. And 1 otherwise (when last symbol exists, and it is a digit from 1 to 9).

Correct query (looking on your query) is

SELECT Name
FROM Students
WHERE Marks > 75
ORDER BY RIGHT(Name, 3) ASC,
         id ASC;


SQL Server 2012: Users cannot see database properties

I think the problem comes from the fact that all the remote SSMS were v 2008. When I logged in from another computer with SSMS 2012, I could display everything.

But we use the 2 versions: 2008 and 2012. Any one knows a quick fix to access a SQL Server 2012 with a SSMS 2008?

Thank you


Is dmp file suitable despite ORA-01555 'snapshop too old' warnings?

Technically, you could, but it is incomplete, so better repeat the export after performing the necessary actions to avoid ORA-01555 errors (have an appropriately sized undo tablespace, set undo_retention and retention guarantee).


Inserting many documents in collection, but only unique elements, based on specific field

First add an unique constraint on field link.

db.collection.createIndex({"link": 1}, {unique: true})

And then without using insertMany, insert each document of the list using javscript functions.

Lastly remove the index(unique constraint) we no longer need.

db.collection.dropIndex("link_1") // or your index name


Oracle Global Statistics preference NO_INVALIDATE

Simply put, they are the parsed SQL statements in the shared pool related to the table. When they are invalidated, they need to be hard parsed again the next time they run.

A typical use case for this: the database generates a suboptimal execution plan because of stale statistics on a table. The database fails to recognize this mistake on subsequent executions, but you notice it and decide to gather statistics. With NO_INVALIDATE => true, this will not have any effect on the SQL with poor performance, the database will continue to use the old, suboptimal plan. That is why you should gather statistics with the NO_INVALIDATE => false option in such cases.


Find material for improvement of my SQL ability

If you are looking to just improve your SQL coding ability I would go to the following sites:

http://www.sqlservercentral.com - check out the scripts section
http://www.sqlcourse.com/index.html - this site has some good basic tutorials
http://www.sql-tutorial.net/ - also another good site for tutorials
http://www.mssqltips.com/ - covers a lot of different areas but still has good material
http://blog.sqlauthority.com/ - VERY knowledgeable fella. Just search his site for what you are looking for

Here is a list of sample SQL databases for you - http://msftdbprodsamples.codeplex.com/

For some FREE reading on check out - http://www.sqlservercentral.com/articles/books/67441/


Property class Length Annotation PersistedResolvableAnnotation

I found the issue myself. I will document it in case anyone else is facing the issue in future.

The following user definition:

CREATE USER [Angela] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [Angela];

created the following code in the model.xml that avoids publishing, maybe because the schema (Angela) used there is invalid because it simply does not exist:

<Element Type="SqlUser" Name="[Angela]">
            <Property Name="IsWithoutLogin" Value="True" />
            <Relationship Name="DefaultSchema">
                <Entry>
                    <Annotation Type="PersistedResolvableAnnotation" Name="[Angela]">
                        <Property Name="TargetTypeStorage" Value="SqlSchema" />
                        <Property Name="Length" Value="9" />
                        <Property Name="Offset" Value="63" />
                    </Annotation>
                </Entry>
            </Relationship>
        </Element>

After changing the user definition to:

CREATE USER [Angela] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [dbo];

the model lookes as followes and could be published:

<Element Type="SqlUser" Name="[Angela]">
        <Property Name="IsWithoutLogin" Value="True" />
    </Element>

I have a feeling that this strange behavior is kind of a follow-up of another misbehavior because I would expect the not existing schema to raise a compiler error.


This is not true.

Transaction log backups are incremental, that is, they only store incremental changes since the previous transaction log backup.

You are confusing the differential log backup with "backups are not incremental", because differential backups only keep a record of the changes since the last full backup.

(Differential backups work in all recovery models, namely Simple, Bulk-Logged, and Full.)

If you want incremental backups, take transaction log backups regularly.