texts: MFA and Scheduled Jobs/Tasks against your data sources

texts:
I have a question for those of you who are running with MFA as part of the auth process against your data sources: How do you handle Scheduled Jobs? Said job might be launched by your Data Platform's "built-in" scheduler, and maybe there are some straigh-forward allowances baked right into the software for that sort of scenario.
But how do you deal with situations where some third-party app (like an ETL tool, or a self-service analytics solution like Tableau or Alteryx Server) wants to launch a scheduled job in a non-interactive mode at 3a or somesuch? Seems like a stretch.
Thanks!


texts: Merge jsonb objects and sum their values over multiple rows

texts:
I save visits to my database. In my database I have a table with aggregations of visits called visits_agg. This table has one row per hour per hostname. Both are fields in the table which will be used to query the rows needed for viewing statistics.

For example I will query for the visits in the last 24 hours:

SELECT * FROM visits_agg WHERE hostname = 'example.com' AND hour > NOW() - INTERVAL '24 hours'

To create the structure I used this query:

CREATE TABLE IF NOT EXISTS visits_agg (
  id          serial primary key,
  hour        timestamp without time zone default (now() at time zone 'utc'),
  hostname    text not null,
  visits      integer default 0,
  referrers   jsonb not null default '{}'::jsonb,
  paths       jsonb not null default '{}'::jsonb,
  widths      jsonb not null default '{}'::jsonb
);

With some dummy data:

INSERT INTO "visits_agg" ("hour", "hostname", "visits", "referrers", "paths", "widths") VALUES
('2019-01-16 09:00:00', 'example.com', '6', '{"example2.com": 4}', '{"/": 5, "/about": 1}', '{"1280": 5, "1283": 1}'),
('2019-01-16 14:00:00', 'example.com', '7', '{"google": 6}', '{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}', '{"360": 3, "1398": 1, "1680": 1, "2560": 1}'),
('2019-01-16 15:00:00', 'example.com', '6', '{"google": 6}', '{"/about": 1, "/article/title1": 1, "/article/title2": 3, "/": 1}', '{"360": 3, "1280": 2, "1920": 1}'),
('2019-01-16 16:00:00', 'example.com', '6', '{"facebook": 1, "example2.com": 1}', '{"/": 4, "/about": 1, "/article/title1": 1}', '{"360": 1, "1440": 2, "1680": 2, "1920": 1}');

I would like to get the rows from a certain period and sum all referrers, paths, and widths.

So the result of the above records should be something like this

'example.com',
'25',
'{"example2.com": 4, "google": 12, "facebook": 1}',
'{"/": 11, "/about": 6, "/article/title1": 3, "/article/title2": 5}',
'{"360": 7, "1398": 1, "1440": 2, "1680": 3, "2560": 1, "1280": 7, "1283": 1, "1920": 2}'),

The keys in the jsonb objects can be any string and the values are always numbers. I tried to merge the objects, but can't figure out how to sum the values inside the jsonb objects.


texts: Delete duplicated rows by modified_date - PostgreSQL 9.2

texts:
I've got some duplicated rows on a table called ja_jobs:

To find those duplicated data, I'm running this Query:

select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false AND time_job IS NOT NULL AND (time_job,recurrenceid) IN (
select time_job,recurrenceid FROM ja_jobs WHERE clientid = 33731 GROUP BY time_job,recurrenceid HAVING count(*) > 1
)

The query finds duplicated rows by time_job and recurrenceid.

On the following example:

You can see that the jobs are duplicated, but we have three versions of it (Just look on the modified_date column)

I need to delete the new jobs and only keep the OLDEST one.

DELETE from ja_jobs WHERE id IN (14754912,14792799);

How can I do that?
How can I select all the newest jobs and just delete them?

Here what I've got so far:

select min(id) over (partition by time_job,recurrenceid,time_arrival order by created_date) as min_id into junk.test_table FROM ja_jobs
WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false AND (time_job,recurrenceid) IN (
select time_job,recurrenceid FROM ja_jobs WHERE clientid = 33731 GROUP BY time_job,recurrenceid HAVING count(*) > 1
)

But on the junk.test_table table I got duplicated "min_id"


texts: Acceptable to use Antelope and Barracuda simultaneously?

texts:
I've been doing some reading about the Antelope and Barracuda file formats for InnoDB.  I recall at least one source mentioning that the two should not be used simultaneously.

I can't think of a good reason (while using innodb_file_per_table) that I couldn't use Antelope on one table and Barracuda on another.  However, maybe I'm missing something.

Are there any good reasons to avoid using Antelope and Barracuda simultaneously?

(Note: The reason I'm planning on using both simultaneously is that I don't want to mess with our existing (quite large) tables.  Perhaps this isn't a big deal?  Is migrating from Antelope to Barracuda trivial, even for large tables?)


texts: Postgres 9.4 to 9.5 using Yum and pg_upgrade - CentOS 6.7 (Final)

texts:
I am attempting to do an upgrade between Postgres 9.4 to 9.5 on a CentOS 6.7 system.

I have reviewed both:
http://www.postgresonline.com/journal/archives/362-An-almost-idiots-guide-to-install-PostgreSQL-9.5,-PostGIS-2.2-and-pgRouting-2.1.0-with-Yum.html

http://www.postgresql.org/docs/9.5/static/pgupgrade.html

My only concern is data corruption and loss, after some searching it seems that others are having issues during such an upgrade. I was hoping someone with some more experience performing version upgrades with Postgres could provide some insight on the best approach and what I should be concerned about during the upgrade.


texts: ORA-29913 and ORA-30653 error selecting an external table

texts:
I created an external table using a .tbl file and now Im trying to select all rows from that table, using:

select * from users_load;

But Im getting this error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

Do you know why this error is happening? This table have millions of rows, I dont know if can be because of this...


texts:
I have done some research and found that I should store a route as a sequence of stops. Something like:

Start -> Stop A -> Stop B -> Stop C -> End

I have created three tables:

Routes
Stops
RouteStops

...where RouteStops is a junction table.

I have something like:

Routes

+---------+
| routeId |
+---------+
|    1    |
+---------+
|    2    |
+---------+

Stations

+-----------+------+
| stationId | Name |
+-----------+------+
|     1     |   A  |
+-----------+------+
|     2     |   B  |
+-----------+------+
|     3     |   C  |
+-----------+------+
|     4     |   D  |
+-----------+------+

RouteStations

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     2       |       A       |
+-------------+---------------+
|     2       |       D       |
+-------------+---------------+

Route 1 goes through

Station A -> Station C -> Station D

Route 2 goes through

Station A -> Station D

Is this a good way to store routes?

According to Wikipedia:

  [...] the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified [...]

Can I rely on such a database schema or maybe this should be done differently?

This is actually my university project, so I'm just wondering if such schema can be considered as a correct one. For this case, I would probably store only several routes (approx 3-5) and stations (approx 10-15), each route will consist of about 5 stations. I would be also glad to hear how this should look like in case of real and big bus company.


texts:
I have a very basic query against a large table that roughly follows this form:

SELECT
    [Extent1].[A] AS [A],
    [Extent1].[B] AS [B]
FROM [dbo].[SomeTable] AS [Extent1]
WHERE
    ([Extent1].[A] <= (SysUtcDateTime()))
    AND (2 = [Extent1].[B])

This query spends 100% of its time using the clustered index. In different environments I have observed these results:

DEV: 391 rows, 12 logical reads
QA: 341502 rows, 6030 logical reads
UAT: 1273656 rows, 52536 logical reads

It seems to me that this query is about as efficient as it can be given the size of the result set.

Is there a generally accepted correlation between logical reads and the amount of data returned by a query? If so, is it safe to say that these results fall within that acceptance range for a well-performing query?


texts: Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

texts:
Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered indexes, implementing row or page compression, etc.

However, there are a few cases where I was able to reclaim even more space by executing DBCC CLEANTABLE. With hundreds of databases in my environment, it's not possible to know what users do in each one and it's completely acceptable that there will be changes involving the dropping of fixed length columns. I've typically found these opportunities by looking at row counts vs. page counts in some object space utilization scripts I've written. I'd like to take this a step further by attempting to automate the detection of these sorts of scenarios.

What I'd like to know is if anyone out there is actively monitoring for these sorts of opportunities and if so, what do you look for specifically?

My thoughts were to write something along the lines of gathering the max and min size of a row, the number of rows in the table, the number of pages allocated, and the number of pages used, then doing some basic math to log results that are well outside what would be "expected".


texts: Improving performance of a weighted search query in SQLite3

texts:
For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight:

CREATE TABLE search_index (
    id INTEGER(7) REFERENCES other_table,
    term TEXT,
    weight INTEGER(2) DEFAULT 1
);

The weight changes with importance of the term. Think header text vs body copy. Typical entries might look like

> SELECT * FROM search_index;
id|term|weight
1 |foo |10
1 |bar |5
2 |foo |4
3 |baz |20

I query the table by searching for the matching term, then summing up the weights:

SELECT id
    FROM search_index
    WHERE term = 'foo' OR term = 'bar'
    GROUP BY id
    ORDER BY SUM(weight) DESC
    LIMIT 0,128;

The results are paginated. To get the total matches, I count distinct matches:

SELECT COUNT(DISTINCT id) AS count
    FROM search_index
    WHERE term = 'foo' OR term = 'bar';

In theory, these two queries work like a charm and do exactly, what they should. However, in cases with huge result sets (up to 100,000 in my case) the queries become slow beyond usability.

I am quite sure, that the DISTINCT and GROUP BY/ORDER BY SUM() clauses kill any possible query optimizations. I added several indexes, but none are used in the queries (which doesn't surprise me, to be honest), e.g. in an approach to get this optimization. However, I have no idea, if I can

enhance the queries, so that they run with better performance, or
change the schema somehow.

I am happy for any pointers or help. Changing the schema would be possible, so if I went the wrong direction with my search_index table, I'd be glad to change that to a better design.

(Last point: My data is mostly static, there are close to zero write accesses to the table, if that's somehow helpful.)


texts: Is this "idiosyncratic" method to manage constraints declarations beneficial?

texts:
I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this.

At my company, we have a large .sql file that holds all of our schema scripts which get applied to customer servers during updates.

In this file, most schema statements are explicitly typed out (i,e, ALTER TABLE Foo  DROP COLUMN Bar) except for constraints.

For some reason, a previous DBA created a table called DBConstraints. I have included the schema below along with a couple of sample rows on a fake table. They include primary, foreign, unique, check, and default constraints in said table.

Once the rows are in this table, during application of the schema scripts, we call a stored procedure that creates, drops, and applies the constraints from the DBConstraints table based on the constraint's type and definition.

Here is the table along with some sample data.

TABLE DBCONSTRAINTS
ConstraintType | TableName | ConstraintName | ColumnSetDefinition | ReferenceTableName

Primary / Foo / PK_FOO_KeyId | (KeyId) | NULL
Foreign / Foo / FK_Foo_Bar_Link | (ForeignId) references Bar(ForeignId) on delete cascade / Bar
Default / Bar / DK_Bar_UpdatedDate | (GETDATE())

There is a stored procedure called gp_UpdateConstraints which processes them.
An example of one of our schema scripts is something like this:

gp_UpdateConstraints @Action = 'Drop', @TableName = 'Foo' --This will drop constraints.
ALTER TABLE FOO ADD TestColumn BIT NULL;

DELETE FROM DBConstraints WHERE ConstraintName = 'DK_Foo_TestColumn';

    INSERT DBConstraints (ConstraintType, TableName, ConstraintName, ColumnSetDefinition)
    VALUES ('Default', 'Foo', 'DK_Foo_TestColumn', '(0) for TestColumn');

gp_UpdateConstraints @Action = 'Apply', @TableName = 'Foo' --This will reapply constraints.

To me, this seems to be an unnecessary and non-standard way of maintaining constraints, and I don't really see any advantage of doing it this way versus just maintaining the constraints normally. The only thing I know that the procedure does is automatically search for dependencies in foreign keys and drop them based on the table that is being worked on, but even that isn't foolproof.

Does anyone have any ideas why this may have been done this way, and whether it is actually better?

Thanks!


texts: I'm using SQL Server 2008. I would like to know if a specific database has been restored from a device and when did it happened. Is it possible?


texts: Can I get SSMS to show me the Actual query costs in the Execution plan pane?

texts:
I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on.

I understand from How do I read Query Cost, and is it always a percentage? that even when SSMS is told to Include Actual Execution Plan, the "Query cost (relative to the batch)" figures is still based on cost estimates, which can be far off actuals

I understand from Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken” that I can surround invocation of the stored procedure with SET STATISTICS TIME statements, and I will then get a list like this in the Messages pane:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

[etc]

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 206 ms.

with one output message for each statement.

I can 'easily' (though not conveniently) associate the time statistics output with the statement-by-statement execution plans in the Execution plan pane, by counting them: The fourth SQL Server Execution Times message output corresponds to Query 4 in the Execution plan pane, and so on.

But is there a better way?


texts: How fast should I expect to load data on a high performance computer?

texts: I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice so I can start estimating. If you just know the answer on other databases, I would be interested to compare.


texts: plpgsql function to replace ' ' with null after insert using a trigger

texts:
I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was inserted for a specific column.
I want to run a trigger function after each insert to replace the ' ' with Null. However, I am struggling with writing the function.

What I have thus far:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = Null,
deliv2 = Null,
fmt = Null,
spec_instr = Null
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';

This clears the ' ' but it also clears any new input in the columns specified in the function. As an example, if 'this is special instructions' is inserted into column general.spec_instr, the input is cleared after the function ran. How do I rectify this?
Also, is there a way to check all columns of new inserts for ' ' without having to specify the specific column name as in the function above?

EDIT:

CREATE TRIGGER del_spaces_trig
  AFTER INSERT
  ON projects.general
  FOR EACH STATEMENT
  EXECUTE PROCEDURE public.delete_space();


texts:
Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the internal date format, which I need to get down to the second (ie 'mm/dd/yyyy hh12:mi:ss').  I do NOT want to change the query of the output format, I want to change the stored value to be updated.

Question: Is it even possible to get the database precision down to the second? If so how, I assume its an update or alter command?


texts: Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF

texts:
In my question over at Pull every other row, starting at 0, and concatenate them together user @AaronBertrand provided me with a chunk of code that worked fine in test but as soon as I put the procedure using the code into place and tested from the calling app I get an error.

  SELECT error: SELECT failed because the following SET options have
  incorrect settings: 'CONCAT_NULL_YIELDS_NULL'

In testing if I set CONCAT_NULL_YIELDS_NULL OFF the procedure will fail when called from SSMS but if I set CONCAT_NULL_YIELDS_NULL ON it works fine, further testing is showing the calling app to explicitly be setting CONCAT_NULL_YIELDS_NULL OFF and I can not override that. So I need to figure out how to update the code block to prevent this problem.

SELECT @TreeLocationStructure = STUFF (
    (
        SELECT ' -> ' + TreeLocation
        FROM (
            SELECT rn = ROW_NUMBER()
            OVER (ORDER BY ID), TreeLocation
            FROM #TreeSplit
        ) AS x(rn, TreeLocation)
        WHERE rn % 2 = 1
        ORDER BY rn FOR XML PATH,TYPE
    ).value('.[1]','nvarchar(max)'), 1 ,4, ''
);


texts: Handling misspellings when searching across tsvector columns

texts:
I have a projects table with a tsvector column. The column is populated and refreshed via a trigger function when some of the other columns on a given row change:

CREATE OR REPLACE FUNCTION refresh_project_search_vector() RETURNS trigger AS $$
    BEGIN
        NEW.project_search_vector :=
            setweight(to_tsvector('english', NEW.id::text), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.name,'')), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.description,'')), 'B');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

I perform searches using:

...
WHERE project_search_vector @@ plainto_tsquery(search_term)
...

This seems to work well.

However, I also want to account for the user misspelling something. For example, if they search for Californa, I want Postgres to account for that and include/match tsvectors that have "California". (I know about stemming, but it doesn't seem sufficient for many types of misspellings like this.)

So my question is, is there a way to use a similarity logic when using tsvectors for full-text search?

Apologies if I'm asking the wrong question - still new to full-text searching.


texts: Is there a syntax for accessing table names containing slashes?

texts:
I have a linked server registered in my SQL Server 2005 which accesses our SAP database (test system). It uses an ODBC driver provided by SAP, and is connected using the Microsoft OLEDB For ODBC driver. The remote database is a MaxDB 7.7.

I'm currently exploring the possibilities of directly accessing data within our SAP system, but have run into problems regarding certain tables.

The following works without problems:

SELECT Field FROM LinkedServer..Schema.Table WHERE MANDT = '110'

or, respectively

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT Field FROM Table WHERE MANDT = ''110''')

Now, there's some nasty table names within SAP that can contain slashes. I think it's meant to be a "namespace" type of thing contained within the actual table name. On the ABAP side of things, I can easily do

SELECT * FROM /MYWHATEVER/MYTABLE

provided the table exists. But I can't get the table names containing / working via linked server.

Trying

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM /MYWHATEVER/MYTABLE')

gives me "Error 7321 while preparing query...". Using

SELECT * FROM LinkedServer..Schema.[/MYWHATEVER/MYTABLE]

yields "Error 7306 OLEDB-Provider "MSDASQL" can't open table "Schema./MYWHATEVER/MYTABLE" for linked server LinkedServer, it contains errors or doesn't exist".

Is there any way I can access tables containing / on a MaxDB via LinkedServer?

[The error messages may not be literally the same as on an english system, since I had to translate them from german]


texts: Integrating large PostgreSQL table as partition for a new table

texts:
I currently have a relatively large table containing timeseries data (628+ mil. live rows). The table definition (some names changed slightly) at the bottom of question.
I want to attach the existing table as a partition to a new (partitioned) table. However, the existing table has a singular id primary key (mainly used because Django requires it). Attaching the table would require me to update the primary key constraint to (id, timestamp) on the old table.
Because id is unique this isn't an issue, but given the size of the table I wonder if this constraint is checked upon creation (leading to quite the query) or whether only newly added/updated rows are checked. Stopping read/writes to the table for a few minutes is possible, but I can't wait for multiple hours.

Intended new table
Like the old table the id column is mainly required by our ORM sadly. A similar (prop_id, "timestamp", value) index would be used on the partitioned table as well.
CREATE TABLE "newtable" (
    "id"            bigserial NOT NULL,
    "timestamp"     timestamp with time zone NOT NULL,
    "prop_id"       integer NOT NULL,
    "value"         double precision NOT NULL,
    PRIMARY KEY ("id", "timestamp")
) PARTITION BY RANGE ("timestamp")
;

Old table definition
The "id" primary key is an artifact of our ORM (Django) and is inconsequential for any queries we do. We use the (prop_id, "timestamp", value) index 99.9% of the time for index-only scans.
  Column   |           Type           | Collation | Nullable |                         Default                     | Storage | Stats target | Description
-----------+--------------------------+-----------+----------+----------------------------------------------------------+---------+--------------+-------------
 id        | bigint                   |           | not null | nextval('tablename_id_seq'::regclass) | plain   |              |
 timestamp | timestamp with time zone |           | not null |                     | plain   |              |
 value     | double precision         |           | not null |                     | plain   |              |
 prop_id   | integer                  |           | not null |                     | plain   |              |
Indexes:
    "tablename_pkey" PRIMARY KEY, btree (id)
    "tablename_prop_id_timestamp_value_b9bc8326_idx" btree (prop_id, "timestamp", value)
Foreign-key constraints:
    "tablename_prop_id_67f339b0_fk_othertable" FOREIGN KEY (prop_id) REFERENCES othertable(id) DEFERRABLE INITIALLY DEFERRED


texts: How to convert control characters in MySQL from latin1 to UTF-8?


texts: Affected rows after update with OpenQuery

texts:
Is there a way to get the affected row count, when updating through an connection server?

I'm using MSSQL connected to MySQL with ODBC.

UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM table WHERE status=0') SET status=1


texts:
I am going through cleaning up one of our databases, and after deleting an unused index and adding a primary key to the ARPayments table, sp_BlitzIndex is now reporting an index dbo.ARPayment.Unknown (0). I have refreshed my indexes and there is definitely no sign of any such index.

Here is the message it shows in the output from running sp_BlitzIndex 'db_name', @Mode=4:

  Should this table be a heap? dbo.ARPayment.Unknown (0)


texts: Mysql fulltext search my.cnf optimization

texts:
I have open a question on https://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here.

We built a news site. Every day we will input tens of thousands data from web api.

In order to provide a precision search service, our table uses MyISAM, building a fulltext index (title, content, date). Our site is in testing on Godaddy VDS with 2GB RAM, 30GB space (No swap, because VDS do not allow to build swap). The CPU is Intel(R) Xeon(R) CPU L5609 @ 1.87GHz

After running a ./mysqltuner.pl

We get some results:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.20
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 396M (Tables: 39)
[--] Data in InnoDB tables: 208K (Tables: 8)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[!!] User '@ip-XX-XX-XX-XX.ip.secureserver.net'
[!!] User '@localhost'

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 27m 58s (1M q [20.253 qps], 31K conn, TX: 513M, RX: 303M)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 573.8M (28% of installed RAM)
[OK] Slow queries: 0% (56/1M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/162.5M
[OK] Key buffer hit rate: 100.0% (2B cached / 882K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 17K sorts)
[!!] Temporary tables created on disk: 49% (32K on disk / 64K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 298K opened)
[OK] Open file limit used: 41% (421/1K)
[!!] Table locks acquired immediately: 77%
[OK] InnoDB data size / buffer pool: 208.0K/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 400)

And here is my.cnf

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 16M
max_connections = 1024
wait_timeout = 5
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size= 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len = 2
read_rnd_buffer_size=2M
tmp_table_size=128M

I am not sure how to optimization my.cnf depend on ./mysqltuner.pl return results.


texts:
How can i check if IFI is enabled on my PROD server?

I did the following checks:

Ensured that the service account under which SQL Server is running is part of Administrators group.
Perform Volume Maintenance tasks permission is set for "Administrators"

Does this mean that IFI enabled?


texts: How to handle lock timeout error in MySQL?

texts:
I am getting below listed error many times in my application log.

2015-05-19 10:27:57,842 ERROR [Timer-3] [org.hibernate.util.JDBCExceptionReporter] Lock wait timeout exceeded; try restarting transactionCouldn't retrieve InnoDB stat
us due to underlying exception:

BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException
MESSAGE: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

STACKTRACE:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)

I know this error can be happen when one thread has locked particular row of table, and second thread is waiting for it and this waiting time exceed certain amount of time.

But how can I optimize MySQL Configuration or my code to overcome this situation?

I have already read this article about isolation level, and I have already set isolation level to READ COMMITTED.

Is there any expert advice/solution rather than I have mentioned?

EDIT:
I have also increased lock_timeout_period.


texts:
We have a major issues on our MongoDB and I've attached the latest logs and files.  In previous forumns I've seen this get fixed by your team.  Can you please help as soon as possible please, we are a medical facility and need this fixed.
We are running on Windows 2016 Server and running fine until a power outage.
2020-09-11T14:34:18.780-0400 I CONTROL  [main] Trying to start Windows service 'MongoDB'
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] MongoDB starting : pid=1816 port=27017 dbpath=C:\Program Files\DELL\WMS\Database\Mongo 64-bit host=VLDATAWMS02
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] targetMinOS: Windows Vista/Windows Server 2008
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] db version v3.4.10
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] git version: 078f28920cb24de0dd479b5ea6c66c644f6326e9
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] allocator: tcmalloc
2020-09-11T14:34:18.782-0400 I CONTROL  [initandlisten] modules: none
2020-09-11T14:34:18.784-0400 I CONTROL  [initandlisten] build environment:
2020-09-11T14:34:18.784-0400 I CONTROL  [initandlisten]     distarch: x86_64
2020-09-11T14:34:18.784-0400 I CONTROL  [initandlisten]     target_arch: x86_64
2020-09-11T14:34:18.785-0400 I CONTROL  [initandlisten] options: { config: "C:\Program Files\DELL\WMS\MongoDB\mongod.cfg", net: { bindIp: "localhost", port: 27017 }, security: { authorization: "enabled" }, service: true, storage: { dbPath: "C:\Program Files\DELL\WMS\Database\Mongo" }, systemLog: { destination: "file", path: "C:\Program Files\DELL\WMS\MongoDb\mongod.log" } }
2020-09-11T14:34:18.785-0400 I -        [initandlisten] Detected data files in C:\Program Files\DELL\WMS\Database\Mongo created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2020-09-11T14:34:18.786-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=3583M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2020-09-11T14:34:18.797-0400 E STORAGE  [initandlisten] WiredTiger error (0) [1599849258:797507][1816:140721228962256], file:WiredTiger.wt, connection: read checksum error for 4096B block at offset 24576: block header checksum of 1919248233 doesn't match expected checksum of 3720300442
2020-09-11T14:34:18.797-0400 E STORAGE  [initandlisten] WiredTiger error (0) [1599849258:797507][1816:140721228962256], file:WiredTiger.wt, connection: WiredTiger.wt: encountered an illegal file format or internal value
2020-09-11T14:34:18.797-0400 E STORAGE  [initandlisten] WiredTiger error (-31804) [1599849258:797507][1816:140721228962256], file:WiredTiger.wt, connection: the process must exit and restart: WT_PANIC: WiredTiger library panic
2020-09-11T14:34:18.797-0400 I -        [initandlisten] Fatal Assertion 28558 at src\mongo\db\storage\wiredtiger\wiredtiger_util.cpp 361
2020-09-11T14:34:18.797-0400 I -        [initandlisten]
***aborting after fassert() failure
Thank You,


texts: SPExecuteSQL;1 keeps executing internally

texts:
Having a major issues with this procedure called SP_ExecuteSQL;1.

It keeps executing internally in SQL Server 2000 with high utilization of CPU. When I pass its SPID in DBCC inputbuffer it shows as internal.

Is there any way of stopping it?


texts: SSMS suddenly starts generating DEFAULT constraints inlined into the CREATE TABLE statements instead of ALTER TABLE

texts:
I've been using SSMS to generate scripts of the whole database and storing them in the source control.

It's been working fine for many years, but few days ago I noticed that SSMS started generating DEFAULT constraints as a clause inlined into the CREATE TABLE statement instead of adding a separate ALTER TABLE statement.

Here is an example of one table.
This is how the script looked like before:

USE [my db name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConsignmentGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsignmentID] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Notes] [nvarchar](255) NOT NULL,
    CONSTRAINT [PK_ConsignmentGroups] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ConsignmentGroups] ADD  CONSTRAINT [DF_ConsignmentGroups_Notes]  DEFAULT ('') FOR [Notes]
GO

Here DEFAULT constraint is scripted as a separate ALTER TABLE statement.

This is how it looks like now:

USE [my db name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConsignmentGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsignmentID] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Notes] [nvarchar](255) NOT NULL CONSTRAINT [DF_ConsignmentGroups_Notes]  DEFAULT (''),
    CONSTRAINT [PK_ConsignmentGroups] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Here DEFAULT constraint is inlined into the CREATE TABLE statement.

It didn't happen to all tables with DEFAULT constraints. There are about 60 tables with DEFAULT constraints and at least one of them is still scripted as ALTER TABLE statement. I don't think anyone touched the table in the example above (ConsignmentGroups), there were no changes to the table structure. The only change is the style of DEFAULT constraint.

The problem is that this kind of change in the T-SQL script is picked up by the source control as a change. I'd like to know how to control it to avoid it in the future.

I assume that I accidentally changed some setting in SSMS without realising what it does. Does anyone know where this setting could be?

I use SQL Server 2008 Standard

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)
    Sep  3 2014 04:11:34
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

SSMS 2012

Microsoft SQL Server Management Studio     11.0.6020.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML                            3.0 4.0 5.0 6.0
Microsoft Internet Explorer                9.11.9600.18376
Microsoft .NET Framework                   4.0.30319.42000
Operating System                           6.1.7601

and SSMS Boost build 2.19.5952.29737

I don't remember updating any of these components or changing any settings for a while, but apparently something has changed.

I tried to generate these scripts using SSMS 2014 (12.0.4459.0) without SSMSBoost installed on a virtual machine that I don't usually use.

To my surprise the result was the same. The scripts generated by different versions of SSMS running from different computers are identical. This means that it is not a setting in SSMS, there must be something in the database itself.

Also, the fact that one table out of ~60 is scripted differently suggests that this setting is not database-wide, but works on a table level.


texts: Simple query hangs on a specific column condition

texts:
I have two servers, and I a dumped the database of the first to restore it on the second.

Everything works great, except that on the second server, I can't do any selection based on a specific column "user_id" anymore : SELECT * FROM products WHERE user_id = 12 limit 1;

Each time I run it on the second server, it stays still, without any error. And every other request that concerns a selection based on the user_id column is also stuck (where user_id IS NOT NULL, etc.).

I launched mysqlcheck, everything seemed ok, I restarted mysql, but nothing works.

Any idea how to troubleshoot this ?

EDIT :

Curiously, SELECT * FROM products WHERE user_id IS NULL limit 1; works fine.


texts: Grant to one; then revoke from all: does it work in Oracle SQL?

texts:
Consider the following:

grant select on books to frank.
revoke select on books from public.

At the end of executing the above SQL script, will frank have 'select' access to the 'books' table?


texts: Does "Table Valued Function" force the plan to get into cache?

texts: sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?


texts: Is it possible to run query against only part of the Central Managed Servers?

texts:
For example, I want to run this query to get the status of some servers having high availability group set up:

SELECT  s.role_desc,  g.name, r.replica_server_name , GETDATE() as checkTime
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_replicas AS r
        ON s.replica_id = r.replica_id
    INNER JOIN  sys.availability_groups g
        ON r.group_id = g.group_id

...but some of the managed servers are SQL Server 2008, which don't have those views in the query, and hence the query returns error.

I have tried something like:

SELECT  s.role_desc,  g.name, r.replica_server_name , GETDATE() as checkTime
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_replicas AS r
        ON s.replica_id = r.replica_id
    INNER JOIN  sys.availability_groups g
        ON r.group_id = g.group_id
where  r.replica_server_name in
(select server_name  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
where server_name like 'theserversIwant%a'
)

...but it doesn't work. It still queries the entire list of all the managed servers.


texts: How to set AUTHORIZATION on CREATE SCHEMA

texts:
I'm looking to create a new schema for an existing database, which currently only uses the dbo schema. I'm after advice on how to set the AUTHORIZATION (owner name) and what affect this will have.

1. Should I create a new user for this or can I use the dbo user?

I notice the existing Database Roles in the database are owned by the dbo user.

2. If I set the AUTHORIZATION to a new user will I need to create new Database Roles owned by the new user?

I've also noticed the Default schema for existing Users is the dbo schema.

3. Will I need to create new Users with a default schema set to the new schema?

My main reason for creating the new schema is to separate the database objects by name rather than for permissions. I intend on granting certain existing Users access to specific database procedures on the new schema as we do for database procedures on the dbo schema.


texts:
I have a situation, in which i need to design a database for some bakeries. I will need to design the same thing for many bakeries. For each bakery i will have for example, an Employee table, and a Product table (this will in reality much more complicated). The employees and the products do have relationships between them, but entities from one bakery never have any relationship with entities from another bakery. I was thinking, instead of making a database that has a Bakery entity as well, can't i just make several databases that only have Employees and Products in it, and then just pull data from the database i need based on which bakery is requesting it. Since every bakery will have the exact same tables, and the exact same attributes, and bakeries will never interact between each other, i was thinking this might be a good idea.

This is obviously a test example that i am working on for a web app    class project, so I'm pretty new to this, and I'm not sure if    something like this exist.

Do people do this (splitting up your data in multiple databases in
situations like this)?
Is is too slow?
Will just making a Bakery entity and then indexing it be better, even
though my database diagram will get MUCH more complicated that way?


texts:
I have been given the task to update a few rows in a database. There are two columns in which one is ID and the other is CURRENCY. I have updated these rows using the update statements that follow:
update account set currency = 'INR' where id =15;
update account set currency = 'EURO' where id =12;
update account set currency = 'DOLLAR' where id =18;
update account set currency = 'Pound' where id =13;
-- and so on.

Actually, in this case I was able to do it easily using these update statements because of a relatively low number of rows, but what if there are thousands or hundreds of thousands of rows? Is this the only way to update them, or is there any alternate way or script?


texts: Table Field to Reference Field in Other Tables

texts:
Suppose I want to map a set of input values to database table fields E.g.

employeeFirstName --> emp.fName
employeeLastName  --> emp.lName
...               --> ...

In theory I can create a lookup table like this:

create table lkp( input_field varchar(50),
                  table_name varchar(50),
                  column_name varchar(50),
                  constraint table_name_fk foreign key (table_name, column_name)
                             references information_schema.tables (table_name, column_name));

However, view columns can not be used as foreign key references.

Are there any other techniques for enforcing this relationship?

The RDBMS I'm using is SQL Server.


texts: Shrinking 150 TB data warehouse instance is feasible option?

texts:
We are using azure SQL data warehouse(azure synapse analytics) with size of 150 TB among which 75 TB is used and we are planning to shrink database with help of MS. What are the challenges should we aware of before doing it ?

Estimated downtime: 12 to 24 Hrs
Clustered column store index is enabled on instance


texts: Was SSRS installed in SharePoint Integrated Mode?

texts:
I've inherited an old QA database server with SQL Server 2008R2 (yes, yes, I know it's beyond EOL; there's nothing I can do about it!) and SSRS installed.

It's the back end of a SP server on a different VM.

How do I tell whether or not SSRS was installed in Native Mode or SP Integrated Mode?


texts: AWS Aurora MySQL Serverless CPU utilization is growing for unknown reason

texts:
We have been using Aurora serverless databases in staging and production environments for a few months now, and find the CPU remains in the range 15-20% almost all the time. However in the past week the CPU utilisation has been growing, even during periods of zero load and is now at 38%. I'm worried this trend will continue further and begin to become an issue.

I saw the same trend in our Staging environment, but it only lasted 3 days and then returned to the usual level.

What could be driving this and how should I be debugging and resolving the root cause(s)?

SHOW FULL PROCESSLIST

Returns only 6 rows.

----- Update 4th Sept 2019 -----

In the end, I just continued to monitor it, and once it reached 50% it dropped back down. I guess it is a quirk of Aurora serverless? Would be great to hear from anyone else facing this issue as I hate not understanding things.


texts: Looking for an efficient way to fix "Could not parse relay log event entry..." error

texts:
I have a corrupt relay log in MySQL 5.0 and looking for the steps to fix replication without having to reprocess all the binary logs from the master again.

Here is the full error message if you are curious:
Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

The normal fix is quite easy, you just obtain the correct binary log name and position from show slave status and run the change master command. But this is not what I would like to do.

I would like to reprocess only one or few binary logs from the master to recover the corrupted relay log and then continue to use the relay logs that were already created. I am looking for someone who had past experience doing this as there is a potential for failures doing the recovery this way.

Conceptually, I am thinking the following steps might be needed (maybe some of them are optional):

Stop replication on the slave.
Shutdown slave instance.
Move relay logs to a safe location.
See if relay-log.info or master.info files need to be updated manually.
Start slave instance.
Run change master command and reprocess binary logs from the failed position.
Wait until next relay log is created.
Stop replication on the slave.
Shutdown slave instance.
Figure out how to replace 2nd relay log file with relay logs files that are in the safe location and move them in.
See if relay-log.info or master.info files need to be updated manually.
Start slave instance.
Hope for the best.

I am thinking of an alternate method to do it, which seems much simpler:

Stop replication on the slave.
Shutdown slave instance.
Move relay logs to a safe location.
Start slave instance.
Run change master command and reprocess binary logs from the failed position.
Wait until next relay log is created.
Stop replication on the slave.
Use mysqlbinlog to process the relay logs that are in the safe location by piping to mysql client. Identify which file and position to start at. (Potential for issues, unless aborting processing upon first error).
Set the slave to replicate from master at the read master log file/position.


texts:
I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement/test. It basically consists of users, events, user profiles and a messaging system.

Certain users can create events which others can view, this is determined from the userType table. I'm unsure if I should be storing the lost password columns in the users table, would there any benefit for using a separate table for this?

(I'm not to sure if my relationships are setup correctly)

Full size

Appreciate any feedback given =)

Thanks !


texts:
When running queries in MySQL workbench, some of them are captured to the log file at either:

MySQL/Workbench/logs
MySQL/Workbench/sql_history

However, I'd like to see ALL the possible queries that MySQL workbench does. Is there a way to do this on the client-side, i.e., where MySQL workbench is running? For example, it would be nice to see how these graphs are being generated and the system data is being grabbed, etc.:


texts: all_tables and dba_segments giving different space occupied by the same tables

texts:
When I ran the below queries they gave different space occupied by the same table:

select bytes/1024/1024 as space, owner
from dba_segments
where segment_name='PARAMETER_STATUS';

     SPACE OWNER
---------- ------------------------------
      4988 FLSTD01
      3124 FLSTD04
      6416 FLSTD02
       808 FLSTD03

select blocks*16/1024,owner
from all_tables
where table_name='PARAMETER_STATUS';

BLOCKS*16/1024 OWNER
-------------- ------------------------------
    4979.39063 FLSTD01
      802.1875 FLSTD03
    6405.98438 FLSTD02
    1966.34375 FLSTD04

Why is it giving me different space occupied by the same table?

DB_BLOCK_SIZE=16KB


texts: PostgreSQL: Why is the schema owner not the database owner

texts: I've a simple question, yet I am unable to find the answer from the official documentation. When I create a database in PostgreSQL as a specific user, I'm the owner of all the objects under the database except for the schema. Is there a reason why the postgres role is the schema owner by default? I can't think of any explanation for this setup. Just curious ...


texts: How can I make "phraseto_tsquery" as fast as "plainto_tsquery" in PostgreSQL

texts:
Some background:

I have a table, documents with over 200,000 rows that has a column body, which can be very long. I put an index on this table:

CREATE INDEX documents_body_tsvector_index ON documents USING GIN (to_tsvector('english', body));

With this index, my queries that include WHERE clauses like to_tsvector('english', body) @@ to_tsquery('english', 'awesome') are blazing fast, which is great.

The problem:

While plainto_tsquery('english', 'awesome website') runs fast, phraseto_tsquery('english', 'awesome website') runs extremely slow. (I've also triedto_tsquery('english', 'awesome <-> website') as well, and that is also extremely slow.)

The question:

How can I speed this up? Am I doing something wrong with my index? I really need to take position into account—using phraseto_tsquery seems like the best way to do it. Any help would be much appreciated.

(Edit) The EXPLAIN:

Here's the fast query:

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*)
FROM documents
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'termone termtwo');

Aggregate  (cost=57.16..57.17 rows=1 width=8) (actual time=4.386..4.387 rows=1 loops=1)
  Buffers: shared hit=2949
    ->  Bitmap Heap Scan on documents  (cost=36.04..57.15 rows=5 width=0) (actual time=1.609..4.096 rows=3053 loops=1)
      Recheck Cond: (to_tsvector('english'::regconfig, text) @@ '''termone'' & ''termtwo'''::tsquery)
        Heap Blocks: exact=2907
        Buffers: shared hit=2949
          ->  Bitmap Index Scan on documents_text_tsvector_index  (cost=0.00..36.04 rows=5 width=0) (actual time=1.265..1.265 rows=3053 loops=1)
            Index Cond: (to_tsvector('english'::regconfig, body) @@ '''termone'' & ''termtwo'''::tsquery)
            Buffers: shared hit=42
Planning time: 0.094 ms
Execution time: 4.417 ms
(11 rows)

Here's the slow query:

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*)
FROM documents
WHERE to_tsvector('english', body) @@ phraseto_tsquery('english', 'termone termtwo');

Aggregate  (cost=57.16..57.17 rows=1 width=8) (actual time=120252.866..120252.866 rows=1 loops=1)
  Buffers: shared hit=38792
    ->  Bitmap Heap Scan on documents  (cost=36.04..57.15 rows=5 width=0) (actual time=3.065..120250.231 rows=1750 loops=1)
      Recheck Cond: (to_tsvector('english'::regconfig, body) @@ '''termone'' <-> ''termtwo'''::tsquery)
        Rows Removed by Index Recheck: 1303
        Heap Blocks: exact=2907
        Buffers: shared hit=38792
          ->  Bitmap Index Scan on documents_text_tsvector_index  (cost=0.00..36.04 rows=5 width=0) (actual time=1.491..1.491 rows=3053 loops=1)
            Index Cond: (to_tsvector('english'::regconfig, body) @@ '''termone'' <-> ''termtwo'''::tsquery)
            Buffers: shared hit=42
Planning time: 0.540 ms
Execution time: 120252.938 ms
(12 rows)


texts: Why do experienced SQL Server DBA's qualify examples with the default schema dbo?

texts:
In PostgreSQL like in SQL Server we have a default schema under a normal install. In PostgreSQL it's public.

CREATE TABLE foo ( int a );

SELECT *
FROM foo;

Assuming a default SEARCH_PATH (which is public) the above can be written explicitly as,

CREATE TABLE public.foo ( int a );

SELECT *
FROM public.foo;

In SQL Server, there seems to be a default schema of dbo (where all database objects are stored by default). This means you can write,

CREATE TABLE dbo.foo ( int a );

SELECT *
FROM dbo.foo;

Like in PostgreSQL, in Microsoft SQL Server you can change it. If you do, so long as you never qualify it things just work. In PostgreSQL the convention is to never write the default schema unless required. So in examples and in ETL-code you'll never see the public schema explicit. But it almost seems like the convention in Microsoft SQL is to always write the default schema (dbo). Why is that? It seems like this just closes the door to users who don't have write access to dbo, or who otherwise don't want to pollute the main schema. I'm going to assume there is a good reason for this convention in SQL Server. Why not always omit an explicit dbo.?

You can see

Paul White doing this here
Joe Amish doing it here
Aaron Bertrand doing it here


texts: Tell MySQL to start using utf-8 encoding without `convert to`ing it

texts:
In a fairly unique situation, my team has ended up with UTF-8 bytes in a database that thinks the data is encoded as latin1.

At least, I'm 85% certain that this is the situation at hand.

For example, a right single quotation mark was handed to the database by a programming language that had no concept of encodings (Ruby 1.8) and just treated the data as raw bytes (0xE2 0x80 0x99). This data, as far as I can tell (how to verify?), was stored as those actual bytes. So now when the data is read out by a more intelligent programming language (Ruby 1.9), the database helpfully says "Oh! 0xE2 is 'â', 0x80 is '€', 0x99 is '™'", and so instead of "Mike’s", we end up with "Mikeâ€™s". This is also what I get in the mysql prompt when SELECTing that value.

So, essentially, we have a bunch of utf-8 encoded data stored in a database that thinks the data is encoded as latin1.

This makes me to somehow tell the database "No, no matter what you think, this stuff is actually utf-8". CONVERT TO doesn't seem like the right tool, because then I'll end up with permanent "Mikeâ€™s".

Failed/moronic attempt #1

I noticed this:

> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | utf8              |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_unicode_ci   |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

And thought that maybe changing character_set_results to latin1 would trick it into not doing any conversion of the bytes, resulting in the proper display of data on my utf8 OS.

Sure enough, SET character_set_results=latin1; results in ’ instead of â€™. Cool!

So I added this to my ~/.my.cnf (which is the only my.cnf, I checked):

[mysqld]
...
character-set-results=latin1

and when I go back to the MySQL prompt & check the character_set_% variables, it's still utf8.

Yes, it just occurred to me that mysqld is a deamon, which means I probably need to restart the whole mysql process for this to take effect. But whoever installed MySQL on this machine used the dmg instead of the brew (wasn't me!) and the MySQL pref pane is currently telling me that MySQL isn't running even though it clearly is, and anyhow before I go down that rabbit hole, I want to check with an actual DBA and see how ridiculous this is, or if there's just a better, cleaner way to do it.


texts:
The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact.
Lets take currency as an example. We store currency as numeric, but when displayed we want it to be of a certain format, say to_char(_numeric_value, 'FM999G999G999G999G990D00').

The obvious solution is to create a table that stores this format and uses is whenever needed. But since this is very commonly called function we don't want it to be slow.
The solution I came up with is to re-compile format function each time config is changed. I use on-update trigger for that and also store function source-code. This way performance should be the same as if the format was hardcoded, but it doesn't seem to perform as expected.

What are down sides of this solution, am I missing something? Maybe there's an even better solution?

sqlfiddle is down for me currently, so I will post all necessary DDL/DML here.

CREATE TABLE test_config(money_format text);
CREATE TABLE test_config_fncs(fnc_name text, fnc_source TEXT);

--This is function for performance comparison.
CREATE OR REPLACE FUNCTION test_fnc_common_format_money_no_precompile(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, money_format)FROM test_config;
END;
$BODY$
    LANGUAGE plpgsql STRICT STABLE;

CREATE OR REPLACE FUNCTION test_tfnc_config_fnc_precompiler()
    RETURNS trigger AS
$BODY$
BEGIN
    IF (NOT (NEW.money_format = OLD.money_format)) THEN
        EXECUTE
        (
            SELECT
                    replace(fnc_source, '>money_mask<', NEW.money_format)
                FROM test_config_fncs
                WHERE fnc_name='test_fnc_common_format_money'
        );
    END IF;
    RETURN NEW;
END;
$BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER test_tg_config_aiu_fnc_precompile
    AFTER UPDATE
    ON test_config
    FOR EACH ROW
    EXECUTE PROCEDURE test_tfnc_config_fnc_precompiler();
-- DDL DONE
-- INITIAL INSERTS:
INSERT INTO test_config(money_format, date_format) values (null);

INSERT INTO test_config_fncs(fnc_name, fnc_source) VALUES ('test_fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION test_fnc_common_format_money(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, '>money_mask<');
END;
$BODY$
    LANGUAGE plpgsql STRICT IMMUTABLE;
$fnc$);

--TEST QUERIES:
UPDATE test_config SET money_format='FM999G999G999G999G990D00';

SELECT * FROM test_fnc_common_format_money(11111.2222); -- runtime 1700ms per 1 million queries
SELECT * FROM test_fnc_common_format_money_no_precompile(11111.2222); -- 13100ms per 1 million (!)


texts:
I have a 200 GB Firebird database (created with Firebird 2.1.3, currently running 2.1.5), and it appears to be corrupted. When I try to do a backup I get:

  database file appears corrupt <>
  wrong page type
  page 0 is wrong type (expected 0 found 1)
  gds_get_segment failed

gfix ends with a

  deadlock

How can I fix it?

UPDATE

While this particular database wasn't fixed, i guess the solution is try IB FirstAID.

Recommended reading:

Common InterBase/Firebird errors caused by corruptions and their recovery chances
Firebird and InterBase corruptions reasons


texts: Finding gaps-and-islands with repeating group numbers


texts: Should I create a maintenance plan for my database or use jobs?

texts: I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box.  However, after it was created, it seemed difficult to customize.  This left me wondering if I should have just created SQL jobs with the activities I wanted done.  Are there any benefits to using the maintenance plan over manually creating SQL jobs?


texts:
I am wondering how I can improve the speed of this query:

SELECT
    caf.user_id,
    caf.family_number,
    caf.card_number,
    caf.suspension_start_date,
    caf.suspension_end_date,
    caf.balance,
    util.room,
    caf.meal_plan_type,
    student.special_diet,
    student.specify_other,
    users.surname,
    users.type,
    users.name,
    users.contact
FROM
    (SELECT
        *
    FROM
        caf_student_cards) AS caf
        LEFT JOIN
    (SELECT
        user_id, special_diet, specify_other
    FROM
        students) AS student ON caf.user_id = student.user_id
        LEFT JOIN
    (SELECT
        resource_id AS room, user_id
    FROM
        utilizations) AS util ON caf.user_id = util.user_id
        LEFT JOIN
    (SELECT
        surname, name, type, user_id, contact
    FROM
        system_users) AS users ON caf.user_id = users.user_id
WHERE
    contact > 0;


texts: Optimal database structure for fast inserts

texts:
I am using MySQL Percona server.
This table needs to do fast bulk inserts using 'INSERT INTO ... VALUES' for smaller requests (about 100k records) and using 'LOAD DATA INFILE' for larger (up to 10kk records).
Also, there will be selects with full table scan (using indexes there will be like 10-30 million of records out of total 100kk records), they also need to be done as fast as possible.
I tried myisam and got around 50k records inserted per second, with InnoDB that was only about 25k. This numbers are OK, but i would be glad, if i could improve them.

How would you organize such table and what setting to mysql server you would apply? We've got a server with 128G of RAM, i think it might be helpfull.

I did some research on this matter, but still feel myself uncompetent. If you have managed similar tables - please, share your expirience, thank you.


texts: Restore database without overriding Auto growth

texts:
I created a database with auto growth 200MB. After restoring database auto growth changed to 1MB. Because my Backup file database auto growth is 1MB.

Is there a way to restore without overriding the auto growth ?


texts: MS SQL Server 2008 makes hard disk space disappear

texts:
We are running SharePoint 2010 with MS SQL Server 2008 (Windows Server 2008 R2 Enterprise). We have not taken regular SP backups this far which has led to high disk usage. However, even if I shrink databases, we still have a strange disk usage issue. If I take a listing of the all database disk usages, their total disk usage is 21 GB (plus logs taking 9 GB). Still, the allocated hard disk space is 145 GB. If I look at the files in the hard disk using Windows Explorer, I only find a few little files even when hidden files are shown.

The hard drives of the machine are:

C (System drive); 32 GB allocated of 40 GB
D (SQL Server); 8 GB allocated of 15 GB
E (SQL-DB); 145 GB allocated of 180 GB
F (SQL-Logs); 8 GB allocated of 30 GB

If I shrink database logs, it seems that F drive receives free space. Shrinking other databases does not seem to release space on E - even if it did, E still uses 145 GB (versus 21 GB of database sizes in total). Naturally, SQL Server might not release disk space without explicitly telling it to do it. Still, the database size has never been even close to 145 GB but at most some 30 GB.

Where does the space go?

Below is the script I use to retrieve database sizes in MS SQL Server Management Studio. It seems to list all the databases as I compare it to the databases treeview.

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db


texts: Map a Login to a User that was Created Without Mapping to a Login

texts:
This question has been asked before, but I didn't see any answer (https://stackoverflow.com/questions/6468813/assign-a-login-to-a-user-created-without-login-sql-server)

How does one map a login to a user, when the user was originally created using the "SQL User without login" User Type option in the GUI? I'm using SQL Server 2012


texts: Where did these dbo._trace_ Stored Procedures come from?

texts:
I'm looking at some databases at a new company and one in particular has a bunch of Stored Procedures named dbo._trace_*

SQL Default Trace is ON, but I'm not sure if these are related, and if they were, I don't know why I would see them in one database on the server but not in other databases on that server.


texts: Managed backups in Azure SQL Managed Instance

texts:
The Managed Instance allows the admin to ENABLE Compress backups at the instance level.

Does that affect the managed backups?
How does the Managed backups work? as i don't see any record in backupset table.


texts:
Possible Duplicate:
  Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

I want to have a FULLTEXT INDEX on data name in the base SELECT that selects data from another SELECT which unions several tables.

SELECT `data` FROM SELECT (
  SELECT `name` as `data` FROM `names`
  UNION SELECT `title` as `data` FROM `titles`
) as `search`

I want data to have a FULLTEXT INDEX


texts:
I have two functions: fn_Without_Param and fn_With_Param

 CREATE FUNCTION [dbo].[fn_Without_Param]
 (
 )
 ...
 INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 )

and

/*
     I am requesting it so:
    declare @SubmitDate     datetime
    set @SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 )
    select * from [dbo].[fn_With_Param] (@SubmitDate)
*/

CREATE FUNCTION [dbo].[fn_With_Param]
(
    @SubmitDate datetime
)
...
INNER JOIN .. ON .. AND SubmitDate = @SubmitDate

In the first case I have nondeterministic (?) function (because of GETUTCDATE) and I have called the second one with the same input parameter (CONVERT( varchar(10), GETUTCDATE(), 101 ) - meens today date without hours, minutes, seconds, millisecond).
Is my function nondeterministic one? How to detect this, maybe sql server has some public mark. Why is the second function slower?


texts: `Encrypt Connection` causes an invalid certificate error even though server-side encryption is forced

texts:
I have a SQL Server set up to force encrypted connections from all client. It has a standard SSL certificate, using Let's Encrypt as the CA. Browsing to the server via a web browser verifies that the SSL certificate is valid and current.

However, if one checks Encrypt Connection setting in SSMS, one gets an error about invalid certificate.

TITLE: Connect to Server
------------------------------

Cannot connect to <redacted>

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476

------------------------------

The certificate chain was issued by an authority that is not trusted

------------------------------
BUTTONS:

OK
------------------------------

One can then remove the Encrypt Connection, connect anyway, which the connection is then successful. The dm_exec_connection will still show that connection as encrypted nonetheless.

As noted in the comments, Let's Encrypt was recently added to trusted list by Windows, so it might be the older OS not trusting Let's Encrypted certificate. However, that in itself does not explain why clients apparently are able to connect and get encrypted connections by default. If the client explicitly specify Encrypt Connection, then it becomes an issue. But why would it be? In both cases, the Trust Server Certificate is not enabled, so I would have expected the behavior to be the same.

Who's pulling who? What's really going on?

Bonus: If anyone can show a way to determine which certificate is in use for SSL connection that would be appreciated; the server has only one active SSL certificate but I have no way of verifying that it's the one that SQL Server is actually using to identify itself. For all I know, it might be waving around a expired certificate, which may explain why the error.


texts:
I have the following oracle SQL and its works and all but it's quite ugly with all of the ors. Is there a more concise way of doing this?

SELECT * FROM foobar WHERE
  (SUBJECT ='STAT' and TERM ='111') or
  (SUBJECT ='STAT' and TERM ='222') or
  (SUBJECT ='ENGLISH' and TERM ='555') or
  (SUBJECT ='COMM' and TERM ='444') or
  (SUBJECT ='COMM' and TERM ='333') or
  (SUBJECT ='STAT' and TERM ='666')
  ...


texts:
I am maxed out on a DB in SQL Express 10GB.

I deleted rows of approx 2.5G in size. In report I can see number of rows is much lower. But "Data" is still the  same. I am happy about Reserved remaining 10G, but I am unable to insert new rows.

I read that shrinking is not necessary if table will grow again, BUT how do I tell SQL Server that it now has space to use again?


texts: different outcome based on function execution method

texts:
I am mainlining a legacy system and realize that the answer to this may take a deeper explanation of the code but before I go that far I wanted to make sure the solution wasn't something more simple.

Here is the situation:

the function/s in question just generate and return a new recID in our system, nothing really that complicated at all just some pretty situation-specific business logic. One thing this function (in this case the update I am about to describe happens in another function called by the first. this second function just contains the update statement, that is all.) is supposed to update a status record from active to complete. very simple.

I am seeing a strange difference in this update based on how the function is executed.

In some places in the code the function is executed with: SELECT function(X) FROM DUAL;

In other places in the code the function is executed with:

DECLARE
  RetVal number;
BEGIN
  RetVal := function(X);
  DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));
END;

In places where the first example is used, the status doesn't get changed.

When the second example is executed, the status does change.

Could this be something at a higher level that I don't understand/remember about how these two examples execute? Any Ideas? I can't imagine that it matters but I am running these through Toad.


texts: What is the difference between these invocations of COUNT()?

texts: can anyone please let me know difference between count(*), count(1), count(0), count('FAST')


texts:
This is for SQL Server:

I have a table that has been partitioned. Sometimes, I need to completely update an existing partition - the updates could be extensive enough to touch all rows of the partition.

. Can I..

Make a copy of the partition
Apply updates
Merge the updated partition back?

I am worried about speed of updates. Want to do all updates offline (users would be using the partition actively), and, if possible, switch the updated partion in, so that there would be minimal impact to users...


texts:
I have a problem with my database.  I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error.  I searched for the cause of the error for several days.  Since I cannot find the problem,  I've decided to share it with you.

Message log:

Executing SQL script in server

ERROR: Error 1064: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near ') NULL DEFAULT NULL , n_category VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY K' at line 3

CREATE TABLE IF NOT EXISTS `telo2p`.`d_category`
(
  `idd_category` INT(11) NOT NULL AUTO_INCREMENT,
  `f_sells` DOUBLE(11) NULL DEFAULT NULL,
  `n_category` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idd_category`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

SQL script execution finished: statements: 6 succeeded, 1 failed

Any advice about what I am doing wrong here?


texts: Update Ranking Points Based on Date Range

texts:
Currently, I am building a personal project.

A site that stores Racing Information and grants point to weekly individual winners.

These racers have groups and points earned by the group will be ranked on a monthly basis.

The query below returns Top racing information of the current week where the current week starts every Tuesday.
SELECT *
FROM timeatk
WHERE DATE(posted)
    BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
ORDER BY timer ASC LIMIT 0, 10

But, I am working on a CRON, where a PHP script should be triggered depending on the date on a for-loop. Coz' every Tuesday, I need to add points for the weekly winners.
For the weekly results:
I would like to UPDATE the weekly top ten racers based on the lowest time on the "timer" column from the last week's Tuesday until Monday of the current week.
Weekly Winners:

1st = 10points
2nd = 9points
3rd = 8points
.. until
10th = 1point

for ($score = 10; $score > 0; $score--) {
        $data = [
            'score' => $score
        ];
        $sql = "update ometimeatk.timeatk_has_list set weekly_score = :score where DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY) AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)";
        $stmt= $conn->prepare($sql);
        $stmt->execute($data);
}

Here's the table structure:
CREATE TABLE `timeatk_has_list` (
  `id` int(11) NOT NULL,
  `group_name` varchar(80) NOT NULL,
  `racer_has_name` varchar(255) NOT NULL,
  `racer_has_mobile` varchar(255) NOT NULL,
  `racer_has_nationality` varchar(255) NOT NULL,
  `racer_has_email` varchar(255) NOT NULL,
  `motor` varchar(255) NOT NULL,
  `gear_ratio` varchar(255) NOT NULL,
  `chassis` varchar(255) NOT NULL,
  `timer` varchar(255) NOT NULL,
  `posted` datetime DEFAULT NULL,
  `weekly_score` smallint(6) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here's a simple SELECT query to check the result.
The UPDATE query above updates ALL rows weekly_score with 1.
And here's the working SQL query with the help of J.D:
UPDATE timeatk_has_list
INNER JOIN
(
        SELECT *, ROW_NUMBER() OVER (order by timer desc) AS RacerRank
        FROM timeatk_has_list
        WHERE DATE(posted)
            BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
            AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        ORDER BY timer ASC LIMIT 0, 10
) c
    ON ometimeatk.timeatk_has_list.id = c.id
SET ometimeatk.timeatk_has_list.weekly_score = 11 - c.RacerRank


texts: SharePoint changing statistics options daily

texts:
SharePoint switches OFF and ON options like AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC, AUTO_CREATE_STATISTICS,  INCREMENTAL on SharePoint databases daily at 12 AM and that generates lots of noise in SQL Server error log.

What action should I take(Either in SQL Server or in SharePoint)  to keep the error log clean?


texts: How can I make SQL Server Agent job history available after failing over an AG?

texts:
I am implementing a new HA/DR architecture using Always On AGs for use in both a HA a DR scenario.

I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled when a failover takes place but now realise that, once this happens, no job history will be available. Job history is essential to determine what hadn't run or failed on the now defunct primary and needs to be run on the new primary.

I am struggling to find any documentation about how to overcome this and make that data available on the new primary.

Has anyone been confronted with this and how did you get around it?


texts: Memory\Pool Nonpaged Bytes low but Memory\Pool Paged Bytes high

texts:
Can someone help identify how these values can indicate memory leak.

In the system I am monitoring Memory\Pool Nonpaged Bytes are low (on average 100mb) but Memory\Pool Paged Bytes are high (on average 280mb).

I read somewhere that these numbers should be under 150 and 250 respectively otherwise it indicates memory leaks. What are your thoughts? what other memory counters should i look at to say that 280mb is high and indicative of memory leak issues?


texts: Too many open connections with MongoDB 2.6 + PyMongo 2.7.2

texts:
I was running MongoDB 2.4 with a replica set which contains 1 primary, 1 secondary and an arbiter. We were using PyMongo 2.6 and MongoEngine 0.8.2.

Recently, we performed an upgrade to MongoDB 2.6, and also upgraded PyMongo to 2.7.2 and MongoEngine to 0.8.7. This setup worked fine for almost 12 hours, after which we started getting the below error:

[initandlisten] connection refused because too many open connections: 819

The ulimit is 1024 which worked perfectly with MongoDB 2.4* and hence we have not increased it. Increasing it might solve the problem temporarily but we will hit it again in the future. Somehow the root cause seems to be the inability of PyMongo to close the connections. Any pointers why this happens?

*When using MongoDB 2.4 with PyMongo 2.7.2 and MongoEngine 0.8.7, everything works well and the max connections are about 250. It is only with MongoDB 2.6, PyMongo 2.7.2 and MongoEngine 0.8.7 that the number of connections shoots up to 819.


texts: Is there any reliable and fast database architecture that allows to store a dictionary(containing up to hundred thousand words) using mongodb?


texts: DB2 sorting by lowercase uppercase - problem

texts:
I'm struggling with DB2 - sorting by Polish letters with diacritics and uppercase/lowercase. When I created a database using:

CREATE DATABASE P1DS8 AUTOMATIC STORAGE YES ON '/home/db2inst1' DBPATH ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY PL COLLATE USING SYSTEM_912_PL PAGESIZE 32 K

Sorting by Polish letters works properly, however ascending sorting returns results ordered by lowercase as first aAbB... How I can change this setting to revert results to AaBb..?

Is the 'COLLATE' section is responsible for all aspects of sorting? (Specific language letters, lowercase, uppercase etc. )


texts:
For ages I have been using MySQL 5.1.37.

Recently I noticed that this version is no longer supported in repository, so I need to upgrade mysql.

I assume that changing the version should be complicated process with changes in my.cnf, and may be with the changes in the code.

Questions

Which version is the most stable and preferred?
Which version should I use?


texts: How to move a PostgresSQL/ PostGIS to another drive

texts:
Firstly I'd like to say I know little about databases (GIS background) and I might have bluffed it all so far.
But we wanted to set up a database for use with QGIS.
My understanding was that the PostgresSQL made the server and PostGIS add on made the databases on that server.
I'm not operating with a network or dedicated servers I have a C drive and a Q drive. We installed PostgresSQL (servers?) and PostGIS (database?) on my Q drive where I created and manipulated all my data.
But now we want to move it all to a portable drive (E) so that others can access the database by pluging into the drive is this possible?

and if so how does someone who does not understand databases nor SQL do it?

Cheers
matthew


texts: How to trigger Mysql parallel query function to start?

texts:
Please help,how to trigger Mysql parallel query function to start?
MySQL version is 8.0.19.

mysql> show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_parallel_read_threads | 4     |
+------------------------------+-------+
1 row in set (0.00 sec)

Judging from the execution plan, although the cost value is high, it does not trigger parallel queries.

mysql> explain analyze SELECT count(a.name) FROM b,a WHERE b.id = a.id AND a.id < 10000\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(a.`name`)  (actual time=79199.970..79199.971 rows=1 loops=1)
    -> Inner hash join (b.id = a.id)  (cost=29804029261222.55 rows=29803521726988) (actual time=52129.791..79198.823 rows=9999 loops=1)
        -> Table scan on b  (cost=31.81 rows=18758239) (actual time=0.385..19630.712 rows=20000000 loops=1)
        -> Hash
            -> Filter: (a.id < 10000)  (cost=4909601.51 rows=15888229) (actual time=2.266..52117.583 rows=9999 loops=1)
                -> Table scan on a  (cost=4909601.51 rows=47669458) (actual time=2.262..48985.413 rows=50000000 loops=1)

1 row in set (1 min 19.25 sec)


texts: Create function with a parameter, which will become the part of IN (1, 2, 3) clause

texts:
Following is the part of function declaration. Unfortunately it doesn't work the way I would like it to.

CREATE FUNCTION my_cool_func(some_parameter INTEGER, some_other_parameter INTEGER[])
RETURNS BOOLEAN
LANGUAGE 'plpgsql'
STABLE

AS $BODY$

DECLARE count_entries INTEGER;

BEGIN

    SELECT COUNT(some_table.id) INTO count_entries FROM some_table
    WHERE ...
    AND some_table.some_integer_field IN (some_other_parameter);

        IF (count_entries > 0) THEN...

some_other_parameter is a list of integer values (1, 2, 3).

My questions:

Is this function declaration correct?
How do I define the default value for some_other_parameter?


texts: sql update query for two connected tables

texts:
UPDATE rooms set r.vacant=true
FROM reservation s,rooms r
WHERE r.room_no = s.room_no AND s.cus_id=1

I created above sql queryto update room status.I want to set vacant as true when customer_id =1.
But this code didn't work on mysql.
Whats wrong with this?


texts: SQL server Log shipping Copying failed due to incorrect username and password

texts:
I have 2 sql server 2008 servers with log shipping. Now im trying to add one more secondary server to the existing log shipping. While copying the log files to secondary I got the below error on SQL server.

Copying log backup files. Primary Server: 'MASTER', Primary Database: 'Test', Backup Source Directory: '\X.X.X.X\ls\', Backup Destination Directory: 'C:\LS'
 **Error: The user name or password is incorrect.*

Meanwhile on primary Server I found the below error on event viewer.
An account failed to log on.
Network Information:

Workstation Name:   Secondary
Source Network Address: 172.X.X.X
Source Port:        49174

  This event is generated when a logon request fails. It is generated on
  the computer where access was attempted.

  The Subject fields indicate the account on the local system which
  requested the logon. This is most commonly a service such as the
  Server service, or a local process such as Winlogon.exe or
  Services.exe.

Can anyone guide me to resolve this?


texts: Trying to view database properties and getting a cannot impersonate `guest` error

texts:
One of my DBAs is trying to look at the database properties of a database by right clicking and selecting properties and is getting the following error:

Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)

Points of interest:

The guest account has been disabled (no connect permission)
The DBA is a member of db_owner on the database, but not sysadmin on the instance.
He has this permission through a role, but does not have a user directly in the instance let alone the database.
This is a windows authenticated login.
I tried creating a SQL Id and testing but couldn't replicate.
The version of the instance is SQL Server 2016 (SP1) - 13.0.4001.0 (X64)
On Windows Server 2012 R2 Standard 6.3

What I don't understand is why he would be impersonating anything since he is a member of db_owner, particularly when trying to open the properties dialog.
I've checked here:
Cannot execute as the database principal because the principal “guest” does not exist but the service account running the named account has sysadmin access so this shouldn't be a problem.


texts: Flush/Clean MySql ib_logfile0 ib_logfile1

texts: Is there a way to flush ib_logfile0 and ib_logfile1 without dumping the tables to a .sql file, deleting, then re-inserting? Somebody entered a couple plain text credit card numbers into the customer notes table. It came up in my PCI scan and I removed them from the table but they still exist in the log files :(


texts: How do I manage column order when using PL/SQL's %ROWTYPE records?

texts:
I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:

DECLARE
  TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
  l_data t_toptab_list;
BEGIN
  SELECT ... -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_data
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.

  -- EDIT:
  FORALL i IN 1..l_data.count
    INSERT INTO toptab VALUES l_data(i);
END;

In order for the BULK COLLECT to work properly, I have to list the columns in the SELECT clause in exactly the same order as they are declared in the toptab table. So the problem is that this code would be run in multiple environments. How do I either: (a) guarantee that all environments have the exact same column ordering, or (b) explicitly specify which columns go to which record elements but still use a bulk bind?

I understand that (a) could be done through strict schema management procedures. But let's say that's not an option here. In fact the different schemas may already have different column orderings. Can I reorder the columns without rebuilding the entire table? (Prohibitive.)

I know that (b) can be accomplished without the BULK COLLECT by explicitly selecting into each record element, but I really want to do it in bulk.

Any ideas?

Edit: I want to bulk insert into toptab with the data I've collected. I separate these two steps because I need to batch the inserts into smaller transactions. (We use streams and find smaller transactions keep things moving smoother. However, batching each transaction is still much faster than doing N individual inserts.)


texts: SQL Server permissions to create, restore, delete only some databases

texts: On MS SQL Server 2008 R2, I need to have user which can create, restore, DBCC CHECKDB and then drop databases. But he must not be able to access or delete specified databases on server (security reasons). Is this setup possible?


texts:
I am trying to dump a database which has a size of just over 100 GiB. I am using the following command (with pv to give a progress bar to observe where this problem occurs):

pg_dump -h <host> -U <user> <db> | pv -c -s $(psql -h <host> -U <user> <db> -tc "SELECT pg_database_size('<db>')") -N dump | gzip > <db>.sql.gz

The dump chugs along happily until it reaches 56.8GiB raw passing through the pipeline (~25GiB of the compressed file), and then just dies. I am at a loss as to why this is happening. The filesystem is ext4, so it's not a size limitation there.

I'm currently running it again, but splitting files into 10GiB chunks. Let's see how this goes.

Any ideas why, no matter what, the dump stops at 56.8GiB?

EDIT: The server I'm trying to get the dump from is a pod running on Google Kubernetes Engine. I have tried dumping both on the pod itself, and from another pod connecting through a service, and the problem persists.


texts: How to exclude tempdb from sp_MSforeachdb?

texts:
Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list.

exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup.user_name AS [User],
ceiling(bkup.backup_size /1048576) as ''Size Meg'' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig'',
bkup.backup_start_date AS [Backup Started],
bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)],
CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bms.description AS [Description]
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
,bkup.database_backup_lsn
,bkup.differential_base_lsn
,bkup.first_lsn
,bkup.last_lsn
,bkup.fork_point_lsn
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
--WHERE backup_finish_date = (SELECT MAX(bkup.backup_finish_date) FROM  msdb.dbo.backupset bkup WHERE sysdb.name = bkup.database_name) --Last backup
WHERE backup_finish_date > DATEADD(DAY, -1, (getdate()))  -- Last 60 days
AND bkup.type=''L''
AND sysdb.name = ''?''
ORDER BY backup_start_date DESC, backup_finish_date';

I tried writing 'IF ''?'' NOT IN (''tempdb'') SELECT.......' but all results were identical for only one database.
Then I tried writing AND sysdb.name <> ''tempdb'''' but it gave an error:

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.


texts:
I am trying to set up mongodb sharding and almost done.

 1. completed 1 config replicaSet
 2. completed 1 shardsvr replicaSet
 3. multiple mongos routers connected to config and shardsvr also done

I think I am almost completed about sharding and I can manage "SHARDED CLUSTER" in mongodb compass.

But I still unclear with the facts of read and write via mongos. Many sharding guide end at the point after mongos is running. But I want to know is my following structure is valid or not to run.

 1. I have 6 servers (3 normal,3 high performance web servers)
 2. Normal 3 servers run as config
 3. High Performance 3 run as 1 shard and multiple mongos (mongos on each)

May I know is my structure in correct form or in wrong for sharding concept. Is it ready to use with application to connect mongos with this situation ? Thank you.


texts: select values from a table where column values should be different

texts:
Let us say there are 4 columns in a table .

cpt,cpt4mod,itemcharge,eff_date.

condition1: if the values of cpt,cpt4 and itemcharge are same, but eff_date is different. Then I need to fetch the latest record based on eff_date.

condtition2: if the values of cpt and cpt4 are same, but eff_date and itemcharge are different. Then I need to fetch the latest record based on eff_date.

condtition3:if all columns have the same values, then I need to fetch the latest record based on eff_date

condtition4: if the values of cpt,cpt4and,eff_date are same  butitemcharge  is different. Then I need to fetch all the records except the latest one


texts:
I am trying to setup transactional replication in SQL Server 2008 R2 on two servers on separate domains. I am able to create the distributor, create the publication, and create the subscription, however when I attempt to initialize the subscription, I receive and error stating "The process could not connect to the Subscriber".

Publisher/Distributor

Publication - Uses Windows account on Server1 for both Snapshot Agent and LogReader Agent

Subscriber

Subscription Process Account: uses Windows account on Server1 to run. This same Windows account is setup on Server2, with db_owner privileges on the database to receive the replicated data.

Subscriber Connection: Impersonate agent process account(Windows Authentication)

I have tried numerous combinations of Windows Accounts, SQL Server Accounts, Default SQL Server Agents, Admin accounts, and nothing works. Both servers can ping each other and all the ports are setup correctly as far as I know.

Any help or insight on where to look would be much appreciated.


texts: MySQL: How to decrement an unsigned int column?

texts:
I have a table with a column unsigned int, let's name it col1.

I want to decrement the column by a number, if value after decrement is less than 1 then set it to 1. Like this:

UPDATE `tbl1` SET `col1` = GREATEST(1, `col1`-3);

This works fine, but if col1 is 0 or 1 or 2 and I try to do -3 then MySQL throws an error.

I've tried this and this but didn't worked.


texts: SQL Server Database Suspect with Memory table

texts:
Today my database seems suspect. I am running SQL Server 2016 with memory tables. The below code won't work to fix the suspect. SQL Server keeps saying I have to restore. Any ideas?

EXEC sp_resetstatus dbname;
ALTER DATABASE dbnameSET EMERGENCY
 DBCC checkdb(dbname)
ALTER DATABASE dbnameSET SINGLE_USER WITH ROLLBACK IMMEDIATE

 DBCC CheckDB (dbname, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbnameSET MULTI_USER


texts:
I've got a table with the following DDL:

CREATE TABLE dbo.SubscriberProduct (SubscriberId INT NOT NULL
  , ProductId INT NOT NULL
  , PRIMARY KEY (SubscriberId, ProductId));
GO

I've then enabled change tracking on it and initially populated it with roughly 15m rows, later on it's going to be just incremental changes.

However I'm facing an issue that Change Tracking queries are terribly slow and CHANGETABLE function seems to be the bottleneck. That's the query I'm running locally. It took 40 seconds to return 1000 rows, which is far too long than my expectations.

SELECT TOP (1000) CT.SYS_CHANGE_VERSION
  , CT.SYS_CHANGE_CREATION_VERSION
  , CT.SYS_CHANGE_OPERATION
  , CT.SYS_CHANGE_COLUMNS
  , CT.SYS_CHANGE_CONTEXT
FROM CHANGETABLE(CHANGES dbo.SubscriberProduct, 0) AS CT;

That's the execution plan I've captured: https://www.brentozar.com/pastetheplan/?id=HyIEYPK8z

Normally I'd know where to start, but not this time as I've got no clue how to tune these kind of queries, so I'm asking to help.


texts:
This tutorial says the following:

  For proponents of denormalization, the thinking is as follows:
  normalization creates more tables as you proceed towards higher normal
  forms, but more tables mean there are more joins to be made when data
  is retrieved, which in turn slows down your queries. For that reason,
  to improve the performance of certain queries, you can override the
  advantages to data integrity and return the data structure to a
  lower normal form.

Why is data integrity not possible when using denormalization? I mean wouldn't the DBMS make sure that all redundant copies of some piece of data gets updated automatically when that piece of data is updated?

In Oracle, for example, denormalization is implemented using materialized views, and when the original tables are modified, then the data in the materialized view will be modified automatically (you can choose that the data not be modified automatically also). And even if you don't use materialized views (if your DBMS doesn't support it for example) and implemented the denormalization manually, you can still write triggers yourself to keep the data consistent (so using denormalization does not necessarily mean that data integrity is not possible).