texts: Help with removing duplicate reversed pairs in relational algebra

texts:
I have a listing of data that contains duplicate reversed pairs and I need to remove them.. Call them, name1 and name2. Where I have Tom and Mike, and Mike and Tom, such that this single pair is being counted twice.

 name1 | name2
-------|-------
 Tom   | Mike
 Pete  | Jenny
 Bill  | Jenny
 Joe   | Mary
 Mike  | Tom
 Jenny | Pete
 Jenny | Bill
 Mary  | Joe
 Linda | Jenny

The List was a product of an initial match of student with guidance counselor,  then the product of the student/counselor and counselor table, which resulted in a longer list and was able to reduce to the pairs above. But, now can't get rid of the duplicates.

While I might have made a mistake in combing the tables that made the pairing, I am stuck with that table for now.. This is a listing of student paired with guidance counselors.

Is there a way to de-dup the list, or do I need to start over?


texts: Beautify/reformat the SQL script button partially working

texts:
I am making my way through Murach's MySQL, 2nd Edition and I've encountered a bit of a speed bump.

When writing scripts to create tables, etc., I've always been able to write scripts and with the cursor at the end, press the Beautify/reformat the SQL script button and they get cleaned up.

With the following code, it only gets partially cleaned up (just the SELECT statement). The following code does what it's supposed to, but the Beautify/reformat button doesn't clean up everything in the editor. Any suggestions?

use ap;

drop procedure if exists test;

delimiter //

create procedure test()
begin
declare first_invoice_due_date date;

SELECT
    MIN(invoice_due_date)
INTO first_invoice_due_date FROM
    invoices
WHERE
    invoice_total - payment_total - credit_total > 0;

if first_invoice_due_date < now() then
select 'Outstanding invoices overdue!';
elseif first_invoice_due_date = now() then
select 'Outstanding invoices are due today!';
else
select 'No invoices are overdue.';
end if;
end//


texts: MySQL community server 8.0.18 conflicts with ODBC connector 5.1

texts: Is it possible to connect to MySQL server 8.0.18.0 using an older MySQL 5.1.36 ODBC connector?


texts: Insert Missing Date row for each group With & W/O using CTE

texts:
This is my calendar table:

DECLARE @StartDate   DATETIME
DECLARE @EndDate     DATETIME

SET @StartDate       =      '9/24/2018';
SET @EndDate         =      '9/28/2018';

WITH theDates AS
     (SELECT @StartDate AS theDate
      UNION ALL
      SELECT DATEADD(DAY, 1, theDate)
        FROM theDates
       WHERE DATEADD(DAY, 1, theDate) <=  @EndDate
     )
INSERT INTO dbo.tblDateRangeNBNO
SELECT theDate FROM theDates
OPTION (MAXRECURSION 0);

I need to join this to the another table to get each Group's (Values to be carried from same group) missing rows for that date.

Another table:

MeetRoom | StartDT                       | Status | MinsUsed |
 tpa         2018-09-25 08:15:00.000         IN         50
 tpa         2018-09-26 08:35:00.000         FL         90
 ngf         2018-09-24 08:00:00.000         TN         600
 ngf         2018-09-27 17:56:31.563         MN         210
 pdb         2018-09-24 02:11:00.000         TL         200
 pdb         2018-09-28 14:54:46.473         TD         150

Expected result set:

'FC' will be the Status & 1440 will be MinsUsed for each missing MeetRoom dates

MeetRoom | StartDT                       | Status | MinsUsed |
 tpa         2018-09-24 00:00:00.000         FC         1440
 tpa         2018-09-27 00:00:00.000         FC         1440
 tpa         2018-09-28 00:00:00.000         FC         1440
 ngf         2018-09-25 00:00:00.000         FC         1440
 ngf         2018-09-26 00:00:00.000         FC         1440
 ngf         2018-09-28 00:00:00.000         FC         1440
 pdb         2018-09-25 00:00:00.000         FC         1440
 pdb         2018-09-26 00:00:00.000         FC         1440
 pdb         2018-09-27 00:00:00.000         FC         1440

I've the following query, but skipping few records (not sure whats the missing piece)

SELECT p.DateRange, COALESCE(bi.[Status], 'FC') [Status], p.MeetRoom
                    FROM
                    (
                      SELECT MeetRoom, DateRange
                        FROM
                      (
                        SELECT MeetRoom, MIN(DateRange) Min_Date, MAX(DATEADD(DAY, 1, DateRange)) Max_Date
                          FROM tblBI b RIGHT JOIN tblDateRangeNBNO d  ON CAST(d.DateRange AS DATE) = CAST(b.StartDT AS DATE)
                         GROUP BY MeetRoom
                      ) q CROSS JOIN tblDateRangeNBNO dr
                       WHERE dr.DateRange BETWEEN q.min_date AND DATEADD(DAY, 1, q.max_date)
                    ) p LEFT JOIN tblBI bi
                        ON p.MeetRoom= bi.MeetRoom AND CAST(p.DateRange AS DATE) = CAST(bi.StartDT AS DATE)
                    WHERE bi.[Status] IS NULL


texts: DBCC show_statistics: density doesn't match what I calculate

texts:
I've read in several articles that the density of a column is (1.0 / count(distinct columnname)).  I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column density in DBCC show_statistics even AFTER updating statistics with fullscan is .48.

I am confused as to where this number could be coming from.  Can anyone explain it to me?


texts: How do I get the MySQL binary log coordinates from the binary files?

texts:
I'm attempting to set up replication for a large (120 GB) MySQL database and I messed up.

I shut down the master, copied the data files, and restarted the master. Now I've set up the slave & started it up, and I realize that I neglected to get the MASTER_LOG_FILE and MASTER_LOG_POS values needed to setup replication.

I still have an untouched copy of the data files. Is there any way to determine MASTER_LOG_FILE and MASTER_LOG_POS from those files?

Related question:

what happens if you set the MASTER_LOG_POS to an earlier point in time?
Am I correct in assuming duplicate queries will be executed, possibly resulting in duplicate data?

Edit

I've been investigating the mysqlbinlog command and discovered what looks like the correct info by executing:

mysqlbinlog --to-last-log --start-datetime='2015-06-21 20:05:00'
    mysql-bin.000006 | grep end_log_pos

The output looks something like this:

... a whole bunch more lines ...

#150621 20:10:21 server id 1  end_log_pos 720159843     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720161877     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720162762     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164796     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164815     Stop

I'm guessing I should use one of those last two positions, but I'm not sure which.


texts: Is it possible to use variables in MySQL schema definition?

texts:
I need to do something like this:

use mydb;
SET @DBNAME = 'users';
CREATE TABLE `some_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE,
  CONSTRAINT `key_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES @DBNAME.`users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
);

REFERENCES @DBNAME.users - is the part I'm interested in.

Is it possible to somehow parametrize DB name in schema definition?

Thanks!


texts: Cannot fix ERROR:ORA-01031: insufficient privileges

texts:
I have tried almost every solution on the internet however I still cannot fix this problem. I cannot login as sysdba every time I try SQL>connect /as sysdba I get the error ERROR:ORA-01031: insufficient privileges.

My account is in ora_dba group. I'm using oracle 10g. I'm new to database so I really don't know what to do.


texts: How does SQL Server store the information in Job Schedule Properties?

texts:
I know there is SELECT * FROM msdb..sysjobschedules, but that just shows the next time a job is scheduled to run.

I want to know how this is all determined and stored.  So where does it store the value for Recurs every Monday check box?


texts:
I am building a SQL query in Oracle 10g that needs to sum overtime hours for the base table, but needs fields from the parent tables as well. I am looking for the fastest way to build the query. The Project_ID in the first three tables is the same, where the Work_ID is one-to-many to the fourth table.

Table J
Project_ID VARCHAR(6)
J_Title VARCHAR(25)

Table K
Project_ID VARCHAR(6)
K_Title VARCHAR(25)

Table L
Project_ID VARCHAR(6)
L_Title VARCHAR(25)
Work_ID VARCHAR(6)

Table C
Work_ID VARCHAR(6)
L_Title VARCHAR(25)
CHRG_DT DATE
WRK_HR NUMBER(5,2)

My best guess looks like this (It's simplified, and the date is actually passed as a variable to the stored procedure):

SELECT
 J.J_Title,
 K.K_Title,
 L.L_Title,
 L.Work_ID,
SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
FROM C,
L,
K,
J
WHERE J.Project_ID = 'ABC'
AND K.Project_ID = J.Project_ID
AND L.Project_ID = K.Project_ID
AND C.Work_ID = L.Work_ID
GROUP BY L.Work_ID,
L.Project_ID,
K.Project_ID,
J.J_Title,
K.K_Title,
L.L_Title

UPDATE: Omitted two fields in Table C. Changed Group By fields, and added Work_ID to output for clarity. Sorry for changes, I have to type this from another network, can't just cut and paste. Also trying to leave out unrelated fields.

This is the output I am getting:

J_Title K_Title L_Title Work_ID WRK_HR
------- ------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC  DEF         30
JOB_ABC KO_ABC  LO_ABC  DFG          3

This is the output I am looking for:

J_Title K_Title L_Title WRK_HR
------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC      33


texts: How to load 100k + Records into progres database from a CSV file?

texts:
Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of batches so that unloaded data/batch can be revisited, corrected and loaded again).

Our project involves a Java application and we initially thought of using JDBC, but reading each row and committing to database slows down the application.

Please suggest if you know an approach to get this job done.


texts: MySQL open_files_limit - cannot change this variable

texts:
I'm running into issues because my open_files_limit for mysql is only 1024.

Centos 7, MySQL Community Server 5.6.22-log

It's actually a vps dedicated to mysql for a WHM server (remote mysql), but that should be beside the point.

Config files

my.cnf:

[mysqld]
open_files_limit = 100000
open-files-limit = 100000 #I've read that the dashes are required on older versions, but I threw it in anyway.
innodb_buffer_pool_size = 600M
...

# and the same for mysqld_safe
[mysqld_safe]
open_files_limit = 100000
open-files-limit = 100000
...

/etc/security/limits.conf:

*       hard    nofile  100000
*       soft    nofile  100000

Command outputs

My os open_files_limit for root:

[root@mack ~]# ulimit -Hn -Sn
open files                      (-n) 100000
open files                      (-n) 100000

And then as mysql user:

[root@mack ~]# su mysql
bash-4.2$ ulimit -Hn -Sn
open files                      (-n) 100000
open files                      (-n) 100000

MySQL status:

[root@mack ~]# service mysql status
Redirecting to /bin/systemctl status  mysql.service
mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
   Active: active (running) since Wed 2014-12-24 10:41:09 EST; 40min ago
  Process: 2982 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
  Process: 2970 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 2981 (mysqld_safe)
   CGroup: /system.slice/mysqld.service
           ââ2981 /bin/sh /usr/bin/mysqld_safe
           ââ3268 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --open-files-limit=100000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/my...

Dec 24 10:41:08 mack systemd[1]: Starting MySQL Community Server...
Dec 24 10:41:09 mack mysqld_safe[2981]: 141224 10:41:09 mysqld_safe Logging to '/var/log/mysqld.log'.
Dec 24 10:41:09 mack mysqld_safe[2981]: 141224 10:41:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Dec 24 10:41:09 mack systemd[1]: Started MySQL Community Server.

I've restarted mysql, stopped, checked status to make sure it stopped, then started it, and rebooted the whole system.

Evidence that my.cnf works:

mysql> show global variables like '%buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 629145600 |
+-------------------------+-----------+
1 row in set (0.00 sec)

Now here's where my blood pressure goes up:

mysql> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

What am I missing?  Is there another my.cnf that's overwriting my value?

[root@mack ~]# find / -name "*.cnf"
/usr/share/mysql/my-default.cnf
/usr/share/doc/mysql-community-server-5.6.22/my-default.cnf
/var/lib/mysql/auto.cnf
/etc/my.cnf
/etc/pki/tls/openssl.cnf

I looked into all of those and there is no mention of open_files_limit.  For the heck of it, I grep'd through these looking for the setting:

[root@mack ~]# grep -r "open_files_limit" /etc
/etc/my.cnf:open_files_limit = 100000
/etc/my.cnf:open_files_limit = 100000
[root@mack ~]# grep -r "open_files_limit" /var
[root@mack ~]# grep -r "open_files_limit" /usr
/usr/share/vim/vim74/syntax/ora.vim:syn keyword oraKeywordUnd     _number_cached_attributes _offline_rollback_segments _open_files_limit
Binary file /usr/sbin/mysqld matches
Binary file /usr/sbin/mysqld-debug matches
Binary file /usr/bin/mysqlbinlog matches
/usr/bin/mysqld_safe:      --open_files_limit=*) open_files="$val" ;;

But nope, they don't affect it.


texts: Adding a nullable column concurrently with an update or insert

texts:
In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update?

For example:

ALTER TABLE MyTable ADD MyColumn varchar(255) NULL;


texts: How to get MS Access to reach a Linked server on MSSQL

texts:
We have an MS Access DB that has been in place for 16 years in a Front End/BackEnd configuration. It is now running under the latest version of MS Access using ACCDB files. Since its inception, we have added the element of connecting to a remote MySQL server to access some of its tables R/O. This works fine. We create an ODBC connection to the remote MySQL server, then in Access we choose - External Data>>ODBC Database>>Link to the data source, select the ODBC driver and then select the tables we need. They then appear as linked tables with a globe icon.

For reasons too lengthy to get into, we have the need to move the MS Access Back End to an MSSQL server. So we have set up one on the LAN  (SQL 2012). It works fine, and I can do basically the same type of ODBC connection in Access to reach any DB we create on that server.

We then use the SQL Server Management Studio to create a Linked Server of the remote MYSQL server. This works fine as well. Using the SSMS, we can see all the tables of the Linked server and even query data using the OPENQUERY method. We cannot seem to use the 4-part name ("servername.databasename.schemaname.tablename") to query the data. Any attempt to do so returns an error similar to this:

... contains no columns that can be selected or the current user does not have permissions on that object.

But the real question is - what would I do in MS Access to reach the Linked server? When I create an ODBC connection to the MSSQL server, all I can see is the DBs created there. I cannot see the Linked Server, therefore I cannot link any of its tables to MS Access. I have scoured the web, and some threads touch on this, but are not clear enough for me to figure this out.

Thanks in advance to anyone who can help.


texts: What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL?   I'm used to MySQLWorkbench for working with MySQL databases.  Now I have some sqlite databases to work with and would like to find a similar tool.


texts: Mysql find 10 nearest point from given lat/long per type?

texts:
I have a database given with 3 different types of locations A, B and C. These points have latitude and longitude information with them.

Now I have a random lat-long given and I want to find the closest 10 points per type to that given location - how can I do that? Anyone has an idea?`

Thanks for any help


texts: Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain

texts:
I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts that data locally.

The powershell works perfectly fine if I run it in Powershell ISE, but when I put it into a powershell step in a SQL Agent job it fails.  I have the job using a proxy sql agent account that I set up that uses a windows account, let's call it domain\PowershellAgent.  I have granted the account PowershellAgent sufficient permissions on the server as far as I can tell, but I still get the error.

The full error is:

  Login failed.  The login is from an untrusted domain and cannot be
  used with Windows authentication.

What can I do to get this working?

Example of a connection:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBADatabase;Trusted_Connection=True;Connect Timeout=5; MultiSubnetFailover=Yes"
$sqlConnection.Open()

$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $Query
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 60
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id

$DataReader = $sqlCommand.ExecuteReader()

$Datatable.Load($DataReader)


texts: pg_stat_all_indexes shows 0 idx_scan even index is used

texts:
I can see that index is actually used in a query by using explain but pg_stat_all_indexes table shows 0 for that index. (all idx_scan, idx_tup_read and idx_tup_fetch are 0)

When this statictics are updated?
How can I see how many times my index is used?


texts:
I'm doing a uni assignment.
I have

a stock table where stock is assigned an ID (product_ID).
a sales table where the stock is purchased.
an invoice table where sales are processed.

I want the invoice entry to contain many product_id's and their quantities.
I currently have "Invoice_id, Customer_id, Staff_id, Product_id, Quantity, Price, Total Cost".
Doing it this way I can only have one product_id per entry. I want there to be multiple entries under one invoice_ID.
Am I thinking of this the right way? When you get an invoice it says the product on it and how many of them you bought.


texts: SQL Server 2012. Update second table based on input from first table

texts: I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the web site. When this happens, is there a way on the SQL Server backend to update/insert that value into Database02?


texts: Multiple group by with inner query in Oracle

texts:
I have a table Record with three columns: p_id, score, city.

P_id       score      city

12          20       Mumbai
12          80       Delhi
12          50       Pune
13          20       Mumbai
13          30       Delhi
13          50       Pune
13          90       Hyd

Here I want to have a view with p_id, max_score, max_score_city, min_score, min_score_city. Which means for each player, I want max score and the city in which it was scored, min score and the city in which it was scored,

p_id     max_score   max_score_city     min_score   min_score_city
12          80            Delhi             20           Mumbai
13          90            Hyd               30           Mumbai

I know I need to apply group by on p_id for min and max score, but how do I join both group by and how to relate player and city in them.
I am new to SQL, can someone please help?


texts:
How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details?

I don't have the option to restart the server or taking it offline first and put online back again as it is a production environment.


texts: Automatically set table name prefix according to other field in PowerDesigner

texts:
I'm working with PowerDesigner 16.5 and I have a PDM generated out of its corresponding LDM. The table names are inherited from the LDM and I'm fine with them, but I would like to generate the DDL with a table name prefix according the dimensional type (dimension or fact) of each. At this point I don't mind if it is done just in the DDL generation or in the name-to-code translation.

So a mydimensiontable table would have a code as CREATE TABLE dim_mydimensiontable and myfacttable would have a code as CREATE TABLE fact_myfacttable ... If not possible with the dimensional type, maybe a custom field could work as well for me.

Is it possible? Please note, I'm an experience data model designer but my experience with heavy customization/tweaking/hacking in PowerDesigner is very very limited, so I would need almost step by step explanations. :-/

Thank you very much in advance.

Nacho


texts: about giving same column name in every table of database

texts:
I have a database with 5 tables.
In every table there is a column for checking status.
If i give the name to that column which is same in all the 5 tables;
What will happen?
Is it correct according to conventions?


texts: PostgreSQL created a PostgreSQL user on mac. Is this necessary?

texts:
I just installed PostgreSQL on a Mac (Mountain Lion) today.

The installer created a mac user PostgreSQL on my machine. After installation, the user still exists.

My question is that why postgreSQL needs a new user? Can I remove this user? MySQL server does not require such a user being created.

Can someone help?


texts:
I'm trying to run pg_dump on a relatively small database (2G), but pg_dump keeps crashing with an out of memory error.

du -h --max-depth=0 9.4
1.9G    9.4

The command:

pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress;

Any ideas on how to allow the dump to happen?

****  Edit *****
-bash-4.2$ pg_dump -t new_answers -F tar -b -f new_answers.dump mydatabase
out of memory

the new_answers table has a TEXT column, but all the objects in are < 1k.  Also,there is only about 300 rows.  "dmesg | grep OOM" turned up nothing.


texts: Should xp_logevent and RAISERROR ... WITH LOG work on LocalDB?

texts:
I need to run a script to configure a LocalDB (v11.0.3000) instance and database (from a .NET 4.5 program) and I want to record results at various stages.  Someone suggested xp_logevent but it doesn't seem to work for me.  Should it?  When that failed, I tried RAISERROR ... WITH LOG but that didn't work, either.  Both write to the SQL Server log but neither write to the Windows Application event log (or any other log that I can see).

Note: although I want to run the script from a program, at this stage, I'm just using  Mgmt Studio.

I've tried:

EXEC master..xp_logevent 50001, 'Test'
EXEC master..xp_logevent 50002, 'Test', informational
EXEC master..xp_logevent 50003, 'Test', warning
EXEC master..xp_logevent 50004, 'Test', error

and

RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5) WITH LOG;

I've tried the same code under SQL Server Express (v11.0.3128) and it worked.

The account I'm using is sysadmin.  I've tried it with the account in BUILTIN\Administrators in case that was part of the problem - I know you have to be an admin to create an event source - but it didn't work.

If there's nothing that can be done to get either to work, is there another way of getting output from LocalDB on a client machine?  All that will be installed is LocalDB and .NET 4.5.

UPDATE

I've tried this on a second machine, recently upgraded to SQL Express 2012 SP1, and already with LocalDB, and got the same results.


texts: Can a production Oracle DB server also have a dataguard active standby for a different production database?

texts:
If you have a production oracle database (PRIM1) protected using Oracle Dataguard to a standby (STBY1) physical server in a different data centre, the standby server needs to be a similar specification to the primary server, so that in the event of a disaster it can become the primary.

This setup means that you have redundant hardware 'just in case'.

If you have another production database (PRIM2), could you install this on the same physical server as STBY1 of the first application and install the standby for this application (STBY2) on the primary server of the first application (PRIM1).

This setup would mean maximising use of the hardware and in the event of a failover, both primary servers might be running on the same machine so you might need additional memory and/or CPU available to provision in the event of a disaster but you would be using the hardware most of the time.

Any thoughts on the issues a setup like this would create ?


texts: what does that mean? COMMAND [PeriodicTaskRunner] task: UnusedLockCleaner took: 273ms

texts:
With mloginfo I found a strange log entry. Didn't find anything about it in MongoDB docs and other related sites.

$ mloginfo mongodb.log  --distinct
     source: mongodb.log
       host: example
      start: 2016 Aug 25 14:57:57.630
        end: 2016 Aug 26 08:02:05.751
date format: iso8601-local
     length: 572308
     binary: mongod
    version: 3.2.8
    storage: wiredTiger

DISTINCT

   28069    connection accepted from ... # ... ( ... now open)
   27587    end connection ... ( ... now open)
      15    exception: ... on:
      10    serverStatus was very slow:
       4    task: ... took: ... ms
       3    killcursors: found ... of
       3    SocketException handling request, closing client connection:
       1    git version:
       1    waiting for connections on port

what is that exactly? What is a PeriodicTaskRunner and what does UnusedLockCleaner do? Any actions required from DBA?

2016-08-25T21:03:38.449+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 105ms
2016-08-26T00:21:40.285+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 184ms
2016-08-26T01:25:40.940+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 273ms
2016-08-26T01:55:41.357+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 234ms


texts: How can I limit SQL Server Agent's simultaneous jobs number?

texts:
I'm looking for a setting (other than schedules shifting) which could limit the number of jobs running simultaneously.
I have 844 jobs in my SQL Agent (SQL Server 2017 Enterprise). Most of them needs few minutes to complete, there are about 190 jobs that needs half of hour. All of them are scheduled to run every hour.
If they run all together, I can't fit total duration time below 60 minutes.
Is there any setting that could queue 744 jobs until one from those 844 finishes and SQL Agent would start the next job waiting in the queue?


texts: ORDER BY on field that isn't in a SELECT using DISTINCT or GROUP BY to get distinct records

texts:
Migrated from MySQL where this query worked:

SELECT DISTINCT
    code,
    title,
    country
FROM
    data_a
ORDER BY
    sortorder

Basically, get a list and sort it by a user/company defined sortorder that is not based on code, title, or country.

Now that I'm in Postgres, this query does not work and gets a:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 12:  sortorder
          ^
SQL state: 42P10
Character: 135

Doing this however returns duplicates because there are additional fields not listed here that make that entire record unique. I just want the unique values from code, title, or country in order of the sortorder.

I tried to do a sub-query thinking I could sort it in the sub-query and bring it into the parent query already sorted, but it just comes over in non particular order... well, maybe it is particular, but it isn't the order we want.

SELECT
    DISTINCT s.*
FROM
    (
        SELECT
            code,
            title,
            country
        FROM
            data_a
        GROUP BY
            code,
            title,
            country,
            sortorder
        ORDER BY
            sortorder
    ) s;

Any suggestions for how to do this?


texts: Two MySQL Server accessing same database over NFS

texts:
I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following:

one MySQL instance will be a producer
one MySQL will be read only consumer

On the MySQL site, it is mentioned this possible with MyISAM and merge storage engine only, but they never recommend it.

http://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html


texts: Why does MySQL server 8+ create .ini config files in a MySQL server 5.5 folder?

texts:
I have been struggling with MySQL timezone issues for a while I think I finally know why. In the past 2 days my timestamps were saved 2 hours behind my actual machine time (2+ UTC Jerusalem).

I tried running these commands:

SELECT @@global.time_zone;
SELECT @@session.time_zone;

And they both return SYSTEM, which means MySQL configures it's timezone based on a .cnf/.ini file. Problem is that I have no such file. I have went through all the "default locations" MySQL is supposed to read it's configuration from and there was not one file. My machine right now is 9:15 AM (UTC +2) but for some reason MySQL timestamps save at 7:15 AM.

In a fluke I caught this piece of condemning info:

Apparantly the server reads it's configuration from .ini inside a 5.5 version of MySQL. But it didn't exist! Only after I used the "Options file" inside workbench for the first time it created it. Here is where:

Mind you I never used 5.5. I am too young for that (though I have and old installation of WAMP from the days I screwed around with wordpress). I tried to diagnose the situation by trying to find my installed version of MySQL but I got this error:

mysql: [ERROR] Found option without preceding group in config file C:\Program Files\MySQL\MySQL Server 8.0\my.cnf at line 1.
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

Any reason why 8 MySQL server wishes to read .ini files from a 5.5 folder that didn't even exist?


texts:
I have a HEAP table with no indexes (on SQL Server 2012 Std). I need to create a clustered index on an INT column. There are ~1.2 billion rows and the int column is nullable and not unique. I'm struggling to figure out how much space this will take up in the filegroup. Could someone advise please?

Edit: I'll sort in the tempdb by the way


texts:
I have a database where column names are all camel cased. No problems encountered.
Will that be a problem under some scenarios, as per recent versions of MySQL, Postgres?


texts:
I have a table where one of the columns is an enum.

mysql> SELECT COLUMN_TYPE FROM information_schema.columns WHERE [...];
+--------------------------------------------------------------+
| COLUMN_TYPE                                                  |
+--------------------------------------------------------------+
| enum('1 DAY','1 WEEK','1 MONTH','3 MONTH','1 YEAR','2 YEAR') |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

I want to use these values in a subsequent query, something like this:

mysql> SELECT LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 DAY)), 2, 0) A,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 WEEK)), 2, 0) B,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, 0) C,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 YEAR)), 2, 0) D,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 2 YEAR)), 2, 0) E,
              YEAR(DATE_SUB(NOW(), INTERVAL 1 DAY)) F,
              YEAR(DATE_SUB(NOW(), INTERVAL 1 WEEK)) G,
              YEAR (DATE_SUB(NOW(), INTERVAL 1 MONTH)) H,
              YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) I,
              YEAR(DATE_SUB(NOW(), INTERVAL 2 YEAR)) J\G
*************************** 1. row ***************************
A: 05
B: 05
C: 04
D: 05
E: 05
F: 2018
G: 2018
H: 2018
I: 2017
J: 2016
1 row in set (0.00 sec)

I run these queries from a script and parsing the result of the first query and creating the second query isn't a massive problem. I was just wondering if someone could suggest a way to do this in one go.


texts: Why am I getting a deadlock for a single UPDATE query?

texts:
I have two processes which execute code like this in parallel:

begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;

This results in deadlocks.

My understanding of what causes deadlocks is like the scenario described in this question, with "interwoven" UPDATE statements updating two different rows in a different order. I don't understand how a single UPDATE statement could result in a deadlock. I'm not able to replicate the deadlock scenario using two parallel psql sessions in my dev environment. My guesses for why I can't replicate it:

I'm misunderstanding my code which creates the deadlock error, and there are actually multiple UPDATE statements in each transaction
The "interwoven" aspect is happening, but "within" the UPDATE statement which covers multiple rows, so it's difficult to replicate.

Is it possible for this single UPDATE to be creating the deadlock?


texts: How do I connect to Oracle Server without installing Oracle Client on client machines?

texts: I am using Microsoft VB.Net to create a Windows application, and would like some suggestions on how to connect to an Oracle database.


texts:
Very simple question -
I have two database roles (Basic, Admin).  Say I explicitly deny the Basic role from deleting from table A and grant that to the Admin role.  If I am a user who is in both roles, can I delete from table A?


texts: How to deal with large offsets in select?

texts:
Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'.

create extension if not exists pgcrypto;
create table jtest (data jsonb not null default '{}');
insert into jtest (data)
select json_build_object('id', gen_random_uuid(), 'key', i)::jsonb
FROM generate_series(1,200000) i;
create index jtest_key on jtest ((data->'key'));

First query (fast):

EXPLAIN ANALYZE
select j.data
from jtest j
order by j.data->'key'
limit 20;

-- "Limit  (cost=0.42..1.43 rows=20 width=74) (actual time=0.023..0.044 rows=20 loops=1)"
-- "  ->  Index Scan using jtest_key on jtest j  (cost=0.42..10150.42 rows=200000 width=74) (actual time=0.023..0.039 rows=20 loops=1)"
-- "Planning time: 0.082 ms"
-- "Execution time: 0.066 ms"

Second query with large offset (slow):

EXPLAIN ANALYZE
select j.data
from jtest j
order by j.data->'key'
offset 100000
limit 20;

-- "Limit  (cost=5075.42..5076.44 rows=20 width=74) (actual time=112.861..112.896 rows=20 loops=1)"
-- "  ->  Index Scan using jtest_key on jtest j  (cost=0.42..10150.42 rows=200000 width=74) (actual time=0.027..106.378 rows=100020 loops=1)"
-- "Planning time: 0.105 ms"
-- "Execution time: 112.931 ms"

In PG docs I found:

  The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.

But they don't say anything about how to deal with large offsets. How I can improve this query? Also is this behaviour (inefficiency for large offsets) common to all RDMS or just PG (using 9.4)?


texts:
I have two tables in Redshift that I am trying to do a join on to get zip code demographics based on a users normalized ip address. By normalized address, I mean that it is concerted to a uniform length string that has the periods stripped out and can be directly compared to one another. For example, this is applied to all ips before any joins are done and is stored in the tables:

lpad(split_part(ip, '.', 1), 3, '0') ||
lpad(split_part(ip, '.', 2), 3, '0') ||
lpad(split_part(ip, '.', 3), 3, '0') ||
lpad(split_part(ip, '.', 4), 3, '0')

so 209.170.151.71 would be transformed into 209170151071.

I have two tables. This first is visitor_details which contains the following:

-----------------------------
| visitor_id |      ip      |
-----------------------------
|      1     | 209170151071 |
|      2     | 123170167071 |
      ...           ...
| 50000000   | 001213020341 |
-----------------------------

The I have a table called geo_ip which has the following structure:

----------------------------------------
|    start_ip |    end_ip      |  zip  |
----------------------------------------
|209170151071 | 209170151071   | 11101 |
|309170151071 | 409170151071   | 11102 |
      ...           ...           ...
|509170151071 | 609170151071   | 11103 |
----------------------------------------

I'm trying to run the following query:

WITH vd AS (
  SELECT visitor_id,
         ip_address as c_ip
  FROM dev.visitor_details
)
SELECT
  visitor_id,
  c_ip,
  g.*
FROM
  vd
JOIN
  dev.geo_ip g
  ON vd.c_ip BETWEEN g.startip and g.endip
LIMIT 500;

The sort keys on geo ip are an interleaved sort key using both startip and endip. The table also doesn't seem to be skewed. However, running the query results in a very long execution time (never completed). Looking at the explain I see the following:

XN Limit  (cost=0.00..245.17 rows=500 width=238)
   ->  XN Nested Loop DS_BCAST_INNER  (cost=0.00..18442148764959.20 rows=37610983146614 width=238)
         Join Filter: ((("inner".startip)::text <= ("outer".ip_address)::text) AND (("inner".endip)::text >= ("outer".ip_address)::text))
         ->  XN Seq Scan on visitor_details  (cost=0.00..596971.20 rows=59697120 width=72)
         ->  XN Seq Scan on geo_ip g  (cost=0.00..56702.71 rows=5670271 width=166)
 ----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----

Can anyone make any suggestions on how to either optimize the query of the table setup to make it run efficiently?


texts: SQL Server cluster instance restarted twice, need help digging through cluster logs

texts:
Scenario: two Windows Server 2012 Standard boxes, housing 4 SQL Server 2012 Standard instances (2 active on each node). Housed in Cisco UCS B200 blades and on a Hitachi HUS 110 SAN (all cluster drives in a raid 10 pool, hooked up by 4x8 gig FC paths)

Friday one instance restarted twice in the morning, and another instance restarted later in the day that I have no logs from. The instance that restarted twice, I have the extended events sessions and cluster logs for, below is an portion of the cluster logs. The logs are word wrapped and best shown in notepad unwrapped.

I realize that the isalive returns false is one of the cluster diagnostic checks and when it fails the Windows Cluster will restart the instance affected. I am having a hard time pinpointing where and what in the cluster logs I should be reading to figure out my exact root causes.

As far as I can tell the two root causes are as follows, from the first run
"Query timeout expired " and the second run "TCP Provider: The specified network name is no longer available"

Now am I posting this here because what I want to know is if I am right in pointing to these lines as the reason for the restart. The "why did they happen and how do I fix them" is questions I'll have, assuming I've pointed out the two right items, but I need to know what I'm trying to fix to attempt to learn to fix it.  I would ask other people at work but I'm the person who built this stuff and sadly the only DBA and the only one with half a clue how it works. I've been reading quite a bit on the cluster log since the crash but I'm spinning my head in circles and am hopeful someone out here in magic internet land can help me out.

I'm trying to avoid posting the whole bunch of logs, and I realize I may need to get a better look into things it's just that they don't look nice here and I'm not sure where else to throw them to point them to someone to read to give me a hand. If someone wants to see more and has an idea of where to throw them just let me know.

Thanks for your time.

First time it happened:

  000010f4.00000b5c::2015/03/13-03:53:59.474 WARN  [RES] SQL Server : [sqsrvres] Failed to retrieve data column. Return code -1
  000010f4.00000b34::2015/03/13-03:54:01.709 ERR   [RES] SQL Server : [sqsrvres] Failure detected, diagnostics heartbeat is lost
  000010f4.00000b34::2015/03/13-03:54:01.709 INFO  [RES] SQL Server : [sqsrvres] IsAlive returns FALSE
  ..
  000010f4.00000b5c::2015/03/13-03:56:59.509 ERR   [RES] SQL Server : [sqsrvres] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)

Second time it happened:

  000002e4.00001f7c::2015/03/13-07:28:36.827 ERR   [RES] SQL Server : [sqsrvres] Failure detected, diagnostics heartbeat is lost
  000002e4.00001f7c::2015/03/13-07:28:36.827 INFO  [RES] SQL Server : [sqsrvres] IsAlive returns FALSE
  000002e4.00001f7c::2015/03/13-07:28:36.827 WARN  [RHS] Resource SQL Server (RPT1) IsAlive has indicated failure.
  000002e4.000015f4::2015/03/13-07:29:02.062 ERR   [RES] SQL Server : [sqsrvres] ODBC Error: [08S01] [Microsoft][SQL Server Native Client 11.0]TCP Provider: The specified network name is no longer available. (64)
  and
  000002e4.000015f4::2015/03/13-07:29:02.062 ERR   [RES] SQL Server : [sqsrvres] ODBC Error: [08S01] [Microsoft][SQL Server Native Client 11.0]Communication link failure (64)


texts: Does Microsoft SQL Server offer an Advisory Locks feature like Postgres?

texts:
In Postgres, the Advisory Locks feature provides for arbitrary application-defined locks to be administered centrally in the database server. These locks are not related to tables or rows or such.

Does Microsoft SQL Server offers something like these Advisory Locks?


texts:
I have database in Vertica on 4 nodes (RedHat OS). It was working until I decided to restart my DB. After nodes were shut down, they can't be UP anymore.

I try to start DB via adminTools: nodes are starting to initialize, but then they shut down. Using last good epoch or admintools -t start_db with --force key don't help.

In logs (dbLog, vertica.log) I see only one type of error:

SP_connect: unable to connect via UNIX socket  to /tmp/4803 (pid=12533):
Error: Connection Refused.

It is enough space in /tmp and iptables service is disabled.

Also I tried to create new database, so it was created and worked well.


texts: What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

texts:
We do have a Percona XtraDB Cluster(Master-Master-Master) setup in one country. Now we want to setup a disaster recovery setup in some other country. Please provide us the best possible ways to do that.

Is it possible to link between PXC(as Master)and  Mysql community version (as Slave)?
Or else can we just add one more node to PXC itself which serves as slave in that cluster??

Percona version:5.6


texts: Automating the service restart from another server in Windows server 2016

texts:
I want to restart the services on Application server from DB server depending on the output of query.
Ex: In DB server i have written one procedure which will generate automated mail if no transaction inserted in one table.
If no transaction inserted from last 3 minutes , i want to restart the dependent service which is on another app server


texts:
I have the following function for geocoding, it works fine without the EXCEPTION WHEN OTHERS THEN part. However, I want the loop continue in case if there is an exception. So I am trying to do this by using EXCEPTION WHEN OTHERS THEN
CREATE OR REPLACE FUNCTION mygetcounty2() RETURNS void LANGUAGE PLPGSQL AS $$
DECLARE idVariable uuid;
begin
FOR i IN 1..100 LOOP

SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1;

UPDATE address_table set county =
(select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) ))
where id = idVariable;
EXCEPTION WHEN OTHERS THEN
     UPDATE address_table set parse_address_error = true where id = idVariable;
END LOOP;
return;
end;
$$;

But I am getting the following message when trying to create the function
ERROR:  syntax error at or near "EXCEPTION"
LINE 15: EXCEPTION WHEN OTHERS THEN
         ^
SQL state: 42601
Character: 511

Any idea what I am doing wrong?
EDIT:
I added a new begin, end block inside the loop. I no longer got an error when creating the function.
Create or replace function mygetcounty2()
returns void
language plpgsql
as
$$
DECLARE idVariable uuid;
begin
FOR i IN 1..100 LOOP

begin

SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1;

UPDATE address_table set county =
(select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) ))
where id = idVariable;
EXCEPTION WHEN OTHERS THEN
      raise notice 'oops %', sqlstate;
     UPDATE address_table set parse_address_error = true where  id = idVariable;

    end;
END LOOP;
return;
end;
$$;


texts: SSMS SQL Server 2014 - Selecting BackupSets for a Restore takes a long time

texts:
I'm having an issue with SSMS SQL Server 2014 (before and after SP1).

When I go and select 'restore database' from the server and select a database, it just sits there and spin and spins. More than 24 hours with no results during one initial test.

Test's I've done:

Tested against the same database using both SSMS 2008 R2 and 2014/SP1 both on the server and on my laptop.

Using SSMS SQL Server 2014:

7 days in MSDB backupfile 1.9 mil records didn't finish in the time I took lunch.

After purging...

3 days in MSDB backupfile 767k records took 1 minute 31 seconds.

Using SSMS SQL Server 2008 R2:

90 days in msdb dbo.backupfile 6.7 mil records was instant and finished loading in less than 3 seconds.

I ran a trace while testing using both versions against both databases...

When SSMS 2014 was executing the trace files would grow to almost 500MB...
When SSMS 2008R2 was executing the trace file grew to 260k

I've also applied various indexes to the msdb database from other posts online. It helped a little when the records were reduced, but not enough to the desired effect.

So, in conclusion I think it might be 2014 SSMS that is the issue...thoughts?


texts: Granting access to users to run specific SQL jobs that runs SSIS packages

texts: We need to grant a couple of users access to run only specific SQL jobs that runs SSIS packages. Is there a solution that can be used to accomplish this?


texts: Character varying index overhead & length limit

texts:
I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million.

I have read about B-Trees, but I can't determine how much disk space the index will consume or if such large-sized values will prevent the index from functioning performantly.

What are the impacts upon disk space and time of a B-Tree index on character varying columns with such potentially large lengths?


texts: MySQL - Truncated incorrect datetime value

texts:
I need to migrate some data from between to tables. The first has a varchar(255) field where a date is stored. I want to insert that date in a timestamp field in the second table.
The varchar field store the value in this ISO 8601 format: 2020-09-24T15:08:07+02:00.
I tried different conversions using STR_TO_DATE and casts but I always get the same error:
Truncated incorrect datetime value: '2020-06-01T09:38:08+02:00'
On selecting the value seems to be converted correctly:
SELECT STR_TO_DATE(varcharDate,'%Y-%m-%dT%H:%i:%s') = 2020-06-01 09:38:08


texts:
I have looked into sys.syscachedobjects table. I found some records have an object type of parsetree.

Can anyone tell me in detail what a parsetree is? Is this a data structure used by SQL Server, and can it help simplify my job as a DBA?


texts: T-SQL for Query that can be done in SQL. What do they want?

texts:
We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId...)
I am going over a book exercise that asks for a T-SQL statement to find
all vendors with at least one invoice. But I am confused, since this can be perfectly done with standard SQL:

SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
GROUP BY VendorName having Count(InvoiceId) > = 1

So, what could they be asking? Should I come up with a procedure to select all vendors with more than n invoices; n= 0,1,2 ?

If so, why is this not working for the general case of n invoices:

 CREATE Procedure InvoiceCount @NumberofInvoices Int
AS
BEGIN
SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
WHERE Count(InvoiceId) > = @Number of Invoices
EXEC InvoiceCount @Number of Invoices = n

Why doesn't this work?
  Thank you.


texts: Auto Update Statistics threshold SQL Server 2016

texts:
since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update statistics with fullscan. With this new behavior the auto update statistics runs after a smaller modification rate and uses only small sample rate. Until our maintenance job will update statistics again with fullscan next time, the auto statistics update runs and resets the ‘fullscan’ results. This could cause a performance problem in our application.
Is there any way to use the old behavior with auto statistics update without going back to a lower compatibility level?


texts: SQL Server 2017: How do Batch Mode Adaptive Joins work?

texts:
Batch Mode Adaptive Joins are part of a family of features in the 2017 query processor that consists of:

Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback

So how do Adaptive Joins work?


texts: Retrieve Data that do not exist in the primary table, exist in the secondary only

texts:
Problem:
I would like to retrieve the value that do not exist in the list table b but it exist in table a.

I don't know how to do it.

Please remember that the data will change very often.

SELECT [id]
      ,[name]
      ,[email]
      ,[phone]
      ,[cellphone]
      ,[none]
  FROM [test].[dbo].[datatable]

SELECT [id]
      ,[name]
      ,[email]
      ,[phone]
      ,[cellphone]
      ,[none]
  FROM [test].[dbo].[datatable2]


texts: How performant is finding a substring in database?

texts:
To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item.

For example:

I have a request to /animals/dog/darktheme/. The category is 'animals', thus the category_url in the mysql DB is /animals/.

SELECT DISTINCT category_url FROM items
    WHERE '/animals/dog/darktheme/' LIKE CONCAT(category_url,'%');

reference

This query will have to run for literally every page load of my site. I'm still in the planning stage, thus can change my design if need be.

Does this kind of query perform reasonably well? Let's say there's 10,000 items and 250 different category_urls which are all like /animals/, /birds/, /reptiles/, etc.

Any tips on optimizing performance for this kind of query, or any better ways to do it, from the database/query perspective?


texts: Need help achieving HAProxy active/passive configuration for mysql

texts:
I'm using HAProxy for mysql database backends. The database had to be master-slave. The goal is to point all mysql clients at the proxy and for all requests to go to mysql server #1 and only go to mysql server #2 if #1 is down.

There is an understanding and acceptance that when we are failed over to node 2, the database will be read only.

One thing I tried was setting the weight of node 1 to 256 and this kind of works but I don't want one out of 256 database calls going to node2; I need all calls to get directed to node1 unless node1 is actually down.


texts: SSMS 2012 intellisense is not working on one of the servers?

texts:
I dont know if this question is asked - I found similar question - How to enable intellisense, but my case is little different.

We have 2 SQL Servers 2012 - one is in the other room, other is in our Data Center.

When we write a query on our "next door" SQL Server intellisense working perfectly.

When we write a query on out DC SQL Server intellisense is not wokring - in a new window it works from time to time and does not underline wrong table names. In a window with big script does not working at all.

The DC server is newer version. SSMS lacks SP.

What can cause such issue? Any ideas?


texts: Transaction Logs: How much log data generated (hourly or daily)?

texts:
I am trying to find out the amount of transaction-log data generated by SQL Server 2016 during every hour or every day. By "data generated" I mean how much data (in bytes/KBs/etc) was written to disk every hour (or every day).
Is there a way to find this out?
Our database is in FULL recovery mode and we do have regular transaction log backups. So, I am on the opinion that querying the backup metadata in msdb may help to achieve that. Does it work ? Will it give me correct and reliable results?
Second option will probably be to look at the amount of read/write IO that is happening to the transaction log files. Can this one work? If yes, how can I do that? Are there any SQL Server DMVs providing such information? What about Windows tools? (such as Windows performance monitors)?
If possible at all, I would prefer the second option above because it won't require having transaction log backups. Therefore, it can be used even with SIMPLE recovery model databases. So, my question is, is it possible?
Are there any other alternatives? Such as SQL Server tools or views that are readily available?
Please note, I need this data because we are trying to estimate the amount of network IO that will be required if we create a (near) real-time replication of our databases in the cloud. So, I thought we should somehow measure the amount of IO attributed to the transaction log files. Is my assumption correct that the required IO will be equal to the amount of Io to the transaction log files? Is this how SQL Server transaction replication work ? (i.e. by sending VLFs to the replicated site)?


texts: Table-Valued Functions from Linked Server

texts:
Why does when I try to run:

SELECT * FROM [servername].master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK)

I get error:

Msg 195, Level 15, State 15, Line 45
'servername.master.sys.dm_os_volume_stats' is not a recognized function name.

And when I run it in the server (ex: servername), I get different error:

SELECT * FROM master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK)

Msg 317, Level 16, State 1, Line 1
Table-valued function 'dm_os_volume_stats' cannot have a column alias.

But it gets no error if I removed the AS btb (NOLOCK)

My goal is to query from a linked server and get free disk space for each server (using union). Below is part of the query:

FROM [servername].[master].sys.master_files AS f WITH(NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

But Im still looking for a solution for the issue regarding TVFs.


texts: Is there a way to get instance_id from msdb.dbo.sysjobhistory during the execution of a SQL Job step?

texts:
I'm writing a logging process that will be stored in tables so that the information can be easily referenced and reported. Most of the structure already exists in the system tables for the SQL jobs (msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, etc); however, I want more detail for each execution of a package, so I'm implementing a logging system that will keep track of each task in the package. Basically, for each task (Data Flow, Execute SQL Task, etc), a detailed, custom description of what has occurred will be stored. More specifically, in the event of a failure, the name of the task that failed and the reason for the failure can be quickly determined.

Tying each log record to the ID of the job and step is not an issue, those already exist and are easily captured. The instance_id generated in msdb.dbo.sysjobhistory, however, does not exist until after the execution of the step. Getting that ID doesn't seem possible during the execution of the package.

By the way, I tested when a record is inserted into msdb.dbo.sysjobhistory by creating a job and running a single step with the following T-SQL code:

WAITFOR DELAY '00:01:00'

The goal was to see when the instance_id is generated and inserted into msdb.dbo.sysjobhistory, which occurred after the step had completed.


texts:
At our company we have a very large MySQL server with almost 1.000 databases with a total size of 500 GB. Every customer has is own database. And each database has about 120-125 tables!

In the past we made backups with mysqldump. The server is very slow while creating backups and it almost takes 5 hours to take backups. The databases have for 99% Innodb tables.

I understand taking backups of a lot of data takes a lot of time. But what is the best solution to take backups? The problem is that our customers are working 24/7 with our webapplications. The system is very slow while taking backups.


texts: Is there a way that we could backup the database in number of smaller parts or change the backup path?

texts:
Regards to this question that I had asked a day ago I want to know if there is any way that I could take the database backup in the number of smaller parts.

Or
If there is any way that I could change the database backup path to my local drive.

I am connecting to remote through Microsoft SQL Server Management Studio 2017.


texts: Insert data into a column where other columns have constraints

texts:
I want to insert data into the zipcode column of the employee3 table.  SQL says write - INSERT INTO Employee3 (ZipCode) VALUES (28279);, however I keep getting this error message:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EMPLOYEEID', table
'AdventureWorks2012.dbo.EMPLOYEE3'; column does not allow nulls.

INSERT fails.

I understand other columns have "NOT NULL" CONSTRAINTS, but I only want to INSERT data to the ZipCode column. Is there anyway around having to update the other columns?


texts: Oracle11g - Convert alpha characters to numeric in select

texts:
I am only able to convert alpha characters to numeric by nesting REPLACE. Is there a better way to handle this?

Desired conversion:

a - 1
A - 1
b - 2
B - 2
c - 3
C - 3
...
z - 26
Z - 26

Example:

select  '1a2b3c4d5e' as "before",
        REPLACE(REPLACE('1a2b3c4d5e','a','1'),'b','2') as "after"
  from dual

Output:

before      after
1a2b3c4d5e  11223c4d5e

I'm hoping there is an alternative, as none seemed to exist here. Maybe there is a clever way to use REGEXP_REPLACE?


texts: creating trigger which will insert into table from view

texts:
I have view LIVE_MATCHES (home,away,comp_name) and
table MATCHES (priority ,sport,sub_list)

I need to create a trigger that inserts in TABLE from VIEW after the view is updated or inserted (doesn't matter) and to check that unique column (sub_list) in table Matches isn't already inserted. Only need to insert from view is comp_name, sport and priority are fixed variables.

 CREATE OR REPLACE TRIGGER LIVE_MATCHES_TO_MATCHES
        instead of insert ON LIVE_MATCHES
       for each row
              declare
       p_priority number:= 1;
       p_sport number:=0;
       begin
         insert into MATCHES (sub_list , priority , sport, created)
         select :old.comp_name , p_priority, p_sport,sysdate
         from dual
          where not exists (
         select 1 from MATCHES
        where sub_list = :old.comp_name);
       end;

I edited trigger so it is compiled now without errors but it does not insert into table MATCHES.

What am I doing wrong in this trigger.


texts: How does SSMS get a list of files in a backup?

texts: When restoring a database that has multiple files, SSMS gives you the option to restore as other paths and file names. How does it retrieve the list of files from the backup? Can I do that in a SQL script? C#? PS?


texts: Data Consistency Between Two MySQL Databases Running Different Versions

texts:
I need to migrate an old web application using old PHP and MySQL versions.

I am intending to start by migrating the code that interacts with the database to a new server with the latest versions and allow the old server to use an API in the new server. Ideally I would like to have the data consistent between the database in the new server and the one in the old server.

It seems that replication only works only when the master is only one version behind the slave. In this case the difference is a way more than one version. I also want changes to the database on the new server to reflect on the database on the old server.

Are there any tools/techniques to help with this situation other than ensuring the data consistency programmatically with cron jobs?


texts: What does "cleaning up" mean in MySQL InnoDB transaction status and how to prevent it?

texts:
In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load:

> SHOW ENGINE innodb STATUS;
...
------------
TRANSACTIONS
------------
...
---TRANSACTION 70150348714, not started
MySQL thread id 132590, OS thread handle 0x2b2a712ca700, query id 10842420427 <ip> <user> cleaning up
...

The only information I have found is:

  The thread has processed one command and is preparing to free memory and reset certain state variables.

https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

And this question comment mentions:

  You may need to fine tune some memory related parameters if you see this often in your instance.

Many transactions are getting stuck in the "cleaning up" state - what could cause that?

What could be the actual problem? Why are so many transactions in "cleaning up" state? What memory parameters can I fine tune to prevent it?

Any advice will be greatly appreciated.


texts: Are there any security implications with enabling "Data Access" on a sever?

texts:
I'm trying to run an OPENQUERY statement within a local server but am getting the following error:

  Server 'MyLocalServer' is not configured for DATA ACCESS.

If I set MyLocalServer's Data Access option to true, what are the potential security implications?

Also can this affect anything with an AlawaysOn AG that MyLocalServer is a part of?


texts: Full-text upgrade option to select during SQL Server in-place upgrade

texts: What Full-text upgrade option should be selected ideally while doing SQL Server in-place upgrade? The Full-text Upgrade screen provides three Full-Text upgrade options (import, rebuild and reset). What should be selected ideally and what are the pros and cons of each option? Also how do I know if full text is currently used or not?


texts: Summarise data into count per hour, average per day

texts:
I need to summarise a dataset to show the hourly totals per day. The dataset contains travel card transactions for a period of 4 weeks. I would like to see the total transactions per hour (00:00-01:00, 01:00-02:00,...23:00-00:00) for each of the 28 days.
If this is achievable I would hope to resample this to show the average transactions per hour for weekday and weekend, respectively.
The datetime field is datatype timestamp without timezone i.e. 'YYYY-MM-DD HH:MM:SS'.
Is this something PostgreSQL can accomplish?


texts: Update a table faster than an INNER JOIN

texts:
I have a SQL Server 2012 stored procedure with an inner join that takes 7,387ms to execute (I saw it in Activity Monitor):

[...]
declare @childrenIds as dbo.CodeIdList;

[...]
Update
    dbo.Code
set
    dbo.Code.CommissioningFlag = 21
From
    dbo.Code
INNER JOIN
    @childrenIds c
ON
    dbo.Code.CodeId =  c.CodeId

CodeIdList is:

CREATE TYPE [dbo].[CodeIdList] AS TABLE (
    [CodeId] INT NULL
);

Is there a faster way to update the Code table than using an INNER JOIN?

I'm not a dba so I don't know if you need more details about my question. If you need them, please ask.

I have run sp_helpindex on Code table with these results:

index_name     | index_description
PK_CODE        | clustered, unique, primary key located on PRIMARY   | CodeId
UC_CODE_SERIAL | nonclustered, unique, unique key located on PRIMARY | Serial

Code table creation script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ...]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

At this moment there are 1.006.896 rows in the Code table.

Execution plan:

XML version at http://pastebin.com/9yXsRfva

SQL Server version info:

Microsoft SQL Server Management Studio                      11.0.6020.0 Herramientas cliente de Microsoft Analysis Services         11.0.6020.0 Microsoft Data Access Components (MDAC)                      6.1.7601.17514
Microsoft MSXML                                              3.0 4.0 6.0
Microsoft Internet Explorer                                  9.11.9600.18314
Microsoft .NET Framework                                     4.0.30319.42000
Sistema operativo                                            6.1.7601

Select @@VERSION:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 6.1  (Build 7601: Service Pack 1)


texts:
DB:
Oracle RAC Database Release 11.2.0.4.0

I want to shrink SYS.AUD$ table. Some records were deleted but table still has about 25GB.
I read that it is necessary to move AUD$ segment to another tablespace using dbms_audit_mgmt package.
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDTBS');
end;

Then:
alter table sys.aud$ enable row movement;

alter table sys.aud$ shrink space cascade;

alter table sys.aud$ disable row movement;

My questions are:

Is this "dbms_audit_mgmt.set_audit_trail_location" an ONLINE operation?
After that, should i move aud$ table back to SYSTEM tablespace?

Thank you guys


texts: BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1

texts:
I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. I know that calling xp_cmdshell is not best practice but please don't comment on this.
After investigation I found that it only fails (as expected) when FIRSTROW = 1, but I need a header inside the file.
Table:
CREATE TABLE [dbo].[test_table](
    [id] [int] NULL,
    [title] [varchar](10) NULL,
    [val] [int] NULL
)

Format file:
12.0
3
1       SQLCHAR              0       4       ";"    1     "id"             ""
2       SQLCHAR              0       10      ";"    2     "title"          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR              0       4       "\r\n"     3     "val"            ""

Data file:
1,first,0
2,second,2

Bulk insert:
DECLARE @cmd VARCHAR(4000);
SET @cmd = 'sqlcmd -b -S <servername> -Q "set nocount on; set dateformat dmy; bulk insert [test_db].[dbo].[test_insert] from ''C:\temp\test_table.csv'' with ( DATAFILETYPE = ''char'', TABLOCK, MAXERRORS = 1000, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\r\n'', BATCHSIZE = 100000, FORMATFILE = ''C:\temp\test_table.txt'', FIRSTROW = 2 );"';
EXEC xp_cmdshell @cmd;


texts: mongodb replication node stuck at “STARTUP2” with optimeDate as 1970

texts:
i have just setup replica sets with three nodes . the third node is stuck at stateStr STARTUP2 with "optimeDate" : ISODate("1970-01-01T00:00:00Z"). However its showing no error message. Is this alright. On primary rs.status() yeilds

{
    "set" : "qdit",
    "date" : ISODate("2013-06-18T22:49:41Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "q.example.com:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 2940,
            "optime" : {
                "t" : 1371593311,
                "i" : 1
            },
            "optimeDate" : ISODate("2013-06-18T22:08:31Z"),
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "q1.example.com:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 457,
            "optime" : {
                "t" : 1371593311,
                "i" : 1
            },
            "optimeDate" : ISODate("2013-06-18T22:08:31Z"),
            "lastHeartbeat" : ISODate("2013-06-18T22:49:40Z"),
            "lastHeartbeatRecv" : ISODate("2013-06-18T22:49:40Z"),
            "pingMs" : 0,
            "syncingTo" : "twitnot.es:27017"
        },
        {
            "_id" : 2,
            "name" : "q2.example.com:27017",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 300,
            "optime" : {
                "t" : 0,
                "i" : 0
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2013-06-18T22:49:40Z"),
            "lastHeartbeatRecv" : ISODate("2013-06-18T22:49:41Z"),
            "pingMs" : 7
        }
    ],
    "ok" : 1
}

also

db.printSlaveReplicationInfo() on yields

source:   qdit1.queuedit.com:27017
     syncedTo: Tue Jun 18 2013 22:08:31 GMT+0000 (UTC)
         = 2894 secs ago (0.8hrs)
source:   qdit2.queuedit.com:27017
     syncedTo: Thu Jan 01 1970 00:00:00 GMT+0000 (UTC)
         = 1371596205 secs ago (380998.95hrs)

Is this alright. Also how can i test my replication especially the third node


texts: CmdExec Powershell Job Step Escape Double Quotes

texts:
I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with getting the escape characters right, or tweaking my code.

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath `"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip`" -CompressionLevel Optimal"

I've also tried it using \ as the escape character

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath \"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip\" -CompressionLevel Optimal"

I tried running this script (with \ escape characters) in the command line and it ran fine. Thanks again for any help.


texts: If my Data, Log and Tempdb drives are on the same LUN, are they using 1 physical drive?

texts: My SQL Server Data, Log and TempDB resides on different logical drives.  But when I check the drive properties under Hardware tab, their location are located on the same Port, Bus, Target ID and LUN.  Does this mean they are physically residing on the same disk ?  How can I check LUN properties properly ?   We are using VM on SAN.


texts:
I've been running into some problems with MySQL Workbench that I'm not sure will be able to be fixed (namely, what appears to be an old bug that may have resurfaced), so I'd like to be able to find a good backup to it in the event that I can't use it. I've sifted through several suggested alternatives, such as Navicat (Navicat is not free for business use, it only has a 30-day trial), but none of them have satisfactorily met my current requirements.

What I need:

Linux compatible (preferably Linux native, I'm using Ubuntu)
Able to connect to remote databases via SSH tunneling
Free and/or Open Source (I currently don't have the funds to shell out $100+ for a product and would rather support a good Open Source project, anyway)
An interface at least as good a Workbench's (it could just be because I'm used to it, but I like its interface, very clean and rather intuitive tabbed interface; doesn't have to be identical, though)
Supports MySQL 5+

While it's nice to have, I don't need the extra data modeling stuff that Workbench has. I also don't need fancy GUI stuff (query designers and the like). A way to view the data in an orderly manner and a place to edit queries is really all I need.

I'm currently checking out HeidiSQL, but it reminds me of something I used to use when I was running Win9x/Win2k, only runs through WINE, has crashed on me once already, and just overall feels clunky, though I do like its easy ability to dump a database or do changes to multiple tables.


texts: Behaviour of Bulk Insert in pgsql or any Database

texts:
I'm using PostgreSQL 9.2.4.

I'm working with multiple tables with large number of data.

I have two tables Table_One which  has one of the field with varchar(250) and Table_Two with one of the field varchar(200).

Lets say, Table_One has 20 records in which 2 records has field with 250 characters.

I'm doing a bulk insert by some query like below

insert into Table_Two(id, INSTANCEwithVarchar, MIN, MAX)(SELECT id, INSTANCEwithVarchar, MIN, MAX from Table_One)

I will get an exception while inserting the record of Table_One which has varchar 250.

My question is,

Will Table_Two has 18 records inserted?

Or bulk insert into Table_Two will fail completely even if one record among the 20 gets violate the constraints?

If not, say if Table_Two has 18 records added, then in what order the failure will happen? if the first record of Table_One has varchar 250, then will the whole process fail or pgsql itself ignores failures and add remaining records successfully?

(Using PostgreSQL 9.2)


texts: Truncate parent table in Oracle when child table is empty

texts:
Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in

 ORA-02449: unique/primary keys in table referenced by foreign keys

Is there a way to hint to the DBMS that child is empty, so that the foreign key constraint doesn't need to be disabled?


texts: Lock timeouts on a table with two keys, where only one connection writes for a given value of the first key at a time

texts:
I have a table user_rollup_message_list that is keyed by (user_id, message_reference).  It is a write-heavy table, and gets enough writes that I get many lock timeouts, with a timeout of 50 seconds, on writes each day.  It is possible that the writes are starved because of reads (I don't actually know how to profile this), but this is unlikely because MySQL prioritizes writes against reads.

Our engine is InnoDB, which does row-level locking. As I understand, the reason locks are needed on INSERT (we are actually using INSERT IGNORE if that makes a difference) is to make sure we won't duplicate a unique key, either against an existing row or one being added concurrently.

My table has two unique keys:

PRIMARY KEY (`id`,`user_id`),
UNIQUE KEY `rollup_message_list` (`user_id`,`message_reference`),

Which refer to these columns:

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`message_reference` varchar(80) DEFAULT NULL,

so the primary key should not cause timeouts.  However in my Java application I get many per day:

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

As for the other unique key, an aspect of the application is that for a fixed user_id, writes should actually be very sparse. If the MySQL server locks intelligently, it should be able to avoid deadlocks entirely, but perhaps inserts are unwisely locking the entire table.

How can I diagnose or improve this?  I believe with the right query or table design, it should be possible to get the locks to the queries that need them, based on the application behavior.


texts: Best db setup to accomplish yes/no list of clients for daily calls

texts:
I will preface this by saying I am a relative novice to Access but I have managed to develop some basic databases. I have some working knowledge of expressions and macros but VBA is still a bit of a mystery to me.

I have a problem that is simple to visualise mentally but seems to be quite a challenge to implement in a database (at least for me :)).
I work in a program which involves calling clients on a daily basis who are typically elderly and living alone who have various health issues which render them potentially vulnerable to sudden adverse health events. We need to improve our management of our daily call roster which involves determining each day whether clients need to be called or not based on two basic conditions:

Pre-selected regular days the client has established not to be called. (e.g. no call Mon, Wed, Fri etc.)
Ad-hoc changes to call days (going into hospital, away on holiday from date to date etc.).

I initially started the project by incorporating a multivalue field in the Clients table which makes it easy to select or deselect days, but when it comes to developing an expression to filter the list for any particular day it is proving to be a challenge.

I then considered a "DaysOfWeek" table which could be referenced by a junction table "ClientCallDays". The challenge is in being able to resolve any resulting query to only one result for each client each day.

I am now considering including yes/no fields for each day of the week, which is easy then to deal with in a form or IIF query with OR operators nested (e.g. "If today falls between dateX and dateY or is selected in the Yes/No field corresponding to Today, then don't call, otherwise call") but may violate normalization.


texts: Setting Select Permission on 100+ tables in a database

texts:
I got the 'Select permission denied' error for some of my tables in a database.

In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties > Permissions > Select the User/Role and tick the Select Permission in the 'Explicit' tab below.

But I want to do that for over a 100 tables. Is there a better way to do it?


texts: Update Statement takes forever to complete

texts:
I have table around 1250000 records, and 80 columns

Table Structure

CREATE TABLE [dbo].[Table] (
[Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] NULL,
[Column4] [int] NULL, [Column5] [int] NULL, [Column6] [int] NULL, [Column7] [int] NULL, [Column8] [int] NULL,
[Column9] [int] NULL, [Column10] [varchar](20) NULL, [Column11] [datetime] NULL, [Column12] [int] NULL,
[Column13] [money] NULL, [Column14] [money] NULL, [Column15] [varchar](40) NULL, [Column16] [int] NULL,
[Column17] [int] NULL, [Column18] [varchar](20) NULL, [Column19] [varchar](50) NULL, [Column20] [int] NULL,
[Column21] [int] NULL, [Column22] [int] NULL, [Column23] [int] NULL, [Column24] [int] NULL,
[Column25Date] [datetime] NULL, [Column26] [int] NULL, [Column27] [int] NULL, [Column28] [int] NULL,
[Column29] [int] NULL, [Column30] [int] NULL, [Column75] [varchar](50) NULL, [Column31] [int] NULL,
[Column32] [varchar](15) NULL, [Column33] [int] NULL, [Column34] [money] NULL,
[Column35] [dbo].[T_Boolean2] NOT NULL, [Column36] [money] NULL, [Column37] [money] NULL,
[Column38] [int] NULL, [Column39] [int] NULL, [Column40] [varchar](255) NULL, [Column41PrintDate] [datetime] NULL,
[Column42PostDate] [datetime] NULL, [Column43FirstName] [varchar](30) NULL, [Column44LastName] [varchar](30) NULL,
[Column45Email] [varchar](50) NULL, [Column46PrimaryPhone] [varchar](10) NULL,
[Column47SecondaryPhone] [varchar](10) NULL, [Column48PrimaryPhoneType] [smallint] NULL,
[Column49SecondaryPhoneType] [smallint] NULL, [Column50PrimaryPhoneType] [smallint] NULL,
[Column51SecondaryPhoneType] [smallint] NULL, [Column52EndDate] [datetime] NULL,
[Column53] [dbo].[T_Boolean2] NOT NULL, [Column54] [int] NULL, [InsertTimeStamp] [datetime] NOT NULL,
[UpdateTimeStamp] [datetime] NULL, [Column55ID] [int] NULL, [Column56] [int] NULL, [Column57] [int] NULL,
[Column58] [int] NULL, [Column59] [money] NULL, [Column60] [money] NULL, [Column61] [money] NOT NULL,
[Column62] [money] NOT NULL, [Column63] [varchar](30) NULL, [Column64] [varchar](30) NULL,
[Column65] [varchar](10) NULL, [Column66] [varchar](6) NULL, [Column67] [varchar](10) NULL,
[Column68] [varchar](35) NULL, [Column69Date] [datetime] NULL, [Column70Date] [datetime] NULL,
[Column71Date] [datetime] NULL, [Column72] [varchar](255) NULL, [Column73PIN] [varchar](6) NULL,
[Column74Date] [datetime] NULL, [Column76Date] [datetime] NULL,
CONSTRAINT [XPKColumn1] PRIMARY KEY CLUSTERED (
[Column1] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

It has 2 Update triggers also to perform background calculations
which will update same Table when status changes and some other conditions

When I Update one is its column with normal where condition it is running more than 1 hour but still couldn't able to update the records so i am forced to kill the statement

Update Table Column5 = 12345 where Column5 = 6789

It has to update around 35000 records

Table does have proper indexes with this column and i manually update  statistics of the table but still taking too much time to update

Around 10 indexes are there for table

Then I tried update in 2 batches it updated but took very time

I tried to update other column of same table it took few seconds to update

Update Table Column3 = 6789 where Column3 = 12345

how i can improve update statement queries speed fast and using above simple logic

Please suggest any solution,

Thanks in advance


texts: retrun multiple functions from dynamic query

texts:
I need to return multiple fields from dynamic query.I am referring this link below code what I am doing so far

CREATE OR REPLACE FUNCTION get_names(ind_zone text,dst text,nmind text,ind text,implst text,rsvr text,n34b text,prjapr text)
RETURNS   SETOF RECORD AS
$BODY$
DECLARE
sql text;
BEGIN
sql:='select indzone,dstr,nmindar,plno,pltar,nmalt from plotboundary where indzone='|| quote_literal(ind_zone)||' AND dstr='||quote_literal(dst)||' AND nmindar='||quote_literal(nmind)||' AND indx='|| quote_literal(ind);
IF implst IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' implst = ' || quote_literal(implst);
END IF;
IF rsvr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' rsvr = ' || quote_literal(rsvr);
END IF;
IF n34b IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' n34b = ' || quote_literal(n34b);
END IF;
IF prjapr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' prjapr = ' || quote_literal(prjapr);
END IF;
RETURN QUERY EXECUTE sql;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_names( text,
 text, text, text, text, text, text, text)
  OWNER TO postgres;

calling function like

 select * from get_names('Tumakuru Zone','Tumakuru','Vasanthanarasapura 2nd Phase','Allotted','Yet to Start',null,null,null)
    AS t(indzone text,dstr text,nmindar text,plno text,pltar text,nmalt text);

I am getting error like

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying(20) does not match expected type text in column 1.
CONTEXT:  PL/pgSQL function get_names(text,text,text,text,text,text,text,text) line 22 at RETURN QUERY


texts: I am trying to delete the old files from fast_recovery_area folder inside Oracle on Windows as I am running out of space. The database is not in archivelog mode. I am connecting to RMAN and using the following command. My query is - can I delete those files directly from disk?


texts:
I have this query:

SELECT table1.foo, table2.bar, table3.abc, table4.xyz
  FROM table1 table1, table2 table2, table3 table3, table4 table4
 WHERE table1.id = table2.id
  AND table1.id2 = table3.id2
  AND table1.id3 = table4.id3
  AND (table1.foo BETWEEN 10 AND 30)

I'd like to get additional rows in results, with data in table1 that doesn't have matching records in table2 and table3, but still only those that match data in table4.
How can I write this?
I think I need two left joins and keep the third normal join.

Tables:

TABLE1:
 id (references TABLE2),
 id2 (references TABLE3),
 id3 (references TABLE4),
 foo

TABLE2
 id,
 bar

TABLE3
 id,
 abc

TABLE4
 id,
 xyz


texts: Implementing a function written for Postgres 9.4 in Postgres 8.4

texts:
I have a simple function (tested on Postgres 9.4) that I need to implement on Postgres 8.4. The error I get is that function format does not exist. I've been trying to find resources but 8.4 is 6 years old at this stage and it's hard to find relevant information apart from the docs!

CREATE OR REPLACE FUNCTION create_table()
  RETURNS void AS
$BODY$
DECLARE
    rows RECORD;

BEGIN
    FOR rows IN SELECT column as tablename from table
        LOOP
            EXECUTE format('
               CREATE TABLE IF NOT EXISTS %I (
                date date,
                number integer
               )', rows.tablename);

        END LOOP ;
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Here's the error message:

ERROR:  function format(unknown, text) does not exist
LINE 1: SELECT  format('
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any suggestions would be greatly appreciated!

EDIT: I've amended the loop in my function to this:

        LOOP
            EXECUTE 'CREATE TABLE IF NOT EXISTS ' || rows || ' (
                date date,
                number integer
               )', rows.tablename;

        END LOOP ;

However now I'm getting this error on quote_ident():

ERROR:  function quote_ident(record) does not exist


texts: What to have in the main table and what to have in meta_table?

texts:
I'm try to figure out the best scheme for the users table for my software. I'm trying to separate the main data and auxiliary data into 2 table called users and user_meta for the sake of extensibility (something like what Wordpress has done).
Now I need to figure out which data to put into which table. Here is the basic data a user might have.

user_id
email
password
user_type -> int
register_date
phone
name
image -> url
description -> text (might contain html)
... so many other possible attributes for a user which is specific to my software

It would be great if you could help me decide which kind of data belongs to which table.


texts: How do I use window functions ("over") together with crosstab in Postgres?


texts: Mongo import error: exception:read error, or input line too long (max length: 16777216)

texts:
I have a shareded collection which I'm trying to migrate from an existing production Cluster running v 2.4 to a new one running 2.6.

As both clusters are shareded, I'm using mongoexport from the old and mongoimport to the new so the data will go through the config servers.
The way I'm doing it is the call mongoexport process and pipe the results into mongoimport process.

I'm guessing that the export | import process affects the size of the exported json which fails to be imported back to the new cluster.
Is there any way I can avoid having this problem or even ignore some documents that cause this error? (I'd rather lose a few than lost all 8 million records).

Is there a way to go around this?

Thanks in advance


texts: Is there a backup operator role in Microsoft SQL Server 2008?

texts:
I have a Windows Server 2008 with Microsoft SQL Server 2008 and VERITAS NetBackup installed. Basically, this NetBackup requires a valid logon ID into the SQL Server that is powerful enough to do a backup and restoration.

I have created a local ID on the OS called backup01 and assign it to the Backup Operator group. Next, I created a login in SQL Server 2008 with for backup01 with Windows Authentication. As for server roles, I assign it as 'public' and 'sysadmin'. With this, the backup and restoration via NetBackup will always work.

However, our security policy discourage the use of sysadmin or administrator equivalent rights to be assigned to non-administrator users.

In this case, where can I find this backup operator role in Microsoft SQL Server? Or is there any guide or tutorial that walks you through on creating a new role? Thank you!


texts: How are databases able to process queries in parallel while still retaining correct order?

texts: How do databases process queries in parallel (more than one query at a time) while still keeping the correct order (queries are performed in the same order that they are submitted)? Is there a kind of queue structure? thanks.


texts: Business Periods/Weeks to date conversion

texts:
Our current date system divides the business year into 'Periods' which last for 4 weeks. Period one begins on a fixed date, lets say 1st March.

I have a simple table with Weeks and which period they come under. I would like to add another column [Date] with each day in it, effectively multiplying my number of rows by 7. How can I go about calculating these values using an inbuilt calender in SSMS?


texts: Is it more secure to go through a 3rd database to connect two databases using the same login?

texts:
We have the following setup:

Multiple production database containing private data which is used by desktop software
A web database for a public website which needs some data from the private databases
An intermediary database which contains a few views and stored procedures that pull data from the private databases

Currently the website logs in to web database, and the web database connects to intermediary database to pull data or execute stored procedures on the production databases. All the databases are on the same SQL instance, and the entire process uses the same user account.

The user account has full access to the web database and the intermediary database, but can only access specific views and stored procedures of and private database

Is this really more secure than just making the public database connect directly to the private ones?

It seems like the intermediary database is only there to complicates things since the same login is used to access data in all the databases, and it is already limited to just the views / SPs it needs in the private databases. I am hoping to remove it.


texts: Combine column from multiple rows into single row

texts:
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this delimited list from SELECT clause and COALESCE trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.

The data looks like this:

id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy

My results need to look like this:

id  customer_code comments
------------------------------
1   Dilbert        Hard Worker
2   Wally          Lazy

So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.

My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.


texts: Delete records with multiple values for a column

texts:
I am attempting to clean some data from within the AdventureWorks database on SQL Server 2012. Some of the records have multiple values for the Rate field.

I have already found a query for selecting the rows:

select x.*
from t as x
where x.ID
IN (
    select ID
    from t
    group by ID
    having count(Rate) > 1
)

Here is the simplified output:

ID   Rate   City
4    8.62   Minneapolis
4    23.72  Minneapolis
4    29.85  Minneapolis
6    24.00  Redmond
6    28.75  Redmond

Now, I wish to delete the records with multiple values for the Rate field and keep ONLY the highest Rate for each ID. For example:

ID   Rate   City
4    29.85  Minneapolis
6    28.75  Redmond


texts:
what is most important issues and cautious we have to do before upgrade to MySQL 6.0?

How do you do the upgrade? setting up a new slave and do yum upgrade? what is the best way to upgrade with minimal downtime?


texts: Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

texts:
I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with sp_ExecuteSQL. I verified this with the TIME STATISTICS.

My dynamic SQL incorporates a series of closed and re-opened quotes so that values can be dynamically inserted into the statement. Would this make any difference?


texts: Field order in a composite index order with high selectivity and low selectivity fields

texts:
I have a SQL Server table with over 3 billion rows. One of my query takes an extremely long time so I am considering optimizing it. The query looks like this:

SELECT [Enroll_Date]
      ,Count(*) AS [Record #]
      ,Count(Distinct UserID) AS [User #]
  FROM UserTable
  GROUP BY [Enroll_Date]

The [Enroll_Date] is a low selectivity column with less than 50 possible values, while the UserID column is a high selectivity column with more than 200 million distinct values. Based on my research I believe I should create a non-clustered composite index on these two columns, and in theory the high selectivity column should be the first column. But I am not sure in my case, would that work because I am using the low selectivity column in the group by clause.

This table has no clustered index.


texts: PostgreSQL Select value row depending on another value row

texts:
How to select only the value of the column phase that corresponds to the value of the column type?, that way I want to show only the TAXI_SMALL values if the value of type is small_airport, or TAXI_LARGE If the value of type is large_airport as in a sample of my table:

ident
type
aircraft
phase
co2_lto_kg

LFPG
large_airport
AC300
APPROACH
2033.64

LFPG
large_airport
AC300
CLIMB OUT
3279.83

LFPG
large_airport
AC300
TAKE OFF
1281.1932

LFPG
large_airport
AC300
TAXI_LARGE
3675.672

LFPG
large_airport
AC300
TAXI_MEDIUM
2261.952

LFPG
large_airport
AC300
TAXI_SMALL
1413.72

The desired result:

ident
type
aircraft
phase
co2_lto_kg

LFPG
large_airport
AC300
APPROACH
2033.64

LFPG
large_airport
AC300
CLIMB OUT
3279.83

LFPG
large_airport
AC300
TAKE OFF
1281.1932

LFPG
large_airport
AC300
TAXI_LARGE
3675.672

This is the code I've so far and I know that the CASE instance is not at all right...
select
ident, type, aircraft, phase,
avg(co2_by_phase_lto) as co2_lto_kg,
case(
    WHEN t.type ilike 'large'||'%' THEN phase('TAXI_LARGE')
)
from aviation.emissionsco2 as t
where t.aircraft ilike '%'||'300' and
t.rep_airp ilike '%'||'lfpg'
group by ident,phase


texts: Need Help Normalizing a Database for Media Website

texts:
I've created a media website where visitors can watch trailers, view pictures, etc. Along the way, I've found that I've needed additional images for sharing, etc. I went the "easy" route and grew the "Media" table to something that's become somewhat ugly. I can continue to grow the Media table, but I know it's bad design to have a table that has too many columns.
Here's the gist/diagram of the current schema (the Media table is shortened to include only relevant columns, it has about ~40 columns total): https://dbdiagram.io/d/5fbbcf413a78976d7b7d1252
I thought about "peeling" away at the Media table by creating a MediaVideos table as I'd like to have a trailer and also a short clip (that would be available just for registered users). However, I stopped as I quickly realized that If I were going to essentially rearchitecht things, I should do it the right way.
So, I'm thinking of creating two new tables: MediaAssets and MediaAssetTypes. Something like this: https://dbdiagram.io/d/5fbbd2513a78976d7b7d12e8
The issue, I feel, is that means the table would have some columns that wouldn't be used for some assets. Is that ok? For example, I only need the height & width for some images/assets, not all.
I just want to get a second (or more) opinion on this before I essentially tear apart my application to get it right.
Any suggestions & ideas are welcomed!
Thank you!
UPDATE:
After thinking it further and thinking about J.D.'s reply, I've thought of a third scenario where I keep the MediaImages table but add a Height, Width, and ImageTypeId to support images that I need to know the height/width of. And then create a MediaTrailers table with a TrailerTypeId: https://dbdiagram.io/d/5fbc21363a78976d7b7d1d87


texts: Caching impression counts to avoid mysql inserts

texts:
We currently get a lot of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on intervals with a cron.

Is this possible? What happens if 2 people hit at the exact same millisecond? Will the counter still go up twice or will it only increment once? Some more information on how to handle this would be great.