texts: How can I improve performance on a 3 table query?

texts:
I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year).  I need the aggregation down to the minute.  To accomplish this, I'm querying two tables and a view, but it's taking approximately 1 hour per execution so I'm trying to improve its performance.

My Setup

View 1 holds my time intelligence for period reporting.  It's based upon a customized calendar table that uses date as its primary level of aggregation.  This is my primary filter for values, returning 14 rows for a bi-weekly reporting period.

Table 1 is a calendar table, with values to the minute.  It has values for a period of 20 years, so there are in excess of 10.5 million records.  This table provides the level of detail I need for my analysis.

Table 2 is a proprietary table from our RMS vendor and holds the resource utilization records necessary for the analysis.  It resides in a separate database (on the same server) as Table 1 and View 1, and I can't make any changes to it.  For a biweekly period, it will return approximately 350 rows.

My Problem
For a period of time defined through View 1, I need to return all of the rows from Table 1 left joined to Table 2.

My Query:

SELECT t1.calendar_dttm, t2.unit, t2.start_dttm, t2.end_dttm
FROM Table_1 t1
LEFT JOIN Table_2 t2 on  t1.calendar_dttm >= t2.start_dttm
                     and t1.calendar_dttm <=t2.end_dttm
JOIN View_1 v1 on  t1.calendar_dttm >= v1.start_dttm
               and t1.calendar_dttm <= v1.end_dttm
WHERE v1.period_diff = -1

This takes 24.5 mins to return 26855 rows, and eventually I'll need to run it for year (or more) long periods of time.

EDIT:  As I look at the execution plan, I think I need to filter both tables for the reporting period before conducting the joins.  Here's the execution plan:

And here's View_1:

SELECT
c1.[Date ID]
, c1.Date
, c1.DAY
, c1.DayofMonth
, c1.DayofWeek
, c1.Week
, c1.MONTH
, c1.YEAR
, c1.Shift
, c1.DayofPayperiod
, c1.DayofFLSA
, c1.PayPeriod
, c1.paydate
, c1.flsa_end
, FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14)) period_diff
, DATEDIFF(yy,GETDATE(), c1.date) year_cal_diff
, DATEPART(dy,c1.date) doy
, DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) day_offset
, CASE
    when DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) < 0
    then '-'
    else '+'
    end + right('0000'+ REPLACE(DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date),'-',''),4)
    day_offset_label
, CEILING(FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14))/26) year_run_diff
, DATEADD(HH,7,c1.Date) shift_start,DATEADD(s,111599,c1.date) shift_end

FROM Table_Calendar c1

It takes static, custom date data from a table and then applies calculates dynamic period info based upon the date its run.


texts: Rename primary key constraint in Teradata

texts: Does Teradata have a way to rename a primary key constraint, rather than dropping it and recreating it? I'm using SP_RENAME for SQL Server and ALTER TABLE...RENAME CONSTRAINT for Oracle, but I don't know a corresponding command for Teradata.


texts: MySQL - Updating 100m+ rows vs truncating and inserting

texts:
I've got a table in the following format:

id: primary key
foreign_id: foreign key
code_1: int w index
code_2: int with index

I have a file that comes in every week with 200m records in the format:

name (links to the foreign_id above via a separate table lets call it table 2)
code_1
code_2

Table 2 has format:
 - id
 - name

In terms of speed / memory usage, would truncating + inserting the 200m rows be better than updating the 200m rows?

Would it perhaps be better to have table 1 and table2 as a single table and update the rows that way?

Initially the reason for splitting out the table required for the dump (table 1) and the id/name table (table 2) is to allow a truncate and insert -> is this the most performant?


texts: Can PostgreSQL use nulls in its indexes?

texts:
I've been reading this book which says that

  The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this condition.

I recognize that the book is more than 10 years old, but it has already proven quite useful--Using instructions gleaned from its pages, I've sped a query up by a factor of ten.

Further, in running EXPLAIN ANALYZE on a SELECT query, I've found that none of my indexes are being used, even when by all rights, they ought to be.

Thus, my question is:

Supposing there is a table which has a column, whose column definition includes "NOT NULL", and that an index exists which covers this column, would this index be used in a query of that table where the columns is part of the query?

Like:

CREATE TABLE my_table(
a varchar NOT NULL
);

CREATE INDEX ix_my_table ON my_table(a);

SELECT a from my_table;


texts: Mongo assigning user permsions issue on docker

texts:
I'm using docker image mongo and I extend it to create a user and assign permissions on image build with such a line COPY init.sh /docker-entrypoint-initdb.d/

Here is the init.sh:

#!/bin/bash
set -e;

mongo -u mongo -p asdfasdf --authenticationDatabase admin <<-EOJS
    use admin;
    db.createUser({
      user: "mongouser",
      pwd: "asdfasdf",
      roles: []
    });

    db.grantRolesToUser(
      "mongouser",
      [
        "root",
        {role: "readWrite", db: "translations"},
        {role: "readWrite", db: "notifications"}
      ]
    );

    exit;
    EOJS

all, looks good, when I check in the terminal I can see that user exists and has all permissions

root@8f26d6da46a7:/# mongo -u mongo -p asdfasdf  --authenticationDatabase admin
> use admin
switched to db admin
> db.system.users.find();
{ "_id" : "admin.mongo", "userId" : UUID("7197ece0-014c-44ea-a5d9-cbb6ef683582"), "user" : "mongo", "db" : "admin", "credentials" : { "SCRAM-SHA-1" : { "iterationCount" : 10000, "salt" : "hRtMnmboNWoZMK0uzbftpQ==", "storedKey" : "AoFg3PCSQpcT/mcl94w8Kb1C0kg=", "serverKey" : "V1gll09EhlqAnk3kVoWn3s+0X5c=" }, "SCRAM-SHA-256" : { "iterationCount" : 15000, "salt" : "phs+erLhT7CwBHqo9ruK5eN7JO2GVC9t8ovutQ==", "storedKey" : "Rv8CTc/b1hY6xFAdyXjC5nFdvwbvxFzeln065rEMjyI=", "serverKey" : "VkkZ1BNBf5gfduuHMXGQlGgrumMct1nvbC8AEfFJXvw=" } }, "roles" : [ { "role" : "root", "db" : "admin" } ] }
{ "_id" : "admin.mongouser", "userId" : UUID("74ad059c-57de-4a75-93c4-3d3bb00d5a14"), "user" : "mongouser", "db" : "admin", "credentials" : { "SCRAM-SHA-1" : { "iterationCount" : 10000, "salt" : "hh2dgliZhjl/1Is97rwiCg==", "storedKey" : "nkD+K7dbnI1PMks/gae4EuADm+Q=", "serverKey" : "696PKwhA+8Aib6QEfWRxkH0HXgE=" }, "SCRAM-SHA-256" : { "iterationCount" : 15000, "salt" : "4SE8LG8dOP07QJME8dwhTyEicmQtcIlRZjPnsQ==", "storedKey" : "7LPWRkX1U0MVfKfvQ2Pf7fMz7T5MntMV6tS5JfgFTmU=", "serverKey" : "nTk38FwgZPPf9Tlv5/Ow5C+SFvS01ciTkjqwzRp5HWs=" } }, "roles" : [ { "role" : "readWrite", "db" : "notifications" }, { "role" : "root", "db" : "admin" }, { "role" : "readWrite", "db" : "translations" } ] }

but when web application trying to run some query it's throwing error Authentication failed. for both databases.

It worked before when I use only one database (translations), but after I add permissions for the notifications database it stops working for both databases.

What I'm doing wrong, it seems that permissions set correctly?


texts: Can I give the same name to a table and a stored procedure?

texts:
Is it possible to name a stored procedure the same as a table? I have an existing table named 'buyers', and I'm trying to create a stored procedure named 'buyers', but I'm getting this error:

  There is already an object named 'buyers' in the database.


texts: Migrating Oracle 9i database to MySql database on a different Windows Server

texts:
I need to migrate my Oracle 9i database with all its tables from windows server 2003 to MySql on another Windows Server 2012. The oracle database is connected to Visual Studio web project. I have successfully imported the files (.aspx) to Microsoft Visual Studio 2013 Community edition.

(Mind that I need to keep original database intact for few weeks of testing peroid, simply the migration should be , creating a clone copy and not moving the database.)

I am facing problems as the visual studio project has code for connection with old oracle database. I need to recreate the functioning website.

The only thing remaining is migrating that old database to new server.
I tried SQLWays .  But It does that on single machine not from another server.

Please suggest solutions for migration between different servers.

Further, what needs to be changed in my vb code even if I migrated the database successfully to get it working with visual studio 2013 project.
(or may I ask a different question for that?)


texts: checking the normal form of the current table

texts:
I have a schema

R1(A,B,C,D)

with the following functional dependencies

A->B
CD->B
A->CD
CD->A

I need to figure out which is the highest normal form in which the following table exists. Here A is the primary key.

2NF- Since B, C and D are fully dependent on A, 2NF holds.

3NF-Since B,C and D are not transitively dependent on A. 3NF holds.

Since on the LHS of functional dependencies, there are only super keys, BCNF holds.

Is my solution fully correct? or am i lacking something? I feel this is not 100% correct. Here CD is also a key attribute so do I not need to check my non key attributes with it?


texts:
I check my db,and seems to be UTF8 complaint

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER        |VALUE
NLS_CHARACTERSET |AL32UTF8

But when i do a search on a VIEW

SQL> select titolo,anno,genere from generale where titolo like  '%Roma dr%' order by titolo;

TITOLO                                                      |ANNO   |GENERE
------------------------------------------------------------|-------|------------
Roma drogata: la polizia non pu?? intervenire               |1975   |Poliziesco

1 row selected.

Elapsed: 00:00:00.02

The two ?? tell me something is wrong.
Any solution to give a full utf8?Thanks


texts: mysqlworkbench after cmd mysql startup went wrong

texts: i have problems with my mysql server. I have installed the newest mysqlserver but having problems after starting my server from command line on localhost. Before that i was running xammp. The problem is that when starting from command line and not from xammp, mysqlworkbench shows me tables from another datadir when connecting with database . I really don't know how to fix my problem.


texts: Postgres - How to get Multi-Count Query using a Where Clause for a Join Table

texts:
I have the following query that gets the counts across multiple tables that are joined:

select
    count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
    count(distinct e.planningitemdata->>'shipTo') as shipTo,
    count(distinct e.planningitemdata->>'productId') as product,
    count(distinct eo.orderlineid) as orderline,
    count(distinct e.planningitemid) as planningitem
from
    eventplanningitem e
join
    eventorderline eo
        on e.eventid = eo.eventid
join
    orderline o
        on eo.orderlineid = o.id
        and o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06'
where
    e.eventId = '9f6d3d50-05ca-4441-a4e4-24de2b52de5b'

I want to filter the "orderlineid" counts based on a date filter, but my issue is that when I try to apply it to the join as shown above, it 0's out all the results of all the other counts. Same thing happens if I try to add a where clause to the end. If I make the orderline table a left outer join, I get the count of the orderlines that don't match the date criteria.

Result:

0;0;0;0;0

A similar version produces the same thing:

select
        count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
        count(distinct e.planningitemdata->>'shipTo') as shipTo,
        count(distinct e.planningitemdata->>'productId') as product,
        count(distinct eo.orderlineid) as orderline,
        count(distinct e.planningitemid) as planningitem
    from
        eventplanningitem e
    join
        eventorderline eo
            on e.eventid = eo.eventid
    join
        orderline o
            on eo.orderlineid = o.id
            and o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06'
    where
        e.eventId = '9f6d3d50-05ca-4441-a4e4-24de2b52de5b'
        and (o.deliveryrequesteddate >='2018-03-06' and o.deliveryrequesteddate <= '2018-05-06')

I get counts if I make the orderline join a left outer join, but it includes the orderLine records that actually fall outside the date criteria so it's also not correct.

I want to avoid breaking this into multiple queries but I wanted to check if I'm missing something before going that route.


texts:
In Oracle 11, what happens if I leave sys.aud$ to get bloated up to soak up the whole SYSTEM TABLESPACE?

Does it start rewriting the oldest records?  Can Oracle be configured to do so?

Does it stop auditing? Can Oracle be configured to overwrite the oldest records instead?

Does the DBMS crash? It seems not, it will prevent further actions until an operator handles the situation?


texts: Programmatically finding minimum set of fields necessary to create a unique composite key

texts:
I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for every row.

The way I do it now is I just start with 1 field and then I keep concatenating fields until I find a key that is unique for all records. This can be a little time consuming or I might end up concatenating more columns than I really needed to in order to obtain the unique key.

Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key? So if there is 1 field in the table that is unique for all records then that 1 field name would be returned. If I needed to concatenate [memberid], [claimid], and [date of service] in order to obtain a unique key then those 3 field names would be the result of the script.


texts: How to implement superkey sub key method on cascading tables

texts:
If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to return the extended row (that joins the securitymaster with the sub table in a UDF or Stored Proc)?

In my example, I will abbreviate the securitymaster table as A, the subtables are B,C,...,Z. Each subtables has its own specific columns. Let's further imagine the A has the first column a1 which is the PK and the last column a10 that holds the value B,...,Z (I.e. a FK to the subtable name). The number of columns in the sub table differ and will normally be specific to each table, however some tables may share the same column names.

If I have a securityID, say @sID, then, I will need to join the table as defined in A.a10 where A.a1 = @sID. I will then need to present select * from F (let's imagine A.a10 was equal to F). I have tried with Execute, but people say its a bad method, also I don't know how to return the value or the combined row from an EXEC within a sp. UDF looks impossible as they can not be dynamic. An example would be really useful as I feel this should be a fundamental tenant of any DB?

Many thanks


texts: Installation of PostgreSQL-11: Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable

texts:
I've added the official repository to my sources.list,

deb http://apt.postgresql.org/pub/repos/apt/ cosmic-pgdg main 11

When I try to install PostgreSQL-11 on Ubuntu Cosmic (18.10), I get the following error

$ apt-get install postgresql-11
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 postgresql-11 : Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable
E: Unable to correct problems, you have held broken packages.

How do I go about resolving this error?


texts: Using views with no insert table VS Audit tables

texts:
I(not a DBA) am considering designing Audit tables for an application that needs to be really fast as well fully audited (undo is not necessary). I referred to some from so and I found variations of the below

Create an insert only table with _to and _from and create a
view from these.
Create an Insert only table with just last_updated.
Create a separate Audit table with both old and new values
Create a separate Audit header table and Audit Detail table

I am choosing the following option and reasoning:

3 . Separate table but a single per table audit table - and just have created_by, created_date, updated_by and updated_date columns

because I think insert only table would create too many records and might slow down quickly considering it will be a hotspot data for my application.
Views will still be slower since they are just stored queries?
Separate Audit table would help be separate functionality and feels right.
Separate Header/Details is too much of an overhead

My question is considering the characteristics of application please provide if this makes sense.


texts: Yet another "ERROR 1005 can't create table" question

texts:
Well, I've been looking at the ERROR 1005 questions, but I haven't been able to see my error among the issues addressed by those questions. This code is generated by MySQL Workbench under Linux. Can anyone with sharp eyes spot the error? It occurs when trying to create AB_test_questions.

For context, table ABs refers to subjects of an A/B test, and AB_test_questions represents questions which compare two subjects against each other (hence two foreign keys referencing the ABs table).

CREATE TABLE IF NOT EXISTS `db`.`ABs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `text` VARCHAR(255) NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'represents the subject of an AB test';

CREATE TABLE IF NOT EXISTS `db`.`AB_test_questions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `AB1` INT NOT NULL,
  `AB2` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `AB1_idx` (`AB1` ASC),
  INDEX `AB2_idx` (`AB2` ASC),
  CONSTRAINT `fk_AB_test_questions_ABs_id1`
    FOREIGN KEY (`AB1`)
    REFERENCES `db`.`ABs` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_AB_test_questions_ABs_id2`
    FOREIGN KEY (`AB2`)
    REFERENCES `db`.`ABs` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'represents an AB test question';


texts:
After having an awful experience with a dedicated server and spending 3 whole days repairing corrupted databases (Yes I know.... backup!).... I have narrowed the issues down to a few websites. The trouble is, these websites are Magento websites.

I am having to import the .ibd files into the /var/lib/mysql folder and DISCARD/IMPORT the tablespace.

Is there a way to alter ALL tables in the database at once, i.e ALTER TABLE * DISCARD TABLESPACE and then move the files across... Then ALTER TABLE * IMPORT TABLESPACE ?


texts: Backup postgresql docker image from WAL using wal-e

texts:
I have an image which has wal-e installed and working.  It successfully on backup-push sends my WAL to the remote drive.

However when I run backup-fetch, I get the error

wal_e.main   INFO     MSG: starting WAL-E
        DETAIL: The subcommand is "backup-fetch".
        STRUCTURED: time=2017-09-05T07:45:24.721673-00 pid=3839 wal_e.main   ERROR    MSG: attempting to overwrite a live data directory
        DETAIL: Found a postmaster.pid lockfile, and aborting
        HINT: Shut down postgres. If there is a stale lockfile, then remove it after being very sure postgres is not running.
        STRUCTURED: time=2017-09-05T07:45:24.750882-00 pid=3839

So I follow the error and shut down with pg_ctl stop and I get:

pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

Then I move to another shutdown method pkill postgres.

When i do that it seems to work in shutting down postgres, but then kicks me out of the container and runs the restart protocol.

To fix this, I could get rid of the restart:always in my compose file, but I don't want to do this as it poses issues down the line for my orchestration integrity.

Any other ideas on how to restore from WAL using wal-e on postgresql docker image?

I aslo tried running

docker exec -t postgres_1 pkill postgres; /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch /var/lib/postgresql/data LATEST

Which would in theory stop and then restore in one swoop, but it does what I would expect it to do.  Stop which restarts the container and then returns the expected error:

-bash: /usr/bin/envdir: No such file or directory

I still got the same error if I ran pg_ctl -U postgres stop or I got can not find command pg_ctl when I ran su postgres and then pg_ctl stop


texts: How to do something like UPDATE DELAYED in MySQL

texts:
I have an averages table that should keep track of an average value over time.  I don't want to have a row for each value, just a single row that continuously updates the average.  What I've come up with is this:

set @value=4;
set @name="myAverageValue";
UPDATE `timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

Many clients may be doing this at the same time, and I don't want there to be any locking issues.  I don't care what order the updates are run in, since in the end it will all end up the same.  I just want to have a query that sends the UPDATE to the database, and it will process it eventually, similar to an INSERT DELAYED.  Does UPDATE LOW_PRIORITY do this, or does that cause the client to wait until it is available?


texts:
I'm having a bit of trouble figuring out how to do this -- I need to select all rows from table t1, join table t2 -- but include a null row from t2 for each record in t1 (even if rows match in t2).

For my case, I actually need to do this with four tables:

Table t1 has t1_id, name

Table t2 has t2_id, name

Table t3 has t3_id, name

Table t4 has t4_id, name

I need to join them all to get something like:

-----------------------------
t1_id | t2_id | t3_id | t4_id
-----------------------------
1     | NULL  | NULL  | NULL
-----------------------------
1     | 1     | NULL  | NULL
-----------------------------
1     | 1     | 1     | NULL
-----------------------------
1     | 1     | 1     | 1
-----------------------------

This would be the final result of a join of the four tables, each having one row with an tx_id of 1.


texts: Combining data from 3 tables depending values stored in 1 table

texts:
I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single member.

I have a bookings, guests and members table:

Bookings:
id, day, booking_id, guest_id, member_id, start_date, end_date

Guests:
id, first_name, last_name

Members:
id, first_name, last_name

The Bookings table contains a new booking row per day booked per person. Each of these bookings can relate to another by booking_id (These are group bookings).
A booking must have a single member related to it but can have multiple guests.
However a single booking does not have a column showing it is a group booking other than if the booking_id is else where in the Bookings table.

From what I understand is I want to self-join multiple times (can potentially have ~30 of the same booking_id's) on booking_id

Then left-join on the Members where Bookings.member_id = Members.id WHERE Bookings.member_id != 0

And left-join on the Guests where Bookings.guest_id = Guests.id WHERE Bookings.guest_id != 0

Sorry if this is confusing and thank you for reading it

For example:

Bookings:
+----+-----+------------+-----------+----------+------------+----------+
| id | day | booking_id | member_id | guest_id | start_date | end_date |
+----+-----+------------+-----------+----------+------------+----------+
| 1  | 01  | 476        | 91        | 0        | 01-06-15   | 01-06-15 |
| 2  | 01  | 476        | 0         | 64       | 01-06-15   | 01-06-15 |
| 3  | 01  | 519        | 32        | 0        | 01-06-15   | 01-06-15 |
| 4  | 02  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 5  | 02  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 6  | 02  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
| 7  | 03  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 8  | 03  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 9  | 03  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
| 10 | 04  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 11 | 04  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 12 | 04  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
+----+-----+------------+-----------+----------+------------+----------+

Members:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 32 | Travis     | Pilsner   |
| 84 | George     | Warrok    |
| 91 | Rob        | Williams  |
+----+------------+-----------+

Guests:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 15 | Sarah      | Phillips  |
| 29 | Katie      | Hills     |
| 64 | Laura      | Time      |
+----+------------+-----------+

To Produce:

+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
| day | booking_id | member_id | guest_id | start_date | end_date | names                                      |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
| 01  | 476        | 91        | 64       | 01-06-15   | 01-06-15 | Rob Williams, Laura Time                   |
| 01  | 519        | 32        | 0        | 01-06-15   | 01-06-15 | Travis Pilsner                             |
| 02  | 634        | 84        | 29, 15   | 02-06-15   | 04-06-15 | Geroge Warrok, Katie Hills, Sarah Phillips |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+


texts: Changed stored procedure causes VB runtime error

texts:
ALTER  Procedure [dbo].[HRptForm3A]
@StrCond Varchar(1000),
@SelYear int,
@Type varchar(1)
as
Declare @Tbl varchar(30)
    if ( @Type = 'O')
        begin
            set @Tbl = 'TblResult'
        end
    else if( @Type = 'I')
        begin
            set @Tbl = 'TblGResult'
        end
Declare @FromPayPeriodId Int
Declare @ToPayPeriodId Int
    select @FromPayPeriodId=isnull(Min(PayPeriodId),0),@ToPayPeriodId=isnull(Max(PayPeriodId),0) from tblPayPeriodMst Where
    Month(FromDate) in (4,5,6,7,8,9,10,11,12) and Year(FromDate)=@SelYear or
    Month(FromDate) in (1,2,3)and Year(FromDate)=@SelYear+1
Declare @Qry2 Varchar(1000)
Set @Qry2='Select E.Empid,E.Empcode,E.Empname,E.EmpFhName,E.Condate,E.PfNo as AccountNo,E.VPF,E.Leftdate,E.ReasonForLeft,R.payperiodId,
R.Lop,R.LWF,R.PFGross,R.PF,R.EPFE,R.EPFR, **Month(P.FromDate)+1** as Monthnumber ,Year(P.FromDate) as YearNumber,
Case
When Month(P.FromDate) in(4,5,6,7,8,9,10,11,12) then  Month(P.FromDate)
when Month(P.FromDate) in(1,2,3) then  Month(P.FromDate)
end as Mnth
From
PayEmpMstView as E,' + @Tbl + ' as R,tblPayPeriodMst as P
where  E.Empid=R.Empid  and len(E.PFNo)>0
and R.PayPeriodId=P.PayPeriodid  and
P.PayPeriodid between ' + Convert(Varchar(10),@FromPayPeriodId) + ' and '+ Convert(Varchar(10),@ToPayPeriodId)+ @StrCond +
' Order by E.EmpCode,Year(P.FromDate),Month(P.ToDate)'
Exec(@Qry2)

Exec ('Select E.EmpCode,E.EmpId  from tblPayEmployee E Where  0=0  ' +  @StrCond )

GO

when i change the highlighted value Month(P.FromDate) to Month(P.FromDate)+1. its showing exception error in the coding part of vb.net as HRESULT:800340 error
this change is working in sql server 2000 but not in 2005.
how to overcome this

Karthik added this code in the comments. It appears to be VB.NET code. It's not super legible.

MNth(4) = "Apr-" & Year :
MNth(5) = "May-" & Year.ToString :
MNth(6) = "Jun-" & Year.ToString :
MNth(7) = "Jul-" & Year.ToString
MNth(8) = "Aug-" & Year :
MNth(9) = "Sep-" & Year.ToString :
MNth(10) = "Oct-" & Year.ToString :
MNth(11) = "Nov-" & Year.ToString
MNth(0) = "Dec-" & Year :
MNth(1) = "Jan-" & (Year + 1).ToString :
MNth(2) = "Feb-" & (Year + 1).ToString :
MNth(3) = "Mar-" & (Year + 1).ToString
Dim J As Integer J = 4 ' k = 1
  IdOfEmp = Val(DS.Tables(0).Rows(TotalCnt).Item("EmpId"))
  While J < 13
    Try
      While IdOfEmp = Val(DS.Tables(0).Rows(TotalCnt).Item("EmpId"))
        If J = Val(DS.Tables(0).Rows(TotalCnt).Item("MonthNumber"))
          Then .PrintString(" | | | | | | | | |") :
               .NewLine() :
               .Tab(.LeftMargin)
        If J = 12
          Then J = 0
        End If
       .PrintString(MNth(J), 9, DosPrint.Align.Left)

I'm not sure that the best approaches are being followed here, but ... I'm just trying to clean things up.


texts:
I have a collection as follows:

{
    "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
    "date" : "20180126",
    "oid" : "000",
    "aid" : "0000000000",
    "currentavalible" : "7",
    "deletedbyuser" : "0",
    "reply" : [
        {
            "oidaid" : "000000",
            "nickname" : "****",
            "date" : "2018-01-26 07:32:45",
            "recomm" : "13",
            "unrecomm" : "0",
            "contents" : "aaaaaaaaaaaa"
        },
        {
            "oidaid" : "000088",
            "nickname" : "para****",
            "date" : "2018-01-26 08:29:54",
            "recomm" : "12",
            "unrecomm" : "0",
            "contents" : "bbbbbb"
        }
    ]
}

To find in reply that  "recomm" is "13" i tried:

db.comments.find( { 'reply.recomm' : '13' })
db.comments.find( { 'reply' :{ 'recomm' : '13'}  })
db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' })
db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply' : { 'recomm' : '12'} })

And all fetched me nothing.

What seems to be the error in my syntax?


texts:
They seem to be created by tools like DTA.

For practical purposes they could be deleted, ignored or chosen to be created, but until then, they have no direct influence on the database.


texts: Modify a value inside of a nested dynamic column

texts:
In the table (items), I have a dynamic column (attr).

I have a very simple nested dynamic column.

INSERT INTO items (attr) VALUES (COLUMN_CREATE('level1', COLUMN_CREATE('level2','level2_value')));

Which when viewed with:

SELECT COLUMN_JSON(attr) FROM items;

Returns:

  { "level1" : { "level2" : "level2_value" } }

How can I modify the "level2_value" value?

Things I've Tried

Example 1

UPDATE items SET attr=COLUMN_ADD(attr, 'level1', COLUMN_ADD(attr, 'level2', 'NEWVALUE')) WHERE id=1;

  Results in: { "level1" : { "level1" : {"level2" : "level2_value"},
  "level2" : "NEWVALUE" } }

Example 2

UPDATE items SET attr=COLUMN_ADD(COLUMN_GET(attr, 'level1' AS CHAR), 'level2', 'NEWVALUE') WHERE id=1;

  Results in: { "level2" : "NEWVALUE" }


texts: Why MySQL performs full table scan if subquery is used in update statement


texts: Multiple query to columns indexed in MySql

texts:
I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure:

+----------------+
|    products    |
|----------------|
| id       int   |
| title    string|
| date     date  |
| category string|
+----------------+

so it occurred to me to create a composite index for the columns that I want to filter:

CREATE INDEX index_name
ON products(c2,c3,c4);

The problem, as the documentation says, is that the query optimizer cannot use the index to perform searches if the columns do not form a prefix to the left of the index. then I could not make a WHERE condition to a specific column if it is not in the correct order of the index, which is a problem for me, since my query could include one or more conditions in any order, according to the user's need for example:

SELECT * FROM products WHERE title = ""
SELECT * FROM products WHERE date = ""
SELECT * FROM products WHERE category = ""
SELECT * FROM products WHERE title = "" AND date = ""
SELECT * FROM products WHERE title = "" AND date = "" AND category = ""

So an index of multiple columns is not the best option for my case, so I thought to add an individual id to each of my columns, is this a good practice or is there another way to address this problem?


texts: Postgres inet array contains a single ip

texts:
Requirement: user can add single ips or ip address ranges to a database table(for example permissions table having ipAddresses of inet[] type).  I need to query that a single ip is contained in the array of ipAddresses field which can be array of ip addresses or ip address ranges or both. I have tried to add where query like this:

WHERE "ipAddresses" @> '192.168.1.5'::inet

and also like this

WHERE "ipAddresses" >> '192.168.1.5'::inet

but no luck.
Thanks in advance.

id          uuid      NOT NULL  gen_random_uuid()
permissions uuid[]
ipAddresses inet[]
createdAt   timestamp with time zone        now()
updatedAt   timestamp with time zone        now()

source: pg array functions and
pg network address functions


texts: same mysql queries are much slower when running from script

texts:
I have a mysql script with many queries operating (creating, joining, inserting,etc.) on tables of sizes of several million rows. I run the script on Linux (server version 5.7.17-0ubuntu0.16.04.1, InnoDB engine) using

mysql -ppassword -uuser db < script.sql

Using show processlist; I find that some queries in the script are really slow - they are stuck in Sending data state and take an hour or more to complete. When they complete similar queries in the same script take less than 2 minutes.

Confusingly, if I run the same script pasting commands line by line directly into the mysql console, all queries, even the first one, take less than 2 minutes. Also, I can kill the long running query from the script and rerun it interactively in the same 2 minutes.

It cannot be an effect of caching because I am testing two different fresh instances.

What could be causing such a difference?


texts: How can I enforce that values in a table column match the Regular Expression "[axyto0-9\s]{0,2}[\s0-9]{0,10}"?

texts:
I have a table as below

CREATE TABLE dbo.DemoTable
(
Value VARCHAR(12)
)

I would like to constrain it to only contain rows where Value matches the following pattern

[axyto0-9\s]{0,2}[\s0-9]{0,10}

Start of string
A single character present in the list below between
zero and 2 times, as many times as possible

A single character from the list “axyto” (case sensitive)
A character in the range between “0” and “9”
A “whitespace character”

Match a single character present in the list below between zero and 10 times, as many times as possible

A “whitespace character”
A character in the range between “0” and “9”

End of string

How can I do this in SQL Server? (inspired by this question on SO)


texts: Restricted logins with automatic full permission in databases

texts:
My scenario:
I have some groups with full - and inappropriate - access to all Sql Server instances. I want to change that.

My Plan:
Give full and automatic access to all databases (it is customer databases and this groups must be allowed to change any property if necessary).

Restrict permissions on Instance Level, because this groups should not be allowed to do any unexpected change on instance properties.

I don't want to create a user in databases, because our external customer could drop the user ( they have db_owner permission )

Someone have any idea?
Tks


texts:
Hello I have a table in Postgres and one of the fields is type Date with the format '2019-09-10' and I'm transferring this table into a SQLite one BUT in this SQLite I'm getting this field as Date with this format 'Fri Sept 13 2019 00:00:00 GMT+0000 (UTC)' and I don't want that I just want it to be as in the Postgres table '2019-09-13'

this is the DDL of the Postgres:

CREATE TABLE public."Checks" (
    "customerId" int4 NOT NULL,
    "checkDate" date NULL
);

and this is the DDL for the SQLite:

CREATE TABLE `Checks` (`customerId` INTEGER NOT NULL NOT NULL, `checkDate` DATE);


texts:
I have generated a dump from Master  and I have to construct slave and give privileges and binary log position and file name of master for it to obsorb from master. Master is a high transaction OLTP application having TPS of 74.21 inserts/s, 113.17 updates/s, 0.00 deletes/s, 77468.29 reads/s. Now when DML operations are about to operate on SLAVE via binlogs would there be any impact on Master's performance. Say for example Load Avg or IO operations on Master that might get affected. As it allows slave to read the binlog info .

Note: Master and Slave have same server configuration.

Mysql Version : 5.0.77

OS : CentOS 5.4

Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE)

Database Size: 380 GB

This question strikes me when I wanted to scp some of the files to Slave. Whereas it has just 34MBPS avg speed within the network and it took reads IO operations a lot and Load Avg was hitting more than 6, 7 , 11 etc .. Ofcourse its not going to use SSH as it uses mysql socket to connect. Just a measurement to have slave in place by not hindering master's perf.

My questions are:

Could there be any load spiking in cpu processors or IO, that which can slow down Master Perf?
If any medium that I can trfr data that uses less cpu/IO utilization ?

Regards,

Mannoj Kumar


texts:
We are setting up a new SQL Server with over 2 TB size of Database, and this is a heavy OLTP. Let me know, if there is any impact of placing SQL binaries in C drive? or i would need another Drive? Server is already having these drives

SQL Server databases data ﬁles – G
SQL Server database log ﬁles – L
TempDB data ﬁle(s) – T (local drive)
Backups – H:

And these are Pure FlashArray storage mounted onto Windows.
SQL server 2017 Standard version will be installed on MS Windows Server 2016 Standard.

Your help is really appreciable. Let me know, if you need any other details?
Also let me know, any best practice for SQL installation.


texts: How can I pivot a view into a join so I get multiple query rows as columns in a single row?

texts:
I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data into my view.

What I am trying to achieve is to join this view to my main vehicle table so that I can select the various properties as columns in a single row.

My view:

Vehicle_ID  Property     Value
1           Doors        2
1           Engine Size  2 liter
1           Tire Size    50cm
1           Color        Blue
2           Doors        4
2           Engine Size  3 liter

And my main vehicle table:

Vehicle_ID  Description  Date_Bought  Owner_ID
1           BMW M3 Sport 2000/12/23   1
2           Audi A5 3XL  2015/03/19   1

So what I would like to get out is something like

Vehicle ID  Description   Date_Bought   Doors   [Engine Size]  [Tire Size]  [Color]
1           BMW M3 Sport  2000/12/23    2       2 liter        50cm         Blue

My overall SQL knowledge is fairly limited, I'm pretty good with JOINS and stuff, but the logic of the PIVOT command and its syntax completely escape me.


texts:
I have enabled cdc and using it , it works fine now the problem is that we have many tables in a database and also we have many databases so how to configure cdc cleanup job to run again and again after a specific time , I read this article tried what he said but its not working for me .

I also tried this SP sp_MScdc_cleanup_job , I executed this it clean all data but i need it to be automatic

Let suppose i want to run it every minute just for check that its running or not , What should i do ?


texts: Platform to manage and analyze peer evaluations?

texts:
Our academic department (at a US university) collects mandatory paper-based peer evaluations at undergraduate level for a number of classes each semester. So in a typical class of 60 students, folks must complete a final project in teams. They would form 10-15 teams of 4-5 people each. By the end of semester, each team member is required to rate every member of their team including him/herself and add any written feedback. To avoid free-riding, this information goes into the final grade calculation.

We are now looking for ways to ditch paper-based evaluations for good and make all peer submissions electronic. It seem simple but online survey providers cannot give us what we need due to very limited capabilities of their platforms. For example, Qualtrics (that is designed to build and analyze surveys) can only allow typing each respondent name manually or coping and pasting several rows of data. Imagine having 60 students per class, 8 classes each semester? Typing those names will not only take forever but also create many typos and misspellings due to human error. (Excel spreadsheets can still be imported into Qualtrics but for different purposes. Qualtrics has an array of other technical issues.)

We want to be able to upload a spreadsheet with all registered students and their team memberships into a specific survey design, so that by choosing their team from a dropdown menu on the first page, a respondent will see a list of all their team members on the next page. Are you aware of any online source that can help us do that?

I will greatly appreciate any feedback from you.

Thanks!


texts: Handling new fields when merging in data using hashbytes?

texts:
We load in data from stage into our ODS and check for differences using hashbytes. We calculate hashbytes from the stage tables and insert/update into the destination table and also store the hashbytes value in the destination.

The problem arises when there's a new field that we need to bring in from a source system. We'll add a new column in the ODS table with a default value but the calculated hashbytes are different because of this new field. As a result, everything gets updated even if nothing has changed. We'd have to update Hashbytes column in the large (300M but could be 1B rows) table whenever we add a new field which is not often but enough to be cumbersome.

What are the best approaches to handle this?

I'm thinking remove the hashbytes column from the ODS table and just calculate it in the proc for stage and ODS values. I inherited this code so I don't know why the hashbytes is stored in the ODS table, is that a best practice?

Approach #1

UPDATE b
SET Field1 = a.Field1
    ,Field2 = a.Field2
FROM source a
INNER JOIN destination b ON a.PrimaryKeyField = b.PrimaryKeyField
WHERE CAST(HASHBYTES('SHA1', CONCAT(a.[Field1], '|', a.[Field2])
    <> CAST(HASHBYTES('SHA1', CONCAT(b.[Field1], '|', b.[Field2])

Approach #2

UPDATE b
SET Field1 = a.Field1
    ,Field2 = a.Field2
FROM source a
INNER JOIN destination b ON a.PrimaryKeyField = b.PrimaryKeyField
WHERE  (a.Field1 <> b.Field1
    OR isnull(a.Field2,'') <> isnull(b.Field2,''))


texts: How to create a secondary read-only reporting database with < 15 minute latency

texts:
We have a 2012 standard edition instance that is doing log-shipping of 2 related databases every 15 minutes to a secondary 2012 standard edition instance for DR purposes. Although it provides read-only access, users are automatically disconnected every 15 minutes when the restore job runs (it takes up to 2 minutes to complete the restores).

Without interfering with that process, we would like to set up a secondary instance for reporting and developer troubleshooting purposes that would also be kept up to date at least every 15 minutes (the reports need near real-time data), but would not involve periodic disconnections, enable different security than the primary (so that devs can read from the secondary but not the primary), and preferably allow  indexes to be created that do not exist on the primary (to improve reporting performance).

1) How can we achieve this?
2) How can we achieve this without having to upgrade to Enterprise Edition?


texts: SQL Server parallelism for DML operations

texts:
I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements  mostly likely in a data warehouse environments when query cost exceeds cost threshold of parallelism.

ORACLE offers parallel DML operations like INSERT, UPDATE or DELETE since version 8i.

Does SQL Server use parallelism for DML operations?


texts: Ola backups happening on both Primary and Secondary AG

texts:
I configured Ola's scripts on a staging AG configuration to test out the logic. When I checked the backup folder today after a week, I see that the backups are happening both on the primary replica and copy_only backups on the secondary replica.

It is my understanding that the backups are supposed to happen on the secondary replica - And I have configured the AG to "Prefer secondary". I don't want the backups happening on both primary and secondary on this 2 node AG. What can be done for this to work? Please advise.


texts: Insert select - using selected value in ON DUPLICATE KEY UPDATE

texts:
In the following query, oversimplified from my actual scenario but close enough:

INSERT INTO foo(bar_id, baz)
SELECT
    id,
    5 AS new_baz
FROM
    bar
ON DUPLICATE KEY UPDATE baz=new_baz

I get an Unknown column 'new_baz' in 'field list' error. However, if I change the last line to contain baz=bar.id instead then it all works, so it seems like it's an issue with the fact that new_baz is not actually from a column in a table, but a literal value.

How can I use the value of new_baz in the last ON DUPLICATE KEY UPDATE line?


texts: Dropping a table with a non-printable character in its name

texts:
I have a legacy table with a name containing non-printable characters (CHAR(31), to be specific).

The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+_ creates the CHAR(31) character (which means "US" - Unit Separator). The previous developer could have hit this combination by mistake, and created the table with this weird character in it.

When we issue a SELECT command against the table, it returns results. But when we try to issue any DDL against it (DROP, sp_rename, etc), the character causes a problem.

Examples:

DROP TABLE Table_Name;

Raises:

  Msg 15225 - No item by the name of 'Table_Name' could be found in the current database 'MyDB', given that @itemtype was input as '(null)'.

EXEC sp_rename N'Table_Name', N'NewTableName';

Raises:

  Msg 102 - Incorrect syntax near '_Name'.

I already duplicated the table with the correct name, and have corrected it on the dependent objects. The remaining step is just dropping it from the database.

Just an insight: when we copy+paste from SQL Server to Notepad++, it shows the hidden character ("US") on the middle of the table name, beside the underscore:


texts: Will different tables for different Users help in performence

texts:
I have this Posts table. Somehow Read/Write ratio is 50:50. And we have to write very frequently.

Now suppose I am going to update 1000 post 500 users each, every minute. I think this will overcrowd the post table. So I am thing to have n different table for Posts like Posts_a, Posts_b, Posts_c.. and so on.

And I will have a posts_table field in the users meta data.

Now I want to know if that is a proper solution.


texts:
Requirement:

I'm required to prefix a couple of strings to the existing one.

I.e. The current way, the Accounts been recorded in AccountID field looks as

  "621230093VN00"

Under the new format it should look as

  "JKB-621230093VN00"

Context:

The field named AccountID, being a type of Char(15) which is the
primary key of a Table named Account and the Same is referenced
to other Master tables such as ClientAccount and Transaction tables
such as Account Ledger, Payment, Receipts and Journals etc.

Stats:

Account Table and Clientaccount Table have 50K records each;
AccountLedger have about 5 Million records;
Payment, Receipts, and Journals have combined  1 Million records;

Our Concerns and Thinking:

Why we went ahead in prefixing the field instead of creating it separately was to not mess with Structure and System which was performing very well and thinking that it had a minimal consequences which results in than creating it as a separate field.

How we think to go about:

For such, I understand that I need to drop all foreign keys associated to the table, drop the primary key, Increase the length of the field and any fields associated to the table (foreign) and finally recreate the primary keys and foreign keys again.

I seek your advice on this matter to say what I'm needed to is correct and if not point out anything I might have missed or anything you think I should do prior to performing this task.


texts: Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL?

texts:
Hopefully the question is self explanatory.  I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN

Can somebody clear this up for me?  What's the "(materialized)" mean if materialized views aren't a thing in MySQL?  Using MySQL Server 5.6, Workbench 6.3.


texts:
Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data.

I have ran sp_BlitzIndex and it  returned this:

  dbo.Job.PK_Job_1 (1): Row lock waits: 399; total duration: 21 minutes;
  avg duration: 3 seconds; Page lock waits: 171; total duration: 2
  minutes; avg duration: 1 seconds; Lock escalation attempts: 125,805;
  Actual Escalations: 0.

This table only has 35K rows in it, but it is heavily used and updated every few seconds. The current index is on the PK which is a unique value. I do not know much about index tuning, but if I remove this index and create a nonclustered index for the JobID + ClientID, am I on the right track to reducing the aggressiveness of the index? Or am I making it worse?


texts: Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

texts:
One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words are considered as system stop lists therefore won't appear in the index keywords. How can I remove these words from the system stop list and add them to the indexed keywords?

Thank you.


texts: What is lost when I create a Foreign Key using `WITH NOCHECK`?

texts:
I know that if I do an EXISTS() call on a FK lookup value, then, if that FK constraint is trusted, the result is immediate.

And if it is not trusted (like when I create the FK using WITH NOCHECK) then SQL Server has to go and check to table to see if the value is actually there.

Is there anything else I lose by using NOCHECK?


texts: Check if column has same value in any row

texts:
I have the following case.
+-----------+--------+-------------+
| ArticleID | UserID | IsConnected |
+-----------+--------+-------------+
|         1 |     25 | True        |
|         1 |     45 | True        |
|         2 |     55 | False       |
+-----------+--------+-------------+

So I need to create a View who will return True if there is more than 1 record in the table with same ArticleID.
To make this easier the table looks the same, but without IsConnected column.


texts: Restoring a database with just .idb and .frm files

texts:
I've never attempted something like this before. We had a WAMP installation with WordPress running on my wife's PC, and the Aestan tray menu failed. I figured we need to uninstall and re-install WAMP, which left the data files in .ibd and .frm files along with the core WordPress installation. I'm trying to bring them back to life again to be used. The WAMP people recommended I find a way to restore the database files.

I read a post here (Recover MySQL database from data folder without ibdata1 from ibd files) regarding someone who had the same set of "leftover" files, and was given some advice regarding how to restore using some MySQL utilties. They said, "you can download a MySQL utility that can generate the SQL needed to create the table." I went to Oracle and downloaded the Windows version of their utilities, but there are around 24 .exe files in there! I have no idea which one to use. I was expecting a single app in there, not a collection.

I'm not afraid to try this, but it's not clear which utility(ies) I should use. I'm trusting that the instructions could make sense once I'm actually attempting it. I've been in PHPAdmin a few times so I do know how to make some edits, but doing a restore like this with .ibd and .frm files is new territory for me.

Details: My WAMP installation was using MySQL 5.6.12, and the new WAMP installation uses 5.6.17. The WAMP people said if I restore the database in the 5.6.12 then I'll be able to choose the 5.6.12 setup from the Aestan tray menu. I have made backup copies of the 5.6.12 database files. So I'm ready to attempt this, but would appreciate a little advice from any of you.


texts: How to set the wal_level in AWS RDS Postgresql?

texts:
How do I know which wal_level my AWS RDS Postgres 11.1 database has?

I checked 'parameter groups', but was unable to find the wal_level.

Once found, how do I change it?


texts: Space not reclaimed after dropping MyISAM table

texts:
I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I get Error 28 (out of disk space) when I try to do pretty much anything with my database.

I have heard that due to the way that InnoDB manages tables, you have to do some extra work to reclaim space in certain situations after doing deletes/drops; however I did not think this was the case with MyISAM.

I have read that in some situations, after doing a massive delete from a table, optimizing the table can help free space, however since I have dropped the table, that is not an option.

What am I missing? What can I do to free the space?

I am using Centos 6.4 Final with MySql 5.5.25

EDIT: I learned what the problem was - Our servers are monitored by our hosting provider, and due to this, the result of df is not live, but rather reflects an infrequent snapshot of our server taken by the monitor. its insanely annoying.


texts: PostgreSQL, finding elements by value in numeric JSON arrays

texts:
I have a table defined as:

create table dummy (jdata jsonb);

I inserted the following two rows:

insert into dummy values ('["dog","cat","elephant","waffle"]');
insert into dummy values ('[1,2,3,4]');

I am trying to make use of the jsonb ?& operator which lets you ask the question "Do all of these key/element strings exist?"

An example using the string fields works:

select * from dummy where jdata ?& array['cat','dog'];
            jdata
--------------------------------------
["dog", "cat", "elephant", "waffle"]
(1 row)

But, trying to do this with an array that contains numbers does not work:

select * from dummy where jdata ?& array['1','2'];
  jdata
  -------
(0 rows)

select * from dummy where jdata ?& array['1','2'];
 jdata
 -------
 (0 rows)

select * from dummy where jdata ?& array[1,2];
ERROR:  operator does not exist: jsonb ?& integer[]
LINE 1: select * from dummy where jdata ?& array[1,2];
                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I know that the ?& operator works on text arrays, but therein lies the problem.  Does anyone know how to get the json operators to work on numeric arrays?


texts: What frequency of hash/sort spills into tempdb is concerning?

texts:
Our enterprise application uses SQL Server for data storage and is primarily an OLTP system. However, an important component of our application generates a significant OLAP workload.
Our write latency to tempdb is about 100ms. This trend holds over time, and ALLOW_SNAPSHOT_ISOLATION is turned off. We are troubleshooting this concerning problem and the only interesting thing we've found so far is that there are a significant number of hash and sort spills to tempdb. We surmise this is coming from our OLAP workload.
Question
What frequency of spills is concerning? Any? How many spills/sec? Our preliminary data indicates that we have about 2 hash spills per second and 25 sort spills per minute.
Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency?
Other information
We're using multiple files for tempdb as recommended per number of cores. The tempdb files are on a RAID 1+0 SAN (with high performance SSDs) but that's the same device as the main DB data and log files. The tempdb files are sized large enough that they grow very infrequently. We aren't using trace flags 1117 or 1118. Another variable is that this set up is shared for a number of different databases that all experience medium to high load.
Our 100 ms write latency is much greater than the acceptable ranges for tempdb write latency that we have found on MSDN, SQL Skills, and other sites. However, write latency for our other databases is good (below 10ms). Based on other stats, it does appear we're using tempdb heavily, particularly for internal objects. So we're digging in to try to find out why our application is using internal objects so heavily.
We do have real performance issues on our platform that manifest in varying ways. We've been monitoring perf counters, looking at DM views, and analyzing our app behavior to try to dig into the resource usage characteristics of our system. We're focused on spills right now as we've read that spills have drastic negative impact because they are performed on disk instead of in memory. And we appear to have a very high number of spills, but I wanted to get some input on what people consider "high."


texts: How to reclaim unused space from clustered index table

texts:
I have a clustered index table with more than 11,000,000 records running on SQL Server 2008 R2 Standard Edition.  I have noticed the table has about 90 GB of free space.

This is the table definition:

CREATE TABLE [dbo].[nsPDFDocument](
    [DocumentID] [varchar](100) NOT NULL,
    [DocumentDate] [char](10) NOT NULL,
    [DocumentTime] [char](10) NOT NULL,
    [DocumentSize] [int] NOT NULL,
    [PDFData] [image] NULL,
    [FileName] [varchar](255) NULL,
    [OwnerID] [varchar](50) NULL,
    [Title] [varchar](150) NULL,
    [CreationDate] [char](10) NULL,
    [CreationTime] [char](10) NULL,
    [Provisional] [char](1) NULL,
    [PreviousVersionID] [varchar](100) NULL,
    [isLockedBy] [varchar](50) NULL,
    [SecondaryStorageURI] [varchar](150) NULL,
    [PreviousExportUri] [varchar](150) NULL,
    [PurgedDocumentHash] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have tried:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET NOCOUNT ON;

CREATE TABLE #PDFsToFix(
       ID    INT IDENTITY NOT NULL PRIMARY KEY,
       DocID VARCHAR(100) NOT NULL
);

CREATE TABLE #OneBatch(
       ID    INT          NOT NULL PRIMARY KEY,
       DocID VARCHAR(100) NOT NULL

);

INSERT INTO #PDFsToFix( DocID )
       SELECT DocumentID
       FROM dbo.nsPDFDocument
       WHERE CAST(PDFData AS VARBINARY(MAX)) = 0x00;

DECLARE @processedID INT = 0;
DECLARE @batchSize   INT;
DECLARE @changed     INT

WHILE EXISTS( SELECT * FROM #PDFsToFix WHERE ID > @processedID )
BEGIN
       TRUNCATE TABLE #OneBatch;
       INSERT INTO #OneBatch( ID, DocID )
              SELECT TOP 50 ID, DocID FROM #PDFsToFix WHERE ID > @processedID ORDER BY ID;
       SELECT @batchSize = COUNT(ID), @processedID = MAX(ID) FROM #OneBatch;

       BEGIN TRANSACTION
       UPDATE dbo.nsPDFDocument SET PDFData = NULL
       WHERE DocumentID IN( SELECT DocID FROM #OneBatch )
          AND CAST(PDFData AS VARBINARY(MAX)) = 0x00;
       SET @changed = @@ROWCOUNT;
       IF @changed <> @batchSize
       BEGIN
          ROLLBACK;
          RAISERROR( N'Updated %d rows, expected %d', 10, 1, @changed, @batchSize );
          BREAK;
       END
       ELSE
       BEGIN
          PRINT 'Updated 50 rows';
          COMMIT;
       END
END

After running that query I did not get good result.

What steps should I follow to regain space from the table?

Note: I run a "Reorganize" index maintenance job every weekend; that does  not help at all.


texts: Can power failure rollback mysql innodb database of many days?

texts:
I have an application running on a x86 system with MySQL InnoDB database. This application store many rows per day and yesterday, due to a power failure, I experienced a rollback of a one entire day of data. Simply I have a hole of data of the day before the power failure, and I'm sure there were many data before the power failure because I checked backup dumps.

How can a power failure involving data committed and so many rows? Is because of the log structure of innodb? Can I do something in MySQL configuration (expect to avoid power failure)?

My configuration is a standard-default of the mysql installer in Windows x64 7 environment.


texts: In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

texts:
SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value?

Is it UTC?
Is it local for the server the backup was taken?
Is it local for the server the backup is restored?


texts: How to control Segmentation min/max data_id on a non-clustered ColumnStore index

texts:
Given a simple row-based table without a PK but with a row-based clustered index like so:

create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value])

Then I wish to add a column store index that is segmented in the same order as the clustered index above:

create nonclustered columnstore index CS_IX_PropertyValue on dbo.PropertyValue(
    PropertyId, Value
)
with (drop_existing = on, maxdop = 1); -- maxdop=1 to preserve the order by property

MaxDop hint to preserve order came from: here

Then the following query was used to report the min/max data_id for the PropertyId column and it the full range was reported on each of the 7 segments:

create view [Common].[ColumnStoreSegmentationView]
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose: List ColumnStore table segment min/max of columns.

     Source: https://joyfulcraftsmen.com/blog/cci-how-to-load-data-for-better-columnstore-segment-elimination/
             https://dba.stackexchange.com/a/268329/9415

    Modified    By            Description
    ----------  ----------    -----------------------------------------------------------------------------------------
    2020.06.02  crokusek/inet Initial Version
  ---------------------------------------------------------------------------------------------------------------------*/
select --top 20000000000
       s.Name as SchemaName,
       t.Name as TableName,
       i.Name as IndexName,
       c.name as ColumnName,
       c.column_id as ColumnId,
       cs.segment_id as SegmentId,
       cs.min_data_id as MinValue,
       cs.max_data_id as MaxValue
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id
  join sys.partitions as p
    on p.object_id = t.object_id
  join sys.indexes as I
    on i.object_id = p.object_id
   and i.index_id = p.index_id
  join sys.index_columns as ic
    on ic.[object_id] = I.[object_id]
   and ic.index_id = I.index_id
  join sys.columns c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
  join sys.column_store_segments cs
    on cs.hobt_id = p.hobt_id
   and cs.column_id = ic.index_column_id
 --order by s.Name, t.Name, i.Name, c.Name, cs.Segment_Id
GO

I tried making the clustered index unique which did slightly affect the reported ranges but still was not monotonically increasing.

Any ideas?

Here is a Link that accomplished the segmentation in this manner but I don't see any difference.

Version:  Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)


texts: teradata database unable to increase database size in permanent space

texts:
Anyone with teradata database experience? I'm new to this database and I've been tasked to increase the size of a database. I am using Teradata Administrator to do this. I am using Tools > Modify Database then increased the size in Perm space.

I'm getting the following error message when trying to modify the database to increase the size:

3541: The request to assign new PERMANENT space is invalid

My understanding so far is that key is to have enough space in DBC.
The database that I am using is a child database of the parent (DBC).

I can see from running queries that I should have enough size to increase my database since DBC is big enough.

Example:

select sum(currentperm) c1, sum (maxperm) c2, c2-c1 from dbc.diskspace;

Results:

currentperm         maxperm                 c2-c1
293,696,239,616.00  2,382,684,039,413.76    2,088,987,799,797.76
273.525938          2219.047434             1945.521496

This tells me I have 1.9TB available in DBC. Am I reading this correctly?
I have plenty of space. So why can't I increase my db?


texts:
If an Oracle authentication service has been configured, such as SecurID, does this mean that local users using Username/Password cannot use these credentials to login?
From this link it appears as that is the case but I was hoping to get some confirmation: https://docs.oracle.com/cd/A57673_01/DOC/net/doc/NWANO233/ch3.htm


texts: Drawing a Entity-Relationship Diagram with No Relationships

texts:
As a school project I have made a small database with two tables, users and rooms.

users has two columns: username and password
rooms has room_name, room no, size

Can an entity-relationship diagram (ERD) be made for this? Since there isn't a relationship I'm not sure how.

At the application program tier, the project just uses PHP to let the users login and get the details of the room.


texts: Finding Deleted / Updated record statement from SQL Server transaction Log backup file

texts:
One of my production server's data was updated / deleted by someone.

I want to find out when the data was deleted or updated, and if it's updated/ deleted then through which statement it was updated (means the query statement details like through which query it is happened).
I have a transaction log file i.e . .trn file.


texts: Linked Server in SSMS 2014 - Shared Memory Provider, error: 0 - The pipe has been ended

texts:
I'm trying to connect to Mongo database from Microsoft SQL Server Management Studio through Linked Servers using Mongo ODBC Driver. Right now I'm testing Easysoft ODBC Driver.

I can connect to the database and can perform some queries. But if I try to execute query which will return a lot of data I receive this error:

Msg 109, Level 20, State 0, Line 2
A transport-level error has occurred when receiving results from the server.
(provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

I receive this error exactly after 31 seconds.  I will appreciate any help.


texts: Dynamic SQL query - how do I add an int to the code?

texts:
Consider:

Declare @stringsvar varchar(1000)
Declare @Emp_id int
DECLARE  @strvar SYSNAME = 'Employee_test'

SET @stringsvar = ('select * from' + ' ' + @strvar + ' where emp_id' + ' =' + @Emp_id)
Print @stringsvar

The above query is giving an error as mentioned below:

  Msg 245, Level 16, State 1, Line 17 Conversion failed when converting
  the nvarchar value 'select * from Employee_test where emp_id =' to
  data type int.

What needs to be done in this case?


texts: How to create multiple graphs with TitanDB (+Cassandra) in the same machine?

texts:
I'm experimenting with TitanDB (with Cassandra as storage) + Rexter.

I've been reading the documentation during hours and trying to create multiple graph DBs on the same machine, but I didn't find the way to do it.

I find two main problems:

In the TitanDB docs, the Cassandra section tells how to create a graph on Cassandra, but the only parameters they comment are the host and the port, there isn't any reference to the graph name :s).
In the Rexter section we have essentially the same problem. We can define a list of named graphs, but every graph only can be mapped to a 3-tuple (backend type, host, and port).

The only way I can imagine is to instantiate more than one Cassandra server, and make that each Cassandra server listens to a different port. Then, configure Rexter to map every graph to a different cassandra instance.

There is a way to create more than one graph using the same Cassandra instance?


texts:
I'm not sure if this is the right place to ask this question, so apologies in advance if not.

Problem I'm facing is: One of my server's signal wait floats at around %35 and won't go down.

Top 3 waits are:

  SOS_SCHEDULER_YIELD (55%)

  OLEDB (10%)

  ASYNC_NETWORK_IO (6%)

CPU utilization of the instance is at around 22%.

What might be causing this to happen?. Where should I look at to investigate further?
I'm on SQL Server 2012.

Thanks all.


texts: Why would a table with a Clustered Columnstore Index have many open rowgroups?

texts:
I was experiencing some performance issues with a query yesterday and upon further investigation, I noticed what I believe is odd behavior with a clustered columnstore index that I'm trying to get to the bottom of.

The table is

CREATE TABLE [dbo].[NetworkVisits](
    [SiteId] [int] NOT NULL,
    [AccountId] [int] NOT NULL,
    [CreationDate] [date] NOT NULL,
    [UserHistoryId] [int] NOT NULL
)

with the index:

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_NetworkVisits]
   ON [dbo].[NetworkVisits] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

The table currently has 1.3 Billion rows in it and we are constantly inserting new rows to it. When I say constantly, I mean all the time. It's a steady stream of inserting one row at a time to the table.

Insert Into NetworkVisits (SiteId, AccountId, CreationDate, UserHistoryId)
Values (@SiteId, @AccountId, @CreationDate, @UserHistoryId)

Execution plan here

I also have a scheduled job that runs every 4 hours to delete duplicate rows from the table. The query is:

With NetworkVisitsRows
  As (Select SiteId, UserHistoryId, Row_Number() Over (Partition By SiteId, UserHistoryId
                                    Order By CreationDate Asc) RowNum
        From NetworkVisits
       Where CreationDate > GETUTCDATE() - 30)
DELETE
FROM NetworkVisitsRows
WHERE RowNum > 1
Option (MaxDop 48)

The execution plan has been pasted here.

While digging into the issue, I noticed that the NetworkVisits table had roughly 2000 rowgroups in it, with about 800 of them being in an open state and no where near the max allowed (1048576). Here is a small sample of what I was seeing:

I ran a reorganize on the index, which compressed all but 1 rowgroup, but this morning I checked again and we again have multiple open rowgroups - the one that was created yesterday after the reorganize, then 3 others each created roughly around the time the deletion job ran:

TableName       IndexName           type_desc               state_desc  total_rows  deleted_rows    created_time
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        36754       0               2019-12-18 18:30:54.217
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        172103      0               2019-12-18 20:02:06.547
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        132628      0               2019-12-19 04:03:10.713
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        397718      0               2019-12-19 08:02:13.063

I'm trying to determine what possibly could be causing this to create new rowgroups instead of using the existing one.

Is it possibly memory pressure or contention between the insert and the delete? Is this behavior documented anywhere?

We're running SQL Server 2017 CU 16 Enterprise Edition on this server.

The INSERT is MAXDOP 0, the DELETE is MAXDOP 48. The only closed rowgroups are the ones from the initial BULKLOAD and then the REORG_FORCED that I did yesterday, so the trim reasons in sys.dm_db_column_store_row_group_physical_stats are REORG and NO_TRIM respectively. There are no closed rowgroups beyond those. There are no updates being run against this table. We average about 520 executions per minute on the insert statement. There is no partitioning on the table.

I am aware of trickle inserts. We do the same thing elsewhere and are not experiencing the same issue with multiple open row groups. Our suspicion is it has to do with the delete. Each newly created row group is around the time of the scheduled deletion job. There are only two delta stores showing deleted rows. We don't actually delete a lot of data from this table, for example during one execution yesterday it deleted 266 rows.


texts: SQL Conditional Join - remove duplication from Stored Procedure

texts:
This stored procedure accepts a search parameter.  I want to join on a table valued function if the search parameter is not null.  The way I have written it works, but it seems like lots of duplication of code.

Is there a better way of writing this query to avoid the check for the parameter?  Something like a conditional join?

--this is actually passed in to the stored procedure as a parameter which may be NULL (no search text just filters on page)
DECLARE @Search varchar(100)

IF @Search IS NULL

WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO, 0
    FROM IMF AS I
)
SELECT FIM_NO, RNK
    FROM Catalog_Search_Results
    ORDER BY rnk desc

ELSE

WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO,funcItemSearch.RNK
    FROM IMF AS I
    INNER JOIN dbo.Catalog_ItemSearch(@Search) funcItemSearch ON I.FIM_NO = funcItemSearch.FIM_NO
)
SELECT FIM_NO, Rnk
    FROM Catalog_Search_Results
    ORDER BY Rnk desc;


texts: Issue: Why Restore database wizard/GUI for SQL Server 2014 loading old backup set to restore

texts:
I am using SQL server 2014 to restore backups using the GUI, however, the back sets to restore are from old backups that are no longer available along with transaction logs that span months ago.

How can I repair the wizard so that it populates the most recent full and diffs and transaction logs like the way it used to work by default?

Here is a sample screenshot of the of the restore database screen I am making reference to


texts: How to perform the dreaded double hop with linked servers?

texts:
The situation:
I have SERVER1, SERVER2, ACCESSDB on SERVER3.

I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB.

I want to be able to access the linked server on SERVER1 from SERVER2 (aka the double hop).

When I log into SERVER2 I get the good ol - "dont have permissions" issue when I test the connection. I checked SERVER3 logs and it says the incoming connection is ANONYMOUS. I need to be able to pass some (any) credentials to SERVER3 from SERVER2.

NO, I cannot use a SQL account. I have to use Windows Auth.

Yes, I need to use KERBEROS and set up SPNs. That's where my question comes in. How do I properly set up KERBEROS and delegation?

I want to be able to run

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

from SERVER2 and have it return 'KERBEROS' and not 'NTLM'.

What specific commands do I need to run to properly set up KERBEROS/SPNs? How do I create this "chain"?

FYI - Both instances are NAMED instances on SQL2008 R2 Standard (x64).


texts:
I am running a mysql database on linux with several InnoDB tables. At some point, the ibdata1 file got quite big and I wanted to delete it. So I made a dump, deleted the file and restarted the server.

Suprisingly, the dump didn't contain all data, but only up to a certain date (Dec 6). I know for certain that before restarting, the database held data up to the current date (Dec 27).

Not all hope was lost though. Every night there is a backup done of the whole file system (simple copy and paste to magtapes), so I recovered the datadir folder from Dec 26. I've then used this folder as mysql's new datadir, but in there I can also only find data up to Dec 6.

How is this possible? Since the data was visibly there, where did mysql store it, if not inside the datadir folder? Was it just in RAM for, like, 3 weeks?

my.cnf looks like this:

[client]
port            = 3306
socket          = /home/user/mysql/mysqld.sock

[mysqld]
port            = 3306
#bind-address   = 0.0.0.0
socket          = /home/user/mysql/mysqld.sock
datadir         = /home/user/mysql/data
log-error       = /home/user/mysql/logs/mysqld.err
#skip-locking
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=1

I dont know if you can help me, since this issue is kind of specific, but the most important question here is: If you know for certain that your database holds some data at point X, and you know there was a backup of the file system at point X - how would you go along recovering this data? I mean, it must exist somewhere, doesn't it?

best regards

EDIT

I've looked in the mysqld.err file and I found the following warning:

151227 19:21:35  mysqld started
InnoDB: ##########################################################
InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?
InnoDB: Log sequence number in ib_logfiles is 0 2439577407, log
InnoDB: sequence numbers stamped to ibdata file headers are between
InnoDB: 0 2446246316 and 0 2446246316.
InnoDB: ##########################################################
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
151227 19:21:35  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
151227 19:21:35  InnoDB: Error: page 1 log sequence number 0 2446123651
InnoDB: is in the future! Current system log sequence number 0 2439577407.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.

It goes on like that for a while. Any suggestions?


texts: Can't connect with UDL file to SQL Server 2016 but with SSMS it works without problems

texts:
I have a strange issue using a UDL file.
I can't connect with the UDL file to SQL Server 2016 with a specific user (login) but when I'm trying to connect with the same user via SSMS,  it works without problems.

A Diffrent user can connect both ways without issues.

What's wrong with my user/UDL file?


texts: Query returning all posts by friends and friends of friends

texts:
I'm currently representing friends in a table called relations, which has two columns (userA, userB) which are the user IDs of two users. If user 1 and user 3 are friends, I'd insert (1, 3) and (3, 1) into this table.

I also have a table called posts, which has, among other fields, an id and an ownerId.

So something like this:

relations:
 userA | userB
   1   |   3
   3   |   1
   3   |   4
   4   |   3
   4   |   2
   2   |   4

posts:
 id | ownerId | ...
  1 |    2    | ...
  2 |    3    | ...
  3 |    4    | ...

I'd like to select every post whose owner is a friend or friend of friend.

For example, if the user the query was selecting for was userId=1, the query should return posts 2 and 3, since userId=3 is a friend, and userId=4 is a friend of a friend).

I've come up with the following, but was wondering if there are better ways to do this (that don't involve a UNION).

SELECT posts.id FROM posts
INNER JOIN relations ON relations.userA=[userId]
WHERE relations.userB=posts.ownerId
UNION
SELECT posts.id FROM posts
INNER JOIN relations AS r1 ON r1.userA=[userId]
INNER JOIN relations AS r2 ON r2.userA=r1.userB
WHERE r2.userB=posts.ownerId AND r2.userB<>[userId];


texts:
I have multiple MySQL servers running on CentOS. I would like to use mytop to monitor real time database query activities, but do not want to install mytop locally on each of my MySQL instances. How can I install mytop on a network server to monitor multiple MySQL servers (nodes) on the same network just by running a command like the one below for example:

mytop -h db-test-1

This should display results for database db-test-1, and for db-test-2 or 3, just basically change the db hostname appropriately.


texts: How do I go about easy table input without creating an application?

texts:
I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something in a reference table rather than typing an arbitrary ID number.

I want to create the databases in postgresql properly (with ID numbers as primary key) - but don't want to have to look up the ID all the time when manually entering info into the database.

Is there a way I can use an INSERT statement (or something similar) and use a name field, rather than the auto ID?

Example of a record table and reference table:

Record Table:

CREATE TABLE testreport.records
(
  recordid serial NOT NULL,
  userid integer,
  CONSTRAINT records_pkey PRIMARY KEY (recordid),
  CONSTRAINT records_r_user_fkey FOREIGN KEY (userid)
      REFERENCES testreport.r_user (userid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Reference Table:

CREATE TABLE testreport.r_user
(
  userid serial NOT NULL,
  username text,
  CONSTRAINT r_user_pkey PRIMARY KEY (userid)
)

I want to be able to insert data by typing in the username, rather than having to type in the userid. Is there a way I can do this?

Note: All fields in username are unique

Thanks


texts: postgresql bigserial or sequence using jpa

texts: I'm a java developer working with jpa, before to define the indexes in my entities i would like to known  which one is faster o better to use?.


texts:
I have a very simple query:

SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id

With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to me). Test carried out on the messages table containing 1,000,000+ rows.

I have I have a primary key set on both tables (message_id and user_id) and a foreign key setup connecting messages.user_id and users.user_id.

The reason for this query is to provide the total number of records for a paging system.

What else can I do to speed up the query?


texts: Speed up complex join with disjunctive condition

texts:
I have a table that contains information about orders made by users and their phone numbers. The table looks like this one:

table Orders (
    OrderID int,
    UserName varchar(50),
    PhoneNumber1 varchar(50),
    PhoneNumber2 varchar(50),
    PhoneNumber3 varchar(50)
)

Problem: Fixed a user u, count the number of different users that share at least one phone number with u. In other words, count the number of distinct users v that satisfy one or more of the following conditions:

u.PhoneNumber1 = v.PhoneNumber1
u.PhoneNumber2 = v.PhoneNumber2
u.PhoneNumber3 = v.PhoneNumber3

My solution (OrderID is the primary key, I added the other columns in the group by clause just to display them in the result):

select o1.OrderID,
       o1.PhoneNumber1,
       o1.PhoneNumber2,
       o1.PhoneNumber3,
       count(distinct o2.UserName)
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    or o1.PhoneNumber2 = o2.PhoneNumber2
    or o1.PhoneNumber3 = o2.PhoneNumber3
group by o1.OrderID, o1.PhoneNumber1, o1.PhoneNumber2, o1.PhoneNumber3

The Orders table contains about 300K records, and my query requires too much time to complete (my estimate is about 3 hours, but I didn't let it finish completely).

What strategies can I adopt to speed up the computation? Is there, for example, an equivalent statement that yields the same result?

Note: I tried to create three non-clustered indexes on the PhoneNumber columns but didn't notice significant improvements.


texts:
I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data".

The table

CREATE TABLE IF NOT EXISTS details (
  id int(11) NOT NULL,
  date date NOT NULL,
  time int(2) NOT NULL,
  minutes_online decimal(5,0) NOT NULL,
  minutes_playing decimal(5,0) NOT NULL,
  minutes_chatting decimal(5,0) NOT NULL,
  minutes_away decimal(5,0) NOT NULL
  PRIMARY KEY (id,date,time)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

The simple query

mysql> SELECT * FROM details WHERE id = 3014595;

Explain

mysql> EXPLAIN SELECT * FROM details WHERE id = 3014595;
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | details   | ref  | PRIMARY       | PRIMARY | 4       | const | 1482 |       |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+

Profile for the query

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000078 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000126 |
| System lock                    | 0.000011 |
| Table lock                     | 0.000030 |
| init                           | 0.000027 |
| optimizing                     | 0.000117 |
| statistics                     | 0.040077 |
| preparing                      | 0.000029 |
| executing                      | 0.000006 |
| Sending data                   | 7.536960 |
| end                            | 0.000013 |
| query end                      | 0.000004 |
| freeing items                  | 0.000037 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+

As you can see, the SELECT statement used the index and read only 1482 rows. Yet, the query spent 7.536960 seconds sending the data. It is like the query read much more rows it needed.

It is a simple query, with just 7 fields (row avg 59 Bytes) and no fancy function. Any idea what can be causing this?

Note: id is the user ID. Each user may have at least one entry for every hour of every day. Therefore, id is not unique.

Edit: I have another table with the same structure and much more rows (34 Million). If I run the same query on this larger table, it returns the results in less than 1 second.

The only difference is that the larger table does not get as many queries as the smaller table.

Is it possible that the number of queries is slowing down the process? MySQL cache is on. I've also CakePHP caching the queries to reduce the number of queries.
Is it possible that the file where the table is saved is corrupted or something?

Update
The issue was solved by separating the data tier from the web tier. The data tier also got an upgrade on the RAM and is running on raid10.


texts:
I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure

USE [xxxx]
GO
/****** Object:  StoredProcedure [dbo].[MARK_ACTIVE]    Script Date: 4/20/2015 5:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[xxxx]
AS
SET ANSI_NULLS ON
INSERT INTO [123.456.7.890].[DBASE1].[dbo].[TABLE]      (smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX)
SELECT TOP 3 smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX
FROM [098.765.4.321].[[DBASE2].[dbo].[TABLE2] cr
WHERE
NOT EXISTS (SELECT  id,smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX          FROM [123.456.7.890].[DBASE1].[dbo].[TABLE]  c
  WHERE cr.smsdt = c.smsdt)

and here's my job settings

My General

My Steps

My advance steps

My Scheduler

When I run the stored proc it executes for less than 10 seconds but when i try to call that stored proc using the job this is what happen

it just keep on in progress state but nothing happens, looks like it got stuck in that state.


texts: column (column_name) of relation (table_name) does not exist

texts: The parameters are identical to the columns of the reference table, but still accuse that such a column does not exist in this table.


texts: Is there an Oracle rdbms monitoring API?

texts:
We have about a thousand customers with our application installed out of which 500 have an oracle database.

Monitoring the database is only one of the requirements.

Installing Oracle Enterprise Manager will give me all the info I need for my databases but no information for the other important metrics (we got some synchronization going on through database link) and a number of process which should be up and running outside of the db.

So my question boils down to this: Is there an oracle rdbms API I could use to integrate the oracle metrics we need in our monitoring tool?


texts: On my local computer, where is the data stored for a search on a dynamodb database

texts:
I am using RazorSQL to access a dynamodb database in a "SQL"-like way.
 One of the things RazorSQL lets you do is the sql command select * from table where ... on all columns. This is significant better than Amazon as Amazon only lets you do the filter on the hash and primary_key columns.

My question is whether the search is performed on my local computer or on the Amazon servers.

This matters because if my database has 100 lines and my search returns 3, I should only get charged for 3 rows of data retrieval if the search happens on the Amazon end, but if the search happens on my end, thus necessitating transfer of the datatable to my computer, I would get charged for 100 retrievals.


texts: Improving lots of Left Joins ( SQL Server 2008 - procedure )

texts:
This query is running with 8 minutes to complete.

I'm trying to help the programmer with this task.

I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us.

What do you guys do, when you face a query like this??

declare @CodSegmentacao int,
        @CodOndaAtu int,
        @CodOndaAnt int,
        @CodJobTarget int

set @CodSegmentacao = 9
set @CodOndaAtu = 55
set @CodOndaAnt = 52
set @CodJobTarget = 10771

select
    TA.Ordem, TA.DescJobRel, TA.CodJob as CodJobAtu,
    isnull(TB.CodJob, 0) as CodJobAnt,
    isnull(TC.Quant, 0) as AmostraAtu,
    isnull(TD.Quant, 0) as AmostraAnt,
    isnull(TE.Perc, 0) as TGWTotalAtu,
    isnull(TF.Perc, 0) as TGWTotalAnt,
    isnull(TG.Perc, 0) as TGWOnlyAtu,
    isnull(TH.Perc, 0) as TGWOnlyAnt,
    isnull(TI.Perc, 0) as TGWDesigAtu,
    isnull(TJ.Perc, 0) as TGWDesigAnt,
    isnull(TK.Perc, 0) as DesvioAtu,
    isnull(TL.Perc, 0) as DesvioAnt,
    isnull(TM.Perc, 0) as DesvioSDAtu,
    isnull(TN.Perc, 0) as DesvioSDAnt,
    isnull(TP.Perc, 0) as DesvioDAtu,
    isnull(TQ.Perc, 0) as DesvioDAnt
from
    (select
         T2.CodJobRel, T2.DescJobRel,
         case when T1.FlagTotal = 1 then 0 else case when T4.CodJob = @CodJobTarget then 1 else 2 end end as Ordem,
         T4.CodJob
     from
         tblSegmentacaoJobRel T1 with (nolock)
     inner join
         tblJobRel T2 with (nolock) on T1.CodJobRel = T2.CodJobRel
     inner join
         tblJobRelJob T3 with (nolock) on T2.CodJobRel = T3.CodJobRel
     inner join
         tblJob T4 with (nolock) on T3.CodJob = T4.CodJob
     where
         T1.CodSegmentacao = @CodSegmentacao and T4.CodOnda = @CodOndaAtu) TA
left join
   (select
        T2.CodJobRel, T2.DescJobRel, T4.CodJob
    from
        tblSegmentacaoJobRel T1 with (nolock)
    inner join
        tblJobRel T2 with (nolock) on T1.CodJobRel = T2.CodJobRel
    inner join
        tblJobRelJob T3 with (nolock) on T2.CodJobRel = T3.CodJobRel
    inner join
        tblJob T4 with (nolock) on T3.CodJob = T4.CodJob
    where
        T1.CodSegmentacao = @CodSegmentacao and T4.CodOnda = @CodOndaAnt) TB on TA.CodJobRel = TB.CodJobRel
left join
    tblConsolidadoJob TC with (nolock) on TA.CodJob = TC.CodJob and TC.TipoCons = 'Amostra'
left join
    tblConsolidadoJob TD with (nolock) on TB.CodJob = TD.CodJob and TD.TipoCons = 'Amostra'
left join
    tblConsolidadoJob TE with (nolock) on TA.CodJob = TE.CodJob and TE.TipoCons = 'TGWTotal'
left join
    tblConsolidadoJob TF with (nolock) on TB.CodJob = TF.CodJob and TF.TipoCons = 'TGWTotal'
left join
    tblConsolidadoJob TG with (nolock) on TA.CodJob = TG.CodJob and TG.TipoCons = 'TGWOnly'
left join
    tblConsolidadoJob TH with (nolock) on TB.CodJob = TH.CodJob and TH.TipoCons = 'TGWOnly'
left join
    tblConsolidadoJob TI with (nolock) on TA.CodJob = TI.CodJob and TI.TipoCons = 'TGWDesig'
left join
    tblConsolidadoJob TJ with (nolock) on TB.CodJob = TJ.CodJob and TJ.TipoCons = 'TGWDesig'
left join
    tblConsolidadoJob TK with (nolock) on TA.CodJob = TK.CodJob and TK.TipoCons = 'Desvio'
left join
    tblConsolidadoJob TL with (nolock) on TB.CodJob = TL.CodJob and TL.TipoCons = 'Desvio'
left join
    tblConsolidadoJob TM with (nolock) on TA.CodJob = TM.CodJob and TM.TipoCons = 'DesvioSD'
left join
    tblConsolidadoJob TN with (nolock) on TB.CodJob = TN.CodJob and TN.TipoCons = 'DesvioSD'
left join
    tblConsolidadoJob TP with (nolock) on TA.CodJob = TP.CodJob and TP.TipoCons = 'DesvioD'
left join
    tblConsolidadoJob TQ with (nolock) on TB.CodJob = TQ.CodJob and TQ.TipoCons = 'DesvioD'
order by
    TA.Ordem, TA.DescJobRel

Since now, thanks you very much.

I was looking at the execution plan. I didn't see problems there.


texts:
Suppose we have a simple update query:

UPDATE foo
SET
  bar=$1
WHERE id=$2

where bar=$1 is generated based on user's input and it might be null, e.g.

UPDATE foo
SET
  ${assignmentList}
WHERE id=$2

How do I make this update operation into a no-op in case assignmentList variable is empty?

I have tried using 1=1, i.e.

UPDATE foo
SET
  1=1
WHERE id=$2

but this gives a syntax error.