texts: Releasing disk space by using DELETE or TRUNCATE?

texts:
More than a week ago, I deleted all the rows in a postgresql table (not via truncate, instead with delete from ...). Select count (*) reveals table rows are now 0.

When I now run queries to query disk space, I still see the table taking up space. Specifically, all the indexes still exist and are taking up space. How do I get rid of them and release disk space? Secondly, why do they remain in the first place, when I've gotten rid of all the rows?

Here's the table's detailed description in case warranted:

                                      Table "public.links_photoobjectsubscription"
     Column     |           Type           |                                 Modifiers
----------------+--------------------------+----------------------------------------------------------------------------
 id             | integer                  | not null default nextval('links_photoobjectsubscription_id_seq'::regclass)
 viewer_id      | integer                  | not null
 updated_at     | timestamp with time zone | not null
 seen           | boolean                  | not null
 type_of_object | character varying(15)    | not null
 which_photo_id | integer                  |
 which_link_id  | integer                  |
 which_group_id | integer                  |
 which_salat_id | integer                  |
Indexes:
    "links_photoobjectsubscription_pkey" PRIMARY KEY, btree (id)
    "links_photoobjectsubscription_seen" btree (seen)
    "links_photoobjectsubscription_updated_at" btree (updated_at)
    "links_photoobjectsubscription_viewer_id" btree (viewer_id)
    "links_photoobjectsubscription_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photoobjectsubscription_viewer_id_fkey" FOREIGN KEY (viewer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    "which_group_id_photoobjectsubscription" FOREIGN KEY (which_group_id) REFERENCES links_group(id) ON DELETE CASCADE
    "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE
    "which_salat_id_photoobjectsubscription" FOREIGN KEY (which_salat_id) REFERENCES links_salatinvite(id) ON DELETE CASCADE


texts:
We are creating a warehouse system. In this system, we will have a Extract page (I don't know if this is the right word to describe it, but it's the same as the log in our bank account, where we have all transactions, with the values and then, subtract it to the total value, it's a bank extract I guess).

We need a table to be like this:

The table will have all transactions from fiscal notes (with entries and exits of products). If you notice, you can see the ID 1 will appear in position 1, 2, and 4. I did try to use LAG, but with the LAG function, it's getting the ID 3 value.

How can I get the lag value, but with the desired product id? I need the last total value of PR_ID = 1.

I tried this:

begin tran
insert into almoxarifado.Movimentacao
  (produto_id,documento,data,saldo_anterior,entradas,saidas)
select
Lotes.produto_id as produto_id,
NF.numero as documento,
nf.data_cadastro as data,
>>The lag value would be here, I need this field, to be the last total value,
>>but from this specific product.
Lotes.quantidade as entradas,
0 as saidas
from Almoxarifado.Entradas
join Compras.Notas_Fiscais NF on Entradas.nota_fiscal_id = NF.id
join Compras.Notas_Fiscais_Produtos NFP on NFP.nota_fiscal_id = NF.id
join Almoxarifado.Lotes on Lotes.nota_fiscal_produto_id = NFP.id
 where convert(date,NF.data_cadastro) = '2017-01-10'

Every row needs to have its own total. Let's say day 2, we had 3 water gallons, it gives me a total of 10. then, day 4 I got 3 gallons, I need the row with the total = 10, and in this new row, the total of 13.

It's like a bank extract, where you see your expenses, and the total on each row. Because, the next step would create a view, that will select this table, by product, so , I can see every total, every entry and product exit during a period of time.


texts: SQL Database structure to store calculations

texts:
I am in the process of designing a database structure to store some calculation information.

Information about the problem:

Each "calculation" is a collection of scripts allowing to calculate value, target, low limit and high limit. All of those are optional.
A calculation script is a string, that will be parsed by the client app. Example: 50 + {OutsideTemperature}

I have tried to follow the principles of normalization, as I understood those, and I came up with:

CREATE TABLE CalculationScript(
ScriptId int NOT NULL PRIMARY KEY,
Script VARCHAR(8000) NOT NULL
);

CREATE TABLE Calculation(
CalculationId int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL
);

CREATE TABLE ValueScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE TargetScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE LowLimitScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE HighLimitScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

The table will be used as a configuration table. The client app will query the calculation, parse the scripts and run the calculation. For example, the calculation:

Name: testCalc
Value: 50 + {OutsideTemperature}/2
LowLimit: 10 - 5^({OutsideTemperature})

Will yield, at runtime, if it is 0°C outside

Name: testCalc
Value: 50
LowLimit: 9

And if it is 5°C outside:

Name: testCalc
Value: 52.5
LowLimit: -3115

Before moving forward with it, I would like some feedback on this structure, and in particular, is it overkill to create so many tables for such a simple problem?

As there is 1-1 relations all across the chain, having one table would obviously be easier, but I am worried about having a mostly empty table full of varchar(8000) fields.


texts:
I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current composite 6 field primary, then make the current key a unique key for referential integrity and recreate the indexes.

    CREATE TABLE [dbo].[Autocompleter](
        [CountryId] [int] NOT NULL,
        [ProvinceId] [int] NOT NULL,
        [LocationId] [int] NOT NULL,
        [PlaceId] [int] NOT NULL,
        [EstabId] [int] NOT NULL,
        [LocaleId] [int] NOT NULL,
        [Title] [varchar](400) NOT NULL,
        [Hotels] [int] NULL,
        [AlternateTitles] [varchar](4000) NULL,
        [EnableHotels] [bit] NOT NULL,
        [EnableHolidays] [bit] NOT NULL,
        [DisplayPriority] [int] NOT NULL,
     CONSTRAINT [PK_autocompleter_1] PRIMARY KEY CLUSTERED
    (
        [CountryId] ASC,
        [ProvinceId] ASC,
        [LocationId] ASC,
        [PlaceId] ASC,
        [EstabId] ASC,
        [LocaleId] ASC
    )

Any gotchas I should be looking out for ? if it is an identity field I am thinking this should not break code that inserts into the table (as long as it specifies the columns explicitly)

I plan to create a new clustered index on the surrogate key and then make the current clustered index on the 6 fields a NC index.


texts:
We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait time on this wait event. Following is the current setting in our 11g db.

Sqls order by cpu

CPU Time (s) Executions  CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
7,279.38 9,694 0.75 31.50 7,297.79 99.75 0.27 0dp0c8wux0jnt    select case when max(LMA.LEAS...
6,562.18 1,395 4.70 28.40 6,582.28 99.69 0.31 3g2kw67v3v0qs    SELECT NAME, vpd_predicates.ge...
4,229.12 836,735 0.01 18.30 4,234.99 99.86 0.00 55a17fhdqg315 DBMS_SCHEDULER  begin :con := SELECT_POLICY_FU...
2,683.17 968 2.77 11.61 2,605.03 103.00 7.61 dfd3gcuamscs8    select sc.Doc_ID docId , sc.DO...
2,434.46 853,025 0.00 10.54 2,442.95 99.65 0.00 4y5m5y6y6wvzs DBMS_SCHEDULER  SELECT COUNT (*) FROM CONTACT_...

Top 5 Timed Foreground Events 08-Feb-2011
Event   Waits   Time(s) Avg wait (ms)   % DB time   Wait Class
DB CPU      23,108      94.74
db file sequential read 1,332,461   797 1   3.27    User I/O
direct path read    430,600 523 1   2.14    User I/O
db file scattered read  245,444 224 1   0.92    User I/O
SQL*Net more data to client 2,057,853   155 0   0.64    Network


texts: Definition of "table variable" and "base table"

texts:
When reading SQL standard text (SQL:2003), I can not get the meaning of following sentence (in "Cluase 4.3 Tables" in SQL:2003-1):

  SQL-data consists entirely of table variables, called base tables.

I can not find the definition of "table variable" in the standard text, am I miss something obvious?


texts: What could empty our cache (plan and buffer)?

texts:
If I execute sp_BlitzFirst in my server, sometimes it tells me 2 things:

Plan Cache Erased Recently (indeed my plan cache is around a few hundreds MBs)
(sometimes) Page Life to low (indeed my page life expectancy is a few minutes and the buffer is down a few GBs instead of 40-50 most of the time)

Most of the time it's ok and the page life is around 2hour.

If I execute this:

DECLARE @path NVARCHAR(260)
SELECT @path=path FROM sys.traces WHERE is_default = 1

--Security Audit: Audit DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG,
--DBCC CHECKALLOC, DBCC CHECKFILEGROUP Events, and more.
SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (116) AND (TextData like 'DBCC FREE%' OR TextData like 'DBCC FLUSH%')
ORDER BY StartTime DESC

I get nothing, so I guess this means no one executed any DBCC FREEPROCCACHE or DROPCLEANBUFFERS. In case of memory pressure, SQL Server doesn't empty the cache (plan or buffer) but instead remove some items (with an algorithm of it's own), so I shouldn't see everything almost empty.

So what could empty my cache?

Trace with:

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%DBCC%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%ALTER DATABASE%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_configure%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec sp_reset_connection'

Didn't give anything, I don't have anything about FLUSHPROCINDB or FREEPROCCACHE or FREESYSTEMCACHE or ALTER DATABASE in my trace.

Perf Monitor of my cache we can see the drop occurring every 3-7min:

No sp_configure after 2016-11-23 when I run this query:

Link

No flush in sp_readerrorlog. The server hasn't rebooted.


texts: Design a table of items with many features; how to group those features

texts:
I have this table Storeswhich represents stores. Those stores are managed for many types of persons, like Administrators, Managers, Supervisors and so on, i.e. Each store has an administrator, a manager, a supervisor

I could make N tables for each type/position and link them by their respective id to the Stores table, but the requirement says it is possible that the positions could increase.

With that being said, is the following approach correct? Create a Stores_structure table with a foreing key 'id_position_fk' linked to a Positions table. That Positions table has listed:

id_position | description
_______________________________
1           | Administrator
2           | Manager

and so On. And the Stores_structure (still looking for a better table name) has:

id     | id_position_fk  | Name         | phone_number
_____________________________________________________
1      |  1              | John Smith   |
2      |  1              | Paul Smith   |
3      |  2              | Robert Smith |

And the id from Stores_structure is linked in the Stores table with a many to many relationship

The first red flag I see is with that approach: it is posible to have two 'Administrators' for a single store; other thing I'm concerned about is the 'excessive' "normalization" of the many-to-many table

Is there a better approach?


texts: Under which conditions would selecting a small column off a primary key be slow?

texts:
Usually querying the table is in the millisecond range at most.  But sometimes it takes 30 seconds and then times out.

What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow?

If I had to guess, I'd say the query is being prevented from being run because another query is altering the table in some way, but my knowledge of locking is severely limited.  Or it could be possible, maybe?, that another transaction is holding the table even though the table isn't really being used anymore for that transaction.

I can't use a SQL Profiler.  Is there some list of things I can systematically go through to check?

Thanks in advance.


texts: MySQL: Is it safe to have a table with multiple millions of rows?

texts:
I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once a day compute most of the work so that when a user wants to do a search, it would be faster.

However this cache table could easily have 10 to 50 million rows. Is this bad? Is this worth it? Will I gain in efficiency? What are things that I should keep an eye for?

We use InnoDB.


texts:
I am designing a database application which will be using PostgreSQL 9.3.  My question is regarding bulk loading a CSV to a partitioned table.  I have done a lot of research, but I'm still unsure of the best way to proceed.

In our application, we download data in CSV format every day from web-enabled devices and upload these to a single database table.  Each CSV contains a record for every minute in the day and we have a few hundred devices, so we end up loading about a million records each day.  This will increase though, as we are adding more devices every day.

We have found that we have performance issues as this table gets too large, so I would like to redesign it as a partitioned master table which uses range partitioning on the date field, so that each child table contains the records for a single month.  This seems like the best balance for us between table size and number of partitions.

My understanding is that the most efficient way for us to load data would be to insert the records to a staging table, then alter the table so it is a child of our master table.  In our case, though, this won't be possible because we need to update the table every day with new data; we cannot wait until we have a full partition after each month, then make that month's table a child of the master table.

So far, I've come up with three possible solutions:

Use bulk loading to send the CSV to the master table.  Triggering would be used to send the data to the correct child partition, as described here. I'm very concerned about the performance of this, as my understanding is that the triggers would be applied to every record that gets inserted.
According to that link, using rules instead of triggers would give better performance, as they would apply to the entire query instead of every record. Unfortunately, it also says rules are not supported with bulk inserts that use the COPY command.
Use bulk loading to send the CSV to the correct child table.  Since we generally upload one CSV at a time which contains one day's data for one device, we can add some logic to our ETL code that determines the correct child table for that day and sends it there. This is not ideal because I would like the partitioned table design to abstract this logic away and take care of it in the background.
Create another table which contains only the current month's data.  Update this table each day, and when the month is over, alter this table so it becomes a child of the master table with all the other past months' data.  Then create a new current-month table and start updating it instead.

I like this approach because it minimizes updates to the master table.  We do have a use case where we have to re-load data from previous months, so we will probably still have to use one of the previous methods for these cases.
The disadvantage with this approach is that our master table will not contain the data for the current month, as that will be located in a different table.

I'd appreciate anyone's input on this problem and the best solution, as I'm still somewhat new at this.  Please let me know if I've made any incorrect assumptions or I'm just flat-out wrong about something.


texts: overwrite table with data from another table - SQL

texts:
Our users want to refresh a QA database from production but want two tables (lets call them T1 and T2) to retain the original QA data. So I copied two tables from QA (DB1) to a temp QA database (DB2). Then refreshed DB1 from production. After the refresh, I want to overwrite T1 and T2 data from DB2 to DB1 so it can contain pre-refresh QA values.

I have done the following:

Use

select *
INTO D1.dbo.T1
FROM D2.dbo.T1

Then refreshed D1 from prod
Then truncate T1 with the following step:

SELECT COUNT(*) AS BeforeTruncateCount
FROM T1;
GO
TRUNCATE TABLE T1;
GO
SELECT COUNT(*) AS AfterTruncateCount
FROM T1;
GO

Now when I go  back to copy data from D2.T1 to D1.T1, I get the error that there is already an object named T1 in the database.

Should I drop the table and copy?

Or is there any better method for the whole procedure?


texts: Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation)

texts:
Before you start suggesting this question for duplicate read it, I have searched and i haven't found any proper solution.

So, i'm building a social networking site (I know that you are laughing right now)
and i want to save the friendship states and the messages that users send everyday.

Now let me explain the problem more clearly, I thought that if i use a table for the friendships and have 40K users (and the average user have 50 friendships) that means that the table is going to grow to 2 million rows!

This will result to slow data processing or even to a database failure.

What if I use the file system to store the friendships instead of a Database?
I mean, i can store friendships in files.  (every user could have a "buddy.dat" file in his personal subdirectory that contains his friendships)

I have already done this thing for the messages, every user has a personal subdirectory created with a "messageges.dat" file that contains his conversations.  (This means that I'm going to have a directory with 40K personal sub-directories! 1 for every user)

Are there any consequences if I use the file system for this purpose?
(store the friendships/messages to files and then retrieve the data from there)

What is your opinion / solution?

EDIT:

I have news guys!!
I made a script to generate 40K directories in the "messages" directory and another script that generated 2 million rows in the users table!!

So, in the 40K directories case, they didn't slow down my site's scripts, it only took me half a minute to list them if i wanted with the file browser in a Debian distro.
In the mysql 2 million rows case, the 2 million rows didn't slow down my database or the site either, everything was ok.


texts: How do I find I.O. statistics (by index) for queries in progress

texts:
By running SET STATISTICS IO ON before running a SQL query, I can get an idea of the number of logical reads performed on each index after the query completes.

By looking at the view sys.dm_exec_requests, I can get an idea of the number of logical reads (total) that have been performed so far for any query that's in progress.

How can I get an idea of how many logical reads have been performed on each index so far for queries in progress?


texts: What does Error: 17828, Severity: 20, State: 4 mean?

texts:
I've found this guy in my SQL log:

"The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xx.x.xx.xxx]

Error: 17828, Severity: 20, State: 4."

but I can't find any information on it.

We are running SQL 2005 Enterprise (x86) - SP2 and the error is coming from the AD server. The users are getting errors on the application side - "Connection failure". It's random too and I can't reproduce it.

Does anyone know how to fix this (besides 'contact the vendor')?


texts:
I have created a new, clean firefox profile from scratch, and added couple of bookmarks. These, I have learned, are stored in places.sqlite file inside the profile directory. This file seems to be too big (1.2 MB) and I am wondering why:

$ ls -lh places.sqlite
-rw------- 1 martin martin 1.2M Sep  8 07:24 places.sqlite

When I open it with sqlite3 from the commandline, I see there are couple of tables:

$ sqlite3 places.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
sqlite> .tables
moz_anno_attributes  moz_favicons         moz_items_annos
moz_annos            moz_historyvisits    moz_keywords
moz_bookmarks        moz_hosts            moz_places
moz_bookmarks_roots  moz_inputhistory

but none of them contain much data (I have tried SELECT * FROM <TABLE> ; for all of them).

Further, if I dump the entire database into file dump.sqlite:

sqlite> .output dump.sqlite
sqlite> .dump

The file has only couple of KB:

$ ls -lh
total 1.2M
-rw------- 1 martin martin  39K Sep  8 19:01 dump.sqlite
-rw------- 1 martin martin 1.2M Sep  8 07:24 places.sqlite

I am aware of the vacuum command in sqlite3. I did that, but it has no effect. The database file is still same size.

How can I investigate why the database file is so big, and what does it contain ?


texts: Postgres authentication fails: log refers to "line 90" in pg_hba.conf that shouldn't exist

texts:
Whatever settings I try, I keep getting connection errors, from external clients as well as trying to connect from a running console on the server itself.

My last attempt for a local connection with "user_test":

psql -h /var/run/postgresql -p5433 -U user_test

When I checked the log on /var/log/postgresql/postgresql-12-db_test.log he last line on the log puzzled me: why is the log referring to "line 90" in the pg_hba.conf? That line should not exist???

2019-12-23 00:24:50.620 CET [11398] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2019-12-23 00:24:50.620 CET [11398] LOG:  listening on IPv6 address "::", port 5433
2019-12-23 00:24:50.623 CET [11398] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2019-12-23 00:24:50.649 CET [11399] LOG:  database system was shut down at 2019-12-23 00:24:50 CET
2019-12-23 00:24:50.654 CET [11398] LOG:  database system is ready to accept connections
2019-12-23 00:25:22.452 CET [11426] user_test@user_test LOG:  provided user name (user_test) and authenticated user name (postgres) do not match
2019-12-23 00:25:22.452 CET [11426] user_test@user_test FATAL:  Peer authentication failed for user "user_test"
2019-12-23 00:25:22.452 CET [11426] user_test@user_test DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

Here's a summary of my setup:

Fresh installation of postgesql 12 on ubuntu cloud-server
created a cluster "db_test" (alongside to the standard installation "main")
added a role "admin" and assigned user "user_test" to the cluster
told postgres to listen to all ports (ALTER SYSTEM SET listen_addresses='*';)
modified pg_hba.conf (sudo nano /var/lib/postgresql/12/db_test/pg_hba.conf)

That file currently reads (had many other settings none worked):

# TYPE  DATABASE USER ADDRESS METHOD
local all user_test trust
hostssl all all 31.164.122.223 md5

restarted the process using either:

postgres@servername:~$ sudo service postgresql@12-db_test restart

or

pg_ctlcluster 12 db_test start


texts: During index rebuilds through Maintenance Task in SQL Server 2014, do we need to apply Keep Index Online while re-indexing option in the Maintenance Wizard ?


texts: What are the advantages of a monitor server for log shipping

texts: I'm researching and implementing log shipping as part of disaster recovery for our SQL server. I've seen that a monitor server is recommended (but not required) for monitoring the log shipping process. What is the advantage of having that monitor server? Is it the only way to view the history of the log shipping process? Is it useful to have in case one of the servers goes down so you can get some data on what happened?


texts: Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

texts:
We are extracting content sections from word documents and have been going back-and-forth on how to store this arbitrary data. We are using PostgreSQL and our plan was to use JSON fields to save arbitrary sections in JSON fields.

Ideally we would like to just map out all of the relations into relations, however there is a lot of diversity in the types of data (tables, text, images, lists, etc.). None of the columns in each of the documents are the same and the same section in DocumentA can have a table and the same section in DocumentB can be made up of text or picture, hence the reason to store it in JSON.

This is an example:

DocumentA: has a table with fields named

ColA|ColB|ColC
val1|val2|val3
val4|val5|val6

DocumentB: has a table document with fields named

ColB|ColC|ColD
val1|val2|val3
val4|val5|val6

DocumentC: has a table document with with just a paragraph of text and a table

Note: more than one type of data can come out of each document (e.g. DocumentA can have 2x tables and a text data).

Store it in a single field in postgreSQL as a JSON array that is flat is our first thought.

This would be a single row in PostgreSQL for (e.g. INSERT INTO documentData(id,documentName,JSONField) VALUES (1, 'DocumentA', '<JSONDATA>') documentA.

[
 {
  "Content Type":"Table"
  "ColA": "val1",
  "ColB": "val2",
  "ColC": "val3"
 },
 {
  "Content Type":"Table"
  "ColA": "val4",
  "ColB": "val5",
  "ColC": "val6"
 }
]

Another record INSERT INTO documentData(id,documentName,JSONField) VALUES (2, 'DocumentB', '<JSONDATA>'

[
 {
  "Content Type":"Table"
  "ColB": "val1",
  "ColC": "val2",
  "ColD": "val3"
 },
 {
  "Content Type":"Table"
  "ColB": "val4",
  "ColC": "val5",
  "ColD": "val6"
 }
]

Another record INSERT INTO documentData(id,documentName,JSONField) VALUES (3, 'DocumentC', '<JSONDATA>'

[
 {
  "Content Type":"Text"
  "Data": "Random paragraph of text",
 },
 {
  "Content Type": Table",
  "ColA": "val1",
  "ColE": "val2",
  "ColF": "val3"
 }
]

The main issue I see doing it this way could potentially result in difficulty of querying the data. Are there any other suggestions?

Ultimate goal is to extract contents within certain control documents. The control documents have tables that have columns and cells that have names of parts of other documents we have to reference. Storing it in RTF would be a good thought, but not sure how to deal with searching and referencing the material reliably.


texts: MariaDB suddenly requiring database name prefixed to table name

texts:
I'm new to MariaDB (and databases in general). I'm using MariaDB 10.5.9 on Windows 10 from terminal.
I'm not sure how/why, but for some reason MariaDB is requiring my SQL statements using table names to prefix the database name to the table name, even when I have specified 'use database_name' and the line shows MariaDB [database_name].
So for example, this previously worked:
MariaDB [(none)]> use database_name;
Database changed
MariaDB [database_name]> ALTER TABLE table_name ADD COLUMN columnA int;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'columnA' at line 1

whereas now I must type:
MariaDB [(none)]> use database_name;
Database changed
MariaDB [database_name]> ALTER TABLE database_name.table_name ADD COLUMN columnA int;

I may be mistaken, but I'm 99% sure I didn't have to type the database name once I had specified use database_name; and all the help examples/resources don't include that.
This question here is similar, where the answer indicates use database_name; prevents the need to attach the database to the table name (however I'm not using SQL keywords as column names).
Wondering if there's a setting that might change the need to always require the database_name prepended to table_name?
Any suggestions (including suggestions to improve my question) would be appreciated!
Thanks!


texts: B+ Tree structure with buckets (Begginer question)

texts:
I am enrolled in a course and I am confused about few concepts in the structure of the B+Tree. I know what it does and what's the difference between it and BTree, but I got a bit conflicted when I saw 2 different examples:

1-B+Tree with buckets at the end:

2-B+Tree without buckets at the end:

Example 2 is the most popular example I noticed on the web.
My instructor told me that in example 2 the pointers of the leaves can point to buckets too, but wait a second it would be the same as the first example but with the last pointer to the left going to the next leaf.

Question: Is it mandatory for a b+tree leaf to have a pointer towards the next leaf?

My instructor told me to make it easier on myself to assume the bucket as a leaf that has a pointer towards the next bucket. It's better not to understand something on understanding something using a wrong way.

There's this clash inside me about the B+tree structure. Is there more than one structure for a b+tree?


texts: Having primary key in both fields but repeated in either fields allowed repeating

texts:
Is it possible to have both fields to be primary key but allows repeating  For either 1 field in Oracle SQL.

   Student_ID  Date
   1001        23/10/2016  // allowed
   1002        23/10/2016  // allowed
   1002        24/10/2016  // allowed
   1002        24/10/2016  // not allowed


texts: I've read in a forum that if your database capacity is more than 1G you should buy its license. Is this true? How much will it cost?


texts: How to perform asynchronous cascade slave replication of PostgreSQL database?

texts:
This is a feasibility/architectural question (I am no database administrator). Let's imagine a PostgreSQL database running online (like those from Heroku for example).

One would like to maintain a slave copy of this database on a remote PC by connecting to this online database (24/48 hour intervals). Is this possible from a PostgreSQL perspective? If yes, what would be the best Postgres implementation/configuration strategy? What do I need to learn?

The slave copy should catch up on all modifications made to the online database (eventually by specifying some kind of 'until' date if necessary). This slave would be used in read mode only. Ideally, slaves to the slave must be implemented in this asynchronous way too (i.e., in a cascading fashion).


texts: Any way to update VARBINARY value on certain position?

texts:
for example, if i have string '012345678', and i need to update position number 4 into another byte/character 'x' for example:
INSERT INTO foo(pk,pos) VALUES('abcdefghi',5)
ON DUPLICATE KEY UPDATE
  pk = CONCAT(
     SUBSTRING(pk,1,VALUES(pos)-1),
     SUBSTRING(VALUES(pk),VALUES(pos),1),
     SUBSTRING(pk,VALUES(pos)+1)
  );

-- simpler example:
SELECT CONCAT(SUBSTRING('abcdefghi',1,5-1),SUBSTRING('123456789',5,1),SUBSTRING('abcdefghi',5+1));
-- abcd5fghi

how to do this in VARBINARY data type?


texts: Creating Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security

texts:
We've recently deployed a new SSRS Server 2012, that can use Windows Integrated Security. We configured the servers to properly use Kerberos authentication and we solved the double hop issue.

Now, all our reports are using Shared data sources that are configured to use Windows Integrated Security instead of storing an execution user on the data source setup page.

It all works fine and I was very happy about it, as it increased our security (as the execute permission is derived from the AD user and not from the hard-coded execution user), but now we're running into a problem:

When we try to create a Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security, we're getting the error message:

  Subscriptions cannot be created because the credentials used to run reports are not stored, or if a linked report, the link is no longer valid

I've googled around, and all the links I've found tell me to simply either configure the shared data source to have stored credentials, or configure the report to have custom data source with stored credentials.

This contradicts the whole point of us have Windows Integrated Security!

Is there any way around it?

The sole purpose of SSPI in our use case is to authenticate to the database. The RS server has reports that use data sources on different SQL Servers. We are not, for example, using Integrated Security for row-level security, or to show different data to different users.


texts:
I'm playing around with PostgreSQL and would like to validate that everything is valid after migrating a data model.

In: how-to-show-all-invalid-objects-in-postgresql it is stated that only indexes need to be checked, but I noticed that I can create a function that references a table that does not exist:

CREATE OR REPLACE FUNCTION DUMMY()
RETURNS INT AS $$
BEGIN
    return( select count(1) from table_that_does_not_exist );
END;
$$
LANGUAGE plpgsql;

In the same fashion, I can create a valid function and drop the dependent table without a complaint

Is there a way to detect that a function is in an invalid state, preferably from SQL? Is there other things that can be invalid in the same way (and how to detect that)?

While I'm at it, In Db2 a table has an internal counter that is increased for certain operations (drop column for example). When this counter reaches a certain value (3?) the table becomes in-accessible and needs to be reorganized (vacuum seems to be a similar operation in Postgres). Can a table in Postgres become invalid after a certain number of "dangerous" operations, if so, how would one detect that a table is about to enter an invalid state?

I guess what I'm trying to find out how I can detect if there are any invalid objects in the database before discovering it in run-time.


texts: MySQL statement-based replication and stored procedure

texts:
When you have a MySQL statement-based replication and you create a stored procedure that modify some rows (INSERT, UPDATE, DELETE commands...), What is really replicated on the slave when you call this procedure on the master ? The CALL himself or the commands (INSERT, UPDATE, DELETE...) inside the procedure ?

For example, create such a procedure:

delimiter |

CREATE PROCEDURE myproc ()
      BEGIN

        DELETE FROM mytable LIMIT 1;

      END |

delimiter ;

Then call on the master:

master> CALL myproc();

=> does it replicates "CALL myproc()" or "DELETE FROM mytable LIMIT 1" ?


texts: How to clone a table schema (including indices) in SQL Server?

texts:
Is there a command that will completely clone the schema of a table?  This would include all constraints, foreign keys, and indices on the table.

In SSMS the Script Table as | Create To menu command does not include indices defined on the table.


texts: Clustering vs. transactional replication vs. availability groups

texts:
Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails.

As a developer and not a DBA, I am struggling to understand when to use which scenario for my failover / high availability:

Two (or more) servers in a Windows Failover cluster, SQL Server as a clustered instance
Two (or more) SQL Server instances that are kept up to date with transactional replication
Two (or more) SQL Servers in a SQL Server Availability Group, configured in a synchronous commit mode

Which of each of those scenarios works for what kind of workload, and what kind of failure / outage can be handled by those scenarios? Are they even comparable / exchangable?


texts:
I don't have this kind of ID in my table, still MySQL is giving me a result.
This same result is giving when i place in my php code.


texts: “ORA-03146: invalid buffer length for TTC field” after upgrade from Oracle 12 to 19c

texts:
We are experiencing problems with updating a table record BLOB column. We get the "ORA-03146: invalid buffer length for TTC field" error. We basically are saving e.g. PDF attachments to the DB. The problem occurred after the upgrade from Oracle 12.0.1.2 to 19c (19.3.0.0), now the error is occurring on a larger scale. We have a .Net application, connecting to Oracle through ODP.NET (Oracle.DataAccess) and using NHibernate. We have done an upgrade from Oracle 12.1.0.2 to Oracle 19.3.0.0 using DBUA and the DB server is on Windows 2012 platform.
The error occurs after executing the command: {OpsSqlPrepare2(): SQL: UPDATE SIRIUS.Attachment SET Request = :p0, LOANREQUEST = :p1, ARCHIVE = :p2, FILENAME = :p3, FILESIZE = :p4, BINARYCONTENT = :p5, ATTACHMENTTABLE = :p6, ATTACHMENTTYPE = :p7, NOTE = :p8, MIMETYPE = :p9, CUSTOMID = :p10, ISEXTERNAL = :p11, ISSEND = :p12, DocumentNameDial = :p13, DOCUMENTENUMTYPE = :p14, AttachmentTypeDial = :p15 WHERE ID = :p16}
BINARYCONTENT is a BLOB column.
The error is different from the two environments we have:
Oracle DB 19c/Oracle client 19c - ORA-03146: invalid buffer length for TTC field
Oracle DB 19c/Oracle client 12 - ORA-03138: Connection terminated due to security policy violation


texts: Join multiple tables without duplicated results and without UNION in PostgreSQL

texts:
Lets assume we have two tables A and B in PostgreSQL:
A

title
first_group_id
second_group_id

My Title
1
2

B

group_id
element_name

1
First

1
Second

1
Third

2
Fourth

2
Fifth

I want to get the following result:

A.title
B0.element_name
B1.element_name

My Title
First

My Title
Second

My Title
Third

My Title

Fourth

My Title

Fifth

If I query something like the following - I will get 6 rows instead of 5 cause all rows from B0 will be mixed with all rows from B1 and the number of result rows will grow in progression if B0 or B1 have more rows:
SELECT A.title, B0.element_name, B1.element_name
LEFT JOIN B B0 ON A.first_group_id = B0.group_id
LEFT JOIN B B1 ON A.second_group_id = B1.group_id
FROM A;

A.title
B0.element_name
B1.element_name

My Title
First
Fourth

My Title
First
Fifth

My Title
Second
Fourth

My Title
Second
Fifth

My Title
Third
Fourth

My Title
Third
Fifth

I can achieve the desired behavior by using UNION but I wonder if there is a more efficient way to do that? Especially when it's required to make more than two JOINs in the same manner.


texts: Clear PostgreSQL logs in AWS Ubuntu Server

texts:
I am trying to clear the logs of an postgresql under /var/log/postgresql/ but I am getting error message as below:
 /var/log/postgresql$ sudo > postgresql-11-main.log
 -bash: postgresql-11-main.log: Permission denied

Below is folder files structure:
 /var/log/postgresql$ ls -la
 total 1097296
 drwxrwxr-t  2 root     postgres       4096 Apr 25 06:56 .
 drwxrwxr-x 12 root     syslog         4096 Apr 28 06:35 ..
 -rw-r-----  1 postgres adm      1107919237 Apr 29 06:33 postgresql-11-main.log
 -rw-r-----  1 postgres adm        14480619 Apr 25 06:56 postgresql-11-main.log.1
 -rw-r-----  1 postgres adm         1199751 Apr 18 06:49 postgresql-11-main.log.2.gz

Also, is it okay to clear the logs of an PostgreSQL? will it harm any existing database or work flow?


texts: How to capture runtime parameters with extended event?

texts:
I use rpc_completed event in order to capture executed sprocs. I am also to view the statement and passed parameters by using the following way:
right-click extended event -> Watch Live Data and finally see statement+parameters
However I want to see the captured data in table format, but I wasn't able to implement it. When I run below query it shows statement without parameters. How can I see parameters in the table form? Need your suggestions.
IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL
DROP TABLE #capture_waits_data
SELECT CAST(target_data as xml) AS targetdata
INTO #capture_waits_data
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE xes.name = 'LongRunningQueries'
AND xet.target_name = 'ring_buffer';
--*/
/**********************************************************/
SELECT
CONVERT(datetime2,SWITCHOFFSET(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,
xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS statement,
xed.event_data.value('(data[@name="duration"]/value)[1]', 'bigint')/1000 AS duration_ms,
xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
FROM #capture_waits_data
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)
WHERE 1=1 and xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') <> 'NT SERVICE\SQLSERVERAGENT'

Here is actual used extended event:
CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb'))
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


texts: How to manage stored procedure dependencies in SSDT?

texts:
I have multiple stored procedures, some that depend upon others. When publishing my database, I receive an error when creating some stored procedures due to dependency issues. SSDT seems to be creating the stored procedures in alphabetical order, not dependency order like I need.

For example, suppose sp_ProcA depends upon sp_ProcB. SSDT is trying to create sp_ProcA first and fails because sp_ProcB hasn't been created yet.

I've looked through project and script settings. I've found nothing related to dependency management.


texts: When building a new standby database, is it possible to have it start shipping archive logs from the primary without having the datafiles on the standby yet?


texts: Writing data to SSISDB.Catalog.Executions

texts: I am trying to test a stored procedure which reads some values from SSISDB.Catalog.Executions. For achieving the same goal I want to write dummy data to SSISDB.Catalog.Executions. Is that even possible? If yes, how?


texts: SSIS Connection String Expression at Run Time and during Execution

texts:
I have an Excel connection manager which should write to a file which does not exist prior to runtime but rather is created during execution. I have an expression set for the connection string property of the excel connection manager which points to the file that will be created. The problem I am having is that since the file does not yet exist (prior to execution), when I go to run the package it is giving me an error since it can not find the file. If i create a dummy file and place it where the excel connec manager is pointing the package will run ok.

What I have tried to do is point the excel connection manager to an existing file prior to run time since it will not give an error and then hopping it will then pick up the new Path (excel connection string) from the Expressions but what is happening is that since I have an expressions set for the connection string property it will not let me overwrite the connection string property, it keeps defaulting to the value of the expressions which contains other variable that are populated during run time so that is where I am getting my error. The expressions string prior to runtime is not pointing to an actual file because it does not yet exist but mainly because the expressions is not fully populated until runtime since it also contains variables.

As of right now it seems like I might have to just point to a dummy template file (without using any expressions) prior to runtime and then maybe use a Script to change the connection string property of the excel connec manager to = a variable containing an expression containing the path to the file....

Does this sound like the way to go  or am I over complicating things?


texts:
I’m trying to secure my Databases and wanted to know what are the default or built in users for Postgres databases?
Am I able to change the usernames or remove these users?


texts:
When I work on a huge database, and after following normalization rules, I found that I have many tables for each part of my huge database. That makes me worry about the final number of tables, it will be in the hundreds.

What is the best choice for me?

make many tables with a small number of columns? or
make few tables with many columns?


texts: Linked server Informix driver mismatch error on driver and applciation

texts:
I tried to read all the questions here that has been asked before, but could not find any clue on my problem.

I'm trying to setup a linked server to connect on INFORMIX server, I installed the driver, i configured it on the 32Bit odbc, connection was successful, how ever when i tried to create a linked server I bumped into this error

****OLE DB provider "MSDASQL" for linked server "CMS_MY" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "CMS_MY".****

any idea what i missed and any configuration that I look into?
I'm using SQL Server 2012

Thank you very much.


texts: Why does a query on a local instance with local database files cause NETWORK_IO waits?

texts:
I have followed this tutorial to get the wait stats of a particular query while it is 'in flight' using extended events. Whilst the tutorial refers to an index rebuild, I just did a select statement from a single table

The waits (using the query in the tutorial) were

Wait Type            Wait Count  Wait Time  Resource Wait Time  Signal Wait Time
-------------------  ----------  ---------  ------------------  ----------------
NETWORK_IO                  693        778                 776                 2
SOS_SCHEDULER_YIELD           6          0                   0                 0

I ran the query in SSMS on the same box the instance is installed on (used . as my server name when connecting)
the data files for the database are on the C:/ drive of the box so I am confused as to why NETWORK_IO is showing?

As a further test, I disabled the network adapter on the box and found the same result (obviously with slightly different numbers)

Would I be right in thinking that NETWORK_IO relates to SQL server returning the result to SSMS?


texts: Query INFORMATION_SCHEMA for list of indexes

texts: How does one query INFORMATION_SCHEMA in a standards-compliant database such as Postgres to obtain a list of all indexes in one database/catalog?


texts: Is it possible to create this type of database for a massive forum?

texts:
Kind of like how craigslist you select a state, then a city, and you're in that community.

How do I make my forum website's home page have 2 drop menus for a state and city, then it would bring the member to a specific Forum? is this really difficult?

Please direct me to what I need to learn to make this happen!


texts: Select failed from V$LOGMNR_CONTENTS when called from a procedure

texts:
Assuming the following user with these privileges granted:
-- create admin user on CDB
CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE system QUOTA UNLIMITED ON system ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER c##myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to c##myadmin                            ;
GRANT CREATE SESSION TO c##myadmin                 ;
GRANT CREATE TABLE TO c##myadmin                   ;
GRANT EXECUTE_CATALOG_ROLE TO c##myadmin           ;
GRANT EXECUTE ON DBMS_LOGMNR TO c##myadmin         ;
GRANT SELECT ON V_$DATABASE TO c##myadmin          ;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##myadmin   ;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##myadmin      ;
GRANT SELECT ON V_$LOG TO c##myadmin               ;
GRANT SELECT ON V_$LOGFILE TO c##myadmin           ;
GRANT RESOURCE, CONNECT TO c##myadmin              ;

Now, when I connected as my myadmin, I can run the following:
BEGIN
  DECLARE v NUMBER := 0;
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/log/arc0000013.0001', Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(StartScn=>23456789, EndScn=>23567890,  Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
  select count(*) into v from v$logmnr_contents;
END;
END;
/

PL/SQL procedure successfully completed.

but when it created as a procedure it failed with insufficient privileges:
Create Or Replace Procedure Test AS
 v NUMBER:=0;
BEGIN
 DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/log/arc0000013.0001', Options=>DBMS_LOGMNR.new);
 DBMS_LOGMNR.START_LOGMNR(StartScn=>23456789, EndScn=>23567890,  Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
 Select Count(*) into v from v$logmnr_contents;
END;
/
Exec Test
/

Procedure Test compiled

Error starting at line 9 in command -
BEGIN Test; END;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "C##MYADMIN.TEST", line 6
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

If I comment out the select the procedure succeeds.
Is there an additional privilege to enable it to run the select from a procedure?


texts: Postgres's equivalent for Oracle's datafile recovery

texts:
In oracle, one can do a recovery from datafiles.
Does Postgres have an equivalent procedure?
I am already aware of the WAL files in Postgres, but I believe this is different. Datafiles in Oracle are the actual files on disk.

Data files are the operating system files that store the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs

Does something like this exist for Postgres?


texts: Determining the full postgresql size on windows

texts:
While opening the Programs and Features tool panel on my Windows 7 (64-bit), I found that my postgresql (8.4) install was taking 108 GB.
I do not use postgresql myself, I just use a tool that need a postgresql db, and I am fairly confident in the fact that my postgresql databases are not that large.

When I go to the install folder of postgresql (C:\Program Files (x86)\PostgreSQL) and then clicks on properties, it displays a size of 1.45 GB. In c:\users\postgres I get 13.5 MB. I end up quite astonished by the windows diagnosis of 108 GB.

So here is the question :
How could I calculate the whole size on disk taken by postgresql (including everything : soft, logs, db, tools, etc...). Is everything located under the install folder and the postgres user folder?

I also would like to have your views on which is responsible of the mistake (if any) about the size calculation in the tool panel, postgresql or windows? How could windows end up showing that very large size information?

Thanks for your inputs!

Edit : I made several searches on the windows tool after having read the @a_horse_with_no_name comment's, and found some detailled information :

http://social.technet.microsoft.com/Forums/zh/w7itprogeneral/thread/95af2aa5-507f-45ab-9bd1-4c4c7cde8145
http://technet.microsoft.com/en-us/magazine/gg558108.aspx
http://community.spiceworks.com/how_to/show/2238-how-add-remove-programs-works

So, only the my main question remains, how could I calculate the whole size taken by postgresql on my disk. Thanks.


texts: How to create subquery on multiple result columns?

texts:
I have two tables, both sharing the following columns:

table inventory: id, key, timestamp
table stocks: id, key, timestamp

I want to select all occurrences of the key from inventory table together with the minimum timestamp found:

SELECT key, MIN(timestamp) as timestamp from inventory GROUP BY key;

Using that result, I want to remove anything from stocks table where the key matches, and the timestamp is from before the timestamp in the inventory table.

Problem is that I want to return 2 result columns from the SELECT, so howc an I reference them during the delete?

Pseudocode:

DELETE FROM stocks where key = inventory.key and timestamp < inventory.timestamp;

Question: how can I combine those two queries in once go? Maybe with subselect or similar?

Sample data:

table inventory;
#id, key, timestamp
1, key1, 2018-10-10
2, key1, 2018-10-10
3, key2, 2018-10-10

table stocks:
#id, key, timestamp
55, key1, 2018-10-10
56, key1, 2018-10-10
57, key2, 2018-10-09

As a result, only row with id 57 in table stocks should get removed, because it's timestamp is before the timestamp of the key2 row in table inventory.

Could I do better than?:

DELETE s FROM stocks AS s
JOIN (
    SELECT key, MIN(timestamp) as timestamp FROM inventory GROUP BY key
) as i
WHERE i.key = s.key AND s.timestamp < i.timestamp


texts: SQL Server: Frequent Login timeout expired in Linked server

texts:
I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local server. Linked server has been established successfully. The query used in Stored procedure looks like below.

SELECT ID,Col1,Col2,Col3,Col4,Col5 into #temp from [111.111.111.111].ProdServer.dbo.SourceTable

Insert into Localtable(ID,Col1,Col2,Col3,Col4,Col5)
SELECT ID,Col1,Col2,Col3,Col4,Col5
FROM #temp where ID NOT IN (select ID from Localtable)

The job execution sometimes takes 25-30 secs which will successfully executed. Sometimes it takes 48-50 seconds which results in failure. Please refer failure message below and other details in above screenshot.

Failure Message:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 42000] (Error 53)  OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.

More Details

The table is locked by nobody and no queries run on it.
In Linked Server Properties, both Query Timeout and Connection Timeout
has been set to 0.
In Server properties remote query Timeout is also set to 600 Secs.

As last option I tried to change Connection Timeout Settings from its default value 15 to 6000 or higher. I could not achieve it. Please suggest solution to execute my scheduled jobs successfully even it takes 48 minutes or slight more.

Right Click on server > Click on properties > Click view connection properties on left pane under connection - to see Connection Timeout Property

1 new observation:
The job is failing if it takes 48 seconds or more to run.


texts:
I have a login user called TestUser created at the server level that is assigned to a read only role, now I need to create a user for the TestUser login on MyDb

USE [MyDB]
GO
CREATE USER [Test] FOR LOGIN [TestUser]
GO
GRANT SELECT TO [Test]
GO
DENY DELETE TO [Test]
GO
DENY INSERT TO [Test]
GO
DENY UPDATE TO [Test]
GO

everything is good but when I script that Test user created, I don't see in any place the script that I'm giving select and denying insert, delete and update


texts: MySQL TRUNCATE TABLE blocks queries in other databases

texts:
When I run automated tests against my test_development database, I often observe degraded performance for my production users, even though those users aren't accessing the same database as the test suite. In viewing the process list, it seems that the degradation corresponds to frequent TRUNCATE TABLE statements which run on the test db (to reset the db state for the next test). I see a system lock for the TRUNCATE TABLE statement, and I see "Waiting for query cache lock" for the production users. Here is part of my process list:

mysql> show full processlist\G
*************************** 8. row ***************************
 Id: 67503075
User: delivery
Host: xxxxxxxxxxx:34734
db: test_development
Command: Query
Time: 0
State: System lock
Info: TRUNCATE `change_version`
*************************** 12. row ***************************
Id: 67503141
User: delivery
Host: xxxxxxxxxxx:37959
db: delivery
Command: Query
Time: 0
State: Waiting for query cache lock
Info: SELECT COUNT(*) FROM `address_single_4` WHERE `address_single_4`.`reportId` = 75483054
12 rows in set (0.04 sec)

Why is the TRUNCATE TABLE statement (apparently) blocking queries that are not in the same database, and how can I fix it?

I am on MySQL 5.6.21, running in Amazon RDS. All tables are InnoDB.


texts: How to interpret and modify a dBase (.dbf) file?

texts:
I'm working with dBASE (.dbf) files for a project using a GIS software because it seems that they are more efficient. I've converted several .xml files to .dbf with OpenOffice 3.4 and loaded into the GIS however in some columns, the fields that appeared with different numbers in Open Office, turn to a series of one (1) in the GIS attribute table.

This is an example of my first row of one element:

Na,N,6,2

I think that changing the first row of every columns of my .dbf file I can set up the properties.

First question:

I would like to know what does it means every character. I know that the first (e.g. Na) is the name of the field, N is probably the type? Number? I've no idea about the third and the forth (red and blue arrows).

Second question:

Why if I write Na when I load the file it become NA, with capitalized letters?

Thanks a lot!


texts: Merits of inserting to temp table and renaming versus just adding/removing columns

texts:
If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed.

When I use RedGate SQL Compare to create conversion scripts its strategy is to create a temporary table, copy all the data into that table, drop the old table, and then rename the temporary table.

I assume this cleans up the pages as all the rows were "perfectly" sequentially inserted.

I recently had a DBA tell me that this "copy and rename" approach is inefficient, expensive and unnecessary.

What are the merits of these two approaches?


texts: Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?

texts:
I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com.
RPE uses a SQL Server database.

Their old POS server had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data.
However, my client doesn't seem to make backups very often - the last one is dated January 5, 2015.

I was able to copy the C:\Program Files\Microsoft SQL Server\ folder which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1.

I installed the RPE software on the new computer and a SQL Server instance called MSSQL10_50.PCAMERICA.
The new computer is using SQL Server 2008 R2, while I believe the old computer would have been using SQL Server 2005.

I am not a DBA, especially when it comes to SQL Server.

Is is possible to 'recover' the database from the 'raw' .mdf and .ldf files from the old computer to the new computer / SQL Server instance?
If so, how should I proceed?


texts: Arithmetic overflow error when using sp_Blitz

texts:
I am getting this error:

  Msg 8115, Level 16, State 2, Procedure sp_Blitz, Line 469  Arithmetic overflow error converting expression to data type int.

when running sp_Blitz in a server with 52 user databases.  I also get it when I run it with @CheckUserDatabaseObjects = 0.

I have the latest version from 10/14/2016.

Any ideas what I am doing wrong or what over parameters I should run this with since there are so many user databases?


texts: GRANT EXECUTE ON PROCEDURE unable to USE database

texts:
I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance.

The stored procedure is defined with SQL SECURITY as DEFINER.

As a user with permission to create a stored procedure

USE dataDB;
DROP PROCEDURE IF EXISTS fetchData;

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY DEFINER
  COMMENT 'Accepts an ID and returns the record'
BEGIN
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;

########
GRANT EXECUTE ON PROCEDURE `dataDB`.fetchData TO 'otherUser'@'%';
FLUSH PRIVILEGES;

########

As 'otherUser'@'%'

USE dataDB;
/* SQL Error (1044): Access denied for user 'kny_opea'@'%' to database 'kny_mint_pre' */

CALL dataDB.fetchData(3);
/* SQL Error (1370): execute command denied to user 'otherUser'@'%' for routine 'dataDB.fetchData' */

I then granted the following

GRANT EXECUTE ON `dataDB`.* TO 'otherUser'@'%';

But this becomes an issue as the user then has execute permissions to any other existing procedure and any future procedures within dataDB.

How do I grant execute permission to a single procedure to the invoker without granting execute privilege to all procedures or another privilege that would allow the user to USE the database?


texts: How to use database users as a foreign key field in any table

texts: I want to create a table where I can have a column username which will be the foreign key and it will only contain usernames of existing users on that database. For example, if I want to insert 'John' in that field, there have to be a database user 'John' otherwise database should throw error. Any help?


texts: MySQL database for storing city bounding boxes and names

texts:
I need to design a mysql database which contains a table of cities which supports only a single query - given a point, return the city (or cities) which it falls in. I have the bounding box for each city. What should the column look like to store the city bounding box to fully take advantage of mysql's spatial-indexing, multiple POINT columns? POLYGON? And what does the query look like.

Most discussion I've seen of spatial indexing involves the inverse question - given a bounding box, return the set of entries which have a POINT column which falls within the bounding box, which they are able to do using MBRWITHIN to obtain.


texts:
I have a relation R(A,B,C,G,H,I) with the functional dependencies (FDs for brevity) F = {A→B; A→C; C,G→H; C,G→I; B→H}. I want to decompose it into BCNF.

So, we have AG as candidate key. I consider the first FD A→B. I see that A is not a super key, so I determine its closure as ABCH and decompose it as R1(A,B,C,H) and R2(A,G,I).

Now, R1 satisfies the FD A→B, A→C, but violates B→H, so I decompose it further as R3(B,H)  and R4(A,B,C).

Now, my question is: How do I fit satisfy the other FD C,G→I and C,G→H in R2, as R2 does not contain C or H?


texts: UPDATE ... RETURNING * with large results error

texts:
I am using Postgres-XL (Distributed version of Postgres)

When I run the query:

UPDATE <table> SET <column> = <value> WHERE <conditions> RETURNING *;

and the result has about 600-700 rows. Then it works correctly, giving back results -- But if the number of results is in the range of 2000 - 3000 results then I get the error:

portal "" does not exist

This happens both from code and the psql command line.

Is there some configuration I can tune that controls how much data I can fetch or any way to increase the number of results I am able to pull from this query. Any pointers will be helpful.


texts:
I'm trying to create a table referencing a primary key from the table person;

CREATE TABLE Membership (foreign key (USERNAME) references Person(USERNAME),MembershipNo INT Primary Key,DatePaid date NOT NULL,DateTo date,AnnualFee INT NOT NULL)
                                      *
ERROR at line 1:
ORA-00904: "USERNAME": invalid identifier

I'm ether blind as a bat(possible) and there is a syntax error or I'm missing something.

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='PERSON';

USERNAME     ROLE    DATEJOINED  ADDRESS     EMAILADDRESS    PHONENUMBER

`

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='PERSON';

STUBT001     SYS_C00145052   P   PERSON                  ENABLED     NOT DEFERRABLE  IMMEDIATE   VALIDATED   GENERATED NAME          17-OCT-2013         SYS_C00145052


texts:
One of my developers is trying to perform insert into a table by selecting from a table.

Below is the query that was run. The query takes extremely long time for execution (4hrs odd).

Number of records on the table where it is getting imported is 1.2 million.

Database - Oracle 12c on Linux with archivelog mode on.

insert  /*+ parallel(24) */  into data1.frido_dev select  /*+ parallel(24) */  *
from DATA_MAIN;

How can the insert of such bulk volumes be optimized? Can we use append? Will that be helpful?

I'm not good at tuning queries, would be great if someone helps me out here.


texts:
I am using:

SELECT top 10 *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_INSERT_ROWS') AND AllocUnitName = 'dbo.HeartBeat'

This return some rows, but I cannot tell which column contains a date and time.

I want to know WHEN the record was inserted. The inserted record contains a TIMESTAMP provided by the user BUT I want to see if the user is actually inserting the record at the time given by TIMESTAMP


texts:
Configured Master-Master Replication
But both servers slave shows the below error !!

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from
binary log: 'Could not find first log file name in binary log index
file'

How to fix this


texts: Unable to use SSL Certificates in SQL Server on Linux

texts:
Hello everyone so here is the problem:
SQL Server 2019 installed on Ubuntu 20.04 (working before setting SSL certificates)
I was following the Official guide of Microsoft to secure my SQL server with SSL on Ubuntu 20.04.
I firstly tried using directly CA certificates created with Certbot and Let's encrypt. I spend hours trying without any luck.
So I decided to try the tutorial as it was with self signed certificates following the guide AS IS without any change.
These are my commands:
root@racknerd:~# openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=beta.mydomain.com' -keyout mssql.key -out mssql.pem -days 365
Generating a RSA private key
................+++++
.......................................................................................+++++
writing new private key to 'mssql.key'
-----
root@racknerd:~# sudo chown mssql:mssql mssql.pem mssql.key
root@racknerd:~# sudo chmod 600 mssql.pem mssql.key
root@racknerd:~# sudo mv mssql.pem /etc/ssl/certs/
root@racknerd:~# sudo mv mssql.key /etc/ssl/private/
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# systemctl stop mssql-server

Obviously I used a real domain that it's mapped to this machine IP but this is not the problem.
Following Microsoft's guide I restart the service and I keep having this error:
Unable to open one or more of the user-specified certificate file(s)
2021-01-16 08:58:05.35 spid23s     Error: 49940, Severity: 16, State: 1.
2021-01-16 08:58:05.35 spid23s     Unable to open one or more of the user-specified certificate file(s). Verify that the certificate file(s) exist with read permissions for the user and group running SQL Server.
2021-01-16 08:58:05.37 spid23s     Error: 49939, Severity: 16, State: 1.
2021-01-16 08:58:05.37 spid23s     Unable to initialize user-specified certificate configuration. The server is being shut down. Verify that the certificate is correctly configured. Error[30]. State[51].
2021-01-16 08:58:05.39 spid21s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

This is the same error that I get when I do the process using the Let's Encrypt certificates. I suppose so that stating I set the right permission on the certificates, the right owner there is something wrong with the official documentation of Microsoft or there is no way to make this work. I saw that someone else had the same problems but didn't find any fix for that, has something changed since then?
Many thanks


texts: SQL Server 2019 setting Force Encryption with a script

texts: I have to update 50+ servers to use encrypted connection strings. Is there a way to script setting Force Encryption and TrustServerCertificate (e.g Powershell, or ???) so that I don't have to open Configuration Manager and set on each box?


texts: How to drop SQL Server database currently in use and in Single user mode

texts:
I have a Database on SQL Server 2008, which I want to drop.
Currently it is in single user mode and it is currently in use.

select * from sys.sysprocesses

returns

Msg 924, Level 14, State 1, Line 1
Database 'G_MAIN_DE' is already open and can only have one user at a time.

and I do not know how to identify the session I have to kill.

An attempt to set it offline

ALTER DATABASE G_MAIN_DE SET OFFLINE WITH ROLLBACK IMMEDIATE

yields

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'G_MAIN_DE' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


texts: Generate a series of months within a period


texts: How to know if logreader agent already exists?

texts: I'm creating a script to recreate a database for test purposes, and the database has some publications.  In order to create or not the logreader agent I need to know if the current database already has one, or destroy the existing agent prior to recreate the database. I know there is some class in SMO can do that, but I need to achieve the same with T-SQL.  Someone can help?


texts: Is storing data that can be inferred go against data normalization?

texts:
To simplify lets say I have two tables PROJECTS and JOBS. A user creates a project and runs 1 or more jobs using a project.

So it would possible look something like this:

PROJECTS

[PK] project_id
etc.

JOB

[PK] job_id
[FK] project_id
job_run_date
etc.

Ideally I want to be able to list all projects by last used (last job run) and display them to the user. When I create a job, I can insert the job then update the project entry to have a last used; or I can just have it insert the job, then when I'm displaying all projects, I can do a join and aggregation with JOBS to get the last used date.

I figure, the drawback of the join is that as you have more jobs, performance may be slowly impacted over time to do the joins and aggregations. Whereas, being somewhat redundant increases the chance of a transaction failure and wastes a bit of space.

If I were to choose the update both tables (i.e. have a last_used` column in PROJECTS), would that be going against normalization (or good db design practices)?


texts: Fastest way to perform simple operations on 1 million rows

texts:
I have a simple table with more than 1 million rows in an InnoDB MySQL table.

Here are the steps that I need to perform on each row:

SELECT * FROM tbl where work_done=0
make some data processings its fast enough.
UPDATE tbl set data=data and work_done=1 where id=$row['id']

The above steps are very simple and my code doesn't take much time. I have an index on the work_done column but it is still taking time to even run a simple select query.

One approach I am considering is to divide the big table into small tables of a few thousands of rows, then run my code on each table, but this method feels 'dirty'.

I am running my code in many threads and I have to perform this task many times in future that's why I want to optimize it.

Is there a better approach?

Here is my my.ini:

skip-external-locking
key_buffer_size = 32K
max_allowed_packet = 2M
table_open_cache = 8
sort_buffer_size = 128K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
net_buffer_length = 4K
thread_stack = 256K
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 250M
innodb_log_file_size = 90M
innodb_log_buffer_size = 30M
innodb_open_files=300
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
max_allowed_packet = 32M
key_buffer_size = 8M
sort_buffer_size = 8M


texts: Table already exists error when trying to import sql file

texts:
I am trying to upload a backup sql file through phpMyAdmin.

I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty db.

I get the following error message.

#1050 - Table '`db`.`t`' already exists

Inside the import file each CREATE TABLE statement is suffixed by IF NOT EXISTS, so why is this being reported as an error?

I have tried to take out the table reported from the sql file, but then the next table just throws up the error, and the next etc until we have a sql import with no tables left.

I am using Windows XP with MySQL 5.5.37-win32, these versions could be different from the versions used when creating the backup, would this matter?

Thanks in advance.

Edit : Added SQL code.

This is just a sample, the code is created by phpMyAdmin's export function

-- phpMyAdmin SQL Dump
-- version 4.1.4
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 03, 2014 at 07:24 PM
-- Server version: 5.6.15-log
-- PHP Version: 5.4.14

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `mbfour`
--

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

--
-- Table structure for table `cars`
--

CREATE TABLE IF NOT EXISTS `cars` (
  `car_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(200) NOT NULL,
  `status` varchar(20) NOT NULL,
  `capacity` varchar(5) NOT NULL,
  PRIMARY KEY (`car_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `cars`
--

INSERT INTO `cars` (`car_id`, `type`, `status`, `capacity`) VALUES
(1, 'automatic', 'built', '4L'),
(2, 'automatic', 'in-production', '2L'),
(3, 'automatic', 'built', '2L'),
(4, 'automatic', 'in-production', '4L');
....
....

Much more sql data but seems pointless to post as it is all formatted the same.


texts: How to make sure that mysqldump command is working in command prompt

texts:
I'm getting this error:

C:\Users\IT-Admin>mysqldump
'mysqldump' is not recognized as an internal or external command,
operable program or batch file.


texts: Can I use a Composite Key as criteria for selection in a query?

texts:
I have created a meeting minutes table and would like to use two fields as a unique ID for each meeting (field one is a meeting code (lookup from another table) for each type of meeting and field two is the meeting date - the combination would be unique for each record). An example would be ADHOC11/11/2015.  I want to then set that composite key as the criteria for selecting the meeting to print a meeting minutes report.

I was able to set both fields as the composite key, but it would not let me save the table due to the "primary key cannot be null" error.  Is that because one of the fields was a lookup?

I want the user to be able to select the type of meeting (code) from a drop down list, enter the meeting date, complete the remainder of meeting table fields and then be able to enter the meeting type and date combo to pull the minutes for that meeting when running the report.

What is the best way to accomplish this goal?


texts: High performance count with where clause and dynamic data

texts:
I am looking for a high-performance approach to solve this problem.
I have a table on a SQL-Server 2019 with 316 columns and several million rows.
For this query, there are only a few of those columns that are relevant.  Something like a date-field and an amount-field (maybe the id-field as well).
Now I would like to know how many entries exist in a specific date range (from now, to X days in the past) that has an amount that is lower than Y.
My current approach (split the request into two requests):

Create a nonclustered index over the date-field & id-field.

Do a first select with a where-clause to get only entries in the relevant date range. (Select only id) Will this avoid a full-table scan?

Do a second select (count) with a where-clause over the ids (and maybe the amount-field).  The id-field is also the primary key and clustered index.

At step three I am not really sure if I can put the amount-field into the where-clause without any performance loss.  If that is not possible, maybe I should select only the ids and the amount-field and do the count in my application (in memory)?  It would be great if it is possible to avoid this.
Also, I am not sure if it will be necessary to split the request at step three if I will have 1,000,000 Ids which will hit the condition from step two.
What do you think about my approach?  Should I go for it, or is there a better one?


texts: MySQL: Error reading communication packets

texts:
I get this warning in mysql,

[Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets)

I have been through few topics in google and according to some suggestion I increased the max_allowed_packet from 128 to 512 to 1024 still the same behaviour.

I am using Drupal 7, and yes there are lots of blob data types, but 1024 Mb of max_allowed_packet should be enough in my opinion.

Any other workaround how to overcome this warning ?

EDIT:

Added some settings as @Rolando's suggestions/answer, I still get the same warning.

My mysql config looks like this:

[client]
port        = 3306
socket      = /tmp/mysql.sock
default-character-set = utf8

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1024M
table_open_cache = 128
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 192K
# Query cache disabled
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 100
thread_concurrency = 10
tmp_table_size = 128M
max_heap_table_size = 128M
log_error                = /var/log/mysql/mysql-error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

log_warnings = 2

server-id   = 1
binlog-format = row
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin = mysql-bin
log-slave-updates
relay-log=mysqld-relay-bin
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_data_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql
innodb_buffer_pool_size = 8G
character-set-server = utf8
#innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 2047M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[mysql]
default-character-set = utf8

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_save]
syslog

My application uses only InnoDB, but there are few database like mysql, which came with the standard mysql installations are only the ones which uses MyISAM engine type, I guess that should not be my concern though.

As you can see I have replication too, the warning is the same one in replicated server too, whose config is identical as this.


texts: What is meant by Account is disabled in dba_users password column for account status as locked?

texts: When querying dba_views, for some of the users the password column shows account disabled. These users are locked accounts as account_status column says. I want to understand what is meant by account disabled? Is it same as expired and locked account or it has some other meaning?


texts: Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014

texts: I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it is suddenly taking 35 minutes to finish. I have uploaded the actual plan on SQL Server 2014.


texts: Which database could handle storage of billions/trillions of records?

texts:
We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of.  Each day we capture about ~1.4 billion flow records which would look like this in json format:

{
   "tcp_flags": "0",
   "src_as": "54321",
   "nexthop": "1.2.3.4",
   "unix_secs": "1352234521",
   "src_mask": "23",
   "tos": "0",
   "prot": "6",
   "input": "105",
   "doctets": "186",
   "engine_type": "0",
   "exaddr": "2.3.4.5",
   "engine_id": "2",
   "srcaddr": "9.8.7.6",
   "dst_as": "12345",
   "unix_nsecs": "752265174",
   "sysuptime": "2943529544",
   "dst_mask": "24",
   "dstport": "80",
   "last": "2943523241",
   "srcport": "52672",
   "dpkts": "4",
   "output": "111",
   "dstaddr": "6.5.4.3",
   "first": "2943517993"
}

We would like to be able to do fast searches (less than 10 seconds) on the data set, most likely over narrow slices of time (10 - 30 mintes intervals).  We also want to index the majority of the data points so we can do searches on each of them quickly.  We would also like to have an up to date view of the data when searches are executed.  It would be great to stay in the open source world, but we are not opposed to looking at proprietary solutions for this project.

The idea is to keep approximately one month of data, which would be ~43.2 billion records.  A rough estimate that each record would contain about 480 bytes of data, would equate to ~18.7 terabytes of data in a month, and maybe three times that with indexes.  Eventually we would like to grow the capacity of this system to store trillions of records.

We have (very basically) evaluated couchbase, cassandra, and mongodb so far as possible candidates for this project, however each proposes their own challenges.  With couchbase the indexing is done at intervals and not during insertion of the data so the views are not up to date, cassandra's secondary indexes are not very efficient at returning results as they typically require scanning the entire cluster for results, and mongodb looks promising but appears to be far more difficult to scale as it is master/slave/sharded.  Some other candidates we plan to evaluate are elasticsearch, mysql (not sure if this is even applicable), and a few column oriented relational databases.  Any suggestions or real world experience would be appreciated.


texts:
I created a read replica from RDS , and I do want to delete it.

I am so worry about affecting the origin RDS which is in PROD .

Is it recommended to take snapshots before deletion of read replica instance ?
Or it is a safe action , known that AWS console shows the screen when I click on delete?


texts: How to fix accented character substitutions on French text in MySQL db

texts:
I'm building a small database for a charity I volunteer with. It will track events in English and French.  When I enter characters like an accented e (as in "journée") it appears in the database as (journÃ©e).  If I can get it in the database properly the é comes out as a black diamond with a question mark (�).

Can anyone tell me how to prevent this?

Here's my table:

CREATE TABLE `dtf_events` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name_en` VARCHAR(50) NULL DEFAULT NULL,
    `name_fr` VARCHAR(50) NULL DEFAULT NULL,
    `description_en` VARCHAR(256) NULL DEFAULT NULL,
    `description_fr` VARCHAR(256) NULL DEFAULT NULL,
    `publish_start` DATE NULL DEFAULT NULL,
    `publish_end` DATE NULL DEFAULT NULL,
    `event_date` DATE NULL DEFAULT NULL,
    `link` VARCHAR(1024) NULL DEFAULT NULL,
    `category` ENUM('operational','fundraising','news','test') NULL DEFAULT NULL,
    `comment` TEXT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=34
;

And here's a sample of the output I get if the proper accented character is in the db.  Note that this code generates a partial page that is intended to fit into an iframe in a text widget on a WordPress site that I have very little administrative control over.

28-f�v-2018 - Date Limite pour demande d'application
24-mar-2018 - Jour d'informations pour volontaire
23-sep-2018 - Journ�e d�orientation: b�n�voles et familles
24-oct-2018 - Journee du Vol 2018mer &middot; 31-d�c-1969 - start is past, end is future

The é in février shows up with a black diamond with a question mark.  That is pulled from a DATE field in the database in YYYY-MM-DD format and converted with the strftime() function:

$friendly_date = strftime("%a &middot; %d-%b-%Y",strtotime($event_date));

You can see the output in the Evènements à Venir section on the right side of the page at http://yow.dreamstakeflight.ca/accueil/  (I had nothing to do with the design of that site ;{})


texts: Domain Account or Windows account for Transactional Replication

texts:
All the online resources say to use a Windows account for Agent accounts, i.e. Snapshot, Distributor and Log Reader to set up Replication.

See the following links as examples:

Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)
Replication Security Best Practices

My question is: what is the problem with using domain accounts for the above, which is much easier to maintain and administer?


texts: FILESTREAM is enabled, but I get a message that it's not. Anyone able to help?

texts:
I have been given the task of setting SharePoint Foundation 2010 up in work.  I have hit quite a few problems (see other questions I have posted), so I decided to start from scratch again.  I have installed SharePoint and SQL Server 2008.  Now I'm at the step of provisioning the BLOB storage and have the following three queries to run:

use [ContentDbName]
if not exists (select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')create master key encryption by password = N'Admin Key Password !2#4'

use [ContentDbName]
if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')alter database [ContentDbName] add filegroup RBSFilestreamProvider contains filestream

use [ContentDbName]
alter database [ContentDbName] add file (name = RBSFilestreamFile, filename = 'c:\Blobstore') to filegroup RBSFilestreamProvider

The first bit executes without a problem, but the second gives the message:

Msg 5591, Level 16, State 3, Line 2
FILESTREAM feature is disabled.

I have went into the Configuration Manager and checked that FILESTREAM was enabled, which it was.  I restarted the service and still get the same message.  Even restarting the server totally does not help.

What should I do?

Thanks!
Mark


texts: SQL Server LCK_M_X lock from .NET application

texts:
We've recently enabled READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION on our SQL Server 2014 database, which is accessed primarily from 2 versions our ASP.NET application via ADO.NET.

The new version (still in beta) is working fine, while our legacy (currently still in production) version is exhibiting a peculiar problem inside a particular API.

This API opens a DB transaction and executes a number of SELECTs, INSERTs, and UPDATEs over a set of tables (between 3 and 5, depending on the exact request).

The problem is, the API gets stuck when executed by the legacy version of the app. Inspection by Activity Monitor shows that there are two processes with distinct Session IDs (which correspond to two separate DB connections, right?), one of which is trying to execute an UPDATE, while the other is doing nothing else but blocking the first one.

My question is: am I correct in assuming that the legacy version of our app has some sort of bug that causes the API request to use two separate connections in parallel (possibly one for reading and one for updating), which then trigger the lock we're observing?

If not, what else could possibly explain the behavior we're seeing?

What's bothering me is the fact that the new snapshot isolation level should not cause ANY locks between parallel transactions. At least that's how I understand it. I'd be happy to be shown otherwise!

FWIW, killing the inactive session allows the UPDATE to succeed without a problem. Without that, they both remain "hung" forever. The problem also didn't occur (to my knowledge, at least) before we enabled the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options.


texts: Ram & Concurrency: MySQL InnoDB LOAD INLINE FILE on 50 30GB CSV files

texts:
As the title suggests I'm moving around a lot of data. This is in an environment with a 24 core CPU and 2TB of M.2 storage.

My first question is: given I have 16GB of ram, does that mean it's impossible for me to load a 30GB file into SQL (as the entire thing must be stored in ram?)

The second question: This is a fresh table I'm loading into, so I have the luxury of dictating which engine I can use. I've read that InnoDB is better for writing (in which I'm doing alot of). However, MySQL manual says that the CONCURRENCY clause will work on MyISAM. So which engine should I use?

Any other helpful advice is appreciated.

My mysqld conf:

innodb_buffer_pool_size = 12G
innodb_log_file_size    = 4G
innodb_write_io_threads = 18


texts: Grabbing SQL Dump of Master DB Without Downtime

texts:
I'm curious whether downtime will be necessary to grab a SQL dump of my master database.

Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that is being replicated onto slave. All tables in that database are InnoDB. This is the command I want to run:

mysqldump --master-data --single-transaction --hex-blob dbname | gzip > dbname.sql.gz

I'm running MySQL 5.1 and here is a redacted version of my my.cnf file:

[mysqld]
default-storage-engine=InnoDB
character-set-server=UTF8
lower_case_table_names=1
transaction_isolation=READ-COMMITTED
wait_timeout=86400
interactive_timeout=3600
delayed_insert_timeout=10000
connect_timeout=100000
max_connections=750
max_connect_errors=1000
back_log=50
max_allowed_packet=1G
max_heap_table_size=64M
tmp_table_size=64M
bulk_insert_buffer_size=128M
innodb_buffer_pool_size=10000M
innodb_data_file_path=ibdata1:256M:autoextend
innodb_file_per_table=1
innodb_additional_mem_pool_size=32M
innodb_log_file_size=1G
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=240
innodb_flush_log_at_trx_commit=2
innodb_open_files=8192
innodb_support_xa=ON
thread_cache_size=500
expire_logs_days=2
server-id=1
log_bin=1
binlog_format=MIXED
sync_binlog=0

[mysqldump]
max_allowed_packet=128M

Am I good without downtime or not? I'm concerned about a possible read lock being placed on tables.


texts: RMAN & Archivelog Deletion Policy confusion

texts:
Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman.

Tied to the same issue is the configure Archivelog deletion policy to backed up X times to disk.

Deleting input, or deleting input all, seems counterintuitive to me when the deletion policy is set to 7 times to match an overall backup policy of 7 days.

Is there anyone here who can explain to someone who may be just a little dense how this all plays together? And what should the Archivelog deletion policy be if the goal is to have one week’s worth of backups available at all times? All backups are weekly backed up to a tape drive. With seven days as overall, and 7 times on Archivelog deletions policy, am I backing it up 7 times or 7 times 7 times?

Thank you for any help anyone can offer……


texts: Configuration options for relational algebra evaluator "ra.jar"

texts:
I am participating in the Stanford Database Class, and there are some problem sets to be solved in "mathy" relational algebra syntax.

The course instructors demand from students to use this open-source, free jar file "ra.jar"  to submit solutions via web forms.

RA: an interactive relational algebra interpreter
Version 2.1b by Jun Yang (junyang@cs.duke.edu)
>

I wonder if there is a better evaluator available? This one is pretty good but

on the command-line-interpreter prompt, the cursor position is hard
to see
the tool translates relational alegbra experssions to SQL and submits
them to SQLite. It does not show (return to the user) the
intermediate steps.  There should be a -log or verbose option to
return the SQL generated. (The ra tool has a -v option to return the submitted ra query).

I want the CREATE VIEW.... and SELECT ... SQL statements that the jar file generates and sends to SQLite. Alternatively, can I configure SQLite to echo/log all sql commands submitted to it? Is there an option for the .properties file?


texts:
I am trying to understand the below query being run from our developers on read only database which just runs forever.

Table here is partitioned table on partition column trickey per day.

Query is something like below

Select
   a,
   b,
   c,
   d,
   trickey
from
   table
where
   (1=1)
   and (1=1) and convert(varchar(10),trickey,112) BETWEEN 20190913 and 20190913

Will above query not eligible for partition key elimination OR how can i improve or tune up the above query?

Also what is the benefit of (1=1 )used in where clause and does that help or degrade performance?

@Justin- Modified query

Select
       a,
       b,
       c,
       d,
       trickey
    from
       table
    where
       (1=1)
       and (1=1) ---and convert(varchar(10),trickey,112) BETWEEN 20190913 and 20190913 replace this one with modified version

and trickey>=cast('2019-09-13' as date)
and trickey< cast('2019-09-14' as date)


texts: Multi-Subnet AG - DNS flips to secondary IP

texts:
I have a 3-node FCI:  1 primary / 1 secondary co-located in same data center on same subnet, and 1 secondary in a DR data center on another subnet.

Periodically, the virtual IP (VIP) for the AG name flips to the DR site and clients can't connect the Primary site.  The quick fix is to remove the listener from the node in failover cluster manager, then remove the DNS entry that points to the DR site from AD.

I've checked most SQL and Windows logs and I can't find anything related to this.  In fact, the AG doesn't actually flip to the DR site; it's just the DNS entry that gets updated with the incorrect IP for the DR server.

Has anyone else experienced this?  I have 13 AGs, on a 9 node cluster, and only 2 AGs have experienced this.  One of those I tore down and completely rebuilt with a new IP on the AG listeners, but the issue returned a week or two later.

Thanks for any help or info!


texts: How to get the PC models with identical speed?

texts:
I am trying to get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).

Result required as : model with the bigger number, model with the smaller number, speed, and RAM. The result I am expecting is

╔═══════╦═══════╦═══════╦═════╗
║ model ║ model ║ speed ║ ram ║
╠═══════╬═══════╬═══════╬═════╣
║  1233 ║  1121 ║   750 ║ 128 ║
║  1233 ║  1232 ║   500 ║  64 ║
║  1260 ║  1232 ║   500 ║  32 ║
╚═══════╩═══════╩═══════╩═════╝

and table structure is

╔═══════╦═══════╦═════╗
║ model ║ speed ║ ram ║
╠═══════╬═══════╬═════╣
║  1121 ║   600 ║ 128 ║
║  1121 ║   600 ║ 128 ║
║  1121 ║   750 ║ 128 ║
║  1232 ║   450 ║  32 ║
║  1232 ║   450 ║  64 ║
║  1232 ║   500 ║  32 ║
║  1232 ║   500 ║  64 ║
║  1233 ║   500 ║  64 ║
║  1233 ║   750 ║ 128 ║
║  1233 ║   800 ║ 128 ║
║  1233 ║   900 ║ 128 ║
║  1260 ║   500 ║  32 ║
╚═══════╩═══════╩═════╝

The query I am trying is this and I am using MySQL

select pca.model, pcb.model, pca.speed, pca.ram
from pc pca
where pca.speed in (
    select pcb.speed
    from pc pcb
    where pca.speed = pcb.speed
);