MySQL 5.1.63 - using tuning-primer - Table Cache question tuning_primer says the following:

TABLE CACHE
Current table_open_cache = 812 tables
Current table_definition_cache = 256 tables
You have a total of 498 tables
You have 812 open tables.
Current table_cache hit rate is 1%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

I don't understand this.
I only have 498 tables, however my.cnf shows table_cache   = 812.

If I have only 498 tables, should I not DECREASE my table_cache value?

The table_definition_cache is set to its default of 256.  Should this be increased alsO?

Show status:

|Open_table_definitions            | 498         |
|Open_tables                       | 812         |
|Opened_files                      | 291680      |
|Opened_table_definitions          | 0           |
|Opened_tables                     | 0           |

We are running everything with InnoDB.
Any help on this would be appreciated.

A single "open table" instance means different things in different storage engines, but you can have more "open tables" than you actually have... tables.

Tables that are "open" were used at some point since the server was restarted, yet may not have been used for days or hours ... but there's no point in MySQL going around looking for something to do, closing tables that haven't been used in a while, because it will eventually just have to open them again... unless, of course, we want to proactively avoid unnecessary consumption of system resources... and how aggressively it closes idle tables is the gist of the purpose of table_open_cache.  Too small, and you end up opening and closing the same tables over and over.  Too large, and you demand more file descriptors (and probably small amounts of additional memory) from your operating system than your workload justifies.

What you're seeing here with tuning-primer is partially explained by what we might loosely refer to as the observer effect -- changing what we're measuring, by the act of measuring.

The "tuning-primer" script does things to your server that can alter the subsequent values that the script uses to draw its conclusions... As just one example, it queries information_schema.TABLES while gathering InnoDB stats.  What does that do?  It opens tables.  How many tables does it open?  Well... how many have ya got? :)  (It may not actually open all of them, depending on some under-the-hood details in the implementation_schema, but it at least opens all of your InnoDB tables).

Depending on the number of tables you have and the level of traffic on your server (such as how many tables were open when the script was started), this script can completely fill the open table cache and then incorrectly report that the cache is 100% used.

To see this, flush the tables on your server and then run only the "file" test from tuning-primer.  Better still, flush the tables, wait a while, and then run the "file" test again.

Production Environment Disclaimer: FLUSH TABLES should be a harmless operation -- it "closes" all of the tables as soon as any running queries are done, and they will be immediately re-opened by the next query that needs them, and, if you're primarily InnoDB, this is generally almost instantaneous... but on a busy server or with a lot of MyISAM it could take a few seconds, during which your performance may dip slightly.

mysql> FLUSH TABLES;
shell> ./tuning-primer.sh file

For one of my servers, I get this:

You have 8 open tables.
The table_cache value seems to be fine

An hour later, running it again (without flushing the tables) I get this:

You have 40 open tables.
The table_cache value seems to be fine

But if I do this (with or without a FLUSH TABLES before it):

./tuning-primer.sh innodb

...and then this...

./tuning-primer.sh file

You have 512 open tables.
You should probably increase your table_cache

Not the same answer, and not a meaningful one either, because -- would you believe -- that's exactly the limitation imposed by my table_open_cache variable.

My final thought: the best "tuning strategy" from my experience is to tune as few parameters as you can, as seldom as you can, in MySQL.  Aside from the obvious values like innodb_buffer_pool_size, most parameters are best left alone unless a specific reason can be identified why that particular parameter has a value that's not appropriate for your environment.

The developers of this script obviously put a lot of work into it, so it's not my intention to be critical of it.  But just like any tool that tries to make informed decisions based on observing a complex system, its conclusions should not be taken as authoritative without corroboration and investigation.


SQL Server VARCHAR Column Width Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do.

I consistently see agreement that there's a performance hit if the entire row exceeds 8060 bytes.  Other than that, I see disagreement.

Is the claim true that The default is SET ANSI PADDING ON = potential for lots of trailing spaces?  As long as the total row width is less than 8060, are there any real performance concerns in over-sizing VARCHAR columns?

Evidence that column width matters

The same goes for CHAR and VARCHAR data types. Don’t specify more characters in character columns that you need.

http://www.sql-server-performance.com/2007/datatypes/

Length is a constraint on the data (like CHECK, FK, NULL etc)
Performance when the row exceeds 8060 bytes
Can not have unique constraint or index (key column width must be < 900)
The default is SET ANSI PADDING ON = potential for lots of trailing spaces

What are the consequences of setting varchar(8000)?

Evidence that column width DOES NOT matter

If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.

https://stackoverflow.com/questions/7025996/overstating-field-size-in-database-design

The varchar datatype, by contrast, consumes only the amount of
actual space used plus 2 bytes for overhead

http://sqlfool.com/content/PerformanceConsiderationsOfDataTypes.pdf

The question might be better stated as:

  "What is the advantage of over-specifying the maximum length of a variable-length column?"

In general, there is little advantage, and several disadvantages as the various linked answers point out.  Aside from any other concerns, consider that SQL Server is not open-source: there are many 'magic numbers' and heuristics applied based on the information provided to the system.  Without source code access, we could never be entirely sure what the impact of this practice might be.

In some cases, where the average length of a column is significantly higher than the 50% assumed by SQL Server when calculating sort/hash memory grants, you may see a performance improvement by over-specifying the maximum length.  This is a dubious workaround, and should probably only be applied by an explicit CAST or CONVERT (with comments!) rather than changing the base column definition.  Sometimes, it will be preferable to rewrite the query to sort keys instead of whole rows anyway.

Where the maximum row size might exceed the in-row limit (even if no rows actually do), deleting rows can cause unexpected page-splitting if a trigger is present.  Updates may also lead to fragmentation via the same mechanism.

SQL Server does a pretty good job in most cases where it is provided with good, accurate information from metadata.  Compromising this principle for 'convenience' seems unwise to me.  A sensible approach is to choose a maximum length value that is reasonable according to the actual data to be stored, and any foreseeable changes.


Getting Master Current Binlog and Binlog Position in Replication Backup I want to take the current binlog file and binlog position of the master server in a mysqldump on the slave server. I cannot use master-data=2 on slave - how can I do this nevertheless?

Surprise, you can get the Master's binlog file/position from the dump !!! Use --dump-slave.

MySQL 5.6 Documentation says :

  This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.

Just get the CHANGE MASTER TO command from Lines 23-25 of the dump as always.


SQL Query - Set Date Variable by Table/Column Reference How do I set the @DATE variable by using a table/column reference?
Right now I've set it manually as you can see below with the opening:
SET @DATE = '2012-03-31 01:00:00';

The table from which I would pull the date variable is not the table from which the SELECT statement is pulling.
SQL query mockup:
SET @DATE = '2012-03-31 01:00:00';

SELECT
  Column 1
FROM
  Table
WHERE
  table.column = @DATE

If I understand your question correctly, there are two ways that I can think of:

mysql> SET @date = (SELECT source_col FROM source_table WHERE whatever_condition);
Query OK, 1 row affected (0.03 sec)

or

mysql> SELECT source_col FROM source_table WHERE whatever_condition INTO @date;
Query OK, 1 row affected (0.03 sec)

These two are functionally equivalent when only one value is involved, but the second form can also be expanded to handle multiple values.

mysql> SELECT col1, col2 FROM source_tbl WHERE whatever_condition INTO @var1, @var2;

But your question involved using the resulting @date in your query.

mysql> SELECT *
         FROM target_table
        WHERE target_column = @date;

If that's the intended purpose, you can eliminate the variable entirely and use the first example, above, as a subquery inside the final query.

mysql> SELECT *
         FROM target_table
        WHERE target_column = (SELECT source_col FROM source_table
                                WHERE whatever_condition);

http://dev.mysql.com/doc/refman/5.5/en/comparisons-using-subqueries.html


Select MaxDate where hh:mm:ss makes the difference How to select the maximum date from a table where hh:mm:ss is selected?

My query is the following, and returns the latest date, but if there are different times recorded, the date will be returned twice. What I want is to get the absolute maxDate.

   SELECT * FROM [MyDatabase].[dbo].[DogTags] t1
     JOIN
    OPENQUERY(LinkedServer, 'SELECT [TimeStamp]
    ,[Name]
    ,[Owner]
    ,[Address]
     FROM PETS
 WHERE TimeStamp IN (SELECT MAX(TimeStamp) FROM [LinkedServer].[PETS]
    GROUP BY [Name])')t2
    ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

This query returns the following table:

TAGS|TimeStamp              | Name  |Owner | Address    |
ROLF|2010-03-03 16:59:01.190| ROLF  |OLSEN | Juniper St.|
ROLF|2010-03-03 19:40:01.182| ROLF  |OLSEN | Juniper St.|
BLAD|2012-05-01 14:35:11.182| BLAD  |JOHN  | Maple St.  |

The records I want is:

TAGS|TimeStamp              | Name  |Owner | Address    |
ROLF|2010-03-03 19:40:01.182| ROLF  |OLSEN | Juniper St.|
BLAD|2012-05-01 14:35:11.182| BLAD  |JOHN  | Maple St.  |

Does anyone knows the solution how to filter the records to max time?

Thanks for the help.

In the OPENQUERY, you could get the max timestamp for all the dogs at once, and then join to the original PETS table to get the other columns, like so:

SELECT *
FROM [MyDatabase].[dbo].[DogTags] t1
JOIN OPENQUERY(ROPSA,
    'SELECT [TimeStamp], [Name], [Owner], [Address]
     FROM [CustomerDB].[PETS] t3
     JOIN (SELECT [Name], MAX(TimeStamp) AS TimeStamp
           FROM [CustomerDB].[PETS]
           GROUP BY [Name]) t4
     ON t3.[Name]=t4.[Name] AND t3.TimeStamp=t4.TimeStamp') t2
ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

EDIT: Needed to add AS TimeStamp in the subquery.

EDIT: Needed to specify the database everytime in OPENQUERY.


How can I return multiple rows of records in PL/pgSQL I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD.

that is, I want to append to rec so that rec becomes a set of rows when the loop is over, which I can just RETURN at the end of my function.
Currently, I am doing this -

SELECT temp_table.col1, temp_table.col2, temp_table.col3
      INTO rec
      FROM temp_table
      WHERE temp_table.col3 = false;

my full code is here:

CREATE OR REPLACE FUNCTION validation()
  RETURNS RECORD AS $$
DECLARE
        rec RECORD;
        temp_row RECORD;
BEGIN

  CREATE TEMPORARY TABLE temp_table (col1 TEXT, col2 INTEGER, col3 BOOLEAN) ON COMMIT DROP;

  FOR temp_row IN SELECT * FROM staging.validation
  LOOP

    RAISE NOTICE 'sql: %', temp_row.sql;

    EXECUTE format('INSERT INTO temp_table %s', temp_row.sql);

    IF (SELECT DISTINCT temp_table.col3 FROM temp_table WHERE temp_table.col3 = false)=false THEN
      RAISE NOTICE 'there is a false value';

      SELECT temp_table.col1, temp_table.col2, temp_table.col3
      INTO rec
      FROM temp_table
      WHERE temp_table.col3 = false;
    END IF;

  END LOOP;
  RETURN rec;
END; $$
LANGUAGE plpgsql;

Current output after SELECT validation();

validation
(crea_ddf,8095,f)

Desired Output

validation
(crea_ddf,8095,f)
(some_source_system,some_count,f)
(some_other_source_system,some_count,f)
(.....)

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in:

CREATE FUNCTION test() RETURNS SETOF RECORD AS $$
DECLARE
 rec record;
BEGIN
  select 1,2 into rec;
  return next rec;

  select 3,4 into rec;
  return next rec;
END $$ language plpgsql;

Caller:

=> select * from test() as x(a int ,b int) ;
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

Note that SQL being strongly and statically typed, the RECORD pseudo-type is hard to work with.
Often it's less cumbersome to use right from the start a composite type with a full definition of names and type for each column, either with the TABLE(...) syntax for an anonymous type or with CREATE TYPE for a persistent named type.


SSIS: Selecting multiple input columns and mapping them to their respective destination in one action Can selecting multiple available input columns and mapping them to their respective destination be done in one action!?!?! Instead of selecting 1 input at a time! I've tried holding control and the Shift key to select multiple ones and drag them over, but only the one you click on copies over. This is a huge time waster for tables that have multiple columns and for whatever reason SSIS doesn't automatically map them over, even with the columns names matching!  I'm in SSIS 2008 R2 BTW.
The image below is just an example to illustrate what I'm talking about, it is not my personal example:

(source: ggpht.com)

I found the solution, you can Right-click in the column names area on the diagram & Select "Map Items By Matching Names"

(source: hoegaerden.be)


Postgresql BDR problem compiling pluging I'm trying to build deb packages for postgresql BDR plugin, but I'm getting the following error:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -DLINUX_OOM_SCORE_ADJ=0 -fno-omit-frame-pointer -fpic -I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.4/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o pg_resetxlog.o pg_resetxlog.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -DLINUX_OOM_SCORE_ADJ=0 -fno-omit-frame-pointer -fpic pg_resetxlog.o -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5  -Wl,--as-needed  -L/usr/lib/x86_64-linux-gnu -lpgcommon -lpgport -L/usr/lib/x86_64-linux-gnu -lpq -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm  -o bdr_resetxlog
pg_resetxlog.o: In function `memcpy':
/usr/include/x86_64-linux-gnu/bits/string3.h:51: undefined reference to `pg_crc32_table'
pg_resetxlog.o: In function `RewriteControlFile':
/var/lib/postgresql/src/bdr-plugin/pg_resetxlog.c:955: undefined reference to `pg_crc32_table'
pg_resetxlog.o: In function `PrintControlValues':
/var/lib/postgresql/src/bdr-plugin/pg_resetxlog.c:749: undefined reference to `pg_crc32_table'
collect2: error: ld returned 1 exit status
make: *** [bdr_resetxlog] Error 1

To build this deb package I'm using the following commands:

cd ~/src/
git clone -b bdr-plugin/next git://git.postgresql.org/git/2ndquadrant_bdr.git bdr-plugin
git clone -b debian/bdr https://github.com/2ndQuadrant/bdr-packaging bdr-packaging
cp -rfv bdr-packaging/debian bdr-plugin/debian
cd bdr-plugin
dpkg-buildpackage -b -rfakeroot -us -uc

About the dependencies, all the postgresql-BDR server are installed, and all postgresql looks ok:

# dpkg -l | grep postgresq | grep dev
ii  postgresql-bdr-server-dev-9.4       9.4.5-2trusty                    amd64        development files for PostgreSQL-BDR 9.4 server-side programming
ii  postgresql-server-dev-9.1           9.1.21-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.1 server-side programming
ii  postgresql-server-dev-9.2           9.2.16-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.2 server-side programming
ii  postgresql-server-dev-9.3           9.3.12-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.3 server-side programming
ii  postgresql-server-dev-9.5           9.5.2-1.pgdg14.04+1              amd64        development files for PostgreSQL 9.5 server-side programming
ii  postgresql-server-dev-all           173.pgdg14.04+1                  all          extension build tool for multiple PostgreSQL versions

Any idea why I'm getting this error?

Regards

The cause of the issue is that Debian/Ubuntu upgraded postgresql-server-dev-all to require postgresql-server-dev-9.5 which in turns requires libpq-dev 9.5.

There were changes in libpq 9.5 that broke the patched versions of pg_resetxlog and pg_dump bundled in BDR.

BDR 1.0 adds compatibility changes to address this, but there's no real way around the issue that when Debian or Ubuntu or PGDG update libpq, it can break previously working packages.


MySQL insert position value I have trouble while inserting the values corresponding to a column: position. I have multiple inserts like this:

INSERT INTO category_product(id_category,id_product,position) VALUES(2,1,1);
INSERT INTO category_product(id_category,id_product,position) VALUES(2,2,2);
INSERT INTO category_product(id_category,id_product,position) VALUES(2,3,3);
INSERT INTO category_product(id_category,id_product,position) VALUES(3,1,0);
INSERT INTO category_product(id_category,id_product,position) VALUES(3,3,1);

But I would like to have something like an insert within a select, I've tried:

SET @position := 0;
INSERT INTO category_product(id_category,id_product,position)
(SELECT 2,id_product,@position+1 FROM product);

SET @position := -1;
INSERT INTO category_product(id_category,id_product,position)
(SELECT 3,id_product,@position+1 FROM product WHERE [a condition to get id_product 1, 3]);

My problem: it doesn't work like I want and my main reason for this is I'm using a loop to make all the inserts (using a Cursor) and they're just killing me with the time. Any suggestion would be great.

Create Auto Increment Position Column when you Create product TABLE

EXAMPLE

               Create Table category_product
               (
              id_category int,
              id_product int,
               position int Auto_Increment=1
               )

Then Simple Insert

    Insert into category_product
     select 2,id_product from Product


Accessing the OLD/NEW table value in a trigger function in plain SQL I am trying to build a trigger function to automatically update parent order values any time any of it's items changes.

This is what I have created:

CREATE OR REPLACE FUNCTION update_totals() RETURNS void AS $$
    UPDATE orders SET
       total_fees = (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id),
       total_profit = (CASE WHEN total_cost IS NOT NULL THEN total - total_tax - total_cost - (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id) ELSE NULL END)
    WHERE id = OLD.order_id;
$$
LANGUAGE SQL;

CREATE TRIGGER update_totals AFTER INSERT
OR UPDATE OF fees
ON order_items INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

When I try to run this, I get this error:

missing FROM-clause for table "old"

I tried creating a reference to the original table in the trigger:

CREATE TRIGGER update_totals AFTER INSERT
OR UPDATE OF fees
ON order_items
REFERENCING OLD ROW AS old_order
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

But that just gives the error syntax error at or near "REFERENCING".

How do I get the order_id from the original order_items table?

Firstly, to create a trigger on the order_items table, the update_totals() must be a trigger function which RETURNS TRIGGER (bold is mine).

  A data change trigger is declared as a function with no arguments and
  a return type of trigger. Note that the function must be declared with
  no arguments even if it expects to receive some arguments specified in
  CREATE TRIGGER

If not, It causes an error ERROR: function func_order_items must return type trigger.

Secondly, please remember that you cannot have OLD when AFTER INSERT on the trigger. If not, It raises an error ERROR:  record "old" is not assigned yet.

Finally, please take a look at the example as below:

CREATE TABLE IF NOT EXISTS orders(ID INT NOT NULL PRIMARY KEY, X INT);
CREATE TABLE IF NOT EXISTS order_items (ID INT NOT NULL PRIMARY KEY, ORDER_ID INT, Y INT);

INSERT INTO orders VALUES(1, 0);
INSERT INTO order_items VALUES(10, 1, 20);
INSERT INTO order_items VALUES(11, 1, 30);

CREATE OR REPLACE FUNCTION func_order_items() RETURNS trigger AS
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    UPDATE orders
    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = OLD.order_id)
    WHERE ID = OLD.order_id;
  ELSIF (TG_OP = 'INSERT') THEN
    UPDATE orders
    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = NEW.order_id)
    WHERE ID = NEW.order_id;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger_order_items
AFTER INSERT OR UPDATE
ON order_items
FOR EACH ROW EXECUTE PROCEDURE func_order_items();

UPDATE order_items SET Y = 200 WHERE ID = 10;
INSERT INTO order_items VALUES (12, 1, 200);


How to port LTRIM from Oracle to SQL Server? Moving an application from Oracle to SQL Server, I have this pseudo Oracle PL/SQL:

SELECT LTRIM(MyColumn, '-, ') FROM MyTable

I.e. I'm using Oracle's LTRIM with a second argument, specifying the characters to trim from the left side of the string.

Unfortunately, the T-SQL version of LTRIM doesn't allow me to specify the characters to trim.

Currently, I'm rather clueless how to migrate that LTRIM. I'm even thinking about processing the results in my hosting C# application, after I read the MyColumn.

This looks rather inelegant to me.

My question:

Is there any meaningful way of getting an LTRIM-like functionality for T-SQL to pass the characters to trim away?

Edit 1:

I need to replace -, , and  from the beginning of the string.

E.g.:

   -----, ,,, This is ,- a test,---,

would result in

This is ,- a test,---,

Edit 2:

I strongly hope this isn't an XY problem.

Maybe rewriting my whole query would remove the need for LTRIM altogether, although I would rather focus on porting it as 1:1 as possible and later question the usefulness of the LTRIM.

Yeehaw.

DECLARE @MyTable TABLE ( MyColumn VARCHAR(100) );

INSERT @MyTable ( MyColumn )
    VALUES ( '-----, ,,, This is ,- a test,---,' );

SELECT *,
        SUBSTRING(mt.MyColumn, ca.p, LEN(mt.MyColumn))
FROM @MyTable AS mt
CROSS APPLY ( SELECT *
              FROM (VALUES (PATINDEX ('%[^ ,-]%', MyColumn))
            ) AS x (p) ) AS ca;


Display user-defined types and their details I've created a few new UDTs in PostgreSQL. However, now I have two problems:

how to see which UDTs have been defined?
how to see the columns defined within these UDTs?

Unfortunately, I couldn't find anything on that in the PostgreSQL documentation.

Does this get you started?

SELECT n.nspname AS schema,
        pg_catalog.format_type ( t.oid, NULL ) AS name,
        t.typname AS internal_name,
        CASE
            WHEN t.typrelid != 0
            THEN CAST ( 'tuple' AS pg_catalog.text )
            WHEN t.typlen < 0
            THEN CAST ( 'var' AS pg_catalog.text )
            ELSE CAST ( t.typlen AS pg_catalog.text )
        END AS size,
        pg_catalog.array_to_string (
            ARRAY( SELECT e.enumlabel
                    FROM pg_catalog.pg_enum e
                    WHERE e.enumtypid = t.oid
                    ORDER BY e.oid ), E'\n'
            ) AS elements,
        pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
    FROM pg_catalog.pg_type t
    LEFT JOIN pg_catalog.pg_namespace n
        ON n.oid = t.typnamespace
    WHERE ( t.typrelid = 0
            OR ( SELECT c.relkind = 'c'
                    FROM pg_catalog.pg_class c
                    WHERE c.oid = t.typrelid
                )
        )
        AND NOT EXISTS
            ( SELECT 1
                FROM pg_catalog.pg_type el
                WHERE el.oid = t.typelem
                    AND el.typarray = t.oid
            )
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND pg_catalog.pg_type_is_visible ( t.oid )
    ORDER BY 1, 2;

In psql you can \set ECHO_HIDDEN on to make psql show you the queries
used to generate the output of the \d... commands. I've found these queries
to be very useful as a starting point when digging metadata out of databases.

Update: 2019-12-16

For composite types, the columns metadata can be determined using something like the following:

WITH types AS (
    SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
        FROM pg_catalog.pg_type t
        JOIN pg_catalog.pg_namespace n
            ON n.oid = t.typnamespace
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = 'c'
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                    SELECT 1
                        FROM pg_catalog.pg_type el
                        WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
),
cols AS (
    SELECT n.nspname::text AS schema_name,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            a.attname::text AS column_name,
            pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
            a.attnotnull AS is_required,
            a.attnum AS ordinal_position,
            pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
        FROM pg_catalog.pg_attribute a
        JOIN pg_catalog.pg_type t
            ON a.attrelid = t.typrelid
        JOIN pg_catalog.pg_namespace n
            ON ( n.oid = t.typnamespace )
        JOIN types
            ON ( types.nspname = n.nspname
                AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
)
SELECT cols.schema_name,
        cols.obj_name,
        cols.column_name,
        cols.data_type,
        cols.ordinal_position,
        cols.is_required,
        coalesce ( cols.description, '' ) AS description
    FROM cols
    ORDER BY cols.schema_name,
            cols.obj_name,
            cols.ordinal_position ;


Except command doesn't work in SQL I am trying to make an except in SQL but it doesnt exclude the data even though they are identical.

SELECT A.payment_id,
                            SUM(A.importe_trx) monto_total
FROM  [dbo].[ABONOS] A
WHERE payment_id = '980190391415704'
GROUP BY A.payment_id

SELECT B.payment_id payment_id,
                            SUM(B.precio_venta) monto_total
FROM [dbo].[SALES_VISANET_USD] B
WHERE payment_id = '980190391415704'
GROUP BY B.payment_id

I used that query to get the data and it's the same, but it doesn't exclude it with the EXCEPT command in the middle

SELECT A.payment_id,
                            SUM(A.importe_trx) monto_total
FROM  [dbo].[ABONOS] A
WHERE payment_id = '980190391415704'
GROUP BY A.payment_id

EXCEPT

SELECT B.payment_id payment_id,
                            SUM(B.precio_venta) monto_total
FROM [dbo].[SALES_VISANET_USD] B
WHERE payment_id = '980190391415704'
GROUP BY B.payment_id

I use "sum" because in one of the tables it has different amounts with the same payment_id, but when adding and grouping them they give the same amount and the same data as the other table.

From you comment:

  "importe_trx" and "precio_venta" stores the same data (float) that are the prices

That means you are using float data type to store these numerical values.
Problem with the float data type is that it always store rounded values, it can not precisely represent decimal values. Each values is saved as (0 or 1)*1/2+(0 or 1)*1/4+(0 or 1)*1/8+..., so 18.42 + 24.56 can be 42.98000000001 in float terms. SSMS will round these numbers, so you will not see the difference.

To fix this problem you should use decimal data type. Never use floats in any accounting data.


Work with table named Group I have a table named Group in my database , when I try to work on it it gave error :

 SELECT GroupID,GroupName FROM Group

it said :

     Msg 102, Level 15, State 1, Line 1
     Incorrect syntax near 'Group'.

I know that Group is illegal name for table but its a old database with too many data and relations , and I cant rename this table , is there any way to work with table ?

Add brackets with table, as Group is a reserved word for Group by clause in SQL Server.

SELECT GroupID,
GroupName
FROM [dbo].[Group]


How to avoid timeouts during big DML operations I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again.

The problem I have is that this runs for about 4 hours or so, and during that time the system that uses this database gets really slow and timeout a lot.

I would not care if my INSERT is slower but it shouldn't impact other users.

I was thinking in doing some batch of let's say 500 rows and use the WAITFOR, but was wondering if there could be a better option for doing this.

Break the operation into chunks of separate transactions.

BEGIN TRANSACTION;
  INSERT ...
  INSERT ...
  -- maybe 1000 or 2500 of these
COMMIT TRANSACTION;

GO
WAITFOR DELAY '00:00:01';
GO

BEGIN TRANSACTION;
  INSERT ...
  INSERT ...
  -- maybe 1000 or 2500 more of these
COMMIT TRANSACTION;

This will prevent all 55,000 inserts from jamming up your log and operating as a single, blocking transaction. I talked about this methodology to some degree in this blog post even though it talks about more tightly controlled DELETE operations.

If you can, have the file(s) regenerated in a format that makes them easier to BULK INSERT or bcp.


Recommended user ID column names to use I use user IDs a lot in tables, and I would like to find out when naming these columns, what's the best way to go about naming them. Should you be specific to what the user ID is, or should you keep using the same column name(s) to keep it consistent?

So for example:

Column names for subscriptions

user_subscriber_id = the user ID that made the subscription.

user_subscribed_to_id = the user ID that was subscribed to.

Column names for comments

sender_id = the user ID that made the comment.

reply_to_id = the user ID of the comment replied to.

Or should I always use user_id, and in the case where there needs to be 2 user IDs in a table (like above), use user_id and secondary_user_id, where the user_id should always be the user that does an action, and secondary_user_id should always be the user ID of the receiver, so:

Column names for subscriptions

user_id = the user ID that made the subscription.

secondary_user_id = the user ID that was subscribed to.

Column names for comments

user_id = the user ID that made the comment.

secondary_user_id = the user ID of the comment replied to.

Community wiki answer:

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

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

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


Any potential complications from ms sql backup db to disk = 'nul', disk = 'nul', ...? Our development team has a tool that notifies us of issues with our DB backups, but they have not yet given us the ability to exclude certain databases from the alerts - DBs that we do not want backups of are alerting us.

The only way I've found to resolve this are backups to the windows NUL device, however that takes much more time than I expected, and seems to still use processor time, and probabaly take RAM and I/O.

In attempting to speed up the process I tried several sets of backup options, collected data about time, and found that the more backup files I allocate as NUL, the faster it went. SQL 2008 R2 capped me at 64 files, i.e.

BACKUP DATABASE <db_name> TO DISK = 'NUL', DISK = 'NUL', ...
64 disk = 'NUL' being a clear winner for fastest option.

I'm worried about the server resources though, and am now wondering about striking a balance between 'speed' and 'not hogging all resources while this is happening'.

Any advice on resources or other ideas would be awesome. Thanks.

So... you are getting alerted when a backup doesn't occur, and you want to suppress these alerts for certain databases, that you already know you don't need to back up?

If this is the case, it is very likely the alert system is reading the contents of msdb.dbo.backupset and other associated tables.

If you don't have the ability to exclude those in the alerts (and you can't just ignore them or make an email rule to delete them), then why don't you fake out the alert system by manually inserting those records yourself?

WARNING: THIS IS PROBABLY DEFINITELY A BAD IDEA, BUT FRANKLY NOT AS BAD AS RUNNING UNNECESSARY HOURS-LONG CPU AND IO-INTENSIVE BACKUPS THAT GO TO NUL AND ULTIMATELY DO THE EXACT SAME THING

So I'm not going to craft the INSERT statements for you (see my note above about this being a bad idea) but you'll probably need correctly-formed and properly associated records in some or all of the following tables:

msdb.dbo.backupfile
msdb.dbo.backupfilegroup
msdb.dbo.backupmediafamily
msdb.dbo.backupmediaset
msdb.dbo.backupset

I'd run a trace while running a backup to see what exactly it writes.

Good luck, but fixing the alert system is by far the best idea.


How does Checkpoint know which point to recover from in PostgreSQL? I'm trying to better understand Checkpoint internals, specifically in PostgreSQL.

My current understanding is that Crash Recovery will always start from the latest REDO location that is marked when
a Checkpoint starts. Data in all WAL records before the REDO location are guaranteed to be on disk (in data files), and those WAL records are no longer needed.

I'm having a hard time understanding the following:

1) How is the REDO location determined exactly. When a Checkpoint starts, does it literally say "REDO location is the current WAL record + 1"?

2) When we do determine the REDO location, how is it that we are guaranteed that all WAL records before this are no longer needed?

Specifically, how is the following scenario handled:

Checkpoint 1 runs and writes out dirty page A to disk.
Dirty page A then has new rows inserted
Checkpoint 1 finishes
Checkpoint 2 starts and determines REDO location.

How can Checkpoint 2 be sure that all previous data is written to disk if pages can be dirtied after Checkpoint 1 flushes them, is the gist of the question.

How is the REDO location determined exactly. When a Checkpoint starts, does it literally say "REDO location is the current WAL record + 1"?

In essence, yes.  Exactly, no.  There is code in there about rounding up for cases where the record will not fit in the remainder of the log file and other corner cases.  To get an exact answer you have to read the source code.  See CreateCheckPoint in src/backend/access/transam/xlog.c

  When we do determine the REDO location, how is it that we are guaranteed that all WAL records before this are no longer needed?

A checkpoint starts (after computing the REDO pointer) by surveying all pages in the buffer cache, and marking the ones which were dirty at that time.  Before a checkpoint can end successfully, all of those marked pages need to have been written and flushed.  Once an initially dirty page has been flushed, the WAL that protected that initial dirtying of the page is no longer needed, because the page itself is safely on disk and can speak for itself.  If the page starts out dirty and is then re-dirtied multiple times throughout the checkpoint, then the subsequent dirtying actions will each have their own WAL records, which will necessarily fall after the REDO pointer.  Those WAL records will be retained, at least until the successful completion of the next checkpoint after the current one.


Add [Is Primary Key Column] to this query I made this query, to list tables, its columns, data type and etc:

select t.name as 'Table',
       c.name 'Column',
       ty.name 'Data_Type',
       c.max_length as 'Max_Lenght',
       c.is_nullable as 'Null',
       c.is_identity as 'Identity'
from sys.tables t
    join sys.columns c on t.object_id=c.object_id
    join sys.types ty on c.system_type_id=ty.system_type_id
        order by 1

I have 2 questions:

Ques.1:

How can I add a column 'Is primary key?' to it? I could not find a table that help me ( I took a look at sys.indexes, sys.foreign_keys, INFORMATION_SCHEMA.table_constraints ).

Ques.2:

In sys.columns, int values have the max_lenght as 4, and some other fields too. What is this 4? 4kb per data? datetime and decimal too.

Thanks.

Query that adds "is_primary_key" field for each column

The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK.

Also, you want to use [user_type_id] instead of [system_type_id] to avoid a Cartesian product. Most of the time there won't be a difference between these two fields. But if you have User Defined Data Types (UDDTs), or use the sysname datatype (which is an alias to NVARCHAR(128)), then the [system_type_id] value will be repeated in the sys.types catalog view.

The following query lists all fields in all tables, adding a computed field to denote if the column is part of the PK or not. It handles composite PKs as well.

SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey]
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
ORDER BY tbl.[name], col.[name];

Info on max_length column

The max_length column in sys.columns is the maximum number of bytes that the column can take up per row. In the case of fixed-length fields such as INT, DATETIME, etc, those fields always take up their maximum amount of space, unless you are using the SPARSE option (per column setting) or have enabled Data Compression (per index setting).

For variable-length fields such as VARCHAR, NVARCHAR, XML, etc, that value is the maximum number of bytes it can take up. A value of -1 indicates a value of approx 2 GB (Int32.MaxValue) which is used by the MAX (VARCHAR, NVARCHAR, and VARBINARY) and XML types.

The Unicode string types (NCHAR and NVARCHAR) that are not declared as MAX will display a max_length of 2 * declared_max since the (safe) assumption is that they use 2 bytes per "character". This is not always the case since Supplementary Characters are actually 4 bytes per "character". But, this is why sysname, being an alias for NVARCHAR(128), has a max_length of 256. Similarly, the less frequently used Double Byte Character Set (DBCS) collations that allow for mapping more than 256 characters in an 8-bit VARCHAR / CHAR field will store characters in either 1 or 2 bytes, depending on the character. Meaning, for VARCHAR data using a DBCS collation, and for NVARCHAR data, declaring a column or variable as VARCHAR(x) or NVARCHAR(x) does not guarantee x characters are storable: you can only fit x characters if all of those characters are of the standard length for that type (i.e. 1 byte for VARCHAR, 2 bytes for NVARCHAR). So, an NVARCHAR(3) field is given 6 bytes maximum to use. That can fit: 3 regular two-bytes Unicode characters, or 1 regular two-byte character and 1 Supplementary Character at 4 bytes (which is only 2 characters, not 3). It cannot fit 2 Supplementary Characters as that would require 8 bytes.

The deprecated TEXT, NTEXT, and IMAGE types that nobody is using anymore (that's sarcasm) show a max_length of 16, presumably being the size of the pointer left on the data page that points to the LOB page holding the data.


How do I force SQLite to materialize a subquery? Because SQLite sometimes evaluates functions like random() more than once if they appear in a subquery, which may be a bug, eg:

select op, op from (select abs(random())%10 as op from (select 1));
3|1

I need an easy way to force SQLite to materialize the subquery so that random() is only evaluated once

this is a simplified example for demonstration - my real world query does not have from (select 1) but selects from a view

From the SQLite docs (my emphasis):

  Subqueries might also need to be materialized when they appear in the
  FROM clause of a SELECT statement. For example:

SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;

  Depending on the query, SQLite might need to materialize the (SELECT
  b FROM ex2) subquery into a temporary table, then perform the join
  between ex1 and the temporary table. The query optimizer tries to
  avoid this by "flattening" the query. In the previous example the
  query can be flattened, and SQLite will automatically transform the
  query into

SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;

  More complex
  queries may or may not be able to employ query flattening to avoid the
  temporary table. Whether or not the query can be flattened depends on
  such factors as whether or not the subquery or outer query contain
  aggregate functions, ORDER BY or GROUP BY clauses, LIMIT clauses, and
  so forth. The rules for when a query and cannot be flattened are very
  complex and are beyond the scope of this document.

You may be able to work around the problem with a view, but it seems that in this case at least, adding a dummy union is enough to prevent "flattening":

select op, op
from (select abs(random())%10 as op from (select 1) union all select 1 where 1=2);

6|6


Unique index corrupted SQL. Select query returns single row but create unique index fails I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here.

When I run a select query for these key values, it returns only one row, but when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) these errors are thrown:

  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'uc1SycContents'. The duplicate key value is (7696031,
  08703987-557d-e111-9888-e61f13c44f03).
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'nc2MSmerge_contents'. The duplicate key value is (424222,
  7696031, 08703987-557d-e111-9888-e61f13c44f03).
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'nc4MSmerge_contents'. The duplicate key value is
  (08703987-557d-e111-9888-e61f13c44f03, 7696031).
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'nc3MSmerge_contents'. The duplicate key value is (-425819,
  7696031, bf8a5a9c-6f7d-e111-9888-e61f13c44f03).
  Msg 2601, Level 14, State 1, Line 2
  Cannot insert duplicate key row in object 'dbo.MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_genhistory' and the
  index name 'unc1MSmerge_genhistory'. The duplicate key value is
  (236bfa48-965e-4a65-b4cd-ac527de04d1b, ).

How can I repair my database?

if you consider error, Msg 1505, Level 16, State 1, Line 2 The CREATE
  UNIQUE INDEX statement terminated because a duplicate key was found
  for the object name 'dbo.MSmerge_contents' and the index name
  'uc1SycContents'. The duplicate key value is (7696031,
  08703987-557d-e111-9888-e61f13c44f03)...... I am running below Query
     select * from msmerge_contents where rowguid='08703987-557d-e111-9888-e61f13c44f03'
  and it is returning only 1 row

When you have index corruption problems (ie. keys present in NC index but not in base table or vice-versa) you must be very careful about the SQL you use to validate data. At this moment your data is inconsistent but the query optimizer does not know that and completely trusts your schema, including these incorrect indexes. As such it may optimize your query to use one of the NC indexes that is missing a key and the result will also miss a a key falsely returning no duplicates. To solve this catch-22 situation you need to force the optimizer hand by explicitly requesting an index or another and make sure the projected list of columns can be satisfied by the index you enforced (ie. no *). Assuming uc1SycContents is not the clustered index, try out the following:

select rowguid
from msmerge_contents with INDEX (1)
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

select rowguid
from msmerge_contents with INDEX ([uc1SycContents])
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

This will forcefully check if the rowguid has a duplicate for that guid in the base table clustered index (index id 1) vs. the index uc1SycContents. I expect that the first query returns 2 (or more) rows while the second returns 1.


Copy a single record to a replica table before update using trigger (Oracle 12c) I want to insert a complete record (before updating original record) to a replica table using trigger. Following are the sample tables:

create table customer
(
     cID number,
     cName varchar2(30)
     -- all other attributes will be defined here
);

create table replica
as (select * from customer where 1 = 2);

and trigger:

create or replace trigger update_Trigger
before update on customer
for each row
begin
    if updating then
        insert into replica
            select * from customer where cID = :old.cID;
          -- also done with :new.cID; but not effect...
    end if;
end;

Table is not empty, I have some records in it. But when I run update statement on customer table, I get error:

SQL> UPDATE CUSTOMER
  2  SET CNAME = 'C' WHERE CID = 102;

UPDATE CUSTOMER
       *
ERROR at line 1:
ORA-04091: table DBADMIN.CUSTOMER is mutating, trigger/function may no
ORA-06512: at "DBADMIN.UPDATE_TRIGGER", line 3
ORA-04088: error during execution of trigger 'DBADMIN.UPDATE_TRIGGER'

What is wrong with this? What am I missing? I know this can be done by using :new.attribute and :old.attribute but there many attributes in the original table, I am working on. Is there any short method for getting and inserting the whole row?

You can use this format for copying your original values over:

CREATE OR REPLACE TRIGGER update_Trigger
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        INSERT INTO  replica VALUES(:OLD.cID, :OLD.cName, ... );
    END IF;
END;
/

You will need to specify each field you want inserted into replica

This will be slow and cause a lot of network/database traffic if you are updating 1,000s of rows at a time.


Can I emulate Oracle's autonomous transaction in MySQL? I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any IUD on the former tables, can be locked by other IUD that trigger inserts into the same queue, and that IUD that fails will not be logged, since those inserts will be rolled back as well.

I don't care much for entries in the queue where the triggering IUD was rolled back; the queue is read by some process that invalidates / refreshes cache and similar actions. Should anything have been rolled back, some cache will be refreshed with the same data. No big deal.
The business case here is a website with a great number of sets that consist of single products. Any one product can be in up to 15,000 sets. If the price of a product changes, the prices of all those sets need to be recalculated. I don't want to do that on the same transaction when saving the price of a product, because that would take too much time and lock too much of the database, so I enqueue the recalculation.

For logging, there are some actions I want to log, even (especially) if they fail. I can't do that now.

Were this Oracle, which I used until recently, I would've used PRAGMA AUTONOMOUS_TRANSACTION to handle the insert in a separate transaction, that would always commit no matter what the other transaction did.

Can I emulate this behaviour by using MyISAM tables for the logs and the queues, with concurrent_insert set to ALWAYS, while the rest of my tables are InnoDB? (I expect multiple inserts into them, that's the whole issue, while they're seldom read (logs), or only once (queues)).

If you have access to the Aria engine or another non-transactional engine, then yes.
Very short proof of concept, tested on MariaDB 10.5.4:
CREATE TABLE test_aria_engine ( a int, b varchar(1000) )
ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=1 TABLE_CHECKSUM=1
COMMENT='test-aria_engine with transactional on 1'

The transactional in Aria is not for real transactions, it is only last operation log, still good in case of server crash the data is consistent.
Now create an InnoDB table:
CREATE TABLE test_innodb_engine ( a int , b varchar(1000) )

And test:
BEGIN TRANSACTION;

-- insert in aria table
INSERT INTO test_aria_engine ( a, b) VALUES (4 , 'ddd');

-- insert in innodb table
INSERT INTO test_innodb_engine( a, b) VALUES ( 4 , 'dddd');

-- rollback
ROLLBACK;

After the ROLLBACK has been initiated let's check the table contents of the Aria and InnoDB tables:
SELECT * FROM test_aria_engine;

|a  |b  |
|---|---|
|4  |ddd|

So, ROLLBACK cannot rollback ARIA engine
SELECT * FROM test_innodb_table;

No result.

So in the InnoDB table ROLLBACK has done its job.
This shows how to avoid transaction logic in MariaDB or using a similar engine like MyISAM in MySQL.


Slow database drop in mysql Just installed a new 24 core server and it is noticeably slower when doing truncates and drops.  Example:

$ mysql -u root lmcdental_test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.6.23-72.1 Percona Server (GPL), Release 72.1, Revision 0503478

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, 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> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop database lmcdental_test6;
Query OK, 66 rows affected (6.65 sec)

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000022 |
| checking permissions | 0.000548 |
| Opening tables       | 0.000017 |
| System lock          | 6.651106 |
| Opening tables       | 0.000061 |
| System lock          | 0.000041 |
| Opening tables       | 0.000571 |
| System lock          | 0.000668 |
| query end            | 0.000009 |
| closing tables       | 0.000044 |
| freeing items        | 0.000025 |
| cleaning up          | 0.000043 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)

Please notice the "System lock".  This is running Ubuntu 14.04LTS.

The same database on a 16 core system is almost instantaneous.  Any ideas on how to mitigate the 6.65 second system lock?

Thank you.

I have addressed this once before : MySQL database drop insanely slow

My suggestion in that post was this

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

If all your data uses InnoDB, this might work for you.

Four years ago, I thought having tons of users was also a symptom.

If a patch was missed, here is another likely suspect: Bug #61188 DROP TABLE extremely slow

  [15 Aug 2011 22:14] James Day

  The fix for this is in MySQL 5.5.15 and is expected to be in 5.1.59. At the time of writing it hasn't yet been added to the  release notes but that should happen soon.

  [7 Oct 2011 0:46] John Russell
    Added to changelog:

  The DROP TABLE command for an InnoDB table could be very slow, in a
    configuration with a combination of table compression, partitioning,
    and a large buffer pool.

Please give it a try and see tell us if it worked.


Changing Key (PK, FK) properties in DROP/Add column statements I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout

table_name( Field_1  datatype PK, Field_2 datatype,....)

and I want the inserted column, say Field_k to be part of a PK jointly with the existing single-field PK.

I haven't found any source for how to do this or for whether it is possible or not. Any advice, please?

You need to drop the FKs that reference it, then drop the PK and re-create it, then re-create the FK. There's no magic syntax for this, but you can get some ideas here for how to generate the drop/create scripts for the FKs:

Why dropping all foreign keys from a script is not working?

  would I get a warning if I am (unknowingly, of course) violating some constraints in the process, i.e., if I forget to drop some referenced table? Just curious as to how SQL server handles integrity/constraint violations.

You won't be able to drop the PK while any FK still references it. And of course you won't be able to re-create the FK until those other tables also have a column that can fill in for the new column you're adding to the PK.


Will these queries return the same results? I have this simple query:

SELECT
    friend_id
FROM
    default_friend
WHERE
    user_id = 1

And the results are below:

+-----------+
| friend_id |
+-----------+
|      2250 |
|      4901 |
|      7187 |
|      9337 |
|      9843 |
+-----------+

Now I have this query too a bit more complex:

SELECT
    friend_id, user_id, approved
FROM
    default_friend
WHERE
    user_id = 1
        AND (friend_id IN (SELECT
            friend_id
        FROM
            default_friend
        WHERE
            user_id = 1))
        AND approved = 0

My question is: will the SELECT into the second query return the same data as the data returned by first query? Meaning friend_id = 2250, 4901, 7187, 9337, 9843 or I need to run the first and the programmatically split the results and build the second one? Also I'll like to know which way is more optimum!

No, you don't have to run the first query, get the results, build the second one dynamically and then run it.

And you don't need this complex second query either. The simple one will suffice:

SELECT
    friend_id, user_id, approved
FROM
    default_friend
WHERE
    user_id = 1
  AND
    approved = 0 ;

And you don't really need the user_id and the approved in the SELECT list either. They are going to be 1 and 0 in all rows, aren't they?

So, the question is. Why don't you just keep the original, 1st query, adding only the additional condition (approved = 0) ?:

SELECT
    friend_id
FROM
    default_friend
WHERE
    user_id = 1
  AND
    approved = 0 ;


Linked server - connection fail from remote computer (using ssms) I'm facing issue with linked server on Microsoft SQL Server 2012 (SP4).
When I remotely connect to SQL server (where linked server is created) with ssms and try to use/test linked server connection (e.g. Test Connection), I get the error

  Msg 18456, Level 14, State 1, Line 14, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

But, when I log locally to SQL server I can access linked server with no problems. I'm using windows authentication.

Here is my configuration:
A (my workstation, ssms client)
B middle SQL server (linked server definition, pointing to C)
C target SQL server

So, the problem only appears when I'm connecting from A (ssms) to B and from here try to access SQL server on C via linked server. Locally, on B, the linked server is accessible corrrectly.
It seems like I'm facing double hop problem, but since my knowledge on this topic is (yet) not deep enough, I would like to ask for some help with diagnosing the issue.

Any help would be really appreciated!
Regards,
Domen

The linked server is configured as follows:
Connections are made using login's current context.

EXEC master.dbo.sp_addlinkedserver @server = N'linkedservername', @srvproduct=N'SQL_SERVER', @provider=N'SQLNCLI11', @datasrc=N'MY_SERVER_C'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'remote proc transaction promotion', @optvalue=N'true'

It is as you guessed a double hop issue.

Recreating the issue, trying to connect from my local SSMS, to the source server, and then connecting to a second destination server via a Linked Server "connection test" that is configured as "be made using the current security context".

Workaround #1

Implement kerberos authentication

The kerberos configuration manager is much easier than manually creating the commands

Download Link

More information on implementing kerberos Here

Also check out step 11 of This link:

  How do I set up Kerberos for linked servers?

  Set up SPNs for both instances. Set delegation to Trust this user for
  delegation to any service on the service account being used on the
  instance where the linked server is set up. Basically, the server that
  must pass the ticket along must be set up for delegation.

Workaround #2

Using a SQL Login to map to the windows login.

Step 1: Create the Login on the destination server and give it the needed rights

Step 2: Change Linked server mapping

Note: if you are adding an other account than the one you are currently creating the linked server with, then it will still throw an error.

Step 3: Test the connection again:


Enabling SQL Server authentication for restored database user I've restored a remote database to my local, and it contains some users which are used to execute jobs.

We are trying to execute the job and it is seems to return a connection error (connection to the same database).

It is using a proxy user to run CMD EXEC commands, and we've checked the final user behind the proxy, turns out it seems that this user is not allowed to login to the SQL Server.

We've entered with SA account and taken ownership of the database, however, we are unable to change the user setting.

Please advise.

The screenshot you've posted is just showing that it's a user which uses Windows authentication to log on to SQL Server, though this doesn't have any bearing on whether or not the user can actually access a database.

If you go to the Server Roles tab you can assign a fixed server role which will allow various sets of permissions to the instance, or if you go to User Mapping, you can specify a role for that user against a particular database, or under securables you also have more granular permissions if you only want to assign very specific permissions.

I would ensure that your user has the required permissions mapped against the database you are trying to access.  If you're still seeing an error after that, post the specific error message.

Also, just to more specifically answer your question - it's not possible to switch the user to use SQL Server authentication; in this case you would just create a new user which uses SQL authentication, as it is specified at the time the user is created.


Transactional Replication failing between SQL Server 2012 and SQL Server 2016 versions As per the Microsoft documentation - A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example:

  a SQL Server 2012 (11.x) Publisher can have SQL Server 2014 (12.x) and
  SQL Server 2016 (13.x) Subscribers;

and

  a SQL Server 2016 (13.x) Publisher can have SQL Server 2014 (12.x) and
  SQL Server 2012 (11.x) Subscribers.

But the subscription I am trying to create from

  Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) to
  Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)

is failing and here is the error message I receive -

  The selected subscriber does not satisfy the minimum version
  compatibility level of the selected publication.

Are these versions not compatible?

The other half of the version requirements is that your distributor be the highest SQL Server version in your Replication topography.

In your case, you are replicating from SQL Server 2012 to SQL Server 2016, which works only if your distributor is also SQL Server 2016.

If you are running your distribution database on your 2012 publisher, configuration will fail when you add the subscriber because a subscriber is at a higher version than the distributor. You would need to either run a dedicated distributor (recommended), or use your 2016 subscriber as the distributor server.

The error message you posted is deceptive because it's not a subscriber/publisher version issue, but rather a subscriber/distributor version issue.


How do I prevent changes to my PostgreSQL database? My steps:

run the final pg_dumpall on a server prod-server-old
Shut down prod-server-old for ever.
copy the output to a different server (prod-server-new)
Restore the db there.
All traffic goes to prod-server-new now

How can I avoid modification to the database during pg_dumpall (between step1 and step2), so that modifications during this time cannot be lost?

In my case it is 100% OK to have a small down time.

There are several databases in the "cluster" (btw, I do not like the word "cluster". I guess most people think a "cluster" is a group of several computers, but in this case it means one Postgres server, which holds several databases).

My question was marked as possible duplicate to "Make Postgres database temporarily read-only (for performing volume snapshots)". I don't think it is a duplicate, since in my case is different since I don't ask for a temporary read-only state.

One work-around:

Change the port of PostgreSQL on prod-server-old. This way it is unlikely that clients connect to the DB during pg_dumpall --port=OTHER_PORT.


how to get any record in any table that would violate a foreign key constraint that would prevent my delete? basically I am looking for something like:

before you can delete record x from my_table you need to:

1) delete x,y and z from table1

2) delete m and n from table2

3) delete a,b,c from table3

I have not starting coding yet, I am posting the question because sometimes other DBAs have had the same idea and they might be willing to share the code.

I have though, seen similar questions (see below - but they haven't fulfilled my requirements)

Checking referential integrity rule violation before deletion

Checking rule violations before deleting a record

and this is an interesting article related to this matter:

Handling Constraint Violations and Errors in SQL Server

Please note that I don't want to disable any constraints. I just want to check all that will break if I do a delete - before I go ahead and do it.

I have a table called tblBOrder which references a bunch of different tables.

when I run the following script to check all the tables it references I get the result below.

declare @table sysname
declare @Obj int

select @table            = 'tblBOrder'                   --'dbo.tblBCataloguePriceSet'   --'tblBCataloguePriceSetItem'
select @referenced_table = ''                            --'dbo.tblBCataloguePriceSet'

select @obj     = OBJECT_ID(@table)
select @ref_obj = OBJECT_ID(@referenced_table)

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    sch.name AS [referenced_schema],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas sch2
    ON tab2.schema_id = sch2.schema_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where 1=1
  --AND ( @ref_obj IS NULL OR @ref_obj = tab2.object_id )
  AND ( @OBJ IS NULL OR @obj = tab1.object_id)

Now I want to delete a specific record from my order table.
this is the record I want to delete: USZ000026936

Using the following script I generate a script to view all the records that I need to get rid of first, before I can delete my USZ000026936 in the order table.

SET NOCOUNT ON

declare @RowId varchar(100)
declare @TableName sysname
declare @Command varchar(max)

select @RowID = 'USZ000026936'
select @TableName = 'tblBOrder'

select fkc.*
from sys.foreign_key_columns fkc
    inner join sys.columns col
           on fkc.parent_object_id = col.object_id
          and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName

select @Command = isnull(@Command + CHAR(10) + CHAR(13) + ' union all ', '') + 'select ''' + object_name(parent_object_id) +
    ''' where exists(select * from ' + object_name(parent_object_id) + ' where ' + col.name+ ' = ' + '''' + cast(@RowId as varchar) + '''' + ')'
from sys.foreign_key_columns fkc
    join sys.columns col on
        fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName

print @command

and here is the script generated:

select 'tblBOrderItem' where exists(select * from tblBOrderItem where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderRefund2' where exists(select * from tblBOrderRefund2 where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderAuth2' where exists(select * from tblBOrderAuth2 where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderHistory' where exists(select * from tblBOrderHistory where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderPayment' where exists(select * from tblBOrderPayment where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderOfferOverride' where exists(select * from tblBOrderOfferOverride where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderShipOverride' where exists(select * from tblBOrderShipOverride where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderSpecialDeliveryInstruction' where exists(select * from tblBOrderSpecialDeliveryInstruction where strBxOrderNo = 'USZ000026936')

 union all select 'tblBReturn' where exists(select * from tblBReturn where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderRelationship' where exists(select * from tblBOrderRelationship where strBxOrderNo1 = 'USZ000026936')

 union all select 'tblBOrderRelationship' where exists(select * from tblBOrderRelationship where strBxOrderNo2 = 'USZ000026936')

 union all select 'tblPanViewerAuditLog' where exists(select * from tblPanViewerAuditLog where strBxOrderNo = 'USZ000026936')


Why doesn't the $1 syntax work for all queries? I tried to do this:
COPY postgres_log FROM $1 WITH csv;

And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives:
ERROR:  syntax error at or near "$1"

Um... So does that mean that I'm forced to send it a scary string like this?
COPY postgres_log FROM '/full/path/to/logfileblablabla.csv' WITH csv;

If so, why? Why don't all query types support the parameterized queries interface? Isn't this like asking to enable SQL query injection vulnerabilities?

Yes, you cannot use parameters with COPY.
Why? Because nobody has implemented it. Feel free to come up with a patch.
Currently only SELECT, INSERT, UPDATE and DELETE support parameters.
You can use the format function with the %L format to comfortably construct an SQL string with a string literal while avoiding the danger of SQL injection.


SSAS and SQL Server on the same server I have a server that is primarily used for SSAS, heavy processing, however I have SQL Server on it alongside SSAS, but I don't want sql server to hold ssas back.

I have set the SQL Server MAX memory to only 4 GB, and I don't have any large user databases on it.

TempDB also has been kept to 4 files, low profile.

Should I enable the server features that I normally do, like
lock pages in memory and Perform Volume Maintenance Tasks for sql server?

As you can see on the picture below
CPU, memory and disk space are relatively OK, but the processing will also be high.

I am really looking for some technical info, so that I can evaluate things up and apply to each situation.

  Lock Pages in Memory

  This Windows policy determines which accounts can use a process to
  keep data in physical memory, preventing the system from paging the
  data to virtual memory on disk. Locking pages in memory may keep the
  server responsive when paging memory to disk occurs. The SQL Server
  Lock Pages in Memory option is set to ON in 32-bit and 64-bit
  instances of SQL Server 2014 Standard edition and higher when the
  account with privileges to run sqlservr.exe has been granted the
  Windows "Locked Pages in Memory” (LPIM) user right.

These are examples of problems I had while managing SSAS from another server:

Linked Server from SQL Server to SSAS

routine to backup ssas databases

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS

Why is SQL Server on the same box?

"Perform Volume Maintenance Tasks" should certainly be granted to the service account used to run SQL Server since that will allow SQL Server to not spend time unnecessarily zero-ing files during expansion of existing files and creation of new files.

"Lock Pages in Memory" would be a good consideration if you want to guarantee SQL Server has enough memory to perform the workload assigned.  If you don't care about the SQL Server workload, why is it on the same box as SSAS?


How can I create an ad-hoc domain user on SQL Server Okay, so here's the problem. I have an application I need to install and run locally for testing and debugging. The application has been designed to run on a domain and as such expects to log into the database using domain credentials.

I can't modify the section of code that handles the logins without a serious amount of work which I don't have time for this week, nor do I have any ability to add the machine hosting the database to the domain (contractors machines aren't allowed to be added to the domain under the I.T. policy).

So I'm in a bit of a bind. I cannot use the CREATE LOGIN [DOMAIN\User] From Windows routine as I get the following error:

Windows NT user or group 'DOMAIN\User' not found. Check the name again.

I can't just insert into the relevant security tables: sys.server_principals table,  sys.SysLogins etc. because

Ad hoc updates to system catalogs are not allowed.

Is there any way I can back door my local SQL Server installation to accept these user credentials from the application without being attached to the domain?

Of course adding the machine to the domain and logging in as a domain user would resolve this issue in a couple of minutes, but political issues prevent that from happening.

You cannot actually login to SQL Server with any domain account. You can either pass a SQL Login with its password, or you can logon using "Windows Authentication". What the latter means is that the account that is executing the windows executable is passed to SQL Server to authenticate.

That means you just need to execute the app using a local machine account and add that account as a windows login to SQL Server.

If the application on the other hand is running on a domain computer and you just pointed it to use SQL Server on your machine, you are out of luck. However, if you were able to change the connection string to point to your machine, you should be able to just add a SQL Login to that same connection string.


MS Access 2007/2010 Compact & Repair not working I have a database that I use in VB.  As records update theres a lot of useless info in there because access doesn't reuse the same spots.  So that information has to be removed.  Before I would use compact & repair and access 2000 and it would correct this.  It would correctly adjust the file size and remove all the wasted space or whatever happens in Access.

Compact and repair feature in 2007 and 2010 does not fix the file size.. why?

Open that database in 2010, and do this:

Click the External Data ribbon group
Click the Access item
Use the Browse button to locate the database from which you want to import the database
Be SURE to select the first option (i.e. the "Import tables, blah blah" option)
Click OK
In the Import Objects dialog, select the tables you want to import. Be sure to review the settings in the Options button to see if you need any of those (chances are you do not).
Click OK, and Access will import the selected objects to your new database

Now, close out the database and make a copy of it. Reopen the new database, and Compact it.

If you're still have troubles after that, then there is a good chance that you have troubles with the installation of Access/Office or Windows on your machine. Be sure that you've fully updated Office and Windows, and try again.

If you're still having troubles compacting, then you may need read this MSDN forum thread.


how to convert query related to calculate work hours in MySQL to MS Access I want to calculate work hours in each day using MS Access.I have query for same thing in MySQL but this thing is not working in MS Access using pdo php.

query in MySql

    SELECT MachineNo, Empcardno, Date, SEC_TO_TIME(SUM(tsecs)) AS total_time
FROM (
  SELECT CASE WHEN MachineNo = @mn AND Empcardno = @en AND `Date` = @dt
              THEN @rn := @rn + 1
              ELSE @rn := 1
         END AS rn,
         @mn := MachineNo AS MachineNo, @en := Empcardno AS Empcardno,
         @dt := `Date` AS `Date`,
         CASE WHEN @rn % 2 = 1 THEN -TIME_TO_SEC(`Time`)
              ELSE TIME_TO_SEC(`Time`)
         END AS tsecs
  FROM timesheet
  CROSS JOIN (SELECT @mn := 0, @en := 0, @dt := '', @rn := 0) init
  ORDER BY Date, Time
) t
GROUP BY MachineNo, Empcardno, Date

how to convert this query to make compatible with ms access pdo connection using php

table structure and data in ms-access

https://i.stack.imgur.com/YaCPp.png

SELECT MachineNo, Empcardno, [Date],
       FORMAT(SUM(IIF(rn MOD 2 = 0, -TimeValue([Time]), TimeValue([Time]))),'hh:nn:ss') AS total_time
FROM (
  SELECT t1.MachineNo, t1.Empcardno, t1.Date, t1.Time, COUNT(t2.MachineNo) AS rn
  FROM timesheet t1
  LEFT JOIN timesheet t2 ON t2.MachineNo = t1.MachineNo
                        AND t2.Empcardno = t1.Empcardno
                        AND t2.Date = t1.Date
                        AND TimeValue(t2.Time) < TimeValue(t1.Time)
  GROUP BY t1.MachineNo, t1.Empcardno, t1.Date, t1.Time
)  AS t
GROUP BY MachineNo, Empcardno, Date

for complete detail please see the link
https://stackoverflow.com/questions/62297919/how-to-convert-query-related-to-calculate-work-hours-in-mysql-to-ms-access/62317847#comment110225804_62317847


Can't run rs.initiate() when do mongodb replication set Using CentOS 7.

MongoDB version: 3.2.15

Hostname: node1

Make directory:

mkdir /mongo-metadata

/etc/mongod.conf

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /mongo-metadata
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1  # Listen to local interface only, comment to listen on all interfaces.

#security:

#operationProfiling:

replication:
  replSetName: rs0

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

Start the replication member:

mongod --config /etc/mongod.conf

Run mongo:

mongo
# try
rs.initiate()
{
        "info2" : "no configuration specified. Using a default configuration for the set",
        "me" : "node1:27017",
        "ok" : 0,
        "errmsg" : "No host described in new configuration 1 for replica set rs0 maps to this node",
        "code" : 93
}

# try
config = {
    _id : "rs0",
     members : [
         {_id : 0, host : "node1:27017"},
         {_id : 1, host : "node2:27017"},
     ]
}
rs.initiate(config)
{
    "ok" : 0,
    "errmsg" : "No host described in new configuration 1 for replica set rs0 maps to this node",
    "code" : 93
}

Reference:

https://docs.mongodb.com/manual/reference/command/replSetInitiate/

So what's the reason? It can't realize the hostname?

Answer is simple! You don't have "node1" defined in your /etc/hosts file. Your machine don't know IP address of "node1" (or "node2").

echo "127.0.0.1  node1" >> /etc/hosts

And then other thing, you have defined (in config file) that your mongod is listening only localhost. So it means that your whole replica set must be in that one machine and if it is so, all those three members cannot use same port 27017, every member must have different port.


C++ stoi equivalent in SQL Server's T-SQL I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts.  Specifically, I'm looking to be able to grab numerics at the beginning of the string, and ignore non-numerics afterwards.  I need this to sort the following strings and need them to show up in the following order:

  99ABC
  123ABCD
  ABC
  DEFGH

So I was thinking if I had something like STOI, I can do:

ORDER BY CASE WHEN (ISNUMERIC(LEFT(TEXT, 1))) THEN STOI(TEXT) ELSE 999999 END, TEXT

I can precompute the results of STOI (or the results of the above CASE statement) and store into an extra INT column nightly.  So I don't really care about real-time performance.  I guess I'm just looking for a way without having to check all the chars of the string:

DECLARE @numEnd INT = 0
WHILE (ISNUMERIC(LEFT(TEXT, @numEnd)) BEGIN
     SET @numEnd = @numEnd + 1
END
DECLARE @stoi INT = CAST(LEFT(TEXT, @numEnd) AS INT)

DECLARE @Test VARCHAR(32)

SET @Test = '99ABC 123ABCD ABC DEFGH'

DECLARE @stoi INT = TRY_CONVERT(INT, LEFT(LTRIM(@Test), PATINDEX('%[^0123456789]%', LTRIM(@Test + ';')) - 1))

SELECT @stoi

  99


What happens to the index of a primary key after a DROP CONSTRAINT? I am running PostgreSQL 9.1.4.

I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :

1 - Remove the pkey constraint on the current primary key because it is currently not a serial type.

2 - Add a serial type column and have that new column be the primary key.

3 - Setting the old column of step #1 to UNIQUE and recreate foreign keys for all tables that had a foreign key pointing to my table. In fact, those tables would all have two foreign keys pointing to my table: one towards the old column that is currently the primary key, and another towards the new serial type column

I have successfully created a script that does this, but I wish to know:

a) Does dropping the pkey constraint at step #1 above also drop the index associated to that primary key?

b) If not, is there any way to reuse that index? After adding back the UNIQUE constraint at step #3, would a new index be created or would it use the one that existed before? No content is being changed in the table except for the new column that is created (serial primary key).

EDIT (Some clarifications):

Let's call the old pk column OLD_PK and the to-be-created-by-my-script new pk column NEW_PK
The OLD_PK column's type is INT
The OLD_PK column will not and can not be dropped (its information still has value) therefore it also can not be converted itself to a type serial, the existing data must stay.
I am aware that serial is a shortcut. Like I said I have come up with a working script, the part a colleague and I weren't sure about is the index and how it would work from that point of view.
All tables that previously had one foreign key pointing to OLD_PK will, after the execution of the script, have one foreign key pointing to OLD_PK (say "fkey_old_pk") and another foreign key pointing to NEW_PK (say "fkey_new_pk")

Disclaimer
This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.
For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):
pg_index.indisprimary:
For PRIMARY KEY constraint  ... TRUE
For UNIQUE constraint       ... FALSE
pg_constraint.contype:
PRIMARY KEY constraint  ... 'p'
UNIQUE constraint       ... 'u'
You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:
UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:
UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;


Updating column based on conditions from another table I am updating a column to values based on conditions from another table. I want only the values to be updated for rows where the condition meets. It shouldn't overwrite the rows where the conditions do not meet to null (as other values exists). For e.g.

update i2017_mar_ye a
set    a.status = (select case
                            when b.cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'RE_Others'
                            when b.cvg_cstat in ('B','I','K','N') then 'RE_Lapse'
                            when b.cvg_cstat in ('D') then 'RE_Death'
                            when b.cvg_cstat in ('E','S') then 'RE_Surrender'
                            when b.cvg_cstat in ('H') then 'RE_Rider Claims'
                          end
                   from  i2016_mar b
                   where a.pol_id = b.pol_id
                   and   a.cvg_num = b.cvg_num
                   and   a.cvg_iss_dt <= to_date('31-March-2016')
                   and   a.cvg_cstat in ('1','2','3','4')
                   and   b.cvg_cstat not in ('1','2','3','4')
                   and   a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9'));
commit;

update i2017_mar_ye a
set    a.status = (select case
                            when a.cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'DE_Others'
                            when a.cvg_cstat in ('B','I','K','N') then 'DE_Lapse'
                            when a.cvg_cstat in ('D') then 'DE_Death'
                            when a.cvg_cstat in ('E','S') then 'DE_Surrender'
                            when a.cvg_cstat in ('F') then 'DE_Maturity'
                            when a.cvg_cstat in ('H') then 'DE_Rider Claims'
                          end
                   from  i2016_mar b
                   where rownum = 1
                   and   a.pol_id = b.pol_id
                   and   a.cvg_num = b.cvg_num
                   and   a.cvg_iss_dt <= to_date('31-March-2016')
                   and   b.cvg_cstat in ('1','2','3','4')
                   and   a.cvg_cstat not in ('1','2','3','4')
                   and   a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9'));
commit;

update i2017_mar_ye
set    status = case
                  when cvg_cstat in ('1','2','4') then 'NB_Inforce'
                  when cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'NB_Others'
                  when cvg_cstat in ('B','I','K','N') then 'NB_Lapse'
                  when cvg_cstat in ('D') then 'NB_Death'
                  when cvg_cstat in ('E','S') then 'NB_Surrender'
                  when cvg_cstat in ('F') then 'NB_Maturity'
                  when cvg_cstat in ('H') then 'NB_Rider Claims'
                end
where  cvg_iss_dt > to_date('31-March-2016')
and    cvg_cstat not like 'P%'
and    cvg_cstat not like 'R%'
and    cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9');
commit;

Here only, DE series is updated. RE series gets set to null.

For the first two statements, try:

set    a.status = COALESCE( (select case
                        ...
                                and   a.cvg_type_cd not in (...)), a.status);

This way, if the select case statement generates a NULL value, you'll set the status back to what it already was.

For the third, try this:

set    status = case
                  ...
                  when cvg_cstat in ('H') then 'NB_Rider Claims'
                  else status
                end

Similarly, rather than allowing the case statement to return a NULL value, you force it to return the original status if none of the updated values match.

NOTE: You might get better performance in the first two queries if you modified the query to eliminate rows from a (that is, i2017_mar_ye) that should not be updated. For instance, in the second query, add a WHERE clause on the outer query:

WHERE a.cvg_cstat NOT IN ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR','B','I','K','N','D','E','S','F','H')
  AND a.cvg_iss_dt <= to_date('31-March-2016')
  AND a.cvg_cstat not in ('1','2','3','4')
  AND a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9');

You can remove the last three of those lines from the subquery at that point, as well.

Depending on your DBMS, you may be able to use a join between i2017_mar_ye and i2016_mar within the UPDATE statement, but only update i2017_mar_ye. That might allow for better performance as well.


Order result by number of occurence of a word in a text I am working with sqlite3/python .

I would like to order results by the number of occurence of %@% in text.

For exemple

Jimmy 10 // Jimmy wrote 10 strings like %@%
James 2
Sophia 0
..etc

The code could be something like this :

cursor = db.execute("SELECT name, text
                       FROM MyTable
                      WHERE text LIKE '%@%'
                      ORDER BY someMagic DESC")

I am looking for someMagic if it exists :-)

SELECT LENGTH(text)-LENGTH(REPLACE(text,'@',''))as counting from table
ORDER BY counting DESC

SQL Fiddle


"Could not insert a backup or restore history/detail record in the msdb database" While restoring msdb Today I found msdb database was in suspected mode, so I restored MSDB from my latest backup(As I have daily backup for this database). When I restore the MSDB database  using Tsql i got below errors, but database restore was successful.

Processed 17056 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 5 pages for database 'msdb', file 'MSDBLog' on file 1.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).
Msg 3009, Level 16, State 1, Line 2
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
The statement has been terminated.
RESTORE DATABASE successfully processed 17061 pages in 6.953 seconds (19.169 MB/sec).

But after restore I found that SQL server instance is running proper and all database are accessible. But to ensure about the error I got during restore I used CHECKDB command for the MSDB database.It shows below errors.

DBCC results for 'msdb'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 2515, Level 16, State 1, Line 1
The page (1:1044), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'msdb'.

So what are these errors? Also is it due to the error I got during restore? Is there anything I need to do as I got error while restore?

Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).

This might happen if someone have done a reseed on the dbo.backupset.

From table definition :

PK__backupse*  --> clustered, unique, primary key located on PRIMARY --> backup_set_id

To fix that error :

--- get the max backup set id from the backupset table

    SELECT MAX(backup_set_id) + 1 FROM msdb.dbo.backupset

 --- then reseed it

   dbcc checkident('dbo.backupset', RESEED, VALUE_FROM_ABOVE)

Now run DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS to see if everything returns fine.

  Msg 8921, Level 16, State 1, Line 1
  Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Try running DBCC CHECKDB with TABLOCK hint + make sure that the drive tempdb resides has enough space and tempdb is not having restrictive growth (autogrowth OFF).

Lastly,

  Msg 2515, Level 16, State 1, Line 1
  The page (1:1044), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap.
  CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).

If you run DBCC CHECKDB('msdb') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGSand still it gives error, then its most likely a problem with your IO subsystem. Try to check windows event logs.

Have Page checksums enabled. Also, take a full backup of msdb (which will reset differential bitmap) and then run DBCC CHECKDB('msdb') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGS

In an unlikely event, you should try going backward and find a good backup to restore.


Find least recently used mysql table index I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables.

While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if there is a tool that could help me find out least recently used indices from all the tables.

For eg. , if table "A" has three indices defined "index_1", "index_2" and "index_3", and out of them "index_3" is used least frequently , assume its used every 1/10000 queries made on the table, then the output of the script or tool should be "index_3".

Is there a good way or a tool that could help me run this analysis on the database?

Thanks in advance.

Starting with MySQL 5.6, the performance_schema instruments Table I/O, and computes aggregated statistics by table, and by index.

See table performance_schema.table_io_waits_summary_by_index_usage:

http://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html#table-io-waits-summary-by-index-usage-table

Finding the least recently used index involves time and timestamps.

The performance schema measure counting I/O against an index.  It can be used to find the least often used index, which in practice should be pretty close.

Full example here:

http://sqlfiddle.com/#!9/c129c/4


MySQL importing csv file by Data import wizard I tried importing a CSV file in MySQL via the data import wizard.
My CSV file has 70,000 records but only 61 are imported.
I tried checking the CSV file, recreating the file from source, and changing the location of the CSV file, but nothing works.
I even reinstalled MySQL version 8.0.25.
Please suggest a solution or any other way of importing.

Try using mysqldump command for dumping data from source to destination server.
Step1 : Take backup from Source server for particular table.
mysqldump --single-transaction  --set-gtid-purged=OFF  --force  -h 192.168.XX.XX -u user_name -p --databases database_name --tables table_name --lock-tables=false > mysql_user.sql

Step2 : Restore the backup on Destination.
mysql -u user_name -p -h192.168.X.X database_name < mysql_user.sql