sql_slovak_cp1250_ci_as vs Slovak_CI_AS Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS?

I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. Should not be a problem?

Thank you very much.

Collation determines two things.

The code page used (and hence characters that can be stored) for non unicode data.
The comparison semantics for all textual data.

(NB: SQL Server 2012 also introduces _SC, supplementary character, collations for  UTF-16 encoding rather than UCS-2 but these are not relevant to your question here)

SELECT *
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%Slovak%CI%AS'

Returns the following information

name                      description
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Slovak_CI_AS              Slovak, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Slovak_100_CI_AS          Slovak-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
SQL_Slovak_CP1250_CI_AS   Slovak, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 94 on Code Page 1250 for non-Unicode Data

So on 2012 there are two windows collations and one SQL Collation. The description doesn't indicate the code page for the windows versions but that they are all the same can be seen from below.

CREATE TABLE #Collations
(
     code TINYINT PRIMARY KEY,
     SQL_Slovak_CP1250_CI_AS CHAR(1) COLLATE SQL_Slovak_CP1250_CI_AS,
     Slovak_CI_AS CHAR(1) COLLATE Slovak_CI_AS,
     Slovak_100_CI_AS CHAR(1) COLLATE Slovak_100_CI_AS,
     /*Included just to show an example of a different code page*/
     Latin1_General_CI_AS CHAR(1) COLLATE Latin1_General_CI_AS
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
            (code)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM   E08

UPDATE #Collations
SET    SQL_Slovak_CP1250_CI_AS = CAST(code AS BINARY(1)),
       Slovak_CI_AS = CAST(code AS BINARY(1)),
       Slovak_100_CI_AS = CAST(code AS BINARY(1)),
       Latin1_General_CI_AS = CAST(code AS BINARY(1))

SELECT *
FROM   #Collations

Or, alternatively, using the COLLATIONPROPERTY function

SELECT COLLATIONPROPERTY('SQL_Slovak_CP1250_CI_AS', 'CodePage') AS SQL_Slovak_CP1250_CI_AS,
       COLLATIONPROPERTY('Slovak_CI_AS', 'CodePage')            AS Slovak_CI_AS,
       COLLATIONPROPERTY('Slovak_100_CI_AS', 'CodePage')        AS Slovak_100_CI_AS,
       COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage')    AS Latin1_General_CI_AS

Returns

+-------------------------+--------------+------------------+----------------------+
| SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS | Latin1_General_CI_AS |
+-------------------------+--------------+------------------+----------------------+
|                    1250 |         1250 |             1250 |                 1252 |
+-------------------------+--------------+------------------+----------------------+

So as far as point 1 is concerned the collation makes no difference for SQL_Slovak_CP1250_CI_AS vs Slovak_CI_AS.

For point 2 see Comparing SQL collations to Windows collations. The Collation semantics section has some discussion about how the two differ but skipping ahead to Recommendations

  SQL collations are provided for backward compatibility with earlier
  versions of SQL Server. Windows collations provide consistent string
  comparisons for both Unicode and for non-Unicode text in SQL Server
  that are also consistent with string comparisons in the Windows
  operating system. For all these reasons, Windows collations are
  preferred unless there are backward compatibility issues or specific
  performance issues that require a SQL collation.

So you should be using the Windows Collations anyway unless you have some specific backward compatibility need. Also I would use Slovak_100_CI_AS as this is more recent and avoids issues like this.

To see examples of specifics of how the comparison semantics differ between the three collations you can use the temporary table created earlier and try

SELECT code,
       /*Exclude some characters that will mess up the formatting*/
       CASE WHEN code IN (9,10,12,13) THEN '' ELSE SQL_Slovak_CP1250_CI_AS END AS [Char],
       DENSE_RANK() OVER (ORDER BY SQL_Slovak_CP1250_CI_AS) AS SQL_Slovak_CP1250_CI_AS,
       DENSE_RANK() OVER (ORDER BY Slovak_CI_AS) AS Slovak_CI_AS,
       DENSE_RANK() OVER (ORDER BY Slovak_100_CI_AS) AS Slovak_100_CI_AS
FROM   #Collations
ORDER BY SQL_Slovak_CP1250_CI_AS

That yields the following results

+------+------+-------------------------+--------------+------------------+
| code | Char | SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS |
+------+------+-------------------------+--------------+------------------+
|    0 |      |                       1 |            1 |                1 |
|    1 |      |                       2 |            2 |                2 |
|    2 |      |                       3 |            3 |                3 |
|    3 |      |                       4 |            4 |                4 |
|    4 |      |                       5 |            5 |                5 |
|    5 |      |                       6 |            6 |                6 |
|    6 |      |                       7 |            7 |                7 |
|    7 |      |                       8 |            8 |                8 |
|    8 |      |                       9 |            9 |                9 |
|   14 |      |                      10 |           10 |               10 |
|   15 |      |                      11 |           11 |               11 |
|   16 |      |                      12 |           12 |               12 |
|   17 |      |                      13 |           13 |               13 |
|   18 |      |                      14 |           14 |               14 |
|   19 |      |                      15 |           15 |               15 |
|   20 |      |                      16 |           16 |               16 |
|   21 |      |                      17 |           17 |               17 |
|   22 |      |                      18 |           18 |               18 |
|   23 |      |                      19 |           19 |               19 |
|   24 |      |                      20 |           20 |               20 |
|   25 |      |                      21 |           21 |               21 |
|   26 |      |                      22 |           22 |               22 |
|   27 |      |                      23 |           23 |               23 |
|   28 |      |                      24 |           24 |               24 |
|   29 |      |                      25 |           25 |               25 |
|   30 |      |                      26 |           26 |               26 |
|   31 |      |                      27 |           27 |               27 |
|  127 |      |                      28 |           28 |               28 |
|  129 |      |                      29 |           29 |              106 |
|  131 |      |                      30 |           30 |              107 |
|  136 |      |                      31 |           31 |              108 |
|  144 |      |                      32 |           32 |              109 |
|  152 |      |                      33 |           33 |              110 |
|   39 | '    |                      34 |           34 |               29 |
|   45 | -    |                      35 |           35 |               30 |
|  173 |      |                      36 |           36 |                1 |
|  150 | –    |                      37 |           37 |               31 |
|  151 | —    |                      38 |           38 |               32 |
|   32 |      |                      39 |            1 |                1 |
|  160 |      |                      40 |           39 |               33 |
|    9 |      |                      41 |           40 |               34 |
|   10 |      |                      42 |           41 |               35 |
|   11 |      |                      43 |           42 |               36 |
|   12 |      |                      44 |           43 |               37 |
|   13 |      |                      45 |           44 |               38 |
|   33 | !    |                      46 |           45 |               39 |
|   34 | "    |                      47 |           46 |               40 |
|   35 | #    |                      48 |           47 |               41 |
|   36 | $    |                      49 |           48 |               42 |
|   37 | %    |                      50 |           49 |               43 |
|   38 | &    |                      51 |           50 |               44 |
|   40 | (    |                      52 |           51 |               45 |
|   41 | )    |                      53 |           52 |               46 |
|   42 | *    |                      54 |           53 |               47 |
|   44 | ,    |                      55 |           54 |               48 |
|   46 | .    |                      56 |           55 |               49 |
|   47 | /    |                      57 |           56 |               50 |
|   58 | :    |                      58 |           57 |               51 |
|   59 | ;    |                      59 |           58 |               52 |
|   63 | ?    |                      60 |           59 |               53 |
|   64 | @    |                      61 |           60 |               54 |
|   91 | [    |                      62 |           61 |               55 |
|   92 | \    |                      63 |           62 |               56 |
|   93 | ]    |                      64 |           63 |               57 |
|   94 | ^    |                      65 |           64 |               58 |
|   95 | _    |                      66 |           65 |               59 |
|   96 | `    |                      67 |           66 |               60 |
|  123 | {    |                      68 |           67 |               61 |
|  124 | |    |                      69 |           68 |               62 |
|  125 | }    |                      70 |           69 |               63 |
|  126 | ~    |                      71 |           70 |               64 |
|  166 | ¦    |                      72 |           71 |               65 |
|  168 | ¨    |                      73 |           72 |               66 |
|  180 | ´    |                      74 |           73 |               67 |
|  184 | ¸    |                      75 |           74 |               68 |
|  161 | ˇ    |                      76 |           75 |               69 |
|  162 | ˘    |                      77 |           76 |               70 |
|  255 | ˙    |                      78 |           77 |               71 |
|  178 | ˛    |                      79 |           78 |               72 |
|  189 | ˝    |                      80 |           79 |               73 |
|  145 | ‘    |                      81 |           80 |               74 |
|  146 | ’    |                      82 |           81 |               75 |
|  130 | ‚    |                      83 |           82 |               76 |
|  147 | “    |                      84 |           83 |               77 |
|  148 | ”    |                      85 |           84 |               78 |
|  132 | „    |                      86 |           85 |               79 |
|  139 | ‹    |                      87 |           86 |               80 |
|  155 | ›    |                      88 |           87 |               81 |
|   43 | +    |                      89 |           88 |               84 |
|   60 | <    |                      90 |           89 |               85 |
|   61 | =    |                      91 |           90 |               86 |
|   62 | >    |                      92 |           91 |               87 |
|  177 | ±    |                      93 |           92 |               88 |
|  171 | «    |                      94 |           93 |               89 |
|  187 | »    |                      95 |           94 |               90 |
|  215 | ×    |                      96 |           95 |               91 |
|  247 | ÷    |                      97 |           96 |               92 |
|  164 | ¤    |                      98 |           97 |               82 |
|  167 | §    |                      99 |           98 |               93 |
|  169 | ©    |                     100 |           99 |               94 |
|  172 | ¬    |                     101 |          100 |               95 |
|  174 | ®    |                     102 |          101 |               96 |
|  176 | °    |                     103 |          102 |               97 |
|  181 | µ    |                     104 |          103 |               98 |
|  182 | ¶    |                     105 |          104 |               99 |
|  183 | ·    |                     106 |          105 |              100 |
|  134 | †    |                     107 |          106 |              102 |
|  135 | ‡    |                     108 |          107 |              103 |
|  149 | •    |                     109 |          108 |              104 |
|  133 | …    |                     110 |          109 |              101 |
|  137 | ‰    |                     111 |          110 |              105 |
|  128 | €    |                     112 |          111 |               83 |
|   48 | 0    |                     113 |          112 |              111 |
|   49 | 1    |                     114 |          113 |              112 |
|   50 | 2    |                     115 |          114 |              113 |
|   51 | 3    |                     116 |          115 |              114 |
|   52 | 4    |                     117 |          116 |              115 |
|   53 | 5    |                     118 |          117 |              116 |
|   54 | 6    |                     119 |          118 |              117 |
|   55 | 7    |                     120 |          119 |              118 |
|   56 | 8    |                     121 |          120 |              119 |
|   57 | 9    |                     122 |          121 |              120 |
|   97 | a    |                     123 |          122 |              121 |
|   65 | A    |                     123 |          122 |              121 |
|  193 | Á    |                     124 |          123 |              122 |
|  225 | á    |                     124 |          123 |              122 |
|  226 | â    |                     125 |          124 |              123 |
|  194 | Â    |                     125 |          124 |              123 |
|  195 | Ă    |                     126 |          125 |              124 |
|  227 | ă    |                     126 |          125 |              124 |
|  185 | ą    |                     127 |          126 |              125 |
|  165 | Ą    |                     127 |          126 |              125 |
|  196 | Ä    |                     128 |          127 |              126 |
|  228 | ä    |                     128 |          127 |              126 |
|   66 | B    |                     129 |          128 |              127 |
|   98 | b    |                     129 |          128 |              127 |
|   99 | c    |                     130 |          129 |              128 |
|   67 | C    |                     130 |          129 |              128 |
|  230 | ć    |                     131 |          130 |              129 |
|  198 | Ć    |                     131 |          130 |              129 |
|  199 | Ç    |                     132 |          131 |              130 |
|  231 | ç    |                     132 |          131 |              130 |
|  232 | č    |                     133 |          132 |              131 |
|  200 | Č    |                     133 |          132 |              131 |
|   68 | D    |                     134 |          133 |              132 |
|  100 | d    |                     134 |          133 |              132 |
|  239 | ď    |                     135 |          134 |              133 |
|  207 | Ď    |                     135 |          134 |              133 |
|  208 | Đ    |                     136 |          135 |              134 |
|  240 | đ    |                     136 |          135 |              134 |
|  101 | e    |                     137 |          136 |              135 |
|   69 | E    |                     137 |          136 |              135 |
|  235 | ë    |                     138 |          137 |              136 |
|  203 | Ë    |                     138 |          137 |              136 |
|  201 | É    |                     139 |          138 |              137 |
|  233 | é    |                     139 |          138 |              137 |
|  236 | ě    |                     140 |          139 |              138 |
|  204 | Ě    |                     140 |          139 |              138 |
|  202 | Ę    |                     141 |          140 |              139 |
|  234 | ę    |                     141 |          140 |              139 |
|   70 | F    |                     142 |          141 |              140 |
|  102 | f    |                     142 |          141 |              140 |
|  103 | g    |                     143 |          142 |              141 |
|   71 | G    |                     143 |          142 |              141 |
|   72 | H    |                     144 |          143 |              142 |
|  104 | h    |                     144 |          143 |              142 |
|  105 | i    |                     145 |          144 |              143 |
|   73 | I    |                     145 |          144 |              143 |
|  237 | í    |                     146 |          145 |              144 |
|  205 | Í    |                     146 |          145 |              144 |
|  206 | Î    |                     147 |          146 |              145 |
|  238 | î    |                     147 |          146 |              145 |
|   74 | J    |                     148 |          147 |              146 |
|  106 | j    |                     148 |          147 |              146 |
|  107 | k    |                     149 |          148 |              147 |
|   75 | K    |                     149 |          148 |              147 |
|   76 | L    |                     150 |          149 |              148 |
|  108 | l    |                     150 |          149 |              148 |
|  229 | ĺ    |                     151 |          150 |              149 |
|  197 | Ĺ    |                     151 |          150 |              149 |
|  188 | Ľ    |                     152 |          151 |              150 |
|  190 | ľ    |                     152 |          151 |              150 |
|  179 | ł    |                     153 |          152 |              151 |
|  163 | Ł    |                     153 |          152 |              151 |
|   77 | M    |                     154 |          153 |              152 |
|  109 | m    |                     154 |          153 |              152 |
|  110 | n    |                     155 |          154 |              153 |
|   78 | N    |                     155 |          154 |              153 |
|  209 | Ń    |                     156 |          155 |              154 |
|  241 | ń    |                     156 |          155 |              154 |
|  242 | ň    |                     157 |          156 |              155 |
|  210 | Ň    |                     157 |          156 |              155 |
|   79 | O    |                     158 |          157 |              156 |
|  111 | o    |                     158 |          157 |              156 |
|  214 | Ö    |                     159 |          158 |              157 |
|  246 | ö    |                     159 |          158 |              157 |
|  211 | Ó    |                     160 |          159 |              158 |
|  243 | ó    |                     160 |          159 |              158 |
|  213 | Ő    |                     161 |          160 |              159 |
|  245 | ő    |                     161 |          160 |              159 |
|  212 | Ô    |                     162 |          161 |              160 |
|  244 | ô    |                     162 |          161 |              160 |
|  112 | p    |                     163 |          162 |              161 |
|   80 | P    |                     163 |          162 |              161 |
|   81 | Q    |                     164 |          163 |              162 |
|  113 | q    |                     164 |          163 |              162 |
|  114 | r    |                     165 |          164 |              163 |
|   82 | R    |                     165 |          164 |              163 |
|  192 | Ŕ    |                     166 |          165 |              164 |
|  224 | ŕ    |                     166 |          165 |              164 |
|  248 | ř    |                     167 |          166 |              165 |
|  216 | Ř    |                     167 |          166 |              165 |
|   83 | S    |                     168 |          167 |              166 |
|  115 | s    |                     168 |          167 |              166 |
|  156 | ś    |                     169 |          168 |              167 |
|  140 | Ś    |                     169 |          168 |              167 |
|  170 | Ş    |                     170 |          169 |              168 |
|  186 | ş    |                     170 |          169 |              168 |
|  223 | ß    |                     171 |          170 |              169 |
|  138 | Š    |                     172 |          171 |              170 |
|  154 | š    |                     172 |          171 |              170 |
|   84 | T    |                     173 |          172 |              171 |
|  116 | t    |                     173 |          172 |              171 |
|  157 | ť    |                     174 |          173 |              172 |
|  141 | Ť    |                     174 |          173 |              172 |
|  254 | ţ    |                     175 |          174 |              173 |
|  222 | Ţ    |                     175 |          174 |              173 |
|  153 | ™    |                     176 |          175 |              174 |
|   85 | U    |                     177 |          176 |              175 |
|  117 | u    |                     177 |          176 |              175 |
|  220 | Ü    |                     178 |          177 |              176 |
|  252 | ü    |                     178 |          177 |              176 |
|  250 | ú    |                     179 |          178 |              177 |
|  218 | Ú    |                     179 |          178 |              177 |
|  217 | Ů    |                     180 |          179 |              178 |
|  249 | ů    |                     180 |          179 |              178 |
|  251 | ű    |                     181 |          180 |              179 |
|  219 | Ű    |                     181 |          180 |              179 |
|  118 | v    |                     182 |          181 |              180 |
|   86 | V    |                     182 |          181 |              180 |
|   87 | W    |                     183 |          182 |              181 |
|  119 | w    |                     183 |          182 |              181 |
|  120 | x    |                     184 |          183 |              182 |
|   88 | X    |                     184 |          183 |              182 |
|   89 | Y    |                     185 |          184 |              183 |
|  121 | y    |                     185 |          184 |              183 |
|  221 | Ý    |                     186 |          185 |              184 |
|  253 | ý    |                     186 |          185 |              184 |
|  122 | z    |                     187 |          186 |              185 |
|   90 | Z    |                     187 |          186 |              185 |
|  191 | ż    |                     188 |          187 |              186 |
|  175 | Ż    |                     188 |          187 |              186 |
|  159 | ź    |                     189 |          188 |              187 |
|  143 | Ź    |                     189 |          188 |              187 |
|  142 | Ž    |                     190 |          189 |              188 |
|  158 | ž    |                     190 |          189 |              188 |
+------+------+-------------------------+--------------+------------------+


Would adding an INDEX on varchar column in Mysql increase performance? I have the following table to store log messages:

CREATE TABLE `log` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `timestamp` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `logger` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `level` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `message` varchar(4000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `thread` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `file` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `line` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `machine` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10321 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

There will be lots of SELECTs with a WHERE machine = 'xyz'

Would making this column an INDEX improve performance on that?

Answer is yes, indexing varchar data field increases the performance but it depends on the query you performed against that particular table. Lets consider the following two entities,

Card

Card Id (PK)
Card Type
Card Name

Registration

Reg Id (PK)
Card Id (FK)
Number of Registration

Lets consider a query, what are the total number of registration by card type?

Here, the card type is not frequently updated, it is either Credit or Debit Card. Therefore index on the card type data field is a good choice. Card type contains the values Credit card and Debit Card only.

If your non-key data field have such data and you need to perform the query in such a way mentioned above, you can create a non-unique key index for performance.

I hope this answer will help you. Thank you.


AlwaysOn commit on Primary if Secondary goes down I have two node SQL Server AlwaysOn Availability Group configured in Synchronous with Automatic Failover mode.

Now since in Synchronous mode, the transactions need to commit on secondary replica first and after its acknowledgment only, it gets committed on the primary node.

Now if my secondary replica node goes down, will the transactions not commit only on my primary node, until unless my secondary comes up? Will my transactions not get hardened to disk?

Books Online has a really good page on this, including:

  If primary's session-timeout period is exceeded by a secondary
  replica, the primary replica temporarily shifts into
  asynchronous-commit mode for that secondary replica. When the
  secondary replica reconnects with the primary replica, they resume
  synchronous-commit mode.

Put another way, the primary holds transactions for a short period of time, and it'll feel like there's a blocking lock that's stopping your transaction from completing. After the primary gives up on the secondary, the transaction will go through, but only be hardened on the primary - not the unavailable secondary.

The default session timeout period is 10 seconds.

If you want the primary to only accept transactions when a certain number of secondaries are available, you'll need to upgrade to SQL Server 2017 and use the new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting.


MariaDB: How can I grant read-only privileges for Stored Procedures? I've granted SELECT privileges for a user to select on all tables of a database. However that user is unsable to view store procedures.
I'd want to grant only the permission for SHOW CREATE PROCEDURE (or something alike).
Is that possible on MariaDB 10.x?

Per the knowledge base, SELECT privileges on mysql.proc is sufficient.


Postgresql: How to index jsonb array of objects column I have a table that has a jsonb column which has an array of objects.

Looks like this for every row.

[{grade: 'A', subject: 'MATH'}, {grade: 'B', subject: 'PHY'}...]

Now querying over it thanks to this post https://stackoverflow.com/a/30592076/2405689 .

Problem comes it takes at least 2.4 secs to finish querying a count of all students with grade IN (A, B, C).

I would like to get some help on indexing this because, the indexes I have tried didn't do anything.

DROP INDEX idx_subjects_subject;
DROP INDEX idx_subjects_grade;
CREATE INDEX idx_subjects_subject ON results USING GIN((subjects-> 'subject'));
CREATE INDEX idx_subjects_grade ON results USING GIN((subjects-> 'grade'));

Also did (separately):

DROP INDEX idx_subjects_standard;
CREATE INDEX idx_subjects_standard ON results USING GIN(subjects);

I'm querying them like this.

SELECT COUNT(*)
FROM results
WHERE EXISTS
    (
        SELECT 1
        FROM jsonb_array_elements(subjects) AS j(data)
        WHERE (data #>> '{subject}') LIKE '%MATH%'
        AND
        (data #>> '{grade}') IN ('A', 'B', 'C')
    )
AND
    "examYear" = '2010'
AND
    "examType" = 'CSEE'
;

I also tried querying it like this :

SELECT COUNT(*)
FROM results
WHERE EXISTS
    (
    SELECT 1
    FROM jsonb_array_elements(subjects) AS j(data)
    WHERE data @> '{"subject": "B/MATH", "grade": "A"}'
    OR data @> '{"subject": "B/MATH", "grade": "B"}'
    OR data @> '{"subject": "B/MATH", "grade": "C"}'

    )
AND
    "examYear" = '2010'
AND
    "examType" = 'CSEE'
;

But this gave me a reversed effect (3 secs query).

Here is my explain analyze block.

"Aggregate  (cost=1888617.19..1888617.20 rows=1 width=8) (actual time=2517.116..2517.117 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on results  (cost=470767.18..1888021.96 rows=238090 width=0) (actual time=680.456..2514.633 rows=24002 loops=1)"
"        Recheck Cond: ("examYear" = '2010'::text)"
"        Rows Removed by Index Recheck: 557945"
"        Filter: (("examType" = 'CSEE'::text) AND (SubPlan 1))"
"        Rows Removed by Filter: 500054"
"        Heap Blocks: exact=40156 lossy=53452"
"        ->  Bitmap Index Scan on idx_results_subjects  (cost=0.00..470707.66 rows=528405 width=0) (actual time=672.375..672.375 rows=524056 loops=1)"
"              Index Cond: ("examYear" = '2010'::text)"
"        SubPlan 1"
"          ->  Function Scan on jsonb_array_elements j  (cost=0.00..2.13 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=458487)"
"                Filter: (((data #>> '{subject}'::text[]) ~~ '%MATH%'::text) AND ((data #>> '{grade}'::text[]) = ANY ('{A,B,C}'::text[])))"
"                Rows Removed by Filter: 8"
"Planning time: 0.126 ms"
"Execution time: 2517.145 ms"

I'm on postgresql: 9.6.1

The following code will be able to use a GIN index on the jsonb column:

SELECT COUNT(*) FROM results
WHERE subjects @> '[{"subject": "B/MATH", "grade": "A"}]'

The difference to your example is that I don't unpack the array, I just query the jsonb column directly. The GIN index can be used for the @> operator on jsonb columns, but it can't be used for arbitrary functions. You're using the @> operator in the end, but not on the actual jsonb column but on your extracted json.

Indexing a LIKE condition with a wildcard at the beginning is far, far more difficult. You would need a trigram index for this, but I've no idea how to use one for data inside an array in a jsonb column, if that is even possible. You should strongly consider storing this information in a more formal way that avoids having to use LIKE at all.

A LIKE that doesn't have a wildcard at the beginning like 'MATH%' can use a btree index under some conditions (see https://www.postgresql.org/docs/9.5/static/indexes-types.html for some details, you have to pay attention to the locale for this).

If this weren't an array, but a plain object inside the jsonb column, you could use a functional index like e.g.

CREATE INDEX ON results((subject->>'grade'));

This is maybe also possible with an array inside jsonb, but I can't think of a reasonable way to do this right now.

Your current schema makes everything an order of magnitude more difficult to write and makes it very hard to impossible to properly take advantage of indexes. If you have the option, consider storing this data in a table with a subject and a grade column, this would make this problem far easier.


How can I exclude range overlaps if one range may be empty? Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table:

CREATE TABLE heights_in_feet(
  size TEXT NOT NULL, min INTEGER NOT NULL, max INTEGER NOT NULL
);
INSERT INTO heights_in_feet (size, min, max)
  VALUES ('wee', 0, 2), ('not so wee', 3, 6), ('friggin huge', 7, 10);

To prevent ambiguity, I add an exclusion constraint:

ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (int4range("min", "max") WITH &&);

At first, this seems to be correct:

INSERT INTO heights_in_feet (size, min, max)
  VALUES ('medium', 4, 5); -- INSERT fails, as it should

However, it doesn't work with empty ranges:

INSERT INTO heights_in_feet (size, min, max)
  VALUES ('medium', 5, 5); -- INSERT succeeds!

This is bad: it's now ambiguous whether 5 feet is "medium" or "not so wee".

The issue is that Postgres considers int4range(5,5) to be empty, and thus non-overlapping:

SELECT int4range(3, 6) && int4range(4, 5); -- t
SELECT int4range(3, 6) && int4range(5, 5); -- f

It does consider (3,6) to contain (5,5):

SELECT int4range(3, 6) @> int4range(5, 5);

... but 1) I can't use that in an exclusion constraint because it's not commutative and 2) it also returns true for int4range(3, 6) @> int4range(50, 50); apparently any empty range is "contained by" any non-empty range.

How can I exclude new rows that would create ambiguous classifications?

Use inclusive ranges

Adding an argument like '[]' to int4range tells it to use inclusive ranges, meaning that, eg, (5,5) is not empty because it contains 5. Eg:

SELECT int4range(3, 6) @> int4range(5, 5, '[]');   -- t
SELECT int4range(3, 6) @> int4range(50, 50, '[]'); -- f

Therefore, this exclusion works as I expected:

ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (int4range("min", "max", '[]') WITH &&);


Mysql update different fileds set depend on condition I have two tables "a" :

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  `ib` int(11) DEFAULT NULL,
  KEY `id` (`id`)
)

 +----+------+------+------+
| id | i1   | i2   | ib   |
+----+------+------+------+
|  1 |    1 |    1 |   10 |
|  2 |    1 |    1 |    1 |
|  3 |    1 |    1 |    2 |
|  4 |    1 |    1 |    3 |
|  5 |    1 |    1 |    5 |
+----+------+------+------+

and "b"

CREATE TABLE `b` (
  `i` int(11) DEFAULT NULL
)

+------+
| i    |
+------+
|    5 |
|    1 |
|   10 |
+------+

My script parse external data (jid,j1,j2) and update table "a".
With 2 commands:

update a set i1=j1, i2=j2 where id=jid
   and not exists (select 1 from b where a.ib=b.i)

and

update a set i1=j1 where id=jid
  and exists (select 1 from b where a.ib=b.i)

So, I want to update both fields i1 and i2 if I have not related record in "b" table, and update only one field i1 if I have related record.

If I have input data

(1,2,3)
(2,5,6)
(3,7,8)

My table "a" should be:

+----+------+------+------+
| id | i1   | i2   | ib   |
+----+------+------+------+
|  1 |    2 |    1 |   10 |   <- i1 changed
|  2 |    5 |    1 |    1 |   <- i1 changed
|  3 |    7 |    8 |    2 |   <- both i1 and i2 changed
|  4 |    1 |    1 |    3 |
|  5 |    1 |    1 |    5 |
+----+------+------+------+

I am pretty sure - it can be done with only one sql command.

I like the JOIN syntax, it's clearer to me than subqueries:

UPDATE a LEFT JOIN b ON (a.ib=b.i)
SET i1 = j1,
    i2 = if(isnull(b.i), j2 ,i2)
WHERE a.id = jid;

This will change the jid record: i1 to j1 always and i2 to j2 if no match exists, and to the same value if the match exists. You may want to index b.i (probably as a primary key) if there are a lot of records and you want performance.


Who changed SQL server max memory configuration? Is there a way to tell who changed SQL server's max memory after a several SQL reboots?. I looked at the default trace log for event category 81 (memory configuration changes) but could not find anything.
SQL server -->reports--> configuration report changes is also empty.
Please suggest.

YES, there is a way to find out Who did it.

On SQL Log file viewer, on left panel select Windows NT to expand and select Application to display.

You can see details of date, User, computer, etc:

message:
Date        8/02/2014
Log     Windows NT (Application)

Source      MSSQLSERVER
Category        (2)
Event       2342
User        ???????   THIS IS WHO  "OFFENDER"
Computer    THIS IS THE HOST where he did it

Message
Configuration option 'min server memory (MB)' changed from 2048 to 512. Run the RECONFIGURE statement to install.

Good luck.


What is Postgres lock behaviour when UPDATE WHERE clause does a table scan? Say you have a large table with tens of millions of rows.
You want to UPDATE large_table SET col=value WHERE col=other_value... but col is not indexed and an EXPLAIN shows that this query will perform a seq scan over the whole table.
What is the lock behaviour here?  According to most accounts Postgres only locks the affected rows of an UPDATE query and does not have lock escalation. So does it search for the rows to update first, then only lock the found rows?  It seems like there would potentially be problems of other queries updating rows concurrently in that case though. Does it lock each row "as it finds them" i.e. locking rows progressively as it goes through the seq scan?
So I think the best case here is it locks rows as it finds them, and the affected rows (only) will be locked for up to as long as the UPDATE query takes to complete.
But I am worried that this query could instead end up blocking all writes to the table until it completes.
I have read this: https://habr.com/en/company/postgrespro/blog/503008/ and I think the worst case will not happen, but here https://blog.heroku.com/curious-case-table-locking-update-query is a possibly inaccurate representation of similar info that gives me some doubts.
The application only uses SELECT, SELECT FOR UPDATE and UPDATE queries (i.e. no other explicit locks taken apart from those). The table has foreign keys to other tables, and other tables have foreign key to this table.
We're on Postgres 11.

For the discussion, let's assume your execution plan looks like
        QUERY PLAN
--------------------------
 Update on mytab
   ->  Seq Scan on mytab
         Filter: (id = 1)

I also assume that you are using the default READ COMMITTED isolation level.
Then PostgreSQL will sequentially read to the table.
Whenever it finds a row that matches the filter, that row will be locked and updated.
If locking a row is blocked by a concurrent query, PostgreSQL waits until the lock goes away. Then it re-evaluates the filter condition and either moves on (if the condition no longer applies on account of a concurrent modification) or it locks and updates the modified row.
See the documentation:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

In particular, it is possible that two UPDATE statements that each modify several rows deadlock with each other, since they acquire locks as the proceed and locks are always held until the end of the transaction.


Concat issue has me perplexed MSSQL 2005 Server Workgroup.

I have a database that is serving as a "Warehouse" for another, older, slower cobol database/application.

I've recently moved the warehouse from one SQL Server instance to a new instance, to keep the warehousing I'm doing separate from other production databases.

The issue I'm having has me perplexed. The table I have, as a copy of the older database, is has, for the purpose of this question, 4 fields: ID, LName, FName, MI, FullName - all NOT NULL. On the report server I have a calculated field FullName: LastName, FName MI:

FullName = LName + ', ' + FName + ' ' + MI;

What's happening is the names are not joining consistently, and it has me completely baffled.

DECLARE @ID int;
SET @ID = 1;
SELECT ID, 'LName' FName, LName                                   Field FROM TABLE WHERE ID IN (@ID) UNION
SELECT ID, 'FName' FName, FName                                   Field FROM TABLE  WHERE ID IN (@ID) UNION
SELECT ID, 'MI'    FName, MI                                      Field FROM TABLE  WHERE ID IN (@ID) UNION
SELECT ID, 'LFM'   FName, LName + ', ' + FName + ' ' + MI Field FROM TABLE  WHERE ID IN (@ID)

This outputs the expected:

1   FName   Jack
1   MI  T
1   LName   Frost
1   LFM Frost, Jack T

But if I switch it to a different ID I get:

2   FName   Joe
2   MI  E
2   LName   Schmoe
2   LFM Schmoe

It's not an issue with nulls (Three fields have data). Trim, coalesce, different combinations of name (LFM, MFL, LF, MF, etc) end up the same: Either the person has a full name... or the person has the first one listed.

To my knowledge I did not have this issue in the other isntance. New instance is on the same server.

Original Server Instance: 2005 9.0.3042 Serv\SQL2005
New Server Instance:      2005 9.0.1399 Serv\DataWarehouse

Note: Query against "original" database works as expected. Query against a report built upon this warehouse works as expected.

So Original Database > Data Warehouse > Report. Original works fine. Report works fine. Data warehouse doesn't. Warehouse and Report are in the same instance, but different databases.

Based on your sample data, the data you're actually querying for ID = 1396779 is different

No amount of trims, isnulls etc will change Schmoe into Frost or Jack into Joe.
Ergo, data is different.

Otherwise, you have some DB or Server confusion going on. A report based on the DataWarehouse can't give correct data if your query against the DataWarehouse is wrong.

Note that a cross database Synonyms or View, or a table in a different schema (your query doesn't use dbo.PCDB_VISIT for example) could mislead you. Or you have some client alias that means you are pointing at the wrong server from SSMS but the Report is correct.

Try this to make sure that you are where you think you are

DECLARE @ID int;
SET @ID = 1396779;
SELECT @@SERVERNAME, DB_NAME(), ID, 'LName' FName, LName Field FROM PCDB_VISIT PV


postgres schema grants usage I have a database called layers. in it there are two schemas 1. how 2. base_layers

each schema has its own login name and password and both are not superusers. I want the ability to query across both schemas from within the schema logins(not using postgres superuser)

above I am running this query from the base_layers schema login and it gives me this permission denied error. if i run this query through the postgres connection it works... what is the proper way to be granting usage from schema to schema without making each login name a superuser

With a super-user, do a one-time GRANT to both logins and add WITH GRANT OPTION to the end. It sounds like the users have the ability to query their own schemas but are not allowed to grant it to others.

https://www.postgresql.org/docs/9.0/static/sql-grant.html


Does it makes sense to partition on a foreign key? I have a MySQL table which looks a bit like this:

ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed)

Selects should go very fast, updates almost never happens, inserts are not so frequent.
Queries will always query data within the same OtherID. The other filter will always be a range of NegativeTimestamp (which is a negative timestamp, so I can use ASC index while I always need the timestamps to be DESC), or the top (for example top 1000 rows with lowest timestamps). Recent data (lowest NegativeTimestamps) are requested most frequently, but there is no clear line which timestamps are more frequently requested than others.

I'm not sure if I should partition on OtherID (as queries will then never involve two partitions), or on Timestamp, or on none of them.

Any other optimizations are always welcome of course. If possible, also explain why so I can use this information in the future.

Extra info:

Table:

CREATE TABLE `myTable` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `MarketID` int(11) NOT NULL,
  `Close_Datetime` datetime NOT NULL,
  `Data` decimal(30,15) DEFAULT NULL,
  `Close_NegativeTimestamp` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `IX_FK_MarketPriceCandle` (`MarketID`),
  KEY `OpenTimestamp` (`Open_Datetime`),
  KEY `CloseTimestamp` (`Close_Datetime`),
  KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`),
  KEY `MarketIDIndex` (`MarketID`),
  KEY `CloseNegativeTimestampMarketID` (`MarketID`,`Close_NegativeTimestamp`),
  CONSTRAINT `FK_MarketMyTable` FOREIGN KEY (`MarketID`) REFERENCES `markets` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=110011638 DEFAULT CHARSET=utf8;

This table has currently more than 100 million rows and will continue to grow.

Most executed query:

SELECT * FROM myTable p
 WHERE p.MarketID = @marketID AND p.Close_NegativeTimestamp <= @start AND p.Close_NegativeTimestamp >= @end
 ORDER BY p.Close_NegativeTimestamp ASC;

FOREIGN KEYS are not supported in PARTITIONed tables.  (With luck this might change in 5.8.)

If you don't have over a million rows, don't bother to PARTITION.

If you are not depending on FOREIGN KEY CONSTRAINTS, don't use them.  INDEXes will suffice.

If you would like to discuss these further, please provide SHOW CREATE TABLE (even if not finalized) and the main SELECTs.

Based on what you said, I would guess that you need a compound index:

INDEX(OtherID, NegativeTimestamp)

Edit

I see a redundant index on MarketID.  Also UNIQUE(ID) is redundant since the PRIMARY KEY is a UNIQUE key.

If the two Close columns have the same information, but one is negated, then the negative on can probably be removed.  Then use DESC in the ORDER BY.

The use of @variables in the WHERE may be poorly optimized; let's see EXPLAIN SELECT ....

I would replace all of the indexes with just 2:

PRIMARY KEY (`MarketID`, Close_Datetime, ID),
INDEX(ID),  -- This is sufficient for AUTO_INCREMENT (except for protecting from deliberate dup)
-- nonexistent field?  KEY `OpenTimestamp` (`Open_Datetime`),
-- unneeded?   KEY `CloseTimestamp` (`Close_Datetime`),
-- unneeded?   KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`),
-- covered     KEY `MarketIDIndex` (`MarketID`),
-- replaced    KEY (`MarketID`,`Close_NegativeTimestamp`),

Also, I would shrink most of the fields to save space and improve cacheability.  BIGINT is not really needed?  MEDIUMINT would suffice for MarketID?  decimal(30,15) takes 14 bytes, mostly zeros.  DOUBLE (8 bytes) might work?

(I am assuming this is Stock Market data?)

If you choose to get rid of the FK and go with PARTITION, then I would need to see the other important SELECTs.

But, for that matter, do you need ID?  Can't (MarketID, CloseDate) be the unique PRIMARY KEY?  Note: It would be a 3-byte DATE, not a 5 or 8-byte DATETIME.


How to get N rows after the first row matching a condition? I am implementing a cursor based pagination.

The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the last page).

The first page is simple:

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

Assuming this query returns IDs 4, 5, 2, 1, 3, then the next query must produce 5 next results from the same query after id 3.

Note: We cannot use LIMIT ... OFFSET after the first page because the offset is relative to the position of the referenced result that we do not know.

The only way I can think of solving this problem is:

Find row number in the dataset for ID 3:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 3;

Then use position to offset the dataset.

All together:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 3
)
LIMIT 10;

The downside of this approach is that data_index needs to load the entire sub-query into memory. Is there a more efficient way?

You could simply add the column used for the ordering (description) to the SELECT list and the WHERE clause of the following queries:

-- 1st query:
SELECT
  id, description
FROM probe
ORDER BY description, id
LIMIT 5 ;

Lets say that you get @id = 3 and @description = 'Beta' as the last (5th) row. You can then use these values to get the next (5) rows:

-- next query:
SELECT
  id, description
FROM probe
WHERE (description, id) > (@description, @id)
ORDER BY description, id
LIMIT 5 ;


A slight variation of Vérace very detailed answer. The idea is to enumerate the rows per id, and per id plus, if the condition holds. I named the difference grp. If grp changes it means that there is a change to whether the condition holds. I.e. min and max grouped by grp will apply to intervals where condition either holds or fails.

I used a having clause for the duration condition.

select id, min(the_time), max(the_time) -- min and max per interval
from (
    select id, the_value, the_time
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time)
         - row_number() over (partition by id
                                 , case when the_value < 10
                                        then 1
                                        else 0
                                   end
                              order by the_time) as grp
    from tab
) as x
where the_value < 10 -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4; -- duration

The added sample data results in:

1   3   9
1   11  15

It is possible to determine the condition in one place by nesting the query, but I'm not sure whether that is useful or not:

select id, MIN(the_time) AS min, MAX(the_time) AS max -- min and max per interval
from (
    select id, the_value, the_time, condition
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time)
         - row_number() over (partition by id, condition
                              order by the_time) as grp
   from (
       select id, the_value, the_time, the_value < 10 as condition
       from tab
   ) as a
) as x
where condition -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4 -- duration
ORDER BY id, min;


Use column alias in GROUP BY I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly?

SELECT
  KOS_VER_ID        AS "Vertrag"
  , WHR_ISO_3_CODE  AS "Waehrung"
  , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN as "Kostenart"
  , SUM (KOS_BETRAG) AS "Summe pro KOA"
FROM
  KOSTEN
  , WAEHRUNG
  , SCHLUESSELTABELLE
WHERE
  KOSTEN.KOS_VERHI_WHR_ID = WAEHRUNG.WHR_ISO_ID
  AND KOSTEN.KOS_KOA_ST_KZN = SCHLUESSELTABELLE.ST_ID
  AND KOS_VER_ID in (2509, 2510, 2511)
GROUP BY
  KOS_VER_ID
  , WHR_ISO_3_CODE
  , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN
ORDER BY
  "Vertrag"
  , "Kostenart"
;

Why does the alias not work in the GROUP BY clause?

Because these simply are the syntax rules. A column alias may be used in the ORDER BY clause but not in the GROUP BY clause.

If you look at the syntax diagrams this becomes obvious:

The order by clause is defined as:

So after the ORDER BY an expression is allowed, a position (order by 1) or a c_alias which stands for "column alias".

The group by is defined as:

As you can see, no c_alias option after it, so you can't use a column alias.

This is nothing special to Oracle though. This is how it was defined in the SQL standard.


Query on bridge table I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the tables:

Authors (Au_Id  PK, Au_fname, Au_lname, ...)

Titles (Title_Id PK, Title, Title_type, ...)

TitleAuthor (Au_Id PK, Title_Id PK, Au_order, ...)

In TitleAuthor, I am  not sure of what Au_order means, but I suspect it is the order in which an author's name is cited in a book with more than one author. Au_order is a TinyInt data type, btw. These queries are supposed to be introductory, i.e. no fancy joins or subqueries.

This is what I have tried:

1)

Select Au_Id from `TitleAuthor` where  Count(Auth_id)>1;

But this gives us the author name, not the book name (remember: intro query)

2) Figuring out which books/titles are mentioned more than once in TitleAuthor:

Select Title_id from Title-Author where Count(Title_Id)>1;

This gives me no results for some reason, though no error messages either. I suspect I may need grouping and having, but this is not working either.

Any Ideas?

There are many ways. Probably simplest:

SELECT Title_id
FROM   TitleAuthor
GROUP  BY Title_id
HAVING count(*) > 1;

You can JOIN to Titles to get book details.


SQL Server Spill level 0 - what is it? I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before:

<Warnings>
<SpillToTempDb SpillLevel="0" SpilledThreadCount="1" />
<ExchangeSpillDetails WritesToTempDb="237" />
</Warnings>

Does anyone have any information on "Spill level 0"?

Before everyone sends me off to lmgtfy.com -  I've been there :-) and there is literally just a single result for this on Google; and on that page "Spill Level 0" is mentioned, but no other info.

I've looked in my SQL Server internals books, Bing.com, and so on.
Nothing.

My guess is that it has something to do with control thread spilling over, or perhaps intra-query deadlock?
The query itself is pretty basic; SELECT DISTINCT with 3 INNER JOINs, followed by 2 LEFT.

Any clue will be greatly appreciated.

And please note: The query performance has been fixed, but the mystery of that spill remains. I am not asking for help to improve this query - that's why it is not included here. I simply want to get an idea about spill 0.

Environment: SQL Server 2016 Ent. SP2 (no CUs) . MAXDOP = 4 set by RG., SSMS v18.5

I also include here a screenshot of these two operators in the plan.

Thank you!

An exchange spill (ExchangeSpillDetails) occurs only in response to an intra-query parallelism deadlock. SQL Server resolves the deadlock by forcing one or more of the exchanges (parallelism operators) to write its buffers to tempdb.

There is no concept of a spill "level" for an exchange spill (unlike sort or hash spills, which may require multiple passes or recursion). The unpopulated value is reported as zero.

You will generally want to avoid parallel deadlocks because they are resolved by the Resource Monitor, which by default only wakes up every five seconds. It will check for deadlocks more frequently than that if there has been a recent deadlock (of any kind) but it's never going to be great for performance.

One more general note: Parallel deadlocks occur due to dependencies between threads, almost always related to preserved ordering. Parallel merge join with order-preserving exchanges on both inputs are a good example of this.

For a cool visualization of an intra-query parallel deadlock, please see Grokking the Paul White Parallel Deadlock Demo by Forrest McDaniel.


Dynamic SQL in MySQL stored routines According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And why lift it for procedures, but not functions or triggers?

Just hearing the question makes me think of two aspects:

ASPECT #1 : Functions are supposed to be DETERMINISTIC

If this so, this implies that a function should present the same return data consistently for a given set of parameters, NO MATTER WHEN YOU CALL THE FUNCTION.

Now, imagine a function that produces a different answer because of gathering data at different times of the day based on static SQL in the function. In a sense, that can still be considered DETERMINISTIC if you query the same set of tables and columns every time, given the same set of parameters.

What if you could change the underlying tables of a function via Dynamic SQL? You be violating the definition of a DETERMINISTIC function.

Notice that MySQL added this option in /etc/my.cnf

log-bin-trust-function-creators

Although this may be an oversimplification to say, this allows functions to be allowed to write data into binary logs without strictly enforcing the DETERMINISTIC property.

ASPECT #2 : Triggers should be able to be rolled back

Could you imagine a trigger with all the same behaviors as a function and then introducing Dynamic SQL into the mix?
Could you imagine trying to apply MVCC (Multiversion Concurrecy Control) against Dynamic SQL after applying MVCC to the base table the trigger was meant for?

You would essentially have data that grows quadratically (even exponentially) just in MVCC alone. The process of managing the rollback of SQL with triggers that can be non-DETERMINISTIC would be ungodly complex, to say the least.

In light of these two aspects, I'm sure MySQL Developers thought of these things and quickly dismissed them by imposing restrictions.

So, why lift the restriction for Procedures? Simply put, there is no concern over DETERMINISTIC properties or Rollback.


Impact on database while increasing database initial size I have a database whose initial size is 900 GB for .mdf file and 13 GB for log file, I want to increase its initial size to + 100 GB , while doing that in the properties -> filegroup->initial size, do I require a SQL Server restart or downtime after applying the changes?

Also while the size is getting increased, will it hold lock on all the reads & writes that is being done on the database?

I'm doing this as I am planning to pre-allocate size to my database instead of relying upon autogrowth. (I have got general suggestions from this site where pre growing the size of the database is considered to be better than auto growth).

We don't have instant file initialization on. I am new to this dba work and I wasn't aware of the usefulness of this until now.

I want to increase its initial size to + 100 GB , while doing that in the properties -> filegroup->initial size, do I require a SQL Server restart or downtime after applying the changes?

No. Increasing initial size of the database should be transparent and does
not require a service restart.

If your database is highly active, suggest you to do this during less active time.

Below are my recommendations :

Enable auto growth to be in MB as opposed to a % increase.
Enable Instant File Initialization, this prevents the writing of zeroes prior to space allocation. Which means when auto growth or when you have to increase size of data files, it's extremely fast.

Refer to my answer for : Database defragmentation and autogrowth settings


Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.

SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM employee LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

The employee table might contain large number of rows. I want to fetch only some employees at a time. For example I want to fetch 3 employees with their phone numbers. I am trying to run this query.

SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS employee
LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

But I get this error. ERROR:  column "employee.empname" must appear in the GROUP BY clause or be used in an aggregate function
The only difference between two queries is that I am using sub query in the latter to limit the rows before joining. How do I solve this error?

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case).
You can add the columns you want in the SELECT into the GROUP BY clause:
SELECT e.empname, array_agg(p.phonenumber) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT OUTER JOIN phones AS p ON e.empid = p.empid
GROUP BY e.empid, e.empname
ORDER BY e.empname ;

or use a subquery (and transfer the GROUP BY there):
SELECT e.empname,
       (SELECT array_agg(p.phonenumber)
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
ORDER BY e.empname ;

which could also be written as:
SELECT e.empname,
       (SELECT array_agg(p.phonenumber)
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers
FROM employee AS e
ORDER BY e.empname LIMIT 3 OFFSET 0 ;

Since you are in version 9.3+. you can also use a LATERAL join:
SELECT e.empname,
       p.phonenumbers
FROM
   (SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT JOIN LATERAL
   (SELECT array_agg(phonenumber) AS phonenumbers
    FROM phones
    WHERE e.empid = phones.empid
   ) AS p ON TRUE
ORDER BY e.empname ;


Populate a column using the values of other ones I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the first eight columns and updated with a trigger as I insert data to a new line for the first rows, maintaining the Area, x, and y columns with their values. As I show in the attached image.

So, the "New Column" just not receives the values from Area, x and y.
Those three last columns have got the following codes that area working normally

Codes for Area Column:

CREATE OR REPLACE FUNCTION update_area()
  RETURNS TRIGGER AS
    $$
    BEGIN
      NEW.area := round(ST_Area(NEW.geom):: numeric,2);
      RETURN NEW;
    END;
    $$
  LANGUAGE plpgsql;

the trigger of that:

CREATE TRIGGER update_area
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_area();

Code for the x and y columns:

ALTER TABLE "EDIFICACAO_4" ADD X double precision;
ALTER TABLE "EDIFICACAO_4" ADD y double precision;
UPDATE "EDIFICACAO_4" SET X = ST_X(ST_Centroid(geom));
UPDATE "EDIFICACAO_4" SET Y = ST_Y(ST_Centroid(geom));

The trigger of that:

CREATE OR REPLACE FUNCTION update_en()
  RETURNS TRIGGER AS
    $$
    BEGIN

      NEW.X := round(ST_X(ST_Centroid(NEW.geom))::numeric,3);
      NEW.Y := round(ST_Y(ST_Centroid(NEW.geom))::numeric,3);
      RETURN NEW;

    END;
    $$
  LANGUAGE plpgsql;

CREATE TRIGGER update_en
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_en();

Just create a single function that handles all columns you want to update. Essentially the same as you already do with X and Y:

CREATE OR REPLACE FUNCTION update_edificacao()
  RETURNS TRIGGER AS
    $$
    BEGIN

      NEW.X := round(ST_X(ST_Centroid(NEW.geom))::numeric,3);
      NEW.Y := round(ST_Y(ST_Centroid(NEW.geom))::numeric,3);
      NEW.area := round(ST_Area(NEW.geom):: numeric,2);

      -- the following was taken from your other question
      NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU");

      -- if you have more columns to deal with, add them here
      NEW.some_other_column := ....;
      NEW.yet_another_column := ....;

      RETURN NEW;

    END;
    $$
  LANGUAGE plpgsql;

Then create one trigger that calls the function that handles all columns:

CREATE TRIGGER update_en
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_edificacao();

And drop all other trigger functions and triggers.


MS SQL Server autogrow configuration with heavy loaded database I have a database its size around 200G and it is heavy loaded with many inserts happens every day (it is grow around 3 to 4 GB every day), but the Auto-Grow setting is set to 1 MB.

I think this is a problem and I think if I set it to 1GB it will increase the performance and capability, is that right?

I think with 1MB auto-grow the database will be always busy to allocate a new space, is that right ?
Any suggestion for best practical way to increase the performance?

Thanks all

1 MB is not the right choice for autogrowth !

You should always set auto growth to a sensible value that suits your environment. I tend to capture the autgrowth value for a month and then average it out to give me a ball park figure of autogrowth for my database .

I have provided a sample script here as well. Also refer to Mike Walsh's answer.

The default trace keeps track of AUTOGROWTH events out-of-the box. Below script will give you a good starting point.

Also, Instant file initialization is a boon when auto growth kicks in (ONLY applies to data files).

IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
  ,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.[FileName] as LogicalFileName
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
,mf.physical_name AS PhysicalFileName
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
join sys.master_files mf on (mf.database_id = ftg.databaseid) and (mf.name = ftg.[FileName])
WHERE (ftg.EventClass = 92  -- Data File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

select count(1) as NoOfTimesEventFired
        , CONVERT(VARCHAR(10), StartTime, 120) as StartTime
        , EventName
        , DatabaseName
        , [LogicalFileName]
        , PhysicalFileName
        , SUM(GrowthMB) as TotalGrowthMB
        , SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [LogicalFileName], PhysicalFileName
--having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
order by CONVERT(VARCHAR(10), StartTime, 120)

select * from #autogrowthTotal_Final

Original Script from SimpleTalk.com (modified a bit to help me for reporting).


Can't connect to SQL Server DB from SSMS network error Here some background on the setup. The app server and the db server are on a workgroup no domain. Both are AWS EC2 instances. A partner went and install an evaluation copy of SQL Server 2014. I apply the licence key (SQL Server 2014 Standard) on the instance and run the upgrade wizard since then nothing is working as it should. Before the expired date you could connect to the SQL server from the app server using the SSMS. Now getting an network error 18456.

Firewalls are disabled
Under the SQL Configuration manager TCP/IP have been enable.
Can telnet form the APP server to the SQL server on port 1433 and 1434
Can login with my sql user using SSMS on the SQL server it self

I had a look at these websites as will but still no luck.
https://logicalread.com/errors-sql-server-login-failures-pd01/#.WsNZsIhuZEY
http://sqltechtips.blogspot.co.za/2018/03/sql-server-login-failed-error-18456.html

Sorry I am not an DB admin. Any help will be greatly appreciated
Gerrit

As you said that the SQL Server Error code : 18456 is coming with state: 8.

Mostly the Error code : 18456 with state : 8 coming due to user authentication.

As per MSDN Blog here the key to the message is the 'State' which the server will accurately set to reflect the source of the problem.

For Troubleshooting Error 18456

As SQLBlog of @AaronBertrand here ,  you can find out every error details with description , based on their state with Error code: 18456.

After Edit

As SQLSERVERLEARNER Blog here  the

  Error Number: 18456 Severity: 14 State: 1

may have so many reasons.

Reasons for this error:

User does not have access on the server
Provided user name is wrong
Provided Password is Wrong
Using windows login credentials for SQL Server authentication
Login for the user is disabled
Login was successful but access to the server failed
Problems with SQL Service
Password for the user has to be changed
User does not have access to perform the specified acton on the
server, Only has limited access
User does not have previlages to access the specific database,
 agent service etc
Even here MSDN BOL here
 mention that for State : 1 , Error information is not available.
 This state usually means you do not have permission to receive the
 error details. Contact your SQL Server administrator for more
 information.
As here
 State 1 now occurs when a login is disabled – but actually, the
 error in the log is 18470, not 18456 – because the login is
 disabled, it doesn't get that far.  See state 7. Prior to SQL
 Server 2005, State 1 always appeared in the log for all login
 failures, making for fun troubleshooting. The login is disabled
 and the password is incorrect.  This shows that password validation occurs first, since if the password is correct and the
 login is disabled, you get error 18470 (see state 1 above). It's
 possible that your application is sending cached credentials and
 the password has been changed or reset in the meantime – you may
 try logging out and logging back in to refresh these credentials.

For further your ref here and here


How do I query for fields that have AT LEAST a given range? Say I have a table of the form:

+-----+-------+
| Key | Value |
+-----+-------+
| A   |     1 |
| A   |     3 |
| A   |     4 |
| B   |     2 |
| B   |     3 |
| C   |     3 |
| C   |     4 |
| C   |     5 |
+-----+-------+

I want to be able to return all of the keys that have at least a given range.

For example:

A query for a Value range of 2 would return A, B, C
A query for a Value range of 3 would return A, C
A query for a Value range of 4 would return A

Can this be done with SQL (specifically SQLite)? If so, how?

To find those with a "range" of at least 4 for example

SELECT "Key"
FROM   YourTable
GROUP  BY "Key"
HAVING (1 + MAX("Value") - MIN ("Value")) >= 4;

Obviously alter the 4 as appropriate for the other cases.

SQL Fiddle


Listener.ora Location What is the location of the Listener.Ora file in Windows OS?
I need to edit  that file and I am not able to locate where it is.

You will find listener.ora in here:

  %ORACLE_HOME%/network/admin

The usual problem is that %ORACLE_HOME% isn't set as a system variable and not exposed to a command shell.

My quickest way to locate ORACLE_HOME from the command shell is:

C:\>where sqlplus
The full path to the executable will show. Go there.
Go one up, and add /network/admin

Another approach is to look in the registry.

Watch out for variable TNS_ADMIN which overrides Oracle default network directory.


MySQL error: Access denied for user 'a'@'localhost' (using password: YES) I use the root account created the account 'a'@'%'. But I can't use the account to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. Please see the transcript below. I hope someone can help me to explain it. Thanks.

mysql> grant all on *.* to 'a'@'%' identified by a;
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 'a' at line 1
mysql> grant all on *.* to 'a'@'%' identified by 'a';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'a'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for a@%                                                                                |
+-----------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'a'@'%' IDENTIFIED BY PASSWORD '*667F407DE7C6AD07358FA38DAED7828A72014B4E' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@localhost ~]# mysql -h localhost -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -h 127.0.0.1 -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u a
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.5.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.17, for Linux (x86_64) using readline 5.1

Connection id:      20
Current database:
Current user:       a@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.17 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         15 days 15 hours 20 min 18 sec

Threads: 1  Questions: 40  Slow queries: 0  Opens: 41  Flush tables: 1  Open tables: 4  Queries per second avg: 0.000
--------------

mysql>

Edit:

Yes, MySQL is listening on port 3306.

[root@localhost ~]# nmap localhost

Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2012-01-18 07:35 CST
Interesting ports on localhost.localdomain (127.0.0.1):
Not shown: 1674 closed ports
PORT     STATE SERVICE
22/tcp   open  ssh
25/tcp   open  smtp
111/tcp  open  rpcbind
631/tcp  open  ipp
840/tcp  open  unknown
3306/tcp open  mysql

Nmap finished: 1 IP address (1 host up) scanned in 0.064 seconds
[root@localhost ~]#

Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

Sometimes, USER() and CURRENT_USER() are different. That's because mysql authentication follows a specfic protocol.

According to MySQL 5.0 Certification Study Guide

pages 486,487 state the following on mysql's authentication algorithm:

  There are two stages of client access control:

  In the first stage, a client attempts to connect and the server either
  accepts or rejects the connection. For the attempt to succeed, some
  entry in the user table must match the host from which the client
  connects, the username, and the password.

  In the second stage (which occurs only if a client has already
  connected sucessfully), the server checks every query it receives from
  the client to see whether the client has sufficient privileges to
  execute it.

  The server matches a client against entries in the grant tables based
  on the host from which the client connects and the user the client
  provides. However, it's possible for more than one record to match:

  Host values in grant tables may be specified as patterns contains
  wildcard values. If a grant table contains entries from
  myhost.example.com, %.example.com, %.com, and %, all of them
  match a client who connects from myhost.example.com.

  Patterns are not allowed for the User values in grant table entries,
  but a username may be given as an empty string to specify an anonymous
  user. The empty string matches any username and thus effectively acts
  as a wildcard.

  When the Host and the User values in more than one user table record
  match a client, the server must decide which one to use. It does this
  by sorting records with the most specific Host and User column values
  first, and choosing the matching record that occurs first in the
  sorted list, Sorting take place as follows:

  In the Host Column, literal values such as localhost, 127.0.0.1,
  and myhost.example.com sort ahead of values such as %.example.com
  that have pattern characters in them. Pattern values are sorted
  according to how specific they are. For example, %.example.com is
  more specific than %.com, which is more specific than %.

  In the User column, non-blank usernames sort ahead of blank usernames.
  That is, non-anonymous users sort ahead of anonymous users.

  The server performs this sorting when it starts. It reads the grant
  tables into memory, sorts them, and uses the  in-memory copies for
  access control.

From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.

With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands

SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;

Make sure that

every user has a password.
there are no anonymous users (when user is blank)

This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.

Run this query:

SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';

If you get back a row with no password, that fully explains why mysq -u a works.

UPDATE 2012-01-19 11:12 EDT

Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

This question is an excellent heads up about MySQL user authentication.

Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user 'dbuser' to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@'10.1.2.20') with a password like 'pass1' and that user to login remotely from anywhere (dbuser@'%') with a remote password like 'pass2'.

Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.

This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).


mysql restore specific date data from a dump file I have mysql [version 5.6] database dump file. I want to restore only one day data from that dump file.

Anyone has any clue ?

You have two alternatives:

1. Load the file into a new database table and do a query for the particular date that you want.

or

2. Hand edit the dump file and load that (also in a new database).

The second alternative has several disadvantages:

hand editing is unreliable and error prone.
you may break integrity constraints - not easy to see in a load of text!
it's a pain in the ass :-) and will probably take you more time than alternative 1.

Conclusion:

Load the whole file - query the data you want,

CREATE my_table AS SELECT * FROM main_table
WHERE the_date = date_of_interest;

and then put that into a separate table, and then:

mysqldump db_name my_table > my_table.sql

Et voilà!


Number of locks acquired during query execution I execute a statement in SSMS like:

select Ident
from PART_Stamm
where VerketteterArtikelID = 'D9FA5CEC-E3C5-11E6-B088-F079596E5F58'

There I can show the execution plan. This works perfectly. Now I need to also show the number of locks acquired on table PART_Stamm during the execution of the query above. How can I get this information?

This is the perfect use for extended events.

-- replace Session_id with your spid
CREATE EVENT SESSION [Specific_Locks] ON SERVER
ADD EVENT sqlserver.lock_acquired(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_escalation(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_released(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_timeout_greater_than_0(
    WHERE ([sqlserver].[session_id]=(55)))
ADD TARGET package0.event_file(SET filename=N'Specific_Locks',max_file_size=(100))
GO

ALTER EVENT SESSION [Specific_Locks] ON SERVER STATE=START
GO

-- run your query

ALTER EVENT SESSION [Specific_Locks] ON SERVER STATE=STOP
GO

This will give you everything your session does, however, you can filter on the object ids for the table and indexes to make it more readable. I included acquired, released, timeout, and escalation as I don't know your environment.

If you want to look at the captured data, there are two built in ways without using outside programs.

Use sys.fn_xe_file_target_read_file() with proper supplied arguments
Use SMSS to open the file target for you and give you a semi-intelligible display (picture below)


How to prevent LCK_M_IX waiting/locking on the following Delete and Insert queries *Edit: Solution turned out to be nothing in the answers here, I was using SQLAlchemy in Python, which is an ORM. I was executing the delete statement below in a transaction, but never committing it. This lead to around 10 open transactions occuring that eventually all needed to be rolled back, thus locking the entire table until rollback was complete.

Given the following table structure, records are being continuously inserted with the update_time of a very recent datetime. A separate Connection to the DB is periodically trimming old records with dates of older than 2 weeks.

Table Structure:

The following Insert is being run very frequently, with different values:

The following delete statement is repeated until required, running a select right after to see if the process is complete

delete top(5000) from trade_options with (READPAST) where update_time < '<Two Weeks Ago>'

Activity Monitor shows that the Delete is locking, and the Insert is waiting ( LCK_M_IX):

Thanks

**Edit: Here are the Indexes/Keys as script outputs

CREATE NONCLUSTERED INDEX [IX_order_option] ON [dbo].[order_option]
(
    [update_time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[order_option] ADD PRIMARY KEY CLUSTERED
(
    [exchange_id] ASC,
    [symbol] ASC,
    [update_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Your problem has a name of lock escalation. You can read more on this here: How to resolve blocking problems that are caused by lock escalation in SQL Server.

  By default, SQL Server will acquire the finest-grain lock possible, in
  order to attain the  greatest concurrency. In most cases, this means
  SQL Server will acquire row (RID or  KEY) locks. SQL Server can
  acquire hundreds or thousands of individual locks on data  in a single
  table without causing any problems. In some cases, however, if SQL
  Server  determines that a query will access a range of rows within a
  clustered index, it may instead acquire page locks. After all, if
  every row on a page is going to be accessed, it's  easier to manage a
  single page lock than dozens, or hundreds, of row locks. In other
  cases,  primarily when there is no usable index to help process a
  query, SQL Server may lock an  entire table right at the beginning of
  processing a query.

In your case I suspect there are some indexes on the table (this means that delete acquires locks on them too) or you do your deletes in one transaction (this means that all the locks are held until the end of the transaction). In this case this happens:

  SQL Server  will escalate locks when any individual session acquires
  more than 5,000 locks in a  single statement. In this case, there is
  no randomness in choosing which session will get  its locks escalated;
  it is the session that acquired the locks.

So you should get rid of the wrapping transaction if it exists, or decrease batch size to a smaller one, or disable lock escatation on this table:

alter table order_option
set ( lock_escalation = disable )

  Do you think lowering the delete batch to less than 5000, or removing
  the index would help? In the case of removing the index, wouldn't that
  slow the delete considerably?

You should not drop this index, it helps you with your delete.

You can try batches of 2000 rows, it should help.

I don't know the kind of queries that run against this table but it could make sense to have clustered index on update_time and PK as nonclustered


What is the purpose of PGBouncer's parameter server_reset_query? Recently I started learning about pgbouncer, but there are some things I do not understand about the parameter server_reset_query.

Question 1: What is the main purpose of server_reset_query?

Question 2: There are three modes of the pool_mode of pgbouncer: session ,transaction  and
            statement  mode. When the pool_mode is session, we usually set the values of
            server_reset_query to 'DISCARD ALL'. When the pool_mode is transaction
            we usually set the values of server_reset_query to ''，

I do not know the details about this, so can anybody explain this?

Since pgbouncer is a connection pooler, it will reuse one actual connection to the database server for potentially many client connections.  It has to make sure that whatever session state the first client created is reset when the next client gets the connection.  Otherwise the first client might do something like SET statement_timeout = '5min', and that would apply to all subsequent clients who happen to be assigned that connection.  To avoid that, pgbouncer issues the "server reset query" before handing out the server connection to a new client.  As the documentation says

  A good choice for Postgres 8.2 and below is:

  server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;

  for 8.3 and above its enough to do:

  server_reset_query = DISCARD ALL;

The main reason this setting exists is that the DISCARD ALL statement, which was invented for this very purpose, was not available before PostgreSQL 8.3.

The setting applies independently of the pool mode.  Note that the client could issue a SET statement or something like that in any pool mode.

The documentation also says

  When transaction pooling is used, the server_reset_query should be empty, as clients should not use any session features.

I think this is a faith-based approach.  If you think your code is clean and you need the extra performance, go for it.  I've been bitten by this, though.  For example, if you use check_postgres to monitor your pgbouncer connections, it will issue a SET statement_timeout like I showed above and mess up your connections.


Does varchar(n) affect the number of records which can be stored per page even if you store less than n? I'm struggling to find enough documentation to help me answer my own question.

The common story seems to be that it doesn't make a difference to actual database size when you use a much higher value for n than will ever be used.

As an extreme example:
VARCHAR(5000) but only storing on average a small number of characters such as 4 characters.

That seems to be correct from a few tests I've run.

I've heard people saying that MySQL will take the higher value into account when managing pages meaning a potential for less records per page and that this can affect performance when running queries.

I can't see anything in the documentation to suggest this is actually the case so really looking for some clarity from a more experienced DBA.

So thanks in advance for any help. Please let me know if my question needs to be clearer.  My question is, does using a higher value for varchar(n) than will be stored affect the number of records which can be stored per page even if you store less than n?

I'm pretty new to this so if possible I'd also really appreciate a steer on how I can determine how many records are included in pages and then run some benchmarks.

I can only speculate what the people meant. Maybe it's related to how MySQL computes index statistics depending on n. The only case that comes to my mind is whether n is higher than 127 or not.

As you may know InnoDB stores a string with its length. The length is stored in so called offset fields. The offset can be either one or two bytes. In REDUNDANT format if the maximum length is less than 128 bytes the offset field will be one byte. Otherwise - two bytes.
In COMPACT and DYNAMIC format the actual field length is taken into account. So, if your field is VARCHAR(5000), but you store a short string like abc the offset field will be one byte.

@jkavalik made a good point about MEMORY tables. VARCHAR(5000) is effectively TEXT i.e. it would need off-index pages to store long values.
If a SELECT query ever needs a temporary table it will be of MEMORY type. Because it doesn't support variable length types it's going to be CHAR(5000). So even if you store only short values the temporary table will be stored on disk. It will be a big performance hit.


MySQL Detecting cheaters I have This table :

    SET NAMES utf8;
SET time_zone = '+00:00';

CREATE TABLE `app_log` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `serial` int(11) NOT NULL,
  `dataEntryIP` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dataEntryUA` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `domain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `timeStamp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `serial` (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=4328 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample of the possible data would be like :

INSERT INTO `app_log` (`ID`, `serial`, `dataEntryIP`, `dataEntryUA`, `domain`, `timeStamp`) VALUES
(6,33605,'2.5.29.18','Google Chrome','www.domain1.com','2017-08-18 18:44:23'),
(7,33606,'8.102.167.213','Google Chrome','domain1.com','2017-08-18 18:45:48'),
(8,33607,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 18:58:13'),
(9,33608,'8.24.10.14','Google Chrome','domain0.com','2017-08-18 18:58:13'),
(10,33609,'8.24.10.14','Google Chrome','domain1.com','2017-08-18 19:06:44'),
(11,33610,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 19:06:44'),
(12,33611,'7.10.14.11','Google Chrome','domain2.com','2017-08-18 19:12:48'),
(13,33612,'7.10.14.11','Google Chrome','www.domain2.com','2017-08-18 19:12:48'),
(14,33613,'9.45.21.18','Google Chrome','domain3.com','2017-08-18 19:18:10');

Now I am in need of selecting any IP which have used more than one domain, for this sample, it is : 8.24.10.14 because it have used both domains : domain0 and domain1

How would I do this ??

Thank you very much

You can use this query:

SELECT dataEntryIP FROM app_log
GROUP BY dataEntryIP
HAVING COUNT(*) > 1

Be careful above query doesn't use any index.

If your table will be grow, index the dataEntryIP column

ALTER TABLE `app_log`
ADD INDEX `dataEntryIP` (`dataEntryIP`);

Update 1

Oh sorry I did not understand your question.

I suggest before you insert the data into app_log remove www. prefix to unify domains (www.domain1.com is equal to domain1.com).

But for now I think it can help:

SELECT dataEntryIP from (
SELECT replace(domain, 'www.', '') as domain_without_www ,dataEntryIP from
app_log
GROUP BY domain_without_www,dataEntryIP ) t1
GROUP BY dataEntryIP
HAVING COUNT(*) > 1

But It is very slow, if your table is big.

if you can remove www. before insert to table, your query will be easier and faster.


To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)? Premise: I ship a Java application which will make a JDBC connection to a MySQL database. (At the time of this writing it could be 5.1 or 5.5, but hopefully the question and answer don't rely heavily on the version.)

Observation: Clearly, I can use the the MySQL JDBC driver (Connector/J) or the MariaDB JDBC driver (MariaDB Java Client) to make the connection to the MySQL server. One huge benefit from the standpoint of an ISV is that the MariaDB driver is LGPL licensed while the MySQL driver is GPL licensed. Performance of the MariaDB driver seems very good.

Question: To what extent is the MariaDB JDBC driver a perfect replacement for the MySQL JDBC driver?

I'm not looking for a debate on the relative merits of the two. But rather I'm hoping someone can answer along the lines of one of these:

The MariaDB JDBC driver has important issues with X and Y, so you'll have troubles if you use either of these.
Based on some article or specification or code or other evidence, the MariaDB JDBC driver fully implements [at least] all methods available in the MySQL driver. You'll be fine.

I'm getting a bit nervous.

First, the upside: I've used the MariaDB JDBC driver in an "always on" service in production with a database connection pool and it is running fine for a couple of months now. The service only uses basic JDBC functions with simple queries (e.g. no joins, no blobs). I'm about to release another "always on" service that uses the MariaDB JDBC driver in a similar fashion.
I decided to use the MariaDB JDBC driver after (re)viewing the source code: compared to the Oracle MySQL JDBC driver source code the the MariaDB JDBC driver source code is a lot more readable and understandable.

But I get the impression (9/2014) development has slowed down (link is to a graph showing bugs created vs. resolved). If I browse through the open bugs I see many serious bugs open for far too long (issues with Hibernate and also time-zones/dates). Some of these may not be bugs but then they should have been closed by now. I also encountered one bug (via STASH-4012) that should be re-opened: bug CONJ-72 is closed but the last comment (from 4/2014) indicates the problem is not resolved for Hibernate.

All in all I do not consider the MariaDB JDBC driver a perfect replacement for the Oracle MySQL JDBC driver. I'm happy to continue to use it for the simple stuff (where all queries are known beforehand and tested) for now, but I might have to switch back to the Oracle MySQL JDBC driver in the future.


Is it possible to rebuild a dropped index? Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?

I agree that "correct answer" isn't correct.

You can't rebuild indexes that have been dropped, you would need to create them again, and it would make no sense to do the insert last after the indexes have been dropped and created anyway.

I presume the correct answer is in fact something like

Disable the non clustered indexes.
Do the Insert
Rebuild the non clustered indexes.

As this is a heap this could be implemented with

ALTER INDEX ALL ON OrdersHistory DISABLE
INSERT ...
ALTER INDEX ALL ON OrdersHistory REBUILD

Which has zero impact on existing permissions, and low administrative effort compared with other possible alternatives such as the solution you propose of dropping and recreating all indexes.