texts: Database after restore process still display Restoring

texts:
I restored Database from Backup File (in Management Studio) and after restore, Database showed Restoring, then I deleted files from disk (engine's stopped), then I tried Restore again with success, on both cases I leave Restore Process on Default Options

I have no idea why, dump file I created after restart of enviroment, without active Connection(s)

my question: is possible somehow to stop the Restoring status, if is there some tools for that


texts: Optimize SELECT DISTINCT (with existing index) to avoid sequential scan

texts:
I have a records table with ~3 million records. The table has a column called record_type with an index.

                                         Table "public.records"
      Column       |            Type             |                      Modifiers
-------------------+-----------------------------+------------------------------------------------------
 id                | integer                     | not null default nextval('records_id_seq'::regclass)
 record_type       | character varying(255)      |

Indexes:
    "records_pkey" PRIMARY KEY, btree (id)
    "index_records_on_record_type" btree (record_type)

The following query takes more than 1.8 seconds to complete:

SELECT DISTINCT record_type FROM records;

If I check the query planner, it looks like PostgreSQL performs a sequential scan on the table. I would expect the database to take advantage of the index.

EXPLAIN SELECT DISTINCT record_type FROM records;
                              QUERY PLAN
-----------------------------------------------------------------------
 HashAggregate  (cost=101961.23..101961.40 rows=17 width=3)
   Group Key: record_type
   ->  Seq Scan on records  (cost=0.00..94465.78 rows=2998178 width=3)
(3 rows)

Is there a way to speed up this query?


texts: Grouping on any one of multiple columns in Postgres

texts:
Is it possible to create some sort of chain of grouping in Postgres? Let's say I have the following chart:

CREATE TABLE foo AS
SELECT row_number() OVER () AS id, *
FROM ( VALUES
  ( 'X', 'D', 'G', 'P' ),
  ( 'F', 'D', 'L', 'M' ),
  ( 'X', 'N', 'R', 'S' ),
  ( 'Y', 'I', 'W', NULL ),
  ( 'U', 'Z', 'E', NULL )
) AS f(a,b,c,d);

id | a | b | c | d
------------------
 1 | X | D | G | P
 2 | F | D | L | M
 3 | X | N | R | S
 4 | Y | I | W |
 5 | U | Z | E |

I want to somehow craft a GROUP BY that yields three groups:

1, 2 and 3 together

1 and 2 because of a common D in the b column
1 and 3 because of a common X in the a column

4 alone (no common values in any of the columns; nulls shouldn't match)
5 alone (no common values in any of the columns; nulls shouldn't match)

I'm currently using Postgres 9.5, but we'll upgrade eventually to 9.6, so if there's anything in there that'll help me, I'm open to hearing it.

In other words, I'm looking for something like (let's say I used array_agg(DISTINCT a), etc. to keep display simpler):

   ids    |     as     |     bs     |       cs        |      ds
-----------------------------------------------------------------------
{1, 2, 3} | {'X', 'F'} | {'D', 'N'} | {'G', 'L', 'R'} | {'P', 'M', 'S'}
{4}       | {'Y'}      | {'I'}      | {'W'}           | {NULL}
{5}       | {'U'}      | {'Z'}      | {'E'}           | {NULL}

(I'm not exactly sure how the nulls would display so don't get too hung up on that; the important point is that they shouldn't match each other.)

When I use GROUP BY CUBE (a, b, c, d), I get way more than three results...ditto GROUP BY ROLLUP and GROUP BY GROUPING SETS.

Is there an elegant way in Postgres? I can imagine how you would do it in Ruby via Active Record (loop through every record, group it with previous grouped sets that match), but I'd like to keep this in Postgres if possible.


texts: MySql/MariaDB cannot change default datadir on Debian 9.1 server

texts:
I have a problem to move the default datadir of Maria DB to another partition, it appears to be very common but i tried everything I can without luck.

Mysql is installed as Mariadb 10.1.26 with the default debian package (apt-get install mysql-server) on a Debian 9.1 (stretch) server, mysqld -v returns mysqld 10.1.26-MariaDB-0+deb9u1

Default_mysql_datadir : /var/lib/mysql

New_mysql_datadir : /home/mysql

/var/lib/mysql is mounted to "/" (/dev/md3)

/home/mysql is mounted to "/home" (/dev/md4)

What I've tried

# systemctl stop mysql
# mv /var/lib/mysql /home

Change datadir in /etc/mysql/my.cnf

datadir = /home/mysql

Check if the rights/permissions are ok

# chown -R mysql.mysql /home/mysql

apparmor is NOT installed nor running on the system though the /etc/apparmor.d/usr.sbin.mysqld file is existing with the following rules :

/home/mysql/ r,
/home/mysql/** rwk,

I even tried to create and empty /var/lib/mysq folder refering to this bug

But when I start I always get the same error :

# systemctl start mysql
[Warning] Can't create test file /home/mysql/<user>.lower-test
#007/usr/sbin/mysqld: Can't change dir to '/home/mysql/' (Errcode: 13 "Permission denied") 2017-09-07  0:16:59 140119808397888 [ERROR] Aborting
mariadb.service: Main process exited, code=exited, status=1/FAILURE
Failed to start MariaDB database server.
mariadb.service: Unit entered failed state.
mariadb.service: Failed with result 'exit-code'.

Any suggestion ?

Thanks


texts:
Here is my old query which was working fine :

SELECT tag.tag
FROM tag, v_bookmark_tag_assoc
WHERE tag.tag_id = v_bookmark_tag_assoc.tag_id
AND v_bookmark_tag_assoc.bookmark_id = '123'
ORDER BY RAND() LIMIT 10

Now I need to update the query to feed in all the "v_bookmark_tag_assoc.bookmark_id" as in IN clause of MySql, so that I can have all the result up in one output array for all input ids.

Problem is if if modify query like so :

SELECT tag.tag
FROM tag, v_bookmark_tag_assoc
WHERE tag.tag_id = v_bookmark_tag_assoc.tag_id
AND v_bookmark_tag_assoc.bookmark_id IN(123,456,678,890)
ORDER BY RAND() LIMIT 10

It doesn't make sense at all using LIMIT of 10 for each input id is illegitimate here this way.

Please help me out to modify this query in such a way that I can have 10 tags for each input bookmark id.


texts: Oracle: Traversing a graph of my foreign keys?

texts: Given a table, what is a query that will show foreign key constrains, both immediate and nested? I.e. if T2 has an FK on T1, and T3 has an FK on T2, I would like to show T1 -> T2 -> T3.


texts: What are "Invisible Columns" and how are they used?

texts:
I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature?

  Columns can be given an INVISIBLE attribute in a CREATE TABLE or ALTER
  TABLE statement. These columns will then not be listed in the results
  of a SELECT * statement, nor do they need to be assigned a value in an
  INSERT statement, unless INSERT explicitly mentions them by name.

  Since SELECT * does not return the invisible columns, new tables or
  views created in this manner will have no trace of the invisible
  columns. If specifically referenced in the SELECT statement, the
  columns will be brought into the view/new table, but the INVISIBLE
  attribute will not.

  Invisible columns can be declared as NOT NULL, but then require a
  DEFAULT value


texts: Impact of replication compression & encryption on performance, traffic?

texts:
What is the impact of MySQL compressed-slave-protocol on replication traffic?
According to High performance MySQL it can lower the traffic to a third of the same replication without the compression. Can you give me something more accurate (more information on this topic)?

What about SSL encryption? It will surely enlarge every single packet, I am not sure how much of an impact it will have on the overall traffic though.

What about performance?


texts: Replication Server version 15.7.1 - Sybase ASE 15.5 to Oracle EE 12.1.0.2 (64 bit) replication failing

texts:
I have 2 Sybase DB and 1 Oracle DB, I would like to replicate one of the tables in Sybase DB X (table name: customer_order) into my Sybase DB Y (table name: customer_order), and from the Sybase DB Y replicate into Oracle. The replication linking is like this:-

Sybase DB X --> Sybase DB Y --> Oracle

The replication from Sybase DB X to Sybase DB Y is working fine, but replication failed from Sybase DB Y to Oracle. When I tried to insert/delete/update some records on Sybase DB Y, it has been replicated to Oracle, but...when I tried to insert/delete/update some records on Sybase DB X, these transactions are able to replicate to Sybase DB Y, but it couldn't replicate into Oracle. WHY??

Appreciate if anyone could solve my doubts. Thanks a lot.

FYI:-

Replication Server version 15.7.1
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Adaptive Server Enterprise/15.5/EBF 19395 SMP ESD#5/P/Sun_svr4/OS 5.8/asear155/2568/64-bit/FBO/Fri Dec 9 09:16:52 2011


texts: How to define the logical name in a backup ? ( Sql Server 2008 )

texts:
I saw this question but it didn't help me.

Is there a simple way to do :

Backup database SomeDatabase
   to disk ='c:\teste\SomeDatabase.bkp'

configuring the Logical Name?

We have some databases, restored of backups of another database (to follow same structure) and when I use RESTORE FILELISTONLY, it shows me a different name. So the Database TestDBA has the logical name BANK.


texts: Using postgres with logstash - log filename is causing issues

texts: I'm having some trouble forwarding postgres logs to logstash. Normally it's pretty easy to do, but the problem with postgres is that the current logfile has the current date in the filename, so the name changes every day, making it very difficult to put the filename into logstash's config. Is there anything I can do about this? Maybe there's some trick to logstash that I can use to resolve this?


texts: How to connect an MSSQL database engine to a local network

texts:
I need to let my public IP connect to a database that is located within the same local network.

Ex: MY public IP is: 190.222.222.222 The local IP of that PC is: 1.1.1.4 And this server already has an MSSQL (Localhost) engine. This means that if I connect to the Public IP it automatically calls me the LOCALHOST database engine.

I need that at the time of connecting to the public IP this call / connect to the local server database engine 1.1.1.7

The connection between server 1.1.1.4 and 1.1.1.7 is working. The firewall ports are open


texts:
Does anyone have any SQL code to automatically generate randomized birth dates, where the date of birth is less than today? Please add Date of Birth Range parameters, eg: from 18 to 70 years old.

Is there any inline SQL or function to do this?

We are trying to obfuscate our column [Date of Birth].

Thanks,


texts: In which table we should put the foreign key

texts: For example I have a table Admin and a table Role. To do one-to-one relationship between these two tables I can put the foreign key either in the Admin or the Role table. My doubt is if its icual put the foreign key either on the Amin or Role table?


texts: Teradata Install 32bit appears to have failed

texts:
I'm getting the error "The installation of ODBC -32bit appears to have failed. Do you want to continue the installation?" and I'm not sure how to solve it.

I've never installed teradata before, and I've tried clicking yes to continue. Even if I continue I'm not able to find teradata in my list of drivers when I'm creating a new data source.

Any suggestions or help would be greatly appreciated.

Edit:
I'm installing the 16.10.05.00 version that I got from the teradata website into a Windows 10 laptop.

I have had other co-workers be able to install the version on similar laptops just fine.


texts: Load database from Solaris (Sybase 15.0.3) to VMWare Solaris (Sybase 15.7.0)

texts:
I need to perform a load of a Sybase (15.0.3) Database from a Sun Solaris 5.10 environment   (on sparc architecture) to Sybase (15.7.0) on Solaris 10 (on i86pc architecture) running on my vmware virtual machine.

Does anyone have a recipe (correct steps) ?


texts: Shrinking database which is not having any insert/update

texts:
We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any insert/update/Delete.

As this database will only be used for reporting, we are thinking of shrinking the database. However, this increases the index fragmentation. We are thinking of following below steps:

Shrink the database with re-organize
Re-organize indexes

Will this hamper the database performance? What other options can we consider for recovering the space?

Update: In the process, We change the recovery model to simple, backup the database to move the database out of Pseudo-Simple state, and then planning to shrink the database.


texts: Copy large database from one server to another

texts:
I want to copy a database from one server to another. The source database is a geo-replicated read-only secondary. In case it matters, I want to use this as the source to avoid loading up the production (primary) database.
The source and target servers are in different resource groups, but they are located in the same area and in the same subscription.
This might seem like pretty standard stuff, but the problem I am having is that neither the export functionality nor the copy functionality within the Azure portal will work. The export fails with

Error code 400: the size of the database to be exported is bigger than what is supported for this operation.

The copy fails in a slightly different way; when I try to configure the target database I cannot set the data storage size, it defaults to 32GB and the slider to change it is missing:

The source database is a little over 1TB in size.
What other options do I have for creating a copy of that database? Or is there a way to work around the error displayed above?


texts: Do I need to index a key that I partition by?

texts:
I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state.  I have an index on phone number, and the performance is great.  We also need to SELECT by last name in a given US state.  Should I create an index on lastname AND state, or there's no point in doing that because the table is already partitioned by state?

Will that take up unnecessary space, since InnoDB should already know what partition is which state, and it only has to find all rows matching last name in that partition?

We aren't doing any joins whatsoever...this is just for a simple query like this:

SELECT * FROM table WHERE lastname = "Smith" AND state="NY"


texts: Mimic Standard Edition Functionality on Evaluation/Developer Edition

texts:
In an attempt to do proper Edition Evaluations for future projects, I'm trying to find a good way to mimic Standard Edition functionality limitations on a SQL Server 2016 Evaluation or Developer Edition instance.  I'm thinking that for a given database, I could configure Resource Governor in tandem with some DDL triggers that block Enterprise-feature commands via EVENTDATA calls to emulate a lot of the resource usage and feature limitations that are specific to Standard Edition.  Certain instance-level limitations such as Buffer Pool size, etc. cannot be emulated directly, but I can limit many of these things at the query level which I'll consider good enough.  Is there anything that the combination of Resource Governor and DDL triggers wouldn't be able to cover?  Alternatively, does anyone have a solution already available to do this that I've not yet stumbled across?  Are there other approaches that would be easier?

This question relates to the core database engine.  I don't think there's even a feasible approach to trying this with other features such as SSIS, SSAS, etc., but if anyone knows of a similar approach for those features, I'm all ears.

Final note here for anyone suggesting I just install Standard Edition; I hesitate on this approach due to the licensing costs that would be associated with it.  I would either have to purchase licenses outright or have sufficient MSDN license coverage for any users touching it.  Sadly, this project is going to involve more people than have MSDN licenses and the cost to fully purchase Standard Edition 6+ months prior to the project going live isn't going to get approval from a budget committee, especially when approval of the project rests of the feasibility testing we need to conclude beforehand.


texts:
I am stuck at getting a query with joining 2 tables. Below is the database structure.
What I want to achieve is to list all the information from one table with join on the second table but it has 3 conditions. I have written the following SQL but it's not working.I want only 2 rows returning all rows data in each 3 columns.
Please advice.

Tables are:

My SQL:

SELECT b.*,
       CASE WHEN pc.caster_type = '1' THEN pc.name END as  primary_caster ,
       CASE WHEN pc.caster_type = '2' THEN pc.name END as  secondary_caster ,
       CASE WHEN pc.caster_type = '3' THEN pc.name END as  caster
FROM broadcasts b
left join rel_braodcast_casters pc ON pc.fk_broadcast_id = b.broadcast_id ;

Getting this result:

I do not want multiple records for each broadcast. I want to replace NULL values with corresponding rows of data.

Thanks in advance.


texts:
We are noticing performance issues with one of our applications.

When the application issues begin the procedure cache size goes from 4.5GB to 0. The plans are then rebuilt and still the application is slow. If I update stats on a key table the issue goes away.

I'm just trying to understand how procedure cache may drop other than DBCC FREEPROCCACHE?

EDIT: Sorry this is SQL 2005 9.00.4226


texts: Are sql tuning ways same for different DB engine？

texts:
I used Oracle for the half past year and learned some tricks of sql tuning,but now our DB is moving to greenplum and the project manager suggest us to change some of the codes that writted in Oracle sql for their efficiency.

I am curious that Are sql tuning ways same for different DB engine,like oracle,postgresql,mysql and so on？if yes or not,why?Any suggestion are welcomed!


texts: Install two instances of mysql 5.6 and 5.7

texts:
How to install two mysql servers on one Debian server?

apt-get install libaio1
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.11-1debian8_amd64.deb-bundle.tar
tar -xvf mysql-server_5.7.11-1debian8_amd64.deb-bundle.tar
dpkg-preconfigure mysql-community-server_*.deb && dpkg -i mysql-{common,community-client,client,community-server,server}_*.deb
apt-get -f install

How to set datadir, port number etc so the new installation don't override the current server setup?


texts: does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server

texts:
when an automatic checpoint runs in background in sql server, i know it flushes all the dirty pages to data file.

does it also flushes dirty pages which are related to un-committed transactions into data file or it only flushes dirty pages related to committed transactions?

I am still not able to get a single source where I can get the entire information about database recovery in SQL Server. More so, I am interested to how SQL Server does recovery in case of system failure and how it depends on CHECKPOINT and are there any other factors on which DB recovery depends?

Does recovery process depends on Recovery model as well?

Any inputs / pointers would be greatly appreciated. :)


texts: Pivoting Dynamic Rows into Columns in MySQL

texts:
I have two tables Student_Attendance and Students.

I am using this query:

SELECT
    sa.Student_Id,
    sa.Attendance_Date,
    CASE WHEN sa.Attendance_Status as Status
FROM student_attendance sa
INNER JOIN courses c
    ON c.Course_Id = sa.Course_Id
INNER JOIN students es
    ON es.Student_Id =  sa.Student_Id
WHERE sa.Course_Id = 1
ORDER BY sa.Attendance_Date DESC;

I am getting this result from above query:

But I want something like below:

In the student_attendance table there are more than 50 rows.

How 7 attendance_date rows should be converted into 7 columns as I have given below?

Can someone help me please?


texts: Server quit without updating PID file when trying to start MySQL server?

texts:
So I created my .bash_profile, opened it in a text editor and added:
export PATH=$PATH:/usr/local/mysql/bin

and then I saved it. Next, I tried running
sudo /usr/local/mysql/support-files/mysql.server start

was told to enter my password (so I did), and then terminal said
Starting MySQL

and then it kept printing dots until it said:
ERROR! The server quit without updating PID file (/usr/local/mysql/data/Nicks-MacBook-Pro.local.pid).

The error logs look like:
2021-01-25T20:50:49.707062Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.23) starting as process 15092
2021-01-25T20:50:49.710232Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2021-01-25T20:50:49.834599Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-25T20:50:49.943719Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-25T20:50:50.037810Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-01-25T20:50:50.125494Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-25T20:50:50.126128Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-25T20:50:50.151069Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.23'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
~
~
~
~
~
(END)

The output of ls -l /usr/local/mysql/data is:
total 377992
-rw-rw----    1 _mysql  admin    196608 Jan 27 23:20 #ib_16384_0.dblwr
-rw-rw----    1 _mysql  admin   8585216 Jan 25 15:50 #ib_16384_1.dblwr
drwxrwx---   12 _mysql  admin       384 Jan 27 22:34 #innodb_temp
-rw-rw----    1 _mysql  admin    112320 Jan 28 19:07 Nicks-MacBook-Pro.local.err
-rw-rw----    1 _mysql  admin        56 Jan 25 15:50 auto.cnf
-rw-rw----    1 _mysql  admin       667 Jan 25 15:50 binlog.000001
-rw-rw----    1 _mysql  admin       528 Jan 27 22:54 binlog.000002
-rw-r-----    1 _mysql  admin       156 Jan 27 22:34 binlog.000003
-rw-r-----    1 _mysql  admin        48 Jan 27 22:34 binlog.index
-rw-rw----    1 _mysql  admin      1676 Jan 25 15:50 ca-key.pem
-rw-rw----    1 _mysql  admin      1112 Jan 25 15:50 ca.pem
-rw-rw----    1 _mysql  admin      1112 Jan 25 15:50 client-cert.pem
-rw-rw----    1 _mysql  admin      1676 Jan 25 15:50 client-key.pem
-rw-r-----    1 _mysql  admin      3372 Jan 27 22:54 ib_buffer_pool
-rw-rw----    1 _mysql  admin  50331648 Jan 27 23:20 ib_logfile0
-rw-rw----    1 _mysql  admin  50331648 Jan 25 15:50 ib_logfile1
-rw-rw----    1 _mysql  admin  12582912 Jan 27 22:35 ibdata1
-rw-r-----    1 _mysql  admin  12582912 Jan 27 22:35 ibtmp1
drwxrwx---    8 _mysql  admin       256 Jan 25 15:50 mysql
-rw-rw----    1 _mysql  admin  25165824 Jan 27 22:35 mysql.ibd
-rw-rw----    1 _mysql  admin      2481 Jan 27 22:34 mysqld.local.err
-rw-r-----    1 _mysql  admin         4 Jan 27 22:34 mysqld.local.pid
drwxrwx---  111 _mysql  admin      3552 Jan 25 15:50 performance_schema
-rw-rw----    1 _mysql  admin      1680 Jan 25 15:50 private_key.pem
-rw-rw----    1 _mysql  admin       452 Jan 25 15:50 public_key.pem
-rw-rw----    1 _mysql  admin      1112 Jan 25 15:50 server-cert.pem
-rw-rw----    1 _mysql  admin      1676 Jan 25 15:50 server-key.pem
drwxrwx---    3 _mysql  admin        96 Jan 25 15:50 sys
-rw-rw----    1 _mysql  admin  16777216 Jan 27 23:20 undo_001
-rw-rw----    1 _mysql  admin  16777216 Jan 25 15:50 undo_002

How do I fix this?


texts: SQL Server TDE Stuck at Encryption_State 2 and 0% on a Tiny DB

texts:
I'm having a heck of a time with this issue and can't figure out what is wrong. I'm not sure how long this tiny DB has been in 'encryption_state = 2' from query:

SELECT * FROM sys.dm_database_encryption_keys

but it won't budge past 0%. State 2 means that it is currently trying to encrypt the DB.  No other DB has any issues, encrypted or unencrypted.

Command:

ALTER DATABASE x SET ENCRYPTION OFF

Result:

  Msg 33109, Level 16, State 1, Line 2
  Cannot disable database encryption while an encryption, decryption, or key change scan is in progress.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.

Running:

ALTER DATABASE x SET ENCRYPTION ON

actually returns a command completed, but percentage still stays at 0%.

There's no DB corruption from DBCC CHECKDB. There's no locking/blocking going on (not that the logical blocking would affect TDE since it affects the DB on the page level).  I'm at a loss short of calling MS on this. Anyone have any ideas? I'm going to try to restore this to a different DB server and test unencrypting the backup there.

Thanks.

SELECT DB_NAME(database_id), percent_complete, *
FROM sys.dm_database_encryption_keys
go

Edit & update:

Restored to a diff server with the cert, it goes to 1.187865% for 'percent_complete' then immediately reverts back to 0% in about 1 seconds time.  Firing up profiler now to catch something in the background perhaps, checking to see if extended events would help.

Oh boy, profiler shows error 824 suspect DB page. DBCC CheckDB consistently shows no errors. Time to brush up on my CHECKDB internals, I know Paul Randal blogs about error 824. Will update this for others who might have this issue.

Final Edit:
Resolution to this problem was that unused sections of the PRIMARY filegroup had SGAM corruption.  CHECKDB  doesn't check empty blocks so it never found it out.  Although there was no chance of anything being written there, figured best COA was to move all the data to a new DB during a brief outage using RedGate and testing it a few times.  Just made it in time.  Lesson learned: To test corruption on unwritten sections of the database, turn on and off encryption as CHECKDB won't.


texts: Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

texts:
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4.  Is there a way to generate ALTER statements for every table and column that needs to change?

I'm upgrading MySQL hosted on Amazon RDS from MySQL 5.7 to 8.0.  The Pre-patch compatibility tool tells me:

  The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.

  More Information:
  https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

It then lists about 6,000 databases and columns that need to be updated:

mydb - schema's default character set: utf8
mydb.mytable.mycolumn - column's default character set: utf8

I'd like to be able to generate all the ALTER statements I'll need to run similar to how MySQL command querying all MyISAM database helped my convert all my tables from MyISAM to Innodb.


texts: Affinity side effects / TF 8002- how to measure

texts:
Environment: We have Microsoft SQL Server 2016 (SP2-CU15) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0.
We have multiple SE instances on one physical server.
We use processor affinity mask to tell which instance use what cores. Some cores are dedicated to instance, but most cores are shared between more than one instance. (Without using affinity mask we would not be able to use all physical machine power)
Problem: we have noticed that one instance is disturbing another one in case there is some heavy CPU load is running. Especially if that is one (!) big query running for long period of time. There always seems to be enough CPU power left available to affected instance not to experience visible slowdowns.
I do believe that trace flag 8002 would help in this situation as described in this link.
What is the the wait stats (or other metrics) that I should monitor to ensure that trace flag really gives desired positive effect?


texts: Getting the Date of Some Day of Last Week

texts:
In learning how to get the date of a day of the week from the previous week (e.g. the date of Monday from the previous week), I found the following two expressions coming up commonly, where you change the very last number (e.g. 0=Monday) depending on which day you want:

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

SELECT DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)

Both seem to return the correct Date 2017-05-29 when I run it today (2017-06-17).

My question is - how does this work?  It seems to be getting a count of weeks since 6 or 7 weeks after 1900-01-01? And why does it work with both a 6 and 7?

Also, are there edge cases one would need to be aware of where it might not give the expected results?


texts: Wildly different performance for CTE vs. duplicated table expression

texts:
I have a view written like

create view v1 as
with conn_query as (xxx)
select stuff from conn_query where some_cond
union
select stuff from conn_query where some_other_cond

But suddenly querying this view became very slow (10 seconds for a result of 2 records), even though the
single queries in the union where very fast (milliseconds). Apparently the union caused xxx to be
fully evaluated without pushing down the arguments (10 seconds is about also the time it takes to select count(*) from v1)

Rewriting the view to

create view v1 as
select stuff from (xxx) where some_cond
union
select stuff from (xxx) where some_other_cond

fixed this. Unfortunately, this makes now the SQL xxx appear twice which is a bit ugly.

So now I'm curious if on a conceptual level, is there a good reason for this behaviour?

This is with Oracle 11.2.0.4.0

UPDATE/DUPLICATE:

I got exactly the expected fast behaviour with the cte variant by using

create view v1 as
with conn_query as (select /*+INLINE*/ xxx)
select stuff from conn_query where some_cond
union
select stuff from conn_query where some_other_cond

as suggested in https://dba.stackexchange.com/a/172559/137386. This undocumented hint prevents Oracle from trying to be smart and materialzing the CTE just because it is accessed more than once.


texts: How do I fix the naming of columns in Metabase?

texts:
Metabase currently imports the column names and tries to figure out the wording. This convention is fundamentally broken in my schema. For example, when it scans my PostgreSQL database it'll bring in the columns as,

As St Tre As Appoint Or Name First
As St Tre As Appoint Or Name Last
As St Tre As Name First
As St Tre As Name Last
As St Tre As Name Organization
As St Tre As Name Prefix Cd
As St Tre As Name Short
As St Tre As Name Suffix Cd
As St Tre As Per Sent Type Cd
As St Tre As Primary Phone Ext
As St Tre As Primary Phone Number

In reality they look like,

assttreaspersenttypecd
assttreasnameorganization
assttreasnamelast
assttreasnamesuffixcd
assttreasnamefirst
assttreasnameprefixcd
assttreasnameshort
assttreasstreetaddr1

Where assttreas is a shorthand for "Assistant Treasurer". Is there any easy way to fix this or disable this "feature".


texts: How to clean up duplicate objects in SYS/SYSTEM

texts:
I have a sql query like below to check duplicate objects in SYS/SYSTEM.

select OBJECT_NAME, OBJECT_TYPE from
DBA_OBJECTS where OBJECT_NAME||OBJECT_TYPE
in (select OBJECT_NAME||OBJECT_TYPE from
DBA_OBJECTS where OWNER='SYS') and
OWNER='SYSTEM' and OBJECT_NAME not in
('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES',
'DBMS_REPCAT_AUTH');

I have a list of large duplicated objects. My question / problem is : how to get rid of the objects? What is the best way to approach this?


texts: What is the format of raw geometry column in PostGIS?

texts: It doesn't seem to be a WKB, since it's basically a string, so what is it?


texts: Negative amount of remaining transactions before wraparound

texts:
Running a PostgreSQL 11 instance with a table of more than two billion rows.

I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens:

Query source:

SELECT
   oid::regclass::text AS table,
   age(relfrozenxid) AS xid_age,
   mxid_age(relminmxid) AS mxid_age,
   least(
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid),
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;

Output:

-[ RECORD 1 ]---------------+------------------------------
table                       | tablename
xid_age                     | 2146483650
mxid_age                    | 0
tx_before_wraparound_vacuum | -1946483650
size                        | 1583 GB
last_autovacuum             | 2019-08-18 05:03:56.64232+00

Why does it return a negative number for tx_before_wraparound_vacuum?

Does this mean that there have been 1946483650 transactions more than allowed since the last wraparound protection was run?


texts: Query to find products with all the given attributes

texts:
I have a table in mySQL to store product attributes like this:

------------------------------------
| product_id | att_val1 | att_val2 |
------------------------------------
| 1001001    | x202     | 2200     |
| 1001002    | t100     | 180000   |
| 1001003    | T100     | 1080     |
| 1001001    | T100     | 700      |
| 1001005    | SS120    | 1080     |
| 1001001    | SS120    | 1080     |
| 1001004    | SS120    | 1080     |

I want to get all product_ids that have all the asked properties, I came up with this query

SELECT product_id FROM product_attributes
WHERE product_id EXISTS (SELECT product_id from product_attributes WHERE att_val1 = 'x202' AND att_val2 = '2200')
AND product_id EXISTS (SELECT product_id from product_attributes WHERE att_val1 = 'SS120' AND att_val2 = '1080')

Is there a better way to achieve this result?

UPDATED (better sample)

I'm going to change the sample data to string values for better clarification

-------------------------------------------
|  product_id  |  attribute  |  value     |
-------------------------------------------
| iPhone 6s    | Screen Size | 4.7        |
| iPhone 7     | Screen Size | 4.7        |
| iPhone 8     | Screen Size | 4.7        |
| iPhone 6s    | Storage     | 64GB       |
| iPhone 7     | Storage     | 128GB      |
| iPhone 8     | Storage     | 64GB       |
| iPhone 6s    | NFC         | FALSE      |
| iPhone 7     | NFC         | FALSE      |
| iPhone 8     | NFC         | TRUE       |

I want to get the iPhone 8 which has Screen Size = 4.7 AND Storage = 64GB AND NFC = TRUE. There could be more than 3 parameters to filter.

SELECT product_id FROM product_attributes
WHERE product_id IN (SELECT product_id from product_attributes
                        WHERE attribute = 'Screen Size' AND value = '4.7')
AND product_id IN (SELECT product_id from product_attributes
                        WHERE attribute = Storage AND value = '64GB')
AND product_id IN (SELECT product_id from product_attributes
                        WHERE attribute = NFC AND value = 'TRUE')
GROUP BY product_id

If I use OR in WHERE statement I will end up having iPhone 6s, 7 and 8.

SELECT product_id FROM product_attributes
WHERE product_id IN (SELECT product_id from product_attributes
        WHERE (attribute = 'Screen Size' AND value = '4.7')
        OR (attribute = Storage AND value = '64GB')
        OR (attribute = NFC AND value = 'TRUE')

So I think I've to repeat the AND product_id IN ... part for as many filters as I have.

I hope I'm wrong! and there is a better way to achieve this result.


texts: Does pg_restore continue after internet connection got lost

texts:
I know a query runs until it returns data when connection to the client is lost. However, what if you use pg_restore. Does it upload the restore file and then does the restore on the server? Or does it restore step by step requiring the client to stay connected.
I want to know this because my employee uses a vpn that disconnects automatically after some time. And the restore takes longer that the timeout XD.
When the connection got lost I got the pg_restore: error: error returned by PQputCopyData: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.  error.
However on the server I can see the process is still up and running using SELECT * FROM pg_stat_activity;


texts: Trying to monitor CPU usage; difference between last_execution_time and total_time_elapsed?

texts:
I've been using this Query in order to find the top 20 CPU usages of a day the last 2 hours.

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan,
    qs.last_execution_time
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
where last_execution_time between dateadd(hour,-2,getdate()) and getdate()
ORDER BY
    qs.total_worker_time DESC, qs.last_execution_time

Which gives me the output (this is cropped, there are many more columns and rows in reality)

total_elapsed_time_inSeconds    last_execution_time
494020                          2017-06-12 10:39:13.003

I calculated the seconds to amass more than 100 hours, yet the last_execution_time is from 8 minutes ago?

Have I misunderstood the code, or does the total_elapsed_time_inSeconds accumulate and the Query is ran often as a SQL job or so?


texts: Connecting client application to MariaDB Galera cluster

texts:
I currently have a REST API written in Flask that connects to a MariaDB server. I'm thinking about replacing the server with a Galera cluster to improve availability and ensure continuity in case one of the nodes goes down. What I'm having trouble understanding is how client applications connect to the cluster itself. As I'm currently only using a single database server, I can connect using the following code:

engine = create_engine('mysql://username:password@hostaddress/database_name)

If I were to have three nodes in the cluster with the addresses 192.168.1.1, 192.168.1.2 and 192.168.1.3 how would I connect the application to the cluster? I assume that it would be possible to replace the current hostname value with one of the node IPs e.g. 192.168.1.1 but I'd imagine that if I did that and that node went down, the application would no longer be able to connect to the cluster because it's specifically trying to connect to that one node. How can I ensure that the application continues to function if a node fails?

I'm still new to the idea of Galera cluster so apologies if I've misunderstood something about how it works. Any advice would be much appreciated.


texts: Data Warehouse: Can a Transaction Table also be a Dimension?

texts:
We have a transaction table. Not only does sales team want to analyze by CustomerId or Productid sold, but also by sales amount sold or quantities.
Kimball states, a fact table here can also be a dimension. In that case, could I leave by transaction table as a large dimension table? Just want to understand Kimballs article below.

https://www.kimballgroup.com/2007/12/design-tip-97-modeling-data-as-both-a-fact-and-dimension-attribute/


texts: Is this pattern named and how to normalize it?

texts:
I am given a data table :

State ( StateId, StateNme,
        StateCounty1, Statecounty1Seat, StateCounty1Rep,
        StateCounty2, StateCounty2Seat, StateCounty2Rep,
        StateCounty3,...... )

Meaning for every StateCounty we have three entries. Does this pattern have a name?  How do we normalize this?

I assume we separate the triple StateCounty, StateCountySeat, StatecountyRep into a separate table. Then {State, StateCounty} is a candidate key (Fair assumption: no two counties on the same state have the same name). And we
can see it is already in 3NF.

Does this make sense?


texts: Does the target database need to have the same structure of the source database before to use IMPDP?

texts:
When I do a import from another database have I need the same estruture of the source database in the target database?
For example, if my source database use ASM and my target database no, what have I to do before to make the import?
In the last time I received an error during the import. The error was about the creation of a tablespace and the datafiles. The erro said that the path of the datafile does not exist. For this problem I used the remap_datafile. But I wonder if is there another way to do it without this parameters. Mainly if there are several datafiles
In the last time I didn't use ASM, it was file system on different machines
My scenery is a full export and import


texts: which option is better for not slow down the performance of queries?

texts:
I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the question is which option is best:

A. Record each payment made, this means customers have multiple records.

B. Have only one record per customer and an attribute in the database where there concatenate previous payments, so it's a string that has previous payments.

Note: The DB will have many customers that's why I'm worried about performance with this situation, especially when viewing the information on the DB.

I had never done such a system and therefore would not know how much will be affected the performance of the DB having one of these two configurations and a large number of records.

if there is a better solution is welcome


texts: Oracle query get schema name, size and creation date

texts:
I'm having trouble making a query with the following output, Schema Name, Size and Creation Date.
I'm new to Oracle and after more than 4 hours of searching I decided to create a question here.

I found the following query that returns name and size.

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group by owner;


texts: SQL Server backup failing. Error: 3041, Severity: 16, State: 1

texts:
First of all, I'm very new at stackexchange, so please bear with me.

I'm running a SQL Server 9.0.4060.

My problem is: my SQL Server backup keeps failing on a lot of the databases.

The Maintenance plan runs to different backup jobs:

DIFF backup everyday at 20:30 and FULL backup every Sunday at 20:30.

I get these two errors foreach database that fails to complete the backup:

Date        06-11-2012 20:31:06
Log     SQL Server (Current - 07-11-2012 11:43:00)
Source      Backup
Message
BACKUP failed to complete the command BACKUP DATABASE (dbname) WITH DIFFERENTIAL. Check the backup application log for detailed messages.

Date        06-11-2012 20:31:06
Log     SQL Server (Current - 07-11-2012 11:43:00)
Source      Backup
Message
Error: 3041, Severity: 16, State: 1.

Extra errors.

Date     06-11-2012 17:53:27
Log      SQL Server (Current - 07-11-2012 11:43:00)
Source   Logon
Message  Error: 18456, Severity: 14, State: 16.

Date     06-11-2012 17:53:27
Log      SQL Server (Current - 07-11-2012 11:43:00)
Source   Logon
Message  Login failed for user 'sa'. [CLIENT: <local machine>]

Date     06-11-2012 20:30:11
Log      SQL Server (Current - 09-11-2012 11:27:00)
Source   spid15s
Message
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

There are to other similar errors to the one above, with 'SQL Plans' and 'Object Plans'. Instead of 'Bound Trees'.

If you guys need any other information, just say the word.


texts: Is network traffic encrypted when writing remote backups using SQL Server TDE?

texts:
They do say there is no such thing as a “stupid question”, so here goes:

I understand that SQL Server Transparent Data Encryption (TDE) encrypts data at rest, so that your database files (.mdf) and your backup files (.bak) are encrypted should someone break into your storage and steal those files. I also understand that the data is decrypted when read from disk so that it is unencrypted in memory (in motion). Therefore data requested by a user running a remote query (select * from SensitiveData) will be unencrypted when travelling over the network and thus vulnerable to intercept.

So, assuming all the above is correct, here is my stupid question: If my SQL Server instance is on computer A and my TDE-database backups are written off to storage on remote computer B, is the backup operation data encrypted as it travels from computer A to be written to disk at computer B? I assume it must be (because I suppose the encryption operation occurs on computer A first), but I can’t find confirmation of this in any of the Microsoft documentation or on the blogs. And likewise, during a restore operation – were anyone to intercept the data being transferred from disk at computer B to restore the database at computer A – would they find that data in motion encrypted?


texts: How to grant only functions while revoking tables?

texts:
I am a newbie to PostgreSQL, and configuring a new database.

I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table.

GRANT CONNECT ON DATABASE db1 TO role1;
GRANT USAGE ON SCHEMA schema1 TO role1;

REVOKE ALL ON table1 FROM role1;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema1 TO role1;

If I don't REVOKE table privilege, direct table access will be allowed. If I REVOKE table privilege, and GRANT functions, Users access the functions but see table access restriction error.

psql:./test1.psql:6: ERROR:  permission denied for relation table1

How can I make user role1 can access data using only functions while disallowing all direct access to table? I think I missed something, but I can't figure it out.

P.S. I am using Postgres 9.2.x.


texts:
I'm comparing values from two different database.
In one table the name is stored with dots(i.e A.B C) and in another table the same value is stored as AB C(i.e in second table none of the names contain dots)
In SQL how can I compare the values? Dot can come anywhere in the name.


texts: How to deny drop, delete permission for a table?

texts: I want to give Create , alter , read permission on object to user but not insert , update ,delete,drop permission to user , How can i do it in sql server 2008?


texts: SQL Error: ORA-14300: While partitioning and subpartitioning

texts:
I have tried this earlier and it works:

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

My data is stored like this..

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

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

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

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

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

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

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

Please let me know if there is a solution.


texts:
This one has me stumped and I'm sure there is a simple explanation that I cannot fathom at present.

A few informational pieces:

vCentre Database - SIMPLE Recovery Model
SQL Server 2008 R2 Standard Edition

We have a FULL Database backup (driven by Ola's scripts) that is failing as a result of a full transaction log.   We are running the backup WITH CHECKSUM which I suspect may be the cause, but I don't entirely understand why the FULL Backup would need to perform a write operation against the database log file.

I'm conscious that the FULL Backup will need to read from the transaction log to the LSN of the oldest active transaction for consistency. However, does the FULL BACKUP task write a minute amount into the log file to note that it's happened / happening as well?

Error from the Ola Log:

Date and time: 2017-03-13 21:30:40
Command: BACKUP DATABASE [DB] TO DISK = N'M:\Folder\file.bak' WITH CHECKSUM, NO_COMPRESSION

Processed 1579192 pages for database 'DB', file 'DB_dat' on file 1.

Processed 855886 pages for database 'DB', file 'DB_log' on file 1.

Msg 9002, Level 17, State 3, Server SERVER\INSTANCE, Line 1

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Msg 3013, Level 16, State 1, Server SERVER\INSTANCE, Line 1

BACKUP DATABASE is terminating abnormally.

Outcome: Failed

Duration: 00:13:19


texts: How to name a constraint which was unnamed during the table creation?

texts:
I had created the table by specifying the columns as primary key and not null without giving them any constraint names and I have populated the data. I want to give names to them now.

How do I do this in oracle or mysql ?

If I can't do this what's the easiest and next best solution?


texts: Altering the existing partition which is partitioned by date range in SQL Server 2012

texts:
I have a huge partition table with almost 1 billion records which is partitioned by date range i.e. from 2015-01-01 to 2017-12-01 with 1-36 partitions. There is a requirement of adding data to this table prior 2015 i.e. adding data of the year 2014. So i tried doing the below

ALTER PARTITION FUNCTION partition_function_name()
{
    SPLIT RANGE ( boundary_value ) };

But this can be done for adding the partitions after 2017 and I want to add it before 2015.
Is there a way I can add the partitions prior 2015 efficiently without moving the existing data as the data is very huge.


texts: Slow MySql server - even on a super fast server

texts:
I have just upgraded my 12 year old server to a really fast new one.  It is a dedicated server, Dell R620 1 x Intel E5-2620 (2.0GHz, 6C) CPU 32GB RAM - 4 x 250GB SSD RAID 1, in short vastly faster in every way to my old server.  However I still have a rather slow response whenever I query the MySql database.

Loading up any static is lightning fast.  Also when I view my other website (this server hosts two websites) that makes use of Mongodb, it is lighting fast.  Currently I'm only using +- 2.5Gb or ram out of the 32Gb.  Is there some setting for MySql to allow it to use more resources in order to speed up?  Alternatively is there anything else I can do (just dealing with settings or MySql queries) to speed up this slow MySql server?

The MySql version is: 5.1.73.  I'm running CentOs


texts: Fast recovery vs Accelerated database recovery in SQL server 2019

texts:
while comparing the features available in different SQL server editions, i see that Fast recovery and Accelerated Database recovery are 2 separate features.
I am unable to find any documentation specific for Fast recovery, and other online blogs seem to use both features interchangeably.
Does anyone know how exactly are they different?
The screenshot is captured from Editions and supported features of SQL Server 2019 (15.x)


texts:
I have a database on Machine A. What I want is to dump my database in such a way that when I import it on another machine (Machine B) only the structure of the tables and Database are created.

I want no data.

Can mysqldump be used here?


texts: Can I change Fill Factor option for our database server?

texts: I have a production database for ERP which has clustered and non clustered indexes. Some other tables have index type as heap and  i don't understand what does heap means. Every month i have a job run this command sp_msforeachtable "dbcc dbreindex('?')"  to rebuild all indexes in our database  after that i checked the fragmentation and i found it to be still 98%. I searched on web to discover that our  Fill factor option for our server is 0, so can i change it to suitable percentage and what factors I need to consider?


texts: Why turning off condition_fanout_filter speed up query

texts:
bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &

Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#52'
         AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
         AND l_quantity >= 4
         AND l_quantity <= 4 + 10
         AND p_size BETWEEN 1 AND 5
         AND l_shipmode IN ( 'AIR', 'AIR REG' )
         AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#11'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             AND l_quantity >= 18
             AND l_quantity <= 18 + 10
             AND p_size BETWEEN 1 AND 10
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#51'
             AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
             AND l_quantity >= 29
             AND l_quantity <= 29 + 10
             AND p_size BETWEEN 1 AND 15
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' );

condition_fanout_filter=off
RESULT:  1 row in set (13.73 sec)

explain:
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |   100.00 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

condition_fanout_filter=on
RESULT:  1 row in set (30.57 sec)

explain:
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |     1.33 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |     0.67 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

From column 'filtered', "condition_fanout_filter=on" should be faster.
Other important things
this only happens in the 15k RPM SAS 600GB HDD and with --innodb-flush-method=O_DIRECT.
Either using an SSD or not set O_DIRECT, this problem would disappear.


texts: Additional space required for INDEX REORGANIZE

texts:
MSDN states:

  ALTER INDEX REORGANIZE; however, log space is required.

The database recovery mode is Simple, does that implies the log space is actually NOT required ?.


texts: SQL Server Logon Trigger Implications - are there any side effects possible in my scenario?

texts:
Some of developers on my team know passwords from SQL accounts that have extended permissions
We would like to track and be alerted whenever any of developers are using any of those SQL accounts to connect
We are about to implement logon trigger that would for each log in attempt, evaluate login's properties, and send email report if they match certain criteria. Logic is following:

if original_login() in (...SQL accounts list here...) and:
a) client IP address is 192.168.x.x  VPN subnet  (no Production apps connecting from this subnet, only developers can) or
b) client host name in (... list of Dev's host machine names...) or
c) client application name in (SSMS,az-Data etc.)
exec sp_send_dbmail (send report over email to DBA)

Trigger would have "execute as" clause and run on behalf of a SQL login whose only permission is to send db mail emails
What can be unwanted side effects of enabling this kind of logon trigger on Production ?
Can it slow login process or cause any other issues ?
p.s. I am aware about DAC and how to use it.
Tested connecting using DAC and counting on it to help me disable the trigger if any trouble begins


texts:
I'm wondering how multicast actually works. So here is my setup. I have one source flat file(csv) of about 100k rows, maybe about 250-300 columns, and I'm multicasting that file into 7 one-to-one tables. Only the primary key column is duplicated among these tables. This setup seemed to work fine, but now it seems we're maybe running into memory issues. I'm wondering if multicasting that data to 7 destinations is too much or not. Any thoughts on memory usage?  I don't really care how fast this runs, just looking to reduce memory usage.

One alternative I was thinking of was maybe just creating 7 different connection managers for the same file, and running everything sequentially. Would that be better? Thanks!


texts: How keep overview about database permissions?

texts:
As DB-Administrator I need to keep overview about my servers and databases in general - and from a security context about logins, users, db-roles and permissions in particular.

It is possible to write queries that deliver all relevant information to reduce effort required to lookup each and every user / permission manually within SSMS for example to find out the settings or to create kind of a documentation about it (that get's outdated the next day).
Doing this I created a table having the following information torn out of my servers:

Servername | Datebasename | Username | Login | DatabaseRolename | Permission | PermissionState | SchemaName | ObjectName

This includes information about User-Permissions on object-level directly as well as through DB-Roles and also recursive DB-Role-in-DB-Role permissions inherited.

Instead of looking at this data or create a small application that allows more comfortable lookups / searches or filtering I would like to have it presented more graphical.
I looked at some sampe js libraries that allow visualization. Something that may
be appropriate is this samples:

http://vowl.visualdataweb.org/webvowl/

http://orgo.stolarsky.com/

My question is if anyone knows a way for visualization of this data? Maybe there is a solution for this everyone is using I did not yet stumble about.


texts: MySQL master slave replication-troubleshooting

texts:
Why does the Procedure/function created on the master replicates to slave even after enabling table filters using "replicate-do-table". This create a lot of problems in the production setup as it rewrites the function already in the slave with the one modified in master.
I need to have two different functions in master and slave, only the table data needs to be replicated. Solution please

How to repeat:
Create a table in master

Replicate the table to slave with replicate-do-table option

create a function on master

it rewrites the function which was already there in slave with the same name


texts: Select All Records From Table That Do Not Exist In Secondary Table

texts:
This should be straight forward, but I am getting my tail kicked with the proper syntax.

I have this syntax

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM
    ___SampleRequired bm
LEFT JOIN
    ___Sample td
    ON LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name))
WHERE
    bm.Type LIKE '%Mandate%'
    AND bm.Location = 'StapleChase'

which I want to show all beer that exist in the SampleRequired Table that DO NOT exist in the Sample Table.  When I execute the above query I get 24 results returned.  My expected returned result set is 23 because

BLUE MOON BELGIAN WHITE

exists in both of the tables.

How should I change this query so that I ONLY get the results that exist in SampleRequired that DO NOT exist in Sample?

***Had to use pastebin bc SO complained my post was mostly code...sorry

https://pastebin.com/jH6eX2du


texts: What is the best way to keep track of 1000 RDS hosted databases?

texts: I want to know what is 'best-practices' for managing an extreme number of RDS instances.  I have no problem managing five.  But managing dozens or hundreds, that's another task.  I'm thinking use a python or php script along with the AWS CLI to report back RDS statuses like maintenance due, or instance failure.  I know this is a broadly based question, but any feedback or direction on this subject would be appreciated.


texts: Sorting the Table and getting the position

texts:
I am able to sort a table using the following code:SELECT * FROM Persons
ORDER BY LastName

But I wanted to get the ranking/position of a person using that order.

For example when I sort Person table by age, I want to get the rank/position of a person named Antinio Trias.

How will I do it?


texts: How does the transaction log record a transaction?

texts:
We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS!
Ah, but there could be several ways for SQL Server to record the transactions. So the question is, how does it record the transactions?
Here are the following ways I envisage it is working:

It merely makes a record of which statement was run. In this case, the transaction log contains the actual text of the DML statement, something like this:
"INSERT INTO test_database.dbo.Test_People (FirstName,LastName) VALUES ('James' ,'Smith')"

In this case the transaction log contains the whole of what is quoted above.
Then, in a transaction log restore (after a full backup restore), SQL Server will actually execute the statement contained in the transaction log, against the database, to update it.

It stores a record of the entire row of data, with all its values (even those values that were not updated), and the date at which the row was modified/inserted.

Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting it or overwriting the same row in the table with the data in the transaction log.

It stores a record of the entire data page, with all its values (even those values that were not updated), and the date at which the data page was modified/updated/deleted.

Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting the page or overwriting the page with the same pageID.
Or, perhaps it is none of the above. Does anybody know?


texts: Normalization and multivalued attributes

texts:
I am designing a relational database in mySQL - a library management system. I want to show examples of the data in 1NF, 2NF, 3NF, BCNF, etc. My example of 1NF looks something like this

'A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.'

I am unsure of if the data is in 1NF. As a book can be written by multiple authors, the attributes author_first_name, author_middle_name, and author_last_name are multivalued attributes. How should I take this into consideration in my example of 1NF?


texts: Linked Server Using a DNS Alias Returning Bad Data

texts:
Somehow on the same DEV server we have two Linked Servers to the same PROD server. While that, in and of itself, is not an issue; we have noted something we can't explain and we wonder of anyone else has experienced something similar.
One of the Linked Servers is named using the actual machine name (ProductionSQLServerData) and the second is named using the DNS alias (ProdData) for that machine.
In a command window from any machine both names ping to the same IP address. We have checked the source and the target machines and there are no entries in the host files that would misdirect the queries.
We are stumped and wonder ir anyone else has experienced this type of behavior and was able to track down the reason for it. Thanks in advance for any response.


texts:
For the life of me, I can't figure out why Postgres won't let me issue this update statement below. Assume any table called dim_dates where there's a column dateType:

ALTER TABLE dim_dates DROP COLUMN "dateType";
ALTER TABLE dim_dates ADD COLUMN "dateType" character(1);
update dim_dates set dateType='D' where year>2000;

It barfs on line 3, saying:

ERROR: column "datetype" of relation "dim_dates" does not exist
SQL state: 42703
Character: 126

Yet I just (re)added the column! I initially had already added the column through the designer, and was attempting to fill in the data for the new table and was only running what is now line 3, but it just doesn't want to let me update the value. I don't want 'D' as a default - but the existing data should have 'D' and I'm going to add further data.

This is a new database, 9.3, no triggers or other evil things to mess up developers (I'm the guy who installed it, and I'm old hat at MS-SQL and MySql, but new to Postgres and trying to get experience with it).


texts: SQL Server 2012 - Create Alias from default instance (i.e. '.') to named instance of SQL 2012

texts:
Scenario is: I have SQL 2012 running as a named instance 'SQL2012'. I can connect to this via SSMS using '.\SQL2012'

Working on a new project, I now want this named instance to be accessible as '.'

To complicate matters, I also have SQL 2014 installed as the default instance, don't need this simultaneously so we can assume this instance is stopped.

I have tried various combinations of alias, the most obvious being:

I've also tried setting the port number to the Dynamic Port found in the network configuration, but nothing I've tried so far seems to work.

Is this supported, and if so, what am I missing?


texts:
I have some users who are logging in with a wrong password and their accounts get locked.

Now I need to find the source of the failed login attempt. The machine, username etc.

Is it possible to find this?

I did try

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;

and also,

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail
where returncode > 0

Both the above queries show up results but I'm not sure if the results are the ones which actually gets locked.

Server - RHEL
DB - Oracle 12c


texts: Text Storage and Database Design Optimization in a SQL Server Database

texts:
Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to structure this database? Also considering the number of rows (r) the database may grow to in size.

Example: given a table with three columns, one being an auto incrementing integer ID column, another being a Fruit column where acceptable fruits are 'Apple','Banana','Orange', and 'Strawberry', along with the final column being Color with 'Red', 'Orange', 'Green', and 'Yellow' as acceptable values. Let us assume the Fruit and Color column have a data type of varchar(50). Which of the following would be a good practice and suitable database design?

Should each value be directly represented in the table as follows given the previous example. Would it be fair to say that after a large number of rows querying such a table would be very inefficient due to the large storage and computation requirements required on the text?

+-----+--------+--------+
| ID  | Fruit  | Color  |
+-----+--------+--------+
| 1   | Banana | Yellow |
| 2   | Apple  | Green  |
| ... | ...    | ...    |
| 500 | Orange | Orange |
+-----+--------+--------+

OR
For each column that has one of multiple values applicable to it, should that be represented in another table with integer values, and then joined together with an INNER JOIN? Followed by an integer representation in the main table? Does this depend on the length of the text values being stored in that field? For the given example, we would then have three tables.

+----+------------+
| ID | FruitName  |
+----+------------+
|  1 | Apple      |
|  2 | Banana     |
|  3 | Orange     |
|  4 | Strawberry |
+----+------------+
+----+------------+
| ID | FruitColor |
+----+------------+
|  1 | Red        |
|  2 | Green      |
|  3 | Orange     |
|  4 | Yellow     |
+----+------------+

Then followed by:

+-----+-------+-------+
| ID  | Fruit | Color |
+-----+-------+-------+
| 1   | 2     | 4     |
| 2   | 1     | 2     |
| ... | ...   | ...   |
| 500 | 3     | 3     |
+-----+-------+-------+

Is there any mathematical 'rule-of-thumb' when designing a database considering the number of rows and the type of data to be stored in each column? Or should the second schema be followed in general.


texts: We currently have active/active windows 2008 cluster hosting 2 instances of SQL 2008 R2. We have a new hardware for windows 2012 clustering. We want to use this new hardware to upgrade the active/active win 2008 cluster to win 2012 cluster. Is there an upgrade path without changing the 2 SQL instance names? We can't upgrade the SQL instance to SQL 2012 though. Is it better to stand up the new cluster 2012 by shutting down the old cluster, removing from AD and use the old cluster virtual name or is it better to stand up a brand new cluster 2012 and install SQL 2008 R2 and migrate the databases. Which one is the best practice or best way of doing it? Appreciate your advise on this. Thanks.


texts: Date of last change of an InnoDB table structure

texts:
I was wondering how to determine when was the last change (ALTER TABLE...) done to a given table.

An old answer is available but unfortunately that doesn't work for InnoDB tables.

Would this work, looking at the frm file date time?

-rw-r-----   1 mysql    mysql       18622 Oct 24 21:07 mysql/data/mydb/mytable.frm
-rw-r-----   1 mysql    mysql     2114688 Oct 28 06:26 mysql/data/mydb/mytable.ibd

Oct 24 21:07 seems to roughly correspond to the last time a change in the structure was performed.
Is that reliable information?


texts: How to pivot without fixed columns in TSQL?

texts:
I'm struggling with pivoting a quite simple table. All examples and tutorials on the web, are not what I'm looking for, so maybe you guys can help me out here (I must say that my T-SQL knowledge isn't so good...)

Let me explain the situation:

I have a table Trucks

CREATE TABLE [dbo].[Trucks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL)

SET IDENTITY_INSERT [dbo].[Trucks] ON
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (1, N'AAA-BBB')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (2, N'AAA-CCC')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (3, N'BBB-WWW')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (4, N'SKL-POL')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (5, N'QAS-ZSD')
SET IDENTITY_INSERT [dbo].[Trucks] OFF

Each truck has an amount of compartments. Each compartment has a name and a capacity

CREATE TABLE [dbo].[Compartments](
    [Id] [int]  IDENTITY(1,1) NOT NULL,
    [TruckId] [int] NOT NULL,
    [Compartment] [nvarchar](50) NOT NULL,
    [Capacity] [bigint] NULL)
SET IDENTITY_INSERT [dbo].[Compartments] ON
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (1, 1, N'C1', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (2, 1, N'C2', 4000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (3, 1, N'C3', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (4, 1, N'C4', 4000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (5, 1, N'C5', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (6, 2, N'Vak 1', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (8, 2, N'Vak 2', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (9, 2, N'Vak 3', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (10, 2, N'Vak 4', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (11, 3, N'1', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (12, 3, N'2', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (13, 3, N'3', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (14, 3, N'4', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (15, 3, N'5', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (16, 3, N'6', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (17, 3, N'7', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (18, 3, N'8', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (19, 3, N'9', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (20, 3, N'10', 500)
SET IDENTITY_INSERT [dbo].[Compartments] OFF

I want to write a SP that returns a list of compartments for a certain Truck (based on the TruckId).
This is the easy part:

SELECT * FROM COMPARTMENTS WHERE TruckId = @p_TruckId

This gives me a table that looks like this:

What I want to do, is return a table that has the compartment name as header and the capacity as value.

This will be a table with only 1 row of values. The number of columns depends of the number of compartments for that given truck.

I looked into the PIVOT function, but you need to know the amount of columns in advance for that. I also found a dynamic PIVOT example, but I can't seem to make it work in my situation.

Can somebody assist me here? That would be great!

EDIT: A link to the example I've found


texts:
I have 3 tables: users, photos, avatars (photo of user_1 that currently see user_2)
when user deleted, all his photos and avatars, that shown to other users also removes
What I do wrong?

cursor.execute("""CREATE TABLE IF NOT EXISTS users(
id int AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER NOT NULL UNIQUE,
goal VARCHAR(255) DEFAULT NULL,
gender VARCHAR(255) DEFAULT NULL,
dob DATE DEFAULT NULL,
country VARCHAR(255) DEFAULT NULL,
city VARCHAR(255) DEFAULT NULL)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS photos(
id INTEGER AUTO_INCREMENT,
user_id INTEGER NOT NULL,
photo VARCHAR(255) NOT NULL,
KEY (id),
PRIMARY KEY (user_id, photo),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS avatars (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER,
avatar VARCHAR(255),
shower_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
FOREIGN KEY (avatar) REFERENCES photos (photo) ON DELETE CASCADE,
FOREIGN KEY (shower_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

But I get an error

1822, "Failed to add the foreign key constraint. Missing index for constraint 'avatars_ibfk_2' in the referenced table 'photos'")

mysqlsh.exe --version
C:\Program Files\MySQL\MySQL Shell 8.0\bin\mysqlsh.exe   Ver 8.0.19 for Win64 on x86_64 - for MySQL 8.0.19 (MySQL Community Server (GPL))


texts:
After reading Which one is more efficient: select from linked server or insert into linked server? I wonder if this is true also for SSIS?

For example, I have 2 SQL Servers (A and B) and SSIS solution which extract data from A and load it to B (A->B). What's the difference in performance, between deploying SSIS package on server A or B?


texts: what is "killcursors: found 0 of 1" from mongodb.log with replicaset?

texts:
This is output of mloginfo log --distinct. What does killcursors mean exactly and what's the DBA action? We use latest mongod in a 3 node replicaset.

With a search engine i found only on mongodb-user mailing list

  Killcursors is a database command that removes cursor references on
  the server. It's normal and harmless. You'll probably only get an
  exception when inserting in safe mode.

I wish more info.

    source: mongodb.log
       host: example
      start: 2016 Jul 15 07:35:28.419
        end: 2016 Aug 19 08:00:15.874
date format: iso8601-local
     length: 244025
     binary: mongod
    version: 3.2.8
    storage: wiredTiger

DISTINCT

   64062    connection accepted from ... # ... ( ... now open)
   63984    end connection ... ( ... now open)
       4    killcursors: found ... of
       3    assertion ... ns: ... query:
       1    git version:
       1    waiting for connections on port

Logs

2016-08-05T15:29:36.935+0200 I COMMAND  [conn39339] killcursors: found 0 of 1
2016-08-05T15:29:36.937+0200 I COMMAND  [conn39327] killcursors: found 0 of 1
2016-08-05T15:29:36.945+0200 I COMMAND  [conn39329] killcursors: found 0 of 1
2016-08-05T15:29:36.946+0200 I COMMAND  [conn39343] killcursors: found 0 of 1


texts: Use the following business rules to create a Crow’s Foot ERD

texts: Is there any mistake in the ERD???? it looks weird.


texts: How to investigate non-specific Generate and Publish Scripts Wizard errors

texts:
I am trying to use the Generate and Publish Scripts wizard in SSMS 2012 against a 2012 SQL instance and am getting errors. Trying to generate a schema-only script of the tables and schemas. I allowed it to continue when there were errors. 50% of the tables fail, specifically in the Preparing table xxx and the result is error. The final result is failure.

This is a db on a test instance, the db on the prod instance (same-ish) works great. I only have db_datareader and db_datawriter on the instances. I cannot view the error log as a result, I cannot view the event log either. I am running ssms on a third server. Where can I get more info on the errors, if they are even in either log, and would they be on the instance servers or on the sssms server?


texts: SQL Server - query optimization for highly skewed data distributions

texts:
I am trying to optimize a query which looks similar to this:

select top(1)
    t1.Table1ID,
    t1.Column1,
    t1....
    ....
    t2.Table2ID,
    t2....
    ....
    c.FirstName,
    c.LastName,
    c....
from BigTable1 t1
join BigTable2 t2
    on t1.Table1ID = t2.Table1ID
join Customer c
    on t2.CustomerID = c.CustomerID
join Table4 t4
    on t4.Table4ID = t2.Table4ID
join Table5 t5
    on t5.Table5ID = t1.Table5ID
join Table6 t6
    on t6.Table6ID = t5.Table6ID
where
    t4.Column1 = @p1
    and t1.Column1 = @p2
    and t3.FirstName = @FirstName
    and t3.LastName = @LastName
    and t6.Column1 = @p5
    and (@p6 is null or t2.Column6 = @p6)
order by t2.Table2ID desc
option(recompile);

BigTable1, BigTable2, Customer - are big transactional table (hundreds millions of rows), Table4,Table5,Table6 are relativly static and small lookup tables.
The problem is that data in those big tables has pretty skewed distribution and because of that very often this query performs very poorly (estimated number of rows in execution plan are very different from the actual). Updating statistics on those big tables does not help (200 steps in histogram are not enough to cover all the skews in data distribution). For example, in Customer table there are a few (FirstName, LastName) combinations that correspond to about 500k records.

I see 2 options to improve performance of such queries:

Split this query into smaller ones and saving intermidiate results
into temporary tables. With this approach of materializing
intermidiate results into temporary tables we can give the optimizer
a better opportunity  for cardinality estimations, but we add a
substantial additiobal load on tempdb (cause this query is executed
rather frequently, up to several times a second). And another
drawback is that it is not faster for all parameter values, it
appears to be significantly better for atypical parameter values
(when the original query can take minutes, this takes just few
seconds), but for parameters that correspond to a more o less unique
(or infrequent) rows the approach with temp tables is slower.
Create filtered statistics for the spike values, for example for
Customer table it would look like this:

declare @sql nvarchar(max) = N'', @i int, @N int;
select top (1000)
identity(int,1,1) as id,
FirstName,
LastName,
count(*) as cnt
into #FL
from Customer
where
FirstName is not null
and LastName is not null
group by
FirstName,
LastName
order by cnt desc;

set @N = @@ROWCOUNT;
set @i = 1;

while @i <= @N
begin
    select @sql = 'CREATE STATISTICS Customer_FN_LN_' + cast(id as varchar(10)) + ' ON dbo.Customer(CustomerID) WHERE FirstName = ''' + FirstName + ''' AND LastName = ''' + LastName + ''' WITH FULLSCAN, NORECOMPUTE'
from #FL
where id = @i;
exec sp_executesql @sql;
set @i = @i + 1;
end;

So if we create this filtered stats for those 3 big tables and re-create those stats, lets say, every week, we should be OK with row estimations in the original query.

From my previous experience I usually used approach with temp tables, but in this case it looks like the approach with filtered statistics is more attractive. I haven't use it in production yet though and I am curious what could be the drawbacks.

So my questions are:

Are there any other approaches to help optimizer deal with highly skewed data distributions?
What are the drawbacks of manually created and handled filtered statistics in the 2nd approach?


texts: MSSQL efficient paging including a join on big tables

texts:
We have a table with products and a table with a current selection/assortment of products. Now need to join them create an export file by some .net code.

Currently using query like this:

SELECT * FROM ASSORTMENT a
INNER JOIN PRODUCTS p on a.clientID = 38 and a.productID = p.productID
ORDER BY a.ID_ROW OFFSET 100000 ROWS FETCH NEXT 1000 ROWS ONLY

Problem is that the assortment can contain discontinued products, that are not existing in products table anymore, so need a join to verify. Also need each time exactly same amount of rows per select, e.g. 1000.

The query gets slower if the offset gets higher because it does a full join of products and assortment table each time. On 3 Million products and an assortment of 200.000 articles it takes multiple seconds to fetch next 1000 if the offset is over 50.0000

Is there a more efficient way of doing this?


texts:
I raised a request to use Tableau Server, which internally fetches data from SQL db.
Now a person assigned to resolve the ticket is asking to provide Elevated Authors Information. What does this mean?
Is this highest level of authorisation? Please let me know any additional information is required. thanks


texts: How to convert postgresql dump file into mysql dump file

texts: I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance


texts: Restoring data into a replicated table after failed purge process

texts:
We have a monthly purge process that purges data from a few tables using a somewhat complex SSIS package.

Up until this time everything is running smooth and fine without any issues.

However, if the purge process does fail then my only resort would be to restore those tables back to the state they were in before the purge stated.  We take backups of the affected tables before we start purging data, however my concern is these tables are in replication and in case if I have to restore the data back do I need to just use import/export or insert into and get done with it or do I need to remove the table out of replication, add the data and then add the table back and generate a snapshot for these articles only?

What if I want to restore the complete table. How am I suppose to do it when the tables are part of transactional replication?


texts: Restore of db's on local locks me out of accessing other sites

texts:
Running my local dev websites on my Mac locally and when I am restoring a database via terminal, which the restore is running I cannot access anything that uses mysql, I get a broken pipe notice.

Is there anyway to allow a restore without it giving me this issue when trying to work on a second, seperate site that uses a direct db but same server?

The error I get when trying to access any development site that uses mysql during a restore

Notice: PDO::__construct(): send of 130 bytes failed with errno=32 Broken pipe in /Library/WebServer/Documents/Sites/site_name/includes/database/database.inc on line 307

Not normally an issue but currently restoring a particular database that is so large it would take 40 minutes to restore.

Server version: 5.6.22 MySQL Community Server (GPL)

Command used

$ mysql -u root -p itrs < clientx_web_live_200315.sql

My.cnf

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

wait_timeout=3600


texts: Advantages of POINT over DOUBLE for distance queries?

texts:
I'm about to start an application that will need a simple distance query to order results. After some search I've discovered some functions that seems to do this work pretty easily without needing PostGIS, what seems to be too much for my task.

My question is: what would be better from a performance point of view, using a POINT datatype to store lat/long or two DOUBLE fields, separately? Would they have any difference?

Bonus question: is the approach from that link a nice idea indeed?


texts: How to create a Postgres trigger to ensure field is non-updateable

texts:
I'm trying to create a Postgres trigger to ensure after a column's value has been set, it cannot be updated (basically make it readonly). So far I've come up with the following draft trigger.

1) First, am I on the correct track?
2) Is there value in returning NEW?
3) Must I preface original_id with NEW.?

CREATE FUNCTION check_id_change() RETURNS TRIGGER AS $$
    BEGIN
        IF OLD.original_id <> NEW.original_id THEN
            RAISE EXCEPTION 'cannot change original_id';
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER client_update_trigger AFTER UPDATE ON client FOR EACH ROW
      EXECUTE PROCEDURE check_id_change();


texts: Data Warehouse: Staging Area, DWH databases and ETL tool on one server?

texts:
I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the transformation from stg to dwh running on one single machine because of reduced hardware cost and less administration effort:

sda: 512 GB SDD -> OS (Centos 7), Applications, Swap space, MySQL
Database application (but no data), csv files from data sources, ETL
tool (Pentaho)
sdb: 960 GB SDD -> MySQL tablespace for staging area tables
sdc: 960 GB SDD -> MySQL tablespace for data warehouse tables
sdd: 4 TB HDD -> backup & archive disk
Memory: 160 GB
Prozessors: 2x Intel Xeon E5-2620v3 - 12 cores

Tasks:

During stg loading: LOAD DATA INFILE from csv to stg
During transformation: Pentaho would SELECT from stg and INSERT into dwh in parallel for multiple tables. I thought having stg and dwh on separate disks will avoid read / write bottlenecks
During working hours: Reporting tool or Analysts do SELECT on dwh

From your point of view or experience is there any reason why I should not use this layout and instead split stg and dwh on two different servers like I did in the past? (e.g. MySQL performance might suffer from parallel SELECT and INSERT, etc.)

Thanks for your feedback!


texts: What is a LSN (log sequence number)? How does xtrabackup Incremental Backup work?

texts:
I was surprised that xtrabackup supports incremental backups. xtrabackup copies the data files and understand the format of data files (can have a look inside)? What is a LSN for dummies? I found What is log sequence number ? How it is used in MySQL?.

Quote from Incremental Backup

  An incremental backup copies each page whose LSN is newer than the
  previous incremental or full backup’s LSN. There are two algorithms in
  use to find the set of such pages to be copied. The first one,
  available with all the server types and versions, is to check the page
  LSN directly by reading all the data pages. The second one, available
  with Percona Server, is to enable the changed page tracking feature on
  the server, which will note the pages as they are being changed. This
  information will be then written out in a compact separate so-called
  bitmap file. The xtrabackup binary will use that file to read only the
  data pages it needs for the incremental backup, potentially saving
  many read requests. The latter algorithm is enabled by default if the
  xtrabackup binary finds the bitmap file. It is possible to specify
  xtrabackup --incremental-force-scan to read all the pages even if the
  bitmap data is available.

I still don't understand how this works.
Do you use xtrabackup Incremental Backup? Do you recommend xtrabackup Incremental Backup?


texts: Dealing with data stored as arrays in a MySQL DB

texts:
So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to deal with it.

It basically seems to link 2 tables and add a view count. Here is an example of the data:

a:4:{i:4;i:196;i:26;i:27;i:5;i:155;i:34;i:4;}

So I think this means there are 4 entries in the array, each with 2 attributes. The first - i.e. 4, 26, 5, 34 are "store codes". The second lot (196, 27, 155, 4) are number of plays. God knows why they are stored like this as there is already another table that links the video with the stores and they could've just stuck another column there for view count.

Anyhow, what I want to do is order by view count based on store id within that array. Do you think this is possible and does anyone have any ideas how to do this? If storing data like this is a standard, do you know the name for it as I could probably take it from there?

Thanks!


texts:
I was trying to delete some data from the table dm.billables:

1 - Select the id - PASS

SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306;

2 -  Delete the data from the table below - PASS

DELETE FROM dm.billables_links WHERE billable_id IN (select billable_id FROM junk.clientid_10306);

3 -  Delete data from the table below - FAIL

DELETE FROM dm.billables WHERE billable_id IN (select billable_id FROM junk.clientid_10306);

ERROR:

ERROR:  update or delete on table "billables" violates foreign key constraint "fk_billable_must_exist" on table "billables"
DETAIL:  Key (billable_id)=(14188857) is still referenced from table "billables".

Constraint:

ALTER TABLE dm.billables_links
  ADD CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id)
      REFERENCES dm.billables (billable_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

There is no data on dm.billables_link anymore. Why am I getting the error though?

Table dm.billables:

CREATE TABLE
    billables
    (
        billable_id BIGINT DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass") NOT
        NULL,
        account_id BIGINT NOT NULL,
        code CHARACTER VARYING(64) NOT NULL,
        info "TEXT",
        m_unit USER-DEFINED,
        m_unit_custom CHARACTER VARYING(64),
        unit_cost NUMERIC(16,4),
        tax_aggregate_id_cost BIGINT,
        unit_price NUMERIC(16,4),
        tax_enabled_price BOOLEAN DEFAULT true,
        tax_aggregate_id_price BIGINT,
        ts_created TIMESTAMP(6) WITH TIME ZONE DEFAULT "transaction_timestamp"() NOT NULL,
        ts_modified TIMESTAMP(6) WITH TIME ZONE DEFAULT "transaction_timestamp"() NOT NULL,
        ts_last_used TIMESTAMP(6) WITH TIME ZONE,
        is_demo BOOLEAN DEFAULT false NOT NULL,
        CONSTRAINT pk_billables PRIMARY KEY (billable_id),
        CONSTRAINT fk_price_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_price)
        REFERENCES tax_aggregates (tax_aggregate_id),
        CONSTRAINT fk_cost_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_cost) REFERENCES
        tax_aggregates (tax_aggregate_id),
        CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
        CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY[("m_unit" IS NOT NULL),
        ("m_unit_custom" IS NOT NULL)] <> ARRAY[true, true])
    );

Table dm.billalbes_link:

CREATE TABLE
    billables_links
    (
        billable_link_id BIGINT DEFAULT "nextval"('"dm"."billables_links_billable_link_id_seq"'::
        "regclass") NOT NULL,
        billable_id BIGINT NOT NULL,
        customer_id BIGINT,
        role_id BIGINT,
        mobiuser_id BIGINT,
        CONSTRAINT pk_billables_links PRIMARY KEY (billable_link_id),
        CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id) REFERENCES billables
        (billable_id),
        CONSTRAINT fk_customer_must_exist FOREIGN KEY (customer_id) REFERENCES
        dbname_live.public.ja_customers (id),
        CONSTRAINT fk_role_must_exist FOREIGN KEY (role_id) REFERENCES dbname_live.public.ja_role
        (id),
        CONSTRAINT cc_one_and_only_one_target CHECK ((((("customer_id" IS NOT NULL))::INTEGER + (
        ("role_id" IS NOT NULL))::INTEGER) + (("mobiuser_id" IS NOT NULL))::INTEGER) = 1)
    );


texts: How to show all users of all databases in mongodb?

texts: I know show users or db.getUsers() can show all users in current database in mongodb, but how to list all users in all databses for ease of management as an administrator?


texts: What is a connection in MySQL Workbench?

texts:
I've searched so much and still found no answer to what exactly a connection is. I'm a beginner and don't know how connecting to databases/servers works, so I can't move forward yet.

What does the connection connect to and from?

What do I put in the "hostname" and "port" if I'm making a schema for my website at "localhost/magento" in my browser?

Thanks


texts: Permissions for SSIS/SQL Server agent jobs for multiple user groups

texts: What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only? For example, I have 3 folders in the Integration Services catalog. Dept_A, Dept_B, and Dept_C. Each AD group should be able to DEPLOY to their own department's folder, and also create/schedule/run jobs that call these packages. I can control the permissions for deployment to those 3 folders by folder/project permissions. However, when it comes to the SQL Server Agent, things don't seem fine-grained enough. There are database roles in msdb: SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole. However, I'm not seeing how I can limit security to keep each AD group only able to run their own packages from a job.