texts: Why do I get Invalid identifier error even though column exist

texts:
WITH distinct_appl_temp AS (
  SELECT DISTINCT
    c_number
   ,j_group
   ,pi_c_dt
   ,act_can
  FROM app_fact vd
  WHERE
      vd.c_number   = 12335
    AND vd.act_can    = 1
    AND vd.pi_c_dt    = (
        SELECT
          MIN( v2.pi_c_dt )
        FROM app_fact v2
        WHERE
            v2.c_number   = vd.c_number
          AND v2.j_group    = vd.j_group
          AND v2.act_can    = vd.act_can
          AND add_months( v2.pi_c_dt,+6 ) > vd.pi_c_dt
      )
  GROUP BY
    vd.c_number
   ,vd.j_group
   ,vd.pi_c_dt
   ,vd.act_can
  ORDER BY
    vd.j_group
   ,vd.act_can
) SELECT
  dt.c_number
 ,dt.j_group
 ,dt.pi_c_dt
 ,dt.act_can
 ,da.pi_c_dt
FROM distinct_appl_temp dt
 ,   app_fact da
WHERE
    dt.c_number   = da.c_number
  AND dt.j_group    = da.j_group
  AND dt.pi_c_dt    = da.pi_c_dt
  AND dt.act_can    = da.act_can


texts:
I'm working on booking service.

I have a table where users enter their open hours. These pieces of information allow me to generate slot times for a particular day.

For example, for row one, I know that the user works on Monday morning between 10:00 am and 12:00 am and he proposes a service of 30 minutes.

We have 4 slots of 30 minutes each.

10:00 -> 10:30
10:30 -> 11:00
11:00 -> 11:30
11:30 -> 12:00

When I generate these slot times, I need to relate them to a particular day. For example December 16, Monday.

10:00 -> 10:30
10:30 -> 11:00
11:00 -> 11:30
11:30 -> 12:00

But I don't know how to do that. In my database, I know that the particular user works on Monday, but it's mean nothing for SQL. How can I generate that on a particular Monday?

For the moment, I don't have a lot of things to show...

I created this table, it represents the working hours of a User.

For example.

Monday 10:00am to 12:00am
Monday 3:00pm to 6:00pm

CREATE TABLE allow_day_and_time (
    id BIGINT NOT NULL
    workingday varchar(50) NOT NULL,
    start_time time NOT NULL,
    end_time time NOT NULL,
);

I had an idea, but I'm not sure if its the right way to do that...

Generate a series of days between today and a random end day.
Return the timestamps of all allowed days filled on allow_day_and_time table.

CREATE OR REPLACE FUNCTION fetchWorkingDays()
 RETURNS TABLE(id bigint, workingday character varying, end_time time without time zone, start_time time without time zone)
 LANGUAGE plpgsql
AS $function$
DECLARE
        day_ VARCHAR;
BEGIN
    FOR day_ IN
    SELECT
        TO_CHAR(date_trunc('day', DD)::DATE, 'day')
    FROM
        generate_series(NOW()::timestamp, '2019-12-31'::timestamp, '1 day'::interval) DD LOOP
        RETURN QUERY SELECT * FROM allow_day_and_time WHERE allow_day_and_time.workingday = '_day';
    END LOOP;
END;
$function$

Maybe, I was not clear in my explanation.

The main idea of this is to generate slot times by taking to account all constraints.

I don't want to record the availabilities in DB but record booking slots.

What're these constraints?

The user has working hours stocked in allow_days_and_times table (Which means that the user can work on Monday to 10:00 am to 3:00 pm for example).

Here the allow_days_and_times table

CREATE TABLE allow_day_and_time (
period_id uuid DEFAULT uuid_generate_v4(),
day VACHAR(50) NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL
)

The user has blocked periods stocked in block_period table, like Christmas holidays for example.

Here the block_period table

CREATE TABLE block_period (
period_id uuid DEFAULT uuid_generate_v4(),
start_period TIMESTAMP NOT NULL,
end_period TIMESTAMP NOT NULL
)

Finally, the user has theses booked times stocked in booked_slots

Here the booked_slots table

CREATE TABLE booked_slots (
booking_id uuid DEFAULT uuid_generate_v4(),
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL
)

I've simplified as much as possible the table.

Okay, now, let's say I have an API in charge to find all free slots for a particular user. GET /api/user/xxx/availabilities

How can I generate all the availabilities for this user by taking to account all these constraints?


texts:
I'm using MariaDB 10.1. How to get actual query time for all of executed query? It seems that SHOW PROFILES; was deprecated. Is there a better solution for database tuning?

Edit: It's deprecated only in MySQL 5.6.7.


texts: How do I login to a Postgres database without a password?

texts:
Right now my Postgres' pg_hba.conf file has the following lines:

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
#Should allow connection without password
host    all all 0.0.0.0/0   trust
host    all all ::0/0   trust

(I know that this is insecure. I'm just testing, and once it is successful, I'll keep it only specific users & databases)

My understanding was that trust should allow a user to connect without a password.

But when I do psql -U postgres -d postgres -h 127.0.0.1 -w, I get the error message: psql: fe_sendauth: no password supplied

What do I need to do to allow users to connect without a password?


texts: How to List all roles and their members (users/groups) of an Azure Analysis Services tabular model/database

texts:
I have a tabular model/database in an Azure Analysis Server instance. Model/database has roles and users/groups were added on each role. I can see the list by right click on the role and then go to membership.

My questions are:

1-Is there a script I can use to list the roles and the users in each role without using the method described? It can be using powershell, TMSL or any other ideas.

2-When you add a group as a role member, the group is displayed as a long hexadecimal string, is there a way to get the name of the group?


texts: Removing superfluous tables with a synchronization?

texts: I'm using SQL Server 2008 r2's replication functionality to update my subscriber database through a transactional pull subscription.  When I mark it for reinitialization, it does fix the schema and data of any modified local tables that exist in the publication snapshot, but it doesn't remove any new tables (and presumably SPs, triggers, etc.) that have been added.  Is there any way to get the synchronization to remove superfluous objects like tables that don't exist in the publication shapshot, in addition to updating and adding existing objects?


texts: SQL Server 2016 on Microsoft Failover cluster

texts: Do I need SQL Server 2016 Enterprise Edition for installing it on a Microsoft failover cluster?


texts: I am trying to connect My R studio to SQL Database on the Remote Desktop

texts:
The SQL Server on the remote desktop is configured to have neither login nor password, it is configured to use Windows Authentication.

Code

I am using the following code to connect:

library(DBI)
library(RODBC)
dbhandle <- odbcDriverConnect('driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS')

Error Generated

Warning messages:

1:

    In odbcDriverConnect("driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS") :
      [RODBC] ERROR: state 28000, code 18456,
message [Microsoft][ODBC SQL Server Driver][SQL Server]

Login failed for user 'HMINDIA\274074'

2:

In odbcDriverConnect("driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS")
   ODBC connection failed


texts: Hiding a SQL Server vs Turning Off SQL Browser

texts:
Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager?

I've always assumed they do the same thing and have have hidden the server as a backup in case the SQL Browser service ever gets turned on but cannot find any documentation confirming this.

Thanks in advance!


texts: PostgreSQL can't start after tuning some options

texts:
I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this

postgresql.service - LEGACY unit for "postgresql" rc script
          Loaded: loaded (/etc/rc.conf)
          Active: failed (Result: exit-code) since Mon 2013-02-18 15:55:37 BRT; 1min 8s ago
            Docs: man:arch-daemons(8)
         Process: 7216 ExecStop=/etc/rc.d/postgresql stop (code=exited, status=0/SUCCESS)
         Process: 8375 ExecStart=/etc/rc.d/postgresql start (code=exited, status=1/FAILURE)
         Process: 8372 ExecStartPre=/usr/bin/echo WARNING: A legacy rc script is being used. Consider removing this from your DAEMONS array and enabling a native systemd service instead. (code=exited, status=0/SUCCESS)
        Main PID: 19616 (code=exited, status=0/SUCCESS)
          CGroup: name=systemd:/system/postgresql.service

Feb 18 15:55:32 gtsserver echo[8372]: WARNING: A legacy rc script is being used. Consider removing this from y...tead.
Feb 18 15:55:32 gtsserver su[8381]: (to postgres) root on none
Feb 18 15:55:32 gtsserver su[8381]: pam_unix(su-l:session): session opened for user postgres by (uid=0)
Feb 18 15:55:37 gtsserver postgresql[8375]: :: Starting PostgreSQL    [BUSY] pg_ctl: could not start server
Feb 18 15:55:37 gtsserver postgresql[8375]: Examine the log output.
Feb 18 15:55:37 gtsserver postgresql[8375]: [FAIL]
Feb 18 15:55:37 gtsserver systemd[1]: postgresql.service: control process exited, code=exited status=1
Feb 18 15:55:37 gtsserver systemd[1]: Failed to start LEGACY unit for "postgresql" rc script.
Feb 18 15:55:37 gtsserver systemd[1]: Unit postgresql.service entered failed state

Tried with 150, same behavior...
It happens the same error if I change shared buffer (currently 24), to something like 32.
The server is a 8GB arch linux
Linux gtsserver 3.7.8-1-ck #1 SMP PREEMPT Thu Feb 14 19:14:20 EST 2013 x86_64 GNU/Linux


texts: HowTo: [Output File Cleanup] workaround for UNC targets

texts:
Hallengren job [Output File Cleanup] silently fails when the output file directory is a network file share.

Question:  Is there a better, cleaner way to resolve the issue detailed below when the job step Output File destination is a network file share.

UNC paths are not supported by the job [Output File Cleanup] when setting SET @OutputFileDirectory to a file share.  Sample error.

ERROR: UNC paths (\machine\share) are not supported.

Sample command:

cmd /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs" /m ___.txt /d -30 2^>^&1') do if EXIST "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs"\%v echo del "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs"\%v& del "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs"\%v"

Full text of sampled error as directly observed from CMD prompt:

c:\ >if EXIST "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs"\ERROR: UNC paths (\machine\share) are not supported. echo del "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs"\ERROR: UNC paths (\machine\share) are not supported.   & del "\MyBackupServer01\Backup$\SQLServerToBackup02\MaintenancePlanLogs"\ERROR: UNC paths (\machine\share) are not supported.

Workaround:

echo ""  && echo ""  && echo ""  && echo "" && echo Starting delete of files on: && date /t && time /t && echo \MyBackupServer01\Backup$\SQLServerToBackup02 && pushd \MyBackupServer01\Backup$\SQLServerToBackup02 && echo Search Criteria:  .\MaintenancePlanLogs /M ___.txt /D -30  /C && echo. && ForFiles /S /P .\MaintenancePlanLogs /M ___.txt /D -30 /C "cmd /c if @isdir==FALSE echo @file && dir @file && echo DEL @file && DEL @file " & popd && echo ""  && echo Cleanup finished.


texts: SSIS 2017 KerberosSupport - Double Hop Issue

texts:
I am having an issue with what appears to be a kerberos double hop issue with SSIS 2017.

The setup consists of a backend database cluster servers (DBSERVER01\02) an Application\SSIS server (APPSERVER01) and various clients connecting from other hosts with SSMS. Managed Service accounts are used for all SQL services and trusted for delegation, SPNs have been set for the MSSQL DB Service, the SQL Kerberos checker passes all tests. An SSISDB catalogue has been deployed and enabled for Always ON support, the SSIS configuration on APPSERVER01 has been updated to point to the catalogue database via the database cluster listener.

Via SSMS on the APPSERVER01 when connecting to Integration Services it works fine, when connecting form any other host the following error is received:
“The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)”

Does SISS support this type of deployment? If so what am I missing?


texts:
While trying to install Mariadb galera cluster through yum in my RHEL server I get the following error:

[root@Arrac1 MongoDB]# yum install MariaDB-Galera-server
MariaDB-client galera Loaded plugins: product-id, refresh-packagekit,
security, subscription-manager This system is not registered to Red
Hat Subscription Management. You can use subscription-manager to
register. Setting up Install Process Resolving Dependencies
--> Running transaction check
---> Package MariaDB-Galera-server.x86_64 0:10.0.21-1.el6 will be installed
---> Package MariaDB-client.x86_64 0:10.0.21-1.el6 will be installed
---> Package galera.x86_64 0:25.3.9-1.rhel6.el6 will be installed
--> Processing Dependency: openssl for package: galera-25.3.9-1.rhel6.el6.x86_64
--> Finished Dependency Resolution Error: Package: galera-25.3.9-1.rhel6.el6.x86_64 (mariadb)
       Requires: openssl  You could try using --skip-broken to work around the problem

** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows: authd-1.4.3-30.el6.x86_64 has missing requires of openssl
unixODBC-2.2.14-12.el6_3.i686 has missing requires of libltdl.so.7
unixODBC-2.2.14-12.el6_3.i686 has missing requires of libreadline.so.6

But I have Openssl installed in my host:
[root@Arrac1 MongoDB]# openssl version
OpenSSL 1.0.2d 9 Jul 2015
Now how should I proceed?


texts: Transactional Replication and distribution database size

texts:
I have a transactional replication set up with initialize from backup.
In the secondary database server,i have a drive(d:) for data files alone.
I have noticed that this drive is getting filled and i see that distribution database is like 250 GB now.
What are steps to take to take care of this space issue.?
I know that adding additional disk space is one solution.But i have to keep the replication running till the new purchase order is done and the space is added.


texts: Bulk edit tables and columns descriptions

texts:
In a question on StackOverflow I found this query, that displays table and column description for all the tables in current database:

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

This helps me a lot because I immediately see all the tables and columns that have no description.
Now, it would be really cool to be able to directly edit these descriptions.
Is there a software that allows bulk editing of descriptions for tables and/or columns?


texts: MySQL: why provide size of a datatype in table definition?

texts:
Below is an example table definition:

CREATE TABLE IF NOT EXISTS `mytable` (
      `id` MEDIUMINT(5) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) DEFAULT NULL DEFAULT '',
      `content` text NOT NULL DEFAULT '',
      `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00',
      `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00',
      `published` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

For example: as per MySQL datatype tutorial  MEDIUMINT has 3 Bytes storage and can contains value from -8388608 to 8388607.

Then why should write Mediumint(5), tinyint(1), int(11) etc... in
table definition?
What is the impact of defining size of datatype in
table definition?
Is there any affect of defining size on memory acquisition?
If we don't define size of datatype, it automatically define the size. So, isn't it better to not define the size?

My question is not limited to Mediumint and int datatype. It is for all numeric datatypes which MySQL support.Please provide your suggestions as per the concern I raised.


texts:
I have an article table where I want the slug to be unique.

CREATE TABLE article (
   title char(50) NOT NULL,
   slug  char(50) NOT NULL
);

When the user enters a title e.g. News on Apple, I want to check the database to see if a corresponding slug exists e.g. news-on-apple. If it does, I'll suffix a numerical value until I find a unique one e.g. news-on-apple-1. Can that be achieved with a recursive CTE query instead of doing recursion in my ORM. Is there a good ballpark number where I should stop recursing and error out. I can imagine people using the same title a 1000 times which would result in 1000 queries just to create 1 article.

It's possible that my understanding of recursive CTE is incorrect and there's no better way to find a unique slug. Please suggest any alternatives.


texts: What are the penalties of using many (thousands) of column families or keyspaces in Cassandra?

texts:
I am in the process of evaluating the best design for our Cassandra installation.

There is not so much information out there on the Internet about using the first two levels of access that Cassandra provides—keyspaces and column families.

I am wondering if and what the penalties will be if you choose to create an extensive amount of keyspaces or column families (>10.000).

An old blog post somewhere suggested that Cassandra reserves memory for each column family. The article was about the 0.6 version and the current version is 1.0. Is this still the case and a real problem?

What are the penalties of using many thousands of column families or keyspaces in Cassandra?


texts:
I´m currently developing a vacationplaner and I´m facing some problems during the database design stage.

The Requirements

The User has a specified amount of Vacation Days. Therefore I have to keep track of how much days he has left (tbl vacationUser & userAccount).

The User can create a request, which needs to be confirmed by another user that is specified as Admin (tbl group). While the Request is unconfirmed, the unconfirmed days are stored (to prevent the user to make too much requests) in the tbl userAccount. After a user from group Admin has confirmed that, the unconfirmed day´s are subtracted from daysLeft.

In addition to the specified group of the User (Admin/ User) EACH User is part of a Department. Each Department has a MinStock, if that is reached, no user of this department can take vacation on these days.

For EACH department exists a User that is responsible for that department, and ONE user who is the "deputy" of that responsible user. If the responsible User HAS vacation, the deputy CANNOT take vacation on these days.

So that is the short version of what I´m trying to do.

The Problem

Database Design

This database design was made with use of the junction tableconcept. The data are mostly normalized and summarized in one table users.

My thoughts on this Design

I thought it would enable me a good way to get all important data, by accessing the Userstable. If a User is a Responsible or Deputy of a department, there would be an ID otherwise it would be null.

Here is my Question - a bit hidden under the "giving you information part"

As I stared to think about this design, i don´t liked that I have two tables that just have an ID responsible & deputy table.
The responsible & deputy of a department are on the one hand Departmentrelated information, but on the other hand userrelated information. By using the junction table users this information are connected, but is it the right way I did that? Or should responsible & deputy just be related to the Department table?

Is it better to connect the userAccount & vacationGroupdirectly with the vacationUser table by setting the userID in those tables and add an FK?
The same for the department and the  `responsible & deputy (my thought´s on this are mentioned above).

I would wish to discuss this design and would like to know if I will run into any problems here.

Any help is welcomed!

If you need some further Information, leave a comment!

Edit:
Here is the entire SQL code, to play with the database SQL

EDIT 2:

I´ve recognized that the current design does not work, because I will never be able to associate a User with a userAccount, because there must an existing  UserAccount, before I can fill the table Users.
So probably my initial thoughts were correct, that I have to connect the userAccount directly with the vacationUser table via the userID.

But when I do this, from the logical part I should also connect the group directly with the user. I would have to add the Column groupID to the user´s table to associated both. Is that correct?

Does it even make sense to use the table users?

Here is a visualization of some of my Edit 2 thoughts, combined with some Ideas from my Initial Concept.
With this design I´ll probably not have the troubles about the Useraccount (EDIT 2), but will keep the easy accessibility to verify if a User is responsible or deputyof a Department. But I´m still unsure if I shouldn´t do it completely the way as I mentioned in EDIT 2. What do you guys think?

SQL 2

I hope someone will still help/ answer me, even if this Question is going to be bigger and bigger. Therefore I will stop here and wait for your ideas :)


texts: Cannot start SQL SERVER SERVICE in Windows Server 2000

texts:
This morning this problem occurs when my co-worker says they cannot connect to database.

I've checked the server and found that the SQL SERVER SERVICE is currently stopped. The problem is I cannot start it manually I've also tried to start the service in services.msc and there's still no luck.

We are using SQL SERVER 2000 ENTERPRISE EDITION
Here the screencaps:

Heres the what it says in the Event Logs

SQL Server is aborting. Fatal exception c0000005 caught.


texts: SELECT RELEASE_LOCK causing "Mysql has gone away"

texts:
During a long running process (90m+), our code calls SELECT RELEASE_LOCK('..');. However it always gives a "Mysql has gone away" error when we do this.

We never got to see the return value of the RELEASE_LOCK; the query didn't execute. We lost connection before the result was found.

We have never encountered this problem before. It's after an upgrade of Magento 1.12 to 1.13

We have tried a number of things including increasing various timeouts but to no avail. What might you suggest is a good debugging strategy or something that may be causing this issue?


texts: Full database backup using xtrabackup stream

texts:
I'm new to Percona XtraBackup. I've been trying to perform a full backup stream from my local ( test server around 600GB ) to remote server.

I just have some questions and I need guides, and I think this is the best place.

I have this command, which I executed in my local

innobackupex --user=user --password=password --stream=tar /which/directory/ | pigz | ssh user@10.11.12.13 "cat - > /mybackup/backup.tar.gz"

My questions are :

My log scan is not changing / increasing

>> log scanned up to (270477048535)
>> log scanned up to (270477048535)
>> log scanned up to (270477048535)
>> log scanned up to (270477048535)
>> log scanned up to (270477048535)

I've read a comment before and someone says log scan will not increase if no one is using the database. ( Yes, no one is using the database )
It's been running for a while.

I've tried to use xtrabackup to a local test server with around 1.7TB and finished in just a few hours. Is this because I'm using stream that's why it is slow?

What is the purpose of "/which/directory/" in my command? Is it going to store the file in /which/directory/ first and then transfer to my remote server ? Why do I have to specify a directory?
No created file on my local server /which/directory/ and to my remote
server /mybackup/.

Am I doing something wrong ?

Is there a much easier way to perform this?

My only goal is to backup my local database to a remote server, I'm doing this stream because I don't have enough disk space to store my backup locally.

I'm using MariaDB 5.5 and Percona XtraBackup 2.2


texts: How to list all users that have grants over a package

texts:
I'm looking for some query to list all users that have grants over a package.

For example, user Schema_A has been granted to execute package: B.MyPackage.

I'm querying views or tables like: role_tab_privs, role_sys_privs,sys.dba_sys_privs, dba_role_privs... but I can't find what I'm looking.


texts: While upgrading mongodb from 3.2 to 4.4 replica set. Do I need to download and replace the binaries of all the version from 3.2 to 4.4 all at once and then restart the mongod at the end or I have to restart the mongod after every version download and restart the server, and then download 3.4 and replace and restart, repeat the process till end?


texts: alter table which is under the load (sql server)

texts:
we have table that being used for sending sms and other notification for confirmation as second factor.

Some changes in business logic requires us to alter table to make some columns NOT NULL.

Our stumbling-stone is that table should be accessed during 24/7, application writes to and reads from it pretty frequently and table has tens of millions rows. And when command like

alter table NOTIFICATION_TABLE
alter column C1 int null

starting to execute it obviously tries to lock table and seemingly cannot do it because table is being used by other requests. That situation end up with that alter command hangs out and nothing happened during 5 minutes. After that timeout we stop it because do not want occasionally crush our system.

Any thoughts about how we can implement this modification without stopping  our system?


texts:
short version: what options do i have to move a table that has blob data over to a new filegroup, without blowing out the txlog?

details:
we have an extremely over-allocated filegroup ("DA1"). the plan is: move all the objects to a new (right-sized) filegroup("DA2"), shrink/delete DA1 to reclaim all that disk space*. Easy, right? So, I already moved most all the objects to DA2, most all were pretty small. This last table has blob data, it's about 250GB. prod is in FULL recovery. What we've tried in dev: 1) tried a shrink, took over 11 hours before we killed it. 2) i tried SELECT INTO a new table on DA2 so I could delete&rename, however, the log grew too large and consumed the log drive (constraints here too :(  I do have large maintenance windows on the weekends, but cannot change the recovery model. Any other ideas I can test out?

*we're talking about 2TB to reclaim, and thus has created problems/pressure on restoring this db to lower environments because it's just_too_big.


texts:
Here's the environment:

Linux(x64): 16.04
MongoDB: 3.2.15

Error:

root@system-test:~# mongo
MongoDB shell version: 3.2.15
connecting to: test
2017-07-25T07:31:28.528+0000 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: errno:111 Connection refused
2017-07-25T07:31:28.530+0000 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:229:14
@(connect):1:6

exception: connect failed
root@system-test:~#

When I tried to see the status I get this:

root@system-test:~# sudo service mongodb status
● mongodb.service - High-performance, schema-free document-oriented database
   Loaded: loaded (/etc/systemd/system/mongodb.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2017-07-25 07:45:32 UTC; 1min 8s ago
  Process: 1721 ExecStart=/usr/bin/mongod --quiet --config /etc/mongod.conf (code=exited, status=14)
 Main PID: 1721 (code=exited, status=14)

Jul 25 07:45:32 system-test systemd[1]: Started High-performance, schema-free document-oriented database.
Jul 25 07:45:32 system-test systemd[1]: mongodb.service: Main process exited, code=exited, status=14/n/a
Jul 25 07:45:32 system-test systemd[1]: mongodb.service: Unit entered failed state.
Jul 25 07:45:32 system-test systemd[1]: mongodb.service: Failed with result 'exit-code'.

How it happened:

I tried to stop MongoDB and did the following

sudo service mongo stop [This didn't work].

Then i tried kill - 2 <PID> [Seem didn't worked too]

But after this command #mongo stopped working and getting the above error.

Please help guys


texts:
I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code.

BEGIN TRANSACTION
if (@isRemote = 1)
    BEGIN
       CREATE SYNONYM SY_SUBJECTS FOR [MT00011].[DBO].[SUBJECTS];
    END
ELSE
    BEGIN
       CREATE SYNONYM SY_SUBJECTS FOR [SUBJECTS];
    END
-- do some complex work on SY_SUBJECTS
DROP SYNONYM SY_SUBJECTS
COMMIT

This code gets called by multiple requests simultaneously. I am concerned that it will cause problems if this code gets called at the same time. It executes 99% of the time on a single SQL database, and the "remote" case is run after hours.

Is there a problem here?


texts:
I'm young in the IT world, and I'm learning daily. I just started working with SQL databases, and I have lots of basic questions.

One in particular, What is the function of rebuilding indexes? I have yet to find a good answer through Google.

Any feedback is appreciated.


texts: What minimum permissions and/or roles must a SQL Server login possess to be able to grant database db_datareader role to other logins?

texts: To delegate the ability for a security admin to add new SQL logins to the db_datareader role within a database, what are the least permissions and/or roles the security admin must possess?


texts:
Can we get below information?

SQL Server 2008 R2 write history of Last one week?
I want to know the Disk utilization per/hour for last one week.

We want know reason for strange IO increase in one of our PROD servers.

Please let me know if someone can help me on this. Thanks.


texts:
I am trying to summarize Bridge results for each pair (north/south pair and east/west pair).

Match points for pair north/south is MPNS, for pair east/west is MPEW. Is there a way to summarize all match points for pair from MPNS and MPEW given the following data?

Boards results for sample are:
board ns ew score MPNS MPEW
1     1  2  110     0   4
1     3  4  120     2   2
1     5  6  150     4   0
2     1  4  150     4   0
2     2  6  120     2   2
2     3  5  100     0   4
3     1  6  100     1   3
3     4  2  150     4   0
3     5  3  100     1   3

and how get result for pairs:

pair sum
  1     5
  2     6
  3     5
  4     6
  5     9
  6     5


texts: Upgrade SQL Server database Using Compatibility Level

texts:
I've made a backup of a SQL Server database running on SQL Server 2008.
I've restored that backup into a SQL Server 2012 instance.
I've set the compatibility level on the restored instance to SQL Server 2012 (110).

What does this do to the database?

Does it, for example,

cause stored procedures to be examined for incompatibilities
cause any updates to all of the data pages in the database
Cause updates to to datapages as the are accessed in the future
Cause errors in the future if stored procedures or views are executed that use incompatible syntax

Where in books on line, or else where, can I find more detail?


texts: How to get more physical details when executing an SQL query?

texts:
I want to get some physical features about a SQL query execution with PostgreSQL, like

Read/write page accesses
I/O operations
Response time
Sequential I/O throughput rate
Deadlocks
Pages scanned Queries ratio
Hit ratio, Pages read, Logging, Ratio of using indexes Rows selected , Average sort time, Number of locks held, Number of sorts Throughput..

and more, depends on what's available


texts:
Having a hard time explaining my scenario and what I am trying to accomplish. I have two tables like so, I have reduced the values for my scenarios

Table1
+----------------------+
| ID |   Time          |
+----------------------+
| 1 | 2018-05-15 09:00 |
| 1 | 2018-05-15 09:10 |
| 1 | 2018-05-15 09:20 |
| 2 | 2018-05-15 09:30 |
| 2 | 2018-05-15 09:40 |
+----------------------+
`
`
Table2
+----------------------+
| ID |   Time          |
+----------------------+
| 1 | 2018-05-15 09:21 |
| 1 | 2018-05-15 09:31 |
| 2 | 2018-05-15 09:41 |
+----------------------+

I want to join these on the IDs but match on the latest result. By this I want the latest match ID from Table1 to Join with Latest match of ID with Table 2. So if I have a match of ID from both tables, I want the latest date to be displayed in the result and specify its coming from the other table. Once its used, the next match will get the latest unused date. Not familiar with looping through the results to handle this situation

The output I want to produce from the above query (Third column it to represent where that row comes from):

+----------------------+--------+
| ID |   Time          | Table  |
+----------------------+--------+
| 1 | 2018-05-15 09:00 | Table1 |
| 1 | 2018-05-15 09:21 | Table2 |
| 1 | 2018-05-15 09:31 | Table2 |
| 2 | 2018-05-15 09:30 | Table1 |
| 2 | 2018-05-15 09:41 | Table2 |
+----------------------+--------+


texts: Partitioned Tables and Indexes - what are the downsides?

texts:
When talking about Partitioned Tables and Indexes for tables with less than 100 partitions,

no nonaligned indexes:

with that I mean:

    Nonaligned index

  An index partitioned independently from its corresponding table.

  That is, the index has a different partition scheme or is placed on a
  separate filegroup from the base table.

  Designing an nonaligned partitioned index can be useful in the
  following cases:

  The base table has not been partitioned.

  The index key is unique and it does not contain the partitioning
  column of the table.

  You want the base table to participate in collocated joins with more
  tables using different join columns

Are there other performance drawbacks than:

1 - slowing down some DBCC commands

2 - Queries that use operators such as TOP or MAX/MIN on columns other than the partitioning column may experience reduced performance with partitioning because all partitions must be evaluated.

3 -

  Queries that use partition elimination could have comparable or
  improved performance with larger number of partitions. Queries that do
  not use partition elimination could take longer to execute as the
  number of partitions increases.


texts: show extended index equivalent for MySQL 5.7

texts:
If you look at this documentation for show index in version 8.0, it says

  SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [WHERE expr]

  The optional EXTENDED keyword causes the output to include information about hidden indexes that MySQL uses internally and are not accessible by users.

Without the optional EXTENDED keyword, only the normal index parts are visible, the primary key at the end is not visible (innodb). I want to see the extended version in 5.7. How do I see it?

I tried checking in information_schema tables like TABLE_CONSTRAINTS, KEY_COLUMN_USAGE etc. The extended info is also not there.


texts: Trying to map postal codes to their respective cities, but running into some issues

texts:
The Problem
I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now:

Now, let's take Tuskegee University as an example to illustrate my problem. Here is Tuskegee University's address:

1200 W Montgomery Rd
Tuskegee, AL 36088

For the city database, I grabbed postal code data from Geonames, and I cross referenced this data with USPS. In both dumps, for '36088' I get Tuskegee Institute. So, when I do the following:
SELECT a1.name,a1.address,b1.name,b1.postal_code
   FROM university a1
   JOIN city b1 ON a1.city_id = b1.city_id
   WHERE a1.name = 'Tuskegee University';

I get back:

"Tuskegee University";"Kresge Center, 3rd Floor";"Tuskegee Institute";"36088"

Thus, making my address:

Kresge Center, 3rd Floor
Tuskegee Institute, AL 36088

The Question
There are many problems here (e.g., the address: I downloaded university data from IPEDS and obviously their address does not match Google Places). However, what I want to focus on is the issue with my city table. I feel like the schema here may not be the best approach to the problem, but I'm not really sure what the best approach might be. Do I create separate city and postal code tables? But, in the end, how do I associate 36088 with Tuskegee, AL? Is there a better source here that I have just not found?
A Possible Solution (with problems)
Say I switched up the DB schema, such that it looked like this:

Then, I could have the postal code and city be independent of each other. Thus, the university table points to Tuskegee for city_id, and the 36088 for postal code. However, I now have an issue. If someone types in a city, I cannot tell them cities within a certain radius. I could get the lat/long for cities and create a geog column in the city table as well, but that seems a bad solution. I could associate cities with postal codes (i.e., city_id column in postal_codes table), but then I am faced with the same problem of mapping 36088 to Tuskegee, or having 'Tuskegee Institute'  in my city table (which I obviously don't want).
An Interesting Side Note
CityGrid seems to have completely ignored the problem I am facing, going with Tuskegee Inst for the city.
As well, a USPS lookup for this zip code tells me the following:

The preferred city in 36088 is...
TUSKEGEE INSTITUTE AL
Other acceptable cities for 36088 are
TUSKEGEE INST AL

However, the mailing address according to Tuskegee University's website says the following:

ATTN: (Recipient's name)
Tuskegee University (Dept. or Office name)
(Building name, Room #)
1200 W. Montgomery Rd.
Tuskegee, AL 36088

So, it seems that Tuskegee should also be an acceptable name for USPS, but it's not listed as such.


texts: Delete Only Log Backup History in SQL Server

texts:
Is it possible to run something like dbo.sp_delete_backuphistory to only delete transaction log backups without rolling your own scripts? Using Ola's maintenance scripts, we do 5 minute log backups on all our critical databases and nightly differential and weekly full backups. This means we get about 8500 transaction log backups in a month, but only 30ish differentials and 4-5 full backups.

We keep the full and differentials for much longer than we do log backups, so we'd like to keep the backup history for those, but we'd like to reduce the size of our backup tables (particularly backupset) as it's slowing down our monitoring tool.


texts: Why are the checkboxes absent under the Notify Operator Task?

texts:
I have several maintenance plans configured on a SQL 2008 R2 (10.50.6529).  When I remotely connect to it with SSMS 2008 R2 or SSMS 2016 to edit the notify operator task there is nothing listed, just blank.

If I RDP to the server and open SSMS locally, the expected checkboxes appear.

Why is this so?  Is this a just a one-off bug?

I thought, for the most part, that it didn't matter which version of SSMS you use.


texts: VMWare Memory Sizing and SQL Server 2014

texts:
We have a production server that dumps it's Page Life Expectancy many times a day (climbs to 2500s and falls back to near 0s)... After watching some training videos about virtualization for SQL Server, I was pointed towards several Perfmon counters to look at:

Memory Limit in MB
Memory Ballooned in MB
Memory Reservation in MB
Memory Swapped in MB

Ballooned, Reserved, and Swapped are all at 0 but Memory Limit was set to nearly 3tb (perhaps a default setting like SQL's Max Memory setting...)

I viewed these counters during an event when the PLE dropped to 0 and none of the counters changed. According to Spotlight, Buffer Cache and Procedure Cache are remaining at high % utilization and no stats seemed to really budge except the PLE itself.

To me, this points to not enough RAM allocated to the machine to the point that many of the queries are forcing the server to look to disk to get results.

What steps do I need to take to prove or refute this?

Server Config:
- Windows 2008 r2 VM
- 4 cores
- 12GB RAM
- main DB is ~150GB


texts: Is it possible to use Hibernate for OLAP-server?

texts:
I have a PostgreSQL database with Java Hibernate. I need a XMLA datasource for a pivot grid client component.

Is it possible to use Hibernate for a OLAP-server?
Which OLAP-server does support Hibernate (POJOs)?


texts: Calculations over UNION, merge query repetitive code

texts:
I am struggling with this query a lot mainly because of the repetition which possibly could be reduced - hopefully.

The general idea is to display sums of each table for a given month and display it as a pivot table.

Current code is in my opinion very messy and repetitive but the main thing is it doesn't allow me to make the calculations, due the UNION joining.

For each month as following ( I assume this has to be a derivative table that takes values of the other and calculates this formula for each month ):

Calculation Formula = (
    employees_salaries +
    employees_salaries_insurence_costs +
    employees_agreement +
    employees_agreement_insurence_cost +
    employees_extras +
    employees_to_hand +
    employees_delegations ) - employees_deductions

Current query is working displays everything properly but the questions and desired output :

Question : Is there any possible query minification
         - Possible query structure to enable the calculation formula

Desired output : Current layout is what needs to stay

I am trying since few days but I am unable to accomplish my desired result, any tips will be really helpful.

Output

+------------------------------------+---------------------+------+-------+-------+-----+------+-------+----------+-----------+---------+----------+----------+
|         employees_salaries         |       January       | Febr | March | April | May | June | July  |  August  | September | October | November | December |
+------------------------------------+---------------------+------+-------+-------+-----+------+-------+----------+-----------+---------+----------+----------+
| employees_salaries                 | 0                   |    0 |     0 |     0 |   0 |    0 |     0 | 15826.12 |         0 |       0 |        0 |        0 |
| Calculations                       | Calculation Formula |      |       |       |     |      |       |          |           |         |          |          |
| employees_salaries_insurence_costs | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |  5044.44 |         0 |       0 |        0 |        0 |
| employees_agreement                | 0                   |    0 |     0 |     0 |   0 |    0 |   250 |        0 |         0 |       0 |        0 |     3550 |
| employees_agreement_insurence_cost | 0                   |    0 |     0 |     0 |   0 |    0 | 71.05 |        0 |         0 |       0 |        0 |   178.65 |
| employees_extras                   | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |        0 |         0 |    1.00 |        0 |        0 |
| employees_deductions               | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |    45.60 |         0 |    1.00 |        0 |        0 |
| employees_to_hand                  | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |        0 |         0 |  111.00 |   555.00 |        0 |
| employees_delegations              | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |        0 |      2520 |     540 |        0 |        0 |
+------------------------------------+---------------------+------+-------+-------+-----+------+-------+----------+-----------+---------+----------+----------+

MCVE Example + Schema : https://www.db-fiddle.com/f/8RvfD3uA4cSSVgmhoP2co6/9


texts:
We have a MS SQL 2008 R2 service running on a Windows 7 computer. I've developed a webapplication that runs on a Digital Ocean droplet. This droplet makes a connection with this computer through a subdomain "sql.client-domain.ext" (fictive domain name).
There is a firewall which only allows connections from the droplet to the computer where SQL service is running.
I want an encrypted connection between the droplet and the computer. I've purchased a certificate.
On my local test machine I've installed the PFX certificate in the personal folder (in MMC). I've added the 'NETWORK SERVICE' user with READ permissions for the certificate key.
In the SQL management console, I can't see the certificate anywhere. I tried with service restarts, nothing.
I've also tried to change the hostname to the subdomain and added the subdomain in the hosts file under the Windows directory.
EXEC sp_addserver 'sql.client-domain.ext', local;

Host file: 127.0.0.1 sql.client-domain.ext

I can connect in the SQL client program to sql.client-domain.ext but I can't pick the certificate.
I only need the certificate for TCP connections, not local connections.
Any hints or experienced advice?


texts:
I have a Microsoft SQL Server 2012 database table with around 7 million crowd-sourced records, primarily containing a string name value with some related details. For nearly every record it seems there are a dozen similar typo records and I am trying to do some fuzzy matching to identify record groups such as "Apple", "Aple", "Apples", "Spple", etc. These names can also contain multiple words with spaces between them.

I've come up with a solution of using an edit-distance scalar function that returns number of keystrokes required for transformation from string1 to string2 and using that function to join the table to itself. As you can imagine, this doesn't perform that well since its having to execute the function millions of times to evaluate a join.

So I put that in a cursor so at least only one string1 is being evaluated at a time, this at least gets results coming out but after letting it run for weeks it has only made it through evaluating 150,000 records. With 7 million to evaluate, I don't think I have the kind of time my method is going to take.

I put full text indexes on the string names, but couldn't really find a way to use the full text predicates when I didn't have a static value I was searching.

Any ideas how I could do something like the following in a way that wouldn't take months to run?

  SELECT t1.name, t2.name
  FROM names AS t1
  INNER JOIN names AS t2
       ON EditDistance(t1.name,t2.name) = 1
       AND t1.id != t2.id

I've tried soundex, but since the names can contain spaces and multiple words per value I get too many false positives to use it reliably.


texts: MySQL Insert into two tables using new IDs

texts:
I'm trying to select data already in the database and insert it into two tables using the generated ID from table one.

table1 contains configuration options, table2 contains pricing data. Table2 uses the auto ID from table1.

I have all these products in Table1 and I have to use the same config options in another group in that table, but WHMCS doesn't have copy for config options.

I was thinking of going into the table and running a select query like so:

INSERT INTO tblproductconfigoptionssub
    (
        id
        , configid
        , optionname
        , sortorder
        , hidden
    )
SELECT
    NULL
    , 76
    , optionname
    , sortorder
    , hidden
FROM tblproductconfigoptionssub
WHERE configid = 41

That works for data from the first table, by selecting all records from config group id 41 and reinsert them, except now they are linked to config group 76.

Table2 contains the pricing data and it has a column called relid that is linked to the ID generated from tblproductconfigoptionssub above.

Table2 already has pricing for the items in table1 under their original IDs. The records from Table2 would have to be copied, but with the new IDs generated by the query above.

How do I modify the statement above to insert the new data into Table2, along with the prices?

I don't really want to do a PHP script for this.  However, I think I need to make a MySQL function to select all items from config group 41 and then store values in variables. Then insert into two tables.

Is there a way to do this with INSERT statements?


texts:
I have a remote linked server that got moved to a different IP address.
After dropping the server with sp_dropserver and add it with new URL with sp_addlinkedservers, will I have to add everything back again (username, password, etc.)?
Does sp_dropserver destroy the associated data ? Or that data is linked to the ID and kept? The server and respective SW is the same, only IP changes.


texts: Get a column from the latest row by another column

texts:
Take a look at this sqlfiddle. I have a table that contains history of records for another table(irrelevant, foreign key included). Each history item will contain a status and time fields. I want to write a select statement that would give me a list of unique(by foreign key) histories with the latest status.

From other searches I think what I need is

SELECT MAX(created) as created, originalMsgId
from ReplayHistory
group by originalMsgId

created             Id
------------------------
2018-06-22 19:18:43 11
2018-06-22 09:28:43 12

but it doesn't return the column I need; status.

The query I am using is

SELECT MAX(created) as created, originalMsgId, status
from ReplayHistory
group by originalMsgId, status

which returns me

created              Id  status
----------------------------------
2018-06-22 19:18:43  11  Finished
2018-06-22 19:17:43  11  InProgress
2018-06-22 09:28:43  12  InProgress
2018-06-22 19:16:43  11  NotStarted
2018-06-22 09:27:43  12  NotStarted

I know that this is happening because of group by status, but if I need it in select then I have to keep it in group by.


texts: Is it possible to move mongodb indexes to another file system?

texts:
Is it possible to separate MongoDB files location so the indexes are on another file system?

In the example below, I have database with 1.5TB of data + indexes of 330GB.
I have a very fast storage that is not large enough to hold the entire data.
I assume that if I move there the indexes I can get better performance.

Is there a way to do so?

# ls -l /data/mongodb --block-size=G
total 1962G
-rw-------. 1 root root    1G May 27 20:17 collection-0--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 collection-2--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 collection-4--8049282980716619517.wt
-rw-------. 1 root root 1627G May 27 20:17 collection-7--8049282980716619517.wt
drwx------. 2 root root    1G May 27 20:14 diagnostic.data
-rw-------. 1 root root    1G May 27 20:17 index-1--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 index-3--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 index-5--8049282980716619517.wt
-rw-------. 1 root root    1G May 27 20:17 index-6--8049282980716619517.wt
-rw-------. 1 root root   15G May 27 20:14 index-8--8049282980716619517.wt
-rw-------. 1 root root  320G May 27 20:17 index-9--8049282980716619517.wt
drwx------. 2 root root    1G May 27 13:54 journal
-rw-------. 1 root root    1G May 27 20:17 _mdb_catalog.wt
-rw-------. 1 root root    0G May 27 20:17 mongod.lock
-rw-------. 1 root root    1G May 27 20:17 sizeStorer.wt
-rw-------. 1 root root    1G May 23 12:50 storage.bson
-rw-------. 1 root root    1G May 23 12:50 WiredTiger
-rw-------. 1 root root    1G May 27 20:17 WiredTigerLAS.wt
-rw-------. 1 root root    1G May 23 12:50 WiredTiger.lock
-rw-------. 1 root root    1G May 27 20:17 WiredTiger.turtle
-rw-------. 1 root root    1G May 27 20:17 WiredTiger.wt
#


texts: How do SSIS connection strings send passwords?

texts:
I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(username)'.". errors and the SSIS package fails to run.
The connection string of the SSIS package indicates the username (which is correct for the target DB), but I do not see how it passes the password to connect to that DB and pull that data. So how does SSIS send passwords if not in the connection string?
The Package Protection Level is set to EncryptAllWithPassword

Windows Authorization only option to run.


texts:
With a MySQL database, is there a way of logging and/or backing up incremental changesets?

We have a number of large databases (100-200 GB), for which we take daily backups using mysqldump. These backups are a snapshot of the entire database, which can be restored as whole. However, taking/restoring from these backups is not a quick process.

As such, I'd like to be able to take backups of incremental changes - i.e. a backup of what's changed in a given day. Then, assuming a database was in a state consistent with a previous snapshot, we could restore the set of incremental changes to get it to the same/current state.

To some extent, this could be done by operating on mysqldump files - i.e. comparing today's snapshot dump with the previous day's snapshot dump to create a .sql backup of commands to run to convert from the previous snapshot state to today's snapshot state. However, I'd rather not write this myself if there's already a better way of doing this.

Also, I'm conscious that, to some degree this is analogous to MySQL master-slave replication, with the exceptions that the changesets are grouped by time period (i.e. a 24hr window) and not automatically read into another database / server. Furthermore, the binary logs are for the entire database server, not for individual databases.

Additional Comments:

With some further research on this, I've also come across the incremental backup functions in MySQL Enterprise, but we're not using the enterprise version.


texts:
SSMS 2012 does not work with VSS 6. We installed modern source control, TFS 2010 server.

Then I Installed Team Foundation Server MSSCCI Provider 2010.
However the only choice for Source control  plug-in in SSMS 2012 is still  VSS6.
My VS2010 works just fine, I could connect to TFS in a team explorer.

How to add TFS as a second source code control plugin in SSMS 2012?


texts:
I want to monitor only sp's.

Does SQL Server Profiler listen to all events and then filters the desired data (e.g. sp's)?

Or rather the sql emits  only Profiler pre-configured events ?


texts: How is data lake and polyglot persistence different?

texts: I just came across a new technology called polyglot persistence. However, it sounds very much like a data lake. So, how are these two things different?


texts:
Consider the following tables:

=> table1
| aid |
| --- |
| a1  |
| a1  |
| a3  |
| a2  |
| ... |

=> table2
| aid | count | va    | vb    |
| --- | ----- | ----- | ----- |
| a1  | c1    | va1   | vb1   |
| a2  | c2    | va2   | vb2   |
| ... | ...   | ...   | ...   |

Basically, table2 aggregates data from several other tables, including from table1.
One way to update table2 data in MySQL is:

INSERT INTO `table2`(`aid`, `count`)
SELECT `src`.`aid`, `src`.`count` FROM
(
    SELECT `aid`, COUNT(`aid`) AS `count`
    FROM `table1`
    GROUP BY `aid`
) AS `src`
ON DUPLICATE KEY UPDATE `table2`.`count` = `src`.`count`

Although this code works, it seems to be wordy (going around in circle).
At first, a better approach would be:

INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid` AS `src`
ON DUPLICATE KEY UPDATE `table2`.`count` = `src`.`count`

Unfortunately, due some unknown reason, this more direct approach doesn't work.
Why?


texts:
I was trying to insert around 70K records to mysql database through phpmyadmin and was shocked of how slow it is. It took around 5 hours to complete!

Please find below the details of the table/database:

I only have access to PHPMyAdmin and FTP. So I cannot use SSH to
upload the data unfortunately.
I was only uploading to one table with 8 columns. No foreign keys or computed columns of any kind. Only one auto-increment for the primary key which is not part of the query
I checked the status tab and no locks or running queries on that table to cause slowliness.
Some of the values are in Arabic (UTF8)

I tried the following methods to upload the data:

Separate Insert Queries through SQL tab: Extremely slow and takes 5
hours. It gave 500 error but it continued to upload the data and didn't stop.
Joined insert queries through SQL tab [e.g. (record1),(record2),(record3)]: takes long time and suprisingly does not add anything to the database. No errors as well.
Import Tab: Execution time errors
Through PHP: Memory was exceeded and do not have access to increase the memory as the server is not under my control

The insert query is simple

INSERT INTO mytable (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col7`,`col8`)values(1,100,10,'val1','val2','val3','val4','val5');

I would like to upload the data as fast as possible as I only have 5 minutes to provide that data to the customers. Any way to do a bulk insert to be as fast as possible. Alternatives to phpmyadmin that can be added through FTP are welcome as well.


texts: SELECT same columns of Foreign table multiple time

texts:
I have tables as follows:

ORG

    id  Name     media_budget  p_contact  s_contact
------  -------  ------------  ---------  -----------
     1  FIFA          4654623          1            3
     2  FIFA_AP          1234          2            3
     3  ICC            203254          3            2

USERS

    id  fname    lname   contact  email
------  -------  ------  -------  -----------------
     1  JHONE    MICHEL   124552  email@gmail.com
     2  William  Martin   254623  gmail@email.com
     3  Ann      Lee      321546  lee@gmail.com

I want to SELECT user.fname for ORG.p_contact and ORG.s_contact for each row in ORG, following statement works fine with single record in ORG however, when query return multiple rows it throws an error

"Error Code: 1242
Subquery returns more than 1 row"

my statement is:

SELECT o.Name, o.media_budget,
(SELECT u.fname AS primaryName    FROM org o LEFT OUTER JOIN users u ON o.p_contact = u.id) AS PrimaryContact,
(SELECT usr.fname AS SecondaryName  FROM org og LEFT OUTER JOIN users usr ON og.s_contact = usr.id) AS SecondaryContact
 FROM org o;

What should be changed in sub-query to achieve desire result :

    id  Name    media_budget  PrimaryContact      SecondaryContact
------  ------  ------------  --------------      ----------------
     3  ICC           203254          Ann            William
     4  FIFA            2656          JOHN            Ann


texts: Orphan records after delete statement in stored procedure

texts:
I have a database where some of the tables have parent --> child --> grandchild relationships through foreign keys as shown below.

CREATE TABLE Parent
(
     ParentID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    ,SomeValue VARCHAR(100) NOT NULL
);

CREATE TABLE Child
(
     ChildID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    ,ParentID INT UNSIGNED NOT NULL
    ,SomeValue VARCHAR(100) NOT NULL
);

ALTER TABLE Child
ADD CONSTRAINT FK_Child_ParentID
FOREIGN KEY (ParentID) REFERENCES Parent (ParentID)
ON DELETE CASCADE;

CREATE TABLE GrandChild
(
     GrantChildID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    ,ChildID INT UNSIGNED NOT NULL
    ,SomeValue VARCHAR(100) NOT NULL
);

ALTER TABLE GrandChild
ADD CONSTRAINT FK_GrandChild_ChildID
FOREIGN KEY (ChildID) REFERENCES Child (ChildID)
ON DELETE CASCADE;

Some sample data I have tested with is:

INSERT INTO Parent (SomeValue) VALUES ('ParentA'), ('ParentB'), ('ParentC'), ('ParentD');

INSERT INTO Child (ParentID, SomeValue) VALUES (1, 'ChildA'), (1, 'ChildB'), (2, 'ChildC'), (3, 'ChildD'), (3, 'ChildE');

INSERT INTO GrandChild (ChildID, SomeValue) VALUES (1, 'GrandChildA'), (2, 'GrandChildB'), (2, 'GrandChildB'), (3, 'GrandChildD'), (3, 'GrandChildE'), (3, 'GrandChildF');

And the stored procedures I'm using to delete records are:

DELIMITER //
CREATE PROCEDURE DeleteChild(IN Child_ID INT)
BEGIN

    DELETE FROM Child
    WHERE
        ChildID = Child_ID;

END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE DeleteParent(IN Parent_ID INT)
BEGIN

    DELETE FROM Parent
    WHERE
        ParentID = Parent_ID;

END //
DELIMITER ;

After having done some testing I've discovered a data integrity problem where under certain circumstances it's possible for a parent record to be deleted without it's children and grandchildren records also being deleted, even though I have the ON DELETE CASCADE option enabled. I've managed to replicate the issue and the following steps result in it occurring.

Start a long-running statement that inserts a large number of records into the table 'GrandChild'. I've been trying to simulate large insert jobs so I'm running the following statement which takes several minutes if there are around 1 million records:

INSERT INTO GrandChild (ChildID, SomeValue) SELECT ChildID, SomeValue FROM GrandChild;
While the previous statement is running, issue another statement to delete a record from the table 'Child' that has grandchildren records in 'GrandChild'. I've tested this by calling the proc DeleteChild(1). The statement hangs and eventually times out after 50 seconds (the default innodb_lock_wait_timeout value) with an error 1205 after failing to get a lock on 'GrandChild'.
Before the previous statement times out, issue another statement that deletes a record from the table 'Parent' that also has child and grandchild records. I've testing this by calling proc DeleteParent(1).

The ordering looks something like the following:

What happens here is that as soon as the statement in step 2 times out, the statement in step 3 completes but it does so having only deleted the matching records in the table 'Parent' but not any of the record's children or grandchildren, leaving me with orphan records.

Can someone please explain to me why this might be happening?

I'm using MariaDB 10.2.11 with InnoDB and most of the settings I'm using are the defaults. Let me know if any extra information is needed to help debug this.

Thanks!


texts: Create Copy of all rows where foreign key matches specified and then replace foreign key

texts:
I'm new to database administration (this is my first time) and have been tasked with creating a database with functionality that allows users to select a "Model" as a current state which can then be used as a default/baseline for a new Model when editing.

My question would be, how does one copy every single row from multiple tables where the foreign key matches the Model_ID of the selected Model to then be used in a new Model?

Would this require selecting the rows from the tables into a tempdb and then re-inserting into the originaldb, this seems quite resource heavy for such a task. Is there a better solution?

I've attached an img of the database design I have created just in case it is of use. help and advice would be greatly appreciated, Thank You.

Update

After taking the suggestion of Akina, this is my plan on how the rows will be duplicated for user usage (Pseudocode). If there are any glaring errors feel free to correct.

@X int -- the selected existing Model taken as a paremeter
@new_model_Name -- fields to be entered into the model table
@new_model_completionUser -- fields to be entered into the model table
@new_model_dateCreated -- fields to be entered into the model table

insert into model values (new_model_Name, new_model_completionUser, new_model_dateCreated)

insert into table1 (old_data1, old_data2,..., New_Model_ID)
select * from table1 where Model_ID = X

insert into table2 (old_data1, old_data2,..., New_Model_ID)
select * from table2 where Model_ID = X

--etc


texts:
I am unable to use an ODBC 64-bit driver with SSMS 2012 running on a SQL Server 2012 64-bit instance. However, I believe that the 32-bit driver works.

My question is can I use SSMS 2005 32-bit to create a linked server using ODBC on a 64 bit instance of SQL Server 2012? I tried to do this, but when I clicked on OK to create the linked server I got the message:

  Index was outside of the bounds of the array

Is what I am doing completely wrong or is there somehow a way to create this linked server using a 32-bit ODBC connection?


texts:
Background: I am busy with an MSc in GIS. It involves using postgresql 9.2 with the postgis extension as the spatial database and QGIS to view, edit and analyse the data. I know postgis is also capable of editing and analyzing spatial data. However, the end users do not have experience with SQL, therefore the point and click QGIS GUI. Part of my thesis involves a guide on installing Postgres/postgis on a server. The problem is, I have very little experience with databases and servers. Although, I have been able to succesfully install and use postgresql/postgis on my win 7 pc, I'm not sure if it is the same for servers.

*Question:*Is postgresql/postgis installed on a server in the same manner as on a pc? If not, please explain (in as much detail as possile) or point me in the right direction. Also, what configurations has to be done for client pc's to connect to postgresql/postgis on the server?

Thanks in advance.


texts: Postgres does not use the index when sorting

texts:
I'm trying to do a query on a table, it gives me the correct result it is slowl when there is an order by .

explain  analyze  select *
from "articles"
cross join plainto_tsquery(COALESCE('russian', get_current_ts_config()), 'search')
AS "tsquery" where searchable @@ "tsquery" order by "date" desc limit 10 offset 0;

Without order by I get a result in 50ms but with the order by it takes 5 seconds.

I tried to fix it by adding this index

CREATE INDEX articles_desc_index ON articles (date DESC NULLS LAST)

But it does not work

    Limit  (cost=2954.42..2954.44 rows=10 width=1520) (actual time=2999.486..2999.504 rows=10 loops=1)
  ->  Sort  (cost=2954.42..2956.15 rows=692 width=1520) (actual time=2999.484..2999.497 rows=10 loops=1)
        Sort Key: articles.published_at DESC
        Sort Method: top-N heapsort  Memory: 44kB
        ->  Nested Loop  (cost=457.36..2939.47 rows=692 width=1520) (actual time=28.453..2981.421 rows=5577 loops=1)
              ->  Function Scan on tsquery  (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.006 rows=1 loops=1)
              ->  Bitmap Heap Scan on articles  (cost=457.36..2932.54 rows=692 width=1488) (actual time=28.441..2970.125 rows=5577 loops=1)
                    Recheck Cond: (searchable @@ tsquery.tsquery)
                    Heap Blocks: exact=5197
                    ->  Bitmap Index Scan on articles_searchable_index  (cost=0.00..457.19 rows=692 width=0) (actual time=27.755..27.755 rows=5587 loops=1)
                          Index Cond: (searchable @@ tsquery.tsquery)
Planning time: 0.180 ms
Execution time: 2999.576 ms


texts: Get a list of SQL scripts that ran on my server

texts:
The schema change history report tells me when the schema of a DB was last changed.

I would have run an x.sql file on that same DB for those changes to have happened. Does SQL Server log that I ran x.sql on 3/01/2016 at 3:15?


texts: MySQL 8.0.17 on Windows - I forgot my root password, dont need to keep any previous DBs

texts:
I have previously used MySQL for a class about a year ago and not since. I've forgotten the password and I need to use it again.  I don't need to be able to access any previous databases, so I tried stopping the service in command prompt and uninstalling then re-installing MySQL but this still resulted in me getting stuck at the "Accounts and Roles" section of the MySQL Installer where it asks for my root password.

Is there a way I can reset it or start a new root account all together? I've seen tutorials for Ubuntu but I'm not knowledgeable enough yet to translate this to windows.


texts:
Is there a way to link two tables in order?

Table A (Actual):

EX  STATUS  DLV     Actual FEE    Expected Fee
N    ACT    MAIL    1.25
N    ACT    EMAIL   1.25
N    ACT    PICKUP  0
N    NON    MAIL    0
N    NON    EMAIL   1.25
N    NON    PICKUP  0
Y                   0

Table B (Expected):

EX  STATUS  DLV     Expected FEE
N    ACT    MAIL    1.25
N    ACT    EMAIL   0
N    ACT    PICKUP  0
N    NON    MAIL    1.25
N    NON    EMAIL   0
N    NON    PICKUP  0
Y                   0

Table A (Expected Result):

EX  STATUS  DLV     Actual FEE    Expected Fee
N    ACT    MAIL    1.25          1.25
N    ACT    EMAIL   1.25          0
N    ACT    PICKUP  0             0
N    NON    MAIL    0             1.25
N    NON    EMAIL   1.25          0
N    NON    PICKUP  0             0
Y                   0             0

I want to incorporate the follow logic within the join:
A.EX = B.EX (join on first)

but then on the combination of the other 3, since they are unique.

I tried this but of course it didn't work:

FROM
dbo.claims a
left outer join dbo.pricing p on a.EX = p.EX
and a.STATUS = p.STATUS
and a.DLV = p.DLV

I was hoping to link table B to table A to get the expected fee.
If Ex = Y, then it's 0 regardless of status or DLV.  If Ex=N, then I have to depend on the other two columns.

I'm sorry if this is confusing.


texts: Unable to access mysql database remotely

texts:
I ran this command-

[root@dev-img mysql]# mysql -u root -h 192.168.1.181 -e "use mysql;"

and this is the output I got-

ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.65' (using password: NO)

Is there anything wrong with my command? I've searched everywhere and the syntax seems to be as above.


texts:
I'm new at programming with PHP/mySQL and I have got a little problem here.

This is how my code looks like:

SELECT
 COUNT(*) AS Number , Name
FROM
 table
GROUP BY
 Name
ORDER BY
 Number DESC
LIMIT 100

Out of this table:

Oliver
Michael
Oliver
Steven
Michael
Oliver
Oliver

... it generates:

Oliver  | 4
Michael | 2
Steven  | 1

My question: How can I add ranking numbers so that it looks like this:

1 | Oliver  | 4
2 | Michael | 2
3 | Steven  | 1

Thanks for your help!


texts: Estimation of appropriate innodb buffer pool size

texts:
I'm reading this thread How large should be mysql innodb_buffer_pool_size?

And perform the querys suggested by RolandoMySQLDBA and I get 307 RIBPS and 264G. That's mean that I need 307GB of RAM?

Currently I have a innodb_buffer_pool_size value of 2097152000 but the server has 128GB of RAM and according to MySQL Workbench Server Status the InnoDB BufferUsage is at 100%

This is wrong rigth?
I mean, I must to increase the innodb_buffer_pool_size?

MySQL version is 5.5.29


texts: SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

texts:
I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job.

Now it fails intermittently with the following error:

SQL agent history says says:

  Date      6/12/2015 6:00:00 a.m. Log      Job History (PBI) Step ID       1
  Server        DEV-DW Job Name     PBI Step Name       Customer DETL
  Duration      00:00:12 Sql Severity   16 Sql Message ID   27195 Operator
  Emailed    Operator Net sent   Operator Paged  Retries Attempted  0
  Message Executed as user: \. The operation failed
  because the execution timed out. [SQLSTATE 42000] (Error 27195).  The
  step failed.

In the SQL logs it says:

  Unsafe assembly 'microsoft.sqlserver.integrationservices.server,
  version=11.0.0.0, culture=neutral, publickeytoken=xxxxxxxxxxxx,
  processorarchitecture=msil' loaded into appdomain 73
  (SSISDB.dbo[runtime].103).

Why would it only fail sometimes?
Why is it failing now?
What is the root cause?
How can I fix this?

Running SQL 2012 SP2 (11.0.5343) on Win 2008 R2 Standard SP1 on VMWare.

Edit: Updated to SQL 2012 SP3 (11.0.6020).

Edit: have added more context and clarified.

Following suggestion from @TheGameisWar, I queried ssisdb

SELECT *
FROM [SSISDB].[catalog].[event_messages]
WHERE event_name = 'OnError'

Returns no error messages for that date.


texts: When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

texts:
I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that it is safe to do so. A comment on this PostgreSQL update here says:

  ... the user can force the old behavior [LESS OPTIMIZED] by
  specifying MATERIALIZED; this would mainly be useful when the query
  had deliberately been employing WITH as an optimization fence to
  prevent a poor choice of plan.

I have been wondering, given that there is a situation where it is safe to use the CTE optimization (NOT MATERIALIZED keyword), under which circumstances can the less optimized "MATERIALIZED" keyword "prevent a poor choice of plan" as the quote says or provide an even better plan?

A final note, this is a copy of the question over at https://stackoverflow.com/questions/60701158/when-does-the-postgresql-12-cte-materialized-option-provide-an-optimization-adva. I was given the advice that this question fits this community better and to thus repost here.


texts: Query problem: there can be only one auto column

texts:
Can someone please tell me what's wrong with this table definition.
mysql version is 5.1.52-log

root@localhost spoolrdb> create table spoolqueue (
                             queue int,
                             idx bigint not null auto_increment,
                             status smallint,
                             querystring varchar(2048),
                             contenttype varchar(255),
                             characterencoding varchar(16),
                             body text,
                             primary key(queue,idx)
                             );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key


texts: How can I import the fga_log$ table with impdp Oracle 12 R1

texts:
I am trying to import with impdp a save made as follows:

expdp '"sys/mmm as sysdba"' full=y COMPRESSION=ALL DIRECTORY=LOGICS_EXPORTS COMPRESSION_ALGORITHM=BASIC LOGTIME=ALL CONTENT=ALL DUMPFILE=FULL-INCLUDE-FGA_LOG-16-OCT-2019%U.dmp LOGFILE=FULL-INCLUDE-FGA_LOG-16-OCT-2019.log PARALLEL= 7 INCLUDE=AUDIT_TRAILS

The idea is to have the fga audit table saved, the export is completed successfully but now, when importing:

impdp '"sys/mmm as sysdba"' DIRECTORY=LOGICS_EXPORTS PARALLEL=7 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE DUMPFILE=FULL-INCLUDE-FGA_LOG-16-OCT-2019%U.dmp LOGFILE=IMPORT-FULL-INCLUDE-FGA_LOG-16-OCT-2019.log TABLES=SYS.FGA_LOG$FOR_EXPORT REMAP_TABLE=SYS.FGA_LOG$FOR_EXPORT:FGA_AUDITS;

ORA-39002: invalid operation
ORA-39166: Object SYS.FGA_LOG was not found or could not be exported or imported.

This is the log of the expdp:

16-OCT-19 10:03:20.315: Estimate in progress using BLOCKS method...
16-OCT-19 10:03:22.251: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
16-OCT-19 10:03:22.449: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
16-OCT-19 10:03:22.688: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
16-OCT-19 10:03:25.579: Total estimation using BLOCKS method: 1.500 GB
16-OCT-19 10:03:25.969: Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
16-OCT-19 10:03:25.971: . . exported "SYS"."DAM_CLEANUP_EVENTS$"                 5.328 KB       7 rows
16-OCT-19 10:03:25.973: Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
16-OCT-19 10:03:25.976: . . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
16-OCT-19 10:03:26.015: . . exported "SYS"."DAM_CONFIG_PARAM$"                   5.148 KB      15 rows
16-OCT-19 10:03:26.353: . . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.257 KB      37 rows
16-OCT-19 10:03:30.827: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
16-OCT-19 10:03:33.541: Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
16-OCT-19 10:03:37.260: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
16-OCT-19 10:04:02.411: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
16-OCT-19 10:04:15.722: . . exported "SYS"."AUD$"                                172.9 MB 7354730 rows
16-OCT-19 10:04:25.963: Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
16-OCT-19 10:04:25.968: Processing object type DATABASE_EXPORT/STATISTICS/MARKER
16-OCT-19 10:04:25.982: Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
16-OCT-19 10:04:25.986: Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
16-OCT-19 11:15:24.400: . . exported "SYS"."FGA_LOG$FOR_EXPORT"                  8.201 GB 144651189 rows
16-OCT-19 11:15:24.654: . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               4.945 KB       2 rows
16-OCT-19 11:15:25.468: Master table "SYS"."SYS_EXPORT_FULL_03" successfully loaded/unloaded
16-OCT-19 11:15:25.470: ******************************************************************************
16-OCT-19 11:15:25.471: Dump file set for SYS.SYS_EXPORT_FULL_03 is:
16-OCT-19 11:15:25.475:   /salvas/logica/buc/FULL-INCLUDE-FGA_LOG-16-OCTUBRE-201901.dmp
16-OCT-19 11:15:25.476:   /salvas/logica/buc/FULL-INCLUDE-FGA_LOG-16-OCTUBRE-201902.dmp
16-OCT-19 11:15:25.477:   /salvas/logica/buc/FULL-INCLUDE-FGA_LOG-16-OCTUBRE-201903.dmp
16-OCT-19 11:15:25.478:   /salvas/logica/buc/FULL-INCLUDE-FGA_LOG-16-OCTUBRE-201904.dmp
16-OCT-19 11:15:25.488: Job "SYS"."SYS_EXPORT_FULL_03" successfully completed at Wed Oct 16 11:15:25 2019 elapsed 0 01:12:08


texts:
I'm using MySQL 5.1, PHP 5.3 and the table is MyISAM. Here's the DTD of this very fake table:

CREATE TABLE `temp1` (
  `value` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This script basically prints the row count for the table temp1, then inserts a row, then prints the row count again, then sleeps for a second and prints the row count one more time. The INSERTs are done with the write user and the SELECTs are done with the read user.

<?php

$value = "test";
$ro_user = "ro";
$ro_pass = "ro";
$write_user = "write";
$write_pass = "write";
$db_name = "db";

$ro_db = mysql_connect('localhost', $ro_user, $ro_pass);
mysql_select_db($db_name, $ro_db);

$res = mysql_query("SELECT * FROM temp1 WHERE value = '" . mysql_real_escape_string($value) . "'", $ro_db);
var_dump(mysql_num_rows($res));

$write_db = mysql_connect('localhost', $write_user, $write_pass);
mysql_select_db($db_name, $write_db);
mysql_query("INSERT INTO temp1 (`value`)  VALUES ('" . mysql_real_escape_string($value) . "')", $write_db);

$res = mysql_query("SELECT * FROM temp1 WHERE value = '" . mysql_real_escape_string($value) . "'", $ro_db);
var_dump($res);
var_dump(mysql_num_rows($res));

sleep(1);

$res = mysql_query("SELECT * FROM temp1 WHERE value = '" . mysql_real_escape_string($value) . "'", $ro_db);
var_dump($res);
var_dump(mysql_num_rows($res));

The printout are usually something like this:

int(12)
resource(8) of type (mysql result)
int(12)
resource(9) of type (mysql result)
int(13)

This seems to indicate that the INSERT isn't fully complete by the time the second SELECT query is executed. So my questions are:

Are MySQL INSERT statements usually asynchronous? It's hard to get information about this, but I thought the answer was no.
Are there any MySQL settings which would influence this behavior?
Is the table engine significant here?
Is there any possibility of caching going on which would cause this? I'm running this on a vagrant box with default settings.

Even weirder, I can't reproduce this behavior in MySQL 5.5 or 5.6, or any InnoDB table. No race condition shows up there.


texts: Unable to create partial indexes using bloom

texts:
I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them all with 1 bloom index.
However, when I create a bloom index with WHERE clause using below syntax, I get below error:
CREATE EXTENSION bloom;

CREATE INDEX idx_bloom_bar ON message USING bloom
(s, p, c, m, e, id)
WHERE (islatest = true)
WITH (length=56, col1=4, col2=4, col3=4, col4=4, col5=4, col6=4);

SQL Error [42601]: ERROR: syntax error at or near "WITH"
Position: 165

However, without WHERE clause, I'm able to create this index successfully.
Please let me know if we can use them as a replacement of multiple partial indexes or not to reduce number of indexes on my table.


texts: SQL Server Error - The wait operation timed out (provider: TCP Provider, error: 0 - The wait operation timed out.)

texts:
I'm running a couple web applications on the same set of servers. In my logs across a number of different queries in both apps (as well as calls to the ASPState session state database) I see errors similar to the following:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.) ---System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out&#xD;&#xA;  at
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal&amp; connection)  at
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal&amp; connection)  at
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&amp; connection)  at
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource 1 retry, DbConnectionOptions userOptions)  at
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)  at
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource 1 retry)  at
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource 1 retry)  at
System.Data.SqlClient.SqlConnection.Open()  at

One web application is fairly heavily accessed (~500 requests/sec) and one is very lightly used. I see the errors in the logs for both apps, so it seems to be an environment issue as opposed to a specific app. The server setup is 4 web servers behind a load balancer connected to 2 SQL servers in an AlwaysOn availability group. All of this is running in Azure.

Lots of searching, but can't seem to find anything that helps either identify the cause or even narrow it down to a SQL configuration issue vs Azure configuration vs network connectivity issue vs clustering issue vs ??? As best I can tell it can't connect either because it can't find the network path or the server can't accept connections. Any help would be appreciated. Thanks!


texts:
We are hosting sharepoint DBs with AlwaysOn AGs and FCI. My boss asked me what the options for encryption are on the content database, it's backup files and at the column level?

I have read about TDE. Could this harm performance?

Is windows level encryption possible?

Are there any other options?


texts:
I have a simple data flow.  I'm trying to join two sets of data from two different databases.  The TXT database is collation SQL_Latin1_General_CP1_CI_AS. The other dataset (Cost) is coming from Dynamics AX which is Latin1_General_CI_AS.

AX columns are Unicode.  So I have put data conversion on TXT columns to Unicode

However no matter what I do I cannot get rid of the MERGE JOIN ERROR and it's driving me insane.

  TITLE: Microsoft Visual Studio

  Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata.

Both datasets are set to sort by ITEMID (and I have set sort keys in both).  Any ideas???


texts: PostgreSQL pg_basebackup tar file does not yield pg_xlog upon unpacking

texts:
I configured a basebackup on a Postgres 9.5 server, specifying that the xlogs should be fetched, and that the file will be a compressed tar archive.  I configured this basebackup to run on my standby server filesystem (which was setup with postgres internal streaming asynchronous replication).

Details as follows:

archive_mode = off
max_wal_senders = 5
pg_basebackup -U postgres -D - -Ft -Xf -z  > mycompressed.tar.gz

When I unpacked my tar file, the pg_xlog directory (which is setup as a physical directory, not a link) is empty, though the backup completion was clean.   I can get the DB to come up, but I get OID not found errors when I select some objects, indicating an inconsistent state.  Am I missing something?

Note:  When I run this procedure on the master, there are no issues at all, and all of the WAL files are captured in the tar file.


texts: SSRS using external images through tls 1.2 leaves src(unknown)

texts:
One of our SSRS reports loads images from external sources and one of these sources has been locked down to tls 1.2 (which is fair and we're expecting more of our clients to be doing this in the future)

The tls 1.2 update has been applied to the server holding the ReportServer database and the two Reporting Services endpoints held on seperate machines.

All three machines have been tested locally and can all load the secured image in their browsers but when a report is ran using SSRS it fails

I've set up a report for testing which has no database connection, it is simply a non secured image link and a tls 1.2 secured image link,

The error I get is: Warning        [rsInvalidImageReference] The ImageData for the image ‘Image2’ is invalid. Details: The request was aborted: Could not create SSL/TLS secure channel.

This leaves the image as src(unknown) in the actual report

So I feel like I've missed something in the installation of the tls 1.2 patch

Anyone have any ideas what might be going on?


texts: Losing records during transitioning to a failover cluster

texts:
Recently we had an outage in our primary SQL Server cluster and traffic was directed to the fail-over cluster. This process has taken a few seconds. The funny thing is that application logs show we have successfully inserted a few records to the database during the transition time but they do not exist in the DB!

Since the database maintenance is done by a third-party company, I do not have much detail information.

Is that event possible to have the records inserted to the DB and then lost them during the transition to the fail-over cluster?


texts: Is there any difference between 'LIMIT 0, 1' and 'LIMIT 1'?

texts:
I recently stumbled upon example codes, which differed by these notations.

SELECT * FROM table LIMIT 0, 1
SELECT * FROM table LIMIT 1

The first argument should be considered as the offset if I'm not wrong, the purpose of those two queries is to limit the selection to up the first row of table

Is there any negative effect when leaving out the offset/how is it possible to leave it out in the first place? Or did I misunderstood the queries?


texts: Why is it taking longer to create an index after column size increases?

texts:
Our vendor changed column widths on almost every column in the entire database.  The database is around 7TB, 9000+ tables. We are trying to create an index on a table that has 5.5billion rows. Before the vendor's upgrade we could create the index in 2 hours. Now it takes days. What they have done is increase any varchar(xx) size to varchar(256).  So most columns used to be varchar(18) or varchar(75), etc.

Anyway the primary key consists of 6 columns that combined width was 126 characters. Now after the upgrade, the primary key is 1283 characters which violates SQL Servers limit of 900 characters.  The entire table column width went from a total combined varchar count of 1049 to a total combined varchar count of 4009.

There is not an increase in data, the table doesn't take up any more "space" than it did before all the column width increase, but performance to create something as simple as an index is now taking an unreasonable amount of time.

Can anyone explain why it is taking so much longer to create and index when the only thing done was increasing the size of the columns?

The index we are trying to create is nonclustered since the pk is the clustered index.  After several attempts to create the index, we gave up. I think it ran 4 or 5 days without completion.

I tried this in a non-production environment by taking a file system snapshot and brought the database up on a quieter server.


texts: Why do my Batch Requests increase when doing a Differential Backup

texts:
I have started doing some long-term tracking of various activities on our 2012 SQL Server and I have noticed an increase in Batch Requests during our incremental backups.

To give an idea, normal day-to-day we are around 10-20 batch requests per second but during the time our differential backup runs, it jumps to 100-130 per second.

I know this is not a lot but it made me curious as to what is going on that it increases 10-fold.

Not sure what info you might need to help troubleshoot this issue.


texts: Profiling and debugging "Incorrect syntax was encountered while parsing :setvar."

texts:
I have the following script using SQLCMD setvar, but I receive an error when I parse the script:

:setvar dbname "PROD"
:setvar backuppath = "e:\sql\restore\PROD.bak"

USE [master];
GO

-- backup the existing PRODUCTION database
BACKUP DATABASE [$(dbname)]
TO DISK = '$(backuppath)'
WITH COPY_ONLY, COMPRESSION

-- we need to clear the existing PRODCOPY users
-- before we restore and replace
IF DB_ID('PRODCOPY') IS NOT NULL
BEGIN
    -- Remove any connected users
    ALTER DATABASE [PRODCOPY]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    WAITFOR DELAY '00:00:05'
    ALTER DATABASE [PRODCOPY]
    SET MULTI_USER
END

-- restore PROD as PRODCOPY
-- ...

The error is:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :setvar.

If I comment out the section:

IF DB_ID('PRODCOPY') IS NOT NULL
BEGIN
...
END

Then the error goes away. It seems like the use of SQLCMD is actually testing whether the PRODCOPY database exists, which of in this case it doesn't.

I've tried to use profiler, but although I can see the SQL commands:

SET PARSEONLY ON
SET PARSEONLY OFF

However, profiler is not logging anything in between the two instructions that should be parsed, but I still see the error! Any ideas what I need to do to see the content that is supposed to be parsed by the server? Which columns, events to trace?

Is this an oddity with SQLCMDs?


texts: [Linux LEAP 15.1][mariadb 10.4.3][C API] The UTF8 characters are not handled as before (10.2.9)

texts:
This C source code extract is expected to execute a query... but even if I change the name of the table from "Localités" to "Localites", the program no longer display the utf8 characters as the database could when I execute the same query in command line.

This post

The situation "before" gave those results that were expected:
Expected situation

I really don't know how tell the client (as the server respects the unicode) to display the datas as this program did since upgrade :{


texts: What do we call the equivalent of CSS "Specificity" in SQL?

texts:
In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example,

div p span {...}

Is more specific than, and thus would override:

span {...}

In SQL, one can start a query like:

SELECT database.table.columnOne, database.table.columnTwo FROM ...

The same query can be expressed, less specifically, as:

SELECT columnOne, columnTwo FROM ...

Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?


texts: DB2 ARCHIVE mode - Can all archive logs be deleted after doing the FULL OFFLINE backup?

texts:
I am interested in one thing, if I understood correctly.
DB2 is in Archive mode (online backups are made and archive logs keep coming which is OK).

If I made offline full backup today and I know I will not need returning to previous days etc. can I be free to delete all archive logs? In case of doing the restore of today's backup I suppose I will not get some error "S000013.log is missing" or something like that?

By my opinion it should be OK but I want to check this with experts?


texts:
I have a table that is used for log data; the data is only INSERTed, never UPDATEd. The table has no business data whatsoever, and thus it is not a problem if I cannot recover it in case of a disaster.

In addition to inserting, I periodically want to empty this table.

The table has no foreign keys, and only one clustered index on the field which contains the log date/time.

The database recovery mode is currently set to FULL. Is it possible to prevent logging transactions when writing/deleting from this specific table, while still logging changes to other tables?


texts: MySQL : Update some fields depending on each other

texts:
I have a table with 16 fields as User1, User2, ... User16, ( + some other fields ) , I have a stored procedure named "Register" that get a "User Name" and put it in the first Null field of those 16 fields, for example if User1 and User2 have value, new "User Name" will put in User3, there is no problem

I have another stored procedure named "UnRegister" that get a "User Name" and set to Null the value of field that have given "User Name"

There is no problem in Updating table and putting Null for given "User Name" but the problem is after Updating User(X) to Null, User(X+1) value should put in User(X) field !

An example :

User1 = "U1"
User2 = "U2"
User3 = "U3"
User4 = "U4"
User5 = NULL
...
User16 = NULL

Now , I want to Unregister "U2" and the result values should be like this :

User1 = "U1"
User2 = "U3"
User3 = "U4"
User4 = NULL
User5 = NULL
...
User16 = NULL

I think, I should use 16 CASE statement in an UPDATE statement for all 16 fields and 16 WHEN for each CASE !

Is there another way to do that ?

Is there design problems ?!

In fact, this a tournament with 16 users

thanks ...


texts:
I am working on the tool that will silently deploy SQL server instance and I need to run a few scripts after installation, but I struggle to find where SQLCMD utility is.
For example, I use this:
FEATURES=SQLENGINE,SDK
But after installation is finished there is no SQLCMD on the system.
Where is it?
UPDATE:
I've managed to create a silent installation that includes sqlcmd tool with this:
FEATURES=SQLENGINE,Tools


texts:
In Oracle when you try to drop a Global Temporary Table that is in use you get the following exception:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

This is caused by a session that is using the GTT in a current transaction. Is there a way to query the data dictionary to determine which sessions are blocking the drop?  I know I can get the sessions with in progress transactions with something like this, but I would like to narrow it down further.

select * from v$session
 where session_id in (select session_id from dba_locks where lock_type='Transaction');


texts: INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query

texts:
I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id and using at least two rows) and so I have not been able to confirm the effect to a satisfactory degree.

Presume all the data in the table exists and that some of the quantity rows will be different:

INSERT INTO variations (id, name, quantity)
VALUES
(1, 'John', 4),
(2, 'Amy', 5),
(3, 'Elizabeth', 6)
ON DUPLICATE KEY UPDATE
id=VALUES(id),
name=VALUES(name),
quantity=VALUES(quantity);

If the quantities in the table are 1, 2 and 3 then what I've constituted is that the above query should UPDATE them to 4, 5 and 6. However manually running the command returns Affected rows: 0 and the data in my application is beyond any doubt not changing.

Clarifications:

The name rows may have their values change, I'm trying to keep the code simple.
The id is the primary key and there are no other keys.
No UPDATE has to happen if the all of the data for all of the row are the same between the query and table row.
Sometimes only one column of data might change, sometimes all columns except id might change.
There is no clarification in regards to VALUES() if it is using the $row (e.g. from PHP) data to UPDATE if it differs from what is already in the row.
There is no clarification in regards to if all the columns must be specified (besides the the primary or a unique key).
The documentation at both MariaDB and MySQL leaves a nebulous mystical cloud of unknowns.
I'm running MariaDB 10.2.6 (MySQL 5.5.5).

I'm tempted just to dump the code in to a loop to run an UPDATE query per row as I don't have time to create a huge project out of this. None of the existing threads here or at SO properly address the multiple UPDATE single query issue (examples of using case look like monstrous nightmares to construct) and so I'm hoping for an answer that can clarify this beyond any doubt so I can learn from this frustrating challenge.