Get stopwords of a InnoDB fulltext index I can set stopwords for a MySQL fulltext index with SET innodb_ft_user_stopword_table ... before creating the fulltext index.

But how can I figure out the existing stopwords of before created fulltext index?

I can request information_schema.INNODB_FT_DEFAULT_STOPWORD for the default stopwords of the server. But I would like to know the used stopwords for a specific table.

In the MariaDB knowlegebase, I found a description how to determine the stopwords: https://mariadb.com/kb/en/library/full-text-index-stopwords/#innodb-stopwords

But I can't set innodb_ft_user_stopword_table to a "normal" table with several fields. (I'm using MariaDB 10.1.39.)

A way to find out the used stopwords for MyISAM tables, I would like to know too.

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.


In Postgres (tested with v9.3) you can use the dedicated inet data type, to store IPv4 addresses with only 7 bytes (or IPv6 with 19 bytes) and with automatic integrity checks and dedicated functions and type casts etc.
Schema
The translated schema could look like this:
CREATE TABLE log (
  id serial PRIMARY KEY
, dst_port int
, src_ip inet
, dst_ip inet
);
CREATE INDEX ON log (dst_port);
CREATE INDEX ON log (src_ip);

I moved to dst_port int to the 2nd position to optimize alignment / padding:

Configuring PostgreSQL for read performance

Now we can use standard window functions (not possible in MySQL).
Step 1: Fold groups of consecutive dst_ip for same (dst_port)
One special difficulty: The aggregate function min() / max() are not yet implemented for inet in Postgres 9.4. Both are in the upcoming Postgres 9.5!
So I substituted with DISTINCT ON in the first step:

Select first row in each GROUP BY group?

SELECT DISTINCT ON (dst_port, ip_grp)
       dst_ip, count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct, dst_port
FROM  (
   SELECT dst_ip, dst_port, dst_ip - row_number() OVER (PARTITION BY dst_port
                                                        ORDER BY dst_ip) AS ip_grp
   FROM   log
   ORDER  BY dst_port, dst_ip
   ) sub
ORDER  BY dst_port, ip_grp, dst_ip;

Result as desired - with a count of rows (could be upper IP as well).
You can subtract/add integer from/to the inet type. By subtracting the row_number() all consecutive rows get the same grp - the value of grp is irrelevant, just the fast that it's the same per partition (dst_port).
Then we can GROUP BY ... - or in this special case DISTINCT ON dst_port, ip_grp. I use another window function to get the count ip_ct in the same step: count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct.
Note that consecutive IPs can cross byte boundaries (see my comment to question).
Detailed explanation for this technique:

Select longest continuous sequence

Step 2: Fold groups of consecutive dst_port for same (dst_ip, ip_ct)
SELECT dst_ip, ip_ct, min(dst_port) AS dst_port, count(*) AS port_ct
FROM  (
   SELECT *, dst_port - row_number() OVER (PARTITION BY dst_ip, ip_ct
                                           ORDER BY dst_port) AS port_grp
   FROM  (
      SELECT DISTINCT ON (dst_port, ip_grp)
             dst_ip, count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct, dst_port
      FROM  (
         SELECT dst_ip, dst_port, dst_ip - row_number() OVER (PARTITION BY dst_port
                                                              ORDER BY dst_ip) AS ip_grp
         FROM   log
         ORDER  BY dst_port, dst_ip
         ) sub1
      ORDER  BY dst_port, ip_grp, dst_ip
      ) sub2
   ) sub3
GROUP  BY 1, 2, port_grp
ORDER  BY 1, 3, 2;

Basically, repeat the same logic like in the first step, applied to the result of the first step.
But now you have to group on ip_ct additionally. And this time, you can use the simpler min(dst_port), since the port number is a plain integer.
SQL Fiddle demonstrating all.


WAL Archiving and pg_basebackup For backing up our postgres database, we're considering 2 separate options:

1- Logical backup with pg_dump
2- pg_basebackup with consistent WAL Archiving.

So I have two questions:

1- How often should we make a base backup?
Can we just create it once and then just archive the WAL files afterwards?

2- Do we need both approaches or one would suffice?

How often should we make a base backup? Can we just create it once and then just archive the WAL files afterwards?

You can, but the more WALs that have accumulated, the longer it will take to restore.  You should periodically do a test restoration.  If it takes "too long" (whatever that means for your organization) then you should take a new base backup which will need to replay less WAL.

What I usually do is keep the first base backup ever taken (for a given major version) so that I can PITR to any point.  Then keep annual base backups, so that PITR can take less time if starting from one of them.  And take monthly base backups so that crash-recovery restoration would take less time.  Once a new monthly base backup has been tested, then the second-previous one is deleted, unless it happens to also be the annual one.  If I do some maintenance operation which generates a large amount of WAL, I might take an out-of-cycle backup because otherwise restoration from the last monthly might take too long to replay.  Of course this is only an example, you have to suit yourself.

  Do we need both approaches or one would suffice?

At some point in the distant future, it might be hard to find suitable hardware unto which to restore a base backup, (with virtual machines that may be less of a concern, but
it just pushes it down a level.  For how long will it be easy to find VM software that still supports 32-bit guests?). A logical backup could help in that case.  That is about the only reason I can think of to take routine "pg_dump" if you are also doing base backups and archiving.  I have wanted historical logical dumps occasionally, so I just did PITR onto a dummy server, then ran pg_dump against that dummy server.


How to select distinct? not select duplicated row I have 2 table

select DISTINCT (B.MAIN_ID), A.NAME,  A.NAME_ID, A.CREATED_DATE
 from FAMILY A, ADDRESS B where B.F_ID = A.N_ID

result:

MAIN_ID   |  Name     |  NAME_ID | CREATED_DATE
100       |  Erica    |  254     | 20190808-01:02:01
100       |  Erica    |  245     | 20190808-00:01:01
300       |  Eden     |  331     | 20190808-00:03:34

I want to be remove duplicated rows 'MAIN_ID' by created date, I need last created record. How can I do this? thanks

MAIN_ID   |  Name     |  NAME_ID | CREATED_DATE
100       |  Erica    |  254     | 20190808-01:02:01
300       |  Eden     |  331     | 20190808-00:03:34

You can use ROW_NUMBER() ranking function and a subquery (derived table or CTE):

WITH
  cte AS
  ( SELECT
      B.MAIN_ID, A.NAME, A.NAME_ID, A.CREATED_DATE,
      ROW_NUMBER() OVER (PARTITION BY B.MAIN_ID
                         ORDER BY A.CREATED_DATE DESC)
        AS RN
    FROM
      FAMILY A JOIN ADDRESS B ON B.F_ID = A.N_ID
  )
SELECT MAIN_ID, NAME, NAME_ID, CREATED_DATE
FROM cte
WHERE RN = 1 ;


pg_restore certain tables only I'm looking for a way to use pg_restore to restore a newly-created database from a dump file, but only certain tables from that file. (There are a lot of extra tables in the database that are slow to restore and that I don't care about.)

I first tried pg_restore with the -t/--table flag, but it didn't enable the extensions I need for those tables. Not a big deal; I can manually run psql -c "CREATE EXTENSION ..." before the pg_restore command. My bigger issue is that the -t command seems to skip other things related to the tables, like constraints and indexes.

Indexes are annoying but I could run pg_restore --list and use awk/grep/etc. to get a list of indexes and pass them in to pg_restore with the -I/--index flag, so the process would be (I think):

pg_restore -t table1 -t table2 --schema-only ...
pg_restore -I index1 -I index2 ...
pg_restore -t table1 -t table2 --data-only ...

But (a) that still won't set up the constraints for me, and (b) it feels like I'm going down a rabbit hole with this roundabout solution for something I thought would be straightforward.

I know you can tell pg_dump to only dump certain tables, but I was hoping to avoid that due to the long time it takes to make dumps of my database.

if you're using the "custom" format , you can use the -l (ell) and -L options to do this.

create your dump, using the "custom" format
run pg_restore -l /path/to/dump/file > table_of_contents.txt
edit table_of_contents.txt and just remove references to the tables you don't want anymore. (Make sure you get all of them.  You can comment out a line with a semicolon.)
restore your database with pg_restore ... -L table_of_contents.txt /path/to/dump/file

Profit!


How can I move a MySQL database from 5.1 to 5.6? I have pretty big database, like with 5 000 000 rows in total. It is located in a remote server. I want to copy this database to my personal computer, for testing purposes. However I don't know the best option for this operation. Dumping the database does not look user friendly. Can I use Navicat's data transfer function to do this? Could it end with error? I think this operation can last few hours. Is there any other ways?

It's all here. What's wrong with

mysqldump options | mysql localhost_name -u me -pmy_password

(with valid options for your particular setup)? Check here for a sample. I don't know for sure, but Navicat is, in all likelihood doing something similar in the background. This is possibly better.


group by like string I have a table employee in this table i have id company_name employee_id

1 abc  2015_acb_1
2 abc  2015_acb_2
3 abc  2015_acb_3
4 abc  2015_acb_4
5 xyz  2016_xyz_9
5 xyz  2017_xyz_2
5 xyz  2016_xyz_3
5 xyz  2019_xyz_5

I want to select unique string such as from employee table

_abc_
_xyz_

i have try select employee_id where employee_id like %_%; its not giving as i expected

SELECT DISTINCT SUBSTRING(employee_id FROM 5 FOR 5)
FROM employee;

  some of record contains 3 to 4 _ symbol and position is different

SELECT DISTINCT CONCAT('_',
                       SUBSTRING_INDEX(SUBSTRING_INDEX(employee_id, '_', 2), '_', -1),
                       '_')
FROM employee;


Introduce delay between master slave setup in MariaDB We're using a master-slave solution using MariaDB 5.5.44. Is there a way to manually introduce an artificial delay between the master and slave? I'm thinking of something like 1 minute or so. Is there a mysqld option that does this?

I see that MySQL 5.6 has the MASTER_DELAY parameter for the CHANGE MASTER TO command, but I suppose it doesn't exist in 5.5 (or MariaDB 5.5). Is there another way to cause a delay?

The MASTER_DELAY option is currently scheduled to be in MariaDB 10.2.  There is an issue for the feature.

Until that is added to MariaDB, your best option is pt-slave-delay from Percona.  I've used it before and it is pretty easy to get going but since it isn't part of the mysql server, it's not as reliable.


Create TRIGGER to insert after the insertion of specific value I have the following

TABLE #1 id time sender receiver message status aux_id

TABLE #2 time_to_send sender receiver message aux_id

The TABLE #2 has a process that sends the data to an api which returns a Boolean.

So if the process success

INSERT INTO `TABLE #1` (time,sender,receiver,message,status,aux_id)
VALUES (CURRENT_TIMESTAMP(),'sender','receiver','message','OK','1')

If doesn't

INSERT INTO `TABLE #1` (time,sender,receiver,message,status,aux_id)
VALUES (CURRENT_TIMESTAMP(),'sender','receiver','message','ERROR','1')

I want to create a TRIGGER to REINSERT the data to TABLE #2 IF the process insert into the status field ERROR value.

I'm trying this without success

mysql> DELIMITER ||
mysql> USE db
mysql> CREATE TRIGGER resend
-> ON TABLE #1
-> AFTER INSERT
-> AS
-> IF EXISTS (SELECT * FROM TABLE #1 WHERE status LIKE 'ERROR' AND aux_id =! 2)
-> BEGIN
-> INSERTO INTO TABLE #2 (time_to_send,sender,receiver,message,aux_id)
-> VALUES (DATE_ADD(NOW(),INTERVAL 10 MIN),NEW.sender,NEW.receiver,NEW.message,'2')
-> END;||

Any ideas?

I think you have the trigger syntax wrong. Try something like (I don't fully understand the scenario so you will have to adjust the logic in the trigger):

delimiter @
create trigger resend
after insert on t1
for each row
    IF NEW.status = 'ERROR' THEN
        INSERT INTO t2 (time,sender,receiver,status,aux_id)
        VALUES (NEW.time,NEW.sender,NEW.receiver, NEW.status, NEW.aux_id);
    END IF @

delimiter ;


How to group many information une one select There are my database

articles
id | art_code | art_name
1  | 5678     | computer
2  | 0987     | display
3  | 8547     | keyboard

action
id | code        | action      | qte
1  | 5678        | IN          | 4
2  | 0987        | OUT         | 3
3  | 5678        | IN          | 6
4  | 5678        | OUT         | 2

I would like to SELECT all value of article with SUM() of IN / OUT group by article

ex:

RESULT:

    id | art_code | art_name  | IN  |  OUT
    1  | 5678     | computer  | 10  |   2
    2  | 0987     | display   | 3   |   NULL
    3  | 8547     | keyboard  | NULL|   NULL

I try but, I've a result with :

RESULT:

    id | art_code | art_name  | IN  |  OUT
    1  | 5678     | computer  | 10  |   NULL
    1  | 5678     | computer  | NULL|   2
    2  | 0987     | display   | 3   |   NULL

I can't have SUM(IN) and SUM(OUT) in 1 line by article.art_code.

An idea?

What you are trying to do is called pivoting. You essentially want the results grouped by code and action but re-arranged so that instead of one row per code and action you get one row per code only, all the distinct action values (well, just two in your case) forming separate columns.

In other words, instead of

action   code  value
-------  ----  ------
action1  IN    value1
action1  OUT   value2
action2  IN    value3
...      ...   ...

you are trying to get

action   IN      OUT
-------  ------  ------
action1  value1  value2
action2  value3  ...
...      ...     ...

Some SQL products support a dedicated PIVOT syntax. MySQL is not one of them, but you can use other ways.

One common way is conditional aggregation. You will group the rows by code only, because you want one row per code in the output. The aggregation by action will be achieved by introducing a conditional as the aggregate function's argument. This is how you code that in SQL:

SELECT
  art.id,
  art.art_code,
  art.art_name,
  SUM(CASE act.action WHEN 'IN'  THEN act.qte END) AS `IN`,
  SUM(CASE act.action WHEN 'OUT' THEN act.qte END) AS `OUT`
FROM
  article AS art
  LEFT JOIN action AS act ON art.art_code = act.code
GROUP BY
  art.id,
  art.art_code,
  art.art_name
ORDER BY
  art.id ASC
;

Each SUM expression aggregates qte just for one of the action values, either 'IN' or 'OUT', using a CASE expression. As shown in the query, you need two SUM expressions, one per each value of action. If you had more, you would need to add more SUMs accordingly.

However, as there only two values here, it may make sense to try alternative methods. One such alternative is in Akina's answer. Here is another option:

SELECT
  art.id,
  art.art_code,
  art.art_name,
  act_in.`IN`,
  act_out.`OUT`
FROM
  article AS art

  LEFT JOIN
  (
    SELECT code, SUM(qte) AS `IN`  FROM action WHERE action = 'IN'  GROUP BY code
  ) AS act_in
  ON art.art_code = act_in.code

  LEFT JOIN
  (
    SELECT code, SUM(qte) AS `OUT` FROM action WHERE action = 'OUT' GROUP BY code
  ) AS act_out
  ON art.art_code = act_out.code

ORDER BY
  art.id ASC
;

As you can see, getting each of the two columns (IN and OUT) requires one outer join to a derived table containing the aggregated results for the corresponding action value. It is easy to see how this approach would not be a good idea if you needed to produce ten such columns. Not only would the query itself become cumbersome much more quickly than the conditional aggregation method, but the performance would deteriorate very soon too. However, for just two columns this option might be worth a shot.

Both suggestions presented in this answer can be found and played with at dbfiddle.uk (the setup was borrowed from Akina's fiddle):


Will too many tables slow Mysql down? I'm making a database which should be able to handle over 1,000,000,000 rows.

in order to optimize it's performance i've decided to split tables.

so instead of having 1,000,000,000 entries in one table for over 10 years i would split it to 3650 tables and one table for each day.

there would be no more than join or union of 2 queries now i want to know wether having too many tables degrade performance or not and if so How much ?

Overall, splitting tables adds overhead for a perceived benefit that isn't there. Tables of a few billion rows do need to be carefully designed as far as index and normalization, however splitting adds computation /software overheads and may generate far worse queries.

To directly answer the question:

Too many tables on disk will have no impact on MySQL (unless you have a filesystem that can't rapidly open a filename from a very full directory (mitigated in 8.0 (frm data in tablespace) and innodb_file_per_table=0).

As far as active tables, those being used frequently in queries there are effects from:

table_open_cache limits the number of tables cached and so does innodb_open_files.

Exceeding these cache limits will cause tables to close and open and need to be re-examined adding overhead to queries.


Why public synonyms become invalid I have 2 packages and 2 public synonyms for those packages. There are dependencies between those packages. If I make changes to any of those packages and compile them then packages are compiled (specs and bodies) and valid but both synonyms are invalid. I know I can recompile synonyms but how to avoid this please? Is there a way to keep all objects compiled?
Thank you.

If you are modifying any object in Oracle where a synonym is linked, there is no alternative, you should recreate the synonyms.

I found that because in my application I have sometime the need to recreate custom views (e.g. for attribute length) , and each time i need to recreate synonyms.

Even adding a rows in a table lead the synonym to be invalid.


SQL server permissions during installation I'm trying to find out when a SQL server installation grants permissions on the OS.

I know that the following permissions are giving during setup msdn article about permissions.

However what I don't know is how SQL server will react if the permissions are there, but granted through a group. My problem is that the OS group policies do not allow for several of the required permissions to be granted. And we are going to add the users to a group, that is then included in the group policy.

Will SQL server correctly detect that the required permissions are present, and skip adding the users individually? Or will the installation end on error because it can not grant the required privileges?

We're planning to test this soon, however due to time constraints I was hoping some-one here has experience with this issue, and can give me an answer.

Update: I'm quickly adding some information in the hopes that someone can explain the observed behavior to me...

We are able to install SQL server, despite group policies preventing nearly all the permissions mentioned in the before linked MSDN article. However if we then try and run an SSIS package, or certain server wide jobs, we receive the error that SQL Server lacks the required privileges.

Why was SQL Server able to install, without error even, despite not being able to add the sql server agent and service users to the User Right policies it needs according to the MSDN documentation?

Currently SQL Server is running, despite the user not having local permissions, and not having the Log on as a service permissions... Am I wrong in my understanding of why these permissions are required?

I will say up front that I do not know why your company would have policies that lock down the OS so much that a Microsoft product cannot be installed and provided the appropriate permissions during an installation. I would think they might be doing that backwards. I supported the military as a contractor for many years and that included secured networks, we did not lock down the OS until after all software was installed on the server. We at most wanted to ensure the software installed itself appropriately without any issue.

Outside of that, it is going to depend on what permissions you are not allowing to be set. If it is being restricted on what OS level permissions are set within the local group policy that are going to prevent the services from starting the installation will likely fail.

Shorting SQL Server services the documented permissions required to operate might affect your supportability with Microsoft. Just something to keep in mind.

Edit/Update

There is no in-depth doc that is going to go through what all the installation process does on a server. You can weed through the installation log under the default directory here: C:\program files\Microsoft SQL Server\110\Setup Bootstrap\Log. The 110 would be based on which version of SQL Server you are working with: 100 = SQL Server 2008/R2; 110 = SQL Server 2012; 120 = SQL Serve 2014.

I don't know anyone has gone through and determined the outcome of each little affect of an OS level permission not existing. All that is needed to understand is that permissions assigned according to documentation show what is required for a supported installation of SQL Server. You are basically going to experience sporadic issues based on what you need to do. Every permissions has a purpose, you just may not have reached or hit that purpose as of yet.


Change language used to parse decimal number When my application - which I cannot change - tries to execute the following ad hoc statement:

Insert into Payment(idProduct,PaymentTypeId,total,paymentdate,cost)
  VALUES ('48838','2','550',GETDATE(), '348,4')

I get this error:

  Error converting data type varchar to numeric.

This is because the cost column is a decimal, and the application is sending commas instead of decimals.

What configuration do I need to change? I tried changing default language to Spanish (advanced properties for the server) and the login to Spanish too, but it is not working. The language property in the server is English and I can't figure out how to change it.

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost varchar(32);
ALTER TABLE dbo.Payment ADD dcost
  AS (CONVERT(decimal(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num varchar(32) = '3,6';
SELECT CONVERT(decimal(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.


Create global temp table on one DB and use it in a query on another DB - is it possible? My issue is as follows:

I'm trying to create a global temp table using data from DB1, then use that temp table in a query executed in DB2 and then have the results imported into a new table on DB2. Since the query is a bit more complicated a simple Merge join won't do as I need to transform the data and add an extra column (dense_rank) based on data from both Databases.
According to one of my colleagues this should all be possible to do in SSIS.

Simplified Query that I would like to execute on connection from DB2:

Select db2.a,db2.b,db2.c,db1.c, db1.d,
DENSE_RANK() OVER (PARTITION BY db2.a order by db1.c asc) as FinalRank
from DB2.table  as db2
left join ##globaltemp as db1 on db2.a=db1.a

After extensive googling and different tries, the DB2 source connection won't find my temp table (##globaltemp) which has been created in DB1.

Is such an operation even possible ?

tldr: Is it possible to create a global temp table in SSIS session and pull data from it no matter the Database?

Even a local temporary table (#Table) would work as long as you stayed in a session context. A global temporary table (##Table) is only global in that it can be seen by other sessions.

What is probably happening here is your create ##Table session is going out of scope/context and when the session goes away, there goes it's temp table. Whether local or global. So the process that created the temporary table is out of scope and it's temp table is gone.

In other words - the problem isn't the scope of the database you are connected to. any temporary table can be access regardless of the database you are using. A temp table isn't created in any of your user databases (DB1 or DB2), they are always created in TempDB.

Try using a derived table or consider a staging table, etc.


Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)? I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get aggregated through a process of copy-paste and buggy macros.

My intent is to create a simple python script which aggregates all of the data for an experiment into an SQLite database and then produces the requisite CSV/XLSX output.

My issue is that for a single trial of our experiment, we end up with about 100 variables recorded at about 10 different time points. My initial impulse was to create a value and variable table:

CREATE TABLE value (val_id INTEGER PRIMARY KEY,
                    value TEXT,
                    var_id INTEGER,
                    event_id INTEGER,
                    exp_id INTEGER,
                    FOREIGN KEY (var_id) REFERENCES variable(var_id),
                    FOREIGN KEY (event_id) REFERENCES event(event_id),
                    FOREIGN KEY (exp_id) REFERENCES experiemnt(exp_id)
);

CREATE TABLE variable (var_id INTEGER PRIMARY KEY,
                      var_name TEXT,
                       var_type TEXT
);

value:
val_id | value | var_id | ...
0      | 10    | 0
1      | "ROSC"| 5

variable:
var_id | var_name | var_type
0      | Pressure | DECIMAL
...
5      | Outcome  | TEXT

But this feels wrong and I have a hunch at the "proper" way to do this is to have a single data table with the hundreds of columns that would otherwise be described in the variable table since this would make it easier to do type checking (yes, I know SQLite doesn't do this, but in principle).

Any insight into how to tackle this would be hugely appreciated.

What you are describing is the EAV (Entity Attribute Value) model which most database professionals would run a mile from. It's also sarcastically called the OTLT (One True Lookup Table) and is a classic novice mistake. Your hunch is correct!

Here (and here) is the opinion of Joe Celko (a veteran SQL programmer who is/was a member of the SQL standards committee.  The fact that he titles his article with the phrase "EAV of destruction" should give you a clue :-).  Celko also calls this the Massively Unified Code Key.

It's no coincidence that the acronym is MUCK! :-)

Storing data in this way destroys many of the advantages of relational databases such as DRI (Declarative Referential Integrity), CHECK constraints and DEFAULT values.

Please create a table with 100 fields and 10 rows - if that's what your data requires, then do it. Perhaps a couple of other fields with experiment ID, datetime and experimenter_id would also be useful? That way you can perform analyses over given periods, various other aggregates - basically slice and dice all of your data.

< Personal opinion > If you haven't chosen your database yet and are happy to use F/LOSS, then may I recommend PostgreSQL - it's SQL dialect is by far the richest of the Open Source databases (I have no connection to the project). Check here for how to enforce datatypes in SQLite, but PostgreSQL offers more than SQLite - it's multi-user for example and no need to jump through hoops to enforce datatypes.< / Personal opinion >

[EDIT1]

Just one further remark, and for completeness, there is only one significant system out there that makes use of the EAV model - and that's Magento (1, 2). Its principal niche is the fashion industry where the EAV model may be suitable for sparse tables (fashion items tend to be available in a myriad of colours, syles, sizes...). It is popular (1, 2), but then so is MySQL which is inferior in many ways to PostgreSQL, Firebird and (multi-user capability aside) SQLite.


Why does the order of parameters matter for sp_trace_create? The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options:

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @options = 0,
  @tracefile = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @tracefile = N'C:\temp\TestTrace',
  @options = 0;

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

The first batch creates a new trace, selects its id, and then closes the trace. One result set is returned:

@new_trace_id
2

The second batch fails with an error:

  Msg 214, Level 16, State 3, Procedure sp_trace_create, Line 1
  Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.

Why does parameter order affect the output of stored procedure sp_trace_create? And why does it fail with such a misleading error message?

I believe this is because it is an extended stored procedure and the parameter names are actually entirely ignored. It just goes off position.

I have renamed them as below (and given them all the same name) and it still works fine.

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @rubbish = @new_trace_id OUTPUT,
  @rubbish = 0,
  @rubbish = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;

A similar documentation bug was filed by Aaron about sp_executesql.

Another annoying aspect of that stored procedure is that the @maxfilesize must be passed as 'bigint' and it doesn't accept a literal integer. I assume that this is also because it is an extended stored procedure.


SSRS minus first two dates as the header I have a SSRS report that has one column called startime that is listed in mm:dd:yyyy hh:mm:ss format.  For the header of this column I want to minus the first date from the second date to give me an idea of how long is between the two dates, this will likely be in minutes.

I don't need a running total, I just need to be able to minus the first date from the second date in the column will be good enough.  I have bee playing around with the following but its throwing up some strange results.

=Fields!StartTime.Value - Previous(Fields!StartTime.Value)

Does anyone have any ideas?

Thanks

Can you do the calculations in the dataset the report uses? May have an easier time doing that then building the expression needed to calculate the column for SSRS


SQL Server clustered index high fragmentation I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.

Inserts are working perfectly fine. After inserting 2000 rows the fragmentation is at around 4%.

However, each records will be updated at least 3 times afterwards. This generates a fragmentation of this clustered index of more than 99% (with default Fill factor)..

Or with other fill factors I tested:
- Setting a Fill factor of 80 on the clustered index (Fragmentation > 98%)
- Setting a Fill factor of 50 on the clustered index (Fragmentation > 94%)

So this doesn't look like it would help a lot...

I tested each fill factor setting on a newly created table with 2000 inserts (and the 3+ updates)

there is no select or update which does not include the primary key in the query.

Has anybody an idea why this index has such a high fragmentation?

Updates are made through ADO.NET this is the generated command (traced by the SQL Profiler):

exec sp_executesql N'UPDATE MyTable SET MyValue = @MyValue WHERE MyId = @MyId',
N'@MyValue varchar(50),@MyId int',@MyValue='This is some random value',@MyId=1234

Other updates follow the same pattern (update other values, but alway providing the @MyId parameter)

Thank you for your help :)

FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%.

If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't enough space, this will cause a page split, which is the process that creates the necessary space.

What's a bit misleading about page splits is that there are "good splits" and "bad splits," even though the performance counter counts all of them.

A good split is when new rows are added to the end of the index, like what happens when you do your initial batch of INSERTs. The new row doesn't fit on the last page, so the storage engine must allocate a new page and logically hook it up to the last index page. The new page will probably exist physically after the old-last index page.
A bad split is when a page must be inserted in the middle of an index: the new page is attached to the index structure logically on both sides, but may (likely) not exist in contiguous physical order to those pages.

Fragmentation is the discrepancy between the logical and physical order, and for the most part, only the bad type of page splits cause fragmentation.

Because you're inflating the size of already-existing rows, this causes the bad type of page split, which is why you're seeing high fragmentation numbers.

It's unclear what your exact process is, and how many rows are ultimately going to end up in this table. If it's something like a one-time population, do the full population process, then turn around and rebuild the clustered index with 100% FILLFACTOR.

If this process happens continuously, you could "pre-allocate" the space by doing the following: add a dummy variable-width column to the table, populate it to the max length on INSERT, and then on the first UPDATE, set the dummy value to NULL while updating the real value. This method will probably add overhead to the logging mechanism because of all the data values flying around.

In general, though, you only need to concern yourself with fragmentation when all of these factors are true:

The index is large enough that randomly reading the pages (extents) is too slow (choose your own definition of what "too slow" means)
The index will be scanned
The index pages (extents) will be physically read from disk (i.e., they are not already in the buffer pool)
The storage subsystem is poor at handling random reads (i.e., spindle-based, and the bytes won't come out of cache)

I would recommend, however, putting in place a solid index maintenance solution to keep things more or less in check. You can search this site for recommendations with regards to that.


Which tables to create according to relations We are planning a new system with a new DB (MS SQL Server).

We have the following entities and the following relations:

Entities:
Clubs,
Chains,
PaymentTypes,
etc...

Relations:
Clubs-Chains (M-M);
(Clubs_Chains)-Commission (1-1);
Clubs-Chains-PaymentTypes (M-M);

Commission is a numeric field containing perecentage value , and is not an entity.

We would like the DB to be normalized.
We thought about the following option, but we are not sure if it is the best solution for the scenario:

Clubs_Chains table:
ClubChainID PK int not null,
ClubID FK int not null,
ChainID FK int not null,
Commission int

Clubs_Chains_PayMethod table:
ClubChainID PK int not null,
PaymentTypeID PK int not null

Thanks!

It isn't necessary to have a surrogate primary key on your clubs_chains table.

The combination of the two foreign keys in clubs_chains is adequate for the primary key.

You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key.  This might be helpful since it would allow more direct joins between clubs_chains_paymethod and your clubs and chains tables, without sacrificing any referential integrity.

On the other hand, some people just love using a surrogate primary key, even on intersection tables.  If that is your data modelling style, then it's OK too.


alter primary id field to uniqueidentifier GUID as default in sql server I have tables with id(primary key - int) with identity specification (1,1) .
I want to change this column to uniqueidentifier with default ( newid() ) .

What I've tried is

ALTER TABLE myTable ALTER COLUMN id uniqueidentifier default NEWID();

but I got this message

Incorrect syntax near the keyword 'default'.

Additionally ,every tables of my database has id primary key column and I want to change them to uniqueidentifier with default newID() , by looping or something .

every tables of my database has id primary key column and I want to change them to nvarchar(36) with default newID()

No. You do not want to make that change as no good can come of it.
You currently have a PK (Clustered, I assume) that is:

Compact: 4 bytes per row
Efficient: comparisons are simple 4-byte-value-to-4-byte-value (i.e. a binary comparison)
Minimally Fragmenting: new values are sequential and added to the end of the table.

You are wanting to change this to a Clustered PK that is:

Wide: 72 bytes per row (GUID is 16 bytes, which is 32 characters in string form for the hex values plus 4 dashes for 36 characters total, which is 72 bytes in UTF-16 / NVARCHAR).
Inefficient: If using UNIQUEIDENTIFIER it is not so bad as that is still a binary comparisons, just like with INT, but is 16 bytes unlike INT which is 4. Storing this as a string is now a 36 character comparison (36 bytes in VARCHAR and 72 bytes in NVARCHAR) which is slower than the 16 byte UNIQUEIDENTIFIER. Finally, most people storing GUIDs as strings forget to use a binary Collation (e.g. Latin1_General_100_BIN2) to at least make the comparisons byte-by-byte since linguistic rules aren't necessary. Using a case-insensitive Collation, or even a case-sensitive one, is definitely slower as it will apply locale-based linguistic rules.
Highly Fragmenting: new values are all over the place, leading to greatly increased page splits. And if you decrease FILLFACTOR to reduce the number of page splits, you are also reducing the performance of the index as it is spread out over a larger amount of pages.

Please keep in mind the down-stream negative effects of this change due to:

Clustered index keys are copied into non-clustered indexes. Assuming, again, that this PK is Clustered, then each non-clustered index on this table will have that 72 byte value copied into it. Three non-clustered indexes on this table is 72 bytes * 3 = 216 bytes plus the original 72 of the Clustered index = 288 bytes total. On the other hand, the current INT would only be 4 bytes * 3 = 12 bytes plus the original 4 = 16 bytes total. Per row.

PKs are often used by FKs, which is the PK copied into one or more other tables. 72 bytes per row is just for this table. If this PK is used in 2 other tables as a Foreign Key, then that is 72 bytes * 2 = 144 extra bytes. On the other hand the current INT would only be 4 bytes * 2 = 8 bytes.
Is the FK column indexed? If yes, then that is another 72 bytes instead of 4.

Data pages are loaded into memory (i.e. the Buffer Pool) before their row(s) can be read and used. Larger rows and/or lower FILLFACTOR means more data pages are required to hold those rows. That means more time to read them from disk into memory, and obviously the more memory they will require. This need competes with other queries, plan cache, etc.

If you need to have a GUID to have a value that is know to external systems, just add a UNIQUEIDENTIFIER column and index it; you can then look it up to get the INT value to use for all other JOINs, etc. And, if it has to be stored as a string, then use VARCHAR given the only characters are A, B, C, D, E, F, and - (NVARCHAR is a completely unnecessary waste of space) and be sure to specify a binary Collation (one ending in _BIN2).
But don't change the current structure.

Regarding the update to the question in which it was clarified that the intended datatype to change to is really UNIQUEIDENTIFIER and not NVARCHAR(36): the overall advice does not change from "do not do it". While UNIQUEIDENTIFIER is a better choice than NVARCHAR(36) (smaller and comparisons are binary), it is really just a "less bad" choice more than a "better / good choice".


Use federated table to sync only selected columns Scenario: I need to sync a table in a remote server with the table in my production server. The problem being that the table structures are not same.

I have looked into the concepts of federated table only to find out that the two tables are exactly same, structure wise, which is not the case here.

So basically, is there any way I can sync only few columns,the matching columns,of those tables?

The data in federated tables are not stored locally. These tables just provide a way to access the data on the remote server through a local table. So if you want to truly sync with a table on a remote server, then you need to actually copy the data across.

Option 1 - copy data using federated table and event scheduler

To do this, create the federated table with identical column definitions, and then e.g. enable the event scheduler and create an EVENT to sync from there to your local, non-identical table. The details of how you sync the two tables will depend on how the table is being modified on the remote server. (E.g. is it a table that is only ever INSERTed to, not UPDATEd? That will make your job easier. Or if it's UPDATEd, is there at least a timestamp column which will tell you when each row was last updated? In the worst case you'll have to delete all the local rows and re-populate the local table every time the EVENT runs. The EVENT code you write could involve a SELECT into a CURSOR and then a loop where you fetch from the cursor and INSERT/UPDATE to your local table.)

Option 2 - don't copy data, use federated table together with a view

Another option, if you don't mind that the data stays in the remote table, may be to create a local table with any extra columns you need that are not found in the remote table, and then create a VIEW that joins the two tables together. You can then name the view columns whatever you like. Then use this VIEW when you need to SELECT from the remote table. The disadvantage with this is that SELECTs against this view will be slower than using a purely local table, and also this isn't as robust because you depend on the remote database and the connection to it being available at all times.

Option 3 - use replicated table together with a view

A third option could be to use master-slave replication. Make your local database a slave and the remote a master. Then only replicate the particular table. Then create a VIEW similar to option 2.


Percona xtradb cluster node2 won't start I have configured Percona Xtradb cluster 5.6. The first node starts fine when bootstrapped, but the second node seems to have an issue with this variable setting:

wsrep_provider=/usr/lib/libgalera_smm.so

I am running the cluster nodes on Ubuntu 14.04. For the error log, this is what is throws at me:

150608 18:43:03 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql

150608 18:43:03 mysqld_safe Skipping wsrep-recover for empty datadir:
/var/lib/mysql
150608 18:43:03 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1
to wsrep_start_position
2015-06-08 18:43:03 0 [Warning] Using unique option prefix key_buffer instead
of key_buffer_size is deprecated and will be removed in a future release.
Please use the full name instead.
2015-06-08 18:43:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2015-06-08 18:43:03 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56-log)
starting as process 5977 ...
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library
'/usr/lib/galera3/libgalera_ssm.so'
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen():
/usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such
file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP:
wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22).
Reverting to no provider.
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library
'none'
2015-06-08 18:43:03 5977 [ERROR] Aborting

2015-06-08 18:43:03 5977 [Note] WSREP: Service disconnected.
2015-06-08 18:43:04 5977 [Note] WSREP: Some threads may fail to exit.
2015-06-08 18:43:04 5977 [Note] Binlog end
2015-06-08 18:43:04 5977 [Note] /usr/sbin/mysqld: Shutdown complete

I have tried commenting out #wsrep_provider and starting the node with the following command

$sudo /etc/init.d/mysql start --wsrep_provider="/usr/lib/libgalera_ssm.so"

But this does not seem to help. The node still fails to start.

The libgalera_ssm.so file is actually present on both servers, hence I am kinda confused why it works on one server and not the other. But it is worth pointing out that even node one gives the same error when I try to start it without using bootstrap-pxc and just use /etc/init.d/mysql start.

The log entries

2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22). Reverting to no provider.

show that MySQL cannot find the file /usr/lib/galera3/libgalera_ssm.so so it cannot use it as a WSREP provider.  Check that this file exists and is accessible.  If your system is 64-bit, it might be in /usr/lib64/.

Also please post the error message you get when starting the node -- without reading the exact error message it's a shot in the dark.


How to return rows between two datetimes stored as date and time? How to view the result between 2 dates and time ?

My current Code:

SELECT *
FROM [DB].[dbo].[TABLE]
WHERE [USER_ID] = '005'
AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016'
AND [TIME] >= '21:00:00'                   -- for date 03-04-2016
AND [TIME] <= '05:00:00'                   -- for date 04-04-2016

ORDER BY [DATE] ASC, [TIME] ASC

This code does not work and I do not know how to fix it

why doesn't this request work ?

It is impossible for any row to satisfy both [TIME] >= '21:00:00' and     [TIME] <= '05:00:00'

SQL server doesn't read your source code comments to know that the time should be applied conditionally.

You need to change the code in order to apply these predicates (which will be residual predicates assuming a seek on date) conditionally on the boundary dates so the time element is only relevant on the days these conditions apply to.

SELECT *
FROM [DB].[dbo].[TABLE]
WHERE [USER_ID] = '005'
AND [DATE] BETWEEN '20160403' AND '20160404'
AND ([TIME] >= '21:00:00' OR [DATE] > '20160403')
AND ([TIME] <= '05:00:00' OR [DATE] < '20160404') ;

or the equivalent (perhaps more self-documenting though you would need to check the execution plans for efficiency):

SELECT *
FROM [DB].[dbo].[TABLE]
WHERE [USER_ID] = '005'
AND ( [DATE] = '20160403' AND [TIME] >= '21:00:00'
  -- OR [DATE] > '20160403' AND [DATE] < '20160404'
   OR [DATE] = '20160404' AND [TIME] <= '05:00:00'
    ) ;


PostgreSQL to HDFS to Spark, or PostgreSQL directly to Spark SQL? If the end goal is to perform computations with Spark, what are the reasons one would first transfer Postgres data to an HDFS (with Sqoop) vs. just using Spark SQL with Postgres directly (with the JDBC?)

The answer to this question (which is referencing MongoDB, not PostgreSQL...but still applicable) mentions that these are the two options, but I'm wondering what would motivate choosing one over the other.

This might be too broad of a question but as with any system, the less steps and dependencies you have, the easier it is to support it as long as it meets current and future (or future enough) business goals.

In your use case scenario it doesn't look like you have any use for HDFS and SQOOP.  In a lot of environments they might.  For example they might want to use HDFS as a file store and keep the records handy even after they were processed with Spark.  It could be that they are customer records for example, and are aggregated, this way you would keep the originals handy so you could give each individual customer their own records if they need it.

By going directly into Spark and based on your job, you will mutate the data and you might not be able to get the original back.  By keeping a copy in HDFS you could also always recreate the end result as you have a copy of what made it.

If you don't need the data to persists in the form it was when you pulled it, or don't need to be able to recreate the output by going through the same process you might not need to.  Another item might be that you want a live dev/qa environment.  By putting it in HDFS first, you can follow the same process for all environments using the most recent data.  You might not be able to recreate an issue or scenario if the data get's processed but not maintained/stored.

Those are some of the MANY possible reasons.  It really depends.


Query looks optimal but logical reads is huge I run the following (really simple) query in SQL Server:

    SELECT MAX(PK_Field1)
    FROM MainTable kh
    WHERE kh.PK_Field1 >= '2014-12-01T00:00:00'
    AND kh.PK_Field2 = 1572
    AND kh.PK_Field3= 'FD5BF2F3-8ED7-479C-A71F-D04E4288CBFC'

And I get these stats from it:

Table 'MainTable'. Scan count 9, logical reads 31078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 44 ms.

Although the elapsed time is not that much, this query is executed around a thousand times every ten minutes, so having more than 30k logical reads doesn't seem quite optimal to me.
However, the main index in this table is designed in a way that a query like that can take full advantage from it. The table MainTable contains this:

PK_Field1 datetime
PK_Field2 int
PK_Field3 uniqueidentifier
Another_Field datetime

This table contains no other columns, it has 3 million records and the only index is the clustered one on PK fields (in same order as defined in the table, ordered ASC). The actual execution plan shows the following:

Additionally, fields PK_Field2 and PK_Field3 are foreign key referencing two other tables. Referenced columns are IDs in their respective tables.

What do you think? Is there room for some optimization?

Sorry for the late response, I've been quite busy.
I tried reordering the columns in the index by adding a new non-clustered index with the following fields and order:

PK_Field3 uniqueidentifier
PK_Field1 datetime
PK_Field2 int

The results are great:

(1 filas afectadas)
Table 'MainTable'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 filas afectadas)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

The actual query plan shows no parallellism.
Now I have a tough work to decide whether to keep it or not, despite the good results, since I don't find quite optimal to keep a 100 MB index for a single query. If I find out it is useful for other usual queries I'll keep it.
Thanks for your suggestions!


How can I take a full backup from MySQL without any System tables? We are downgrading our version of MariaDB, and are looking to take a full backup of all of the databases.  I do not want to include system tables since they would be incompatible between the two versions.  What would be the best way to accomplish this?

After reading through http://mysqlserverteam.com/creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/, it seems like everyone is saying that you have to manually exclude them, however I don't want to manually have to type in all of the system databases.  What is the best way to accomplish this?

Currently I have the following options -

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction --gtid --master-data --dump-slave --add-drop-database"

This is the current method I have tried -

SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

echo $SQL

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

Dblistfile includes all of my database names that I want to backup.  Is there a better way to do this?

Four and a half years ago in my first post to Any option for mysqldump to ignore databases for backup?, I collected the database names to memory rather than to disk.

Here is the code from my first answer

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

This could be adjusted to collect the names in SQL rather than in the shell

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYDBLIST1=`mysql -ANe"${SQLSTMT}"`
MYDBLIST2=`echo "${MYDBLIST1}" | sed 's/,/ /g'`
MYSQLDUMP_DATABASES="--databases ${MYDBLIST2}"
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

A couple of months ago, I briefly mentioned using mysqlpump (See my second post to Any option for mysqldump to ignore databases for backup?) which now has --exclude-databases.


Best practice for copying tables from server to server Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB)

copyTables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[copyTables]
AS
-- NOCOUNT ON added to prevent extra result sets from interfering
-- with SELECT statements. XACT_ABORT ON to terminate the transaction
-- and rollback if a run-time error occurs.
SET NOCOUNT, XACT_ABORT ON

BEGIN
    DECLARE @serverName varchar(50), @dbName varchar(50), @schemaName varchar(50)

    SET @serverName = QUOTENAME('serverB')
    SET @dbName = QUOTENAME('db')
    SET @schemaName = QUOTENAME('dbo')

    BEGIN TRY
        BEGIN TRANSACTION

        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl1', 'copyLog'
        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl2', 'copyLog'
        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl3', 'copyLog'
        ...

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Insert error into log table

        ROLLBACK
    END CATCH
END
GO

copyTable

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[copyTable]
    @serverName varchar(50),
    @dbName varchar(50),
    @schemaName varchar(50),
    @tableName varchar(50),
    @logTableName varchar(50)
AS
-- NOCOUNT ON added to prevent extra result sets from interfering
-- with SELECT statements. XACT_ABORT ON to terminate the transaction
-- and rollback if a run-time error occurs.
SET NOCOUNT, XACT_ABORT ON

BEGIN
    DECLARE @localFullName varchar(200), @localShortName varchar(150),
        @foreignFullName varchar(200), @logShortName varchar(150);

    SET @localFullName = QUOTENAME(@dbName) + '.' + QUOTENAME(@schemaName)
        + '.' + QUOTENAME(@tableName);
    SET @localShortName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @foreignFullName = QUOTENAME(@serverName) + '.' + QUOTENAME(@dbName)
        + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @logShortName = QUOTENAME(@logTableName) + '.' + QUOTENAME(@schemaName)
        + '.' + QUOTENAME(@tableName);

    IF EXISTS
    (
        SELECT *
        FROM sys.objects
        WHERE object_id = OBJECT_ID(@localShortName)
                AND type in (N'U')
    )
    BEGIN
        DROP TABLE @localShortName;
        SELECT *
            INTO @localFullName
            FROM @foreignFullName;

        INSERT INTO @logShortName (stamp, [message])
        VALUES
            (
                    GETDATE(),
                    'Table ' + @foreignFullName + ' was copied'
            );
    END
END

This looks like a good working solution. Keep in mind however, that copying large amounts of data within a transaction will potentially grow your logfiles significantly. That costs a lot of time and can cause unexpected drive space shortages. Also, during that time the target tables are completely unaccessible for outside connection. The source tabes might also be blocked, depending on you transaction isolation level.

It might be better to break the actual data movement down into small batches. But then you would loose transactional consistency if concurrent writes happen.

As Aaron mentioned, you might also want to look into existing tools like DataCompare (would also not guarantee transactional consistency). If you have to execute this copy process regularly, you could also look into snapshot replication. (This would guarantee transactional consistency, but it would need to be setup by a sysadmin. However, after that initial setup you could start the synchronization yourself.)

Finally, if you go with your solution, make sure that in the error condition you first rollback the transaction and then record to the error log table, otherwise you would rollback that new entry too. You should also check that there is actually a transaction at the time of the error: IF XACT_STATE() != 0 ROLLBACK


MongoDb: sharded replica set: data distribution How does the data get distributed among the shards?
How to backup and restore an infrastructure with 2 shards (3 config servers and 1 mongos)
When should I choose MongoDb Shard with Replica Set vs MongoDb Replica set only?
I have 2 shards. I use mongodump and mongorestore for backup and restore, is it a good practice?
Sometimes, the 3 config server are out of sync. What does it mean? How can I quickly get rid of these issues?

Sometimes, the 3 config server are out of sync. What does it mean? How can I quickly get rid of these issues?

  Most convenient way would be to copy (cp or rsync) the configdb data directory content between hosts in cases of inconsistency

When should I choose MongoDb Shard with Replica Set vs MongoDb Replica set only?

  Replica ONLY: When you desire to make your mongo data highly available
  and add redundancy in your solution. The benefit can be to offload
  reporting requests to secondary nodes and/or make mongodb cluster
  sustain network partitions or disaster recovery

  Sharding ONLY: When you desire to scale your mongodb cluster
  horizontally to achieve load balancing and distribute traffic to
  multiple hosts you must choose sharding. For example, you can achieve
  partitioning benefits with sharding enabled on databases.

  Sharding with replica set: When you desire to enable all benefits
  stated above i.e. scale you cluster horizontally to handle large
  volumes of data for load balancing and also achieve HA within each
  shard you must choose this approach


Mysql database design with XML I intend to create a wiki webpage, but it has some few more limitations than regular wikis. The content is completely user-editable, but the structure of the page is determined previously. To make things simpler, let's say that they have a structure of a (title, author, date, chapter1, chapter2,...,chapter10).

So some of them may be empty (like last chapters). I would definitely like the ability to search into it and, if possible, I'd like it to be expandible (to add more fields). The page is edited online with my own editor, so I have full control of the $_POST fields, and each one is in a different variable. So I was thinking about this 2 possible designs:

The page is first formatted using XML elements (through PHP) and then inserted into a single text cell in the table. This allows to make backups easier, as well as it's flexible for expansion and does not waste many space resources. The table would also include a modified date and last editor.
The mysql table has many columns and each one has a different field. This is not flexible, nor resource-savvy, but it's much easier to operate with.

I was thinking about learning the PHP functions for reading and writing XML and then work with the first option. But I'm new in MYSQL databases (and db design), so I was wondering if this is a good practice before I get on it or there are other ways.

So, what method is considered to be a "good practice", 1st, 2nd or different one?

If the only thing you are storing in the database is the XML data, what is to stop you from storing the file directly on the filesystem and bypassing the overhead of querying the database for the single field?

If backups are your concern, there are methods to backup a directory as well.

Usage of MySQL (or other RDBMS) implies there is a relational aspect to the data you are storing. If there's no relationship, this is probably not the tool you are looking for.


What does HSJOIN mean in an Explain Plan? I have the following EXPLAIN PLAN results from a query on my DB2 database.

0 SELECT STATEMENT ( Estimated Costs =  5.928E+02 [timerons] )
       1 RETURN
           2 HSJOIN
               3 [O] FETCH LTBP
                   4 IXSCAN LTBP~M #key columns:  0
               5 [I] FETCH LTBK
                   6 IXSCAN LTBK~V #key columns:  0

What does the HSJOIN on line 2 mean?


How Restore backup as overwrite on a database in use in SQL Server? I Want to restore a backup with overwriting on a database in use
for this problem I want to create a query base on these steps :
1- Remove All Connections [MyDB]
2- Create a backup from [MyDB]
3- Restore a Backup as an Overwrite on [MyDB] from specifying a path
Thank you very much for your help

You can kill all connections this way:
USE [master];

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

Then you pass to single user mkode:
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

Then we create a backup:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
GO

Then we replace the database:
--Do Actual Restore
Restore Database [MyDatabase] From Disk = 'D:\Path\to\Backup.bak'
With Replace,
Move 'Data file logical name' To 'New physical path and name.mdf',
Move 'Log file logical name' To 'New physical path and name.ldf'';

And then we go back to multi user mode:
/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO


Gathering Read Routing information using SMO is inaccurate in SQL Server 2016 I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016.

With earlier versions the routing list was quite simple, the list would be processed in order presented, but with 2016 they introduced the round-robin algorithm allowing you to have multiple secondary replicas accept the read traffic.

While this data is accessible through T-SQL it does not appear as though SMO has been updated to reflect this, which means that you cannot accurately get, or set the configuration that way as it is still a simple string collection.

Is there some attribute that I am missing that would allow me to get accurate data for SQL Server 2016? (example SMO call for C# below)

It is just the round-robin info that does not seem to be accessible, it lists all of the replicas that are in the read-routing list, it just does not seem to differentiate the various groups of replicas that would exist.

Connect bug report. Connect is dead...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SmoTesting
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter the servername");
            string connectServer = Console.ReadLine();
            Console.WriteLine("Enter the AG name");
            string agName = Console.ReadLine();

            Server srv = new Server();

            try
            {
                srv = new Server(connectServer);
                srv.ConnectionContext.StatementTimeout = 60; //timeout after 60 seconds running the query

                foreach (AvailabilityGroup ag in srv.AvailabilityGroups)
                {
                    if (ag.Name == agName)
                    {
                        ag.PrimaryReplicaServerName.ToString());
                        foreach (AvailabilityReplica ar in ag.AvailabilityReplicas)
                        {
                            if (ar.Name.ToString() == "connectServer")
                            {
                                foreach (Object obj in ar.ReadonlyRoutingList)
                                {
                                    Console.WriteLine("  {0}", obj);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.InnerException.ToString());
            }
            finally
            {
                srv.ConnectionContext.Disconnect();
            }

            Console.WriteLine("press a key");
            Console.Read();
        }
    }
}

The issue was fixed in SSMS 17.3, however you'll need to load the "applocal" assembly and not the one from the GAC. In addition, in order to be "backwards compatible", the ReadOnlyRoutingList property will stay a string and act/behave just like it did before. The newly added LoadBalancedReadOnlyRoutingList (and a getter/setter with it) will have the correct representation you're looking for.

  Connect is dead...

Long live Connect!

Code Repro (cut up for readability) To See New Properties and Methods:

#Load the Applocal assembly from SSMS 17.4
[System.Reflection.Assembly]::LoadFrom('C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.SMO.dll')

#Create a new server instance hitting the listener named SQL2016Listen
$SI = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "SQL2016Listen"

#Get the list of Availability Replicas for an Availability Group called "SeedingTest"
$ARs = $SI.AvailabilityGroups | where {$_.Name -eq "SeedingTest"} | select AvailabilityReplicas

#Grab the first replica in the list of replicas and show all of the members that have "load" somewhere in the name
$ARs.AvailabilityReplicas[0] | get-member | where {$_.name -like "*load*"} | select name


PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4? What is the difference between the following two functions

gen_random_uuid() provided by pgcrypto extension
uuid_generate_v4() provided by uuid-ossp extension

Are they both the same behind the scenes? Any performance impacts on using one?

Are they the same?

No.

The Postgres documetation for uuid-ossp suggests using gen_random_uuid() If you only need randomly-generated (version 4) UUIDs,

The uuid-ossp extension also provides other types of UUID (such as mac-addresses based)

  The difference?

I looked at the source and discovered that

uuid_generate_v4() uses arc4random to determine the random part.
gen_random_uuid() uses fortuna instead.

Other than that they do the same job.


Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files Hi guys I have question about how two different Hard disk setups can possibly affect performance of my database.

I have two Options.

Option 1:

One Physical Harddisk and then Multiple Virtual hard drives for SQL Server's Database files (.mdf , .ndf),Log files, TempDB, Backups etc

Option 2:

One Physical Harddisk and then One huge Virtual hard drive for SQL Server's Database files (.mdf , .ndf),Log files, TempDB, Backups etc

To my knowledge, If these were all physical hard drives, having multiple drives for all these files (tempDB, Log file, backups, database files), would definitely benefit from  parallel processing.

But is there any drawbacks of having one massive Virtual Hard drive vs multiple Virtual Hard drives as in reality, when Sql Server is reading or writing , it will be all done from one Physical Hard disk?

Is one method preferred over other? Any suggestions or pointers in the right direction is much appreciated. Thanks

There is no appreciable difference in performance when the underlying hardware is the same, assuming we're talking about a single physical disk.

However, separate logical drives might help you maintain your sanity, and could result in lower physical fragmentation of the SQL-related volumes


SQL Error: ORA-14300: While partitioning and subpartitioning I have tried this earlier and it works:

I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE
Hence for the ID present in T1, fetch the DATE and STATE from T2
Create new table T3 which is partitioned form of T1 on DATE key, sub-partitioned on STATE key
Pull all records from T1 and insert every row in T3 with not just two additional columns DATE and STATE. But also in their respective partition/sub-partition

My data is stored like this..

I used following create statement to create my table T3
CREATE TABLE SLABELSVALUE
(   "DOC_ID" VARCHAR2(80 BYTE) NOT NULL ENABLE,
...
...
"DOC_DATETIME" DATE NOT NULL ENABLE, //additional in T3 compared to T1
"DOC_STATE" VARCHAR2(5 BYTE),  //additional in T3 compared to T1
CONSTRAINT...
PARTITION BY RANGE (DOC_DATETIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))  **Line added for auto partition
SUBPARTITION BY LIST (DOC_STATE)
 SUBPARTITION TEMPLATE(
 SUBPARTITION IN_PB VALUES('IN-PB') , //All states
 SUBPARTITION IN_RJ VALUES('IN-RJ') ,
 ...
)
(
PARTITION p0 VALUES LESS THAN (TO_DATE('01-MAY-2006','DD-MON-YYYY'))
//Ealier when it worked I defined all partitions for testing,
//When I included above lines `**` for auto partitioning it doesn't work
);

In my trial scenario, it worked when I did not include Interval partitioning mechanism.
I also defined all the partitions explicitly for testing.

I have T1 data in production which reaches > 1 TB.
1 Billion rows each month
First data back in 2006..that old

Error Summary
What am I trying now?
I do not want to explicitly mention 10 years X 12(months) partitions up till this date. Hence included INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) and I do not want to define partitions explicitly for future months.
And defined first partition as per Oracle
What is wrong now?

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions

Have I checked for NULL values
Yes, as other blogs suggest I checked for NULL values in my columns and there is none.
What I suspect?
With Interval partitioning, sub-partitions might not be supported.
The statement that produces the error is:
INSERT INTO T3
SELECT T1.*, T2.DOC_DATETIME, T2.DOC_STATE
FROM T1
LEFT OUTER JOIN T2
ON (T1.DOC_ID =  T2.DOC_ID)
WHERE T2.DOC_DATETIME BETWEEN '01-APR-06' AND SYSDATE;

I am getting output if I remove the INSERT from line 1 and just keep SELECT.
Table structures:
T1 (Doc_ID varchar not null PK, label_ID varchar not null, label_val varchar not null)
T2 (DOc_ID varchar not null PK, Doc_State varchar not null, Doc_date DATE not null)
T3 (DOC_ID...,label_ID...,label_val..., Doc_State..., Doc_Date)

Please let me know if there is a solution.

I had to explicitly define all my range partitions. Interval partitioning did not work well with sub-partitioning template.

Oracle does HARD Limit of 1m partitions to my interval partitions.

I defined partitions as

PARTITION jan06 VALUES LESS THAN (TO_DATE('01-FEB-2006','DD-MON-YYYY')),
...
ALL THE WAY FOR NEXT 20 YEARS..
...
PARTITION jan26 VALUES LESS THAN (TO_DATE('01-FEB-2026','DD-MON-YYYY')),

Refer here and there


backing up selected tables in a database We need to transport database backups over a lower bandwidth connection. We only want the data of specific tables, not the whole database.

In SQL Server 2008 R2, how can this be done? I am attempting to find the solution with the least amount of programming or scripting.

The smallest for of a BACKUP DATABASE can only be a filegroup.  So in other words, if your tables are all confined to a selective filegroup you could achieve your goal through backing up that filegroup.

But I'm guessing that you probably don't have all of your backup-desirable tables in one filegroup isolated from the rest.  In which case you'll have to get a little creative.

What I'd do is just script out your desired tables.  You can do this by right-clicking your database and selecting generate scripts.  Walking through that wizard you can select the specific database objects to script out.  You can also set the option to back up both schema and data.


Compare Number of Rows of Tables I have two or more tables different by structure and I want to compare their numbers of rows, which I get with the count-star query:

select count(*) from table_a

For two tables I would like to see the difference of the count-star-queries, for example. If this difference is 0, then the tables have the same number of rows.

For more tables you can calculate an average of the count-star queries (or something more meaningful than the average, up to you) and compare the single count-star's with this value. The main point is that, if all the N tables have the same size, I have as result N columns (or rows) filled with 0's. If the tables have not the same number of rows, then in each of the N columns (or rows) I expect to see the difference of the number of rows of a given table and the average (or something more meaningful, cf. above).

I have this problem on an Oracle DB but I could need this query on PostgreSQL in the future, probably we will migrate.

The most efficient the query is, the coolest, of course. Please comment on this also if you want.

A subquery with UNION ALL and the aggregate function avg() used as window aggregate function should do the trick:

SELECT tbl::text, ct, round(ct - avg(ct) OVER (), 2) AS deviation
FROM  (
   SELECT tableoid::regclass AS tbl, count(*) AS ct FROM t GROUP BY 1
   UNION ALL
   SELECT tableoid, count(*) FROM tbl GROUP BY 1
   -- UNION ALL ...
   ) sub

SQL Fiddle.


MySQL insert into different databases tables based on PK value Lets assume i have two databases, both having a table 'user_feedback', sharding data between the two databases. My goal is to insert all records where Primary key Id%2=0 goes to db1.user_feedback, and Id%2=1 goes to db2.user_feedback.

Is there a condition in the INSERT statement that can allow me to do this? How can i achieve simply achieve this, both if the two databases are located on the same MySQL server, and when they both reside on a different MySQL server?

** UPDATE **
Ok, what i am trying to achieve is to have several tables in the same MySQL server to shard data and limit one table from becoming too big, hence slowing up queries on this table. The table is expected to grow very large (billion records) with heavy writes. That is the reason we have decided to resort to sharding. The table does not currently exist, but we want to create this shard solution in advance before we hit production with this table.

Hi you can try the below query.

insert into db1.user_feedback as select * from db2.user_feedback where < your
conditions>;
insert into db2.user_feedback as select * from db1.user_feedback where < your
conditions>;


MySQL "SLEEP" command throws syntax error The 'SLEEP' command in MySQL is throwing a syntax error:

mysql> sleep(5);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sleep(5)' at line 1

This is on MySQL 5.6.23.  I believe I have used SLEEP on the current version of MySQL on this server in the past, but I'm not sure what could have changed since then.

I've tried restarting the database server, but the problem persists.  For what it's worth, the reason I want to use SLEEP is to determine the rate of Queries and Questions running on the server, ie

show global status like 'Qu%'; sleep(60); show global status like 'Qu%';

You have to use

select sleep(5);

because it is similar to other mysql functions.


Multi-Server Extended Events investigations Do you happen to know if there's a method of centralizing the Extended Events information of many servers in one place (for easier administration and diagnostic).

Ideally I would like to have something like this:
http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/04/15/sql-2012-system-health-reporting-dashboard-visualizing-sp-server-diagnostics-results.aspx?CommentPosted=true

Only that it'll support Multi server environments and not a server/server  deployment.

Thanks in advance,
Roni.

You can set up Data Collection and create custom collection items for each event that you want to capture. There's this small project called Extended T-SQL Collector that can take care of collecting the data from Extended Events sessions and upload the events to a centralized MDW.

Here is an example of monitoring blocking and deadlocking with Extended T-SQL Collector