texts:
I have below jsonb in table.
'{
   "Owner":[
      {
         "Edgeid":4,
         "Weight":40,
         "EdgeColor":"Black"
      },
      {
         "Edgeid":1,
         "Weight":10,
         "EdgeColor":"Black"
      },
      {
         "Edgeid":2,
         "Weight":20,
         "EdgeColor":"Black"
      }
   ],
   "Supporter":[
      {
         "Edgeid":3,
         "Weight":30,
         "EdgeColor":"Red"
      }
   ]
}'

i am using below code to unnest "Owner" & "Supporter" into different columns
SELECT  column_name::JSONB -> 'Owner' AS owner_property,
        column_name::JSONB -> 'Supporter' AS supporter_property
        FROM table_name;

now suppose if i need to form jsonb like above from two column data, what would be my code.
Thanks in Advance.


texts: How to restore a database to the latest file in a backup set

texts:
USE ICT;
GO
BACKUP DATABASE ICT
TO DISK = 'E:\Backup\ICT.bak' -- Verwijder dit bestand a.u.b.
GO

RESTORE DATABASE ICT_TEST FROM disk = 'E:\Backup\ICT.bak'
WITH REPLACE,
   MOVE 'ICT' TO 'E:\DATA\ICT_TEST.mdf',
   MOVE 'ICT_log' TO 'E:\DATA\ICT_TEST_log.ldf'
GO

This script makes a backup from my PROD DB en restores it to my TEST DB. When i run this script twice, i get the first backup in the file. How can i make sure that the latest backup will be restored over the TEST DB? I understand that you can tell SQL:

RESTORE DATABASE ICT_TEST FROM disk = 'E:\Backup\ICT.bak'
WITH REPLACE, FILE = 2

But i don't want to do this every time.


texts: Transaction Log Backup Affecting Applications Performance

texts:
We have a database around terabytes of data. We are taking FULL backup every week, differential backup daily and transaction log backup after every hour. Sometimes this transaction log backup takes some time to complete (around 10-15 minutes). In this period we find that application(s) performance affects a lot(normally very slow) that use the same database.

Does transaction log backup affect the performance of the application?
If yes, then how to make speedup the process of taking transaction log backup?


texts: Problems during migration from SQL Server to MySQL via MySQL Workbench

texts:
We've found most of the options we needed to include the necessary CHARSET. Here is what we've included in the MySQL my.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

But we still get an error which points to a wrong charset and/or collation:

10:17:38 [INF][      copytable]: Statement execution failed: Incorrect string value: '\xF3\xB3\xB4\xAF)<...' for column 'LangText' at row 57

Complete Log file: https://ufile.io/52s13

Can anybody point to a solution for his issue? Thanks!

We need to migrate a SQL Server database to MySQL for the first time (for a customer project). To do so, we've quickly set up a local testing machine to try one of the many tutorials to migrate via MySQL Workbench.

Installed/ prerequisites:

Local Windows 10 (64-bit)
MySQL Workbench 6.3 (Community Server)
MySQL Server 8.0
Microsoft SQL Server Management Studio (SSMS) 17.0
Microsoft SQL Server 2017 (Developer Version)
Driver: SQL Server Native Client 11.0

Unfortunately, we run into some problems during migration and re-importing the DB on another machine. To quickly move forward, we edited the resulting SQL dump with the following queries:

Regarding Export:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mssql_to_mysql_20180730.local.sql
sed -i 's/ENGINE=InnoDB DEFAULT/ENGINE=InnoDB ROW_FORMAT=DYNAMIC/g' mssql_to_mysql_20180730.local.sql
sed -i 's/DEFAULT CHARSET=utf8mb4/ROW_FORMAT=DYNAMIC CHARSET=utf8mb4/g' mssql_to_mysql_20180730.local.sql

Performance:

sed -i '1s/^/SET foreign_key_checks=0;\nSET unique_checks=0;\nSET autocommit=0;\n/' mssql_to_mysql_20180730.local.sql
sed -i -e "\$aCOMMIT;\nSET unique_checks=1;\nSET foreign_key_checks=1;\n" mssql_to_mysql_20180730.local.sql

Additionally, we've adjusted the target db:

SET GLOBAL innodb_default_row_format=DYNAMIC;

How can we set these options during the migration in workbench? Is there an overview on which options can be altered during the migration configuration?


texts: postgreSQL 9.3 streaming replication delay

texts:
I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of replication delay:

# SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS slave_lag;
slave_lag
-----------
5.50896

The servers aren't particularly busy, so I was wondering what I could do to investigate this problem or possibly fix it.


texts:
I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables.

I wonder if it's efficient to store a lot of rows with NULLS on those rows that didn't change. For example this is a simplistic representation of my products table;

The products_history table has all it's field (except the FK and non-logical rows) set to NULL, whereas the normal products table has NOT NULL on all, except description.

Now what I had in mind is to push a duplicate of the actual product row and push the change into the products_history table. So let's say I have this row in products:

{600, CURRENT_TIMESTAMP, 2, 'BS001', NULL, 49.95}

and would change the price to 48.50. So I would push which values has changed, in this case the base_price and add a change-row to the history table;

{600, THE_date_created_FROM_PRODUCT, NULL, NULL, 49.95}
{600, CURRENT_TIMESTAMP, NULL, NULL, 48.50}

So here on the first modification of a Product it will get 2 rows, and each modification afterwards only one new with the modified fields). After it will update the current product row with the new base_price.

This approach works for me and is quite effective (as the history table will only be filled with product data if a product get's actual edited for the first time), but I am wondering if it this is efficient by storing all those NULL-values. Would it affect my performance after a while or would the impact not be that great?

Otherwise; I'm curious what would be good approaches to do this in MySQL, or even Django ORM-specific ways.


texts: Connect to a local PostgreSQL server using peer auth with pgAdmin

texts:
Context

I have a PostgreSQL server running on a remote machine, but I'm connected locally through VPN.

Its pg_hba.conf file is accepting peer auth for local connections;

local   all             postgres                                peer

I don't like to change this.

If I follow what is written here: https://documentation.help/pgAdmin3/connect.html ;

  The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.

I got this error on pgAdmin3:

I even tried not to leave the Host field blank but to write the name of the socket file in there: /var/run/postgresql -> the behaviour is the same.
And if I insert postgresql:// instead, I get;

An error has occurred:

07:55:28: Error: Error connecting to the server: could not translate host name
"postgresql://" to address: Name or service not known

And pgAdmin4 doesn't let me have the Host field blank:

Until there, I was logged in as the normal Ubuntu user.

So I decided to try while being postgres Ubuntu user, just like when I want to run a pg_* command using peer auth (e.g. $ sudo -u postgres pg_* -U postgres bla bla):

pgAdmin3:

$ sudo -u postgres pgAdmin3
Client is not authorized to connect to Server07:44:11: Erorr:
Unable to initialize GTK+, is DISPLAY set properly?

pgAdmin4:

$ sudo -u postgres pgAdmin4
QStandardPaths: XDG_RUNTINE_DIR not set, defaulting to '/tmp/runtime-postgres'
Client is not authorized to connect to Serverqt.qpa.screen: QXcbConnection:
Could not connect to display :1.0
Could not connect to any X display.

Next I decided to check this out on my local machine in order to by-pass these display related errors

pgAdmin3:

$ sudo -u postgres pgadmin3
No protocol specified
07:49:51: Error: Unable to initialize GTK+, is DISPLAY set properly?

I do not have pgAdmin4 on my local machine, but I guess I will encounter some same kind of error.

Question

How can I log to the local PostgreSQL server which is based on a peer authentication using pgAdmin3 or pgAdmin4?

Update:

These three new lines were added to the log file in /var/log/postgres/postgresql-12-main.log after the unsuccessful connection using pgAdmin3:

2020-04-16 16:34:55.548 CEST [17842] postgres@postgres LOG:  provided user name (postgres) and authenticated user name (ubuntumainusername) do not match
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres FATAL:  Peer authentication failed for user "postgres"
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 85: "local   all             postgres                                peer"


texts: way to prevent queries from waiting for table level lock

texts:
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in MyISAM. (I've heard of using InnoDB instead of MyISAM, but we cannot change the engine in the near future).
We could spot it to an update-query which is performed when a moderator activates a comment on the articlesite. This is the process:

update-query is processed  SET status = 1 WHERE id = 5 (index is set)
the cached files of the page are deleted

At this point the whole page becomes slow. The database itself is busy for minutes. I fetched the processlist a few times and saw about 60 entries of different select-queries, which were all on the state waiting for table level lock.
1. I don't unterstand why this update on the table article_comments can affect select-statements for table article to wait for table level lock. In processlist almost all waiting queries were from this table. I've read about the fact that updates/inserts are preferred to selects and that this can cause such problems, but the articles-table itself isn't updated when comments become activated, so the selects shouldn't wait. Did I missunterstand that?

2. Is there something besides changing to InnoDB to prevent this behaviour or at least to get a better balance? I'm very irritated about the fact that this problem did not appear before moving the database to the new server. I guess there is some misconfiguration but I don't know how to identify.


texts: Is splitting a CSV column into a separate table (enforcing 1NF) unnecessary complication?

texts:
I had very carefully asked the table designer why he had chosen to create CSV columns in multiple tables.

The designer's answer was that splitting CSV columns into separate tables was "unnecessarily complicated".

One CSV column is used in an implicit join to count how many times a value in the multitude of values in the CSV column has been used.

The where cause that binds the two table uses a like % | table_b.csv_column | % operation.

I read and learnt that normalisation is not always required if there is a good reason not to.
However, I don't know what could be good reasons for not normalising.

Is this (avoiding "complication") a good reason for not enforcing first normal form (1NF)?

TABLE: TB_DISASTER_CAUSE_CATE

CATEGORY_ID   CATEGORY_N CATEGORY_L L
------------- ---------- ---------- -
DC006001002   ****                3 Y
DC006001003   ****                3 Y
DC006002001   ****                3 Y
DC007001001   ****                3 Y
DC007002001   ****                3 Y
DC007002002   ****                3 Y
DC007003001   ****                3 Y
DC007003002   ****                3 Y
DC007003003   ****                3 Y

TABLE: TB_DISASTER_HISTORY

SEQ DISASTER_TYPE DISASTER_CAUSE
----- ------------- ------------------------------------------------------------
   32 DT001003002   DC001001004|DC002001002|DC003001001|DC007002001
   33 DT002003007   DC001001004|DC007002001
   34 DT002003009   DC001001003|DC003001002|DC007002001
   16 DT002003001   DC001001004|DC002001002|DC005001003|DC007002001
   17 DT002003001   DC001001004|DC002001002|DC005001003|DC006001003|DC007002001
   18 DT002003007   DC001001003|DC001002002|DC002001001|DC002002002|DC004001001|
                    DC007002001

   19 DT002003007   DC001001003|DC001001004|DC003001002|DC007002001
   20 DT002003007   DC001001003|DC001001004|DC007002001
   21 DT002003007   DC001001003|DC003004001|DC007002001

  SEQ DISASTER_TYPE DISASTER_CAUSE
----- ------------- ------------------------------------------------------------
   22 DT002003007   DC001001003|DC001001004|DC002001002|DC007002001

QUERY

select
          x.category_id as category_id
          ,x.cnt as ccnt
          ,y.category_nm as category_nm
        from(

        select category_id , count(*) cnt from
            TB_DISASTER_CAUSE_CATE  a ,
            tb_disaster_history b

           where last_yn  = 'Y'
             and b.disaster_cause like '%'||a.category_id||'%'
             and b.disaster_type=#disaster_type#
         group by category_id  ) x

         inner join (
         select * from tb_disaster_cause_cate
         where last_yn='Y') y
         on x.category_id = y.category_id;


texts: The database principal owns a schema in the database, and cannot be dropped message

texts: I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck schemae is blue and unremovable.  How can I remove the principal from these schemas?


texts: SQL table with same name as catalog view

texts:
When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be used as a table name.

Are there any conflicts or gotchas that I should be concerned with using Events as a table name in MS SQL Server?


texts: Design pattern for storing a database of databases

texts:
I have been tasked with designing a cloud platform that allows a user to manage the menus of a chain of restaurants from one place. The menus are basically massive TVs at the ordering counter. These currently run MySQL and are updated manually.

What is the design pattern to store all of these store's menus in one database? For example, the name of the product, price and category(drink, burger etc.) is not going to be unique at each store.

The best solution I've come up with so far, is to add in a restaurant table and use the restaurantID as the foreign key in the products table and only sync the relevant products. However, this makes things difficult on the restaurant side because the product IDs will have big gaps in them.

Is there another way to do this? Or is this the general design idea for this sort of application?


texts:
R = {A, B, C, D, E, F}

Dependencies:

{A} -> {C,F}
{C} -> {D}
{B} -> {E}

a. this relation is in 1st normal form (NF). Explain why it is not in 2nd NF and not in 3rd NF.

b. make the required adjustments to convert relation to 2nd NF.

c. make the required adjustments to convert relation to 3rd NF.


texts: How do I install pgcrypto in PostgreSQL 8.4?

texts:
I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it.

There is no pgcrypto if I try to install it using apt-get install pgcrypto and I don't find any files starting with pgcrypto in my system (I tried find / -name "pgcrypto*").

How do I install pgcrypto so I can use the digest('word-to-hash','sha1') function in my database queries?

Update: I'm struggling to install pgcrypto on another Ubuntu machine. After installing the package using sudo apt-get install postgresql-contrib-8.4 how do I install it to my current PostgreSQL database?


texts: Postgresql - Recovery using recovery.conf

texts:
I am trying to recover a database using recovery.conf file for making  point-in-time copy.

On recovery.conf file, I added a recovery target time as below.

recovery_target_time = '2011-06-16 04:10:00 IST'

But in the  logs I can see if it advances the time by 3.5 hours to 07:40 and actual recovery is attempted for this time, not the time I given in recovery_target_time.

2011-06-16 12:53:45 IST LOG:  starting archive recovery
2011-06-16 12:53:45 IST LOG:  restore_command = '/app/postgresinstall/bin/pg_standby -l /dbdata/archive %f %p %r'
2011-06-16 12:53:45 IST LOG:  recovery_target_time = '2011-06-16 07:40:00+05:30'
cp: cannot stat `/dbdata/archive/00000001.history': No such file or directory

Why this difference of 3.5 hours and I need help in figuring out whats going on in deciding the recovery target time?

Update: Here are time details from database

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part
-----------+-----------
         5 |        30
(1 row)

postgres=# select now();
               now
----------------------------------
 2011-08-12 13:08:19.333068+05:30
(1 row)

OS Date/Time:
postgres@xxxxx:~$ date
Fri Aug 12 13:10:19 IST 2011

I faced the same problem when I try to restore from WAL archives on same server and my backup server. Also I can consistently reproduce this problem on both servers.

So if I need to create a near to current time backup, I use a recovery_target_time 3.5 hours behind current time and start the DB with recovery.conf


texts: Merging two tables by minimal interval values

texts:
I have two tables with two columns keeping tabs of categorical values, such as:

Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+

These Begin and End columns represent a continuous interval in which the value is valid. In the above example, the interval would be days in a month, so according to Table 1 I'd have a normal condition from day 1 to day 8, critical from day 8 to day 23, and normal again from the 23rd to the 30th. And I know from Table 2 that John was supervising from the 1st to the 14th, and Janice took over from the 14th to the 30th.

What I want is to merge these two tables, to have both Condition and Supervisor values in the same table, with the minimal interval for each pairing of values. So, this:

Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+

What can be guaranteed of the tables is that:

each will have its respective "Begin" and "End" fields.
those will have the same span (the "Begin" value of the first row and the "End" value of the last row are the same for every table).
that every interval is sequential (the "End" value of row n is always equal to the "Begin" value of row n+1).
that the value of "End" will always be higher than "Begin" for a given row.

I had this worked out programatically in python, but I'm trying to scrap that script from my workflow and do everything directly in the DB. Ultimately I could replicate my python function in plpgsql, but I wonder if there is a more SQL-esque way of achieving this?


texts: I run sp_who on the database, and there is a "TASK MANAGER" process. What is it?

texts:
I have a sporadic problem, when upgrading SQL Server in a single-user mode, some other application somehow logs into the database and kicks my process out.

Last time when that happened, I ran sp_who, and I could see that a process that took control of the database is "TASK MANAGER". EDIT By "took control" I mean that my process crashed with exception Database '...' is already open and can only have one user at a time. while it was running database upgrade code. SqlConnection was NOT closed or disposed.

Anyone could tell me what is this process, what is it's purpose, and how in the world it could get into a database, which is in single-user mode, and there is an active connection?


texts: restore compressed dump query in postgresql asks password

texts:
I'm using ubuntu OS and I took backup using

sudo -u postgres pg_dump dbname | gzip > filename.gz

in the file path as...

/home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname | gzip > filename.gz

Later, I tried to restore it back using these following below...

 1. gunzip -c filename.gz | psql glialmine
 2. cat filename.gz | gunzip | psql
 3. sudo -u postgres  psql pg_restore -d dbname  filename.gz

all these 3 restore option I used asks password, but I have only one login and I tried it and its not accepting it I also tried root password and linux login password too but its not working.

For first two it asks password like below

Password:
psql: fe_sendauth: no password supplied

and for 3rd one it asks password and its error for my password like below

 Password for user pg_restore:
 psql: FATAL:  password authentication failed for user "pg_restore"


texts: Why would an UPDATE SET REPLACE() statement match rows, but change none and give no warnings?

texts:
I'm seeking a certain string in a field and want to replace it with a new string. Specifically, I want all references to one URL to be changed to another URL. I've crafted this SQL statement and am running it at a mysql> prompt on CentOS 5.5 using MySQL Community Server 5.1.54.
update [table] set [field] = REPLACE([field],'%domain.com%','%domain.org%');

The response is:
Query OK, 0 rows affected (0.02 sec)
Rows matched: 618  Changed: 0  Warnings: 0

How can I track down why no changes are being made?
EDIT 1:
Thanks to Aaron Bertrand, I discovered that REPLACE() cannot handle wildcards and I was using it completely wrong (think: missing WHERE clause). Here's is my reformed statement:
UPDATE [table]
SET [column] =
REPLACE (
    [column],
    'companydomain.com',
    'companydomain.org' )
WHERE
    [column]
LIKE
    '%companydomain.com%';

To which I receive the old, familiar:
Query OK, 0 rows affected (0.02 sec)
Rows matched: 167  Changed: 0  Warnings: 0

What could I be doing wrong?
EDIT 2:
I'll tell you what I was doing wrong!! I was not questioning assumptions. My assumption was that the string that I was replacing was in all lower case. The WHERE clause was returning all things that looked LIKE %companydomain.com%. That includes all capitalization permutations such as CompanyDomain.com, CoMpAnYdOmAiN.com and etc.
It passed that on to REPLACE() which then was looking strictly for companydomain.com to then replace it with companydomain.org.
REPLACE (
    [column],
    'companydomain.com',
    'companydomain.org' )

So of course my records were being returned, but nothing was being replaced. Once I changed REPLACE() to take the capitalization into account, all records were updated and it appears that all is well. The correct REPLACE() syntax for my scenario was thus:
REPLACE (
    [column],
    'CompanyDomain.com',
    'companydomain.org' )


texts: How to display Postgres version in the CLI pompt?

texts:
I whish to display the version of the server I am connected to in the command-line (console) interface prompt. From what I read in the documentation, it is possible to execute a shell command, it is possible to display a psql variable value.

The idea is to get the server's version information on connection and use it in the client's prompt. How can I (in the .psqlrc file ?) assign the server version in a psql variable ?

Edit: Jack Douglas♦ has the right answer by pointing out the \gset feature. It ended up with a

show server_version
\gset
\set PROMPT1 '%:server_version: >'

Thank you.


texts:
I have a table as follows:

token  | status   | userid | serviceid
-------+----------+--------+----------
token1 |   ACTIVE |      1 |         2
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL

Token values (token1, token2, ..., tokenN) are only for illustrative purposes. They are completely random hash values.

I want to update a specific row, where token is not used, e.g.

WHERE STATUS="Inactive" AND USERID=NULL AND SERVICEID=NULL

...but I can't really match TOKEN value.

TOKEN column consists of random and unique values, and it also serves as INDEX in my table.

Updating single row is impossible, because all rows can really match this WHERE clause.

UPDATE tbltokens
SET status="Used", userid=1, serviceid=2
WHERE status="Unused"
AND userid=NULL
AND serviceid=NULL;

How can I update a specific row, with unique (and unknown) value and thus not specifying it when executing query?

Would be best to fetch first row where the above mentioned conditions are met and use its token value so I can update first row where conditions are met?

I want to update any single record that fits to desired criteria, and this is WHERE status="Unused" AND serviceid=NULL AND userid=NULL


texts: SQL Server 2016 - sp_execute_external_script

texts:
I'm trying to execute a new stored proc in 2016 named sp_execute_external_script. I first need to enable external scripts as follows:

sp_configure 'external scripts enabled', 1;

After doing this, I see this message:

Configuration option 'external scripts enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.

I then execute RECONFIGURE and see a message saying, "Command(s) completed successfully".

I then attempt to execute the following, to see if R is working correctly:

exec sp_execute_external_script @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go

When I do so, I see the following error:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.

Any ideas why?


texts: Adding a local user account into SQL Server

texts:
I have a (developer) instance of SQL Server 2014 running in my machine. For quick background I'm on a small network like this:

Workgroup: HOME
Users

HOME\PCA
HOME\PCB
HOME\PCC

I'm HOME\PCA. Now I was trying to see if I can make the other machines connect to my instance using an application I'm working on. Port 1433 is working fine and if I go to PCB or PCC and test to create a new ODBC connection I successfully reach the instance.

When in SSMS trying to add HOME\PCB or HOME\PCC It will just deny and tell: Windows NT user or group 'HOME\PCB' not found. Check the name again. Checking in my Security/Logins I see that I am PCA\DevMachine (DevMachine) being my Windows login. If I try yo 'Search' in the 'New Login' dialog the only Location available is PCA.

I don't have a lot of experience in SQL Server permissions but my assumption is that it just wont let the other computers be added to my SQL Server instance because it takes that PCA is the server and so PCB and PCC should lay under PCA\PCB sort of scheme.

Is this anything close to the cause of this behavior? or, is there anything I'm missing that is out of the obvious (All network cables are plugged)?

Regards


texts: How to return multiple columns (each a composite type) in plpythonu

texts:
I have created a plpythonu function that should return a table with multiple columns. At present, it returns a single column with multiple components.

I came across this answer which is A) a little old and B) requires me to separate the components into a table outside of the function.

Here's a simple function that illustrates the problem:

CREATE TYPE foo AS (a INT, b INT);
CREATE OR REPLACE FUNCTION bar () RETURNS SETOF foo AS $$
    return [(1,2),(3,4)]
$$ LANGUAGE plpythonu;

SELECT bar();

This returns two records but only a single column. I want it to return a table with two records and two columns. Is this not possible? That would seem odd but an hour's worth of Googling has gotten me nowhere.


texts:
I have three tables (palettes, boxes, layout) with sample values:

Palettes

|ID_P|sign|
|   1|  P1|
|   2|  P2|
|   3|  P3|
|   4|  P4|

Boxes

|ID_B|sign|
|   1|  B1|
|   2|  B2|
|   3|  B3|
|   4|  B4|

Layout

|ID_L|ID_P|ID_B|
|   1|   1|   1|
|   2|   2|   2|
|   3|   2|   3|
|   4|   3|   4|
|   5|   4|   4|

So I can have 3 cases:

One palete with one box (ID_L = 1)
One palette with more than one boxes on it (ID_L = 2,3)
One big box on more than one palette (ID_L= 4,5)

Now, I need to group it, so I need three separate queries, that will return:

for case 1: all ID_P's of 1:1 "relation"

for case 2: all ID_P's of 1:N "relation"

for case 3: all ID_P's pf N:1 "relation"

Basically I have sollutions for case 2 and 3, but have no idea how to get case 1.


texts: How can I have SQL Server listening on more than one IP address?

texts:
I have a an SQL server that normally listens on a private IP address (10.x.x.x). However, I would like to be able to connect to the database over the public (31.x.x.x). I tried to enable this tonight, but when I restarted the SQL Server service, the server wasn't listening on the internal IP address any more.

I was using the configuration manager to let the SQL Server know the IP addresses it should be listening on.

I am running SQL Server 2008R Standard.


texts: Postgresql: Get pg_stat_statments.calls but per PID

texts:
I am trying to collect information about a particular query, namely select 1 (or more globally, any select ?), and I need to know which application is issuing it the most. I know that I can get the total number of calls from pg_stat_statements, which is the total count, and I can also get which process is issuing it through pg_stat_activity, but I am not sure how I can combine this info to tell which process is issuing it the most. Any ideas?

I can't see a possibility for a join because the pg_stat_statments appears to coalesce all this data across all processes. If I can't use these tables, any others I can use before I can run some other form of profiling?


texts: Why is my primary key a separate relation taking up storage?

texts:
I have a table with a primary key. Postgres automatically created an index on this primary key, and it is separately taking up +34% more storage.

Why does Postgres not simply keep the table sorted by the primary key? It's auto increment so it will always be in order. I tried CLUSTER but that did not decrease the size of this primary key index. Why is a primary key index even needed if the table itself will always be in that order?


texts: Commercial database systems versus open source database systems

texts:
Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version?

The reason for my question is that:

Commercial systems cost upwards of 10.000 $
The most popular websites (e.g. Twitter, Facebook, etc.) all use some open-source engine (for e.g. Twitter used MySQL and is switching slowly to Cassandra), thus proving the reliability of such systems

Is there any financial and/or technical advantage that commercial systems have over the open-source alternatives?


texts: How to check if functional dependencies are preserved?

texts:
I have the following functional dependencies for the schema R=(a,b,c,d,e,g):

{b→c, dg→ce, bc→dg, e→a, g→bd}.

I want to decompose R into two schema's: R1=(a,b,c,e) and R2=(b,d,g) and make sure the functional dependencies are still preserved.

For this schema the candidate keys are b and g. Therefore R1 will contain these dependencies: b→ace and ce→a while R2 will contain bg→d.

I think that the decomposition preserves all the dependencies because:

We can remove ae from b→ace and get b→c (b is a candidate key so b→c holds). This way we get our first original dependency.
We can represent bg→d also as dg→b because g is a candidate key and the closure of G contains B (certainly after the addition of another attribute to G dg→b still holds). Because b is a candidate key then b→ce because B+ contains CE then by the rule of transitivity: dg→b→ce ⇒ dg→ce which proves that another original dependency is preserved.

Using similar tricks preservation of all other original dependencies can be proven. I'm wondering if my logic is correct and transitivity tricks are allowed to prove preservation.


texts: put the result of a query into a variable and WITH statement

texts:
I need to put the result of a query into a variable.
Just a query, works as successful
DECLARE @count INT = (

SELECT count (*)
FROM [AdventureWorks].[Person].[Address]
);

select @count;

But if I need to use the WITH statement in a query, then I get a syntax error
DECLARE @count INT = (
   WITH person_address (id)
   as (
    SELECT AddressID
    FROM [AdventureWorks].[Person].[Address]
   )
   SELECT count (*)
   FROM person_address
);

select @count;

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

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

How do I put the query value into a variable if the WITH clause is used in the SQL statement?


texts: SQL Server 2008 R2 - remove line below heading (which has ----,----) - dont have SSIS

texts:
I have found this line of code online

sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv"

This is used in a SQL job, to export to a CSV file.

There is no SSIS installed, I cannot install SSIS on the machine.

This is what is exported

name,database_id,source_database_id,owner_sid,create_date,compatibility_level,collation_name,user_access,user_access_desc,is_read_only,is_auto_close_on,is_auto_shrink_on,state,state_desc,is_in_standby,is_cleanly_shutdown,is_supplemental_logging_enabled,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on,recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,is_auto_create_stats_on,is_auto_update_stats_on,is_auto_update_stats_async_on,is_ansi_null_default_on,is_ansi_nulls_on,is_ansi_padding_on,is_ansi_warnings_on,is_arithabort_on,is_concat_null_yields_null_on,is_numeric_roundabort_on,is_quoted_identifier_on,is_recursive_triggers_on,is_cursor_close_on_commit_on,is_local_cursor_default,is_fulltext_enabled,is_trustworthy_on,is_db_chaining_on,is_parameterization_forced,is_master_key_encrypted_by_server,is_published,is_subscribed,is_merge_published,is_distributor,is_sync_with_backup,service_broker_guid,is_broker_enabled,log_reuse_wait,
log_reuse_wait_desc,is_date_correlation_on,is_cdc_enabled,is_encrypted,is_honor_broker_priority_on
----,-----------,------------------,---------,-----------,-------------------,--------------,-----------,----------------,------------,----------------,-----------------,-----,----------,-------------,-------------------,-------------------------------,------------------------,-----------------------------,-----------------------------,--------------,-------------------,------------------,-----------------------,-----------------------,-----------------------,-----------------------------,-----------------------,----------------,------------------,-------------------,----------------,-----------------------------,------------------------,-----------------------,------------------------,----------------------------,-----------------------,-------------------,-----------------,-----------------,--------------------------,---------------------------------,------------,-------------,------------------,--------------,-------------------,-------------------,-----------------,--------------,
-------------------,----------------------,--------------,------------,---------------------------
master,1,NULL,0x01,2010-04-08 09:13:36.390,100,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,1,ON,0,3,SIMPLE,2,CHECKSUM,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0xxxx0-0000-0000-0000-000000000000,0,0,NOTHING,0,0,0,0
tempdb,2,NULL,0x01,2014-09-15 08:39:08.587,100,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,0,3,SIMPLE,2,CHECKSUM,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,Exxxxx7-BE30-4829-8AB1-DAA8730978AE,1,0,NOTHING,0,0,0,0

Is there anyway to amend the command in order to exclude the line below the heading please, I need the heading, but they can't accept the line below the heading (the line starting with ----,--)


texts: Linked Server : How to set an exclusive lock on a remote table?

texts:
As I found, you can set a lock on your desired table within a transaction with :

begin tran
    select 1 from myTable
        with (tablock /*or tablockx to block even reads from table*/, holdlock)
    -- do your work
commit tran

But when I run this code on a linked server table, the table still accessible from within linked server! Why? How I can set a lock on this remote table like my example?


texts: SQL Server Logon Auditing and Reporting the data from a DB Table

texts:
Is it possible to configure a SQL Server database to write audit information for logons (success and failure) to a database table that can be queried?

The requirement is to be able to generate a monthly report that captures:

Date / Time
Login Name
User Name
Source Hostname (i.e. the device where the connection is coming from)
Source Username (username on the device the connection is coming from)

Ideally we could capture that data, have it in a table somewhere and query it monthly for the report.

SQL Server Audit doesn't appear to let me capture that in a table - it seems to be a file, security log or application log and my Google Fu has not been strong enough to find anything so far to meet the requirement.

Short of a login trigger to interrogate the session and record that connection attempt in a table that can be queried later, are there any other options available?


texts: Question about a two-node MariaDB Galera Cluster

texts:
Hypothetical situation:

Node1 is where data gets written to by the app server ... it was where the cluster was bootstrapped from. Node 2 is the second node in the cluster. Both these nodes run in the same data center.

Let's say there was a network event; maybe a sysadmin pulled out a switch he wasn't supposed to pull ... we don't know!

Now, both nodes are momentarily offline and both nodes come back online again after say a 5 min downtime where the sysadmin realizes his mistake and hastily puts the switch back in.

Now since both nodes were affected and came back online about the same time ... the cluster automatically corrected itself after spewing several messages like this:

WSREP: Quorum: No node with complete state

re-bootstrapping prim from partitioned components

WSREP: Full re-merge of primary 9j421w44-0e4c-12r5-7fa2-3b63f2c92b0p found

Nobody notices this slight hiccup and everyone assumes everything is fine.

Now after a few days one of the devs begins noticing that data being written to node1 is being overwritten by older data. They pore through app logs and finally through the mysql logs and see the ^above messages.

They run this query on both nodes:

SHOW STATUS LIKE 'wsrep_last_committed';

They find that the value for node 1 lags behind node 2 and come to the conclusion that node2 is overwriting data from node1.

The database admin says "HOGWASH! THIS IS NOT POSSIBLE IN A SYNCHRONOUS REPLICATION CLUSTER SETUP!!"

My questions - is this hypothetical scenario possible? If it is possible, could the Dev be correct in implying that node 2 is overwriting data from node 1?

Asking for a friend (who knows that having a 2 node cluster is just asking for split brain) ...


texts: Copying Partitioned table data to another table on different SQL Server

texts:
We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment.

But the problem is that , Import Export utility does not copy partition tables the same way in target database even if we have created the same partition reference on the target database.

So, now we have only one option left is that of Bulk-copy. But not sure if
bulk copy supports exporting partition data to a target partitioned table.
Do let me know your thoughts on it.


texts: Is it possible to target a specific instance in a clustered environment with group policy?

texts:
Not sure where to ask this, but here goes:

I'm in a clustered environment, not the primary DBA (and he's not very AD/GP savvy either) and I'm being asked to set security permissions for an AD Service Account on a specific instance on a cluster that hosts multiple instances. Is that even a possibility?


texts:
I have a table with a field in with data such as:

id | path
1  | /2/9/1
2  | /2/4/11
3  | /1/3/11
4  | /5/3/2
5  | /2/1/4

I'm wanting to do a select query to show only the first number between the slashes.  Can you use a Regex in the select part of a query?  If so, what is the syntax to find what I need?


texts:
I'm going to start off apologizing since this is my first post and looking at other questions they don't quite match my own situation, also I'm a SysAdmin, not a DBA so my understanding of SQL is rusty and limited.

My organization frequently encounters vendors who in order for their application to run they do what we refer to as lazy programming and request SA credentials or demand that their custom account has sysadmin rights.  My organization also has data protection/compliance policies which require us to grant only required access and rights.

Most recently a vendor that I am working with wants their account to be a sysadmin, but it actually only needs access to a single database.  If we give the AD account (because their application only works with Windows Authentication) sysadmin rights I can use the application's GUI to browse and select the database, but if we only make that same account the DBOwner we can't find anything.

Is there a way to find out if it is trying to poll other databases or determine what the lowest level of rights needed for the required database(s) would be?  When I tried asking the vendor I was told, "It requires sysadmin rights, but its ok because it only accesses the one database."  Which isn't ok when it comes to our policies.

Thanks for any advice.


texts: Which is the uniqueness level of object_id?

texts: Which is the uniqueness level of object_id in SQL Server? I mean, is this value unique per database, per instance, per server,... Also, what happens when I restore the database in another server or instance? And what happens with the object_id if I create the database from a script instead of a backup?


texts:
We have our main MySQL server in city A. We have the bulk of our customer service staff in city B. The staff are complaining that the connection is too slow to the server. The options are:

Have them go out over the public internet instead of our direct link between cities; that way, ping might be longer but they aren't competing with the VOIP traffic between cities
Cluster the database so that writes and reads can happen simultaneously on local servers in both cities.

2 is much preferable, but as you can probably tell by my description I have absolutely no clue how to implement it or even if it is 'clustering.' The best I could find for MySQL Cluster is that it would require using NDB tables and I'd rather not convert our entire database to that. What are my options here? Thanks.


texts: Changing the SQL Server service account - any additional considerations?

texts:
I have inherited a few instances that are running under LocalSystem. I need to change this to a domain account as we are changing our backup strategy to go to a network share and then to tape and I need to be able to assign permissions.

I know that the recommended way to change the service account is to use SQL Server Configuration Manager. However, is there anything else I need to be cognizant of? How can I be sure that directory permissions will suffice for the new service account? Will Configuration Manager assign proper permissions to folders that contain the data/log/binaries, regardless of where they are?


texts: Why default db privilleges are not applied on a new schema?

texts:
I have a user with all privileges granted by default in database properties. I create a new schema as postgres and table there. I expect that the user should have an access to this table but it looks like it doesn't because he also requires an access to the schema.

Why default db privileges are not applied on schema?
How to make make default privileges really default on any new schemas?


texts:
I am trying to generate email alerts from OEM to multiple email addresses. I have configured the SMTP server details from 'Setup' screen. And then I have added the email addresses under the 'Preferences' tab in OEM.
To check if the alerts are working fine, I have shutdown database and restarted 3/4 times. But only once the alerts notification came and that too only to one email address and not to all the email ids mentioned.

Please let me know why this is working in this way. Also, am I missing anything?? Do I need to add anything else to get the notification emails correctly for all the email ids??

Also, there is one screen for notification schedule. Do I have to define some schedule???


texts: No longer able to expand jobs in SQL Server Agent

texts:
In SSMS on a Windows 7 machine, while connecting to a server running SQL Server 2000, I am no longer able to click on the Jobs to see the Object Explorer Details. Previously, this was not an issue, but it appears to have begun not long after a restart of the SQL Server Agent.

  Failed to retrieve data for this request...

  Severity:  11
  Error Number: 0
  State: 0

In SQL Server Enterprise Manager on my XP machine, I am able to view the Job details, but it takes a long time; trying to view the properties of an individual Job also takes an inordinate amount of time.

In SSMS, I am not having any trouble opening tables and running queries or opening procs and running them.

Any insight into causes and/or fixes?

Responses to comments:

Connectivity is good.
msdb.sysjobhistory shows about 6500 records, with 90% of the records created in 2014.
RDP into the 2000 box indicates the same slowness for SQL Server Agent jobs display.
I do not experience lag time when:

I connect to the server
I open a table or procedure
When I run a basic query

Whenever I attempt to view the Job Details under SQL Server Agent, the lag time is significant.
Queries against msdb.sysjobhistory and msdb.sysjobs run fine in Query Analyzer.


texts: What does double number sign ## mean in SQL?

texts:
A meta post gives a nice snippet of SQL

declare @search nvarchar(max) = ##searchfor:string?carefully %listen##
select id [Post Link]
     , score
     , creationdate
     , lastactivitydate
     , closeddate
     , owneruserid as [User Link]
from posts
where body like concat('%', @search, '%') collate SQL_Latin1_General_CP1_CI_AI
or title like concat('%', @search, '%') collate SQL_Latin1_General_CP1_CI_AI

What does double number sign ## mean in there?

I guess that is some kind of string syntax, but I cannot even guess a keyword for that. googling "number sign ## in SQL" returns "prefix temporary tables", which is not the case.


texts: Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells?

texts:
Do varchar/text/longtext columns in MySQL occupy full length size in filesystem even for incomplete cells?

Referring to this blog article for example I have the following columns and i have put in a given string in it.

CHAR (72) - can store upto 72 characters - i put in a string "abcd"
VARCHAR (72) - can store upto 72 characters - i put in a string "abcd"
TINYTEXT - can store upto 255 characters - i put in a string "abcd"
TEXT - can store upto 65,535 characters - i put in a string "abcd"
LONGTEXT - can store upto 4,294,967,295 characters - i put in a string "abcd"

How much actual physical space will be occupied by each of these columns for a row? The full size upto which each column is capable of storing? Or only as much as required for storing "abcd"?


texts: How can I get MySQL to use the value returned by a function as a default?

texts:
I have a pre-existing table that has a column to that can hold a UUID.

I'd like to be able to define a default such that new rows coming in (from legacy systems) will still get a UUID defined, via the UUID() function -- is it possible to configure the default of a column to be a value returned by UUID (in a similar manner to how you can for the UNIX_TIMESTAMP() in phpMyAdmin)


texts:
our RAID10 failed miserably and we now have a corrupted psql database. I copied the main/ folder and now are working with a copy trying to recover data. I have progressed far enough to have made an pg_resetxlog, which at least allowed me start up the database again. I have REINDEX and have made VACUUM and set to overwrite with zeros...
At this point I get the

missing chunk number 0 for toast value x

and

unexpected chunk number 1 (expected 0) for toast value

using the script (taken from https://newbiedba.wordpress.com/2015/07/07/postgresql-missing-chunk-0-for-toast-value-in-pg_toast/)

for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; done

and writing the results to a file, I get some 2000 ?lines? or ?IDs? of ?rows? that are affected.
I don't know what to do from here on. I guess I have to delete the affected lines?

Thanks for the help!
Bert


texts: Log Shipping with minimum synchronization gap

texts:
We will have an application that will go live soon. I will be using Log Shipping for Disaster Recovery  but I am confused about the Job scheduling. The transaction logs will be taken every 10 minutes and is expected to have around 15MB files generated every time. Now if I schedule the Jobs as follows

Back up Every 10 Minutes -----Network Share
Copy every    15 Minutes----- Local Disk
Restore Every 20 Minutes

Now at any point in time there will be a maximum data loss of 10 minutes if the primary goes down. Is there any way to reduce that time and also at the same time make sure the job does not collide with each other... Any expert opinion will be greatly appreciated.


texts: Global only scoped trace flags in MS SQL Server

texts: If the scope of the trace flag is global only does it mean I can't use it only for one connection while not affecting the performance of others?


texts: At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on a fulltext index?

texts:
I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.

I guess that it depends on the amount of indexed data, the time after which the index does not "performs well" (maybe after a lot of writes), the time taken by running  CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) (is it "blocking" DB when running?), etc. so the answer to this question may not be very precise.

However, I would like to know if there are some general rules, principles, recommendations to follow (couldn't find any).


texts:
How to Tune and Optimize MySQL Database, as a reference we are using mysqltuner.pl and we got the below output of slave host which we created recently. our future plan is the perform this same action on master, when performing this action on master slave will become master.

Both on Centos 6.7
Master Version MySQL-server-5.5.41-1.el6.x86_64
Slave Version mysql-community-server-5.6.28-2.el6.x86_64

-------- General Statistics -------------------------------------                                                                      -------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.28-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ------------------------------                                                                      -------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be in                                                                      secure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in MyISAM tables: 335M (Tables: 62)
[--] Data in InnoDB tables: 107G (Tables: 1670)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 551

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 21m 48s (3K q [0.071 qps], 1K conn, TX: 8M, RX: 270M)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 109.1G global + 29.2M per thread (600 max threads)
[!!] Maximum possible memory usage: 126.3G (100% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 0% (2/600)
[OK] Key buffer size / total MyISAM indexes: 1.0G/153.6M
[OK] Key buffer hit rate: 100.0% (7M cached / 2K reads)
[!!] Query cache efficiency: 0.0% (0 cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (7 temp sorts / 125 sorts)
[!!] Joins performed without indexes: 252
[OK] Temporary tables created on disk: 21% (344 on disk / 1K total)
[OK] Thread cache hit rate: 99% (2 created / 1K connections)
[OK] Table cache hit rate: 23% (309 open / 1K opened)
[OK] Open file limit used: 0% (48/20K)
[OK] Table locks acquired immediately: 99% (76K immediate / 76K locks)
[!!] Connections aborted: 20%
[!!] InnoDB data size / buffer pool: 108.0G/107.0G

How do we work on join queries Run OPTIMIZE TABLE closing MySQL connections and footprint for system stability.

-------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
        Your applications are not c properly
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 1M, or use smaller result sets)
        join_buffer_size (> 3.0M, or always use indexes with joins)
        innodb_buffer_pool_size (>= 107G)


texts: Can I do a combination of SQL Server Basic availability groups with SQL Replication?

texts: I need to use SQL Server Standard 2016 to keep the costs low but achieve HA. In addition we need to have two Read nodes for reporting purposes. I am wondering if I can use SQL Server Replication to get data for the two Read nodes. Is it possible to have this setup? What kind of issues this kind of setup may have down the road?


texts:
I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

G0:

mysql> select * from test.t1;
+----+--------+
| id | node   |
+----+--------+
|  1 | DB-G0  |
+----+--------+
1 rows in set (0.00 sec)

G1:

mysql> select * from test.t1;
+----+--------+
| id | node   |
+----+--------+
|  2 | DB-G1  |
+----+--------+
1 rows in set (0.00 sec)

I'm then using the spider engine offered by MariaDB 10.4 (mysqld  Ver 10.4.12-MariaDB-1:10.4.12+maria~bionic-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)) to combine them like this:

CREATE TABLE t1 (
   id BIGINT PRIMARY KEY,
   node TEXT
) ENGINE=Spider
COMMENT='wrapper "mysql"'
PARTITION BY HASH (id) (
 PARTITION par0 COMMENT = 'srv "G0", tbl "t1"',
 PARTITION par1 COMMENT = 'srv "G1", tbl "t1"'
);

It's working well for a simple query like this:

MariaDB [(none)]> select * from test.t1;
+-----+--------+
| id  | node   |
+-----+--------+
|   1 | DB-G0  |
|   2 | DB-G1  |
+-----+--------+
2 rows in set (0.013 sec)

But it doubled the results when using this condition:

MariaDB [(none)]> select * from test.t1 where id != 0;
+-----+--------+
| id  | node   |
+-----+--------+
|   1 | DB-G0  |
|   2 | DB-G1  |
|   1 | DB-G0  |
|   2 | DB-G1  |
+-----+--------+
4 rows in set (0.017 sec)

Here is my Spider related settings:

#
# * Spider
#
spider_bulk_update_mode      = 2
spider_bulk_update_size      = 512K
spider_bulk_size             = 512K
spider_conn_wait_timeout     = 5
spider_connect_timeout       = 3
spider_direct_dup_insert     = 1
spider_direct_order_limit    = 1
spider_multi_split_read      = 1
spider_net_read_timeout      = 5
spider_net_write_timeout     = 5
spider_quick_mode            = 3
spider_quick_page_size       = 1000
spider_remote_trx_isolation  = 1
spider_support_xa            = 0

spider_remote_time_zone        = +00:00
spider_remote_access_charset   = utf8mb4
spider_remote_autocommit       = 1
spider_remote_default_database = 0

spider_general_log          = 1
spider_internal_sql_log_off = 0
spider_log_result_errors    = 4

I commented out all the above settings and tried again, the results are same.
Is there something wrong with me or it is a bug?


texts:
That may sound like a silly question, but let me explain - I wanted to log on to MySQL as user root from several other systems, so I created this:
mysql> create user root@`10.254.%.%` identified by 'w0ws1kr1t';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@`10.254.%.%`;
Query OK, 0 rows affected (0.00 sec)

But when I log on from a system with eg IP 10.254.1.95, it fails with:
2020-07-28 08:05:32 17917 [Warning] Access denied for user 'root'@'10.254.1.95' (using password: YES)

On the other hand, if I don't use root but newarch, it works. I couldn't find it in the docs - is root reserved for local logins only?


texts: Does SQL Server query timeout settings change require a server restart?

texts:
I'm trying to execute a query that times out after 10 minutes. From experience I know that the query takes about 18 minutes to execute. It's not ideal, and this is being worked on, however for now I just need it to execute.

Having changed the Remote query timeout parameter to 0, however, the query is STILL timing out after 10 minutes. I get the message:

The statement has been terminated.
Msg -2, Level 11, State 0, Line 16
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This is using SSMS on a remote computer. Why is the timeout setting not taking effect? Do I need to restart the server? (This isn't possible right now)


texts:
How can I get a listing of all triggers for a database along w/ the table that it belongs to?

I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.


texts: Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

texts:
I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date  columns.

As far as I can see, the (very confusing) docs say you'll have to break out your hex editor and hack the .frm files manually, and switch tables to use the memory storage engine temporarily (what if tables are bigger than memory?)! It goes on and on about how the timestamp, datetime and time columns are not compatible with older versions. As far as I can see from the field definitions, they are backward compatible by default, but have different storage requirements in their implementations, and they have added a new option to specify fractional parts of varying resolution. Of course, when upgrading you will not be using these new formats because they are not available in 5.5.

They make many references to versions prior to 5.6.4 - but I'm not clear whether this applies only to those upgrading from 5.6.[123], or includes anything from 5.*.

The Percona upgrade docs provide a pointer to the same upgrade instructions, but seem to make out that it's a simple binary upgrade and a run of mysql_upgrade, with no mention of this issue.

Am I being concerned about nothing?


texts:
I have an old_server with 50 databases.  I want to create new_server with just 3 databases.

When I try using mysqldump, it seems like all the database directories (although they are empty on new_server) and many unnecessary support files for the other 47 tables are being copied.

Is there any way to avoid this (or clean up afterwards, if unavoidable)?  I would like new_server to be as clean as possible, with no traces of those other 47 databases, if at all possible.

I am tempted to delete those files and directories directly from the file system (or at least temporarily move them to /tmp), but I'd like to avoid this if possible.


texts: Ubuntu / MySQL / Percona XtraBackup gives error "sh: 1: xtrabackup_55: not found"

texts:
Hello I installed Ubuntu 12.04.2 LTS on a fresh box.

This installed MySQL 5.5.29.

I installed percona-xtrabackup-2.0.6-521.tar.gz, and moved the executables to /usr/bin/

But innobackupex is giving an error: "sh: 1: xtrabackup_55: not found"

david@server1:/usr/bin$ sudo innobackupex --user=root --password=123 /home/david/dbb/
[sudo] password for david:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130401 12:17:04  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130401 12:17:04  innobackupex: Connected to database with mysql child process (pid=1488)
130401 12:17:10  innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

sh: 1: xtrabackup_55: not found
innobackupex: fatal error: no 'mysqld' group in MySQL options

The files are present and executable:

david@server1:/usr/bin$ ls -l inno*
-rwxr-xr-x 1 root  root  108823 Mar 19 14:24 innobackupex
lrwxrwxrwx 1 david david     12 Mar 19 14:24 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x 1 root  root   10096 Jan 16 17:24 innochecksum
-rwxr-xr-x 1 root  root  400192 Jan 16 17:24 innotop
david@server1:/usr/bin$
david@server1:/usr/bin$ ls -l xtra*
-rwxr-xr-x 1 root root  9799993 Mar 19 14:21 xtrabackup
-rwxr-xr-x 1 root root  8361704 Mar 19 14:24 xtrabackup_51
-rwxr-xr-x 1 root root 12699861 Mar 19 14:15 xtrabackup_55
david@server1:/usr/bin$

Path is correct:

david@server1:/usr/bin$ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

I get the same results if I run

innobackupex
./innobackupex
sudo innobackupex
sudo ./innobackupex

Any ideas?


texts:
We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.

For our BeginDate and EndDate, we want them to be date, not datetime. Always on beginning of date. So if table changes at 5/3/2018 2:30pm,  SQL Temporal tables will automatically will place datetime, however, we just want date 5/3/2018. Is there anyway to only implement dates, without using Views (which convert the datetime to date), and without modifying/updating the Temporal history table? Is there a property or setting to only use regular date? If no other options, what is best out of the two, views or updating temporal history table?

CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
      CONSTRAINT DF_Department_SysEndTime
    DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));


texts: I need to create directory in Linux with date stamp from SQL*Plus. Is there a command that can be run in *.sql file to create a directory with current date?


texts:
I am working with an Oracle Database and I have a user (called XY) with permissions R/W. I would like to to do automatic backups of the user schema XY. I don't own the server and I don't know if the provider does backup of my schema. Probably does,but I would like to do myself the automatic Backup. I use as Oracle Client the tool SQL Developer.

I know that I can do Extras-> Database Copy but it is not automatic.

Can I achieve this with this client? If so, how?If it is not possible, what alternative do I have?

Thanks!


texts: MySQL server with root user that can be accessed remotely with just a password. How hard is it to break in?

texts:
I have a MySQL server that has a root user enabled.
This root user is protected by a password and can be accessed remotely: %.

The reason for this is from time to time I use MySQL Workbench to access the server and run queries directly to see some stats.

I know that this is bad.

My question is how hard is it to break in.

The server has fail2ban installed so I think multiple failed attempts will block the intruder via IP. The password is somewhat strong.

As a follow up, what can I do to still be able to access the server with MySQL Workbench but improve security of the box.


texts:
I am connecting to Oracle server using PLSQL,this is my tnsnames.ora(C:\Oracle\ora90\network\ADMIN) config:

  MY_ERP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  orcl)
    )
  )

and this is my env variable of ORACLE_HOME:

C:\Oracle\ora90

When I am connecting to server,it throw this error:

error while trying to retrieve text for error ORA-12154

Am I missing something? The OS: Windows 10. PLSQL:7.1.4.1390. Oracle 9.


texts: Can replicated commands be forced to follow each other in transactional replication?

texts:
We have transactional replication setup between an SQL Server 2012 box and a SQL Server 2016 box.

Every half an hour the source table articles are altered by DELETE then INSERT sequence. This sequence comes from a stored procedure executed by an SSIS packgage. Sometimes the DELETE has been replicated to the subscriber, but the INSERT hasn't made it there yet.

The result is interim missing data at the subscriber. This interim state can last several minutes as the commands get replicated out to all the articles under replication. These table articles are used as the basis for SSRS reports at the subscriber, which become inaccurate due to this issue.

Inside the stored procedures that house the DELETE - INSERT statements we tried wrapping both commands in BEGIN TRAN - COMMIT TRAN, but this didn't appear to help.

Ideally the delete - insert would be combined to happen at once (kind of like MERGE) using a different SQL command, or wrapping the commands in something akin to BEGIN TRAN - COMMIT TRAN

I've trended a row count on the replicated tables at the subscriber and there can be a span of 4 - 10 minutes where the deletes have occurred, but the inserts have not.


texts: Update records in Postgres from a select query

texts:
I am having 2 tables in PostgreSQL, have to fetch total records from both table . If count > 50 then update status=5 of oldest record. Here is query for oldest record. Have to update oldest record in single query. Here is query I have tried to find oldest record

 (select   'SPORTS' AS eventType,activity.articleId,activity.title,activity.articleText,
           activity.createdDate,activity.creatorscreenname as creatorScreenNameStr
  FROM     SPORTS activity
  where    activity.status=1
  and      activity.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   'MUSIC' AS musicType,activity.wikiTopicId as articleId,
           activity.title as title,activity.problemDescription as articleText,
           activity.createdDate,activity.creatorScreenName as creatorScreenNameStr
  FROM     MUSIC activity
  where    activity.status=1
  and      activity.createdBy=101
  order by createdDate ASC limit 1)
 ORDER BY 5 ASC limit 1


texts: In pgPool-II 3.7.5 configuration pgpool.conf, is recovery_user a role or a system user?

texts:
I'm having trouble configuing pgPool-II 3.7.5 for online recovery. In pgpool.conf, there is a parameter called recovery_user along with a recovery_password that are somehow used to run recovery_1st_stage_command.

The pgPool docs say of recovery_user:

  Specifies the PostgreSQL user name to perform online recovery.

To me, this indicates very clearly that recovery_user is the name of a PostgreSQL user (role in the database). However, in a previous version of the docs, it also says

  You need to setup ssh so that recovery_user can login from the primary to the standby without being asked for a password.

which clearly indicates that recovery_user should be a system user. Granted this is documentation from pretty different versions but it's hard for me to believe that the implementation of this feature changed in that way.

Which is it, is recovery_user a database role or a server user?

Bonus: Please explain how pgPool-II orchestrates running recovery_1st_stage_command


texts:
As we are building a multi-tenant app, we will be using one DB only approach, and having a column with customer_id to isolate accounts data.

The question is, is it possible in postgres to shard data based on a range of column values? (ex: customer_id > n & < n+ 100 )
thus not having all customers into one node? or its only possible to shard a specific customer id data?


texts:
I'm creating a system as college's mini project, in which I've two levels of admins. The level-0 admin is a single user which will add/manages both users and level-1 admins. Can you suggest me good database design for the system.

Do I have to create different tables for all different kind of users?

And if single table will work how would be the table look like?
EDIT
So, this is what I used to do before.
Table-0 Admin with fields adminId, adminName and password
Table-1 SubAdmin with fields sadminId, adminId (references Admin table's field adminId), sadminName, and password.
Table-2 Users with fields uid, adminId (references Admin table's field adminId), userName, and password.


texts: SQL Server mixed mode authentication not being set

texts:
I have a local developer instance of SQL Server installed on my machine. I set the mixed mode authentication:

Open SSMS
Connect to localhost (windows auth)
Right click localhost connection, right click -> properties
Security tab
Choose "SQL Server and Windows Authentication mode"

I restarted my computer, and verified the above settings are selected.

I cannot login using a username and password I created. Windows Event Viewer shows

  Login failed for user 'ctx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: ]

Why does event view say "Integrated authentication only" when SQL Server shows "SQL Server and Windows Authentication mode"?

-- Windows 10 Enterprise

SELECT @@VERSION
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17763: )


texts: Can I see Historical Queries run on a SQL Server database?

texts:
Someone was running a query on our SQL Server database remotely and their system crashed.

They have no backup of that query and want to see what was run on the server.

Is it possible to find this query in a log or in a history somewhere?


texts: SQL Server 2008 R2 DETA Error: Can not get minimal database information in allotted time

texts:
I have a 18,6 GB SQL Server 2008 R2 profiler trace file. Trace file recorded at tuning profile. When I submit this trace file to database engine tuning advisor I get this error:

  Error: Can not get minimal database information in allotted time

How can I solve this ?

Windows Server 2008 R2, SQL Server 2008 R2
24 GB ram , 16 cores server


texts: Configure Master with Backup Master Replication in mySQL

texts:
Hello guys, Im currently try to figure out how to set this topology up.
I know how to do the Master to Slave configuration.
But the part of struggling is Master to Master(backup)
Anyone know how to configure that part ?
I'm using MySQL 8.0
Thanks for reading and your time !


texts: What is the best approach to managing daily excel flat file imports into SQL Server with varying column counts per file?

texts:
I need to load excel spreadsheets into SQL Server on a daily basis. It's simple enough to build an SSIS package but, the problem that I am facing is that the client is allowed to name the column headers as they like, the columns can be in whatever order, and the column count can vary.

This is the best I can get the owner to agree to.

Each client's excel file will have one column that is the same no matter what. This column will be the Tax ID Number.
Each client must use the same format on their excel spreadsheet each time they submit their file.

This is what the final product must look like.

I need to be able to load automate loading all the excel data and headers into a SQL Server database without losing any data with each client having varying column counts.
the asp.net mvc application must be able to query a tax id that is entered by the customer. The query must return the headers from the excel spreadsheet and all the corresponding data associated with that record.

I tried to get better consistency but, this is the best I can get. I have normally worked with data that had predefined parameters so, I would appreciate any help with suggestions to help me move this forward.


texts:
I have a query as part of my process, that I execute hundreds of times in a loop.

Initially, Table A contains all records (20mil).  Table B contains 0 records.
Primary key in both tables ID
the I execute:

select * from table A where a.ID not in (select ID from table b) limit 10000
##magic stuff in python
insert everything to table B, once again, .

Initially, the query runs super fast, but after Nth loop (100th+), size of table B increases, to the point where I it takes a bit of time to perform the NOT IN operation.

Does anyone have recommendations on how I can speed up the query?
- So far, I've tweaked the default mysql bugger to be 1.5gbs (ids are pretty small INTs, so that should be enough).

Caveats:

1) One way to do this would be to remove * from table A after I've processed them.  However, I want to keep table A in tact.

... only method I could think of I adding another column to table A (which I'd index) called 'PROCESSED'... then update that column with a second query once the records have been processed/posted, but I was hoping there was an easier solution.

Thank you all in advance.


texts:
I am writing a game that uses an embedded mySQL database for keeping track of scores and   I am releasing an alpha soon.

I know there are going to be features added down the road which are going to require changes to the database structure.

Up to now, whenever I changed the database's schema, I just deleted what i had and had a new one generated.

But now that I'm releasing an alpha, I don't want to be so careless with my players' score data.

It feels like I would like to give the database a "version number", so when the game starts, I can check what version database it is, and if it's not the current version, automatically do whatever surgery on the database to give it the right schema.

My intuitive approach to doing this is adding a single row, single column table called "DB_VERSION" and then checking it each time on load.  This way, if I change the database, I can increase the number in my code, and when I come across a database in the wild that has a lower number, I can upgrade the database structure automatically.

While this will work, intuitively something is telling me this is The Wrong Way.

Is there a built-in way to give a database a version ID? Is there some other approach to solving this problem that I haven't thought of? I'm sure this isn't a unique problem.

Thanks!

Here is the code I would like you to review:

When the database is first built:

statement.execute ( "CREATE TABLE DBVersion ( Version INT NOT NULL )" );

and then each time the program starts:

statement.execute ( "Select Version from DBVersion");
int databaseVersion = rs.getInt ( "Version" );

if( Database.version != databaseVersion ) {
   //Update database schema
}


texts: How to check the consistency of tables in mysql

texts:
I have few questions regarding consistency in Mysql in two scenarios

1st scenario:
How can I verify a restored backup matches the original DB data on all the tables?

We have two hosts with the same database A and B (the same data, restored to different DBs) if there any way to check if the data is really the same. Maybe kind of md5 hash on table would help. If there are existing solutions? Of course it's not complicated to do something by myself, kind of validator.

2nd scenario
Let's say we have very strict logical schema for database. I need to check that our DB works according to schema. Let's say every Order has ProductId and etc. It sounds much more complicated, if you have faced these problem, how you deal with them.

Thanks!


texts: complex query joining multiple tables with aggregate functions

texts:
I have the following db structure:

Channels and packages table have many to many relations by channel_package table.

User can purchase packages or channels separately which is stored in purchases table. So when a user purchases a channel channel_id is stored as product_id and cat_id=2 and when a package is purchased, package_id is stored as product_id and cat_id=1.

What I want to get is list of channels having maximum expiry for a particular user.

If the purchases table would only store channels it would have been easy like

SELECT
    p.id,c.name,
    max(p.expiry)
FROM
    purchases as p
    LEFT JOIN channels as c
        on c.id=p.product_id
WHERE
    cat_id = 2
GROUP by c.id

So how can i get the same result considering the packages (packages contains channels, if the channel contained in the package has more expiry? The package's expiry should be displayed as max(expiry).


texts: Deploy SSRS 2012 report to a distant Sharepoint 2010 server

texts:
My installation :
- SQL server 2008 r2 + Sharepoint server (main farm) = VM1
- SQL server 2012 + SSRS 2012 (not native mode) = VM2

I would like, if possible , make my SSRS configuration manager (VM2) to point to the report server database (VM1).

The problem is that as I installed SSRS in Sharepoint integrated mode (not native), the configuration manager opens and asks for a server and instance. Whatever I try to put in the server case, VM 1 (what I want ) or even VM2 , it can not establish the connection.

Machines ping each others.

Any ideas ?


texts:
I am trying to connect FedEx Shipping Manager Integration Assistant to a SQL 2008 R2 database.  I have a view created that has all the necessary fields to populate in the Integration Assistant.  The problem, I am having, is I can make a change to the view on the SQL box, and it's not getting to the Integration Assistant.

I have attempted a new Integration, but, the new fields are not showing up.  I have even attempted to use Excel and connect through using Microsoft Query, and I'm getting old data.  I can change the view, but, it won't show up in Microsoft Query.


texts: SSIS package deployed to SQL Server hangs when running a Powershell script

texts:
We have a SSIS package that tidies up a few files and folders, and want to automate it to run every night via the SQL Server Agent.
When running the package via Visual Studio (2015 and 2017) the package completes successfully. When the package is deployed to SQL Server 2012, it never gets past the element that runs a Powershell script - it simply hangs forever (3 days is the record so far).
SSIS data flow:

"Run Compression PowerShell Script" task:

The Powershell script is very basic - two lines of code:
$filePathName = $args[0];

Compress-Archive -Path $filePathName -DestinationPath $filePathName

We think it may be a permissions issue, but we're not sure where to progress this further.
Any help is gratefully received.
Thank you


texts: Method for identifying max_allowed_packet queries generating errors in MySQL

texts:
I'm wondering if anyone knows of a good way to track down queries causing max_allowed_packet errors.

Even if you turn on general query logging the query isn't logged as the
connections aborted before it gets that far.

I'm trying to give something to the development team to help track this down.  All I know is a general application account that causes it periodically.  It's not like clockwork so it seems to be because of some kind of user generated interaction (versus a cronjob for example).

I had thought about bumping the limit on the master with out adjusting the value on the slave to let replication break but this is coming from a read only account, meaning it has to be a select of some kind.

The current value is set to 8M and there's not an expected use case dev would expect to generate much so just upping the limit to sweep it under the rug isn't really addressing the problem.   I'd like involved parties to know what exactly is causing it so an informed decision can be made on whether bumping the limit is the right course of action (versus fixing some bug that shouldn't be generating queries that large).

I'm trying to get buy in to get app changes to either inspect query size before lobbing it over the fence, or at least catch those specific exceptions in a manner that provides sufficient information to find out what's generating it.  They have other priorities so it would be nice if I could just say "this is exactly it"

Edit: Turning on general query logging with an upped max allowed size to let the selects goes through is prohibitive since the pattern is not predictable and just 'letting it run for a couple days' isn't possible with the space available.


texts:
mysql  Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using  EditLine wrapper

I'm trying to configure my MYSQL instance to turn off some of the stricter settings currently I've done this:

   mysql> select @@GLOBAL.sql_mode;
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@GLOBAL.sql_mode                                                                                |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

sudo vi /etc/mysql/mysql.cnf

[mysqld]
sql-mode=""

mysqld restart. Then try to see what the global SQL mode is:

mysql> select @@GLOBAL.sql_mode;
        +-------------------------------------------------------------------------------------------------------------------------------------------+
        | @@GLOBAL.sql_mode                                                                                |
        +-------------------------------------------------------------------------------------------------------------------------------------------+
        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

Anyone any ideas on what to try next. Can't seem to get this setting to stick permanently and can't see any other files to try.

Edit:
locate returns:

/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/etc/mysql/my.cnf.fallback
/var/lib/dpkg/alternatives/my.cnf

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf


texts: How can I speed up a DateTime comparison query in MySQL?

texts:
I am performing the following query on a MySQL table with around a million records ( ~350MB ).  It takes roughly 1.5 seconds every time.  If I remove the OR last_updated > last_geocoded comparison, the query time improves by an order of magnitude, to less than 100ms.  If I remove the last_geocoded = 0 line, the query time stays at roughly 1.5 seconds.

SELECT id, company_id, raw_address
FROM locations
WHERE
    raw_address IS NOT NULL
    AND (
        last_geocoded = 0
        OR last_updated > last_geocoded
    )
LIMIT 25

If I reduce the query to just WHERE last_updated > last_geocoded, the query time is less than 100ms.

SELECT id, company_id, raw_address
FROM locations
WHERE last_updated > last_geocoded
LIMIT 25

How can I speed up the original query?  Why is that WHERE statement slowing down my query so much?

Below is the table schema showing my keys:

CREATE TABLE `locations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(11) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `raw_address` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `city_code` varchar(255) DEFAULT NULL,
  `county` varchar(255) DEFAULT NULL,
  `county_code` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `state_code` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `country_code` varchar(255) DEFAULT NULL,
  `constituency` varchar(255) DEFAULT NULL,
  `constituency_code` varchar(255) DEFAULT NULL,
  `postal_code` int(5) unsigned zerofill DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lon` float(10,6) DEFAULT NULL,
  `headquarters` tinyint(1) unsigned DEFAULT NULL,
  `research_and_development` tinyint(1) unsigned DEFAULT NULL,
  `manufacturing` tinyint(1) unsigned DEFAULT NULL,
  `distribution` tinyint(1) unsigned DEFAULT NULL,
  `sales` tinyint(1) unsigned DEFAULT NULL,
  `retail` tinyint(1) unsigned DEFAULT NULL,
  `source_id` int(11) unsigned DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `last_geocoded` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `raw_address` (`raw_address`),
  KEY `last_updated` (`last_updated`),
  KEY `last_geocoded` (`last_geocoded`),
  KEY `city` (`city`),
  KEY `county` (`county`),
  KEY `state` (`state`),
  KEY `country` (`country`),
  KEY `postal_code` (`postal_code`)
) ENGINE=InnoDB AUTO_INCREMENT=997680 DEFAULT CHARSET=latin1;

Below is the original query run with EXPLAIN:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","locations","range","raw_address,last_geocoded","raw_address",258,NULL,509543,"Using where"


texts: pgAdmin 4 restore ignores "Clean before restore"

texts:
I am using pgadmin v4.22 to backup and restore a database. This works fine the first time, but when I want to restore it again to a backup with deleting the old data, pgAdmin seems to ignore the Clean before restore setting. I end up with multiple identical entries.
This is what I am doing:
Right click on the database, and click on Restore...
Then I select myBackup.backup and set Role name to postgres.
In the Restore options tab I activate:
Sections: Data
Queries: Clean before restore

When clicking on restore, it succeeds in about 1.5 seconds for a 9 MB backup. This is the log:
pg_restore: connecting to database for restore
pg_restore: implied data-only restore
pg_restore: processing data for table "public.myTable"
pg_restore: executing SEQUENCE SET some_number_seq

It does not mention any clean step. When I query the database I have identical entries now.


texts: I've decided to shard a collection with 500M+ documents into 24 shards. All shards are on the same machine without replicas as was advised in this title (core sharding). The shard key is {"_id":1}. I encountered, that chunk migration is extremely slow. It have been processing 2 days and progress is about 30%. I tried to move chunks manually. I stopped the balancer and executed commands from this title But it seems to me that nothing happened. I started the balancer again and it continue to migrate chunks in the same way. Is there any method to make it faster?


texts: how to prevent any update to my database before a exp operation?

texts:
I want to migrate my database from AIX to Red Hat vai exp/imp.

how to prevent clients make any update to my database during the migration progress ?


texts: Fileshare witness for 3 node Multisubnet AAG setup

texts:
I am planning for a 3 node AG, 2 in DC and 1 in San Jose. Host OS will be Windows Server 2012 R2.

We dont have an option of a 3rd site, so my question is where can I place the FSW if I have to do a planned manual failover?

Also, once manual failover is performed, should I be disabling the votes of the nodes in the primary site?

Please advise.


texts: Name of not null constraint is missing in error message

texts:
The relation was created in Postgres 9.6 with use of the following commands:

 CREATE TABLE import_event (
   import_event_id BIGINT NOT NULL,
   filename VARCHAR (200) CONSTRAINT NN_import_event__filename NOT NULL
 );

 ALTER TABLE ONLY import_event ADD CONSTRAINT PK_import_cak_key PRIMARY KEY (import_event_id);

Notice that not null constraint is named.
Then the following command was executed

 insert into import_event (import_event_id) values (1);

Of course, the constraint violation error was raised. The content of the message is as follows

 ERROR:  null value in column "filename" violates not-null constraint
 DETAIL:  Failing row contains (1, null).**strong text**

The problem is that the name of not null constraint is missing in that message. However, the correct error message:

 ERROR:  new row for relation "import_event" violates check constraint "nn_import_event__filename"

is produced when not null constraint is declared as follows

 ALTER TABLE ONLY import_event ADD CONSTRAINT NN_import_event__filename CHECK(filename IS NOT NULL);

Is that a bug in Postgres 9.6 or is there a problem with constraint declaration?


texts:
context :

Teachers create problems to their Students
A Problem have Numerals(Questions)
A Numeral(Question) have Steps to guide the student to the answer
Choice represent the type of mathematical abstraction that fits
better

Solve: (unknown variable, starting expression)
Simplify: (reference, target)
Plot: (slope,b)

Base Models and Relations:

Problems: one to Many Numerals(Questions)
Numerals: zero to Many Steps
Steps:

Requirements:

Numerals and Steps now have a choice which where described above, currently just 3 choices but it's expected to grow. only one and at least one choice
Depending on the choice the numeral or step will add new attributes
for now choices do not have any relations between them
choice in Numeral could be different to the choice in Step and vice versa (see Example)

Example: did my best to make a close abstraction to show the idea, I've made a JSON Structure, almost identical to the structure used in my front end. Maybe the example is a little bit dumb but it gives a taste.

My Attempts:
For simplicity , Models are shown with the minimum attributes

1st:

-My concerning with this approach is that the amount of FK in the Numeral and Step model. Since only one choice is possible, i would have choices-1 amount of useless columns per record

2nd:

-I'm programmatically checking for the choice field in the Numeral or the Step, and then using the proper table to fill the data, this second attempt started as a solution to fix the empty columns in the 1st approach
-My concerning with this approach is that the amount of tables will grown 2*n, since for each choice i need to create the corresponding table for step or numeral

I'm open to suggestions or ideas about this attempts.
Edit:
Thanks to @J.D for suggesting this idea. i've modified a bit because i need to keep track of the choices and their fields somewhere somewhere. I think this solution is easier to mantain and removes the need to add tables in case of new choices

3rd:


texts:
I'm not able to add administrator role in SQL Server 2008 R2.

When I try to add new user it throws me the following error:

  Cannot read property Collation.This property is not available on SQL Server 7.0. (Microsoft.SqlServer.Smo).


texts: Should I join datetime to a date using cast or range?

texts:
This question is a take-off from the excellent one posed here:

Cast to date is sargable but is it a good idea?

In my case, I am not concerned with the WHERE clause but in joining to an events table which has a column of type DATE

One table has DATETIME2 and the other has DATE... so I can effectively JOIN using a CAST( AS DATE) or I can use a "traditional" range query (>= date AND < date+1).

My question is which is preferable? The DATETIME values will almost never match the predicate DATE value.

I expect to stay on the order of 2M rows having the DATETIME and under 5k having the DATE (if this consideration makes a difference)

Should I expect the same behavior on the JOIN as I might using the WHERE clause? Which should I prefer to retain performance with scaling? Does the answer change with MSSQL 2012?

My generalized use-case is to treat my events table like a calendar table

SELECT
    events.columns
    ,SOME_AGGREGATIONS(tasks.column)
FROM
    events
LEFT OUTER JOIN
    tasks
        --This appropriately states my intent clearer
        ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn
        --But is this more effective/scalable?
        --ON tasks.datetimecolumn >= events.datecolumn
        --AND tasks.datetimecolumn < DATEADD(day,1,events.datecolumn)
GROUP BY
    events.columns


texts: mysql - calculate traffic for tmp tables

texts:
I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. So, placing tmpdir to SSD is good idea, or it will die with our heavy load within 2 or 3 monthes?

I simply want to get to calculate "traffic", being written by DB to tmpdir during some period of time (hour, day, etc), and using this value calculate "average" life of SSD.

How can I do this?
Kingston SSD SH103S3/240G
mysql-5.6 (900+ tables, 140Gb size of database) Debian-9


texts:
I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows.

CREATE TABLE `foobar` (
`foo_id` INT(10) UNSIGNED NOT NULL,
`bar_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`foo_id`, `bar_id`),
INDEX `bar_id_idx` (`bar_id`))

I have another table counting the foo_id's in foobar:

CREATE TABLE `foo_amount` (
`foo_id` INT(10) UNSIGNED NOT NULL,
`amount` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`foo_id`),
INDEX `amount_idx` (`amount`))

The counting could be done like this:

INSERT INTO foo_amount (SELECT foo_id, COUNT(*) AS amount FROM foobar GROUP BY foo_id);

But I would have to recompute the table with every inserted/deleted row in foobar.

An insert usually adds a new bar-object with several hundered foo's. For example inserting a bar with bar_id 42 having foo's with foo_id's 3, 8, 26, 44, .... would look like:

INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42), ...;

My second attempt was to update the foo_count table after every inserted bar object:

INSERT INTO foo_amount (SELECT foo_id, 1 FROM foobar WHERE bar_id = 42)
ON DUPLICATE KEY UPDATE amount = amount + 1;

But this is very slow. Do you have any ideas on how to optimize this? An option might be to accumulate new bar's in a temporary foo_count_tmp and merging it with foo_count every now and then. The foo_count table wouldn't be up-to-date all the time, but that's ok. But how would I trigger the updating then?


texts: CONCAT() between different alphabet echoes out '&' ampersand

texts:
I want to concatenate different alphabet strings and get a weird latin encoding with ampersands after the sixth greek character (I want to concatenate greek and english alphabet strings).

Lets say I have :

COL 1   COL 2
John    Johnson

and instead of John Johnson i get John Johnso&adasdasa

any help ?


texts: SSIS Package when executed via agent job or manually on SQL Server does nothing, but shows it completed successfully. What gives?

texts: I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.